const { pool } = require("../../config/db");
const XLSX = require('xlsx');
const fs = require('fs');
const path = require('path');

// ─────────────────────────────────────────────
// GENERATE ITEM CODE (race-condition safe)
// ─────────────────────────────────────────────
const generateItemCode = async () => {
  const result = await pool.query(
    `SELECT COALESCE(MAX(CAST(SUBSTRING(item_code FROM 5) AS INT)), 0) + 1 AS next_num
     FROM procurement_itemmaster`
  );
  const nextNum = parseInt(result.rows[0].next_num, 10);
  return `ITM-${String(nextNum).padStart(3, "0")}`;
};

// GET ITEM GROUPS
const getItemGroups = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }
    
    const result = await pool.query(
      `SELECT id, group_name, group_id
       FROM group_master
       WHERE is_deleted = false AND org_id = $1
       ORDER BY group_name ASC`,
      [orgId]
    );
    
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("Error in getItemGroups:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// DOWNLOAD TEMPLATE
// ─────────────────────────────────────────────
const downloadTemplate = async (req, res) => {
  try {
    const templateData = [{
      'Item Name': 'Example: Ethanol',
      'UOM': 'Ltr',
      'HSN Code': '2207',
      'Description': 'High quality ethanol',
      'Tax (%)': '18',
      'Group Name': 'Raw Materials',
      'QC Required': 'Yes',
      'Weighment Required': 'No'
    }];

    const worksheet = XLSX.utils.json_to_sheet(templateData);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'ItemMasterTemplate');

    const instructions = [
      { 'Field': 'Item Name', 'Required': 'Yes', 'Description': 'Name of the item/product' },
      { 'Field': 'UOM', 'Required': 'Yes', 'Description': 'Unit of Measurement (e.g., Ltr, Kg, Pcs)' },
      { 'Field': 'HSN Code', 'Required': 'No', 'Description': 'HSN/SAC code for GST' },
      { 'Field': 'Description', 'Required': 'No', 'Description': 'Additional item description' },
      { 'Field': 'Tax (%)', 'Required': 'No', 'Description': 'Tax percentage (0-100). Leave empty for no tax' },
      { 'Field': 'Group Name', 'Required': 'No', 'Description': 'Item group from Group Master' },
      { 'Field': 'QC Required', 'Required': 'No', 'Description': 'Yes/No or True/False. Indicates if Quality Check is required' },
      { 'Field': 'Weighment Required', 'Required': 'No', 'Description': 'Yes/No or True/False. Indicates if Weighment is required for this item' }
    ];
    
    const instructionsSheet = XLSX.utils.json_to_sheet(instructions);
    XLSX.utils.book_append_sheet(workbook, instructionsSheet, 'Instructions');

    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=item_master_template.xlsx');
    res.send(buffer);

  } catch (err) {
    console.error("Error in downloadTemplate:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// CREATE ITEM
// ─────────────────────────────────────────────
const createItem = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const created_by = req.user?.id;

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

    const {
      itemName,
      uom,
      hsnCode,
      description,
      taxPercentage,
      groupId,
      groupName,
      qcRequired,
      weighmentRequired
    } = req.body;

    if (!itemName || !uom) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: itemName and uom are required",
      });
    }

    const item_code = await generateItemCode();

    let finalGroupId = null;
    let finalGroupName = null;
    
    if (groupId) {
      const groupCheck = await pool.query(
        `SELECT id, group_name FROM group_master 
         WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [groupId, orgId]
      );
      if (groupCheck.rows.length > 0) {
        finalGroupId = groupId;
        finalGroupName = groupCheck.rows[0].group_name;
      }
    } else if (groupName) {
      const groupCheck = await pool.query(
        `SELECT id, group_name FROM group_master 
         WHERE group_name = $1 AND org_id = $2 AND is_deleted = false`,
        [groupName, orgId]
      );
      if (groupCheck.rows.length > 0) {
        finalGroupId = groupCheck.rows[0].id;
        finalGroupName = groupCheck.rows[0].group_name;
      }
    }

    let finalTaxPercentage = null;
    if (taxPercentage !== undefined && taxPercentage !== null && taxPercentage !== '') {
      const taxValue = parseFloat(taxPercentage);
      if (!isNaN(taxValue) && taxValue >= 0 && taxValue <= 100) {
        finalTaxPercentage = taxValue;
      }
    }

    const finalQcRequired = qcRequired === true || qcRequired === 'true';
    const finalWeighmentRequired = weighmentRequired === true || weighmentRequired === 'true';

    const result = await pool.query(
      `INSERT INTO procurement_itemmaster (
        item_code, item_name, uom, hsn_code,
        description, tax_percentage, group_id, group_name, org_id, created_by, 
        qc_required, weighment_required
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12
      ) RETURNING *`,
      [
        item_code,
        itemName,
        uom || null,
        hsnCode || null,
        description || null,
        finalTaxPercentage,
        finalGroupId,
        finalGroupName,
        orgId,
        created_by || null,
        finalQcRequired,
        finalWeighmentRequired
      ]
    );

    res.status(201).json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in createItem:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// BULK UPLOAD ITEMS
// ─────────────────────────────────────────────
const bulkUploadItems = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const created_by = req.user?.id;
    const { confirmed = false } = req.body;

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

    if (!req.file) {
      return res.status(400).json({ success: false, message: "No file uploaded" });
    }

    const existingGroups = await pool.query(
      `SELECT id, group_name FROM group_master 
       WHERE org_id = $1 AND is_deleted = false`,
      [orgId]
    );
    
    const groupMap = new Map();
    existingGroups.rows.forEach(group => {
      groupMap.set(group.group_name.toLowerCase(), group.id);
    });

    let buffer = req.file.buffer;
    if (!buffer && req.file.path) {
      buffer = fs.readFileSync(req.file.path);
      fs.unlinkSync(req.file.path);
    }

    if (!buffer || buffer.length === 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Unable to read file. Please check the file." 
      });
    }

    let workbook;
    let data;
    
    try {
      workbook = XLSX.read(buffer, { type: 'buffer' });
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      data = XLSX.utils.sheet_to_json(worksheet, { defval: "" });
      
      if (data.length === 0) {
        return res.status(400).json({ 
          success: false, 
          message: "No data found in file." 
        });
      }
    } catch (parseError) {
      return res.status(400).json({ 
        success: false, 
        message: `Failed to parse Excel file: ${parseError.message}` 
      });
    }

    const requiredColumns = ['Item Name', 'UOM'];
    
    const firstRow = data[0];
    const missingColumns = requiredColumns.filter(col => !firstRow.hasOwnProperty(col) && 
      !firstRow.hasOwnProperty(col.toLowerCase()) &&
      !firstRow.hasOwnProperty(col.toUpperCase()));
    
    if (missingColumns.length > 0) {
      return res.status(400).json({
        success: false,
        message: `Missing required columns: ${missingColumns.join(', ')}. Please use the template format.`,
        validationErrors: missingColumns
      });
    }

    const validationResults = {
      isValid: true,
      totalRows: data.length,
      validRows: [],
      invalidRows: [],
      summary: {
        totalItems: data.length,
        validCount: 0,
        invalidCount: 0,
        missingItemNames: 0,
        missingUOMs: 0,
        invalidGroups: 0,
        invalidTaxPercentage: 0
      }
    };

    for (let i = 0; i < data.length; i++) {
      const row = data[i];
      const rowNumber = i + 2;

      const itemName = row['Item Name'] || row['itemName'] || row['ITEM_NAME'] || row['item_name'];
      const uom = row['UOM'] || row['uom'] || row['Unit'] || row['unit'];
      const groupName = row['Group Name'] || row['groupName'] || row['GROUP_NAME'] || row['group_name'];
      
      let taxPercentage = null;
      const taxValue = row['Tax (%)'] || row['tax_percentage'] || row['TAX_PERCENTAGE'] || row['Tax %'] || row['Tax'];
      if (taxValue !== undefined && taxValue !== null && taxValue !== '') {
        const taxNum = parseFloat(taxValue);
        if (!isNaN(taxNum) && taxNum >= 0 && taxNum <= 100) {
          taxPercentage = taxNum;
        }
      }

      let qcRequired = false;
      const qcValue = row['QC Required'] || row['qc_required'] || row['QC_REQUIRED'] || row['Quality Check'];
      if (qcValue !== undefined && qcValue !== null && qcValue !== '') {
        const qcString = String(qcValue).toLowerCase();
        qcRequired = qcString === 'yes' || qcString === 'true' || qcString === '1' || qcString === 'y';
      }

      let weighmentRequired = false;
      const weighmentValue = row['Weighment Required'] || row['weighment_required'] || row['WEIGHMENT_REQUIRED'] || row['Weighment'];
      if (weighmentValue !== undefined && weighmentValue !== null && weighmentValue !== '') {
        const weighmentString = String(weighmentValue).toLowerCase();
        weighmentRequired = weighmentString === 'yes' || weighmentString === 'true' || weighmentString === '1' || weighmentString === 'y';
      }
      
      const validationErrors = [];
      
      if (!itemName || String(itemName).trim() === '') {
        validationErrors.push("Item Name is required");
        validationResults.summary.missingItemNames++;
      }
      
      if (!uom || String(uom).trim() === '') {
        validationErrors.push("UOM is required");
        validationResults.summary.missingUOMs++;
      }

      if (taxValue && taxValue !== '' && taxPercentage === null) {
        validationErrors.push("Tax percentage must be a number between 0 and 100");
        validationResults.summary.invalidTaxPercentage++;
      }

      if (groupName && String(groupName).trim() !== '') {
        const normalizedGroupName = String(groupName).trim();
        if (!groupMap.has(normalizedGroupName.toLowerCase())) {
          validationErrors.push(`Group '${normalizedGroupName}' not found`);
          validationResults.summary.invalidGroups++;
        }
      }

      if (validationErrors.length > 0) {
        validationResults.invalidRows.push({
          row: rowNumber,
          errors: validationErrors,
          data: {
            itemName: itemName || '',
            uom: uom || '',
            groupName: groupName || ''
          }
        });
        validationResults.summary.invalidCount++;
      } else {
        validationResults.validRows.push({
          row: rowNumber,
          data: {
            itemName: String(itemName).trim(),
            uom: String(uom).trim(),
            groupName: groupName ? String(groupName).trim() : null,
            hsnCode: row['HSN Code'] || row['hsnCode'] || row['HSN_CODE'] || row['hsn_code'] || null,
            description: row['Description'] || row['description'] || row['DESCRIPTION'] || null,
            taxPercentage: taxPercentage,
            qcRequired: qcRequired,
            weighmentRequired: weighmentRequired
          }
        });
        validationResults.summary.validCount++;
      }
    }

    if (!confirmed) {
      return res.status(200).json({
        success: true,
        requiresConfirmation: true,
        message: `Please confirm upload of ${validationResults.summary.validCount} valid items. ${validationResults.summary.invalidCount} items have errors and will be skipped.`,
        validationSummary: validationResults.summary,
        invalidRowsPreview: validationResults.invalidRows.slice(0, 10),
        totalInvalidRows: validationResults.invalidRows.length
      });
    }

    const results = {
      created: [],
      errors: [],
      total: validationResults.summary.validCount,
      successCount: 0,
      errorCount: 0,
      skippedCount: validationResults.summary.invalidCount
    };

    for (const validRow of validationResults.validRows) {
      try {
        const { data: rowData, row: rowNumber } = validRow;
        
        let groupId = null;
        let finalGroupName = null;
        if (rowData.groupName) {
          groupId = groupMap.get(rowData.groupName.toLowerCase());
          finalGroupName = rowData.groupName;
        }

        const item_code = await generateItemCode();

        const result = await pool.query(
          `INSERT INTO procurement_itemmaster (
            item_code, item_name, uom, hsn_code,
            description, tax_percentage, group_id, group_name, org_id, created_by, 
            qc_required, weighment_required
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
          RETURNING id, item_code, item_name, group_id, group_name, tax_percentage, qc_required, weighment_required`,
          [
            item_code,
            rowData.itemName,
            rowData.uom,
            rowData.hsnCode,
            rowData.description,
            rowData.taxPercentage,
            groupId,
            finalGroupName,
            orgId,
            created_by || null,
            rowData.qcRequired,
            rowData.weighmentRequired
          ]
        );

        results.created.push({
          row: rowNumber,
          id: result.rows[0].id,
          item_code: result.rows[0].item_code,
          item_name: result.rows[0].item_name,
          group_id: result.rows[0].group_id,
          group_name: result.rows[0].group_name,
          tax_percentage: result.rows[0].tax_percentage,
          qc_required: result.rows[0].qc_required,
          weighment_required: result.rows[0].weighment_required
        });
        results.successCount++;

      } catch (err) {
        console.error(`Error processing row ${rowNumber}:`, err);
        results.errors.push({ 
          row: rowNumber, 
          message: err.message, 
          data: rowData 
        });
        results.errorCount++;
      }
    }

    let toastMessage = '';
    let toastType = 'success';
    
    if (results.successCount === results.total && results.skippedCount === 0) {
      toastMessage = `✅ Successfully uploaded all ${results.successCount} items!`;
      toastType = 'success';
    } else if (results.successCount > 0 && results.skippedCount > 0) {
      toastMessage = `⚠️ Upload complete: ${results.successCount} items created, ${results.skippedCount} invalid items skipped.`;
      toastType = 'warning';
    } else if (results.successCount === 0 && results.skippedCount > 0) {
      toastMessage = `❌ Upload failed: No valid items found. ${results.skippedCount} items had errors.`;
      toastType = 'error';
    } else if (results.successCount > 0 && results.errorCount > 0) {
      toastMessage = `⚠️ Partial upload: ${results.successCount} created, ${results.errorCount} failed, ${results.skippedCount} skipped.`;
      toastType = 'warning';
    } else {
      toastMessage = `✅ Upload completed: ${results.successCount} items created successfully!`;
      toastType = 'success';
    }

    res.status(200).json({
      success: results.successCount > 0,
      message: toastMessage,
      toastType: toastType,
      data: {
        created: results.created,
        errors: results.errors,
        total: results.total,
        successCount: results.successCount,
        errorCount: results.errorCount,
        skippedCount: results.skippedCount
      }
    });

  } catch (err) {
    console.error("Error in bulkUploadItems:", err);
    res.status(500).json({ 
      success: false, 
      message: "Failed to process bulk upload. Please try again.",
      error: err.message 
    });
  }
};

// ─────────────────────────────────────────────
// GET ALL ITEMS (with pagination)
// ─────────────────────────────────────────────
const getAllItems = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

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

    const { search = "", page = 1, limit = 10, groupId = "" } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    let countQuery = `
      SELECT COUNT(*) AS total
      FROM procurement_itemmaster
      WHERE is_deleted = false AND org_id = $1
    `;
    
    let dataQuery = `
      SELECT id, item_code, item_name, uom, hsn_code,
              description, tax_percentage, group_id, group_name,
              created_at, updated_at, created_by, updated_by, 
              qc_required, weighment_required
      FROM procurement_itemmaster
      WHERE is_deleted = false AND org_id = $1
    `;
    
    let queryParams = [orgId];
    let paramIndex = 2;
    
    if (search) {
      countQuery += ` AND (item_name ILIKE $${paramIndex} OR item_code ILIKE $${paramIndex} OR hsn_code ILIKE $${paramIndex})`;
      dataQuery += ` AND (item_name ILIKE $${paramIndex} OR item_code ILIKE $${paramIndex} OR hsn_code ILIKE $${paramIndex})`;
      queryParams.push(searchTerm);
      paramIndex++;
    }
    
    if (groupId) {
      countQuery += ` AND group_id = $${paramIndex}`;
      dataQuery += ` AND group_id = $${paramIndex}`;
      queryParams.push(parseInt(groupId));
      paramIndex++;
    }
    
    const countResult = await pool.query(countQuery, queryParams.slice(0, paramIndex - 1));
    const total = parseInt(countResult.rows[0].total);
    
    dataQuery += ` ORDER BY created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    queryParams.push(parseInt(limit), offset);
    
    const result = await pool.query(dataQuery, queryParams);

    const itemsWithGroups = result.rows.map(item => ({
      ...item,
      group_id: item.group_id || null,
      group_name: item.group_name || null,
      tax_percentage: item.tax_percentage || null,
      qc_required: item.qc_required || false,
      weighment_required: item.weighment_required || false
    }));

    res.json({
      success: true,
      data: itemsWithGroups,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalEntries: total,
        itemsPerPage: parseInt(limit),
      },
    });

  } catch (err) {
    console.error("Error in getAllItems:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET ALL ITEMS (no pagination)
// ─────────────────────────────────────────────
const getAllItemsWithoutPagination = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT id, item_code, item_name, uom, hsn_code, description, 
              tax_percentage, group_id, group_name, qc_required, weighment_required
       FROM procurement_itemmaster
       WHERE is_deleted = false AND org_id = $1
       ORDER BY item_name ASC`,
      [orgId]
    );

    const transformedData = result.rows.map(item => ({
      id: item.id,
      item_code: item.item_code,
      item_name: item.item_name,
      uom: item.uom || "",
      hsn_code: item.hsn_code,
      description: item.description || "",
      tax_percentage: item.tax_percentage || null,
      group_id: item.group_id || null,
      group_name: item.group_name || null,
      qc_required: item.qc_required || false,
      weighment_required: item.weighment_required || false
    }));

    res.json({ success: true, data: transformedData });

  } catch (err) {
    console.error("Error in getAllItemsWithoutPagination:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET ITEM BY ID
// ─────────────────────────────────────────────
const getItemById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT id, item_code, item_name, uom, hsn_code,
              description, tax_percentage, group_id, group_name,
              created_at, updated_at, created_by, updated_by, 
              qc_required, weighment_required
       FROM procurement_itemmaster
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    const itemData = {
      ...result.rows[0],
      group_id: result.rows[0].group_id || null,
      group_name: result.rows[0].group_name || null,
      qc_required: result.rows[0].qc_required || false,
      weighment_required: result.rows[0].weighment_required || false
    };

    res.json({ success: true, data: itemData });

  } catch (err) {
    console.error("Error in getItemById:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// UPDATE ITEM
// ─────────────────────────────────────────────
const updateItem = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId || req.user?.email;

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

    const {
      itemName,
      uom,
      hsnCode,
      description,
      taxPercentage,
      groupId,
      groupName,
      qcRequired,
      weighmentRequired
    } = req.body;

    if (!itemName || !uom) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: itemName and uom are required",
      });
    }

    const check = await pool.query(
      `SELECT id, group_id, group_name, qc_required, weighment_required FROM procurement_itemmaster
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    let finalGroupId = groupId || check.rows[0].group_id;
    let finalGroupName = groupName || check.rows[0].group_name;
    
    if (groupId) {
      const groupCheck = await pool.query(
        `SELECT group_name FROM group_master 
         WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [groupId, orgId]
      );
      if (groupCheck.rows.length > 0) {
        finalGroupName = groupCheck.rows[0].group_name;
      } else {
        finalGroupId = null;
        finalGroupName = null;
      }
    } else if (groupName) {
      const groupCheck = await pool.query(
        `SELECT id FROM group_master 
         WHERE group_name = $1 AND org_id = $2 AND is_deleted = false`,
        [groupName, orgId]
      );
      if (groupCheck.rows.length > 0) {
        finalGroupId = groupCheck.rows[0].id;
        finalGroupName = groupName;
      } else {
        finalGroupId = null;
        finalGroupName = null;
      }
    }

    let finalTaxPercentage = null;
    if (taxPercentage !== undefined && taxPercentage !== null && taxPercentage !== '') {
      const taxValue = parseFloat(taxPercentage);
      if (!isNaN(taxValue) && taxValue >= 0 && taxValue <= 100) {
        finalTaxPercentage = taxValue;
      }
    }

    const finalQcRequired = qcRequired !== undefined ? 
      (qcRequired === true || qcRequired === 'true') : 
      check.rows[0].qc_required;

    const finalWeighmentRequired = weighmentRequired !== undefined ?
      (weighmentRequired === true || weighmentRequired === 'true') :
      check.rows[0].weighment_required;

    const result = await pool.query(
      `UPDATE procurement_itemmaster SET
        item_name = $1,
        uom = $2,
        hsn_code = $3,
        description = $4,
        tax_percentage = $5,
        group_id = $6,
        group_name = $7,
        qc_required = $8,
        weighment_required = $9,
        updated_by = $10,
        updated_at = NOW()
       WHERE id = $11 AND org_id = $12 AND is_deleted = false
       RETURNING id, item_code, item_name, uom, hsn_code, description, 
                 tax_percentage, group_id, group_name, updated_at, 
                 qc_required, weighment_required`,
      [
        itemName,
        uom,
        hsnCode || null,
        description || null,
        finalTaxPercentage,
        finalGroupId,
        finalGroupName,
        finalQcRequired,
        finalWeighmentRequired,
        updated_by || null,
        parseInt(id),
        orgId,
      ]
    );

    const updatedItem = {
      ...result.rows[0],
      group_id: result.rows[0].group_id || null,
      group_name: result.rows[0].group_name || null,
      tax_percentage: result.rows[0].tax_percentage || null,
      qc_required: result.rows[0].qc_required || false,
      weighment_required: result.rows[0].weighment_required || false
    };

    res.json({ success: true, data: updatedItem });

  } catch (err) {
    console.error("Error in updateItem:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// SOFT DELETE ITEM
// ─────────────────────────────────────────────
const deleteItem = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId || req.user?.email;

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

    const result = await pool.query(
      `UPDATE procurement_itemmaster
       SET is_deleted = true,
           updated_by = $1,
           updated_at = NOW(),
           deleted_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, item_code, item_name, group_id, group_name, tax_percentage, 
                 qc_required, weighment_required`,
      [updated_by || null, parseInt(id), orgId]
    );

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

    res.json({ success: true, message: "Item deleted successfully", data: result.rows[0] });

  } catch (err) {
    console.error("Error in deleteItem:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// HARD DELETE ITEM
// ─────────────────────────────────────────────
const hardDeleteItem = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `DELETE FROM procurement_itemmaster
       WHERE id = $1 AND org_id = $2
       RETURNING id, item_code, item_name, group_id, group_name, 
                 qc_required, weighment_required`,
      [parseInt(id), orgId]
    );

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

    res.json({ success: true, message: "Item permanently deleted", data: result.rows[0] });

  } catch (err) {
    console.error("Error in hardDeleteItem:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  createItem,
  getAllItems,
  getAllItemsWithoutPagination,
  getItemById,
  updateItem,
  deleteItem,
  hardDeleteItem,
  bulkUploadItems,
  downloadTemplate,
  getItemGroups,  
};