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

/**
 * ─── DB COMPATIBILITY LAYER ───────────────────────────────────────────────────
 */
const dbQuery = (sql, params) => {
  if (typeof pool === "function") return pool(sql, params);
  if (pool && typeof pool.query === "function") return pool.query(sql, params);
  throw new Error("db.js export is not a recognised pool type");
};

const withTransaction = async (fn) => {
  if (pool && typeof pool.connect === "function") {
    const client = await pool.connect();
    try {
      await client.query("BEGIN");
      const result = await fn((sql, params) => client.query(sql, params));
      await client.query("COMMIT");
      return result;
    } catch (err) {
      await client.query("ROLLBACK");
      throw err;
    } finally {
      client.release();
    }
  }

  try {
    await dbQuery("BEGIN");
    const result = await fn(dbQuery);
    await dbQuery("COMMIT");
    return result;
  } catch (err) {
    try { await dbQuery("ROLLBACK"); } catch (_) { }
    throw err;
  }
};

// Helper function to add days
const addDays = (date, days) => {
  const result = new Date(date);
  result.setDate(result.getDate() + days);
  return result;
};

/* ===========================
   ✅ GET SHIFT PLANS STATISTICS
=========================== */
const getShiftPlansStats = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    
    if (!org_id) {
      return res.status(400).json({ success: false, message: "Organization ID not found" });
    }

    const result = await dbQuery(
      `SELECT sp.* 
       FROM shift_plans sp
       WHERE sp.org_id = $1 
         AND sp.is_deleted = false`,
      [org_id]
    );
    
    const plans = result.rows;
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    
    let active = 0;
    let upcoming = 0;
    let completed = 0;
    
    plans.forEach(plan => {
      const start = new Date(plan.start_date);
      start.setHours(0, 0, 0, 0);
      const totalDays = plan.total_days || 7;
      const end = addDays(start, totalDays - 1);
      end.setHours(23, 59, 59, 999);
      
      if (today < start) {
        upcoming++;
      } else if (today > end) {
        completed++;
      } else {
        active++;
      }
    });
    
    res.json({
      success: true,
      data: {
        total: plans.length,
        active,
        upcoming,
        completed
      }
    });
  } catch (err) {
    console.error("GET SHIFT PLANS STATS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET ALL SHIFT PLANS (including pattern plans)
=========================== */
const getShiftPlans = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const { page = 1, limit = 10, status, search, plan_type } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);

    let whereClause = `WHERE sp.org_id = $1 AND sp.is_deleted = false`;
    const queryParams = [org_id];
    let paramIndex = 2;

    // Filter by plan type (pattern vs date-based)
    if (plan_type === 'pattern') {
      // Pattern plans have is_pattern = true OR no start_date logic
      whereClause += ` AND (sp.is_pattern = true OR sp.start_date IS NULL)`;
    } else if (plan_type === 'dated') {
      whereClause += ` AND (sp.is_pattern = false OR sp.start_date IS NOT NULL)`;
    }

    if (search) {
      whereClause += ` AND (sp.plan_name ILIKE $${paramIndex} OR sp.id::text ILIKE $${paramIndex})`;
      queryParams.push(`%${search}%`);
      paramIndex++;
    }

    const countResult = await dbQuery(
      `SELECT COUNT(*) AS total FROM shift_plans sp ${whereClause}`,
      queryParams
    );
    const totalRecords = parseInt(countResult.rows[0]?.total || 0);

    const result = await dbQuery(
      `SELECT sp.*,
         CONCAT(e.first_name, ' ', e.last_name) AS created_by_name
       FROM shift_plans sp
       LEFT JOIN employees e ON sp.created_by = e.id
       ${whereClause}
       ORDER BY sp.created_at DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      [...queryParams, parseInt(limit), offset]
    );

    let plans = result.rows;
    
    // Only filter by date status for non-pattern plans
    if (status && status !== 'all') {
      const today = new Date();
      today.setHours(0, 0, 0, 0);
      plans = plans.filter(plan => {
        // Pattern plans don't have dates, so they're always "active"
        if (plan.is_pattern) {
          return status === 'active';
        }
        
        if (!plan.start_date) return status === 'active';
        
        const start = new Date(plan.start_date);
        start.setHours(0, 0, 0, 0);
        const totalDays = plan.total_days || 7;
        const end = addDays(start, totalDays - 1);
        end.setHours(23, 59, 59, 999);
        
        if (status === 'active') return today >= start && today <= end;
        if (status === 'upcoming') return today < start;
        if (status === 'completed') return today > end;
        return true;
      });
    }

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

/* ===========================
   ✅ UPDATE SHIFT PLAN
=========================== */
const updateShiftPlan = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;
    const { plan_name, start_date, total_days, cycle_length } = req.body;

    if (!org_id) {
      return res.status(400).json({ success: false, message: "Session expired" });
    }

    const planCheck = await dbQuery(
      `SELECT id FROM shift_plans 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );

    if (!planCheck.rows.length) {
      return res.status(404).json({ success: false, message: "Plan not found" });
    }

    const updates = [];
    const values = [];
    let paramIndex = 1;

    if (plan_name !== undefined) {
      updates.push(`plan_name = $${paramIndex++}`);
      values.push(plan_name);
    }
    if (start_date !== undefined) {
      updates.push(`start_date = $${paramIndex++}`);
      values.push(start_date);
    }
    if (total_days !== undefined) {
      updates.push(`total_days = $${paramIndex++}`);
      values.push(total_days);
    }
    if (cycle_length !== undefined && total_days === undefined) {
      updates.push(`total_days = $${paramIndex++}`);
      values.push(cycle_length);
    }

    if (updates.length === 0) {
      return res.status(400).json({ success: false, message: "No fields to update" });
    }

    updates.push(`updated_by = $${paramIndex++}`);
    values.push(userId || null);
    updates.push(`updated_at = NOW()`);
    values.push(id, org_id);

    const query = `
      UPDATE shift_plans 
      SET ${updates.join(", ")}
      WHERE id = $${paramIndex} AND org_id = $${paramIndex + 1}
      RETURNING *
    `;

    const result = await dbQuery(query, values);
    
    res.json({
      success: true,
      message: "Plan updated successfully",
      data: result.rows[0]
    });
  } catch (err) {
    console.error("UPDATE SHIFT PLAN ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ SAVE SHIFT PLANNER
=========================== */
const saveShiftPlanner = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;
    const { groups, startDate, totalDays = 7, plan_name } = req.body;

    if (!groups || !Array.isArray(groups) || groups.length === 0) {
      return res.status(400).json({ success: false, message: "groups[] is required" });
    }
    if (!startDate) {
      return res.status(400).json({ success: false, message: "startDate is required" });
    }
    if (!org_id) {
      return res.status(400).json({ success: false, message: "Session expired" });
    }

    const { plan, groupIdMap } = await withTransaction(async (query) => {
      const planResult = await query(
        `INSERT INTO shift_plans 
           (plan_name, start_date, total_days, org_id, created_by, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
         RETURNING *`,
        [plan_name || null, startDate, totalDays, org_id, userId || null]
      );
      const plan = planResult.rows[0];
      const groupIdMap = {};

      for (const group of groups) {
        if (!group.name || !Array.isArray(group.members)) continue;

        const existing = await query(
          `SELECT id FROM shift_groups 
           WHERE group_name = $1 AND org_id = $2 AND is_deleted = false
           LIMIT 1`,
          [group.name.trim(), org_id]
        );

        let groupId;

        if (existing.rows.length > 0) {
          groupId = existing.rows[0].id;
        } else {
          for (const emp of group.members) {
            if (!emp.employee_id || isNaN(Number(emp.employee_id))) {
              throw new Error(`Invalid employee_id: ${emp.employee_id}`);
            }
            const empCheck = await query(
              `SELECT id FROM employees 
               WHERE id = $1 AND status = 'Active' AND is_deleted = false`,
              [emp.employee_id]
            );
            if (empCheck.rows.length === 0) {
              throw new Error(`Employee ${emp.employee_id} not found or inactive`);
            }
          }

          const grpResult = await query(
            `INSERT INTO shift_groups 
               (group_name, org_id, created_by, created_at, updated_at)
             VALUES ($1, $2, $3, NOW(), NOW())
             RETURNING id`,
            [group.name.trim(), org_id, userId || null]
          );
          groupId = grpResult.rows[0].id;

          for (const emp of group.members) {
            await query(
              `INSERT INTO shift_group_members 
                 (group_id, employee_id, org_id, created_at)
               VALUES ($1, $2, $3, NOW())
               ON CONFLICT (group_id, employee_id) DO NOTHING`,
              [groupId, emp.employee_id, org_id]
            );
          }
        }

        groupIdMap[group.name] = groupId;
      }

      return { plan, groupIdMap };
    });

    return res.status(201).json({
      success: true,
      message: "Shift planner saved successfully",
      data: {
        plan_id: plan.id,
        plan_name: plan.plan_name,
        start_date: plan.start_date,
        total_days: plan.total_days,
        groups: Object.entries(groupIdMap).map(([name, id]) => ({ name, id })),
      },
    });
  } catch (error) {
    console.error("SAVE SHIFT PLANNER ERROR:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET SHIFT PLAN BY ID - WITH DESIGNATIONS
=========================== */
const getShiftPlanById = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user?.orgId;

    const planResult = await dbQuery(
      `SELECT * FROM shift_plans WHERE id = $1::integer AND org_id = $2::integer AND is_deleted = false`,
      [id, org_id]
    );
    if (!planResult.rows.length) {
      return res.status(404).json({ success: false, message: "Shift plan not found" });
    }
    const plan = planResult.rows[0];

    const groupsResult = await dbQuery(
      `SELECT sg.id, sg.group_name,
         COALESCE(
           (SELECT json_agg(
             json_build_object(
               'employee_id', e.employee_id,
               'id', e.id,
               'first_name', e.first_name,
               'last_name', e.last_name,
               'designation_name', COALESCE(d.designation_name, ''),
               'profile_image', e.profile_image
             ) ORDER BY e.first_name
           )
           FROM shift_group_members sgm
           INNER JOIN employees e ON e.id = sgm.employee_id
           LEFT JOIN designations d ON d.id::text = e.designation_id AND d.org_id = e.org_id AND d.is_deleted = false
           WHERE sgm.group_id = sg.id
             AND e.status = 'Active' 
             AND e.is_deleted = false
           ),
           '[]'::json
         ) AS members
       FROM shift_groups sg
       WHERE sg.org_id = $1 AND sg.is_deleted = false
       ORDER BY sg.created_at DESC`,
      [org_id]
    );
    
    return res.status(200).json({
      success: true,
      data: { ...plan, groups: groupsResult.rows },
    });
  } catch (err) {
    console.error("GET SHIFT PLAN BY ID ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ DELETE SHIFT PLAN
=========================== */
const deleteShiftPlan = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;

    const result = await dbQuery(
      `UPDATE shift_plans
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2::integer AND org_id = $3::integer AND is_deleted = false
       RETURNING id`,
      [userId || null, id, org_id]
    );

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

    return res.json({ success: true, message: "Shift plan deleted successfully" });
  } catch (err) {
    console.error("DELETE SHIFT PLAN ERROR:", err);
    return res.status(500).json({ success: false, message: "Delete failed" });
  }
};

/* ===========================
   ✅ CREATE GROUP
=========================== */
const createGroup = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;
    const { name, members } = req.body;

    if (!name?.trim()) {
      return res.status(400).json({ success: false, message: "Group name is required" });
    }
    if (!Array.isArray(members) || members.length === 0) {
      return res.status(400).json({ success: false, message: "At least one member is required" });
    }
    if (!org_id) {
      return res.status(400).json({ success: false, message: "Session expired" });
    }

    const dupCheck = await dbQuery(
      `SELECT id FROM shift_groups WHERE group_name = $1 AND org_id = $2 AND is_deleted = false`,
      [name.trim(), org_id]
    );
    if (dupCheck.rows.length > 0) {
      return res.status(409).json({ success: false, message: `Group "${name}" already exists` });
    }

    const group = await withTransaction(async (query) => {
      const grpResult = await query(
        `INSERT INTO shift_groups (group_name, org_id, created_by, created_at, updated_at)
         VALUES ($1, $2, $3, NOW(), NOW())
         RETURNING *`,
        [name.trim(), org_id, userId || null]
      );
      const group = grpResult.rows[0];

      for (const emp of members) {
        if (!emp.employee_id) {
          throw new Error(`Employee ID is required`);
        }

        const empCheck = await query(
          `SELECT id, employee_id, employee_code, first_name, last_name, status, is_deleted 
           FROM employees 
           WHERE id = $1 AND status = 'Active' AND is_deleted = false`,
          [emp.employee_id]
        );

        if (empCheck.rows.length === 0) {
          throw new Error(`Employee with ID ${emp.employee_id} not found or is not active`);
        }
      }

      for (const emp of members) {
        await query(
          `INSERT INTO shift_group_members (group_id, employee_id, org_id, created_at)
           VALUES ($1, $2, $3, NOW())
           ON CONFLICT (group_id, employee_id) DO NOTHING`,
          [group.id, emp.employee_id, org_id]
        );
      }

      return group;
    });

    const completeGroup = await dbQuery(
      `SELECT sg.id, sg.group_name,
         COALESCE(
           (SELECT json_agg(
             json_build_object(
               'employee_id', e.employee_id,
               'id', e.id,
               'employee_code', e.employee_code,
               'first_name', e.first_name,
               'last_name', e.last_name,
               'designation_name', COALESCE(d.designation_name, ''),
               'profile_image', e.profile_image
             ) ORDER BY e.first_name
           )
           FROM shift_group_members sgm
           INNER JOIN employees e ON e.id = sgm.employee_id
           LEFT JOIN designations d ON d.id::text = e.designation_id AND d.org_id = e.org_id AND d.is_deleted = false
           WHERE sgm.group_id = sg.id
             AND e.status = 'Active' 
             AND e.is_deleted = false
           ),
           '[]'::json
         ) AS members
       FROM shift_groups sg
       WHERE sg.id = $1`,
      [group.id]
    );

    return res.status(201).json({
      success: true,
      message: `Group "${group.group_name}" created successfully`,
      data: completeGroup.rows[0] || { ...group, members },
    });
  } catch (error) {
    console.error("CREATE GROUP ERROR:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET ALL GROUPS - WITH DESIGNATIONS
=========================== */
const getGroups = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;

    const orgIdInt = parseInt(org_id);
    if (isNaN(orgIdInt)) {
      return res.status(400).json({ success: false, message: "Invalid organization ID" });
    }

    // First, get all groups
    const groupsResult = await dbQuery(
      `SELECT sg.id, sg.group_name, sg.created_at
       FROM shift_groups sg
       WHERE sg.org_id = $1 AND sg.is_deleted = false
       ORDER BY sg.created_at DESC`,
      [orgIdInt]
    );

    // For each group, fetch members separately
    const groupsWithMembers = [];
    
    for (const group of groupsResult.rows) {
      // Fetch members for this group
      const membersResult = await dbQuery(
        `SELECT 
           e.id,
           e.employee_id,
           e.employee_code,
           e.first_name,
           e.last_name,
           COALESCE(d.designation_name, '') AS designation_name,
           e.profile_image,
           e.email,
           e.phone,
           e.status
         FROM shift_group_members sgm
         INNER JOIN employees e ON e.id = sgm.employee_id
         LEFT JOIN designations d ON d.id::text = e.designation_id AND d.org_id = e.org_id AND d.is_deleted = false
         WHERE sgm.group_id = $1
           AND e.status = 'Active' 
           AND e.is_deleted = false
         ORDER BY e.first_name`,
        [group.id]
      );

      groupsWithMembers.push({
        id: group.id,
        group_name: group.group_name,
        name: group.group_name,
        created_at: group.created_at,
        members: membersResult.rows.map(member => ({
          id: member.id,
          employee_id: member.employee_id,
          employee_code: member.employee_code,
          first_name: member.first_name,
          last_name: member.last_name,
          designation_name: member.designation_name || '',
          profile_image: member.profile_image,
          email: member.email,
          phone: member.phone,
          status: member.status
        }))
      });
    }

    return res.json({
      success: true,
      data: groupsWithMembers,
      totalRecords: groupsWithMembers.length,
    });
  } catch (err) {
    console.error("GET GROUPS ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

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

    const result = await dbQuery(
      `UPDATE shift_groups
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2::integer AND org_id = $3::integer AND is_deleted = false
       RETURNING id`,
      [userId || null, id, org_id]
    );

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

    return res.json({ success: true, message: "Group deleted successfully" });
  } catch (err) {
    console.error("DELETE GROUP ERROR:", err);
    return res.status(500).json({ success: false, message: "Delete failed" });
  }
};

/* ===========================
   ✅ GET PLAN CELLS
=========================== */
const getPlanCells = async (req, res) => {
  try {
    const { shift_id, date, plan_id } = req.query;
    const org_id = req.session.user?.orgId;

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

    let query = `
      SELECT 
        spc.id,
        spc.plan_id,
        spc.shift_id,
        spc.group_id,
        spc.assign_date,
        spc.col_index,
        spc.org_id,
        spc.created_at,
        spc.updated_at,
        sg.group_name,
        s.shift_name,
        s.start_time,
        s.end_time
      FROM shift_plan_cells spc
      LEFT JOIN shift_groups sg ON sg.id = spc.group_id AND sg.is_deleted = false
      LEFT JOIN shifts s ON s.id = spc.shift_id AND s.is_deleted = false
      WHERE spc.org_id = $1
    `;

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

    if (shift_id) {
      query += ` AND spc.shift_id = $${paramIndex++}`;
      params.push(shift_id);
    }

    if (plan_id) {
      query += ` AND spc.plan_id = $${paramIndex++}`;
      params.push(plan_id);
    }

    if (date) {
      query += ` AND spc.assign_date = $${paramIndex++}`;
      params.push(date);
    }

    query += ` ORDER BY spc.assign_date, spc.col_index`;

    const result = await dbQuery(query, params);

    return res.json({
      success: true,
      data: result.rows,
      count: result.rows.length
    });
  } catch (error) {
    console.error("getPlanCells error:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ SAVE PLAN CELLS
=========================== */
const savePlanCells = async (req, res) => {
  try {
    const { plan_id } = req.params;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;
    const { cells, plan_name, startDate, totalDays } = req.body;

    if (!org_id) {
      return res.status(400).json({ success: false, message: "Session expired" });
    }

    let planCheck = await dbQuery(
      `SELECT id FROM shift_plans 
       WHERE id = $1::integer AND org_id = $2::integer AND is_deleted = false`,
      [plan_id, org_id]
    );

    let actualPlanId = plan_id;

    if (!planCheck.rows.length) {
      const planName = plan_name || `Shift Plan ${new Date().toLocaleDateString()}`;
      const start = startDate || new Date().toISOString().split('T')[0];
      const days = totalDays || 7;

      const newPlan = await dbQuery(
        `INSERT INTO shift_plans 
           (plan_name, start_date, total_days, org_id, created_by, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
         RETURNING id`,
        [planName, start, days, org_id, userId || null]
      );
      actualPlanId = newPlan.rows[0].id;
    }

    if (!cells || !Array.isArray(cells) || cells.length === 0) {
      return res.status(400).json({ success: false, message: "cells[] is required" });
    }

    let insertedCount = 0;
    let skippedCount = 0;

    await dbQuery(
      `DELETE FROM shift_plan_cells WHERE plan_id = $1 AND org_id = $2`,
      [actualPlanId, org_id]
    );

    for (let i = 0; i < cells.length; i++) {
      const cell = cells[i];
      const { shift_id, group_id, assign_date, col_index, day_index } = cell;
      
      const colIdx = col_index !== undefined ? col_index : day_index;

      if (!group_id || colIdx === undefined) {
        skippedCount++;
        continue;
      }

      const groupCheck = await dbQuery(
        `SELECT id FROM shift_groups WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [group_id, org_id]
      );

      if (!groupCheck.rows.length) {
        skippedCount++;
        continue;
      }

      try {
        const result = await dbQuery(
          `INSERT INTO shift_plan_cells 
             (plan_id, shift_id, group_id, col_index, assign_date, org_id, created_at, updated_at)
           VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW())
           RETURNING id`,
          [actualPlanId, shift_id || null, group_id, colIdx, assign_date || null, org_id]
        );
        
        if (result.rows[0]?.id) {
          insertedCount++;
        }
      } catch (err) {
        console.error(`Cell ${i} error:`, err.message);
        skippedCount++;
      }
    }

    return res.status(201).json({
      success: true,
      message: `${insertedCount} cell(s) inserted, ${skippedCount} skipped`,
      data: {
        plan_id: actualPlanId,
        inserted: insertedCount,
        skipped: skippedCount
      },
    });
  } catch (err) {
    console.error("SAVE PLAN CELLS ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET SHIFT PLAN CELLS WITH GROUPS
=========================== */
const getShiftPlanCellsWithGroups = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const { shift_id, plan_id, date } = req.query;

    if (!org_id) {
      return res.status(400).json({ success: false, message: "Session expired" });
    }

    let query = `
      SELECT 
        spc.id,
        spc.plan_id,
        spc.shift_id,
        spc.group_id,
        spc.assign_date,
        spc.col_index,
        spc.org_id,
        spc.created_at,
        spc.updated_at,
        sg.group_name,
        sg.created_at as group_created_at,
        COALESCE(
          (SELECT json_agg(
            json_build_object(
              'employee_id', e.employee_id,
              'id', e.id,
              'employee_code', e.employee_code,
              'first_name', e.first_name,
              'last_name', e.last_name,
              'designation_name', COALESCE(d.designation_name, ''),
              'profile_image', e.profile_image,
              'email', e.email,
              'phone', e.phone,
              'status', e.status
            ) ORDER BY e.first_name
          )
          FROM shift_group_members sgm
          INNER JOIN employees e ON e.id = sgm.employee_id
          LEFT JOIN designations d ON e.designation_id = d.id
          WHERE sgm.group_id = sg.id
            AND e.status = 'Active' 
            AND e.is_deleted = false
          ),
          '[]'::json
        ) AS members
      FROM shift_plan_cells spc
      LEFT JOIN shift_groups sg ON spc.group_id = sg.id AND sg.is_deleted = false
      WHERE spc.org_id = $1
    `;

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

    if (shift_id) {
      query += ` AND spc.shift_id = $${paramIndex++}`;
      params.push(shift_id);
    }

    if (plan_id) {
      query += ` AND spc.plan_id = $${paramIndex++}`;
      params.push(plan_id);
    }

    if (date) {
      query += ` AND spc.assign_date = $${paramIndex++}`;
      params.push(date);
    }

    query += ` GROUP BY spc.id, sg.id, sg.group_name
               ORDER BY spc.assign_date, spc.col_index`;

    const result = await dbQuery(query, params);

    const groupedByShift = {};
    const groupedByDate = {};

    result.rows.forEach(cell => {
      if (!groupedByShift[cell.shift_id]) {
        groupedByShift[cell.shift_id] = [];
      }
      groupedByShift[cell.shift_id].push(cell);

      if (!groupedByDate[cell.assign_date]) {
        groupedByDate[cell.assign_date] = [];
      }
      groupedByDate[cell.assign_date].push(cell);
    });

    return res.json({
      success: true,
      data: result.rows,
      grouped_by_shift: groupedByShift,
      grouped_by_date: groupedByDate,
      total_records: result.rows.length,
      summary: {
        unique_shifts: Object.keys(groupedByShift).length,
        unique_dates: Object.keys(groupedByDate).length,
        total_groups: new Set(result.rows.map(r => r.group_id)).size
      }
    });
  } catch (err) {
    console.error("GET SHIFT PLAN CELLS WITH GROUPS ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET GROUPS FOR PLAN
=========================== */
const getGroupsForPlan = async (req, res) => {
  try {
    const { plan_id } = req.params;
    const org_id = req.session.user?.orgId;

    let resolvedPlanId = plan_id;
    if (plan_id === "latest") {
      const latest = await dbQuery(
        `SELECT id FROM shift_plans WHERE org_id = $1 AND is_deleted = false ORDER BY created_at DESC LIMIT 1`,
        [org_id]
      );
      if (!latest.rows.length) {
        return res.json({ success: true, data: [] });
      }
      resolvedPlanId = latest.rows[0].id;
    }

    const result = await dbQuery(
      `WITH plan_groups AS (
        SELECT DISTINCT sg.id, sg.group_name
        FROM shift_groups sg
        WHERE sg.org_id = $2 AND sg.is_deleted = false
        ${resolvedPlanId !== "all" ? `
          AND EXISTS (
            SELECT 1 FROM shift_plan_cells spc 
            WHERE spc.group_id = sg.id AND spc.plan_id = $1
          )
        ` : ''}
      )
      SELECT 
        pg.id,
        pg.group_name,
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'employee_id', e.employee_id,
                'id', e.id,
                'employee_code', e.employee_code,
                'first_name', e.first_name,
                'last_name', e.last_name,
                'designation_name', COALESCE(d.designation_name, ''),
                'profile_image', e.profile_image
              ) ORDER BY e.first_name
            )
            FROM shift_group_members sgm
            JOIN employees e ON e.id = sgm.employee_id
            LEFT JOIN designations d ON e.designation_id = d.id
            WHERE sgm.group_id = pg.id
              AND e.status = 'Active' 
              AND e.is_deleted = false
          ),
          '[]'::json
        ) AS members
      FROM plan_groups pg
      ORDER BY pg.group_name`,
      resolvedPlanId !== "all" ? [resolvedPlanId, org_id] : [org_id]
    );

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

/* ===========================
   ✅ GET GROUP FOR SHIFT DATE
=========================== */
const getGroupForShiftDate = async (req, res) => {
  try {
    const { shift_id, date } = req.query;
    const org_id = req.session.user?.orgId;

    if (!shift_id || !date) {
      return res.status(400).json({ 
        success: false, 
        message: "shift_id and date are required" 
      });
    }

    if (!org_id) {
      return res.status(400).json({ 
        success: false, 
        message: "Organization ID not found" 
      });
    }

    console.log(`Looking for group: shift_id=${shift_id}, date=${date}, org_id=${org_id}`);

    const result = await dbQuery(
      `SELECT spc.group_id, sg.group_name, spc.plan_id, spc.col_index, spc.assign_date
       FROM shift_plan_cells spc
       LEFT JOIN shift_groups sg ON sg.id = spc.group_id AND sg.is_deleted = false
       WHERE spc.shift_id = $1 
         AND spc.assign_date = $2 
         AND spc.org_id = $3
         AND spc.is_deleted = false
       LIMIT 1`,
      [parseInt(shift_id), date, org_id]
    );

    if (result.rows.length === 0) {
      console.log(`No group found for shift ${shift_id} on ${date}`);
      return res.json({ 
        success: true, 
        data: null, 
        message: `No group assigned to shift ${shift_id} on ${date}` 
      });
    }

    console.log(`Found group ${result.rows[0].group_id} for shift ${shift_id} on ${date}`);

    return res.json({
      success: true,
      data: {
        group_id: result.rows[0].group_id,
        group_name: result.rows[0].group_name,
        plan_id: result.rows[0].plan_id,
        col_index: result.rows[0].col_index,
        assign_date: result.rows[0].assign_date
      }
    });
  } catch (err) {
    console.error("GET GROUP FOR SHIFT DATE ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET GROUP BY SHIFT AND DATE (Alias)
=========================== */
const getGroupByShiftAndDate = getGroupForShiftDate;


/* ===========================
   ✅ GET PATTERN PLAN FOR EDIT - COMPLETE WORKING VERSION
=========================== */
const getPatternPlanForEdit = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user?.orgId;

    if (!org_id) {
      return res.status(400).json({ success: false, message: "Organization ID not found" });
    }

    // Get plan details
    const planResult = await dbQuery(
      `SELECT * FROM shift_plans 
       WHERE id = $1::integer AND org_id = $2::integer AND is_deleted = false`,
      [id, org_id]
    );
    
    if (!planResult.rows.length) {
      return res.status(404).json({ success: false, message: "Plan not found" });
    }
    
    const plan = planResult.rows[0];
    const cycleLength = plan.total_days || 7;

    // Get all groups with their members - FIXED: Removed reference to e.designation
    const groupsResult = await dbQuery(
      `SELECT sg.id, sg.group_name,
         COALESCE(
           (SELECT json_agg(
             json_build_object(
               'employee_id', e.employee_id,
               'id', e.id,
               'first_name', e.first_name,
               'last_name', e.last_name,
               'designation_name', COALESCE(d.designation_name, ''),
               'profile_image', e.profile_image,
               'email', COALESCE(e.email, ''),
               'phone', COALESCE(e.phone, ''),
               'status', COALESCE(e.status, 'Active')
             ) ORDER BY e.first_name
           )
           FROM shift_group_members sgm
           INNER JOIN employees e ON e.id = sgm.employee_id
           LEFT JOIN designations d ON d.id::text = e.designation_id AND d.org_id = e.org_id AND d.is_deleted = false
           WHERE sgm.group_id = sg.id
             AND e.status = 'Active' 
             AND e.is_deleted = false
           ),
           '[]'::json
         ) AS members
       FROM shift_groups sg
       WHERE sg.org_id = $1 AND sg.is_deleted = false
       ORDER BY sg.group_name`,
      [org_id]
    );

    // Get all shifts
    const shiftsResult = await dbQuery(
      `SELECT id, shift_name, start_time, end_time 
       FROM shifts 
       WHERE org_id = $1 AND is_deleted = false
       ORDER BY start_time`,
      [org_id]
    );
    const shifts = shiftsResult.rows;

    // Get all cells for this plan
    const cellsResult = await dbQuery(
      `SELECT spc.shift_id, spc.group_id, spc.col_index, spc.assign_date
       FROM shift_plan_cells spc
       WHERE spc.plan_id = $1 AND spc.org_id = $2`,
      [id, org_id]
    );

    // Build grid from cells
    const grid = {};
    cellsResult.rows.forEach(cell => {
      const key = `${cell.shift_id}-${cell.col_index}`;
      const group = groupsResult.rows.find(g => g.id === cell.group_id);
      if (group) {
        grid[key] = {
          groupName: group.group_name,
          groupId: cell.group_id,
          members: group.members || []
        };
      }
    });

    // Calculate off days for each group
    const offDayMap = {};
    groupsResult.rows.forEach((group, groupIdx) => {
      let foundOffDay = null;
      for (let day = 0; day < cycleLength; day++) {
        let hasWork = false;
        for (const shift of shifts) {
          const key = `${shift.id}-${day}`;
          if (grid[key] && grid[key].groupName === group.group_name) {
            hasWork = true;
            break;
          }
        }
        if (!hasWork) {
          foundOffDay = day % 7;
          break;
        }
      }
      offDayMap[group.group_name] = foundOffDay !== null ? foundOffDay : (groupIdx % 7);
    });

    // Transform groups to format expected by frontend
    const transformedGroups = groupsResult.rows.map(group => ({
      id: group.id,
      name: group.group_name,
      group_name: group.group_name,
      members: group.members || []
    }));

    // Debug log - check if designation_name is being returned
    console.log("=== BACKEND RESPONSE DEBUG ===");
    console.log("Total groups:", transformedGroups.length);
    if (transformedGroups.length > 0 && transformedGroups[0].members.length > 0) {
      console.log("First member full data:", JSON.stringify(transformedGroups[0].members[0], null, 2));
      console.log("First member designation_name:", transformedGroups[0].members[0].designation_name);
    } else {
      console.log("No members found in groups");
    }

    res.json({
      success: true,
      data: {
        plan: {
          id: plan.id,
          plan_name: plan.plan_name,
          start_date: plan.start_date,
          total_days: plan.total_days,
          cycle_length: plan.total_days,
          created_at: plan.created_at
        },
        groups: transformedGroups,
        shifts: shifts,
        grid: grid,
        cycleLength: cycleLength,
        offDayMap: offDayMap,
        cells: cellsResult.rows
      }
    });
  } catch (err) {
    console.error("GET PATTERN PLAN FOR EDIT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
/* ===========================
   ✅ UPDATE PATTERN PLAN
=========================== */
const updatePatternPlan = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id } = req.params;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;
    const { plan_name, cycle_length, groups, grid } = req.body;

    if (!org_id) {
      return res.status(400).json({ success: false, message: "Session expired" });
    }

    const planResult = await client.query(
      `SELECT start_date, total_days FROM shift_plans 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );

    if (!planResult.rows.length) {
      return res.status(404).json({ success: false, message: "Plan not found" });
    }

    const plan = planResult.rows[0];
    const startDate = new Date(plan.start_date);
    const totalDays = cycle_length || plan.total_days || 7;

    await client.query("BEGIN");

    await client.query(
      `UPDATE shift_plans 
       SET plan_name = $1, 
           total_days = $2, 
           updated_by = $3, 
           updated_at = NOW()
       WHERE id = $4 AND org_id = $5`,
      [plan_name, totalDays, userId, id, org_id]
    );

    await client.query(
      `DELETE FROM shift_plan_cells WHERE plan_id = $1 AND org_id = $2`,
      [id, org_id]
    );

    const groupIdMap = {};
    for (const group of groups) {
      const groupName = group.group_name || group.name;
      if (!groupName) continue;
      
      const existing = await client.query(
        `SELECT id FROM shift_groups 
         WHERE group_name = $1 AND org_id = $2 AND is_deleted = false`,
        [groupName, org_id]
      );

      if (existing.rows.length > 0) {
        groupIdMap[groupName] = existing.rows[0].id;
      } else {
        const newGroup = await client.query(
          `INSERT INTO shift_groups (group_name, org_id, created_by, created_at, updated_at)
           VALUES ($1, $2, $3, NOW(), NOW())
           RETURNING id`,
          [groupName, org_id, userId]
        );
        groupIdMap[groupName] = newGroup.rows[0].id;
      }
    }

    let insertedCount = 0;
    for (const [key, cellData] of Object.entries(grid)) {
      if (!cellData || !cellData.groupName) continue;
      
      const [shiftId, colIndex] = key.split('-');
      const groupId = groupIdMap[cellData.groupName];
      
      if (!groupId) {
        console.error(`Group ID not found for ${cellData.groupName}`);
        continue;
      }

      const assignDate = new Date(startDate);
      assignDate.setDate(startDate.getDate() + parseInt(colIndex));
      const dateStr = assignDate.toISOString().split('T')[0];

      await client.query(
        `INSERT INTO shift_plan_cells 
           (plan_id, shift_id, group_id, assign_date, col_index, org_id, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW())`,
        [id, parseInt(shiftId), groupId, dateStr, parseInt(colIndex), org_id]
      );
      insertedCount++;
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `Plan updated successfully with ${insertedCount} assignments`,
      data: { plan_id: id, insertedCount }
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("UPDATE PATTERN PLAN ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};
/* ===========================
   ✅ SAVE REPEATING PATTERN PLAN - DEBUG VERSION
=========================== */
const saveRepeatingPattern = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;
    const { plan_name, cycle_length = 7, groups, grid, offDayMap } = req.body;

    console.log("=== SAVE REPEATING PATTERN DEBUG ===");
    console.log("org_id:", org_id);
    console.log("userId:", userId);
    console.log("plan_name:", plan_name);
    console.log("cycle_length:", cycle_length);
    console.log("groups count:", groups?.length);
    console.log("grid keys:", Object.keys(grid || {}).length);
    console.log("offDayMap:", offDayMap);

    // Validation
    if (!groups || !Array.isArray(groups) || groups.length === 0) {
      return res.status(400).json({ success: false, message: "groups[] is required" });
    }
    if (!org_id) {
      return res.status(400).json({ success: false, message: "Session expired" });
    }

    const client = await pool.connect();
    
    try {
      await client.query("BEGIN");

      // Create the plan
      const today = new Date().toISOString().split('T')[0];
      const planResult = await client.query(
        `INSERT INTO shift_plans 
           (plan_name, start_date, total_days, org_id, created_by, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
         RETURNING id`,
        [plan_name || `Pattern ${new Date().toLocaleDateString()}`, today, cycle_length, org_id, userId || null]
      );
      
      const planId = planResult.rows[0].id;
      console.log(`✅ Created plan with ID: ${planId}`);

      // Build group ID mapping
      const groupIdMap = {};
      for (const group of groups) {
        const groupName = group.name || group.group_name;
        if (!groupName) {
          console.log("Skipping group with no name:", group);
          continue;
        }
        
        console.log(`Processing group: ${groupName}`);
        
        // Check if group already exists
        const existing = await client.query(
          `SELECT id FROM shift_groups 
           WHERE group_name = $1 AND org_id = $2 AND is_deleted = false`,
          [groupName.trim(), org_id]
        );

        if (existing.rows.length > 0) {
          groupIdMap[groupName] = existing.rows[0].id;
          console.log(`  Using existing group ID: ${existing.rows[0].id}`);
        } else {
          // Create new group
          const newGroup = await client.query(
            `INSERT INTO shift_groups (group_name, org_id, created_by, created_at, updated_at)
             VALUES ($1, $2, $3, NOW(), NOW())
             RETURNING id`,
            [groupName.trim(), org_id, userId || null]
          );
          groupIdMap[groupName] = newGroup.rows[0].id;
          console.log(`  Created new group ID: ${newGroup.rows[0].id}`);

          // Add members to new group
          const members = group.members || [];
          for (const member of members) {
            const employeeId = member.id || member.employee_id;
            if (employeeId) {
              await client.query(
                `INSERT INTO shift_group_members (group_id, employee_id, org_id, created_at)
                 VALUES ($1, $2, $3, NOW())
                 ON CONFLICT (group_id, employee_id) DO NOTHING`,
                [newGroup.rows[0].id, employeeId, org_id]
              );
              console.log(`    Added member: ${employeeId}`);
            }
          }
        }
      }

      // Get all shifts
      const shiftsResult = await client.query(
        `SELECT id, shift_name FROM shifts WHERE org_id = $1 AND is_deleted = false`,
        [org_id]
      );
      console.log(`Found ${shiftsResult.rows.length} shifts:`, shiftsResult.rows.map(s => s.shift_name));

      // Insert pattern cells
      let insertedCount = 0;
      let skippedCount = 0;
      
      for (const shift of shiftsResult.rows) {
        console.log(`Processing shift: ${shift.shift_name} (ID: ${shift.id})`);
        
        for (let day = 0; day < cycle_length; day++) {
          const key = `${shift.id}-${day}`;
          const cellData = grid[key];
          
          if (cellData && cellData.groupName) {
            const groupId = groupIdMap[cellData.groupName];
            if (groupId) {
              // Calculate date for this day (today + day offset)
              const assignDate = new Date();
              assignDate.setDate(assignDate.getDate() + day);
              const dateStr = assignDate.toISOString().split('T')[0];
              
              console.log(`  Day ${day}: Assigning group ${cellData.groupName} (ID: ${groupId}) on ${dateStr}`);
              
              // Insert using existing schema
              const insertResult = await client.query(
                `INSERT INTO shift_plan_cells 
                   (plan_id, shift_id, group_id, assign_date, col_index, org_id, created_at, updated_at)
                 VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW())
                 RETURNING id`,
                [planId, shift.id, groupId, dateStr, day, org_id]
              );
              
              if (insertResult.rows[0]?.id) {
                insertedCount++;
                console.log(`    ✅ Inserted cell ID: ${insertResult.rows[0].id}`);
              }
            } else {
              console.log(`    ❌ Group ID not found for: ${cellData.groupName}`);
              skippedCount++;
            }
          }
        }
      }

      await client.query("COMMIT");
      console.log(`✅ SUCCESS: Inserted ${insertedCount} cells, skipped ${skippedCount}`);

      res.json({
        success: true,
        message: `Pattern "${plan_name || 'Untitled'}" saved successfully with ${insertedCount} assignments.`,
        data: { 
          plan_id: planId, 
          inserted_count: insertedCount,
          skipped_count: skippedCount
        }
      });
      
    } catch (err) {
      await client.query("ROLLBACK");
      console.error("❌ Transaction error:", err);
      console.error("Error details:", err.message);
      console.error("Stack:", err.stack);
      throw err;
    } finally {
      client.release();
    }
  } catch (error) {
    console.error("❌ SAVE REPEATING PATTERN ERROR:", error);
    console.error("Error stack:", error.stack);
    res.status(500).json({ 
      success: false, 
      message: error.message,
      details: error.stack 
    });
  }
};
/* ===========================
   ✅ PLACEHOLDER FUNCTIONS
=========================== */
const getShiftForDate = async (req, res) => {
  res.json({ success: true, data: null, message: "Not implemented yet" });
};

const getActivePattern = async (req, res) => {
  res.json({ success: true, data: null, message: "Not implemented yet" });
};

// ===========================
// MODULE EXPORTS - FINAL CLEAN VERSION
// ===========================
module.exports = {
  // Statistics and listing
  getShiftPlansStats,
  getShiftPlans,
  
  // Plan CRUD
  getShiftPlanById,
  deleteShiftPlan,
  updateShiftPlan,
  
  // Save operations
  saveShiftPlanner,
  savePlanCells,
  
  // Group operations
  createGroup,
  getGroups,
  deleteGroup,
  getGroupsForPlan,
  
  // Cell operations
  getPlanCells,
  getShiftPlanCellsWithGroups,
  
  // Group lookup by shift/date
  getGroupForShiftDate,
  getGroupByShiftAndDate,
  
  // Pattern plan operations
  getPatternPlanForEdit,
  updatePatternPlan,
  
  // Pattern saving
  saveRepeatingPattern,
  
  // Placeholders
  getShiftForDate,
  getActivePattern
};