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

/* ===========================
   CREATE LEAVE GROUP
=========================== */
const createLeaveGroup = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const userId = req.session.user.userId;

    const {
      group_name,
      group_code,
      description,
      annual_leave,
      sick_leave,
      casual_leave,
      maternity_leave,
      paternity_leave,
      comp_off,
      unpaid_leave,
      other_leave,
      maternity_paternity_eligible,
      comp_off_eligibility,
      leave_encashment_applicable,
      carry_forward_allowed,
      max_carry_forward_days,
    } = req.body;

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

    // Check duplicate name within org
    const duplicate = await pool.query(
      `SELECT id FROM leave_groups WHERE org_id = $1 AND group_name = $2 AND is_deleted = false`,
      [org_id, group_name]
    );
    if (duplicate.rows.length) {
      return res.status(409).json({
        success: false,
        message: `Leave group "${group_name}" already exists`,
      });
    }

    const result = await pool.query(
      `INSERT INTO leave_groups (
        org_id, group_name, group_code, description,
        annual_leave, sick_leave, casual_leave,
        maternity_leave, paternity_leave, comp_off,
        unpaid_leave, other_leave,
        maternity_paternity_eligible, comp_off_eligibility,
        leave_encashment_applicable, carry_forward_allowed,
        max_carry_forward_days, created_by, updated_by
      ) VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$18
      ) RETURNING *`,
      [
        org_id,
        group_name,
        group_code || null,
        description || null,
        annual_leave || 0,
        sick_leave || 0,
        casual_leave || 0,
        maternity_leave || 0,
        paternity_leave || 0,
        comp_off || 0,
        unpaid_leave || 0,
        other_leave || 0,
        maternity_paternity_eligible || "No",
        comp_off_eligibility || "No",
        leave_encashment_applicable || "No",
        carry_forward_allowed || "No",
        max_carry_forward_days || 0,
        userId,
      ]
    );

    res.status(201).json({
      success: true,
      message: "Leave group created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE LEAVE GROUP ERROR:", error);
    res.status(500).json({ success: false, message: error.message, detail: error.detail || null });
  }
};

/* ===========================
   GET ALL LEAVE GROUPS
   (paginated — for table view)
=========================== */
const getAllLeaveGroups = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { page = 1, limit = 10, search, is_active } = req.query;
    const offset = (page - 1) * limit;

    let query = `
      SELECT
        lg.*,
        COUNT(eb.id) AS employee_count
      FROM leave_groups lg
      LEFT JOIN employee_benefits eb
        ON eb.leave_group_id = lg.id AND eb.is_deleted = false
      WHERE lg.is_deleted = false AND lg.org_id = $1
    `;
    const params = [org_id];
    let idx = 2;

    if (search) {
      query += ` AND (lg.group_name ILIKE $${idx} OR lg.group_code ILIKE $${idx})`;
      params.push(`%${search}%`);
      idx++;
    }

    if (is_active !== undefined) {
      query += ` AND lg.is_active = $${idx}`;
      params.push(is_active === "true");
      idx++;
    }

    query += ` GROUP BY lg.id`;

    // Count before pagination
    const countResult = await pool.query(
      `SELECT COUNT(*) AS total FROM (${query}) AS sub`,
      params
    );
    const totalRecords = parseInt(countResult.rows[0].total);

    query += ` ORDER BY lg.group_name ASC LIMIT $${idx} OFFSET $${idx + 1}`;
    params.push(limit, offset);

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

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(totalRecords / limit),
        totalRecords,
        recordsPerPage: parseInt(limit),
      },
    });
  } catch (error) {
    console.error("GET ALL LEAVE GROUPS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   GET LEAVE GROUPS LIST
   (lightweight — for dropdowns/auto-fetch in Benefits form)
=========================== */
const getLeaveGroupsList = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    const result = await pool.query(
      `SELECT
        id, group_name, group_code,
        annual_leave, sick_leave, casual_leave,
        maternity_leave, paternity_leave, comp_off,
        maternity_paternity_eligible, comp_off_eligibility,
        leave_encashment_applicable, carry_forward_allowed,
        max_carry_forward_days
      FROM leave_groups
      WHERE org_id = $1 AND is_active = true AND is_deleted = false
      ORDER BY group_name ASC`,
      [org_id]
    );

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

/* ===========================
   GET LEAVE GROUP BY ID
=========================== */
const getLeaveGroupById = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;

    const result = await pool.query(
      `SELECT
        lg.*,
        COUNT(eb.id) AS employee_count
      FROM leave_groups lg
      LEFT JOIN employee_benefits eb
        ON eb.leave_group_id = lg.id AND eb.is_deleted = false
      WHERE lg.id = $1 AND lg.org_id = $2 AND lg.is_deleted = false
      GROUP BY lg.id`,
      [id, org_id]
    );

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

    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("GET LEAVE GROUP BY ID ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   UPDATE LEAVE GROUP
=========================== */
const updateLeaveGroup = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.userId;
    const u = req.body;

    const existing = await pool.query(
      `SELECT id FROM leave_groups WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );
    if (!existing.rows.length) {
      return res.status(404).json({ success: false, message: "Leave group not found" });
    }

    // Check duplicate name (exclude self)
    if (u.group_name) {
      const dup = await pool.query(
        `SELECT id FROM leave_groups
         WHERE org_id = $1 AND group_name = $2 AND is_deleted = false AND id != $3`,
        [org_id, u.group_name, id]
      );
      if (dup.rows.length) {
        return res.status(409).json({
          success: false,
          message: `Leave group "${u.group_name}" already exists`,
        });
      }
    }

    const result = await pool.query(
      `UPDATE leave_groups SET
        group_name                   = COALESCE($1,  group_name),
        group_code                   = COALESCE($2,  group_code),
        description                  = COALESCE($3,  description),
        annual_leave                 = COALESCE($4,  annual_leave),
        sick_leave                   = COALESCE($5,  sick_leave),
        casual_leave                 = COALESCE($6,  casual_leave),
        maternity_leave              = COALESCE($7,  maternity_leave),
        paternity_leave              = COALESCE($8,  paternity_leave),
        comp_off                     = COALESCE($9,  comp_off),
        unpaid_leave                 = COALESCE($10, unpaid_leave),
        other_leave                  = COALESCE($11, other_leave),
        maternity_paternity_eligible = COALESCE($12, maternity_paternity_eligible),
        comp_off_eligibility         = COALESCE($13, comp_off_eligibility),
        leave_encashment_applicable  = COALESCE($14, leave_encashment_applicable),
        carry_forward_allowed        = COALESCE($15, carry_forward_allowed),
        max_carry_forward_days       = COALESCE($16, max_carry_forward_days),
        is_active                    = COALESCE($17, is_active),
        updated_by                   = $18,
        updated_at                   = NOW()
      WHERE id = $19 RETURNING *`,
      [
        u.group_name || null,
        u.group_code || null,
        u.description || null,
        u.annual_leave !== undefined ? u.annual_leave : null,
        u.sick_leave !== undefined ? u.sick_leave : null,
        u.casual_leave !== undefined ? u.casual_leave : null,
        u.maternity_leave !== undefined ? u.maternity_leave : null,
        u.paternity_leave !== undefined ? u.paternity_leave : null,
        u.comp_off !== undefined ? u.comp_off : null,
        u.unpaid_leave !== undefined ? u.unpaid_leave : null,
        u.other_leave !== undefined ? u.other_leave : null,
        u.maternity_paternity_eligible || null,
        u.comp_off_eligibility || null,
        u.leave_encashment_applicable || null,
        u.carry_forward_allowed || null,
        u.max_carry_forward_days !== undefined ? u.max_carry_forward_days : null,
        u.is_active !== undefined ? u.is_active : null,
        userId,
        id,
      ]
    );

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

/* ===========================
   TOGGLE ACTIVE STATUS
=========================== */
const toggleLeaveGroupStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.userId;

    const existing = await pool.query(
      `SELECT id, is_active FROM leave_groups WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );
    if (!existing.rows.length) {
      return res.status(404).json({ success: false, message: "Leave group not found" });
    }

    const newStatus = !existing.rows[0].is_active;

    const result = await pool.query(
      `UPDATE leave_groups SET is_active = $1, updated_by = $2, updated_at = NOW()
       WHERE id = $3 RETURNING id, group_name, is_active`,
      [newStatus, userId, id]
    );

    res.json({
      success: true,
      message: `Leave group ${newStatus ? "activated" : "deactivated"} successfully`,
      data: result.rows[0],
    });
  } catch (error) {
    console.error("TOGGLE LEAVE GROUP ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   DELETE LEAVE GROUP (soft)
=========================== */
const deleteLeaveGroup = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;

    const existing = await pool.query(
      `SELECT id FROM leave_groups WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );
    if (!existing.rows.length) {
      return res.status(404).json({ success: false, message: "Leave group not found" });
    }

    // Block delete if employees are assigned to this group
    const assigned = await pool.query(
      `SELECT COUNT(*) AS cnt FROM employee_benefits
       WHERE leave_group_id = $1 AND is_deleted = false`,
      [id]
    );
    if (parseInt(assigned.rows[0].cnt) > 0) {
      return res.status(400).json({
        success: false,
        message: `Cannot delete — ${assigned.rows[0].cnt} employee(s) are assigned to this leave group. Reassign them first.`,
      });
    }

    await pool.query(
      `UPDATE leave_groups SET is_deleted = true, updated_at = NOW() WHERE id = $1`,
      [id]
    );

    res.json({ success: true, message: "Leave group deleted successfully" });
  } catch (error) {
    console.error("DELETE LEAVE GROUP ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

module.exports = {
  createLeaveGroup,
  getAllLeaveGroups,
  getLeaveGroupsList,
  getLeaveGroupById,
  updateLeaveGroup,
  toggleLeaveGroupStatus,
  deleteLeaveGroup,
};