

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

/* ===========================
   GET LEAVE TYPES
=========================== */
const getLeaveTypes = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const result = await pool.query(
      `SELECT id, name, color, is_paid, default_days_per_year
       FROM leave_types
       WHERE org_id = $1 AND is_deleted = false
       ORDER BY name`,
      [org_id]
    );
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET LEAVE TYPES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   GET MY LEAVE BALANCES
=========================== */
const getMyLeaveBalances = async (req, res) => {
  try {
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;
    const currentYear = new Date().getFullYear();

    const result = await pool.query(
      `SELECT 
         lt.id as type_id,
         lt.name as type,
         lt.color,
         lt.is_paid,
         lt.default_days_per_year as total,
         COALESCE(lb.used_days, 0) as used,
         (lt.default_days_per_year - COALESCE(lb.used_days, 0)) as remaining
       FROM leave_types lt
       LEFT JOIN leave_balances lb 
         ON lb.leave_type_id = lt.id 
         AND lb.user_id = $1 
         AND lb.year = $2
         AND lb.is_deleted = false
       WHERE lt.org_id = $3 AND lt.is_deleted = false
       ORDER BY lt.name`,
      [userId, currentYear, org_id]
    );
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET LEAVE BALANCES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   APPLY FOR LEAVE
=========================== */
const applyLeave = async (req, res) => {
  try {
    const { leaveTypeId, mode, fromDate, toDate, hours, reason, days, employeeId } = req.body;
    const requesterId = req.session.user.id;
    const org_id = req.session.user.orgId;

    if (!leaveTypeId) return res.status(400).json({ success: false, message: "leaveTypeId is required" });
    if (!fromDate)    return res.status(400).json({ success: false, message: "fromDate is required" });
    if (!reason)      return res.status(400).json({ success: false, message: "reason is required" });
    if (!days || days <= 0) return res.status(400).json({ success: false, message: "days must be > 0" });
    if (mode === "full"  && !toDate)              return res.status(400).json({ success: false, message: "toDate required for full-day leave" });
    if (mode === "hours" && (!hours || hours <= 0)) return res.status(400).json({ success: false, message: "hours required for hourly leave" });

    let targetUserId = requesterId;
    if (employeeId) {
      const empResult = await pool.query(
        `SELECT e.id, e.email, e.first_name, e.last_name, u.id as user_id
         FROM employees e
         LEFT JOIN users u ON u.email = e.email
        WHERE e.id = $1 AND e.org_id = $2 AND e.status = 'Active' AND e.is_deleted = false`,
        [employeeId, org_id]
      );
      if (!empResult.rows.length) return res.status(404).json({ success: false, message: "Employee not found" });
      const emp = empResult.rows[0];
      if (emp.user_id) {
        targetUserId = emp.user_id;
      } else {
        const fullName = `${emp.first_name || ""} ${emp.last_name || ""}`.trim() || emp.email;
        const newUser = await pool.query(
          `INSERT INTO users (name, email, org_id, role, created_by)
           VALUES ($1, $2, $3, 'employee', $4)
           ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
           RETURNING id`,
          [fullName, emp.email, org_id, requesterId]
        );
        targetUserId = newUser.rows[0].id;
      }
    }

    const typeCheck = await pool.query(
      `SELECT id FROM leave_types WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [leaveTypeId, org_id]
    );
    if (!typeCheck.rows.length) return res.status(400).json({ success: false, message: "Invalid leave type" });

    const from  = new Date(fromDate);
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    if (from < today) return res.status(400).json({ success: false, message: "Leave cannot start in the past" });
    if (mode === "full" && toDate && new Date(toDate) < from) {
      return res.status(400).json({ success: false, message: "End date must be after start date" });
    }

    const overlap = await pool.query(
      `SELECT id FROM leave_requests
       WHERE employee_id = $1
         AND status IN ('pending', 'approved')
         AND ((from_date BETWEEN $2 AND $3) OR (to_date BETWEEN $2 AND $3) OR ($2 BETWEEN from_date AND to_date))
         AND is_deleted = false`,
      [targetUserId, fromDate, toDate || fromDate]
    );
    if (overlap.rows.length > 0) return res.status(400).json({ success: false, message: "Leave already exists for selected dates" });

    const result = await pool.query(
      `INSERT INTO leave_requests (employee_id, leave_type_id, mode, from_date, to_date, hours, days, reason, status, org_id, created_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'pending', $9, $10)
       RETURNING *`,
      [targetUserId, leaveTypeId, mode || "full", fromDate, toDate || null, hours || null, days, reason, org_id, requesterId]
    );

    res.status(201).json({ success: true, message: "Leave applied successfully", data: result.rows[0] });
  } catch (err) {
    console.error("APPLY LEAVE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   FORWARD TO HR (single)
=========================== */
const forwardToHR = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;

    const requestCheck = await pool.query(
      `SELECT lr.id FROM leave_requests lr
       WHERE lr.id = $1 AND lr.org_id = $2 AND lr.is_deleted = false AND lr.status = 'pending' AND lr.forwarded_at IS NULL`,
      [id, org_id]
    );
    if (!requestCheck.rows.length) {
      return res.status(404).json({ success: false, message: "Request not found, not pending, or already forwarded" });
    }

    const result = await pool.query(
      `UPDATE leave_requests SET forwarded_at = NOW(), updated_by = $1, updated_at = NOW() WHERE id = $2 RETURNING *`,
      [userId, id]
    );

    res.json({ success: true, message: "Forwarded to HR", data: result.rows[0] });
  } catch (err) {
    console.error("FORWARD TO HR ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   BULK FORWARD TO HR
=========================== */
const forwardBulkToHR = async (req, res) => {
  const client = await pool.connect();
  try {
    const { ids } = req.body;
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;

    if (!ids || !ids.length) {
      return res.status(400).json({ success: false, message: "No request IDs provided" });
    }

    await client.query("BEGIN");

    const results = [];
    for (const id of ids) {
      const result = await client.query(
        `UPDATE leave_requests
         SET forwarded_at = NOW(), updated_by = $1, updated_at = NOW()
         WHERE id = $2 AND org_id = $3 AND status = 'pending' AND forwarded_at IS NULL
         RETURNING *`,
        [userId, id, org_id]
      );
      if (result.rows.length) results.push(result.rows[0]);
    }

    await client.query("COMMIT");
    res.json({ success: true, message: `${results.length} requests forwarded to HR`, data: results });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("BULK FORWARD ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

/* ===========================
   ASSIGN COVERAGE
   ─ FIX: shift_group_members.employee_id is stored as integer in DB
     but employee codes like "EMP226" are varchar strings.
     Use ::text cast on both sides to compare safely.
   ─ FIX: upsert used $3::integer which explodes when coverageUserId
     is a valid integer but the CASE expression still tries to cast NULL
     through integer. Use explicit NULL check instead.
=========================== */
// const assignCoverage = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { id } = req.params;
//     const { coverageUserId, note } = req.body;
//     const userId = req.session.user.id;
//     const org_id = req.session.user.orgId;

//     console.log("=== ASSIGN COVERAGE DEBUG ===");
//     console.log("Leave Request ID:", id);
//     console.log("Coverage User ID:", coverageUserId);
//     console.log("Coverage User ID type:", typeof coverageUserId);
//     console.log("Current User ID:", userId);
//     console.log("Org ID:", org_id);

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

//     // ── 1. Validate the leave request ────────────────────────────────────
//     const reqCheck = await client.query(
//       `SELECT lr.id, lr.employee_id, lr.from_date, lr.to_date, lr.days
//        FROM leave_requests lr
//        WHERE lr.id = $1 AND lr.org_id = $2
//          AND lr.is_deleted = false AND lr.status = 'pending'`,
//       [id, org_id]
//     );
    
//     if (!reqCheck.rows.length) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({
//         success: false,
//         message: "Leave request not found or not pending",
//       });
//     }
//     const leaveReq = reqCheck.rows[0];
//     console.log("Leave request found:", leaveReq);

//     // ── 2. Resolve shift_group_id for the covering user ───────────────────
//     let shiftGroupId = null;
//     let coverageUserData = null;

//     if (coverageUserId && coverageUserId !== null && coverageUserId !== "null" && coverageUserId !== 0) {
//       // Convert to integer if it's a string
//       const targetUserId = parseInt(coverageUserId);
      
//       // First, check if the user exists in users table
//       const userCheck = await client.query(
//         `SELECT 
//            u.id,
//            u.name,
//            u.email,
//            u.role,
//            e.id AS employee_id,
//            e.employee_code,
//            e.first_name,
//            e.last_name,
//            e.department_id,
//            e.designation_id,
//            e.status as employee_status
//          FROM users u
//          LEFT JOIN employees e
//            ON e.email = u.email
//            AND e.org_id = u.org_id
//            AND e.is_deleted = false
//          WHERE u.id = $1 
//            AND u.org_id = $2
//            AND u.is_deleted = false`,
//         [targetUserId, org_id]
//       );
      
//       console.log("User check result length:", userCheck.rows.length);
//       console.log("User check result:", JSON.stringify(userCheck.rows, null, 2));
      
//       if (!userCheck.rows.length) {
//         // Try to find by employee_code or other identifier
//         const altCheck = await client.query(
//           `SELECT 
//              u.id,
//              u.name,
//              u.email,
//              u.role,
//              e.id AS employee_id,
//              e.employee_code,
//              e.first_name,
//              e.last_name
//            FROM employees e
//            LEFT JOIN users u ON u.email = e.email AND u.org_id = e.org_id
//            WHERE e.id = $1 AND e.org_id = $2 AND e.status = 'Active' AND e.is_deleted = false`,
//           [targetUserId, org_id]
//         );
        
//         console.log("Alt check result:", altCheck.rows);
        
//         if (altCheck.rows.length) {
//           coverageUserData = altCheck.rows[0];
//           console.log("Found coverage user via employee lookup:", coverageUserData.name);
//         } else {
//           await client.query("ROLLBACK");
//           return res.status(404).json({
//             success: false,
//             message: `Coverage user with ID ${coverageUserId} not found. Please select a valid user.`,
//             debug: { userId: coverageUserId, orgId: org_id }
//           });
//         }
//       } else {
//         coverageUserData = userCheck.rows[0];
//         console.log("Coverage user found:", coverageUserData.name, "Role:", coverageUserData.role);
//       }
      
//       if (coverageUserData) {
//         // Check if user is eligible (not 'others' role)
//         if (coverageUserData.role === 'others') {
//           await client.query("ROLLBACK");
//           return res.status(400).json({
//             success: false,
//             message: "Cannot assign coverage to a user with 'others' role",
//           });
//         }
        
//         // Look up their shift group using employee_id
//         const employeeId = coverageUserData.employee_id;
//         console.log("Employee ID for coverage user:", employeeId);

//         if (employeeId) {
//           const grpCheck = await client.query(
//             `SELECT sg.id, sg.group_name
//              FROM shift_group_members sgm
//              JOIN shift_groups sg ON sg.id = sgm.group_id AND sg.is_deleted = false
//              WHERE sgm.employee_id = $1 AND sgm.org_id = $2
//              LIMIT 1`,
//             [employeeId, org_id]
//           );
          
//           console.log("Shift group check result:", grpCheck.rows);
          
//           if (grpCheck.rows.length) {
//             shiftGroupId = grpCheck.rows[0].id;
//             console.log("Found shift group:", grpCheck.rows[0].group_name, "ID:", shiftGroupId);
//           } else {
//             console.log("No shift group found for employee:", employeeId);
//           }
//         } else {
//           console.log("No employee record found for user:", coverageUserId);
//         }
//       }
//     }

//     // ── 3. Upsert into leave_coverage ─────────────────────────────────────
//     const finalCoverageUserId = coverageUserData ? coverageUserData.id : null;
//     const coverageStatus = finalCoverageUserId ? "assigned" : "removed";

//     const upsertResult = await client.query(
//       `INSERT INTO leave_coverage
//          (leave_request_id, covered_employee_id, covering_user_id, shift_group_id,
//           coverage_note, status, org_id, created_by, updated_by)
//        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $8)
//        ON CONFLICT (leave_request_id)
//        DO UPDATE SET
//          covering_user_id = EXCLUDED.covering_user_id,
//          shift_group_id   = EXCLUDED.shift_group_id,
//          coverage_note    = EXCLUDED.coverage_note,
//          status           = EXCLUDED.status,
//          updated_by       = EXCLUDED.updated_by,
//          updated_at       = NOW(),
//          is_deleted       = false
//        RETURNING *`,
//       [
//         id,                          // $1 leave_request_id
//         leaveReq.employee_id,        // $2 covered_employee_id
//         finalCoverageUserId,         // $3 covering_user_id
//         shiftGroupId,                // $4 shift_group_id
//         note || null,                // $5 coverage_note
//         coverageStatus,              // $6 status
//         org_id,                      // $7 org_id
//         userId,                      // $8 created_by / updated_by
//       ]
//     );

//     console.log("Upsert result:", upsertResult.rows[0]);

//     // ── 4. Keep leave_requests.coverage_user_id in sync ─────────────────
//     await client.query(
//       `UPDATE leave_requests
//        SET coverage_user_id = $1,
//            coverage_note    = $2,
//            updated_by       = $3,
//            updated_at       = NOW()
//        WHERE id = $4`,
//       [finalCoverageUserId, note || null, userId, id]
//     );

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

//     // ── 5. Return enriched row ────────────────────────────────────────────
//     const enriched = await pool.query(
//       `SELECT
//          lc.*,
//          cov.name          AS covering_user_name,
//          cov.email         AS covering_user_email,
//          sg.group_name     AS shift_group_name
//        FROM leave_coverage lc
//        LEFT JOIN users cov ON cov.id = lc.covering_user_id
//        LEFT JOIN shift_groups sg ON sg.id = lc.shift_group_id
//        WHERE lc.id = $1`,
//       [upsertResult.rows[0].id]
//     );

//     res.json({
//       success: true,
//       message: finalCoverageUserId ? "Coverage assigned" : "Coverage removed",
//       data: enriched.rows[0],
//     });
//   } catch (err) {
//     await client.query("ROLLBACK");
//     console.error("ASSIGN COVERAGE ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   } finally {
//     client.release();
//   }
// };
/* ===========================
   ASSIGN COVERAGE - FIXED (creates user if doesn't exist)
=========================== */
const assignCoverage = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id } = req.params;
    const { coverageUserId, note } = req.body;
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;

    console.log("=== ASSIGN COVERAGE DEBUG ===");
    console.log("Leave Request ID:", id);
    console.log("Coverage User ID:", coverageUserId);
    console.log("Current User ID:", userId);
    console.log("Org ID:", org_id);

    await client.query("BEGIN");

    // ── 1. Validate the leave request ────────────────────────────────────
    const reqCheck = await client.query(
      `SELECT lr.id, lr.employee_id, lr.from_date, lr.to_date, lr.days
       FROM leave_requests lr
       WHERE lr.id = $1 AND lr.org_id = $2
         AND lr.is_deleted = false AND lr.status = 'pending'`,
      [id, org_id]
    );
    
    if (!reqCheck.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        success: false,
        message: "Leave request not found or not pending",
      });
    }
    const leaveReq = reqCheck.rows[0];
    console.log("Leave request found:", leaveReq);

    // ── 2. Resolve shift_group_id and user for the covering employee ─────
    let shiftGroupId = null;
    let coverageUserData = null;

    if (coverageUserId && coverageUserId !== null && coverageUserId !== "null" && coverageUserId !== 0) {
      const targetUserId = parseInt(coverageUserId);
      
      // First, check if the user exists in users table
      const userCheck = await client.query(
        `SELECT 
           u.id,
           u.name,
           u.email,
           u.role,
           e.id AS employee_id,
           e.employee_code,
           e.first_name,
           e.last_name
         FROM users u
         LEFT JOIN employees e
           ON e.email = u.email
           AND e.org_id = u.org_id
           AND e.is_deleted = false
         WHERE u.id = $1 
           AND u.org_id = $2
           AND u.is_deleted = false`,
        [targetUserId, org_id]
      );
      
      console.log("User check result:", userCheck.rows);
      
      if (userCheck.rows.length) {
        coverageUserData = {
          id: userCheck.rows[0].id,
          name: userCheck.rows[0].name,
          employee_id: userCheck.rows[0].employee_id
        };
        console.log("Found coverage user via user lookup:", coverageUserData);
      } else {
        // Try to find by employee_id (the ID passed is employee_id, not user_id)
        const empCheck = await client.query(
          `SELECT 
             e.id AS employee_id,
             e.employee_code,
             e.first_name,
             e.last_name,
             e.email,
             u.id AS user_id,
             u.name
           FROM employees e
           LEFT JOIN users u ON u.email = e.email AND u.org_id = e.org_id
           WHERE e.id = $1 AND e.org_id = $2 AND e.status = 'Active' AND e.is_deleted = false`,
          [targetUserId, org_id]
        );
        
        console.log("Employee check result:", empCheck.rows);
        
        if (empCheck.rows.length) {
          const emp = empCheck.rows[0];
          const fullName = `${emp.first_name || ""} ${emp.last_name || ""}`.trim() || emp.employee_code;
          
          if (emp.user_id) {
            // User exists, use existing user_id
            coverageUserData = {
              id: emp.user_id,
              name: emp.name || fullName,
              employee_id: emp.employee_id
            };
            console.log("Found existing user for employee:", coverageUserData);
          } else {
            // Create a new user for this employee
            const email = emp.email || `${emp.employee_code}@example.com`;
            const newUser = await client.query(
              `INSERT INTO users (name, email, org_id, role, created_by, created_at, updated_at)
               VALUES ($1, $2, $3, 'employee', $4, NOW(), NOW())
               RETURNING id`,
              [fullName, email, org_id, userId]
            );
            coverageUserData = {
              id: newUser.rows[0].id,
              name: fullName,
              employee_id: emp.employee_id
            };
            console.log("Created new user for employee:", coverageUserData);
          }
        } else {
          await client.query("ROLLBACK");
          return res.status(404).json({
            success: false,
            message: `Employee with ID ${coverageUserId} not found.`,
          });
        }
      }
      
      if (coverageUserData && coverageUserData.employee_id) {
        // Look up their shift group
        const grpCheck = await client.query(
          `SELECT sg.id, sg.group_name
           FROM shift_group_members sgm
           JOIN shift_groups sg ON sg.id = sgm.group_id AND sg.is_deleted = false
           WHERE sgm.employee_id = $1 AND sgm.org_id = $2
           LIMIT 1`,
          [coverageUserData.employee_id, org_id]
        );
        
        if (grpCheck.rows.length) {
          shiftGroupId = grpCheck.rows[0].id;
          console.log("Found shift group:", grpCheck.rows[0].group_name, "ID:", shiftGroupId);
        }
      }
    }

    // ── 3. Upsert into leave_coverage ─────────────────────────────────────
    const finalCoverageUserId = coverageUserData ? coverageUserData.id : null;
    const coverageStatus = finalCoverageUserId ? "assigned" : "removed";

    const upsertResult = await client.query(
      `INSERT INTO leave_coverage
         (leave_request_id, covered_employee_id, covering_user_id, shift_group_id,
          coverage_note, status, org_id, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $8)
       ON CONFLICT (leave_request_id)
       DO UPDATE SET
         covering_user_id = EXCLUDED.covering_user_id,
         shift_group_id   = EXCLUDED.shift_group_id,
         coverage_note    = EXCLUDED.coverage_note,
         status           = EXCLUDED.status,
         updated_by       = EXCLUDED.updated_by,
         updated_at       = NOW(),
         is_deleted       = false
       RETURNING *`,
      [
        id,                          // $1 leave_request_id
        leaveReq.employee_id,        // $2 covered_employee_id
        finalCoverageUserId,         // $3 covering_user_id
        shiftGroupId,                // $4 shift_group_id
        note || null,                // $5 coverage_note
        coverageStatus,              // $6 status
        org_id,                      // $7 org_id
        userId,                      // $8 created_by / updated_by
      ]
    );

    console.log("Upsert result:", upsertResult.rows[0]);

    // ── 4. Keep leave_requests.coverage_user_id in sync ─────────────────
    await client.query(
      `UPDATE leave_requests
       SET coverage_user_id = $1,
           coverage_note    = $2,
           updated_by       = $3,
           updated_at       = NOW()
       WHERE id = $4`,
      [finalCoverageUserId, note || null, userId, id]
    );

    await client.query("COMMIT");

    // ── 5. Return enriched row ────────────────────────────────────────────
   // In assignCoverage function, update the enriched query:
const enriched = await pool.query(
  `SELECT
     lc.*,
     cov.name          AS covering_user_name,
     cov.email         AS covering_user_email,
     sg.group_name     AS shift_group_name,
     sg.id             AS shift_group_id
   FROM leave_coverage lc
   LEFT JOIN users cov ON cov.id = lc.covering_user_id
   LEFT JOIN shift_groups sg ON sg.id = lc.shift_group_id
   WHERE lc.id = $1`,
  [upsertResult.rows[0].id]
);

    res.json({
      success: true,
      message: finalCoverageUserId ? "Coverage assigned" : "Coverage removed",
      data: enriched.rows[0],
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("ASSIGN COVERAGE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};
/* ===========================
   APPROVE LEAVE REQUEST
=========================== */
const approveRequest = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id } = req.params;
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;

    await client.query("BEGIN");

    const requestResult = await client.query(
      `SELECT lr.*, lt.default_days_per_year
       FROM leave_requests lr
       JOIN leave_types lt ON lt.id = lr.leave_type_id
       WHERE lr.id = $1 AND lr.org_id = $2 AND lr.is_deleted = false
         AND lr.status = 'pending' AND lr.forwarded_at IS NOT NULL`,
      [id, org_id]
    );
    if (!requestResult.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Request not found or not ready for approval" });
    }

    const request     = requestResult.rows[0];
    const currentYear = new Date().getFullYear();

    const leaveTypeResult = await client.query(
      `SELECT is_paid FROM leave_types WHERE id = $1`,
      [request.leave_type_id]
    );
    const isPaid = leaveTypeResult.rows[0]?.is_paid !== false;

    if (isPaid) {
      const balanceResult = await client.query(
        `SELECT id, used_days, total_days FROM leave_balances
         WHERE user_id = $1 AND leave_type_id = $2 AND year = $3 AND is_deleted = false
         FOR UPDATE`,
        [request.employee_id, request.leave_type_id, currentYear]
      );

      if (balanceResult.rows.length === 0) {
        await client.query(
          `INSERT INTO leave_balances (user_id, leave_type_id, year, total_days, used_days, org_id, created_by)
           VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [request.employee_id, request.leave_type_id, currentYear, request.default_days_per_year, request.days, org_id, userId]
        );
      } else {
        const balance     = balanceResult.rows[0];
        const newUsedDays = parseFloat(balance.used_days) + parseFloat(request.days);
        if (newUsedDays > balance.total_days) {
          await client.query("ROLLBACK");
          return res.status(400).json({
            success: false,
            message: `Insufficient balance. Available: ${balance.total_days - balance.used_days} days`,
          });
        }
        await client.query(
          `UPDATE leave_balances SET used_days = $1, updated_by = $2, updated_at = NOW() WHERE id = $3`,
          [newUsedDays, userId, balance.id]
        );
      }
    }

    const result = await client.query(
      `UPDATE leave_requests
       SET status = 'approved', approved_by = $1, approved_at = NOW(), updated_by = $1, updated_at = NOW()
       WHERE id = $2 RETURNING *`,
      [userId, id]
    );

    await client.query("COMMIT");
    res.json({ success: true, message: "Leave approved", data: result.rows[0] });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("APPROVE LEAVE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};


// /* ===========================
//    GET ACTIVE LEAVE COVERAGES
//    Returns all active leave coverages for the current date
// =========================== */
// const getActiveLeaveCoverages = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;
//     const today = new Date().toISOString().split('T')[0];
    
//     const result = await pool.query(
//       `SELECT 
//          lc.id,
//          lc.leave_request_id,
//          lc.covered_employee_id,
//          lc.covering_user_id,
//          lc.coverage_note,
//          lc.status as coverage_status,
//          lr.from_date,
//          lr.to_date,
//          lr.status as leave_status,
//          lr.leave_type_id,
//          cov.name as covering_user_name,
//          emp.name as covered_employee_name
//        FROM leave_coverage lc
//        JOIN leave_requests lr ON lr.id = lc.leave_request_id
//        LEFT JOIN users cov ON cov.id = lc.covering_user_id
//        LEFT JOIN users emp ON emp.id = lc.covered_employee_id
//        WHERE lr.org_id = $1 
//          AND lr.is_deleted = false
//          AND lr.status IN ('approved', 'pending')
//          AND lc.status = 'assigned'
//          AND lr.from_date <= $2
//          AND lr.to_date >= $2`,
//       [org_id, today]
//     );
    
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET ACTIVE LEAVE COVERAGES ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
/* ===========================
   GET ACTIVE LEAVE COVERAGES
   Returns all active leave coverages for the current date
=========================== */
const getActiveLeaveCoverages = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const today = new Date().toISOString().split('T')[0];
    
    console.log("Getting active leave coverages for date:", today);
    
    const result = await pool.query(
      `SELECT 
         lc.id,
         lc.leave_request_id,
         lc.covered_employee_id,
         lc.covering_user_id,
         lc.coverage_note,
         lc.status as coverage_status,
         lr.from_date,
         lr.to_date,
         lr.status as leave_status,
         lr.leave_type_id,
         cov.name as covering_user_name,
         emp.name as covered_employee_name,
         sg.group_name as shift_group_name
       FROM leave_coverage lc
       JOIN leave_requests lr ON lr.id = lc.leave_request_id
       LEFT JOIN users cov ON cov.id = lc.covering_user_id
       LEFT JOIN users emp ON emp.id = lc.covered_employee_id
       LEFT JOIN shift_groups sg ON sg.id = lc.shift_group_id
       WHERE lr.org_id = $1 
         AND lr.is_deleted = false
         AND lr.status IN ('approved', 'pending')
         AND lc.status = 'assigned'
         AND lr.from_date <= $2::date
         AND lr.to_date >= $2::date`,
      [org_id, today]
    );
    
    console.log(`Found ${result.rows.length} active leave coverages`);
    
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET ACTIVE LEAVE COVERAGES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
/* ===========================
   REJECT LEAVE REQUEST
=========================== */
const rejectRequest = async (req, res) => {
  try {
    const { id }     = req.params;
    const { reason } = req.body;
    const userId     = req.session.user.id;
    const org_id     = req.session.user.orgId;

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

    const requestCheck = await pool.query(
      `SELECT id FROM leave_requests WHERE id = $1 AND org_id = $2 AND is_deleted = false AND status = 'pending'`,
      [id, org_id]
    );
    if (!requestCheck.rows.length) {
      return res.status(404).json({ success: false, message: "Request not found or not pending" });
    }

    const result = await pool.query(
      `UPDATE leave_requests
       SET status = 'rejected', rejected_reason = $1, updated_by = $2, updated_at = NOW()
       WHERE id = $3 RETURNING *`,
      [reason, userId, id]
    );

    res.json({ success: true, message: "Leave rejected", data: result.rows[0] });
  } catch (err) {
    console.error("REJECT LEAVE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   WITHDRAW LEAVE REQUEST
=========================== */
const withdrawRequest = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;

    const requestCheck = await pool.query(
      `SELECT id FROM leave_requests WHERE id = $1 AND org_id = $2 AND is_deleted = false AND status = 'pending'`,
      [id, org_id]
    );
    if (!requestCheck.rows.length) {
      return res.status(404).json({ success: false, message: "Request not found or cannot be withdrawn" });
    }

    await pool.query(
      `UPDATE leave_requests SET is_deleted = true, updated_by = $1, updated_at = NOW() WHERE id = $2`,
      [userId, id]
    );

    res.json({ success: true, message: "Leave request withdrawn" });
  } catch (err) {
    console.error("WITHDRAW LEAVE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// /* ===========================
//    GET TEAM MEMBERS (FIXED - with shift groups)
//    Returns all users with their shift group information
// =========================== */
// const getTeamMembers = async (req, res) => {
//   try {
//     const userId = req.session.user.id;
//     const org_id = req.session.user.orgId;
//     const { from_date, to_date, exclude_user_id } = req.query;

//     console.log("=== GET TEAM MEMBERS DEBUG ===");
//     console.log("User ID:", userId);
//     console.log("Org ID:", org_id);

//     const queryParams = [org_id, userId];
//     let paramIndex = 3;

//     let exclusionClause = "";
//     if (exclude_user_id) {
//       exclusionClause = ` AND u.id != $${paramIndex}`;
//       queryParams.push(exclude_user_id);
//       paramIndex++;
//     }

//     let dateClause = "";
//     if (from_date && to_date) {
//       dateClause = `
//         AND u.id NOT IN (
//           SELECT DISTINCT lr2.coverage_user_id
//           FROM leave_requests lr2
//           WHERE lr2.status IN ('approved','pending')
//             AND lr2.is_deleted = false
//             AND lr2.coverage_user_id IS NOT NULL
//             AND lr2.from_date <= $${paramIndex + 1}
//             AND lr2.to_date >= $${paramIndex}
//         )`;
//       queryParams.push(from_date, to_date);
//     }

//     // FIXED QUERY - Properly join shift groups using employee id
//     const sql = `
//       SELECT DISTINCT
//         u.id,
//         u.name,
//         u.role,
//         d.dept_name AS department,
//         e.employee_id AS emp_code,
//         e.profile_image,
//         e.id AS employee_table_id,
//         sg.id AS shift_group_id,
//         sg.group_name AS shift_group_name
//       FROM users u
//       INNER JOIN employees e
//         ON e.email = u.email
//         AND e.org_id = u.org_id
//         AND e.status = 'Active'
//         AND e.is_deleted = false
//       LEFT JOIN departments d 
//         ON d.id = e.department_id 
//         AND d.is_deleted = false
//       LEFT JOIN shift_group_members sgm
//         ON sgm.employee_id = e.id  -- Use e.id, not e.employee_id string
//         AND sgm.org_id = u.org_id
//       LEFT JOIN shift_groups sg
//         ON sg.id = sgm.group_id
//         AND sg.is_deleted = false
//       WHERE u.org_id = $1
//         AND u.is_deleted = false
//         AND u.role != 'others'
//         AND u.id != $2
//         ${exclusionClause}
//         ${dateClause}
//       ORDER BY sg.group_name NULLS LAST, u.name
//     `;

//     console.log("Executing query with params:", queryParams);
//     const result = await pool.query(sql, queryParams);
    
//     console.log(`Found ${result.rows.length} team members`);
//     console.log("Sample member with shift group:", result.rows[0] ? {
//       name: result.rows[0].name,
//       shift_group_id: result.rows[0].shift_group_id,
//       shift_group_name: result.rows[0].shift_group_name
//     } : "none");
    
//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET TEAM MEMBERS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
/* ===========================
   GET TEAM MEMBERS (FIXED - with shift groups)
   Returns all users with their shift group information
=========================== */
const getTeamMembers = async (req, res) => {
  try {
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;
    const { from_date, to_date, exclude_user_id } = req.query;

    console.log("=== GET TEAM MEMBERS DEBUG ===");
    console.log("User ID:", userId);
    console.log("Org ID:", org_id);

    const queryParams = [org_id, userId];
    let paramIndex = 3;

    let exclusionClause = "";
    if (exclude_user_id && exclude_user_id !== "undefined" && exclude_user_id !== "null") {
      exclusionClause = ` AND u.id != $${paramIndex}::integer`;
      queryParams.push(parseInt(exclude_user_id));
      paramIndex++;
    }

    let dateClause = "";
    if (from_date && to_date) {
      dateClause = `
        AND u.id NOT IN (
          SELECT DISTINCT lr2.coverage_user_id
          FROM leave_requests lr2
          WHERE lr2.status IN ('approved','pending')
            AND lr2.is_deleted = false
            AND lr2.coverage_user_id IS NOT NULL
            AND lr2.from_date <= $${paramIndex + 1}::date
            AND lr2.to_date >= $${paramIndex}::date
        )`;
      queryParams.push(from_date, to_date);
    }

    // Fixed query with proper type casting
    const sql = `
      SELECT DISTINCT
        u.id,
        u.name,
        u.role,
        COALESCE(d.dept_name, 'Unknown') AS department,
        e.employee_id AS emp_code,
        e.profile_image,
        e.id AS employee_table_id,
        sg.id AS shift_group_id,
        sg.group_name AS shift_group_name
      FROM users u
      INNER JOIN employees e
        ON e.email::text = u.email::text
        AND e.org_id::integer = u.org_id::integer
        AND e.status = 'Active'
        AND e.is_deleted = false
      LEFT JOIN departments d 
        ON d.id::text = e.department_id::text 
        AND d.is_deleted = false
      LEFT JOIN shift_group_members sgm
        ON sgm.employee_id::integer = e.id::integer
        AND sgm.org_id::integer = u.org_id::integer
      LEFT JOIN shift_groups sg
        ON sg.id::integer = sgm.group_id::integer
        AND sg.is_deleted = false
      WHERE u.org_id = $1::integer
        AND u.is_deleted = false
        AND u.role != 'others'
        AND u.id != $2::integer
        ${exclusionClause}
        ${dateClause}
      ORDER BY sg.group_name NULLS LAST, u.name
    `;

    console.log("Executing query with params:", queryParams);
    const result = await pool.query(sql, queryParams);
    
    console.log(`Found ${result.rows.length} team members`);
    
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET TEAM MEMBERS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   GET LEAVE REQUESTS
   stage=pending   → status='pending' AND forwarded_at IS NULL  (Manager coverage view)
   stage=forwarded → status='pending' AND forwarded_at IS NOT NULL (HR approval view)
   stage=all / none → everything (Dashboard view)
   Includes employee shift group + coverage employee shift group.
=========================== */
/* ===========================
   GET LEAVE REQUESTS - COMPLETE FIXED VERSION
=========================== */
const getLeaveRequests = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { status, stage, from_date, to_date } = req.query;

    console.log("=== GET LEAVE REQUESTS DEBUG ===");
    console.log("Org ID:", org_id);
    console.log("Status filter:", status);
    console.log("Stage filter:", stage);
    console.log("From date:", from_date);
    console.log("To date:", to_date);

    let query = `
      SELECT
        lr.id, lr.mode, lr.from_date, lr.to_date,
        lr.hours, lr.days, lr.reason, lr.status,
        lr.coverage_note, lr.forwarded_at, lr.approved_at,
        lr.rejected_reason, lr.created_at, lr.updated_at,
        lr.coverage_user_id,

        emp.id AS employee_id,
        emp.name AS employee_name,
        COALESCE(dep.dept_name, 'Unknown') AS department,
        e_emp.employee_id AS emp_code,
        e_emp.profile_image,

        sg_emp.id AS employee_shift_group_id,
        sg_emp.group_name AS employee_shift_group_name,

        lt.id AS leave_type_id,
        lt.name AS leave_type,
        lt.color,

        lc.covering_user_id AS coverage_id,
        cov.name AS coverage_name,
        sg_cov.id AS coverage_shift_group_id,
        sg_cov.group_name AS coverage_shift_group_name,

        app.id AS approved_by_id,
        app.name AS approved_by_name
      FROM leave_requests lr
      INNER JOIN users emp ON emp.id = lr.employee_id
      INNER JOIN leave_types lt ON lt.id = lr.leave_type_id

      -- Employee to employee details
      LEFT JOIN employees e_emp ON e_emp.email = emp.email 
        AND e_emp.org_id = emp.org_id 
        AND e_emp.status = 'Active' 
        AND e_emp.is_deleted = false
      LEFT JOIN departments dep ON dep.id::text = e_emp.department_id::text AND dep.is_deleted = false

      -- Employee shift group
      LEFT JOIN shift_group_members sgm_emp ON sgm_emp.employee_id = e_emp.id AND sgm_emp.org_id = emp.org_id
      LEFT JOIN shift_groups sg_emp ON sg_emp.id = sgm_emp.group_id AND sg_emp.is_deleted = false

      -- Coverage
      LEFT JOIN leave_coverage lc ON lc.leave_request_id = lr.id AND lc.status = 'assigned' AND lc.is_deleted = false
      LEFT JOIN users cov ON cov.id = lc.covering_user_id
      LEFT JOIN shift_groups sg_cov ON sg_cov.id = lc.shift_group_id AND sg_cov.is_deleted = false
      LEFT JOIN users app ON app.id = lr.approved_by

      WHERE lr.org_id = $1::integer 
        AND lr.is_deleted = false
    `;

    const queryParams = [org_id];
    let paramIndex = 2;

    // Apply filters based on stage
    if (stage === "pending") {
      query += ` AND lr.status = 'pending' AND lr.forwarded_at IS NULL`;
    } else if (stage === "forwarded") {
      query += ` AND lr.status = 'pending' AND lr.forwarded_at IS NOT NULL`;
    } else if (status && status !== "undefined" && status !== "") {
      query += ` AND lr.status = $${paramIndex}::text`;
      queryParams.push(status);
      paramIndex++;
    }

    // Date filters
    if (from_date && from_date !== "undefined" && from_date !== "") {
      query += ` AND lr.from_date >= $${paramIndex}::date`;
      queryParams.push(from_date);
      paramIndex++;
    }
    if (to_date && to_date !== "undefined" && to_date !== "") {
      query += ` AND lr.to_date <= $${paramIndex}::date`;
      queryParams.push(to_date);
      paramIndex++;
    }

    query += ` ORDER BY lr.created_at DESC`;

    console.log("Executing query with params:", queryParams);
    const result = await pool.query(query, queryParams);
    
    console.log(`Found ${result.rows.length} leave requests`);
    
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET LEAVE REQUESTS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
// /* ===========================
//    GET LEAVE BALANCE BY EMPLOYEE (Group-based)
//    GET /api/leaves/balance/employee/:employeeId
// =========================== */
// const getEmployeeLeaveBalance = async (req, res) => {
//   try {
//     const { employeeId } = req.params;
//     const org_id         = req.session.user.orgId;
//     const currentYear    = new Date().getFullYear();

//     // ✅ Ensure we ONLY get active employees with status = 'Active'
//     const empResult = await pool.query(
//       `SELECT e.id AS emp_id, e.leave_group_id, u.id AS user_id
//        FROM employees e
//        LEFT JOIN users u ON u.email = e.email AND u.org_id = e.org_id
//        WHERE e.id = $1 
//          AND e.org_id = $2 
//          AND e.status = 'Active' 
//          AND e.is_deleted = false
//        LIMIT 1`,
//       [employeeId, org_id]
//     );
    
//     if (!empResult.rows.length) {
//       return res.status(404).json({ 
//         success: false, 
//         message: "Employee not found or not active" 
//       });
//     }

//     const { user_id, leave_group_id } = empResult.rows[0];

//     // Rest of the function remains the same...
//     let groupAllocation = null;
//     if (leave_group_id) {
//       const groupResult = await pool.query(
//         `SELECT annual_leave, sick_leave, casual_leave, maternity_leave,
//                 paternity_leave, comp_off, unpaid_leave, other_leave
//          FROM leave_groups
//          WHERE id = $1 AND org_id = $2 AND is_active = true AND is_deleted = false
//          LIMIT 1`,
//         [leave_group_id, org_id]
//       );
//       if (groupResult.rows.length) groupAllocation = groupResult.rows[0];
//     }

//     const typesResult = await pool.query(
//       `SELECT id, name, color, is_paid, default_days_per_year
//        FROM leave_types
//        WHERE org_id = $1 AND is_deleted = false
//        ORDER BY name`,
//       [org_id]
//     );
//     const leaveTypes = typesResult.rows;

//     const GROUP_COLUMN_MAP = {
//       annual_leave:    ["annual leave", "annual"],
//       sick_leave:      ["sick leave", "sick"],
//       casual_leave:    ["casual leave", "casual"],
//       maternity_leave: ["maternity leave", "maternity"],
//       paternity_leave: ["paternity leave", "paternity"],
//       comp_off:        ["comp off", "compensatory off", "comp-off"],
//       unpaid_leave:    ["unpaid leave", "unpaid"],
//       other_leave:     ["other leave", "other"],
//     };

//     const totalByTypeId = {};
//     for (const lt of leaveTypes) {
//       const nameLower = lt.name.toLowerCase();
//       let resolvedTotal = null;
//       if (groupAllocation) {
//         for (const [col, keywords] of Object.entries(GROUP_COLUMN_MAP)) {
//           if (keywords.some((kw) => nameLower.includes(kw))) {
//             const val = parseFloat(groupAllocation[col]);
//             if (!isNaN(val)) { resolvedTotal = val; break; }
//           }
//         }
//         if (resolvedTotal === null) {
//           const otherVal = parseFloat(groupAllocation.other_leave);
//           resolvedTotal = !isNaN(otherVal) ? otherVal : lt.default_days_per_year;
//         }
//       } else {
//         resolvedTotal = lt.default_days_per_year;
//       }
//       totalByTypeId[lt.id] = resolvedTotal;
//     }

//     let usedByTypeId = {};
//     if (user_id) {
//       const usedResult = await pool.query(
//         `SELECT leave_type_id, COALESCE(SUM(days), 0) AS used_days
//          FROM leave_requests
//          WHERE employee_id = $1 
//            AND org_id = $2 
//            AND status = 'approved'
//            AND is_deleted = false 
//            AND EXTRACT(YEAR FROM from_date) = $3
//          GROUP BY leave_type_id`,
//         [user_id, org_id, currentYear]
//       );
//       usedResult.rows.forEach((r) => { usedByTypeId[r.leave_type_id] = parseFloat(r.used_days); });
//     }

//     const data = leaveTypes.map((lt) => {
//       const total     = totalByTypeId[lt.id] ?? lt.default_days_per_year;
//       const used      = usedByTypeId[lt.id]  ?? 0;
//       const remaining = Math.max(total - used, 0);
//       return { 
//         type_id: lt.id, 
//         type: lt.name, 
//         color: lt.color, 
//         is_paid: lt.is_paid, 
//         total, 
//         used, 
//         remaining 
//       };
//     });

//     res.json({ success: true, data });
//   } catch (err) {
//     console.error("GET EMPLOYEE LEAVE BALANCE ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
/* ===========================
   GET LEAVE BALANCE BY EMPLOYEE (Group-based)
=========================== */
const getEmployeeLeaveBalance = async (req, res) => {
  try {
    const { employeeId } = req.params;
    const org_id = req.session.user.orgId;
    const currentYear = new Date().getFullYear();

    console.log("=== GET EMPLOYEE LEAVE BALANCE DEBUG ===");
    console.log("Employee ID:", employeeId);
    console.log("Org ID:", org_id);

    // Ensure we get active employees
    const empResult = await pool.query(
      `SELECT e.id AS emp_id, e.leave_group_id, u.id AS user_id
       FROM employees e
       LEFT JOIN users u ON u.email = e.email AND u.org_id = e.org_id
       WHERE e.id = $1::integer 
         AND e.org_id = $2::integer 
         AND e.status = 'Active' 
         AND e.is_deleted = false
       LIMIT 1`,
      [employeeId, org_id]
    );
    
    if (!empResult.rows.length) {
      return res.status(404).json({ 
        success: false, 
        message: "Employee not found or not active" 
      });
    }

    const { user_id, leave_group_id } = empResult.rows[0];
    console.log("Found user_id:", user_id, "leave_group_id:", leave_group_id);

    // Get group allocation if exists
    let groupAllocation = null;
    if (leave_group_id) {
      const groupResult = await pool.query(
        `SELECT annual_leave, sick_leave, casual_leave, maternity_leave,
                paternity_leave, comp_off, unpaid_leave, other_leave
         FROM leave_groups
         WHERE id = $1::integer AND org_id = $2::integer AND is_active = true AND is_deleted = false
         LIMIT 1`,
        [leave_group_id, org_id]
      );
      if (groupResult.rows.length) {
        groupAllocation = groupResult.rows[0];
        console.log("Found group allocation:", groupAllocation);
      }
    }

    // Get all leave types
    const typesResult = await pool.query(
      `SELECT id, name, color, is_paid, default_days_per_year
       FROM leave_types
       WHERE org_id = $1::integer AND is_deleted = false
       ORDER BY name`,
      [org_id]
    );
    const leaveTypes = typesResult.rows;
    console.log(`Found ${leaveTypes.length} leave types`);

    const GROUP_COLUMN_MAP = {
      annual_leave:    ["annual leave", "annual"],
      sick_leave:      ["sick leave", "sick"],
      casual_leave:    ["casual leave", "casual"],
      maternity_leave: ["maternity leave", "maternity"],
      paternity_leave: ["paternity leave", "paternity"],
      comp_off:        ["comp off", "compensatory off", "comp-off"],
      unpaid_leave:    ["unpaid leave", "unpaid"],
      other_leave:     ["other leave", "other"],
    };

    // Calculate totals per leave type
    const totalByTypeId = {};
    for (const lt of leaveTypes) {
      const nameLower = lt.name.toLowerCase();
      let resolvedTotal = null;
      
      if (groupAllocation) {
        for (const [col, keywords] of Object.entries(GROUP_COLUMN_MAP)) {
          if (keywords.some((kw) => nameLower.includes(kw))) {
            const val = parseFloat(groupAllocation[col]);
            if (!isNaN(val)) { 
              resolvedTotal = val; 
              break; 
            }
          }
        }
        if (resolvedTotal === null) {
          const otherVal = parseFloat(groupAllocation.other_leave);
          resolvedTotal = !isNaN(otherVal) ? otherVal : lt.default_days_per_year;
        }
      } else {
        resolvedTotal = lt.default_days_per_year;
      }
      totalByTypeId[lt.id] = resolvedTotal;
    }

    // Calculate used days
    let usedByTypeId = {};
    if (user_id) {
      const usedResult = await pool.query(
        `SELECT leave_type_id, COALESCE(SUM(days), 0) AS used_days
         FROM leave_requests
         WHERE employee_id = $1::integer 
           AND org_id = $2::integer 
           AND status = 'approved'
           AND is_deleted = false 
           AND EXTRACT(YEAR FROM from_date) = $3::integer
         GROUP BY leave_type_id`,
        [user_id, org_id, currentYear]
      );
      
      usedResult.rows.forEach((r) => { 
        usedByTypeId[r.leave_type_id] = parseFloat(r.used_days); 
      });
      console.log("Used days by type:", usedByTypeId);
    }

    const data = leaveTypes.map((lt) => {
      const total = totalByTypeId[lt.id] ?? lt.default_days_per_year;
      const used = usedByTypeId[lt.id] ?? 0;
      const remaining = Math.max(total - used, 0);
      return { 
        type_id: lt.id, 
        type: lt.name, 
        color: lt.color, 
        is_paid: lt.is_paid, 
        total, 
        used, 
        remaining 
      };
    });

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

// /* ===========================
//    GET LEAVE BALANCE BY EMPLOYEE
//    GET /api/leaves/balance/:employeeId
// =========================== */
// const getLeaveBalanceByEmployee = async (req, res) => {
//   try {
//     const { employeeId } = req.params;
//     const org_id         = req.session.user.orgId;
//     const currentYear    = new Date().getFullYear();

//     // ✅ Ensure we ONLY get active employees
//     const empResult = await pool.query(
//       `SELECT u.id as user_id
//        FROM employees e
//        JOIN users u ON u.email = e.email AND u.org_id = e.org_id
//        WHERE e.id = $1 
//          AND e.org_id = $2 
//          AND e.status = 'Active' 
//          AND e.is_deleted = false
//        LIMIT 1`,
//       [employeeId, org_id]
//     );
    
//     if (!empResult.rows.length) {
//       return res.status(404).json({ 
//         success: false, 
//         message: "Employee not found or not active" 
//       });
//     }

//     const userId = empResult.rows[0].user_id;

//     const result = await pool.query(
//       `SELECT
//          lt.id                          AS type_id,
//          lt.name                        AS type,
//          lt.color,
//          lt.is_paid,
//          lt.default_days_per_year       AS total,
//          COALESCE(lb.used_days, 0)      AS used,
//          (lt.default_days_per_year - COALESCE(lb.used_days, 0)) AS remaining
//        FROM leave_types lt
//        LEFT JOIN leave_balances lb
//          ON lb.leave_type_id = lt.id
//         AND lb.user_id       = $1
//         AND lb.year          = $2
//         AND lb.is_deleted    = false
//        WHERE lt.org_id = $3 AND lt.is_deleted = false
//        ORDER BY lt.name`,
//       [userId, currentYear, org_id]
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET LEAVE BALANCE BY EMPLOYEE ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
/* ===========================
   GET LEAVE BALANCE BY EMPLOYEE
=========================== */
const getLeaveBalanceByEmployee = async (req, res) => {
  try {
    const { employeeId } = req.params;
    const org_id = req.session.user.orgId;
    const currentYear = new Date().getFullYear();

    console.log("=== GET LEAVE BALANCE BY EMPLOYEE DEBUG ===");
    console.log("Employee ID:", employeeId);
    console.log("Org ID:", org_id);

    // Ensure we get active employees
    const empResult = await pool.query(
      `SELECT u.id as user_id
       FROM employees e
       JOIN users u ON u.email = e.email AND u.org_id = e.org_id
       WHERE e.id = $1::integer 
         AND e.org_id = $2::integer 
         AND e.status = 'Active' 
         AND e.is_deleted = false
       LIMIT 1`,
      [employeeId, org_id]
    );
    
    if (!empResult.rows.length) {
      return res.status(404).json({ 
        success: false, 
        message: "Employee not found or not active" 
      });
    }

    const userId = empResult.rows[0].user_id;
    console.log("Found user_id:", userId);

    const result = await pool.query(
      `SELECT
         lt.id AS type_id,
         lt.name AS type,
         lt.color,
         lt.is_paid,
         lt.default_days_per_year AS total,
         COALESCE(lb.used_days, 0) AS used,
         (lt.default_days_per_year - COALESCE(lb.used_days, 0)) AS remaining
       FROM leave_types lt
       LEFT JOIN leave_balances lb
         ON lb.leave_type_id = lt.id
        AND lb.user_id = $1::integer
        AND lb.year = $2::integer
        AND lb.is_deleted = false
       WHERE lt.org_id = $3::integer AND lt.is_deleted = false
       ORDER BY lt.name`,
      [userId, currentYear, org_id]
    );

    console.log(`Found ${result.rows.length} leave balances`);
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET LEAVE BALANCE BY EMPLOYEE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  getLeaveTypes,
  getMyLeaveBalances,
  getLeaveRequests,
  applyLeave,
  assignCoverage,
  forwardToHR,
  forwardBulkToHR,
  approveRequest,
  rejectRequest,
  withdrawRequest,
  getTeamMembers,
  getLeaveBalanceByEmployee,
  getEmployeeLeaveBalance,
  getActiveLeaveCoverages ,
};