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

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

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

//         pim.id AS product_id,

//         pim.item_name,

//         pim.uom,

//         (COALESCE(stock.available_qty, 0) - COALESCE(alloc.total_allocated, 0)) AS available_stock,

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

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

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

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

//         CASE

//           WHEN COALESCE(alloc.total_required, 0) = 0
//           THEN 0

//           ELSE ROUND(
//             (
//               COALESCE(alloc.total_allocated, 0)
//               /
//               alloc.total_required
//             ) * 100,
//             0
//           )

//         END AS allocation_percentage

//       FROM procurement_itemmaster pim

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

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

//         GROUP BY product_id

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

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

//       AND pim.is_deleted = false

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

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

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


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

//         pim.id AS product_id,

//         pim.item_name,

//         pim.uom,

//         /* ===================================================== */
//         /* AVAILABLE STOCK */
//         /* ===================================================== */

//         COALESCE(stock.available_qty, 0) AS available_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,

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

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

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

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

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

//         CASE

//           WHEN COALESCE(alloc.total_required, 0) = 0
//           THEN 0

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

//         END AS allocation_percentage

//       FROM procurement_itemmaster pim

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

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

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

//     // const { productId } = req.params;
//     const productId = req.params.productId || null;

//     // 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.vehicle_assigned,

//     //       da.gate_pass_generated,

//     //       da.shipment_completed,

//     //       da.priority,

//     //       da.remarks,

//     //       da.allocation_status,

//     //       stock.available_qty,

//     //       stock.reserved_qty,

//     //       pim.item_name,

//     //       pim.uom,

//     //       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.product_id = $2

//     //   ORDER BY

//     //   CASE
//     //       WHEN da.allocation_status = 'full' THEN 1
//     //       WHEN da.allocation_status = 'partial' THEN 2
//     //       ELSE 3
//     //   END,

//     //   da.delivery_date ASC,

//     //   da.priority ASC,

//     //   da.created_at ASC

//     //   LIMIT 5
//     //   `,
//     //   [orgId, productId],
//     // );

//     //   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 AS item_id,
//     //     da.required_qty,
//     //     da.allocated_qty,
//     //     da.balance_qty,
//     //     da.delivery_date,
//     //     da.vehicle_assigned,
//     //     da.gate_pass_generated,
//     //     da.shipment_completed,
//     //     da.priority,
//     //     da.remarks,
//     //     da.allocation_status,

//     //     stock.available_qty,
//     //     stock.reserved_qty,

//     //     pim.item_name,
//     //     pim.uom,

//     //     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 ($2::int IS NULL OR da.product_id = $2)

//     // ORDER BY

//     // CASE
//     //     WHEN da.allocation_status = 'full' THEN 1
//     //     WHEN da.allocation_status = 'partial' THEN 2
//     //     ELSE 3
//     // END,

//     // da.delivery_date ASC,
//     // da.priority ASC,
//     // da.created_at ASC
//     // `,
//     //     [orgId, productId || null],
//     //   );

//     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 AS item_id,

//       da.required_qty,
//       da.allocated_qty,
//       da.balance_qty,
//       da.delivered_qty,
//       da.next_dispatch_date,


//       da.delivery_date,

//       da.vehicle_assigned,
//       da.gate_pass_generated,
//       da.shipment_completed,

//       da.priority,
//       da.remarks,

//       da.allocation_status,

//       sd.id AS shipment_id,
//       sd.shipment_number,
//       sd.shipment_status,
//       sd.vehicle_number,
// sd.vehicle_type,
//       sd.created_at AS shipment_created_at,

//       stock.available_qty,
//       stock.reserved_qty,

//       pim.item_name,
//       pim.uom,

//       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 LATERAL (
//     SELECT
//         sd.id,
//         sd.shipment_number,
//         sd.shipment_status,
//         sd.vehicle_number,
//         sd.vehicle_type,
//         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

//   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 ($2::int IS NULL OR da.product_id = $2)

//   ORDER BY

//   CASE
//       WHEN da.allocation_status = 'full' THEN 1
//       WHEN da.allocation_status = 'partial' THEN 2
//       ELSE 3
//   END,

//   da.delivery_date ASC,
//   da.priority ASC,
//   da.created_at ASC
//   `,
//       [orgId, productId || null],
//     );
//     res.json({
//       success: true,
//       data: result.rows,
//     });
//   } catch (err) {
//     console.error(err);

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

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

//     // const { productId } = req.params;
//     const productId = req.params.productId || null;

//     // 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.vehicle_assigned,

//     //       da.gate_pass_generated,

//     //       da.shipment_completed,

//     //       da.priority,

//     //       da.remarks,

//     //       da.allocation_status,

//     //       stock.available_qty,

//     //       stock.reserved_qty,

//     //       pim.item_name,

//     //       pim.uom,

//     //       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.product_id = $2

//     //   ORDER BY

//     //   CASE
//     //       WHEN da.allocation_status = 'full' THEN 1
//     //       WHEN da.allocation_status = 'partial' THEN 2
//     //       ELSE 3
//     //   END,

//     //   da.delivery_date ASC,

//     //   da.priority ASC,

//     //   da.created_at ASC

//     //   LIMIT 5
//     //   `,
//     //   [orgId, productId],
//     // );

//     //   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 AS item_id,
//     //     da.required_qty,
//     //     da.allocated_qty,
//     //     da.balance_qty,
//     //     da.delivery_date,
//     //     da.vehicle_assigned,
//     //     da.gate_pass_generated,
//     //     da.shipment_completed,
//     //     da.priority,
//     //     da.remarks,
//     //     da.allocation_status,

//     //     stock.available_qty,
//     //     stock.reserved_qty,

//     //     pim.item_name,
//     //     pim.uom,

//     //     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 ($2::int IS NULL OR da.product_id = $2)

//     // ORDER BY

//     // CASE
//     //     WHEN da.allocation_status = 'full' THEN 1
//     //     WHEN da.allocation_status = 'partial' THEN 2
//     //     ELSE 3
//     // END,

//     // da.delivery_date ASC,
//     // da.priority ASC,
//     // da.created_at ASC
//     // `,
//     //     [orgId, productId || null],
//     //   );

//  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 AS item_id,

//     da.required_qty,
//     da.allocated_qty,
//     da.balance_qty,
//     da.delivered_qty,

//     da.next_dispatch_date,

//     da.delivery_date,

//     da.vehicle_assigned,
//     da.gate_pass_generated,
//     da.shipment_completed,

//     da.priority,
//     da.remarks,

//     da.allocation_status,

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

//     sd.id AS shipment_id,
//     sd.shipment_number,
//     sd.shipment_status,
//     sd.vehicle_number,
//     sd.vehicle_type,

//     sd.created_at AS shipment_created_at,

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

//     stock.available_qty,
//     stock.reserved_qty,

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

//     pim.item_name,
//     pim.uom,

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

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

//     /* ===================================================== */
//     /* UI FLAGS */
//     /* ===================================================== */

//   CASE
//     WHEN
//         da.balance_qty > 0
//         AND (
//             sd.shipment_status IS NULL
//             OR sd.shipment_status = 'Cancelled'
//             OR da.next_dispatch_date IS NOT NULL
//         )
//     THEN true
//     ELSE false
// END AS show_in_allocation_queue,

//     CASE
//         WHEN sd.shipment_status IN (
//             'Pending',
//             'Assigned',
//             'Gate In',
//             'Loaded',
//             'Dispatched'
//         )
//         THEN true
//         ELSE false
//     END AS show_in_assigned_vehicle,

//     CASE
//         WHEN
//             da.next_dispatch_date IS NULL
//             OR CURRENT_DATE >= da.next_dispatch_date
//         THEN true
//         ELSE false
//     END AS can_assign_next_vehicle

// FROM dispatch_allocations da

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

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

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

// LEFT JOIN LATERAL (

//     SELECT

//         sd.id,
//         sd.shipment_number,
//         sd.shipment_status,
//         sd.vehicle_number,
//         sd.vehicle_type,

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

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

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

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

// WHERE da.org_id = $1

// AND ($2::int IS NULL OR da.product_id = $2)

// AND COALESCE(da.is_deleted, false) = false

// /* ===================================================== */
// /* SORTING */
// /* ===================================================== */

// ORDER BY

// CASE
//     WHEN da.balance_qty > 0 THEN 1
//     ELSE 2
// END,

// da.delivery_date ASC,

// da.priority ASC,

// da.created_at ASC
// `,
//   [orgId, productId || null]
// );
//     res.json({
//       success: true,
//       data: result.rows,
//     });
//   } catch (err) {
//     console.error(err);

//     res.status(500).json({
//       success: false,
//       message: "Failed to fetch dispatch allocations",
//     });
//   }
// };
const getProductDispatchAllocations = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

    const productId = req.params.productId || null;

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

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

    // =====================================================
    // FETCH DISPATCH ALLOCATIONS
    // =====================================================

    const result = await pool.query(
      `
      SELECT

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

          da.id,

          da.sales_order_id,
          da.sales_order_number,

          da.customer_id,
          da.customer_name,

          da.product_id AS item_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,

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

          sd.id AS shipment_id,

          sd.shipment_number,

          sd.shipment_status,

          sd.vehicle_number,

          sd.vehicle_type,

          sd.dispatch_datetime,

          sd.created_at AS shipment_created_at,

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

          COALESCE(stock.available_qty, 0) AS available_qty,

          COALESCE(stock.reserved_qty, 0) AS reserved_qty,

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

          pim.item_name,

          pim.uom,

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

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

          /* ===================================================== */
          /* UI FLAGS */
          /* ===================================================== */

          CASE
              WHEN
                  COALESCE(da.balance_qty, 0) > 0
                  AND
                  (
                      sd.shipment_status IS NULL

                      OR sd.shipment_status IN (
                          'Cancelled',
                          'Completed'
                      )

                      OR da.next_dispatch_date IS NOT NULL
                  )
              THEN true
              ELSE false
          END AS show_in_allocation_queue,

          CASE
              WHEN sd.shipment_status IN (
                  'Pending',
                  'Assigned',
                  'Gate In',
                  'Loaded',
                  'Dispatched'
              )
              THEN true
              ELSE false
          END AS show_in_assigned_vehicle,

          CASE
              WHEN
                  da.next_dispatch_date IS NULL
                  OR CURRENT_DATE >= da.next_dispatch_date
              THEN true
              ELSE false
          END AS can_assign_next_vehicle,

          /* ===================================================== */
          /* BUTTON FLAGS */
          /* ===================================================== */

          CASE
              WHEN COALESCE(da.allocated_qty, 0) = 0
              THEN true
              ELSE false
          END AS show_allocate_button,

          CASE
              WHEN
                  COALESCE(da.allocated_qty, 0) > 0
                  AND COALESCE(da.balance_qty, 0) > 0
                  AND
                  (
                      sd.id IS NULL
                      OR sd.shipment_status IN (
                          'Cancelled',
                          'Completed'
                      )
                  )
              THEN true
              ELSE false
          END AS show_assign_vehicle_button,

          CASE
              WHEN sd.shipment_status = 'Pending'
              THEN true
              ELSE false
          END AS show_vehicle_assigned_badge,

          CASE
              WHEN
                  sd.shipment_status = 'Cancelled'
                  AND COALESCE(da.balance_qty, 0) > 0
              THEN true
              ELSE false
          END AS show_reassign_vehicle_button

      FROM dispatch_allocations da

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

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

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

      LEFT JOIN LATERAL (

          SELECT

              sd.id,

              sd.shipment_number,

              sd.shipment_status,

              sd.vehicle_number,

              sd.vehicle_type,

              sd.dispatch_datetime,

              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

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

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

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

      WHERE da.org_id = $1

      AND ($2::int IS NULL OR da.product_id = $2)

      AND COALESCE(da.is_deleted, false) = false

      /* ===================================================== */
      /* SORTING */
      /* ===================================================== */

      ORDER BY

      CASE
          WHEN COALESCE(da.balance_qty, 0) > 0
          THEN 1
          ELSE 2
      END,

      CASE
          WHEN da.allocation_status = 'partial'
          THEN 1

          WHEN da.allocation_status = 'waiting'
          THEN 2

          WHEN da.allocation_status = 'full'
          THEN 3

          ELSE 4
      END,

      da.delivery_date ASC,

      da.priority ASC,

      da.created_at ASC
      `,
      [orgId, productId || null],
    );

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

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

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

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

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

//     // GET STOCKS
//     const stockResult = await client.query(
//       `
//       SELECT
//           product_id,
//           available_qty
//       FROM inventory_stock
//       WHERE org_id = $1
//       `,
//       [orgId],
//     );

//     const stockMap = {};

//     stockResult.rows.forEach((s) => {
//       stockMap[s.product_id] = Number(s.available_qty || 0);
//     });

//     // FETCH PENDING ALLOCATIONS FIFO
//     const allocationResult = await client.query(
//       `
//       SELECT
//           id,
//           product_id,
//           required_qty,
//           allocated_qty,
//           balance_qty
//       FROM dispatch_allocations
//       WHERE org_id = $1
//       ORDER BY
//           delivery_date ASC,
//           created_at ASC
//       `,
//       [orgId],
//     );

//     for (const row of allocationResult.rows) {
//       let available = stockMap[row.product_id] || 0;

//       const required = Number(row.required_qty);

//       let allocated = 0;
//       let balance = required;
//       let status = "waiting";

//       // FULL
//       if (available >= required) {
//         allocated = required;
//         balance = 0;
//         status = "full";

//         stockMap[row.product_id] = available - required;
//       }

//       // PARTIAL
//       else if (available > 0) {
//         allocated = available;
//         balance = required - available;
//         status = "partial";

//         stockMap[row.product_id] = 0;
//       }

//       // WAITING
//       else {
//         allocated = 0;
//         balance = required;
//         status = "waiting";
//       }

//       await client.query(
//         `
//         UPDATE dispatch_allocations
//         SET
//             allocated_qty = $1,
//             balance_qty = $2,
//             allocation_status = $3,
//             available_stock = $4,
//             updated_at = CURRENT_TIMESTAMP
//         WHERE id = $5
//         `,
//         [allocated, balance, status, stockMap[row.product_id] || 0, row.id],
//       );
//     }

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

//     res.json({
//       success: true,
//       message: "Auto allocation completed",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

//     console.error(err);

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

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

//   try {
//     const orgId = req.session?.user?.orgId;
//     const createdBy = req.session?.user?.id;

//     const {
//       allocation_id,

//       customer_name,
//       destination,

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

//       transport_mode,
//       vehicle_type,
//       vehicle_number,
//       transport_agency_name,
//       vehicle_capacity,

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

//       freight_type,
//       freight_amount,

//       lr_number,
//       seal_number,

//       dispatch_datetime,
//       expected_delivery_datetime,
//     } = req.body;

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

//     // Generate Shipment Number
//     const shipmentNumber = `SHP-${Date.now()}`;

//     // CREATE SHIPMENT HEADER
//     const shipmentResult = await client.query(
//       `
//       INSERT INTO shipment_dispatches (

//         org_id,

//         allocation_id,

//         shipment_number,

//         customer_name,
//         destination,

//         product_id,
//         product_name,

//         allocated_qty,
//         dispatch_qty,

//         uom,

//         available_stock,

//         transport_mode,
//         vehicle_type,
//         vehicle_number,

//         transport_agency_name,

//         vehicle_capacity,

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

//         freight_type,
//         freight_amount,

//         lr_number,
//         seal_number,

//         dispatch_datetime,
//         expected_delivery_datetime,

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

//       )

//       RETURNING *
//       `,
//       [
//         orgId,

//         allocation_id,

//         shipmentNumber,

//         customer_name,
//         destination,

//         product_id,
//         product_name,

//         allocated_qty,
//         dispatch_qty,

//         uom,

//         available_stock,

//         transport_mode,
//         vehicle_type,
//         vehicle_number,

//         transport_agency_name,

//         vehicle_capacity,

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

//         freight_type,
//         freight_amount,

//         lr_number,
//         seal_number,

//         dispatch_datetime,
//         expected_delivery_datetime,

//         createdBy,
//       ],
//     );

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

//     // CREATE SHIPMENT ITEM
//     await client.query(
//       `
//       INSERT INTO shipment_dispatch_items (

//         shipment_id,

//         allocation_id,

//         product_id,

//         product_name,

//         allocated_qty,

//         dispatch_qty

//       )

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

//         allocation_id,

//         product_id,

//         product_name,

//         allocated_qty,

//         dispatch_qty,
//       ],
//     );

//     // UPDATE ALLOCATION STATUS
//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET vehicle_assigned = true
//       WHERE id = $1
//       `,
//       [allocation_id],
//     );

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

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

//     console.error(err);

//     res.status(500).json({
//       success: false,
//       message: "Failed to create shipment",
//     });
//   } finally {
//     client.release();
//   }
// };
// const runAutoAllocation = 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;

//     // =====================================================
//     // GET STOCKS
//     // =====================================================

//     const stockResult = await client.query(
//       `
//       SELECT
//           product_id,
//           available_qty,
//           reserved_qty
//       FROM inventory_stock
//       WHERE org_id = $1
//       `,
//       [orgId],
//     );

//     const stockMap = {};

//     stockResult.rows.forEach((s) => {
//       stockMap[s.product_id] = {
//         available_qty: Number(s.available_qty || 0),
//         reserved_qty: Number(s.reserved_qty || 0),
//       };
//     });

//     // =====================================================
//     // FETCH ALLOCATIONS FIFO
//     // =====================================================

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

//           id,

//           sales_order_id,

//           product_id,

//           required_qty,

//           allocated_qty,

//           balance_qty,

//           allocation_status,

//           delivery_date

//       FROM dispatch_allocations

//       WHERE org_id = $1

//       ORDER BY

//           delivery_date ASC,

//           priority ASC,

//           created_at ASC
//       `,
//       [orgId],
//     );

//     // =====================================================
//     // LOOP
//     // =====================================================

//     for (const row of allocationResult.rows) {
//       const stockData = stockMap[row.product_id] || {
//         available_qty: 0,
//         reserved_qty: 0,
//       };

//       let available = Number(stockData.available_qty || 0);

//       const required = Number(row.required_qty || 0);

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

//       const existingBalance = Number(row.balance_qty || required);

//       // =====================================================
//       // SKIP FULLY ALLOCATED
//       // =====================================================

//       if (existingAllocated >= required) {
//         continue;
//       }

//       // =====================================================
//       // REMAINING REQUIRED
//       // =====================================================

//       const neededQty = required - existingAllocated;

//       let newlyAllocatedQty = 0;

//       let finalAllocated = existingAllocated;

//       let finalBalance = existingBalance;

//       let status = row.allocation_status || "waiting";

//       // =====================================================
//       // FULL ALLOCATION
//       // =====================================================

//       if (available >= neededQty) {
//         newlyAllocatedQty = neededQty;

//         finalAllocated = existingAllocated + neededQty;

//         finalBalance = 0;

//         status = "full";

//         stockMap[row.product_id].available_qty = available - neededQty;
//       }

//       // =====================================================
//       // PARTIAL ALLOCATION
//       // =====================================================
//       else if (available > 0) {
//         newlyAllocatedQty = available;

//         finalAllocated = existingAllocated + available;

//         finalBalance = required - finalAllocated;

//         status = "partial";

//         stockMap[row.product_id].available_qty = 0;
//       }

//       // =====================================================
//       // WAITING
//       // =====================================================
//       else {
//         newlyAllocatedQty = 0;

//         finalAllocated = existingAllocated;

//         finalBalance = required - existingAllocated;

//         status = existingAllocated > 0 ? "partial" : "waiting";
//       }

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

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

//       allocated_qty = $1,

//       balance_qty = $2,

//       allocation_status = $3,

//       available_stock = $4,

//       updated_by = $5,

//       updated_at = CURRENT_TIMESTAMP

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

//     finalBalance,

//     status,

//     stockMap[row.product_id]?.available_qty || 0,

//     userId,

//     row.id,
//   ],
// );
//       // =====================================================
//       // INSERT HISTORY
//       // =====================================================

//       if (newlyAllocatedQty > 0) {
//         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
//           )
//           `,
//           [
//             row.id,

//             row.sales_order_id,

//             row.product_id,

//             newlyAllocatedQty,

//             finalBalance,

//             "auto",

//             status,

//             "FIFO auto allocation",

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

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

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

//     res.json({
//       success: true,
//       message: "Auto allocation completed successfully",
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

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

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

const runAutoAllocation = 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;

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

    if (!orgId) {
      await client.query("ROLLBACK");

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

    // =====================================================
    // GET STOCKS
    // =====================================================

    const stockResult = await client.query(
      `
      SELECT
          product_id,
          available_qty,
          reserved_qty
      FROM inventory_stock
      WHERE org_id = $1
      `,
      [orgId],
    );

    // =====================================================
    // STOCK MAP
    // =====================================================

    const stockMap = {};

    stockResult.rows.forEach((s) => {
      stockMap[s.product_id] = {
        available_qty: Number(s.available_qty || 0),
        reserved_qty: Number(s.reserved_qty || 0),
      };
    });

    // =====================================================
    // FETCH FIFO ALLOCATIONS
    // =====================================================

    const allocationResult = await client.query(
      `
      SELECT

          id,

          sales_order_id,

          product_id,

          required_qty,

          allocated_qty,

          balance_qty,

          allocation_status,

          delivery_date,

          priority

      FROM dispatch_allocations

      WHERE org_id = $1

      ORDER BY

          delivery_date ASC,

          priority ASC,

          created_at ASC
      `,
      [orgId],
    );

    // =====================================================
    // LOOP ALLOCATIONS
    // =====================================================

    for (const row of allocationResult.rows) {
      const stockData = stockMap[row.product_id] || {
        available_qty: 0,
        reserved_qty: 0,
      };

      let available = Number(stockData.available_qty || 0);

      // SAFETY
      if (available < 0) {
        available = 0;
      }

      const required = Number(row.required_qty || 0);

      const existingAllocated = Number(row.allocated_qty || 0);

      const existingBalance = Number(row.balance_qty || required);

      // =====================================================
      // SKIP INVALID / FULL
      // =====================================================

      if (required <= 0) {
        continue;
      }

      if (existingAllocated >= required) {
        continue;
      }

      // =====================================================
      // REMAINING REQUIRED
      // =====================================================

      const neededQty = required - existingAllocated;

      let newlyAllocatedQty = 0;

      let finalAllocated = existingAllocated;

      let finalBalance = existingBalance;

      let status = row.allocation_status || "waiting";

      // =====================================================
      // FULL ALLOCATION
      // =====================================================

      if (available >= neededQty) {
        newlyAllocatedQty = neededQty;

        finalAllocated = existingAllocated + neededQty;

        finalBalance = 0;

        status = "full";

        stockMap[row.product_id].available_qty = available - neededQty;
      }

      // =====================================================
      // PARTIAL ALLOCATION
      // =====================================================
      else if (available > 0) {
        newlyAllocatedQty = available;

        finalAllocated = existingAllocated + available;

        finalBalance = required - finalAllocated;

        status = "partial";

        stockMap[row.product_id].available_qty = 0;
      }

      // =====================================================
      // WAITING
      // =====================================================
      else {
        newlyAllocatedQty = 0;

        finalAllocated = existingAllocated;

        finalBalance = required - existingAllocated;

        status = existingAllocated > 0 ? "partial" : "waiting";
      }

      // =====================================================
      // SAFETY CHECKS
      // =====================================================

      if (finalAllocated > required) {
        finalAllocated = required;
      }

      if (finalBalance < 0) {
        finalBalance = 0;
      }

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

      await client.query(
        `
        UPDATE dispatch_allocations
        SET

            allocated_qty = $1,

            balance_qty = $2,

            allocation_status = $3,

            available_stock = $4,

            updated_by = $5,

            updated_at = CURRENT_TIMESTAMP

        WHERE id = $6
        `,
        [
          finalAllocated,

          finalBalance,

          status,

          Math.max(stockMap[row.product_id]?.available_qty || 0, 0),

          userId,

          row.id,
        ],
      );

      // =====================================================
      // UPDATE INVENTORY STOCK
      // =====================================================

      if (newlyAllocatedQty > 0) {
        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
          `,
          [newlyAllocatedQty, row.product_id, orgId],
        );
      }

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

      if (newlyAllocatedQty > 0) {
        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
          )
          `,
          [
            row.id,

            row.sales_order_id,

            row.product_id,

            newlyAllocatedQty,

            finalBalance,

            "auto",

            status,

            "FIFO auto allocation",

            userId,
          ],
        );
      }
    }

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

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Auto allocation completed successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");

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

    return res.status(500).json({
      success: false,
      message: "Allocation failed",
      error: err.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 manualAllocateStock = async (req, res) => {

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

//   try {

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

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

//     const {
//       allocation_id,
//       allocate_qty,
//     } = req.body;

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

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

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

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

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

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

//     const availableStock =
//       Number(stock.available_qty || 0);

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

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

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

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

//     if (qty > availableStock) {
//       return res.status(400).json({
//         success: false,
//         message: "Insufficient stock",
//       });
//     }

//     // TOTAL ALLOCATED
//     const newAllocated =
//       existingAllocated + qty;

//     // PREVENT OVER ALLOCATION
//     if (newAllocated > requiredQty) {
//       return res.status(400).json({
//         success: false,
//         message: "Allocation exceeds required qty",
//       });
//     }

//     const balanceQty =
//       requiredQty - newAllocated;

//     let status = "waiting";

//     if (balanceQty === 0) {
//       status = "full";
//     }
//     else if (newAllocated > 0) {
//       status = "partial";
//     }

//     // UPDATE ALLOCATION
//     await client.query(
//       `
//       UPDATE dispatch_allocations
//       SET
//           allocated_qty = $1,
//           balance_qty = $2,
//           allocation_status = $3,
//           updated_at = CURRENT_TIMESTAMP
//       WHERE id = $4
//       `,
//       [
//         newAllocated,
//         balanceQty,
//         status,
//         allocation_id,
//       ]
//     );

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

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

//     res.json({
//       success: true,
//       message: "Stock allocated successfully",
//     });

//   } catch (err) {

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

//     console.error(err);

//     res.status(500).json({
//       success: false,
//       message: "Manual allocation failed",
//     });

//   } finally {
//     client.release();
//   }
// };
// const manualAllocateStock = async (req, res) => {
//   const orgId = req.session?.user?.orgId;

//   const userId = req.session?.user?.id;
//   console.log(userId, "manual..");

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

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

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

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

//     const allocationResult = await client.query(
//       `
//       SELECT *
//       FROM dispatch_allocations
//       WHERE id = $1
//       AND org_id = $2
//       `,
//       [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 STOCK
//     // =====================================================

//     const stockResult = await client.query(
//       `
//   SELECT *
//   FROM inventory_stock
//   WHERE product_id = $1
//   AND org_id = $2
//   FOR UPDATE
//   `,
//       [allocation.product_id, orgId],
//     );

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

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

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

//     // =====================================================
//     // VALUES
//     // =====================================================

//     const availableStock = Number(stock.available_qty || 0);

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

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

//     const existingBalance = Number(allocation.balance_qty || 0);

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

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

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

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

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

//       return res.status(400).json({
//         success: false,
//         message: "Insufficient stock",
//       });
//     }

//     // =====================================================
//     // NEW CALCULATIONS
//     // =====================================================

//     const newAllocated = existingAllocated + qty;

//     if (newAllocated > requiredQty) {
//       await client.query("ROLLBACK");

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

//     const balanceQty = requiredQty - newAllocated;

//     let status = "waiting";

//     if (balanceQty === 0) {
//       status = "full";
//     } else if (newAllocated > 0) {
//       status = "partial";
//     }

//     let deliveryStatus = "Pending";

//     if (newAllocated > 0 && balanceQty > 0) {
//       deliveryStatus = "Partial";
//     }

//     if (balanceQty === 0) {
//       deliveryStatus = "Ready";
//     }

//     // =====================================================
//     // UPDATE MASTER ALLOCATION TABLE
//     // =====================================================

//     await client.query(
//       `
//   UPDATE dispatch_allocations
//   SET
//       allocated_qty = $1,

//       balance_qty = $2,

//       allocation_status = $3,

//       delivery_status = $4,

//       created_by = COALESCE(created_by, $5),

//       updated_by = $5,

//       updated_at = CURRENT_TIMESTAMP

//   WHERE id = $6
//   `,
//       [
//         newAllocated,
//         balanceQty,
//         status,
//         deliveryStatus, // ✅ MISSING PARAM
//         userId,
//         allocation_id,
//       ],
//     );
//     // =====================================================
//     // UPDATE INVENTORY STOCK
//     // =====================================================

//     // 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, allocation.product_id, orgId],
//     // );
//     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, 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,

//           created_at
//       )
//       VALUES
//       (
//           $1,
//           $2,
//           $3,
//           $4,
//           $5,
//           $6,
//           $7,
//           $8,
//           $9,
//           CURRENT_TIMESTAMP
//       )
//       `,
//       [
//         allocation_id,

//         allocation.sales_order_id,

//         allocation.product_id,

//         qty,

//         balanceQty,

//         "manual",

//         status,

//         remarks || null,

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

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

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

//     res.json({
//       success: true,
//       message: "Stock allocated successfully",
//       data: {
//         allocation_id,
//         allocated_qty: newAllocated,
//         balance_qty: balanceQty,
//         allocation_status: status,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");

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

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

const manualAllocateStock = async (req, res) => {
  const orgId = req.session?.user?.orgId;
  console.log("rtyuityuio");

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

  const client = await pool.connect();

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

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

    // =====================================================
    // VALIDATE INPUT
    // =====================================================

    const qty = Number(allocate_qty || 0);

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

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

    // =====================================================
    // CHECK EXISTING ALLOCATION
    // =====================================================

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

    // =====================================================
    // CREATE ALLOCATION IF NOT EXISTS
    // =====================================================

    if (allocationResult.rows.length === 0) {
      // GET SALES ORDER

      const soResult = await client.query(
        `
  SELECT

      so.id,
      so.so_number,
      so.customer_id,
      so.customer_name,
      so.delivery_date,

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

      // CREATE DISPATCH 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,

      vehicle_assigned,
      gate_pass_generated,
      shipment_completed,

      created_by,
      updated_by,

      created_at,
      updated_at
  )

  VALUES
  (
      $1,

      $2,

      $3,
      $4,

      $5,
      $6,

      $7,
      0,
      $7,

      0,

      'waiting',

      $8,

      1,

      'Created during manual allocation',

      false,
      false,
      false,

      $9,
      $9,

      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
  )

  RETURNING *
  `,
        [
          orgId,

          so.product_id,

          so.id,
          so.so_number,

          so.customer_id,
          so.customer_name,

          so.qty,

          so.delivery_date || null,

          userId,
        ],
      );

      allocationResult = {
        rows: [insertResult.rows[0]],
      };
    }

    // =====================================================
    // ALLOCATION DATA
    // =====================================================

    const allocation = allocationResult.rows[0];

    // =====================================================
    // GET INVENTORY STOCK
    // =====================================================

    const stockResult = await client.query(
      `
      SELECT *
      FROM inventory_stock
      WHERE product_id = $1
      AND org_id = $2
      FOR UPDATE
      `,
      [allocation.product_id, orgId],
    );

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

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

    const stock = stockResult.rows[0];

    // =====================================================
    // STOCK VALUES
    // =====================================================

    const availableStock = Number(stock.available_qty || 0);

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

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

    // =====================================================
    // VALIDATE STOCK
    // =====================================================

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

      return res.status(400).json({
        success: false,
        message: "Insufficient stock",
      });
    }

    // =====================================================
    // NEW CALCULATIONS
    // =====================================================

    const newAllocated = existingAllocated + qty;

    if (newAllocated > requiredQty) {
      await client.query("ROLLBACK");

      return res.status(400).json({
        success: false,
        message: "Allocation exceeds required qty",
      });
    }

    const balanceQty = requiredQty - newAllocated;

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

          available_stock = $5,

          updated_by = $6,

          updated_at = CURRENT_TIMESTAMP

      WHERE id = $7
      `,
      [
        newAllocated,

        balanceQty,

        allocationStatus,

        deliveryStatus,

        availableStock - qty,

        userId,

        allocation.id,
      ],
    );

    // =====================================================
    // UPDATE INVENTORY
    // =====================================================

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

        balanceQty,

        "manual",

        allocationStatus,

        remarks || null,

        userId,
      ],
    );

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

    await client.query("COMMIT");

    return res.json({
      success: true,

      message: "Stock allocated successfully",

      data: {
        allocation_id: allocation.id,

        sales_order_id: allocation.sales_order_id,

        allocated_qty: newAllocated,

        balance_qty: balanceQty,

        allocation_status: allocationStatus,

        delivery_status: deliveryStatus,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");

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

    return res.status(500).json({
      success: false,
      message: "Manual 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;

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

//     const allocationResult = await client.query(
//       `
//       SELECT *
//       FROM dispatch_allocations
//       WHERE id = $1
//       AND org_id = $2
//       `,
//       [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",
//       });
//     } // =====================================================
//     // ROLLBACK SHIPMENT
//     // =====================================================

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

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

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

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

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

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

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

//     let status = "waiting";

//     if (finalAllocated === 0) {
//       status = "waiting";
//     } 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_at = CURRENT_TIMESTAMP

//   WHERE id = $5
//   `,
//       [finalAllocated, finalBalance, status, remarks || null, 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],
//     );

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

//         is_deleted = true,

//         vehicle_assigned = false,

//         shipment_status = 'Cancelled',

//         updated_at = CURRENT_TIMESTAMP

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

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

//           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: "Allocation cancelled 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 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 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",
    });
  }
};


module.exports = {
  getShipmentBySalesOrderId,
  getDispatchDashboardProducts,
  getProductDispatchAllocations,
  getReadyForDispatch,
  runAutoAllocation,
  createShipment,
  manualAllocateStock,
  cancelAllocation, 
  getAllocationDetails,
  getDispatchedShipments,
  getShipmentById,
  approveShipment,
};
