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

/* ===========================
   ✅ INTERNAL: Mark Holiday Attendance
=========================== */
const markHolidayAttendance = async (holiday_date, org_id) => {
  // const org_id = req.session.user.orgId;
  // const userId = req.session.user.id;
  const employees = await pool.query(
    `SELECT id FROM employees WHERE org_id = $1 AND status = 'Active' AND is_deleted = false`,
    [org_id]
  );

  for (const emp of employees.rows) {
    const existing = await pool.query(
      `SELECT id FROM attendance
       WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
      [emp.id, holiday_date]
    );

    if (existing.rows.length > 0) {
      await pool.query(
        `UPDATE attendance SET
           attendance_status = 'Holiday',
           check_in_time = NULL, check_out_time = NULL,
           work_hours = 0, is_late = false, late_minutes = 0,
           is_checked_out = false,
           updated_at = NOW()
         WHERE id = $1`,
        [existing.rows[0].id]
      );
    } else {
      await pool.query(
        `INSERT INTO attendance (
           employee_id, attendance_date, attendance_status,
           shift_type, work_hours, org_id, created_at, updated_at
         ) VALUES ($1, $2, 'Holiday', 'General', 0, $3, NOW(), NOW())`,
        [emp.id, holiday_date, org_id]
      );
    }
  }
  console.log(`Holiday attendance marked for ${employees.rows.length} employees on ${holiday_date}`);
};

/* ===========================
   ✅ INTERNAL: Reset Holiday Attendance (back to Present)
=========================== */
const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
const resetHolidayAttendance = async (holiday_date, org_id) => {
  await pool.query(
    `UPDATE attendance SET
       attendance_status = 'Present',
       updated_at = NOW()
     WHERE attendance_date = $1
       AND org_id = $2
       AND attendance_status = 'Holiday'
       AND is_deleted = false`,
    [holiday_date, org_id]
  );
  console.log(`Holiday attendance reset for ${holiday_date}`);
};

/* ===========================
   ✅ CREATE HOLIDAY
=========================== */
const createHoliday = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { holiday_date, holiday_name, created_by } = req.body;

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

    const existing = await pool.query(
      `SELECT id FROM holidays
       WHERE holiday_date = $1 AND org_id = $2 AND is_deleted = false`,
      [holiday_date, org_id]
    );
    if (existing.rows.length > 0) {
      return res.status(409).json({ success: false, message: "Holiday already exists for this date" });
    }

    const result = await pool.query(
      `INSERT INTO holidays (holiday_date, holiday_name, org_id, created_by, created_at, updated_at)
       VALUES ($1, $2, $3, $4, NOW(), NOW()) RETURNING *`,
      [holiday_date, holiday_name || "Holiday", org_id, created_by || null]
    );

    await markHolidayAttendance(holiday_date, org_id);

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

/* ===========================
   ✅ GET ALL HOLIDAYS
=========================== */
const getHolidays = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { year, month } = req.query;

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

    const conditions = ["is_deleted = false", "org_id = $1"];
    const params = [org_id];
    let i = 2;

    if (year) { conditions.push(`EXTRACT(YEAR FROM holiday_date) = $${i++}`); params.push(year); }
    if (month) { conditions.push(`EXTRACT(MONTH FROM holiday_date) = $${i++}`); params.push(month); }

    const result = await pool.query(
      `SELECT * FROM holidays WHERE ${conditions.join(" AND ")} ORDER BY holiday_date ASC`,
      params
    );

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

/* ===========================
   ✅ UPDATE HOLIDAY
=========================== */
const updateHoliday = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { id } = req.params;
    const { holiday_date, holiday_name, updated_by } = req.body;

    // Fetch OLD data BEFORE update
    const oldRecord = await pool.query(
      `SELECT holiday_date, org_id FROM holidays WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    if (oldRecord.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Holiday not found" });
    }

    const oldDate = oldRecord.rows[0].holiday_date;
    const orgId = oldRecord.rows[0].org_id;
    const dateChanged = holiday_date && holiday_date !== String(oldDate).split("T")[0];

    const result = await pool.query(
      `UPDATE holidays SET
         holiday_date = COALESCE($1, holiday_date),
         holiday_name = COALESCE($2, holiday_name),
         updated_by   = $3,
         updated_at   = NOW()
       WHERE id = $4 AND is_deleted = false RETURNING *`,
      [holiday_date || null, holiday_name || null, updated_by || null, id]
    );

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

    // If date changed — reset old date attendance, mark new date attendance
    if (dateChanged) {
      await resetHolidayAttendance(oldDate, orgId);
      await markHolidayAttendance(holiday_date, orgId);
    }

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

/* ===========================
   ✅ DELETE HOLIDAY
=========================== */
const deleteHoliday = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { id } = req.params;
    const { updated_by } = req.body;

    // Get holiday info BEFORE deletion
    const holiday = await pool.query(
      `SELECT holiday_date, org_id FROM holidays WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    if (holiday.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Holiday not found" });
    }

    const { holiday_date, org_id } = holiday.rows[0];

    await pool.query(
      `UPDATE holidays SET is_deleted = true, updated_by = $1, updated_at = NOW() WHERE id = $2`,
      [updated_by || null, id]
    );

    await resetHolidayAttendance(holiday_date, org_id);

    res.json({ success: true, message: "Holiday deleted and attendance reset to Present" });
  } catch (error) {
    console.error("DELETE HOLIDAY ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ CHECK IF DATE IS HOLIDAY
=========================== */
const isHoliday = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { date} = req.query;

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

    const result = await pool.query(
      `SELECT * FROM holidays
       WHERE holiday_date = $1 AND org_id = $2 AND is_deleted = false AND is_active = true`,
      [date, org_id]
    );

    res.json({
      success: true,
      is_holiday: result.rows.length > 0,
      data: result.rows[0] || null,
    });
  } catch (error) {
    console.error("CHECK HOLIDAY ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET UPCOMING HOLIDAYS
=========================== */
const getUpcomingHolidays = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const {  days = 30 } = req.query;

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

    const today = new Date().toISOString().split("T")[0];

    const result = await pool.query(
      `SELECT * FROM holidays
       WHERE holiday_date >= $1 AND org_id = $2
         AND is_deleted = false AND is_active = true
       ORDER BY holiday_date ASC
       LIMIT $3`,
      [today, org_id, parseInt(days)]
    );

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

/* ===========================
   ✅ GET HOLIDAYS BY YEAR
=========================== */
const getHolidaysByYear = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { year } = req.params;
    // const { org_id } = req.query;

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

    const result = await pool.query(
      `SELECT * FROM holidays
       WHERE EXTRACT(YEAR FROM holiday_date) = $1
         AND org_id = $2 AND is_deleted = false
       ORDER BY holiday_date ASC`,
      [year, org_id]
    );

    res.json({ success: true, data: result.rows, count: result.rows.length });
  } catch (error) {
    console.error("GET HOLIDAYS BY YEAR ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET HOLIDAY CALENDAR (grouped by month)
=========================== */
const getHolidayCalendar = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { year } = req.query;

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

    const result = await pool.query(
      `SELECT EXTRACT(MONTH FROM holiday_date) AS month, holiday_date, holiday_name
       FROM holidays
       WHERE EXTRACT(YEAR FROM holiday_date) = $1
         AND org_id = $2 AND is_deleted = false
       ORDER BY holiday_date ASC`,
      [year, org_id]
    );

    const calendar = {};
    result.rows.forEach((row) => {
      const m = parseInt(row.month);
      if (!calendar[m]) calendar[m] = [];
      calendar[m].push({ date: row.holiday_date, name: row.holiday_name });
    });

    res.json({ success: true, data: calendar, year });
  } catch (error) {
    console.error("GET HOLIDAY CALENDAR ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ BULK CREATE HOLIDAYS
=========================== */
const bulkCreateHolidays = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { holidays, created_by } = req.body;

    if (!holidays?.length || !org_id) {
      return res.status(400).json({ success: false, message: "holidays array and org_id required" });
    }

    const results = [];
    let successCount = 0;

    for (const holiday of holidays) {
      try {
        const existing = await pool.query(
          `SELECT id FROM holidays
           WHERE holiday_date = $1 AND org_id = $2 AND is_deleted = false`,
          [holiday.holiday_date, org_id]
        );

        if (existing.rows.length > 0) {
          results.push({ date: holiday.holiday_date, success: false, message: "Already exists" });
          continue;
        }

        const result = await pool.query(
          `INSERT INTO holidays (holiday_date, holiday_name, org_id, created_by, created_at, updated_at)
           VALUES ($1, $2, $3, $4, NOW(), NOW()) RETURNING *`,
          [holiday.holiday_date, holiday.holiday_name || "Holiday", org_id, created_by || null]
        );

        await markHolidayAttendance(holiday.holiday_date, org_id);

        results.push({ date: holiday.holiday_date, success: true, data: result.rows[0] });
        successCount++;
      } catch (err) {
        results.push({ date: holiday.holiday_date, success: false, message: err.message });
      }
    }

    res.json({
      success: true,
      message: `${successCount} holidays created successfully`,
      data: results,
    });
  } catch (error) {
    console.error("BULK CREATE HOLIDAYS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET HOLIDAY STATISTICS
=========================== */
const getHolidayStatistics = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { year } = req.query;

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

    const result = await pool.query(
      `SELECT
         EXTRACT(MONTH FROM holiday_date) AS month,
         COUNT(*) AS holiday_count,
         STRING_AGG(holiday_name, ', ') AS holiday_names
       FROM holidays
       WHERE EXTRACT(YEAR FROM holiday_date) = $1
         AND org_id = $2 AND is_deleted = false
       GROUP BY EXTRACT(MONTH FROM holiday_date)
       ORDER BY month ASC`,
      [year, org_id]
    );

    const total = result.rows.reduce((s, r) => s + parseInt(r.holiday_count), 0);

    res.json({
      success: true,
      data: result.rows,
      summary: {
        year,
        total_holidays: total,
        months_with_holidays: result.rows.length,
      },
    });
  } catch (error) {
    console.error("GET HOLIDAY STATISTICS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

module.exports = {
  createHoliday,
  getHolidays,
  updateHoliday,
  deleteHoliday,
  isHoliday,
  getUpcomingHolidays,
  markHolidayAttendance,
  resetHolidayAttendance,
  getHolidaysByYear,
  getHolidayCalendar,
  bulkCreateHolidays,
  getHolidayStatistics,
};