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

// =====================================================
// HELPER: Generate PR Number
// =====================================================
const generatePRNumber = async () => {
  try {
    const result = await pool.query(
      `UPDATE public.prefixes 
       SET last_number = last_number + 1 
       WHERE module_name = 'pr' 
         AND status = 'ACTIVE' 
         AND is_deleted = false
       RETURNING prefix_code, last_number`
    );
    
    if (result.rows.length === 0) {
      throw new Error('No prefix configuration found for PR');
    }
    
    const prefix = result.rows[0].prefix_code;
    const nextNumber = result.rows[0].last_number;
    const formattedNumber = nextNumber.toString().padStart(4, '0');
    return `${prefix}-${formattedNumber}`;
  } catch (error) {
    console.error("Error generating PR number:", error);
    const timestamp = Date.now().toString().slice(-6);
    return `PR-${timestamp}`;
  }
};

// =====================================================
// HELPER: Get Requisition by ID (Internal)
// =====================================================
const getRequisitionByIdInternal = async (id, orgId) => {
  const result = await pool.query(
    `SELECT 
       r.*,
       r.requested_by_id,
       CASE 
         WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
           CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))
         ELSE 
           COALESCE(r.requested_by, 'N/A')
       END as requested_by_name,
       CASE 
         WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
           json_build_object('firstName', u.first_name, 'lastName', u.last_name, 'id', u.id)
         ELSE 
           NULL
       END as user,
       COALESCE(
         (SELECT json_agg(json_build_object(
           'id', ri.id,
           'item_code', ri.item_code,
           'item_name', ri.item_name,
           'description', ri.description,
           'quantity', ri.quantity,
           'quantity_po_raised', COALESCE(ri.quantity_po_raised, 0),
           'uom', ri.uom
         ) ORDER BY ri.id)
         FROM requisition_items ri 
         WHERE ri.requisition_id = r.id
       ), '[]'::json) as items
     FROM requisitions r
     LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
     WHERE r.id = $1 AND r.org_id = $2 AND r.is_deleted = false`,
    [parseInt(id), orgId]
  );

  return result.rows[0] || null;
};

// =====================================================
// GET ALL REQUISITIONS (paginated)
// =====================================================
const getAllRequisitions = 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,
      sortBy = "created_at",
      sortOrder = "DESC",
      filterByUserId = null,
      filterByDepartment = null,
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    // Scoping: non-admin users see only their own requests (filterByUserId)
    // Department users see their department's requests (filterByDepartment)
    // Admin: no extra filter (neither param sent)
    let scopeClause = "";
    const scopeParams = [];

    if (filterByUserId) {
      scopeClause = `AND r.requested_by_id = $3`;
      scopeParams.push(parseInt(filterByUserId));
    } else if (filterByDepartment) {
      scopeClause = `AND r.department ILIKE $3`;
      scopeParams.push(filterByDepartment);
    }

    const baseParamCount = 2 + scopeParams.length;

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total
       FROM requisitions r
       LEFT JOIN users u ON r.requested_by_id = u.id
       WHERE r.is_deleted = false AND r.org_id = $1
         ${scopeClause}
         AND (
           r.pr_number ILIKE $2 OR
           r.department ILIKE $2 OR
           COALESCE(u.first_name, '') || ' ' || COALESCE(u.last_name, '') ILIKE $2 OR
           r.requested_by ILIKE $2
         )`,
      [orgId, searchTerm, ...scopeParams]
    );

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

    const limitParam  = `$${baseParamCount + 1}`;
    const offsetParam = `$${baseParamCount + 2}`;
    const sortParam   = `$${baseParamCount + 3}`;

    const result = await pool.query(
      `SELECT 
         r.id,
         r.pr_number,
         r.pr_date,
         r.pr_due_date,
         r.department,
         r.requested_by,
         r.status,
         r.po_status,
         r.comment,
         r.created_at,
         r.updated_at,
         r.requested_by_id,
         CASE 
           WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
             CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))
           ELSE 
             COALESCE(r.requested_by, 'N/A')
         END as requested_by_name,
         CASE 
           WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
             json_build_object('firstName', u.first_name, 'lastName', u.last_name, 'id', u.id)
           ELSE 
             NULL
         END as user,
         COALESCE(
           (SELECT json_agg(json_build_object(
             'id', ri.id,
             'item_code', ri.item_code,
             'item_name', ri.item_name,
             'description', ri.description,
             'quantity', ri.quantity,
             'quantity_po_raised', COALESCE(ri.quantity_po_raised, 0),
             'uom', ri.uom
           ) ORDER BY ri.id)
           FROM requisition_items ri 
           WHERE ri.requisition_id = r.id
         ), '[]'::json) as items
       FROM requisitions r
       LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
       WHERE r.is_deleted = false AND r.org_id = $1
         ${scopeClause}
         AND (
           r.pr_number ILIKE $2 OR
           r.department ILIKE $2 OR
           COALESCE(u.first_name, '') || ' ' || COALESCE(u.last_name, '') ILIKE $2 OR
           r.requested_by ILIKE $2
         )
       ORDER BY 
         CASE WHEN ${sortParam} = 'id' THEN r.id::text END ${sortOrder},
         CASE WHEN ${sortParam} = 'pr_number' THEN r.pr_number END ${sortOrder},
         CASE WHEN ${sortParam} = 'pr_date' THEN r.pr_date::text END ${sortOrder},
         CASE WHEN ${sortParam} = 'pr_due_date' THEN r.pr_due_date::text END ${sortOrder},
         CASE WHEN ${sortParam} = 'department' THEN r.department END ${sortOrder},
         CASE WHEN ${sortParam} = 'requested_by' THEN COALESCE(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, '')), r.requested_by) END ${sortOrder},
         CASE WHEN ${sortParam} = 'status' THEN r.status END ${sortOrder},
         CASE WHEN ${sortParam} = 'po_status' THEN r.po_status END ${sortOrder},
         CASE WHEN ${sortParam} = 'created_at' THEN r.created_at::text END ${sortOrder}
       LIMIT ${limitParam} OFFSET ${offsetParam}`,
      [orgId, searchTerm, ...scopeParams, parseInt(limit), offset, sortBy]
    );

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

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

// =====================================================
// GET ALL REQUISITIONS (no pagination)
// =====================================================
const getAllRequisitionsWithoutPagination = 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 
         r.id,
         r.pr_number,
         r.pr_date,
         r.pr_due_date,
         r.department,
         r.requested_by,
         r.status,
         r.po_status,
         r.comment,
         r.created_at,
         r.updated_at,
         r.requested_by_id,
         CASE 
           WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
             CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))
           ELSE 
             COALESCE(r.requested_by, 'N/A')
         END as requested_by_name,
         CASE 
           WHEN r.requested_by_id IS NOT NULL AND u.id IS NOT NULL THEN 
             json_build_object('firstName', u.first_name, 'lastName', u.last_name, 'id', u.id)
           ELSE 
             NULL
         END as user,
         COALESCE(
           (SELECT json_agg(json_build_object(
             'item_code', ri.item_code,
             'item_name', ri.item_name,
             'description', ri.description,
             'quantity', ri.quantity,
             'quantity_po_raised', COALESCE(ri.quantity_po_raised, 0),
             'uom', ri.uom
           ))
           FROM requisition_items ri 
           WHERE ri.requisition_id = r.id
         ), '[]'::json) as items
       FROM requisitions r
       LEFT JOIN users u ON r.requested_by_id = u.id AND u.is_deleted = false
       WHERE r.is_deleted = false AND r.org_id = $1
       ORDER BY r.created_at DESC`,
      [orgId]
    );

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

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

// =====================================================
// GET REQUISITION BY ID OR PR NUMBER
// =====================================================
const getRequisitionById = 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 requisition = await getRequisitionByIdInternal(id, orgId);
    
    if (!requisition) {
      return res.status(404).json({ success: false, message: "Requisition not found" });
    }

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

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

// =====================================================
// CREATE REQUISITION
// =====================================================
const createRequisition = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const orgId = req.user?.orgId;
    const userId = req.user?.id || req.user?.userId;

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

    const {
      pr_date,
      pr_due_date,
      department,
      requested_by,
      comment,
      items = []
    } = req.body;

    if (!items || items.length === 0) {
      return res.status(400).json({ success: false, message: "At least one item is required" });
    }

    const pr_number = await generatePRNumber();
    
    let requestedByName = null;
    let requestedById = null;
    
    if (userId) {
      const userResult = await pool.query(
        `SELECT id, first_name, last_name FROM users WHERE id = $1 AND is_deleted = false`,
        [userId]
      );
      
      if (userResult.rows.length > 0) {
        requestedById = userResult.rows[0].id;
        requestedByName = `${userResult.rows[0].first_name} ${userResult.rows[0].last_name}`.trim();
      }
    }
    
    if (!requestedByName && requested_by) {
      requestedByName = requested_by;
    }

    await client.query('BEGIN');

    const requisitionResult = await client.query(
      `INSERT INTO requisitions (
        pr_number, pr_date, pr_due_date, department, requested_by,
        requested_by_id, status, po_status, comment, org_id, created_by, updated_by
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
      RETURNING *`,
      [
        pr_number,
        pr_date || new Date(),
        pr_due_date || null,
        department || null,
        requestedByName,
        requestedById,
        'Pending',
        'Open',
        comment || null,
        String(orgId),
        userId,
        userId
      ]
    );

    const requisition = requisitionResult.rows[0];

    for (const item of items) {
      await client.query(
        `INSERT INTO requisition_items (
          requisition_id, item_code, item_name, description, quantity, uom, quantity_po_raised
        ) VALUES ($1, $2, $3, $4, $5, $6, $7)`,
        [
          requisition.id,
          String(item.item_code),
          String(item.item_name),
          item.description || null,
          Number(item.quantity) || 0,
          item.uom || null,
          0
        ]
      );
    }

    await client.query('COMMIT');

    const completeRequisition = await getRequisitionByIdInternal(requisition.id, String(orgId));

    res.status(201).json({
      success: true,
      message: "Requisition created successfully",
      data: completeRequisition
    });

  } catch (err) {
    await client.query('ROLLBACK');
    console.error("Error in createRequisition:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// =====================================================
// UPDATE REQUISITION STATUS
// =====================================================
const updateRequisitionStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status, comment } = req.body;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.id || req.user?.email;
    
    const approvedByName = req.user?.name || 
      `${req.user?.firstName || ''} ${req.user?.lastName || ''}`.trim() || 
      updated_by;

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

    const validStatuses = ['Pending', 'Approved', 'Rejected', 'Blocked', 'Partially Issued', 'Fully Issued'];
    
    if (!validStatuses.includes(status)) {
      return res.status(400).json({ success: false, message: "Invalid status" });
    }

    const result = await pool.query(
      `UPDATE requisitions 
       SET status = $1,
           comment = COALESCE($2, comment),
           updated_by = $3,
           updated_at = NOW()
       WHERE id = $4 AND org_id = $5 AND is_deleted = false
       RETURNING *`,
      [status, comment, updated_by, parseInt(id), orgId]
    );

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

    if (status === "Approved") {
      try {
        const fullRequisition = await getRequisitionByIdInternal(id, orgId);
        if (fullRequisition) {
          emitPRApproval(fullRequisition, approvedByName, req.user?.id);
          console.log(`📢 Notification sent for PR: ${fullRequisition.pr_number}`);
        }
      } catch (notifyErr) {
        console.error("Failed to send notification:", notifyErr);
      }
    }

    res.json({
      success: true,
      message: `Requisition ${status.toLowerCase()} successfully`,
      data: {
        requisition: result.rows[0]
      }
    });

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

// =====================================================
// UPDATE REQUISITION COMMENT
// =====================================================
const updateRequisitionComment = async (req, res) => {
  try {
    const { id } = req.params;
    const { comment } = req.body;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.id;

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

    const result = await pool.query(
      `UPDATE requisitions 
       SET comment = $1, updated_by = $2, updated_at = NOW()
       WHERE id = $3 AND org_id = $4 AND is_deleted = false
       RETURNING *`,
      [comment, updated_by, parseInt(id), orgId]
    );

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

    res.json({ success: true, message: "Comment updated successfully", data: result.rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, message: err.message });
  }
};

// =====================================================
// UPDATE REQUISITION ITEMS (Bulk update with deletion)
// =====================================================
const updateRequisitionItems = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const { items } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id || req.user?.userId;

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

    // Check if requisition exists and is still Pending
    const checkResult = await client.query(
      `SELECT id, status FROM requisitions 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [parseInt(id), orgId]
    );

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

    const requisitionStatus = checkResult.rows[0].status;
    if (requisitionStatus !== "Pending") {
      return res.status(400).json({ 
        success: false, 
        message: `Cannot edit requisition with status: ${requisitionStatus}. Only Pending requisitions can be edited.` 
      });
    }

    await client.query('BEGIN');

    // Get existing item IDs from database
    const existingItemsResult = await client.query(
      `SELECT id FROM requisition_items WHERE requisition_id = $1`,
      [parseInt(id)]
    );
    const existingItemIds = existingItemsResult.rows.map(row => row.id);
    
    // Get new item IDs from request (only those with existing IDs)
    const newItemIds = items.filter(item => item.id).map(item => item.id);
    
    // Find IDs to delete (exist in DB but not in request)
    const idsToDelete = existingItemIds.filter(id => !newItemIds.includes(id));
    
    // Delete removed items
    if (idsToDelete.length > 0) {
      await client.query(
        `DELETE FROM requisition_items 
         WHERE id = ANY($1::int[]) AND requisition_id = $2`,
        [idsToDelete, parseInt(id)]
      );
      console.log(`Deleted ${idsToDelete.length} items from requisition ${id}`);
    }

    // Update or insert items
    for (const item of items) {
      if (item.id) {
        // Update existing item
        await client.query(
          `UPDATE requisition_items 
           SET item_code = $1, 
               item_name = $2, 
               description = $3, 
               quantity = $4, 
               uom = $5, 
               updated_at = NOW()
           WHERE id = $6 AND requisition_id = $7`,
          [
            item.item_code,
            item.item_name,
            item.description || null,
            parseFloat(item.quantity) || 0,
            item.uom || null,
            item.id,
            parseInt(id)
          ]
        );
      } else {
        // Insert new item
        await client.query(
          `INSERT INTO requisition_items (
            requisition_id, item_code, item_name, description, quantity, uom, quantity_po_raised
          ) VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [
            parseInt(id),
            item.item_code,
            item.item_name,
            item.description || null,
            parseFloat(item.quantity) || 0,
            item.uom || null,
            0
          ]
        );
      }
    }

    // Update requisition updated_at timestamp
    await client.query(
      `UPDATE requisitions SET updated_at = NOW(), updated_by = $1 WHERE id = $2`,
      [userId, parseInt(id)]
    );

    await client.query('COMMIT');

    const updatedRequisition = await getRequisitionByIdInternal(id, orgId);

    res.json({
      success: true,
      message: "Requisition items updated successfully",
      data: updatedRequisition
    });

  } catch (err) {
    await client.query('ROLLBACK');
    console.error("Error in updateRequisitionItems:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// =====================================================
// SOFT DELETE REQUISITION
// =====================================================
const deleteRequisition = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.id;

    const result = await pool.query(
      `UPDATE requisitions
       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, pr_number`,
      [updated_by, parseInt(id), orgId]
    );

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

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

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

// =====================================================
// HARD DELETE REQUISITION
// =====================================================
const hardDeleteRequisition = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

    await client.query('BEGIN');
    await client.query('DELETE FROM requisition_items WHERE requisition_id = $1', [parseInt(id)]);
    const result = await client.query(
      `DELETE FROM requisitions WHERE id = $1 AND org_id = $2 RETURNING id, pr_number`,
      [parseInt(id), orgId]
    );

    if (result.rowCount === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ success: false, message: "Requisition not found" });
    }

    await client.query('COMMIT');
    res.json({ success: true, message: "Requisition permanently deleted", data: result.rows[0] });

  } catch (err) {
    await client.query('ROLLBACK');
    console.error("Error in hardDeleteRequisition:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// =====================================================
// SEND QUOTATION REQUEST
// =====================================================
const sendQuotationRequest = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

    const requisition = await getRequisitionByIdInternal(id, orgId);

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

    await pool.query(
      `UPDATE requisitions 
       SET comment = COALESCE(comment, '') || '\nQuotation request sent on ' || NOW(),
           updated_at = NOW()
       WHERE id = $1 AND org_id = $2`,
      [parseInt(id), orgId]
    );

    res.json({
      success: true,
      message: "Quotation request sent successfully",
      data: requisition
    });

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

const getPRItemPOStatus = async (req, res) => {
  try {
    const { pr_id } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });
    const orgIdStr = String(orgId);
 
    const result = await pool.query(
      `SELECT
         MIN(pi.id) AS id,
         pi.requisition_item_id,
         pi.item_code,
         pi.item_name,
         COALESCE(ri.description, '') AS description,
         SUM(pi.requested_quantity) AS total_quantity,
         SUM(pi.remaining_quantity) AS remaining_quantity,
         COALESCE(ri.uom, '') AS uom,
         SUM(COALESCE(pi.issued_quantity, 0)) AS already_raised_quantity,
         SUM(COALESCE(pi.issued_quantity, 0)) AS issued_quantity,
         MAX(pi.issued_to_name) AS issued_to_name,
         MAX(pi.issued_at) AS issued_at,
         SUM(COALESCE(pi.po_requested_quantity, 0)) AS po_requested_quantity,
         CASE
           WHEN bool_or(pi.status = 'PO Raised') THEN 'PO Raised'
           WHEN bool_or(pi.status = 'PO Requested') THEN 'PO Requested'
           ELSE MAX(pi.status)
         END AS inventory_status
       FROM pr_inventory pi
       INNER JOIN requisitions r ON pi.requisition_id = r.id
       LEFT JOIN requisition_items ri ON ri.id = pi.requisition_item_id
       WHERE r.id = $1
         AND r.org_id = $2
         AND r.is_deleted = false
       GROUP BY
         pi.requisition_item_id,
         pi.item_code,
         pi.item_name,
         ri.description,
         ri.uom
       ORDER BY MIN(pi.id) ASC`,
      [parseInt(pr_id), orgIdStr]
    );
 
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("Error in getPRItemPOStatus:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};



// =====================================================
// APPROVE PR WITH STOCK ENTRY (Pushes to pr_inventory)
// POST /api/procurement/requisitions/:id/approve-with-stock
// =====================================================
const approveRequisitionWithStock = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const { items, comment } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id || req.user?.userId;
    const approvedByName = req.user?.name || 
      `${req.user?.firstName || ''} ${req.user?.lastName || ''}`.trim() || 
      userId;

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

    // Get requisition details
    const requisition = await getRequisitionByIdInternal(id, orgId);
    if (!requisition) {
      return res.status(404).json({ success: false, message: "Requisition not found" });
    }

    if (requisition.status === "Approved") {
      return res.status(400).json({ success: false, message: "Requisition already approved" });
    }

    await client.query("BEGIN");

    // 1. Update requisition status to Approved
    await client.query(
      `UPDATE requisitions 
       SET status = 'Approved', 
           comment = COALESCE($1, comment),
           updated_by = $2, 
           updated_at = NOW()
       WHERE id = $3 AND org_id = $4 AND is_deleted = false`,
      [comment, userId, parseInt(id), orgId]
    );

    // 2. Update requisition items quantities if changed
    for (const item of items) {
      await client.query(
        `UPDATE requisition_items 
         SET quantity = $1, updated_at = NOW()
         WHERE id = $2 AND requisition_id = $3`,
        [item.requested_quantity, item.requisition_item_id, parseInt(id)]
      );
    }

    // 3. Insert into pr_inventory table (without available_stock)
    for (const item of items) {
      await client.query(
        `INSERT INTO pr_inventory (
          requisition_id, requisition_item_id, item_code, item_name,
          requested_quantity, remaining_quantity, status, org_id, created_by, updated_by
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`,
        [
          parseInt(id),
          item.requisition_item_id,
          item.item_code,
          item.item_name,
          item.requested_quantity,
          item.requested_quantity,  // remaining_quantity starts as requested
          'Pending',
          orgId,
          userId,
          userId
        ]
      );
    }

    await client.query("COMMIT");

    // Send notification
    try {
      const fullRequisition = await getRequisitionByIdInternal(id, orgId);
      if (fullRequisition) {
        emitPRApproval(fullRequisition, approvedByName, req.user?.id);
        console.log(`📢 Approval notification sent for PR: ${fullRequisition.pr_number}`);
      }
    } catch (notifyErr) {
      console.error("Failed to send notification:", notifyErr);
    }

    res.json({
      success: true,
      message: `Requisition ${requisition.pr_number} approved successfully and moved to PR Inventory`,
      data: { requisition_id: parseInt(id), pr_number: requisition.pr_number }
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in approveRequisitionWithStock:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

const getApprovedPRsForInventory = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    const result = await pool.query(
      `SELECT
         r.id,
         r.pr_number,
         r.pr_date,
         r.department,
         r.status   AS requisition_status,
         r.po_status,
         COALESCE(
           (SELECT json_agg(row ORDER BY row_id) FROM (
              SELECT
                MIN(pi.id)                                         AS row_id,
                pi.requisition_item_id,
                pi.item_code,
                pi.item_name,

                -- ✅ Use the FIRST row's requested_quantity (not SUM)
                (SELECT pi2.requested_quantity
                 FROM pr_inventory pi2
                 WHERE pi2.requisition_item_id = pi.requisition_item_id
                   AND pi2.requisition_id = r.id
                 ORDER BY pi2.id ASC
                 LIMIT 1)                                          AS requested_quantity,

                SUM(COALESCE(pi.issued_quantity, 0))               AS issued_quantity,

                -- ✅ Use the FIRST row's remaining_quantity (not SUM)
                (SELECT pi2.remaining_quantity
                 FROM pr_inventory pi2
                 WHERE pi2.requisition_item_id = pi.requisition_item_id
                   AND pi2.requisition_id = r.id
                 ORDER BY pi2.id ASC
                 LIMIT 1)                                          AS remaining_quantity,

                MAX(pi.status)                                     AS status,
                MAX(pi.issued_to_name)                             AS issued_to_name,
                MAX(pi.issued_to_id)                               AS issued_to_id,
                MAX(pi.issued_at)                                  AS issued_at,
                COALESCE(ri.uom, '')                               AS uom,
                -- ✅ ADD consumption_status
                COALESCE(pi.consumption_status, 'Not Received')    AS consumption_status,
                COALESCE(
                  (SELECT SUM(s.net_quantity)
                   FROM stocks s
                   WHERE s.material_name ILIKE '%' || pi.item_name || '%'
                     AND s.org_id        = r.org_id
                     AND s.is_deleted    = false
                     AND s.net_quantity  > 0), 0
                ) AS available_stock
              FROM pr_inventory pi
              JOIN requisition_items ri ON ri.id = pi.requisition_item_id
              WHERE pi.requisition_id = r.id
              GROUP BY pi.requisition_item_id, pi.item_code, pi.item_name, ri.uom, pi.consumption_status
           ) row),
           '[]'::json
         ) AS inventory_items
       FROM requisitions r
       WHERE r.org_id   = $1
         AND r.status   IN ('Approved','Partially Issued','Fully Issued')
         AND r.is_deleted = false
         AND EXISTS (SELECT 1 FROM pr_inventory pi WHERE pi.requisition_id = r.id)
       ORDER BY r.created_at DESC`,
      [orgId]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("Error in getApprovedPRsForInventory:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// =====================================================
// FIXED: issueInventoryFromPR
// POST /api/procurement/requisitions/:id/issue-inventory
//
// KEY FIXES:
//  1. Guards against issuing PO Raised items (backend protection)
//  2. Preserves 'PO Requested' status when issuing from stock
//     against an item that still has remainder going to PO
//  3. Updates po_requested_quantity correctly after stock issue
//  4. Correct requisition-level status rollup (respects PO Requested)
// =====================================================
const issueInventoryFromPR = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id: requisitionId }           = req.params;
    const { items, assigned_to_id, notes } = req.body;
    const orgId  = req.user?.orgId;
    const userId = req.user?.id;

    if (!orgId)
      return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!items || items.length === 0)
      return res.status(400).json({ success: false, message: "No items to issue" });
    if (!assigned_to_id)
      return res.status(400).json({ success: false, message: "Please select who this inventory is being issued to" });

    await client.query("BEGIN");

    let totalIssued   = 0;
    const issueRecords = [];

    for (const item of items) {
      if (!(item.issued_quantity > 0)) continue;

      // ── Fetch the canonical pr_inventory row ────────────────────────────
      const currentRecord = await client.query(
        `SELECT id, remaining_quantity, requested_quantity, issued_quantity,
                po_requested_quantity, status, item_name, item_code
         FROM pr_inventory
         WHERE requisition_item_id = $1
           AND requisition_id      = $2
         ORDER BY id ASC
         LIMIT 1`,
        [item.requisition_item_id, requisitionId]
      );

      if (currentRecord.rows.length === 0) {
        throw new Error(`Item ${item.requisition_item_id} not found in pr_inventory`);
      }

      const current = currentRecord.rows[0];

      // No status blocks issuing — PO Requested, PO Raised, Partial all still issuable
      // as long as stock is available and remaining > 0.
      const issueQty       = parseFloat(item.issued_quantity);
      const prevIssued     = parseFloat(current.issued_quantity)      || 0;
      const prevRemaining  = parseFloat(current.remaining_quantity)   || 0;
      const prevPOQty      = parseFloat(current.po_requested_quantity) || 0;

      // Validate issue quantity doesn't exceed remaining
      if (issueQty > prevRemaining) {
        throw new Error(
          `Cannot issue ${issueQty} of "${current.item_name}" — only ${prevRemaining} remaining`
        );
      }

      const newIssuedQty   = prevIssued    + issueQty;
      const newRemainingQty = prevRemaining - issueQty;

      // ── Status logic: preserve PO statuses, only override on full issue ────
      // Rules:
      //   remaining = 0                    → Fully Issued  (done regardless of prior status)
      //   remaining > 0, was PO Requested  → PO Requested  (PO still needed for remainder)
      //   remaining > 0, was PO Raised     → PO Raised     (PO raised, partial stock issued)
      //   remaining > 0, otherwise         → Partially Issued
      let newStatus;
      if (newRemainingQty <= 0) {
        newStatus = 'Fully Issued';
      } else if (current.status === 'PO Requested') {
        newStatus = 'PO Requested'; // keep — PO still needed for remainder
      } else if (current.status === 'PO Raised') {
        newStatus = 'PO Raised';    // keep — PO is live, we just issued some stock too
      } else {
        newStatus = newIssuedQty > 0 ? 'Partially Issued' : 'Pending';
      }

      // Reduce po_requested_quantity by whatever we just issued from stock.
      // This keeps the "how much the PO still needs to cover" accurate.
      let newPORequestedQty = prevPOQty;
      if ((current.status === 'PO Requested' || current.status === 'PO Raised') && prevPOQty > 0) {
        newPORequestedQty = Math.max(0, prevPOQty - issueQty);
      }
      if (newRemainingQty <= 0) {
        newPORequestedQty = 0;
      }

      // ── Update pr_inventory ─────────────────────────────────────────────
      await client.query(
        `UPDATE pr_inventory
         SET issued_quantity       = $1,
             remaining_quantity    = $2,
             po_requested_quantity = $3,
             status                = $4,
             issued_to_id          = $5,
             issued_to_name        = (SELECT CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,''))
                                      FROM users WHERE id = $5),
             issued_at             = NOW(),
             updated_at            = NOW(),
             updated_by            = $6
         WHERE id = $7`,
        [
          newIssuedQty,
          newRemainingQty,
          newPORequestedQty,
          newStatus,
          assigned_to_id,
          userId,
          current.id,
        ]
      );

      // ── FIFO stock deduction ────────────────────────────────────────────
      const stockEntries = await client.query(
        `SELECT id, net_quantity, received_date
         FROM stocks
         WHERE material_name ILIKE $1
           AND is_deleted = false
           AND org_id     = $2
           AND net_quantity > 0
         ORDER BY received_date ASC`,
        [`%${current.item_name}%`, orgId]
      );

      let remainingToDeduct = issueQty;
      for (const stockEntry of stockEntries.rows) {
        if (remainingToDeduct <= 0) break;
        const currentQty = parseFloat(stockEntry.net_quantity);
        const deductAmt  = Math.min(remainingToDeduct, currentQty);
        const newQty     = currentQty - deductAmt;

        if (newQty <= 0) {
          await client.query(`DELETE FROM stocks WHERE id = $1`, [stockEntry.id]);
        } else {
          await client.query(
            `UPDATE stocks SET net_quantity = $1, updated_at = NOW(), updated_by = $2 WHERE id = $3`,
            [newQty, userId, stockEntry.id]
          );
        }
        remainingToDeduct -= deductAmt;
        totalIssued       += deductAmt;
      }

      issueRecords.push({
        item_name:          current.item_name,
        issued:             issueQty,
        new_remaining:      newRemainingQty,
        status:             newStatus,
        po_requested_qty:   newPORequestedQty,
      });
    }

    // ── FIX 4: Correct overall requisition status rollup ───────────────────
    // An item with PO Requested status is NOT fully resolved — don't treat it
    // as "fully issued" for the requisition rollup. Only Fully Issued counts.
    const inventoryItems = await client.query(
      `SELECT status, remaining_quantity FROM pr_inventory WHERE requisition_id = $1`,
      [requisitionId]
    );

    const rows = inventoryItems.rows;

    const allFullyIssued = rows.every(
      r => r.status === 'Fully Issued' || parseFloat(r.remaining_quantity) <= 0
    );
    const allPending = rows.every(
      r => (r.status === 'Pending') && parseFloat(r.remaining_quantity) > 0
    );
    // If any item is PO Requested or PO Raised → requisition is "In Progress"
    // We use 'Partially Issued' as the in-progress label (matches existing enum)
    const requisitionInventoryStatus = allFullyIssued
      ? 'Fully Issued'
      : allPending
        ? 'Pending'
        : 'Partially Issued';

    await client.query(
      `UPDATE requisitions SET status = $1, updated_at = NOW() WHERE id = $2`,
      [requisitionInventoryStatus, requisitionId]
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `${totalIssued} unit(s) issued successfully from inventory`,
      data: {
        total_issued:       totalIssued,
        requisition_status: requisitionInventoryStatus,
        items:              issueRecords,
      },
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in issueInventoryFromPR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

const updatePRInventoryConsumptionStatus = async (req, res) => {
  try {
    const { requisitionId } = req.params;
    const { requisition_item_id, received_quantity } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

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

    // Get current inventory record
    const inventoryRecord = await pool.query(
      `SELECT id, requested_quantity, issued_quantity, remaining_quantity, consumption_status
       FROM pr_inventory
       WHERE requisition_id = $1 AND requisition_item_id = $2`,
      [requisitionId, requisition_item_id]
    );

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

    const record = inventoryRecord.rows[0];
    const totalReceived = (parseFloat(record.issued_quantity) || 0);
    const totalRequested = parseFloat(record.requested_quantity) || 0;
    
    let newConsumptionStatus = 'Not Received';
    if (totalReceived >= totalRequested) {
      newConsumptionStatus = 'Fully Received';
    } else if (totalReceived > 0 && totalReceived < totalRequested) {
      newConsumptionStatus = 'Partially Received';
    }

    // Update consumption status
    await pool.query(
      `UPDATE pr_inventory 
       SET consumption_status = $1, updated_at = NOW(), updated_by = $2
       WHERE requisition_id = $3 AND requisition_item_id = $4`,
      [newConsumptionStatus, userId, requisitionId, requisition_item_id]
    );

    res.json({
      success: true,
      message: "Consumption status updated successfully",
      data: { consumption_status: newConsumptionStatus }
    });

  } catch (error) {
    console.error("Error updating consumption status:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};
// const getIssuedInventoryDetails = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const { id: requisitionId } = req.params;
//     const orgId = req.user?.orgId;

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

//     // Fetch all pr_inventory items with issued quantities and related details
//     const inventoryItems = await client.query(
//       `SELECT 
//         pi.id,
//         pi.requisition_item_id,
//         pi.item_code,
//         pi.item_name,
//         pi.requested_quantity,
//         pi.issued_quantity,
//         pi.remaining_quantity,
//         pi.po_requested_quantity,
//         pi.status,
//         pi.issued_to_id,
//         pi.issued_to_name,
//         pi.issued_at,
//         pi.created_at,
//         pi.updated_at,
//         pi.created_by,
//         pi.updated_by,
//         COALESCE(
//           (SELECT SUM(issued_quantity) 
//            FROM pr_inventory 
//            WHERE requisition_item_id = pi.requisition_item_id 
//              AND requisition_id = pi.requisition_id
//            GROUP BY requisition_item_id), 
//           0
//         ) AS total_issued_for_item,
//         (pi.requested_quantity - COALESCE(pi.issued_quantity, 0)) AS remaining_to_issue,
//         CASE 
//           WHEN pi.remaining_quantity <= 0 THEN 'Fully Issued'
//           WHEN pi.issued_quantity > 0 AND pi.remaining_quantity > 0 THEN 'Partially Issued'
//           WHEN pi.po_requested_quantity > 0 THEN 'PO Requested'
//           WHEN pi.issued_quantity = 0 OR pi.issued_quantity IS NULL THEN 'Pending'
//           ELSE pi.status
//         END AS current_status,
//         u.first_name || ' ' || u.last_name AS issued_to_user_name
//       FROM pr_inventory pi
//       LEFT JOIN users u ON u.id = pi.issued_to_id
//       WHERE pi.requisition_id = $1
//         -- ✅ REMOVED: AND pi.is_deleted = false (this column doesn't exist)
//       ORDER BY pi.created_at DESC`,
//       [requisitionId]
//     );

//     if (inventoryItems.rows.length === 0) {
//       return res.status(404).json({ 
//         success: false, 
//         message: "No inventory items found for this requisition" 
//       });
//     }

//     // Calculate summary statistics
//     const summary = {
//       total_items: inventoryItems.rows.length,
//       total_requested_quantity: 0,
//       total_issued_quantity: 0,
//       total_remaining_quantity: 0,
//       total_po_requested_quantity: 0,
//       items_fully_issued: 0,
//       items_partially_issued: 0,
//       items_pending: 0,
//       items_po_requested: 0
//     };

//     inventoryItems.rows.forEach(item => {
//       summary.total_requested_quantity += parseFloat(item.requested_quantity) || 0;
//       summary.total_issued_quantity += parseFloat(item.issued_quantity) || 0;
//       summary.total_remaining_quantity += parseFloat(item.remaining_quantity) || 0;
//       summary.total_po_requested_quantity += parseFloat(item.po_requested_quantity) || 0;

//       switch (item.current_status) {
//         case 'Fully Issued':
//           summary.items_fully_issued++;
//           break;
//         case 'Partially Issued':
//           summary.items_partially_issued++;
//           break;
//         case 'PO Requested':
//           summary.items_po_requested++;
//           break;
//         case 'Pending':
//           summary.items_pending++;
//           break;
//       }
//     });

//     // Get requisition basic info
//     const requisitionInfo = await client.query(
//       `SELECT 
//         r.pr_number,
//         r.status AS requisition_status,
//         r.department,
//         r.requested_by,
//         ru.first_name || ' ' || ru.last_name AS requested_by_name,
//         r.created_at
//       FROM requisitions r
//       LEFT JOIN users ru ON ru.id = r.requested_by_id
//       WHERE r.id = $1`,
//       [requisitionId]
//     );

//     res.json({
//       success: true,
//       data: {
//         requisition: requisitionInfo.rows[0] || {},
//         issued_items: inventoryItems.rows,
//         summary: summary,
//         issued_total_count: inventoryItems.rows.filter(i => i.issued_quantity > 0).length
//       }
//     });

//   } catch (err) {
//     console.error("Error in getIssuedInventoryDetails:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };

// =====================================================
// NEW: APPROVE PR FOR PO (From Inventory Page)
// POST /api/procurement/requisitions/:id/approve-for-po
// =====================================================
// =====================================================
// GET ISSUED INVENTORY DETAILS FOR A REQUISITION
// GET /api/procurement/requisitions/:id/issued-inventory
// =====================================================
const getIssuedInventoryDetails = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id: requisitionId } = req.params;
    const orgId = req.user?.orgId;

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

    // Fetch all pr_inventory items with issued quantities and related details
    const inventoryItems = await client.query(
      `SELECT 
        pi.id,
        pi.requisition_item_id,
        pi.item_code,
        pi.item_name,
        pi.requested_quantity,
        pi.issued_quantity,
        pi.remaining_quantity,
        pi.po_requested_quantity,
        pi.status,
        COALESCE(pi.consumption_status, 'Not Received') AS consumption_status,
        pi.issued_to_id,
        pi.issued_to_name,
        pi.issued_at,
        pi.created_at,
        pi.updated_at,
        pi.created_by,
        pi.updated_by,
        (pi.requested_quantity - COALESCE(pi.issued_quantity, 0)) AS remaining_to_issue,
        CASE 
          WHEN pi.remaining_quantity <= 0 THEN 'Fully Issued'
          WHEN pi.issued_quantity > 0 AND pi.remaining_quantity > 0 THEN 'Partially Issued'
          WHEN pi.po_requested_quantity > 0 THEN 'PO Requested'
          WHEN pi.issued_quantity = 0 OR pi.issued_quantity IS NULL THEN 'Pending'
          ELSE pi.status
        END AS current_status,
        u.first_name || ' ' || u.last_name AS issued_to_user_name
      FROM pr_inventory pi
      LEFT JOIN users u ON u.id = pi.issued_to_id
      WHERE pi.requisition_id = $1
      ORDER BY pi.created_at DESC`,
      [requisitionId]
    );

    if (inventoryItems.rows.length === 0) {
      return res.status(404).json({ 
        success: false, 
        message: "No inventory items found for this requisition" 
      });
    }

    // Calculate summary statistics
    const summary = {
      total_items: inventoryItems.rows.length,
      total_requested_quantity: 0,
      total_issued_quantity: 0,
      total_remaining_quantity: 0,
      total_po_requested_quantity: 0,
      items_fully_issued: 0,
      items_partially_issued: 0,
      items_pending: 0,
      items_po_requested: 0,
      items_fully_received: 0,
      items_partially_received: 0,
      items_not_received: 0
    };

    inventoryItems.rows.forEach(item => {
      summary.total_requested_quantity += parseFloat(item.requested_quantity) || 0;
      summary.total_issued_quantity += parseFloat(item.issued_quantity) || 0;
      summary.total_remaining_quantity += parseFloat(item.remaining_quantity) || 0;
      summary.total_po_requested_quantity += parseFloat(item.po_requested_quantity) || 0;

      switch (item.current_status) {
        case 'Fully Issued':
          summary.items_fully_issued++;
          break;
        case 'Partially Issued':
          summary.items_partially_issued++;
          break;
        case 'PO Requested':
          summary.items_po_requested++;
          break;
        case 'Pending':
          summary.items_pending++;
          break;
      }
      
      switch (item.consumption_status) {
        case 'Fully Received':
          summary.items_fully_received++;
          break;
        case 'Partially Received':
          summary.items_partially_received++;
          break;
        default:
          summary.items_not_received++;
          break;
      }
    });

    // Get requisition basic info
    const requisitionInfo = await client.query(
      `SELECT 
        r.pr_number,
        r.status AS requisition_status,
        r.department,
        r.requested_by,
        ru.first_name || ' ' || ru.last_name AS requested_by_name,
        r.created_at
      FROM requisitions r
      LEFT JOIN users ru ON ru.id = r.requested_by_id
      WHERE r.id = $1`,
      [requisitionId]
    );

    res.json({
      success: true,
      data: {
        requisition: requisitionInfo.rows[0] || {},
        issued_items: inventoryItems.rows,
        summary: summary,
        issued_total_count: inventoryItems.rows.filter(i => i.issued_quantity > 0).length
      }
    });

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

const approvePRForPO = async (req, res) => {
  try {
    const { id: requisitionId } = req.params;
    const orgId = req.user?.orgId;

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

    // Get items that still need PO (remaining_quantity > 0)
    const remainingItems = await pool.query(
      `SELECT pi.*, ri.uom 
       FROM pr_inventory pi
       JOIN requisition_items ri ON ri.id = pi.requisition_item_id
       WHERE pi.requisition_id = $1 AND pi.remaining_quantity > 0`,
      [requisitionId]
    );

    if (remainingItems.rows.length === 0) {
      return res.status(400).json({ 
        success: false, 
        message: "All items have been issued from inventory. No items need PO." 
      });
    }

    // Update pr_inventory status for items going to PO
    for (const item of remainingItems.rows) {
      await pool.query(
        `UPDATE pr_inventory 
         SET status = 'PO Raised', updated_at = NOW()
         WHERE id = $1`,
        [item.id]
      );
    }

    // Update requisition po_status
    await pool.query(
      `UPDATE requisitions 
       SET po_status = 'Partially Raised', updated_at = NOW()
       WHERE id = $1`,
      [requisitionId]
    );

    res.json({ 
      success: true, 
      message: `${remainingItems.rows.length} items ready for PO`,
      data: { remainingItems: remainingItems.rows }
    });
  } catch (err) {
    console.error("Error in approvePRForPO:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// =====================================================
// GET INVENTORY ISSUES FOR A REQUISITION (Legacy)
// =====================================================
const getIssueHistory = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT ii.*, 
        COALESCE(
          (SELECT json_agg(json_build_object(
            'id', iii.id,
            'item_code', iii.item_code,
            'item_name', iii.item_name,
            'requested_quantity', iii.requested_quantity,
            'issued_quantity', iii.issued_quantity,
            'remaining_to_po', iii.remaining_to_po,
            'uom', iii.uom
          ))
          FROM inventory_issue_items iii
          WHERE iii.inventory_issue_id = ii.id
        ), '[]'::json) as items
       FROM inventory_issues ii
       WHERE ii.requisition_id = $1
       ORDER BY ii.created_at DESC`,
      [parseInt(id)]
    );

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

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

// =====================================================
// GET ALL INVENTORY ISSUES (Legacy)
// =====================================================
const getAllInventoryIssues = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const { page = 1, limit = 10, search = "" } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

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

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total
       FROM inventory_issues ii
       JOIN requisitions r ON ii.requisition_id = r.id
       WHERE r.org_id = $1 AND (ii.pr_number ILIKE $2 OR ii.status ILIKE $2 OR ii.assigned_to_name ILIKE $2)`,
      [orgId, searchTerm]
    );

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

    const result = await pool.query(
      `SELECT ii.*,
        COALESCE(
          (SELECT json_agg(json_build_object(
            'id', iii.id,
            'item_code', iii.item_code,
            'item_name', iii.item_name,
            'requested_quantity', iii.requested_quantity,
            'issued_quantity', iii.issued_quantity,
            'remaining_to_po', iii.remaining_to_po,
            'uom', iii.uom
          ))
          FROM inventory_issue_items iii
          WHERE iii.inventory_issue_id = ii.id
        ), '[]'::json) as items
       FROM inventory_issues ii
       JOIN requisitions r ON ii.requisition_id = r.id
       WHERE r.org_id = $1 AND (ii.pr_number ILIKE $2 OR ii.status ILIKE $2 OR ii.assigned_to_name ILIKE $2)
       ORDER BY ii.created_at DESC
       LIMIT $3 OFFSET $4`,
      [orgId, searchTerm, parseInt(limit), offset]
    );

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

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

// =====================================================
// GET STOCK AVAILABILITY FOR PR
// =====================================================
const getStockAvailabilityForPR = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

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

    const requisition = await getRequisitionByIdInternal(id, orgId);
    
    if (!requisition) {
      return res.status(404).json({ success: false, message: "Requisition not found" });
    }

    const requisitionItems = requisition.items || [];

    if (requisitionItems.length === 0) {
      return res.status(404).json({ 
        success: false, 
        message: "No items found in this requisition" 
      });
    }

    const stockItems = [];
    
    for (const item of requisitionItems) {
      const stockResult = await pool.query(
        `SELECT 
           COALESCE(SUM(net_quantity), 0) as available_stock,
           COUNT(*) as stock_entries
         FROM stocks
         WHERE material_name ILIKE $1 
           AND is_deleted = false 
           AND org_id = $2
           AND net_quantity > 0`,
        [`%${item.item_name}%`, orgId]
      );

      const availableStock = parseFloat(stockResult.rows[0]?.available_stock) || 0;
      const requestedQuantity = parseFloat(item.quantity) || 0;
      
      let stockStatus = "none";
      if (availableStock >= requestedQuantity) {
        stockStatus = "full";
      } else if (availableStock > 0 && availableStock < requestedQuantity) {
        stockStatus = "partial";
      } else {
        stockStatus = "none";
      }

      stockItems.push({
        requisition_item_id: item.id,
        item_code: item.item_code,
        item_name: item.item_name,
        requested_quantity: requestedQuantity,
        original_requested_quantity: requestedQuantity,
        available_stock: availableStock,
        stock_status: stockStatus,
        max_issuable: Math.min(requestedQuantity, availableStock),
        uom: item.uom || "Tons"
      });
    }

    let overallStatus = "full";
    if (stockItems.some(item => item.stock_status === "none" && item.requested_quantity > 0)) {
      overallStatus = "none";
    } else if (stockItems.some(item => item.stock_status === "partial")) {
      overallStatus = "partial";
    }

    res.json({
      success: true,
      data: {
        requisition_id: parseInt(id),
        pr_number: requisition.pr_number,
        items: stockItems,
        overall_status: overallStatus
      }
    });
  } catch (error) {
    console.error("Error in getStockAvailabilityForPR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// =====================================================
// GET STOCK SUMMARY
// =====================================================
const getStockSummary = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const result = await pool.query(
      `SELECT 
         material_name,
         SUM(net_quantity) as total_quantity,
         COUNT(*) as entry_count,
         MAX(received_date) as last_received
       FROM stocks
       WHERE is_deleted = false 
         AND org_id = $1
         AND net_quantity > 0
       GROUP BY material_name
       ORDER BY total_quantity DESC`,
      [orgId]
    );

    res.json({
      success: true,
      data: result.rows.map(row => ({
        productName: row.material_name,
        quantityKg: parseFloat(row.total_quantity) || 0,
        quantityTon: ((parseFloat(row.total_quantity) || 0) / 1000).toFixed(2),
        entryCount: parseInt(row.entry_count)
      }))
    });
  } catch (error) {
    console.error("Error in getStockSummary:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// =====================================================
// GET STOCK LEDGER FOR A MATERIAL
// =====================================================
const getStockLedger = async (req, res) => {
  try {
    const { materialName } = req.params;
    const orgId = req.user?.orgId;
    const { page = 1, limit = 10 } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);

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

    const countResult = await pool.query(
      `SELECT COUNT(*) as total
       FROM stocks
       WHERE material_name ILIKE $1 
         AND is_deleted = false 
         AND org_id = $2`,
      [`%${materialName}%`, orgId]
    );

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

    const result = await pool.query(
      `SELECT 
         s.id,
         s.vehicle_number,
         s.po_number,
         s.supplier_name,
         s.material_name,
         s.net_quantity,
         s.quantity_unit,
         s.received_date,
         s.created_at,
         CONCAT(u.first_name, ' ', u.last_name) as received_by_name
       FROM stocks s
       LEFT JOIN users u ON u.id = s.received_by
       WHERE s.material_name ILIKE $1 
         AND s.is_deleted = false 
         AND s.org_id = $2
         AND s.net_quantity > 0
       ORDER BY s.received_date DESC
       LIMIT $3 OFFSET $4`,
      [`%${materialName}%`, orgId, parseInt(limit), offset]
    );

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalEntries: total,
        itemsPerPage: parseInt(limit)
      }
    });
  } catch (error) {
    console.error("Error in getStockLedger:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// =====================================================
// UPDATE PO RAISED QUANTITY
// =====================================================
const updatePOraisedQuantity = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { requisitionId } = req.params;
    const { items, poId } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

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

    await client.query('BEGIN');

    for (const item of items) {
      await client.query(
        `UPDATE requisition_items 
         SET quantity_po_raised = quantity_po_raised + $1,
             updated_at = NOW()
         WHERE id = $2`,
        [item.quantity, item.requisition_item_id]
      );
    }

    await client.query('COMMIT');

    res.json({
      success: true,
      message: "PO quantities updated successfully"
    });

  } catch (err) {
    await client.query('ROLLBACK');
    console.error("Error in updatePOraisedQuantity:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};



// =====================================================
const approveItemsForPO = async (req, res) => {
  const client = await pool.connect();
 
  try {
    const { id: requisitionId } = req.params;
    const { items, assigned_to_id } = req.body;
    const orgId  = req.user?.orgId;
    const userId = req.user?.id || req.user?.userId;
 
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }
    if (!items || items.length === 0) {
      return res.status(400).json({ success: false, message: "No items selected for PO" });
    }
 
    await client.query("BEGIN");
 
    const result            = [];
    const skipped           = [];
    let itemsIssuedFromStock = 0;
    let itemsRequestedForPO  = 0;
 
    for (const item of items) {
      // ── Fetch current pr_inventory row ─────────────────────────────────
      const existingRecord = await client.query(
        `SELECT id, remaining_quantity, requested_quantity, issued_quantity,
                po_requested_quantity, status, item_name, item_code
         FROM pr_inventory
         WHERE requisition_item_id = $1
           AND requisition_id      = $2
         ORDER BY id ASC
         LIMIT 1`,
        [item.requisition_item_id, requisitionId]
      );
 
      if (existingRecord.rows.length === 0) continue;
 
      const row = existingRecord.rows[0];
 
      // ── FIX 1 & 2: Skip items that are already processed ───────────────
      // PO Requested → already in the PO queue, don't double-add
      // PO Raised    → PO is finalised, cannot modify
      // Fully Issued → nothing left to do
      if (
        row.status === 'PO Requested' ||
        row.status === 'PO Raised'    ||
        row.status === 'Fully Issued'
      ) {
        skipped.push({
          item_name: row.item_name,
          status:    row.status,
          reason:    row.status === 'Fully Issued'
            ? 'Already fully issued from stock'
            : row.status === 'PO Raised'
              ? 'PO already raised — cannot modify'
              : 'PO already requested — awaiting PO creation',
        });
        continue;
      }
 
      const requestQty = parseFloat(row.remaining_quantity) || 0;
      if (requestQty <= 0) {
        skipped.push({ item_name: row.item_name, status: row.status, reason: 'No remaining quantity' });
        continue;
      }
 
      // ── 1. Check available stock ─────────────────────────────────────────
      const stockResult = await client.query(
        `SELECT id, net_quantity, received_date
         FROM stocks
         WHERE material_name ILIKE $1
           AND is_deleted  = false
           AND org_id      = $2
           AND net_quantity > 0
         ORDER BY received_date ASC`,
        [`%${row.item_name}%`, orgId]
      );
 
      const totalAvailableStock = stockResult.rows.reduce(
        (sum, s) => sum + parseFloat(s.net_quantity), 0
      );
 
      const issueFromStock = Math.min(requestQty, totalAvailableStock);
      const remainingForPO = requestQty - issueFromStock;
 
      // ── 2. FIFO stock deduction ──────────────────────────────────────────
      if (issueFromStock > 0) {
        let toDeduct = issueFromStock;
        for (const stockEntry of stockResult.rows) {
          if (toDeduct <= 0) break;
          const currentQty = parseFloat(stockEntry.net_quantity);
          const deductAmt  = Math.min(toDeduct, currentQty);
          const newQty     = currentQty - deductAmt;
 
          if (newQty <= 0) {
            await client.query(`DELETE FROM stocks WHERE id = $1`, [stockEntry.id]);
          } else {
            await client.query(
              `UPDATE stocks
               SET net_quantity = $1::numeric, updated_at = NOW(), updated_by = $2::int
               WHERE id = $3::int`,
              [newQty, userId, stockEntry.id]
            );
          }
          toDeduct -= deductAmt;
        }
      }
 
      // ── 3. Determine new status & quantities ─────────────────────────────
      const newIssuedQty    = (parseFloat(row.issued_quantity) || 0) + issueFromStock;
      const newRemainingQty = remainingForPO;
 
      let newStatus;
      if (remainingForPO <= 0) {
        // Stock covered everything — no PO needed
        newStatus = 'Fully Issued';
        itemsIssuedFromStock++;
      } else {
        // Remainder goes to PO queue
        newStatus = 'PO Requested';
        itemsRequestedForPO++;
        if (issueFromStock > 0) itemsIssuedFromStock++; // partial stock too
      }
 
      // ── 4. Resolve issued_to_name ────────────────────────────────────────
      let issuedToName = row.issued_to_name || null;
      if (assigned_to_id && issueFromStock > 0) {
        const userRes = await client.query(
          `SELECT CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,'')) AS full_name
           FROM users WHERE id = $1`,
          [assigned_to_id]
        );
        if (userRes.rows.length > 0) issuedToName = userRes.rows[0].full_name;
      }
 
      // ── 5. Update pr_inventory ───────────────────────────────────────────
      const updRes = await client.query(
        `UPDATE pr_inventory
         SET issued_quantity       = $1::numeric,
             remaining_quantity    = $2::numeric,
             po_requested_quantity = $3::numeric,
             status                = $4::text,
             issued_to_id          = $5::int,
             issued_to_name        = $6::text,
             issued_at             = CASE WHEN $1::numeric > 0 THEN NOW() ELSE issued_at END,
             updated_at            = NOW(),
             updated_by            = $7::int
         WHERE id = $8::int
         RETURNING *`,
        [
          newIssuedQty,
          newRemainingQty,
          remainingForPO,      // po_requested_quantity = how much the PO needs to cover
          newStatus,
          assigned_to_id || null,
          issuedToName,
          userId,
          row.id,
        ]
      );
 
      result.push({
        ...updRes.rows[0],
        issued_from_stock: issueFromStock,
        requested_for_po:  remainingForPO,
      });
    }
 
    // If everything was skipped with no new work done
    if (result.length === 0) {
      await client.query("ROLLBACK");
      const firstSkip = skipped[0];
      return res.status(400).json({
        success: false,
        message: firstSkip
          ? `${firstSkip.item_name}: ${firstSkip.reason}`
          : "No valid items found to process",
        skipped,
      });
    }
 
    // ── 6. Update requisition po_status ────────────────────────────────────
    await client.query(
      `UPDATE requisitions SET po_status = 'Partially Raised', updated_at = NOW() WHERE id = $1`,
      [requisitionId]
    );
 
    // ── 7. Update requisition status based on all inventory rows ──────────
    const inventoryItems = await client.query(
      `SELECT status, remaining_quantity FROM pr_inventory WHERE requisition_id = $1`,
      [requisitionId]
    );
    const rows = inventoryItems.rows;
 
    const allFullyIssued = rows.every(
      r => r.status === 'Fully Issued' || parseFloat(r.remaining_quantity) <= 0
    );
    const allPending = rows.every(
      r => r.status === 'Pending' && parseFloat(r.remaining_quantity) > 0
    );
    const reqStatus = allFullyIssued ? 'Fully Issued'
      : allPending   ? 'Approved'
      : 'Partially Issued';
 
    await client.query(
      `UPDATE requisitions SET status = $1, updated_at = NOW() WHERE id = $2`,
      [reqStatus, requisitionId]
    );
 
    await client.query("COMMIT");
 
    const summary = [];
    if (itemsIssuedFromStock > 0) summary.push(`${itemsIssuedFromStock} item(s) issued from stock`);
    if (itemsRequestedForPO  > 0) summary.push(`${itemsRequestedForPO} item(s) sent to PO queue`);
    if (skipped.length        > 0) summary.push(`${skipped.length} item(s) skipped (already processed)`);
 
    res.json({
      success: true,
      message: summary.join('; ') || `${result.length} item(s) processed`,
      data: {
        processed_items:        result,
        skipped_items:          skipped,
        items_from_stock:       itemsIssuedFromStock,
        items_requested_for_po: itemsRequestedForPO,
        po_status:              "Partially Raised",
      },
    });
 
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Error in approveItemsForPO:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};
 
module.exports = {
  getAllRequisitions,
  getAllRequisitionsWithoutPagination,
  getRequisitionById,
  createRequisition,
  updateRequisitionStatus,
  updateRequisitionComment,
  updateRequisitionItems,
  deleteRequisition,
  hardDeleteRequisition,
  sendQuotationRequest,
  getIssueHistory,
  getAllInventoryIssues,
  getStockAvailabilityForPR,
  getStockSummary,
  getStockLedger,
  updatePOraisedQuantity,
  getPRItemPOStatus,
  approveRequisitionWithStock,      
  getApprovedPRsForInventory,       
  issueInventoryFromPR,             
  approvePRForPO,                   
  approveItemsForPO,
  getIssuedInventoryDetails,        
  updatePRInventoryConsumptionStatus 
};