const { pool } = require("../../config/db");
const { errorResponse } = require("../../utils/errorResponseHandler");
const { logUserAction } = require("../../utils/userActionLogger");

const createVehicleEntry = async (req, res) => {
  const userId = req.session?.user?.id || null;
  const orgId = req.session?.user?.orgId;

  if (!orgId) {
    return res.status(400).json({
      success: false,
      message: "Organization not found in session",
    });
  }
  try {
    const {
      vehicleNumber,
      vehicleType,
      reference_no,
      movementType,
      driverName,
      driverNumber,
      tankerCapacity,
      sealNumber,
      previousLoad,
      materialTypeId, // ✅ NEW
      batchNo, // ✅ NEW
      quantityInLtrs,
      // gatePassNo,
      // gatePassDate,
    } = req.body;

    //  Validation
    if (!vehicleNumber || !vehicleType || !movementType || !driverName) {
      return res.status(400).json({
        success: false,
        message: "Required parking entry fields missing",
      });
    }

    //  Prepare values
    const formattedVehicleNumber = vehicleNumber.trim().toUpperCase();

    // =========================================
    // CHECK VEHICLE ALREADY INSIDE
    // =========================================

    const existingVehicleQuery = `
  SELECT id, vehicle_number, gate_status, exit_time
  FROM commercial_vehicles
  WHERE vehicle_number = $1
  AND org_id = $2
  ORDER BY id DESC
  LIMIT 1
`;

    const existingVehicleResult = await pool.query(existingVehicleQuery, [
      formattedVehicleNumber,
      orgId,
    ]);

    if (existingVehicleResult.rows.length > 0) {
      const existingVehicle = existingVehicleResult.rows[0];

      // Vehicle still inside plant
      if (
        existingVehicle.gate_status !== "EXITED" &&
        existingVehicle.exit_time === null
      ) {
        return res.status(400).json({
          success: false,
          message: `Vehicle ${formattedVehicleNumber} is already inside the plant`,
        });
      }
    }

    const qcId = null;
    const qcStatus = "ENTRY DONE";
    const weighbridgeStatus = "PENDING";

    const createdBy = req.session.user?.id || null;
    const updatedBy = req.session.user?.id || null;

    //  Tanker JSON (PostgreSQL JSONB)
    let tankerDetails = null;

    if (tankerCapacity || sealNumber || previousLoad) {
      tankerDetails = {
        capacity: tankerCapacity,
        sealNumber,
        previousLoad,
      };
    }

    const year = new Date().getFullYear();

    const countResult = await pool.query(`
  SELECT COUNT(*) FROM commercial_vehicles
`);

    const count = Number(countResult.rows[0].count) + 1;

    const gatePassNo = `GP-${year}-${String(count).padStart(5, "0")}`;

    const gatePassDate = new Date();

    //  INSERT QUERY (Production style)
    const query = `
INSERT INTO commercial_vehicles (
  vehicle_number,
  vehicle_type,
  reference_no,
  movement_type,
  driver_name,
  driver_number,
  qc_id,
  qc_status,
  weighbridge_status,
  tanker_details,
  material_type_id,
  batch_no,
 quantity_in_ltrs,
gate_pass_no,
gate_pass_date,
org_id,
  created_by,
  updated_by,
  created_at,
  updated_at
)
VALUES (
  $1, $2, $3, $4, $5,
  $6, $7, $8, $9, $10,
  $11, $12, $13, $14,
  $15, $16,$17,$18, NOW(), NOW()
)
RETURNING *;
`;

    const values = [
      formattedVehicleNumber,
      vehicleType,
      reference_no,
      movementType,
      driverName,
      driverNumber || null,
      qcId,
      qcStatus,
      weighbridgeStatus,
      tankerDetails ? JSON.stringify(tankerDetails) : null,
      materialTypeId || null, // $11
      batchNo || null, // $12
      quantityInLtrs || null,
      gatePassNo || null,
      gatePassDate || null,
      Number(orgId),
      userId, // $15
      userId, // $16
    ];

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

    const insertedVehicle = result.rows[0];

    const ticketNo = `WB-${year}-${String(insertedVehicle.id).padStart(
      4,
      "0",
    )}`;

    await pool.query(
      `
  UPDATE commercial_vehicles
  SET ticket_no = $1
  WHERE id = $2
  `,
      [ticketNo, insertedVehicle.id],
    );

    insertedVehicle.ticket_no = ticketNo;

    return res.status(201).json({
      success: true,
      message: "Parking vehicle entry created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("Vehicle Entry Error:", error);

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

// const getAllVehicleEntries = async (req, res) => {
//   try {
//     //  Query params (for production usage)
//     const page = parseInt(req.query.page) || 1;
//     const limit = parseInt(req.query.limit) || 10;
//     const offset = (page - 1) * limit;

//     // Optional filters
//     // const search = req.query.search || "";
//     // const vehicleType = req.query.vehicleType || "";

//     //  Base query
//     let query = `
//       SELECT *
//       FROM commercial_vehicles
//       WHERE 1=1
//     `;

//     const values = [];
//     let index = 1;

//     //  Search (vehicle number / driver name)
//     // if (search) {
//     //   query += ` AND (
//     //     vehicle_number ILIKE $${index} OR
//     //     driver_name ILIKE $${index}
//     //   )`;
//     //   values.push(`%${search}%`);
//     //   index++;
//     // }

//     //  Filter by vehicle type
//     // if (vehicleType) {
//     //   query += ` AND vehicle_type = $${index}`;
//     //   values.push(vehicleType.trim().toUpperCase());
//     //   index++;
//     // }

//     //  Sorting + Pagination
//     query += `
//       ORDER BY created_at DESC
//       LIMIT $${index} OFFSET $${index + 1}
//     `;

//     values.push(limit, offset);

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

//     //  Total count (for pagination)
//     const countResult = await pool.query(
//       "SELECT COUNT(*) FROM commercial_vehicles"
//     );

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle entries fetched successfully",
//       data: result.rows,
//       pagination: {
//         total: parseInt(countResult.rows[0].count),
//         page,
//         limit,
//       },
//     });
//   } catch (error) {
//     console.error("Get Vehicles Error:", error);

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

// const getAllVehicles = async (req, res) => {
//   try {
//     const limit = parseInt(req.query.limit) || 10;
//     const lastId = req.query.lastId;

//     let query = `
//       SELECT *
//       FROM commercial_vehicles
//       WHERE is_deleted = false
//     `;

//     const values = [];
//     let index = 1;

//     //  Cursor condition (like Mongo _id < lastId)
//     if (lastId) {
//       query += ` AND id < $${index}`;
//       values.push(lastId);
//       index++;
//     }

//     //  Order + limit (+1 for next page check)
//     query += `
//       ORDER BY id DESC
//       LIMIT $${index}
//     `;
//     values.push(limit + 1);
//     const result = await pool.query(query, values);
//     let vehicles = result.rows;

//     //  Check next page
//     const hasNextPage = vehicles.length > limit;

//     if (hasNextPage) {
//       vehicles.pop();
//     }

//     //  Format response (same as Mongo)
//     const formatted = vehicles.map((v) => ({
//       _id: v.id,
//       vehicleNumber: v.vehicle_number,
//       vehicleType: v.vehicle_type,
//       referenceNo: v.reference_no,
//       driverName: v.driver_name,
//       driverNumber: v.driver_number,
//       movementType: v.movement_type,
//       gateStatus: v.gate_status,
//       timeIn: v.time_in,
//       exitTime: v.exit_time,
//       inWeight: v.in_weight,
//       outWeight: v.out_weight,
//       isBlacklisted: v.is_blacklisted,
//       createdBy: v.created_by,
//       createdAt: v.created_at,
//       product: v.material_details?.materialType || "-", // if JSONB
//       qcId: v.qc_id || null,
//       qcNumber: "-", // until join added
//       qcStatus: v.qc_status,
//     }));

//     return res.status(200).json({
//       success: true,
//       data: formatted,
//       nextCursor: hasNextPage ? formatted[formatted.length - 1]._id : null,
//     });
//   } catch (error) {
//     console.error("Get Vehicles Error:", error);

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

// const getAllVehicles = async (req, res) => {
//   try {
//     const limit = parseInt(req.query.limit) || 10;
//     const lastId = req.query.lastId;

//     let query = `
//    SELECT
//   cv.*,

//   STRING_AGG(md.material_type, ', ') AS product,
//   STRING_AGG(md.quantity_unit, ', ') AS quantity_unit,

//   MAX(md.supplier_name) AS supplier_name,
//   MAX(md.delivery_challan_no) AS delivery_challan_no,
//   MAX(md.quantity_bags) AS quantity_bags,
//   MAX(md.invoice_no) AS invoice_no,

//   u.id AS created_by_id,
//   u.first_name AS created_by_first_name,
//   u.last_name AS created_by_last_name

// FROM commercial_vehicles cv

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

// LEFT JOIN users u
//   ON u.id = cv.created_by

// WHERE cv.is_deleted = false
//     `;

//     const values = [];
//     let index = 1;

//     if (lastId) {
//       query += ` AND cv.id < $${index}`;
//       values.push(lastId);
//       index++;
//     }

//     query += `
//      ORDER BY cv.created_at DESC
//       LIMIT $${index}
//     `;
//     values.push(limit + 1);

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

//     const hasNextPage = vehicles.length > limit;

//     if (hasNextPage) {
//       vehicles.pop();
//     }

//     // const formatted = vehicles.map((v) => ({
//     //   _id: v.id,
//     //   vehicleNumber: v.vehicle_number,
//     //   vehicleType: v.vehicle_type,
//     //   referenceNo: v.reference_no,
//     //   driverName: v.driver_name,
//     //   driverNumber: v.driver_number,
//     //   movementType: v.movement_type,
//     //   gateStatus: v.gate_status,
//     //   timeIn: v.time_in,
//     //   exitTime: v.exit_time,
//     //   inWeight: v.in_weight,
//     //   outWeight: v.out_weight,
//     //   isBlacklisted: v.is_blacklisted,

//     //   returnWeightDone: v.return_weight_done || false,
//     //   // createdBy: v.created_by,
//     //   createdBy: v.created_by_id
//     //     ? {
//     //         id: v.created_by_id,
//     //         firstName: v.created_by_first_name,
//     //         lastName: v.created_by_last_name,
//     //       }
//     //     : null,
//     //   createdAt: v.created_at,

//     //   //  FIXED
//     //   product: v.product || "-",
//     //   quantityUnit: v.quantity_unit || "-",

//     //   qcId: v.qc_id || null,
//     //   qcNumber: "-",
//     //   qcStatus: v.qc_status,
//     // }));
//     const formatted = vehicles.map((v) => ({
//   _id: v.id,

//   vehicleNumber: v.vehicle_number,
//   vehicleType: v.vehicle_type,

//   referenceNo: v.reference_no,

//   driverName: v.driver_name,
//   driverNumber: v.driver_number,

//   movementType: v.movement_type,

//   gateStatus: v.gate_status,

//   timeIn: v.time_in,
//   exitTime: v.exit_time,

//   inWeight: v.in_weight,
//   outWeight: v.out_weight,
//   netWeight: v.net_weight || 0,

//   isBlacklisted: v.is_blacklisted,

//   returnWeightDone: v.return_weight_done || false,

//   createdBy: v.created_by_id
//     ? {
//         id: v.created_by_id,
//         firstName: v.created_by_first_name,
//         lastName: v.created_by_last_name,
//       }
//     : null,

//   createdAt: v.created_at,

//   product: v.product || "-",
//   quantityUnit: v.quantity_unit || "-",

//   supplierName: v.supplier_name || "-",

//   lrNo: v.lr_no || "-",
//   lrDate: v.lr_date || null,

//   transporterName: v.transporter_name || "-",

//   transportOrderNo: v.transport_order_no || "-",

//   destinationName: v.destination_name || "-",

//   deliveryQty: v.delivery_qty || "-",

//   batchNo: v.batch_no || "-",

//   quantityBags: v.quantity_bags || "-",

//   deliveryChallanNo: v.delivery_challan_no || "-",

//   invoiceNo: v.invoice_no || "-",

//   gatePassNo: v.gate_pass_no || "-",

//   gatePassDate: v.gate_pass_date || null,

//   remarks: v.remarks || "-",

//   qcId: v.qc_id || null,

//   qcNumber: "-",

//   qcStatus: v.qc_status,
// }));
//     return res.status(200).json({
//       success: true,
//       data: formatted,
//       nextCursor: hasNextPage ? formatted[formatted.length - 1]._id : null,
//     });
//   } catch (error) {
//     console.error("Get Vehicles Error:", error);

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

const getAllVehicles = async (req, res) => {
  try {
    const limit = parseInt(req.query.limit) || 10;
    const lastId = req.query.lastId;

    const values = [];
    let index = 1;

    let query = `
      SELECT 
        cv.*,

        -- Get product from sales_order_items based on allocation
        soi.product_name,
        soi.qty as order_quantity,
        soi.uom_symbol,
        
        -- Get sales order number
        so.so_number,
        
        -- Get QC requirement from product master
        pm.qc_required,
        
        -- Get supplier and warehouse details
        md.supplier_name,
        md.delivery_challan_no,
        md.quantity_bags,
        md.invoice_no,
        md.quantity_unit,
        md.material_type AS product_old,
        
        w.warehouse_name AS destination_name,

        -- Created by user details
        u.id AS created_by_id,
        u.first_name AS created_by_first_name,
        u.last_name AS created_by_last_name

      FROM commercial_vehicles cv

      -- Join with dispatch_allocations to get product info
      LEFT JOIN dispatch_allocations da 
        ON da.id = cv.allocation_id 
        AND COALESCE(da.is_deleted, false) = false
      
      -- Join with sales_order_items to get product name
      LEFT JOIN sales_order_items soi 
        ON soi.sales_order_id = da.sales_order_id 
        AND soi.product_id = da.product_id
      
      -- Join with sales_orders
      LEFT JOIN sales_orders so 
        ON so.id = da.sales_order_id
      
      -- Join with product master for QC requirement
      LEFT JOIN procurement_itemmaster pm 
        ON pm.id = da.product_id
      
      -- Join with material_details (keep if still needed)
      LEFT JOIN material_details md 
        ON md.vehicle_id = cv.id
      
      -- Join with warehouses
      LEFT JOIN warehouses w
        ON w.id = cv.warehouse_id
      
      -- Join with users
      LEFT JOIN users u 
        ON u.id = cv.created_by

      WHERE cv.is_deleted = false
    `;

    // Cursor pagination
    if (lastId) {
      query += ` AND cv.id < $${index}`;
      values.push(lastId);
      index++;
    }

    // Order + Limit
    query += `
      ORDER BY cv.created_at DESC
      LIMIT $${index}
    `;

    values.push(limit + 1);

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

    let vehicles = result.rows;

    const hasNextPage = vehicles.length > limit;

    if (hasNextPage) {
      vehicles.pop();
    }

    // Response formatting
    const formatted = vehicles.map((v) => ({
      _id: v.id,

      vehicleNumber: v.vehicle_number,
      vehicleType: v.vehicle_type,
      allocation_id: v.allocation_id,
      sourceType: v.source_type,

      referenceNo: v.so_number ?? v.reference_no ?? "-",

      driverName: v.driver_name,
      driverNumber: v.driver_number,

      movementType: v.movement_type,

      gateStatus: v.gate_status,

      timeIn: v.time_in,
      exitTime: v.exit_time,

      // Get in_weight directly from commercial_vehicles
      // Handle decimal places (3 or 4 decimals as stored in DB)
      inWeight: v.in_weight ? parseFloat(v.in_weight) : 0,
      outWeight: v.out_weight ? parseFloat(v.out_weight) : 0,
      netWeight: v.net_weight ? parseFloat(v.net_weight) : 0,

      isBlacklisted: v.is_blacklisted,

      returnWeightDone: v.return_weight_done,
      returnDispatchDone: v.return_dispatch_done,

      createdBy: v.created_by_id
        ? {
            id: v.created_by_id,
            firstName: v.created_by_first_name,
            lastName: v.created_by_last_name,
          }
        : null,

      createdAt: v.created_at,

      // Product details from sales_order_items
      product: v.product_name || v.product_old || "-",
      quantityUnit: v.quantity_unit || v.uom || "-",
      orderQuantity: v.order_quantity || 0,

      // Material Details
      supplierName: v.supplier_name || "-",
      invoiceNo: v.invoice_no || "-",
      quantityBags: v.quantity_bags || "-",
      deliveryChallanNo: v.delivery_challan_no || "-",

      // Transport Details
      lrNo: v.lr_no || "-",
      lrDate: v.lr_date || null,
      transporterName: v.transporter_name || "-",
      transportOrderNo: v.transport_order_no || "-",
      destinationName: v.destination_name || "-",
      deliveryQty: v.delivery_qty || "-",
      batchNo: v.batch_no || "-",

      // Gate Pass
      gatePassNo: v.gate_pass_no || "-",
      gatePassDate: v.gate_pass_date || null,
      remarks: v.security_remarks || "-",

      // QC
      qcId: v.qc_id || null,
      qcNumber: "-",
      qcStatus: v.qc_status,
      qcRequired: v.qc_required || false,
    }));

    return res.status(200).json({
      success: true,
      data: formatted,
      nextCursor: hasNextPage ? formatted[formatted.length - 1]._id : null,
    });
  } catch (error) {
    console.error("Get Vehicles Error:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to fetch vehicle entries",
    });
  }
};
// const getAllVehicles = async (req, res) => {
//   try {
//     const limit = parseInt(req.query.limit) || 10;
//     const lastId = req.query.lastId;

//     const values = [];
//     let index = 1;

//     let query = `
//       SELECT
//         cv.*,

//         md.material_type AS product,
//         so.so_number,
//          pm.qc_required,
//         md.quantity_unit,

//          sd.in_weight AS shipment_in_weight,
//   sd.net_weight_ton AS shipment_net_weight,

//   sdi.product_name AS shipment_product,
//   sdi.dispatch_qty,
//   sdi.allocated_qty,

//         md.supplier_name,
//         md.delivery_challan_no,
//         md.quantity_bags,
//         md.invoice_no,
//          w.warehouse_name AS destination_name,

//         u.id AS created_by_id,
//         u.first_name AS created_by_first_name,
//         u.last_name AS created_by_last_name

//       FROM commercial_vehicles cv

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

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

//   LEFT JOIN procurement_itemmaster pm
//   ON pm.id = sd.product_id

//   LEFT JOIN sales_orders so
//   ON so.id = sd.sales_order_id

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

//       LEFT JOIN users u
//         ON u.id = cv.created_by

//         LEFT JOIN warehouses w
//   ON w.id = cv.warehouse_id

//       WHERE cv.is_deleted = false
//     `;

//     // Cursor pagination
//     if (lastId) {
//       query += ` AND cv.id < $${index}`;
//       values.push(lastId);
//       index++;
//     }

//     // Order + Limit
//     query += `
//       ORDER BY cv.created_at DESC
//       LIMIT $${index}
//     `;

//     values.push(limit + 1);

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

//     let vehicles = result.rows;

//     const hasNextPage = vehicles.length > limit;

//     if (hasNextPage) {
//       vehicles.pop();
//     }

//     // Response formatting
//     const formatted = vehicles.map((v) => ({
//       _id: v.id,

//       vehicleNumber: v.vehicle_number,
//       vehicleType: v.vehicle_type,
//       shipment_id: v.shipment_id,
//       sourceType: v.source_type,

//       // referenceNo: v.reference_no,
//       referenceNo: v.so_number ?? v.reference_no ?? "-",

//       driverName: v.driver_name,
//       driverNumber: v.driver_number,

//       movementType: v.movement_type,

//       gateStatus: v.gate_status,

//       timeIn: v.time_in,
//       exitTime: v.exit_time,

//       // inWeight: v.in_weight,
//       // inWeight: v.shipment_in_weight || v.in_weight || 0,
//       inWeight:
//         v.source_type === "SHIPMENT"
//           ? (v.shipment_in_weight ?? 0)
//           : (v.in_weight ?? 0),
//       outWeight: v.out_weight,
//       netWeight: v.net_weight || 0,

//       isBlacklisted: v.is_blacklisted,

//       returnWeightDone: v.return_weight_done,
//       returnDispatchDone: v.return_dispatch_done,

//       createdBy: v.created_by_id
//         ? {
//             id: v.created_by_id,
//             firstName: v.created_by_first_name,
//             lastName: v.created_by_last_name,
//           }
//         : null,

//       createdAt: v.created_at,

//       // Material Details
//       // product: v.product || "-",
//       // product: v.shipment_product || v.product || "-",
//       product:
//         v.source_type === "SHIPMENT"
//           ? (v.shipment_product ?? "-")
//           : (v.product ?? "-"),
//       quantityUnit: v.quantity_unit || "-",

//       supplierName: v.supplier_name || "-",

//       invoiceNo: v.invoice_no || "-",

//       quantityBags: v.quantity_bags || "-",

//       deliveryChallanNo: v.delivery_challan_no || "-",

//       // Transport Details
//       lrNo: v.lr_no || "-",

//       lrDate: v.lr_date || null,

//       transporterName: v.transporter_name || "-",

//       transportOrderNo: v.transport_order_no || "-",

//       destinationName: v.destination_name || "-",

//       deliveryQty: v.delivery_qty || "-",

//       batchNo: v.batch_no || "-",

//       // Gate Pass
//       gatePassNo: v.gate_pass_no || "-",

//       gatePassDate: v.gate_pass_date || null,

//       remarks: v.security_remarks || "-",

//       // QC
//       qcId: v.qc_id || null,

//       qcNumber: "-",

//       qcStatus: v.qc_status,
//       qcRequired: v.qc_required || false,
//     }));

//     return res.status(200).json({
//       success: true,
//       data: formatted,
//       nextCursor: hasNextPage ? formatted[formatted.length - 1]._id : null,
//     });
//   } catch (error) {
//     console.error("Get Vehicles Error:", error);

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

const blacklistVehicle = async (req, res) => {
  const client = await pool.connect(); //  transaction-safe

  try {
    const { vehicleNumber, reason } = req.body;

    if (!vehicleNumber || !reason) {
      return res.status(400).json({
        success: false,
        message: "Vehicle number & reason required",
      });
    }

    const plate = vehicleNumber.trim().toUpperCase();

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

    //  Check existing blacklist (not deleted)
    const existingResult = await client.query(
      `
      SELECT * FROM blacklist_vehicles
      WHERE plate_number = $1 AND is_deleted = FALSE
      LIMIT 1
      `,
      [plate],
    );

    let blacklistId;

    if (existingResult.rows.length > 0) {
      //  Reactivate existing
      const existing = existingResult.rows[0];

      const updateResult = await client.query(
        `
        UPDATE blacklist_vehicles
        SET 
          reason = $1,
          is_active = TRUE,
          updated_by = $2,
          updated_at = NOW()
        WHERE id = $3
        RETURNING id
        `,
        [reason, req.session?.user?.id || null, existing.id],
      );

      blacklistId = updateResult.rows[0].id;
    } else {
      //  Create new blacklist
      const insertResult = await client.query(
        `
        INSERT INTO blacklist_vehicles (
          plate_number,
          reason,
          created_by,
          created_at,
          updated_at
        )
        VALUES ($1, $2, $3, NOW(), NOW())
        RETURNING id
        `,
        [plate, reason, req.user?._id || null],
      );

      blacklistId = insertResult.rows[0].id;
    }

    //  Link commercial vehicles
    await client.query(
      `
      UPDATE commercial_vehicles
      SET 
        blacklist_id = $1,
        is_blacklisted = TRUE,
        updated_at = NOW()
      WHERE vehicle_number = $2
        AND is_deleted = FALSE
      `,
      [blacklistId, plate],
    );

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

    return res.json({
      success: true,
      message: "Vehicle blacklisted",
    });
  } catch (err) {
    await client.query("ROLLBACK"); // ❌ rollback on error
    console.error("Blacklist Error:", err);

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

const getVehiclePrefill = async (req, res) => {
  try {
    const { vehicleNumber } = req.params;

    if (!vehicleNumber) {
      return res.status(400).json({
        success: false,
        message: "Vehicle number required",
      });
    }

    const plate = vehicleNumber.trim().toUpperCase();

    //  Fetch latest entry (like .sort({ createdAt: -1 }))
    const result = await pool.query(
      `
      SELECT *
      FROM commercial_vehicles
      WHERE vehicle_number = $1
        AND is_deleted = FALSE
      ORDER BY created_at DESC
      LIMIT 1
      `,
      [plate],
    );

    const vehicle = result.rows[0];

    if (!vehicle) {
      return res.status(200).json({
        success: true,
        data: null,
        message: "No previous entry",
      });
    }

    return res.status(200).json({
      success: true,
      data: {
        vehicleType: vehicle.vehicle_type,
        driverName: vehicle.driver_name,
        driverNumber: vehicle.driver_number,
        movementType: vehicle.movement_type,
        // goods: vehicle.goods,
        // warehouseId: vehicle.warehouse_id,
        // inWeight: vehicle.in_weight,

        // JSONB (if exists)
        // tankerDetails: vehicle.tanker_details || null,
        // materialDetails: vehicle.material_details || null,
      },
    });
  } catch (error) {
    console.error("Prefill Error:", error);

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

const getBlacklistVehicles = async (req, res) => {
  try {
    const page = parseInt(req.query.page, 10) || 1;
    const limit = parseInt(req.query.limit, 10) || 10;

    const offset = (page - 1) * limit;

    //1. Fetch paginated data
    const dataQuery = `
      SELECT *
      FROM blacklist_vehicles
      WHERE is_deleted = FALSE
        AND is_active = TRUE
      ORDER BY created_at DESC
      LIMIT $1 OFFSET $2;
    `;

    const dataResult = await pool.query(dataQuery, [limit, offset]);

    //2. Get total count
    const countQuery = `
      SELECT COUNT(*) AS total
      FROM blacklist_vehicles
      WHERE is_deleted = FALSE
      AND is_active = TRUE;
    `;

    const countResult = await pool.query(countQuery);

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

    return res.status(200).json({
      success: true,
      data: dataResult.rows,
      pagination: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    });
  } catch (error) {
    console.error("Fetch Blacklist Error:", error);

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

const checkVehicleBlacklist = async (req, res) => {
  try {
    let { plateNumber } = req.params;

    //  Validation
    if (!plateNumber) {
      return res.status(400).json({
        success: false,
        message: "Plate number required",
      });
    }

    //  Normalize (same as Mongo)
    plateNumber = plateNumber.replace(/\s+/g, "").toUpperCase();

    //  Query (production-safe)
    const query = `
      SELECT plate_number, reason
      FROM blacklist_vehicles
      WHERE plate_number = $1
        AND is_active = TRUE
        AND is_deleted = FALSE
      LIMIT 1;
    `;

    const result = await pool.query(query, [plateNumber]);

    //  If found
    if (result.rows.length > 0) {
      const vehicle = result.rows[0];

      return res.status(200).json({
        success: true,
        blocked: true,
        message: `Vehicle ${vehicle.plate_number} is blacklisted`,
        reason: vehicle.reason,
      });
    }

    //  Not found
    return res.status(200).json({
      success: true,
      blocked: false,
    });
  } catch (error) {
    console.error("Blacklist Check Error:", error);

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

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

  try {
    const { id } = req.params;

    await client.query("BEGIN");

    // 1. Check blacklist record
    const blacklistResult = await client.query(
      `SELECT id, plate_number 
       FROM blacklist_vehicles
       WHERE id = $1 AND is_active = TRUE
       LIMIT 1`,
      [id],
    );

    if (blacklistResult.rows.length === 0) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        success: false,
        message: "Blacklist record not found",
      });
    }

    const blacklist = blacklistResult.rows[0];

    // 2. Deactivate blacklist entry
    await client.query(
      `UPDATE blacklist_vehicles
       SET is_active = FALSE,
           updated_at = NOW()
       WHERE id = $1`,
      [id],
    );

    //3. Remove blacklist from vehicles
    await client.query(
      `UPDATE commercial_vehicles
       SET is_blacklisted = FALSE,
           blacklist_id = NULL
       WHERE blacklist_id = $1`,
      [id],
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Vehicle un-blacklisted successfully",
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("UNBLOCK ERROR:", error);

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

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

  try {
    let { plateNumber, reason } = req.body;
    const userId = req.session?.user?.id || null;
    const orgId = req.session?.user?.orgId;

    // Validation
    if (!plateNumber || !reason) {
      return res.status(400).json({
        success: false,
        message: "Plate number and reason are required",
      });
    }

    // Normalize
    const plate = plateNumber.replace(/\s+/g, "").toUpperCase();

    await client.query("BEGIN");

    // 1. Check existing
    const existingResult = await client.query(
      `SELECT id 
       FROM blacklist_vehicles
       WHERE plate_number = $1
       LIMIT 1`,
      [plate],
    );

    let blacklistId;

    if (existingResult.rows.length > 0) {
      // UPDATE existing
      blacklistId = existingResult.rows[0].id;

      await client.query(
        `UPDATE blacklist_vehicles
         SET reason = $1,
             is_deleted = FALSE,
             is_active = TRUE,
             updated_by = $2,
             updated_at = NOW()
         WHERE id = $3`,
        [reason, userId || null, blacklistId],
      );
    } else {
      // INSERT new
      const insertResult = await client.query(
        `INSERT INTO blacklist_vehicles (
          plate_number,
          reason,
          created_by,
          is_active,
          is_deleted,
          created_at,
          updated_at
        )
        VALUES ($1, $2, $3, TRUE, FALSE, NOW(), NOW())
        RETURNING id`,
        [plate, reason, userId || null],
      );

      blacklistId = insertResult.rows[0].id;
    }

    // 2. Sync commercial vehicles
    await client.query(
      `UPDATE commercial_vehicles
       SET blacklist_id = $1,
           is_blacklisted = TRUE
       WHERE vehicle_number = $2
         AND is_deleted = FALSE`,
      [blacklistId, plate],
    );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Vehicle blacklisted successfully",
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("Blacklist Create Error:", error);

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

// const getWeighbridgeCards = async (req, res) => {
//   try {
//     const query = `
//       SELECT
//         id,
//         vehicle_number AS "vehicleNo",
//         vehicle_type AS "vehicleType",
//         movement_type AS "movementType",
//         driver_name AS "driverName",
//         driver_number AS "driverPhone",
//         warehouse_id AS "warehouseId"
//       FROM commercial_vehicles
//       WHERE
//         is_deleted = false
//         AND weighbridge_status = 'PENDING'
//       ORDER BY created_at DESC
//       LIMIT 4
//     `;

//     const { rows } = await pool.query(query);

//     return res.status(200).json({
//       success: true,
//       data: rows,
//     });
//   } catch (error) {
//     console.error("Cards API Error:", error);

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

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

//     //1. Check vehicle exists
//     const existingQuery = `
//       SELECT * FROM commercial_vehicles WHERE id = $1
//     `;
//     const { rows } = await pool.query(existingQuery, [id]);

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

//     const vehicle = rows[0];

//     //2. Check gate status
//     if (vehicle.gate_status === "Exited") {
//       return res.status(400).json({
//         success: false,
//         message: "Cannot update exited vehicle",
//       });
//     }

//     const {
//       movementType,
//       vehicleType,
//       vehicleNumber,
//       driverName,
//       driverNumber,
//       warehouseId,
//       inWeight,
//       referenceNo,
//       supplierName,
//       materialType,
//       materialName,
//       quantityUnit,
//       tankerCapacity,
//       sealNumber,
//       previousLoad,
//     } = req.body;

//     //3. Update Query (COALESCE keeps old values if null)
//     const updateQuery = `
//       UPDATE commercial_vehicles SET
//         movement_type = COALESCE($1, movement_type),
//         vehicle_type = COALESCE($2, vehicle_type),
//         vehicle_number = COALESCE($3, vehicle_number),
//         driver_name = COALESCE($4, driver_name),
//         driver_number = COALESCE($5, driver_number),
//         warehouse_id = COALESCE($6, warehouse_id),
//         in_weight = COALESCE($7, in_weight),
//         reference_no = COALESCE($8, reference_no),

//         supplier_name = COALESCE($9, supplier_name),
//         material_type = COALESCE($10, material_type),
//         material_name = COALESCE($11, material_name),
//         quantity_unit = COALESCE($12, quantity_unit),

//         tanker_capacity = COALESCE($13, tanker_capacity),
//         seal_number = COALESCE($14, seal_number),
//         previous_load = COALESCE($15, previous_load),

//         weighbridge_status = 'COMPLETED',
//         updated_by = $16,
//         updated_at = NOW()

//       WHERE id = $17
//       RETURNING *
//     `;

//     const clean = (val) => {
//       return val === undefined || val === "" ? null : val;
//     };

//     const cleanNumber = (val) => {
//       return val === undefined || val === "" ? null : Number(val);
//     };

//     const values = [
//       clean(movementType),
//       clean(vehicleType),
//       clean(vehicleNumber),
//       clean(driverName),
//       clean(driverNumber),
//       cleanNumber(warehouseId),
//       cleanNumber(inWeight), //  FIXED
//       clean(referenceNo),

//       clean(supplierName),
//       clean(materialType),
//       clean(materialName),
//       clean(quantityUnit),

//       cleanNumber(tankerCapacity),
//       clean(sealNumber),
//       clean(previousLoad),

//       req.user?.id || null,
//       id,
//     ];

//     // const values = [
//     //   movementType,
//     //   vehicleType,
//     //   vehicleNumber,
//     //   driverName,
//     //   driverNumber,
//     //   warehouseId,
//     //   inWeight,
//     //   referenceNo,

//     //   supplierName,
//     //   materialType,
//     //   materialName,
//     //   quantityUnit,

//     //   tankerCapacity,
//     //   sealNumber,
//     //   previousLoad,

//     //   req.user?.id || null,
//     //   id,
//     // ];

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

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle updated successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("Update Vehicle Error:", error);

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

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

//   try {
//     const { id } = req.params;
//     const poItems = req.body.poItems ? JSON.parse(req.body.poItems) : [];
//     const { dcId, invoiceNo, qtyBags } = req.body;
//     const materialsData =
//       poItems.length > 0
//         ? JSON.stringify(
//             poItems.map((item) => ({
//               name: item.item_name,
//               qty: item.quantity,
//               price: item.unit_price,
//             })),
//           )
//         : null;

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

//     // 🔹 1. Check vehicle exists
//     const existing = await client.query(
//       `SELECT * FROM commercial_vehicles WHERE id = $1`,
//       [id],
//     );

//     if (existing.rows.length === 0) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({
//         success: false,
//         message: "Vehicle not found",
//       });
//     }

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

//     // 🔹 2. Gate check
//     if (vehicle.gate_status === "Exited") {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Cannot update exited vehicle",
//       });
//     }

//     const {
//       movementType,
//       vehicleType,
//       vehicleNumber,
//       driverName,
//       driverNumber,
//       warehouseId,
//       inWeight,
//       referenceNo,
//       supplierName,
//       materialType,
//       materialName,
//       quantityUnit,
//       // tankerCapacity,
//       // sealNumber,
//       // previousLoad,
//     } = req.body;

//      let parsedMaterialType = [];

//     try {
//       parsedMaterialType = materialType ? JSON.parse(materialType) : [];
//     } catch {
//       parsedMaterialType = [];
//     }

//     // 🔹 Helpers
//     const clean = (val) => (val === undefined || val === "" ? null : val);

//     const cleanNumber = (val) => {
//       if (val === undefined || val === "" || val === "NaN") return null;
//       const num = Number(val);
//       return isNaN(num) ? null : num;
//     };

//     // 🔹 3. Update VEHICLE TABLE
//     const vehicleQuery = `
//       UPDATE commercial_vehicles SET
//         movement_type = COALESCE($1, movement_type),
//         vehicle_type = COALESCE($2, vehicle_type),
//         vehicle_number = COALESCE($3, vehicle_number),
//         driver_name = COALESCE($4, driver_name),
//         driver_number = COALESCE($5, driver_number),
//         warehouse_id = COALESCE($6, warehouse_id),
//         in_weight = COALESCE($7, in_weight),
//         reference_no = COALESCE($8, reference_no),

//         weighbridge_status = 'COMPLETED',
//         updated_by = $9,
//         updated_at = NOW()

//       WHERE id = $10
//       RETURNING *
//     `;

//     //  clean(sealNumber),
//     // clean(previousLoad),
//     //  seal_number = COALESCE($10, seal_number),
//     // previous_load = COALESCE($11, previous_load),
//     // tanker_capacity = COALESCE($9, tanker_capacity),

//     const vehicleValues = [
//       clean(movementType),
//       clean(vehicleType),
//       clean(vehicleNumber),
//       clean(driverName),
//       clean(driverNumber),
//       cleanNumber(warehouseId),
//       cleanNumber(inWeight),
//       clean(referenceNo),

//       req.user?.id || null,
//       id,
//     ];

//     const vehicleResult = await client.query(vehicleQuery, vehicleValues);

//     // 🔹 4. UPSERT MATERIAL DETAILS
//     const materialCheck = await client.query(
//       `SELECT id FROM material_details WHERE vehicle_id = $1`,
//       [id],
//     );

//     if (materialCheck.rows.length > 0) {
//       // ✅ UPDATE
//       await client.query(
//         `
//      UPDATE material_details SET
//   supplier_name = COALESCE($1, supplier_name),
//   material_type = COALESCE($2, material_type),
//   material_name = COALESCE($3, material_name),
//   quantity_unit = COALESCE($4, quantity_unit),
//   po_number = COALESCE($5, po_number),
//   invoice_no = COALESCE($6, invoice_no),
//   delivery_challan_no = COALESCE($7, delivery_challan_no),
//   quantity_bags = COALESCE($8, quantity_bags),
//   materials = COALESCE($9, materials),
//   updated_at = NOW()
// WHERE vehicle_id = $10
//       `,
//         [
//           [
//             clean(supplierName),
//             clean(materialType),
//             clean(materialName),
//             clean(quantityUnit),
//             clean(referenceNo),
//             clean(invoiceNo),
//             clean(dcId),
//             cleanNumber(qtyBags),
//             materialsData,
//             id,
//           ],
//         ],
//       );
//     } else {
//       // ✅ INSERT
//       await client.query(
//         `
//      INSERT INTO material_details (
//   vehicle_id,
//   supplier_name,
//   material_type,
//   material_name,
//   quantity_unit,
//   po_number,
//   invoice_no,
//   delivery_challan_no,
//   quantity_bags,
//   materials
// )
// VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
//       `,
//         [
//           id,
//           clean(supplierName),
//           clean(materialType),
//           clean(materialName),
//           clean(quantityUnit),
//           clean(referenceNo),
//           clean(invoiceNo),
//           clean(dcId),
//           cleanNumber(qtyBags),
//           materialsData,
//         ],
//       );
//     }

//     // 🔹 5. MERGE NEW ITEM INTO PURCHASE ORDER ITEMS
//     if (referenceNo && materialName && materialName.trim() !== "") {
//       const cleanName = materialName.trim();

//       // 1. Get PO ID
//       const poRes = await client.query(
//         `SELECT id FROM purchase_orders WHERE po_number = $1`,
//         [referenceNo],
//       );

//       if (poRes.rows.length > 0) {
//         const poId = poRes.rows[0].id;

//         // 2. Check if item already exists (case-insensitive)
//         const itemCheck = await client.query(
//           `
//       SELECT id FROM purchase_order_items
//       WHERE po_id = $1
//       AND LOWER(item_name) = LOWER($2)
//       `,
//           [poId, cleanName],
//         );

//         // 3. Insert if NOT exists
//         if (itemCheck.rows.length === 0) {
//           await client.query(
//             `
//         INSERT INTO purchase_order_items (
//           po_id,
//           item_name,
//           quantity,
//           unit_price,
//           created_at
//         )
//         VALUES ($1, $2, 0, 0, NOW())
//         `,
//             [poId, cleanName],
//           );
//         }
//       }
//     }

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

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle updated successfully",
//       data: vehicleResult.rows[0],
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("Update Vehicle Error:", error);

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

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

//   try {
//     const { id } = req.params;
//     const { outWeight, movementTypeOut } = req.body;
//     const userId = req.session.user?.id || null;
//     // console.log(userId);

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

//     /* 🔍 LOCK VEHICLE ROW */
//     const vehicleResult = await client.query(
//       `SELECT * FROM commercial_vehicles
//        WHERE id = $1 AND is_deleted = false
//        FOR UPDATE`,
//       [id],
//     );

//     if (vehicleResult.rowCount === 0) {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "Vehicle not found", 404);
//     }

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

//     /* ❌ VALIDATIONS */
//     if (vehicle.gate_status === "Exited") {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "Vehicle already exited", 400);
//     }

//     if (vehicle.stock_created) {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "Stock already created", 400);
//     }

//     /* 🔍 QC VALIDATION */
//     const qcResult = await client.query(
//       `SELECT * FROM quality_checks
//        WHERE vehicle_id = $1 AND is_deleted = false`,
//       [id],
//     );

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

//     if (!qc || qc.complete_status !== "Completed") {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "QC not completed", 400);
//     }

//     /* 📝 UPDATE VEHICLE EXIT */
//     await client.query(
//       `UPDATE commercial_vehicles
//        SET out_weight = $1,
//            movement_type_out = $2,
//            gate_status = 'Exited',
//            exit_time = NOW(),
//            updated_by = $3
//        WHERE id = $4`,
//       [outWeight, movementTypeOut, userId, id],
//     );

//     /* 📦 INWARD CREATION (ONLY FOR UNLOADING) */
//     let inwardId = vehicle.inward_id;

//     if (
//       vehicle.movement_type === "Unloading" &&
//       vehicle.in_weight &&
//       !vehicle.inward_id
//     ) {
//       const inwardResult = await client.query(
//         `INSERT INTO inward (
//           vehicle_id, product_name, reference_no,
//           vendor_name, total_quantity, quantity_unit,
//           org_id, created_by
//         ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
//         RETURNING id`,
//         [
//           id,
//           vehicle.material_name || vehicle.goods || "General Material",
//           vehicle.reference_no,
//           vehicle.supplier_name || null,
//           vehicle.in_weight,
//           vehicle.quantity_unit || null,
//           vehicle.org_id,
//           userId,
//         ],
//       );

//       inwardId = inwardResult.rows[0].id;

//       await client.query(
//         `UPDATE commercial_vehicles SET inward_id = $1 WHERE id = $2`,
//         [inwardId, id],
//       );
//     }

//     /* ⚖️ CALCULATE NET QUANTITY */
//     const gross = vehicle.in_weight || 0;
//     const tare = outWeight || 0;

//     const netQty =
//       vehicle.movement_type === "Unloading" ? gross - tare : tare - gross;

//     const materialResult = await client.query(
//       `SELECT
//       po_number,
//       supplier_name,
//       material_type,
//       material_name,
//       quantity_unit
//    FROM material_details
//    WHERE vehicle_id = $1`,
//       [id],
//     );

//     const materialSnapshot = materialResult.rows.map((m) => ({
//       poNumber: m.po_number,
//       supplierName: m.supplier_name,
//       materialType: m.material_type,
//       materialName: m.material_name,
//       quantityUnit: m.quantity_unit,
//     }));

//     // 🔹 CREATE GRN (ONLY IF NOT EXISTS)
//     let poId = null;
//     let grnId = null;
//     if (!vehicle.grn_id) {
//       // 1. Get PO ID

//       if (vehicle.reference_no) {
//         const poRes = await client.query(
//           `SELECT id FROM purchase_orders WHERE po_number = $1`,
//           [vehicle.reference_no],
//         );

//         if (poRes.rows.length > 0) {
//           poId = poRes.rows[0].id;
//         }
//       }

//       // 2. Generate GRN Number (simple version)
//       const grnNumber = `GRN-${Date.now()}`;

//       // 3. Insert GRN
//       const grnResult = await client.query(
//         `INSERT INTO grn (
//       grn_number,
//       po_id,
//       po_number,
//       vendor_name,
//       gate_entry_no,
//       invoice_no,
//       delivery_challan_no,
//       vehicle_id,
//       created_by
//     )
//     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
//     RETURNING id`,
//         [
//           grnNumber,
//           poId,
//           vehicle.reference_no,
//           vehicle.supplier_name,
//           vehicle.gate_entry_no || vehicle.vehicle_number,
//           vehicle.invoice_no || null,
//           vehicle.delivery_challan_no || null,
//           id,
//           userId,
//         ],
//       );

//       const grnId = grnResult.rows[0].id;

//       // 4. Update vehicle
//       await client.query(
//         `UPDATE commercial_vehicles
//      SET grn_id = $1, grn_status = 'Draft'
//      WHERE id = $2`,
//         [grnId, id],
//       );
//     }

//     let poItems = [];

//     if (poId) {
//       const poItemsRes = await client.query(
//         `SELECT id, item_name, quantity, unit_price
//      FROM purchase_order_items
//      WHERE po_id = $1`,
//         [poId],
//       );

//       poItems = poItemsRes.rows;
//     }

//     // 🔹 INSERT INTO GRN ITEMS
//     if (poItems.length > 0) {
//       for (const item of poItems) {
//         await client.query(
//           `INSERT INTO grn_items (
//         grn_id,
//         purchase_order_item_id,
//         item_name,
//         ordered_qty,
//         received_qty,
//         rejected_qty,
//         unit_price
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7)`,
//           [
//             grnId,
//             item.id,
//             item.item_name,
//             item.quantity || 0,
//             item.quantity || 0,
//             0,
//             item.unit_price || 0,
//           ],
//         );
//       }
//     } else {
//       // 🔹 FALLBACK (NO PO ITEMS)
//       await client.query(
//         `INSERT INTO grn_items (
//       grn_id,
//       item_name,
//       ordered_qty,
//       received_qty
//     )
//     VALUES ($1,$2,$3,$4)`,
//         [
//           grnId,
//           vehicle.material_name || "General",
//           vehicle.in_weight || 0,
//           vehicle.in_weight || 0,
//         ],
//       );
//     }

//     /* 📊 CREATE STOCK */
//     const stockResult = await client.query(
//       `INSERT INTO stocks (
//         vehicle_id,
//         vehicle_number,
//         material_details,
//         warehouse_id,
//         gross_weight,
//         tare_weight,
//         net_quantity,
//         received_by,
//         qc_by,
//         reference_no,
//         created_by
//       ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
//       RETURNING *`,
//       [
//         id,
//         vehicle.vehicle_number,
//         // JSON.stringify(vehicle.material_details || {}),
//         JSON.stringify(materialSnapshot),
//         vehicle.warehouse_id,
//         gross,
//         tare,
//         Math.abs(netQty),
//         userId,
//         // vehicle.qc_id,
//         userId,
//         vehicle.reference_no,
//         userId,
//       ],
//     );

//     const stock = stockResult.rows[0];

//     /*  MARK STOCK CREATED */
//     await client.query(
//       `UPDATE commercial_vehicles
//        SET stock_created = true
//        WHERE id = $1`,
//       [id],
//     );

//     /* 🚗 REMOVE FROM PARKING */
//     // await client.query(
//     //   `UPDATE parking_bays
//     //    SET vehicles = vehicles - $1
//     //    WHERE vehicles ? $1`,
//     //   [vehicle.vehicle_number]
//     // );

//     /* 📝 LOG ACTION */
//     await logUserAction({
//       userId,
//       action: "VEHICLE_EXIT_STOCK_CREATED",
//       description: `Vehicle ${vehicle.vehicle_number} exited & stock ${stock.id} created`,
//       ip: req.ip,
//     });

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

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle exit & stock created",
//       data: { vehicleId: id, stock },
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error(error);
//     return errorResponse(res, "Exit failed", 500);
//   } finally {
//     client.release();
//   }
// };

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

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

//     const poItems = req.body.poItems ? JSON.parse(req.body.poItems) : [];

//     const {
//       dcId,
//       invoiceNo,
//       qtyBags,
//       movementType,
//       vehicleType,
//       vehicleNumber,
//       driverName,
//       driverNumber,
//       warehouseId,
//       inWeight,
//       referenceNo,
//       supplierName,
//       materialType,
//       item_id,
//       materialName,
//       quantityUnit,
//     } = req.body;

//     // ✅ Parse multi-select items
//     let parsedMaterialType = [];
//     try {
//       parsedMaterialType = materialType ? JSON.parse(materialType) : [];
//     } catch {
//       parsedMaterialType = [];
//     }

//     // ✅ Convert → safe DB format
//     const materialTypeString =
//       parsedMaterialType.length > 0 ? parsedMaterialType.join(", ") : null;

//     // ✅ Materials JSON (PO priority)
//     const materialsData =
//       poItems.length > 0
//         ? JSON.stringify(
//             poItems.map((item) => ({
//               item_id: item.item_id, // 🔥 VERY IMPORTANT
//               item_code: item.item_code, // 🔥 NEW FIELD
//               name: item.item_name,
//               qty: item.quantity,
//               price: item.unit_price,
//             })),
//           )
//         : parsedMaterialType.length > 0
//           ? JSON.stringify(
//               parsedMaterialType.map((name) => ({
//                 name,
//                 qty: 0,
//                 price: 0,
//               })),
//             )
//           : null;

//     const clean = (val) => (val === undefined || val === "" ? null : val);

//     const cleanNumber = (val) => {
//       if (!val || val === "NaN") return null;
//       const num = Number(val);
//       return isNaN(num) ? null : num;
//     };

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

//     // 🔹 Check vehicle
//     const existing = await client.query(
//       `SELECT * FROM commercial_vehicles WHERE id = $1`,
//       [id],
//     );

//     if (!existing.rows.length) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({
//         success: false,
//         message: "Vehicle not found",
//       });
//     }

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

//     if (vehicle.gate_status === "Exited") {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Cannot update exited vehicle",
//       });
//     }

//     // 🔹 Update vehicle
//     const vehicleResult = await client.query(
//       `
//       UPDATE commercial_vehicles SET
//         movement_type = COALESCE($1, movement_type),
//         vehicle_type = COALESCE($2, vehicle_type),
//         vehicle_number = COALESCE($3, vehicle_number),
//         driver_name = COALESCE($4, driver_name),
//         driver_number = COALESCE($5, driver_number),
//         warehouse_id = COALESCE($6, warehouse_id),
//         in_weight = COALESCE($7, in_weight),
//         reference_no = COALESCE($8, reference_no),
//         weighbridge_status = 'COMPLETED',
//         updated_by = $9,
//         updated_at = NOW()
//       WHERE id = $10
//       RETURNING *
//       `,
//       [
//         clean(movementType),
//         clean(vehicleType),
//         clean(vehicleNumber),
//         clean(driverName),
//         clean(driverNumber),
//         cleanNumber(warehouseId),
//         cleanNumber(inWeight),
//         clean(referenceNo),
//         req.user?.id || null,
//         id,
//       ],
//     );

//     // 🔹 Upsert material details
//     const materialCheck = await client.query(
//       `SELECT id FROM material_details WHERE vehicle_id = $1`,
//       [id],
//     );

//     const materialValues = [
//       clean(supplierName),
//       clean(materialTypeString), // ✅ fixed
//       clean(item_id),
//       clean(materialName),
//       clean(quantityUnit),
//       clean(referenceNo),
//       clean(invoiceNo),
//       clean(dcId),
//       cleanNumber(qtyBags),
//       materialsData,
//       id,
//     ];

//     if (materialCheck.rows.length > 0) {
//       await client.query(
//         `
//       UPDATE material_details SET
//   supplier_name = COALESCE($1, supplier_name),
//   material_type = COALESCE($2, material_type),
//   item_id       = COALESCE($3, item_id),
//   material_name = COALESCE($4, material_name),
//   quantity_unit = COALESCE($5, quantity_unit),
//   po_number     = COALESCE($6, po_number),
//   invoice_no    = COALESCE($7, invoice_no),
//   delivery_challan_no = COALESCE($8, delivery_challan_no),
//   quantity_bags = COALESCE($9, quantity_bags),
//   materials     = COALESCE($10, materials),
//   updated_at = NOW()
// WHERE vehicle_id = $11
//         `,
//         materialValues,
//       );
//     } else {
//       await client.query(
//         `
//         INSERT INTO material_details (
//           vehicle_id,
//           supplier_name,
//           material_type,
//            item_id,
//           material_name,
//           quantity_unit,
//           po_number,
//           invoice_no,
//           delivery_challan_no,
//           quantity_bags,
//           materials
//         )
//        VALUES ($11,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
//         `,
//         materialValues,
//       );
//     }

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

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle updated successfully",
//       data: vehicleResult.rows[0],
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("Update Vehicle Error:", error);

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

// const getWeighbridgeCards = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;

//     const query = `

//       SELECT

//         cv.id,

//         -- VEHICLE
//         cv.vehicle_number AS "vehicleNo",

//         cv.vehicle_type AS "vehicleType",

//         cv.movement_type AS "movementType",

//         -- DRIVER
//         cv.driver_name AS "driverName",

//         cv.driver_number AS "driverPhone",

//         -- WAREHOUSE
//         cv.warehouse_id AS "warehouseId",

//         -- SOURCE FLOW
//         cv.source_type AS "sourceType",

//         -- SHIPMENT DETAILS
//         cv.shipment_id AS "shipmentId",

//         cv.shipment_number AS "shipmentNumber",

//         cv.sales_order_number AS "salesOrderNumber",

//         -- CUSTOMER
//         cv.customer_name AS "customerName",

//         -- STATUS
//         cv.weighbridge_status AS "weighbridgeStatus",

//         cv.gate_status AS "gateStatus",

//         cv.created_at AS "createdAt"

//       FROM commercial_vehicles cv

//       WHERE

//         cv.is_deleted = false

//         AND cv.weighbridge_status = 'PENDING'

//         AND cv.gate_status = 'Inside'

//         AND cv.org_id = $1

//       ORDER BY cv.created_at DESC

//       LIMIT 4

//     `;

//     const { rows } = await pool.query(query, [orgId]);

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

//       count: rows.length,

//       data: rows,
//     });
//   } catch (error) {
//     console.error("Cards API Error:", error);

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

//       message: "Failed to fetch card data",

//       error: error.message,
//     });
//   }
// };

const getWeighbridgeCards = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    const query = `
      SELECT 
        cv.id,
        cv.vehicle_number AS "vehicleNo",
        cv.vehicle_type AS "vehicleType",
        cv.movement_type AS "movementType",
        cv.driver_name AS "driverName",
        cv.driver_number AS "driverPhone",
        cv.warehouse_id AS "warehouseId",
        cv.source_type AS "sourceType",
        cv.shipment_id AS "shipmentId",
        cv.shipment_number AS "shipmentNumber",
        cv.sales_order_number AS "salesOrderNumber",
        cv.sales_order_id AS "salesOrderId",
        cv.customer_name AS "customerName",
        cv.weighbridge_status AS "weighbridgeStatus",
        cv.weighment_status AS "weighmentStatus",
        cv.gate_status AS "gateStatus",
        cv.in_weight,
        cv.out_weight,
        cv.net_weight,
        cv.in_weight_kgs,
        cv.tare_weight_ton,
        cv.net_weight_ton,
        cv.created_at AS "createdAt",
        -- Calculate net weight in TONS (out - in)
        CASE 
          -- If both in_weight and out_weight are in TONS
          WHEN cv.out_weight IS NOT NULL AND cv.in_weight IS NOT NULL 
            THEN (cv.out_weight - cv.in_weight)
          -- If net_weight_ton is available (already in TONS)
          WHEN cv.net_weight_ton IS NOT NULL
            THEN cv.net_weight_ton
          -- If in_weight_kgs (KG) and out_weight (TONS)
          WHEN cv.out_weight IS NOT NULL AND cv.in_weight_kgs IS NOT NULL
            THEN cv.out_weight - (cv.in_weight_kgs / 1000)
          -- If net_weight (KG) is available
          WHEN cv.net_weight IS NOT NULL
            THEN cv.net_weight / 1000
          ELSE NULL
        END AS "calculatedNetWeightTon"
      FROM commercial_vehicles cv
      WHERE 
        cv.is_deleted = false
        AND cv.org_id = $1
        AND (
          cv.weighbridge_status = 'COMPLETED' 
          OR cv.weighment_status = 'COMPLETED'
          OR (cv.in_weight IS NOT NULL AND cv.out_weight IS NOT NULL)
          OR (cv.in_weight_kgs IS NOT NULL AND cv.net_weight_ton IS NOT NULL)
        )
        AND cv.sales_order_number IS NOT NULL
        AND cv.gate_status != 'Exited'
        AND (cv.invoice_created IS NULL OR cv.invoice_created = false)
      ORDER BY cv.weighment_completed_at DESC NULLS LAST, cv.created_at DESC
      LIMIT 10
    `;

    const { rows } = await pool.query(query, [orgId]);

    // Format response with proper net weight calculation in TONS
    const formattedData = rows.map((row) => {
      let netWeightTon = null;
      let inWeightTon = null;
      let outWeightTon = null;

      // Calculate net weight in TONS
      if (row.out_weight && row.in_weight) {
        // Both in TONS
        netWeightTon = Number(row.out_weight) - Number(row.in_weight);
        inWeightTon = Number(row.in_weight);
        outWeightTon = Number(row.out_weight);
      } else if (row.net_weight_ton) {
        // Already in TONS
        netWeightTon = Number(row.net_weight_ton);
        inWeightTon = row.in_weight_kgs
          ? Number(row.in_weight_kgs) / 1000
          : row.in_weight || 0;
        outWeightTon = netWeightTon + inWeightTon;
      } else if (row.out_weight && row.in_weight_kgs) {
        // Out in TONS, In in KG
        netWeightTon =
          Number(row.out_weight) - Number(row.in_weight_kgs) / 1000;
        inWeightTon = Number(row.in_weight_kgs) / 1000;
        outWeightTon = Number(row.out_weight);
      } else if (row.net_weight && row.in_weight_kgs) {
        // Net in KG, In in KG
        netWeightTon = Number(row.net_weight) / 1000;
        inWeightTon = Number(row.in_weight_kgs) / 1000;
        outWeightTon = netWeightTon + inWeightTon;
      } else if (row.net_weight && row.in_weight) {
        // Net in KG, In in TONS
        netWeightTon = Number(row.net_weight) / 1000;
        inWeightTon = Number(row.in_weight);
        outWeightTon = netWeightTon + inWeightTon;
      }

      return {
        id: row.id,
        vehicleNo: row.vehicleNo,
        vehicleType: row.vehicleType,
        movementType: row.movementType,
        driverName: row.driverName,
        driverPhone: row.driverPhone,
        warehouseId: row.warehouseId,
        sourceType: row.sourceType,
        shipmentId: row.shipmentId,
        shipmentNumber: row.shipmentNumber,
        salesOrderNumber: row.salesOrderNumber,
        salesOrderId: row.salesOrderId,
        customerName: row.customerName,
        weighbridgeStatus: row.weighbridgeStatus,
        weighmentStatus: row.weighmentStatus,
        gateStatus: row.gateStatus,
        createdAt: row.createdAt,
        // Return values in TONS
        inWeight: inWeightTon ? inWeightTon.toFixed(3) : null,
        outWeight: outWeightTon ? outWeightTon.toFixed(3) : null,
        netWeight: netWeightTon ? netWeightTon.toFixed(3) : null,
        hasWeighment: !!(inWeightTon || outWeightTon),
      };
    });

    return res.status(200).json({
      success: true,
      count: formattedData.length,
      data: formattedData,
    });
  } catch (error) {
    console.error("Cards API Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch card data",
      error: error.message,
    });
  }
};

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

  try {
    const { id } = req.params;

    // 🔹 Parse PO items safely
    let poItems = [];
    try {
      poItems = req.body.poItems ? JSON.parse(req.body.poItems) : [];
    } catch {
      poItems = [];
    }

    const {
      dcId,
      invoiceNo,
      invoiceAmount,
      qtyBags,
      movementType,
      vehicleType,
      vehicleNumber,
      driverName,
      driverNumber,
      warehouseId,
      inWeight,
      referenceNo,
      supplierName,
      materialType,
      item_id,
      materialName,
      quantityUnit,
      item_code, // ✅ NEW (from frontend)
      item_name, // ✅ NEW (from frontend)
      transporterName,
      lrNo,
      lrDate,
      transportOrderNo,
      destinationName,
      deliveryQty,
      batchNo,
      remarks,
      // netWeight,
    } = req.body;

    // 🔹 Parse materialType (multi-select)
    let parsedMaterialType = [];
    try {
      parsedMaterialType = materialType ? JSON.parse(materialType) : [];
    } catch {
      parsedMaterialType = [];
    }

    const materialTypeString =
      parsedMaterialType.length > 0 ? parsedMaterialType.join(", ") : null;

    // 🔹 Clean helpers
    const clean = (val) => (val === undefined || val === "" ? null : val);

    const cleanNumber = (val) => {
      if (!val || val === "NaN") return null;
      const num = Number(val);
      return isNaN(num) ? null : num;
    };

    // 🔥 Extract first item (fallback logic)
    const firstItem = poItems?.[0] || null;

    // const finalItemId = firstItem?.item_id || item_id || null;
    const finalItemId = null;

    const finalItemName =
      firstItem?.item_name || item_name || materialName || null;
    // const finalItemCode = firstItem?.item_code || item_code || null;
    // const itemCodesArray =
    //   poItems.length > 0
    //     ? poItems.map((i) => i.item_code).filter(Boolean)
    //     : item_code
    //       ? [item_code]
    //       : [];
    // const itemCodesJSON =
    //   itemCodesArray.length > 0 ? JSON.stringify(itemCodesArray) : null;
    // const finalItemCode = itemCodesJSON;
    const itemCodesArray =
      poItems.length > 0
        ? poItems.map((i) => i.item_code).filter(Boolean)
        : item_code
          ? JSON.parse(item_code)
          : [];

    const finalItemCode =
      itemCodesArray.length > 0 ? JSON.stringify(itemCodesArray) : null;

    // 🔹 Build materials JSON
    const materialsData =
      poItems.length > 0
        ? JSON.stringify(
            poItems.map((item) => ({
              item_id: item.item_id, // ✅ FIXED
              item_code: item.item_code,
              name: item.item_name,
              qty: item.quantity,
              price: item.unit_price,
            })),
          )
        : parsedMaterialType.length > 0
          ? JSON.stringify(
              parsedMaterialType.map((name) => ({
                name,
                qty: 0,
                price: 0,
              })),
            )
          : null;

    await client.query("BEGIN");

    // 🔹 Check vehicle exists
    const existing = await client.query(
      `SELECT * FROM commercial_vehicles WHERE id = $1`,
      [id],
    );

    if (!existing.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        success: false,
        message: "Vehicle not found",
      });
    }

    const vehicle = existing.rows[0];

    if (vehicle.gate_status === "Exited") {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: "Cannot update exited vehicle",
      });
    }

    // 🔹 Update vehicle
    const vehicleResult = await client.query(
      `
      UPDATE commercial_vehicles SET
        movement_type = COALESCE($1, movement_type),
        vehicle_type = COALESCE($2, vehicle_type),
        vehicle_number = COALESCE($3, vehicle_number),
        driver_name = COALESCE($4, driver_name),
        driver_number = COALESCE($5, driver_number),
        warehouse_id = COALESCE($6, warehouse_id),
        in_weight = COALESCE($7, in_weight),
        reference_no = COALESCE($8, reference_no),
        transporter_name = COALESCE($9, transporter_name),
lr_no = COALESCE($10, lr_no),
lr_date = COALESCE($11, lr_date),
transport_order_no = COALESCE($12, transport_order_no),
destination_name = COALESCE($13, destination_name),
delivery_qty = COALESCE($14, delivery_qty),
batch_no = COALESCE($15, batch_no),
security_remarks = COALESCE($16, security_remarks),
weighbridge_status = 'IN_PROGRESS',
updated_by = $17,
updated_at = NOW()
WHERE id = $18
      RETURNING *
      `,
      [
        clean(movementType),
        clean(vehicleType),
        clean(vehicleNumber),
        clean(driverName),
        clean(driverNumber),
        cleanNumber(warehouseId),
        cleanNumber(inWeight),
        clean(referenceNo),
        clean(transporterName),
        clean(lrNo),
        clean(lrDate),
        clean(transportOrderNo),
        clean(destinationName),
        cleanNumber(deliveryQty),
        clean(batchNo),
        clean(remarks),
        req.user?.id || null,
        id,
      ],
    );

    // 🔹 Check material_details exists
    const materialCheck = await client.query(
      `SELECT id FROM material_details WHERE vehicle_id = $1`,
      [id],
    );

    const materialValues = [
      clean(supplierName),
      clean(materialTypeString),
      clean(finalItemId),
      clean(finalItemName),
      clean(finalItemCode), // ✅ NEW
      clean(quantityUnit),
      clean(referenceNo),
      clean(invoiceNo),
      cleanNumber(invoiceAmount),
      clean(dcId),
      cleanNumber(qtyBags),
      materialsData,
      id,
    ];

    if (materialCheck.rows.length > 0) {
      // 🔹 UPDATE
      await client.query(
        `
        UPDATE material_details SET
          supplier_name = COALESCE($1, supplier_name),
          material_type = COALESCE($2, material_type),
          item_id       = COALESCE($3, item_id),
          material_name = COALESCE($4, material_name),
          item_code     = COALESCE($5, item_code), -- ✅ NEW
          quantity_unit = COALESCE($6, quantity_unit),
          po_number     = COALESCE($7, po_number),
          invoice_no    = COALESCE($8, invoice_no),
         invoice_amount = COALESCE($9, invoice_amount),
delivery_challan_no = COALESCE($10, delivery_challan_no),
quantity_bags = COALESCE($11, quantity_bags),
materials     = COALESCE($12, materials),
updated_at = NOW()
WHERE vehicle_id = $13
        `,
        materialValues,
      );
    } else {
      // 🔹 INSERT
      await client.query(
        `
        INSERT INTO material_details (
          vehicle_id,
          supplier_name,
          material_type,
          item_id,
          material_name,
          item_code,  -- ✅ NEW
          quantity_unit,
          po_number,
          invoice_no,
          invoice_amount,
          delivery_challan_no,
          quantity_bags,
          materials
        )
       VALUES ($13,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
        `,
        materialValues,
      );
    }

    await client.query("COMMIT");

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

    //   message: "Vehicle exited successfully",

    //   pdfUrl: `/uploads/docs/${year}/${month}/${pdfFileName}`,
    // });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("Update Vehicle Error:", error);

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

const fs = require("fs");
const path = require("path");

const downloadWeighmentSlip = async (req, res) => {
  try {
    const { id } = req.params;
    console.log("am i getting id", id);

    const result = await pool.query(
      `
      SELECT 
        weighment_slip_file,
        return_weight_done
      FROM commercial_vehicles
      WHERE id = $1
    `,
      [id],
    );

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

    const vehicle = result.rows[0];

    // weighment not completed
    if (!vehicle.return_weight_done) {
      return res.status(400).json({
        success: false,
        message:
          "Please complete weighment first. PDF slip will be generated after successful weighment.",
      });
    }

    // pdf missing
    if (!vehicle.weighment_slip_file) {
      return res.status(404).json({
        success: false,
        message: "Weighment slip not generated",
      });
    }

    // ABSOLUTE FILE PATH
    const filePath = path.join(
      __dirname,
      "../../",
      vehicle.weighment_slip_file.replace(/^\/+/, ""),
    );
    // FILE NOT EXISTS
    if (!fs.existsSync(filePath)) {
      return res.status(404).json({
        success: false,
        message: "PDF file not found on server",
      });
    }

    // FORCE DOWNLOAD
    return res.download(filePath, `weighment-slip-${id}.pdf`);
  } catch (error) {
    console.error("Download Slip Error:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to download weighment slip",
    });
  }
};

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

//   try {
//     const { id } = req.params;
//     const { outWeight, movementTypeOut } = req.body;
//     const userId = req.session.user?.id || null;

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

//     /* 🔍 LOCK VEHICLE */
//     const vehicleResult = await client.query(
//       `SELECT * FROM commercial_vehicles
//        WHERE id = $1 AND is_deleted = false
//        FOR UPDATE`,
//       [id],
//     );

//     if (vehicleResult.rowCount === 0) {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "Vehicle not found", 404);
//     }

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

//     if (vehicle.gate_status === "Exited") {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "Vehicle already exited", 400);
//     }

//     if (vehicle.stock_created) {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "Stock already created", 400);
//     }

//     /* 🔍 QC CHECK */
//     const qcResult = await client.query(
//       `SELECT * FROM quality_checks
//        WHERE vehicle_id = $1 AND is_deleted = false`,
//       [id],
//     );

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

//     if (!qc || qc.complete_status !== "Completed") {
//       await client.query("ROLLBACK");
//       return errorResponse(res, "QC not completed", 400);
//     }

//     /* 📝 EXIT UPDATE */
//     await client.query(
//       `UPDATE commercial_vehicles
//        SET out_weight = $1,
//            movement_type_out = $2,
//            gate_status = 'Exited',
//            exit_time = NOW(),
//            updated_by = $3
//        WHERE id = $4`,
//       [outWeight, movementTypeOut, userId, id],
//     );

//     /* ⚖️ NET QTY */
//     const gross = vehicle.in_weight || 0;
//     const tare = outWeight || 0;

//     const netQty =
//       vehicle.movement_type === "Unloading" ? gross - tare : tare - gross;

//     /* 🔹 FETCH MATERIAL DETAILS (IMPORTANT FIX) */
//     const materialRes = await client.query(
//       `SELECT
//         po_number,
//         supplier_name,
//         invoice_no,
//         delivery_challan_no,
//         material_name,
//         quantity_unit
//        FROM material_details
//        WHERE vehicle_id = $1
//        LIMIT 1`,
//       [id],
//     );

//     const material = materialRes.rows[0] || {};

//     const vendorName = material.supplier_name || null;
//     const poNumber = material.po_number || vehicle.reference_no || null;
//     const invoiceNo = material.invoice_no || null;
//     const dcNo = material.delivery_challan_no || null;

//     /* 🔹 CREATE GRN */
//     let poId = null;
//     let grnId = vehicle.grn_id;

//     if (!vehicle.grn_id) {
//       if (poNumber) {
//         const poRes = await client.query(
//           `SELECT id FROM purchase_orders WHERE po_number = $1`,
//           [poNumber],
//         );

//         if (poRes.rows.length > 0) {
//           poId = poRes.rows[0].id;
//         }
//       }

//       const grnNumber = `GRN-${Date.now()}`;

//       const grnResult = await client.query(
//         `INSERT INTO grn (
//           grn_number,
//           po_id,
//           po_number,
//           vendor_name,
//           gate_entry_no,
//           invoice_no,
//           delivery_challan_no,
//           vehicle_id,
//           created_by
//         )
//         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
//         RETURNING id`,
//         [
//           grnNumber,
//           poId,
//           poNumber,
//           vendorName,
//           vehicle.gate_entry_no || vehicle.vehicle_number,
//           invoiceNo,
//           dcNo,
//           id,
//           userId,
//         ],
//       );

//       // ✅ FIXED (no const)
//       grnId = grnResult.rows[0].id;

//       await client.query(
//         `UPDATE commercial_vehicles
//          SET grn_id = $1, grn_status = 'Draft'
//          WHERE id = $2`,
//         [grnId, id],
//       );

//       /* 🔹 FETCH PO ITEMS */
//       let poItems = [];

//       if (poId) {
//         const poItemsRes = await client.query(
//           `SELECT id, item_name, quantity, unit_price
//            FROM purchase_order_items
//            WHERE po_id = $1`,
//           [poId],
//         );

//         poItems = poItemsRes.rows;
//       }

//       /* 🔹 INSERT GRN ITEMS */
//       if (poItems.length > 0) {
//         for (const item of poItems) {
//           await client.query(
//             `INSERT INTO grn_items (
//               grn_id,
//               purchase_order_item_id,
//               item_name,
//               unit_price
//             )
//             VALUES ($1,$2,$3,$4)`,
//             [grnId, item.id, item.item_name, item.unit_price || 0],
//           );
//         }
//       } else {
//         await client.query(
//           `INSERT INTO grn_items (
//             grn_id,
//             item_name
//           )
//           VALUES ($1,$2)`,
//           [
//             grnId,
//             material.material_name || "General",
//             // vehicle.in_weight || 0,
//           ],
//         );
//       }
//     }

//     /* 📊 CREATE STOCK */
//     const stockResult = await client.query(
//       `INSERT INTO stocks (
//         vehicle_id,
//         vehicle_number,
//         material_details,
//         warehouse_id,
//         gross_weight,
//         tare_weight,
//         net_quantity,
//         received_by,
//         qc_by,
//         reference_no,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
//       RETURNING *`,
//       [
//         id,
//         vehicle.vehicle_number,
//         JSON.stringify(material),
//         vehicle.warehouse_id,
//         gross,
//         tare,
//         Math.abs(netQty),
//         userId,
//         userId,
//         vehicle.reference_no,
//         userId,
//       ],
//     );

//     await client.query(
//       `UPDATE commercial_vehicles
//        SET stock_created = true
//        WHERE id = $1`,
//       [id],
//     );

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

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle exit, GRN & stock created",
//       data: stockResult.rows[0],
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error(error);
//     return errorResponse(res, "Exit failed", 500);
//   } finally {
//     client.release();
//   }
// };

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

  try {
    const { id } = req.params;
    console.log(id, "am i getting this id");
    const { outWeight, netWeight, movementTypeOut, remarks } = req.body;
    const userId = req.session.user?.id || null;
    const org_id = req.session.user?.orgId;

    await client.query("BEGIN");

    /* LOCK VEHICLE */
    const vehicleResult = await client.query(
      `
  SELECT 
    cv.*,

    md.material_type AS product,
    md.supplier_name,
    md.quantity_bags,
    md.delivery_challan_no,
    md.quantity_unit,
    w.warehouse_name AS destination_name,

    u.first_name || ' ' || u.last_name 
      AS created_by_name

  FROM commercial_vehicles cv

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

  LEFT JOIN warehouses w
    ON w.id = cv.warehouse_id

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

  WHERE cv.id = $1
    AND cv.is_deleted = false

  FOR UPDATE OF cv
  `,
      [id],
    );

    if (vehicleResult.rowCount === 0) {
      await client.query("ROLLBACK");
      return errorResponse(res, "Vehicle not found", 404);
    }

    const vehicle = vehicleResult.rows[0];

    if (vehicle.stock_created) {
      await client.query("ROLLBACK");
      return errorResponse(res, "Stock already created", 400);
    }

    /* QC CHECK */
    const qcResult = await client.query(
      `SELECT * FROM quality_checks 
       WHERE vehicle_id = $1 AND is_deleted = false`,
      [id],
    );

    const qc = qcResult.rows[0];

    if (!qc || qc.complete_status !== "Completed") {
      await client.query("ROLLBACK");
      return errorResponse(res, "QC not completed", 400);
    }

    /* EXIT UPDATE */
    await client.query(
      `
UPDATE commercial_vehicles 
SET 
    out_weight = $1,
    net_weight = $2,
    movement_type_out = $3,
    security_remarks = $4,
    weighbridge_status = 'COMPLETED',
    return_weight_done = TRUE,
    exit_time = NOW(),
    updated_by = $5,
    updated_at = NOW()
WHERE id = $6
`,
      [
        outWeight,
        netWeight,
        movementTypeOut || null,
        remarks || null,
        userId,
        id,
      ],
    );

    /* Update QC with out_weight and net_weight */
    await client.query(
      `
UPDATE quality_checks
SET
    out_weight = $1,
    net_weight = $2,
    updated_at = NOW()
WHERE vehicle_id = $3
`,
      [outWeight, netWeight, id],
    );

    /* Fetch QC data for certificate generation */
    const qcDataRes = await client.query(
      `
SELECT
    qc.*,
    cv.in_weight,
    cv.out_weight,
    cv.net_weight,
    cv.vehicle_number,
    md.quantity_bags,
    md.material_name
FROM quality_checks qc
LEFT JOIN commercial_vehicles cv
    ON cv.id = qc.vehicle_id
LEFT JOIN material_details md
    ON md.vehicle_id = cv.id
WHERE qc.vehicle_id = $1
`,
      [id],
    );

    const qcData = qcDataRes.rows[0];
    console.log(qcData, "qcdata..");

    /* Calculate QC deductions for certificate */
    const itemCode = qcData.parameters?.items?.[0]?.item_code;

    const masterRes = await client.query(
      `
SELECT *
FROM qc_params
WHERE item_code = $1
`,
      [itemCode],
    );

    const receivedWeightMT = Number(qcData.net_weight);
    console.log("Received MT:", receivedWeightMT);

    //     const parameterHistoryRes = await client.query(
    //       `
    // SELECT *
    // FROM qc_parameter_history
    // WHERE qc_id = $1
    // ORDER BY id
    // `,
    //       [qc.id],
    //     );

    //     const parameterHistory = parameterHistoryRes.rows;
    //     console.log(parameterHistory, "history data..");

    //     const masterParameters = qcData.parameters?.items?.[0]?.parameters || [];

    //     const deductionRows = masterParameters.map((row) => ({
    //       parameter_name: row.parameterName,
    //       specification_type: row.condition,
    //       specification_value: row.specMax,
    //       accepted_with_penalty: row.penaltyMax != null ? `${row.penaltyMin ?? ""}${row.penaltyMin ? " to " : ""}${row.penaltyMax}` : "-",
    //       actual_result: row.value,
    //       deduction_percent: Number(row.deductionPercent || 0).toFixed(2),
    //       deduction_mt: Number(row.deductionWeight || 0) / 1000
    //     }));

    const parameterHistoryRes = await client.query(
      `
SELECT
  parameter_name,
  condition,
  spec_min,
  spec_max,
  penalty_min,
  penalty_max,

  ROUND(AVG(value)::numeric, 3) avg_value,

  ROUND(
    AVG(deduction_percent)::numeric,
    3
  ) avg_deduction_percent,

  ROUND(
    AVG(deduction_weight)::numeric,
    3
  ) avg_deduction_weight,

  MAX(parameter_status) parameter_status

FROM qc_parameter_history
WHERE qc_id = $1
GROUP BY
  parameter_name,
  condition,
  spec_min,
  spec_max,
  penalty_min,
  penalty_max
ORDER BY parameter_name
`,
      [qc.id],
    );

    const deductionRows =
      //  const deductionRows =
      parameterHistoryRes.rows.map((row) => ({
        parameter_name: row.parameter_name,

        specification_type: row.condition,

        specification_value: row.spec_max || row.spec_min,

        accepted_with_penalty:
          row.penalty_max != null
            ? `${row.penalty_min ?? ""}${
                row.penalty_min ? " to " : ""
              }${row.penalty_max}`
            : "-",

        actual_result: Number(row.avg_value).toFixed(2),

        deduction_percent: Number(row.avg_deduction_percent || 0),

        deduction_mt:
          (receivedWeightMT * Number(row.avg_deduction_percent || 0)) / 100,

        parameter_status: row.parameter_status,
      }));

    // console.log(deductionRows, "pdf rows");

    // let totalDeductionMT = 0;

    // deductionRows.forEach((row) => {
    //   totalDeductionMT += row.deduction_mt;
    // });

    // const bagWeightMT = (quantityBags * 200) / 1000000;
    const netWeightMT = Number(qcData.net_weight || 0);

    const bagQty = Math.round(netWeightMT * 2);
    const bagWeightMT =
      (bagQty * Number(qcData.bag_weight_per_bag || 0)) / 1000000;

    const totalDeductionMT =
      deductionRows.reduce((sum, row) => {
        return (
          sum + (receivedWeightMT * Number(row.deduction_percent || 0)) / 100
        );
      }, 0) + bagWeightMT;

    // const quantityBags = Number(qcData.quantity_bags || 0);
    const quantityBags = Math.round(
      (Number(qcData.net_weight || 0) / 1000) * 2,
    );

    // totalDeductionMT += bagWeightMT;
    // const finalWeightMT = receivedWeightMT - totalDeductionMT;
    const finalWeightMT = receivedWeightMT - totalDeductionMT;

    console.log(finalWeightMT, "weight");

    /* NET QTY - Use netWeight from request body */
    const receivedQty = parseFloat(netWeight) || 0;

    /* FETCH MATERIAL DETAILS */
    const materialRes = await client.query(
      `SELECT 
        po_number,
        supplier_name,
        invoice_no,
        delivery_challan_no,
        material_name,
        quantity_unit,
        material_type,
        materials
       FROM material_details
       WHERE vehicle_id = $1
       LIMIT 1`,
      [id],
    );

    const material = materialRes.rows[0] || {};
    const vendorName = material.supplier_name || null;
    const poNumber = material.po_number || vehicle.reference_no || null;
    const invoiceNo = material.invoice_no || null;
    const dcNo = material.delivery_challan_no || null;

    /* CREATE GRN */
    let poId = null;
    let grnId = vehicle.grn_id;
    let poItems = [];

    if (!vehicle.grn_id) {
      if (poNumber) {
        const poRes = await client.query(
          `SELECT id FROM purchase_orders WHERE po_number = $1`,
          [poNumber],
        );

        if (poRes.rows.length > 0) {
          poId = poRes.rows[0].id;
        }
      }

      const grnNumber = `GRN-${Date.now()}`;

      const grnResult = await client.query(
        `INSERT INTO grn (
          grn_number,
          po_id,
          po_number,
          vendor_name,
          gate_entry_no,
          invoice_no,
          delivery_challan_no,
          vehicle_id,
          grn_date,
          created_by,
          created_at,
          org_id
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,NOW(),$9,NOW(),$10)
        RETURNING id`,
        [
          grnNumber,
          poId,
          poNumber,
          vendorName,
          vehicle.gate_entry_no || vehicle.vehicle_number,
          invoiceNo,
          dcNo,
          id,
          userId,
          org_id,
        ],
      );

      grnId = grnResult.rows[0].id;

      await client.query(
        `UPDATE commercial_vehicles 
         SET grn_id = $1, grn_status = 'Draft'
         WHERE id = $2`,
        [grnId, id],
      );

      /* FETCH PO ITEMS */
      if (poId) {
        const poItemsRes = await client.query(
          `SELECT id, item_name, quantity, unit_price, uom
           FROM purchase_order_items
           WHERE po_id = $1`,
          [poId],
        );
        poItems = poItemsRes.rows;
        console.log("PO Items fetched:", poItems.length);
      }

      /* INSERT GRN ITEMS - USE netWeight AS received_qty */
      if (poItems.length > 0) {
        console.log("Creating GRN items...");
        for (const item of poItems) {
          const orderedQty = parseFloat(item.quantity) || 0;
          const receivedQtyValue = receivedQty;
          const acceptedQtyValue = receivedQtyValue;
          const rejectedQtyValue = 0;
          const unitPrice = parseFloat(item.unit_price) || 0;
          const totalPrice = acceptedQtyValue * unitPrice;

          await client.query(
            `INSERT INTO grn_items (
              grn_id,
              purchase_order_item_id,
              item_name,
              ordered_qty,
              received_qty,
              accepted_qty,
              rejected_qty,
              unit_price,
              total_price,
              rejected_unit_price,
              rejected_total_price,
              grand_total,
              store_location
            ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)`,
            [
              grnId,
              item.id,
              item.item_name,
              orderedQty,
              receivedQtyValue,
              acceptedQtyValue,
              rejectedQtyValue,
              unitPrice,
              totalPrice,
              0,
              0,
              totalPrice,
              "Main Store",
            ],
          );
          console.log(
            `Created GRN item for: ${item.item_name}, Received: ${receivedQtyValue}`,
          );
        }
      } else {
        /* Fallback if no PO items found */
        const defaultMaterial =
          material.material_name || vehicle.goods || "General Material";

        await client.query(
          `INSERT INTO grn_items (
            grn_id,
            item_name,
            ordered_qty,
            received_qty,
            accepted_qty,
            rejected_qty,
            unit_price,
            total_price,
            rejected_unit_price,
            rejected_total_price,
            grand_total,
            store_location
          ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)`,
          [
            grnId,
            defaultMaterial,
            receivedQty,
            receivedQty,
            receivedQty,
            0,
            0,
            0,
            0,
            0,
            0,
            "Main Store",
          ],
        );
        console.log(
          `Created default GRN item for: ${defaultMaterial}, Received: ${receivedQty}`,
        );

        /* Create a default PO item array for stock creation */
        poItems = [
          {
            id: null,
            item_name: defaultMaterial,
            quantity: receivedQty,
            unit_price: 0,
            uom: material.quantity_unit || "Tons",
          },
        ];
      }
    } else {
      /* If GRN already exists, fetch PO items separately */
      if (poNumber) {
        const poRes = await client.query(
          `SELECT id FROM purchase_orders WHERE po_number = $1`,
          [poNumber],
        );
        if (poRes.rows.length > 0) {
          poId = poRes.rows[0].id;
          const poItemsRes = await client.query(
            `SELECT id, item_name, quantity, unit_price, uom
             FROM purchase_order_items
             WHERE po_id = $1`,
            [poId],
          );
          poItems = poItemsRes.rows;
        }
      }
    }

    /* CREATE STOCK ENTRIES - Use netWeight */
    const createdStocks = [];

    if (poItems.length > 0) {
      /* Calculate quantity per item (distribute net quantity proportionally) */
      const totalPoQuantity = poItems.reduce(
        (sum, item) => sum + (parseFloat(item.quantity) || 0),
        0,
      );

      for (const poItem of poItems) {
        /* Calculate quantity for this specific item */
        let itemNetQuantity = receivedQty;
        if (totalPoQuantity > 0 && poItem.quantity) {
          const itemRatio = parseFloat(poItem.quantity) / totalPoQuantity;
          itemNetQuantity = receivedQty * itemRatio;
        }

        const materialName = poItem.item_name;
        const quantityUnit = poItem.uom || "Tons";

        console.log(
          `Creating stock entry for: ${materialName}, Quantity: ${itemNetQuantity} ${quantityUnit}`,
        );

        const stockResult = await client.query(
          `INSERT INTO stocks (
            vehicle_id,
            vehicle_number,
            po_number,
            supplier_name,
            material_type,
            material_name,
            quantity_unit,
            warehouse_id,
            gross_weight,
            tare_weight,
            net_quantity,
            received_by,
            qc_by,
            reference_no,
            created_by,
            material_details,
            org_id,
            received_date
          )
          VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,NOW())
          RETURNING *`,
          [
            id,
            vehicle.vehicle_number,
            poNumber,
            vendorName,
            materialName,
            materialName,
            quantityUnit,
            vehicle.warehouse_id,
            vehicle.in_weight || 0,
            outWeight || 0,
            itemNetQuantity,
            userId,
            userId,
            vehicle.reference_no,
            userId,
            JSON.stringify({
              ...material,
              item_name: materialName,
              item_quantity: poItem.quantity,
            }),
            vehicle.org_id,
          ],
        );

        createdStocks.push(stockResult.rows[0]);
      }
    } else {
      /* Fallback: Create single stock entry */
      const finalMaterialName =
        material.material_name || vehicle.goods || "General Material";
      const finalQuantityUnit = material.quantity_unit || "Tons";

      const stockResult = await client.query(
        `INSERT INTO stocks (
          vehicle_id,
          vehicle_number,
          po_number,
          supplier_name,
          material_type,
          material_name,
          quantity_unit,
          warehouse_id,
          gross_weight,
          tare_weight,
          net_quantity,
          received_by,
          qc_by,
          reference_no,
          created_by,
          material_details,
          org_id,
          received_date
        )
        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,NOW())
        RETURNING *`,
        [
          id,
          vehicle.vehicle_number,
          poNumber,
          vendorName,
          finalMaterialName,
          finalMaterialName,
          finalQuantityUnit,
          vehicle.warehouse_id,
          vehicle.in_weight || 0,
          outWeight || 0,
          receivedQty,
          userId,
          userId,
          vehicle.reference_no,
          userId,
          JSON.stringify(material),
          vehicle.org_id,
        ],
      );
      createdStocks.push(stockResult.rows[0]);
    }

    await client.query(
      `UPDATE commercial_vehicles 
       SET stock_created = true 
       WHERE id = $1`,
      [id],
    );

    /* GENERATE PDFs */
    const generateWeighmentSlipPDF = require("../../services/pdf/generateWeighmentSlipPDF");
    const generateQCCertificatePDF = require("../../services/pdf/generateQCCertificatePDF");

    /* Generate Weighment Slip PDF */
    const pdf = await generateWeighmentSlipPDF({
      ...vehicle,
      out_weight: outWeight,
      exit_time: new Date(),
      product: material.material_type || "-",
      supplier_name: material.supplier_name || "-",
      delivery_challan_no: material.delivery_challan_no || "-",
      delivery_qty: receivedQty,
      net_weight: receivedQty,
      gross_weight: vehicle.in_weight || 0,
      tare_weight: outWeight || 0,
      userId,
    });

    /* Generate QC Certificate PDF */
    const qcPdfData = {
      ...qcData,
      po_date: qcData.po_date,
      invoice_no: qcData.invoice_no,
      received_weight_mt: receivedWeightMT.toFixed(3),
      total_deduction_mt: totalDeductionMT.toFixed(3),
      final_weight_mt: finalWeightMT.toFixed(3),
      total_deduction_percent: qcData.total_deduction_percent,
      bag_weight_mt: bagWeightMT.toFixed(3),
      parameters: deductionRows,
      qc_result: qcData.qc_result,

      bag_type: qcData.bag_type,

      bag_qty: qcData.bag_qty,

      bag_weight_per_bag: qcData.bag_weight_per_bag,

      certificate_file: qcData.qc_certificate_file,

      attachment: qcData.attachment,
    };

    const qcPdf = await generateQCCertificatePDF(qcPdfData);

    /* Save PDFs to filesystem */
    const fs = require("fs");
    const path = require("path");

    const now = new Date();
    const year = now.getFullYear();
    const month = String(now.getMonth() + 1).padStart(2, "0");
    const pdfDir = path.join(process.cwd(), `uploads/docs/${year}/${month}`);

    if (!fs.existsSync(pdfDir)) {
      fs.mkdirSync(pdfDir, { recursive: true });
    }

    /* Save Weighment Slip */
    const pdfFileName = `weighment-slip-${id}-${Date.now()}.pdf`;
    const pdfPath = path.join(pdfDir, pdfFileName);
    fs.writeFileSync(pdfPath, pdf);

    await client.query(
      `UPDATE commercial_vehicles SET weighment_slip_file = $1 WHERE id = $2`,
      [`uploads/docs/${year}/${month}/${pdfFileName}`, id],
    );

    /* Save QC Certificate */
    const qcFileName = `qc-certificate-${id}-${Date.now()}.pdf`;
    const qcPath = path.join(pdfDir, qcFileName);
    fs.writeFileSync(qcPath, qcPdf);

    await client.query(
      `UPDATE quality_checks SET qc_certificate_file = $1, updated_at = NOW() WHERE id = $2`,
      [`uploads/docs/${year}/${month}/${qcFileName}`, qc.id],
    );

    await client.query("COMMIT");

    const totalQuantity = createdStocks.reduce(
      (sum, stock) => sum + (parseFloat(stock.net_quantity) || 0),
      0,
    );

    console.log("=== FINAL RESULT ===");
    console.log(`Created ${createdStocks.length} stock entries`);
    console.log(`Total quantity: ${totalQuantity}`);

    return res.status(200).json({
      success: true,
      message: `Vehicle exit successful. Created ${createdStocks.length} stock entries. Total quantity: ${totalQuantity}`,
      data: {
        stocks: createdStocks,
        grnId: grnId,
        totalStockEntries: createdStocks.length,
        totalQuantity: totalQuantity,
      },
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Exit Error:", error);
    return errorResponse(res, error.message || "Exit failed", 500);
  } finally {
    client.release();
  }
};

const deleteVehicle = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.session?.user?.id || null;

    const query = `
      UPDATE commercial_vehicles
      SET 
        is_deleted = true,
        updated_by = $1,
        updated_at = NOW()
      WHERE id = $2
      RETURNING id
    `;

    const result = await pool.query(query, [userId, id]);

    if (result.rowCount === 0) {
      return res.status(404).json({
        success: false,
        message: "Vehicle not found",
      });
    }

    return res.status(200).json({
      success: true,
      message: "Vehicle deleted successfully",
    });
  } catch (error) {
    console.error("Delete Vehicle Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete vehicle",
    });
  }
};

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

//     const updatedVehicle = await CommercialVehicle.findByIdAndUpdate(
//       id,
//       {
//         gateStatus: "Exited",
//         exitTime: new Date(),
//       },
//       { new: true }
//     );

//     if (!updatedVehicle) {
//       return res.status(404).json({
//         success: false,
//         message: "Vehicle not found",
//       });
//     }

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle exited successfully",
//       data: updatedVehicle,
//     });
//   } catch (error) {
//     console.log("EXIT VEHICLE ERROR:", error);

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

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

//     console.log("is it hitting the api here...")

//     const query = `
//       UPDATE commercial_vehicles
//       SET
//         gate_status = 'Exited',
//         exit_time = NOW()
//       WHERE id = $1
//       RETURNING *;
//     `;

//     const result = await pool.query(query, [id]);

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

//     return res.status(200).json({
//       success: true,
//       message: "Vehicle exited successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.log("EXIT VEHICLE ERROR:", error);

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

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

  try {
    const { id } = req.params;

    console.log("EXIT VEHICLE API HIT...");

    // =====================================================
    // FETCH VEHICLE DETAILS
    // =====================================================

    // const vehicleQuery = `
    //   SELECT
    //     cv.id,
    //     cv.vehicle_number,
    //     cv.gate_status,
    //     cv.exit_time,
    //     cv.weighbridge_status,
    //     cv.source_type,

    //     sd.id AS shipment_id,
    //     sd.shipment_number,
    //     sd.shipment_status,
    //     sd.return_weight_done,
    //     sd.qc_done

    //   FROM commercial_vehicles cv

    //   LEFT JOIN shipment_dispatches sd
    //     ON sd.vehicle_id = cv.id

    //   WHERE cv.id = $1
    // `;
    const vehicleQuery = `
  SELECT
    cv.id,
    cv.vehicle_number,
    cv.gate_status,
    cv.exit_time,
    cv.weighbridge_status,
    cv.source_type,
    cv.sales_order_id,

    sd.id AS shipment_id,
    sd.shipment_number,
    sd.shipment_status,
    sd.return_weight_done,
    sd.qc_done,

    EXISTS (
      SELECT 1
      FROM sales_order_items soi

      INNER JOIN procurement_itemmaster pim
        ON pim.id = soi.product_id

      WHERE soi.sales_order_id = cv.sales_order_id
      AND pim.qc_required = true
    ) AS qc_required

  FROM commercial_vehicles cv

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

  WHERE cv.id = $1
`;

    const vehicleResult = await client.query(vehicleQuery, [id]);

    // =====================================================
    // VEHICLE NOT FOUND
    // =====================================================

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

    const vehicle = vehicleResult.rows[0];

    console.log(vehicle, "vehicle details...");

    // =====================================================
    // VEHICLE ALREADY EXITED
    // =====================================================

    if (vehicle.gate_status === "Exited") {
      return res.status(400).json({
        success: false,
        message: "Vehicle has already exited from the gate",
      });
    }

    // =====================================================
    // WEIGHBRIDGE VALIDATION
    // BOTH FLOWS MUST COMPLETE WEIGHBRIDGE
    // =====================================================

    if (vehicle.weighbridge_status !== "COMPLETED") {
      return res.status(400).json({
        success: false,
        message:
          "Vehicle exit is not allowed until weighbridge process is completed",
      });
    }

    // =====================================================
    // FLOW 1 : NORMAL VEHICLE FLOW
    // source_type IS NULL
    // ONLY WEIGHBRIDGE COMPLETION REQUIRED
    // =====================================================

    const isNormalFlow = !vehicle.source_type;

    if (isNormalFlow) {
      const updateQuery = `
        UPDATE commercial_vehicles

        SET
          gate_status = 'Exited',
          exit_time = NOW(),
          updated_at = NOW()

        WHERE id = $1

        RETURNING *;
      `;

      const result = await client.query(updateQuery, [id]);

      return res.status(200).json({
        success: true,
        message: "Vehicle exited successfully",
        data: result.rows[0],
      });
    }

    // =====================================================
    // FLOW 2 : SHIPMENT FLOW
    // source_type = 'SHIPMENT'
    // QC + RETURN WEIGHT VALIDATIONS REQUIRED
    // =====================================================

    const isShipmentFlow = vehicle.source_type === "SHIPMENT";

    if (isShipmentFlow) {
      // =========================================
      // SHIPMENT VALIDATION
      // =========================================

      if (!vehicle.shipment_id) {
        return res.status(400).json({
          success: false,
          message: "Shipment details are not mapped to this vehicle",
        });
      }

      // =========================================
      // QC VALIDATION
      // =========================================

      // if (!vehicle.qc_done) {
      //   return res.status(400).json({
      //     success: false,
      //     message:
      //       "Vehicle exit is not allowed until QC verification is completed",
      //   });
      // }
      const qcRequired = vehicle.qc_required === true;

      if (qcRequired && !vehicle.qc_done) {
        return res.status(400).json({
          success: false,
          message:
            "Vehicle exit is not allowed until QC verification is completed",
        });
      }

      // =========================================
      // RETURN WEIGHT VALIDATION
      // =========================================

      const isPartialDispatch = vehicle.shipment_status === "Partial";
      //     const isPartialDispatch =
      // vehicle.shipment_status
      //   ?.toLowerCase() === "partial";

      if (isPartialDispatch && !vehicle.return_weight_done) {
        return res.status(400).json({
          success: false,
          message:
            "Vehicle exit is not allowed until return weighment is completed",
        });
      }
    }

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

    const updateQuery = `
      UPDATE commercial_vehicles

      SET
        gate_status = 'Exited',
        exit_time = NOW(),
        updated_at = NOW()

      WHERE id = $1

      RETURNING *;
    `;

    const result = await client.query(updateQuery, [id]);

    // =====================================================
    // UPDATE GATE PASS STATUS
    // =====================================================

    await client.query(
      `
  UPDATE gate_passes

  SET
    pass_status = 'EXITED',
    exited_at = NOW(),
    updated_at = NOW()

  WHERE commercial_vehicle_id = $1
  AND pass_status = 'ACTIVE'
  `,
      [id],
    );

    // =====================================================
    // SUCCESS RESPONSE
    // =====================================================

    return res.status(200).json({
      success: true,
      message: "Vehicle exited successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.log("EXIT VEHICLE ERROR:", error);

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

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

    const query = `
      SELECT
        cv.id,
        cv.vehicle_number,
        cv.vehicle_type,
        cv.driver_name,
        cv.driver_number,
        cv.movement_type,
        cv.reference_no,
        cv.in_weight,
        cv.out_weight,
        cv.net_weight,
        cv.gate_pass_no,
        cv.gate_pass_date,
        cv.transporter_name,
        cv.lr_no,
        cv.lr_date,
        cv.transport_order_no,
        cv.destination_name,
        cv.delivery_qty,
        cv.batch_no,
        cv.created_at,

        md.material_name,
        md.supplier_name,
        md.delivery_challan_no,
        md.quantity_bags,
        md.invoice_no

      FROM commercial_vehicles cv

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

      WHERE cv.id = $1
    `;

    const result = await pool.query(query, [id]);

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

    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (error) {
    console.log(error);

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

const getDispatchWaitingVehicles = async (req, res) => {
  try {
    //     const result = await pool.query(`
    // SELECT
    //   sd.id,
    //   sd.shipment_number,
    //   sd.sales_order_number,
    //   sd.vehicle_number,
    //   sd.vehicle_type,
    //   sd.driver_name,
    //   sd.driver_contact_number,
    //   sd.dispatch_datetime,
    //   sd.customer_name

    // FROM shipment_dispatches sd

    // WHERE sd.vehicle_assigned = true
    // AND sd.shipment_status = 'Pending'
    // AND COALESCE(sd.is_deleted,false) = false

    // ORDER BY sd.created_at DESC
    // `);
    const result = await pool.query(`
SELECT
  sd.id,
  sd.shipment_number,
  sd.sales_order_number,
  sd.vehicle_number,
  sd.vehicle_type,
  sd.driver_name,
  sd.driver_contact_number,
  sd.dispatch_datetime,
  sd.customer_name

FROM shipment_dispatches sd

WHERE sd.vehicle_assigned = true
AND sd.shipment_status = 'Pending'
AND sd.approval_status = 'Approved'
AND COALESCE(sd.is_deleted,false) = false

ORDER BY sd.created_at DESC
`);

    return res.status(200).json({
      success: true,
      data: result.rows,
    });
  } catch (error) {
    console.log(error);

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

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

    await pool.query(
      `
      UPDATE shipment_dispatches
      SET shipment_status = 'Gate In'
      WHERE id = $1
      `,
      [id],
    );

    return res.status(200).json({
      success: true,
      message: "Gate In Updated",
    });
  } catch (error) {
    console.log(error);

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

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

  try {
    await client.query("BEGIN");

    const userId = req.session?.user?.id || null;
    const orgId = req.session?.user?.orgId;

    if (!orgId) {
      return res.status(400).json({
        success: false,
        message: "Organization not found",
      });
    }

    const {
      // VEHICLE
      vehicleNumber,
      vehicleType,
      movementType,
      driverName,
      driverNumber,
      tankerCapacity,
      sealNumber,
      previousLoad,
      reference_no,
      materialTypeId,
      quantityInLtrs,
      batchNo,
      // SHIPMENT
      sourceType,
      shipmentId,
      shipmentNumber,
      salesOrderId,
      salesOrderNumber,
      allocationId,
      transportAgencyId,
      customerName,
      dispatchStatus,
    } = req.body;

    // ========================
    // VALIDATION
    // ========================

    if (!vehicleNumber || !vehicleType || !movementType || !driverName) {
      return res.status(400).json({
        success: false,
        message: "Required fields missing",
      });
    }

    // ========================
    // FORMAT
    // ========================

    const formattedVehicleNumber = vehicleNumber.trim().toUpperCase();

    // ========================
    // CHECK IF VEHICLE IS ALREADY INSIDE THE PLANT
    // ========================

    const existingVehicleQuery = `
      SELECT id, vehicle_number, gate_status, gate_pass_no, created_at
      FROM commercial_vehicles
      WHERE vehicle_number = $1 
      AND org_id = $2
      AND gate_status = 'Inside'
      ORDER BY created_at DESC
      LIMIT 1
    `;

    const existingVehicleResult = await client.query(existingVehicleQuery, [
      formattedVehicleNumber,
      orgId,
    ]);

    if (existingVehicleResult.rows.length > 0) {
      const existingVehicle = existingVehicleResult.rows[0];
      return res.status(400).json({
        success: false,
        message: `Vehicle ${formattedVehicleNumber} is already inside the plant. It must exit before a new entry can be created.`,
        data: {
          vehicle_number: existingVehicle.vehicle_number,
          gate_pass_no: existingVehicle.gate_pass_no,
          gate_status: existingVehicle.gate_status,
          entry_time: existingVehicle.created_at,
        },
      });
    }

    const qcStatus = "ENTRY DONE";
    const weighbridgeStatus = "PENDING";
    const gateStatus = "Inside";

    // ========================
    // TANKER DETAILS
    // ========================

    let tankerDetails = null;

    if (tankerCapacity || sealNumber || previousLoad) {
      tankerDetails = {
        capacity: tankerCapacity || null,
        sealNumber: sealNumber || null,
        previousLoad: previousLoad || null,
      };
    }

    // ========================
    // GATE PASS
    // ========================

    const year = new Date().getFullYear();

    const countResult = await client.query(`
        SELECT COUNT(*) 
        FROM commercial_vehicles
      `);

    const count = Number(countResult.rows[0].count) + 1;

    const gatePassNo = `GP-${year}-${String(count).padStart(5, "0")}`;
    const gatePassDate = new Date();

    // ========================
    // INSERT
    // ========================

    const insertQuery = `
      INSERT INTO commercial_vehicles (

        vehicle_number,
        vehicle_type,

        reference_no,

        movement_type,

        driver_name,
        driver_number,

        tanker_details,

        material_type_id,

        batch_no,

        quantity_in_ltrs,

        qc_status,

        weighbridge_status,

        gate_status,

        gate_pass_no,
        gate_pass_date,

        source_type,

        shipment_id,
        shipment_number,

        sales_order_id,
        sales_order_number,

        allocation_id,

        transport_agency_id,

        customer_name,

        dispatch_status,

        org_id,

        created_by,
        updated_by,

        created_at,
        updated_at

      )

      VALUES (

        $1,$2,$3,
        $4,
        $5,$6,
        $7,
        $8,
        $9,
        $10,
        $11,
        $12,
        $13,
        $14,$15,
        $16,
        $17,$18,
        $19,$20,
        $21,
        $22,
        $23,
        $24,
        $25,
        $26,$27,
        NOW(),NOW()

      )

      RETURNING *;
    `;

    const values = [
      formattedVehicleNumber,
      vehicleType,
      reference_no || null,
      movementType,
      driverName,
      driverNumber || null,
      tankerDetails ? JSON.stringify(tankerDetails) : null,
      materialTypeId || null,
      batchNo || null,
      quantityInLtrs || null,
      qcStatus,
      weighbridgeStatus,
      gateStatus,
      gatePassNo,
      gatePassDate,
      sourceType || "SHIPMENT",
      shipmentId || null,
      shipmentNumber || null,
      salesOrderId || null,
      salesOrderNumber || null,
      allocationId || null,
      transportAgencyId || null,
      customerName || null,
      dispatchStatus || "Pending",
      Number(orgId),
      userId,
      userId,
    ];

    const result = await client.query(insertQuery, values);
    const insertedVehicle = result.rows[0];

    // ========================
    // CREATE GATE PASS CREATION (Commented as per original)
    // ========================

    // ========================
    // WEIGHBRIDGE TICKET
    // ========================

    const ticketNo = `WB-${year}-${String(insertedVehicle.id).padStart(4, "0")}`;

    await client.query(
      `
      UPDATE commercial_vehicles
      SET ticket_no = $1
      WHERE id = $2
      `,
      [ticketNo, insertedVehicle.id],
    );

    insertedVehicle.ticket_no = ticketNo;

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

    if (shipmentId) {
      await client.query(
        `
    UPDATE shipment_dispatches
    SET
      vehicle_id = $1,
      shipment_status = 'Gate In'
    WHERE id = $2
    `,
        [insertedVehicle.id, shipmentId],
      );
    }

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Dispatch vehicle entry created successfully",
      data: insertedVehicle,
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Dispatch Vehicle Entry Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to create dispatch vehicle entry",
      error: error.message,
    });
  } finally {
    client.release();
  }
};
// const createDispatchVehicleEntry = async (req, res) => {
//   const client = await pool.connect();

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

//     const userId = req.session?.user?.id || null;

//     const orgId = req.session?.user?.orgId;

//     if (!orgId) {
//       return res.status(400).json({
//         success: false,
//         message: "Organization not found",
//       });
//     }

//     const {
//       // VEHICLE
//       vehicleNumber,
//       vehicleType,
//       movementType,

//       driverName,
//       driverNumber,

//       tankerCapacity,
//       sealNumber,
//       previousLoad,

//       reference_no,

//       materialTypeId,

//       quantityInLtrs,

//       batchNo,

//       // SHIPMENT
//       sourceType,

//       shipmentId,

//       shipmentNumber,

//       salesOrderId,

//       salesOrderNumber,

//       allocationId,

//       transportAgencyId,

//       customerName,

//       dispatchStatus,
//     } = req.body;

//     // ========================
//     // VALIDATION
//     // ========================

//     if (!vehicleNumber || !vehicleType || !movementType || !driverName) {
//       return res.status(400).json({
//         success: false,
//         message: "Required fields missing",
//       });
//     }

//     // ========================
//     // FORMAT
//     // ========================

//     const formattedVehicleNumber = vehicleNumber.trim().toUpperCase();

//     const qcStatus = "ENTRY DONE";

//     const weighbridgeStatus = "PENDING";

//     const gateStatus = "Inside";

//     // ========================
//     // TANKER DETAILS
//     // ========================

//     let tankerDetails = null;

//     if (tankerCapacity || sealNumber || previousLoad) {
//       tankerDetails = {
//         capacity: tankerCapacity || null,

//         sealNumber: sealNumber || null,

//         previousLoad: previousLoad || null,
//       };
//     }

//     // ========================
//     // GATE PASS
//     // ========================

//     const year = new Date().getFullYear();

//     const countResult = await client.query(`
//         SELECT COUNT(*)
//         FROM commercial_vehicles
//       `);

//     const count = Number(countResult.rows[0].count) + 1;

//     const gatePassNo = `GP-${year}-${String(count).padStart(5, "0")}`;

//     const gatePassDate = new Date();

//     // ========================
//     // INSERT
//     // ========================

//     const insertQuery = `
//       INSERT INTO commercial_vehicles (

//         vehicle_number,
//         vehicle_type,

//         reference_no,

//         movement_type,

//         driver_name,
//         driver_number,

//         tanker_details,

//         material_type_id,

//         batch_no,

//         quantity_in_ltrs,

//         qc_status,

//         weighbridge_status,

//         gate_status,

//         gate_pass_no,
//         gate_pass_date,

//         source_type,

//         shipment_id,
//         shipment_number,

//         sales_order_id,
//         sales_order_number,

//         allocation_id,

//         transport_agency_id,

//         customer_name,

//         dispatch_status,

//         org_id,

//         created_by,
//         updated_by,

//         created_at,
//         updated_at

//       )

//       VALUES (

//         $1,$2,$3,
//         $4,
//         $5,$6,
//         $7,
//         $8,
//         $9,
//         $10,
//         $11,
//         $12,
//         $13,
//         $14,$15,
//         $16,
//         $17,$18,
//         $19,$20,
//         $21,
//         $22,
//         $23,
//         $24,
//         $25,
//         $26,$27,
//         NOW(),NOW()

//       )

//       RETURNING *;
//     `;

//     const values = [
//       formattedVehicleNumber,
//       vehicleType,

//       reference_no || null,

//       movementType,

//       driverName,
//       driverNumber || null,

//       tankerDetails ? JSON.stringify(tankerDetails) : null,

//       materialTypeId || null,

//       batchNo || null,

//       quantityInLtrs || null,

//       qcStatus,

//       weighbridgeStatus,

//       gateStatus,

//       gatePassNo,
//       gatePassDate,

//       sourceType || "SHIPMENT",

//       shipmentId || null,
//       shipmentNumber || null,

//       salesOrderId || null,
//       salesOrderNumber || null,

//       allocationId || null,

//       transportAgencyId || null,

//       customerName || null,

//       dispatchStatus || "Pending",

//       Number(orgId),

//       userId,
//       userId,
//     ];

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

//     const insertedVehicle = result.rows[0];

//     // ========================

//     // CREATE GATE PASS CREATATION
//     // ========================

//   //   await client.query(
//   //     `
//   // INSERT INTO gate_passes (
//   //   commercial_vehicle_id,
//   //   pass_number,
//   //   pass_type,
//   //   pass_status,
//   //   generated_at,
//   //   generated_by,
//   //   org_id,
//   //   sales_order_number,
//   //   created_at,
//   //   updated_at
//   // )
//   // VALUES (
//   //   $1,$2,$3,$4,
//   //   NOW(),
//   //   $5,$6,$7,
//   //   NOW(),NOW()
//   // )
//   // `,
//   //     [insertedVehicle.id, gatePassNo, "ENTRY", "ACTIVE",salesOrderNumber, userId, orgId],
//   //   );

// //  await client.query(
// //   `
// //   INSERT INTO gate_passes (
// //     commercial_vehicle_id,
// //     pass_number,
// //     pass_type,
// //     pass_status,
// //     generated_at,
// //     generated_by,
// //     org_id,
// //     sales_order_number,
// //     created_at,
// //     updated_at
// //   )
// //   VALUES (
// //     $1, $2, $3, $4,
// //     NOW(),
// //     $5, $6, $7,
// //     NOW(), NOW()
// //   )
// //   `,
// //   [insertedVehicle.id, gatePassNo, "ENTRY", "ACTIVE", userId, orgId, salesOrderNumber]
// // );

//     // ========================
//     // WEIGHBRIDGE TICKET
//     // ========================

//     const ticketNo = `WB-${year}-${String(insertedVehicle.id).padStart(
//       4,
//       "0",
//     )}`;

//     await client.query(
//       `
//       UPDATE commercial_vehicles
//       SET ticket_no = $1
//       WHERE id = $2
//       `,
//       [ticketNo, insertedVehicle.id],
//     );

//     insertedVehicle.ticket_no = ticketNo;

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

//     // if (shipmentId) {
//     //   await client.query(
//     //     `
//     //     UPDATE shipment_dispatches
//     //     SET shipment_status = 'Gate In'
//     //     WHERE id = $1
//     //     `,
//     //     [shipmentId],
//     //   );
//     // }
//     if (shipmentId) {
//       await client.query(
//         `
//     UPDATE shipment_dispatches
//     SET
//       vehicle_id = $1,
//       shipment_status = 'Gate In'
//     WHERE id = $2
//     `,
//         [insertedVehicle.id, shipmentId],
//       );
//     }

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

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

//       message: "Dispatch vehicle entry created successfully",

//       data: insertedVehicle,
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("Dispatch Vehicle Entry Error:", error);

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

//       message: "Failed to create dispatch vehicle entry",

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

const getDispatchWeighbridgeDetails = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    const { shipmentId } = req.params;

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

    const query = `

      SELECT

        -- SHIPMENT
        sd.id,

        sd.shipment_number,

        sd.shipment_status,

   

        sd.org_id,

        -- SALES ORDER
        sd.sales_order_id,

        sd.sales_order_number,

        -- CUSTOMER
        sd.customer_name,

        sd.customer_code,

        -- SHIPPING
        sd.shipping_address,

        sd.shipping_city,

        sd.shipping_state,

        sd.shipping_pincode,

        -- TRANSPORT
        sd.transport_mode,

        sd.transport_agency_id,

        sd.transport_agency_name,

        -- VEHICLE
        sd.vehicle_type,

        sd.vehicle_number,

        sd.vehicle_capacity,

        -- DRIVER
        sd.driver_name,

        sd.driver_contact_number,

        sd.driver_license_number,

        -- DISPATCH
        sd.freight_amount,

        sd.lr_number,

        sd.seal_number,

        sd.dispatch_datetime,

        sd.expected_delivery_datetime,

        -- SAFETY
        sd.density,

        sd.dispatch_temperature,

        sd.hazard_class,

        sd.ppe_required,

        sd.emergency_contact_person,

        sd.emergency_contact,

        sd.fire_safety_equipment,

        sd.spill_kit_available,

        -- PRODUCT
        sdi.product_id,

        sdi.product_name,

        sdi.allocated_qty,

        sdi.dispatch_qty,

        sdi.batch_number,

        sdi.uom,

        -- EXTRA
        sd.created_at

      FROM shipment_dispatches sd

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

      WHERE
      sd.id = $1

      AND sd.org_id = $2

      LIMIT 1

    `;

    const result = await pool.query(query, [shipmentId, orgId]);

    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,

        message: "Shipment dispatch not found",
      });
    }

    const row = result.rows[0];

    // =========================
    // CLEAN RESPONSE
    // =========================

    const responseData = {
      shipment: {
        id: row.id,

        shipmentNumber: row.shipment_number,

        shipmentStatus: row.shipment_status,

        dispatchStatus: row.dispatch_status,
      },

      salesOrder: {
        salesOrderId: row.sales_order_id,

        salesOrderNumber: row.sales_order_number,
      },

      customer: {
        customerName: row.customer_name,

        customerCode: row.customer_code,
      },

      shipping: {
        address: row.shipping_address,

        city: row.shipping_city,

        state: row.shipping_state,

        pincode: row.shipping_pincode,
      },

      transport: {
        transportMode: row.transport_mode,

        transportAgencyId: row.transport_agency_id,

        transportAgencyName: row.transport_agency_name,
      },

      vehicle: {
        vehicleType: row.vehicle_type,

        vehicleNumber: row.vehicle_number,

        vehicleCapacity: row.vehicle_capacity,
      },

      driver: {
        driverName: row.driver_name,

        driverContact: row.driver_contact_number,

        driverLicense: row.driver_license_number,
      },

      dispatch: {
        freightAmount: row.freight_amount,

        lrNumber: row.lr_number,

        sealNumber: row.seal_number,

        dispatchDate: row.dispatch_datetime,

        expectedDeliveryDate: row.expected_delivery_datetime,
      },

      product: {
        productId: row.product_id,

        productName: row.product_name,

        allocatedQty: row.allocated_qty,

        dispatchQty: row.dispatch_qty,

        batchNumber: row.batch_number,

        uom: row.uom,
      },

      safety: {
        density: row.density,

        dispatchTemperature: row.dispatch_temperature,

        hazardClass: row.hazard_class,

        ppeRequired: row.ppe_required,

        emergencyContactPerson: row.emergency_contact_person,

        emergencyContact: row.emergency_contact,

        fireSafetyEquipment: row.fire_safety_equipment,

        spillKitAvailable: row.spill_kit_available,
      },
    };

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

      message: "Dispatch weighbridge details fetched successfully",

      data: responseData,
    });
  } catch (error) {
    console.error("GET DISPATCH WEIGHBRIDGE DETAILS ERROR:", error);

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

      message: "Failed to fetch dispatch weighbridge details",

      error: error.message,
    });
  }
};

const getMaterialGroupBySO = async (req, res) => {
  try {
    const orgId = req.session.user.orgId;

    const { soNumber } = req.params;

    const query = `

SELECT

    ig.id AS group_id,

    ig.group_name,

    p.item_name,

    p.id AS product_id,

    so.so_number,

    soi.qty,

    soi.uom_symbol

FROM sales_orders so

LEFT JOIN sales_order_items soi
ON soi.sales_order_id = so.id

LEFT JOIN procurement_itemmaster p
ON p.id = soi.product_id

LEFT JOIN group_master ig
ON ig.id = p.group_id

WHERE so.so_number = $1
AND so.org_id = $2

LIMIT 1

`;

    const result = await pool.query(query, [soNumber, orgId]);

    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "No material group found",
      });
    }

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

      data: result.rows[0],
    });
  } catch (error) {
    console.log("GET MATERIAL GROUP ERROR:", error);

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

      message: "Internal server error",

      error: error.message,
    });
  }
};

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

//   const client = await pool.connect();

//   try {

//     const { id } = req.params;

//     const {
//       inWeight,
//       remarks,
//     } = req.body;

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

//     // =========================
//     // CHECK VEHICLE
//     // =========================

//     const existingVehicle =
//       await client.query(
//         `
//         SELECT
//           id,
//           shipment_id,
//           source_type,
//           gate_status
//         FROM commercial_vehicles
//         WHERE id = $1
//         `,
//         [id]
//       );

//     if (
//       existingVehicle.rows.length === 0
//     ) {

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

//       return res.status(404).json({

//         success: false,

//         message:
//           "Vehicle not found",

//       });

//     }

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

//     // =========================
//     // VALIDATE SOURCE
//     // =========================

//     if (
//       vehicle.source_type !==
//       "SHIPMENT"
//     ) {

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

//       return res.status(400).json({

//         success: false,

//         message:
//           "Invalid dispatch vehicle",

//       });

//     }

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

//     const vehicleUpdate =
//       await client.query(
//         `
//         UPDATE commercial_vehicles
//         SET

//           in_weight = $1,

//           weighbridge_status =
//           'COMPLETED',

//           weighment_completed_at =
//           NOW(),

//           security_remarks = $2,

//           updated_at = NOW()

//         WHERE id = $3

//         RETURNING *
//         `,
//         [
//           inWeight || null,
//           remarks || null,
//           id,
//         ]
//       );

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

//     const dispatchQty = Number(
//   vehicleUpdate.rows[0]?.dispatch_qty || 0
// );

// const uom =
//   vehicleUpdate.rows[0]?.uom || "KG";

// let netWeight = Number(inWeight || 0);

// if (uom === "T" || uom === "KL") {
//   netWeight = Number(inWeight || 0) / 1000;
// }

//     if (vehicle.shipment_id) {

//       await client.query(
//         `
//       UPDATE shipment_dispatches
// SET

//   shipment_status = 'Weighed',

//   in_weight = $1,

//   net_weight = $2,

//   weight_uom = $3,

//   weighment_completed_at = NOW(),

//   updated_at = NOW()

// WHERE id = $4
//         `,
//         [vehicle.shipment_id]
//       );

//     }

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

//     return res.status(200).json({

//       success: true,

//       message:
//         "Dispatch weighment completed successfully",

//       data:
//         vehicleUpdate.rows[0],

//     });

//   } catch (error) {

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

//     console.error(
//       "DISPATCH WEIGHMENT ERROR:",
//       error
//     );

//     return res.status(500).json({

//       success: false,

//       message:
//         "Failed to complete dispatch weighment",

//       error: error.message,

//     });

//   } finally {

//     client.release();

//   }

// };

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

//     const orgId = req.session.user?.orgId;

//     // const result = await pool.query(
//     //   `
//     //   SELECT

//     //     -- PRIMARY
//     //     sd.id,
//     //     sd.org_id,

//     //     -- SHIPMENT
//     //     sd.shipment_number,
//     //     sd.shipment_status,
//     //     sd.dispatch_number,

//     //     -- SALES ORDER
//     //     sd.sales_order_id,
//     //     sd.sales_order_number,
//     //     sd.so_number,

//     //     -- CUSTOMER
//     //     sd.customer_name,
//     //     sd.customer_code,

//     //     -- SHIPPING
//     //     sd.shipping_address,
//     //     sd.shipping_city,
//     //     sd.shipping_state,
//     //     sd.shipping_pincode,

//     //     -- TRANSPORT
//     //     sd.transport_mode,
//     //     sd.transport_agency_id,
//     //     sd.transport_agency_name,

//     //     -- VEHICLE
//     //     sd.vehicle_type,
//     //     sd.vehicle_number,
//     //     sd.vehicle_capacity,

//     //     -- DRIVER
//     //     sd.driver_name,
//     //     sd.driver_contact_number,
//     //     sd.driver_license_number,

//     //     -- DISPATCH
//     //     sd.freight_amount,
//     //     sd.lr_number,
//     //     sd.lr_no,
//     //     sd.lr_date,
//     //     sd.seal_number,
//     //     sd.dispatch_datetime,
//     //     sd.expected_delivery_datetime,

//     //     -- DOCUMENTS
//     //     sd.invoice_no,
//     //     sd.delivery_challan_no,
//     //     sd.gate_pass_no,
//     //     sd.gate_pass_date,

//     //     -- DESTINATION
//     //     sd.destination_name,

//     //     -- PRODUCT
//     //     sdi.product_id,
//     //     sdi.product_name,
//     //     sdi.allocated_qty,
//     //     sdi.dispatch_qty,
//     //     sdi.batch_number,
//     //     sdi.uom_id,

//     //     -- WEIGHMENT
//     //     sd.in_weight,
//     //     sd.out_weight,
//     //     sd.net_weight,
//     //     sd.return_weight_done,
//     //     sd.exit_time,

//     //     -- EXTRA
//     //     sd.delivery_qty,
//     //     sd.no_of_bags,
//     //     sd.remarks,
//     //     sd.created_at,

//     //     -- USER
//     //     u.first_name || ' ' || u.last_name AS created_by

//     //   FROM shipment_dispatches sd

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

//     //   LEFT JOIN users u
//     //     ON u.id = sd.created_by

//     //   WHERE sd.id = $1
//     //   AND sd.org_id = $2
//     //   AND sd.is_deleted = false

//     //   LIMIT 1
//     //   `,
//     //   [id, orgId]
//     // );

// const result = await pool.query(
//   `
//   SELECT

//     -- PRIMARY
//     sd.id,
//     sd.org_id,

//     -- SHIPMENT
//     sd.shipment_number,
//     sd.shipment_status,

//     -- SALES ORDER
//     sd.sales_order_id,
//     sd.sales_order_number,

//     -- CUSTOMER
//     sd.customer_name,
//     sd.customer_code,

//     -- SHIPPING
//     sd.shipping_address,
//     sd.shipping_city,
//     sd.shipping_state,
//     sd.shipping_pincode,

//     -- TRANSPORT
//     sd.transport_mode,
//     sd.transport_agency_id,
//     sd.transport_agency_name,

//     -- VEHICLE
//     sd.vehicle_type,
//     sd.vehicle_number,
//     sd.vehicle_capacity,

//     -- DRIVER
//     sd.driver_name,
//     sd.driver_contact_number,
//     sd.driver_license_number,

//     -- DISPATCH
//     sd.freight_amount,
//     sd.lr_number,
//     sd.seal_number,
//     sd.dispatch_datetime,
//     sd.expected_delivery_datetime,

//     -- DOCUMENTS
//     sd.invoice_no,
//     sd.delivery_challan_no,
//     sd.gate_pass_no,
//     sd.gate_pass_date,

//     -- DESTINATION
//     sd.destination_name,

//     -- PRODUCT
//     sdi.product_id,
//     sdi.product_name,
//     sdi.allocated_qty,
//     sdi.dispatch_qty,
//     sdi.batch_number,
//     sdi.uom_id,

//     -- WEIGHMENT
//     sd.in_weight,
//     sd.out_weight,
//     sd.net_weight,
//     sd.return_weight_done,
//     sd.exit_time,

//     -- EXTRA
//     sd.delivery_qty,
//     sd.remarks,
//     sd.created_at,

//     -- USER
//     u.first_name || ' ' || u.last_name AS created_by

//   FROM shipment_dispatches sd

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

//   LEFT JOIN users u
//     ON u.id = sd.created_by

//   WHERE sd.id = $1
//   AND sd.org_id = $2
//   AND sd.is_deleted = false

//   LIMIT 1
//   `,
//   [id, orgId]
// );

//     if (result.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Dispatch not found",
//       });
//     }

//     return res.status(200).json({
//       success: true,
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.log("FETCH DISPATCH WEIGH ERROR", error);

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

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

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

//     const { inWeight, remarks } = req.body;

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

//     // =====================================
//     // CHECK VEHICLE + SHIPMENT + UOM
//     // =====================================

//     const existingVehicle = await client.query(
//       `
//         SELECT

//           cv.id,
//           cv.shipment_id,
//           cv.source_type,
//           cv.gate_status,

//           sdi.dispatch_qty,
//           sdi.product_name,

//           pim.uom

//         FROM commercial_vehicles cv

//         LEFT JOIN shipment_dispatch_items sdi
//           ON sdi.shipment_id = cv.shipment_id

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

//         WHERE cv.id = $1
//         `,
//       [id],
//     );

//     if (existingVehicle.rows.length === 0) {
//       await client.query("ROLLBACK");

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

//         message: "Vehicle not found",
//       });
//     }

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

//     // =====================================
//     // VALIDATE SOURCE
//     // =====================================

//     if (vehicle.source_type !== "SHIPMENT") {
//       await client.query("ROLLBACK");

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

//         message: "Invalid dispatch vehicle",
//       });
//     }

//     // =====================================
//     // RAW MACHINE WEIGHT (KG)
//     // =====================================

//     const rawWeight = Number(inWeight || 0);

//     // =====================================
//     // BUSINESS DISPLAY WEIGHT
//     // =====================================

//     const uom = vehicle.uom || "KG";

//     let displayWeight = rawWeight;

//     // KG -> Tons
//     if (uom === "T") {
//       displayWeight = rawWeight / 1000;
//     }

//     // KG -> KL
//     else if (uom === "KL") {
//       displayWeight = rawWeight / 1000;
//     }

//     // =====================================
//     // UPDATE COMMERCIAL VEHICLE
//     // STORE RAW MACHINE KG
//     // =====================================

//     const vehicleUpdate = await client.query(
//       `
//         UPDATE commercial_vehicles
//         SET

//           in_weight = $1,

//           weighbridge_status =
//           'COMPLETED',

//           weighment_completed_at =
//           NOW(),

//           security_remarks = $2,

//           updated_at = NOW()

//         WHERE id = $3

//         RETURNING *
//         `,
//       [rawWeight, remarks || null, id],
//     );

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

//     if (vehicle.shipment_id) {
//       await client.query(
//         `
//         UPDATE shipment_dispatches
//         SET

//           shipment_status =
//           'Weighed',

//           in_weight = $1,

//           net_weight = $2,

//           weight_uom = $3,

//           weighment_completed_at =
//           NOW(),

//           updated_at = NOW()

//         WHERE id = $4
//         `,
//         [rawWeight, displayWeight, uom, vehicle.shipment_id],
//       );
//     }

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

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

//       message: "Dispatch weighment completed successfully",

//       data: {
//         ...vehicleUpdate.rows[0],

//         net_weight: displayWeight,

//         weight_uom: uom,
//       },
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("DISPATCH WEIGHMENT ERROR:", error);

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

//       message: "Failed to complete dispatch weighment",

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

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

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

//     const { inWeight, remarks } = req.body;

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

//     // =====================================
//     // FETCH VEHICLE
//     // =====================================

//     const existingVehicle = await client.query(
//       `
//       SELECT

//         cv.id,
//         cv.shipment_id,
//         cv.source_type,
//         cv.gate_status,

//         cv.in_weight AS tare_weight_ton,

//         sdi.dispatch_qty,
//         sdi.product_name,

//         pim.uom

//       FROM commercial_vehicles cv

//       LEFT JOIN shipment_dispatch_items sdi
//         ON sdi.shipment_id = cv.shipment_id

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

//       WHERE cv.id = $1
//       `,
//       [id],
//     );

//     if (existingVehicle.rows.length === 0) {
//       await client.query("ROLLBACK");

//       return res.status(404).json({
//         success: false,
//         message: "Vehicle not found",
//       });
//     }

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

//     // =====================================
//     // VALIDATE
//     // =====================================

//     if (vehicle.source_type !== "SHIPMENT") {
//       await client.query("ROLLBACK");

//       return res.status(400).json({
//         success: false,
//         message: "Invalid dispatch vehicle",
//       });
//     }

//     // =====================================
//     // USER ENTERS TON DIRECTLY
//     // =====================================

//     const grossWeightTon = Number(inWeight || 0);

//     if (!grossWeightTon || grossWeightTon <= 0) {
//       await client.query("ROLLBACK");

//       return res.status(400).json({
//         success: false,
//         message: "Invalid weight",
//       });
//     }

//     // =====================================
//     // TON → KG
//     // =====================================

//     const grossWeightKg = Number((grossWeightTon * 1000).toFixed(3));
//     // =====================================
//     // TARE WEIGHT
//     // =====================================

//     const tareWeightTon = Number(vehicle.tare_weight_ton || 0);

//     // =====================================
//     // NET WEIGHT
//     // =====================================

//     const netWeightTon = Number((grossWeightTon - tareWeightTon).toFixed(3));

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

//     const vehicleUpdate = await client.query(
//       `
//       UPDATE commercial_vehicles
//       SET

//         in_weight = $1,

//         in_weight_kgs = $2,

//         net_weight_ton = $3,

//         weighbridge_status = 'COMPLETED',

//         weighment_completed_at = NOW(),

//         security_remarks = $4,

//         updated_at = NOW()

//       WHERE id = $5

//       RETURNING *
//       `,
//       [grossWeightTon, grossWeightKg, netWeightTon, remarks || null, id],
//     );

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

//     if (vehicle.shipment_id) {
//       await client.query(
//         `
//         UPDATE shipment_dispatches
//         SET

//           shipment_status = 'Dispatched',
//           in_weight = $1,

//           in_weight_kgs = $2,

//           gross_weight_ton = $3,

//           tare_weight_ton = $4,

//           net_weight_ton = $5,

//           weighment_completed_at = NOW(),

//           updated_at = NOW()

//         WHERE id = $6
//         `,
//         [
//           grossWeightTon,
//           grossWeightKg,
//           grossWeightTon,
//           tareWeightTon,
//           netWeightTon,
//           vehicle.shipment_id,
//         ],
//       );
//     }

//     // shipment_status = 'Weighed',
//     await client.query("COMMIT");

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

//       message: "Dispatch weighment completed successfully",

//       data: {
//         ...vehicleUpdate.rows[0],

//         gross_weight_ton: grossWeightTon,

//         gross_weight_kgs: grossWeightKg,

//         tare_weight_ton: tareWeightTon,

//         net_weight_ton: netWeightTon,
//       },
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("DISPATCH WEIGHMENT ERROR:", error);

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

  try {
    const { id } = req.params;
    const { inWeight, remarks } = req.body;

    await client.query("BEGIN");

    // =====================================
    // FETCH VEHICLE WITH ALL REFERENCES
    // =====================================
    const existingVehicle = await client.query(
      `
      SELECT
        cv.id,
        cv.shipment_id,
        cv.source_type,
        cv.gate_status,
        cv.sales_order_number,
        cv.dispatch_allocation_id,
        cv.in_weight AS tare_weight_ton,
        sdi.dispatch_qty,
        sdi.product_name,
        pim.uom
      FROM commercial_vehicles cv
      LEFT JOIN shipment_dispatch_items sdi
        ON sdi.shipment_id = cv.shipment_id
      LEFT JOIN procurement_itemmaster pim
        ON pim.id = sdi.product_id
      WHERE cv.id = $1
      `,
      [id],
    );

    if (existingVehicle.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        success: false,
        message: "Vehicle not found",
      });
    }

    const vehicle = existingVehicle.rows[0];

    // =====================================
    // VALIDATE - Accept ALLOCATION source_type
    // =====================================
    if (
      vehicle.source_type !== "SHIPMENT" &&
      vehicle.source_type !== "ALLOCATION"
    ) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Invalid dispatch vehicle. Current source_type: '${vehicle.source_type}'. Expected: 'SHIPMENT' or 'ALLOCATION'`,
      });
    }

    // =====================================
    // USER ENTERS TON DIRECTLY - Support 4 decimals
    // =====================================
    const grossWeightTon = Number(parseFloat(inWeight).toFixed(4));

    if (!grossWeightTon || grossWeightTon <= 0) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: "Invalid weight",
      });
    }

    // =====================================
    // TON → KG (with 4 decimal precision)
    // =====================================
    const grossWeightKg = Number((grossWeightTon * 1000).toFixed(4));

    // =====================================
    // TARE WEIGHT
    // =====================================
    const tareWeightTon = Number(
      parseFloat(vehicle.tare_weight_ton || 0).toFixed(4),
    );

    // =====================================
    // NET WEIGHT (with 4 decimals)
    // =====================================
    const netWeightTon = Number((grossWeightTon - tareWeightTon).toFixed(4));

    // =====================================
    // UPDATE COMMERCIAL VEHICLES TABLE
    // =====================================
    const vehicleUpdate = await client.query(
      `
      UPDATE commercial_vehicles
      SET
        in_weight = $1,
        in_weight_kgs = $2,
        net_weight_ton = $3,
        weighbridge_status = 'IN_PROGRESS',
        weighment_completed_at = NOW(),
        security_remarks = $4,
        updated_at = NOW()
      WHERE id = $5
      RETURNING *
      `,
      [grossWeightTon, grossWeightKg, netWeightTon, remarks || null, id],
    );

    // =====================================
    // UPDATE DISPATCH ALLOCATION TABLE using dispatch_allocation_id
    // =====================================
    if (vehicle.dispatch_allocation_id) {
      const allocationData = await client.query(
        `
        SELECT 
          allocated_qty,
          dispatched_qty,
          total_dispatched_qty,
          remaining_shipment_qty,
          sales_order_number
        FROM dispatch_allocations
        WHERE id = $1 AND is_deleted = false
        `,
        [vehicle.dispatch_allocation_id],
      );

      if (allocationData.rows.length > 0) {
        const allocation = allocationData.rows[0];
        const dispatchQty = Number(vehicle.dispatch_qty || 0);
        const newDispatchedQty = Number(
          (Number(allocation.dispatched_qty || 0) + dispatchQty).toFixed(4),
        );
        const newTotalDispatchedQty = Number(
          (Number(allocation.total_dispatched_qty || 0) + dispatchQty).toFixed(
            4,
          ),
        );
        const newRemainingQty = Number(
          (
            Number(allocation.remaining_shipment_qty || 0) - dispatchQty
          ).toFixed(4),
        );

        const isShipmentCompleted = newRemainingQty <= 0;

        await client.query(
          `
          UPDATE dispatch_allocations
          SET
            dispatched_qty = $1,
            total_dispatched_qty = $2,
            remaining_shipment_qty = $3,
            shipment_completed = $4,
            allocation_status = CASE 
              WHEN $4 = true THEN 'COMPLETED'
              ELSE allocation_status
            END,
            updated_at = NOW()
          WHERE id = $5
          `,
          [
            newDispatchedQty,
            newTotalDispatchedQty,
            Math.max(0, newRemainingQty),
            isShipmentCompleted,
            vehicle.dispatch_allocation_id,
          ],
        );
      }
    }

    // =====================================
    // UPDATE SALES ORDER
    // =====================================
    if (vehicle.sales_order_number) {
      const salesOrderData = await client.query(
        `
        SELECT 
          remaining_qty,
          total_quantity
        FROM sales_orders
        WHERE so_number = $1
        `,
        [vehicle.sales_order_number],
      );

      if (salesOrderData.rows.length > 0) {
        const salesOrder = salesOrderData.rows[0];
        const dispatchQty = Number(vehicle.dispatch_qty || 0);
        const newRemainingQty = Number(
          Math.max(
            0,
            Number(salesOrder.remaining_qty || 0) - dispatchQty,
          ).toFixed(4),
        );
        const isFullyInvoiced = newRemainingQty <= 0;

        await client.query(
          `
          UPDATE sales_orders
          SET
            remaining_qty = $1,
            fully_invoiced = $2,
            total_invoiced_qty = ROUND((total_quantity - $1)::numeric, 4),
            updated_at = NOW()
          WHERE so_number = $3
          `,
          [newRemainingQty, isFullyInvoiced, vehicle.sales_order_number],
        );
      }
    }

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Dispatch weighment completed successfully",
      data: {
        ...vehicleUpdate.rows[0],
        gross_weight_ton: Number(grossWeightTon.toFixed(4)),
        gross_weight_kgs: Number(grossWeightKg.toFixed(4)),
        tare_weight_ton: Number(tareWeightTon.toFixed(4)),
        net_weight_ton: Number(netWeightTon.toFixed(4)),
        sales_order_number: vehicle.sales_order_number,
        dispatch_allocation_id: vehicle.dispatch_allocation_id,
      },
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("DISPATCH WEIGHMENT ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to complete dispatch weighment",
      error: error.message,
    });
  } finally {
    client.release();
  }
};

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

    const orgId = req.session.user?.orgId;

    console.log("COMMERCIAL VEHICLE ID:", id);
    console.log("ORG ID:", orgId);

    const result = await pool.query(
      `
      SELECT

        -- COMMERCIAL VEHICLE
        cv.id AS commercial_vehicle_id,
        cv.ticket_no,
        cv.vehicle_number AS cv_vehicle_number,
        cv.driver_name AS cv_driver_name,
        cv.driver_number,
        cv.movement_type,
        cv.in_weight,
        cv.out_weight,
        cv.weighbridge_status,
        cv.qc_status,
        cv.gate_status,
        cv.return_weight_done,
        cv.time_in,

        -- SHIPMENT
        sd.id AS shipment_dispatch_id,
        sd.shipment_number,
        sd.shipment_status,

        -- SALES ORDER
        sd.sales_order_id,
        sd.sales_order_number,

        -- CUSTOMER
        sd.customer_name,
        sd.customer_code,

        -- VEHICLE
        sd.vehicle_type,
        sd.vehicle_number,
        sd.vehicle_capacity,

        -- DRIVER
        sd.driver_name,
        sd.driver_contact_number,

        -- TRANSPORT
        sd.transport_agency_id,
        sd.transport_agency_name,
        sd.transport_mode,

        -- DELIVERY
        sd.lr_number,
        sd.eway_bill_number,
        sd.seal_number,
        sd.dispatch_datetime,
        sd.expected_delivery_datetime,

        -- ADDRESS
        sd.shipping_address,
        sd.shipping_city,
        sd.shipping_state,

        -- PRODUCT
        sdi.product_id,
        sdi.product_name,
        sdi.dispatch_qty,
        sdi.batch_number,
        sdi.allocated_qty,
        pim.uom,

        -- WEIGHMENT
        sd.in_weight AS dispatch_in_weight,
        sd.out_weight AS dispatch_out_weight,
        sd.net_weight,
        sd.exit_time,

        -- GATE PASS
        sd.gate_pass_no,
        sd.gate_pass_date,

        -- EXTRA
        sd.remarks,
        sd.created_at,

        -- CREATED USER
        u.first_name || ' ' || u.last_name AS created_by

      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

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

      WHERE cv.id = $1
      AND cv.org_id = $2
      AND cv.is_deleted = false

      LIMIT 1
      `,
      [id, orgId],
    );

    if (result.rowCount === 0) {
      return res.status(404).json({
        success: false,
        message: "Return dispatch vehicle not found",
      });
    }

    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (error) {
    console.log("GET RETURN DISPATCH WEIGHBRIDGE DETAILS ERROR:", error);

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

// const fs = require("fs");
// const path = require("path");
// const generateDispatchInvoicePDF = require("../../services/pdf/generateDispatchInvoicePDF");
const generateDispatchWeighmentPDF = require("../../services/pdf/generateDispatchWeighmentPDF");
// const generateDeliveryChallanPDF = require("../../services/pdf/generateDeliveryChallanPDF");
// const generateEWayPDF = require("../../services/pdf/generateEWayPDF");
// const generateEInvoicePDF = require("../../services/pdf/generateEInvoicePDF");
const generateGatePassPDF = require("../../services/pdf/generateGatePassPDF");
const mergePDFBuffers = require("../../services/pdf/mergePDFBuffers");

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

  try {
    await client.query("BEGIN");

    // =========================================
    // PARAMS
    // =========================================

    const { id } = req.params;
    const vehicleId = req.params.id;
    console.log(id, "ftgyhjklghjkl");

     const { 
      outWeight, 
      remarks,
      lr_number,
      seal_number,
      expected_delivery_date,
      hazard_classification,
      ppe_requirements,
      emergency_contact_person,
      emergency_contact_number,
      safety_instructions_read,
      spill_kit_available,
      transporter_name
    } = req.body;

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

    // =========================================
    // FETCH VEHICLE
    // =========================================

    const vehicleResult = await client.query(
      `
    SELECT
      id,
      shipment_id,
      movement_type,
       driver_number,
  time_in,
  exit_time
    FROM commercial_vehicles
    WHERE id = $1
    FOR UPDATE
    `,
      [vehicleId],
    );

    if (vehicleResult.rowCount === 0) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        success: false,
        message: "Vehicle not found",
      });
    }

    const vehicle = vehicleResult.rows[0];

    // if (!vehicle.shipment_id) {
    //   await client.query("ROLLBACK");

    //   return res.status(400).json({
    //     success: false,
    //     message: "Dispatch not mapped to vehicle",
    //   });
    // }

    // const dispatchId = vehicle.shipment_id;
    // =========================================
    // LOCK DISPATCH
    // =========================================

    // const dispatchResult =
    //   await client.query(
    //     `
    //     SELECT *
    //     FROM shipment_dispatches
    //     WHERE id = $1
    //     FOR UPDATE
    //     `,
    //     [id],
    //   );
// First, lock the vehicle row
const lockVehicle = await client.query(
  `
  SELECT id FROM commercial_vehicles
  WHERE id = $1
  FOR UPDATE
  `,
  [vehicleId],
);

// Then fetch the data without FOR UPDATE
const dispatchResult = await client.query(
  `
  SELECT 
    cv.*,
    soi.uom_symbol as uom
  FROM commercial_vehicles cv
  LEFT JOIN dispatch_allocations da ON da.id = cv.allocation_id
  LEFT JOIN sales_order_items soi ON soi.sales_order_id = da.sales_order_id 
    AND soi.product_id = da.product_id
  WHERE cv.id = $1
  `,
  [vehicleId],
);

    // if (dispatchResult.rowCount === 0) {
    //   await client.query("ROLLBACK");

    //   return res.status(404).json({
    //     success: false,
    //     message: "Dispatch not found",
    //   });
    // }

    const dispatch = dispatchResult.rows[0];
    console.log(dispatch, "ftyuiop");

    // =========================================
    // VALIDATION
    // =========================================

    // if (dispatch.return_weight_done) {
    //   await client.query("ROLLBACK");

    //   return res.status(400).json({
    //     success: false,
    //     message: "Return weight already completed",
    //   });
    // }

    // =========================================
    // WEIGHT CALCULATION
    // =========================================
    const movementType = (vehicle.movement_type || "").trim().toLowerCase();

    const inWeight = Number(vehicle.in_weight || 0);

    // const outWeightValue = Number(outWeight || 0);
    const rawOutWeight = Number(outWeight || 0);

    const uom = (dispatch.uom || "").trim().toUpperCase();

    let outWeightValue = 0;

    // ===============================
    // TON
    // ===============================

    if (uom === "T") {
      outWeightValue = rawOutWeight;
    }

    // ===============================
    // KG → TON
    // ===============================
    else if (uom === "KG") {
      // outWeightValue = Number((rawOutWeight / 1000).toFixed(3));
      outWeightValue = rawOutWeight;
    }

    // ===============================
    // KL → TON
    // ===============================
    else if (uom === "KL") {
      // outWeightValue = Number((rawOutWeight / 1000).toFixed(3));
      outWeightValue = rawOutWeight;
    }

    // ===============================
    // DEFAULT
    // ===============================
    else {
      outWeightValue = rawOutWeight;
    }

    let grossWeight = 0;
    let tareWeight = 0;
    let netWeight = 0;

    // =====================================
    // LOADING
    // =====================================

    if (movementType === "loading") {
      // vehicle entered empty
      // vehicle exits loaded

      tareWeight = inWeight; // 2

      grossWeight = outWeightValue; // 32

      netWeight = grossWeight - tareWeight;
      // netWeight = 40;
    }

    // =====================================
    // UNLOADING
    // =====================================
    else {
      // vehicle entered loaded
      // vehicle exits empty

      grossWeight = inWeight;

      tareWeight = outWeightValue;

      netWeight = grossWeight - tareWeight;
    }

    // =====================================
    // VALIDATION
    // =====================================

    if (netWeight <= 0) {
      await client.query("ROLLBACK");

      return res.status(400).json({
        success: false,
        message: "Invalid net weight calculated",
      });
    }
    // =========================================
    // UPDATE DISPATCH
    // =========================================

    // await client.query(
    //   `
    //   UPDATE shipment_dispatches
    //   SET
    //     out_weight = $1,
    //     net_weight = $2,
    //     remarks = $3,
    //     return_weight_done = true,
    //     exit_time = NOW(),
    //     shipment_status = 'Completed',
    //     updated_by = $4,
    //     updated_at = NOW()
    //   WHERE id = $5
    //   `,
    //   [tareWeight, netWeight, remarks || null, userId],
    // );

    await client.query(
      `
      UPDATE commercial_vehicles
      SET
        out_weight = $1,
        return_dispatch_done = true,
        transporter_name = COALESCE($2, transporter_name),
        lr_no = COALESCE($3, lr_no),
        seal_number = COALESCE($4, seal_number),
        expected_delivery_date = COALESCE($5, expected_delivery_date),
        hazard_classification = COALESCE($6, hazard_classification),
        ppe_requirements = COALESCE($7, ppe_requirements),
        emergency_contact_person = COALESCE($8, emergency_contact_person),
        emergency_contact_number = COALESCE($9, emergency_contact_number),
        safety_instructions_read = COALESCE($10, safety_instructions_read),
        spill_kit_available = COALESCE($11, spill_kit_available),
        updated_at = NOW()
      WHERE id = $12
      `,
      [
        outWeightValue,
        transporter_name || null,
        lr_number || null,
        seal_number || null,
        expected_delivery_date || null,
        hazard_classification ? JSON.stringify(hazard_classification) : null,
        ppe_requirements ? JSON.stringify(ppe_requirements) : null,
        emergency_contact_person || null,
        emergency_contact_number || null,
        safety_instructions_read || false,
        spill_kit_available || false,
        vehicleId
      ],
    );
    const allocationResult = await client.query(
      `
  SELECT
    id,
    required_qty,
    delivered_qty
  FROM dispatch_allocations
  WHERE id = $1
  FOR UPDATE
  `,
      [dispatch.allocation_id],
    );

    if (allocationResult.rowCount === 0) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        success: false,
        message: "Dispatch allocation not found",
      });
    }

    const allocation = allocationResult.rows[0];

    // =========================================
    // CALCULATIONS
    // =========================================

    const existingDelivered = Number(allocation.delivered_qty || 0);

    const dispatchQty = Number(netWeight || 0);

    const requiredQty = Number(allocation.required_qty || 0);

    const newDeliveredQty = existingDelivered + dispatchQty;

    const newBalanceQty = Math.max(requiredQty - newDeliveredQty, 0);

    // =========================================
    // DELIVERY STATUS
    // =========================================

    let deliveryStatus = "Completed";

    if (newBalanceQty > 0) {
      deliveryStatus = "Partial";
    }

    // =========================================
    // UPDATE ALLOCATION
    // =========================================

    const shouldRequeue = newBalanceQty > 0;

    await client.query(
      `
  UPDATE dispatch_allocations
  SET
    delivered_qty = $1,

    balance_qty = $2,

    delivery_status = $3,

    vehicle_assigned = $4,

    shipment_completed = $5,

    updated_at = NOW()

  WHERE id = $6
  `,
      [
        newDeliveredQty,
        newBalanceQty,
        deliveryStatus,

        shouldRequeue ? false : allocation.vehicle_assigned,

        shouldRequeue ? false : allocation.shipment_completed,

        dispatch.allocation_id,
      ],
    );

    // =========================================
    // SALES ORDER STATUS UPDATE
    // =========================================

    //   let soStatus = "Partial";

    //   if (newBalanceQty <= 0) {
    //     soStatus = "Closed";
    //   }

    //   await client.query(
    //     `
    // UPDATE sales_orders
    // SET
    //   status = $1,
    //   updated_at = NOW()
    // WHERE id = $2
    // `,
    //     [soStatus, dispatch.sales_order_id],
    //   );

    // =========================================
    // FETCH COMPLETE DATA
    // =========================================

//     const detailsResult = await client.query(
//       `
//         SELECT

//     sd.*,

//     -- ORGANIZATION
//     org.org_name,
//     org.address AS org_address,
//     org.gst_number AS org_gstin,

//     org.phone AS org_phone,
//     org.email AS org_email,
//     org.cin_number AS org_cin,

//     -- CUSTOMER

// c.company_name AS customer_name,
// c.gst AS customer_gst,

// c.shipping_address AS customer_address,
// c.ship_city AS customer_city,
// c.ship_state AS customer_state,
// c.ship_pin_code AS customer_pincode,

// c.phone AS customer_phone,
// c.email AS customer_email,


//     -- SALES ORDER
//     so.so_number AS sales_order_no,
//     so.total_amount,

//     -- SALES ORDER ITEMS
//     soi.rate,
//     soi.amount,
//     soi.qty AS so_qty,
//     soi.uom_symbol,
//     soi.description,

//     -- PRODUCT
//    pim.item_name AS product_name,

//     -- USER
//     u.first_name || ' ' || u.last_name
//       AS prepared_by,

//     -- TRANSPORT
//     ta.agency_name AS transport_agency_name

// FROM shipment_dispatches sd

// LEFT JOIN sales_orders so
//   ON so.id = sd.sales_order_id

// LEFT JOIN sales_order_items soi
//   ON soi.sales_order_id = so.id

// LEFT JOIN customers c
//   ON c.customer_code = so.customer_id

// LEFT JOIN organizations org
//   ON org.id = sd.org_id

// LEFT JOIN procurement_itemmaster pim
//   ON pim.id = soi.product_id

// LEFT JOIN users u
//   ON u.id = sd.created_by

// LEFT JOIN transport_agencies ta
//   ON ta.id = sd.transport_agency_id

// WHERE sd.id = $1
//         `,
//       [dispatchId],
//     );
const detailsResult = await client.query(
  `
    SELECT
      cv.*,
      -- ORGANIZATION
      org.org_name,
      org.address AS org_address,
      org.gst_number AS org_gstin,
      org.phone AS org_phone,
      org.email AS org_email,
      org.cin_number AS org_cin,
      
      -- CUSTOMER
      c.company_name AS customer_name,
      c.gst AS customer_gst,
      c.shipping_address AS customer_address,
      c.ship_city AS customer_city,
      c.ship_state AS customer_state,
      c.ship_pin_code AS customer_pincode,
      c.phone AS customer_phone,
      c.email AS customer_email,
      
      -- SALES ORDER
      so.so_number AS sales_order_no,
      so.total_amount,
      
      -- SALES ORDER ITEMS
      soi.rate,
      soi.amount,
      soi.qty AS so_qty,
      soi.uom_symbol,
      soi.description,
      
      -- PRODUCT
      pim.item_name AS product_name,
      
      -- USER
      u.first_name || ' ' || u.last_name AS prepared_by,
      
      -- TRANSPORT
      ta.agency_name AS transport_agency_name,
      
      -- ALLOCATION DETAILS
      da.required_qty,
      da.allocated_qty,
      da.delivered_qty,
      da.balance_qty,
      da.allocation_status
      
  FROM commercial_vehicles cv
  
  LEFT JOIN dispatch_allocations da 
    ON da.id = cv.allocation_id
    
  LEFT JOIN sales_orders so 
    ON so.id = da.sales_order_id
    
  LEFT JOIN sales_order_items soi 
    ON soi.sales_order_id = so.id 
    AND soi.product_id = da.product_id
    
  LEFT JOIN customers c 
    ON c.customer_code = so.customer_id
    
  LEFT JOIN organizations org 
    ON org.id = cv.org_id
    
  LEFT JOIN procurement_itemmaster pim 
    ON pim.id = da.product_id
    
  LEFT JOIN users u 
    ON u.id = cv.created_by
    
  LEFT JOIN transport_agencies ta 
    ON ta.id = cv.transport_agency_id
    
  WHERE cv.id = $1
  `,
  [vehicleId],
);

    const dispatchData = detailsResult.rows[0];

    // =========================================
    // ORGANIZATION DETAILS
    // =========================================

    const orgResult = await client.query(
      `
  SELECT
    id,
    org_name,
    address,
    gst_number,
    cin_number,
    pan_number,
    phone,
    email,
    website,
    state,
    country,
    pincode,
    logo
  FROM organizations
  WHERE id = $1
  LIMIT 1
  `,
      [dispatch.org_id],
    );

    const orgData = orgResult.rows[0] || {};
    console.log(orgData);

    // =========================================
    // PDF DATA
    // =========================================

    const pdfData = {
      ...dispatchData,

      // COMPANY

      // company_name: "Vivek Bio Products Pvt Ltd",

      // company_address: "Hyderabad Telangana",

      // company_phone: "9876543210",

      // company_email: "sales@vivekbio.com",

      // company_gstin: "36AAICV8996R1ZU",

      // company_cin: "U24100TG2022PTC165236",
      // COMPANY
      // MOVEMENT
      movement_type: vehicle.movement_type || "-",

      // DRIVER
      driver_contact: vehicle.driver_number || "-",

      // IN / OUT TIME
      in_time: vehicle.time_in || null,
      out_time: vehicle.out_time || null,

      company_name: orgData.org_name || "-",

      company_address: orgData.address || "-",

      company_phone: orgData.phone || "-",

      company_email: orgData.email || "-",

      company_gstin: orgData.gst_number || "-",

      company_cin: orgData.cin_number || "-",

      company_pan: orgData.pan_number || "-",

      company_website: orgData.website || "-",

      company_state: orgData.state || "-",

      company_country: orgData.country || "-",

      company_pincode: orgData.pincode || "-",

      company_logo: orgData.logo_url || null,

      // customer
      customer_name: dispatchData.customer_name,

      customer_address: `${dispatchData.customer_address || ""}, 
   ${dispatchData.customer_city || ""}, 
   ${dispatchData.customer_state || ""} - 
   ${dispatchData.customer_pincode || ""}`,

      customer_gst: dispatchData.customer_gst,

      // WEIGHTS

      gross_weight: grossWeight,

      tare_weight: tareWeight,

      net_weight: netWeight,

      dispatch_qty: netWeight,

      // NUMBERS

      dispatch_no: dispatchData.shipment_number,

      shipment_no: dispatchData.shipment_number,

      challan_number: dispatchData.shipment_number,

      ticket_no: dispatchData.shipment_number,

      // VEHICLE

      vehicle_no: dispatchData.vehicle_number,

      // TRANSPORT

      transport_agency: dispatchData.transport_agency_name,

      // DATES

      dispatch_date: new Date(),

      challan_date: new Date(),

      order_date: dispatchData.created_at,

      // REMARKS

      remarks: remarks || dispatchData.remarks,

      // USER

      weighed_by: dispatchData.prepared_by,

      approved_by: dispatchData.prepared_by,

      security_name: dispatchData.prepared_by,

      weighbridge_operator: dispatchData.prepared_by,
    };

    // =========================================
    // ITEMS
    // =========================================

    // const items = [
    //   {
    //     product_name: dispatchData.product_name || "-",

    //     hsn: dispatchData.hsn_code || "-",

    //     uom: "KG",

    //     qty: netWeight,

    //     amount: netWeight,

    //     batch_no: dispatchData.batch_number || "-",

    //     gross_weight: grossWeight,

    //     tare_weight: tareWeight,

    //     net_weight: netWeight,
    //   },
    // ];
    const items = [
      {
        product_name: dispatchData.product_name || "-",

        hsn: dispatchData.hsn_code || "-",

        uom: dispatchData.uom_symbol || "KG",

        qty: dispatchData.so_qty || netWeight,

        rate: dispatchData.rate || 0,

        amount:
          dispatchData.amount ||
          Number(dispatchData.rate || 0) *
            Number(dispatchData.so_qty || netWeight),

        batch_no: dispatchData.batch_number || "-",

        gross_weight: grossWeight,

        tare_weight: tareWeight,

        net_weight: netWeight,
      },
    ];

    // =========================================
    // FETCH GATE PASS
    // =========================================

    const gatePassResult = await client.query(
      `
  SELECT
    gp.*,

    cv.vehicle_number,
    cv.vehicle_type,
    cv.driver_name,
    cv.gate_status,
    cv.sales_order_number,
    cv.customer_name,

    sd.shipment_number,
     ta.agency_name,

    u.name AS created_by_name

  FROM gate_passes gp

  LEFT JOIN commercial_vehicles cv
    ON cv.id = gp.commercial_vehicle_id

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

    LEFT JOIN transport_agencies ta
  ON ta.id = sd.transport_agency_id



  LEFT JOIN users u
    ON u.id = gp.generated_by

  WHERE gp.commercial_vehicle_id = $1
  LIMIT 1
  `,
      [vehicleId],
    );

    const gatePassData = gatePassResult.rows[0] || null;

    // =========================================
    // GENERATE PDFs
    // =========================================

    // const dispatchInvoicePdf = await generateDispatchInvoicePDF(
    //   pdfData,
    //   items,
    //   pdfData,
    // );

    const dispatchWeighmentPdf = await generateDispatchWeighmentPDF(pdfData);

    // const deliveryChallanPdf = await generateDeliveryChallanPDF(pdfData, items);

    // const eWayPdf = await generateEWayPDF();

    // const eInvoicePdf = await generateEInvoicePDF();

    let gatePassPdf = null;

    // if (gatePassData) {
    //   gatePassPdf = await generateGatePassPDF(gatePassData);
    // }

    const finalMergedPdf = await mergePDFBuffers([
      dispatchWeighmentPdf,
      // gatePassPdf,
    ]);

    // =========================================
    // SAVE PDF
    // =========================================

    const now = new Date();

    const year = now.getFullYear();

    const month = String(now.getMonth() + 1).padStart(2, "0");

    const pdfDir = path.join(
      process.cwd(),
      `uploads/docs/dispatch/${year}/${month}`,
    );

    if (!fs.existsSync(pdfDir)) {
      fs.mkdirSync(pdfDir, {
        recursive: true,
      });
    }

    const pdfFileName = `dispatch-${id}-${Date.now()}.pdf`;

    const pdfPath = path.join(pdfDir, pdfFileName);

    fs.writeFileSync(pdfPath, finalMergedPdf);

    const relativePdfPath = `uploads/docs/dispatch/${year}/${month}/${pdfFileName}`;

await client.query(
  `
  UPDATE commercial_vehicles
  SET
    dispatch_weighment_path = $1,
    weighbridge_status = 'COMPLETED',
    weighment_completed_at = NOW(),
    updated_at = NOW()
  WHERE id = $2
  `,
  [relativePdfPath, vehicleId]
);
    // =========================================
    // UPDATE DB
    // =========================================

    // await client.query(
    //   `
    //   UPDATE shipment_dispatches
    //   SET
    //     dispatch_documents_file = $1
    //   WHERE id = $2
    //   `,
    //   [relativePdfPath, dispatchId],
    // );

    // =========================================
    // COMMIT
    // =========================================

    await client.query("COMMIT");

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

      message: "Dispatch return weight completed successfully",

      data: {
        dispatch_id: id,

        gross_weight: grossWeight,

        tare_weight: tareWeight,

        net_weight: netWeight,

        pdf: relativePdfPath,
      },
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.log("DISPATCH RETURN WEIGHT ERROR", error);

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

      message: "Failed to complete dispatch return weight",

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


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

    const result = await pool.query(
      `
      SELECT dispatch_weighment_path
      FROM commercial_vehicles
      WHERE id = $1
      `,
      [id],
    );

    if (result.rowCount === 0) {
      return res.status(404).json({
        success: false,
        message: "Commercial vehicle not found",
      });
    }

    const filePath = result.rows[0]. dispatch_weighment_path;

    if (!filePath) {
      return res.status(404).json({
        success: false,
        message: "Dispatch PDF not available",
      });
    }

    const absolutePath = path.join(process.cwd(), filePath);

    if (!fs.existsSync(absolutePath)) {
      return res.status(404).json({
        success: false,
        message: "PDF file missing on server",
      });
    }

    return res.download(absolutePath);
  } catch (error) {
    console.log("DOWNLOAD DISPATCH PDF ERROR", error);

    return res.status(500).json({
      success: false,
      message: "Failed to download PDF",
      error: error.message,
    });
  }
};

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

//     const result = await pool.query(
//       `
//       SELECT dispatch_documents_file
//       FROM shipment_dispatches
//       WHERE id = $1
//       `,
//       [id],
//     );

//     if (result.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Dispatch not found",
//       });
//     }

//     const filePath = result.rows[0].dispatch_documents_file;

//     if (!filePath) {
//       return res.status(404).json({
//         success: false,
//         message: "Dispatch PDF not available",
//       });
//     }

//     const absolutePath = path.join(process.cwd(), filePath);

//     if (!fs.existsSync(absolutePath)) {
//       return res.status(404).json({
//         success: false,
//         message: "PDF file missing on server",
//       });
//     }

//     return res.download(absolutePath);
//   } catch (error) {
//     console.log("DOWNLOAD DISPATCH PDF ERROR", error);

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

const getShipmentBySalesOrderId = async (req, res) => {
  try {
    const { salesOrderId } = req.params;

    const result = await pool.query(
      `
      SELECT
        sd.id AS shipment_id,
        sd.shipment_number,
        sd.shipment_status
      FROM shipment_dispatches sd
      WHERE sd.sales_order_id = $1
      ORDER BY sd.id DESC
      LIMIT 1
      `,
      [salesOrderId],
    );

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

    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (error) {
    console.error(error);
    return res.status(500).json({
      success: false,
      message: "Server Error",
    });
  }
};

const getPOVendors = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized",
      });
    }

    const result = await pool.query(
      `
      SELECT DISTINCT
        vendor_id,
        vendor_name
      FROM purchase_orders
      WHERE org_id = $1
        AND is_deleted = false
        AND vendor_name IS NOT NULL
      ORDER BY vendor_name ASC
      `,
      [orgId],
    );

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET PO VENDORS ERROR:", err);

    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};
// by me 10-6-26

const getPOsByVendor = async (req, res) => {
  try {
    const { vendor_name } = req.query;
    const orgId = req.user?.orgId;

    if (!vendor_name) {
      return res.status(400).json({
        success: false,
        message: "Vendor name required",
      });
    }

    const result = await pool.query(
      `
  SELECT
    po.id,
    po.po_number,
    po.po_date,
    po.vendor_name,
    po.po_due_date,

    COALESCE(
      (
        SELECT json_agg(
          json_build_object(
            'item_name', poi.item_name,
            'item_code', poi.item_code,  -- ← Use item_code
            'quantity', poi.quantity,
            'uom', poi.uom,
            'group_id', im.group_id,
            'group_name', ig.group_name
          )
        )
        FROM purchase_order_items poi
        LEFT JOIN procurement_itemmaster im 
          ON im.item_code = poi.item_code  -- ← Join on item_code
        LEFT JOIN group_master ig 
          ON ig.id = im.group_id
        WHERE poi.po_id = po.id
      ),
      '[]'::json
    ) AS items

  FROM purchase_orders po
  WHERE po.vendor_name = $1
    AND po.org_id = $2
    AND po.is_deleted = false
  ORDER BY po.id DESC
  `,
      [vendor_name, orgId],
    );

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET POS BY VENDOR ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

// const getPOsByVendor = async (req, res) => {
//   try {
//     const { vendor_name } = req.query;

//     const orgId = req.user?.orgId;

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

//     const result = await pool.query(
//       `
//       SELECT
//         po.id,
//         po.po_number,
//         po.po_date,
//         po.vendor_name,
//         po.po_due_date,

//         COALESCE(
//           (
//             SELECT json_agg(
//               json_build_object(
//                 'item_name', poi.item_name,
//                 'quantity', poi.quantity,
//                 'uom', poi.uom
//               )
//             )
//             FROM purchase_order_items poi
//             WHERE poi.po_id = po.id
//           ),
//           '[]'::json
//         ) AS items

//       FROM purchase_orders po

//       WHERE po.vendor_name = $1
//         AND po.org_id = $2
//         AND po.is_deleted = false

//       ORDER BY po.id DESC
//       `,
//       [vendor_name, orgId]
//     );

//     res.json({
//       success: true,
//       data: result.rows,
//     });
//   } catch (err) {
//     console.error("GET POS BY VENDOR ERROR:", err);

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

// const getWeighbridgeCard = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;

//     const query = `

//       SELECT

//         cv.id,

//         -- VEHICLE
//         cv.vehicle_number AS "vehicleNo",

//         cv.vehicle_type AS "vehicleType",

//         cv.movement_type AS "movementType",
//         cv.reference_no AS "poNumber",

//         -- DRIVER
//         cv.driver_name AS "driverName",

//         cv.driver_number AS "driverPhone",

//         -- WAREHOUSE
//         cv.warehouse_id AS "warehouseId",

//         -- SOURCE FLOW
//         cv.source_type AS "sourceType",

//         -- SHIPMENT DETAILS
//         cv.shipment_id AS "shipmentId",

//         cv.shipment_number AS "shipmentNumber",

//         cv.sales_order_number AS "salesOrderNumber",

//         -- CUSTOMER
//         cv.customer_name AS "customerName",

//         -- STATUS
//         cv.weighbridge_status AS "weighbridgeStatus",

//         cv.gate_status AS "gateStatus",

//         cv.created_at AS "createdAt"

//       FROM commercial_vehicles cv

//       WHERE

//         cv.is_deleted = false

//         AND cv.weighbridge_status = 'PENDING'

//         AND cv.gate_status = 'Inside'

//         AND cv.org_id = $1

//       ORDER BY cv.created_at DESC

//       LIMIT 4

//     `;

//     const { rows } = await pool.query(query, [orgId]);

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

//       count: rows.length,

//       data: rows,
//     });
//   } catch (error) {
//     console.error("Cards API Error:", error);

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

//       message: "Failed to fetch card data",

//       error: error.message,
//     });
//   }
// };

// const getWeighbridgeCard = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;

//     const query = `
//       SELECT
//         cv.id,
//         -- VEHICLE
//         cv.vehicle_number AS "vehicleNo",
//         cv.vehicle_type AS "vehicleType",
//         cv.movement_type AS "movementType",
//         cv.reference_no AS "poNumber",
//         -- DRIVER
//         cv.driver_name AS "driverName",
//         cv.driver_number AS "driverPhone",
//         -- WAREHOUSE
//         cv.warehouse_id AS "warehouseId",
//         -- SOURCE FLOW
//         cv.source_type AS "sourceType",
//         -- SHIPMENT DETAILS
//         cv.shipment_id AS "shipmentId",
//         cv.shipment_number AS "shipmentNumber",
//         cv.sales_order_number AS "salesOrderNumber",
//         -- CUSTOMER
//         cv.customer_name AS "customerName",
//         -- STATUS
//         cv.weighbridge_status AS "weighbridgeStatus",
//         cv.gate_status AS "gateStatus",
//         cv.created_at AS "createdAt",

//         -- SALES ORDER DETAILS
//         so.total_quantity AS "totalOrderQty",
//         so.total_invoiced_qty AS "totalInvoicedQty",
//         so.remaining_qty AS "remainingQty",
//         so.fully_invoiced AS "fullyInvoiced",
//         so.approval_status AS "approvalStatus",
//         so.approved_by AS "approvedById",
//         so.approved_at AS "approvedAt",
//         so.delivery_date AS "deliveryDate",
//         so.dispatch_schedule_date AS "dispatchScheduleDate",
//         so.po_reference AS "poReference",
//         so.total_amount AS "totalAmount",
//         so.status AS "orderStatus",
//         so.customer_address AS "customerAddress",
//         so.customer_gst AS "customerGST",

//         -- ADVANCE PAYMENT DETAILS
//         so.with_advance_payment AS "withAdvancePayment",
//         so.advance_payment_percent AS "advancePaymentPercent",
//         so.advance_payment_amount AS "advancePaymentAmount",
//         so.advance_payment_status AS "advancePaymentStatus",

//         -- APPROVED BY USER DETAILS
//         u.first_name AS "approvedByFirstName",
//         u.last_name AS "approvedByLastName",

//         -- CALCULATIONS
//         CASE
//           WHEN so.total_quantity > 0
//           THEN ROUND((so.remaining_qty / so.total_quantity) * 100, 2)
//           ELSE 0
//         END AS "remainingPercentage",

//         -- DISPATCH TYPE
//         CASE
//           WHEN so.fully_invoiced = true THEN 'FULL'
//           WHEN so.remaining_qty > 0 AND so.total_invoiced_qty > 0 THEN 'PARTIAL'
//           WHEN so.remaining_qty = so.total_quantity THEN 'PENDING'
//           ELSE 'IN_PROGRESS'
//         END AS "dispatchType"

//       FROM commercial_vehicles cv
//       LEFT JOIN sales_orders so ON cv.sales_order_number = so.so_number AND cv.org_id = so.org_id
//       LEFT JOIN users u ON so.approved_by = u.id
//       WHERE
//         cv.is_deleted = false
//         AND cv.weighbridge_status = 'PENDING'
//         AND cv.gate_status = 'Inside'
//         AND cv.org_id = $1
//       ORDER BY cv.created_at DESC
//       LIMIT 4
//     `;

//     const { rows } = await pool.query(query, [orgId]);

//     const formattedData = rows.map(row => ({
//       // Vehicle Details
//       id: row.id,
//       vehicleNo: row.vehicleNo,
//       vehicleType: row.vehicleType,
//       movementType: row.movementType,
//       poNumber: row.poNumber,

//       // Driver Details
//       driverName: row.driverName,
//       driverPhone: row.driverPhone,

//       // Warehouse
//       warehouseId: row.warehouseId,

//       // Source Flow
//       sourceType: row.sourceType,

//       // Shipment Details
//       shipmentId: row.shipmentId,
//       shipmentNumber: row.shipmentNumber,
//       salesOrderNumber: row.salesOrderNumber,

//       // Customer Details
//       customerName: row.customerName,
//       customerDetails: {
//         name: row.customerName,
//         address: row.customerAddress,
//         gst: row.customerGST
//       },

//       // Sales Order Details
//       salesOrder: {
//         soNumber: row.salesOrderNumber,
//         poReference: row.poReference,
//         totalQuantity: row.totalOrderQty,
//         totalInvoicedQty: row.totalInvoicedQty,
//         remainingQty: row.remainingQty,
//         fullyInvoiced: row.fullyInvoiced || false,
//         dispatchType: row.dispatchType || 'PENDING',
//         remainingPercentage: row.remainingPercentage || 0,
//         approvalStatus: row.approvalStatus,
//         approvedBy: row.approvedByFirstName && row.approvedByLastName
//           ? `${row.approvedByFirstName} ${row.approvedByLastName}`.trim()
//           : null,
//         approvedAt: row.approvedAt,
//         deliveryDate: row.deliveryDate,
//         dispatchScheduleDate: row.dispatchScheduleDate,
//         totalAmount: row.totalAmount,
//         orderStatus: row.orderStatus
//       },

//       // Advance Payment Details
//       advancePayment: {
//         withAdvancePayment: row.withAdvancePayment || false,
//         percent: row.advancePaymentPercent,
//         amount: row.advancePaymentAmount,
//         status: row.advancePaymentStatus
//       },

//       // Status
//       weighbridgeStatus: row.weighbridgeStatus,
//       gateStatus: row.gateStatus,
//       createdAt: row.createdAt
//     }));

//     return res.status(200).json({
//       success: true,
//       count: formattedData.length,
//       data: formattedData,
//     });
//   } catch (error) {
//     console.error("Cards API Error:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch card data",
//       error: error.message,
//     });
//   }
// };

// const getWeighbridgeCard = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;

//     const query = `
//       SELECT
//         cv.id,
//         -- VEHICLE
//         cv.vehicle_number AS "vehicleNo",
//         cv.vehicle_type AS "vehicleType",
//         cv.movement_type AS "movementType",
//         cv.reference_no AS "poNumber",
//         -- DRIVER
//         cv.driver_name AS "driverName",
//         cv.driver_number AS "driverPhone",
//         -- WAREHOUSE
//         cv.warehouse_id AS "warehouseId",
//         -- SOURCE FLOW
//         cv.source_type AS "sourceType",
//         -- SHIPMENT DETAILS
//         cv.shipment_id AS "shipmentId",
//         cv.shipment_number AS "shipmentNumber",
//         cv.sales_order_number AS "salesOrderNumber",
//         -- CUSTOMER
//         cv.customer_name AS "customerName",
//         -- STATUS
//         cv.weighbridge_status AS "weighbridgeStatus",
//         cv.gate_status AS "gateStatus",
//         cv.created_at AS "createdAt",

//         -- SALES ORDER DETAILS
//         so.total_quantity AS "totalOrderQty",
//         so.total_invoiced_qty AS "totalInvoicedQty",
//         so.remaining_qty AS "remainingQty",
//         so.fully_invoiced AS "fullyInvoiced",
//         so.approval_status AS "approvalStatus",
//         so.approved_by AS "approvedById",
//         so.approved_at AS "approvedAt",
//         so.delivery_date AS "deliveryDate",
//         so.dispatch_schedule_date AS "dispatchScheduleDate",
//         so.po_reference AS "poReference",
//         so.total_amount AS "totalAmount",
//         so.status AS "orderStatus",
//         so.customer_address AS "customerAddress",
//         so.customer_gst AS "customerGST",

//         -- ADVANCE PAYMENT DETAILS
//         so.with_advance_payment AS "withAdvancePayment",
//         so.advance_payment_percent AS "advancePaymentPercent",
//         so.advance_payment_amount AS "advancePaymentAmount",
//         so.advance_payment_status AS "advancePaymentStatus",

//         -- APPROVED BY USER DETAILS
//         u.first_name AS "approvedByFirstName",
//         u.last_name AS "approvedByLastName",

//         -- DISPATCH ALLOCATION DETAILS (Current Trip)
//         da.id AS "allocationId",
//         da.allocated_qty AS "allocatedQty",
//         da.dispatched_qty AS "dispatchedQty",
//         da.pending_dispatch_qty AS "pendingDispatchQty",
//         da.total_dispatched_qty AS "totalDispatchedQty",
//         da.remaining_shipment_qty AS "remainingShipmentQty",
//         da.trip_number AS "tripNumber",
//         da.shipment_count AS "shipmentCount",
//         da.allow_multiple_shipments AS "allowMultipleShipments",
//         da.shipment_pending AS "shipmentPending",
//         da.allocation_status AS "allocationStatus",
//         da.vehicle_assigned AS "vehicleAssigned",
//         da.shipment_completed AS "shipmentCompleted",

//         -- Calculate trip allocation type
//         CASE
//           -- Check if this is the first trip and fully allocated
//           WHEN da.trip_number = 1
//             AND da.allocated_qty >= so.total_quantity
//             AND (so.total_quantity > 0)
//             THEN 'FULL_ALLOCATION'
//           -- Partial allocation (multiple trips expected)
//           WHEN da.remaining_shipment_qty > 0
//             OR da.allow_multiple_shipments = true
//             THEN 'PARTIAL_ALLOCATION'
//           -- Single trip but not full (partial in one trip)
//           WHEN da.allocated_qty < so.total_quantity
//             THEN 'PARTIAL_ALLOCATION'
//           ELSE 'FULL_ALLOCATION'
//         END AS "tripAllocationType",

//         -- Calculate allocated percentage for this trip
//         CASE
//           WHEN so.total_quantity > 0
//           THEN ROUND((da.allocated_qty / so.total_quantity) * 100, 2)
//           ELSE 0
//         END AS "tripAllocatedPercentage",

//         -- Calculate remaining after this trip
//         (so.total_quantity - COALESCE(da.total_dispatched_qty, 0) - COALESCE(da.allocated_qty, 0)) AS "remainingAfterCurrentTrip",

//         -- CALCULATIONS for overall order
//         CASE
//           WHEN so.total_quantity > 0
//           THEN ROUND((so.remaining_qty / so.total_quantity) * 100, 2)
//           ELSE 0
//         END AS "remainingPercentage",

//         -- DISPATCH TYPE for overall order
//         CASE
//           WHEN so.fully_invoiced = true THEN 'FULL'
//           WHEN so.remaining_qty > 0 AND so.total_invoiced_qty > 0 THEN 'PARTIAL'
//           WHEN so.remaining_qty = so.total_quantity THEN 'PENDING'
//           ELSE 'IN_PROGRESS'
//         END AS "dispatchType"

//       FROM commercial_vehicles cv
//       LEFT JOIN sales_orders so ON cv.sales_order_number = so.so_number AND cv.org_id = so.org_id
//       LEFT JOIN users u ON so.approved_by = u.id
//       LEFT JOIN dispatch_allocations da ON cv.sales_order_number = da.sales_order_number
//         AND cv.org_id = da.org_id
//         AND da.is_deleted = false
//         AND da.vehicle_assigned = true
//         AND da.shipment_completed = false
//       WHERE
//         cv.is_deleted = false
//         AND cv.weighbridge_status = 'PENDING'
//         AND cv.gate_status = 'Inside'
//         AND cv.org_id = $1
//       ORDER BY cv.created_at DESC
//       LIMIT 4
//     `;

//     const { rows } = await pool.query(query, [orgId]);

//     const formattedData = rows.map(row => {
//       // Determine trip display info
//       let tripDisplayText = '';
//       let tripBadgeColor = '';

//       if (row.tripAllocationType === 'FULL_ALLOCATION') {
//         tripDisplayText = 'Full Allocation';
//         tripBadgeColor = 'success';
//       } else if (row.tripAllocationType === 'PARTIAL_ALLOCATION') {
//         tripDisplayText = `Partial Allocation (${row.tripAllocatedPercentage}%)`;
//         tripBadgeColor = 'warning';
//       } else {
//         tripDisplayText = 'Trip Allocation Pending';
//         tripBadgeColor = 'secondary';
//       }

//       return {
//         // Vehicle Details
//         id: row.id,
//         vehicleNo: row.vehicleNo,
//         vehicleType: row.vehicleType,
//         movementType: row.movementType,
//         poNumber: row.poNumber,

//         // Driver Details
//         driverName: row.driverName,
//         driverPhone: row.driverPhone,

//         // Warehouse
//         warehouseId: row.warehouseId,

//         // Source Flow
//         sourceType: row.sourceType,

//         // Shipment Details
//         shipmentId: row.shipmentId,
//         shipmentNumber: row.shipmentNumber,
//         salesOrderNumber: row.salesOrderNumber,

//         // Customer Details
//         customerName: row.customerName,
//         customerDetails: {
//           name: row.customerName,
//           address: row.customerAddress,
//           gst: row.customerGST
//         },

//         // Sales Order Details
//         salesOrder: {
//           soNumber: row.salesOrderNumber,
//           poReference: row.poReference,
//           totalQuantity: row.totalOrderQty,
//           totalInvoicedQty: row.totalInvoicedQty,
//           remainingQty: row.remainingQty,
//           fullyInvoiced: row.fullyInvoiced || false,
//           dispatchType: row.dispatchType || 'PENDING',
//           remainingPercentage: row.remainingPercentage || 0,
//           approvalStatus: row.approvalStatus,
//           approvedBy: row.approvedByFirstName && row.approvedByLastName
//             ? `${row.approvedByFirstName} ${row.approvedByLastName}`.trim()
//             : null,
//           approvedAt: row.approvedAt,
//           deliveryDate: row.deliveryDate,
//           dispatchScheduleDate: row.dispatchScheduleDate,
//           totalAmount: row.totalAmount,
//           orderStatus: row.orderStatus
//         },

//         // Current Trip Allocation Details
//         currentTrip: {
//           allocationId: row.allocationId,
//           tripNumber: row.tripNumber || 1,
//           allocatedQty: row.allocatedQty,
//           dispatchedQty: row.dispatchedQty,
//           pendingDispatchQty: row.pendingDispatchQty,
//           totalDispatchedQty: row.totalDispatchedQty,
//           remainingShipmentQty: row.remainingShipmentQty,
//           allocationType: row.tripAllocationType,
//           allocatedPercentage: row.tripAllocatedPercentage,
//           remainingAfterCurrentTrip: row.remainingAfterCurrentTrip,
//           allowMultipleShipments: row.allowMultipleShipments,
//           shipmentCount: row.shipmentCount,
//           shipmentPending: row.shipmentPending,
//           allocationStatus: row.allocationStatus,
//           displayText: tripDisplayText,
//           badgeColor: tripBadgeColor,
//           // Helper flags
//           isFullAllocation: row.tripAllocationType === 'FULL_ALLOCATION',
//           isPartialAllocation: row.tripAllocationType === 'PARTIAL_ALLOCATION'
//         },

//         // Advance Payment Details
//         advancePayment: {
//           withAdvancePayment: row.withAdvancePayment || false,
//           percent: row.advancePaymentPercent,
//           amount: row.advancePaymentAmount,
//           status: row.advancePaymentStatus
//         },

//         // Status
//         weighbridgeStatus: row.weighbridgeStatus,
//         gateStatus: row.gateStatus,
//         createdAt: row.createdAt
//       };
//     });

//     return res.status(200).json({
//       success: true,
//       count: formattedData.length,
//       data: formattedData,
//     });
//   } catch (error) {
//     console.error("Cards API Error:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch card data",
//       error: error.message,
//     });
//   }
// };

const getWeighbridgeCard = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    const query = `
      SELECT 
        cv.id,
        -- VEHICLE
        cv.vehicle_number AS "vehicleNo",
        cv.vehicle_type AS "vehicleType",
        cv.movement_type AS "movementType",
        cv.reference_no AS "poNumber",
        -- DRIVER
        cv.driver_name AS "driverName",
        cv.driver_number AS "driverPhone",
        -- WAREHOUSE
        cv.warehouse_id AS "warehouseId",
        -- SOURCE FLOW
        cv.source_type AS "sourceType",
        -- SHIPMENT DETAILS
        cv.shipment_id AS "shipmentId",
        cv.shipment_number AS "shipmentNumber",
        cv.sales_order_number AS "salesOrderNumber",
        -- CUSTOMER
        cv.customer_name AS "customerName",
        -- STATUS
        cv.weighbridge_status AS "weighbridgeStatus",
        cv.gate_status AS "gateStatus",
        cv.created_at AS "createdAt",
        
        -- SALES ORDER DETAILS
        so.total_quantity AS "totalOrderQty",
        so.total_invoiced_qty AS "totalInvoicedQty",
        so.remaining_qty AS "remainingQty",
        so.fully_invoiced AS "fullyInvoiced",
        so.approval_status AS "approvalStatus",
        so.approved_by AS "approvedById",
        so.approved_at AS "approvedAt",
        so.delivery_date AS "deliveryDate",
        so.dispatch_schedule_date AS "dispatchScheduleDate",
        so.po_reference AS "poReference",
        so.total_amount AS "totalAmount",
        so.status AS "orderStatus",
        so.customer_address AS "customerAddress",
        so.customer_gst AS "customerGST",
        
        -- ADVANCE PAYMENT DETAILS
        so.with_advance_payment AS "withAdvancePayment",
        so.advance_payment_percent AS "advancePaymentPercent",
        so.advance_payment_amount AS "advancePaymentAmount",
        so.advance_payment_status AS "advancePaymentStatus",
        
        -- APPROVED BY USER DETAILS
        u.first_name AS "approvedByFirstName",
        u.last_name AS "approvedByLastName",
        
        -- DISPATCH ALLOCATION DETAILS (Current Trip)
        da.id AS "allocationId",
        da.allocated_qty AS "allocatedQty",
        da.dispatched_qty AS "dispatchedQty",
        da.pending_dispatch_qty AS "pendingDispatchQty",
        da.total_dispatched_qty AS "totalDispatchedQty",
        da.remaining_shipment_qty AS "remainingShipmentQty",
        da.trip_number AS "tripNumber",
        da.shipment_count AS "shipmentCount",
        da.allow_multiple_shipments AS "allowMultipleShipments",
        da.shipment_pending AS "shipmentPending",
        da.allocation_status AS "allocationStatus",
        da.vehicle_assigned AS "vehicleAssigned",
        da.shipment_completed AS "shipmentCompleted",
        
        -- SALES ORDER ITEMS (Aggregated as JSON array)
        COALESCE(
          (
            SELECT JSON_AGG(
              JSON_BUILD_OBJECT(
                'id', soi.id,
                'productId', soi.product_id,
                'productCode', soi.product_code,
                'productName', soi.product_name,
                'description', soi.description,
                'qty', soi.qty,
                'uomSymbol', soi.uom_symbol,
                'rate', soi.rate,
                'amount', soi.amount,
                'gstAmount', soi.gst_amount,
                'taxPercent', soi.tax_percent
              )
              ORDER BY soi.id
            )
            FROM sales_order_items soi
            WHERE soi.sales_order_id = so.id 
              AND soi.is_deleted = false
          ),
          '[]'::json
        ) AS "salesOrderItems",
        
        -- Calculate trip allocation type
        CASE 
          -- Check if this is the first trip and fully allocated
          WHEN da.trip_number = 1 
            AND da.allocated_qty >= so.total_quantity 
            AND (so.total_quantity > 0)
            THEN 'FULL_ALLOCATION'
          -- Partial allocation (multiple trips expected)
          WHEN da.remaining_shipment_qty > 0 
            OR da.allow_multiple_shipments = true
            THEN 'PARTIAL_ALLOCATION'
          -- Single trip but not full (partial in one trip)
          WHEN da.allocated_qty < so.total_quantity
            THEN 'PARTIAL_ALLOCATION'
          ELSE 'FULL_ALLOCATION'
        END AS "tripAllocationType",
        
        -- Calculate allocated percentage for this trip
        CASE 
          WHEN so.total_quantity > 0 
          THEN ROUND((da.allocated_qty / so.total_quantity) * 100, 2)
          ELSE 0
        END AS "tripAllocatedPercentage",
        
        -- Calculate remaining after this trip
        (so.total_quantity - COALESCE(da.total_dispatched_qty, 0) - COALESCE(da.allocated_qty, 0)) AS "remainingAfterCurrentTrip",
        
        -- CALCULATIONS for overall order
        CASE 
          WHEN so.total_quantity > 0 
          THEN ROUND((so.remaining_qty / so.total_quantity) * 100, 2)
          ELSE 0
        END AS "remainingPercentage",
        
        -- DISPATCH TYPE for overall order
        CASE 
          WHEN so.fully_invoiced = true THEN 'FULL'
          WHEN so.remaining_qty > 0 AND so.total_invoiced_qty > 0 THEN 'PARTIAL'
          WHEN so.remaining_qty = so.total_quantity THEN 'PENDING'
          ELSE 'IN_PROGRESS'
        END AS "dispatchType"

      FROM commercial_vehicles cv
      LEFT JOIN sales_orders so ON cv.sales_order_number = so.so_number AND cv.org_id = so.org_id
      LEFT JOIN users u ON so.approved_by = u.id
      LEFT JOIN dispatch_allocations da ON cv.sales_order_number = da.sales_order_number 
        AND cv.org_id = da.org_id
        AND da.is_deleted = false
        AND da.vehicle_assigned = true
        AND da.shipment_completed = false
      WHERE 
        cv.is_deleted = false
        AND cv.weighbridge_status = 'PENDING'
        AND cv.gate_status = 'Inside'
        AND cv.org_id = $1
      ORDER BY cv.created_at DESC
      LIMIT 4
    `;

    const { rows } = await pool.query(query, [orgId]);

    const formattedData = rows.map((row) => {
      // Determine trip display info
      let tripDisplayText = "";
      let tripBadgeColor = "";

      if (row.tripAllocationType === "FULL_ALLOCATION") {
        tripDisplayText = "Full Allocation";
        tripBadgeColor = "success";
      } else if (row.tripAllocationType === "PARTIAL_ALLOCATION") {
        tripDisplayText = `Partial Allocation (${row.tripAllocatedPercentage}%)`;
        tripBadgeColor = "warning";
      } else {
        tripDisplayText = "Trip Allocation Pending";
        tripBadgeColor = "secondary";
      }

      return {
        // Vehicle Details
        id: row.id,
        vehicleNo: row.vehicleNo,
        vehicleType: row.vehicleType,
        movementType: row.movementType,
        poNumber: row.poNumber,

        // Driver Details
        driverName: row.driverName,
        driverPhone: row.driverPhone,

        // Warehouse
        warehouseId: row.warehouseId,

        // Source Flow
        sourceType: row.sourceType,

        // Shipment Details
        shipmentId: row.shipmentId,
        shipmentNumber: row.shipmentNumber,
        salesOrderNumber: row.salesOrderNumber,

        // Customer Details
        customerName: row.customerName,
        customerDetails: {
          name: row.customerName,
          address: row.customerAddress,
          gst: row.customerGST,
        },

        // Sales Order Details
        salesOrder: {
          soNumber: row.salesOrderNumber,
          poReference: row.poReference,
          totalQuantity: row.totalOrderQty,
          totalInvoicedQty: row.totalInvoicedQty,
          remainingQty: row.remainingQty,
          fullyInvoiced: row.fullyInvoiced || false,
          dispatchType: row.dispatchType || "PENDING",
          remainingPercentage: row.remainingPercentage || 0,
          approvalStatus: row.approvalStatus,
          approvedBy:
            row.approvedByFirstName && row.approvedByLastName
              ? `${row.approvedByFirstName} ${row.approvedByLastName}`.trim()
              : null,
          approvedAt: row.approvedAt,
          deliveryDate: row.deliveryDate,
          dispatchScheduleDate: row.dispatchScheduleDate,
          totalAmount: row.totalAmount,
          orderStatus: row.orderStatus,
          // Sales Order Items Array
          items: row.salesOrderItems || [],
        },

        // Current Trip Allocation Details
        currentTrip: {
          allocationId: row.allocationId,
          tripNumber: row.tripNumber || 1,
          allocatedQty: row.allocatedQty,
          dispatchedQty: row.dispatchedQty,
          pendingDispatchQty: row.pendingDispatchQty,
          totalDispatchedQty: row.totalDispatchedQty,
          remainingShipmentQty: row.remainingShipmentQty,
          allocationType: row.tripAllocationType,
          allocatedPercentage: row.tripAllocatedPercentage,
          remainingAfterCurrentTrip: row.remainingAfterCurrentTrip,
          allowMultipleShipments: row.allowMultipleShipments,
          shipmentCount: row.shipmentCount,
          shipmentPending: row.shipmentPending,
          allocationStatus: row.allocationStatus,
          displayText: tripDisplayText,
          badgeColor: tripBadgeColor,
          // Helper flags
          isFullAllocation: row.tripAllocationType === "FULL_ALLOCATION",
          isPartialAllocation: row.tripAllocationType === "PARTIAL_ALLOCATION",
        },

        // Advance Payment Details
        advancePayment: {
          withAdvancePayment: row.withAdvancePayment || false,
          percent: row.advancePaymentPercent,
          amount: row.advancePaymentAmount,
          status: row.advancePaymentStatus,
        },

        // Status
        weighbridgeStatus: row.weighbridgeStatus,
        gateStatus: row.gateStatus,
        createdAt: row.createdAt,
      };
    });

    return res.status(200).json({
      success: true,
      count: formattedData.length,
      data: formattedData,
    });
  } catch (error) {
    console.error("Cards API Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch card data",
      error: error.message,
    });
  }
};

module.exports = {
  createVehicleEntry,
  getAllVehicles,
  blacklistVehicle,
  getVehiclePrefill,
  getWeighbridgeCards,
  getBlacklistVehicles,
  checkVehicleBlacklist,
  unblockVehicle,
  createBlacklistVehicle,
  updateVehicleEntry,
  markVehicleExit,
  deleteVehicle,
  exitVehicle,
  getWeighbridgeVehicleById,
  downloadWeighmentSlip,
  getDispatchWaitingVehicles,
  updateGateIn,
  createDispatchVehicleEntry,
  getDispatchWeighbridgeDetails,
  getMaterialGroupBySO,
  completeDispatchWeighment,
  completeDispatchReturnWeight,
  getReturnDispatchWeighbridgeDetails,
  downloadDispatchDocument,
  getPOVendors,
  getPOsByVendor,
  getWeighbridgeCard,
};
