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 payload = JSON.parse(req.body.payload);

    const { vehicleId, items, poNumber,status } = payload;
    
const attachment_path = req.file
  ? req.file.path.replace(/\\/g, "/")
  : null;

    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,
        attachment_path
      )
      VALUES
      (
        $1,$2,$3,$4,$5,
        $6,$7,$8,$9,$10,
        $11,$12,$13,$14,
        $15,$16,$17,$18,$19,$20
      )
      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,
        attachment_path,
      ],
    );

    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

//     `;
// 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 (REMOVED - set to NULL or default)
//     NULL::VARCHAR AS source_type,

//     NULL::INT AS shipment_id,

//     NULL::VARCHAR AS shipment_number,

//     NULL::VARCHAR AS sales_order_number,

//     NULL::VARCHAR AS customer_name,

//     qc.received_weight AS in_weight,
    
//     NULL::NUMERIC AS 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

//   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,
//     CASE 
//       WHEN cv.source_type = 'PO' THEN md.po_number::VARCHAR
//       WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN so.so_number
//       ELSE NULL::VARCHAR
//     END AS po_number,
//     CASE 
//       WHEN cv.source_type = 'PO' THEN md.supplier_name
//       WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN cv.customer_name
//       ELSE NULL
//     END AS vendor,
//     CASE 
//       WHEN cv.source_type = 'PO' THEN md.material_type
//       WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN pim.item_name
//       ELSE NULL
//     END AS material_type,
//     CASE 
//       WHEN cv.source_type = 'PO' THEN md.item_id
//       WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN da.product_id
//       ELSE NULL
//     END AS item_id,
//     CASE 
//       WHEN cv.source_type = 'PO' THEN md.item_code
//       WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN pim.item_code
//       ELSE NULL
//     END AS item_code,
//     cv.vehicle_number,
//     '[]'::jsonb AS parameters,
//     'ENTRY DONE' AS status,
//     cv.created_at,
//     TRUE AS is_pending,
//     cv.created_by,
//     cv.source_type,
//     cv.shipment_id,
//     cv.shipment_number,
//     cv.sales_order_number,
//     cv.customer_name,
//     cv.in_weight,
//     cv.delivery_qty
//   FROM commercial_vehicles cv
  
//   -- Left join for PO source
//   LEFT JOIN material_details md ON md.vehicle_id = cv.id AND cv.source_type = 'PO'
  
//   -- Left join for Shipment/Sales Order source using dispatch_allocation_id
//   LEFT JOIN dispatch_allocations da ON da.id = cv.dispatch_allocation_id 
//     AND cv.source_type IN ('SHIPMENT', 'SALES_ORDER')
  
//   -- Left join to get sales order details
//   LEFT JOIN sales_orders so ON so.id = COALESCE(da.sales_order_id, cv.sales_order_id::integer)
  
//   -- Join procurement_itemmaster for product details and QC check
//   LEFT JOIN procurement_itemmaster pim ON pim.id = COALESCE(md.item_id, da.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 condition
//     AND (
//       -- For PO source: QC must be required
//       (cv.source_type = 'PO' AND (pim.qc_required = TRUE OR pim.qc_required IS NULL))
//       OR
//       -- For SHIPMENT/SALES_ORDER source: always include (can add QC condition if needed)
//       cv.source_type IN ('SHIPMENT', 'SALES_ORDER')
//       OR
//       -- For NULL source_type (legacy data)
//       cv.source_type IS NULL
//     )
//     -- Search filter
//     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 so.so_number ILIKE '%' || $1 || '%'
//       OR cv.customer_name ILIKE '%' || $1 || '%'
//       OR pim.item_name ILIKE '%' || $1 || '%'
//       OR pim.item_code ILIKE '%' || $1 || '%'
//       OR cv.shipment_number ILIKE '%' || $1 || '%'
//       OR cv.sales_order_number 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 dataQuery = `

//   WITH 
//   qc_data AS (
//     SELECT
//       qc.id::INTEGER AS id,
//       qc.sample_id::VARCHAR AS sample_id,
//       qc.po_number::VARCHAR AS po_number,
//       qc.vendor::VARCHAR AS vendor,
//       md.material_type::VARCHAR AS material_type,
//       md.item_id::INTEGER AS item_id,
//       md.item_code::VARCHAR AS item_code,
//       qc.vehicle_number::VARCHAR AS vehicle_number,
//       COALESCE(qc.parameters, '[]'::jsonb) AS parameters,
//       qc.status::VARCHAR AS status,
//       qc.created_at::TIMESTAMP AS created_at,
//       FALSE::BOOLEAN AS is_pending,
//       CONCAT(u.first_name, ' ', u.last_name)::VARCHAR AS created_by,
//       NULL::VARCHAR AS source_type,
//       NULL::INTEGER AS shipment_id,
//       NULL::VARCHAR AS shipment_number,
//       NULL::VARCHAR AS sales_order_number,
//       NULL::VARCHAR AS customer_name,
//       qc.received_weight::NUMERIC AS in_weight,
//       NULL::NUMERIC AS 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
//     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::INTEGER AS id,
//       NULL::VARCHAR AS sample_id,
//       CASE 
//         WHEN cv.source_type = 'PO' THEN md.po_number::VARCHAR
//         WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN so.so_number::VARCHAR
//         ELSE NULL::VARCHAR
//       END AS po_number,
//       CASE 
//         WHEN cv.source_type = 'PO' THEN md.supplier_name::VARCHAR
//         WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN cv.customer_name::VARCHAR
//         ELSE NULL::VARCHAR
//       END AS vendor,
//       CASE 
//         WHEN cv.source_type = 'PO' THEN md.material_type::VARCHAR
//         WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN pim.item_name::VARCHAR
//         ELSE NULL::VARCHAR
//       END AS material_type,
//       CASE 
//         WHEN cv.source_type = 'PO' THEN md.item_id::INTEGER
//         WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN da.product_id::INTEGER
//         ELSE NULL::INTEGER
//       END AS item_id,
//       CASE 
//         WHEN cv.source_type = 'PO' THEN md.item_code::VARCHAR
//         WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN pim.item_code::VARCHAR
//         ELSE NULL::VARCHAR
//       END AS item_code,
//       cv.vehicle_number::VARCHAR AS vehicle_number,
//       '[]'::jsonb AS parameters,
//       'ENTRY DONE'::VARCHAR AS status,
//       cv.created_at::TIMESTAMP AS created_at,
//       TRUE::BOOLEAN AS is_pending,
//       cv.created_by::VARCHAR AS created_by,
//       cv.source_type::VARCHAR AS source_type,
//       cv.shipment_id::INTEGER AS shipment_id,
//       cv.shipment_number::VARCHAR AS shipment_number,
//       cv.sales_order_number::VARCHAR AS sales_order_number,
//       cv.customer_name::VARCHAR AS customer_name,
//       cv.in_weight::NUMERIC AS in_weight,
//       cv.delivery_qty::NUMERIC AS delivery_qty
//     FROM commercial_vehicles cv
//     LEFT JOIN material_details md ON md.vehicle_id = cv.id AND cv.source_type = 'PO'
//     LEFT JOIN dispatch_allocations da ON da.id = cv.dispatch_allocation_id 
//       AND cv.source_type IN ('SHIPMENT', 'SALES_ORDER')
//     LEFT JOIN sales_orders so ON so.id = COALESCE(da.sales_order_id, cv.sales_order_id::integer)
//     LEFT JOIN procurement_itemmaster pim ON pim.id = COALESCE(md.item_id, da.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')
//       AND (
//         (cv.source_type = 'PO' AND (pim.qc_required = TRUE OR pim.qc_required IS NULL))
//         OR cv.source_type IN ('SHIPMENT', 'SALES_ORDER')
//         OR cv.source_type IS NULL
//       )
//       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 so.so_number ILIKE '%' || $1 || '%'
//         OR cv.customer_name ILIKE '%' || $1 || '%'
//         OR pim.item_name ILIKE '%' || $1 || '%'
//         OR pim.item_code ILIKE '%' || $1 || '%'
//         OR cv.shipment_number ILIKE '%' || $1 || '%'
//         OR cv.sales_order_number 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 dataQuery = `

  WITH 
  qc_data AS (
    SELECT
      qc.id::INTEGER AS id,
      qc.sample_id::VARCHAR AS sample_id,
      qc.po_number::VARCHAR AS po_number,
      qc.vendor::VARCHAR AS vendor,
      md.material_type::VARCHAR AS material_type,
      md.item_id::INTEGER AS item_id,
      md.item_code::VARCHAR AS item_code,
      qc.vehicle_number::VARCHAR AS vehicle_number,
      COALESCE(qc.parameters, '[]'::jsonb) AS parameters,
      qc.status::VARCHAR AS status,
      qc.created_at::TIMESTAMP AS created_at,
      FALSE::BOOLEAN AS is_pending,
      CONCAT(u.first_name, ' ', u.last_name)::VARCHAR AS created_by,
      NULL::VARCHAR AS source_type,
      NULL::INTEGER AS shipment_id,
      NULL::VARCHAR AS shipment_number,
      NULL::VARCHAR AS sales_order_number,
      NULL::VARCHAR AS customer_name,
      qc.received_weight::NUMERIC AS in_weight,
      NULL::NUMERIC AS 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
    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::INTEGER AS id,
      NULL::VARCHAR AS sample_id,
      -- FIXED: Get PO number from material_details for PO source
      CASE 
        WHEN cv.source_type = 'PO' OR cv.source_type IS NULL THEN md.po_number::VARCHAR
        WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN so.so_number::VARCHAR
        ELSE NULL::VARCHAR
      END AS po_number,
      -- FIXED: Get vendor from material_details for PO source
      CASE 
        WHEN cv.source_type = 'PO' OR cv.source_type IS NULL THEN md.supplier_name::VARCHAR
        WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN cv.customer_name::VARCHAR
        ELSE NULL::VARCHAR
      END AS vendor,
      -- FIXED: Get material_type from material_details for PO source
      CASE 
        WHEN cv.source_type = 'PO' OR cv.source_type IS NULL THEN md.material_type::VARCHAR
        WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN pim.item_name::VARCHAR
        ELSE NULL::VARCHAR
      END AS material_type,
      -- FIXED: Get item_id from material_details for PO source
      CASE 
        WHEN cv.source_type = 'PO' OR cv.source_type IS NULL THEN md.item_id::INTEGER
        WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN da.product_id::INTEGER
        ELSE NULL::INTEGER
      END AS item_id,
      -- FIXED: Get item_code from material_details for PO source
      CASE 
        WHEN cv.source_type = 'PO' OR cv.source_type IS NULL THEN md.item_code::VARCHAR
        WHEN cv.source_type IN ('SHIPMENT', 'SALES_ORDER') THEN pim.item_code::VARCHAR
        ELSE NULL::VARCHAR
      END AS item_code,
      cv.vehicle_number::VARCHAR AS vehicle_number,
      '[]'::jsonb AS parameters,
      'ENTRY DONE'::VARCHAR AS status,
      cv.created_at::TIMESTAMP AS created_at,
      TRUE::BOOLEAN AS is_pending,
      cv.created_by::VARCHAR AS created_by,
      cv.source_type::VARCHAR AS source_type,
      cv.shipment_id::INTEGER AS shipment_id,
      cv.shipment_number::VARCHAR AS shipment_number,
      cv.sales_order_number::VARCHAR AS sales_order_number,
      cv.customer_name::VARCHAR AS customer_name,
      cv.in_weight::NUMERIC AS in_weight,
      cv.delivery_qty::NUMERIC AS delivery_qty
    FROM commercial_vehicles cv
    -- FIXED: Remove the source_type condition from LEFT JOIN
    LEFT JOIN material_details md ON md.vehicle_id = cv.id
    LEFT JOIN dispatch_allocations da ON da.id = cv.dispatch_allocation_id 
      AND cv.source_type IN ('SHIPMENT', 'SALES_ORDER')
    LEFT JOIN sales_orders so ON so.id = COALESCE(da.sales_order_id, cv.sales_order_id::integer)
    LEFT JOIN procurement_itemmaster pim ON pim.id = COALESCE(md.item_id, da.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')
      -- FIXED: Include NULL source_type (legacy PO data)
      AND (
        (cv.source_type = 'PO' OR cv.source_type IS NULL) AND (pim.qc_required = TRUE OR pim.qc_required IS NULL)
        OR cv.source_type IN ('SHIPMENT', 'SALES_ORDER')
      )
      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 so.so_number ILIKE '%' || $1 || '%'
        OR cv.customer_name ILIKE '%' || $1 || '%'
        OR pim.item_name ILIKE '%' || $1 || '%'
        OR pim.item_code ILIKE '%' || $1 || '%'
        OR cv.shipment_number ILIKE '%' || $1 || '%'
        OR cv.sales_order_number 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 OR pim.qc_required IS NULL)

            )

          )

      ) 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 testNoRes = await client.query(
//   `
//   SELECT COALESCE(MAX(test_no), 0) + 1 AS next_test_no
//   FROM qc_parameter_history
//   WHERE qc_id = $1
//   `,
//   [id]
// );

// const testNo = testNoRes.rows[0].next_test_no;

// 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,
// // ],
// // );
// await client.query(
// `
// UPDATE quality_checks
// SET
//   qc_result = $1,
//   total_deduction_percent = $2,
//   total_deduction_weight = $3,
//   final_weight = $4,
//   bag_type = $5,
//   bag_qty = $6,
//   bag_deduction_weight = $7,
//   bag_weight_per_bag = $8,
//   updated_by = $9,
//   updated_at = NOW()
// WHERE id = $10
// `,
// [
//   qcResult,
//   totalDeductionPercent,
//   totalDeductionWeight,
//   finalWeight,
//   bagType || null,
//   bagQty || null,
//   bagDeductionWeight,
//   bagWeightPerBag,
//   userId,
//   id,
// ]
// );

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

// for (const item of processedItems) {
//   for (const p of item.parameters) {
//     await client.query(
//       `
//       INSERT INTO qc_parameter_history
//      (
//   qc_id,
//   test_no,
//   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,$15
//       )
//       `,
//      [
//   id,

//   testNo,

//   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 updateSample = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;
    const { poNumber, vehicleNumber, vendor, status, items: itemsString } = req.body;
    const userId = req.session?.user?.id;
    
    // ✅ FIX 1: Parse items (comes as JSON string from FormData)
    let items;
    try {
      items = typeof itemsString === 'string' ? JSON.parse(itemsString) : itemsString;
    } catch (err) {
      return res.status(400).json({
        success: false,
        message: "Invalid items format",
      });
    }
    
    // Handle file upload
    let filePath = null;
    let originalFilename = null;

    console.log('req.file:', req.file); 
    
    if (req.file) {
      // ✅ FIX 2: Use correct path structure
      filePath = `uploads/${req.file.filename}`;
      originalFilename = req.file.originalname;
      
      console.log('File saved successfully:', {
        filename: req.file.filename,
        originalName: req.file.originalname,
        size: req.file.size,
        path: req.file.path
      });
    } else {
      console.log('No file uploaded');
    }

    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 if needed
    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 (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],
      );
    }

    // GET NEXT TEST NUMBER
    const testNoRes = await client.query(
      `SELECT COALESCE(MAX(test_no), 0) + 1 AS next_test_no
       FROM qc_parameter_history
       WHERE qc_id = $1`,
      [id]
    );

    const testNo = testNoRes.rows[0].next_test_no;

    // Get QC data for calculations
    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,
      };
    });

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

    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),
    );

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

    // Update quality_checks table
    await client.query(
      `UPDATE quality_checks
       SET
         qc_result = $1,
         total_deduction_percent = $2,
         total_deduction_weight = $3,
         final_weight = $4,
         bag_type = $5,
         bag_qty = $6,
         bag_deduction_weight = $7,
         bag_weight_per_bag = $8,
         updated_by = $9,
         updated_at = NOW()
       WHERE id = $10`,
      [
        qcResult,
        totalDeductionPercent,
        totalDeductionWeight,
        finalWeight,
        bagType || null,
        bagQty || null,
        bagDeductionWeight,
        bagWeightPerBag,
        userId,
        id,
      ],
    );

    // INSERT INTO HISTORY TABLE
    for (const item of processedItems) {
      for (const p of item.parameters) {
        await client.query(
          `INSERT INTO qc_parameter_history
           (
             qc_id,
             test_no,
             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, $15)`,
          [
            id,
            testNo,
            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,
          ],
        );
      }
    }

    // ✅ INSERT FILE FOR THIS TEST
    if (filePath) {
      await client.query(
        `INSERT INTO qc_test_files (qc_id, test_no, file_path, original_filename, uploaded_by)
         VALUES ($1, $2, $3, $4, $5)`,
        [id, testNo, filePath, originalFilename, userId]
      );
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "QC Sample updated successfully",
      data: {
        testNo: testNo,
        fileUploaded: !!filePath,
        file: req.file ? req.file.filename : null
      }
    });
    
  } 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) {
      return res.status(400).json({
        success: false,
        message: "Vehicle required",
      });
    }

    await client.query("BEGIN");

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

    const vehicleRes = await client.query(
      `
      SELECT 
        cv.id,
        cv.vehicle_number,
        cv.shipment_number,
        cv.sales_order_number,
        cv.customer_name,
        cv.source_type
      FROM commercial_vehicles cv
      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 (store SO number in po_number)
    // =====================================

    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,
        vehicle.sales_order_number, // Store SO number in po_number field
        vehicle.customer_name, // vendor
        source_type,
        shipmentId || null,
        vehicle.shipment_number,
        vehicle.sales_order_number,
        vehicle.customer_name,
        vehicleId,
        vehicle.vehicle_number,
        qc_status,
        "Completed",
        JSON.stringify({
          checklist: checklist || {},
          parameters: parameters || [],
          remarks: remarks || null,
        }),
        JSON.stringify(parameters || []),
        remarks || null,
        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],
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: `Dispatch QC ${qc_status}`,
      data: {
        qcId,
        sampleId,
      },
    });
  } 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 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,
};
