

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

/* ===========================
   ✅ UPDATE BUFFER TABLE (Manual Trigger)
=========================== */
const updateBufferTable = async (req, res) => {
  try {
    const { date, employee_id } = req.body;
    const org_id = req.session.user.orgId;
  const userId = req.session.user.id;

    const conditions = ["a.is_deleted = false"];
    const params = [];
    let i = 1;

    if (date) { conditions.push(`a.attendance_date = $${i++}`); params.push(date); }
    if (employee_id) { conditions.push(`e.employee_code = $${i++}`); params.push(employee_id); }
    if (org_id) { conditions.push(`a.org_id = $${i++}`); params.push(org_id); }

    const query = `
      INSERT INTO attendance_buffer (
        attendance_id, employee_id, employee_code, employee_name,
        shift_type, attendance_date, check_in_time, check_out_time,
        shift_start_time, shift_end_time, total_hours, overtime_hours,
        late_minutes, attendance_status, month_year, org_id,
        is_present, is_absent, is_late, is_holiday, is_leave,
        created_at, updated_at
      )
      SELECT
        a.id,
        e.id,
        e.employee_code,
        CONCAT(e.first_name, ' ', e.last_name),
        a.shift_type,
        a.attendance_date,
        a.check_in_time,
        a.check_out_time,
        a.shift_start_time,
        a.shift_end_time,
        a.work_hours,
        a.overtime_hours,
        a.late_minutes,
        a.attendance_status,
        TO_CHAR(a.attendance_date, 'YYYY-MM'),
        a.org_id,
        (a.attendance_status = 'Present'),
        (a.attendance_status = 'Absent'),
        (a.attendance_status = 'Late'),
        (a.attendance_status = 'Holiday'),
        (a.attendance_status = 'Leave'),
        NOW(),
        NOW()
      FROM attendance a
      JOIN employees e ON a.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
      WHERE ${conditions.join(" AND ")}
      ON CONFLICT (attendance_id) DO UPDATE SET
        check_in_time      = EXCLUDED.check_in_time,
        check_out_time     = EXCLUDED.check_out_time,
        shift_start_time   = EXCLUDED.shift_start_time,
        shift_end_time     = EXCLUDED.shift_end_time,
        total_hours        = EXCLUDED.total_hours,
        overtime_hours     = EXCLUDED.overtime_hours,
        late_minutes       = EXCLUDED.late_minutes,
        attendance_status  = EXCLUDED.attendance_status,
        is_present         = EXCLUDED.is_present,
        is_absent          = EXCLUDED.is_absent,
        is_late            = EXCLUDED.is_late,
        is_holiday         = EXCLUDED.is_holiday,
        is_leave           = EXCLUDED.is_leave,
        updated_at         = NOW()
    `;

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

    res.json({
      success: true,
      message: "Buffer table updated successfully",
      records_updated: result.rowCount,
    });
  } catch (error) {
    console.error("UPDATE BUFFER ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET MONTHLY SUMMARY
=========================== */
const getMonthlySummary = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    const { employee_id, month, year } = req.query;

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

    const monthYear = `${year}-${String(month).padStart(2, "0")}`;
    const conditions = ["month_year = $1", "org_id = $2", "is_deleted = false"];
    const params = [monthYear, org_id];
    let i = 3;

    if (employee_id) { conditions.push(`employee_code = $${i++}`); params.push(employee_id); }

    const result = await pool.query(
      `SELECT
         employee_id,
         employee_code,
         employee_name,
         shift_type,
         COUNT(*)                                                       AS total_days,
         SUM(CASE WHEN is_present  THEN 1 ELSE 0 END)                  AS present_days,
         SUM(CASE WHEN is_absent   THEN 1 ELSE 0 END)                  AS absent_days,
         SUM(CASE WHEN is_late     THEN 1 ELSE 0 END)                  AS late_days,
         SUM(CASE WHEN is_holiday  THEN 1 ELSE 0 END)                  AS holiday_days,
         SUM(CASE WHEN is_leave    THEN 1 ELSE 0 END)                  AS leave_days,
         SUM(CASE WHEN attendance_status = 'Half Day'    THEN 1 ELSE 0 END) AS half_days,
         SUM(CASE WHEN attendance_status = 'Cross Shift' THEN 1 ELSE 0 END) AS cross_shift_days,
         SUM(CASE WHEN attendance_status = 'Double Shift' THEN 1 ELSE 0 END) AS double_shift_days,
         SUM(CASE WHEN attendance_status = 'Shift Swap'  THEN 1 ELSE 0 END) AS shift_swap_days,
         COALESCE(SUM(total_hours), 0)                                 AS total_monthly_hours,
         COALESCE(SUM(overtime_hours), 0)                              AS total_monthly_overtime,
         COALESCE(SUM(late_minutes), 0)                                AS total_late_minutes,
         COUNT(CASE WHEN late_minutes > 0 THEN 1 END)                  AS late_days_count,
         ROUND(AVG(total_hours), 2)                                    AS avg_daily_hours
       FROM attendance_buffer
       WHERE ${conditions.join(" AND ")}
       GROUP BY employee_id, employee_code, employee_name, shift_type
       ORDER BY employee_name`,
      params
    );

    res.json({
      success: true,
      data: result.rows,
      summary: {
        month,
        year,
        month_year: monthYear,
        total_employees: result.rows.length,
      },
    });
  } catch (error) {
    console.error("GET MONTHLY SUMMARY ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET DAILY BUFFER DATA
=========================== */
const getDailyBufferData = async (req, res) => {
  try {
    const { date } = req.query;
const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    if (!date || !org_id) {
      return res.status(400).json({ success: false, message: "date and org_id required" });
    }

    const result = await pool.query(
      `SELECT
         b.*,
         e.department,
         e.designation
       FROM attendance_buffer b
      JOIN employees e ON b.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
       WHERE b.attendance_date = $1
         AND b.org_id = $2
         AND b.is_deleted = false
       ORDER BY e.first_name, e.last_name`,
      [date, org_id]
    );

    res.json({
      success: true,
      data: result.rows,
      total_records: result.rows.length,
    });
  } catch (error) {
    console.error("GET DAILY BUFFER ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEE TIMESHEET
=========================== */
const getEmployeeTimesheet = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    const { employee_code, month, year } = req.query;

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

    const monthYear = `${year}-${String(month).padStart(2, "0")}`;

    const result = await pool.query(
      `SELECT
         attendance_date,
         shift_type,
         check_in_time,
         check_out_time,
         shift_start_time,
         shift_end_time,
         total_hours,
         overtime_hours,
         late_minutes,
         attendance_status,
         is_present, is_absent, is_late, is_holiday, is_leave
       FROM attendance_buffer
       WHERE employee_code = $1
         AND month_year = $2
         AND org_id = $3
         AND is_deleted = false
       ORDER BY attendance_date ASC`,
      [employee_code, monthYear, org_id]
    );

    const rows = result.rows;
    const summary = {
      total_days: rows.length,
      present_days: rows.filter((r) => r.is_present).length,
      absent_days: rows.filter((r) => r.is_absent).length,
      late_days: rows.filter((r) => r.is_late).length,
      holiday_days: rows.filter((r) => r.is_holiday).length,
      leave_days: rows.filter((r) => r.is_leave).length,
      half_days: rows.filter((r) => r.attendance_status === "Half Day").length,
      double_shift_days: rows.filter((r) => r.attendance_status === "Double Shift").length,
      total_hours: parseFloat(rows.reduce((s, r) => s + parseFloat(r.total_hours || 0), 0).toFixed(2)),
      total_overtime: parseFloat(rows.reduce((s, r) => s + parseFloat(r.overtime_hours || 0), 0).toFixed(2)),
      total_late_minutes: rows.reduce((s, r) => s + parseInt(r.late_minutes || 0), 0),
    };

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

/* ===========================
   ✅ GET DEPARTMENT MONTHLY SUMMARY
=========================== */
const getDepartmentMonthlySummary = async (req, res) => {
  try {
    const { department, month, year} = req.query;
const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    if (!month || !year || !org_id) {
      return res.status(400).json({ success: false, message: "month, year, org_id required" });
    }

    const monthYear = `${year}-${String(month).padStart(2, "0")}`;
    const conditions = ["b.month_year = $1", "b.org_id = $2", "b.is_deleted = false"];
    const params = [monthYear, org_id];
    let i = 3;

    if (department) { conditions.push(`e.department = $${i++}`); params.push(department); }

    const result = await pool.query(
      `SELECT
         e.department,
         COUNT(DISTINCT b.employee_id)                                    AS total_employees,
         SUM(CASE WHEN b.is_present  THEN 1 ELSE 0 END)                  AS present_days,
         SUM(CASE WHEN b.is_absent   THEN 1 ELSE 0 END)                  AS absent_days,
         SUM(CASE WHEN b.is_late     THEN 1 ELSE 0 END)                  AS late_days,
         SUM(CASE WHEN b.is_holiday  THEN 1 ELSE 0 END)                  AS holiday_days,
         COALESCE(SUM(b.total_hours), 0)                                  AS total_hours,
         COALESCE(SUM(b.overtime_hours), 0)                               AS total_overtime,
         COALESCE(SUM(b.late_minutes), 0)                                 AS total_late_minutes,
         ROUND(AVG(b.total_hours), 2)                                     AS avg_hours_per_day
       FROM attendance_buffer b
      JOIN employees e ON b.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
       WHERE ${conditions.join(" AND ")}
       GROUP BY e.department
       ORDER BY e.department`,
      params
    );

    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("GET DEPARTMENT SUMMARY ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ RECALCULATE ALL BUFFER DATA
=========================== */
const recalculateAllBuffer = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    // const { org_i} = req.body;
    if (!org_id) {
      return res.status(400).json({ success: false, message: "org_id required" });
    }

    await pool.query(
      `UPDATE attendance_buffer SET is_deleted = true WHERE org_id = $1`,
      [org_id]
    );

    const result = await pool.query(
      `INSERT INTO attendance_buffer (
         attendance_id, employee_id, employee_code, employee_name,
         shift_type, attendance_date, check_in_time, check_out_time,
         shift_start_time, shift_end_time, total_hours, overtime_hours,
         late_minutes, attendance_status, month_year, org_id,
         is_present, is_absent, is_late, is_holiday, is_leave,
         created_at, updated_at
       )
       SELECT
         a.id, e.id, e.employee_code,
         CONCAT(e.first_name, ' ', e.last_name),
         a.shift_type, a.attendance_date, a.check_in_time, a.check_out_time,
         a.shift_start_time, a.shift_end_time, a.work_hours, a.overtime_hours,
         a.late_minutes, a.attendance_status,
         TO_CHAR(a.attendance_date, 'YYYY-MM'),
         a.org_id,
         (a.attendance_status = 'Present'),
         (a.attendance_status = 'Absent'),
         (a.attendance_status = 'Late'),
         (a.attendance_status = 'Holiday'),
         (a.attendance_status = 'Leave'),
         NOW(), NOW()
       FROM attendance a
       JOIN employees e ON a.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
       WHERE a.is_deleted = false AND a.org_id = $1
       ON CONFLICT (attendance_id) DO UPDATE SET
         check_in_time     = EXCLUDED.check_in_time,
         check_out_time    = EXCLUDED.check_out_time,
         total_hours       = EXCLUDED.total_hours,
         overtime_hours    = EXCLUDED.overtime_hours,
         late_minutes      = EXCLUDED.late_minutes,
         attendance_status = EXCLUDED.attendance_status,
         is_present        = EXCLUDED.is_present,
         is_absent         = EXCLUDED.is_absent,
         is_late           = EXCLUDED.is_late,
         is_holiday        = EXCLUDED.is_holiday,
         is_leave          = EXCLUDED.is_leave,
         is_deleted        = false,
         updated_at        = NOW()`,
      [org_id]
    );

    res.json({
      success: true,
      message: "Buffer recalculated successfully",
      records_processed: result.rowCount,
    });
  } catch (error) {
    console.error("RECALCULATE BUFFER ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET OVERTIME REPORT
=========================== */
const getOvertimeReport = async (req, res) => {
  try {
    const { month, year,  min_overtime = 0 } = req.query;
const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    if (!month || !year || !org_id) {
      return res.status(400).json({ success: false, message: "month, year, org_id required" });
    }

    const monthYear = `${year}-${String(month).padStart(2, "0")}`;

    const result = await pool.query(
      `SELECT
         employee_id, employee_code, employee_name, shift_type,
         COALESCE(SUM(overtime_hours), 0)               AS total_overtime,
         COUNT(CASE WHEN overtime_hours > 0 THEN 1 END) AS overtime_days,
         MAX(overtime_hours)                             AS max_overtime_in_day,
         COALESCE(SUM(total_hours), 0)                  AS total_hours
       FROM attendance_buffer
       WHERE month_year = $1 AND org_id = $2 AND is_deleted = false
       GROUP BY employee_id, employee_code, employee_name, shift_type
       HAVING COALESCE(SUM(overtime_hours), 0) >= $3
       ORDER BY total_overtime DESC`,
      [monthYear, org_id, min_overtime]
    );

    res.json({
      success: true,
      data: result.rows,
      summary: {
        total_overtime_hours: result.rows.reduce((s, r) => s + parseFloat(r.total_overtime || 0), 0),
        total_employees: result.rows.length,
      },
    });
  } catch (error) {
    console.error("GET OVERTIME REPORT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET LATE REPORT
=========================== */
const getLateReport = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    const { month, year, min_late_minutes = 0 } = req.query;

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

    const monthYear = `${year}-${String(month).padStart(2, "0")}`;

    const result = await pool.query(
      `SELECT
         employee_id, employee_code, employee_name, shift_type,
         COALESCE(SUM(late_minutes), 0)                 AS total_late_minutes,
         COUNT(CASE WHEN late_minutes > 0 THEN 1 END)   AS late_days,
         MAX(late_minutes)                               AS max_late_minutes,
         COALESCE(SUM(total_hours), 0)                  AS total_hours
       FROM attendance_buffer
       WHERE month_year = $1 AND org_id = $2 AND is_deleted = false
       GROUP BY employee_id, employee_code, employee_name, shift_type
       HAVING COALESCE(SUM(late_minutes), 0) >= $3
       ORDER BY total_late_minutes DESC`,
      [monthYear, org_id, min_late_minutes]
    );

    res.json({
      success: true,
      data: result.rows,
      summary: {
        total_late_minutes: result.rows.reduce((s, r) => s + parseInt(r.total_late_minutes || 0), 0),
        total_employees: result.rows.length,
      },
    });
  } catch (error) {
    console.error("GET LATE REPORT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEE COMPARISON (multi-month)
=========================== */
const getEmployeeComparison = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
    const { employee_code, start_month, start_year, end_month, end_year} = req.query;

    if (!employee_code || !start_month || !start_year || !end_month || !end_year || !org_id) {
      return res.status(400).json({ success: false, message: "All parameters required" });
    }

    const startMY = `${start_year}-${String(start_month).padStart(2, "0")}`;
    const endMY = `${end_year}-${String(end_month).padStart(2, "0")}`;

    const result = await pool.query(
      `SELECT
         month_year,
         COALESCE(SUM(total_hours), 0)                                       AS total_hours,
         COALESCE(SUM(overtime_hours), 0)                                    AS total_overtime,
         COALESCE(SUM(late_minutes), 0)                                      AS total_late_minutes,
         SUM(CASE WHEN attendance_status = 'Present'  THEN 1 ELSE 0 END)    AS present_days,
         SUM(CASE WHEN attendance_status = 'Absent'   THEN 1 ELSE 0 END)    AS absent_days,
         SUM(CASE WHEN attendance_status = 'Late'     THEN 1 ELSE 0 END)    AS late_days,
         SUM(CASE WHEN attendance_status = 'Half Day' THEN 1 ELSE 0 END)    AS half_days,
         SUM(CASE WHEN attendance_status = 'Holiday'  THEN 1 ELSE 0 END)    AS holiday_days
       FROM attendance_buffer
       WHERE employee_code = $1
         AND org_id = $2
         AND is_deleted = false
         AND month_year >= $3
         AND month_year <= $4
       GROUP BY month_year
       ORDER BY month_year ASC`,
      [employee_code, org_id, startMY, endMY]
    );

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

module.exports = {
  updateBufferTable,
  getMonthlySummary,
  getDailyBufferData,
  getEmployeeTimesheet,
  getDepartmentMonthlySummary,
  recalculateAllBuffer,
  getOvertimeReport,
  getLateReport,
  getEmployeeComparison,
};