// controllers/hrandpayrollsController/organizationProvidentFundController.js

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

const getSession = (req) => ({
  orgId: req.session?.user?.orgId,
  userId: req.session?.user?.userId,
});

/* CREATE - Create new organization PF record */
const createOrganizationPF = async (req, res) => {
  try {
    const { orgId, userId } = getSession(req);
    const {
      pf_number,
      uan_number,
      employee_contribution_percent,
      employer_contribution_percent,
      pension_scheme_applicable,
      gratuity_eligible,
      gratuity_start_date,
      gratuity_years_required,
      pf_join_date,
      status,
      description
    } = req.body;

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

    // Check for duplicate
    const existingCheck = await pool.query(
      `SELECT id FROM organization_provident_fund 
       WHERE org_id = $1 AND pf_number = $2 AND is_deleted = false`,
      [orgId, pf_number]
    );

    if (existingCheck.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: "PF number already exists for this organization"
      });
    }

    const result = await pool.query(
      `INSERT INTO organization_provident_fund (
        org_id, pf_number, uan_number,
        employee_contribution_percent, employer_contribution_percent,
        pension_scheme_applicable, gratuity_eligible,
        gratuity_start_date, gratuity_years_required,
        pf_join_date, status, description, created_by
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
      RETURNING *`,
      [
        orgId, pf_number, uan_number || null,
        employee_contribution_percent || 12,
        employer_contribution_percent || 12,
        pension_scheme_applicable || 'No',
        gratuity_eligible || 'No',
        gratuity_start_date || null,
        gratuity_years_required || 5,
        pf_join_date || null,
        status || 'Active',
        description || null,
        userId
      ]
    );

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

/* GET ALL - Get all organization PF records */
const getAllOrganizationPF = async (req, res) => {
  try {
    const { orgId } = getSession(req);
    const { page = 1, limit = 10, search, status } = req.query;
    const offset = (page - 1) * limit;

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

    if (status) {
      params.push(status);
      where += ` AND status = $${params.length}`;
    }

    if (search) {
      params.push(`%${search}%`);
      where += ` AND (pf_number ILIKE $${params.length} OR uan_number ILIKE $${params.length})`;
    }

    const countResult = await pool.query(
      `SELECT COUNT(*) FROM organization_provident_fund WHERE ${where}`,
      params
    );
    const total = parseInt(countResult.rows[0].count);

    params.push(limit, offset);
    const result = await pool.query(
      `SELECT * FROM organization_provident_fund
       WHERE ${where}
       ORDER BY created_at DESC
       LIMIT $${params.length - 1} OFFSET $${params.length}`,
      params
    );

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

/* GET LIST - Get PF numbers for dropdown */
const getOrganizationPFList = async (req, res) => {
  try {
    const { orgId } = getSession(req);

    const result = await pool.query(
      `SELECT 
         id, pf_number, uan_number,
         employee_contribution_percent,
         employer_contribution_percent,
         pension_scheme_applicable,
         gratuity_eligible,
         gratuity_start_date,
         gratuity_years_required,
         pf_join_date,
         status
       FROM organization_provident_fund
       WHERE org_id = $1 
         AND is_deleted = false 
         AND status = 'Active'
         AND pf_number IS NOT NULL
       ORDER BY pf_number`,
      [orgId]
    );

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

/* GET BY PF NUMBER */
const getOrganizationPFByNumber = async (req, res) => {
  try {
    const { pf_number } = req.params;
    const { orgId } = getSession(req);

    const result = await pool.query(
      `SELECT * FROM organization_provident_fund
       WHERE pf_number = $1 
         AND org_id = $2 
         AND is_deleted = false`,
      [pf_number, orgId]
    );

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

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

/* GET BY ID */
const getOrganizationPFById = async (req, res) => {
  try {
    const { id } = req.params;
    const { orgId } = getSession(req);

    const result = await pool.query(
      `SELECT * FROM organization_provident_fund
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

/* UPDATE */
const updateOrganizationPF = async (req, res) => {
  try {
    const { id } = req.params;
    const { userId } = getSession(req);
    const updates = req.body;

    const existing = await pool.query(
      `SELECT id FROM organization_provident_fund 
       WHERE id = $1 AND is_deleted = false`,
      [id]
    );

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

    const result = await pool.query(
      `UPDATE organization_provident_fund SET
        pf_number = COALESCE($1, pf_number),
        uan_number = COALESCE($2, uan_number),
        employee_contribution_percent = COALESCE($3, employee_contribution_percent),
        employer_contribution_percent = COALESCE($4, employer_contribution_percent),
        pension_scheme_applicable = COALESCE($5, pension_scheme_applicable),
        gratuity_eligible = COALESCE($6, gratuity_eligible),
        gratuity_start_date = COALESCE($7, gratuity_start_date),
        gratuity_years_required = COALESCE($8, gratuity_years_required),
        pf_join_date = COALESCE($9, pf_join_date),
        status = COALESCE($10, status),
        description = COALESCE($11, description),
        updated_by = $12,
        updated_at = NOW()
      WHERE id = $13
      RETURNING *`,
      [
        updates.pf_number,
        updates.uan_number,
        updates.employee_contribution_percent,
        updates.employer_contribution_percent,
        updates.pension_scheme_applicable,
        updates.gratuity_eligible,
        updates.gratuity_start_date,
        updates.gratuity_years_required,
        updates.pf_join_date,
        updates.status,
        updates.description,
        userId,
        id
      ]
    );

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

/* DELETE (Soft Delete) */
const deleteOrganizationPF = async (req, res) => {
  try {
    const { id } = req.params;
    const { userId } = getSession(req);

    const existing = await pool.query(
      `SELECT id FROM organization_provident_fund 
       WHERE id = $1 AND is_deleted = false`,
      [id]
    );

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

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

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

module.exports = {
  createOrganizationPF,
  getAllOrganizationPF,
  getOrganizationPFList,
  getOrganizationPFByNumber,
  getOrganizationPFById,
  updateOrganizationPF,
  deleteOrganizationPF
};