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

// /* ============================================================
//    DAILY CONSUMPTION MODULE — CONTROLLER (NO COST, ONLY QUANTITY)
// ============================================================ */

// /* ============================================================
//    SECTION 1 — CONSUMPTION CATEGORIES
// ============================================================ */

// /* ✅ GET ALL CATEGORIES */
// const getCategories = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     if (!org_id) {
//       return res.status(400).json({ success: false, message: "Organisation ID not found in session" });
//     }

//     const { search, status } = req.query;
//     const params = [org_id];
//     let paramIdx = 2;

//     let query = `
//       SELECT 
//         cc.*,
//         COUNT(ci.id) FILTER (WHERE ci.is_deleted = false) AS item_count
//       FROM consumption_categories cc
//       LEFT JOIN consumption_items ci ON ci.category_id = cc.id
//       WHERE cc.is_deleted = false AND cc.org_id = $1
//     `;

//     if (search) {
//       query += ` AND (cc.name ILIKE $${paramIdx})`;
//       params.push(`%${search}%`);
//       paramIdx++;
//     }
//     if (status) {
//       query += ` AND cc.status = $${paramIdx}`;
//       params.push(status);
//       paramIdx++;
//     }

//     query += ` GROUP BY cc.id ORDER BY cc.name ASC`;

//     const result = await pool.query(query, params);
    
//     const categories = result.rows;
//     const totalCategories = categories.length;
//     const activeCategories = categories.filter(cat => cat.status === "Active").length;
//     const inactiveCategories = categories.filter(cat => cat.status === "Inactive").length;

//     res.json({ 
//       success: true, 
//       data: categories,
//       summary: {
//         totalCategories,
//         activeCategories,
//         inactiveCategories
//       }
//     });
//   } catch (err) {
//     console.error("GET CATEGORIES ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ GET CATEGORY BY ID */
// const getCategoryById = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { id } = req.params;
//     const result = await pool.query(
//       `SELECT cc.*,
//          COUNT(ci.id) FILTER (WHERE ci.is_deleted = false) AS item_count
//        FROM consumption_categories cc
//        LEFT JOIN consumption_items ci ON ci.category_id = cc.id
//        WHERE cc.id = $1 AND cc.org_id = $2 AND cc.is_deleted = false
//        GROUP BY cc.id`,
//       [id, org_id]
//     );
//     if (!result.rows.length) {
//       return res.status(404).json({ success: false, message: "Category not found" });
//     }
//     res.json({ success: true, data: result.rows[0] });
//   } catch (err) {
//     console.error("GET CATEGORY BY ID ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ CREATE CATEGORY */
// const createCategory = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     if (!org_id) {
//       return res.status(400).json({ success: false, message: "Organisation ID not found in session" });
//     }

//     const { name, description, status = "Active" } = req.body;

//     if (!name?.trim()) {
//       return res.status(400).json({ success: false, message: "Category name is required" });
//     }

//     // Duplicate check
//     const existing = await pool.query(
//       `SELECT id FROM consumption_categories WHERE name = $1 AND org_id = $2 AND is_deleted = false`,
//       [name.trim(), org_id]
//     );
//     if (existing.rows.length) {
//       return res.status(409).json({ success: false, message: `Category "${name}" already exists` });
//     }

//     const result = await pool.query(
//       `INSERT INTO consumption_categories
//          (name, description, status, org_id, created_by, updated_by, created_at, updated_at)
//        VALUES ($1, $2, $3, $4, $5, $5, NOW(), NOW())
//        RETURNING *`,
//       [name.trim(), description?.trim() || null, status, org_id, userId || null]
//     );

//     res.status(201).json({ success: true, message: "Category created successfully", data: result.rows[0] });
//   } catch (err) {
//     console.error("CREATE CATEGORY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ UPDATE CATEGORY */
// const updateCategory = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;
//     const { name, description, status } = req.body;

//     const existing = await pool.query(
//       `SELECT * FROM consumption_categories WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [id, org_id]
//     );
//     if (!existing.rows.length) {
//       return res.status(404).json({ success: false, message: "Category not found" });
//     }
//     const old = existing.rows[0];

//     // Duplicate name check (exclude current)
//     if (name && name.trim() !== old.name) {
//       const dupCheck = await pool.query(
//         `SELECT id FROM consumption_categories WHERE name = $1 AND org_id = $2 AND id != $3 AND is_deleted = false`,
//         [name.trim(), org_id, id]
//       );
//       if (dupCheck.rows.length) {
//         return res.status(409).json({ success: false, message: `Category "${name}" already exists` });
//       }
//     }

//     const result = await pool.query(
//       `UPDATE consumption_categories SET
//          name          = $1,
//          description   = $2,
//          status        = $3,
//          updated_by    = $4,
//          updated_at    = NOW()
//        WHERE id = $5 AND org_id = $6 AND is_deleted = false
//        RETURNING *`,
//       [
//         name?.trim() || old.name,
//         description !== undefined ? (description?.trim() || null) : old.description,
//         status || old.status,
//         userId || null,
//         id, org_id,
//       ]
//     );

//     res.json({ success: true, message: "Category updated successfully", data: result.rows[0] });
//   } catch (err) {
//     console.error("UPDATE CATEGORY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ DELETE CATEGORY (soft) */
// const deleteCategory = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;

//     // Block if active items exist
//     const itemCheck = await pool.query(
//       `SELECT id FROM consumption_items WHERE category_id = $1 AND is_deleted = false LIMIT 1`,
//       [id]
//     );
//     if (itemCheck.rows.length) {
//       return res.status(400).json({
//         success: false,
//         message: "Cannot delete category with existing items. Delete all items first.",
//       });
//     }

//     const result = await pool.query(
//       `UPDATE consumption_categories
//        SET is_deleted = true, updated_by = $1, updated_at = NOW()
//        WHERE id = $2 AND org_id = $3 AND is_deleted = false
//        RETURNING id`,
//       [userId || null, id, org_id]
//     );
//     if (!result.rowCount) {
//       return res.status(404).json({ success: false, message: "Category not found" });
//     }
//     res.json({ success: true, message: "Category deleted successfully" });
//   } catch (err) {
//     console.error("DELETE CATEGORY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ============================================================
//    SECTION 2 — CONSUMPTION ITEMS (NO COST)
// ============================================================ */

// /* ✅ GET ITEMS BY CATEGORY */
// const getItemsByCategory = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { id: category_id } = req.params;
//     const { search, status } = req.query;
//     const params = [category_id, org_id];
//     let paramIdx = 3;

//     let query = `
//       SELECT ci.*, cc.name AS category_name
//       FROM consumption_items ci
//       JOIN consumption_categories cc ON cc.id = ci.category_id
//       WHERE ci.category_id = $1 AND ci.org_id = $2 AND ci.is_deleted = false
//     `;

//     if (search) {
//       query += ` AND ci.name ILIKE $${paramIdx}`;
//       params.push(`%${search}%`);
//       paramIdx++;
//     }
//     if (status) {
//       query += ` AND ci.status = $${paramIdx}`;
//       params.push(status);
//       paramIdx++;
//     }

//     query += ` ORDER BY ci.name ASC`;

//     const result = await pool.query(query, params);
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET ITEMS BY CATEGORY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ GET ITEM BY ID */
// const getItemById = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { id } = req.params;
//     const result = await pool.query(
//       `SELECT ci.*, cc.name AS category_name
//        FROM consumption_items ci
//        JOIN consumption_categories cc ON cc.id = ci.category_id
//        WHERE ci.id = $1 AND ci.org_id = $2 AND ci.is_deleted = false`,
//       [id, org_id]
//     );
//     if (!result.rows.length) {
//       return res.status(404).json({ success: false, message: "Item not found" });
//     }
//     res.json({ success: true, data: result.rows[0] });
//   } catch (err) {
//     console.error("GET ITEM BY ID ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ CREATE ITEM */
// const createItem = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id: category_id } = req.params;
//     const { name, uom, status = "Active" } = req.body;

//     if (!name?.trim()) {
//       return res.status(400).json({ success: false, message: "Item name is required" });
//     }

//     // Category must exist
//     const catCheck = await pool.query(
//       `SELECT id FROM consumption_categories WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [category_id, org_id]
//     );
//     if (!catCheck.rows.length) {
//       return res.status(404).json({ success: false, message: "Category not found" });
//     }

//     // Duplicate item name within category
//     const dupCheck = await pool.query(
//       `SELECT id FROM consumption_items WHERE name = $1 AND category_id = $2 AND org_id = $3 AND is_deleted = false`,
//       [name.trim(), category_id, org_id]
//     );
//     if (dupCheck.rows.length) {
//       return res.status(409).json({ success: false, message: `Item "${name}" already exists in this category` });
//     }

//     const result = await pool.query(
//       `INSERT INTO consumption_items
//          (category_id, name, uom, status, org_id, created_by, updated_by, created_at, updated_at)
//        VALUES ($1, $2, $3, $4, $5, $6, $6, NOW(), NOW())
//        RETURNING *`,
//       [category_id, name.trim(), uom?.trim() || null, status, org_id, userId || null]
//     );

//     res.status(201).json({ success: true, message: "Item created successfully", data: result.rows[0] });
//   } catch (err) {
//     console.error("CREATE ITEM ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ UPDATE ITEM */
// const updateItem = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;
//     const { name, uom, status } = req.body;

//     const existing = await pool.query(
//       `SELECT * FROM consumption_items WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [id, org_id]
//     );
//     if (!existing.rows.length) {
//       return res.status(404).json({ success: false, message: "Item not found" });
//     }
//     const old = existing.rows[0];

//     if (name && name.trim() !== old.name) {
//       const dupCheck = await pool.query(
//         `SELECT id FROM consumption_items WHERE name = $1 AND category_id = $2 AND org_id = $3 AND id != $4 AND is_deleted = false`,
//         [name.trim(), old.category_id, org_id, id]
//       );
//       if (dupCheck.rows.length) {
//         return res.status(409).json({ success: false, message: `Item "${name}" already exists in this category` });
//       }
//     }

//     const result = await pool.query(
//       `UPDATE consumption_items SET
//          name          = $1,
//          uom           = $2,
//          status        = $3,
//          updated_by    = $4,
//          updated_at    = NOW()
//        WHERE id = $5 AND org_id = $6 AND is_deleted = false
//        RETURNING *`,
//       [
//         name?.trim() || old.name,
//         uom?.trim() !== undefined ? (uom?.trim() || null) : old.uom,
//         status || old.status,
//         userId || null,
//         id, org_id,
//       ]
//     );

//     res.json({ success: true, message: "Item updated successfully", data: result.rows[0] });
//   } catch (err) {
//     console.error("UPDATE ITEM ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ DELETE ITEM (soft) */
// const deleteItem = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;
    
//     const result = await pool.query(
//       `UPDATE consumption_items
//        SET is_deleted = true, updated_by = $1, updated_at = NOW()
//        WHERE id = $2 AND org_id = $3 AND is_deleted = false
//        RETURNING id`,
//       [userId || null, id, org_id]
//     );
    
//     if (!result.rowCount) {
//       return res.status(404).json({ success: false, message: "Item not found" });
//     }
    
//     res.json({ success: true, message: "Item deleted successfully" });
//   } catch (err) {
//     console.error("DELETE ITEM ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ============================================================
//    SECTION 3 — DAILY CONSUMPTION LOG (QUANTITY ONLY)
// ============================================================ */

// /* ✅ GET LOG ENTRIES */
// const getConsumptionLog = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const {
//       shift_id, entry_date,
//       role_id, category_id,
//       from_date, to_date,
//       page = 1, limit = 100,
//     } = req.query;

//     const params = [org_id];
//     let paramIdx = 2;
//     let where = `dcl.org_id = $1 AND dcl.is_deleted = false`;

//     if (shift_id) {
//       where += ` AND dcl.shift_id = $${paramIdx++}`;
//       params.push(shift_id);
//     }
//     if (entry_date) {
//       where += ` AND dcl.entry_date = $${paramIdx++}`;
//       params.push(entry_date);
//     }
//     if (role_id) {
//       where += ` AND dcl.role_id = $${paramIdx++}`;
//       params.push(role_id);
//     }
//     if (category_id) {
//       where += ` AND dcl.category_id = $${paramIdx++}`;
//       params.push(category_id);
//     }
//     if (from_date && to_date) {
//       where += ` AND dcl.entry_date BETWEEN $${paramIdx++} AND $${paramIdx++}`;
//       params.push(from_date, to_date);
//     }

//     const countResult = await pool.query(
//       `SELECT COUNT(*) AS total FROM daily_consumption_log dcl WHERE ${where}`,
//       params
//     );
//     const totalRecords = parseInt(countResult.rows[0].total || 0);

//     const offset = (parseInt(page) - 1) * parseInt(limit);
//     params.push(parseInt(limit), offset);

//     const result = await pool.query(
//       `SELECT
//          dcl.id, dcl.entry_date,
//          s.shift_name, s.shift_code, s.start_time, s.end_time,
//          cc.name AS category_name,
//          ci.name AS item_name, ci.uom AS item_uom,
//          r.role_name AS logged_by_role,
//          dcl.quantity, dcl.uom,
//          dcl.remarks, 
//          dcl.created_at AS entry_time,
//          dcl.updated_at,
//          dcl.updated_by,
//          dcl.shift_id, dcl.category_id, dcl.item_id, dcl.role_id
//        FROM daily_consumption_log dcl
//        JOIN shifts s ON s.id = dcl.shift_id
//        JOIN consumption_categories cc ON cc.id = dcl.category_id
//        JOIN consumption_items ci ON ci.id = dcl.item_id
//        LEFT JOIN roles r ON r.id = dcl.role_id
//        WHERE ${where}
//        ORDER BY dcl.entry_date DESC, s.start_time
//        LIMIT $${paramIdx++} OFFSET $${paramIdx++}`,
//       params
//     );

//     res.json({
//       success: true,
//       data: result.rows,
//       pagination: {
//         currentPage: parseInt(page),
//         totalPages: Math.ceil(totalRecords / parseInt(limit)),
//         totalRecords,
//         recordsPerPage: parseInt(limit),
//       },
//     });
//   } catch (err) {
//     console.error("GET CONSUMPTION LOG ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ CREATE LOG ENTRY */
// const createLogEntry = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const {
//       category_id, item_id, entry_date, shift_id,
//       role_id, employee_id, quantity, uom, remarks,
//     } = req.body;

//     if (!category_id) return res.status(400).json({ success: false, message: "category_id is required" });
//     if (!item_id) return res.status(400).json({ success: false, message: "item_id is required" });
//     if (!entry_date) return res.status(400).json({ success: false, message: "entry_date is required" });
//     if (!shift_id) return res.status(400).json({ success: false, message: "shift_id is required" });
//     if (quantity === undefined || quantity === "") {
//       return res.status(400).json({ success: false, message: "quantity is required" });
//     }

//     // Duplicate entry check
//     const dupCheck = await pool.query(
//       `SELECT id FROM daily_consumption_log
//        WHERE item_id = $1 AND shift_id = $2 AND entry_date = $3 AND role_id = $4 AND org_id = $5 AND is_deleted = false`,
//       [item_id, shift_id, entry_date, role_id || null, org_id]
//     );
//     if (dupCheck.rows.length) {
//       return res.status(409).json({
//         success: false,
//         message: "An entry already exists for this item, shift, date and role. Use update instead.",
//       });
//     }

//     const result = await pool.query(
//       `INSERT INTO daily_consumption_log
//          (category_id, item_id, entry_date, shift_id, role_id, employee_id,
//           quantity, uom, remarks, org_id, created_by, updated_by, created_at, updated_at)
//        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $11, NOW(), NOW())
//        RETURNING *`,
//       [
//         category_id, item_id, entry_date, shift_id,
//         role_id || null, employee_id || null,
//         parseFloat(quantity), uom || null,
//         remarks?.trim() || null,
//         org_id, userId || null,
//       ]
//     );

//     res.status(201).json({ success: true, message: "Consumption entry added successfully", data: result.rows[0] });
//   } catch (err) {
//     console.error("CREATE LOG ENTRY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ UPDATE LOG ENTRY */
// const updateLogEntry = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;
//     const { quantity, uom, remarks } = req.body;

//     const existing = await pool.query(
//       `SELECT * FROM daily_consumption_log WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [id, org_id]
//     );
//     if (!existing.rows.length) {
//       return res.status(404).json({ success: false, message: "Log entry not found" });
//     }
//     const old = existing.rows[0];

//     const result = await pool.query(
//       `UPDATE daily_consumption_log SET
//          quantity      = $1,
//          uom           = $2,
//          remarks       = $3,
//          updated_by    = $4,
//          updated_at    = NOW()
//        WHERE id = $5 AND org_id = $6 AND is_deleted = false
//        RETURNING *`,
//       [
//         quantity !== undefined ? parseFloat(quantity) : old.quantity,
//         uom !== undefined ? (uom?.trim() || null) : old.uom,
//         remarks !== undefined ? (remarks?.trim() || null) : old.remarks,
//         userId || null,
//         id, org_id,
//       ]
//     );

//     res.json({ success: true, message: "Log entry updated successfully", data: result.rows[0] });
//   } catch (err) {
//     console.error("UPDATE LOG ENTRY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ DELETE LOG ENTRY (soft) */
// const deleteLogEntry = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;
//     const result = await pool.query(
//       `UPDATE daily_consumption_log
//        SET is_deleted = true, updated_by = $1, updated_at = NOW()
//        WHERE id = $2 AND org_id = $3 AND is_deleted = false
//        RETURNING id`,
//       [userId || null, id, org_id]
//     );
//     if (!result.rowCount) {
//       return res.status(404).json({ success: false, message: "Log entry not found" });
//     }
//     res.json({ success: true, message: "Log entry deleted successfully" });
//   } catch (err) {
//     console.error("DELETE LOG ENTRY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ============================================================
//    SECTION 4 — SUMMARY / DASHBOARD
// ============================================================ */

// /* ✅ SUMMARY — total per category per shift for a date */
// const getSummaryByShift = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { entry_date } = req.query;
//     if (!entry_date) {
//       return res.status(400).json({ success: false, message: "entry_date is required" });
//     }

//     const result = await pool.query(
//       `SELECT
//          cc.id AS category_id, cc.name AS category_name,
//          s.id AS shift_id, s.shift_name, s.shift_code,
//          s.start_time, s.end_time,
//          SUM(dcl.quantity) AS total_quantity,
//          COUNT(DISTINCT dcl.item_id) AS distinct_items_logged,
//          COUNT(DISTINCT dcl.role_id) AS roles_that_logged
//        FROM daily_consumption_log dcl
//        JOIN shifts s ON s.id = dcl.shift_id
//        JOIN consumption_categories cc ON cc.id = dcl.category_id
//        WHERE dcl.org_id = $1 AND dcl.entry_date = $2 AND dcl.is_deleted = false
//        GROUP BY cc.id, cc.name, s.id, s.shift_name, s.shift_code, s.start_time, s.end_time
//        ORDER BY cc.name, s.start_time`,
//       [org_id, entry_date]
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET SUMMARY BY SHIFT ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ SUMMARY — per item across date range */
// const getSummaryByItem = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { from_date, to_date } = req.query;
//     if (!from_date || !to_date) {
//       return res.status(400).json({ success: false, message: "from_date and to_date are required" });
//     }

//     const result = await pool.query(
//       `SELECT
//          cc.name AS category_name,
//          ci.id AS item_id, ci.name AS item_name, ci.uom,
//          s.shift_name, s.shift_code,
//          s.start_time, s.end_time,
//          COUNT(dcl.id) AS entry_count,
//          SUM(dcl.quantity) AS total_quantity,
//          MIN(dcl.entry_date) AS first_entry_date,
//          MAX(dcl.entry_date) AS last_entry_date
//        FROM daily_consumption_log dcl
//        JOIN shifts s ON s.id = dcl.shift_id
//        JOIN consumption_categories cc ON cc.id = dcl.category_id
//        JOIN consumption_items ci ON ci.id = dcl.item_id
//        WHERE dcl.org_id = $1 AND dcl.entry_date BETWEEN $2 AND $3 AND dcl.is_deleted = false
//        GROUP BY cc.name, ci.id, ci.name, ci.uom, s.id, s.shift_name, s.shift_code, s.start_time, s.end_time
//        ORDER BY cc.name, ci.name, s.start_time`,
//       [org_id, from_date, to_date]
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET SUMMARY BY ITEM ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ GET ITEMS FOR ENTRY FORM */
// const getItemsForEntryForm = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { category_id, shift_id, entry_date, role_id } = req.query;
//     if (!category_id) {
//       return res.status(400).json({ success: false, message: "category_id is required" });
//     }

//     const resolvedRoleId = role_id || req.session.user?.roleId || null;
//     const resolvedDate = entry_date || new Date().toISOString().split("T")[0];

//     const result = await pool.query(
//       `SELECT
//          ci.id AS item_id, ci.name AS item_name, ci.uom,
//          cc.id AS category_id, cc.name AS category_name,
//          CASE WHEN dcl.id IS NOT NULL THEN true ELSE false END AS already_logged,
//          dcl.quantity AS logged_quantity,
//          dcl.id AS log_id
//        FROM consumption_items ci
//        JOIN consumption_categories cc ON cc.id = ci.category_id
//        LEFT JOIN daily_consumption_log dcl
//          ON dcl.item_id = ci.id
//          AND dcl.shift_id = $2
//          AND dcl.entry_date = $3
//          AND dcl.role_id = $4
//          AND dcl.is_deleted = false
//        WHERE ci.category_id = $1
//          AND ci.status = 'Active'
//          AND ci.is_deleted = false
//          AND cc.status = 'Active'
//          AND cc.is_deleted = false
//          AND ci.org_id = $5
//        ORDER BY ci.name`,
//       [category_id, shift_id || null, resolvedDate, resolvedRoleId, org_id]
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET ITEMS FOR ENTRY FORM ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ CURRENT SHIFT STATUS */
// const getCurrentShiftStatus = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const role_id = req.query.role_id || req.session.user?.roleId;
//     const current_time = req.query.current_time || new Date().toTimeString().slice(0, 5);

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

//     const result = await pool.query(
//       `WITH assigned_items AS (
//          SELECT
//            sea.id AS assignment_id,
//            sea.shift_id, sea.category_id, sea.item_id, sea.is_mandatory,
//            s.shift_name, s.shift_code, s.start_time, s.end_time,
//            cc.name AS category_name,
//            ci.name AS item_name, ci.uom
//          FROM shift_entry_assignments sea
//          JOIN shifts s ON s.id = sea.shift_id
//          JOIN consumption_categories cc ON cc.id = sea.category_id
//          LEFT JOIN consumption_items ci ON ci.id = sea.item_id
//          WHERE sea.org_id = $1 AND sea.role_id = $2 AND sea.is_deleted = false
//            AND s.start_time <= $3::time AND s.end_time >= $3::time
//            AND s.is_deleted = false
//        )
//        SELECT
//          ai.*,
//          CASE
//            WHEN dcl.id IS NOT NULL THEN 'Logged'
//            WHEN ai.is_mandatory = true THEN 'Pending - required'
//            ELSE 'Pending - optional'
//          END AS entry_status,
//          dcl.id AS log_id,
//          dcl.quantity AS logged_quantity,
//          dcl.created_at AS logged_at
//        FROM assigned_items ai
//        LEFT JOIN daily_consumption_log dcl
//          ON dcl.item_id = ai.item_id
//          AND dcl.shift_id = ai.shift_id
//          AND dcl.entry_date = CURRENT_DATE
//          AND dcl.role_id = $2
//          AND dcl.is_deleted = false
//        ORDER BY ai.is_mandatory DESC, ai.category_name, ai.item_name`,
//       [org_id, role_id, current_time]
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET CURRENT SHIFT STATUS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ✅ MISSED SHIFT ENTRIES */
// const getMissedShiftEntries = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const role_id = req.query.role_id || req.session.user?.roleId;
//     const current_time = req.query.current_time || new Date().toTimeString().slice(0, 5);

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

//     const result = await pool.query(
//       `SELECT
//          s.id AS shift_id, s.shift_name, s.shift_code, s.start_time, s.end_time,
//          r.id AS role_id, r.role_name,
//          COUNT(sea.id) AS total_assigned,
//          COUNT(dcl.id) AS total_logged,
//          COUNT(sea.id) - COUNT(dcl.id) AS missing_count,
//          ROUND(100.0 * COUNT(dcl.id) / NULLIF(COUNT(sea.id), 0), 1) AS completion_pct,
//          STRING_AGG(
//            CASE WHEN dcl.id IS NULL THEN COALESCE(ci.name, cc.name) END,
//            ', ' ORDER BY cc.name, ci.name
//          ) AS missing_items_list
//        FROM shift_entry_assignments sea
//        JOIN shifts s ON s.id = sea.shift_id
//        JOIN roles r ON r.id = sea.role_id
//        JOIN consumption_categories cc ON cc.id = sea.category_id
//        LEFT JOIN consumption_items ci ON ci.id = sea.item_id
//        LEFT JOIN daily_consumption_log dcl
//          ON dcl.item_id = sea.item_id
//          AND dcl.shift_id = sea.shift_id
//          AND dcl.entry_date = CURRENT_DATE
//          AND dcl.role_id = sea.role_id
//          AND dcl.is_deleted = false
//        WHERE sea.org_id = $1
//          AND sea.role_id = $2
//          AND sea.is_mandatory = true
//          AND sea.is_deleted = false
//          AND s.end_time < $3::time
//          AND s.is_deleted = false
//        GROUP BY s.id, s.shift_name, s.shift_code, s.start_time, s.end_time, r.id, r.role_name
//        HAVING COUNT(sea.id) - COUNT(dcl.id) > 0
//        ORDER BY s.end_time`,
//       [org_id, role_id, current_time]
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET MISSED SHIFT ENTRIES ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ============================================================
//    SECTION 5 — SHIFT ENTRY ASSIGNMENTS
// ============================================================ */

// const getAssignments = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { shift_id, role_id } = req.query;
//     const params = [org_id];
//     let paramIdx = 2;
//     let where = `sea.org_id = $1 AND sea.is_deleted = false`;

//     if (shift_id) { where += ` AND sea.shift_id = $${paramIdx++}`; params.push(shift_id); }
//     if (role_id) { where += ` AND sea.role_id = $${paramIdx++}`; params.push(role_id); }

//     const result = await pool.query(
//       `SELECT
//          sea.*,
//          s.shift_name, s.shift_code, s.start_time, s.end_time,
//          r.role_name,
//          cc.name AS category_name,
//          ci.name AS item_name
//        FROM shift_entry_assignments sea
//        JOIN shifts s ON s.id = sea.shift_id
//        JOIN roles r ON r.id = sea.role_id
//        JOIN consumption_categories cc ON cc.id = sea.category_id
//        LEFT JOIN consumption_items ci ON ci.id = sea.item_id
//        WHERE ${where}
//        ORDER BY s.start_time, r.role_name, cc.name`,
//       params
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET ASSIGNMENTS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// const createAssignment = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { shift_id, role_id, category_id, item_id, is_mandatory = true } = req.body;

//     if (!shift_id) return res.status(400).json({ success: false, message: "shift_id is required" });
//     if (!role_id) return res.status(400).json({ success: false, message: "role_id is required" });
//     if (!category_id) return res.status(400).json({ success: false, message: "category_id is required" });

//     const result = await pool.query(
//       `INSERT INTO shift_entry_assignments
//          (shift_id, role_id, category_id, item_id, is_mandatory, org_id, created_by, updated_by, created_at, updated_at)
//        VALUES ($1, $2, $3, $4, $5, $6, $7, $7, NOW(), NOW())
//        ON CONFLICT ON CONSTRAINT sea_unique_assign DO UPDATE SET
//          is_mandatory = EXCLUDED.is_mandatory,
//          is_deleted = false,
//          updated_by = EXCLUDED.updated_by,
//          updated_at = NOW()
//        RETURNING *`,
//       [shift_id, role_id, category_id, item_id || null, is_mandatory, org_id, userId || null]
//     );

//     res.status(201).json({ success: true, message: "Assignment created successfully", data: result.rows[0] });
//   } catch (err) {
//     console.error("CREATE ASSIGNMENT ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// const deleteAssignment = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;
//     const result = await pool.query(
//       `UPDATE shift_entry_assignments
//        SET is_deleted = true, updated_by = $1, updated_at = NOW()
//        WHERE id = $2 AND org_id = $3 AND is_deleted = false
//        RETURNING id`,
//       [userId || null, id, org_id]
//     );
//     if (!result.rowCount) {
//       return res.status(404).json({ success: false, message: "Assignment not found" });
//     }
//     res.json({ success: true, message: "Assignment removed successfully" });
//   } catch (err) {
//     console.error("DELETE ASSIGNMENT ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ============================================================
//    SECTION 6 — ALERTS
// ============================================================ */

// const getAlerts = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const role_id = req.query.role_id || req.session.user?.roleId;
//     const { admin } = req.query;

//     let query, params;

//     if (admin === "true") {
//       query = `
//         SELECT
//           sal.id AS alert_id, sal.entry_date, sal.alert_type, sal.alert_message,
//           s.shift_name, s.start_time, s.end_time,
//           r.role_name AS responsible_role,
//           cc.name AS category_name, ci.name AS item_name,
//           sal.created_at AS alerted_at,
//           COUNT(*) OVER (PARTITION BY sal.entry_date, sal.shift_id) AS alerts_for_shift
//         FROM shift_alert_log sal
//         JOIN shifts s ON s.id = sal.shift_id
//         JOIN roles r ON r.id = sal.role_id
//         JOIN consumption_categories cc ON cc.id = sal.category_id
//         LEFT JOIN consumption_items ci ON ci.id = sal.item_id
//         WHERE sal.org_id = $1 AND sal.resolved = false
//         ORDER BY sal.entry_date DESC, s.start_time, r.role_name`;
//       params = [org_id];
//     } else {
//       if (!role_id) {
//         return res.status(400).json({ success: false, message: "role_id is required" });
//       }
//       query = `
//         SELECT
//           sal.id AS alert_id, sal.entry_date, sal.alert_type, sal.alert_message,
//           s.shift_name, s.shift_code, s.start_time, s.end_time,
//           cc.name AS category_name, ci.name AS item_name,
//           sal.created_at AS alerted_at,
//           sal.shift_id, sal.category_id, sal.item_id
//         FROM shift_alert_log sal
//         JOIN shifts s ON s.id = sal.shift_id
//         JOIN consumption_categories cc ON cc.id = sal.category_id
//         LEFT JOIN consumption_items ci ON ci.id = sal.item_id
//         WHERE sal.org_id = $1 AND sal.role_id = $2 AND sal.resolved = false
//           AND sal.entry_date >= CURRENT_DATE - INTERVAL '7 days'
//         ORDER BY sal.entry_date DESC, s.start_time`;
//       params = [org_id, role_id];
//     }

//     const result = await pool.query(query, params);
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET ALERTS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// const generateAlerts = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   try {
//     const { shift_id, entry_date } = req.body;
//     if (!shift_id) {
//       return res.status(400).json({ success: false, message: "shift_id is required" });
//     }
//     const runDate = entry_date || new Date().toISOString().split("T")[0];

//     const result = await pool.query(
//       `SELECT generate_shift_alerts($1, $2, $3) AS new_alerts`,
//       [org_id, shift_id, runDate]
//     );

//     const newAlerts = result.rows[0]?.new_alerts || 0;
//     res.json({
//       success: true,
//       message: `${newAlerts} new alert(s) generated for shift ${shift_id} on ${runDate}`,
//       data: { new_alerts: newAlerts },
//     });
//   } catch (err) {
//     console.error("GENERATE ALERTS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// const resolveAlert = async (req, res) => {
//   const org_id = req.session.user?.orgId;
//   const userId = req.session.user?.userId;
//   try {
//     const { id } = req.params;
//     const result = await pool.query(
//       `UPDATE shift_alert_log
//        SET resolved = true, resolved_at = NOW(), resolved_by = $1, updated_at = NOW()
//        WHERE id = $2 AND org_id = $3 AND resolved = false
//        RETURNING id`,
//       [userId || null, id, org_id]
//     );
//     if (!result.rowCount) {
//       return res.status(404).json({ success: false, message: "Alert not found or already resolved" });
//     }
//     res.json({ success: true, message: "Alert resolved successfully" });
//   } catch (err) {
//     console.error("RESOLVE ALERT ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// module.exports = {
//   getCategories,
//   getCategoryById,
//   createCategory,
//   updateCategory,
//   deleteCategory,
//   getItemsByCategory,
//   getItemById,
//   createItem,
//   updateItem,
//   deleteItem,
//   getConsumptionLog,
//   createLogEntry,
//   updateLogEntry,
//   deleteLogEntry,
//   getSummaryByShift,
//   getSummaryByItem,
//   getItemsForEntryForm,
//   getCurrentShiftStatus,
//   getMissedShiftEntries,
//   getAssignments,
//   createAssignment,
//   deleteAssignment,
//   getAlerts,
//   generateAlerts,
//   resolveAlert,
// };

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

/* ============================================================
   DAILY CONSUMPTION MODULE — COMPLETE CONTROLLER
   Supports: Categories, Items, Consumption Logs with Status
   Status values: 'pending', 'updated', 'Completed', 'Active'
============================================================ */

/* ============================================================
   SECTION 1 — CONSUMPTION CATEGORIES
============================================================ */

/* ✅ GET ALL CATEGORIES */
const getCategories = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    if (!org_id) {
      return res.status(400).json({ success: false, message: "Organisation ID not found in session" });
    }

    const { search, status } = req.query;
    const params = [org_id];
    let paramIdx = 2;

    let query = `
      SELECT 
        cc.*,
        COUNT(ci.id) FILTER (WHERE ci.is_deleted = false) AS item_count
      FROM consumption_categories cc
      LEFT JOIN consumption_items ci ON ci.category_id = cc.id
      WHERE cc.is_deleted = false AND cc.org_id = $1
    `;

    if (search) {
      query += ` AND (cc.name ILIKE $${paramIdx})`;
      params.push(`%${search}%`);
      paramIdx++;
    }
    if (status) {
      query += ` AND cc.status = $${paramIdx}`;
      params.push(status);
      paramIdx++;
    }

    query += ` GROUP BY cc.id ORDER BY cc.name ASC`;

    const result = await pool.query(query, params);
    
    const categories = result.rows;
    const totalCategories = categories.length;
    const activeCategories = categories.filter(cat => cat.status === "Active").length;
    const inactiveCategories = categories.filter(cat => cat.status === "Inactive").length;

    res.json({ 
      success: true, 
      data: categories,
      summary: {
        totalCategories,
        activeCategories,
        inactiveCategories
      }
    });
  } catch (err) {
    console.error("GET CATEGORIES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET CATEGORY BY ID */
const getCategoryById = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { id } = req.params;
    const result = await pool.query(
      `SELECT cc.*,
         COUNT(ci.id) FILTER (WHERE ci.is_deleted = false) AS item_count
       FROM consumption_categories cc
       LEFT JOIN consumption_items ci ON ci.category_id = cc.id
       WHERE cc.id = $1 AND cc.org_id = $2 AND cc.is_deleted = false
       GROUP BY cc.id`,
      [id, org_id]
    );
    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Category not found" });
    }
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET CATEGORY BY ID ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ CREATE CATEGORY */
const createCategory = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    if (!org_id) {
      return res.status(400).json({ success: false, message: "Organisation ID not found in session" });
    }

    const { name, description, status = "Active" } = req.body;

    if (!name?.trim()) {
      return res.status(400).json({ success: false, message: "Category name is required" });
    }

    // Duplicate check
    const existing = await pool.query(
      `SELECT id FROM consumption_categories WHERE name = $1 AND org_id = $2 AND is_deleted = false`,
      [name.trim(), org_id]
    );
    if (existing.rows.length) {
      return res.status(409).json({ success: false, message: `Category "${name}" already exists` });
    }

    const result = await pool.query(
      `INSERT INTO consumption_categories
         (name, description, status, org_id, created_by, updated_by, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $5, NOW(), NOW())
       RETURNING *`,
      [name.trim(), description?.trim() || null, status, org_id, userId || null]
    );

    res.status(201).json({ success: true, message: "Category created successfully", data: result.rows[0] });
  } catch (err) {
    console.error("CREATE CATEGORY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ UPDATE CATEGORY */
const updateCategory = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;
    const { name, description, status } = req.body;

    const existing = await pool.query(
      `SELECT * FROM consumption_categories WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );
    if (!existing.rows.length) {
      return res.status(404).json({ success: false, message: "Category not found" });
    }
    const old = existing.rows[0];

    // Duplicate name check (exclude current)
    if (name && name.trim() !== old.name) {
      const dupCheck = await pool.query(
        `SELECT id FROM consumption_categories WHERE name = $1 AND org_id = $2 AND id != $3 AND is_deleted = false`,
        [name.trim(), org_id, id]
      );
      if (dupCheck.rows.length) {
        return res.status(409).json({ success: false, message: `Category "${name}" already exists` });
      }
    }

    const result = await pool.query(
      `UPDATE consumption_categories SET
         name          = COALESCE($1, name),
         description   = COALESCE($2, description),
         status        = COALESCE($3, status),
         updated_by    = $4,
         updated_at    = NOW()
       WHERE id = $5 AND org_id = $6 AND is_deleted = false
       RETURNING *`,
      [
        name?.trim() || null,
        description?.trim() || null,
        status || null,
        userId || null,
        id, org_id,
      ]
    );

    res.json({ success: true, message: "Category updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("UPDATE CATEGORY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ DELETE CATEGORY (soft) */
const deleteCategory = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;

    // Block if active items exist
    const itemCheck = await pool.query(
      `SELECT id FROM consumption_items WHERE category_id = $1 AND is_deleted = false LIMIT 1`,
      [id]
    );
    if (itemCheck.rows.length) {
      return res.status(400).json({
        success: false,
        message: "Cannot delete category with existing items. Delete all items first.",
      });
    }

    const result = await pool.query(
      `UPDATE consumption_categories
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id`,
      [userId || null, id, org_id]
    );
    if (!result.rowCount) {
      return res.status(404).json({ success: false, message: "Category not found" });
    }
    res.json({ success: true, message: "Category deleted successfully" });
  } catch (err) {
    console.error("DELETE CATEGORY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ============================================================
   SECTION 2 — CONSUMPTION ITEMS
============================================================ */

/* ✅ GET ITEMS BY CATEGORY - WITH ALL FIELDS */
const getItemsByCategory = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { id: category_id } = req.params;
    const { search, status } = req.query;
    const params = [category_id, org_id];
    let paramIdx = 3;

    let query = `
      SELECT ci.*, cc.name AS category_name
      FROM consumption_items ci
      JOIN consumption_categories cc ON cc.id = ci.category_id
      WHERE ci.category_id = $1 AND ci.org_id = $2 AND ci.is_deleted = false
    `;

    if (search) {
      query += ` AND ci.name ILIKE $${paramIdx}`;
      params.push(`%${search}%`);
      paramIdx++;
    }
    if (status) {
      query += ` AND ci.status = $${paramIdx}`;
      params.push(status);
      paramIdx++;
    }

    query += ` ORDER BY ci.name ASC`;

    const result = await pool.query(query, params);
    
    const items = result.rows.map(item => ({
      ...item,
      available_stock: parseFloat(item.available_stock) || 10,
      minimum_stock: parseFloat(item.minimum_stock) || 5,
      quantity: parseFloat(item.quantity) || 0
    }));
    
    res.json({ success: true, data: items });
  } catch (err) {
    console.error("GET ITEMS BY CATEGORY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ CREATE ITEM - WITH FULL DETAILS */
const createItem = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id: category_id } = req.params;
    const { name, uom, item_code, description, status = "Active", quantity = 0, available_stock = 10 } = req.body;

    if (!name?.trim()) {
      return res.status(400).json({ success: false, message: "Item name is required" });
    }

    // Category must exist
    const catCheck = await pool.query(
      `SELECT id FROM consumption_categories WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [category_id, org_id]
    );
    if (!catCheck.rows.length) {
      return res.status(404).json({ success: false, message: "Category not found" });
    }

    // Duplicate item name within category
    const dupCheck = await pool.query(
      `SELECT id FROM consumption_items WHERE name = $1 AND category_id = $2 AND org_id = $3 AND is_deleted = false`,
      [name.trim(), category_id, org_id]
    );
    if (dupCheck.rows.length) {
      return res.status(409).json({ success: false, message: `Item "${name}" already exists in this category` });
    }

    const result = await pool.query(
      `INSERT INTO consumption_items
         (category_id, name, uom, item_code, description, status, quantity, available_stock, org_id, created_by, updated_by, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $10, NOW(), NOW())
       RETURNING *`,
      [category_id, name.trim(), uom?.trim() || null, item_code?.trim() || null, description?.trim() || null, status, quantity || 0, available_stock || 10, org_id, userId || null]
    );

    res.status(201).json({ success: true, message: "Item created successfully", data: result.rows[0] });
  } catch (err) {
    console.error("CREATE ITEM ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ UPDATE ITEM - WITH FULL DETAILS */
const updateItem = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;
    const { name, uom, item_code, description, status, quantity, available_stock } = req.body;

    const existing = await pool.query(
      `SELECT * FROM consumption_items WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );
    if (!existing.rows.length) {
      return res.status(404).json({ success: false, message: "Item not found" });
    }
    const old = existing.rows[0];

    if (name && name.trim() !== old.name) {
      const dupCheck = await pool.query(
        `SELECT id FROM consumption_items WHERE name = $1 AND category_id = $2 AND org_id = $3 AND id != $4 AND is_deleted = false`,
        [name.trim(), old.category_id, org_id, id]
      );
      if (dupCheck.rows.length) {
        return res.status(409).json({ success: false, message: `Item "${name}" already exists in this category` });
      }
    }

    const result = await pool.query(
      `UPDATE consumption_items SET
         name          = COALESCE($1, name),
         uom           = COALESCE($2, uom),
         item_code     = COALESCE($3, item_code),
         description   = COALESCE($4, description),
         status        = COALESCE($5, status),
         quantity      = COALESCE($6, quantity),
         available_stock = COALESCE($7, available_stock),
         updated_by    = $8,
         updated_at    = NOW()
       WHERE id = $9 AND org_id = $10 AND is_deleted = false
       RETURNING *`,
      [
        name?.trim() || null,
        uom?.trim() || null,
        item_code?.trim() || null,
        description?.trim() || null,
        status || null,
        quantity !== undefined ? quantity : null,
        available_stock !== undefined ? available_stock : null,
        userId || null,
        id, org_id,
      ]
    );

    res.json({ success: true, message: "Item updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("UPDATE ITEM ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET ITEM BY ID */
const getItemById = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { id } = req.params;
    const result = await pool.query(
      `SELECT ci.*, cc.name AS category_name
       FROM consumption_items ci
       JOIN consumption_categories cc ON cc.id = ci.category_id
       WHERE ci.id = $1 AND ci.org_id = $2 AND ci.is_deleted = false`,
      [id, org_id]
    );
    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Item not found" });
    }
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET ITEM BY ID ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};



/* ✅ DELETE ITEM (soft) */
const deleteItem = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `UPDATE consumption_items
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id`,
      [userId || null, id, org_id]
    );
    
    if (!result.rowCount) {
      return res.status(404).json({ success: false, message: "Item not found" });
    }
    
    res.json({ success: true, message: "Item deleted successfully" });
  } catch (err) {
    console.error("DELETE ITEM ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ============================================================
   SECTION 3 — DAILY CONSUMPTION LOG (WITH STATUS)
============================================================ */

/* ✅ GET LOG ENTRIES - Updated for date range */
const getConsumptionLog = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const {
      shift_id, entry_date,
      role_id, category_id,
      from_date, to_date,
      page = 1, limit = 100,
    } = req.query;

    const params = [org_id];
    let paramIdx = 2;
    let where = `dcl.org_id = $1 AND dcl.is_deleted = false`;

    if (shift_id) {
      where += ` AND dcl.shift_id = $${paramIdx++}`;
      params.push(shift_id);
    }
    if (entry_date) {
      where += ` AND dcl.entry_date = $${paramIdx++}`;
      params.push(entry_date);
    }
    if (role_id) {
      where += ` AND dcl.role_id = $${paramIdx++}`;
      params.push(role_id);
    }
    if (category_id) {
      where += ` AND dcl.category_id = $${paramIdx++}`;
      params.push(category_id);
    }
    // Fix: Handle date range properly
    if (from_date && to_date) {
      where += ` AND dcl.entry_date >= $${paramIdx} AND dcl.entry_date <= $${paramIdx + 1}`;
      params.push(from_date, to_date);
      paramIdx += 2;
    }

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total FROM daily_consumption_log dcl WHERE ${where}`,
      params.slice(0, paramIdx - 1)
    );
    const totalRecords = parseInt(countResult.rows[0].total || 0);

    const offset = (parseInt(page) - 1) * parseInt(limit);
    params.push(parseInt(limit), offset);

    const result = await pool.query(
      `SELECT
         dcl.id, dcl.entry_date, dcl.status,
         s.shift_name, s.shift_code, s.start_time, s.end_time,
         cc.name AS category_name,
         ci.name AS item_name, ci.uom AS item_uom,
         r.role_name AS logged_by_role,
         dcl.quantity, dcl.uom,
         dcl.remarks, 
         dcl.created_at AS entry_time,
         dcl.updated_at,
         dcl.updated_by,
         dcl.shift_id, dcl.category_id, dcl.item_id, dcl.role_id,
         dcl.employee_id
       FROM daily_consumption_log dcl
       JOIN shifts s ON s.id = dcl.shift_id
       JOIN consumption_categories cc ON cc.id = dcl.category_id
       JOIN consumption_items ci ON ci.id = dcl.item_id
       LEFT JOIN roles r ON r.id = dcl.role_id
       WHERE ${where}
       ORDER BY dcl.entry_date DESC, s.start_time
       LIMIT $${paramIdx++} OFFSET $${paramIdx++}`,
      params
    );

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(totalRecords / parseInt(limit)),
        totalRecords,
        recordsPerPage: parseInt(limit),
      },
    });
  } catch (err) {
    console.error("GET CONSUMPTION LOG ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ CREATE LOG ENTRY - WITH STATUS */
const createLogEntry = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const {
      category_id, item_id, entry_date, shift_id,
      role_id, employee_id, quantity, uom, remarks,
      status = 'pending'
    } = req.body;

    // Validation
    if (!category_id) return res.status(400).json({ success: false, message: "category_id is required" });
    if (!item_id) return res.status(400).json({ success: false, message: "item_id is required" });
    if (!entry_date) return res.status(400).json({ success: false, message: "entry_date is required" });
    if (!shift_id) return res.status(400).json({ success: false, message: "shift_id is required" });
    if (quantity === undefined || quantity === null || quantity === "") {
      return res.status(400).json({ success: false, message: "quantity is required" });
    }

    // Get category_name and item_name for the log
    const categoryResult = await pool.query(
      `SELECT name FROM consumption_categories WHERE id = $1`,
      [category_id]
    );
    const itemResult = await pool.query(
      `SELECT name, uom FROM consumption_items WHERE id = $1`,
      [item_id]
    );

    const category_name = categoryResult.rows[0]?.name || null;
    const item_name = itemResult.rows[0]?.name || null;
    const item_uom = itemResult.rows[0]?.uom || uom;

    // Duplicate entry check
    const dupCheck = await pool.query(
      `SELECT id FROM daily_consumption_log
       WHERE item_id = $1 AND shift_id = $2 AND entry_date = $3 AND role_id = $4 AND org_id = $5 AND is_deleted = false`,
      [item_id, shift_id, entry_date, role_id || null, org_id]
    );
    if (dupCheck.rows.length) {
      return res.status(409).json({
        success: false,
        message: "An entry already exists for this item, shift, date and role. Use update instead.",
      });
    }

    const result = await pool.query(
      `INSERT INTO daily_consumption_log
         (category_id, item_id, entry_date, shift_id, role_id, employee_id,
          quantity, uom, remarks, status, org_id, created_by, updated_by, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $12, NOW(), NOW())
       RETURNING *`,
      [
        category_id, item_id, entry_date, shift_id,
        role_id || null, employee_id || null,
        parseFloat(quantity), item_uom || uom || null,
        remarks?.trim() || null,
        status,
        org_id, userId || null,
      ]
    );

    res.status(201).json({ success: true, message: "Consumption entry added successfully", data: result.rows[0] });
  } catch (err) {
    console.error("CREATE LOG ENTRY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ UPDATE LOG ENTRY - CAN UPDATE STATUS */
const updateLogEntry = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;
    const { quantity, uom, remarks, status } = req.body;

    const existing = await pool.query(
      `SELECT * FROM daily_consumption_log WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );
    if (!existing.rows.length) {
      return res.status(404).json({ success: false, message: "Log entry not found" });
    }
    const old = existing.rows[0];

    const result = await pool.query(
      `UPDATE daily_consumption_log SET
         quantity      = COALESCE($1, quantity),
         uom           = COALESCE($2, uom),
         remarks       = COALESCE($3, remarks),
         status        = COALESCE($4, status),
         updated_by    = $5,
         updated_at    = NOW()
       WHERE id = $6 AND org_id = $7 AND is_deleted = false
       RETURNING *`,
      [
        quantity !== undefined ? parseFloat(quantity) : old.quantity,
        uom !== undefined ? (uom?.trim() || null) : old.uom,
        remarks !== undefined ? (remarks?.trim() || null) : old.remarks,
        status !== undefined ? status : old.status,
        userId || null,
        id, org_id,
      ]
    );

    res.json({ success: true, message: "Log entry updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("UPDATE LOG ENTRY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ DELETE LOG ENTRY (soft) */
const deleteLogEntry = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;
    const result = await pool.query(
      `UPDATE daily_consumption_log
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id`,
      [userId || null, id, org_id]
    );
    if (!result.rowCount) {
      return res.status(404).json({ success: false, message: "Log entry not found" });
    }
    res.json({ success: true, message: "Log entry deleted successfully" });
  } catch (err) {
    console.error("DELETE LOG ENTRY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ============================================================
   SECTION 4 — STATUS SUMMARY FOR DASHBOARD
============================================================ */

/* ✅ GET OVERALL STATUS SUMMARY */
const getOverallStatusSummary = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const result = await pool.query(
      `SELECT 
         COUNT(*) FILTER (WHERE status IN ('updated', 'Completed', 'Active')) AS completed_count,
         COUNT(*) FILTER (WHERE status IN ('pending', 'Pending')) AS pending_count,
         COUNT(*) AS total_count
       FROM daily_consumption_log
       WHERE org_id = $1 AND is_deleted = false`,
      [org_id]
    );
    
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET OVERALL STATUS SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET CATEGORY STATUS SUMMARY */
const getCategoryStatusSummary = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const result = await pool.query(
      `SELECT 
         cc.id AS category_id,
         cc.name AS category_name,
         COUNT(dcl.id) FILTER (WHERE dcl.status IN ('updated', 'Completed', 'Active')) AS completed_count,
         COUNT(dcl.id) FILTER (WHERE dcl.status IN ('pending', 'Pending')) AS pending_count,
         COUNT(dcl.id) AS total_count
       FROM consumption_categories cc
       LEFT JOIN daily_consumption_log dcl ON dcl.category_id = cc.id 
         AND dcl.is_deleted = false
       WHERE cc.org_id = $1 AND cc.is_deleted = false
       GROUP BY cc.id, cc.name
       ORDER BY cc.name`,
      [org_id]
    );
    
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET CATEGORY STATUS SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET SHIFT STATUS SUMMARY */
const getShiftStatusSummary = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { entry_date, shift_id } = req.query;
    
    let query = `
      SELECT 
        s.id AS shift_id,
        s.shift_name,
        COUNT(dcl.id) FILTER (WHERE dcl.status IN ('updated', 'Completed', 'Active')) AS completed_count,
        COUNT(dcl.id) FILTER (WHERE dcl.status IN ('pending', 'Pending')) AS pending_count,
        COUNT(dcl.id) AS total_count
      FROM shifts s
      LEFT JOIN daily_consumption_log dcl ON dcl.shift_id = s.id 
        AND dcl.is_deleted = false
    `;
    
    const params = [org_id];
    let paramIdx = 2;
    let conditions = ` WHERE s.org_id = $1 AND s.is_deleted = false`;
    
    if (entry_date) {
      conditions += ` AND dcl.entry_date = $${paramIdx++}`;
      params.push(entry_date);
    }
    if (shift_id) {
      conditions += ` AND s.id = $${paramIdx++}`;
      params.push(shift_id);
    }
    
    query += conditions;
    query += ` GROUP BY s.id, s.shift_name ORDER BY s.shift_name`;
    
    const result = await pool.query(query, params);
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET SHIFT STATUS SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ============================================================
   SECTION 5 — SUMMARY / DASHBOARD QUERIES
============================================================ */

/* ✅ SUMMARY — total per category per shift for a date */
const getSummaryByShift = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { entry_date } = req.query;
    if (!entry_date) {
      return res.status(400).json({ success: false, message: "entry_date is required" });
    }

    const result = await pool.query(
      `SELECT
         cc.id AS category_id, cc.name AS category_name,
         s.id AS shift_id, s.shift_name, s.shift_code,
         s.start_time, s.end_time,
         SUM(dcl.quantity) AS total_quantity,
         COUNT(DISTINCT dcl.item_id) AS distinct_items_logged,
         COUNT(DISTINCT dcl.role_id) AS roles_that_logged,
         COUNT(dcl.id) FILTER (WHERE dcl.status IN ('updated', 'Completed', 'Active')) AS completed_entries,
         COUNT(dcl.id) FILTER (WHERE dcl.status IN ('pending', 'Pending')) AS pending_entries
       FROM daily_consumption_log dcl
       JOIN shifts s ON s.id = dcl.shift_id
       JOIN consumption_categories cc ON cc.id = dcl.category_id
       WHERE dcl.org_id = $1 AND dcl.entry_date = $2 AND dcl.is_deleted = false
       GROUP BY cc.id, cc.name, s.id, s.shift_name, s.shift_code, s.start_time, s.end_time
       ORDER BY cc.name, s.start_time`,
      [org_id, entry_date]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET SUMMARY BY SHIFT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ SUMMARY — per item across date range */
const getSummaryByItem = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { from_date, to_date } = req.query;
    if (!from_date || !to_date) {
      return res.status(400).json({ success: false, message: "from_date and to_date are required" });
    }

    const result = await pool.query(
      `SELECT
         cc.name AS category_name,
         ci.id AS item_id, ci.name AS item_name, ci.uom,
         s.shift_name, s.shift_code,
         s.start_time, s.end_time,
         COUNT(dcl.id) AS entry_count,
         SUM(dcl.quantity) AS total_quantity,
         MIN(dcl.entry_date) AS first_entry_date,
         MAX(dcl.entry_date) AS last_entry_date,
         COUNT(dcl.id) FILTER (WHERE dcl.status IN ('updated', 'Completed', 'Active')) AS completed_entries
       FROM daily_consumption_log dcl
       JOIN shifts s ON s.id = dcl.shift_id
       JOIN consumption_categories cc ON cc.id = dcl.category_id
       JOIN consumption_items ci ON ci.id = dcl.item_id
       WHERE dcl.org_id = $1 AND dcl.entry_date BETWEEN $2 AND $3 AND dcl.is_deleted = false
       GROUP BY cc.name, ci.id, ci.name, ci.uom, s.id, s.shift_name, s.shift_code, s.start_time, s.end_time
       ORDER BY cc.name, ci.name, s.start_time`,
      [org_id, from_date, to_date]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET SUMMARY BY ITEM ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET ITEMS FOR ENTRY FORM */
const getItemsForEntryForm = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { category_id, shift_id, entry_date, role_id } = req.query;
    if (!category_id) {
      return res.status(400).json({ success: false, message: "category_id is required" });
    }

    const resolvedRoleId = role_id || req.session.user?.roleId || null;
    const resolvedDate = entry_date || new Date().toISOString().split("T")[0];

    const result = await pool.query(
      `SELECT
         ci.id AS item_id, ci.name AS item_name, ci.uom,
         cc.id AS category_id, cc.name AS category_name,
         CASE WHEN dcl.id IS NOT NULL THEN true ELSE false END AS already_logged,
         dcl.quantity AS logged_quantity,
         dcl.status AS logged_status,
         dcl.id AS log_id
       FROM consumption_items ci
       JOIN consumption_categories cc ON cc.id = ci.category_id
       LEFT JOIN daily_consumption_log dcl
         ON dcl.item_id = ci.id
         AND dcl.shift_id = $2
         AND dcl.entry_date = $3
         AND dcl.role_id = $4
         AND dcl.is_deleted = false
       WHERE ci.category_id = $1
         AND ci.status = 'Active'
         AND ci.is_deleted = false
         AND cc.status = 'Active'
         AND cc.is_deleted = false
         AND ci.org_id = $5
       ORDER BY ci.name`,
      [category_id, shift_id || null, resolvedDate, resolvedRoleId, org_id]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET ITEMS FOR ENTRY FORM ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ CURRENT SHIFT STATUS */
const getCurrentShiftStatus = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const role_id = req.query.role_id || req.session.user?.roleId;
    const current_time = req.query.current_time || new Date().toTimeString().slice(0, 5);

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

    const result = await pool.query(
      `WITH assigned_items AS (
         SELECT
           sea.id AS assignment_id,
           sea.shift_id, sea.category_id, sea.item_id, sea.is_mandatory,
           s.shift_name, s.shift_code, s.start_time, s.end_time,
           cc.name AS category_name,
           ci.name AS item_name, ci.uom
         FROM shift_entry_assignments sea
         JOIN shifts s ON s.id = sea.shift_id
         JOIN consumption_categories cc ON cc.id = sea.category_id
         LEFT JOIN consumption_items ci ON ci.id = sea.item_id
         WHERE sea.org_id = $1 AND sea.role_id = $2 AND sea.is_deleted = false
           AND s.start_time <= $3::time AND s.end_time >= $3::time
           AND s.is_deleted = false
       )
       SELECT
         ai.*,
         CASE
           WHEN dcl.id IS NOT NULL THEN 'Logged'
           WHEN ai.is_mandatory = true THEN 'Pending - required'
           ELSE 'Pending - optional'
         END AS entry_status,
         dcl.id AS log_id,
         dcl.quantity AS logged_quantity,
         dcl.status AS log_status,
         dcl.created_at AS logged_at
       FROM assigned_items ai
       LEFT JOIN daily_consumption_log dcl
         ON dcl.item_id = ai.item_id
         AND dcl.shift_id = ai.shift_id
         AND dcl.entry_date = CURRENT_DATE
         AND dcl.role_id = $2
         AND dcl.is_deleted = false
       ORDER BY ai.is_mandatory DESC, ai.category_name, ai.item_name`,
      [org_id, role_id, current_time]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET CURRENT SHIFT STATUS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ MISSED SHIFT ENTRIES */
const getMissedShiftEntries = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const role_id = req.query.role_id || req.session.user?.roleId;
    const current_time = req.query.current_time || new Date().toTimeString().slice(0, 5);

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

    const result = await pool.query(
      `SELECT
         s.id AS shift_id, s.shift_name, s.shift_code, s.start_time, s.end_time,
         r.id AS role_id, r.role_name,
         COUNT(sea.id) AS total_assigned,
         COUNT(dcl.id) AS total_logged,
         COUNT(sea.id) - COUNT(dcl.id) AS missing_count,
         ROUND(100.0 * COUNT(dcl.id) / NULLIF(COUNT(sea.id), 0), 1) AS completion_pct,
         STRING_AGG(
           CASE WHEN dcl.id IS NULL THEN COALESCE(ci.name, cc.name) END,
           ', ' ORDER BY cc.name, ci.name
         ) AS missing_items_list
       FROM shift_entry_assignments sea
       JOIN shifts s ON s.id = sea.shift_id
       JOIN roles r ON r.id = sea.role_id
       JOIN consumption_categories cc ON cc.id = sea.category_id
       LEFT JOIN consumption_items ci ON ci.id = sea.item_id
       LEFT JOIN daily_consumption_log dcl
         ON dcl.item_id = sea.item_id
         AND dcl.shift_id = sea.shift_id
         AND dcl.entry_date = CURRENT_DATE
         AND dcl.role_id = sea.role_id
         AND dcl.is_deleted = false
       WHERE sea.org_id = $1
         AND sea.role_id = $2
         AND sea.is_mandatory = true
         AND sea.is_deleted = false
         AND s.end_time < $3::time
         AND s.is_deleted = false
       GROUP BY s.id, s.shift_name, s.shift_code, s.start_time, s.end_time, r.id, r.role_name
       HAVING COUNT(sea.id) - COUNT(dcl.id) > 0
       ORDER BY s.end_time`,
      [org_id, role_id, current_time]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET MISSED SHIFT ENTRIES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ============================================================
   SECTION 6 — SHIFT ENTRY ASSIGNMENTS
============================================================ */

const getAssignments = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { shift_id, role_id } = req.query;
    const params = [org_id];
    let paramIdx = 2;
    let where = `sea.org_id = $1 AND sea.is_deleted = false`;

    if (shift_id) { where += ` AND sea.shift_id = $${paramIdx++}`; params.push(shift_id); }
    if (role_id) { where += ` AND sea.role_id = $${paramIdx++}`; params.push(role_id); }

    const result = await pool.query(
      `SELECT
         sea.*,
         s.shift_name, s.shift_code, s.start_time, s.end_time,
         r.role_name,
         cc.name AS category_name,
         ci.name AS item_name
       FROM shift_entry_assignments sea
       JOIN shifts s ON s.id = sea.shift_id
       JOIN roles r ON r.id = sea.role_id
       JOIN consumption_categories cc ON cc.id = sea.category_id
       LEFT JOIN consumption_items ci ON ci.id = sea.item_id
       WHERE ${where}
       ORDER BY s.start_time, r.role_name, cc.name`,
      params
    );

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

const createAssignment = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { shift_id, role_id, category_id, item_id, is_mandatory = true } = req.body;

    if (!shift_id) return res.status(400).json({ success: false, message: "shift_id is required" });
    if (!role_id) return res.status(400).json({ success: false, message: "role_id is required" });
    if (!category_id) return res.status(400).json({ success: false, message: "category_id is required" });

    const result = await pool.query(
      `INSERT INTO shift_entry_assignments
         (shift_id, role_id, category_id, item_id, is_mandatory, org_id, created_by, updated_by, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $7, NOW(), NOW())
       ON CONFLICT ON CONSTRAINT sea_unique_assign DO UPDATE SET
         is_mandatory = EXCLUDED.is_mandatory,
         is_deleted = false,
         updated_by = EXCLUDED.updated_by,
         updated_at = NOW()
       RETURNING *`,
      [shift_id, role_id, category_id, item_id || null, is_mandatory, org_id, userId || null]
    );

    res.status(201).json({ success: true, message: "Assignment created successfully", data: result.rows[0] });
  } catch (err) {
    console.error("CREATE ASSIGNMENT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

const deleteAssignment = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;
    const result = await pool.query(
      `UPDATE shift_entry_assignments
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id`,
      [userId || null, id, org_id]
    );
    if (!result.rowCount) {
      return res.status(404).json({ success: false, message: "Assignment not found" });
    }
    res.json({ success: true, message: "Assignment removed successfully" });
  } catch (err) {
    console.error("DELETE ASSIGNMENT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ============================================================
   SECTION 7 — ALERTS
============================================================ */

const getAlerts = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const role_id = req.query.role_id || req.session.user?.roleId;
    const { admin } = req.query;

    let query, params;

    if (admin === "true") {
      query = `
        SELECT
          sal.id AS alert_id, sal.entry_date, sal.alert_type, sal.alert_message,
          s.shift_name, s.start_time, s.end_time,
          r.role_name AS responsible_role,
          cc.name AS category_name, ci.name AS item_name,
          sal.created_at AS alerted_at,
          COUNT(*) OVER (PARTITION BY sal.entry_date, sal.shift_id) AS alerts_for_shift
        FROM shift_alert_log sal
        JOIN shifts s ON s.id = sal.shift_id
        JOIN roles r ON r.id = sal.role_id
        JOIN consumption_categories cc ON cc.id = sal.category_id
        LEFT JOIN consumption_items ci ON ci.id = sal.item_id
        WHERE sal.org_id = $1 AND sal.resolved = false
        ORDER BY sal.entry_date DESC, s.start_time, r.role_name`;
      params = [org_id];
    } else {
      if (!role_id) {
        return res.status(400).json({ success: false, message: "role_id is required" });
      }
      query = `
        SELECT
          sal.id AS alert_id, sal.entry_date, sal.alert_type, sal.alert_message,
          s.shift_name, s.shift_code, s.start_time, s.end_time,
          cc.name AS category_name, ci.name AS item_name,
          sal.created_at AS alerted_at,
          sal.shift_id, sal.category_id, sal.item_id
        FROM shift_alert_log sal
        JOIN shifts s ON s.id = sal.shift_id
        JOIN consumption_categories cc ON cc.id = sal.category_id
        LEFT JOIN consumption_items ci ON ci.id = sal.item_id
        WHERE sal.org_id = $1 AND sal.role_id = $2 AND sal.resolved = false
          AND sal.entry_date >= CURRENT_DATE - INTERVAL '7 days'
        ORDER BY sal.entry_date DESC, s.start_time`;
      params = [org_id, role_id];
    }

    const result = await pool.query(query, params);
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET ALERTS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

const generateAlerts = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const { shift_id, entry_date } = req.body;
    if (!shift_id) {
      return res.status(400).json({ success: false, message: "shift_id is required" });
    }
    const runDate = entry_date || new Date().toISOString().split("T")[0];

    const result = await pool.query(
      `SELECT generate_shift_alerts($1, $2, $3) AS new_alerts`,
      [org_id, shift_id, runDate]
    );

    const newAlerts = result.rows[0]?.new_alerts || 0;
    res.json({
      success: true,
      message: `${newAlerts} new alert(s) generated for shift ${shift_id} on ${runDate}`,
      data: { new_alerts: newAlerts },
    });
  } catch (err) {
    console.error("GENERATE ALERTS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

const resolveAlert = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.userId;
  try {
    const { id } = req.params;
    const result = await pool.query(
      `UPDATE shift_alert_log
       SET resolved = true, resolved_at = NOW(), resolved_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND resolved = false
       RETURNING id`,
      [userId || null, id, org_id]
    );
    if (!result.rowCount) {
      return res.status(404).json({ success: false, message: "Alert not found or already resolved" });
    }
    res.json({ success: true, message: "Alert resolved successfully" });
  } catch (err) {
    console.error("RESOLVE ALERT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};


// =====================================================
// DB MIGRATION — run this once before deploying
// =====================================================
// ALTER TABLE pr_inventory ADD COLUMN IF NOT EXISTS received_quantity NUMERIC DEFAULT 0;
// =====================================================

// =====================================================
// RECEIVE STOCK FROM PR INVENTORY
// POST /api/daily-consumption/stock/receive-from-pr
// =====================================================
const receiveStockFromPR = async (req, res) => {
  const client = await pool.connect();

  try {
    const {
      item_id,
      quantity,          // quantity the user wants to receive NOW (receivable portion)
      uom,
      requisition_id,
      requisition_item_id,
      remarks,
    } = req.body;

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

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

    if (!item_id || !quantity || parseFloat(quantity) <= 0) {
      return res.status(400).json({ success: false, message: "Valid quantity is required" });
    }

    await client.query("BEGIN");

    // ── 1. Get consumption item ──────────────────────────────────────────────
    const itemResult = await client.query(
      `SELECT name, uom, category_id, available_stock
       FROM consumption_items
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [item_id, orgId]
    );

    if (itemResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Consumption item not found" });
    }

    const item = itemResult.rows[0];
    const receiveQty          = parseFloat(quantity);
    const currentAvailableStock = parseFloat(item.available_stock) || 0;
    const newAvailableStock     = currentAvailableStock + receiveQty;

    // ── 2. Validate against pr_inventory — prevent over-receiving ────────────
    // const inventoryRecord = await client.query(
    //   `SELECT issued_quantity, received_quantity, requested_quantity
    //    FROM pr_inventory
    //    WHERE requisition_id = $1 AND requisition_item_id = $2`,
    //   [requisition_id, requisition_item_id]
    // );

    // let newReceivedQty    = receiveQty;
    // let newConsumptionStatus = "Not Received";

    // if (inventoryRecord.rows.length > 0) {
    //   const record          = inventoryRecord.rows[0];
    //   const issuedQty       = parseFloat(record.issued_quantity)   || 0;
    //   const alreadyReceived = parseFloat(record.received_quantity)  || 0;
    //   const receivableQty   = issuedQty - alreadyReceived;          // how much is left

    //   // Guard: cannot receive more than what was issued minus already received
    //   if (receiveQty > receivableQty + 0.001) {          // 0.001 tolerance for float
    //     await client.query("ROLLBACK");
    //     return res.status(400).json({
    //       success: false,
    //       message: `Cannot receive ${receiveQty} ${uom || item.uom}. `
    //              + `Only ${receivableQty.toFixed(2)} ${uom || item.uom} receivable `
    //              + `(Issued: ${issuedQty}, Already received: ${alreadyReceived}).`,
    //     });
    //   }

    //   newReceivedQty = alreadyReceived + receiveQty;

    //   if (newReceivedQty >= issuedQty - 0.001) {
    //     newConsumptionStatus = "Fully Received";
    //   } else if (newReceivedQty > 0) {
    //     newConsumptionStatus = "Partially Received";
    //   }
    // }
    // 4. Ensure received_quantity column exists (auto-migrate, safe to run repeatedly)
await client.query(`
  ALTER TABLE pr_inventory 
  ADD COLUMN IF NOT EXISTS received_quantity NUMERIC DEFAULT 0
`);

// 5. Update pr_inventory consumption status + received_quantity
const inventoryRecord = await client.query(
  `SELECT requested_quantity, issued_quantity, 
          COALESCE(received_quantity, 0) AS received_quantity
   FROM pr_inventory
   WHERE requisition_id = $1 AND requisition_item_id = $2`,
  [requisition_id, requisition_item_id]
);

if (inventoryRecord.rows.length > 0) {
  const record          = inventoryRecord.rows[0];
  const issuedQty       = parseFloat(record.issued_quantity)   || 0;
  const alreadyReceived = parseFloat(record.received_quantity) || 0;
  const receivableQty   = issuedQty - alreadyReceived;

  // Guard: prevent over-receiving
  if (receiveQty > receivableQty + 0.001) {
    await client.query("ROLLBACK");
    return res.status(400).json({
      success: false,
      message: `Cannot receive ${receiveQty} ${uom || item.uom}. `
             + `Only ${receivableQty.toFixed(2)} ${uom || item.uom} receivable `
             + `(Issued: ${issuedQty}, Already received: ${alreadyReceived}).`,
    });
  }

  newReceivedQty = alreadyReceived + receiveQty;

  if (newReceivedQty >= issuedQty - 0.001) {
    newConsumptionStatus = "Fully Received";
  } else if (newReceivedQty > 0) {
    newConsumptionStatus = "Partially Received";
  }

  await client.query(
    `UPDATE pr_inventory
     SET received_quantity  = $1,
         consumption_status = $2,
         updated_at         = NOW(),
         updated_by         = $3
     WHERE requisition_id = $4 AND requisition_item_id = $5`,
    [newReceivedQty, newConsumptionStatus, userId, requisition_id, requisition_item_id]
  );
}

    // ── 3. Update available_stock in consumption_items ───────────────────────
    await client.query(
      `UPDATE consumption_items
       SET available_stock = $1,
           last_stock_update = NOW(),
           updated_at        = NOW(),
           updated_by        = $2
       WHERE id = $3 AND org_id = $4`,
      [newAvailableStock, userId, item_id, orgId]
    );

    // ── 4. Insert into stocks table (audit trail) ────────────────────────────
    await client.query(
      `INSERT INTO stocks (
         material_name, net_quantity, quantity_unit,
         received_date, received_by, org_id
       ) VALUES ($1, $2, $3, $4, $5, $6)`,
      [item.name, receiveQty, uom || item.uom, new Date(), userId, orgId]
    );

    // ── 5. Update pr_inventory: received_quantity + consumption_status ────────
    if (inventoryRecord.rows.length > 0) {
      await client.query(
        `UPDATE pr_inventory
         SET received_quantity   = $1,
             consumption_status  = $2,
             updated_at          = NOW(),
             updated_by          = $3
         WHERE requisition_id = $4 AND requisition_item_id = $5`,
        [newReceivedQty, newConsumptionStatus, userId, requisition_id, requisition_item_id]
      );
    }

    // ── 6. Insert stock_transaction record ───────────────────────────────────
    await client.query(
      `INSERT INTO stock_transactions
         (item_id, transaction_type, quantity, previous_stock, new_stock,
          reference_id, reference_type, remarks, created_by, org_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`,
      [
        item_id,
        "PR_RECEIPT",
        receiveQty,
        currentAvailableStock,
        newAvailableStock,
        requisition_id,
        "PURCHASE_REQUISITION",
        remarks || `Received ${receiveQty} ${uom || item.uom} from PR`,
        userId,
        orgId,
      ]
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: `Successfully received ${receiveQty} ${uom || item.uom} of ${item.name} into stock`,
      data: {
        item_name:           item.name,
        received_quantity:   receiveQty,
        uom:                 uom || item.uom,
        new_available_stock: newAvailableStock,
        consumption_status:  newConsumptionStatus,
        total_received:      newReceivedQty,
      },
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in receiveStockFromPR:", error);
    return res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};



// =====================================================
// GET AVAILABLE STOCK BY ITEM CODES (for PR approval modal)
// GET /api/daily-consumption/stock/available?item_codes=ITM001,ITM002
// =====================================================
const getAvailableStockByItemCodes = async (req, res) => {
  const orgId = req.user?.orgId;
  try {
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const { item_codes } = req.query;
    if (!item_codes) {
      return res.status(400).json({ success: false, message: "item_codes query param is required" });
    }

    const codes = item_codes.split(",").map((c) => c.trim()).filter(Boolean);
    if (codes.length === 0) {
      return res.status(400).json({ success: false, message: "No valid item codes provided" });
    }

    const placeholders = codes.map((_, i) => `$${i + 2}`).join(", ");
    const result = await pool.query(
      `SELECT id, item_code, name, available_stock, uom
       FROM consumption_items
       WHERE org_id = $1
         AND item_code = ANY(ARRAY[${placeholders}])
         AND is_deleted = false`,
      [orgId, ...codes]
    );

    // Return as a map: { item_code -> available_stock }
    const stockMap = {};
    result.rows.forEach((row) => {
      stockMap[row.item_code] = {
        item_id: row.id,
        available_stock: parseFloat(row.available_stock) || 0,
        uom: row.uom,
      };
    });

    res.json({ success: true, data: stockMap });
  } catch (err) {
    console.error("GET AVAILABLE STOCK BY ITEM CODES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};


/* ============================================================
   EXPORT ALL FUNCTIONS
============================================================ */

module.exports = {
  // Categories
  getCategories,
  getCategoryById,
  createCategory,
  updateCategory,
  deleteCategory,
  
  // Items
  getItemsByCategory,
  getItemById,
  createItem,
  updateItem,
  deleteItem,
  
  // Consumption Log
  getConsumptionLog,
  createLogEntry,
  updateLogEntry,
  deleteLogEntry,
  
  // Status Summary (for dashboard)
  getOverallStatusSummary,
  getCategoryStatusSummary,
  getShiftStatusSummary,
  
  // Summary/Dashboard
  getSummaryByShift,
  getSummaryByItem,
  getItemsForEntryForm,
  getCurrentShiftStatus,
  getMissedShiftEntries,
  
  // Assignments
  getAssignments,
  createAssignment,
  deleteAssignment,
  
  // Alerts
  getAlerts,
  generateAlerts,
  resolveAlert,
  receiveStockFromPR,
  getAvailableStockByItemCodes,
};