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

/* ===========================
   ✅ HELPER: Convert "HH:MM" or "HH:MM:SS" → total minutes
=========================== */
const timeToMinutes = (timeStr) => {
  if (!timeStr) return null;
  const parts = timeStr.toString().split(":");
  if (parts.length < 2) return null;
  const hours = parseInt(parts[0], 10);
  const minutes = parseInt(parts[1], 10);
  if (isNaN(hours) || isNaN(minutes)) return null;
  return hours * 60 + minutes;
};

/* ===========================
   ✅ HELPER: Normalize time to "HH:MM"
=========================== */
const cleanTime = (time) => {
  if (!time) return null;
  const str = time.toString().trim();
  // Already HH:MM
  if (/^\d{2}:\d{2}$/.test(str)) return str;
  // HH:MM:SS
  if (/^\d{2}:\d{2}:\d{2}$/.test(str)) return str.slice(0, 5);
  // ISO datetime
  const date = new Date(str);
  if (!isNaN(date)) return date.toTimeString().slice(0, 5);
  return null;
};

/* ===========================
   ✅ HELPER: Calculate shift hours
   Returns { total_hours, total_break_hours, working_hours } as numeric (2 decimal places)
   All times are "HH:MM" strings.
=========================== */
const calculateShiftHours = ({
  start_time,
  end_time,
  lunch_start,
  lunch_end,
  first_break_start,
  first_break_end,
  second_break_start,
  second_break_end,
}) => {
  const startMin = timeToMinutes(start_time);
  const endMin   = timeToMinutes(end_time);

  if (startMin === null || endMin === null) {
    return { total_hours: null, total_break_hours: null, working_hours: null };
  }

  // Total shift duration (handle overnight shifts — end < start means next day)
  let totalMin = endMin - startMin;
  if (totalMin < 0) totalMin += 24 * 60; // overnight shift

  // Break durations
  let breakMin = 0;

  const addBreak = (s, e) => {
    const sm = timeToMinutes(s);
    const em = timeToMinutes(e);
    if (sm !== null && em !== null && em > sm) {
      breakMin += em - sm;
    }
  };

  addBreak(lunch_start, lunch_end);
  addBreak(first_break_start, first_break_end);
  addBreak(second_break_start, second_break_end);

  const workingMin = Math.max(0, totalMin - breakMin);

  // Convert minutes → hours, round to 2 decimal places
  const toHours = (mins) => parseFloat((mins / 60).toFixed(2));

  return {
    total_hours:       toHours(totalMin),
    total_break_hours: toHours(breakMin),
    working_hours:     toHours(workingMin),
  };
};

/* ===========================
   ✅ CREATE SHIFT
=========================== */
const createShift = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.userId;

  try {
    let {
      shift_name,
      shift_code,
      start_time,
      end_time,
      lunch_start,
      lunch_end,
      first_break_start,
      first_break_end,
      second_break_start,
      second_break_end,
    } = req.body;

    if (!shift_name || !start_time || !end_time || !org_id) {
      return res.status(400).json({
        success: false,
        message: "Shift name, start time, and end time are required",
      });
    }

    // Normalize all times
    start_time         = cleanTime(start_time);
    end_time           = cleanTime(end_time);
    lunch_start        = cleanTime(lunch_start);
    lunch_end          = cleanTime(lunch_end);
    first_break_start  = cleanTime(first_break_start);
    first_break_end    = cleanTime(first_break_end);
    second_break_start = cleanTime(second_break_start);
    second_break_end   = cleanTime(second_break_end);

    if (!start_time || !end_time) {
      return res.status(400).json({ success: false, message: "Invalid time format for start_time or end_time" });
    }

    // Duplicate name check
    const existingShift = await pool.query(
      `SELECT id FROM shifts WHERE shift_name = $1 AND is_deleted = false`,
      [shift_name]
    );
    if (existingShift.rows.length > 0) {
      return res.status(409).json({
        success: false,
        message: `Shift with name "${shift_name}" already exists`,
      });
    }

    // Duplicate code check
    if (shift_code) {
      const existingCode = await pool.query(
        `SELECT id FROM shifts WHERE shift_code = $1 AND is_deleted = false`,
        [shift_code]
      );
      if (existingCode.rows.length > 0) {
        return res.status(409).json({
          success: false,
          message: `Shift with code "${shift_code}" already exists`,
        });
      }
    }

    // ── Calculate hours ──────────────────────────────────────────────────────
    const { total_hours, total_break_hours, working_hours } = calculateShiftHours({
      start_time, end_time,
      lunch_start, lunch_end,
      first_break_start, first_break_end,
      second_break_start, second_break_end,
    });

    const result = await pool.query(
      `INSERT INTO shifts (
        shift_name, shift_code, start_time, end_time,
        lunch_start, lunch_end, first_break_start, first_break_end,
        second_break_start, second_break_end,
        total_hours, total_break_hours, working_hours,
        org_id, created_by, created_at, updated_at
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,NOW(),NOW())
      RETURNING *`,
      [
        shift_name, shift_code || null, start_time, end_time,
        lunch_start || null, lunch_end || null,
        first_break_start || null, first_break_end || null,
        second_break_start || null, second_break_end || null,
        total_hours, total_break_hours, working_hours,
        org_id, userId || null,
      ]
    );

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

/* ===========================
   ✅ GET ALL SHIFTS
=========================== */
const getShifts = async (req, res) => {
  const org_id = req.session.user?.orgId;

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

    const { page = 1, limit = 100, search } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);

    // First, get all shifts without the count to debug
    let query = `
      SELECT s.*,
        COUNT(CASE WHEN sa.is_active = true AND sa.is_deleted = false THEN 1 END) as assigned_employees
      FROM shifts s
      LEFT JOIN shift_assignments sa ON s.id = sa.shift_id AND sa.is_deleted = false
      WHERE s.is_deleted = false AND s.org_id = $1
    `;

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

    if (search) {
      query += ` AND (s.shift_name ILIKE $${paramIndex} OR s.shift_code ILIKE $${paramIndex})`;
      queryParams.push(`%${search}%`);
      paramIndex++;
    }

    query += ` GROUP BY s.id ORDER BY s.start_time ASC`;
    
    // Get total count
    const countQuery = `SELECT COUNT(*) as total FROM shifts WHERE is_deleted = false AND org_id = $1`;
    const countResult = await pool.query(countQuery, [org_id]);
    const totalRecords = parseInt(countResult.rows[0]?.total || 0);

    // Add pagination
    query += ` LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    queryParams.push(parseInt(limit), offset);
    
    const result = await pool.query(query, queryParams);

    console.log(`✅ Found ${result.rows.length} shifts for org ${org_id}`);

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

/* ===========================
   ✅ GET SHIFT BY ID
=========================== */
const getShiftById = async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(
      `SELECT s.*,
        COUNT(sa.id) as total_assignments,
        COUNT(CASE WHEN sa.is_active = true THEN 1 END) as active_assignments
       FROM shifts s
       LEFT JOIN shift_assignments sa ON s.id = sa.shift_id AND sa.is_deleted = false
       WHERE s.id = $1 AND s.is_deleted = false
       GROUP BY s.id`,
      [id]
    );

    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Shift not found" });
    }
    res.status(200).json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET SHIFT BY ID ERROR:", err);
    res.status(500).json({ success: false, message: "Fetch failed" });
  }
};

/* ===========================
   ✅ UPDATE SHIFT
=========================== */
const updateShift = async (req, res) => {
  const userId = req.session.user.userId;

  try {
    const { id } = req.params;
    let {
      shift_name, shift_code, start_time, end_time,
      lunch_start, lunch_end, first_break_start, first_break_end,
      second_break_start, second_break_end,
    } = req.body;

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

    // Normalize — fall back to existing DB values if not provided
    const resolvedStartTime         = cleanTime(start_time)         || old.start_time?.toString().slice(0,5);
    const resolvedEndTime           = cleanTime(end_time)           || old.end_time?.toString().slice(0,5);
    const resolvedLunchStart        = start_time !== undefined ? cleanTime(lunch_start)        : old.lunch_start?.toString().slice(0,5);
    const resolvedLunchEnd          = end_time   !== undefined ? cleanTime(lunch_end)          : old.lunch_end?.toString().slice(0,5);
    const resolvedFbStart           = cleanTime(first_break_start)  ?? (first_break_start  !== undefined ? null : old.first_break_start?.toString().slice(0,5));
    const resolvedFbEnd             = cleanTime(first_break_end)    ?? (first_break_end    !== undefined ? null : old.first_break_end?.toString().slice(0,5));
    const resolvedSbStart           = cleanTime(second_break_start) ?? (second_break_start !== undefined ? null : old.second_break_start?.toString().slice(0,5));
    const resolvedSbEnd             = cleanTime(second_break_end)   ?? (second_break_end   !== undefined ? null : old.second_break_end?.toString().slice(0,5));

    // Duplicate name check (exclude current record)
    if (shift_name) {
      const nameCheck = await pool.query(
        `SELECT id FROM shifts WHERE shift_name = $1 AND id != $2 AND is_deleted = false`,
        [shift_name, id]
      );
      if (nameCheck.rows.length > 0) {
        return res.status(409).json({ success: false, message: `Shift "${shift_name}" already exists` });
      }
    }

    // ── Recalculate hours with merged values ─────────────────────────────────
    const { total_hours, total_break_hours, working_hours } = calculateShiftHours({
      start_time:         resolvedStartTime,
      end_time:           resolvedEndTime,
      lunch_start:        resolvedLunchStart,
      lunch_end:          resolvedLunchEnd,
      first_break_start:  resolvedFbStart,
      first_break_end:    resolvedFbEnd,
      second_break_start: resolvedSbStart,
      second_break_end:   resolvedSbEnd,
    });

    const result = await pool.query(
      `UPDATE shifts SET
        shift_name         = $1,
        shift_code         = $2,
        start_time         = $3,
        end_time           = $4,
        lunch_start        = $5,
        lunch_end          = $6,
        first_break_start  = $7,
        first_break_end    = $8,
        second_break_start = $9,
        second_break_end   = $10,
        total_hours        = $11,
        total_break_hours  = $12,
        working_hours      = $13,
        updated_by         = $14,
        updated_at         = NOW()
       WHERE id = $15 AND is_deleted = false
       RETURNING *`,
      [
        shift_name   || old.shift_name,
        shift_code   !== undefined ? (shift_code || null) : old.shift_code,
        resolvedStartTime,
        resolvedEndTime,
        resolvedLunchStart        || null,
        resolvedLunchEnd          || null,
        resolvedFbStart           || null,
        resolvedFbEnd             || null,
        resolvedSbStart           || null,
        resolvedSbEnd             || null,
        total_hours,
        total_break_hours,
        working_hours,
        userId || null,
        id,
      ]
    );

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

/* ===========================
   ✅ DELETE SHIFT (Soft Delete)
=========================== */
const deleteShift = async (req, res) => {
  const userId = req.session.user.userId;

  try {
    const { id } = req.params;

    const assignmentCheck = await pool.query(
      `SELECT id FROM shift_assignments WHERE shift_id = $1 AND is_active = true AND is_deleted = false`,
      [id]
    );
    if (assignmentCheck.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: "Cannot delete shift with active employee assignments. Please reassign employees first.",
      });
    }

    const result = await pool.query(
      `UPDATE shifts SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND is_deleted = false RETURNING id`,
      [userId || null, id]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Shift not found" });
    }
    res.json({ success: true, message: "Shift deleted successfully" });
  } catch (err) {
    console.error("DELETE SHIFT ERROR:", err);
    res.status(500).json({ success: false, message: "Delete failed" });
  }
};

/* ===========================
   ✅ ASSIGN SHIFT TO EMPLOYEE
=========================== */
const assignShiftToEmployee = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.userId;

  try {
    const {
      employee_id, employee_code, shift_id,
      assignment_date, effective_from, effective_to,
      is_permanent, reason, approved_by,
      update_attendance = true,
    } = req.body;

    const empIdentifier = employee_id || employee_code;

    if (!empIdentifier)       return res.status(400).json({ success: false, message: "employee_id is required" });
    if (!assignment_date)     return res.status(400).json({ success: false, message: "assignment_date is required" });
    if (!org_id)              return res.status(400).json({ success: false, message: "Session expired — please log in again" });

    // Resolve employee
    let employeeResult = await pool.query(
      `SELECT id, employee_id, first_name, last_name, org_id FROM employees WHERE employee_id = $1 AND status = 'Active' AND is_deleted = false`,
      [empIdentifier]
    );
    if (employeeResult.rows.length === 0) {
      employeeResult = await pool.query(
        `SELECT id, employee_id, first_name, last_name, org_id FROM employees WHERE employee_code = $1 AND status = 'Active' AND is_deleted = false`,
        [empIdentifier]
      );
    }
    if (employeeResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: `Employee not found: ${empIdentifier}` });
    }

    const emp = employeeResult.rows[0];
    if (emp.org_id !== org_id) {
      return res.status(403).json({ success: false, message: "Employee does not belong to your organisation" });
    }

    const realEmployeeId = emp.id;

    let shiftName = "Draft";
    if (shift_id) {
      const shiftResult = await pool.query(
        `SELECT id, shift_name FROM shifts WHERE id = $1 AND is_deleted = false`, [shift_id]
      );
      if (shiftResult.rows.length === 0) return res.status(404).json({ success: false, message: "Shift not found" });
      shiftName = shiftResult.rows[0].shift_name;
    }

    const effectiveFromDate = effective_from || assignment_date;
    const effectiveToDate   = effective_to   || null;

    const existingAssignment = await pool.query(
      `SELECT id FROM shift_assignments WHERE employee_id = $1 AND assignment_date = $2 AND is_deleted = false`,
      [realEmployeeId, assignment_date]
    );

    let result;

    if (existingAssignment.rows.length > 0) {
      result = await pool.query(
        `UPDATE shift_assignments SET shift_id=$1, effective_from=$2, effective_to=$3, is_permanent=$4,
         is_active=true, reason=$5, approved_by=$6, updated_by=$7, updated_at=NOW()
         WHERE id=$8 RETURNING *`,
        [shift_id||null, effectiveFromDate, effectiveToDate, is_permanent??false, reason||null, approved_by||null, userId||null, existingAssignment.rows[0].id]
      );
      return res.status(200).json({
        success: true,
        message: `Shift "${shiftName}" updated for ${emp.first_name} ${emp.last_name}`,
        data: result.rows[0],
      });
    } else {
      await pool.query(
        `UPDATE shift_assignments SET is_active=false, effective_to=$1, updated_at=NOW()
         WHERE employee_id=$2 AND is_active=true AND is_deleted=false`,
        [effectiveFromDate, realEmployeeId]
      );
      result = await pool.query(
        `INSERT INTO shift_assignments (employee_id,shift_id,assignment_date,effective_from,effective_to,is_permanent,is_active,reason,approved_by,org_id,created_by,created_at,updated_at)
         VALUES ($1,$2,$3,$4,$5,$6,true,$7,$8,$9,$10,NOW(),NOW()) RETURNING *`,
        [realEmployeeId, shift_id||null, assignment_date, effectiveFromDate, effectiveToDate, is_permanent??false, reason||null, approved_by||null, org_id, userId||null]
      );
      if (update_attendance && shift_id) {
        await pool.query(
          `UPDATE attendance SET shift_type=$1, updated_by=$2, updated_at=NOW()
           WHERE employee_id=$3 AND attendance_date>=$4 AND is_deleted=false`,
          [shiftName, userId, realEmployeeId, effectiveFromDate]
        );
      }
      return res.status(201).json({
        success: true,
        message: `Shift "${shiftName}" assigned to ${emp.first_name} ${emp.last_name} successfully`,
        data: result.rows[0],
      });
    }
  } catch (error) {
    console.error("ASSIGN SHIFT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEE CURRENT SHIFT
=========================== */
const getEmployeeCurrentShift = async (req, res) => {
  try {
    const identifier = req.params.employee_id || req.params.employee_code;
    if (!identifier) {
      return res.status(400).json({ success: false, message: "Employee identifier is required" });
    }

    let empCheck = await pool.query(
      `SELECT id FROM employees WHERE employee_id = $1 AND status = 'Active' AND is_deleted = false`, 
      [identifier]
    );
    
    if (empCheck.rows.length === 0) {
      empCheck = await pool.query(
        `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`, 
        [identifier]
      );
    }
    
    if (empCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const result = await pool.query(
      `SELECT 
        sa.id, 
        sa.assignment_date, 
        sa.effective_from, 
        sa.effective_to, 
        sa.is_permanent, 
        sa.is_active,
        s.id AS shift_id, 
        s.shift_name, 
        s.shift_code, 
        s.start_time, 
        s.end_time,
        s.total_hours, 
        s.total_break_hours, 
        s.working_hours
       FROM shift_assignments sa
       LEFT JOIN shifts s ON sa.shift_id = s.id
       JOIN employees e ON sa.employee_id = e.id
       WHERE e.id = $1 AND sa.is_active = true AND sa.is_deleted = false
       ORDER BY sa.created_at DESC 
       LIMIT 1`,
      [empCheck.rows[0].id]
    );

    if (!result.rows.length) {
      return res.status(200).json({
        success: true,
        data: null,
        message: "No active shift assigned"
      });
    }
    
    if (!result.rows[0].shift_id) {
      result.rows[0].shift_name = "Draft";
    }

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

/* ===========================
   ✅ GET EMPLOYEES BY SHIFT
=========================== */
const getEmployeesByShift = async (req, res) => {
  const org_id = req.session.user.orgId;

  try {
    const { shift_id } = req.query;
    
    let query = `
      SELECT 
        e.employee_id, 
        e.first_name, 
        e.last_name,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email, 
        e.phone, 
        s.shift_name, 
        sa.assignment_date, 
        sa.effective_from, 
        sa.is_permanent
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
      LEFT JOIN shifts s ON sa.shift_id = s.id
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE sa.is_active = true AND sa.is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;

    if (shift_id) { 
      query += ` AND sa.shift_id = $${paramIndex}`; 
      queryParams.push(shift_id); 
      paramIndex++; 
    }
    if (org_id) { 
      query += ` AND e.org_id = $${paramIndex}`;    
      queryParams.push(org_id);   
      paramIndex++; 
    }

    query += ` ORDER BY e.first_name, e.last_name`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({ 
      success: true, 
      data: result.rows, 
      totalRecords: result.rows.length 
    });
  } catch (err) {
    console.error("GET EMPLOYEES BY SHIFT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET SHIFT ASSIGNMENT HISTORY
=========================== */
const getShiftAssignmentHistory = async (req, res) => {
  try {
    const identifier = req.params.employee_id || req.params.employee_code;
    const { limit = 10 } = req.query;
    
    if (!identifier) {
      return res.status(400).json({ success: false, message: "Employee identifier is required" });
    }

    let empResult = await pool.query(
      `SELECT id FROM employees WHERE employee_id = $1 AND status = 'Active' AND is_deleted = false`, 
      [identifier]
    );
    
    if (empResult.rows.length === 0) {
      empResult = await pool.query(
        `SELECT id FROM employees WHERE employee_code = $1 AND status = 'Active' AND is_deleted = false`, 
        [identifier]
      );
    }
    
    if (empResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const result = await pool.query(
      `SELECT 
        sa.id, 
        s.shift_name, 
        s.shift_code, 
        sa.assignment_date, 
        sa.effective_from, 
        sa.effective_to,
        sa.is_permanent, 
        sa.is_active, 
        sa.created_at, 
        sa.created_by
       FROM shift_assignments sa 
       LEFT JOIN shifts s ON sa.shift_id = s.id
       WHERE sa.employee_id = $1 AND sa.is_deleted = false
       ORDER BY sa.created_at DESC 
       LIMIT $2`,
      [empResult.rows[0].id, limit]
    );
    
    res.json({ 
      success: true, 
      data: result.rows, 
      totalRecords: result.rows.length 
    });
  } catch (err) {
    console.error("GET SHIFT HISTORY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET SHIFT FOR ATTENDANCE
=========================== */
const getShiftForAttendance = async (req, res) => {
  try {
    const { employee_code, attendance_date } = req.query;
    if (!employee_code || !attendance_date) {
      return res.status(400).json({ success: false, message: "employee_code and attendance_date are required" });
    }
    const result = await pool.query(
      `SELECT s.id as shift_id, s.shift_name, s.start_time, s.end_time,
        s.lunch_start, s.lunch_end, s.first_break_start, s.first_break_end,
        s.second_break_start, s.second_break_end,
        s.total_hours, s.total_break_hours, s.working_hours,
        sa.effective_from, sa.effective_to
       FROM shift_assignments sa
       JOIN shifts s ON sa.shift_id=s.id
      JOIN employees e ON sa.employee_id=e.id AND e.status = 'Active' AND e.is_deleted = false
       WHERE e.employee_code=$1 AND sa.is_active=true AND sa.is_deleted=false
         AND $2 BETWEEN sa.effective_from AND COALESCE(sa.effective_to,$2)
       LIMIT 1`,
      [employee_code, attendance_date]
    );
    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "No shift found for this employee on the specified date" });
    }
    res.status(200).json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET SHIFT FOR ATTENDANCE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ DEACTIVATE SHIFT ASSIGNMENT
=========================== */
const deactivateShiftAssignment = async (req, res) => {
  const userId = req.session.user.userId;
  try {
    const { employee_id, employee_code, effective_to } = req.body;
    const identifier = employee_id || employee_code;
    if (!identifier) return res.status(400).json({ success: false, message: "employee_id is required" });

   let empResult = await pool.query(`SELECT id FROM employees WHERE employee_id=$1 AND status = 'Active' AND is_deleted=false`, [identifier]);
if (empResult.rows.length === 0) empResult = await pool.query(`SELECT id FROM employees WHERE employee_code=$1 AND status = 'Active' AND is_deleted=false`, [identifier]);
    if (empResult.rows.length === 0) return res.status(404).json({ success: false, message: "Employee not found" });

    const result = await pool.query(
      `UPDATE shift_assignments SET is_active=false, effective_to=COALESCE($1,effective_to,CURRENT_DATE), updated_by=$2, updated_at=NOW()
       WHERE employee_id=$3 AND is_active=true AND is_deleted=false RETURNING id, is_active`,
      [effective_to||null, userId, empResult.rows[0].id]
    );
    if (result.rowCount === 0) return res.status(404).json({ success: false, message: "No active shift assignment found" });
    res.json({ success: true, message: "Shift assignment deactivated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("DEACTIVATE SHIFT ASSIGNMENT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ BULK ASSIGN SHIFT
=========================== */
const bulkAssignShift = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.userId;

  try {
    const { employee_codes, shift_id, assignment_date, effective_from, effective_to, reason, approved_by } = req.body;

    if (!employee_codes?.length || !shift_id || !assignment_date) {
      return res.status(400).json({ success: false, message: "employee_codes[], shift_id, and assignment_date are required" });
    }
    if (!org_id) return res.status(400).json({ success: false, message: "Session expired — please log in again" });

    const shiftResult = await pool.query(`SELECT id, shift_name FROM shifts WHERE id=$1 AND is_deleted=false`, [shift_id]);
    if (shiftResult.rows.length === 0) return res.status(404).json({ success: false, message: "Shift not found" });
    const shiftName = shiftResult.rows[0].shift_name;

    const effectiveFromDate = effective_from || assignment_date;
    const effectiveToDate   = effective_to   || null;
    const results = [];
    let successCount = 0, failCount = 0;

    for (const empCode of employee_codes) {
      try {
       let empResult = await pool.query(`SELECT id, org_id FROM employees WHERE employee_id=$1 AND status = 'Active' AND is_deleted=false`, [empCode]);
if (empResult.rows.length === 0) empResult = await pool.query(`SELECT id, org_id FROM employees WHERE employee_code=$1 AND status = 'Active' AND is_deleted=false`, [empCode]);
        if (empResult.rows.length === 0) { failCount++; results.push({ employee_id: empCode, success: false, message: "Employee not found" }); continue; }

        const emp = empResult.rows[0];
        if (emp.org_id !== org_id) { failCount++; results.push({ employee_id: empCode, success: false, message: "Employee not in your organisation" }); continue; }

        await pool.query(`UPDATE shift_assignments SET is_active=false, effective_to=$1, updated_at=NOW() WHERE employee_id=$2 AND is_active=true AND is_deleted=false`, [effectiveFromDate, emp.id]);
        const insertResult = await pool.query(
          `INSERT INTO shift_assignments (employee_id,shift_id,assignment_date,effective_from,effective_to,is_permanent,is_active,reason,approved_by,org_id,created_by,created_at,updated_at)
           VALUES ($1,$2,$3,$4,$5,false,true,$6,$7,$8,$9,NOW(),NOW()) RETURNING id`,
          [emp.id, shift_id, assignment_date, effectiveFromDate, effectiveToDate, reason||null, approved_by||null, org_id, userId||null]
        );
        await pool.query(`UPDATE attendance SET shift_type=$1, updated_by=$2, updated_at=NOW() WHERE employee_id=$3 AND attendance_date>=$4 AND is_deleted=false`, [shiftName, userId, emp.id, effectiveFromDate]);
        successCount++;
        results.push({ employee_id: empCode, success: true, id: insertResult.rows[0].id });
      } catch (error) {
        failCount++;
        results.push({ employee_id: empCode, success: false, message: error.message });
      }
    }

    res.status(200).json({
      success: true,
      message: `Successfully assigned ${successCount} employee(s), ${failCount} failed`,
      data: { success_count: successCount, fail_count: failCount, results },
    });
  } catch (error) {
    console.error("BULK ASSIGN SHIFT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ UPDATE EMPLOYEE SHIFT WITH ATTENDANCE
=========================== */
const updateEmployeeShiftWithAttendance = async (req, res) => {
  const userId = req.session.user.userId;
  const org_id = req.session.user.orgId;
  try {
    const { employee_id, employee_code, new_shift_id, effective_from } = req.body;
    const identifier = employee_id || employee_code;
    if (!identifier || !new_shift_id) return res.status(400).json({ success: false, message: "employee_id and new_shift_id are required" });

    let empResult = await pool.query(`SELECT id FROM employees WHERE employee_id=$1 AND status = 'Active' AND is_deleted=false`, [identifier]);
if (empResult.rows.length === 0) empResult = await pool.query(`SELECT id FROM employees WHERE employee_code=$1 AND status = 'Active' AND is_deleted=false`, [identifier]);
    if (empResult.rows.length === 0) return res.status(404).json({ success: false, message: "Employee not found" });

    const shiftResult = await pool.query(`SELECT shift_name FROM shifts WHERE id=$1 AND is_deleted=false`, [new_shift_id]);
    if (shiftResult.rows.length === 0) return res.status(404).json({ success: false, message: "Shift not found" });

    const employeeId   = empResult.rows[0].id;
    const newShiftName = shiftResult.rows[0].shift_name;
    const effectiveDate = effective_from || new Date().toISOString().split("T")[0];

    await pool.query(`UPDATE shift_assignments SET is_active=false, effective_to=$1, updated_by=$2, updated_at=NOW() WHERE employee_id=$3 AND is_active=true`, [effectiveDate, userId, employeeId]);
    await pool.query(
      `INSERT INTO shift_assignments (employee_id,shift_id,assignment_date,effective_from,is_permanent,is_active,org_id,created_by,created_at,updated_at)
       VALUES ($1,$2,$3,$4,true,true,$5,$6,NOW(),NOW())`,
      [employeeId, new_shift_id, effectiveDate, effectiveDate, org_id, userId]
    );
    const updateResult = await pool.query(
      `UPDATE attendance SET shift_type=$1, updated_by=$2, updated_at=NOW() WHERE employee_id=$3 AND attendance_date>=$4 AND is_deleted=false`,
      [newShiftName, userId, employeeId, effectiveDate]
    );

    res.status(200).json({
      success: true,
      message: `Employee shift updated to ${newShiftName}. ${updateResult.rowCount} attendance records updated.`,
      data: { employee_id: employeeId, new_shift: newShiftName, effective_from: effectiveDate, attendance_records_updated: updateResult.rowCount },
    });
  } catch (error) {
    console.error("UPDATE EMPLOYEE SHIFT WITH ATTENDANCE ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ BULK UPDATE EMPLOYEE SHIFT WITH ATTENDANCE
=========================== */
const bulkUpdateEmployeeShiftWithAttendance = async (req, res) => {
  const userId = req.session.user.userId;
  const org_id = req.session.user.orgId;
  try {
    const { employee_codes, new_shift_id, effective_from } = req.body;
    if (!employee_codes?.length || !new_shift_id) return res.status(400).json({ success: false, message: "employee_codes[] and new_shift_id are required" });

    const shiftResult = await pool.query(`SELECT shift_name FROM shifts WHERE id=$1 AND is_deleted=false`, [new_shift_id]);
    if (shiftResult.rows.length === 0) return res.status(404).json({ success: false, message: "Shift not found" });

    const newShiftName   = shiftResult.rows[0].shift_name;
    const effectiveDate  = effective_from || new Date().toISOString().split("T")[0];
    let totalAttendanceUpdated = 0;
    const results = [];

    for (const empCode of employee_codes) {
     let empResult = await pool.query(`SELECT id FROM employees WHERE employee_id=$1 AND status = 'Active' AND is_deleted=false`, [empCode]);
if (empResult.rows.length === 0) empResult = await pool.query(`SELECT id FROM employees WHERE employee_code=$1 AND status = 'Active' AND is_deleted=false`, [empCode]);
      if (empResult.rows.length === 0) { results.push({ employee_id: empCode, success: false, message: "Employee not found" }); continue; }

      const employeeId = empResult.rows[0].id;
      await pool.query(`UPDATE shift_assignments SET is_active=false, effective_to=$1, updated_by=$2, updated_at=NOW() WHERE employee_id=$3 AND is_active=true`, [effectiveDate, userId, employeeId]);
      await pool.query(`INSERT INTO shift_assignments (employee_id,shift_id,assignment_date,effective_from,is_permanent,is_active,org_id,created_by,created_at,updated_at) VALUES ($1,$2,$3,$4,true,true,$5,$6,NOW(),NOW())`, [employeeId, new_shift_id, effectiveDate, effectiveDate, org_id, userId]);
      const updateResult = await pool.query(`UPDATE attendance SET shift_type=$1, updated_by=$2, updated_at=NOW() WHERE employee_id=$3 AND attendance_date>=$4 AND is_deleted=false`, [newShiftName, userId, employeeId, effectiveDate]);
      totalAttendanceUpdated += updateResult.rowCount;
      results.push({ employee_id: empCode, success: true, attendance_records_updated: updateResult.rowCount });
    }

    res.status(200).json({
      success: true,
      message: `${results.filter(r => r.success).length} employee(s) updated. ${totalAttendanceUpdated} attendance records updated.`,
      data: { total_employees_updated: results.filter(r => r.success).length, total_attendance_records_updated: totalAttendanceUpdated, results },
    });
  } catch (error) {
    console.error("BULK UPDATE EMPLOYEE SHIFT WITH ATTENDANCE ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEE CURRENT SHIFT BY CODE (legacy)
=========================== */
const getEmployeeCurrentShiftByCode = async (req, res) => {
  try {
    const identifier  = req.params.employee_id || req.params.employee_code;
    const { as_of_date } = req.query;
    if (!identifier) return res.status(400).json({ success: false, message: "Employee identifier is required" });

    const currentDate = as_of_date || new Date().toISOString().split("T")[0];

    let empResult = await pool.query(`SELECT id FROM employees WHERE employee_id=$1 AND status = 'Active' AND is_deleted=false`, [identifier]);
if (empResult.rows.length === 0) empResult = await pool.query(`SELECT id FROM employees WHERE employee_code=$1 AND status = 'Active' AND is_deleted=false`, [identifier]);
    if (empResult.rows.length === 0) {
      return res.status(200).json({ success: true, data: { shift_name: "General", shift_id: null, is_active: false, message: "No shift assigned" } });
    }

    const result = await pool.query(
      `SELECT sa.id, sa.assignment_date, sa.effective_from, sa.effective_to, sa.is_permanent, sa.is_active, sa.reason,
        s.id AS shift_id, s.shift_name, s.shift_code, s.start_time, s.end_time,
        s.total_hours, s.total_break_hours, s.working_hours,
        s.lunch_start, s.lunch_end, s.first_break_start, s.first_break_end,
        s.second_break_start, s.second_break_end,
        CONCAT(approver.first_name,' ',approver.last_name) AS approved_by_name
       FROM shift_assignments sa
       LEFT JOIN shifts s ON sa.shift_id=s.id
       LEFT JOIN employees approver ON sa.approved_by=approver.id
       WHERE sa.employee_id=$1 AND sa.is_deleted=false AND (s.is_deleted=false OR s.is_deleted IS NULL)
         AND sa.effective_from<=$2 AND (sa.effective_to IS NULL OR sa.effective_to>=$2)
       ORDER BY sa.is_active DESC, sa.created_at DESC LIMIT 1`,
      [empResult.rows[0].id, currentDate]
    );

    if (!result.rows.length) {
      return res.status(200).json({ success: true, data: { shift_name: "General", shift_id: null, is_active: false, message: "No shift assigned" } });
    }
    if (!result.rows[0].shift_id) result.rows[0].shift_name = "Draft";

    res.status(200).json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET EMPLOYEE CURRENT SHIFT BY CODE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  createShift,
  getShifts,
  getShiftById,
  updateShift,
  deleteShift,
  assignShiftToEmployee,
  getEmployeeCurrentShift,
  getEmployeesByShift,
  getShiftAssignmentHistory,
  getShiftForAttendance,
  deactivateShiftAssignment,
  bulkAssignShift,
  updateEmployeeShiftWithAttendance,
  bulkUpdateEmployeeShiftWithAttendance,
  getEmployeeCurrentShiftByCode,
};