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.*,

        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, movementTypeOut } = req.body;
    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 * FROM commercial_vehicles
    //    WHERE id = $1 AND is_deleted = false
    //    FOR UPDATE`,
    //   [id],
    // );
    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.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],
    // );
    // const year = new Date().getFullYear();

    await client.query(
      `
UPDATE commercial_vehicles 
SET 
    out_weight = $1,
    net_weight = $2,
    movement_type_out = $3,
    security_remarks = $4,

    -- ✅ ADD HERE
    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,
      ],
    );

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

    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..")

  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) / 1000;

    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, "histoy 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
}));

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


    let totalDeductionMT = 0;

    const deductionRowsData = masterParameters.map((row) => ({
  parameter_name: row.parameterName,
  deduction_percent:
    Number(row.deductionPercent || 0).toFixed(2),

  deduction_mt:
    Number(row.deductionWeight || 0) / 1000
}));

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

    const quantityBags =
Number(qcData.quantity_bags || 0);

const bagWeightMT =
(quantityBags * 200) / 1000000;

    totalDeductionMT += bagWeightMT;
    const finalWeightMT = receivedWeightMT - totalDeductionMT;
    console.log(finalWeightMT, "weight")




    /* 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 */
    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 - IMPORTANT: Get all items from the PO */
      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 - WITHOUT created_by COLUMN */
      if (poItems.length > 0) {
        console.log("Creating GRN items...");
        for (const item of poItems) {
          const orderedQty = parseFloat(item.quantity) || 0;
          const receivedQty = orderedQty;
          const acceptedQty = orderedQty;
          const rejectedQty = 0;
          const unitPrice = parseFloat(item.unit_price) || 0;
          const totalPrice = acceptedQty * 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,
              receivedQty,
              acceptedQty,
              rejectedQty,
              unitPrice,
              totalPrice,
              0, // rejected_unit_price
              0, // rejected_total_price
              totalPrice, // grand_total
              "Main Store", // store_location
            ],
          );
          console.log(`Created GRN item for: ${item.item_name}`);
        }
      } else {
        // Fallback if no PO items found
        const defaultMaterial =
          material.material_name || vehicle.goods || "General Material";
        const defaultQuantity = Math.abs(netQty) || 1;

        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,
            defaultQuantity,
            defaultQuantity,
            defaultQuantity,
            0,
            0,
            0,
            0,
            0,
            0,
            "Main Store",
          ],
        );
        console.log(`Created default GRN item for: ${defaultMaterial}`);

        // Create a default PO item array for stock creation
        poItems = [
          {
            id: null,
            item_name: defaultMaterial,
            quantity: defaultQuantity,
            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;
        }
      }
    }

    /* CRITICAL FIX: Create SEPARATE stock entries for EACH PO item */
    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 = Math.abs(netQty);
        let itemNetQuantity = Math.abs(netQty) / 1000;
        if (totalPoQuantity > 0 && poItem.quantity) {
          // Distribute based on PO item quantity ratio
          const itemRatio = parseFloat(poItem.quantity) / totalPoQuantity;
          itemNetQuantity = Math.abs(netQty) * itemRatio;
        }

        // Get the material name from PO item
        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,
            gross,
            tare,
            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 if no PO items
      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,
          gross,
          tare,
          Math.abs(netQty),
          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],
    );

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

    // const pdf = await generateWeighmentSlipPDF({
    //   ...vehicle,

    //   product: material.material_type || "-",

    //   supplier_name: material.supplier_name || "-",

    //   quantity_bags: material.quantity_bags || 0,

    //   delivery_challan_no: material.delivery_challan_no || "-",

    //   delivery_qty: Math.abs(netQty),

    //   net_weight: Math.abs(netQty),

    //   gross_weight: gross,

    //   tare_weight: tare,
    //   userId,
    // });

    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
};

const qcPdf =
await generateQCCertificatePDF(
  qcPdfData
);




    const pdf = await generateWeighmentSlipPDF({
      ...vehicle,

      // 🔥 IMPORTANT UPDATED VALUES
      out_weight: tare,
      exit_time: new Date(),

      product: material.material_type || "-",

      supplier_name: material.supplier_name || "-",

      // quantity_bags: material.quantity_bags || 0,

      delivery_challan_no: material.delivery_challan_no || "-",

      delivery_qty: Math.abs(netQty),

      net_weight: Math.abs(netQty),

      gross_weight: gross,

      tare_weight: tare,

      userId,
    });
    const fs = require("fs");
    const path = require("path");
    // const pdfFileName = `weighment-slip-${id}.pdf`;
    const pdfFileName = `weighment-slip-${id}-${Date.now()}.pdf`;

    const now = new Date();

    const year = now.getFullYear();

    const month = String(now.getMonth() + 1).padStart(2, "0");

    // const pdfDir = path.join(__dirname, `../uploads/docs/${year}/${month}`);
    const pdfDir = path.join(process.cwd(), `uploads/docs/${year}/${month}`);

    // create folder if not exists
    if (!fs.existsSync(pdfDir)) {
      fs.mkdirSync(pdfDir, {
        recursive: true,
      });
    }

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


    const pdfFileNames =
`qc-certificate-${id}-${Date.now()}.pdf`;
const pdfPaths =
path.join(pdfDir, pdfFileNames);

fs.writeFileSync(
  pdfPaths,
  qcPdf
);



await client.query(
`
UPDATE quality_checks
SET qc_certificate_file = $1,
    updated_at = NOW()
WHERE id = $2
`,
[
  `uploads/docs/${year}/${month}/${pdfFileNames}`,
  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();

    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,
    created_at,
    updated_at
  )
  VALUES (
    $1,$2,$3,$4,
    NOW(),
    $5,$6,
    NOW(),NOW()
  )
  `,
      [insertedVehicle.id, gatePassNo, "ENTRY", "ACTIVE", userId, orgId],
    );

    // ========================
    // 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 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 } = 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],
    //   );
    const dispatchResult = await client.query(
      `
    SELECT *
    FROM shipment_dispatches
    WHERE id = $1
    FOR UPDATE
    `,
      [dispatchId],
    );

    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(dispatch.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));
    }

    // ===============================
    // KL → TON
    // ===============================
    else if (uom === "KL") {
      outWeightValue = Number((rawOutWeight / 1000).toFixed(3));
    }

    // ===============================
    // 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;

      grossWeight = outWeightValue;

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

    await client.query(
      `
  UPDATE commercial_vehicles
  SET
    out_weight = $1,
    return_dispatch_done = true,
    updated_at = NOW()
  WHERE id = $2
  `,
      [outWeightValue, 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 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}`;

    // =========================================
    // 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_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,
    });
  }
};

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",

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


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