const { pool } = require("../../config/db");
const qcService = require("../../services/securityops/qcService");
const path = require("path");
const fs = require('fs');

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

//   try {
//     const { vehicleId, parameters } = req.body;
//     const userId = req.session?.user?.id;

//     if (!vehicleId) {
//       return res.status(400).json({
//         success: false,
//         message: "vehicleId is required",
//       });
//     }

//     await client.query("BEGIN");

//     /* ===== 1. GET VEHICLE ===== */
//     const vehicleRes = await client.query(
//       `SELECT
//       cv.*,
//       md.po_number,
//       md.supplier_name,
//       md.material_type
//    FROM commercial_vehicles cv
//    LEFT JOIN material_details md
//      ON md.vehicle_id = cv.id   --  CORRECT RELATION
//    WHERE cv.id = $1`,
//       [vehicleId],
//     );

//     if (vehicleRes.rowCount === 0) {
//       throw new Error("Vehicle not found");
//     }

//     const vehicle = vehicleRes.rows[0];

//     if (!vehicle.reference_no) {
//       throw new Error("PO Number missing in vehicle entry");
//     }

//     /* ===== 2. GENERATE SAMPLE ID ===== */
//     const lastRes = await client.query(
//       `SELECT sample_id FROM quality_checks ORDER BY created_at DESC LIMIT 1`,
//     );

//     let next = 1;

//     if (lastRes.rows.length > 0) {
//       const lastId = lastRes.rows[0].sample_id;
//       next = parseInt(lastId.split("-")[1]) + 1;
//     }

//     const sampleId = `SMP-${String(next).padStart(3, "0")}`;

//     const qcRes = await client.query(
//       `INSERT INTO quality_checks
//   (sample_id, po_number, vendor, vehicle_number, vehicle_id, parameters, status,created_by)
//   VALUES ($1,$2,$3,$4,$5,$6,$7, $8)
//   RETURNING id`,
//       [
//         sampleId,
//         vehicle.po_number,
//         vehicle.supplier_name,
//         vehicle.vehicle_number,
//         vehicle.id,
//         JSON.stringify(parameters), // IMPORTANT
//         "IN PROGRESS",
//         userId
//       ],
//     );
//     const qcId = qcRes.rows[0].id;

//     /* ===== 4. INSERT PARAMETERS ===== */
//     /* ===== 4. INSERT INTO HISTORY TABLE (NEW) ===== */
//     for (const p of parameters) {
//       await client.query(
//         `INSERT INTO qc_parameter_history
//      (qc_id, parameter_name, value, min, max, temp_status, created_by)
//      VALUES ($1,$2,$3,$4,$5,$6,$7)`,
//         [
//           qcId,
//           p.parameterName,
//           p.value || null,
//           p.min || null,
//           p.max || null,
//           p.tempStatus || "Pending",
//           userId,
//         ],
//       );
//     }

//     /* ===== 5. UPDATE VEHICLE ===== */
//     await client.query(
//       `UPDATE commercial_vehicles
//        SET qc_id = $1,
//            qc_status = $2
//        WHERE id = $3`,
//       [qcId, "IN PROGRESS", vehicleId],
//     );

//     await client.query("COMMIT");

//     return res.json({
//       success: true,
//       message: "QC Sample created",
//       data: { qcId, sampleId },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error(err);

//     return res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

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

//   try {
//     const { vehicleId, items } = req.body;
//     const userId = req.session?.user?.id;

//     if (!vehicleId || !items?.length) {
//       return res.status(400).json({
//         success: false,
//         message: "vehicleId and items are required",
//       });
//     }

//     await client.query("BEGIN");

//     // 🔹 GET VEHICLE
//     const vehicleRes = await client.query(
//       `SELECT cv.*, md.po_number, md.supplier_name
//        FROM commercial_vehicles cv
//        LEFT JOIN material_details md ON md.vehicle_id = cv.id
//        WHERE cv.id = $1`,
//       [vehicleId]
//     );

//     if (!vehicleRes.rowCount) throw new Error("Vehicle not found");

//     const vehicle = vehicleRes.rows[0];

//     // 🔹 GENERATE SAMPLE ID
//     const lastRes = await client.query(
//       `SELECT sample_id FROM quality_checks ORDER BY created_at DESC LIMIT 1`
//     );

//     let next = 1;
//     if (lastRes.rows.length) {
//       next = parseInt(lastRes.rows[0].sample_id.split("-")[1]) + 1;
//     }

//     const sampleId = `SMP-${String(next).padStart(3, "0")}`;

//     // 🔹 INSERT QC HEADER
//     const qcRes = await client.query(
//       `INSERT INTO quality_checks
//       (sample_id, po_number, vendor, vehicle_number, vehicle_id, parameters, status, created_by)
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
//       RETURNING id`,
//       [
//         sampleId,
//         vehicle.po_number,
//         vehicle.supplier_name,
//         vehicle.vehicle_number,
//         vehicle.id,
//         JSON.stringify({ items }), // 🔥 FIXED
//         "IN PROGRESS",
//         userId,
//       ]
//     );

//     const qcId = qcRes.rows[0].id;

//     // 🔹 INSERT HISTORY (IMPORTANT)
//     for (const item of items) {
//       for (const p of item.parameters) {
//         await client.query(
//           `INSERT INTO qc_parameter_history
//           (qc_id, item_code, parameter_name, value, min, max, temp_status, created_by)
//           VALUES ($1,$2,$3,$4,$5,$6,$7,$8)`,
//           [
//             qcId,
//             item.item_code,
//             p.parameterName,
//             p.value || null,
//             p.min || null,
//             p.max || null,
//             p.tempStatus || "Pending",
//             userId,
//           ]
//         );
//       }
//     }

//     // 🔹 UPDATE VEHICLE
//     await client.query(
//       `UPDATE commercial_vehicles
//        SET qc_id = $1, qc_status = $2
//        WHERE id = $3`,
//       [qcId, "IN PROGRESS", vehicleId]
//     );

//     await client.query("COMMIT");

//     res.json({
//       success: true,
//       message: "QC Sample created",
//       data: { qcId, sampleId },
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error(err);

//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };


// 28-5-26
// const createSample = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const { vehicleId, items } = req.body;
//     const userId = req.session?.user?.id;

//     if (!vehicleId || !items?.length) {
//       return res.status(400).json({
//         success: false,
//         message: "vehicleId and items are required",
//       });
//     }

//     await client.query("BEGIN");

//     // 🔒 LOCK VEHICLE ROW TO AVOID DUPLICATE QC CREATION
//     const vehicleLockRes = await client.query(
//       `SELECT id, qc_id, qc_status
//        FROM commercial_vehicles
//        WHERE id = $1
//        FOR UPDATE`,
//       [vehicleId],
//     );

//     if (!vehicleLockRes.rowCount) {
//       throw new Error("Vehicle not found");
//     }

//     // 🔒 DUPLICATE QC VALIDATION
//     if (
//       vehicleLockRes.rows[0].qc_id ||
//       vehicleLockRes.rows[0].qc_status === "IN PROGRESS"
//     ) {
//       throw new Error(
//         "QC sample already exists or is already in progress for this vehicle",
//       );
//     }

//     // 🔹 GET VEHICLE
//     const vehicleRes = await client.query(
//       `SELECT cv.*, md.po_number, md.supplier_name
//        FROM commercial_vehicles cv
//        LEFT JOIN material_details md ON md.vehicle_id = cv.id
//        WHERE cv.id = $1`,
//       [vehicleId],
//     );

//     if (!vehicleRes.rowCount) throw new Error("Vehicle not found");

//     const vehicle = vehicleRes.rows[0];

//     // 🔒 VALIDATE ITEMS
//     for (const item of items) {
//       if (!item.item_code) {
//         throw new Error("item_code is required");
//       }

//       if (!Array.isArray(item.parameters) || !item.parameters.length) {
//         throw new Error(`Parameters missing for item ${item.item_code}`);
//       }

//       // 🔒 REMOVE DUPLICATE PARAMETERS INSIDE SAME ITEM
//       const paramNames = item.parameters.map((p) =>
//         p.parameterName?.trim()?.toLowerCase(),
//       );

//       const duplicateParam = paramNames.find(
//         (name, index) => paramNames.indexOf(name) !== index,
//       );

//       if (duplicateParam) {
//         throw new Error(
//           `Duplicate parameter '${duplicateParam}' found in item ${item.item_code}`,
//         );
//       }
//     }

//     // 🔹 GENERATE SAMPLE ID
//     // 🔒 LOCK LAST ROW TO PREVENT DUPLICATE SAMPLE IDS
//     const lastRes = await client.query(
//       `SELECT sample_id
//        FROM quality_checks
//        ORDER BY created_at DESC
//        LIMIT 1
//        FOR UPDATE`,
//     );

//     let next = 1;

//     if (lastRes.rows.length) {
//       const lastSampleId = lastRes.rows[0].sample_id || "SMP-000";

//       next = parseInt(lastSampleId.split("-")[1] || "0", 10) + 1;
//     }

//     const sampleId = `SMP-${String(next).padStart(3, "0")}`;

//     // 🔒 EXTRA UNIQUE CHECK
//     const existingSample = await client.query(
//       `SELECT id
//        FROM quality_checks
//        WHERE sample_id = $1`,
//       [sampleId],
//     );

//     if (existingSample.rowCount) {
//       throw new Error("Duplicate sample id generated. Please retry.");
//     }

//     // 🔹 INSERT QC HEADER
//     const qcRes = await client.query(
//       `INSERT INTO quality_checks
//       (sample_id, po_number, vendor, vehicle_number, vehicle_id, parameters, status, created_by)
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
//       RETURNING id`,
//       [
//         sampleId,
//         vehicle.po_number,
//         vehicle.supplier_name,
//         vehicle.vehicle_number,
//         vehicle.id,
//         JSON.stringify({ items }),
//         "IN PROGRESS",
//         userId,
//       ],
//     );

//     const qcId = qcRes.rows[0].id;

//     // 🔹 INSERT HISTORY (IMPORTANT)
//     for (const item of items) {
//       for (const p of item.parameters) {
//         // 🔒 AVOID DUPLICATE HISTORY RECORDS
//         const existingHistory = await client.query(
//           `SELECT id
//            FROM qc_parameter_history
//            WHERE qc_id = $1
//            AND item_code = $2
//            AND parameter_name = $3`,
//           [qcId, item.item_code, p.parameterName],
//         );

//         if (existingHistory.rowCount) {
//           continue;
//         }

//         await client.query(
//           `INSERT INTO qc_parameter_history
//           (qc_id, item_code, parameter_name, value, min, max, temp_status, created_by)
//           VALUES ($1,$2,$3,$4,$5,$6,$7,$8)`,
//           [
//             qcId,
//             item.item_code,
//             p.parameterName,
//             p.value || null,
//             p.min || null,
//             p.max || null,
//             p.tempStatus || "Pending",
//             userId,
//           ],
//         );
//       }
//     }

//     // 🔹 UPDATE VEHICLE
//     await client.query(
//       `UPDATE commercial_vehicles
//        SET qc_id = $1, qc_status = $2
//        WHERE id = $3`,
//       [qcId, "IN PROGRESS", vehicleId],
//     );

//     await client.query("COMMIT");

//     res.json({
//       success: true,
//       message: "QC Sample created",
//       data: { qcId, sampleId },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

//     console.error(err);

//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };


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

  try {
    const { vehicleId, items, poNumber,status } = req.body;

    const userId = req.session?.user?.id;

    if (!vehicleId || !items?.length) {
      return res.status(400).json({
        success: false,
        message: "vehicleId and items are required",
      });
    }

    await client.query("BEGIN");

    // =====================================================
    // LOCK VEHICLE
    // =====================================================

    const vehicleLockRes = await client.query(
      `
      SELECT id, qc_id, qc_status
      FROM commercial_vehicles
      WHERE id = $1
      FOR UPDATE
      `,
      [vehicleId],
    );

    if (!vehicleLockRes.rowCount) {
      throw new Error("Vehicle not found");
    }

    if (
      vehicleLockRes.rows[0].qc_id ||
      vehicleLockRes.rows[0].qc_status === "IN PROGRESS"
    ) {
      throw new Error(
        "QC sample already exists or already in progress for this vehicle",
      );
    }

    // =====================================================
    // GET VEHICLE
    // =====================================================

    const vehicleRes = await client.query(
      `
      SELECT
        cv.*,

        COALESCE(
          md.po_number,
          cv.reference_no,
          $2
        ) AS po_number,

        md.supplier_name

      FROM commercial_vehicles cv

      LEFT JOIN material_details md
        ON md.vehicle_id = cv.id

      WHERE cv.id = $1
      `,
      [vehicleId, poNumber || null],
    );

    if (!vehicleRes.rowCount) {
      throw new Error("Vehicle not found");
    }

    const vehicle = vehicleRes.rows[0];

    // =====================================================
    // VALIDATIONS
    // =====================================================

    for (const item of items) {
      if (!item.item_code) {
        throw new Error("item_code is required");
      }

      if (
        !Array.isArray(item.parameters) ||
        !item.parameters.length
      ) {
        throw new Error(
          `Parameters missing for item ${item.item_code}`,
        );
      }

      const paramNames = item.parameters.map((p) =>
        p.parameterName?.trim()?.toLowerCase(),
      );

      const duplicateParam = paramNames.find(
        (name, index) => paramNames.indexOf(name) !== index,
      );

      if (duplicateParam) {
        throw new Error(
          `Duplicate parameter '${duplicateParam}' found in item ${item.item_code}`,
        );
      }
    }

    // =====================================================
    // GENERATE SAMPLE ID
    // =====================================================

    const lastRes = await client.query(
      `
      SELECT sample_id
      FROM quality_checks
      ORDER BY created_at DESC
      LIMIT 1
      FOR UPDATE
      `,
    );

    let next = 1;

    if (lastRes.rows.length) {
      const lastSampleId =
        lastRes.rows[0].sample_id || "SMP-000";

      next =
        parseInt(
          lastSampleId.split("-")[1] || "0",
          10,
        ) + 1;
    }

    const sampleId = `SMP-${String(next).padStart(
      3,
      "0",
    )}`;

    // =====================================================
    // QC CALCULATIONS
    // =====================================================

    const receivedWeight = Number(
      vehicle.in_weight || 0,
    );

    const out_weight = vehicle.out_weight;
    const net_weight = vehicle.net_weight;

    let totalDeductionPercent = 0;

    let qcResult = "Accepted";

    const processedItems = items.map((item) => {
      const processedParams = item.parameters.map((p) => {
        const value = Number(p.value);

        let parameterStatus = "Accepted";

        let deductionPercent = 0;

        // =========================================
        // BELOW
        // =========================================

        if (p.condition === "below") {
          if (value <= Number(p.specMax)) {
            parameterStatus = "Accepted";
          } else if (
            p.penaltyMax !== null &&
            value <= Number(p.penaltyMax)
          ) {
            deductionPercent = Number(
              (
                value - Number(p.specMax)
              ).toFixed(3),
            );

            parameterStatus =
              "Accepted With Deduction";

            qcResult = "Accepted With Deductions";
          } else {
            parameterStatus = "Rejected";

            qcResult = "Full Rejected";
          }
        }

        // =========================================
        // ABOVE
        // =========================================

        if (p.condition === "above") {
          if (value >= Number(p.specMin)) {
            parameterStatus = "Accepted";
          } else if (
            p.penaltyMin !== null &&
            value >= Number(p.penaltyMin)
          ) {
            deductionPercent = Number(
              (
                Number(p.specMin) - value
              ).toFixed(3),
            );

            parameterStatus =
              "Accepted With Deduction";

            qcResult = "Accepted With Deductions";
          } else {
            parameterStatus = "Rejected";

            qcResult = "Full Rejected";
          }
        }

        // =========================================
        // BETWEEN
        // =========================================

        if (p.condition === "between") {
          if (
            value >= Number(p.specMin) &&
            value <= Number(p.specMax)
          ) {
            parameterStatus = "Accepted";
          } else {
            parameterStatus = "Rejected";

            qcResult = "Full Rejected";
          }
        }

        totalDeductionPercent += deductionPercent;

        const deductionWeight = Number(
          (
            (receivedWeight * deductionPercent) /
            100
          ).toFixed(3),
        );

        return {
          ...p,

          parameterStatus,

          deductionPercent,

          deductionWeight,
        };
      });

      return {
        ...item,

        parameters: processedParams,
      };
    });

    // =====================================================
    // TOTAL DEDUCTION
    // =====================================================

    const totalDeductionWeight = Number(
      (
        (receivedWeight * totalDeductionPercent) /
        100
      ).toFixed(3),
    );

    // =====================================================
    // BAG CALCULATIONS
    // =====================================================

    const bagType = items[0]?.bagType || "";

    const bagQty = Number(
      items[0]?.bagQty || 0,
    );

    let bagWeightPerBag = 0;

    if (bagType === "Gunny Bags") {
      bagWeightPerBag = 600;
    } else if (bagType === "PVC Bags") {
      bagWeightPerBag = 200;
    }

    const bagDeductionWeight = Number(
      (
        (bagQty * bagWeightPerBag) /
        1000
      ).toFixed(3),
    );

    // =====================================================
    // FINAL WEIGHT
    // =====================================================

    const finalWeight = Number(
      (
        receivedWeight -
        totalDeductionWeight -
        bagDeductionWeight
      ).toFixed(3),
    );

    // =====================================================
    // INSERT QC HEADER
    // =====================================================

    const qcRes = await client.query(
      `
      INSERT INTO quality_checks
      (
        sample_id,
        po_number,
        vendor,
        vehicle_number,
        vehicle_id,
        parameters,
        status,
        qc_result,
        received_weight,
         out_weight,
  net_weight,
        total_deduction_percent,
        total_deduction_weight,
        final_weight,
        bag_type,
        bag_qty,
        bag_deduction_weight,
        bag_weight_per_bag,
        created_by
      )
      VALUES
      (
        $1,$2,$3,$4,$5,
        $6,$7,$8,$9,$10,
        $11,$12,$13,$14,
        $15,$16,$17,$18,$19
      )
      RETURNING id
      `,
      [
        sampleId,

        vehicle.po_number || null,

        vehicle.supplier_name || null,

        vehicle.vehicle_number,

        vehicle.id,

        JSON.stringify({
          items: processedItems,
        }),

        "IN PROGRESS",

        qcResult,

        receivedWeight,
        out_weight,
        net_weight,

        totalDeductionPercent,

        totalDeductionWeight,

        finalWeight,

        bagType || null,

        bagQty || null,

        bagDeductionWeight,

        bagWeightPerBag,

        userId,
      ],
    );

    const qcId = qcRes.rows[0].id;

    // =====================================================
    // INSERT PARAMETER HISTORY
    // =====================================================

    for (const item of processedItems) {
      for (const p of item.parameters) {
        await client.query(
          `
          INSERT INTO qc_parameter_history
          (
            qc_id,
            item_code,
            parameter_name,
            value,
            condition,
            spec_min,
            spec_max,
            penalty_min,
            penalty_max,
             temp_status,
            parameter_status,
            deduction_percent,
            deduction_weight,
            created_by
          )
          VALUES
          (
            $1,$2,$3,$4,$5,$6,
            $7,$8,$9,$10,$11,$12,$13,$14
          )
          `,
          [
            qcId,

            item.item_code,

            p.parameterName,

            p.value,

            p.condition,

            p.specMin,

            p.specMax,

            p.penaltyMin,

            p.penaltyMax,

            status,
            p.parameterStatus,

            p.deductionPercent,

            p.deductionWeight,

            userId,
          ],
        );
      }
    }

    // =====================================================
    // UPDATE VEHICLE
    // =====================================================

    await client.query(
      `
      UPDATE commercial_vehicles
      SET
        qc_id = $1,
        qc_status = $2
      WHERE id = $3
      `,
      [
        qcId,
        "IN PROGRESS",
        vehicleId,
      ],
    );

    await client.query("COMMIT");

    return res.json({
      success: true,

      message:
        "QC Sample created successfully",

      data: {
        qcId,
        sampleId,
        qcResult,
        receivedWeight,
        totalDeductionPercent,
        totalDeductionWeight,
        bagDeductionWeight,
        finalWeight,
        items: processedItems,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");

    console.error(
      "CREATE QC ERROR",
      err,
    );

    return res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

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

//   try {
//     const { page = 1, limit = 10, search = "", order = "desc" } = req.query;

//     const pageNumber = Math.max(parseInt(page) || 1, 1);
//     const pageSize = Math.max(parseInt(limit) || 10, 1);
//     const offset = (pageNumber - 1) * pageSize;

//     //  Prevent SQL injection
//     const sortOrder = order?.toLowerCase() === "asc" ? "ASC" : "DESC";

//     const dataQuery = `
//       WITH qc_data AS (
//         SELECT
//           qc.id,
//           qc.sample_id,
//           qc.po_number,
//           qc.vendor,
//           md.material_type AS material_type,  --  FIXED
//           md.item_id AS item_id,
//            md.item_code AS item_code,
//           qc.vehicle_number,
//           COALESCE(qc.parameters, '{}'::jsonb) AS parameters,
//           qc.status,
//           qc.created_at,
//           FALSE AS is_pending,
//           CONCAT(u.first_name, ' ', u.last_name) AS created_by
//         FROM quality_checks qc
//         LEFT JOIN material_details md
//           ON md.vehicle_id = qc.vehicle_id   -- ⚠️ adjust if needed
//           LEFT JOIN users u
//     ON u.id = qc.created_by   -- IMPORTANT

//         WHERE qc.is_active = TRUE
//         AND (
//           $1 = '' OR
//           qc.sample_id ILIKE '%' || $1 || '%' OR
//           qc.po_number ILIKE '%' || $1 || '%' OR
//           qc.vehicle_number ILIKE '%' || $1 || '%' OR
//           qc.vendor ILIKE '%' || $1 || '%' OR
//           md.material_type ILIKE '%' || $1 || '%'
//         )
//       ),

//       pending_data AS (
//         SELECT
//           cv.id,
//           NULL::VARCHAR AS sample_id,
//           md.po_number::VARCHAR AS po_number,
//           md.supplier_name AS vendor,
//           md.material_type AS material_type,
//           md.item_id AS item_id,
//           md.item_code AS item_code,
//           cv.vehicle_number,
//           '[]'::jsonb AS parameters,
//           'ENTRY DONE' AS status,
//           cv.created_at,
//           TRUE AS is_pending,
//           NULL::TEXT AS created_by
//         FROM commercial_vehicles cv
//         LEFT JOIN material_details md
//           ON md.vehicle_id = cv.id

//         WHERE
//           cv.qc_id IS NULL
//           AND cv.gate_status = 'Inside'
//           AND cv.is_deleted = FALSE
//           AND cv.weighbridge_status = 'COMPLETED'

//           AND (
//             $1 = '' OR
//             cv.vehicle_number ILIKE '%' || $1 || '%' OR
//             md.po_number ILIKE '%' || $1 || '%' OR
//             md.supplier_name ILIKE '%' || $1 || '%' OR
//             md.material_type ILIKE '%' || $1 || '%'
//           )
//       ),

//       merged AS (
//         SELECT * FROM qc_data
//         UNION ALL
//         SELECT * FROM pending_data
//       )

//       SELECT *
//       FROM merged
//       ORDER BY created_at ${sortOrder}
//       LIMIT $2 OFFSET $3;
//     `;

//     const countQuery = `
//       SELECT
//         (
//           SELECT COUNT(*)
//           FROM quality_checks qc
//           LEFT JOIN material_details md
//             ON md.vehicle_id = qc.vehicle_id
//           WHERE qc.is_active = TRUE
//           AND (
//             $1 = '' OR
//             qc.sample_id ILIKE '%' || $1 || '%' OR
//             qc.po_number ILIKE '%' || $1 || '%' OR
//             qc.vehicle_number ILIKE '%' || $1 || '%' OR
//             qc.vendor ILIKE '%' || $1 || '%' OR
//             md.material_type ILIKE '%' || $1 || '%'
//           )
//         ) +
//         (
//           SELECT COUNT(*)
//           FROM commercial_vehicles cv
//           LEFT JOIN material_details md
//             ON md.vehicle_id = cv.id
//           WHERE
//             cv.qc_id IS NULL
//             AND cv.gate_status = 'Inside'
//             AND cv.is_deleted = FALSE
//             AND cv.weighbridge_status = 'COMPLETED'
//             AND (
//               $1 = '' OR
//               cv.vehicle_number ILIKE '%' || $1 || '%' OR
//               md.po_number ILIKE '%' || $1 || '%' OR
//               md.supplier_name ILIKE '%' || $1 || '%' OR
//               md.material_type ILIKE '%' || $1 || '%'
//             )
//         ) AS total_records;
//     `;

//     const dataResult = await client.query(dataQuery, [
//       search,
//       pageSize,
//       offset,
//     ]);
//     const countResult = await client.query(countQuery, [search]);

//     const totalRecords = parseInt(countResult.rows[0].total_records, 10);

//     return res.status(200).json({
//       success: true,
//       data: dataResult.rows,
//       pagination: {
//         totalRecords,
//         currentPage: pageNumber,
//         totalPages: Math.ceil(totalRecords / pageSize),
//         pageSize,
//       },
//     });
//   } catch (error) {
//     console.error("QC GET ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch QC Samples",
//     });
//   } finally {
//     client.release();
//   }
// };

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

//   try {
//     const { id } = req.params;
//     const { poNumber, vehicleNumber, vendor, status, parameters } = req.body;

//     await client.query("BEGIN");

//     /* ===== 1. CHECK EXISTING ===== */
//     const existingRes = await client.query(
//       `SELECT * FROM quality_checks WHERE id = $1`,
//       [id]
//     );

//     if (existingRes.rowCount === 0) {
//       throw new Error("QC Sample not found");
//     }

//     const existingSample = existingRes.rows[0];

//     /* ===== 2. UPDATE BASIC FIELDS ===== */
//     const updateFields = [];
//     const values = [];
//     let idx = 1;

//     if (poNumber) {
//       updateFields.push(`po_number = $${idx++}`);
//       values.push(poNumber.trim().toUpperCase());
//     }

//     if (vehicleNumber) {
//       updateFields.push(`vehicle_number = $${idx++}`);
//       values.push(vehicleNumber.trim().toUpperCase());
//     }

//     if (vendor) {
//       updateFields.push(`vendor = $${idx++}`);
//       values.push(vendor);
//     }

//     if (status) {
//       updateFields.push(`status = $${idx++}`);
//       values.push(status);
//     }
//     const userId = req.session?.user?.id;

//     if (userId) {
//       updateFields.push(`updated_by = $${idx++}`);
//       values.push(userId);
//     }

//     //  update timestamp
//     updateFields.push(`updated_at = NOW()`);

//     if (updateFields.length > 0) {
//       await client.query(
//         `UPDATE quality_checks
//          SET ${updateFields.join(", ")}
//          WHERE id = $${idx}`,
//         [...values, id]
//       );
//     }

//     /* ===== 3. UPDATE PARAMETERS SNAPSHOT (JSONB) ===== */
//     if (parameters && parameters.length > 0) {
//       await client.query(
//         `UPDATE quality_checks
//          SET parameters = $1
//          WHERE id = $2`,
//         [JSON.stringify(parameters), id]
//       );

//       /* ===== 4. INSERT INTO HISTORY (IMPORTANT) ===== */
//       for (const param of parameters) {
//         let tempStatus = param.tempStatus || "Approved";

//         await client.query(
//           `INSERT INTO qc_parameter_history
//            (qc_id, parameter_name, value, min, max, temp_status, created_by)
//            VALUES ($1,$2,$3,$4,$5,$6,$7)`,
//           [
//             id,
//             param.parameterName,
//             param.value || null,
//             param.min || null,
//             param.max || null,
//             tempStatus,
//             req.session.user?.id || null,
//           ]
//         );
//       }
//     }

//     await client.query("COMMIT");

//     /* ===== 5. RETURN UPDATED ===== */
//     const updatedRes = await client.query(
//       `SELECT * FROM quality_checks WHERE id = $1`,
//       [id]
//     );

//     return res.status(200).json({
//       success: true,
//       message: "QC Sample updated successfully",
//       data: updatedRes.rows[0],
//     });

//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("QC UPDATE ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: error.message || "Failed to update QC Sample",
//     });
//   } finally {
//     client.release();
//   }
// };

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

  try {
    const { page = 1, limit = 10, search = "", order = "desc" } = req.query;

    const pageNumber = Math.max(parseInt(page) || 1, 1);

    const pageSize = Math.max(parseInt(limit) || 10, 1);

    const offset = (pageNumber - 1) * pageSize;

    const sortOrder = order?.toLowerCase() === "asc" ? "ASC" : "DESC";

    // =====================================================
    // MAIN DATA QUERY
    // =====================================================

    const dataQuery = `

      WITH 
      qc_data AS (

  SELECT

    qc.id,

    qc.sample_id,

    qc.po_number,

    qc.vendor,

    md.material_type AS material_type,

    md.item_id AS item_id,

    md.item_code AS item_code,

    qc.vehicle_number,

    COALESCE(
      qc.parameters,
      '[]'::jsonb
    ) AS parameters,

    qc.status,

    qc.created_at,

    FALSE AS is_pending,

    CONCAT(
      u.first_name,
      ' ',
      u.last_name
    ) AS created_by,

    -- SHIPMENT SUPPORT
    cv.source_type,

    cv.shipment_id,

    cv.shipment_number,

    cv.sales_order_number,

    sd.customer_name,
    cv.in_weight,
cv.delivery_qty

  FROM quality_checks qc

  LEFT JOIN material_details md
  ON md.vehicle_id = qc.vehicle_id

  LEFT JOIN users u
  ON u.id = qc.created_by

  -- IMPORTANT
  LEFT JOIN commercial_vehicles cv
  ON cv.id = qc.vehicle_id

  LEFT JOIN shipment_dispatches sd
  ON sd.id = cv.shipment_id

  WHERE

    qc.is_active = TRUE

    AND (

      $1 = ''

      OR

      qc.sample_id ILIKE '%' || $1 || '%'

      OR

      qc.po_number ILIKE '%' || $1 || '%'

      OR

      qc.vehicle_number ILIKE '%' || $1 || '%'

      OR

      qc.vendor ILIKE '%' || $1 || '%'

      OR

      md.material_type ILIKE '%' || $1 || '%'

      OR

      cv.sales_order_number ILIKE '%' || $1 || '%'

      OR

      sd.customer_name ILIKE '%' || $1 || '%'

    )

),

      pending_data AS (

        SELECT

          cv.id,

          NULL::VARCHAR AS sample_id,

          -- NORMAL FLOW → PO
          -- SHIPMENT FLOW → SO
          COALESCE(
            md.po_number,
            sd.sales_order_number
          )::VARCHAR AS po_number,

          -- NORMAL → Vendor
          -- SHIPMENT → Customer
          COALESCE(
            md.supplier_name,
            sd.customer_name
          ) AS vendor,

          -- MATERIAL / PRODUCT
          COALESCE(
            md.material_type,
            sdi.product_name
          ) AS material_type,

          COALESCE(
            md.item_id,
            sdi.product_id
          ) AS item_id,

          md.item_code AS item_code,

          cv.vehicle_number,

          '[]'::jsonb AS parameters,

          'ENTRY DONE' AS status,

          cv.created_at,

          TRUE AS is_pending,

          NULL::TEXT AS created_by,

          -- SHIPMENT SUPPORT
          cv.source_type,

          cv.shipment_id,

          cv.shipment_number,

          cv.sales_order_number,

          sd.customer_name,
          cv.in_weight,

cv.delivery_qty

        FROM commercial_vehicles cv

        -- NORMAL FLOW
        LEFT JOIN material_details md
        ON md.vehicle_id = cv.id

        -- SHIPMENT FLOW
        LEFT JOIN shipment_dispatches sd
        ON sd.id = cv.shipment_id

        LEFT JOIN shipment_dispatch_items sdi
        ON sdi.shipment_id = sd.id

        LEFT JOIN procurement_itemmaster pim
        ON pim.id = sdi.product_id

        WHERE

          cv.qc_id IS NULL

          AND cv.gate_status = 'Inside'

          AND cv.is_deleted = FALSE

          AND cv.weighbridge_status IN ('IN_PROGRESS', 'COMPLETED')

          -- QC REQUIRED FLOW
          AND (

            cv.source_type IS NULL

            OR

            cv.source_type = 'NORMAL'

            OR

            (

              cv.source_type = 'SHIPMENT'

              AND pim.qc_required = TRUE

            )

          )

          AND (

            $1 = ''

            OR

            cv.vehicle_number ILIKE '%' || $1 || '%'

            OR

            md.po_number ILIKE '%' || $1 || '%'

            OR

            md.supplier_name ILIKE '%' || $1 || '%'

            OR

            md.material_type ILIKE '%' || $1 || '%'

            OR

            sd.sales_order_number ILIKE '%' || $1 || '%'

            OR

            sd.customer_name ILIKE '%' || $1 || '%'

            OR

            sdi.product_name ILIKE '%' || $1 || '%'

          )

      ),

      merged AS (

        SELECT * FROM qc_data

        UNION ALL

        SELECT * FROM pending_data

      )

      SELECT *

      FROM merged

      ORDER BY created_at ${sortOrder}

      LIMIT $2 OFFSET $3

    `;

    // =====================================================
    // COUNT QUERY
    // =====================================================

    const countQuery = `

      SELECT COUNT(*) AS total_records

      FROM (

        SELECT qc.id

        FROM quality_checks qc

        WHERE qc.is_active = TRUE

        UNION ALL

        SELECT cv.id

        FROM commercial_vehicles cv

        LEFT JOIN shipment_dispatches sd
        ON sd.id = cv.shipment_id

        LEFT JOIN shipment_dispatch_items sdi
        ON sdi.shipment_id = sd.id

        LEFT JOIN procurement_itemmaster pim
        ON pim.id = sdi.product_id

        WHERE

          cv.qc_id IS NULL

          AND cv.gate_status = 'Inside'

          AND cv.is_deleted = FALSE

          AND cv.weighbridge_status = 'COMPLETED'

          AND (

            cv.source_type IS NULL

            OR

            cv.source_type = 'NORMAL'

            OR

            (

              cv.source_type = 'SHIPMENT'

              AND pim.qc_required = TRUE

            )

          )

      ) AS merged_count

    `;

    const dataResult = await client.query(dataQuery, [
      search,
      pageSize,
      offset,
    ]);

    const countResult = await client.query(countQuery);

    const totalRecords = parseInt(countResult.rows[0].total_records, 10);

    return res.status(200).json({
      success: true,

      data: dataResult.rows,

      pagination: {
        totalRecords,

        currentPage: pageNumber,

        totalPages: Math.ceil(totalRecords / pageSize),

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

    return res.status(500).json({
      success: false,

      message: "Failed to fetch QC samples",

      error: error.message,
    });
  } finally {
    client.release();
  }
};

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

//   try {
//     const { id } = req.params;
//     const { poNumber, vehicleNumber, vendor, status, items } = req.body;
//     const userId = req.session?.user?.id;

//     if (!items?.length) {
//       return res.status(400).json({
//         success: false,
//         message: "items are required",
//       });
//     }

//     await client.query("BEGIN");

//     // 🔹 CHECK EXISTING
//     const existingRes = await client.query(
//       `SELECT * FROM quality_checks WHERE id = $1`,
//       [id],
//     );

//     if (!existingRes.rowCount) {
//       throw new Error("QC Sample not found");
//     }

//     // 🔹 UPDATE BASIC FIELDS
//     const updateFields = [];
//     const values = [];
//     let idx = 1;

//     if (poNumber) {
//       updateFields.push(`po_number = $${idx++}`);
//       values.push(poNumber.trim().toUpperCase());
//     }

//     if (vehicleNumber) {
//       updateFields.push(`vehicle_number = $${idx++}`);
//       values.push(vehicleNumber.trim().toUpperCase());
//     }

//     if (vendor) {
//       updateFields.push(`vendor = $${idx++}`);
//       values.push(vendor);
//     }

//     if (status) {
//       updateFields.push(`status = $${idx++}`);
//       values.push(status);
//     }

//     if (userId) {
//       updateFields.push(`updated_by = $${idx++}`);
//       values.push(userId);
//     }

//     updateFields.push(`updated_at = NOW()`);

//     if (updateFields.length) {
//       await client.query(
//         `UPDATE quality_checks 
//          SET ${updateFields.join(", ")}
//          WHERE id = $${idx}`,
//         [...values, id],
//       );
//     }

//     // 🔥 UPDATE PARAMETERS (MULTI ITEM)
//     await client.query(
//       `UPDATE quality_checks SET parameters = $1 WHERE id = $2`,
//       [JSON.stringify({ items }), id],
//     );

//     // 🔥 INSERT HISTORY (IMPORTANT)
//     for (const item of items) {
//       for (const p of item.parameters) {
//         await client.query(
//           `INSERT INTO qc_parameter_history
//           (qc_id, item_code, parameter_name, value, min, max, temp_status, created_by)
//           VALUES ($1,$2,$3,$4,$5,$6,$7,$8)`,
//           [
//             id,
//             item.item_code,
//             p.parameterName,
//             p.value || null,
//             p.min || null,
//             p.max || null,
//             p.tempStatus || "Approved",
//             userId,
//           ],
//         );
//       }
//     }

//     await client.query("COMMIT");

//     res.json({
//       success: true,
//       message: "QC Sample updated successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("QC UPDATE ERROR:", err);

//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

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

  try {
    const { id } = req.params;
    const { poNumber, vehicleNumber, vendor, status, items } = req.body;
    console.log(items)
    const userId = req.session?.user?.id;

    if (!items?.length) {
      return res.status(400).json({
        success: false,
        message: "items are required",
      });
    }

    await client.query("BEGIN");

    // 🔹 CHECK EXISTING
    const existingRes = await client.query(
      `SELECT * FROM quality_checks WHERE id = $1`,
      [id],
    );

    if (!existingRes.rowCount) {
      throw new Error("QC Sample not found");
    }

    // 🔹 UPDATE BASIC FIELDS
    const updateFields = [];
    const values = [];
    let idx = 1;

    if (poNumber) {
      updateFields.push(`po_number = $${idx++}`);
      values.push(poNumber.trim().toUpperCase());
    }

    if (vehicleNumber) {
      updateFields.push(`vehicle_number = $${idx++}`);
      values.push(vehicleNumber.trim().toUpperCase());
    }

    if (vendor) {
      updateFields.push(`vendor = $${idx++}`);
      values.push(vendor);
    }

    if (status) {
      updateFields.push(`status = $${idx++}`);
      values.push(status);
    }

    if (userId) {
      updateFields.push(`updated_by = $${idx++}`);
      values.push(userId);
    }

    updateFields.push(`updated_at = NOW()`);

    if (updateFields.length) {
      await client.query(
        `UPDATE quality_checks 
         SET ${updateFields.join(", ")}
         WHERE id = $${idx}`,
        [...values, id],
      );
    }

    // 🔥 UPDATE PARAMETERS (MULTI ITEM)
   // =====================================================
// GET EXISTING QC
// =====================================================

const qcRes = await client.query(
  `
  SELECT *
  FROM quality_checks
  WHERE id = $1
  `,
  [id],
);

const qcData = qcRes.rows[0];

const receivedWeight = Number(
  qcData.received_weight || 0,
);

let totalDeductionPercent = 0;

let qcResult = "Accepted";

// =====================================================
// QC CALCULATIONS
// =====================================================

const processedItems = items.map((item) => {
  const processedParams = item.parameters.map((p) => {
    const value = Number(p.value);

    let parameterStatus = "Accepted";

    let deductionPercent = 0;

    // =========================================
    // BELOW
    // =========================================

    if (p.condition === "below") {
      if (value <= Number(p.specMax)) {
        parameterStatus = "Accepted";
      } else if (
        p.penaltyMax !== null &&
        value <= Number(p.penaltyMax)
      ) {
        deductionPercent = Number(
          (
            value - Number(p.specMax)
          ).toFixed(3),
        );

        parameterStatus =
          "Accepted With Deduction";

        qcResult = "Accepted With Deductions";
      } else {
        parameterStatus = "Rejected";

        qcResult = "Full Rejected";
      }
    }

    // =========================================
    // ABOVE
    // =========================================

    if (p.condition === "above") {
      if (value >= Number(p.specMin)) {
        parameterStatus = "Accepted";
      } else if (
        p.penaltyMin !== null &&
        value >= Number(p.penaltyMin)
      ) {
        deductionPercent = Number(
          (
            Number(p.specMin) - value
          ).toFixed(3),
        );

        parameterStatus =
          "Accepted With Deduction";

        qcResult = "Accepted With Deductions";
      } else {
        parameterStatus = "Rejected";

        qcResult = "Full Rejected";
      }
    }

    // =========================================
    // BETWEEN
    // =========================================

    if (p.condition === "between") {
      if (
        value >= Number(p.specMin) &&
        value <= Number(p.specMax)
      ) {
        parameterStatus = "Accepted";
      } else {
        parameterStatus = "Rejected";

        qcResult = "Full Rejected";
      }
    }

    totalDeductionPercent += deductionPercent;

    const deductionWeight = Number(
      (
        (receivedWeight * deductionPercent) /
        100
      ).toFixed(3),
    );

    return {
      ...p,

      parameterStatus,

      deductionPercent,

      deductionWeight,
    };
  });

  return {
    ...item,

    parameters: processedParams,
  };
});

// =====================================================
// TOTAL DEDUCTION
// =====================================================

const totalDeductionWeight = Number(
  (
    (receivedWeight * totalDeductionPercent) /
    100
  ).toFixed(3),
);

// =====================================================
// BAG CALCULATIONS
// =====================================================

const bagType = items[0]?.bagType || "";

const bagQty = Number(
  items[0]?.bagQty || 0,
);

let bagWeightPerBag = 0;

if (bagType === "Gunny Bags") {
  bagWeightPerBag = 600;
} else if (bagType === "PVC Bags") {
  bagWeightPerBag = 200;
}

const bagDeductionWeight = Number(
  (
    (bagQty * bagWeightPerBag) /
    1000
  ).toFixed(3),
);

// =====================================================
// FINAL WEIGHT
// =====================================================

const finalWeight = Number(
  (
    receivedWeight -
    totalDeductionWeight -
    bagDeductionWeight
  ).toFixed(3),
);

// =====================================================
// UPDATE QC
// =====================================================

await client.query(
  `
 UPDATE quality_checks
SET
  parameters = $1,
  qc_result = $2,
  total_deduction_percent = $3,
  total_deduction_weight = $4,
  final_weight = $5,
  bag_type = $6,
  bag_qty = $7,
  bag_deduction_weight = $8,
  bag_weight_per_bag = $9
WHERE id = $10
  `,
 [
  JSON.stringify({
    items: processedItems,
  }),

  qcResult,

  totalDeductionPercent,

  totalDeductionWeight,

  finalWeight,

  bagType || null,

  bagQty || null,

  bagDeductionWeight,

  bagWeightPerBag,

  id,
],
);

// =====================================================
// INSERT HISTORY
// =====================================================

for (const item of processedItems) {
  for (const p of item.parameters) {
    await client.query(
      `
      INSERT INTO qc_parameter_history
      (
        qc_id,
        item_code,
        parameter_name,
        value,
        condition,
        spec_min,
        spec_max,
        penalty_min,
        penalty_max,
        temp_status,
        parameter_status,
        deduction_percent,
        deduction_weight,
        created_by
      )
      VALUES
      (
        $1,$2,$3,$4,$5,$6,
        $7,$8,$9,$10,$11,$12,$13,$14
      )
      `,
      [
        id,

        item.item_code,

        p.parameterName,

        p.value,

        p.condition,

        p.specMin,

        p.specMax,

        p.penaltyMin,

        p.penaltyMax,

        status,


        p.parameterStatus,

        p.deductionPercent,

        p.deductionWeight,

        userId,
      ],
    );
  }
}
    await client.query("COMMIT");

    res.json({
      success: true,
      message: "QC Sample updated successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("QC UPDATE ERROR:", err);

    res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

const getParameterHistory = async (req, res) => {
  try {
    const { sampleId } = req.params;

    const history = await qcService.getParameterHistory(sampleId);

    return res.status(200).json({
      success: true,
      count: history.length,
      data: history,
    });
  } catch (error) {
    console.error("QC HISTORY ERROR:", error);

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

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

//   try {
//     const { id } = req.params;

//     await client.query("BEGIN");

//     /* ===== 1. CHECK QC EXISTS ===== */
//     const qcRes = await client.query(
//       `SELECT * FROM quality_checks WHERE id = $1`,
//       [id]
//     );

//     if (qcRes.rowCount === 0) {
//       throw new Error("Sample not found");
//     }

//     const qc = qcRes.rows[0];

//     /* ===== 2. CHECK ALREADY COMPLETED ===== */
//     if (qc.complete_status === "Completed") {
//       return res.status(400).json({
//         success: false,
//         message: "QC already completed",
//       });
//     }

//     /* ===== 3. GET LATEST STATUS PER PARAM ===== */
//     const historyRes = await client.query(
//       `
//       SELECT DISTINCT ON (parameter_name)
//         parameter_name,
//         temp_status
//       FROM qc_parameter_history
//       WHERE qc_id = $1
//       ORDER BY parameter_name, created_at DESC
//       `,
//       [id]
//     );

//     const history = historyRes.rows;

//     let finalStatus = "APPROVED";

//     for (const param of history) {
//       if (param.temp_status === "Rejected") {
//         finalStatus = "REJECTED";
//         break;
//       }

//       if (param.temp_status === "Pending") {
//         return res.status(400).json({
//           success: false,
//           message: "Some parameters still pending",
//         });
//       }
//     }

//     /* ===== 4. UPDATE QC ===== */
//     await client.query(
//       `
//       UPDATE quality_checks
//       SET status = $1,
//           complete_status = 'Completed',
//           updated_at = NOW()
//       WHERE id = $2
//       `,
//       [finalStatus, id]
//     );

//     /* ===== 5. UPDATE VEHICLE ===== */
//     await client.query(
//       `
//       UPDATE commercial_vehicles
//       SET qc_status = $1
//       WHERE id = $2
//       `,
//       [finalStatus, qc.vehicle_id]
//     );

//     await client.query("COMMIT");

//     return res.json({
//       success: true,
//       message: "QC Completed Successfully",
//       data: {
//         qcId: id,
//         status: finalStatus,
//       },
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("COMPLETE QC ERROR:", err);

//     return res.status(500).json({
//       success: false,
//       message: err.message || "Failed to complete QC",
//     });
//   } finally {
//     client.release();
//   }
// };
const completeQC = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;

    await client.query("BEGIN");

    /* ===== 1. CHECK QC EXISTS ===== */
    const qcRes = await client.query(
      `SELECT id, vehicle_id, complete_status 
       FROM quality_checks 
       WHERE id = $1`,
      [id],
    );

    if (!qcRes.rowCount) {
      throw new Error("Sample not found");
    }

    const qc = qcRes.rows[0];

    /* ===== 2. CHECK ALREADY COMPLETED ===== */
    if (qc.complete_status === "Completed") {
      return res.status(400).json({
        success: false,
        message: "QC already completed",
      });
    }

    /* ===== 3. GET LATEST STATUS PER ITEM + PARAM ===== */
    const historyRes = await client.query(
      `
      SELECT DISTINCT ON (item_code, parameter_name)
        item_code,
        parameter_name,
        temp_status
      FROM qc_parameter_history
      WHERE qc_id = $1
      ORDER BY item_code, parameter_name, created_at DESC
      `,
      [id],
    );

    const history = historyRes.rows;

    if (!history.length) {
      return res.status(400).json({
        success: false,
        message: "No QC parameters found",
      });
    }

    let finalStatus = "APPROVED";

    /* ===== 4. VALIDATE ALL PARAMETERS ===== */
    for (const param of history) {
      // ❌ Pending not allowed
      if (param.temp_status === "Pending") {
        await client.query("ROLLBACK");

        return res.status(400).json({
          success: false,
          message: `Pending: ${param.item_code} - ${param.parameter_name}`,
        });
      }

      // ❌ Any rejection → overall rejected
      if (param.temp_status === "Rejected") {
        finalStatus = "REJECTED";
      }
    }

    /* ===== 5. UPDATE QC ===== */
    await client.query(
      `
      UPDATE quality_checks
      SET status = $1,
          complete_status = 'Completed',
          updated_at = NOW()
      WHERE id = $2
      `,
      [finalStatus, id],
    );

    /* ===== 6. UPDATE VEHICLE ===== */
    await client.query(
      `
      UPDATE commercial_vehicles
      SET qc_status = $1
      WHERE id = $2
      `,
      [finalStatus, qc.vehicle_id],
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "QC Completed Successfully",
      data: {
        qcId: id,
        status: finalStatus,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("COMPLETE QC ERROR:", err);

    return res.status(500).json({
      success: false,
      message: err.message || "Failed to complete QC",
    });
  } finally {
    client.release();
  }
};

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

  try {
    const {
      vehicleId,

      shipmentId,

      source_type,

      qc_status,

      product_id,

      product_name,

      parameters,

      checklist,

      remarks,
    } = req.body;

    const userId = req.session?.user?.id;

    if (!vehicleId || !shipmentId) {
      return res.status(400).json({
        success: false,

        message: "Vehicle and shipment required",
      });
    }

    await client.query("BEGIN");

    // =====================================
    // GET VEHICLE + SHIPMENT
    // =====================================

    const vehicleRes = await client.query(
      `
       SELECT

  cv.id,

  cv.vehicle_number,

  cv.shipment_number,

  cv.sales_order_number,

  sd.customer_name

FROM commercial_vehicles cv

LEFT JOIN shipment_dispatches sd
ON sd.id = cv.shipment_id

WHERE cv.id = $1

    
        `,

      [vehicleId],
    );

    if (!vehicleRes.rowCount) {
      throw new Error("Vehicle not found");
    }

    const vehicle = vehicleRes.rows[0];

    // =====================================
    // GENERATE SAMPLE ID
    // =====================================

    const lastRes = await client.query(
      `
        SELECT sample_id
        FROM quality_checks
        ORDER BY id DESC
        LIMIT 1
        `,
    );

    let next = 1;

    if (lastRes.rows.length) {
      const last = lastRes.rows[0].sample_id || "DQC-000";

      next = parseInt(last.split("-")[1] || "0", 10) + 1;
    }

    const sampleId = `DQC-${String(next).padStart(3, "0")}`;

    // =====================================
    // INSERT QC
    // =====================================

    const qcInsert = await client.query(
      `
        INSERT INTO quality_checks (

          sample_id,

          po_number,

vendor,

          source_type,

          shipment_id,

          shipment_number,

          sales_order_number,

          customer_name,

          vehicle_id,

          vehicle_number,

          status,

          complete_status,

          dispatch_qc_data,

          parameters,

          remarks,

          created_by,

          created_at

        )

        VALUES (

          $1,$2,$3,$4,$5,
          $6,$7,$8,$9,$10,
          $11,$12,$13,$14,$15,$16,NOW()

        )

        RETURNING id
        `,

      [
        sampleId,

        source_type,

        vehicle.sales_order_number,

        vehicle.customer_name,

        shipmentId,

        vehicle.shipment_number,

        vehicle.sales_order_number,

        vehicle.customer_name,

        vehicleId,

        vehicle.vehicle_number,

        qc_status,

        "Completed",

        JSON.stringify({
          checklist,

          parameters,

          remarks,
        }),

        JSON.stringify(parameters),

        remarks,

        userId,
      ],
    );

    const qcId = qcInsert.rows[0].id;

    // =====================================
    // UPDATE VEHICLE
    // =====================================

    await client.query(
      `
      UPDATE commercial_vehicles

      SET

        qc_id = $1,

        qc_status = $2,

        updated_at = NOW()

      WHERE id = $3
      `,

      [qcId, qc_status, vehicleId],
    );

    // =====================================
    // UPDATE SHIPMENT
    // =====================================

    const shipmentStatus =
      qc_status === "APPROVED" ? "Ready for Loading" : "QC Rejected";

    await client.query(
      `
      UPDATE shipment_dispatches

      SET

        shipment_status = $1,

        updated_at = NOW()

      WHERE id = $2
      `,

      [shipmentStatus, shipmentId],
    );

    await client.query("COMMIT");

    return res.json({
      success: true,

      message: `Dispatch QC ${qc_status}`,

      data: {
        qcId,

        sampleId,

        status: shipmentStatus,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");

    console.error("DISPATCH QC ERROR:", err);

    return res.status(500).json({
      success: false,

      message: err.message || "Failed to create dispatch QC",
    });
  } finally {
    client.release();
  }
};


const downloadQCCertificate = async (
  req,
  res
) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `
      SELECT
        qc_certificate_file
      FROM quality_checks
      WHERE id = $1
      AND is_deleted = false
      `,
      [id]
    );

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

    const filePath = path.join(
      process.cwd(),
      result.rows[0].qc_certificate_file
    );

    if (!fs.existsSync(filePath)) {
      return res.status(404).json({
        success: false,
        message: "PDF file not found",
      });
    }

    return res.download(
      filePath,
      `QC-Certificate-${id}.pdf`
    );
  } catch (error) {
    console.error(
      "Download QC Certificate Error:",
      error
    );

    return res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

module.exports = {
  createSample,
  getAllSamples,
  updateSample,
  getParameterHistory,
  completeQC,
  createDispatchQC,
  downloadQCCertificate,
};
