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

/* ════════════════════════════════════════════
   HELPER – parse session safely
════════════════════════════════════════════ */
const getSession = (req) => ({
  orgId: req.session?.user?.orgId,
  userId: req.session?.user?.userId,
});

/* ════════════════════════════════════════════
   ✅ CREATE – POST /provident-fund
════════════════════════════════════════════ */
const createProvidentFund = async (req, res) => {
  try {
    const { orgId, userId } = getSession(req);

    const {
      employee_id,
      pf_applicable,
      pf_number,
      employee_contribution_percent,
      employee_contribution_amount,
      employer_contribution_percent,
      employer_contribution_amount,
      total_contribution,
      pf_join_date,
      pension_scheme_applicable,
      gratuity_eligible,
      gratuity_start_date,
    } = req.body;

    /* ── Validation ── */
    if (!employee_id) {
      return res.status(400).json({ success: false, message: "employee_id is required" });
    }
    if (!orgId) {
      return res.status(400).json({ success: false, message: "Organization ID missing from session" });
    }

    /* ── Verify employee exists ── */
    const empCheck = await pool.query(
      `SELECT employee_id FROM employees WHERE employee_id = $1 AND status = 'Active' AND is_deleted = false`,
      [employee_id]
    );
    if (!empCheck.rows.length) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    /* ── Prevent duplicate active PF record ── */
    const dupCheck = await pool.query(
      `SELECT id FROM provident_fund WHERE employee_id = $1 AND org_id = $2 AND is_deleted = false`,
      [employee_id, orgId]
    );
    if (dupCheck.rows.length) {
      return res.status(409).json({
        success: false,
        message: "A PF record already exists for this employee. Use update instead.",
        existing_id: dupCheck.rows[0].id,
      });
    }

    const result = await pool.query(
      `INSERT INTO provident_fund (
        employee_id, org_id,
        pf_applicable, pf_number,
        employee_contribution_percent, employee_contribution_amount,
        employer_contribution_percent, employer_contribution_amount,
        total_contribution, pf_join_date,
        pension_scheme_applicable,
        gratuity_eligible, gratuity_start_date,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
      RETURNING *`,
      [
        employee_id, orgId,
        pf_applicable || "Yes",
        pf_number || null,
        employee_contribution_percent || null,
        employee_contribution_amount || null,
        employer_contribution_percent || null,
        employer_contribution_amount || null,
        total_contribution || null,
        pf_join_date || null,
        pension_scheme_applicable || "No",
        gratuity_eligible || "No",
        gratuity_start_date || null,
        userId,
      ]
    );

    res.status(201).json({
      success: true,
      message: "Provident Fund record created successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("CREATE PF ERROR:", err);
    res.status(500).json({ success: false, message: err.message, detail: err.detail || null });
  }
};

/* ════════════════════════════════════════════
   ✅ GET ALL – GET /provident-fund
   Supports: ?page, ?limit, ?search
════════════════════════════════════════════ */
const getAllProvidentFunds = async (req, res) => {
  try {
    const { orgId } = getSession(req);
    const { page = 1, limit = 10, search } = req.query;
    const offset = (page - 1) * limit;

    const params = [orgId];
    let where = `pf.org_id = $1 AND pf.is_deleted = false`;

    if (search) {
      params.push(`%${search}%`);
      where += ` AND (
        e.first_name  ILIKE $${params.length} OR
        e.last_name   ILIKE $${params.length} OR
        e.employee_id ILIKE $${params.length} OR
        pf.pf_number  ILIKE $${params.length}
      )`;
    }

    const baseQuery = `
      FROM provident_fund pf
      LEFT JOIN employees e ON pf.employee_id = e.employee_id
      WHERE ${where}
    `;

    const countResult = await pool.query(`SELECT COUNT(*) AS total ${baseQuery}`, params);
    const total = parseInt(countResult.rows[0].total);

    params.push(limit, offset);
    const dataResult = await pool.query(
      `SELECT
         pf.*,
         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
         e.employee_id AS emp_code,
         e.department,
         e.designation
       ${baseQuery}
       ORDER BY pf.created_at DESC
       LIMIT $${params.length - 1} OFFSET $${params.length}`,
      params
    );

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

/* ════════════════════════════════════════════
   ✅ GET BY ID – GET /provident-fund/:id
════════════════════════════════════════════ */
const getProvidentFundById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `SELECT
         pf.*,
         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
         e.department,
         e.designation,
         e.email
       FROM provident_fund pf
       LEFT JOIN employees e ON pf.employee_id = e.employee_id
       WHERE pf.id = $1 AND pf.is_deleted = false`,
      [id]
    );

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

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

/* ════════════════════════════════════════════
   ✅ GET BY EMPLOYEE – GET /provident-fund/employee/:employee_id
   Used by Benefits form Step 2 auto-fill
════════════════════════════════════════════ */
const getProvidentFundByEmployee = async (req, res) => {
  try {
    const { employee_id } = req.params;
    const { orgId } = getSession(req);

    const result = await pool.query(
      `SELECT * FROM provident_fund
       WHERE employee_id = $1 AND org_id = $2 AND is_deleted = false
       ORDER BY created_at DESC
       LIMIT 1`,
      [employee_id, orgId]
    );

    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "No PF record found for this employee",
      });
    }

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


/* ════════════════════════════════════════════
   ✅ GET BY PF NUMBER – GET /provident-fund/pf/:pf_number
   Used by Benefits form Step 2 auto-fill
════════════════════════════════════════════ */
const getProvidentFundByPFNumber = async (req, res) => {
  try {
    const { pf_number } = req.params;
    const { orgId } = getSession(req);

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

    const result = await pool.query(
      `SELECT
         pf.*,
         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
         e.department,
         e.designation,
         e.email
       FROM provident_fund pf
       LEFT JOIN employees e ON pf.employee_id = e.employee_id
       WHERE pf.pf_number  = $1
         AND pf.org_id     = $2
         AND pf.is_deleted = false
       ORDER BY pf.created_at DESC
       LIMIT 1`,
      [pf_number, orgId]
    );

    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "No PF record found for this PF number",
      });
    }

    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET PF BY PF_NUMBER ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
/* ════════════════════════════════════════════
   ✅ UPDATE – PUT /provident-fund/:id
════════════════════════════════════════════ */
const updateProvidentFund = async (req, res) => {
  try {
    const { id } = req.params;
    const { userId } = getSession(req);
    const u = req.body;

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

    const result = await pool.query(
      `UPDATE provident_fund SET
        pf_applicable                 = COALESCE($1,  pf_applicable),
        pf_number                     = COALESCE($2,  pf_number),
        employee_contribution_percent = COALESCE($3,  employee_contribution_percent),
        employee_contribution_amount  = COALESCE($4,  employee_contribution_amount),
        employer_contribution_percent = COALESCE($5,  employer_contribution_percent),
        employer_contribution_amount  = COALESCE($6,  employer_contribution_amount),
        total_contribution            = COALESCE($7,  total_contribution),
        pf_join_date                  = COALESCE($8,  pf_join_date),
        pension_scheme_applicable     = COALESCE($9,  pension_scheme_applicable),
        gratuity_eligible             = COALESCE($10, gratuity_eligible),
        gratuity_start_date           = COALESCE($11, gratuity_start_date),
        updated_by                    = $12,
        updated_at                    = NOW()
       WHERE id = $13
       RETURNING *`,
      [
        u.pf_applicable                 || null,
        u.pf_number                     || null,
        u.employee_contribution_percent || null,
        u.employee_contribution_amount  || null,
        u.employer_contribution_percent || null,
        u.employer_contribution_amount  || null,
        u.total_contribution            || null,
        u.pf_join_date                  || null,
        u.pension_scheme_applicable     || null,
        u.gratuity_eligible             || null,
        u.gratuity_start_date           || null,
        userId,
        id,
      ]
    );

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

/* ════════════════════════════════════════════
   ✅ SOFT DELETE – DELETE /provident-fund/:id
════════════════════════════════════════════ */
const deleteProvidentFund = async (req, res) => {
  try {
    const { id } = req.params;
    const { userId } = getSession(req);

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

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

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

/* ════════════════════════════════════════════
   ✅ SUMMARY – GET /provident-fund/summary
════════════════════════════════════════════ */
const getProvidentFundSummary = async (req, res) => {
  try {
    const { orgId } = getSession(req);

    const result = await pool.query(
      `SELECT
         COUNT(*)                                                        AS total_records,
         COUNT(DISTINCT employee_id)                                     AS total_employees,
         COUNT(CASE WHEN pf_applicable          = 'Yes'         THEN 1 END) AS pf_applicable_count,
         COUNT(CASE WHEN pension_scheme_applicable = 'Yes'      THEN 1 END) AS pension_count,
         COUNT(CASE WHEN gratuity_eligible IN ('Yes','Conditional') THEN 1 END) AS gratuity_count,
         ROUND(AVG(employee_contribution_percent), 2)                   AS avg_employee_contribution,
         ROUND(AVG(employer_contribution_percent), 2)                   AS avg_employer_contribution,
         SUM(total_contribution)                                         AS total_pf_corpus
       FROM provident_fund
       WHERE org_id = $1 AND is_deleted = false`,
      [orgId]
    );

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

module.exports = {
  createProvidentFund,
  getAllProvidentFunds,
  getProvidentFundById,
  getProvidentFundByEmployee,
  getProvidentFundByPFNumber, 
  updateProvidentFund,
  deleteProvidentFund,
  getProvidentFundSummary,
};