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

/* ===========================
   ✅ HELPER FUNCTIONS
=========================== */

/**
 * Check for overlapping shift assignments
 */
const checkOverlappingAssignments = async (employeeId, effectiveFrom, effectiveTo, excludeId = null) => {
  let query = `
    SELECT id, effective_from, effective_to, shift_id
    FROM shift_assignments
    WHERE employee_id = $1
      AND is_deleted = false
      AND is_active = true
      AND (
        (effective_from <= $2 AND (effective_to IS NULL OR effective_to >= $2))
        OR
        (effective_from <= $3 AND (effective_to IS NULL OR effective_to >= $3))
        OR
        (effective_from >= $2 AND (effective_to IS NULL OR effective_to <= $3))
      )
  `;

  const params = [employeeId, effectiveFrom, effectiveTo || '9999-12-31'];

  if (excludeId) {
    query += ` AND id != $4`;
    params.push(excludeId);
  }

  const result = await pool.query(query, params);
  return result.rows;
};

/**
 * Validate date range
 */
const validateDateRange = (effectiveFrom, effectiveTo) => {
  if (effectiveTo && new Date(effectiveTo) < new Date(effectiveFrom)) {
    throw new Error("Effective end date cannot be before start date");
  }
};

/**
 * Update attendance records after shift change
 */
const updateAttendanceShift = async (employeeId, newShiftName, effectiveFrom, updatedBy) => {
  return 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, updatedBy, employeeId, effectiveFrom]
  );
};



/**
 * Auto-update assignment status based on dates
 */
const autoUpdateAssignmentStatus = async () => {
  const result = await pool.query(`
    UPDATE shift_assignments
    SET is_active = false,
        updated_at = NOW()
    WHERE is_active = true
      AND effective_to IS NOT NULL
      AND effective_to < CURRENT_DATE
      AND is_deleted = false
    RETURNING id
  `);
  return result.rows;
};

/**
 * Validate approver has proper permissions
 */
const validateApprover = async (approverId) => {
  if (!approverId) return true;

  const result = await pool.query(
    `
    SELECT id, role, can_approve_shifts
    FROM employees
    WHERE id = $1
    AND status = 'Active'
      AND is_deleted = false
      AND (role IN ('admin', 'manager', 'hr') OR can_approve_shifts = true)
    `,
    [approverId]
  );

  return result.rows.length > 0;
};

/**
 * Build paginated query
 */
const buildPaginatedQuery = async (baseQuery, countQuery, params, page, limit) => {
  const offset = (page - 1) * limit;

  const countResult = await pool.query(countQuery, params);
  const totalRecords = parseInt(countResult.rows[0]?.total || 0);

  const paginatedQuery = `${baseQuery} LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
  const queryParams = [...params, limit, offset];

  const result = await pool.query(paginatedQuery, queryParams);

  return {
    data: result.rows,
    pagination: {
      currentPage: parseInt(page),
      totalPages: Math.ceil(totalRecords / limit),
      totalRecords,
      recordsPerPage: parseInt(limit),
    },
  };
};

// ============================================================
// FIND this block in your controller (the one causing the 400):
// ============================================================

// OLD CODE (delete this):
// if (!employee_code || !assignment_date || !org_id) {
//   return res.status(400).json({
//     success: false,
//     message: "Employee code, assignment date, and org_id are required",
//   });
// }

// ============================================================
// REPLACE the entire assignShiftToEmployee function with this:
// ============================================================

const assignShiftToEmployee = async (req, res) => {
  const client = await pool.connect();

  try {
    console.log("=== ASSIGN SHIFT - body received ===", req.body);

    // org_id and userId ALWAYS from session — never from request body
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId;

    const {
      employee_id,     // new field name (business ID like "EMP225")
      employee_code,   // legacy fallback — keep accepting so nothing else breaks
      shift_id,
      assignment_date,
      effective_from,
      effective_to,
      is_permanent,
      reason,
      approved_by,
      update_attendance = true,
    } = req.body;

    // Accept either employee_id or employee_code
    const empIdentifier = employee_id || employee_code;

    // ── Validation ─────────────────────────────────────────────────────────
    if (!empIdentifier) {
      return res.status(400).json({
        success: false,
        message: "employee_id is required",
      });
    }
    if (!shift_id) {
      return res.status(400).json({
        success: false,
        message: "shift_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",
      });
    }
    // ── End validation ──────────────────────────────────────────────────────

    await client.query("BEGIN");

    // Look up employee by business employee_id
    let employeeResult = await client.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]
    );

    // Numeric PK fallback (just in case)
    if (employeeResult.rows.length === 0 && !isNaN(empIdentifier)) {
      employeeResult = await client.query(
        `SELECT id, employee_id, first_name, last_name, org_id
FROM employees
WHERE id = $1 AND status = 'Active' AND is_deleted = false`,
        [parseInt(empIdentifier)]
      );
    }

    if (employeeResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        success: false,
        message: `Employee not found: ${empIdentifier}`,
      });
    }

    const emp = employeeResult.rows[0];

    if (emp.org_id !== org_id) {
      await client.query("ROLLBACK");
      return res.status(403).json({
        success: false,
        message: "Employee does not belong to your organisation",
      });
    }

    // Resolve shift
    const shiftResult = await client.query(
      `SELECT id, shift_name FROM shifts WHERE id = $1 AND is_deleted = false`,
      [shift_id]
    );

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

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

    // Check for existing assignment on this exact date
    const existingAssignment = await client.query(
      `SELECT id FROM shift_assignments
       WHERE employee_id = $1 AND assignment_date = $2 AND is_deleted = false`,
      [emp.id, assignment_date]
    );

    let result;
    let attendanceUpdateCount = 0;

    if (existingAssignment.rows.length > 0) {
      // ── UPDATE existing record ────────────────────────────────────────────
      result = await client.query(
        `UPDATE shift_assignments
         SET shift_id       = $1,
             effective_from = $2,
             effective_to   = $3,
             is_permanent   = $4,
             reason         = $5,
             approved_by    = $6,
             updated_by     = $7,
             updated_at     = NOW()
         WHERE id = $8
         RETURNING *`,
        [
          shift_id,
          effectiveFromDate,
          effectiveToDate,
          is_permanent ?? false,
          reason      || null,
          approved_by || null,
          userId      || null,
          existingAssignment.rows[0].id,
        ]
      );

      if (update_attendance) {
        const ar = await updateAttendanceShift(
          emp.id, shiftResult.rows[0].shift_name, effectiveFromDate, userId
        );
        attendanceUpdateCount = ar.rowCount;
      }

      await client.query("COMMIT");

      return res.status(200).json({
        success: true,
        message: `Shift updated for ${emp.first_name} ${emp.last_name}`,
        data: { assignment: result.rows[0], attendance_records_updated: attendanceUpdateCount },
      });

    } else {
      // ── INSERT new record ─────────────────────────────────────────────────
      // First deactivate any other active assignment for this employee
      // (this is what makes "swap to Draft then swap back" work correctly)
      await client.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]
      );

      result = await client.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 *`,
        [
          emp.id,
          shift_id,
          assignment_date,
          effectiveFromDate,
          effectiveToDate,
          is_permanent ?? false,
          reason      || null,
          approved_by || null,
          org_id,                // ✅ from session
          userId      || null,
        ]
      );

      if (update_attendance) {
        const ar = await updateAttendanceShift(
          emp.id, shiftResult.rows[0].shift_name, effectiveFromDate, userId
        );
        attendanceUpdateCount = ar.rowCount;
      }

      await client.query("COMMIT");

      return res.status(201).json({
        success: true,
        message: `Shift "${shiftResult.rows[0].shift_name}" assigned to ${emp.first_name} ${emp.last_name} successfully`,
        data: { assignment: result.rows[0], attendance_records_updated: attendanceUpdateCount },
      });
    }

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("ASSIGN SHIFT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ GET ALL SHIFT ASSIGNMENTS
=========================== */
const getShiftAssignments = async (req, res) => {
  try {
    const {
      page = 1,
      limit = 10,
      employee_id,
      shift_id,
      is_active,
      start_date,
      end_date,
    } = req.query;

    // org_id from session — restricts results to logged-in org automatically
    const org_id = req.session.user?.orgId;

    let baseQuery = `
      SELECT
        sa.*,
        e.employee_id,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        d.department_name  AS department,
        ds.designation_name AS designation,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name,
        CONCAT(creator.first_name,  ' ', creator.last_name)  AS created_by_name,
        CONCAT(updater.first_name,  ' ', updater.last_name)  AS updated_by_name
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
      JOIN shifts    s  ON sa.shift_id    = s.id
      LEFT JOIN departments  d  ON e.department  = d.id
      LEFT JOIN designations ds ON e.designation = ds.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      LEFT JOIN employees creator  ON sa.created_by  = creator.id
      LEFT JOIN employees updater  ON sa.updated_by  = updater.id
      WHERE sa.is_deleted = false
    `;

    let countQuery = `
      SELECT COUNT(DISTINCT sa.id) as total
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      JOIN shifts    s ON sa.shift_id    = s.id
      WHERE sa.is_deleted = false
    `;

    const queryParams = [];
    let paramIndex = 1;

    // Always filter by session org
    if (org_id) {
      const cond = ` AND sa.org_id = $${paramIndex}`;
      baseQuery += cond;
      countQuery += cond;
      queryParams.push(org_id);
      paramIndex++;
    }

    if (employee_id) {
      const cond = ` AND e.employee_id = $${paramIndex}`;
      baseQuery += cond;
      countQuery += cond;
      queryParams.push(employee_id);
      paramIndex++;
    }

    if (shift_id) {
      const cond = ` AND sa.shift_id = $${paramIndex}`;
      baseQuery += cond;
      countQuery += cond;
      queryParams.push(shift_id);
      paramIndex++;
    }

    if (is_active !== undefined) {
      const cond = ` AND sa.is_active = $${paramIndex}`;
      baseQuery += cond;
      countQuery += cond;
      queryParams.push(is_active === "true");
      paramIndex++;
    }

    if (start_date) {
      const cond = ` AND sa.assignment_date >= $${paramIndex}`;
      baseQuery += cond;
      countQuery += cond;
      queryParams.push(start_date);
      paramIndex++;
    }

    if (end_date) {
      const cond = ` AND sa.assignment_date <= $${paramIndex}`;
      baseQuery += cond;
      countQuery += cond;
      queryParams.push(end_date);
      paramIndex++;
    }

    baseQuery += ` ORDER BY sa.created_at DESC`;

    await autoUpdateAssignmentStatus();

    const result = await buildPaginatedQuery(
      baseQuery,
      countQuery,
      queryParams,
      page,
      limit
    );

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

/* ===========================
   ✅ GET SHIFT ASSIGNMENT BY ID
=========================== */
const getShiftAssignmentById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `
      SELECT
        sa.*,
        e.employee_id,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        d.department_name  AS department,
        ds.designation_name AS designation,
        e.email,
        e.phone,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        s.total_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,
        CONCAT(creator.first_name,  ' ', creator.last_name)  AS created_by_name,
        CONCAT(updater.first_name,  ' ', updater.last_name)  AS updated_by_name
      FROM shift_assignments sa
      JOIN employees e  ON sa.employee_id = e.id
      JOIN shifts    s  ON sa.shift_id    = s.id
      LEFT JOIN departments  d  ON e.department  = d.id
      LEFT JOIN designations ds ON e.designation = ds.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      LEFT JOIN employees creator  ON sa.created_by  = creator.id
      LEFT JOIN employees updater  ON sa.updated_by  = updater.id
      WHERE sa.id = $1 AND sa.is_deleted = false
      `,
      [id]
    );

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

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

/* ===========================
   ✅ UPDATE SHIFT ASSIGNMENT
=========================== */
const updateShiftAssignment = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;
    const {
      shift_id,
      effective_from,
      effective_to,
      is_permanent,
      is_active,
      reason,
      approved_by,
      updated_by,
      update_attendance = true,
    } = req.body;

    await client.query("BEGIN");

    const existing = await client.query(
      `SELECT sa.*, e.id as employee_id, s.shift_name
       FROM shift_assignments sa
       JOIN employees e ON sa.employee_id = e.id
       JOIN shifts    s ON sa.shift_id    = s.id
       WHERE sa.id = $1 AND sa.is_deleted = false`,
      [id]
    );

    if (!existing.rows.length) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Shift assignment not found" });
    }

    const old = existing.rows[0];
    const newEffectiveFrom = effective_from || old.effective_from;
    const newEffectiveTo = effective_to !== undefined ? effective_to : old.effective_to;

    validateDateRange(newEffectiveFrom, newEffectiveTo);

    if (approved_by && !(await validateApprover(approved_by))) {
      await client.query("ROLLBACK");
      return res.status(403).json({
        success: false,
        message: "Approver not found or doesn't have permission",
      });
    }

    const overlapping = await checkOverlappingAssignments(
      old.employee_id,
      newEffectiveFrom,
      newEffectiveTo,
      parseInt(id)
    );

    if (overlapping.length > 0) {
      await client.query("ROLLBACK");
      return res.status(409).json({
        success: false,
        message: "Overlapping shift assignment exists",
        overlapping_assignments: overlapping,
      });
    }

    let newShiftName = old.shift_name;
    if (shift_id && shift_id !== old.shift_id) {
      const shiftResult = await client.query(
        `SELECT shift_name FROM shifts WHERE id = $1 AND is_deleted = false`,
        [shift_id]
      );
      if (shiftResult.rows.length > 0) newShiftName = shiftResult.rows[0].shift_name;
    }

    const result = await client.query(
      `UPDATE shift_assignments
       SET shift_id      = COALESCE($1, shift_id),
           effective_from = COALESCE($2, effective_from),
           effective_to   = $3,
           is_permanent   = COALESCE($4, is_permanent),
           is_active      = COALESCE($5, is_active),
           reason         = COALESCE($6, reason),
           approved_by    = COALESCE($7, approved_by),
           updated_by     = COALESCE($8, updated_by),
           updated_at     = NOW()
       WHERE id = $9
       RETURNING *`,
      [
        shift_id || old.shift_id,
        effective_from || old.effective_from,
        effective_to !== undefined ? effective_to : old.effective_to,
        is_permanent !== undefined ? is_permanent : old.is_permanent,
        is_active !== undefined ? is_active : old.is_active,
        reason || old.reason,
        approved_by || old.approved_by,
        updated_by || old.updated_by,
        id,
      ]
    );

    let attendanceUpdateCount = 0;

    if (update_attendance && shift_id && shift_id !== old.shift_id && is_active !== false) {
      const ar = await updateAttendanceShift(
        old.employee_id,
        newShiftName,
        newEffectiveFrom,
        updated_by
      );
      attendanceUpdateCount = ar.rowCount;
    }

    await client.query("COMMIT");

    res.status(200).json({
      success: true,
      message: "Shift assignment updated successfully",
      data: { assignment: result.rows[0], attendance_records_updated: attendanceUpdateCount },
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("UPDATE SHIFT ASSIGNMENT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ DELETE SHIFT ASSIGNMENT (Soft Delete)
=========================== */
const deleteShiftAssignment = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;
    const { updated_by } = req.body;

    await client.query("BEGIN");

    const assignment = await client.query(
      `SELECT sa.*, e.id as employee_id
       FROM shift_assignments sa
       JOIN employees e ON sa.employee_id = e.id
       WHERE sa.id = $1 AND sa.is_deleted = false`,
      [id]
    );

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

    await client.query(
      `UPDATE shift_assignments
       SET is_deleted = true,
           is_active  = false,
           updated_by = COALESCE($1, updated_by),
           updated_at = NOW()
       WHERE id = $2 AND is_deleted = false`,
      [updated_by || null, id]
    );

    await client.query("COMMIT");

    res.json({ success: true, message: "Shift assignment deleted successfully" });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("DELETE SHIFT ASSIGNMENT ERROR:", err);
    res.status(500).json({ success: false, message: "Delete failed" });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ GET EMPLOYEE CURRENT SHIFT
   Param: employee_id (business ID)
=========================== */
// const getEmployeeCurrentShift = async (req, res) => {
//   try {
//     const employee_id = req.params.employee_id || req.params.employee_code;
//     const { as_of_date } = req.query;

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

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

//     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.working_hours
//        FROM shift_assignments sa
//        JOIN shifts s ON sa.shift_id = s.id
//        JOIN employees e ON sa.employee_id = e.id
//        WHERE e.employee_id = $1          -- varchar match, no type issue
//          AND sa.is_deleted = false
//          AND s.is_deleted  = false
//          AND sa.effective_from <= $2::date
//          AND (sa.effective_to IS NULL OR sa.effective_to >= $2::date)
//        ORDER BY sa.is_active DESC, sa.created_at DESC
//        LIMIT 1`,
//       [employee_id, currentDate]
//     );

//     if (!result.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "No active shift found for this employee",
//       });
//     }

//     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 });
//   }
// };


// const getEmployeeCurrentShift = async (req, res) => {
//   try {
//     const employee_id = req.params.employee_id || req.params.employee_code;
//     const { as_of_date } = req.query;

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

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

//     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.working_hours
//        FROM shift_assignments sa
//        JOIN shifts s ON sa.shift_id = s.id
//        JOIN employees e ON sa.employee_id = e.id
//        WHERE e.employee_id = $1
//          AND sa.is_deleted = false
//          AND sa.is_active = true          -- ✅ Only active assignments
//          AND sa.effective_from <= $2::date
//          AND (sa.effective_to IS NULL OR sa.effective_to >= $2::date)
//        ORDER BY sa.created_at DESC
//        LIMIT 1`,
//       [employee_id, currentDate]
//     );

//     if (!result.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "No active shift found for this employee",
//       });
//     }

//     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 });
//   }
// };

const getEmployeeCurrentShift = async (req, res) => {
  try {
    const employee_id =
      req.params.employee_id || req.params.employee_code;

    const { as_of_date } = req.query;

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

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

    // STEP 1: Validate employee exists
    const employeeCheck = await pool.query(
      `
      SELECT id, employee_id
      FROM employees
      WHERE employee_id = $1
        AND is_deleted = false
      LIMIT 1
      `,
      [employee_id]
    );

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

    // STEP 2: Get active shift
    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.working_hours

      FROM shift_assignments sa

      JOIN shifts s
        ON sa.shift_id = s.id

      WHERE sa.employee_id = $1
        AND sa.is_deleted = false
        AND sa.is_active = true
        AND sa.effective_from <= $2::date
        AND (
          sa.effective_to IS NULL
          OR sa.effective_to >= $2::date
        )

      ORDER BY sa.effective_from DESC,
               sa.created_at DESC
      LIMIT 1
      `,
      [employeeCheck.rows[0].id, currentDate]
    );

    // STEP 3: Employee exists but no active shift
    if (!result.rows.length) {
      return res.status(200).json({
        success: true,
        data: null,
        message: "No active shift assigned",
      });
    }

    // STEP 4: Success
    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });

  } catch (err) {
    console.error("GET EMPLOYEE CURRENT SHIFT ERROR:", err);

    return res.status(500).json({
      success: false,
      message: "Internal server error",
    });
  }
};
/* ===========================
   ✅ GET EMPLOYEE SHIFT HISTORY
=========================== */
const getEmployeeShiftHistory = async (req, res) => {
  try {
    const { employee_id } = req.params;
    const { limit = 50, offset = 0, start_date, end_date } = req.query;

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

    let query = `
      SELECT
        sa.id,
        sa.assignment_date,
        sa.effective_from,
        sa.effective_to,
        sa.is_permanent,
        sa.is_active,
        sa.reason,
        sa.created_at,
        sa.created_by,
        sa.updated_at,
        sa.updated_by,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name,
        CONCAT(creator.first_name,  ' ', creator.last_name)  AS created_by_name
      FROM shift_assignments sa
      JOIN shifts    s  ON sa.shift_id    = s.id
      JOIN employees e  ON sa.employee_id = e.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      LEFT JOIN employees creator  ON sa.created_by  = creator.id
      WHERE e.employee_id = $1 AND sa.is_deleted = false
    `;

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

    if (start_date) {
      query += ` AND sa.assignment_date >= $${paramIndex}`;
      queryParams.push(start_date);
      paramIndex++;
    }

    if (end_date) {
      query += ` AND sa.assignment_date <= $${paramIndex}`;
      queryParams.push(end_date);
      paramIndex++;
    }

    query += ` ORDER BY sa.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    queryParams.push(limit, offset);

    const result = await pool.query(query, queryParams);

    const countResult = await pool.query(
      `SELECT COUNT(*) as total
       FROM shift_assignments sa
       JOIN employees e ON sa.employee_id = e.id
       WHERE e.employee_id = $1 AND sa.is_deleted = false`,
      [employee_id]
    );

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        limit: parseInt(limit),
        offset: parseInt(offset),
        total: parseInt(countResult.rows[0].total),
      },
    });
  } catch (err) {
    console.error("GET EMPLOYEE SHIFT HISTORY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET SHIFT ASSIGNMENTS BY SHIFT
=========================== */
const getAssignmentsByShift = async (req, res) => {
  try {
    const { shift_id } = req.params;
    const { is_active = true, as_of_date } = req.query;

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

    let query = `
      SELECT
        sa.id,
        sa.assignment_date,
        sa.effective_from,
        sa.effective_to,
        sa.is_permanent,
        sa.is_active,
        e.employee_id,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        d.department_name   AS department,
        ds.designation_name AS designation,
        e.email,
        e.phone
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
      LEFT JOIN departments  d  ON e.department  = d.id
      LEFT JOIN designations ds ON e.designation = ds.id
      WHERE sa.shift_id = $1
        AND sa.is_deleted = false
        AND e.is_deleted  = false
    `;

    const queryParams = [shift_id];

    if (is_active === "true") {
      query += ` AND sa.is_active = true AND sa.effective_from <= $2 AND (sa.effective_to IS NULL OR sa.effective_to >= $2)`;
      queryParams.push(targetDate);
    } else if (is_active === "false") {
      query += ` AND sa.is_active = false`;
    }

    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 ASSIGNMENTS BY SHIFT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ DEACTIVATE SHIFT ASSIGNMENT (COMPLETE REWRITE)
=========================== */
const deactivateShiftAssignment = async (req, res) => {
  const client = await pool.connect();

  try {
    console.log("=== DEACTIVATE SHIFT ASSIGNMENT ===");
    console.log("Request body:", req.body);
    
    const { employee_id } = req.body;
    const { effective_to, reason, updated_by } = req.body;
    
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId || req.session.user?.id;

    // Validation
    if (!org_id) {
      return res.status(401).json({ 
        success: false, 
        message: "Session expired. Please log in again." 
      });
    }

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

    await client.query("BEGIN");

    // Find the employee by their business ID
    const employeeResult = await client.query(
      `SELECT id, employee_id, first_name, last_name, org_id
       FROM employees
       WHERE employee_id = $1 AND org_id = $2 AND is_deleted = false`,
      [employee_id, org_id]
    );

    if (employeeResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ 
        success: false, 
        message: `Employee ${employee_id} not found` 
      });
    }

    const employee = employeeResult.rows[0];
    console.log(`Found employee: ${employee.employee_id} (ID: ${employee.id})`);

    // Find ANY active assignment (not just is_active = true)
    // Also check assignments that might be active but have different flags
    const assignmentResult = await client.query(
      `SELECT id, shift_id, effective_from, is_active, is_deleted
       FROM shift_assignments
       WHERE employee_id = $1 
       AND is_deleted = false
       ORDER BY created_at DESC
       LIMIT 1`,
      [employee.id]
    );

    console.log("Assignment query result:", assignmentResult.rows);

    if (assignmentResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ 
        success: false, 
        message: "No shift assignment found for this employee" 
      });
    }

    const assignment = assignmentResult.rows[0];
    const newEffectiveTo = effective_to || new Date().toISOString().split("T")[0];

    // Force update - set is_active to false regardless of current state
    const result = await client.query(
      `UPDATE shift_assignments
       SET is_active = false,
           effective_to = $1,
           reason = COALESCE($2, reason, 'Deactivated by user'),
           updated_by = $3,
           updated_at = NOW()
       WHERE id = $4
       RETURNING id, is_active, effective_to`,
      [newEffectiveTo, reason, updated_by || userId, assignment.id]
    );

    console.log("Update result:", result.rows);

    // Verify the update worked
    const verifyResult = await client.query(
      `SELECT id, is_active, effective_to 
       FROM shift_assignments 
       WHERE id = $1`,
      [assignment.id]
    );
    
    console.log("Verification after update:", verifyResult.rows);

    await client.query("COMMIT");

    if (result.rowCount === 0) {
      return res.status(404).json({
        success: false,
        message: "Failed to update shift assignment"
      });
    }

    res.json({
      success: true,
      message: `Employee ${employee.employee_id} unassigned successfully`,
      data: { 
        assignment_id: result.rows[0].id,
        is_active: result.rows[0].is_active,
        effective_to: result.rows[0].effective_to
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("DEACTIVATE SHIFT ASSIGNMENT ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: err.message,
      stack: err.stack 
    });
  } finally {
    client.release();
  }
};
/* ===========================
   ✅ BULK ASSIGN SHIFT
   Accepts employee_codes[] (business employee_ids).
   org_id read from session.
=========================== */
const bulkAssignShift = async (req, res) => {
  const client = await pool.connect();

  try {
    console.log("=== BULK ASSIGN SHIFT DEBUG ===");
    console.log("req.body:", req.body);

    const org_id = req.session.user?.orgId;  // ✅ always from session
    const userId = req.session.user?.userId;

    const {
      employee_codes,   // array of business employee_ids sent by frontend
      shift_id,
      assignment_date,
      effective_from,
      effective_to,
      reason,
      approved_by,
      update_attendance = true,
    } = 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: "Organisation ID not found in session. Please log in again.",
      });
    }

    validateDateRange(effective_from || assignment_date, effective_to);

    if (approved_by && !(await validateApprover(approved_by))) {
      return res.status(403).json({
        success: false,
        message: "Approver not found or doesn't have permission",
      });
    }

    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" });
    }

    await client.query("BEGIN");

    // Fetch all employees in one query (employee_codes are business employee_id values)
    const placeholders = employee_codes.map((_, i) => `$${i + 1}`).join(",");
    const employeesResult = await client.query(
      `SELECT id, employee_id, first_name, last_name, org_id
       FROM employees
       WHERE employee_id IN (${placeholders}) AND status = 'Active' AND is_deleted = false`,
      employee_codes
    );

    const employeeMap = new Map(employeesResult.rows.map((e) => [e.employee_id, e]));

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

    for (const empCode of employee_codes) {
      try {
        const employee = employeeMap.get(empCode);

        if (!employee) {
          failCount++;
          results.push({ employee_id: empCode, success: false, message: "Employee not found" });
          continue;
        }

        if (employee.org_id !== org_id) {
          failCount++;
          results.push({
            employee_id: empCode,
            success: false,
            message: "Employee does not belong to your organisation",
          });
          continue;
        }

        // Deactivate any current active assignments (swap / override)
        await client.query(
          `UPDATE shift_assignments
           SET is_active   = false,
               effective_to = $1,
               reason       = COALESCE(reason, 'Overridden by bulk assignment on ' || $2),
               updated_at   = NOW()
           WHERE employee_id = $3 AND is_active = true AND is_deleted = false`,
          [effectiveFromDate, assignment_date, employee.id]
        );

        // Insert the new assignment
        const insertResult = await client.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`,
          [
            employee.id,
            shift_id,
            assignment_date,
            effectiveFromDate,
            effectiveToDate,
            reason || null,
            approved_by || null,
            org_id,           // ✅ from session
            userId || null,
          ]
        );

        let attendanceCount = 0;
        if (update_attendance) {
          const ar = await updateAttendanceShift(
            employee.id,
            shiftResult.rows[0].shift_name,
            effectiveFromDate,
            userId
          );
          attendanceCount = ar.rowCount;
          totalAttendanceUpdated += attendanceCount;
        }

        successCount++;
        results.push({
          employee_id: empCode,
          success: true,
          id: insertResult.rows[0].id,
          attendance_records_updated: attendanceCount,
        });
      } catch (error) {
        failCount++;
        results.push({ employee_id: empCode, success: false, message: error.message });
      }
    }

    await client.query("COMMIT");

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

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

    let query = `
      SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.email,
        e.phone,
        d.department_name   AS department,
        ds.designation_name AS designation,
        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
      JOIN shifts    s  ON sa.shift_id    = s.id
      LEFT JOIN departments  d  ON e.department  = d.id
      LEFT JOIN designations ds ON e.designation = ds.id
      WHERE sa.is_active = true
        AND sa.is_deleted = false
        AND e.is_deleted  = false
        AND s.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 ACTIVE ASSIGNMENTS REPORT
=========================== */
const getActiveAssignmentsReport = async (req, res) => {
  try {
    const { department, as_of_date } = req.query;
    const org_id = req.session.user?.orgId;
    const targetDate = as_of_date || new Date().toISOString().split("T")[0];

    let query = `
      SELECT
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        COUNT(DISTINCT sa.employee_id) as total_employees,
        STRING_AGG(DISTINCT d.department_name, ', ') as departments,
        COUNT(CASE WHEN sa.is_permanent = true  THEN 1 END) as permanent_assignments,
        COUNT(CASE WHEN sa.is_permanent = false THEN 1 END) as temporary_assignments
      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
      LEFT JOIN departments d ON e.department = d.id
      WHERE sa.is_deleted = false
        AND sa.is_active  = true
        AND sa.effective_from <= $1
        AND (sa.effective_to IS NULL OR sa.effective_to >= $1)
        AND e.is_deleted = false
    `;

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

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

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

    query += ` GROUP BY s.id, s.shift_name, s.shift_code, s.start_time, s.end_time
               ORDER BY s.start_time ASC`;

    const result = await pool.query(query, queryParams);

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

/* ===========================
   ✅ BULK DEACTIVATE SHIFT ASSIGNMENTS
=========================== */
const bulkDeactivateShiftAssignments = async (req, res) => {
  const client = await pool.connect();

  try {
    const { employee_codes, effective_to, reason, updated_by, update_attendance = true } = req.body;

    if (!employee_codes?.length) {
      return res.status(400).json({ success: false, message: "employee_codes[] is required" });
    }

    await client.query("BEGIN");

    const placeholders = employee_codes.map((_, i) => `$${i + 1}`).join(",");
    const employeesResult = await client.query(
      `SELECT e.id, e.employee_id, sa.id as assignment_id, sa.shift_id, s.shift_name
FROM employees e
LEFT JOIN shift_assignments sa ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
LEFT JOIN shifts s ON sa.shift_id = s.id
WHERE e.employee_id IN (${placeholders}) AND e.status = 'Active' AND e.is_deleted = false`,
      employee_codes
    );

    const results = [];
    let successCount = 0;
    let failCount = 0;
    let totalAttendanceUpdated = 0;
    const newEffectiveTo = effective_to || new Date().toISOString().split("T")[0];

    for (const employee of employeesResult.rows) {
      try {
        if (!employee.assignment_id) {
          failCount++;
          results.push({
            employee_id: employee.employee_id,
            success: false,
            message: "No active shift assignment found",
          });
          continue;
        }

        await client.query(
          `UPDATE shift_assignments
           SET is_active   = false,
               effective_to = COALESCE($1, effective_to, CURRENT_DATE),
               reason       = COALESCE($2, reason, 'Deactivated by bulk operation'),
               updated_by   = COALESCE($3, updated_by),
               updated_at   = NOW()
           WHERE id = $4`,
          [newEffectiveTo, reason, updated_by, employee.assignment_id]
        );

        let attendanceCount = 0;
        if (update_attendance) {
          const ar = await client.query(
            `UPDATE attendance
             SET shift_type = NULL,
                 remarks    = COALESCE(remarks, 'Shift deactivated - needs reassignment'),
                 updated_by = $1,
                 updated_at = NOW()
             WHERE employee_id = $2 AND attendance_date >= $3 AND is_deleted = false`,
            [updated_by, employee.id, newEffectiveTo]
          );
          attendanceCount = ar.rowCount;
          totalAttendanceUpdated += attendanceCount;
        }

        successCount++;
        results.push({
          employee_id: employee.employee_id,
          success: true,
          attendance_records_updated: attendanceCount,
        });
      } catch (error) {
        failCount++;
        results.push({ employee_id: employee.employee_id, success: false, message: error.message });
      }
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `Successfully deactivated ${successCount} employee(s), ${failCount} failed`,
      data: {
        success_count: successCount,
        fail_count: failCount,
        total_attendance_records_updated: totalAttendanceUpdated,
        results,
      },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("BULK DEACTIVATE SHIFT ASSIGNMENTS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ GET UPCOMING SHIFT CHANGES
=========================== */
const getUpcomingShiftChanges = async (req, res) => {
  try {
    const { days_ahead = 7 } = req.query;
    const org_id = req.session.user?.orgId;

    const futureDate = new Date();
    futureDate.setDate(futureDate.getDate() + parseInt(days_ahead));
    const futureDateStr = futureDate.toISOString().split("T")[0];
    const today = new Date().toISOString().split("T")[0];

    let query = `
      SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        d.department_name   AS department,
        e.email,
        s.shift_name        AS new_shift,
        sa.effective_from,
        sa.reason,
        sa.created_at       AS assigned_on,
        CONCAT(creator.first_name, ' ', creator.last_name) AS assigned_by
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
      JOIN shifts    s  ON sa.shift_id    = s.id
      LEFT JOIN departments d  ON e.department = d.id
      LEFT JOIN employees creator ON sa.created_by = creator.id
      WHERE sa.is_deleted = false
        AND sa.is_active  = true
        AND sa.effective_from > $1
        AND sa.effective_from <= $2
    `;

    const queryParams = [today, futureDateStr];
    let paramIndex = 3;

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

    query += ` ORDER BY sa.effective_from ASC, e.first_name, e.last_name`;

    const result = await pool.query(query, queryParams);

    res.json({
      success: true,
      data: result.rows,
      days_ahead: parseInt(days_ahead),
      total_upcoming_changes: result.rows.length,
    });
  } catch (err) {
    console.error("GET UPCOMING SHIFT CHANGES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ EXPORT ASSIGNMENTS
=========================== */
const exportAssignments = async (req, res) => {
  try {
    const { status = "active", format = "json" } = req.query;
    const org_id = req.session.user?.orgId;

    let query = `
      SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        d.department_name   AS department,
        ds.designation_name AS designation,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        sa.effective_from,
        sa.effective_to,
        sa.is_permanent,
        sa.reason,
        sa.assignment_date,
        CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by
      FROM shift_assignments sa
      JOIN employees e  ON sa.employee_id = e.id
      JOIN shifts    s  ON sa.shift_id    = s.id
      LEFT JOIN departments  d  ON e.department  = d.id
      LEFT JOIN designations ds ON e.designation = ds.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      WHERE sa.is_deleted = false
    `;

    const queryParams = [];
    let paramIndex = 1;

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

    if (status === "active") {
      query += ` AND sa.is_active = true`;
    } else if (status === "inactive") {
      query += ` AND sa.is_active = false`;
    }

    query += ` ORDER BY e.employee_id, sa.effective_from DESC`;

    const result = await pool.query(query, queryParams);

    if (format === "csv") {
      const headers = [
        "Employee ID", "First Name", "Last Name", "Department", "Designation",
        "Shift Name", "Shift Code", "Start Time", "End Time", "Effective From",
        "Effective To", "Is Permanent", "Reason", "Assignment Date", "Approved By",
      ];

      const csvRows = [headers];
      for (const row of result.rows) {
        csvRows.push([
          row.employee_id,
          row.first_name,
          row.last_name,
          row.department,
          row.designation,
          row.shift_name,
          row.shift_code,
          row.start_time,
          row.end_time,
          row.effective_from,
          row.effective_to || "",
          row.is_permanent ? "Yes" : "No",
          row.reason || "",
          row.assignment_date,
          row.approved_by || "",
        ]);
      }

      const csv = csvRows.map((r) => r.join(",")).join("\n");
      res.setHeader("Content-Type", "text/csv");
      res.setHeader(
        "Content-Disposition",
        `attachment; filename=shift_assignments_${new Date().toISOString().split("T")[0]}.csv`
      );
      return res.send(csv);
    }

    res.json({
      success: true,
      data: result.rows,
      total_records: result.rows.length,
      exported_at: new Date().toISOString(),
    });
  } catch (err) {
    console.error("EXPORT ASSIGNMENTS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ EXPORTS
=========================== */
module.exports = {
  assignShiftToEmployee,
  getShiftAssignments,
  getShiftAssignmentById,
  updateShiftAssignment,
  deleteShiftAssignment,
  getEmployeeCurrentShift,
  getEmployeeShiftHistory,
  getAssignmentsByShift,
  getEmployeesByShift,
  deactivateShiftAssignment,
  bulkAssignShift,
  bulkDeactivateShiftAssignments,
  getActiveAssignmentsReport,
  getUpcomingShiftChanges,
  exportAssignments,
};