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



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

//     if (!orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized access",
//       });
//     }

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

//         /* ===================================================== */
//         /* PRODUCT */
//         /* ===================================================== */

//         pim.id AS product_id,

//         pim.item_name,

//         pim.uom,

//         /* ===================================================== */
//         /* CURRENT REMAINING STOCK */
//         /* ===================================================== */

//         COALESCE(stock.available_qty, 0) AS remaining_stock,

//         /* ===================================================== */
//         /* RESERVED STOCK */
//         /* ===================================================== */

//         COALESCE(stock.reserved_qty, 0) AS reserved_qty,

//         /* ===================================================== */
//         /* TOTAL REQUIRED */
//         /* ===================================================== */

//         COALESCE(alloc.total_required, 0) AS total_required,

//         /* ===================================================== */
//         /* TOTAL ALLOCATED */
//         /* ===================================================== */

//         COALESCE(alloc.total_allocated, 0) AS total_allocated,

//         /* ===================================================== */
//         /* ORIGINAL STOCK BEFORE ALLOCATION */
//         /* ===================================================== */

//         (
//           COALESCE(stock.available_qty, 0)
//           +
//           COALESCE(alloc.total_allocated, 0)
//         ) AS original_stock,

//         /* ===================================================== */
//         /* REMAINING STOCK PERCENTAGE */
//         /* ===================================================== */

//         CASE

//           WHEN (
//             COALESCE(stock.available_qty, 0)
//             +
//             COALESCE(alloc.total_allocated, 0)
//           ) = 0

//           THEN 0

//           ELSE ROUND(
//             (
//               COALESCE(stock.available_qty, 0)::numeric
//               /
//               NULLIF(
//                 (
//                   COALESCE(stock.available_qty, 0)
//                   +
//                   COALESCE(alloc.total_allocated, 0)
//                 ),
//                 0
//               )::numeric
//             ) * 100,
//             0
//           )

//         END AS remaining_percentage,

//         /* ===================================================== */
//         /* TOTAL ORDERS */
//         /* ===================================================== */

//         COALESCE(alloc.total_orders, 0) AS total_orders,

//         /* ===================================================== */
//         /* TODAY SALES ORDERS */
//         /* ===================================================== */

//         COALESCE(today_so.today_sales_orders, 0) AS today_sales_orders

//       FROM procurement_itemmaster pim

//       /* ===================================================== */
//       /* INVENTORY STOCK */
//       /* ===================================================== */

//       LEFT JOIN inventory_stock stock
//         ON stock.product_id = pim.id
//         AND stock.org_id = $1

//       /* ===================================================== */
//       /* ALLOCATION SUMMARY */
//       /* ===================================================== */

//       LEFT JOIN (

//         SELECT

//           product_id,

//           COUNT(*) AS total_orders,

//           SUM(required_qty) AS total_required,

//           SUM(allocated_qty) AS total_allocated

//         FROM dispatch_allocations

//         WHERE org_id = $1
//           AND is_deleted = false

//         GROUP BY product_id

//       ) alloc
//         ON alloc.product_id = pim.id

//       /* ===================================================== */
//       /* TODAY SALES ORDERS */
//       /* ===================================================== */

//       LEFT JOIN (

//         SELECT

//           soi.product_id,

//           COUNT(DISTINCT soi.sales_order_id) AS today_sales_orders

//         FROM sales_order_items soi

//         INNER JOIN sales_orders so
//           ON so.id = soi.sales_order_id

//         WHERE so.org_id = $1
//           AND DATE(so.created_at) = CURRENT_DATE

//         GROUP BY soi.product_id

//       ) today_so
//         ON today_so.product_id = pim.id

//       /* ===================================================== */
//       /* PRODUCT FILTER */
//       /* ===================================================== */

//       WHERE pim.item_name IN (
//         'Ethanol',
//         'DDGS',
//         'CO2',
//         'ENA'
//       )

//       AND pim.is_deleted = false

//       ORDER BY pim.item_name ASC;
//       `,
//       [orgId]
//     );

//     return res.status(200).json({
//       success: true,
//       message: "Dashboard products fetched successfully",
//       data: result.rows,
//     });

//   } catch (err) {
//     console.error(
//       "DISPATCH DASHBOARD PRODUCTS ERROR:",
//       err
//     );

//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch dashboard products",
//       error: err.message,
//     });
//   }
// };
const getDispatchDashboardProducts = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized access",
      });
    }

    const result = await pool.query(
      `
      WITH product_uom AS (
        SELECT DISTINCT
          soi.product_id,
          soi.uom_symbol
        FROM sales_order_items soi
        INNER JOIN sales_orders so ON so.id = soi.sales_order_id
        WHERE so.org_id = $1
          AND soi.uom_symbol IS NOT NULL
      )
      SELECT
        /* ===================================================== */
        /* PRODUCT INFORMATION */
        /* ===================================================== */
        pim.id AS product_id,
        pim.item_name,
        COALESCE(pu.uom_symbol, pim.uom) AS uom,

        /* ===================================================== */
        /* CURRENT REMAINING STOCK */
        /* ===================================================== */
        COALESCE(stock.available_qty, 0) AS available_stock,

        /* ===================================================== */
        /* RESERVED STOCK */
        /* ===================================================== */
        COALESCE(stock.reserved_qty, 0) AS reserved_qty,

        /* ===================================================== */
        /* TOTAL ALLOCATED FROM DISPATCH_ALLOCATIONS */
        /* ===================================================== */
        COALESCE(alloc.total_allocated, 0) AS allocated_stock,

        /* ===================================================== */
        /* TOTAL REQUIRED QUANTITY */
        /* ===================================================== */
        COALESCE(alloc.total_required, 0) AS total_required,

        /* ===================================================== */
        /* ORIGINAL STOCK (Available + Allocated) */
        /* ===================================================== */
        (
          COALESCE(stock.available_qty, 0) + COALESCE(alloc.total_allocated, 0)
        ) AS original_stock,

        /* ===================================================== */
        /* REMAINING STOCK PERCENTAGE */
        /* ===================================================== */
        CASE
          WHEN (
            COALESCE(stock.available_qty, 0) + COALESCE(alloc.total_allocated, 0)
          ) = 0 THEN 0
          ELSE ROUND(
            (COALESCE(stock.available_qty, 0)::numeric / 
              NULLIF(
                (COALESCE(stock.available_qty, 0) + COALESCE(alloc.total_allocated, 0)),
                0
              )::numeric
            ) * 100,
            0
          )
        END AS allocation_percentage,

        /* ===================================================== */
        /* TOTAL PENDING ORDERS COUNT */
        /* ===================================================== */
        COALESCE(alloc.total_orders, 0) AS total_orders,

        /* ===================================================== */
        /* TODAY'S SALES ORDERS COUNT */
        /* ===================================================== */
        COALESCE(today_so.today_sales_orders, 0) AS today_sales_orders

      FROM procurement_itemmaster pim
      
      /* ===================================================== */
      /* PRODUCT UOM FROM SALES ORDER ITEMS */
      /* ===================================================== */
      LEFT JOIN product_uom pu ON pu.product_id = pim.id

      /* ===================================================== */
      /* INVENTORY STOCK JOIN */
      /* ===================================================== */
      LEFT JOIN inventory_stock stock
        ON stock.product_id = pim.id
        AND stock.org_id = $1

      /* ===================================================== */
      /* ALLOCATION SUMMARY JOIN */
      /* ===================================================== */
      LEFT JOIN (
        SELECT 
          product_id,
          COUNT(*) AS total_orders,
          SUM(required_qty) AS total_required,
          SUM(allocated_qty) AS total_allocated
        FROM dispatch_allocations
        WHERE org_id = $1
          AND is_deleted = false
          AND allocation_status != 'cancelled'
        GROUP BY product_id
      ) alloc ON alloc.product_id = pim.id

      /* ===================================================== */
      /* TODAY'S SALES ORDERS JOIN */
      /* ===================================================== */
      LEFT JOIN (
        SELECT 
          soi.product_id,
          COUNT(DISTINCT soi.sales_order_id) AS today_sales_orders
        FROM sales_order_items soi
        INNER JOIN sales_orders so ON so.id = soi.sales_order_id
        WHERE so.org_id = $1
          AND DATE(so.created_at) = CURRENT_DATE
          AND so.is_deleted = false
        GROUP BY soi.product_id
      ) today_so ON today_so.product_id = pim.id

      WHERE pim.item_name IN ('Ethanol', 'DDGS', 'CO2', 'ENA')
        AND pim.is_deleted = false

      ORDER BY 
        CASE pim.item_name
          WHEN 'Ethanol' THEN 1
          WHEN 'DDGS' THEN 2
          WHEN 'CO2' THEN 3
          WHEN 'ENA' THEN 4
          ELSE 5
        END ASC;
      `,
      [orgId]
    );

    // Format the response data
    const formattedData = result.rows.map(row => ({
      product_id: row.product_id,
      item_name: row.item_name,
      uom: row.uom || 'Units',
      available_stock: parseFloat(row.available_stock) || 0,
      reserved_qty: parseFloat(row.reserved_qty) || 0,
      allocated_stock: parseFloat(row.allocated_stock) || 0,
      total_required: parseFloat(row.total_required) || 0,
      original_stock: parseFloat(row.original_stock) || 0,
      allocation_percentage: parseFloat(row.allocation_percentage) || 0,
      total_orders: parseInt(row.total_orders) || 0,
      today_sales_orders: parseInt(row.today_sales_orders) || 0
    }));

    return res.status(200).json({
      success: true,
      message: "Dashboard products fetched successfully",
      data: formattedData,
    });

  } catch (err) {
    console.error("DISPATCH DASHBOARD PRODUCTS ERROR:", err);
    
    return res.status(500).json({
      success: false,
      message: "Failed to fetch dashboard products",
      error: process.env.NODE_ENV === 'development' ? err.message : undefined,
    });
  }
};

const getProductDispatchAllocations = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const productId = req.params.productId || null;

    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized access",
      });
    }

    const result = await pool.query(
      `
      SELECT
          da.id,
          da.sales_order_id,
          da.sales_order_number,
          da.customer_id,
          da.customer_name,
          da.product_id,
          da.required_qty,
          da.allocated_qty,
          da.balance_qty,
          da.delivered_qty,
          da.available_stock,
          da.next_dispatch_date,
          da.delivery_date,
          da.vehicle_assigned,
          da.gate_pass_generated,
          da.shipment_completed,
          da.priority,
          da.remarks,
          da.allocation_status,
          da.delivery_status,
          da.created_at,
          da.updated_at,
          da.allow_multiple_shipments,
          da.shipment_count,
          da.total_dispatched_qty,
          da.remaining_shipment_qty,

          /* LATEST SHIPMENT */
          sd.id AS shipment_id,
          sd.shipment_number,
          sd.shipment_status,
          sd.approval_status,
          sd.vehicle_number,
          sd.vehicle_type,
          sd.dispatch_datetime,
          sd.created_at AS shipment_created_at,
          sd.dispatch_qty,
          sd.driver_name,

          /* INVOICE */
          si.id AS invoice_id,
          si.invoice_number,
          si.pdf_path,

          /* PRODUCT */
          pim.item_name,
          pim.uom,
          pim.uom_symbol,

          /* ALLOCATION PERCENTAGE */
          ROUND(
              (COALESCE(da.allocated_qty, 0)::numeric / NULLIF(da.required_qty, 0)::numeric) * 100,
              0
          ) AS allocation_percent,

          /* TOTAL DISPATCHED FROM SHIPMENTS */
          COALESCE((
              SELECT SUM(dispatch_qty)
              FROM shipment_dispatches
              WHERE allocation_id = da.id
                AND is_deleted = false
                AND shipment_status NOT IN ('Cancelled')
          ), 0) AS total_dispatched,

          /* ACTIVE SHIPMENT EXISTS */
          CASE
              WHEN EXISTS (
                  SELECT 1 FROM shipment_dispatches
                  WHERE allocation_id = da.id
                    AND is_deleted = false
                    AND shipment_status IN ('Pending', 'Gate In', 'Loaded', 'Dispatched')
              ) THEN true
              ELSE false
          END AS has_active_shipment

      FROM dispatch_allocations da

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

      LEFT JOIN LATERAL (
          SELECT
              sd.id,
              sd.shipment_number,
              sd.shipment_status,
              sd.approval_status,
              sd.vehicle_number,
              sd.vehicle_type,
              sd.dispatch_datetime,
              sd.created_at,
              sd.dispatch_qty,
              sd.driver_name
          FROM shipment_dispatches sd
          WHERE sd.allocation_id = da.id
            AND COALESCE(sd.is_deleted, false) = false
          ORDER BY sd.created_at DESC
          LIMIT 1
      ) sd ON true

      LEFT JOIN sales_invoices si
          ON si.sales_order_number = da.sales_order_number
          AND si.status = 'approved'
          AND COALESCE(si.is_deleted, false) = false

      WHERE da.org_id = $1
        AND ($2::int IS NULL OR da.product_id = $2)
        AND COALESCE(da.is_deleted, false) = false
        AND da.allocated_qty > 0  -- Only show allocations with allocated quantity

      ORDER BY 
          CASE 
              WHEN da.allocation_status = 'partial' THEN 1
              WHEN da.allocation_status = 'full' THEN 2
              ELSE 3
          END,
          da.delivery_date ASC NULLS LAST,
          da.priority ASC,
          da.created_at ASC
      `,
      [orgId, productId || null]
    );

    return res.status(200).json({
      success: true,
      message: "Dispatch allocations fetched successfully",
      total: result.rows.length,
      data: result.rows,
    });

  } catch (err) {
    console.error("GET PRODUCT DISPATCH ALLOCATIONS ERROR:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch dispatch allocations",
      error: err.message,
    });
  }
};

// Get pending sales orders (not yet allocated)
const getPendingSalesOrders = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const productId = req.query.productId || null;

    const result = await pool.query(
      `
      SELECT DISTINCT
        so.id,
        so.so_number,
        so.customer_id,
        so.customer_name,
        so.delivery_date,
        so.created_at,
        soi.product_id,
        soi.product_name,
        soi.qty,
        soi.uom_symbol,
        soi.rate
      FROM sales_orders so
      LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
      LEFT JOIN dispatch_allocations da ON da.sales_order_id = so.id AND da.is_deleted = false
      WHERE so.org_id = $1
        AND so.is_deleted = false
        AND so.approval_status = 'Approved'
        AND (da.id IS NULL OR da.allocated_qty = 0 OR da.allocated_qty < soi.qty)
        AND ($2::int IS NULL OR soi.product_id = $2)
      ORDER BY so.delivery_date ASC NULLS LAST, so.created_at ASC
      `,
      [orgId, productId || null]
    );

    return res.status(200).json({
      success: true,
      message: "Pending sales orders fetched successfully",
      data: result.rows,
    });

  } catch (err) {
    console.error("GET PENDING SALES ORDERS ERROR:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch pending sales orders",
      error: err.message,
    });
  }
};

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

    const result = await pool.query(
      `
      SELECT

          da.id,

          da.sales_order_id,

          da.sales_order_number,

          da.customer_id,

          da.customer_name,

          da.required_qty,

          da.allocated_qty,

          da.balance_qty,

          da.delivery_date,

          da.priority,

          da.vehicle_assigned,

          da.gate_pass_generated,

          da.shipment_completed,

          da.allocation_status,

          pim.id AS product_id,

          pim.item_name,

          pim.uom,

          stock.available_qty,

          stock.reserved_qty,

          ROUND(
              (
                  da.allocated_qty /
                  NULLIF(da.required_qty, 0)
              ) * 100,
              0
          ) AS allocation_percent

      FROM dispatch_allocations da

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

      LEFT JOIN inventory_stock stock
          ON stock.product_id = da.product_id
          AND stock.org_id = da.org_id

      WHERE da.org_id = $1

      AND da.vehicle_assigned = true

      AND da.gate_pass_generated = false

      AND da.shipment_completed = false

      AND da.allocated_qty > 0

      ORDER BY

          da.delivery_date ASC,

          da.priority ASC,

          da.created_at ASC
      `,
      [orgId],
    );

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error(err);

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


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

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

//     const org_id = req.session.user.orgId;
//     const created_by = req.session.user.id;

//     const {
//       allocation_id,
//       sales_order_id,
//       sales_order_number,
//       customer_name,
//       product_id,
//       product_name,
//       allocated_qty,
//       dispatch_qty,
//       uom,
//       transport_mode,
//       transport_agency_id,
//       vehicle_type,
//       vehicle_number,
//       vehicle_capacity,
//       driver_name,
//       driver_contact_number,
//       driver_license_number,
//       freight_amount,
//       lr_number,
//       seal_number,
//       dispatch_datetime,
//       expected_delivery_datetime,
//       density,
//       temperature,
//       next_dispatch_date,
//       "Hazard Classification": hazard_classification,
//       "PPE Required": ppe_required,
//       "Emergency Contact Person": emergency_contact_person,
//       "Emergency Contact Number": emergency_contact_number,
//       "Safety Instructions Issued": safety_instructions_issued,
//       "Fire Safety Equipment Avail": fire_safety_equipment,
//       "Spill Kit Availability": spill_kit_available,
//     } = req.body;

//     // =========================
//     // FETCH SALES ORDER SHIPPING ADDRESS
//     // =========================
//     const soResult = await client.query(
//       `
//       SELECT
//         c.shipping_address,
//         c.ship_city,
//         c.ship_state,
//         c.ship_pin_code,
//         c.gst AS shipping_gst,
//         c.customer_code
//       FROM sales_orders so
//       LEFT JOIN customers c
//         ON c.customer_code = so.customer_id
//       WHERE so.id = $1
//       `,
//       [sales_order_id],
//     );

//     const shipping = soResult.rows[0];

//     // =========================
//     // GENERATE SHIPMENT NUMBER
//     // =========================
//     const shipment_number = `SHP-${Date.now()}-${Math.floor(Math.random() * 1000)}`;

//     // =========================
//     // INSERT SHIPMENT HEADER
//     // =========================
//     const shipmentResult = await client.query(
//       `
//       INSERT INTO shipment_dispatches (
//         org_id, shipment_number, sales_order_id, sales_order_number,
//         allocation_id, product_id, product_name, customer_name,
//         shipping_address, shipping_city, shipping_state, shipping_pincode, shipping_gst,
//         transport_mode, transport_agency_id, vehicle_type, vehicle_number, vehicle_capacity,
//         driver_name, driver_contact_number, driver_license_number,
//         freight_amount, lr_number, seal_number,
//         dispatch_datetime, expected_delivery_datetime,
//         density, dispatch_temperature,
//         hazard_class, ppe_required,
//         emergency_contact_person, emergency_contact,
//         safety_instructions_issued, fire_safety_equipment, spill_kit_available,
//         dispatch_qty, allocated_qty, uom,
//         shipment_status, created_by
//       )
//       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,$28,
//         $29,$30,
//         $31,$32,
//         $33,$34,$35,
//         $36,$37,$38,
//         $39,$40
//       )
//       RETURNING *;
//       `,
//       [
//         org_id, shipment_number, sales_order_id, sales_order_number, allocation_id,
//         product_id, product_name, customer_name,
//         shipping?.shipping_address, shipping?.shipping_city, shipping?.shipping_state, 
//         shipping?.shipping_pincode, shipping?.shipping_gst,
//         transport_mode, transport_agency_id, vehicle_type, vehicle_number, vehicle_capacity,
//         driver_name, driver_contact_number, driver_license_number,
//         freight_amount || null, lr_number, seal_number,
//         dispatch_datetime, expected_delivery_datetime,
//         density || null, temperature || null,
//         hazard_classification, JSON.stringify(ppe_required || []),
//         emergency_contact_person, emergency_contact_number,
//         safety_instructions_issued || false, fire_safety_equipment || false, spill_kit_available || false,
//         dispatch_qty, allocated_qty, uom,
//         "Pending", created_by,
//       ],
//     );

//     const shipment = shipmentResult.rows[0];

//     // =========================
//     // INSERT SHIPMENT ITEM
//     // =========================
//     await client.query(
//       `
//       INSERT INTO shipment_dispatch_items (
//         shipment_id, allocation_id, product_id, product_name,
//         allocated_qty, dispatch_qty, uom
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7)
//       `,
//       [
//         shipment.id, allocation_id, product_id, product_name,
//         allocated_qty, dispatch_qty, uom,
//       ],
//     );

//     // =========================
//     // UPDATE ALLOCATION - ONLY MARK VEHICLE ASSIGNED
//     // DO NOT UPDATE balance_qty, allocation_status, or total_dispatched_qty
//     // =========================
//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET 
//         vehicle_assigned = true,
//         shipment_completed = false,
//         updated_at = CURRENT_TIMESTAMP
//       WHERE id = $1
//       `,
//       [allocation_id],
//     );

//     // =========================
//     // UPDATE SALES ORDER DISPATCH DATE
//     // =========================
//     await client.query(
//       `
//       UPDATE sales_orders
//       SET dispatch_schedule_date = $1
//       WHERE id = $2
//       `,
//       [dispatch_datetime || null, sales_order_id],
//     );

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

//     return res.status(201).json({
//       success: true,
//       message: "Shipment created successfully",
//       data: {
//         shipment: shipment,
//         allocation_id: allocation_id
//       },
//     });

//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("CREATE SHIPMENT ERROR:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Internal server error",
//       error: error.message,
//     });
//   } finally {
//     client.release();
//   }
// };


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

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

//     const org_id = req.session.user.orgId;
//     const created_by = req.session.user.id;

//     const {
//       allocation_id,
//       sales_order_id,
//       sales_order_number,

//       customer_name,

//       product_id,
//       product_name,
//       allocated_qty,
//       dispatch_qty,
//       uom,

//       transport_mode,

//       transport_agency_id,
//       vehicle_type,
//       vehicle_number,
//       vehicle_capacity,

//       driver_name,
//       driver_contact_number,
//       driver_license_number,

//       freight_amount,
//       lr_number,
//       seal_number,

//       dispatch_datetime,
//       expected_delivery_datetime,

//       density,
//       temperature,
//       next_dispatch_date,

//       "Hazard Classification": hazard_classification,
//       "PPE Required": ppe_required,
//       "Emergency Contact Person": emergency_contact_person,
//       "Emergency Contact Number": emergency_contact_number,
//       "Safety Instructions Issued": safety_instructions_issued,
//       "Fire Safety Equipment Avail": fire_safety_equipment,
//       "Spill Kit Availability": spill_kit_available,
//     } = req.body;

//     // =========================
//     // FETCH SALES ORDER SHIPPING ADDRESS
//     // =========================

//     // const soResult = await client.query(
//     //   `
//     //   SELECT
//     //     shipping_address,
//     //     shipping_city,
//     //     shipping_state,
//     //     shipping_pincode,
//     //     shipping_gst
//     //   FROM sales_orders
//     //   WHERE id = $1
//     //   `,
//     //   [sales_order_id]
//     // );

//     const soResult = await client.query(
//       `
//   SELECT
//     c.shipping_address,
//     c.ship_city,
//     c.ship_state,
//     c.ship_pin_code,
//     c.gst AS shipping_gst,
//     c.customer_code

//   FROM sales_orders so

//   LEFT JOIN customers c
//     ON c.customer_code = so.customer_id

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

//     const shipping = soResult.rows[0];

//     // =========================
//     // GENERATE SHIPMENT NUMBER
//     // =========================

//     const shipment_number = `SHP-${Date.now()}`;

//     // =========================
//     // INSERT SHIPMENT HEADER
//     // =========================

//     // const shipmentResult = await client.query(
//     //   `
//     //   INSERT INTO shipment_dispatches (
//     //     org_id,

//     //     shipment_number,

//     //     sales_order_id,
//     //     sales_order_number,

//     //     allocation_id,

//     //     customer_name,

//     //     shipping_address,
//     //     shipping_city,
//     //     shipping_state,
//     //     shipping_pincode,
//     //     shipping_gst,

//     //     transport_mode,

//     //     transport_agency_id,

//     //     vehicle_type,
//     //     vehicle_number,
//     //     vehicle_capacity,

//     //     driver_name,
//     //     driver_contact_number,
//     //     driver_license_number,

//     //     freight_amount,
//     //     lr_number,
//     //     seal_number,

//     //     dispatch_datetime,
//     //     expected_delivery_datetime,

//     //     density,
//     //     dispatch_temperature,

//     //     hazard_class,
//     //     ppe_required,

//     //     emergency_contact_person,
//     //     emergency_contact,

//     //     safety_instructions_issued,
//     //     fire_safety_equipment,
//     //     spill_kit_available,

//     //     shipment_status,

//     //     created_by
//     //   )

//     //   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,$28,
//     //     $29,$30,
//     //     $31,$32,$33,
//     //     $34,
//     //     $35
//     //   )

//     //   RETURNING *;
//     //   `,
//     //   [
//     //     org_id,

//     //     shipment_number,

//     //     sales_order_id,
//     //     sales_order_number,

//     //     allocation_id,

//     //     customer_name,

//     //     shipping?.shipping_address,
//     //     shipping?.shipping_city,
//     //     shipping?.shipping_state,
//     //     shipping?.shipping_pincode,
//     //     shipping?.shipping_gst,

//     //     transport_mode,

//     //     transport_agency_id,

//     //     vehicle_type,
//     //     vehicle_number,
//     //     vehicle_capacity,

//     //     driver_name,
//     //     driver_contact_number,
//     //     driver_license_number,

//     //     freight_amount,
//     //     lr_number,
//     //     seal_number,

//     //     dispatch_datetime,
//     //     expected_delivery_datetime,

//     //     density,
//     //     temperature,

//     //     hazard_classification,
//     //     JSON.stringify(ppe_required || []),

//     //     emergency_contact_person,
//     //     emergency_contact_number,

//     //     safety_instructions_issued,
//     //     fire_safety_equipment,
//     //     spill_kit_available,

//     //     "Pending",

//     //     created_by,
//     //   ],
//     // );
//     const shipmentResult = await client.query(
//       `
//   INSERT INTO shipment_dispatches (
//     org_id,

//     shipment_number,

//     sales_order_id,
//     sales_order_number,

//     allocation_id,

//     product_id,
//     product_name,

//     customer_name,

//     shipping_address,
//     shipping_city,
//     shipping_state,
//     shipping_pincode,
//     shipping_gst,

//     transport_mode,

//     transport_agency_id,

//     vehicle_type,
//     vehicle_number,
//     vehicle_capacity,

//     driver_name,
//     driver_contact_number,
//     driver_license_number,

//     freight_amount,
//     lr_number,
//     seal_number,

//     dispatch_datetime,
//     expected_delivery_datetime,

//     density,
//     dispatch_temperature,

//     hazard_class,
//     ppe_required,

//     emergency_contact_person,
//     emergency_contact,

//     safety_instructions_issued,
//     fire_safety_equipment,
//     spill_kit_available,

//     shipment_status,

//     created_by
//   )

//   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,$28,
//     $29,$30,
//     $31,$32,
//     $33,$34,$35,
//     $36,
//     $37
//   )

//   RETURNING *;
//   `,
//       [
//         org_id,

//         shipment_number,

//         sales_order_id,
//         sales_order_number,

//         allocation_id,

//         product_id,
//         product_name,

//         customer_name,

//         shipping?.shipping_address,
//         shipping?.shipping_city,
//         shipping?.shipping_state,
//         shipping?.shipping_pincode,
//         shipping?.shipping_gst,

//         transport_mode,

//         transport_agency_id,

//         vehicle_type,
//         vehicle_number,
//         vehicle_capacity,

//         driver_name,
//         driver_contact_number,
//         driver_license_number,

//         freight_amount || null,
//         lr_number,
//         seal_number,

//         dispatch_datetime,
//         expected_delivery_datetime,

//         density || null,
//         temperature || null,

//         hazard_classification,
//         JSON.stringify(ppe_required || []),

//         emergency_contact_person,
//         emergency_contact_number,

//         safety_instructions_issued,
//         fire_safety_equipment,
//         spill_kit_available,

//         "Pending",

//         created_by,
//       ],
//     );

//     const shipment = shipmentResult.rows[0];

//     // =========================
//     // INSERT SHIPMENT ITEM
//     // =========================

//     await client.query(
//       `
//       INSERT INTO shipment_dispatch_items (
//         shipment_id,
//         allocation_id,

//         product_id,
//         product_name,

//         allocated_qty,
//         dispatch_qty,

//         uom
//       )

//       VALUES (
//         $1,$2,$3,$4,$5,$6,$7
//       )
//       `,
//       [
//         shipment.id,
//         allocation_id,

//         product_id,
//         product_name,

//         allocated_qty,
//         dispatch_qty,

//         uom,
//       ],
//     );

//     // =========================
//     // UPDATE ALLOCATION STATUS
//     // =========================

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

//       vehicle_assigned = true,

//       shipment_completed =
//         CASE
//           WHEN (balance_qty - $1) <= 0
//           THEN true
//           ELSE false
//         END,

//       next_dispatch_date = $2,

//       updated_at = CURRENT_TIMESTAMP

//   WHERE id = $3
//   `,
//   [
//     dispatch_qty,

//     next_dispatch_date || null,

//     allocation_id,
//   ],
// );
//     await client.query(
//       `
//   UPDATE sales_orders
//   SET
//     dispatch_schedule_date = $1
//   WHERE id = $2
//   `,
//       [dispatch_datetime || null, sales_order_id],
//     );

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

//     return res.status(201).json({
//       success: true,
//       message: "Shipment created successfully",
//       data: shipment,
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("CREATE SHIPMENT ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Internal server error",
//       error: error.message,
//     });
//   } finally {
//     client.release();
//   }
// };
const createShipment = async (req, res) => {
  const client = await pool.connect();

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

    const org_id = req.session.user.orgId;
    const created_by = req.session.user.id;

    const {
      allocation_id,
      sales_order_id,
      sales_order_number,
      customer_name,
      product_id,
      product_name,
      allocated_qty,
      dispatch_qty,
      uom,
      transport_mode,
      transport_agency_id,
      vehicle_type,
      vehicle_number,
      vehicle_capacity,
      driver_name,
      driver_contact_number,
      driver_license_number,
      freight_amount,
      lr_number,
      seal_number,
      dispatch_datetime,
      expected_delivery_datetime,
      density,
      temperature,
      next_dispatch_date,
      "Hazard Classification": hazard_classification,
      "PPE Required": ppe_required,
      "Emergency Contact Person": emergency_contact_person,
      "Emergency Contact Number": emergency_contact_number,
      "Safety Instructions Issued": safety_instructions_issued,
      "Fire Safety Equipment Avail": fire_safety_equipment,
      "Spill Kit Availability": spill_kit_available,
    } = req.body;

    // =========================
    // FETCH ALLOCATION DETAILS
    // =========================
    const allocationDetails = await client.query(
      `
      SELECT 
        id,
        required_qty,
        allocated_qty,
        COALESCE(total_dispatched_qty, 0) as total_dispatched
      FROM dispatch_allocations
      WHERE id = $1 AND org_id = $2
      `,
      [allocation_id, org_id]
    );

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

    const allocation = allocationDetails.rows[0];
    const requiredQty = Number(allocation.required_qty);
    const currentAllocatedQty = Number(allocation.allocated_qty);
    const currentDispatched = Number(allocation.total_dispatched);
    const requestedQty = Number(dispatch_qty);
    const newTotalDispatched = currentDispatched + requestedQty;

    // =========================
    // CHECK IF THIS IS THE FINAL SHIPMENT
    // =========================
    // Condition: required_qty == allocated_qty AND newTotalDispatched == allocated_qty
    const isFullyAllocated = currentAllocatedQty >= requiredQty;
    const willBeFullyDispatched = newTotalDispatched >= currentAllocatedQty;
    
    let shipmentPendingStatus = false;
    
    if (isFullyAllocated && willBeFullyDispatched) {
      // This shipment will complete the dispatch
      shipmentPendingStatus = true;
      console.log("This is the final shipment - marking as shipment_pending = true");
    }

    // =========================
    // FETCH SALES ORDER SHIPPING ADDRESS
    // =========================
    const soResult = await client.query(
      `
      SELECT
        c.shipping_address,
        c.ship_city,
        c.ship_state,
        c.ship_pin_code,
        c.gst AS shipping_gst,
        c.customer_code
      FROM sales_orders so
      LEFT JOIN customers c
        ON c.customer_code = so.customer_id
      WHERE so.id = $1
      `,
      [sales_order_id],
    );

    const shipping = soResult.rows[0];

    // =========================
    // GENERATE SHIPMENT NUMBER
    // =========================
    const shipment_number = `SHP-${Date.now()}-${Math.floor(Math.random() * 1000)}`;

    // =========================
    // INSERT SHIPMENT HEADER
    // =========================
    const shipmentResult = await client.query(
      `
      INSERT INTO shipment_dispatches (
        org_id, shipment_number, sales_order_id, sales_order_number,
        allocation_id, product_id, product_name, customer_name,
        shipping_address, shipping_city, shipping_state, shipping_pincode, shipping_gst,
        transport_mode, transport_agency_id, vehicle_type, vehicle_number, vehicle_capacity,
        driver_name, driver_contact_number, driver_license_number,
        freight_amount, lr_number, seal_number,
        dispatch_datetime, expected_delivery_datetime,
        density, dispatch_temperature,
        hazard_class, ppe_required,
        emergency_contact_person, emergency_contact,
        safety_instructions_issued, fire_safety_equipment, spill_kit_available,
        dispatch_qty, allocated_qty, uom,
        shipment_status, created_by
      )
      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,$28,
        $29,$30,
        $31,$32,
        $33,$34,$35,
        $36,$37,$38,
        $39,$40
      )
      RETURNING *;
      `,
      [
        org_id, shipment_number, sales_order_id, sales_order_number, allocation_id,
        product_id, product_name, customer_name,
        shipping?.shipping_address, shipping?.shipping_city, shipping?.shipping_state, 
        shipping?.shipping_pincode, shipping?.shipping_gst,
        transport_mode, transport_agency_id, vehicle_type, vehicle_number, vehicle_capacity,
        driver_name, driver_contact_number, driver_license_number,
        freight_amount || null, lr_number, seal_number,
        dispatch_datetime, expected_delivery_datetime,
        density || null, temperature || null,
        hazard_classification, JSON.stringify(ppe_required || []),
        emergency_contact_person, emergency_contact_number,
        safety_instructions_issued || false, fire_safety_equipment || false, spill_kit_available || false,
        requestedQty, currentAllocatedQty, uom,
        "Pending", created_by,
      ],
    );

    const shipment = shipmentResult.rows[0];

    // =========================
    // INSERT SHIPMENT ITEM
    // =========================
    await client.query(
      `
      INSERT INTO shipment_dispatch_items (
        shipment_id, allocation_id, product_id, product_name,
        allocated_qty, dispatch_qty, uom
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7)
      `,
      [
        shipment.id, allocation_id, product_id, product_name,
        currentAllocatedQty, requestedQty, uom,
      ],
    );

    // =========================
    // UPDATE ALLOCATION
    // =========================
    await client.query(
      `
      UPDATE dispatch_allocations
      SET 
        vehicle_assigned = true,
        shipment_completed = false,
        shipment_pending = $1,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $2
      `,
      [shipmentPendingStatus, allocation_id],
    );

    // =========================
    // UPDATE SALES ORDER DISPATCH DATE
    // =========================
    await client.query(
      `
      UPDATE sales_orders
      SET dispatch_schedule_date = $1
      WHERE id = $2
      `,
      [dispatch_datetime || null, sales_order_id],
    );

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: shipmentPendingStatus 
        ? "Final shipment created successfully. Dispatch will be completed."
        : "Shipment created successfully",
      data: {
        shipment: shipment,
        allocation_id: allocation_id,
        is_final_shipment: shipmentPendingStatus
      },
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("CREATE SHIPMENT ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Internal server error",
      error: error.message,
    });
  } finally {
    client.release();
  }
};


const manualAllocateStock = async (req, res) => {
  const orgId = req.session?.user?.orgId;
  const userId = req.session?.user?.id;
  const client = await pool.connect();

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

    const { sales_order_id, allocate_qty, remarks, product_id } = req.body;
    const qty = Number(allocate_qty || 0);

    // Basic validation
    if (qty <= 0) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: "Invalid allocation quantity. Please enter a quantity greater than 0.",
      });
    }

    // =====================================================
    // GET SALES ORDER DETAILS
    // =====================================================
    const soResult = await client.query(
      `
      SELECT 
        so.id,
        so.so_number,
        so.customer_id,
        so.customer_name,
        so.delivery_date,
        so.org_id,
        soi.product_id,
        soi.product_name,
        soi.qty as required_qty,
        soi.uom_symbol
      FROM sales_orders so
      LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
      WHERE so.id = $1 AND so.org_id = $2 AND so.is_deleted = false
      LIMIT 1
      `,
      [sales_order_id, orgId]
    );

    if (soResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        success: false,
        message: "Sales Order not found or has been deleted.",
      });
    }

    const so = soResult.rows[0];
    const actualProductId = product_id || so.product_id;

    // =====================================================
    // GET TOTAL ALLOCATED SO FAR
    // =====================================================
    const totalAllocatedResult = await client.query(
      `
      SELECT 
        COALESCE(SUM(allocated_qty), 0) as total_allocated,
        COUNT(*) as trip_count
      FROM dispatch_allocations
      WHERE sales_order_id = $1 
        AND org_id = $2 
        AND is_deleted = false
      `,
      [sales_order_id, orgId]
    );

    const totalAllocatedSoFar = Number(totalAllocatedResult.rows[0].total_allocated);
    const tripCount = Number(totalAllocatedResult.rows[0].trip_count);
    const requiredQty = Number(so.required_qty);
    const nextTripNumber = tripCount + 1;

    // =====================================================
    // CHECK IF EXCEEDS REQUIRED QUANTITY
    // =====================================================
    let stockWarning = null;
    if (totalAllocatedSoFar + qty > requiredQty) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Cannot allocate beyond required quantity. Required: ${requiredQty} ${so.uom_symbol}, Already allocated: ${totalAllocatedSoFar} ${so.uom_symbol}, Requested: ${qty} ${so.uom_symbol}`,
        data: {
          required_qty: requiredQty,
          already_allocated: totalAllocatedSoFar,
          requested_qty: qty,
          max_can_allocate: requiredQty - totalAllocatedSoFar,
          uom: so.uom_symbol
        }
      });
    }

    // =====================================================
    // CHECK INVENTORY STOCK - JUST FOR WARNING (NO BLOCK)
    // =====================================================
    let availableStock = 0;
    try {
      const stockResult = await client.query(
        `
        SELECT * FROM inventory_stock
        WHERE product_id = $1 AND org_id = $2 AND is_deleted = false
        `,
        [actualProductId, orgId]
      );

      if (stockResult.rows.length > 0) {
        const stock = stockResult.rows[0];
        availableStock = Number(stock.available_qty || 0);
        
        // Just create warning, don't block allocation
        if (availableStock < qty) {
          stockWarning = `Warning: Only ${availableStock} ${so.uom_symbol} available in stock. Allocating ${qty} ${so.uom_symbol} anyway.`;
          console.log(stockWarning);
        }
      } else {
        stockWarning = `Warning: No inventory record found for ${so.product_name}. Proceeding with allocation anyway.`;
        console.log(stockWarning);
      }
    } catch (stockErr) {
      // Don't block allocation even if stock check fails
      stockWarning = `Warning: Could not verify stock availability. Proceeding with allocation.`;
      console.log(stockErr.message);
    }

    // =====================================================
    // CALCULATE NEW BALANCE
    // =====================================================
    const newTotalAllocated = totalAllocatedSoFar + qty;
    const newBalanceQty = requiredQty - newTotalAllocated;
    
    let allocationStatus = "pending";
    if (newBalanceQty === 0) allocationStatus = "completed";
    else if (newTotalAllocated > 0) allocationStatus = "partial";

    // =====================================================
    // CREATE NEW ALLOCATION RECORD
    // =====================================================
    const insertResult = await client.query(
      `
      INSERT INTO dispatch_allocations (
        org_id, 
        product_id, 
        sales_order_id, 
        sales_order_number,
        customer_id, 
        customer_name, 
        required_qty,
        allocated_qty,
        balance_qty,
        available_stock,
        allocation_status,
        delivery_date,
        priority,
        remarks,
        created_by,
        updated_by,
        trip_number,
        allow_multiple_shipments,
        vehicle_assigned,
        is_deleted
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20)
      RETURNING *
      `,
      [
        orgId,
        actualProductId,
        so.id,
        so.so_number,
        so.customer_id,
        so.customer_name,
        requiredQty,
        qty,
        newBalanceQty,
        availableStock - qty,  // May go negative - that's fine
        allocationStatus,
        so.delivery_date || null,
        1,
        remarks || `Trip #${nextTripNumber}: Allocated ${qty} ${so.uom_symbol}`,
        userId,
        userId,
        nextTripNumber,
        true,
        false,
        false
      ]
    );

    const newAllocation = insertResult.rows[0];

    // =====================================================
    // UPDATE INVENTORY STOCK - Only if stock record exists
    // =====================================================
    try {
      await client.query(
        `
        UPDATE inventory_stock
        SET available_qty = available_qty - $1,
            reserved_qty = COALESCE(reserved_qty, 0) + $1,
            updated_at = CURRENT_TIMESTAMP
        WHERE product_id = $2 AND org_id = $3
        `,
        [qty, actualProductId, orgId]
      );
    } catch (updateErr) {
      // Don't rollback if stock update fails - just log it
      console.error("Stock update failed but allocation continued:", updateErr.message);
    }

    // =====================================================
    // INSERT ALLOCATION HISTORY
    // =====================================================
    await client.query(
      `
      INSERT INTO dispatch_allocation_history (
        allocation_id, 
        sales_order_id, 
        product_id, 
        allocated_qty,
        balance_after, 
        allocation_type, 
        dispatch_status, 
        remarks, 
        created_by,
        trip_number
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
      `,
      [
        newAllocation.id, 
        sales_order_id, 
        actualProductId, 
        qty, 
        newBalanceQty, 
        "manual", 
        allocationStatus, 
        remarks || `Trip #${nextTripNumber} allocation`,
        userId,
        nextTripNumber
      ]
    );

    await client.query("COMMIT");

    // Return response with warning if any
    const responseData = {
      success: true,
      message: stockWarning || `Successfully allocated ${qty} ${so.uom_symbol} as Trip #${nextTripNumber}`,
      data: {
        allocation_id: newAllocation.id,
        trip_number: nextTripNumber,
        sales_order_id: newAllocation.sales_order_id,
        sales_order_number: so.so_number,
        product_name: so.product_name,
        allocated_qty: qty,
        total_allocated_so_far: newTotalAllocated,
        balance_qty: newBalanceQty,
        required_qty: requiredQty,
        allocation_status: allocationStatus,
        vehicle_assigned: false,
        uom: so.uom_symbol,
        available_stock_remaining: availableStock - qty,
        remarks: newAllocation.remarks,
        created_at: newAllocation.created_at
      }
    };

    // Add warning to response if exists
    if (stockWarning) {
      responseData.warning = stockWarning;
    }

    return res.status(200).json(responseData);

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("MANUAL ALLOCATION ERROR:", err);
    return res.status(500).json({
      success: false,
      message: "Manual allocation failed. Please try again.",
      error: process.env.NODE_ENV === 'development' ? err.message : undefined
    });
  } finally {
    client.release();
  }
};

// const manualAllocateStock = async (req, res) => {
//   const orgId = req.session?.user?.orgId;
//   const userId = req.session?.user?.id;
//   const client = await pool.connect();

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

//     const { sales_order_id, allocate_qty, remarks, product_id } = req.body;
//     const qty = Number(allocate_qty || 0);

//     if (qty <= 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Invalid allocation quantity",
//       });
//     }

//     // =====================================================
//     // GET SALES ORDER DETAILS
//     // =====================================================
//     const soResult = await client.query(
//       `
//       SELECT 
//         so.id,
//         so.so_number,
//         so.customer_id,
//         so.customer_name,
//         so.delivery_date,
//         so.org_id,
//         soi.product_id,
//         soi.product_name,
//         soi.qty,
//         soi.uom_symbol
//       FROM sales_orders so
//       LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
//       WHERE so.id = $1 AND so.org_id = $2
//       LIMIT 1
//       `,
//       [sales_order_id, orgId]
//     );

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

//     const so = soResult.rows[0];
//     const actualProductId = product_id || so.product_id;

//     // =====================================================
//     // GET INVENTORY STOCK FIRST (for validation)
//     // =====================================================
//     const stockResult = await client.query(
//       `
//       SELECT * FROM inventory_stock
//       WHERE product_id = $1 AND org_id = $2
//       `,
//       [actualProductId, orgId]
//     );

//     if (stockResult.rows.length === 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `No inventory record found for product: ${so.product_name}. Please check inventory setup.`,
//         available_stock: 0
//       });
//     }

//     const stock = stockResult.rows[0];
//     const availableStock = Number(stock.available_qty || 0);

//     // =====================================================
//     // CHECK EXISTING ALLOCATION
//     // =====================================================
//     let allocationResult = await client.query(
//       `
//       SELECT * FROM dispatch_allocations
//       WHERE sales_order_id = $1 
//       AND org_id = $2 
//       AND is_deleted = false
//       `,
//       [sales_order_id, orgId]
//     );

//     let allocation;
//     let existingAllocated = 0;
//     let requiredQty = so.qty;

//     if (allocationResult.rows.length === 0) {
//       // Create new allocation
//       const insertResult = await client.query(
//         `
//         INSERT INTO dispatch_allocations (
//           org_id, product_id, sales_order_id, sales_order_number,
//           customer_id, customer_name, required_qty, allocated_qty, 
//           balance_qty, available_stock, allocation_status, delivery_date,
//           priority, remarks, created_by, updated_by, allow_multiple_shipments
//         ) VALUES ($1, $2, $3, $4, $5, $6, $7, 0, $7, $8, 'waiting', 
//           $9, 1, $10, $11, $11, true)
//         RETURNING *
//         `,
//         [
//           orgId, actualProductId, so.id, so.so_number,
//           so.customer_id, so.customer_name, so.qty,
//           availableStock,
//           so.delivery_date || null,
//           remarks || "Manual allocation",
//           userId
//         ]
//       );
//       allocation = insertResult.rows[0];
//       existingAllocated = 0;
//     } else {
//       allocation = allocationResult.rows[0];
//       existingAllocated = Number(allocation.allocated_qty || 0);
//       requiredQty = Number(allocation.required_qty || so.qty);
//     }

//     // =====================================================
//     // VALIDATION: Only prevent allocation if it exceeds original required quantity
//     // =====================================================
//     if (existingAllocated + qty > requiredQty) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cannot allocate beyond required quantity. Required: ${requiredQty} ${so.uom_symbol}, Already allocated: ${existingAllocated} ${so.uom_symbol}, Requested: ${qty} ${so.uom_symbol}`,
//         required_qty: requiredQty,
//         already_allocated: existingAllocated,
//         requested_qty: qty,
//         can_allocate_max: requiredQty - existingAllocated
//       });
//     }

//     // =====================================================
//     // CALCULATE NEW VALUES
//     // =====================================================
//     const newAllocated = existingAllocated + qty;
//     const balanceQty = requiredQty - newAllocated;
    
//     let allocationStatus = "waiting";
//     if (balanceQty === 0) allocationStatus = "full";
//     else if (newAllocated > 0) allocationStatus = "partial";
    
//     let deliveryStatus = "Pending";
//     if (newAllocated > 0 && balanceQty > 0) deliveryStatus = "Partial";
//     if (balanceQty === 0) deliveryStatus = "Ready";

//     // =====================================================
//     // UPDATE ALLOCATION
//     // =====================================================
//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET allocated_qty = $1, balance_qty = $2, 
//           allocation_status = $3, delivery_status = $4,
//           available_stock = $5, updated_by = $6, updated_at = CURRENT_TIMESTAMP
//       WHERE id = $7
//       `,
//       [newAllocated, balanceQty, allocationStatus, deliveryStatus, 
//        availableStock - qty, userId, allocation.id]
//     );

//     // =====================================================
//     // UPDATE INVENTORY STOCK
//     // =====================================================
//     await client.query(
//       `
//       UPDATE inventory_stock
//       SET available_qty = GREATEST(available_qty - $1, 0),
//           reserved_qty = COALESCE(reserved_qty, 0) + $1,
//           updated_at = CURRENT_TIMESTAMP
//       WHERE product_id = $2 AND org_id = $3
//       `,
//       [qty, actualProductId, orgId]
//     );

//     // =====================================================
//     // INSERT HISTORY
//     // =====================================================
//     await client.query(
//       `
//       INSERT INTO dispatch_allocation_history (
//         allocation_id, sales_order_id, product_id, allocated_qty,
//         balance_after, allocation_type, dispatch_status, remarks, created_by
//       ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
//       `,
//       [allocation.id, sales_order_id, actualProductId, qty, 
//        balanceQty, "manual", allocationStatus, remarks || null, userId]
//     );

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

//     return res.json({
//       success: true,
//       message: `Successfully allocated ${qty} ${so.uom_symbol}`,
//       data: {
//         allocation_id: allocation.id,
//         sales_order_id: allocation.sales_order_id,
//         allocated_qty: newAllocated,
//         balance_qty: balanceQty,
//         allocation_status: allocationStatus,
//         delivery_status: deliveryStatus,
//         uom: so.uom_symbol,
//         product_name: so.product_name,
//         available_stock_remaining: availableStock - qty
//       }
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("MANUAL ALLOCATION ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Manual allocation failed: " + err.message,
//       error: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

// const manualAllocateStock = async (req, res) => {
//   const orgId = req.session?.user?.orgId;
//   const userId = req.session?.user?.id;
//   const client = await pool.connect();

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

//     const { sales_order_id, allocate_qty, remarks, product_id } = req.body;
//     const qty = Number(allocate_qty || 0);

//     if (qty <= 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Invalid allocation quantity",
//       });
//     }

//     // =====================================================
//     // GET SALES ORDER DETAILS
//     // =====================================================
//     const soResult = await client.query(
//       `
//       SELECT 
//         so.id,
//         so.so_number,
//         so.customer_id,
//         so.customer_name,
//         so.delivery_date,
//         so.org_id,
//         soi.product_id,
//         soi.product_name,
//         soi.qty,
//         soi.uom_symbol
//       FROM sales_orders so
//       LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
//       WHERE so.id = $1 AND so.org_id = $2
//       LIMIT 1
//       `,
//       [sales_order_id, orgId]
//     );

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

//     const so = soResult.rows[0];
//     const actualProductId = product_id || so.product_id;

//     // =====================================================
//     // GET INVENTORY STOCK FIRST (for validation)
//     // =====================================================
//     const stockResult = await client.query(
//       `
//       SELECT * FROM inventory_stock
//       WHERE product_id = $1 AND org_id = $2
//       `,
//       [actualProductId, orgId]
//     );

//     if (stockResult.rows.length === 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `No inventory record found for product: ${so.product_name}. Please check inventory setup.`,
//         available_stock: 0
//       });
//     }

//     const stock = stockResult.rows[0];
//     const availableStock = Number(stock.available_qty || 0);

//     // =====================================================
//     // CHECK EXISTING ALLOCATION
//     // =====================================================
//     let allocationResult = await client.query(
//       `
//       SELECT * FROM dispatch_allocations
//       WHERE sales_order_id = $1 
//       AND org_id = $2 
//       AND is_deleted = false
//       `,
//       [sales_order_id, orgId]
//     );

//     let allocation;
//     let existingAllocated = 0;
//     let requiredQty = so.qty;

//     if (allocationResult.rows.length === 0) {
//       // Create new allocation
//       const insertResult = await client.query(
//         `
//         INSERT INTO dispatch_allocations (
//           org_id, product_id, sales_order_id, sales_order_number,
//           customer_id, customer_name, required_qty, allocated_qty, 
//           balance_qty, available_stock, allocation_status, delivery_date,
//           priority, remarks, created_by, updated_by, allow_multiple_shipments
//         ) VALUES ($1, $2, $3, $4, $5, $6, $7, 0, $7, $8, 'waiting', 
//           $9, 1, $10, $11, $11, true)
//         RETURNING *
//         `,
//         [
//           orgId, actualProductId, so.id, so.so_number,
//           so.customer_id, so.customer_name, so.qty,
//           availableStock,
//           so.delivery_date || null,
//           remarks || "Manual allocation",
//           userId
//         ]
//       );
//       allocation = insertResult.rows[0];
//       existingAllocated = 0;
//     } else {
//       allocation = allocationResult.rows[0];
//       existingAllocated = Number(allocation.allocated_qty || 0);
//       requiredQty = Number(allocation.required_qty || so.qty);
//     }

//     const maxAllocatable = Math.min(availableStock, requiredQty - existingAllocated);

//     // =====================================================
//     // VALIDATE ALLOCATION QUANTITY
//     // =====================================================
//     if (qty > maxAllocatable) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cannot allocate ${qty} ${so.uom_symbol}. Maximum allocatable is ${maxAllocatable} ${so.uom_symbol}.`,
//         maxAllocatable: maxAllocatable,
//         available_stock: availableStock,
//         required_qty: requiredQty,
//         already_allocated: existingAllocated,
//         requested_qty: qty
//       });
//     }

//     // =====================================================
//     // CALCULATE NEW VALUES
//     // =====================================================
//     const newAllocated = existingAllocated + qty;
//     const balanceQty = requiredQty - newAllocated;
    
//     let allocationStatus = "waiting";
//     if (balanceQty === 0) allocationStatus = "full";
//     else if (newAllocated > 0) allocationStatus = "partial";
    
//     let deliveryStatus = "Pending";
//     if (newAllocated > 0 && balanceQty > 0) deliveryStatus = "Partial";
//     if (balanceQty === 0) deliveryStatus = "Ready";

//     // =====================================================
//     // UPDATE ALLOCATION
//     // =====================================================
//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET allocated_qty = $1, balance_qty = $2, 
//           allocation_status = $3, delivery_status = $4,
//           available_stock = $5, updated_by = $6, updated_at = CURRENT_TIMESTAMP
//       WHERE id = $7
//       `,
//       [newAllocated, balanceQty, allocationStatus, deliveryStatus, 
//        availableStock - qty, userId, allocation.id]
//     );

//     // =====================================================
//     // UPDATE INVENTORY STOCK
//     // =====================================================
//     await client.query(
//       `
//       UPDATE inventory_stock
//       SET available_qty = GREATEST(available_qty - $1, 0),
//           reserved_qty = COALESCE(reserved_qty, 0) + $1,
//           updated_at = CURRENT_TIMESTAMP
//       WHERE product_id = $2 AND org_id = $3
//       `,
//       [qty, actualProductId, orgId]
//     );

//     // =====================================================
//     // INSERT HISTORY
//     // =====================================================
//     await client.query(
//       `
//       INSERT INTO dispatch_allocation_history (
//         allocation_id, sales_order_id, product_id, allocated_qty,
//         balance_after, allocation_type, dispatch_status, remarks, created_by
//       ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
//       `,
//       [allocation.id, sales_order_id, actualProductId, qty, 
//        balanceQty, "manual", allocationStatus, remarks || null, userId]
//     );

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

//     return res.json({
//       success: true,
//       message: `Successfully allocated ${qty} ${so.uom_symbol}`,
//       data: {
//         allocation_id: allocation.id,
//         sales_order_id: allocation.sales_order_id,
//         allocated_qty: newAllocated,
//         balance_qty: balanceQty,
//         allocation_status: allocationStatus,
//         delivery_status: deliveryStatus,
//         uom: so.uom_symbol,
//         product_name: so.product_name,
//         available_stock_remaining: availableStock - qty
//       }
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("MANUAL ALLOCATION ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Manual allocation failed: " + err.message,
//       error: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

// Get allocations for sales orders (to show allocated status)
const getAllocationsBySalesOrder = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const { sales_order_id } = req.params;

    let query = `
      SELECT 
        da.*,
        pim.uom,
        pim.uom_symbol
      FROM dispatch_allocations da
      LEFT JOIN procurement_itemmaster pim ON pim.id = da.product_id
      WHERE da.org_id = $1 AND da.is_deleted = false
    `;
    const params = [orgId];

    if (sales_order_id) {
      query += ` AND da.sales_order_id = $2`;
      params.push(sales_order_id);
    }

    query += ` ORDER BY da.created_at DESC`;

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

    res.json({
      success: true,
      data: result.rows
    });
  } catch (error) {
    console.error("GET ALLOCATIONS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};



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

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

    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;
    const { allocation_id, cancel_qty, remarks } = req.body;

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

    const qty = Number(cancel_qty || 0);

    if (qty <= 0) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: "Invalid cancel qty",
      });
    }

    // =====================================================
    // GET ALLOCATION DETAILS
    // =====================================================

    const allocationResult = await client.query(
      `
      SELECT 
        da.*
      FROM dispatch_allocations da
      WHERE da.id = $1
        AND da.org_id = $2
        AND COALESCE(da.is_deleted, false) = false
      `,
      [allocation_id, orgId],
    );

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

    const allocation = allocationResult.rows[0];
    
    // =====================================================
    // CURRENT VALUES
    // =====================================================

    const existingAllocated = Number(allocation.allocated_qty || 0);
    const requiredQty = Number(allocation.required_qty || 0);
    const totalDispatched = Number(allocation.total_dispatched_qty || 0);
    const vehicleAssigned = allocation.vehicle_assigned || false;

    // Debug logs
    console.log("=== CANCEL ALLOCATION DEBUG ===");
    console.log("Allocation ID:", allocation_id);
    console.log("Existing Allocated Qty:", existingAllocated);
    console.log("Required Qty:", requiredQty);
    console.log("Total Dispatched:", totalDispatched);
    console.log("Vehicle Assigned:", vehicleAssigned);
    console.log("Requested Cancel Qty:", qty);
    console.log("=================================");

    // =====================================================
    // CHECK IF VEHICLE IS ASSIGNED
    // =====================================================
    if (vehicleAssigned === true) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: "Cannot cancel allocation. Vehicle has already been assigned to this dispatch.",
      });
    }

    // =====================================================
    // VALIDATE CANCEL QTY
    // =====================================================

    if (qty > existingAllocated) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Cancel qty (${qty}) exceeds allocated qty (${existingAllocated})`,
      });
    }

    // Prevent canceling already dispatched quantity
    const cancelableQty = existingAllocated - totalDispatched;
    if (qty > cancelableQty) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Cannot cancel ${qty}. Only ${cancelableQty} is available (${totalDispatched} already dispatched)`,
      });
    }

    // =====================================================
    // CALCULATIONS
    // =====================================================

    const finalAllocated = existingAllocated - qty;
    const finalBalance = requiredQty - finalAllocated;

    // =====================================================
    // STATUS
    // =====================================================

    let status = "waiting";
    if (finalAllocated === 0) {
      status = "not_allocated";
    } else if (finalAllocated < requiredQty) {
      status = "partial";
    } else {
      status = "full";
    }

    // =====================================================
    // UPDATE ALLOCATION
    // =====================================================

    await client.query(
      `
      UPDATE dispatch_allocations
      SET
          allocated_qty = $1,
          balance_qty = $2,
          allocation_status = $3,
          cancel_remarks = $4,
          updated_by = $5,
          vehicle_assigned = false,
          shipment_completed = false,
          gate_pass_generated = false,
          shipment_pending = false,
          updated_at = CURRENT_TIMESTAMP
          ${finalAllocated === 0 ? `, is_deleted = false` : ``}
      WHERE id = $6
      `,
      [
        finalAllocated,
        finalBalance,
        status,
        remarks || null,
        userId,
        allocation_id,
      ],
    );

    // =====================================================
    // REVERT INVENTORY STOCK
    // =====================================================

    await client.query(
      `
      UPDATE inventory_stock
      SET
          available_qty = available_qty + $1,
          reserved_qty = GREATEST(COALESCE(reserved_qty,0) - $1, 0),
          updated_at = CURRENT_TIMESTAMP
      WHERE product_id = $2
      AND org_id = $3
      `,
      [qty, allocation.product_id, orgId],
    );

    // =====================================================
    // HISTORY
    // =====================================================

    await client.query(
      `
      INSERT INTO dispatch_allocation_history
      (
          allocation_id,
          sales_order_id,
          product_id,
          allocated_qty,
          balance_after,
          allocation_type,
          dispatch_status,
          remarks,
          created_by,
          created_at
      )
      VALUES
      (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, CURRENT_TIMESTAMP
      )
      `,
      [
        allocation.id,
        allocation.sales_order_id,
        allocation.product_id,
        -qty,
        finalBalance,
        "cancel",
        status,
        remarks || "Allocation cancelled",
        userId,
      ],
    );

    // =====================================================
    // COMMIT
    // =====================================================

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: finalAllocated === 0
        ? "Allocation fully cancelled. Stock returned to inventory."
        : `Allocation reduced by ${qty}.`,
      data: {
        new_allocated_qty: finalAllocated,
        allocation_status: status,
        balance_qty: finalBalance,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CANCEL ALLOCATION ERROR:", err);
    return res.status(500).json({
      success: false,
      message: "Cancel allocation failed",
      error: err.message,
    });
  } finally {
    client.release();
  }
};
// const cancelAllocation = async (req, res) => {
//   const client = await pool.connect();

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

//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;
//     const { allocation_id, cancel_qty, remarks } = req.body;

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

//     const qty = Number(cancel_qty || 0);

//     if (qty <= 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Invalid cancel qty",
//       });
//     }

//     // =====================================================
//     // GET ALLOCATION WITH SHIPMENT DETAILS
//     // =====================================================

//     const allocationResult = await client.query(
//       `
//       SELECT 
//         da.*,
//         sd.id as shipment_id,
//         sd.shipment_status,
//         sd.shipment_number
//       FROM dispatch_allocations da
//       LEFT JOIN shipment_dispatches sd 
//         ON sd.allocation_id = da.id 
//         AND COALESCE(sd.is_deleted, false) = false
//       WHERE da.id = $1
//         AND da.org_id = $2
//         AND COALESCE(da.is_deleted, false) = false
//       `,
//       [allocation_id, orgId],
//     );

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

//     const allocation = allocationResult.rows[0];
    
//     // =====================================================
//     // CURRENT VALUES
//     // =====================================================

//     const existingAllocated = Number(allocation.allocated_qty || 0);
//     const requiredQty = Number(allocation.required_qty || 0);
//     const totalDispatched = Number(allocation.total_dispatched_qty || 0);
//     const hasShipment = allocation.shipment_id !== null;
//     const shipmentStatus = allocation.shipment_status;

//     // Debug logs
//     console.log("=== CANCEL ALLOCATION DEBUG ===");
//     console.log("Allocation ID:", allocation_id);
//     console.log("Existing Allocated Qty:", existingAllocated);
//     console.log("Required Qty:", requiredQty);
//     console.log("Total Dispatched:", totalDispatched);
//     console.log("Has Shipment:", hasShipment);
//     console.log("Shipment Status:", shipmentStatus);
//     console.log("Requested Cancel Qty:", qty);
//     console.log("=================================");

//     // =====================================================
//     // BLOCK ONLY IF SHIPMENT IS IN ADVANCED STAGES
//     // =====================================================
//     if (hasShipment && ["Gate In", "Loaded", "Dispatched", "Completed"].includes(shipmentStatus)) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cannot cancel allocation. Shipment is already in '${shipmentStatus}' status.`,
//       });
//     }

//     // =====================================================
//     // VALIDATE CANCEL
//     // =====================================================

//     if (qty > existingAllocated) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cancel qty (${qty}) exceeds allocated qty (${existingAllocated})`,
//       });
//     }

//     // Prevent canceling already dispatched quantity
//     const cancelableQty = existingAllocated - totalDispatched;
//     if (qty > cancelableQty) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cannot cancel ${qty}. Only ${cancelableQty} is available (${totalDispatched} already dispatched)`,
//       });
//     }

//     // =====================================================
//     // CALCULATIONS
//     // =====================================================

//     const finalAllocated = existingAllocated - qty;
//     const finalBalance = requiredQty - finalAllocated;

//     // =====================================================
//     // STATUS
//     // =====================================================

//     let status = "waiting";
//     if (finalAllocated === 0) {
//       status = "not_allocated";
//     } else if (finalAllocated < requiredQty) {
//       status = "partial";
//     } else {
//       status = "full";
//     }

//     // =====================================================
//     // UPDATE ALLOCATION
//     // =====================================================

//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET
//           allocated_qty = $1,
//           balance_qty = $2,
//           allocation_status = $3,
//           cancel_remarks = $4,
//           updated_by = $5,
//           vehicle_assigned = false,
//           shipment_completed = false,
//           gate_pass_generated = false,
//           shipment_pending = false,
//           updated_at = CURRENT_TIMESTAMP
//           ${finalAllocated === 0 ? `, is_deleted = false` : ``}
//       WHERE id = $6
//       `,
//       [
//         finalAllocated,
//         finalBalance,
//         status,
//         remarks || null,
//         userId,
//         allocation_id,
//       ],
//     );

//     // =====================================================
//     // REVERT INVENTORY STOCK
//     // =====================================================

//     await client.query(
//       `
//       UPDATE inventory_stock
//       SET
//           available_qty = available_qty + $1,
//           reserved_qty = GREATEST(COALESCE(reserved_qty,0) - $1, 0),
//           updated_at = CURRENT_TIMESTAMP
//       WHERE product_id = $2
//       AND org_id = $3
//       `,
//       [qty, allocation.product_id, orgId],
//     );

//     // =====================================================
//     // HANDLE EXISTING SHIPMENT - DELETE/CANCEL IT
//     // =====================================================
//     if (hasShipment) {
//       // Delete/cancel the shipment record
//       await client.query(
//         `
//         UPDATE shipment_dispatches
//         SET
//             is_deleted = true,
//             shipment_status = 'Cancelled',
//             vehicle_assigned = false,
//             updated_at = CURRENT_TIMESTAMP,
//             updated_by = $1
//         WHERE allocation_id = $2
//           AND org_id = $3
//           AND COALESCE(is_deleted, false) = false
//         `,
//         [userId, allocation_id, orgId],
//       );
      
//       console.log(`Shipment cancelled for allocation ${allocation_id}`);
//     }

//     // =====================================================
//     // HISTORY
//     // =====================================================

//     await client.query(
//       `
//       INSERT INTO dispatch_allocation_history
//       (
//           allocation_id,
//           sales_order_id,
//           product_id,
//           allocated_qty,
//           balance_after,
//           allocation_type,
//           dispatch_status,
//           remarks,
//           created_by,
//           created_at
//       )
//       VALUES
//       (
//           $1, $2, $3, $4, $5, $6, $7, $8, $9, CURRENT_TIMESTAMP
//       )
//       `,
//       [
//         allocation.id,
//         allocation.sales_order_id,
//         allocation.product_id,
//         -qty,
//         finalBalance,
//         "cancel",
//         status,
//         remarks || "Allocation cancelled",
//         userId,
//       ],
//     );

//     // =====================================================
//     // COMMIT
//     // =====================================================

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

//     return res.json({
//       success: true,
//       message: finalAllocated === 0
//         ? "Allocation fully cancelled. Shipment has been cancelled and stock returned to inventory."
//         : `Allocation reduced by ${qty}. ${hasShipment ? 'Shipment has been cancelled.' : ''}`,
//       data: {
//         new_allocated_qty: finalAllocated,
//         allocation_status: status,
//         balance_qty: finalBalance,
//         shipment_cancelled: hasShipment
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CANCEL ALLOCATION ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Cancel allocation failed",
//       error: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

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

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

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

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

//     const { allocation_id, cancel_qty, remarks } = req.body;

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

//     const qty = Number(cancel_qty || 0);

//     if (qty <= 0) {
//       await client.query("ROLLBACK");

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

//     // =====================================================
//     // GET ALLOCATION
//     // =====================================================

//     const allocationResult = await client.query(
//       `
//       SELECT *
//       FROM dispatch_allocations
//       WHERE id = $1
//       AND org_id = $2
//       AND COALESCE(is_deleted,false) = false
//       `,
//       [allocation_id, orgId],
//     );

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

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

//     const allocation = allocationResult.rows[0];

//     // =====================================================
//     // GET SHIPMENT
//     // =====================================================

//     const shipmentResult = await client.query(
//       `
//       SELECT *
//       FROM shipment_dispatches
//       WHERE allocation_id = $1
//       AND org_id = $2
//       AND COALESCE(is_deleted,false) = false
//       LIMIT 1
//       `,
//       [allocation_id, orgId],
//     );

//     const shipment = shipmentResult.rows[0];

//     // =====================================================
//     // BLOCK AFTER GATE IN
//     // =====================================================

//     if (
//       shipment &&
//       ["Gate In", "Loaded", "Dispatched", "Completed"].includes(
//         shipment.shipment_status,
//       )
//     ) {
//       await client.query("ROLLBACK");

//       return res.status(400).json({
//         success: false,
//         message: "Cannot cancel allocation after gate in / dispatch started",
//       });
//     }

//     // =====================================================
//     // CURRENT VALUES
//     // =====================================================

//     const existingAllocated = Number(allocation.allocated_qty || 0);

//     const requiredQty = Number(allocation.required_qty || 0);

//     // =====================================================
//     // VALIDATE CANCEL
//     // =====================================================

//     if (qty > existingAllocated) {
//       await client.query("ROLLBACK");

//       return res.status(400).json({
//         success: false,
//         message: "Cancel qty exceeds allocated qty",
//       });
//     }

//     // =====================================================
//     // CALCULATIONS
//     // =====================================================

//     const finalAllocated = existingAllocated - qty;

//     const finalBalance = requiredQty - finalAllocated;

//     const shouldSoftDelete = finalAllocated <= 0;

//     // =====================================================
//     // STATUS
//     // =====================================================

//     let status = "waiting";

//     if (finalAllocated === 0) {
//       status = "waiting";
//     } else if (finalAllocated < requiredQty) {
//       status = "partial";
//     } else {
//       status = "full";
//     }

//     // =====================================================
//     // UPDATE / SOFT DELETE ALLOCATION
//     // =====================================================

//     if (shouldSoftDelete) {
//       // FULL CANCEL
//       await client.query(
//         `
//         UPDATE dispatch_allocations
//         SET

//             is_deleted = true,

//             allocated_qty = 0,

//             balance_qty = required_qty,

//             allocation_status = 'waiting',

//             vehicle_assigned = false,

//             shipment_completed = false,

//             gate_pass_generated = false,

//             cancel_remarks = $1,

//             updated_by = $2,

//             updated_at = CURRENT_TIMESTAMP

//         WHERE id = $3
//         `,
//         [remarks || "Allocation cancelled", userId, allocation_id],
//       );
//     } else {
//       // PARTIAL CANCEL
//      // PARTIAL CANCEL
// await client.query(
//   `
//   UPDATE dispatch_allocations
//   SET

//       allocated_qty = $1,

//       balance_qty = $2,

//       allocation_status = $3,

//       cancel_remarks = $4,

//       updated_by = $5,

//       vehicle_assigned = false,

//       shipment_completed = false,

//       gate_pass_generated = false,

//       updated_at = CURRENT_TIMESTAMP

//   WHERE id = $6
//   `,
//   [
//     finalAllocated,

//     finalBalance,

//     status,

//     remarks || null,

//     userId,

//     allocation_id,
//   ],
// );
//     }

//     // =====================================================
//     // REVERT INVENTORY STOCK
//     // =====================================================

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

//           available_qty =
//               available_qty + $1,

//           reserved_qty =
//               GREATEST(
//                   COALESCE(reserved_qty,0) - $1,
//                   0
//               ),

//           updated_at = CURRENT_TIMESTAMP

//       WHERE product_id = $2
//       AND org_id = $3
//       `,
//       [qty, allocation.product_id, orgId],
//     );

//     // =====================================================
//     // CANCEL SHIPMENT
//     // =====================================================

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

//             is_deleted = true,

//             shipment_status = 'Cancelled',

//             vehicle_assigned = false,

//             updated_at = CURRENT_TIMESTAMP

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

//     // =====================================================
//     // HISTORY
//     // =====================================================

//     await client.query(
//       `
//       INSERT INTO dispatch_allocation_history
//       (
//           allocation_id,

//           sales_order_id,

//           product_id,

//           allocated_qty,

//           balance_after,

//           allocation_type,

//           dispatch_status,

//           remarks,

//           created_by,

//           created_at
//       )

//       VALUES
//       (
//           $1,
//           $2,
//           $3,
//           $4,
//           $5,
//           $6,
//           $7,
//           $8,
//           $9,
//           CURRENT_TIMESTAMP
//       )
//       `,
//       [
//         allocation.id,

//         allocation.sales_order_id,

//         allocation.product_id,

//         -qty,

//         finalBalance,

//         "cancel",

//         status,

//         remarks || "Allocation cancelled",

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

//     // =====================================================
//     // COMMIT
//     // =====================================================

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

//     return res.json({
//       success: true,
//       message: shouldSoftDelete
//         ? "Allocation cancelled and reverted"
//         : "Allocation updated successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

//     console.error("CANCEL ALLOCATION ERROR:", err);

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

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

//     const { allocationId } = req.params;

//     if (!orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized access",
//       });
//     }

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

//     //       /* ===================================================== */
//     //       /* ALLOCATION DETAILS */
//     //       /* ===================================================== */

//     //       da.id,

//     //       da.sales_order_id,

//     //       da.sales_order_number,

//     //       da.customer_id,

//     //       da.customer_name,

//     //       da.product_id,

//     //       da.required_qty,

//     //       da.allocated_qty,

//     //       da.balance_qty,

//     //       da.allocation_status,

//     //       da.priority,

//     //       da.delivery_date,

//     //       da.remarks,

//     //       da.vehicle_assigned,

//     //       da.gate_pass_generated,

//     //       da.shipment_completed,

//     //       da.created_at AS allocation_created_at,

//     //       da.updated_at AS allocation_updated_at,

//     //       /* ===================================================== */
//     //       /* PRODUCT DETAILS */
//     //       /* ===================================================== */

//     //       pim.item_name,

//     //       pim.uom,

//     //       /* ===================================================== */
//     //       /* SALES ORDER */
//     //       /* ===================================================== */

//     //       so.created_at AS so_created_at,

//     //       /* ===================================================== */
//     //       /* SALES ORDER ITEM */
//     //       /* ===================================================== */

//     //       soi.qty AS so_qty,

//     //       soi.rate,

//     //       soi.description,

//     //       soi.uom_symbol,

//     //       soi.product_name,

//     //       /* ===================================================== */
//     //       /* USERS */
//     //       /* ===================================================== */

//     //       creator.first_name || ' ' || creator.last_name
//     //       AS allocation_created_by,

//     //       updater.first_name || ' ' || updater.last_name
//     //       AS allocation_updated_by

//     //   FROM dispatch_allocations da

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

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

//     //   LEFT JOIN sales_order_items soi
//     //       ON soi.sales_order_id = so.id
//     //       AND LOWER(soi.product_name) = LOWER(pim.item_name)

//     //   LEFT JOIN users creator
//     //       ON creator.id = da.created_by

//     //   LEFT JOIN users updater
//     //       ON updater.id = da.updated_by

//     //   WHERE da.org_id = $1
//     //   AND da.id = $2

//     //   LIMIT 1
//     //   `,
//     //   [orgId, allocationId],
//     // );
//     const result = await pool.query(
//   `
//     SELECT

//         /* ===================================================== */
//         /* ALLOCATION DETAILS */
//         /* ===================================================== */

//         da.id,

//         da.sales_order_id,

//         da.sales_order_number,

//         da.customer_id,

//         da.customer_name,

//         da.product_id,

//         da.required_qty,

//         da.allocated_qty,

//         da.balance_qty,

//         da.allocation_status,

//         da.priority,

//         da.delivery_date,

//         da.remarks,

//         da.vehicle_assigned,

//         da.gate_pass_generated,

//         da.shipment_completed,

//         da.created_at AS allocation_created_at,

//         da.updated_at AS allocation_updated_at,

//         /* ===================================================== */
//         /* PRODUCT DETAILS */
//         /* ===================================================== */

//         pim.item_name,

//         pim.uom,

//         /* ===================================================== */
//         /* SALES ORDER */
//         /* ===================================================== */

//         so.created_at AS so_created_at,

//         /* ===================================================== */
//         /* SALES ORDER ITEM */
//         /* ===================================================== */

//         soi.qty AS so_qty,

//         soi.rate,

//         soi.description,

//         soi.uom_symbol,

//         soi.product_name,

//         /* ===================================================== */
//         /* SHIPMENT / DISPATCH DETAILS */
//         /* ===================================================== */

//         sd.shipment_number,

//         sd.vehicle_number,

//         sd.driver_name,

//         sd.expected_delivery_datetime,

//         sd.shipment_status,

//         sd.transport_agency_id,

//         ta.agency_name,

//         /* ===================================================== */
//         /* USERS */
//         /* ===================================================== */

//         creator.first_name || ' ' || creator.last_name
//         AS allocation_created_by,

//         updater.first_name || ' ' || updater.last_name
//         AS allocation_updated_by

//     FROM dispatch_allocations da

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

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

//     LEFT JOIN sales_order_items soi
//         ON soi.sales_order_id = so.id
//         AND LOWER(soi.product_name) = LOWER(pim.item_name)

//     /* ===================================================== */
//     /* SHIPMENT DISPATCH */
//     /* ===================================================== */

//     LEFT JOIN shipment_dispatches sd
//         ON sd.allocation_id = da.id

//     /* ===================================================== */
//     /* TRANSPORT AGENCY */
//     /* ===================================================== */

//     LEFT JOIN transport_agencies ta
//         ON ta.id = sd.transport_agency_id

//     /* ===================================================== */
//     /* USERS */
//     /* ===================================================== */

//     LEFT JOIN users creator
//         ON creator.id = da.created_by

//     LEFT JOIN users updater
//         ON updater.id = da.updated_by

//     WHERE da.org_id = $1
//     AND da.id = $2

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

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

//     return res.status(200).json({
//       success: true,
//       message: "Allocation details fetched successfully",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("ALLOCATION DETAIL ERROR:", err);

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

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

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

//     const orgId = req.session?.user?.orgId;
//     const userId = req.session?.user?.id;
//     const { allocation_id, cancel_qty, remarks } = req.body;

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

//     const qty = Number(cancel_qty || 0);

//     if (qty <= 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Invalid cancel qty",
//       });
//     }

//     // =====================================================
//     // GET ALLOCATION WITH COMMITTED SHIPMENTS
//     // =====================================================

//     const allocationResult = await client.query(
//       `
//       SELECT 
//         da.*,
//         COALESCE((
//           SELECT SUM(dispatch_qty)
//           FROM shipment_dispatches
//           WHERE allocation_id = da.id
//             AND is_deleted = false
//             AND shipment_status NOT IN ('Cancelled', 'Completed')
//         ), 0) as committed_in_shipments
//       FROM dispatch_allocations da
//       WHERE da.id = $1
//       AND da.org_id = $2
//       AND COALESCE(da.is_deleted, false) = false
//       `,
//       [allocation_id, orgId],
//     );

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

//     const allocation = allocationResult.rows[0];
    
//     // =====================================================
//     // CURRENT VALUES
//     // =====================================================

//     const existingAllocated = Number(allocation.allocated_qty || 0);
//     const requiredQty = Number(allocation.required_qty || 0);
//     const totalDispatched = Number(allocation.total_dispatched_qty || 0);
//     const committedInShipments = Number(allocation.committed_in_shipments || 0);

//     // Debug logs
//     console.log("=== CANCEL ALLOCATION DEBUG ===");
//     console.log("Allocation ID:", allocation_id);
//     console.log("Existing Allocated Qty:", existingAllocated);
//     console.log("Required Qty:", requiredQty);
//     console.log("Total Dispatched (Completed):", totalDispatched);
//     console.log("Committed in Pending Shipments:", committedInShipments);
//     console.log("Requested Cancel Qty:", qty);
//     console.log("=================================");

//     // =====================================================
//     // VALIDATE CANCEL
//     // =====================================================

//     if (qty > existingAllocated) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cancel qty (${qty}) exceeds allocated qty (${existingAllocated})`,
//         debug: {
//           allocated_qty: existingAllocated,
//           requested_cancel: qty
//         }
//       });
//     }

//     // Check if there are pending shipments that cannot be cancelled
//     if (committedInShipments > 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cannot cancel allocation. ${committedInShipments}  is already committed in pending shipments. Please cancel the shipment first.`,
//         debug: {
//           committed_in_shipments: committedInShipments,
//           requested_cancel: qty
//         }
//       });
//     }

//     // Prevent canceling already dispatched quantity
//     const cancelableQty = existingAllocated - totalDispatched;
//     if (qty > cancelableQty) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cannot cancel ${qty} ${allocation.uom}. Only ${cancelableQty} ${allocation.uom} is available to cancel (${totalDispatched} already dispatched)`,
//         debug: {
//           cancelable_qty: cancelableQty,
//           requested_cancel: qty,
//           total_dispatched: totalDispatched
//         }
//       });
//     }

//     // =====================================================
//     // CALCULATIONS
//     // =====================================================

//     const finalAllocated = existingAllocated - qty;
//     const finalBalance = requiredQty - finalAllocated;

//     // =====================================================
//     // STATUS
//     // =====================================================

//     let status = "waiting";
//     if (finalAllocated === 0) {
//       status = "not_allocated";
//     } else if (finalAllocated < requiredQty) {
//       status = "partial";
//     } else {
//       status = "full";
//     }

//     // =====================================================
//     // UPDATE ALLOCATION
//     // =====================================================

//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET
//           allocated_qty = $1,
//           balance_qty = $2,
//           allocation_status = $3,
//           cancel_remarks = $4,
//           updated_by = $5,
//           vehicle_assigned = false,
//           shipment_completed = false,
//           gate_pass_generated = false,
//           shipment_pending = false,
//           updated_at = CURRENT_TIMESTAMP
//           ${finalAllocated === 0 ? `, is_deleted = false` : ``}
//       WHERE id = $6
//       `,
//       [
//         finalAllocated,
//         finalBalance,
//         status,
//         remarks || null,
//         userId,
//         allocation_id,
//       ],
//     );

//     // =====================================================
//     // REVERT INVENTORY STOCK
//     // =====================================================

//     await client.query(
//       `
//       UPDATE inventory_stock
//       SET
//           available_qty = available_qty + $1,
//           reserved_qty = GREATEST(COALESCE(reserved_qty,0) - $1, 0),
//           updated_at = CURRENT_TIMESTAMP
//       WHERE product_id = $2
//       AND org_id = $3
//       `,
//       [qty, allocation.product_id, orgId],
//     );

//     // =====================================================
//     // CANCEL ANY PENDING SHIPMENT
//     // =====================================================

//     const pendingShipments = await client.query(
//       `
//       UPDATE shipment_dispatches
//       SET
//           is_deleted = true,
//           shipment_status = 'Cancelled',
//           vehicle_assigned = false,
//           updated_at = CURRENT_TIMESTAMP
//       WHERE allocation_id = $1
//         AND org_id = $2
//         AND COALESCE(is_deleted, false) = false
//         AND shipment_status NOT IN ('Cancelled', 'Completed')
//       RETURNING id
//       `,
//       [allocation_id, orgId],
//     );

//     if (pendingShipments.rows.length > 0) {
//       console.log(`Cancelled ${pendingShipments.rows.length} pending shipments`);
//     }

//     // =====================================================
//     // HISTORY
//     // =====================================================

//     await client.query(
//       `
//       INSERT INTO dispatch_allocation_history
//       (
//           allocation_id,
//           sales_order_id,
//           product_id,
//           allocated_qty,
//           balance_after,
//           allocation_type,
//           dispatch_status,
//           remarks,
//           created_by,
//           created_at
//       )
//       VALUES
//       (
//           $1, $2, $3, $4, $5, $6, $7, $8, $9, CURRENT_TIMESTAMP
//       )
//       `,
//       [
//         allocation.id,
//         allocation.sales_order_id,
//         allocation.product_id,
//         -qty,
//         finalBalance,
//         "cancel",
//         status,
//         remarks || "Allocation cancelled",
//         userId,
//       ],
//     );

//     // =====================================================
//     // COMMIT
//     // =====================================================

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

//     return res.json({
//       success: true,
//       message: finalAllocated === 0
//         ? "Allocation fully cancelled and reverted to pending"
//         : `Allocation reduced by ${qty} ${allocation.uom}`,
//       data: {
//         new_allocated_qty: finalAllocated,
//         allocation_status: status,
//         balance_qty: finalBalance,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CANCEL ALLOCATION ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Cancel allocation failed",
//       error: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };


const getDispatchedShipments = async (req, res) => {
  try {
    // =========================================
    // ORG
    // =========================================

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

    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized access",
      });
    }

    // =========================================
    // SEARCH
    // =========================================

    const search = (req.query.search || "").trim().toLowerCase();

    // =========================================
    // QUERY
    // =========================================

    const result = await pool.query(
      `
      SELECT

          /* ===================================== */
          /* SHIPMENT */
          /* ===================================== */

          sd.id,

          sd.shipment_number,

          sd.sales_order_number,

          sd.customer_name,

          sd.product_name,

          sd.vehicle_number,

          sd.shipment_status,

          sd.dispatch_datetime,

          sd.weighment_completed_at,

          sd.created_at,

          /* ===================================== */
          /* ITEM */
          /* ===================================== */

          sdi.dispatch_qty,

          sdi.uom,

          /* ===================================== */
          /* USER */
          /* ===================================== */

          creator.first_name || ' ' || creator.last_name
          AS created_by

      FROM shipment_dispatches sd

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

      LEFT JOIN users creator
          ON creator.id = sd.created_by

      WHERE sd.org_id = $1

      AND sd.shipment_status = 'Completed'

      AND (
          $2 = ''

          OR LOWER(sd.shipment_number)
              LIKE '%' || $2 || '%'

          OR LOWER(sd.sales_order_number)
              LIKE '%' || $2 || '%'

          OR LOWER(sd.customer_name)
              LIKE '%' || $2 || '%'

          OR LOWER(sd.product_name)
              LIKE '%' || $2 || '%'

          OR LOWER(sd.vehicle_number)
              LIKE '%' || $2 || '%'
      )

      ORDER BY sd.weighment_completed_at DESC
      `,
      [orgId, search],
    );

    // =========================================
    // RESPONSE
    // =========================================

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

      message: "Dispatched shipments fetched successfully",

      data: result.rows,
    });
  } catch (error) {
    console.error("GET DISPATCHED SHIPMENTS ERROR:", error);

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

      message: "Failed to fetch dispatched shipments",

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


const getShipmentById = async (
  req,
  res
) => {

  try {

    // =========================
    // PARAMS
    // =========================

    const { id } = req.params;

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

    // =========================
    // AUTH CHECK
    // =========================

    if (!orgId) {

      return res.status(401).json({
        success: false,
        message: "Unauthorized access",
      });

    }

    // =========================
    // QUERY
    // =========================

    const result = await pool.query(
      `
      SELECT

          /* ===================================== */
          /* SHIPMENT */
          /* ===================================== */

          sd.id,

          sd.shipment_number,

          sd.shipment_status,

          sd.dispatch_datetime,

          sd.expected_delivery_datetime,

          sd.weighment_completed_at,

          sd.created_at,

          sd.updated_at,

          sd.customer_name,

          sd.product_id,

          sd.product_name,

          sd.sales_order_id,

          sd.sales_order_number,

          sd.shipping_address,

          sd.shipping_city,

          sd.shipping_state,

          sd.shipping_pincode,

          sd.shipping_gst,

          sd.transport_mode,

          sd.transport_agency_id,

          sd.vehicle_type,

          sd.vehicle_number,

          sd.vehicle_capacity,

          sd.driver_name,

          sd.driver_contact_number,

          sd.driver_license_number,

          sd.freight_amount,

          sd.lr_number,

          sd.seal_number,

          sd.dispatch_temperature,

          sd.density,

          sd.next_dispatch_date,

          sd.hazard_class,

          sd.ppe_required,

          sd.emergency_contact_person,

          sd.emergency_contact,

          sd.safety_instructions_issued,

          sd.fire_safety_equipment,

          sd.spill_kit_available,

          /* ===================================== */
          /* ITEM */
          /* ===================================== */

          sdi.dispatch_qty,

          sdi.allocated_qty,

          sdi.uom,

          /* ===================================== */
          /* ALLOCATION */
          /* ===================================== */

          da.required_qty,

          da.balance_qty,

          da.available_stock,

          da.allocation_status,

          /* ===================================== */
          /* USERS */
          /* ===================================== */

          creator.first_name || ' ' ||
          creator.last_name
          AS created_by,

          updater.first_name || ' ' ||
          updater.last_name
          AS updated_by,

          /* ===================================== */
          /* TRANSPORT AGENCY */
          /* ===================================== */

          ta.agency_name
          AS transport_agency_name

      FROM shipment_dispatches sd

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

      LEFT JOIN dispatch_allocations da
          ON da.id = sd.allocation_id

      LEFT JOIN users creator
          ON creator.id = sd.created_by

      LEFT JOIN users updater
          ON updater.id = sd.updated_by

      LEFT JOIN transport_agencies ta
          ON ta.id = sd.transport_agency_id

      WHERE
          sd.id = $1

          AND sd.org_id = $2

      LIMIT 1
      `,
      [id, orgId]
    );

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

    if (
      result.rows.length === 0
    ) {

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

    }

    // =========================
    // RESPONSE
    // =========================

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

      message:
        "Shipment details fetched successfully",

      data: result.rows[0],
    });

  } catch (error) {

    console.error(
      "GET SHIPMENT DETAILS ERROR:",
      error
    );

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

      message:
        "Failed to fetch shipment details",

      error: error.message,
    });

  }
};

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

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

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

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

    if (!userId || !orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized",
      });
    }

    // =====================================
    // SHIPMENT
    // =====================================

    const shipmentRes =
      await client.query(
        `
        SELECT
          id,
          sales_order_number,
          approval_status
        FROM shipment_dispatches
        WHERE id = $1
        AND org_id = $2
        `,
        [id, orgId]
      );

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

    const shipment =
      shipmentRes.rows[0];

    // =====================================
    // INVOICE VALIDATION
    // =====================================

    // const invoiceRes =
    //   await client.query(
    //     `
    //     SELECT id
    //     FROM sales_invoices
    //     WHERE sales_order_number = $1
    //     AND status = 'approved'
    //     LIMIT 1
    //     `,
    //     [
    //       shipment.sales_order_number,
    //     ]
    //   );

    // if (
    //   invoiceRes.rows.length === 0
    // ) {
    //   return res.status(400).json({
    //     success: false,
    //     message:
    //       "Invoice is not approved for this shipment",
    //   });
    // }

    // =====================================
    // APPROVE SHIPMENT
    // =====================================

    await client.query(
      `
      UPDATE shipment_dispatches
      SET
          approval_status = 'Approved',
          approved_by = $1,
          approved_at = NOW(),
          updated_at = NOW()
      WHERE id = $2
      `,
      [userId, id]
    );

    return res.status(200).json({
      success: true,
      message:
        "Shipment approved successfully",
    });

  } catch (error) {

    console.error(
      "APPROVE SHIPMENT ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message:
        "Failed to approve shipment",
      error: error.message,
    });

  } finally {
    client.release();
  }
};



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 getAllShipments = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized access",
      });
    }

    const result = await pool.query(
      `
      SELECT 
        sd.id,
        sd.shipment_number,
        sd.sales_order_id,
        sd.sales_order_number,
        sd.allocation_id,
        sd.customer_name,
        sd.product_id,
        sd.product_name,
        sd.dispatch_qty,
        sd.allocated_qty,
        sd.uom,
        sd.vehicle_number,
        sd.shipment_status,
        sd.approval_status,
        sd.created_at,
        sd.updated_at,
        sd.is_deleted,
        sd.driver_name,
        sd.expected_delivery_datetime,
        COALESCE(sd.approval_status, 'Pending') as approval_status
      FROM shipment_dispatches sd
      WHERE sd.org_id = $1
        AND COALESCE(sd.is_deleted, false) = false
        AND sd.shipment_status IN ('Pending', 'Gate In', 'Loaded', 'Dispatched')
      ORDER BY sd.created_at DESC
      `,
      [orgId]
    );

    console.log("Shipments found:", result.rows.length);

    return res.status(200).json({
      success: true,
      message: "Shipments fetched successfully",
      data: result.rows,
    });
  } catch (error) {
    console.error("GET ALL SHIPMENTS ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch shipments",
      error: error.message,
    });
  }
};

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

//     const invoiceRes = await pool.query(
//       `
//       SELECT
//         id,
//         invoice_number,
//         pdf_path
//       FROM sales_invoices
//       WHERE id = $1
//       `,
//       [id]
//     );

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

//     const invoice = invoiceRes.rows[0];

//     if (!invoice.pdf_path) {
//       return res.status(404).json({
//         success: false,
//         message: "PDF not available",
//       });
//     }

//     const filePath = path.join(
//       process.cwd(),
//       invoice.pdf_path
//     );

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

//     res.setHeader("Content-Type", "application/pdf");
//     res.setHeader(
//       "Content-Disposition",
//       `inline; filename=${invoice.invoice_number}.pdf`
//     );

//     fs.createReadStream(filePath).pipe(res);
//   } catch (error) {
//     console.error(error);

//     return res.status(500).json({
//       success: false,
//       message: "Error viewing invoice",
//     });
//   }
// };


// GET /dispatch/shipment-approvals
const getShipmentApprovals = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    const result = await pool.query(
      `
      SELECT
          id AS shipment_id,
          approval_status
      FROM shipment_dispatches
      WHERE org_id = $1
      AND COALESCE(is_deleted, false) = false
      `,
      [orgId]
    );

    return res.status(200).json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET SHIPMENT APPROVALS ERROR:", err);

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

// const getCombinedDispatchQueue = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;
//     const productId = req.query.productId || null;

//     if (!orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized access",
//       });
//     }

//     // =====================================================
//     // PART 1: GET EXISTING ALLOCATIONS (with allocated_qty > 0)
//     // =====================================================
//     const allocationsResult = await pool.query(
//       `
//       SELECT 
//         'allocation' as source_type,
//         da.id as source_id,
//         da.sales_order_id,
//         da.sales_order_number,
//         da.customer_id,
//         da.customer_name,
//         da.product_id,
//         da.required_qty,
//         da.allocated_qty,
//         da.balance_qty,
//         da.delivered_qty,
//         da.available_stock,
//         da.delivery_date,
//         da.allocation_status,
//         da.delivery_status,
//         da.created_at,
//         da.updated_at,
//         da.allow_multiple_shipments,
//         da.shipment_count,
//         da.total_dispatched_qty,
//         da.remaining_shipment_qty,
//         da.next_dispatch_date,
        
//         pim.item_name as product_name,
//         pim.uom,
        
//         sd.id as shipment_id,
//         sd.shipment_number,
//         sd.shipment_status,
//         sd.vehicle_number,
//         sd.dispatch_qty as last_dispatch_qty,
//         sd.created_at as shipment_created_at,
        
//         COALESCE((
//             SELECT SUM(dispatch_qty)
//             FROM shipment_dispatches
//             WHERE allocation_id = da.id
//               AND is_deleted = false
//               AND shipment_status NOT IN ('Cancelled')
//         ), 0) AS total_dispatched,
        
//         CASE
//             WHEN EXISTS (
//                 SELECT 1 FROM shipment_dispatches
//                 WHERE allocation_id = da.id
//                   AND is_deleted = false
//                   AND shipment_status IN ('Pending', 'Gate In', 'Loaded', 'Dispatched')
//             ) THEN true
//             ELSE false
//         END AS has_active_shipment

//       FROM dispatch_allocations da
      
//       LEFT JOIN procurement_itemmaster pim
//           ON pim.id = da.product_id
          
//       LEFT JOIN LATERAL (
//           SELECT
//               sd.id,
//               sd.shipment_number,
//               sd.shipment_status,
//               sd.vehicle_number,
//               sd.dispatch_qty,
//               sd.created_at
//           FROM shipment_dispatches sd
//           WHERE sd.allocation_id = da.id
//             AND COALESCE(sd.is_deleted, false) = false
//           ORDER BY sd.created_at DESC
//           LIMIT 1
//       ) sd ON true

//       WHERE da.org_id = $1
//         AND ($2::int IS NULL OR da.product_id = $2)
//         AND COALESCE(da.is_deleted, false) = false
//         AND da.allocated_qty > 0
      
//       ORDER BY 
//           CASE 
//               WHEN da.allocation_status = 'partial' THEN 1
//               WHEN da.allocation_status = 'full' THEN 2
//               ELSE 3
//           END,
//           da.delivery_date ASC NULLS LAST,
//           da.created_at ASC
//       `,
//       [orgId, productId || null]
//     );

//     // =====================================================
//     // PART 2: GET PENDING SALES ORDERS (NO allocation at all)
//     // FIX: Exclude sales orders that have ANY allocation with allocated_qty > 0
//     // =====================================================
//     const pendingOrdersResult = await pool.query(
//       `
//       SELECT 
//         'pending' as source_type,
//         so.id as source_id,
//         so.id as sales_order_id,
//         so.so_number as sales_order_number,
//         so.customer_id,
//         so.customer_name,
//         soi.product_id,
//         soi.qty as required_qty,
//         0 as allocated_qty,
//         soi.qty as balance_qty,
//         0 as delivered_qty,
//         0 as available_stock,
//         so.delivery_date,
//         'not_allocated' as allocation_status,
//         'Pending' as delivery_status,
//         so.created_at,
//         NULL as updated_at,
//         true as allow_multiple_shipments,
//         0 as shipment_count,
//         0 as total_dispatched_qty,
//         soi.qty as remaining_shipment_qty,
//         NULL as next_dispatch_date,
        
//         soi.product_name,
//         soi.uom_symbol as uom,
        
//         NULL as shipment_id,
//         NULL as shipment_number,
//         NULL as shipment_status,
//         NULL as vehicle_number,
//         NULL as last_dispatch_qty,
//         NULL as shipment_created_at,
        
//         0 as total_dispatched,
//         false as has_active_shipment

//       FROM sales_orders so
      
//       LEFT JOIN sales_order_items soi 
//           ON soi.sales_order_id = so.id
          
//       LEFT JOIN dispatch_allocations da 
//           ON da.sales_order_id = so.id 
//           AND da.is_deleted = false

//       WHERE so.org_id = $1
//         AND so.is_deleted = false
//         AND so.approval_status = 'Approved'
//         AND ($2::int IS NULL OR soi.product_id = $2)
//         -- FIX: Only get orders with NO allocation at all (allocated_qty IS NULL OR allocated_qty = 0)
//         AND (da.id IS NULL OR da.allocated_qty = 0)
      
//       ORDER BY so.delivery_date ASC NULLS LAST, so.created_at ASC
//       `,
//       [orgId, productId || null]
//     );

//     // =====================================================
//     // COMBINE BOTH RESULTS
//     // =====================================================
//     const combinedData = [...allocationsResult.rows, ...pendingOrdersResult.rows];

//     // Remove any potential duplicates by sales_order_id (just in case)
//     const uniqueMap = new Map();
//     for (const item of combinedData) {
//       const key = `${item.sales_order_id}-${item.source_type}`;
//       if (!uniqueMap.has(key)) {
//         uniqueMap.set(key, item);
//       }
//     }
//     const finalData = Array.from(uniqueMap.values());

//     // Sort combined data by delivery date and priority
//     finalData.sort((a, b) => {
//       // Pending orders without allocation come first
//       if (a.allocation_status === 'not_allocated' && b.allocation_status !== 'not_allocated') return -1;
//       if (b.allocation_status === 'not_allocated' && a.allocation_status !== 'not_allocated') return 1;
      
//       // Then partial allocations
//       if (a.allocation_status === 'partial' && b.allocation_status !== 'partial') return -1;
//       if (b.allocation_status === 'partial' && a.allocation_status !== 'partial') return 1;
      
//       // Then by delivery date
//       if (a.delivery_date && b.delivery_date) {
//         return new Date(a.delivery_date) - new Date(b.delivery_date);
//       }
//       if (a.delivery_date && !b.delivery_date) return -1;
//       if (!a.delivery_date && b.delivery_date) return 1;
      
//       return 0;
//     });

//     return res.status(200).json({
//       success: true,
//       message: "Dispatch queue fetched successfully",
//       total: finalData.length,
//       data: finalData,
//       summary: {
//         allocations_count: allocationsResult.rows.length,
//         pending_count: pendingOrdersResult.rows.length
//       }
//     });

//   } catch (err) {
//     console.error("GET COMBINED DISPATCH QUEUE ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch dispatch queue",
//       error: err.message,
//     });
//   }
// };

// Get single allocation details with full info

// const getCombinedDispatchQueue = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;
//     const productId = req.query.productId || null;

//     if (!orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized access",
//       });
//     }

//     // =====================================================
//     // PART 1: GET EXISTING ALLOCATIONS 
//     // Show only if:
//     // 1. allocation_status = 'partial' (always show - has remaining balance)
//     // 2. OR allocation_status = 'full' AND no shipment exists
//     // 3. OR allocation_status = 'full' AND total_dispatched_qty < allocated_qty
//     // HIDE: allocation_status = 'full' AND shipment exists AND total_dispatched_qty >= allocated_qty
//     // =====================================================
//     // const allocationsResult = await pool.query(
//     //   `
//     //   SELECT 
//     //     'allocation' as source_type,
//     //     da.id as source_id,
//     //     da.sales_order_id,
//     //     da.sales_order_number,
//     //     da.customer_id,
//     //     da.customer_name,
//     //     da.product_id,
//     //     da.required_qty,
//     //     da.allocated_qty,
//     //     da.balance_qty,
//     //     da.delivered_qty,
//     //     da.available_stock,
//     //     da.delivery_date,
//     //     da.allocation_status,
//     //     da.delivery_status,
//     //     da.created_at,
//     //     da.updated_at,
//     //     da.allow_multiple_shipments,
//     //     da.shipment_count,
//     //     da.total_dispatched_qty,
//     //     da.remaining_shipment_qty,
//     //     da.next_dispatch_date,
        
//     //     pim.item_name as product_name,
//     //     pim.uom,
        
//     //     sd.id as shipment_id,
//     //     sd.shipment_number,
//     //     sd.shipment_status,
//     //     sd.vehicle_number,
//     //     sd.dispatch_qty as last_dispatch_qty,
//     //     sd.created_at as shipment_created_at,
//     //     sd.approval_status as shipment_approval_status,
        
//     //     COALESCE((
//     //         SELECT SUM(dispatch_qty)
//     //         FROM shipment_dispatches
//     //         WHERE allocation_id = da.id
//     //           AND is_deleted = false
//     //           AND shipment_status NOT IN ('Cancelled')
//     //     ), 0) AS total_dispatched,
        
//     //     CASE
//     //         WHEN sd.id IS NOT NULL THEN true
//     //         ELSE false
//     //     END AS has_shipment

//     //   FROM dispatch_allocations da
      
//     //   LEFT JOIN procurement_itemmaster pim
//     //       ON pim.id = da.product_id
          
//     //   LEFT JOIN LATERAL (
//     //       SELECT
//     //           sd.id,
//     //           sd.shipment_number,
//     //           sd.shipment_status,
//     //           sd.vehicle_number,
//     //           sd.dispatch_qty,
//     //           sd.created_at,
//     //           sd.approval_status
//     //       FROM shipment_dispatches sd
//     //       WHERE sd.allocation_id = da.id
//     //         AND COALESCE(sd.is_deleted, false) = false
//     //       ORDER BY sd.created_at DESC
//     //       LIMIT 1
//     //   ) sd ON true

//     //   WHERE da.org_id = $1
//     //     AND ($2::int IS NULL OR da.product_id = $2)
//     //     AND COALESCE(da.is_deleted, false) = false
//     //     AND da.allocated_qty > 0
//     //     -- KEY CONDITION: Hide if fully allocated AND has shipment AND fully dispatched
//     //     AND NOT (
//     //       da.allocation_status = 'full' 
//     //       AND sd.id IS NOT NULL 
//     //       AND COALESCE(da.total_dispatched_qty, 0) >= da.allocated_qty
//     //     )
      
//     //   ORDER BY 
//     //       CASE 
//     //           WHEN da.allocation_status = 'partial' THEN 1
//     //           WHEN da.allocation_status = 'full' THEN 2
//     //           ELSE 3
//     //       END,
//     //       da.delivery_date ASC NULLS LAST,
//     //       da.created_at ASC
//     //   `,
//     //   [orgId, productId || null]
//     // );
//     const allocationsResult = await pool.query(
//   `
//   SELECT 
//     'allocation' as source_type,
//     da.id as source_id,
//     da.sales_order_id,
//     da.sales_order_number,
//     da.customer_id,
//     da.customer_name,
//     da.product_id,
//     da.required_qty,
//     da.allocated_qty,
//     da.balance_qty,
//     da.delivered_qty,
//     da.available_stock,
//     da.delivery_date,
//     da.allocation_status,
//     da.delivery_status,
//     da.created_at,
//     da.updated_at,
//     da.allow_multiple_shipments,
//     da.shipment_count,
//     da.total_dispatched_qty,
//     da.remaining_shipment_qty,
//     da.next_dispatch_date,
    
//     pim.item_name as product_name,
//     pim.uom,
    
//     sd.id as shipment_id,
//     sd.shipment_number,
//     sd.shipment_status,
//     sd.vehicle_number,
//     sd.dispatch_qty as last_dispatch_qty,
//     sd.created_at as shipment_created_at,
//     sd.approval_status as shipment_approval_status,
    
//     COALESCE((
//         SELECT SUM(dispatch_qty)
//         FROM shipment_dispatches
//         WHERE allocation_id = da.id
//           AND is_deleted = false
//           AND shipment_status NOT IN ('Cancelled')
//     ), 0) AS total_dispatched,
    
//     CASE
//         WHEN sd.id IS NOT NULL THEN true
//         ELSE false
//     END AS has_shipment

//   FROM dispatch_allocations da
  
//   LEFT JOIN procurement_itemmaster pim
//       ON pim.id = da.product_id
      
//   LEFT JOIN LATERAL (
//       SELECT
//           sd.id,
//           sd.shipment_number,
//           sd.shipment_status,
//           sd.vehicle_number,
//           sd.dispatch_qty,
//           sd.created_at,
//           sd.approval_status
//       FROM shipment_dispatches sd
//       WHERE sd.allocation_id = da.id
//         AND COALESCE(sd.is_deleted, false) = false
//       ORDER BY sd.created_at DESC
//       LIMIT 1
//   ) sd ON true

//   WHERE da.org_id = $1
//     AND ($2::int IS NULL OR da.product_id = $2)
//     AND COALESCE(da.is_deleted, false) = false
//     AND da.allocated_qty > 0
    
//     -- 🔥 REPLACE YOUR EXISTING AND NOT CONDITION WITH THIS:
//     AND NOT (
//       da.allocation_status = 'full' 
//       AND sd.id IS NOT NULL 
//       AND COALESCE(da.total_dispatched_qty, 0) >= da.allocated_qty
//       AND da.allocated_qty > 0
//     )
  
//   ORDER BY 
//       CASE 
//           WHEN da.allocation_status = 'partial' THEN 1
//           WHEN da.allocation_status = 'full' THEN 2
//           ELSE 3
//       END,
//       da.delivery_date ASC NULLS LAST,
//       da.created_at ASC
//   `,
//   [orgId, productId || null]
// );

//     // =====================================================
//     // PART 2: GET PENDING SALES ORDERS (NO allocation at all)
//     // =====================================================
//     const pendingOrdersResult = await pool.query(
//       `
//       SELECT 
//         'pending' as source_type,
//         so.id as source_id,
//         so.id as sales_order_id,
//         so.so_number as sales_order_number,
//         so.customer_id,
//         so.customer_name,
//         soi.product_id,
//         soi.qty as required_qty,
//         0 as allocated_qty,
//         soi.qty as balance_qty,
//         0 as delivered_qty,
//         0 as available_stock,
//         so.delivery_date,
//         'not_allocated' as allocation_status,
//         'Pending' as delivery_status,
//         so.created_at,
//         NULL as updated_at,
//         true as allow_multiple_shipments,
//         0 as shipment_count,
//         0 as total_dispatched_qty,
//         soi.qty as remaining_shipment_qty,
//         NULL as next_dispatch_date,
        
//         soi.product_name,
//         soi.uom_symbol as uom,
        
//         NULL as shipment_id,
//         NULL as shipment_number,
//         NULL as shipment_status,
//         NULL as vehicle_number,
//         NULL as last_dispatch_qty,
//         NULL as shipment_created_at,
//         NULL as shipment_approval_status,
        
//         0 as total_dispatched,
//         false as has_shipment

//       FROM sales_orders so
      
//       LEFT JOIN sales_order_items soi 
//           ON soi.sales_order_id = so.id
          
//       LEFT JOIN dispatch_allocations da 
//           ON da.sales_order_id = so.id 
//           AND da.is_deleted = false

//       WHERE so.org_id = $1
//         AND so.is_deleted = false
//         AND so.approval_status = 'Approved'
//         AND ($2::int IS NULL OR soi.product_id = $2)
//         -- Only get orders with NO allocation at all
//         AND da.id IS NULL
//         -- Also exclude orders that have any allocation with allocated_qty > 0
//         AND NOT EXISTS (
//           SELECT 1 FROM dispatch_allocations da2
//           WHERE da2.sales_order_id = so.id
//             AND da2.is_deleted = false
//             AND da2.allocated_qty > 0
//         )
      
//       ORDER BY so.delivery_date ASC NULLS LAST, so.created_at ASC
//       `,
//       [orgId, productId || null]
//     );

//     // =====================================================
//     // COMBINE BOTH RESULTS
//     // =====================================================
//     const combinedData = [...allocationsResult.rows, ...pendingOrdersResult.rows];

//     // Remove duplicates by sales_order_id
//     const uniqueMap = new Map();
//     for (const item of combinedData) {
//       const existing = uniqueMap.get(item.sales_order_id);
//       if (!existing || (item.source_type === 'allocation' && existing.source_type === 'pending')) {
//         uniqueMap.set(item.sales_order_id, item);
//       }
//     }
//     const finalData = Array.from(uniqueMap.values());

//     // Sort combined data
//     finalData.sort((a, b) => {
//       if (a.allocation_status === 'not_allocated' && b.allocation_status !== 'not_allocated') return -1;
//       if (b.allocation_status === 'not_allocated' && a.allocation_status !== 'not_allocated') return 1;
//       if (a.allocation_status === 'partial' && b.allocation_status !== 'partial') return -1;
//       if (b.allocation_status === 'partial' && a.allocation_status !== 'partial') return 1;
//       if (a.delivery_date && b.delivery_date) {
//         return new Date(a.delivery_date) - new Date(b.delivery_date);
//       }
//       if (a.delivery_date && !b.delivery_date) return -1;
//       if (!a.delivery_date && b.delivery_date) return 1;
//       return 0;
//     });

//     return res.status(200).json({
//       success: true,
//       message: "Dispatch queue fetched successfully",
//       total: finalData.length,
//       data: finalData,
//       summary: {
//         allocations_count: allocationsResult.rows.length,
//         pending_count: pendingOrdersResult.rows.length
//       }
//     });

//   } catch (err) {
//     console.error("GET COMBINED DISPATCH QUEUE ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch dispatch queue",
//       error: err.message,
//     });
//   }
// };

// const getCombinedDispatchQueue = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;
//     const productId = req.query.productId || null;

//     if (!orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized access",
//       });
//     }

//     // =====================================================
//     // PART 1: GET EXISTING ALLOCATIONS (with allocated_qty > 0)
//     // CRITICAL: Keep partial allocations ALWAYS
//     // Only hide full allocations that are COMPLETELY dispatched
//     // =====================================================
// //     const allocationsResult = await pool.query(
// //       `
// //       SELECT 
// //         'allocation' as source_type,
// //         da.id as source_id,
// //         da.sales_order_id,
// //         da.sales_order_number,
// //         da.customer_id,
// //         da.customer_name,
// //         da.product_id,
// //         da.required_qty,
// //         da.allocated_qty,
// //         da.balance_qty,
// //         da.delivered_qty,
// //         da.available_stock,
// //         da.delivery_date,
// //         da.allocation_status,
// //         da.delivery_status,
// //         da.created_at,
// //         da.updated_at,
// //         da.allow_multiple_shipments,
// //         da.shipment_count,
// //         da.total_dispatched_qty,
// //         da.remaining_shipment_qty,
// //         da.next_dispatch_date,
        
// //         pim.item_name as product_name,
// //         pim.uom,
        
// //         sd.id as shipment_id,
// //         sd.shipment_number,
// //         sd.shipment_status,
// //         sd.vehicle_number,
// //         sd.dispatch_qty as last_dispatch_qty,
// //         sd.created_at as shipment_created_at,
// //         sd.approval_status as shipment_approval_status,
        
// //         COALESCE((
// //     SELECT SUM(dispatch_qty)
// //     FROM shipment_dispatches
// //     WHERE allocation_id = da.id
// //       AND is_deleted = false
// //       AND shipment_status = 'Completed'  -- ✅ Only count ACTUALLY completed shipments
// // ), 0) AS total_dispatched,
        
// //         CASE
// //             WHEN sd.id IS NOT NULL THEN true
// //             ELSE false
// //         END AS has_shipment

// //       FROM dispatch_allocations da
      
// //       LEFT JOIN procurement_itemmaster pim
// //           ON pim.id = da.product_id
          
// //       LEFT JOIN LATERAL (
// //           SELECT
// //               sd.id,
// //               sd.shipment_number,
// //               sd.shipment_status,
// //               sd.vehicle_number,
// //               sd.dispatch_qty,
// //               sd.created_at,
// //               sd.approval_status
// //           FROM shipment_dispatches sd
// //           WHERE sd.allocation_id = da.id
// //             AND COALESCE(sd.is_deleted, false) = false
// //           ORDER BY sd.created_at DESC
// //           LIMIT 1
// //       ) sd ON true

// //       WHERE da.org_id = $1
// //         AND ($2::int IS NULL OR da.product_id = $2)
// //         AND COALESCE(da.is_deleted, false) = false
// //         AND da.allocated_qty > 0
        
// //         -- 🔥 CRITICAL FIX: Keep partial allocations ALWAYS
// //         -- Only hide FULL allocations that are COMPLETELY dispatched
// //         AND (
// //           -- Keep all partial allocations (always show in queue)
// //           da.allocation_status = 'partial'
// //           OR
// //           -- Keep full allocations that are NOT fully dispatched
// //           (da.allocation_status = 'full' 
// //            AND COALESCE(da.total_dispatched_qty, 0) < da.allocated_qty)
// //           OR
// //           -- Keep full allocations with NO shipment
// //           (da.allocation_status = 'full' 
// //            AND sd.id IS NULL)
// //         )
      
// //       ORDER BY 
// //           CASE 
// //               WHEN da.allocation_status = 'partial' THEN 1
// //               WHEN da.allocation_status = 'full' THEN 2
// //               ELSE 3
// //           END,
// //           da.delivery_date ASC NULLS LAST,
// //           da.created_at ASC
// //       `,
// //       [orgId, productId || null]
// //     );
// const allocationsResult = await pool.query(
//   `
//   SELECT 
//     'allocation' as source_type,
//     da.id as source_id,
//     da.sales_order_id,
//     da.sales_order_number,
//     da.customer_id,
//     da.customer_name,
//     da.product_id,
//     da.required_qty,
//     da.allocated_qty,
//     da.balance_qty,
//     da.delivered_qty,
//     da.available_stock,
//     da.delivery_date,
//     da.allocation_status,
//     da.delivery_status,
//     da.created_at,
//     da.updated_at,
//     da.allow_multiple_shipments,
//     da.shipment_count,
//     da.total_dispatched_qty,
//     da.remaining_shipment_qty,
//     da.next_dispatch_date,
//     COALESCE(da.shipment_pending, false) as shipment_pending,  -- Handle NULL as false
    
//     pim.item_name as product_name,
//     pim.uom,
    
//     sd.id as shipment_id,
//     sd.shipment_number,
//     sd.shipment_status,
//     sd.vehicle_number,
//     sd.dispatch_qty as last_dispatch_qty,
//     sd.created_at as shipment_created_at,
//     sd.approval_status as shipment_approval_status,
    
//     COALESCE((
//       SELECT SUM(dispatch_qty)
//       FROM shipment_dispatches
//       WHERE allocation_id = da.id
//         AND is_deleted = false
//         AND shipment_status = 'Completed'
//     ), 0) AS total_dispatched,
    
//     CASE
//         WHEN sd.id IS NOT NULL THEN true
//         ELSE false
//     END AS has_shipment

//   FROM dispatch_allocations da
  
//   LEFT JOIN procurement_itemmaster pim
//       ON pim.id = da.product_id
      
//   LEFT JOIN LATERAL (
//       SELECT
//           sd.id,
//           sd.shipment_number,
//           sd.shipment_status,
//           sd.vehicle_number,
//           sd.dispatch_qty,
//           sd.created_at,
//           sd.approval_status
//       FROM shipment_dispatches sd
//       WHERE sd.allocation_id = da.id
//         AND COALESCE(sd.is_deleted, false) = false
//       ORDER BY sd.created_at DESC
//       LIMIT 1
//   ) sd ON true

//   WHERE da.org_id = $1
//     AND ($2::int IS NULL OR da.product_id = $2)
//     AND COALESCE(da.is_deleted, false) = false
//     AND da.allocated_qty > 0
    
//     -- 🔥 FIX: Show ALL allocations EXCEPT fully allocated ones with pending shipment
//     -- Use COALESCE to handle NULL shipment_pending as false
//     AND (
//       -- Always show partial allocations
//       da.allocation_status = 'partial'
//       OR
//       -- Show full allocations ONLY if shipment_pending is false (including NULL)
//       (da.allocation_status = 'full' AND COALESCE(da.shipment_pending, false) = false)
//     )
  
//   ORDER BY 
//       CASE 
//           WHEN da.allocation_status = 'partial' THEN 1
//           WHEN da.allocation_status = 'full' THEN 2
//           ELSE 3
//       END,
//       da.delivery_date ASC NULLS LAST,
//       da.created_at ASC
//   `,
//   [orgId, productId || null]
// );

//     // =====================================================
//     // PART 2: GET PENDING SALES ORDERS (NO allocation at all)
//     // =====================================================
//     const pendingOrdersResult = await pool.query(
//       `
//       SELECT 
//         'pending' as source_type,
//         so.id as source_id,
//         so.id as sales_order_id,
//         so.so_number as sales_order_number,
//         so.customer_id,
//         so.customer_name,
//         soi.product_id,
//         soi.qty as required_qty,
//         0 as allocated_qty,
//         soi.qty as balance_qty,
//         0 as delivered_qty,
//         0 as available_stock,
//         so.delivery_date,
//         'not_allocated' as allocation_status,
//         'Pending' as delivery_status,
//         so.created_at,
//         NULL as updated_at,
//         true as allow_multiple_shipments,
//         0 as shipment_count,
//         0 as total_dispatched_qty,
//         soi.qty as remaining_shipment_qty,
//         NULL as next_dispatch_date,
        
//         soi.product_name,
//         soi.uom_symbol as uom,
        
//         NULL as shipment_id,
//         NULL as shipment_number,
//         NULL as shipment_status,
//         NULL as vehicle_number,
//         NULL as last_dispatch_qty,
//         NULL as shipment_created_at,
//         NULL as shipment_approval_status,
        
//         0 as total_dispatched,
//         false as has_shipment

//       FROM sales_orders so
      
//       LEFT JOIN sales_order_items soi 
//           ON soi.sales_order_id = so.id
          
//       LEFT JOIN dispatch_allocations da 
//           ON da.sales_order_id = so.id 
//           AND da.is_deleted = false

//       WHERE so.org_id = $1
//         AND so.is_deleted = false
//         AND so.approval_status = 'Approved'
//         AND ($2::int IS NULL OR soi.product_id = $2)
//         -- Only get orders with NO allocation at all
//         AND da.id IS NULL
//         -- Also exclude orders that have any allocation with allocated_qty > 0
//         AND NOT EXISTS (
//           SELECT 1 FROM dispatch_allocations da2
//           WHERE da2.sales_order_id = so.id
//             AND da2.is_deleted = false
//             AND da2.allocated_qty > 0
//         )
      
//       ORDER BY so.delivery_date ASC NULLS LAST, so.created_at ASC
//       `,
//       [orgId, productId || null]
//     );

//     // =====================================================
//     // COMBINE BOTH RESULTS
//     // =====================================================
//     const combinedData = [...allocationsResult.rows, ...pendingOrdersResult.rows];

//     // Remove duplicates by sales_order_id
//     const uniqueMap = new Map();
//     for (const item of combinedData) {
//       const existing = uniqueMap.get(item.sales_order_id);
//       if (!existing || (item.source_type === 'allocation' && existing.source_type === 'pending')) {
//         uniqueMap.set(item.sales_order_id, item);
//       }
//     }
//     const finalData = Array.from(uniqueMap.values());

//     // Sort combined data: not_allocated first, then partial, then full
//     finalData.sort((a, b) => {
//       // Priority order: not_allocated (1), partial (2), full (3)
//       const order = { 'not_allocated': 1, 'partial': 2, 'full': 3 };
//       const orderA = order[a.allocation_status] || 4;
//       const orderB = order[b.allocation_status] || 4;
      
//       if (orderA !== orderB) return orderA - orderB;
      
//       // If same status, sort by delivery date
//       if (a.delivery_date && b.delivery_date) {
//         return new Date(a.delivery_date) - new Date(b.delivery_date);
//       }
//       if (a.delivery_date && !b.delivery_date) return -1;
//       if (!a.delivery_date && b.delivery_date) return 1;
      
//       return 0;
//     });

//     return res.status(200).json({
//       success: true,
//       message: "Dispatch queue fetched successfully",
//       total: finalData.length,
//       data: finalData,
//       summary: {
//         allocations_count: allocationsResult.rows.length,
//         pending_count: pendingOrdersResult.rows.length
//       }
//     });

//   } catch (err) {
//     console.error("GET COMBINED DISPATCH QUEUE ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch dispatch queue",
//       error: err.message,
//     });
//   }
// };

  const getCombinedDispatchQueue = async (req, res) => {
    try {
      const orgId = req.session?.user?.orgId;
      const productId = req.query.productId || null;

      if (!orgId) {
        return res.status(401).json({
          success: false,
          message: "Unauthorized access",
        });
      }

      // =====================================================
      // PART 1: GET CONSOLIDATED ALLOCATIONS PER SALES ORDER
      // =====================================================
      // const allocationsResult = await pool.query(
      //   `
      //   SELECT 
      //     'allocation' as source_type,
      //     MIN(da.id) as source_id,
      //     da.sales_order_id,
      //     da.sales_order_number,
      //     da.customer_id,
      //     da.customer_name,
      //     da.product_id,
      //     MAX(da.required_qty) as required_qty,
      //     SUM(da.allocated_qty) as allocated_qty,
      //     MAX(da.required_qty) - SUM(da.allocated_qty) as balance_qty,
      //     MAX(da.available_stock) as available_stock,
      //     MAX(da.delivery_date) as delivery_date,
      //     CASE 
      //         WHEN SUM(da.allocated_qty) >= MAX(da.required_qty) THEN 'full'
      //         WHEN SUM(da.allocated_qty) > 0 THEN 'partial'
      //         ELSE 'not_allocated'
      //     END as allocation_status,
      //     MAX(da.delivery_status) as delivery_status,
      //     MIN(da.created_at) as created_at,
      //     MAX(da.updated_at) as updated_at,
      //     BOOL_OR(da.allow_multiple_shipments) as allow_multiple_shipments,
      //     COUNT(*) as trip_count,
      //     SUM(COALESCE(da.total_dispatched_qty, 0)) as total_dispatched_qty,
      //     SUM(da.allocated_qty) - SUM(COALESCE(da.total_dispatched_qty, 0)) as pending_dispatch_qty,
      //     MAX(da.next_dispatch_date) as next_dispatch_date,
          
      //     pim.item_name as product_name,
      //     pim.uom,
          
      //     -- Get the latest active shipment info
      //     sd.id as shipment_id,
      //     sd.shipment_number,
      //     sd.shipment_status,
      //     sd.vehicle_number,
      //     sd.dispatch_qty as last_dispatch_qty,
      //     sd.created_at as shipment_created_at,
      //     sd.approval_status as shipment_approval_status,
          
      //     -- Check if any active shipment exists
      //     EXISTS (
      //         SELECT 1 FROM dispatch_allocations da2
      //         JOIN shipment_dispatches sd2 ON sd2.allocation_id = da2.id
      //         WHERE da2.sales_order_id = da.sales_order_id
      //           AND da2.is_deleted = false
      //           AND sd2.is_deleted = false
      //           AND sd2.shipment_status != 'Completed'
      //     ) as has_active_shipment

      //   FROM dispatch_allocations da
        
      //   LEFT JOIN procurement_itemmaster pim
      //       ON pim.id = da.product_id
            
      //   LEFT JOIN LATERAL (
      //       SELECT
      //           sd.id,
      //           sd.shipment_number,
      //           sd.shipment_status,
      //           sd.vehicle_number,
      //           sd.dispatch_qty,
      //           sd.created_at,
      //           sd.approval_status
      //       FROM shipment_dispatches sd
      //       JOIN dispatch_allocations da2 ON da2.id = sd.allocation_id
      //       WHERE da2.sales_order_id = da.sales_order_id
      //         AND COALESCE(sd.is_deleted, false) = false
      //         AND sd.shipment_status != 'Completed'
      //       ORDER BY sd.created_at DESC
      //       LIMIT 1
      //   ) sd ON true

      //   WHERE da.org_id = $1
      //     AND ($2::int IS NULL OR da.product_id = $2)
      //     AND COALESCE(da.is_deleted, false) = false
      //     AND da.allocated_qty > 0
          
      //   GROUP BY 
      //     da.sales_order_id,
      //     da.sales_order_number,
      //     da.customer_id,
      //     da.customer_name,
      //     da.product_id,
      //     pim.item_name,
      //     pim.uom,
      //     sd.id,
      //     sd.shipment_number,
      //     sd.shipment_status,
      //     sd.vehicle_number,
      //     sd.dispatch_qty,
      //     sd.created_at,
      //     sd.approval_status
        
      //   HAVING (SUM(da.allocated_qty) - SUM(COALESCE(da.total_dispatched_qty, 0))) > 0
        
      //   ORDER BY 
      //       CASE 
      //           WHEN SUM(da.allocated_qty) >= MAX(da.required_qty) THEN 3
      //           WHEN SUM(da.allocated_qty) > 0 THEN 2
      //           ELSE 1
      //       END,
      //       MAX(da.delivery_date) ASC NULLS LAST
      //   `,
      //   [orgId, productId || null]
      // );
      const allocationsResult = await pool.query(
    `
    SELECT 
      'allocation' as source_type,
      MIN(da.id) as source_id,
      da.sales_order_id,
      da.sales_order_number,
      da.customer_id,
      da.customer_name,
      da.product_id,
      MAX(da.required_qty) as required_qty,
      SUM(da.allocated_qty) as allocated_qty,
      MAX(da.required_qty) - SUM(da.allocated_qty) as balance_qty,
      MAX(da.available_stock) as available_stock,
      MAX(da.delivery_date) as delivery_date,
      CASE 
          WHEN SUM(da.allocated_qty) >= MAX(da.required_qty) THEN 'full'
          WHEN SUM(da.allocated_qty) > 0 THEN 'partial'
          ELSE 'not_allocated'
      END as allocation_status,
      MAX(da.delivery_status) as delivery_status,
      MIN(da.created_at) as created_at,
      MAX(da.updated_at) as updated_at,
      BOOL_OR(da.allow_multiple_shipments) as allow_multiple_shipments,
      COUNT(*) as trip_count,
      SUM(COALESCE(da.total_dispatched_qty, 0)) as total_dispatched_qty,
      SUM(da.allocated_qty) - SUM(COALESCE(da.total_dispatched_qty, 0)) as pending_dispatch_qty,
      MAX(da.next_dispatch_date) as next_dispatch_date,
      
      pim.item_name as product_name,
      pim.uom,
      
      -- Get the latest active shipment info
      sd.id as shipment_id,
      sd.shipment_number,
      sd.shipment_status,
      sd.vehicle_number,
      sd.dispatch_qty as last_dispatch_qty,
      sd.created_at as shipment_created_at,
      sd.approval_status as shipment_approval_status,
      
      -- Check if any active shipment exists
      EXISTS (
          SELECT 1 FROM dispatch_allocations da2
          JOIN shipment_dispatches sd2 ON sd2.allocation_id = da2.id
          WHERE da2.sales_order_id = da.sales_order_id
            AND da2.is_deleted = false
            AND sd2.is_deleted = false
            AND sd2.shipment_status != 'Completed'
      ) as has_active_shipment

    FROM dispatch_allocations da
    
    LEFT JOIN procurement_itemmaster pim
        ON pim.id = da.product_id
        
    LEFT JOIN LATERAL (
        SELECT
            sd.id,
            sd.shipment_number,
            sd.shipment_status,
            sd.vehicle_number,
            sd.dispatch_qty,
            sd.created_at,
            sd.approval_status
        FROM shipment_dispatches sd
        JOIN dispatch_allocations da2 ON da2.id = sd.allocation_id
        WHERE da2.sales_order_id = da.sales_order_id
          AND COALESCE(sd.is_deleted, false) = false
          AND sd.shipment_status != 'Completed'
        ORDER BY sd.created_at DESC
        LIMIT 1
    ) sd ON true

    WHERE da.org_id = $1
      AND ($2::int IS NULL OR da.product_id = $2)
      AND COALESCE(da.is_deleted, false) = false
      AND da.allocated_qty > 0
      AND COALESCE(da.vehicle_assigned, false) = false   -- 👈 ADD THIS LINE HERE
      
    GROUP BY 
      da.sales_order_id,
      da.sales_order_number,
      da.customer_id,
      da.customer_name,
      da.product_id,
      pim.item_name,
      pim.uom,
      sd.id,
      sd.shipment_number,
      sd.shipment_status,
      sd.vehicle_number,
      sd.dispatch_qty,
      sd.created_at,
      sd.approval_status
    
    HAVING (SUM(da.allocated_qty) - SUM(COALESCE(da.total_dispatched_qty, 0))) > 0
    
    ORDER BY 
        CASE 
            WHEN SUM(da.allocated_qty) >= MAX(da.required_qty) THEN 3
            WHEN SUM(da.allocated_qty) > 0 THEN 2
            ELSE 1
        END,
        MAX(da.delivery_date) ASC NULLS LAST
    `,
    [orgId, productId || null]
  );

      // =====================================================
      // PART 2: GET PENDING SALES ORDERS (NO allocation at all)
      // =====================================================
      const pendingOrdersResult = await pool.query(
        `
        SELECT 
          'pending' as source_type,
          so.id as source_id,
          so.id as sales_order_id,
          so.so_number as sales_order_number,
          so.customer_id,
          so.customer_name,
          soi.product_id,
          soi.qty as required_qty,
          0 as allocated_qty,
          soi.qty as balance_qty,
          0 as available_stock,
          so.delivery_date,
          'not_allocated' as allocation_status,
          'Pending' as delivery_status,
          so.created_at,
          NULL as updated_at,
          true as allow_multiple_shipments,
          0 as trip_count,
          0 as total_dispatched_qty,
          soi.qty as pending_dispatch_qty,
          NULL as next_dispatch_date,
          soi.product_name,
          soi.uom_symbol as uom,
          NULL as shipment_id,
          NULL as shipment_number,
          NULL as shipment_status,
          NULL as vehicle_number,
          NULL as last_dispatch_qty,
          NULL as shipment_created_at,
          NULL as shipment_approval_status,
          false as has_active_shipment,
          so.advance_payment_status

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

        WHERE so.org_id = $1
          AND so.is_deleted = false
          AND so.approval_status = 'Approved'
          AND ($2::int IS NULL OR soi.product_id = $2)
          
          AND (
            so.advance_payment_status = 'paid'
            OR so.advance_payment_status IS NULL
            OR so.advance_payment_status = ''
          )
          
          -- Only include if there's NO allocation at all
          AND NOT EXISTS (
            SELECT 1 FROM dispatch_allocations da2
            WHERE da2.sales_order_id = so.id
              AND da2.is_deleted = false
              AND da2.allocated_qty > 0
          )
        
        ORDER BY so.delivery_date ASC NULLS LAST, so.created_at ASC
        `,
        [orgId, productId || null]
      );

      // Combine both results
      const combinedData = [...allocationsResult.rows, ...pendingOrdersResult.rows];

      return res.status(200).json({
        success: true,
        message: "Dispatch queue fetched successfully",
        total: combinedData.length,
        data: combinedData,
        summary: {
          allocations_count: allocationsResult.rows.length,
          pending_count: pendingOrdersResult.rows.length
        }
      });

    } catch (err) {
      console.error("GET COMBINED DISPATCH QUEUE ERROR:", err);
      return res.status(500).json({
        success: false,
        message: "Failed to fetch dispatch queue",
        error: err.message,
      });
    }
  };
// const getCombinedDispatchQueue = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;
//     const productId = req.query.productId || null;

//     if (!orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized access",
//       });
//     }

//     // =====================================================
//     // PART 1: GET EXISTING ALLOCATIONS (with allocated_qty > 0)
//     // =====================================================
//     const allocationsResult = await pool.query(
//       `
//       SELECT 
//         'allocation' as source_type,
//         da.id as source_id,
//         da.sales_order_id,
//         da.sales_order_number,
//         da.customer_id,
//         da.customer_name,
//         da.product_id,
//         da.required_qty,
//         da.allocated_qty,
//         da.balance_qty,
//         da.delivered_qty,
//         da.available_stock,
//         da.delivery_date,
//         da.allocation_status,
//         da.delivery_status,
//         da.created_at,
//         da.updated_at,
//         da.allow_multiple_shipments,
//         da.shipment_count,
//         da.total_dispatched_qty,
//         da.remaining_shipment_qty,
//         da.next_dispatch_date,
//         COALESCE(da.shipment_pending, false) as shipment_pending,
        
//         pim.item_name as product_name,
//         pim.uom,
        
//         sd.id as shipment_id,
//         sd.shipment_number,
//         sd.shipment_status,
//         sd.vehicle_number,
//         sd.dispatch_qty as last_dispatch_qty,
//         sd.created_at as shipment_created_at,
//         sd.approval_status as shipment_approval_status,
        
//         COALESCE((
//           SELECT SUM(dispatch_qty)
//           FROM shipment_dispatches
//           WHERE allocation_id = da.id
//             AND is_deleted = false
//             AND shipment_status = 'Completed'
//         ), 0) AS total_dispatched,
        
//         CASE
//             WHEN sd.id IS NOT NULL THEN true
//             ELSE false
//         END AS has_shipment

//       FROM dispatch_allocations da
      
//       LEFT JOIN procurement_itemmaster pim
//           ON pim.id = da.product_id
          
//       LEFT JOIN LATERAL (
//           SELECT
//               sd.id,
//               sd.shipment_number,
//               sd.shipment_status,
//               sd.vehicle_number,
//               sd.dispatch_qty,
//               sd.created_at,
//               sd.approval_status
//           FROM shipment_dispatches sd
//           WHERE sd.allocation_id = da.id
//             AND COALESCE(sd.is_deleted, false) = false
//           ORDER BY sd.created_at DESC
//           LIMIT 1
//       ) sd ON true

//       WHERE da.org_id = $1
//         AND ($2::int IS NULL OR da.product_id = $2)
//         AND COALESCE(da.is_deleted, false) = false
//         AND da.allocated_qty > 0
        
//         -- 🔥 FIX: Show ALL allocations EXCEPT fully allocated ones with pending shipment
//         -- Use COALESCE to handle NULL shipment_pending as false
//         AND NOT (
//           da.allocation_status = 'full' 
//           AND COALESCE(da.shipment_pending, false) = true
//         )
      
//       ORDER BY 
//           CASE 
//               WHEN da.allocation_status = 'partial' THEN 1
//               WHEN da.allocation_status = 'full' THEN 2
//               ELSE 3
//           END,
//           da.delivery_date ASC NULLS LAST,
//           da.created_at ASC
//       `,
//       [orgId, productId || null]
//     );

//     // =====================================================
//     // PART 2: GET PENDING SALES ORDERS (NO allocation at all)
//     // =====================================================
//  const pendingOrdersResult = await pool.query(
//   `
//   SELECT 
//     'pending' as source_type,
//     so.id as source_id,
//     so.id as sales_order_id,
//     so.so_number as sales_order_number,
//     so.customer_id,
//     so.customer_name,
//     soi.product_id,
//     soi.qty as required_qty,
//     0 as allocated_qty,
//     soi.qty as balance_qty,
//     0 as delivered_qty,
//     0 as available_stock,
//     so.delivery_date,
//     'not_allocated' as allocation_status,
//     'Pending' as delivery_status,
//     so.created_at,
//     NULL as updated_at,
//     true as allow_multiple_shipments,
//     0 as shipment_count,
//     0 as total_dispatched_qty,
//     soi.qty as remaining_shipment_qty,
//     NULL as next_dispatch_date,
    
//     soi.product_name,
//     soi.uom_symbol as uom,
    
//     NULL as shipment_id,
//     NULL as shipment_number,
//     NULL as shipment_status,
//     NULL as vehicle_number,
//     NULL as last_dispatch_qty,
//     NULL as shipment_created_at,
//     NULL as shipment_approval_status,
    
//     0 as total_dispatched,
//     false as has_shipment,
    
//     -- Include advance payment status for reference
//     so.advance_payment_status

//   FROM sales_orders so
  
//   LEFT JOIN sales_order_items soi 
//       ON soi.sales_order_id = so.id

//   WHERE so.org_id = $1
//     AND so.is_deleted = false
//     AND so.approval_status = 'Approved'
//     AND ($2::int IS NULL OR soi.product_id = $2)
    
//     -- 🔥 Include orders with:
//     -- 1. advance_payment_status = 'paid' (advance paid)
//     -- 2. OR advance_payment_status IS NULL (no advance required)
//     -- 3. OR advance_payment_status = '' (empty string)
//     AND (
//       so.advance_payment_status = 'paid'
//       OR so.advance_payment_status IS NULL
//       OR so.advance_payment_status = ''
//     )
    
//     -- Only include if there's NO active allocation (allocated_qty > 0)
//     AND NOT EXISTS (
//       SELECT 1 FROM dispatch_allocations da2
//       WHERE da2.sales_order_id = so.id
//         AND da2.is_deleted = false
//         AND da2.allocated_qty > 0
//     )
  
//   ORDER BY so.delivery_date ASC NULLS LAST, so.created_at ASC
//   `,
//   [orgId, productId || null]
// );


//     // =====================================================
//     // COMBINE BOTH RESULTS
//     // =====================================================
//     const combinedData = [...allocationsResult.rows, ...pendingOrdersResult.rows];

//     // Remove duplicates by sales_order_id
//     const uniqueMap = new Map();
//     for (const item of combinedData) {
//       const existing = uniqueMap.get(item.sales_order_id);
//       if (!existing || (item.source_type === 'allocation' && existing.source_type === 'pending')) {
//         uniqueMap.set(item.sales_order_id, item);
//       }
//     }
//     const finalData = Array.from(uniqueMap.values());

//     // Sort combined data
//     finalData.sort((a, b) => {
//       const order = { 'not_allocated': 1, 'partial': 2, 'full': 3 };
//       const orderA = order[a.allocation_status] || 4;
//       const orderB = order[b.allocation_status] || 4;
      
//       if (orderA !== orderB) return orderA - orderB;
      
//       if (a.delivery_date && b.delivery_date) {
//         return new Date(a.delivery_date) - new Date(b.delivery_date);
//       }
//       if (a.delivery_date && !b.delivery_date) return -1;
//       if (!a.delivery_date && b.delivery_date) return 1;
      
//       return 0;
//     });

//     return res.status(200).json({
//       success: true,
//       message: "Dispatch queue fetched successfully",
//       total: finalData.length,
//       data: finalData,
//       summary: {
//         allocations_count: allocationsResult.rows.length,
//         pending_count: pendingOrdersResult.rows.length
//       }
//     });

//   } catch (err) {
//     console.error("GET COMBINED DISPATCH QUEUE ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch dispatch queue",
//       error: err.message,
//     });
//   }
// };



// const getCombinedDispatchQueue = async (req, res) => {
//   try {
//     const orgId = req.session?.user?.orgId;
//     const productId = req.query.productId || null;

//     if (!orgId) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized access",
//       });
//     }

//     // =====================================================
//     // PART 1: GET EXISTING ALLOCATIONS (with allocated_qty > 0)
//     // Including those with partial shipments
//     // =====================================================
//     const allocationsResult = await pool.query(
//       `
//       SELECT 
//         'allocation' as source_type,
//         da.id as source_id,
//         da.sales_order_id,
//         da.sales_order_number,
//         da.customer_id,
//         da.customer_name,
//         da.product_id,
//         da.required_qty,
//         da.allocated_qty,
//         da.balance_qty,
//         da.delivered_qty,
//         da.available_stock,
//         da.delivery_date,
//         da.allocation_status,
//         da.delivery_status,
//         da.created_at,
//         da.updated_at,
//         da.allow_multiple_shipments,
//         da.shipment_count,
//         da.total_dispatched_qty,
//         da.remaining_shipment_qty,
//         da.next_dispatch_date,
        
//         pim.item_name as product_name,
//         pim.uom,
        
//         sd.id as shipment_id,
//         sd.shipment_number,
//         sd.shipment_status,
//         sd.vehicle_number,
//         sd.dispatch_qty as last_dispatch_qty,
//         sd.created_at as shipment_created_at,
//         sd.approval_status as shipment_approval_status,
        
//         COALESCE((
//             SELECT SUM(dispatch_qty)
//             FROM shipment_dispatches
//             WHERE allocation_id = da.id
//               AND is_deleted = false
//               AND shipment_status NOT IN ('Cancelled')
//         ), 0) AS total_dispatched,
        
//         CASE
//             WHEN EXISTS (
//                 SELECT 1 FROM shipment_dispatches
//                 WHERE allocation_id = da.id
//                   AND is_deleted = false
//                   AND shipment_status IN ('Pending', 'Gate In', 'Loaded', 'Dispatched')
//             ) THEN true
//             ELSE false
//         END AS has_active_shipment

//       FROM dispatch_allocations da
      
//       LEFT JOIN procurement_itemmaster pim
//           ON pim.id = da.product_id
          
//       LEFT JOIN LATERAL (
//           SELECT
//               sd.id,
//               sd.shipment_number,
//               sd.shipment_status,
//               sd.vehicle_number,
//               sd.dispatch_qty,
//               sd.created_at,
//               sd.approval_status
//           FROM shipment_dispatches sd
//           WHERE sd.allocation_id = da.id
//             AND COALESCE(sd.is_deleted, false) = false
//           ORDER BY sd.created_at DESC
//           LIMIT 1
//       ) sd ON true

//       WHERE da.org_id = $1
//         AND ($2::int IS NULL OR da.product_id = $2)
//         AND COALESCE(da.is_deleted, false) = false
//         AND da.allocated_qty > 0
//         -- Keep partial allocations even if they have shipments (remaining balance > 0)
//         AND (da.balance_qty > 0 OR da.allocation_status = 'full')
      
//       ORDER BY 
//           CASE 
//               WHEN da.allocation_status = 'partial' THEN 1
//               WHEN da.allocation_status = 'full' THEN 2
//               ELSE 3
//           END,
//           da.delivery_date ASC NULLS LAST,
//           da.created_at ASC
//       `,
//       [orgId, productId || null]
//     );

//     // =====================================================
//     // PART 2: GET PENDING SALES ORDERS (NO allocation at all)
//     // =====================================================
//     const pendingOrdersResult = await pool.query(
//       `
//       SELECT 
//         'pending' as source_type,
//         so.id as source_id,
//         so.id as sales_order_id,
//         so.so_number as sales_order_number,
//         so.customer_id,
//         so.customer_name,
//         soi.product_id,
//         soi.qty as required_qty,
//         0 as allocated_qty,
//         soi.qty as balance_qty,
//         0 as delivered_qty,
//         0 as available_stock,
//         so.delivery_date,
//         'not_allocated' as allocation_status,
//         'Pending' as delivery_status,
//         so.created_at,
//         NULL as updated_at,
//         true as allow_multiple_shipments,
//         0 as shipment_count,
//         0 as total_dispatched_qty,
//         soi.qty as remaining_shipment_qty,
//         NULL as next_dispatch_date,
        
//         soi.product_name,
//         soi.uom_symbol as uom,
        
//         NULL as shipment_id,
//         NULL as shipment_number,
//         NULL as shipment_status,
//         NULL as vehicle_number,
//         NULL as last_dispatch_qty,
//         NULL as shipment_created_at,
//         NULL as shipment_approval_status,
        
//         0 as total_dispatched,
//         false as has_active_shipment

//       FROM sales_orders so
      
//       LEFT JOIN sales_order_items soi 
//           ON soi.sales_order_id = so.id
          
//       LEFT JOIN dispatch_allocations da 
//           ON da.sales_order_id = so.id 
//           AND da.is_deleted = false

//       WHERE so.org_id = $1
//         AND so.is_deleted = false
//         AND so.approval_status = 'Approved'
//         AND ($2::int IS NULL OR soi.product_id = $2)
//         AND (da.id IS NULL OR da.allocated_qty = 0 OR da.allocated_qty < soi.qty)
      
//       ORDER BY so.delivery_date ASC NULLS LAST, so.created_at ASC
//       `,
//       [orgId, productId || null]
//     );

//     // =====================================================
//     // COMBINE BOTH RESULTS
//     // =====================================================
//     const combinedData = [...allocationsResult.rows, ...pendingOrdersResult.rows];

//     // Remove duplicates by sales_order_id (keep allocation version if exists)
//     const uniqueMap = new Map();
//     for (const item of combinedData) {
//       const existing = uniqueMap.get(item.sales_order_id);
//       if (!existing || (item.source_type === 'allocation' && existing.source_type === 'pending')) {
//         uniqueMap.set(item.sales_order_id, item);
//       }
//     }
//     const finalData = Array.from(uniqueMap.values());

//     // Sort combined data
//     finalData.sort((a, b) => {
//       if (a.allocation_status === 'not_allocated' && b.allocation_status !== 'not_allocated') return -1;
//       if (b.allocation_status === 'not_allocated' && a.allocation_status !== 'not_allocated') return 1;
//       if (a.allocation_status === 'partial' && b.allocation_status !== 'partial') return -1;
//       if (b.allocation_status === 'partial' && a.allocation_status !== 'partial') return 1;
//       if (a.delivery_date && b.delivery_date) {
//         return new Date(a.delivery_date) - new Date(b.delivery_date);
//       }
//       if (a.delivery_date && !b.delivery_date) return -1;
//       if (!a.delivery_date && b.delivery_date) return 1;
//       return 0;
//     });

//     return res.status(200).json({
//       success: true,
//       message: "Dispatch queue fetched successfully",
//       total: finalData.length,
//       data: finalData,
//       summary: {
//         allocations_count: allocationsResult.rows.length,
//         pending_count: pendingOrdersResult.rows.length
//       }
//     });

//   } catch (err) {
//     console.error("GET COMBINED DISPATCH QUEUE ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch dispatch queue",
//       error: err.message,
//     });
//   }
// };

const getAllocationDetails = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const { allocationId } = req.params;

    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized access",
      });
    }

    const result = await pool.query(
      `
      SELECT 
        da.id,
        da.sales_order_id,
        da.sales_order_number,
        da.customer_id,
        da.customer_name,
        da.product_id,
        da.required_qty,
        da.allocated_qty,
        da.balance_qty,
        da.delivered_qty,
        da.available_stock,
        da.delivery_date,
        da.allocation_status,
        da.delivery_status,
        da.created_at,
        da.updated_at,
        da.allow_multiple_shipments,
        da.shipment_count,
        da.total_dispatched_qty,
        da.remaining_shipment_qty,
        da.next_dispatch_date,
        da.remarks,
        
        /* PRODUCT INFO */
        pim.item_name as product_name,
        pim.uom,
        pim.uom_symbol,
        
        /* SALES ORDER INFO */
        so.created_at as so_created_at,
        so.approval_status as so_approval_status,
        
        /* ALL SHIPMENTS FOR THIS ALLOCATION */
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'id', sd.id,
                'shipment_number', sd.shipment_number,
                'shipment_status', sd.shipment_status,
                'vehicle_number', sd.vehicle_number,
                'dispatch_qty', sd.dispatch_qty,
                'driver_name', sd.driver_name,
                'created_at', sd.created_at,
                'expected_delivery_datetime', sd.expected_delivery_datetime
              )
              ORDER BY sd.created_at DESC
            )
            FROM shipment_dispatches sd
            WHERE sd.allocation_id = da.id
              AND COALESCE(sd.is_deleted, false) = false
          ),
          '[]'::json
        ) as shipments,
        
        /* TOTAL DISPATCHED */
        COALESCE((
          SELECT SUM(dispatch_qty)
          FROM shipment_dispatches
          WHERE allocation_id = da.id
            AND is_deleted = false
            AND shipment_status NOT IN ('Cancelled')
        ), 0) AS total_dispatched,
        
        /* CANCEL REMARKS IF ANY */
        da.cancel_remarks,
        
        /* CREATED BY / UPDATED BY */
        creator.name as created_by_name,
        updater.name as updated_by_name

      FROM dispatch_allocations da
      
      LEFT JOIN procurement_itemmaster pim
          ON pim.id = da.product_id
          
      LEFT JOIN sales_orders so
          ON so.id = da.sales_order_id
          
      LEFT JOIN users creator
          ON creator.id = da.created_by
          
      LEFT JOIN users updater
          ON updater.id = da.updated_by

      WHERE da.org_id = $1
        AND da.id = $2
        AND COALESCE(da.is_deleted, false) = false
      `,
      [orgId, allocationId]
    );

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

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

  } catch (err) {
    console.error("GET ALLOCATION DETAILS ERROR:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch allocation details",
      error: err.message,
    });
  }
};

// Cancel allocation (partial or full)
// const cancelAllocation = async (req, res) => {
//   const orgId = req.session?.user?.orgId;
//   const userId = req.session?.user?.id;
//   const client = await pool.connect();

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

//     const { allocation_id, cancel_qty, remarks } = req.body;
//     const cancelQty = Number(cancel_qty || 0);

//     if (cancelQty <= 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Invalid cancellation quantity",
//       });
//     }

//     // Get allocation details
//     const allocationResult = await client.query(
//       `
//       SELECT * FROM dispatch_allocations
//       WHERE id = $1 AND org_id = $2 AND is_deleted = false
//       `,
//       [allocation_id, orgId]
//     );

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

//     const allocation = allocationResult.rows[0];
//     const currentAllocated = Number(allocation.allocated_qty || 0);
//     const totalDispatched = Number(allocation.total_dispatched_qty || 0);
//     const availableToCancel = currentAllocated - totalDispatched;

//     if (cancelQty > availableToCancel) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: `Cannot cancel more than ${availableToCancel} units. Already dispatched: ${totalDispatched}`,
//         maxCancelable: availableToCancel
//       });
//     }

//     const newAllocated = currentAllocated - cancelQty;
//     const balanceQty = allocation.required_qty - newAllocated;

//     // Determine new status
//     let allocationStatus = "waiting";
//     if (balanceQty === 0) allocationStatus = "full";
//     else if (newAllocated > 0) allocationStatus = "partial";

//     let deliveryStatus = "Pending";
//     if (newAllocated > 0 && balanceQty > 0) deliveryStatus = "Partial";
//     if (balanceQty === 0) deliveryStatus = "Ready";

//     // Update allocation
//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET allocated_qty = $1,
//           balance_qty = $2,
//           allocation_status = $3,
//           delivery_status = $4,
//           cancel_remarks = $5,
//           updated_by = $6,
//           updated_at = CURRENT_TIMESTAMP
//       WHERE id = $7
//       `,
//       [newAllocated, balanceQty, allocationStatus, deliveryStatus, remarks, userId, allocation.id]
//     );

//     // Return stock to inventory
//     await client.query(
//       `
//       UPDATE inventory_stock
//       SET available_qty = available_qty + $1,
//           reserved_qty = GREATEST(reserved_qty - $1, 0),
//           updated_at = CURRENT_TIMESTAMP
//       WHERE product_id = $2 AND org_id = $3
//       `,
//       [cancelQty, allocation.product_id, orgId]
//     );

//     // Insert history
//     await client.query(
//       `
//       INSERT INTO dispatch_allocation_history (
//         allocation_id, sales_order_id, product_id, allocated_qty,
//         balance_after, allocation_type, dispatch_status, remarks, created_by
//       ) VALUES ($1, $2, $3, $4, $5, 'cancellation', $6, $7, $8)
//       `,
//       [allocation.id, allocation.sales_order_id, allocation.product_id, 
//        -cancelQty, balanceQty, allocationStatus, remarks, userId]
//     );

//     // If allocation becomes zero, mark as deleted
//     if (newAllocated <= 0) {
//       await client.query(
//         `
//         UPDATE dispatch_allocations
//         SET is_deleted = true,
//             cancel_remarks = $1,
//             updated_by = $2,
//             updated_at = CURRENT_TIMESTAMP
//         WHERE id = $3
//         `,
//         [remarks, userId, allocation.id]
//       );
//     }

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

//     return res.json({
//       success: true,
//       message: `Successfully cancelled ${cancelQty} units`,
//       data: {
//         allocation_id: allocation.id,
//         cancelled_qty: cancelQty,
//         remaining_allocated: newAllocated,
//         balance_qty: balanceQty,
//         allocation_status: allocationStatus
//       }
//     });

//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("CANCEL ALLOCATION ERROR:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to cancel allocation",
//       error: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

// Create shipment from allocation
const createShipmentFromAllocation = async (req, res) => {
  const orgId = req.session?.user?.orgId;
  const userId = req.session?.user?.id;
  const client = await pool.connect();

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

    const { allocation_id, vehicle_number, dispatch_qty, remarks, expected_delivery_date } = req.body;
    const dispatchQty = Number(dispatch_qty || 0);

    if (dispatchQty <= 0) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: "Invalid dispatch quantity",
      });
    }

    // Get allocation details
    const allocationResult = await client.query(
      `
      SELECT da.*, pim.uom_symbol, pim.item_name
      FROM dispatch_allocations da
      LEFT JOIN procurement_itemmaster pim ON pim.id = da.product_id
      WHERE da.id = $1 AND da.org_id = $2 AND da.is_deleted = false
      `,
      [allocation_id, orgId]
    );

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

    const allocation = allocationResult.rows[0];
    const remainingToDispatch = allocation.allocated_qty - (allocation.total_dispatched_qty || 0);

    if (dispatchQty > remainingToDispatch) {
      await client.query("ROLLBACK");
      return res.status(400).json({
        success: false,
        message: `Cannot dispatch more than remaining quantity. Remaining: ${remainingToDispatch}`,
        remainingQty: remainingToDispatch
      });
    }

    // Generate shipment number
    const shipmentNumber = `SHP-${Date.now()}-${Math.floor(Math.random() * 1000)}`;

    // Create shipment
    const shipmentResult = await client.query(
      `
      INSERT INTO shipment_dispatches (
        org_id, allocation_id, sales_order_id, sales_order_number,
        customer_name, product_id, product_name,
        allocated_qty, dispatch_qty, uom,
        vehicle_number, shipment_number, shipment_status,
        expected_delivery_datetime, remarks, created_by, created_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, CURRENT_TIMESTAMP)
      RETURNING *
      `,
      [
        orgId, allocation.id, allocation.sales_order_id, allocation.sales_order_number,
        allocation.customer_name, allocation.product_id, allocation.item_name,
        dispatchQty, dispatchQty, allocation.uom_symbol,
        vehicle_number, shipmentNumber, 'Pending',
        expected_delivery_date || null, remarks || null, userId
      ]
    );

    const shipment = shipmentResult.rows[0];

    // Update allocation with shipment info
    const newTotalDispatched = (allocation.total_dispatched_qty || 0) + dispatchQty;
    const newRemainingShipment = allocation.allocated_qty - newTotalDispatched;
    const newShipmentCount = (allocation.shipment_count || 0) + 1;

    await client.query(
      `
      UPDATE dispatch_allocations
      SET total_dispatched_qty = $1,
          remaining_shipment_qty = $2,
          shipment_count = $3,
          last_shipment_date = CURRENT_TIMESTAMP,
          active_shipment_id = $4,
          updated_by = $5,
          updated_at = CURRENT_TIMESTAMP
      WHERE id = $6
      `,
      [newTotalDispatched, newRemainingShipment, newShipmentCount, shipment.id, userId, allocation.id]
    );

    // Update inventory (reduce reserved, remove from available if needed)
    await client.query(
      `
      UPDATE inventory_stock
      SET reserved_qty = GREATEST(reserved_qty - $1, 0),
          available_qty = GREATEST(available_qty - $1, 0),
          updated_at = CURRENT_TIMESTAMP
      WHERE product_id = $2 AND org_id = $3
      `,
      [dispatchQty, allocation.product_id, orgId]
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: `Shipment created successfully for ${dispatchQty} units`,
      data: {
        shipment: shipment,
        allocation: {
          id: allocation.id,
          total_dispatched: newTotalDispatched,
          remaining_shipment_qty: newRemainingShipment,
          shipment_count: newShipmentCount
        }
      }
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CREATE SHIPMENT ERROR:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to create shipment",
      error: err.message,
    });
  } finally {
    client.release();
  }
};


// Backend API endpoint
// In your dispatchController.js

// const getRecentAllocations = async (req, res) => {
//   try {
//     const query = `
//       SELECT 
//         da.id AS allocation_id,
//         da.sales_order_id,
//         da.sales_order_number,
//         da.customer_id,
//         da.customer_name,
//         da.product_id,
//          p.uom AS uom,
//         p.item_name AS product_name,
//         da.required_qty,
//         da.allocated_qty,
//         da.balance_qty,
       
//         da.created_at AS allocated_date,
//         da.delivery_date,
//         da.vehicle_assigned,
//         da.shipment_completed,
//         da.allocation_status,
//         da.trip_number
       
//       FROM dispatch_allocations da
//       LEFT JOIN procurement_itemmaster p ON da.product_id = p.id
//       LEFT JOIN sales_orders so ON da.sales_order_id = so.id
//       WHERE da.allocated_qty > 0
//         AND da.is_deleted = false
//         AND da.balance_qty > 0
//         AND (da.vehicle_assigned = false OR da.vehicle_assigned IS NULL)
//         AND (da.active_shipment_id IS NULL OR da.active_shipment_id = 0)
//       ORDER BY da.created_at DESC
//       LIMIT 20
//     `;
    
//     const result = await pool.query(query);
    
//     res.json({
//       success: true,
//       data: result.rows
//     });
//   } catch (error) {
//     console.error('Error fetching allocations without shipment:', error);
//     res.status(500).json({
//       success: false,
//       message: 'Failed to fetch allocations'
//     });
//   }
// };
// In your backend - update getRecentAllocations function
const getRecentAllocations = async (req, res) => {
  try {
    const query = `
      SELECT 
        da.id AS allocation_id,
        da.sales_order_id,
        da.sales_order_number,
        da.customer_id,
        da.customer_name,
        da.product_id,
        p.uom AS uom,
        p.item_name AS product_name,
        p.group_id AS material_type_id,
        p.group_name AS material_type_name,  -- Directly from procurement_itemmaster
        da.required_qty,
        da.allocated_qty,
        da.balance_qty,
        da.created_at AS allocated_date,
        da.delivery_date,
        da.vehicle_assigned,
        da.shipment_completed,
        da.allocation_status,
        da.trip_number
      FROM dispatch_allocations da
      LEFT JOIN procurement_itemmaster p ON da.product_id = p.id
      LEFT JOIN sales_orders so ON da.sales_order_id = so.id
      WHERE da.allocated_qty > 0
        AND da.is_deleted = false
        AND (da.vehicle_entry_id IS NULL OR da.vehicle_entry_id = 0)
        AND (da.vehicle_assigned = false OR da.vehicle_assigned IS NULL)
        AND (da.active_shipment_id IS NULL OR da.active_shipment_id = 0)
      ORDER BY da.created_at DESC
      LIMIT 20
    `;
    
    const result = await pool.query(query);
    
    res.json({
      success: true,
      data: result.rows
    });
  } catch (error) {
    console.error('Error fetching allocations:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to fetch allocations'
    });
  }
};


// Helper function to determine priority based on delivery date
function getPriorityStatus(deliveryDate) {
  if (!deliveryDate) return 'normal';
  const today = new Date();
  const delivery = new Date(deliveryDate);
  const daysDiff = Math.ceil((delivery - today) / (1000 * 60 * 60 * 24));
  
  if (daysDiff < 0) return 'urgent';
  if (daysDiff <= 3) return 'high';
  if (daysDiff <= 7) return 'medium';
  return 'low';
}


// Backend endpoint to update allocation with vehicle assignment
const assignVehicleToAllocation = async (req, res) => {
  const { allocationId } = req.params;
  const { vehicleEntryId, vehicleNumber } = req.body;

  try {
    const query = `
      UPDATE dispatch_allocations 
      SET 
        vehicle_entry_id = $1,
        vehicle_entry_number = $2,
        vehicle_assigned = true,
        vehicle_assigned_date = CURRENT_TIMESTAMP,
        allocation_status = 'in_progress',
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $3
      RETURNING *
    `;

    const result = await pool.query(query, [vehicleEntryId, vehicleNumber, allocationId]);

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

    res.json({
      success: true,
      data: result.rows[0],
      message: 'Vehicle assigned successfully'
    });
  } catch (error) {
    console.error('Error assigning vehicle:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to assign vehicle'
    });
  }
};


module.exports = {
  assignVehicleToAllocation,
  getShipmentBySalesOrderId,
  getDispatchDashboardProducts,
  getProductDispatchAllocations,
  getReadyForDispatch,
  // runAutoAllocation,
  createShipment,
  manualAllocateStock,
  cancelAllocation, 
  getAllocationDetails,
  getDispatchedShipments,
  getShipmentById,
  approveShipment,
  getShipmentApprovals,
  getCombinedDispatchQueue, 
  getAllShipments,
  getRecentAllocations,
  // viewInvoicePDF,
};
