// const CommercialVehicle = require("../../models/securityops/vehicleModel");
// const Inward = require("../../models/securityops/inwardModel");
// const { errorResponse } = require("../../utils/errorResponseHandler");
// const { logUserAction } = require("../../utils/userActionLogger");
// const { pool } = require("../../config/db");


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

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

//     let {
//       poNumber,
//       vendorName,
//       referenceNo,
//       dcNumber,
//       invoiceNumber,
//       warehouse,
//       isManual,
//       items,
//     } = req.body;

//     const isManualEntry = isManual === true || isManual === "true";

//     // Parse items
//     let parsedItems = [];
//     if (items) {
//       try {
//         parsedItems = typeof items === 'string' ? JSON.parse(items) : items;
//       } catch (e) {
//         parsedItems = [];
//       }
//     }

//     // Generate reference for manual entries
//     let finalReferenceNo = referenceNo?.trim().toUpperCase();
    
//     if (!isManualEntry && poNumber) {
//       finalReferenceNo = poNumber.trim().toUpperCase();
//     } else if (isManualEntry && !finalReferenceNo) {
//       const date = new Date();
//       const dateStr = date.toISOString().slice(0, 10).replace(/-/g, '');
//       const countResult = await client.query(
//         `SELECT COUNT(*) FROM inward WHERE reference_no LIKE $1`,
//         [`INV-${dateStr}-%`]
//       );
//       const nextNum = (parseInt(countResult.rows[0].count) + 1).toString().padStart(4, '0');
//       finalReferenceNo = `INV-${dateStr}-${nextNum}`;
//     }

//     // Validation
//     if (!finalReferenceNo || !vendorName) {
//       return res.status(400).json({
//         success: false,
//         message: "Reference number and vendor name required",
//       });
//     }

//     if (!isManualEntry && !poNumber) {
//       return res.status(400).json({
//         success: false,
//         message: "PO Number required for PO mode",
//       });
//     }

//     if (isManualEntry && (!parsedItems || parsedItems.length === 0)) {
//       return res.status(400).json({
//         success: false,
//         message: "At least one item required for manual entry",
//       });
//     }

//     // Process multiple files
//     let deliveryFiles = [];
//     if (req.files && req.files.length > 0) {
//       if (req.files.length > 10) {
//         return res.status(400).json({
//           success: false,
//           message: "Maximum 10 files allowed",
//         });
//       }
      
//       deliveryFiles = req.files.map((f) => ({
//         originalName: f.originalname,
//         fileName: f.filename,
//         fileSize: f.size,
//         mimeType: f.mimetype,
//         fileUrl: f.path.replace(/^.*uploads/, "uploads").replace(/\\/g, "/"),
//         uploadedAt: new Date(),
//       }));
//     }

//     // Build items JSON
//     let itemsJson = [];
//     if (isManualEntry) {
//       itemsJson = parsedItems.map(item => ({
//         id: item.id,
//         name: item.name,
//         quantity: item.quantity,
//         uom: item.uom,
//         selectedUom: item.selectedUom || item.uom,
//         unit_price: item.unit_price || 0
//       }));
//     } else {
//       itemsJson = parsedItems.map(item => ({
//         id: item.id,
//         name: item.item_name || item.name,
//         quantity: item.quantity,
//         uom: item.uom,
//         unit_price: item.unit_price || 0
//       }));
//     }

//     // Calculate total quantity
//     const totalQuantity = itemsJson.reduce((sum, item) => sum + (parseFloat(item.quantity) || 0), 0);

//     // Get warehouse name
//     let warehouseName = warehouse;
//     if (warehouse && typeof warehouse === 'object') {
//       warehouseName = warehouse.name || warehouse.label;
//     }

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

//     // Insert into inward table
//     const inwardResult = await client.query(
//       `INSERT INTO inward (
//         reference_no, po_number, vendor_name, dc_number, 
//         invoice_number, warehouse, items, is_manual, 
//         delivery_files, org_id, created_by, updated_by, 
//         status, approval_status, total_quantity
//       ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
//       RETURNING *`,
//       [
//         finalReferenceNo,
//         poNumber || null,
//         vendorName,
//         dcNumber || null,
//         invoiceNumber || null,
//         warehouseName || null,
//         JSON.stringify(itemsJson),
//         isManualEntry,
//         JSON.stringify(deliveryFiles),
//         orgId,
//         userId,
//         userId,
//         'Pending',
//         'Pending',
//         totalQuantity
//       ]
//     );

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

//     return res.status(201).json({
//       success: true,
//       message: `Inward created successfully. Awaiting approval.`,
//       data: inwardResult.rows[0],
//       files_uploaded: deliveryFiles.length,
//       mode: isManualEntry ? 'MANUAL' : 'WITH PO'
//     });

//   } catch (err) {
//     await client.query('ROLLBACK');
//     console.error("Create Inward Error:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Create inward failed",
//       error: err.message
//     });
//   } finally {
//     client.release();
//   }
// };






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

//   try {
//     const { id } = req.params;
//     const userId = req.session.user?.id;
//     const orgId = req.session.user?.orgId;

//     // Get inward record with items
//     const inwardResult = await client.query(
//       `SELECT * FROM inward WHERE id = $1 AND is_deleted = false AND approval_status = 'Pending'`,
//       [id]
//     );

//     if (inwardResult.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Inward not found or already processed",
//       });
//     }

//     const inward = inwardResult.rows[0];
//     const isManual = inward.is_manual;
//     const items = inward.items || [];
//     const vendorName = inward.vendor_name;
//     const poNumber = inward.po_number;
//     const dcNumber = inward.dc_number;
//     const invoiceNumber = inward.invoice_number;
//     const warehouse = inward.warehouse;
//     const referenceNo = inward.reference_no;

//     // ✅ FIXED: Use the stored warehouse_id directly — no name lookup needed
//     const warehouseId = inward.warehouse_id ? parseInt(inward.warehouse_id) : null;

//     // CHECK IF WAREHOUSE IS "INVENTORY STORE"
//     const isInventoryStore =
//       warehouse && warehouse.toLowerCase() === "inventory store";

//     console.log("Approving inward:", {
//       id,
//       vendorName,
//       poNumber,
//       warehouse,
//       warehouseId,           // ✅ now comes directly from inward record
//       isInventoryStore,
//       willCreateStock: !isInventoryStore,
//     });

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

//     // ========== CREATE GRN ==========
//     let grnId = null;
//     let poId = null;

//     // Get PO ID if PO number exists
//     if (poNumber && !isManual) {
//       const poRes = await client.query(
//         `SELECT id FROM purchase_orders WHERE po_number = $1`,
//         [poNumber]
//       );
//       if (poRes.rows.length > 0) {
//         poId = poRes.rows[0].id;
//       }
//     }

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

//     const grnResult = await client.query(
//       `INSERT INTO grn (
//         grn_number,
//         po_id,
//         po_number,
//         vendor_name,
//         gate_entry_no,
//         invoice_no,
//         delivery_challan_no,
//         grn_date,
//         created_by,
//         created_at,
//         org_id
//       )
//       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
//       RETURNING id`,
//       [
//         grnNumber,
//         poId,
//         poNumber,
//         vendorName,
//         referenceNo,
//         invoiceNumber,
//         dcNumber,
//         currentDate,
//         userId,
//         currentDate,
//         orgId,
//       ]
//     );

//     grnId = grnResult.rows[0].id;
//     console.log(`GRN created with ID: ${grnId}`);

//     // ========== CREATE GRN ITEMS ==========
//     if (items && items.length > 0) {
//       console.log(`Creating ${items.length} GRN items for inward ${id}`);

//       for (const item of items) {
//         const orderedQty = parseFloat(item.quantity) || 0;
//         const receivedQty = orderedQty;
//         const acceptedQty = orderedQty;
//         const rejectedQty = 0;
//         const unitPrice = parseFloat(item.unit_price) || 0;
//         const totalPrice = acceptedQty * unitPrice;
//         const itemName = item.name || item.item_name;

//         await client.query(
//           `INSERT INTO grn_items (
//             grn_id,
//             item_name,
//             ordered_qty,
//             received_qty,
//             accepted_qty,
//             rejected_qty,
//             unit_price,
//             total_price,
//             grand_total,
//             store_location,
//             created_at
//           ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())`,
//           [
//             grnId,
//             itemName,
//             orderedQty,
//             receivedQty,
//             acceptedQty,
//             rejectedQty,
//             unitPrice,
//             totalPrice,
//             totalPrice,
//             warehouse || "Main Store",
//           ]
//         );
//         console.log(`Created GRN item for: ${itemName}`);
//       }
//     }

//     // ========== CREATE STOCK ENTRIES ONLY IF NOT INVENTORY STORE ==========
//     let createdStocks = [];

//     if (isInventoryStore) {
//       console.log(
//         "⚠️ Warehouse is 'Inventory Store' - SKIPPING stock creation"
//       );
//     } else if (items && items.length > 0) {
//       console.log(
//         `Creating stock entries for ${items.length} item(s) (Warehouse: ${warehouse}, Warehouse ID: ${warehouseId})`
//       );

//       for (const item of items) {
//         const itemQuantity = parseFloat(item.quantity) || 0;
//         const materialName = item.name || item.item_name;
//         const quantityUnit = item.selectedUom || item.uom || "Tons";

//         if (itemQuantity <= 0) {
//           console.log(`Skipping ${materialName} - quantity is 0`);
//           continue;
//         }

//         try {
//           // ✅ warehouseId now correctly comes from inward.warehouse_id
//           const stockResult = await client.query(
//             `INSERT INTO stocks (
//               po_number,
//               supplier_name,
//               material_name,
//               quantity_unit,
//               net_quantity,
//               received_by,
//               created_by,
//               org_id,
//               received_date,
//               grn_id,
//               inward_id,
//               warehouse_id
//             ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
//             RETURNING *`,
//             [
//               poNumber || null,
//               vendorName,
//               materialName,
//               quantityUnit,
//               itemQuantity,
//               userId,
//               userId,
//               orgId,
//               new Date(),
//               grnId,
//               parseInt(id),
//               warehouseId, // ✅ will now be the correct ID, not null
//             ]
//           );
//           createdStocks.push(stockResult.rows[0]);
//           console.log(
//             `✅ Created stock for: ${materialName}, Qty: ${itemQuantity} ${quantityUnit}, Warehouse ID: ${warehouseId}`
//           );
//         } catch (stockErr) {
//           console.error("Error creating stock entry:", stockErr.message);
//         }
//       }
//     }

//     // Update inward approval status
//     await client.query(
//       `UPDATE inward 
//        SET approval_status = 'Approved',
//            status = 'Completed',
//            approved_by = $1,
//            approved_at = NOW(),
//            updated_by = $1,
//            updated_at = NOW()
//        WHERE id = $2`,
//       [userId, id]
//     );

//     // Log action
//     await logUserAction({
//       userId,
//       action: "APPROVE",
//       model: "Inward",
//       documentId: id,
//       details: {
//         referenceNo: inward.reference_no,
//         warehouse: warehouse,
//         warehouseId: warehouseId,
//         isInventoryStore: isInventoryStore,
//         stocksCreated: createdStocks.length,
//       },
//       req,
//     });

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

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

//     return res.json({
//       success: true,
//       message: isInventoryStore
//         ? `✅ Inward approved successfully. GRN created but NO stock entries (Warehouse: ${warehouse})`
//         : `✅ Inward approved successfully. GRN created with ${createdStocks.length} stock entries. Total quantity: ${totalQuantity}`,
//       data: {
//         inwardId: id,
//         referenceNo: inward.reference_no,
//         grnId: grnId,
//         warehouse: warehouse,
//         warehouseId: warehouseId,
//         isInventoryStore: isInventoryStore,
//         stocksCreated: createdStocks.length,
//         totalQuantity: totalQuantity,
//       },
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("Approve Inward Error:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to approve inward: " + err.message,
//       error: err.message,
//     });
//   } finally {
//     client.release();
//   }
// };

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

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

//     const {
//       page = 1,
//       limit = 10,
//       search = "",
//       approvalStatus = "all",
//       sortBy = "created_at",
//       order = "desc",
//     } = req.query;

//     const pageNumber = parseInt(page);
//     const pageSize = parseInt(limit);
//     const offset = (pageNumber - 1) * pageSize;

//     let sortField = "i.created_at";
//     if (sortBy === "vendor") sortField = "i.vendor_name";
//     if (sortBy === "product") sortField = "i.items";
//     if (sortBy === "quantity") sortField = "i.total_quantity";
//     if (sortBy === "createdBy") sortField = "u.first_name";
//     if (sortBy === "status") sortField = "i.approval_status";

//     const sortOrder = order === "asc" ? "ASC" : "DESC";

//     let where = `WHERE i.is_deleted = false`;
//     const values = [];
//     let idx = 1;

//     if (orgId) {
//       where += ` AND i.org_id = $${idx++}`;
//       values.push(orgId);
//     }

//     if (approvalStatus !== "all") {
//       where += ` AND i.approval_status = $${idx++}`;
//       values.push(approvalStatus === "pending" ? "Pending" : approvalStatus === "approved" ? "Approved" : "Rejected");
//     }

//     if (search) {
//       where += ` AND (
//         i.reference_no ILIKE $${idx} OR
//         i.vendor_name ILIKE $${idx} OR
//         i.po_number ILIKE $${idx} OR
//         i.dc_number ILIKE $${idx} OR
//         i.invoice_number ILIKE $${idx}
//       )`;
//       values.push(`%${search}%`);
//       idx++;
//     }

//     const countResult = await client.query(
//       `SELECT COUNT(*) FROM inward i ${where}`,
//       values,
//     );

//     const total = parseInt(countResult.rows[0].count);

//     const dataQuery = `
//       SELECT 
//         i.*,
//         u.first_name,
//         u.last_name,
//         approver.first_name as approver_first_name,
//         approver.last_name as approver_last_name,
//         rejecter.first_name as rejecter_first_name,
//         rejecter.last_name as rejecter_last_name
//       FROM inward i
//       LEFT JOIN users u ON u.id = i.created_by
//       LEFT JOIN users approver ON approver.id = i.approved_by
//       LEFT JOIN users rejecter ON rejecter.id = i.rejected_by
//       ${where}
//       ORDER BY ${sortField} ${sortOrder}
//       LIMIT $${idx++} OFFSET $${idx++}
//     `;

//     values.push(pageSize, offset);

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

//     const formatted = result.rows.map((item) => {
//       let displayProduct = "-";
//       let totalQuantity = item.total_quantity || "-";
      
//       if (item.items && Array.isArray(item.items) && item.items.length > 0) {
//         displayProduct = item.items[0].name || item.items[0].item_name || "-";
//       }

//       return {
//         id: item.id,
//         isManual: item.is_manual,
//         vendor: item.vendor_name || "-",
//         typeRef: item.is_manual ? `Manual / Ref: ${item.reference_no}` : `PO / Ref: ${item.po_number}`,
//         materialName: displayProduct,
//         quantity: totalQuantity,
//         createdBy: item.first_name ? `${item.first_name} ${item.last_name}` : "-",
//         createdAt: item.created_at,
//         status: item.status,
//         approvalStatus: item.approval_status,
//         referenceNo: item.reference_no,
//         poNumber: item.po_number,
//         dcNumber: item.dc_number,
//         invoiceNumber: item.invoice_number,
//         warehouse: item.warehouse,
//         approvedBy: item.approver_first_name ? `${item.approver_first_name} ${item.approver_last_name}` : null,
//         approvedAt: item.approved_at,
//         rejectedBy: item.rejecter_first_name ? `${item.rejecter_first_name} ${item.rejecter_last_name}` : null,
//         rejectedAt: item.rejected_at,
//         rejectionReason: item.rejection_reason,
//         approvalNotes: item.approval_notes,
//       };
//     });

//     return res.json({
//       success: true,
//       message: "Inward list fetched successfully",
//       data: formatted,
//       pagination: {
//         total,
//         page: pageNumber,
//         limit: pageSize,
//         totalPages: Math.ceil(total / pageSize),
//       },
//     });
//   } catch (err) {
//     console.error("Fetch Inward Error:", err);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch inward list",
//     });
//   } finally {
//     client.release();
//   }
// };

// const deleteInward = async (req, res) => {
//   const client = await pool.connect();
  
//   try {
//     const { id } = req.params;
//     const userId = req.session.user?.id;

//     const inwardResult = await client.query(
//       `SELECT * FROM inward WHERE id = $1 AND is_deleted = false`,
//       [id]
//     );
    
//     if (inwardResult.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Inward not found"
//       });
//     }
    
//     const inward = inwardResult.rows[0];
    
//     // Only allow deletion if approval_status is Pending
//     if (inward.approval_status !== "Pending") {
//       return res.status(400).json({
//         success: false,
//         message: "Only pending inward entries can be deleted"
//       });
//     }

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

//     await client.query(
//       `UPDATE inward 
//        SET is_deleted = true, deleted_at = NOW(), deleted_by = $1
//        WHERE id = $2`,
//       [userId, id]
//     );

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

//     res.json({ success: true, message: "Inward deleted successfully" });
//   } catch (err) {
//     await client.query('ROLLBACK');
//     console.error(err);
//     res.status(500).json({ success: false, message: "Delete failed" });
//   } finally {
//     client.release();
//   }
// };

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

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

//     const result = await client.query(
//       `SELECT i.*, 
//         u.first_name, u.last_name,
//         approver.first_name as approver_first_name, approver.last_name as approver_last_name,
//         rejecter.first_name as rejecter_first_name, rejecter.last_name as rejecter_last_name
//        FROM inward i
//        LEFT JOIN users u ON u.id = i.created_by
//        LEFT JOIN users approver ON approver.id = i.approved_by
//        LEFT JOIN users rejecter ON rejecter.id = i.rejected_by
//        WHERE i.id = $1 AND i.is_deleted = false`,
//       [id],
//     );

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

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

//     const formatted = {
//       id: item.id,
//       isManual: item.is_manual,
//       referenceNo: item.reference_no,
//       poNumber: item.po_number,
//       vendorName: item.vendor_name,
//       dcNumber: item.dc_number,
//       invoiceNumber: item.invoice_number,
//       warehouse: item.warehouse,
//       items: item.items,
//       deliveryFiles: item.delivery_files,
//       status: item.status,
//       approvalStatus: item.approval_status,
//       createdBy: item.first_name ? `${item.first_name} ${item.last_name}` : "-",
//       createdAt: item.created_at,
//       approvedBy: item.approver_first_name ? `${item.approver_first_name} ${item.approver_last_name}` : null,
//       approvedAt: item.approved_at,
//       approvalNotes: item.approval_notes,
//       rejectedBy: item.rejecter_first_name ? `${item.rejecter_first_name} ${item.rejecter_last_name}` : null,
//       rejectedAt: item.rejected_at,
//       rejectionReason: item.rejection_reason,
//     };

//     return res.json({
//       success: true,
//       message: "Inward fetched",
//       data: formatted,
//     });
//   } catch (err) {
//     console.error(err);
//     return res.status(500).json({
//       success: false,
//       message: "Fetch inward failed",
//     });
//   } finally {
//     client.release();
//   }
// };

// const updateInward = async (req, res) => {
//   const client = await pool.connect();
  
//   try {
//     const userId = req.session.user?.id;
//     const { id } = req.params;
//     let { vendorName, dcNumber, invoiceNumber, warehouse, items } = req.body;

//     const existingInward = await client.query(
//       `SELECT * FROM inward WHERE id = $1 AND is_deleted = false AND approval_status = 'Pending'`,
//       [id]
//     );
    
//     if (existingInward.rowCount === 0) {
//       return res.status(400).json({
//         success: false,
//         message: "Only pending inward entries can be edited"
//       });
//     }
    
//     const newItems = typeof items === 'string' ? JSON.parse(items) : items;

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

//     await client.query(
//       `UPDATE inward SET
//         vendor_name = $1,
//         dc_number = $2,
//         invoice_number = $3,
//         warehouse = $4,
//         items = $5,
//         total_quantity = $6,
//         updated_by = $7,
//         updated_at = NOW()
//       WHERE id = $8`,
//       [
//         vendorName, 
//         dcNumber, 
//         invoiceNumber, 
//         warehouse,
//         JSON.stringify(newItems),
//         newItems.reduce((sum, item) => sum + (parseFloat(item.quantity) || 0), 0),
//         userId, 
//         id
//       ]
//     );

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

//     res.json({ success: true, message: "Inward updated successfully" });
//   } catch (err) {
//     await client.query('ROLLBACK');
//     console.error(err);
//     res.status(500).json({ success: false, message: "Update failed" });
//   } finally {
//     client.release();
//   }
// };

// module.exports = {
//   createInward,
//   getInwardList,
//   deleteInward,
//   getInwardById,
//   updateInward,
//   approveInward,
// };


const CommercialVehicle = require("../../models/securityops/vehicleModel");
const Inward = require("../../models/securityops/inwardModel");
const { errorResponse } = require("../../utils/errorResponseHandler");
const { logUserAction } = require("../../utils/userActionLogger");
const { pool } = require("../../config/db");


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

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

    let {
      poNumber,
      vendorName,
      referenceNo,
      dcNumber,
      invoiceNumber,
      warehouse,
      warehouseId,   // ✅ NEW: received from frontend
      isManual,
      items,
    } = req.body;

    const isManualEntry = isManual === true || isManual === "true";

    // Parse items
    let parsedItems = [];
    if (items) {
      try {
        parsedItems = typeof items === "string" ? JSON.parse(items) : items;
      } catch (e) {
        parsedItems = [];
      }
    }

    // ✅ Parse warehouseId safely
    const parsedWarehouseId =
      warehouseId && !isNaN(parseInt(warehouseId))
        ? parseInt(warehouseId)
        : null;

    // Generate reference for manual entries
    let finalReferenceNo = referenceNo?.trim().toUpperCase();

    if (!isManualEntry && poNumber) {
      finalReferenceNo = poNumber.trim().toUpperCase();
    } else if (isManualEntry && !finalReferenceNo) {
      const date = new Date();
      const dateStr = date.toISOString().slice(0, 10).replace(/-/g, "");
      const countResult = await client.query(
        `SELECT COUNT(*) FROM inward WHERE reference_no LIKE $1`,
        [`INV-${dateStr}-%`]
      );
      const nextNum = (parseInt(countResult.rows[0].count) + 1)
        .toString()
        .padStart(4, "0");
      finalReferenceNo = `INV-${dateStr}-${nextNum}`;
    }

    // Validation
    if (!finalReferenceNo || !vendorName) {
      return res.status(400).json({
        success: false,
        message: "Reference number and vendor name required",
      });
    }

    if (!isManualEntry && !poNumber) {
      return res.status(400).json({
        success: false,
        message: "PO Number required for PO mode",
      });
    }

    if (isManualEntry && (!parsedItems || parsedItems.length === 0)) {
      return res.status(400).json({
        success: false,
        message: "At least one item required for manual entry",
      });
    }

    // Process multiple files
    let deliveryFiles = [];
    if (req.files && req.files.length > 0) {
      if (req.files.length > 10) {
        return res.status(400).json({
          success: false,
          message: "Maximum 10 files allowed",
        });
      }

      deliveryFiles = req.files.map((f) => ({
        originalName: f.originalname,
        fileName: f.filename,
        fileSize: f.size,
        mimeType: f.mimetype,
        fileUrl: f.path
          .replace(/^.*uploads/, "uploads")
          .replace(/\\/g, "/"),
        uploadedAt: new Date(),
      }));
    }

    // Build items JSON
    let itemsJson = [];
    if (isManualEntry) {
      itemsJson = parsedItems.map((item) => ({
        id: item.id,
        name: item.name,
        quantity: item.quantity,
        uom: item.uom,
        selectedUom: item.selectedUom || item.uom,
        unit_price: item.unit_price || 0,
      }));
    } else {
      itemsJson = parsedItems.map((item) => ({
        id: item.id,
        name: item.item_name || item.name,
        quantity: item.quantity,
        uom: item.uom,
        unit_price: item.unit_price || 0,
      }));
    }

    // Calculate total quantity
    const totalQuantity = itemsJson.reduce(
      (sum, item) => sum + (parseFloat(item.quantity) || 0),
      0
    );

    // Get warehouse name
    let warehouseName = warehouse;
    if (warehouse && typeof warehouse === "object") {
      warehouseName = warehouse.name || warehouse.label;
    }

    await client.query("BEGIN");

    // ✅ Insert into inward table — now includes warehouse_id
    const inwardResult = await client.query(
      `INSERT INTO inward (
        reference_no, po_number, vendor_name, dc_number,
        invoice_number, warehouse, warehouse_id, items, is_manual,
        delivery_files, org_id, created_by, updated_by,
        status, approval_status, total_quantity
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
      RETURNING *`,
      [
        finalReferenceNo,
        poNumber || null,
        vendorName,
        dcNumber || null,
        invoiceNumber || null,
        warehouseName || null,
        parsedWarehouseId,     // ✅ $7 — store the actual warehouse ID
        JSON.stringify(itemsJson),
        isManualEntry,
        JSON.stringify(deliveryFiles),
        orgId,
        userId,
        userId,
        "Pending",
        "Pending",
        totalQuantity,
      ]
    );

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: `Inward created successfully. Awaiting approval.`,
      data: inwardResult.rows[0],
      files_uploaded: deliveryFiles.length,
      mode: isManualEntry ? "MANUAL" : "WITH PO",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Create Inward Error:", err);
    return res.status(500).json({
      success: false,
      message: "Create inward failed",
      error: err.message,
    });
  } finally {
    client.release();
  }
};


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

  try {
    const { id } = req.params;
    const userId = req.session.user?.id;
    const orgId = req.session.user?.orgId;

    // Get inward record with items
    const inwardResult = await client.query(
      `SELECT * FROM inward WHERE id = $1 AND is_deleted = false AND approval_status = 'Pending'`,
      [id]
    );

    if (inwardResult.rowCount === 0) {
      return res.status(404).json({
        success: false,
        message: "Inward not found or already processed",
      });
    }

    const inward = inwardResult.rows[0];
    const isManual = inward.is_manual;
    const items = inward.items || [];
    const vendorName = inward.vendor_name;
    const poNumber = inward.po_number;
    const dcNumber = inward.dc_number;
    const invoiceNumber = inward.invoice_number;
    const warehouse = inward.warehouse;
    const referenceNo = inward.reference_no;

    // ✅ Use stored warehouse_id directly — no name lookup needed
    const warehouseId = inward.warehouse_id ? parseInt(inward.warehouse_id) : null;

    // CHECK IF WAREHOUSE IS "INVENTORY STORE"
    const isInventoryStore =
      warehouse && warehouse.toLowerCase() === "inventory store";

    console.log("Approving inward:", {
      id,
      vendorName,
      poNumber,
      warehouse,
      warehouseId,
      isInventoryStore,
      willCreateStock: !isInventoryStore,
    });

    await client.query("BEGIN");

    // ========== CREATE GRN ==========
    let grnId = null;
    let poId = null;

    if (poNumber && !isManual) {
      const poRes = await client.query(
        `SELECT id FROM purchase_orders WHERE po_number = $1`,
        [poNumber]
      );
      if (poRes.rows.length > 0) {
        poId = poRes.rows[0].id;
      }
    }

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

    const grnResult = await client.query(
      `INSERT INTO grn (
        grn_number,
        po_id,
        po_number,
        vendor_name,
        gate_entry_no,
        invoice_no,
        delivery_challan_no,
        grn_date,
        created_by,
        created_at,
        org_id
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
      RETURNING id`,
      [
        grnNumber,
        poId,
        poNumber,
        vendorName,
        referenceNo,
        invoiceNumber,
        dcNumber,
        currentDate,
        userId,
        currentDate,
        orgId,
      ]
    );

    grnId = grnResult.rows[0].id;
    console.log(`GRN created with ID: ${grnId}`);

    // ========== CREATE GRN ITEMS ==========
    if (items && items.length > 0) {
      console.log(`Creating ${items.length} GRN items for inward ${id}`);

      for (const item of items) {
        const orderedQty = parseFloat(item.quantity) || 0;
        const receivedQty = orderedQty;
        const acceptedQty = orderedQty;
        const rejectedQty = 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const totalPrice = acceptedQty * unitPrice;
        const itemName = item.name || item.item_name;

        await client.query(
          `INSERT INTO grn_items (
            grn_id,
            item_name,
            ordered_qty,
            received_qty,
            accepted_qty,
            rejected_qty,
            unit_price,
            total_price,
            grand_total,
            store_location,
            created_at
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())`,
          [
            grnId,
            itemName,
            orderedQty,
            receivedQty,
            acceptedQty,
            rejectedQty,
            unitPrice,
            totalPrice,
            totalPrice,
            warehouse || "Main Store",
          ]
        );
        console.log(`Created GRN item for: ${itemName}`);
      }
    }

    // ========== CREATE STOCK ENTRIES ONLY IF NOT INVENTORY STORE ==========
    let createdStocks = [];

    if (isInventoryStore) {
      console.log("⚠️ Warehouse is 'Inventory Store' - SKIPPING stock creation");
    } else if (items && items.length > 0) {
      console.log(
        `Creating stock entries for ${items.length} item(s) (Warehouse: ${warehouse}, Warehouse ID: ${warehouseId})`
      );

      for (const item of items) {
        const itemQuantity = parseFloat(item.quantity) || 0;
        const materialName = item.name || item.item_name;
        const quantityUnit = item.selectedUom || item.uom || "Tons";

        if (itemQuantity <= 0) {
          console.log(`Skipping ${materialName} - quantity is 0`);
          continue;
        }

        try {
          const stockResult = await client.query(
            `INSERT INTO stocks (
              po_number,
              supplier_name,
              material_name,
              quantity_unit,
              net_quantity,
              received_by,
              created_by,
              org_id,
              received_date,
              grn_id,
              inward_id,
              warehouse_id
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
            RETURNING *`,
            [
              poNumber || null,
              vendorName,
              materialName,
              quantityUnit,
              itemQuantity,
              userId,
              userId,
              orgId,
              new Date(),
              grnId,
              parseInt(id),
              warehouseId, // ✅ correct ID from inward.warehouse_id
            ]
          );
          createdStocks.push(stockResult.rows[0]);
          console.log(
            `✅ Stock created: ${materialName}, Qty: ${itemQuantity} ${quantityUnit}, Warehouse ID: ${warehouseId}`
          );
        } catch (stockErr) {
          console.error("Error creating stock entry:", stockErr.message);
        }
      }
    }

    // Update inward approval status
    await client.query(
      `UPDATE inward 
       SET approval_status = 'Approved',
           status = 'Completed',
           approved_by = $1,
           approved_at = NOW(),
           updated_by = $1,
           updated_at = NOW()
       WHERE id = $2`,
      [userId, id]
    );

    await logUserAction({
      userId,
      action: "APPROVE",
      model: "Inward",
      documentId: id,
      details: {
        referenceNo: inward.reference_no,
        warehouse,
        warehouseId,
        isInventoryStore,
        stocksCreated: createdStocks.length,
      },
      req,
    });

    await client.query("COMMIT");

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

    return res.json({
      success: true,
      message: isInventoryStore
        ? `✅ Inward approved. GRN created but NO stock entries (Warehouse: ${warehouse})`
        : `✅ Inward approved. GRN created with ${createdStocks.length} stock entries. Total quantity: ${totalQuantity}`,
      data: {
        inwardId: id,
        referenceNo: inward.reference_no,
        grnId,
        warehouse,
        warehouseId,
        isInventoryStore,
        stocksCreated: createdStocks.length,
        totalQuantity,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Approve Inward Error:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to approve inward: " + err.message,
      error: err.message,
    });
  } finally {
    client.release();
  }
};


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

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

    const {
      page = 1,
      limit = 10,
      search = "",
      approvalStatus = "all",
      sortBy = "created_at",
      order = "desc",
    } = req.query;

    const pageNumber = parseInt(page);
    const pageSize = parseInt(limit);
    const offset = (pageNumber - 1) * pageSize;

    let sortField = "i.created_at";
    if (sortBy === "vendor") sortField = "i.vendor_name";
    if (sortBy === "product") sortField = "i.items";
    if (sortBy === "quantity") sortField = "i.total_quantity";
    if (sortBy === "createdBy") sortField = "u.first_name";
    if (sortBy === "status") sortField = "i.approval_status";

    const sortOrder = order === "asc" ? "ASC" : "DESC";

    let where = `WHERE i.is_deleted = false`;
    const values = [];
    let idx = 1;

    if (orgId) {
      where += ` AND i.org_id = $${idx++}`;
      values.push(orgId);
    }

    if (approvalStatus !== "all") {
      where += ` AND i.approval_status = $${idx++}`;
      values.push(
        approvalStatus === "pending"
          ? "Pending"
          : approvalStatus === "approved"
          ? "Approved"
          : "Rejected"
      );
    }

    if (search) {
      where += ` AND (
        i.reference_no ILIKE $${idx} OR
        i.vendor_name ILIKE $${idx} OR
        i.po_number ILIKE $${idx} OR
        i.dc_number ILIKE $${idx} OR
        i.invoice_number ILIKE $${idx}
      )`;
      values.push(`%${search}%`);
      idx++;
    }

    const countResult = await client.query(
      `SELECT COUNT(*) FROM inward i ${where}`,
      values
    );

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

    const dataQuery = `
      SELECT 
        i.*,
        u.first_name,
        u.last_name,
        approver.first_name as approver_first_name,
        approver.last_name as approver_last_name,
        rejecter.first_name as rejecter_first_name,
        rejecter.last_name as rejecter_last_name
      FROM inward i
      LEFT JOIN users u ON u.id = i.created_by
      LEFT JOIN users approver ON approver.id = i.approved_by
      LEFT JOIN users rejecter ON rejecter.id = i.rejected_by
      ${where}
      ORDER BY ${sortField} ${sortOrder}
      LIMIT $${idx++} OFFSET $${idx++}
    `;

    values.push(pageSize, offset);

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

    const formatted = result.rows.map((item) => {
      let displayProduct = "-";
      let totalQuantity = item.total_quantity || "-";

      if (item.items && Array.isArray(item.items) && item.items.length > 0) {
        displayProduct =
          item.items[0].name || item.items[0].item_name || "-";
      }

      return {
        id: item.id,
        isManual: item.is_manual,
        vendor: item.vendor_name || "-",
        typeRef: item.is_manual
          ? `Manual / Ref: ${item.reference_no}`
          : `PO / Ref: ${item.po_number}`,
        materialName: displayProduct,
        quantity: totalQuantity,
        createdBy: item.first_name
          ? `${item.first_name} ${item.last_name}`
          : "-",
        createdAt: item.created_at,
        status: item.status,
        approvalStatus: item.approval_status,
        referenceNo: item.reference_no,
        poNumber: item.po_number,
        dcNumber: item.dc_number,
        invoiceNumber: item.invoice_number,
        warehouse: item.warehouse,
        warehouseId: item.warehouse_id, // ✅ included in list response
        approvedBy: item.approver_first_name
          ? `${item.approver_first_name} ${item.approver_last_name}`
          : null,
        approvedAt: item.approved_at,
        rejectedBy: item.rejecter_first_name
          ? `${item.rejecter_first_name} ${item.rejecter_last_name}`
          : null,
        rejectedAt: item.rejected_at,
        rejectionReason: item.rejection_reason,
        approvalNotes: item.approval_notes,
      };
    });

    return res.json({
      success: true,
      message: "Inward list fetched successfully",
      data: formatted,
      pagination: {
        total,
        page: pageNumber,
        limit: pageSize,
        totalPages: Math.ceil(total / pageSize),
      },
    });
  } catch (err) {
    console.error("Fetch Inward Error:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch inward list",
    });
  } finally {
    client.release();
  }
};


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

  try {
    const { id } = req.params;
    const userId = req.session.user?.id;

    const inwardResult = await client.query(
      `SELECT * FROM inward WHERE id = $1 AND is_deleted = false`,
      [id]
    );

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

    const inward = inwardResult.rows[0];

    if (inward.approval_status !== "Pending") {
      return res.status(400).json({
        success: false,
        message: "Only pending inward entries can be deleted",
      });
    }

    await client.query("BEGIN");

    await client.query(
      `UPDATE inward 
       SET is_deleted = true, deleted_at = NOW(), deleted_by = $1
       WHERE id = $2`,
      [userId, id]
    );

    await client.query("COMMIT");

    res.json({ success: true, message: "Inward deleted successfully" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error(err);
    res.status(500).json({ success: false, message: "Delete failed" });
  } finally {
    client.release();
  }
};


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

  try {
    const { id } = req.params;

    const result = await client.query(
      `SELECT i.*, 
        u.first_name, u.last_name,
        approver.first_name as approver_first_name, approver.last_name as approver_last_name,
        rejecter.first_name as rejecter_first_name, rejecter.last_name as rejecter_last_name
       FROM inward i
       LEFT JOIN users u ON u.id = i.created_by
       LEFT JOIN users approver ON approver.id = i.approved_by
       LEFT JOIN users rejecter ON rejecter.id = i.rejected_by
       WHERE i.id = $1 AND i.is_deleted = false`,
      [id]
    );

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

    const item = result.rows[0];

    const formatted = {
      id: item.id,
      isManual: item.is_manual,
      referenceNo: item.reference_no,
      poNumber: item.po_number,
      vendorName: item.vendor_name,
      dcNumber: item.dc_number,
      invoiceNumber: item.invoice_number,
      warehouse: item.warehouse,
      warehouseId: item.warehouse_id, // ✅ included in detail response
      items: item.items,
      deliveryFiles: item.delivery_files,
      status: item.status,
      approvalStatus: item.approval_status,
      createdBy: item.first_name
        ? `${item.first_name} ${item.last_name}`
        : "-",
      createdAt: item.created_at,
      approvedBy: item.approver_first_name
        ? `${item.approver_first_name} ${item.approver_last_name}`
        : null,
      approvedAt: item.approved_at,
      approvalNotes: item.approval_notes,
      rejectedBy: item.rejecter_first_name
        ? `${item.rejecter_first_name} ${item.rejecter_last_name}`
        : null,
      rejectedAt: item.rejected_at,
      rejectionReason: item.rejection_reason,
    };

    return res.json({
      success: true,
      message: "Inward fetched",
      data: formatted,
    });
  } catch (err) {
    console.error(err);
    return res.status(500).json({
      success: false,
      message: "Fetch inward failed",
    });
  } finally {
    client.release();
  }
};


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

  try {
    const userId = req.session.user?.id;
    const { id } = req.params;

    let {
      vendorName,
      dcNumber,
      invoiceNumber,
      warehouse,
      warehouseId,   // ✅ NEW: received from frontend
      items,
    } = req.body;

    const existingInward = await client.query(
      `SELECT * FROM inward WHERE id = $1 AND is_deleted = false AND approval_status = 'Pending'`,
      [id]
    );

    if (existingInward.rowCount === 0) {
      return res.status(400).json({
        success: false,
        message: "Only pending inward entries can be edited",
      });
    }

    const newItems = typeof items === "string" ? JSON.parse(items) : items;

    // ✅ Parse warehouseId safely
    const parsedWarehouseId =
      warehouseId && !isNaN(parseInt(warehouseId))
        ? parseInt(warehouseId)
        : null;

    // Get warehouse name if it's an object
    let warehouseName = warehouse;
    if (warehouse && typeof warehouse === "object") {
      warehouseName = warehouse.name || warehouse.label;
    }

    const totalQuantity = newItems.reduce(
      (sum, item) => sum + (parseFloat(item.quantity) || 0),
      0
    );

    await client.query("BEGIN");

    // ✅ UPDATE now includes warehouse_id
    await client.query(
      `UPDATE inward SET
        vendor_name   = $1,
        dc_number     = $2,
        invoice_number = $3,
        warehouse     = $4,
        warehouse_id  = $5,
        items         = $6,
        total_quantity = $7,
        updated_by    = $8,
        updated_at    = NOW()
      WHERE id = $9`,
      [
        vendorName,
        dcNumber,
        invoiceNumber,
        warehouseName || null,
        parsedWarehouseId,      // ✅ $5 — store the actual warehouse ID
        JSON.stringify(newItems),
        totalQuantity,
        userId,
        id,
      ]
    );

    await client.query("COMMIT");

    res.json({ success: true, message: "Inward updated successfully" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error(err);
    res.status(500).json({ success: false, message: "Update failed" });
  } finally {
    client.release();
  }
};


module.exports = {
  createInward,
  getInwardList,
  deleteInward,
  getInwardById,
  updateInward,
  approveInward,
};