const { pool } = require("../../config/db");

/* ===========================
   ✅ CREATE PRODUCT
=========================== */
const createProduct = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const {
      product_name,
      // product_code,
      purity,
      uom_id,
      denaturant_type,
      hsn_code,
      gst_rate,
      storage_type,
      status,
      // org_id,
    } = req.body;

    if (!product_name) {
      return res.status(400).json({
        success: false,
        message: "Product name and code are required",
      });
    }

    // 1️⃣ Get prefix
    const prefixRes = await pool.query(
      `SELECT prefix_code 
   FROM prefixes 
   WHERE module_name = $1 
     AND org_id = $2 
     AND is_deleted = false 
   LIMIT 1`,
      ["sales_products", org_id],
    );

    const prefix = prefixRes.rows[0]?.prefix_code || "PR-";

    // 2️⃣ Get last product_code
    const lastRes = await pool.query(
      `SELECT product_code 
   FROM sales_products 
   WHERE org_id = $1 
     AND product_code LIKE $2
   ORDER BY created_at DESC 
   LIMIT 1`,
      [org_id, `${prefix}%`],
    );

    // 3️⃣ Extract last number
    let nextNumber = 1;

    if (lastRes.rows.length > 0) {
      const lastCode = lastRes.rows[0].product_code; // e.g., PR-005
      const lastNum = parseInt(lastCode.replace(prefix, ""), 10);

      if (!isNaN(lastNum)) {
        nextNumber = lastNum + 1;
      }
    }

    // 4️⃣ Format (001, 002...)
    const padded = String(nextNumber).padStart(4, "0");

    // 5️⃣ Final product code
    const generatedCode = `${prefix}${padded}`;

    // const productImage = req.file?.path || null;
    const productImage = req.file?.filename || null;

    const query = `
  INSERT INTO sales_products (
    product_name,
    product_code,
    purity,
     uom_id, 
    denaturant_type,
    hsn_code,
    gst_rate,
    storage_type,
    product_image,
    status,
    org_id,
    created_by,
    description,
    stock
  )
  VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
  RETURNING *;
`;

    const values = [
      product_name,
      generatedCode,
      purity || null,
      uom_id,
      denaturant_type,
      hsn_code,
      gst_rate,
      storage_type,
      productImage,
      status || "active",
      org_id || null,
      userId || null,
      req.body.description || null,
      1
    ];

    const result = await pool.query(query, values);

    res.status(201).json({
      success: true,
      message: "Product created successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error(err);

    if (err.code === "23505") {
      return res.status(400).json({
        success: false,
        message: "Duplicate product code",
      });
    }

    res.status(500).json({ message: "Create failed" });
  }
};

/* ===========================
   ✅ GET PRODUCTS (PAGINATION)
=========================== */
const getProducts = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    const page = Math.max(parseInt(req.query.page) || 1, 1);
    const limit = Math.min(parseInt(req.query.limit) || 10, 100);
    const offset = (page - 1) * limit;

    const whereClause = `WHERE p.is_deleted = false AND p.org_id = $1`;

    // ✅ COUNT
    const countResult = await pool.query(
      `SELECT COUNT(*) FROM sales_products p ${whereClause}`,
      [org_id],
    );

    const totalRecords = parseInt(countResult.rows[0].count);
    const totalPages = Math.ceil(totalRecords / limit);

    // ✅ DATA QUERY
    const result = await pool.query(
      `
     SELECT 
  p.*,
  u.id AS uom_id,
  u.symbol AS unit,
  u.name AS unit_name,

  cu.name AS created_by_name,
  uu.name AS updated_by_name

FROM sales_products p

LEFT JOIN unit_of_measurements u 
  ON p.uom_id = u.id

LEFT JOIN users cu 
  ON p.created_by = cu.id

LEFT JOIN users uu 
  ON p.updated_by = uu.id

WHERE p.is_deleted = false 
  AND p.org_id = $1

ORDER BY p.created_at DESC
LIMIT $2 OFFSET $3
      `,
      [org_id, limit, offset],
    );

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        total: totalRecords,
        page,
        limit,
        totalPages,
      },
    });
  } catch (err) {
    console.error("FETCH PRODUCTS ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
    GET PRODUCT BY ID
=========================== */
const getProductById = async (req, res) => {
  try {
    const id = parseInt(req.params.id);

    if (!id) {
      return res.status(400).json({
        success: false,
        message: "Invalid product ID",
      });
    }

    const result = await pool.query(
      `
    SELECT 
  p.*,

  u.id AS uom_id,
  u.symbol AS unit,
  u.name AS unit_name,

  cu.name AS created_by_name,
  uu.name AS updated_by_name

FROM sales_products p

LEFT JOIN unit_of_measurements u 
  ON p.uom_id = u.id

LEFT JOIN users cu 
  ON p.created_by = cu.id

LEFT JOIN users uu 
  ON p.updated_by = uu.id

WHERE p.id = $1 
  AND p.is_deleted = false
      `,
      [id],
    );

    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Product not found",
      });
    }

    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET PRODUCT ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Failed to fetch product",
    });
  }
};

/* ===========================
   ✅ UPDATE PRODUCT
=========================== */
// const updateProduct = async (req, res) => {
//   try {
//     const { id } = req.params;

//     const {
//       product_name,
//       product_code,
//       purity,
//       unit,
//       denaturant_type,
//       hsn_code,
//       gst_rate,
//       storage_type,
//       status,
//     } = req.body;

//     const productImage = req.file?.path || null;

//     const query = `
//       UPDATE products SET
//         product_name = COALESCE($1, product_name),
//         product_code = COALESCE($2, product_code),
//         purity = COALESCE($3, purity),
//         unit = COALESCE($4, unit),
//         denaturant_type = COALESCE($5, denaturant_type),
//         hsn_code = COALESCE($6, hsn_code),
//         gst_rate = COALESCE($7, gst_rate),
//         storage_type = COALESCE($8, storage_type),
//         status = COALESCE($9, status),
//         product_image = COALESCE($10, product_image),
//         updated_at = NOW()
//       WHERE id = $11
//       RETURNING *;
//     `;

//     const values = [
//       product_name,
//       product_code,
//       purity,
//       unit,
//       denaturant_type,
//       hsn_code,
//       gst_rate,
//       storage_type,
//       status,
//       productImage,
//       id,
//     ];

//     const result = await pool.query(query, values);

//     res.json({
//       success: true,
//       message: "Product updated successfully",
//       data: result.rows[0],
//     });

//   } catch (err) {
//     console.error(err);
//     res.status(500).json({ message: "Update failed" });
//   }
// };

const updateProduct = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.session.user.id;

    const {
      product_name,
      product_code,
      purity,
      uom_id,
      denaturant_type,
      hsn_code,
      gst_rate,
      storage_type,
      status,
    } = req.body;

    const productImage = req.file?.filename || null;

    const query = `
      UPDATE sales_products SET
        product_name = COALESCE($1, product_name),
        product_code = COALESCE($2, product_code),
        purity = COALESCE($3, purity),
        uom_id = COALESCE($4, uom_id),
        denaturant_type = COALESCE($5, denaturant_type),
        hsn_code = COALESCE($6, hsn_code),
        gst_rate = COALESCE($7, gst_rate),
        storage_type = COALESCE($8, storage_type),
        status = COALESCE($9, status),
        product_image = COALESCE($10, product_image),
        description = COALESCE($11, description),
        updated_by = $12,
        updated_at = NOW()
      WHERE id = $13
        AND is_deleted = false
      RETURNING *;
    `;

    const values = [
      product_name || null,
      product_code || null,
      purity || null,
      uom_id || null,
      denaturant_type || null,
      hsn_code || null,
      gst_rate || null,
      storage_type || null,
      status || null,
      productImage,
      req.body.description || null,
      userId,
      id,
    ];

    const result = await pool.query(query, values);

    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Product not found",
      });
    }

    res.json({
      success: true,
      message: "Product updated successfully ✅",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("PATCH UPDATE ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Update failed",
    });
  }
};
/* ===========================
   ✅ DELETE PRODUCT (SOFT)
=========================== */
const deleteProduct = async (req, res) => {
  try {
    const { id } = req.params;

    await pool.query(
      "UPDATE sales_products SET is_deleted = true WHERE id = $1",
      [id],
    );

    res.json({
      success: true,
      message: "Product deleted successfully",
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: "Delete failed" });
  }
};

module.exports = {
  createProduct,
  getProducts,
  getProductById,
  updateProduct,
  deleteProduct,
};
