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

/* ===========================
   ✅ CREATE SHIFT
=========================== */


const createShift = async (req, res) => {
  try {
    console.log("=== CREATE SHIFT DEBUG ===");
    console.log("req.body:", req.body);

    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,
      org_id,
      created_by
    } = req.body;

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

    // Check if shift name already exists
    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`,
      });
    }

    // Check if shift code already exists (if provided)
    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`,
        });
      }
    }

    // Insert shift record - let trigger calculate total_hours and working_hours
    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,
        org_id,
        created_by,
        created_at,
        updated_at
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, 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,
        org_id,
        created_by || 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) => {
  try {
    const { page = 1, limit = 10, org_id, search } = req.query;
    const offset = (page - 1) * limit;

    let query = `
      SELECT
        s.*,
        COUNT(CASE WHEN sa.is_active = true 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
    `;

    const queryParams = [];
    let paramIndex = 1;

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

    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`;

    // ✅ FIXED count query
    const countQuery = `
      SELECT COUNT(*) as total
      FROM (
        ${query}
      ) AS count_table
    `;

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

    query += ` LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    const dataParams = [...queryParams, limit, offset];

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

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(totalRecords / 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) => {
  try {
    const { id } = req.params;
    const {
      shift_name,
      shift_code,
      start_time,
      end_time,
      lunch_start,
      lunch_end,
      first_break_start,
      first_break_end,
      second_break_start,
      second_break_end,
      updated_by
    } = req.body;
    
    // Check if shift exists
    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",
      });
    }
    
    // Check if shift name already exists for another 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 with name "${shift_name}" already exists`,
        });
      }
    }
    
    const old = existing.rows[0];
    
    const result = await pool.query(
      `
      UPDATE shifts
      SET
        shift_name = COALESCE($1, shift_name),
        shift_code = COALESCE($2, shift_code),
        start_time = COALESCE($3, start_time),
        end_time = COALESCE($4, end_time),
        lunch_start = COALESCE($5, lunch_start),
        lunch_end = COALESCE($6, lunch_end),
        first_break_start = COALESCE($7, first_break_start),
        first_break_end = COALESCE($8, first_break_end),
        second_break_start = COALESCE($9, second_break_start),
        second_break_end = COALESCE($10, second_break_end),
        updated_by = COALESCE($11, updated_by),
        updated_at = NOW()
      WHERE id = $12 AND is_deleted = false
      RETURNING *
      `,
      [
        shift_name || old.shift_name,
        shift_code || old.shift_code,
        start_time || old.start_time,
        end_time || old.end_time,
        lunch_start !== undefined ? lunch_start : old.lunch_start,
        lunch_end !== undefined ? lunch_end : old.lunch_end,
        first_break_start !== undefined ? first_break_start : old.first_break_start,
        first_break_end !== undefined ? first_break_end : old.first_break_end,
        second_break_start !== undefined ? second_break_start : old.second_break_start,
        second_break_end !== undefined ? second_break_end : old.second_break_end,
        updated_by || old.updated_by,
        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) => {
  try {
    const { id } = req.params;
    const { updated_by } = req.body;
    
    // Check if shift has active assignments
    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 = COALESCE($1, updated_by),
          updated_at = NOW()
      WHERE id = $2 AND is_deleted = false
      RETURNING id
      `,
      [updated_by || 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) => {
//   try {
//     console.log("=== ASSIGN SHIFT DEBUG ===");
//     console.log("req.body:", req.body);

//     let {
//       employee_code,
//       shift_id,
//       assignment_date,
//       effective_from,
//       effective_to,
//       is_permanent,
//       org_id,
//       created_by
//     } = req.body;

//     // Validation
//     if (!employee_code || !shift_id || !assignment_date || !org_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee code, shift ID, assignment date, and org_id are required",
//       });
//     }

//     // Fetch employee id
//     const employeeResult = await pool.query(
//       `SELECT id, employee_code, first_name, last_name 
//        FROM employees 
//        WHERE employee_code = $1 AND is_deleted = false`,
//       [employee_code]
//     );

//     if (employeeResult.rows.length === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Employee not found",
//       });
//     }

//     const realEmployeeId = employeeResult.rows[0].id;

//     // Check if shift exists
//     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",
//       });
//     }

//     // First, deactivate any existing active assignments for this employee
//     await pool.query(
//       `UPDATE shift_assignments 
//        SET is_active = false, 
//            updated_at = NOW() 
//        WHERE employee_id = $1 
//          AND is_active = true 
//          AND is_deleted = false`,
//       [realEmployeeId]
//     );

//     // Insert new assignment
//     const result = await pool.query(
//       `
//       INSERT INTO shift_assignments (
//         employee_id,
//         shift_id,
//         assignment_date,
//         effective_from,
//         effective_to,
//         is_permanent,
//         is_active,
//         org_id,
//         created_by,
//         created_at,
//         updated_at
//       )
//       VALUES ($1, $2, $3, $4, $5, $6, true, $7, $8, NOW(), NOW())
//       RETURNING *
//       `,
//       [
//         realEmployeeId,
//         shift_id,
//         assignment_date,
//         effective_from || assignment_date,
//         effective_to || null,
//         is_permanent || false,
//         org_id,
//         created_by || null
//       ]
//     );

//     res.status(201).json({
//       success: true,
//       message: `Shift "${shiftResult.rows[0].shift_name}" assigned to ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} successfully`,
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("ASSIGN SHIFT ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };
/* ===========================
   ✅ ASSIGN SHIFT TO EMPLOYEE
=========================== */

const assignShiftToEmployee = async (req, res) => {
  try {
    console.log("=== ASSIGN SHIFT DEBUG ===");
    console.log("req.body:", req.body);

    let {
      employee_code,
      shift_id,
      assignment_date,
      effective_from,
      effective_to,
      is_permanent,
      org_id,
      created_by
    } = req.body;

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

    // Fetch employee id
    const employeeResult = await pool.query(
      `SELECT id, employee_code, first_name, last_name 
       FROM employees 
       WHERE employee_code = $1 AND is_deleted = false`,
      [employee_code]
    );

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

    const realEmployeeId = employeeResult.rows[0].id;
    let shiftName = null;

    // If shift_id is provided, check if shift exists
    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;
    }

    // Check if an assignment already exists for this employee on this date
    const existingAssignment = await pool.query(
      `SELECT id, is_active 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) {
      // Update existing assignment
      result = await pool.query(
        `
        UPDATE shift_assignments
        SET shift_id = $1,
            effective_from = $2,
            effective_to = $3,
            is_permanent = $4,
            is_active = true,
            updated_by = $5,
            updated_at = NOW()
        WHERE id = $6
        RETURNING *
        `,
        [
          shift_id || null,
          effective_from || assignment_date,
          effective_to || null,
          is_permanent || false,
          created_by || null,
          existingAssignment.rows[0].id
        ]
      );

      const message = shift_id 
        ? `Shift "${shiftName}" updated for ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} successfully`
        : `${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} moved to Draft successfully`;

      return res.status(200).json({
        success: true,
        message: message,
        data: result.rows[0],
      });
    } else {
      // First, deactivate any existing active assignments for this employee (different date)
      await pool.query(
        `UPDATE shift_assignments 
         SET is_active = false, 
             updated_at = NOW() 
         WHERE employee_id = $1 
           AND is_active = true 
           AND assignment_date != $2
           AND is_deleted = false`,
        [realEmployeeId, assignment_date]
      );

      // Insert new assignment
      result = await pool.query(
        `
        INSERT INTO shift_assignments (
          employee_id,
          shift_id,
          assignment_date,
          effective_from,
          effective_to,
          is_permanent,
          is_active,
          org_id,
          created_by,
          created_at,
          updated_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, true, $7, $8, NOW(), NOW())
        RETURNING *
        `,
        [
          realEmployeeId,
          shift_id || null,
          assignment_date,
          effective_from || assignment_date,
          effective_to || null,
          is_permanent || false,
          org_id,
          created_by || null
        ]
      );

      const message = shift_id 
        ? `Shift "${shiftName}" assigned to ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} successfully`
        : `${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} moved to Draft successfully`;

      res.status(201).json({
        success: true,
        message: message,
        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 { employee_code } = req.params;

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

//     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.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_code = $1 
//         AND sa.is_active = true 
//         AND sa.is_deleted = false
//         AND s.is_deleted = false
//       ORDER BY sa.created_at DESC
//       LIMIT 1
//       `,
//       [employee_code]
//     );

//     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,
//     });
//   }
// };
/* ===========================
   ✅ GET EMPLOYEE CURRENT SHIFT
=========================== */

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

    console.log("Fetching current shift for employee:", employee_code);

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

    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.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.employee_code = $1 
        AND sa.is_active = true 
        AND sa.is_deleted = false
      ORDER BY sa.created_at DESC
      LIMIT 1
      `,
      [employee_code]
    );

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

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

    // If shift_id is null, it means employee is in Draft
    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) => {
  try {
    const { shift_id, org_id } = req.query;

    let query = `
      SELECT
        e.employee_code,
        e.first_name,
        e.last_name,
        e.department,
        e.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
      JOIN shifts s ON sa.shift_id = s.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 SHIFT ASSIGNMENT HISTORY
=========================== */

const getShiftAssignmentHistory = async (req, res) => {
  try {
    const { employee_code } = req.params;
    const { limit = 10 } = req.query;

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

    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
      JOIN shifts s ON sa.shift_id = s.id
      JOIN employees e ON sa.employee_id = e.id
      WHERE e.employee_code = $1 
        AND sa.is_deleted = false
      ORDER BY sa.created_at DESC
      LIMIT $2
      `,
      [employee_code, 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 SUMMARY 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.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
      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) => {
  try {
    const { employee_code, effective_to, updated_by } = req.body;

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

    // Get employee id
    const employeeResult = await pool.query(
      `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
      [employee_code]
    );

    if (employeeResult.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 = COALESCE($2, updated_by),
          updated_at = NOW()
      WHERE employee_id = $3 
        AND is_active = true
        AND is_deleted = false
      RETURNING id, is_active
      `,
      [effective_to, updated_by, employeeResult.rows[0].id]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({
        success: false,
        message: "No active shift assignment found for this employee",
      });
    }

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


// /* ===========================
//    ✅ ASSIGN SHIFT TO EMPLOYEE
// =========================== */

// const assignShiftToEmployee = async (req, res) => {
//   try {
//     console.log("=== ASSIGN SHIFT DEBUG ===");
//     console.log("req.body:", req.body);

//     let {
//       employee_code,
//       shift_id,
//       assignment_date,
//       effective_from,
//       effective_to,
//       is_permanent,
//       org_id,
//       created_by
//     } = req.body;

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

//     // Fetch employee id
//     const employeeResult = await pool.query(
//       `SELECT id, employee_code, first_name, last_name 
//        FROM employees 
//        WHERE employee_code = $1 AND is_deleted = false`,
//       [employee_code]
//     );

//     if (employeeResult.rows.length === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Employee not found",
//       });
//     }

//     const realEmployeeId = employeeResult.rows[0].id;

//     // If shift_id is provided, check if shift exists
//     let shiftName = null;
//     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;
//     }

//     // First, deactivate any existing active assignments for this employee
//     await pool.query(
//       `UPDATE shift_assignments 
//        SET is_active = false, 
//            updated_at = NOW() 
//        WHERE employee_id = $1 
//          AND is_active = true 
//          AND is_deleted = false`,
//       [realEmployeeId]
//     );

//     // Insert new assignment (shift_id can be null for Draft)
//     const result = await pool.query(
//       `
//       INSERT INTO shift_assignments (
//         employee_id,
//         shift_id,
//         assignment_date,
//         effective_from,
//         effective_to,
//         is_permanent,
//         is_active,
//         org_id,
//         created_by,
//         created_at,
//         updated_at
//       )
//       VALUES ($1, $2, $3, $4, $5, $6, true, $7, $8, NOW(), NOW())
//       RETURNING *
//       `,
//       [
//         realEmployeeId,
//         shift_id || null,
//         assignment_date,
//         effective_from || assignment_date,
//         effective_to || null,
//         is_permanent || false,
//         org_id,
//         created_by || null
//       ]
//     );

//     const message = shift_id 
//       ? `Shift "${shiftName}" assigned to ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} successfully`
//       : `${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} moved to Draft successfully`;

//     res.status(201).json({
//       success: true,
//       message: message,
//       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 { employee_code } = req.params;

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

//     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.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.employee_code = $1 
//         AND sa.is_active = true 
//         AND sa.is_deleted = false
//       ORDER BY sa.created_at DESC
//       LIMIT 1
//       `,
//       [employee_code]
//     );

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

//     // If shift_id is null, it means employee is in Draft
//     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,
//     });
//   }
// };

// /* ===========================
//    ✅ DEACTIVATE SHIFT ASSIGNMENT (Move to Draft)
// =========================== */

// const deactivateShiftAssignment = async (req, res) => {
//   try {
//     const { employee_code, effective_to, updated_by } = req.body;

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

//     // Get employee id
//     const employeeResult = await pool.query(
//       `SELECT id, first_name, last_name FROM employees WHERE employee_code = $1 AND is_deleted = false`,
//       [employee_code]
//     );

//     if (employeeResult.rows.length === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Employee not found",
//       });
//     }

//     // Deactivate current assignment
//     const result = await pool.query(
//       `
//       UPDATE shift_assignments
//       SET is_active = false,
//           effective_to = COALESCE($1, effective_to, CURRENT_DATE),
//           updated_by = COALESCE($2, updated_by),
//           updated_at = NOW()
//       WHERE employee_id = $3 
//         AND is_active = true
//         AND is_deleted = false
//       RETURNING id, is_active
//       `,
//       [effective_to, updated_by, employeeResult.rows[0].id]
//     );

//     if (result.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "No active shift assignment found for this employee",
//       });
//     }

//     // Create a new Draft assignment (shift_id = null)
//     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
//       )
//       SELECT 
//         $1,
//         NULL,
//         CURRENT_DATE,
//         CURRENT_DATE,
//         false,
//         true,
//         org_id,
//         $2,
//         NOW(),
//         NOW()
//       FROM shift_assignments 
//       WHERE employee_id = $1 
//       ORDER BY created_at DESC 
//       LIMIT 1
//       `,
//       [employeeResult.rows[0].id, updated_by]
//     );

//     res.json({
//       success: true,
//       message: `${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} moved to Draft 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 TO EMPLOYEES
=========================== */

const bulkAssignShift = async (req, res) => {
  try {
    console.log("=== BULK ASSIGN SHIFT DEBUG ===");
    console.log("req.body:", req.body);

    let {
      employee_codes,
      shift_id,
      assignment_date,
      effective_from,
      org_id,
      created_by
    } = req.body;

    // Validation
    if (!employee_codes || !employee_codes.length || !shift_id || !assignment_date || !org_id) {
      return res.status(400).json({
        success: false,
        message: "Employee codes array, shift ID, assignment date, and org_id are required",
      });
    }

    const results = [];
    let successCount = 0;
    let failCount = 0;

    for (const employee_code of employee_codes) {
      try {
        // Get employee id
        const employeeResult = await pool.query(
          `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
          [employee_code]
        );

        if (employeeResult.rows.length === 0) {
          failCount++;
          results.push({ employee_code, success: false, message: "Employee not found" });
          continue;
        }

        const realEmployeeId = employeeResult.rows[0].id;

        // Deactivate old assignments
        await pool.query(
          `UPDATE shift_assignments 
           SET is_active = false, 
               updated_at = NOW() 
           WHERE employee_id = $1 
             AND is_active = true 
             AND is_deleted = false`,
          [realEmployeeId]
        );

        // Insert new assignment
        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())
          `,
          [realEmployeeId, shift_id, assignment_date, effective_from || assignment_date, org_id, created_by || null]
        );

        successCount++;
        results.push({ employee_code, success: true });
      } catch (error) {
        failCount++;
        results.push({ employee_code, 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: results
      }
    });
  } catch (error) {
    console.error("BULK ASSIGN SHIFT ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

/* ===========================
   ✅ UPDATE EMPLOYEE SHIFT AND PROPAGATE TO ATTENDANCE
=========================== */

const updateEmployeeShiftWithAttendance = async (req, res) => {
  try {
    console.log("=== UPDATE EMPLOYEE SHIFT WITH ATTENDANCE DEBUG ===");
    console.log("req.body:", req.body);

    let {
      employee_code,
      new_shift_id,
      effective_from,
      updated_by
    } = req.body;

    if (!employee_code || !new_shift_id) {
      return res.status(400).json({
        success: false,
        message: "Employee code and new shift ID are required"
      });
    }

    // Get employee details
    const employeeResult = await pool.query(
      `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
      [employee_code]
    );

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

    const employeeId = employeeResult.rows[0].id;

    // Get new shift details
    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];

    // Deactivate old shift assignment
    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, updated_by, employeeId]
    );

    // Create new shift assignment
    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
      )
      SELECT $1, $2, $3, $4, true, true, org_id, $5, NOW(), NOW()
      FROM employees WHERE id = $1`,
      [employeeId, new_shift_id, effectiveDate, effectiveDate, updated_by]
    );

    // Update FUTURE attendance records (from effective_from onwards)
    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, updated_by, employeeId, effectiveDate]
    );

    res.status(200).json({
      success: true,
      message: `Employee shift updated to ${newShiftName}. ${updateResult.rowCount} future 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
    });
  }
};

/* ===========================
   ✅ UPDATE BULK EMPLOYEE SHIFT WITH ATTENDANCE
=========================== */

const bulkUpdateEmployeeShiftWithAttendance = async (req, res) => {
  try {
    console.log("=== BULK UPDATE EMPLOYEE SHIFT WITH ATTENDANCE DEBUG ===");
    console.log("req.body:", req.body);

    let {
      employee_codes,
      new_shift_id,
      effective_from,
      updated_by
    } = req.body;

    if (!employee_codes || !employee_codes.length || !new_shift_id) {
      return res.status(400).json({
        success: false,
        message: "Employee codes array and new shift ID are required"
      });
    }

    // Get new shift details
    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 employee_code of employee_codes) {
      // Get employee details
      const employeeResult = await pool.query(
        `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
        [employee_code]
      );

      if (employeeResult.rows.length === 0) {
        results.push({ employee_code, success: false, message: "Employee not found" });
        continue;
      }

      const employeeId = employeeResult.rows[0].id;

      // Deactivate old shift assignment
      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, updated_by, employeeId]
      );

      // Create new shift assignment
      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
        )
        SELECT $1, $2, $3, $4, true, true, org_id, $5, NOW(), NOW()
        FROM employees WHERE id = $1`,
        [employeeId, new_shift_id, effectiveDate, effectiveDate, updated_by]
      );

      // Update FUTURE attendance records
      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, updated_by, employeeId, effectiveDate]
      );

      totalAttendanceUpdated += updateResult.rowCount;
      results.push({ 
        employee_code, 
        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: 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 EMPLOYEE CODE
=========================== */

const getEmployeeCurrentShiftByCode = async (req, res) => {
  try {
    const { employee_code } = req.params;
    const { as_of_date } = req.query;
    
    if (!employee_code) {
      return res.status(400).json({
        success: false,
        message: "Employee code 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,
        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
      JOIN employees e ON sa.employee_id = e.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      WHERE e.employee_code = $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
      `,
      [employee_code, currentDate]
    );
    
    if (!result.rows.length) {
      // Return default shift info instead of 404
      return res.status(200).json({
        success: true,
        data: {
          shift_name: "General",
          shift_id: null,
          is_active: false,
          message: "No shift assigned, using General shift"
        },
      });
    }
    
    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
};