const UOM = require("../../models/securityops/uomModel");
const {pool} = require("../../config/db")

/* ================= CREATE UOM ================= */

// const createUom = async (req, res) => {
//   try {

//     const { name, symbol, description } = req.body;

//     if (!name || !symbol) {
//       return res.status(400).json({
//         success: false,
//         message: "Name and Symbol are required"
//       });
//     }

//     const existing = await UOM.findOne({
//       name: name.toUpperCase(),
//       isDeleted: false
//     });

//     if (existing) {
//       return res.status(409).json({
//         success: false,
//         message: "Unit already exists"
//       });
//     }

//     const uom = await UOM.create({
//       name: name.trim().toUpperCase(),
//       symbol: symbol.trim().toUpperCase(),
//       description: description || null,
//       createdBy: req.user?.id || null
//     });

//     return res.status(201).json({
//       success: true,
//       message: "Unit created successfully",
//       data: uom
//     });

//   } catch (error) {
//     console.error("CREATE UOM ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Failed to create unit"
//     });
//   }
// };

const createUom = async (req, res) => {
  const client = await pool.connect();

  try {
    const { name, symbol, description } = req.body;

    //  Validation (same as Mongo)
    if (!name || !symbol) {
      return res.status(400).json({
        success: false,
        message: "Name and Symbol are required",
      });
    }

    const upperName = name.trim().toUpperCase();
    const upperSymbol = symbol.trim().toUpperCase();

    //  Check existing (same logic)
    const existingQuery = `
      SELECT id FROM unit_of_measurements
      WHERE name = $1 AND is_deleted = FALSE
      LIMIT 1;
    `;

    const existingResult = await client.query(existingQuery, [upperName]);

    if (existingResult.rows.length > 0) {
      return res.status(409).json({
        success: false,
        message: "Unit already exists",
      });
    }

    //  Insert
    const insertQuery = `
      INSERT INTO unit_of_measurements
      (name, symbol, description, created_by)
      VALUES ($1, $2, $3, $4)
      RETURNING 
        id,
        name,
        symbol,
        description,
        is_active AS "isActive",
        is_deleted AS "isDeleted",
        created_by AS "createdBy",
        updated_by AS "updatedBy",
        created_at AS "createdAt",
        updated_at AS "updatedAt";
    `;

    const values = [
      upperName,
      upperSymbol,
      description || null,
      req.user?.id || null,
    ];

    const result = await client.query(insertQuery, values);

    return res.status(201).json({
      success: true,
      message: "Unit created successfully",
      data: result.rows[0], //  same structure returned
    });

  } catch (error) {
    console.error("CREATE UOM ERROR:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to create unit",
    });
  } finally {
    client.release();
  }
};


/* ================= GET ALL UOM ================= */

// const getAllUoms = async (req, res) => {
//   try {

//     const uoms = await UOM.find({
//       isDeleted: false
//     })
//     .sort({ createdAt: -1 });

//     return res.status(200).json({
//       success: true,
//       data: uoms
//     });

//   } catch (error) {

//     console.error("GET UOM ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch units"
//     });

//   }
// };
const getAllUoms = async (req, res) => {
  const client = await pool.connect();

  try {
    let { page = 1, limit = 10 } = req.query;

    page = parseInt(page, 10);
    limit = parseInt(limit, 10);

    if (isNaN(page) || page < 1) page = 1;
    if (isNaN(limit) || limit < 1) limit = 10;

    const offset = (page - 1) * limit;

    // Data Query
    const dataQuery = `
      SELECT
        id,
        name,
        symbol,
        description,
        is_active AS "isActive",
        is_deleted AS "isDeleted",
        created_by AS "createdBy",
        updated_by AS "updatedBy",
        created_at AS "createdAt",
        updated_at AS "updatedAt"
      FROM unit_of_measurements
      WHERE is_deleted = FALSE
      ORDER BY created_at DESC
      LIMIT $1 OFFSET $2;
    `;

    // Count Query
    const countQuery = `
      SELECT COUNT(*) FROM unit_of_measurements
      WHERE is_deleted = FALSE;
    `;

    const [dataResult, countResult] = await Promise.all([
      client.query(dataQuery, [limit, offset]),
      client.query(countQuery),
    ]);

    const total = parseInt(countResult.rows[0].count, 10);

    return res.status(200).json({
      success: true,
      data: dataResult.rows,
      pagination: {
        total,
        page,
        pages: Math.ceil(total / limit),
      },
    });

  } catch (error) {
    console.error("GET UOM ERROR:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to fetch units",
    });

  } finally {
    client.release();
  }
};


/* ================= UPDATE UOM ================= */

// const updateUom = async (req, res) => {

//   try {

//     const { id } = req.params;
//     const { name, symbol, description, isActive } = req.body;

//     const uom = await UOM.findById(id);

//     if (!uom || uom.isDeleted) {
//       return res.status(404).json({
//         success: false,
//         message: "Unit not found"
//       });
//     }

//     if (name) {
//       uom.name = name.trim().toUpperCase();
//     }

//     if (symbol) {
//       uom.symbol = symbol.trim().toUpperCase();
//     }

//     if (description !== undefined) {
//       uom.description = description;
//     }

//     if (isActive !== undefined) {
//       uom.isActive = isActive;
//     }

//     uom.updatedBy = req.user?.id || null;

//     await uom.save();

//     return res.status(200).json({
//       success: true,
//       message: "Unit updated successfully",
//       data: uom
//     });

//   } catch (error) {

//     console.error("UPDATE UOM ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Failed to update unit"
//     });

//   }

// };
const updateUom = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;
    const { name, symbol, description, isActive } = req.body;

    //  Validate ID
    if (!id || isNaN(id)) {
      return res.status(400).json({
        success: false,
        message: "Invalid Unit ID",
      });
    }

    //  Check if exists & not deleted
    const checkQuery = `
      SELECT * FROM unit_of_measurements
      WHERE id = $1 AND is_deleted = FALSE
      LIMIT 1;
    `;

    const checkResult = await client.query(checkQuery, [id]);

    if (checkResult.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Unit not found",
      });
    }

    const existing = checkResult.rows[0];

    //  Prepare updated values (same logic as Mongo)
    const updatedName =
      name !== undefined ? name.trim().toUpperCase() : existing.name;
    const updatedSymbol =
      symbol !== undefined ? symbol.trim().toUpperCase() : existing.symbol;
    const updatedDescription =
      description !== undefined ? description : existing.description;
    const updatedIsActive =
      isActive !== undefined ? isActive : existing.is_active;

    //  Optional duplicate check (same as create)
    if (name) {
      const duplicateQuery = `
        SELECT id FROM unit_of_measurements
        WHERE name = $1 AND is_deleted = FALSE AND id != $2
        LIMIT 1;
      `;
      const duplicateResult = await client.query(duplicateQuery, [
        updatedName,
        id,
      ]);

      if (duplicateResult.rows.length > 0) {
        return res.status(409).json({
          success: false,
          message: "Unit already exists",
        });
      }
    }

    //  Update
    const updateQuery = `
      UPDATE unit_of_measurements
      SET
        name = $1,
        symbol = $2,
        description = $3,
        is_active = $4,
        updated_by = $5,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $6
      RETURNING
        id,
        name,
        symbol,
        description,
        is_active AS "isActive",
        is_deleted AS "isDeleted",
        created_by AS "createdBy",
        updated_by AS "updatedBy",
        created_at AS "createdAt",
        updated_at AS "updatedAt";
    `;

    const values = [
      updatedName,
      updatedSymbol,
      updatedDescription,
      updatedIsActive,
      req.user?.id || null,
      id,
    ];

    const result = await client.query(updateQuery, values);

    return res.status(200).json({
      success: true,
      message: "Unit updated successfully",
      data: result.rows[0], //  same response shape
    });

  } catch (error) {
    console.error("UPDATE UOM ERROR:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to update unit",
    });
  } finally {
    client.release();
  }
};


/* ================= DELETE UOM ================= */

// const deleteUom = async (req, res) => {

//   try {

//     const { id } = req.params;

//     const uom = await UOM.findById(id);

//     if (!uom || uom.isDeleted) {
//       return res.status(404).json({
//         success: false,
//         message: "Unit not found"
//       });
//     }

//     uom.isDeleted = true;
//     uom.updatedBy = req.user?.id || null;

//     await uom.save();

//     return res.status(200).json({
//       success: true,
//       message: "Unit deleted successfully"
//     });

//   } catch (error) {

//     console.error("DELETE UOM ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Failed to delete unit"
//     });

//   }

// };
const deleteUom = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;

    //  Validate ID
    if (!id || isNaN(id)) {
      return res.status(400).json({
        success: false,
        message: "Invalid Unit ID",
      });
    }

    //  Check if exists & not deleted
    const checkQuery = `
      SELECT id FROM unit_of_measurements
      WHERE id = $1 AND is_deleted = FALSE
      LIMIT 1;
    `;

    const checkResult = await client.query(checkQuery, [id]);

    if (checkResult.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Unit not found",
      });
    }

    //  Soft delete
    const deleteQuery = `
      UPDATE unit_of_measurements
      SET 
        is_deleted = TRUE,
        updated_by = $2,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $1;
    `;

    await client.query(deleteQuery, [
      id,
      req.user?.id || null,
    ]);

    return res.status(200).json({
      success: true,
      message: "Unit deleted successfully",
    });

  } catch (error) {
    console.error("DELETE UOM ERROR:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to delete unit",
    });

  } finally {
    client.release();
  }
};


module.exports = {
    createUom,
    getAllUoms,
    deleteUom,
    updateUom,

}