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

/* ══════════════════════════════════════════
   GET ALL PACKAGES (for dropdown list)
══════════════════════════════════════════ */
const getAllPackages = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    const result = await pool.query(
      `SELECT
         id,
         package_name,
         package_code,
         description,
         housing_allowance_hra,
         transport_allowance,
         meal_allowance,
         travel_allowance,
         mobile_internet_reimbursement,
         special_allowance,
         other_allowance,
         other_allowance_description,
         bonus_eligibility,
         bonus_percentage_amount,
         is_active
       FROM allowance_packages
       WHERE org_id    = $1
         AND is_active  = TRUE
         AND is_deleted = FALSE
       ORDER BY package_name ASC`,
      [org_id]
    );

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

/* ══════════════════════════════════════════
   GET SINGLE PACKAGE BY ID
══════════════════════════════════════════ */
const getPackageById = async (req, res) => {
  try {
    const { id }   = req.params;
    const org_id   = req.session.user.orgId;

    const result = await pool.query(
      `SELECT *
       FROM allowance_packages
       WHERE id        = $1
         AND org_id    = $2
         AND is_active  = TRUE
         AND is_deleted = FALSE`,
      [id, org_id]
    );

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

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

/* ══════════════════════════════════════════
   CREATE PACKAGE
══════════════════════════════════════════ */
const createPackage = async (req, res) => {
  try {
    const org_id  = req.session.user.orgId;
    const userId  = req.session.user.userId;

    const {
      package_name,
      package_code,
      description,
      housing_allowance_hra,
      transport_allowance,
      meal_allowance,
      travel_allowance,
      mobile_internet_reimbursement,
      special_allowance,
      other_allowance,
      other_allowance_description,
      bonus_eligibility,
      bonus_percentage_amount,
    } = req.body;

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

    const result = await pool.query(
      `INSERT INTO allowance_packages (
         org_id, package_name, package_code, description,
         housing_allowance_hra, transport_allowance, meal_allowance,
         travel_allowance, mobile_internet_reimbursement, special_allowance,
         other_allowance, other_allowance_description,
         bonus_eligibility, bonus_percentage_amount,
         created_by
       ) VALUES (
         $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15
       ) RETURNING *`,
      [
        org_id,
        package_name,
        package_code        || null,
        description         || null,
        housing_allowance_hra         || 0,
        transport_allowance           || 0,
        meal_allowance                || 0,
        travel_allowance              || 0,
        mobile_internet_reimbursement || 0,
        special_allowance             || 0,
        other_allowance               || 0,
        other_allowance_description   || null,
        bonus_eligibility             || "No",
        bonus_percentage_amount       || null,
        userId,
      ]
    );

    res.status(201).json({
      success: true,
      message: "Package created successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("CREATE PACKAGE ERROR:", err);
    if (err.code === "23505") {
      return res.status(409).json({
        success: false,
        message: "A package with this name already exists for your organisation",
      });
    }
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ══════════════════════════════════════════
   UPDATE PACKAGE
══════════════════════════════════════════ */
const updatePackage = 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 allowance_packages
       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: "Package not found",
      });
    }

    const result = await pool.query(
      `UPDATE allowance_packages SET
         package_name                  = COALESCE($1,  package_name),
         package_code                  = COALESCE($2,  package_code),
         description                   = COALESCE($3,  description),
         housing_allowance_hra         = COALESCE($4,  housing_allowance_hra),
         transport_allowance           = COALESCE($5,  transport_allowance),
         meal_allowance                = COALESCE($6,  meal_allowance),
         travel_allowance              = COALESCE($7,  travel_allowance),
         mobile_internet_reimbursement = COALESCE($8,  mobile_internet_reimbursement),
         special_allowance             = COALESCE($9,  special_allowance),
         other_allowance               = COALESCE($10, other_allowance),
         other_allowance_description   = COALESCE($11, other_allowance_description),
         bonus_eligibility             = COALESCE($12, bonus_eligibility),
         bonus_percentage_amount       = COALESCE($13, bonus_percentage_amount),
         updated_by                    = $14,
         updated_at                    = NOW()
       WHERE id = $15 AND org_id = $16
       RETURNING *`,
      [
        u.package_name                  || null,
        u.package_code                  || null,
        u.description                   || null,
        u.housing_allowance_hra         || null,
        u.transport_allowance           || null,
        u.meal_allowance                || null,
        u.travel_allowance              || null,
        u.mobile_internet_reimbursement || null,
        u.special_allowance             || null,
        u.other_allowance               || null,
        u.other_allowance_description   || null,
        u.bonus_eligibility             || null,
        u.bonus_percentage_amount       || null,
        userId,
        id,
        org_id,
      ]
    );

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

/* ══════════════════════════════════════════
   SOFT DELETE PACKAGE
══════════════════════════════════════════ */
const deletePackage = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;

    const existing = await pool.query(
      `SELECT id FROM allowance_packages
       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: "Package not found",
      });
    }

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

    res.json({ success: true, message: "Package deleted successfully" });
  } catch (err) {
    console.error("DELETE PACKAGE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  getAllPackages,
  getPackageById,
  createPackage,
  updatePackage,
  deletePackage,
};