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

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

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

//     // Check if holiday already exists
//     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)
//        VALUES ($1, $2, $3, $4, NOW())
//        RETURNING *`,
//       [holiday_date, holiday_name || "Holiday", org_id, created_by || null]
//     );

//     // Auto-mark attendance for all employees on this holiday
//     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,
//     });
//   }
// };

// /* ===========================
//    ✅ AUTO-MARK HOLIDAY ATTENDANCE
// =========================== */
// const markHolidayAttendance = async (holiday_date, org_id) => {
//   try {
//     // Get all active employees
//     const employees = await pool.query(
//       `SELECT id, employee_code FROM employees 
//        WHERE org_id = $1 AND is_deleted = false`,
//       [org_id]
//     );

//     for (const emp of employees.rows) {
//       // Check if attendance already exists
//       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) {
//         // Update existing to Holiday
//         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,
//                updated_at = NOW()
//            WHERE id = $1`,
//           [existing.rows[0].id]
//         );
//       } else {
//         // Create new holiday attendance
//         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}`);
//   } catch (error) {
//     console.error("MARK HOLIDAY ATTENDANCE ERROR:", error);
//   }
// };

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

//     let query = `
//       SELECT * FROM holidays 
//       WHERE is_deleted = false AND org_id = $1
//     `;
//     const queryParams = [org_id];
//     let paramIndex = 2;

//     if (year) {
//       query += ` AND EXTRACT(YEAR FROM holiday_date) = $${paramIndex}`;
//       queryParams.push(year);
//       paramIndex++;
//     }

//     if (month) {
//       query += ` AND EXTRACT(MONTH FROM holiday_date) = $${paramIndex}`;
//       queryParams.push(month);
//       paramIndex++;
//     }

//     query += ` ORDER BY holiday_date ASC`;

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

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

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

//     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, holiday_name, updated_by || null, id]
//     );

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

//     // If date changed, remark attendance for old and new date
//     if (holiday_date) {
//       const oldDate = await pool.query(
//         `SELECT holiday_date FROM holidays WHERE id = $1`,
//         [id]
//       );
//       if (oldDate.rows[0] && oldDate.rows[0].holiday_date !== holiday_date) {
//         // Reset old holiday date attendance
//         await resetHolidayAttendance(oldDate.rows[0].holiday_date, result.rows[0].org_id);
//         // Mark new holiday date attendance
//         await markHolidayAttendance(holiday_date, result.rows[0].org_id);
//       }
//     }

//     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) => {
//   try {
//     const { id } = req.params;
//     const { updated_by } = req.body;

//     // Get holiday date 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 result = await pool.query(
//       `UPDATE holidays 
//        SET is_deleted = true, 
//            updated_by = $1, 
//            updated_at = NOW()
//        WHERE id = $2
//        RETURNING id`,
//       [updated_by || null, id]
//     );

//     // Reset attendance for that date back to Present
//     await resetHolidayAttendance(holiday.rows[0].holiday_date, holiday.rows[0].org_id);

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

// /* ===========================
//    ✅ RESET HOLIDAY ATTENDANCE (back to Present)
// =========================== */
// const resetHolidayAttendance = async (holiday_date, org_id) => {
//   try {
//     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}`);
//   } catch (error) {
//     console.error("RESET HOLIDAY ATTENDANCE ERROR:", error);
//   }
// };

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

//     const result = await pool.query(
//       `SELECT * FROM holidays 
//        WHERE holiday_date = $1 AND org_id = $2 AND is_deleted = false`,
//       [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) => {
//   try {
//     const { org_id, days = 30 } = req.query;
//     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
//        ORDER BY holiday_date ASC
//        LIMIT $3`,
//       [today, org_id, 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) => {
//   try {
//     const { year } = req.params;
//     const { org_id } = req.query;

//     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
// =========================== */
// const getHolidayCalendar = async (req, res) => {
//   try {
//     const { year, org_id } = req.query;

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

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

//     res.json({
//       success: true,
//       data: calendar,
//       year: 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) => {
//   try {
//     const { holidays, org_id, created_by } = req.body;

//     if (!holidays || !holidays.length || !org_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Holidays array and org_id are 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) {
//           const result = await pool.query(
//             `INSERT INTO holidays (holiday_date, holiday_name, org_id, created_by, created_at)
//              VALUES ($1, $2, $3, $4, NOW())
//              RETURNING *`,
//             [holiday.holiday_date, holiday.holiday_name || "Holiday", org_id, created_by || null]
//           );
          
//           // Mark attendance for this holiday
//           await markHolidayAttendance(holiday.holiday_date, org_id);
          
//           results.push(result.rows[0]);
//           successCount++;
//         }
//       } catch (holidayError) {
//         console.error(`Error creating holiday ${holiday.holiday_date}:`, holidayError);
//       }
//     }

//     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) => {
//   try {
//     const { year, org_id } = req.query;

//     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 totalHolidays = result.rows.reduce((sum, row) => sum + parseInt(row.holiday_count), 0);

//     res.json({
//       success: true,
//       data: result.rows,
//       summary: {
//         year: year,
//         total_holidays: totalHolidays,
//         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
// };


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