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) => {
//   try {
//     const userId = req.user?._id;
//     const orgId = req.user?.orgId;

//     let {
//       poNumber,
//       productName,
//       quantityUnit,
//       vendorName,
//       referenceNo,
//       totalQuantity,
//       deliveryType,
//       trackingNumber,
//       personToReceive,
//       locationBlock,
//       inwardStatus,
//       isReturnable,
//     } = req.body;

//     /* ===== NORMALIZE ===== */
//     productName = productName?.trim();
//     referenceNo = referenceNo?.trim().toUpperCase();
//     vendorName = vendorName?.trim();

//     const qty = Number(totalQuantity);

//     const returnable =
//       isReturnable === true ||
//       isReturnable === "true" ||
//       isReturnable === "Yes";

//     /* ===== VALIDATION ===== */
//     if (!productName || !referenceNo || !totalQuantity) {
//       return res.status(400).json({
//         success: false,
//         message: "Product , Reference No , Quantity required",
//       });
//     }

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

//     /* ===== DUPLICATE ERP RULE (ORG BASED) ===== */
//     // const existing = await Inward.findOne({
//     //   referenceNo,
//     //   // orgId,
//     //   isDeleted: false,
//     // });

//     // if (existing) {
//     //   return res.status(409).json({
//     //     success: false,
//     //     message: "Inward already exists",
//     //   });
//     // }

//     /* ===== FILES ===== */
//     let deliveryFiles = [];

//     if (req.files?.length > 10) {
//       return res.status(400).json({
//         success: false,
//         message: "Max 10 files allowed",
//       });
//     }

//     if (req.files?.length) {
//       deliveryFiles = req.files.map((f) => ({
//         originalName: f.originalname,
//         fileName: f.filename,
//         fileUrl: f.path.replace(/^.*uploads/, "uploads").replace(/\\/g, "/"),
//       }));
//     }

//     /* ===== DELIVERY ENUM SAFE ===== */
//     const allowedDelivery = [
//       "Road",
//       "Courier",
//       "Transport",
//       "Hand Delivery",
//       "Other",
//     ];

//     if (!allowedDelivery.includes(deliveryType)) {
//       deliveryType = "Other";
//     }

//     /* ===== CREATE ===== */
//     const inward = await Inward.create({
//       product: {
//         productName,
//       },

//       uom: {
//         symbol: quantityUnit,
//       },

//       referenceNo,
//       vendorName,
//       poNumber,
//       totalQuantity: qty,

//       deliveryDetails: {
//         deliveryType,
//         trackingNumber,
//         personToReceive,
//         locationBlock,
//         inwardStatus: inwardStatus || "Pending",
//         isReturnable: returnable,
//         deliveryFiles,
//       },

//       status: "Pending",

//       // orgId,
//       createdBy: userId,
//       updatedBy: userId,
//     });

//     return res.status(201).json({
//       success: true,
//       message: "Inward created successfully",
//       data: inward,
//     });
//   } catch (err) {
//     console.log(err);
//     return res.status(500).json({
//       success: false,
//       message: "Create inward failed",
//     });
//   }
// };

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,
      productName,
      quantityUnit,
      vendorName,
      referenceNo,
      totalQuantity,
      deliveryType,
      trackingNumber,
      personToReceive,
      locationBlock,
      inwardStatus,
      isReturnable,
    } = req.body;

    /* ===== NORMALIZE ===== */
    productName = productName?.trim();
    referenceNo = referenceNo?.trim().toUpperCase();
    vendorName = vendorName?.trim();

    const qty = Number(totalQuantity);

    const returnable =
      isReturnable === true ||
      isReturnable === "true" ||
      isReturnable === "Yes";

    /* ===== VALIDATION ===== */
    if (!productName || !referenceNo || !totalQuantity) {
      return res.status(400).json({
        success: false,
        message: "Product , Reference No , Quantity required",
      });
    }

    if (isNaN(qty) || qty <= 0) {
      return res.status(400).json({
        success: false,
        message: "Invalid Quantity",
      });
    }

    /* ===== FILES ===== */
    let deliveryFiles = [];

    if (req.files?.length > 10) {
      return res.status(400).json({
        success: false,
        message: "Max 10 files allowed",
      });
    }

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

    /* ===== DELIVERY ENUM SAFE ===== */
    const allowedDelivery = [
      "Road",
      "Courier",
      "Transport",
      "Hand Delivery",
      "Other",
    ];

    if (!allowedDelivery.includes(deliveryType)) {
      deliveryType = "Other";
    }

    /* ===== BUILD JSONB ===== */
    const deliveryDetails = {
      deliveryType,
      trackingNumber,
      personToReceive,
      locationBlock,
      inwardStatus: inwardStatus || "Pending",
      isReturnable: returnable,
      deliveryFiles,
    };

    /* ===== INSERT ===== */
    const result = await client.query(
      `INSERT INTO inward (
        product_name,
        reference_no,
        vendor_name,
        total_quantity,
        symbol,
        org_id,
        created_by,
        updated_by,
        delivery_details,
        person_to_receive
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9, $10)
      RETURNING *`,
      [
        productName,
        referenceNo,
        vendorName || null,
        qty,
        quantityUnit || null,
        orgId,
        userId,
        userId,
        JSON.stringify(deliveryDetails),
        personToReceive || null,
      ],
    );

    return res.status(201).json({
      success: true,
      message: "Inward created successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error(err);
    return res.status(500).json({
      success: false,
      message: "Create inward failed",
    });
  } finally {
    client.release();
  }
};

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

//     const {
//       page = 1,
//       limit = 10,
//       search = "",
//       status,
//       sortBy = "createdAt",
//       order = "desc",
//     } = req.query;

//     const pageNumber = parseInt(page);
//     const pageSize = parseInt(limit);
//     const sortOrder = order === "asc" ? 1 : -1;

//     let sortField = "createdAt";

//     if (sortBy === "vendor") sortField = "vendorName";
//     if (sortBy === "product") sortField = "productName";
//     if (sortBy === "quantity") sortField = "totalQuantity";
//     if (sortBy === "createdBy") sortField = "createdAt"; // fallback

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

//     let filter = {
//       // orgId,
//       isDeleted: false,
//     };

//     if (status) {
//       filter.status = status;
//     }

//     if (search) {
//       filter.$or = [
//         { referenceNo: { $regex: search, $options: "i" } },
//         { productName: { $regex: search, $options: "i" } },
//         { vendorName: { $regex: search, $options: "i" } },
//       ];
//     }

//     /* ===== COUNT ===== */

//     const total = await Inward.countDocuments(filter);

//     /* ===== DATA ===== */

//     const data = await Inward.find(filter)
//       .populate({
//         path: "vehicleId",
//         select: `
//     vehicleNumber
//     vehicleType
//     driverName
//     driverNumber
//     qcStatus
//     qcId
//     inWeight
//     outWeight
//     timeIn
//     gateStatus
//     referenceNo
//     warehouseId
//     materialDetails
//   `,
//         populate: [
//           {
//             path: "qcId",
//             select: "qcNumber qcStatus",
//           },
//           {
//             path: "warehouseId",
//             select: "warehouseName",
//           },
//         ],
//       })
//       .populate("createdBy", "firstName lastName")
//       .sort({ [sortField]: sortOrder })
//       .skip((pageNumber - 1) * pageSize)
//       .limit(pageSize)
//       .lean();

//     /* ===== FORMAT RESPONSE (UI READY) ===== */

//     const formatted = data.map((item) => {
//       const vehicle = item.vehicleId || {};
//       const qc = vehicle.qcId || {};
//       const material = vehicle.materialDetails || {};

//       return {
//         id: item._id,

//         isManual: !item.vehicleId,

//         vehicleNo: vehicle.vehicleNumber || "-",

//         typeRef: vehicle.vehicleType
//           ? `${vehicle.vehicleType} / Ref: ${vehicle.referenceNo || "-"}`
//           : `Manual / Ref: ${item.referenceNo || "-"}`,

//         driver: vehicle.driverName || "-",

//         qcNo: qc.qcNumber || "-",

//         qualityDone: vehicle.qcStatus || "-",

//         inWeight: vehicle.inWeight ?? item.totalQuantity ?? "-",

//         outWeight: vehicle.outWeight || "-",

//         timeIn: vehicle.timeIn || item.createdAt,

//         gateStatus: vehicle.gateStatus || "Manual",

//         warehouse: vehicle.warehouseId?.warehouseName || "-",

//         materialName:
//           material?.materialName ||
//           item?.productName ||
//           item?.product?.productName ||
//           "-",

//         quantityUnit: item?.quantityUnit || item?.product?.uom?.symbol || "-",

//         vendor: item?.vendorName || material?.supplierName || "-",

//         quantity: item?.totalQuantity,

//         status: item.status,

//         createdBy: item.createdBy
//           ? `${item.createdBy.firstName} ${item.createdBy.lastName}`
//           : "-",
//         createdAt: item.createdAt,
//       };
//     });

//     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 errorResponse(res, "Failed to fetch inward list", 500, err);
//   }
// };
const getInwardList = async (req, res) => {
  const client = await pool.connect();

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

    const {
      page = 1,
      limit = 10,
      search = "",
      status,
      sortBy = "createdAt",
      order = "desc",
    } = req.query;

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

    /* ===== SORT ===== */
    let sortField = "i.created_at";

    if (sortBy === "vendor") sortField = "i.vendor_name";
    if (sortBy === "product") sortField = "i.product_name";
    if (sortBy === "quantity") sortField = "i.total_quantity";

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

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

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

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

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

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

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

    /* ===== DATA QUERY ===== */
    const dataQuery = `
      SELECT 
        i.*,

        cv.vehicle_number,
        cv.vehicle_type,
        cv.driver_name,
        cv.driver_number,
        cv.qc_status,
        cv.in_weight,
        cv.out_weight,
        cv.time_in,
        cv.gate_status,
        cv.reference_no AS vehicle_ref,

        w.warehouse_name,

        u.first_name,
        u.last_name,

          md.supplier_name,
  md.material_name,
  md.quantity_unit AS md_quantity_unit

      FROM inward i

      LEFT JOIN commercial_vehicles cv ON cv.id = i.vehicle_id
      LEFT JOIN warehouses w ON w.id = cv.warehouse_id
      LEFT JOIN users u ON u.id = i.created_by
      LEFT JOIN material_details md ON md.vehicle_id = i.vehicle_id

      ${where}

      ORDER BY ${sortField} ${sortOrder}

      LIMIT $${idx++} OFFSET $${idx++}
    `;

    values.push(pageSize, offset);

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

    /* ===== FORMAT RESPONSE (SAME AS YOUR UI) ===== */
    const formatted = result.rows.map((item) => {
      const delivery = item.delivery_details || {};

      return {
        id: item.id,

        isManual: !item.vehicle_id,

        vehicleNo: item.vehicle_number || "-",

        typeRef: item.vehicle_type
          ? `${item.vehicle_type} / Ref: ${item.vehicle_ref || "-"}`
          : `Manual / Ref: ${item.reference_no || "-"}`,

        driver: item.driver_name || "-",

        qcNo: "-", // you can extend later

        qualityDone: item.qc_status || "-",

        inWeight: item.in_weight ?? item.total_quantity ?? "-",

        outWeight: item.out_weight || "-",

        timeIn: item.time_in || item.created_at,

        gateStatus: item.gate_status || "Manual",

        warehouse: item.warehouse_name || "-",

        materialName: item.product_name || "-",

        quantityUnit: item.uom_symbol || "-",

        vendor: item.vendor_name || item.supplier_name || "-",

        quantity: item.total_quantity,

        materialName: item.product_name || item.material_name || "-",

        status: item.status,

        createdBy: item.first_name
          ? `${item.first_name} ${item.last_name}`
          : "-",

        createdAt: item.created_at,

        /* 🔥 EXTRA (JSON DATA if needed) */
        deliveryType: delivery.deliveryType || "-",
      };
    });

    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) => {
//   try {
//     const userId = req.user?._id;
//     const orgId = req.user?.orgId;
//     const { id } = req.params;

//     if (!id) {
//       return errorResponse(res, "Inward id required", 400);
//     }

//     const inward = await Inward.findOne({
//       _id: id,
//       // orgId,
//       isDeleted: false,
//     });

//     if (!inward) {
//       return errorResponse(res, "Inward not found", 404);
//     }

//     /* ===== BUSINESS RULE (Optional but Production Safe) ===== */
//     if (inward.status === "Closed") {
//       return errorResponse(res, "Closed inward cannot be deleted", 400);
//     }

//     /* ===== SOFT DELETE ===== */
//     inward.isDeleted = true;
//     inward.deletedAt = new Date();
//     inward.deletedBy = userId;

//     await inward.save();

//     /* ===== AUDIT LOG ===== */
//     await logUserAction({
//       userId,
//       action: "DELETE",
//       model: "Inward",
//       documentId: inward._id,
//       details: {
//         referenceNo: inward.referenceNo,
//       },
//       req,
//     });

//     return res.json({
//       success: true,
//       message: "Inward deleted successfully",
//     });
//   } catch (err) {
//     console.error("Delete Inward Error:", err);
//     return errorResponse(res, "Failed to delete inward", 500);
//   }
// };

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

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

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

    /* ===== CHECK EXIST ===== */
    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];

    /* ===== BUSINESS RULE ===== */
    if (inward.status === "Closed") {
      return res.status(400).json({
        success: false,
        message: "Closed inward cannot be deleted",
      });
    }

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

    /* ===== AUDIT LOG ===== */
    await logUserAction({
      userId,
      action: "DELETE",
      model: "Inward",
      documentId: id,
      details: {
        referenceNo: inward.reference_no,
      },
      req,
    });

    return res.json({
      success: true,
      message: "Inward deleted successfully",
    });
  } catch (err) {
    console.error("Delete Inward Error:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to delete inward",
    });
  } finally {
    client.release();
  }
};

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

//     const inward = await Inward.findOne({
//       _id: id,
//       isDeleted: false,
//     })
//       .populate({
//         path: "vehicleId",
//         select: `
//     vehicleNumber
//     vehicleType
//     driverName
//     driverNumber
//     qcStatus
//     qcId
//     inWeight
//     outWeight
//     timeIn
//     gateStatus
//     referenceNo
//     warehouseId
//     materialDetails
//   `,
//         populate: [
//           {
//             path: "qcId",
//             select: "sampleId qcStatus",
//           },
//           {
//             path: "warehouseId",
//             select: "warehouseName",
//           },
//         ],
//       })
//       .populate("createdBy", "firstName lastName")
//       .lean();

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

//     /* ===== FORMAT FOR FORM PREFILL ===== */

//     const vehicle = inward.vehicleId || {};
//     const qc = vehicle.qcId || {};
//     const material = vehicle.materialDetails || {};

//     const formatted = {
//       id: inward._id,

//       vehicleNumber: vehicle.vehicleNumber || "-",
//       vehicleType: vehicle.vehicleType || "Manual",

//       referenceNo: vehicle.referenceNo || inward.referenceNo || "-",

//       driverName: vehicle.driverName || "-",
//       driverNumber: vehicle.driverNumber || "-",

//       qcId: qc.sampleId || "-",
//       qcStatus: vehicle.qcStatus || inward.status || "-",

//       inWeight: vehicle.inWeight ?? inward.totalQuantity ?? "-",
//       outWeight: vehicle.outWeight || "-",

//       timeIn: vehicle.timeIn || inward.createdAt,

//       gateStatus: vehicle.gateStatus || "Manual",

//       productName : inward.productName || inward.product?.productName,

//       warehouseName: vehicle.warehouseId?.warehouseName || "-",

//       materialDetails:
//         material?.materialName ||
//         inward.productName ||
//         inward?.product?.productName ||
//         "-",

//       vendorName: inward.vendorName || material?.supplierName || "-",

//       quantityUnit: inward.quantityUnit || inward?.product?.uom?.symbol || "-",

//       totalQuantity: inward.totalQuantity || "-",

//       createdBy: inward.createdBy
//         ? `${inward.createdBy.firstName} ${inward.createdBy.lastName}`
//         : "-",
//     };

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

// const updateInward = async (req, res) => {
//   try {
//     const userId = req.user?._id;
//     const { id } = req.params;

//     let {
//       poNumber,
//       productName,
//       quantityUnit,
//       vendorName,
//       referenceNo,
//       totalQuantity,
//       deliveryType,
//       personToReceive,
//       locationBlock,
//       isReturnable,
//     } = req.body;

//     const inward = await Inward.findOne({
//       _id: id,
//       isDeleted: false,
//     });

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

//     /* ===== BLOCK VEHICLE INWARD UPDATE ===== */
//     if (inward.vehicleId) {
//       return res.status(400).json({
//         success: false,
//         message: "Vehicle inward cannot be edited",
//       });
//     }

//     /* ===== VALIDATION ===== */
//     const qty = Number(totalQuantity);

//     referenceNo =
//       referenceNo && referenceNo !== "undefined"
//         ? referenceNo.trim().toUpperCase()
//         : "";

//     if (!productName || !referenceNo || !qty) {
//       return res.status(400).json({
//         success: false,
//         message: "Product, Reference, Quantity required",
//       });
//     }

//     /* ===== FILE UPDATE ===== */

//     let deliveryFiles = inward.deliveryDetails?.deliveryFiles || [];

//     if (req.files?.length) {
//       deliveryFiles = req.files.map((f) => ({
//         originalName: f.originalname,
//         fileName: f.filename,
//         fileUrl: f.path.replace(/^.*uploads/, "uploads").replace(/\\/g, "/"),
//       }));
//     }

//     /* ===== UPDATE ===== */

//     // inward.poNumber = referenceNo;
//     if (referenceNo) {
//       inward.poNumber = referenceNo;
//       inward.referenceNo = referenceNo;
//     }
//     // inward.referenceNo = referenceNo?.trim().toUpperCase();
//     inward.vendorName = vendorName;
//     inward.totalQuantity = qty;

//     inward.product = {
//       productName,
//     };

//     inward.uom = {
//       symbol: quantityUnit,
//     };

//     inward.deliveryDetails = {
//       ...inward.deliveryDetails,
//       deliveryType,
//       personToReceive,
//       locationBlock,
//       isReturnable:
//         isReturnable === true ||
//         isReturnable === "true" ||
//         isReturnable === "Yes",
//       deliveryFiles,
//     };

//     inward.updatedBy = userId;

//     await inward.save();

//     await logUserAction({
//       userId,
//       action: "UPDATE",
//       model: "Inward",
//       documentId: inward._id,
//       details: {
//         referenceNo,
//         productName,
//         totalQuantity,
//       },
//       req,
//     });

//     return res.json({
//       success: true,
//       message: "Inward updated successfully",
//     });
//   } catch (err) {
//     console.log(err);
//     return res.status(500).json({
//       success: false,
//       message: "Update inward failed",
//     });
//   }
// };
const getInwardById = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;

    const result = await client.query(
      `
      SELECT 
        i.*,

        cv.vehicle_number,
        cv.vehicle_type,
        cv.driver_name,
        cv.driver_number,
        cv.qc_status,
        cv.in_weight,
        cv.out_weight,
        cv.time_in,
        cv.gate_status,
        cv.reference_no AS vehicle_ref,
        pr.id AS receive_id,
pr.first_name AS receive_first_name,
pr.last_name AS receive_last_name,

        w.warehouse_name,

        u.first_name,
        u.last_name,

        md.supplier_name,
        md.material_name,
        md.quantity_unit AS md_quantity_unit

      FROM inward i

      LEFT JOIN commercial_vehicles cv ON cv.id = i.vehicle_id
      LEFT JOIN warehouses w ON w.id = cv.warehouse_id
      LEFT JOIN users u ON u.id = i.created_by
      LEFT JOIN users pr ON pr.id = i.person_to_receive

      /* 🔥 get one material row only */
      LEFT JOIN LATERAL (
        SELECT *
        FROM material_details md
        WHERE md.vehicle_id = i.vehicle_id
        LIMIT 1
      ) md ON true

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

    /* ===== FORMAT SAME AS BEFORE ===== */
    const delivery = item.delivery_details || {};

    const formatted = {
      id: item.id,

      vehicleNumber: item.vehicle_number || "-",
      vehicleType: item.vehicle_type || "Manual",

      referenceNo: item.vehicle_ref || item.reference_no || "-",

      driverName: item.driver_name || "-",
      driverNumber: item.driver_number || "-",

      qcId: "-", // extend later if needed
      qcStatus: item.qc_status || item.status || "-",

      inWeight: item.in_weight ?? item.total_quantity ?? "-",
      outWeight: item.out_weight || "-",

      deliveryType: delivery.deliveryType || "",
      // personToReceive: delivery.personToReceive || "",
      personToReceive: item.receive_id || "",
      personToReceiveName: item.receive_first_name
        ? `${item.receive_first_name} ${item.receive_last_name}`
        : "",
      locationBlock: delivery.locationBlock || "",

      timeIn: item.time_in || item.created_at,

      gateStatus: item.gate_status || "Manual",

      productName: item.product_name || "-",

      warehouseName: item.warehouse_name || "-",

      materialDetails: item.material_name || item.product_name || "-",

      vendorName: item.vendor_name || item.supplier_name || "-",

      quantityUnit: item.symbol || item.md_quantity_unit || "-",

      totalQuantity: item.total_quantity || "-",

      createdBy: item.first_name ? `${item.first_name} ${item.last_name}` : "-",
    };

    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 {
      poNumber,
      productName,
      quantityUnit,
      vendorName,
      referenceNo,
      totalQuantity,
      deliveryType,
      personToReceive,
      locationBlock,
      isReturnable,
    } = req.body;

    /* ===== FETCH EXISTING ===== */
    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];

    /* ===== BLOCK VEHICLE INWARD ===== */
    if (inward.vehicle_id) {
      return res.status(400).json({
        success: false,
        message: "Vehicle inward cannot be edited",
      });
    }

    /* ===== VALIDATION ===== */
    const qty = Number(totalQuantity);

    referenceNo =
      referenceNo && referenceNo !== "undefined"
        ? referenceNo.trim().toUpperCase()
        : "";

    if (!productName || !referenceNo || !qty) {
      return res.status(400).json({
        success: false,
        message: "Product, Reference, Quantity required",
      });
    }

    /* ===== FILES ===== */
    let deliveryFiles = inward.delivery_details?.deliveryFiles || [];

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

    /* ===== BUILD UPDATED JSON ===== */
    const updatedDelivery = {
      ...inward.delivery_details,
      deliveryType,
      personToReceive,
      locationBlock,
      isReturnable:
        isReturnable === true ||
        isReturnable === "true" ||
        isReturnable === "Yes",
      deliveryFiles,
    };

    /* ===== UPDATE ===== */
    await client.query(
      `UPDATE inward SET
        product_name = $1,
        reference_no = $2,
        vendor_name = $3,
        total_quantity = $4,
        symbol = $5,
        delivery_details = $6,
        updated_by = $7,
        updated_at = NOW()
      WHERE id = $8`,
      [
        productName,
        referenceNo,
        vendorName || null,
        qty,
        quantityUnit || null,
        JSON.stringify(updatedDelivery),
        userId,
        id,
      ],
    );

    /* ===== AUDIT LOG ===== */
    await logUserAction({
      userId,
      action: "UPDATE",
      model: "Inward",
      documentId: id,
      details: {
        referenceNo,
        productName,
        totalQuantity: qty,
      },
      req,
    });

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

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