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

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

/* ════════════════════════════════════════════
   ✅ CREATE – POST /health-insurance
   Creates a new corporate health insurance policy
   and optionally links it to an employee.
════════════════════════════════════════════ */
const createHealthInsurance = async (req, res) => {
  try {
    const { orgId, userId } = getSession(req);

    const {
      corporate_id,
      corporate_policy,
      total_amount,
      image,
      employee_id,
      insurance_provider_name,
      policy_number,
      coverage_type,
      coverage_amount,
      premium_amount,
      employer_contribution,
      employee_contribution,
      insurance_start_date,
      insurance_end_date,
      dependent_coverage_details,
      status,
    } = req.body;

    /* ── Validation ── */
    if (!corporate_id || !corporate_policy || !total_amount) {
      return res.status(400).json({
        success: false,
        message: "corporate_id, corporate_policy, and total_amount are required",
      });
    }
    if (!orgId) {
      return res.status(400).json({
        success: false,
        message: "Organization ID missing from session",
      });
    }

    /* ── If an employee is linked, verify they exist ── */
    if (employee_id) {
      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" });
      }
    }

    const result = await pool.query(
      `INSERT INTO health_insurance (
        org_id, corporate_id, corporate_policy, total_amount, image,
        employee_id, insurance_provider_name, policy_number,
        coverage_type, coverage_amount, premium_amount,
        employer_contribution, employee_contribution,
        insurance_start_date, insurance_end_date,
        dependent_coverage_details, status, created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18)
      RETURNING *`,
      [
        orgId,
        corporate_id,
        corporate_policy,
        total_amount,
        image || null,
        employee_id || null,
        insurance_provider_name || null,
        policy_number || null,
        coverage_type || null,
        coverage_amount || null,
        premium_amount || null,
        employer_contribution || null,
        employee_contribution || null,
        insurance_start_date || null,
        insurance_end_date || null,
        dependent_coverage_details || null,
        status || "Active",
        userId,
      ]
    );

    res.status(201).json({
      success: true,
      message: "Health insurance policy created successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("CREATE HEALTH INSURANCE ERROR:", err);
    res.status(500).json({ success: false, message: err.message, detail: err.detail || null });
  }
};

/* ════════════════════════════════════════════
   ✅ GET ALL – GET /health-insurance
   Supports: ?page, ?limit, ?search, ?status
════════════════════════════════════════════ */
const getAllHealthInsurance = 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 = `hi.org_id = $1 AND hi.is_deleted = false`;

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

    if (search) {
      params.push(`%${search}%`);
      where += ` AND (
        hi.corporate_id           ILIKE $${params.length} OR
        hi.corporate_policy       ILIKE $${params.length} OR
        hi.policy_number          ILIKE $${params.length} OR
        hi.insurance_provider_name ILIKE $${params.length} OR
        e.first_name              ILIKE $${params.length} OR
        e.last_name               ILIKE $${params.length}
      )`;
    }

    const baseQuery = `
      FROM health_insurance hi
      LEFT JOIN employees e ON hi.employee_id = e.employee_id
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      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
         hi.*,
         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
         COALESCE(d.dept_name, 'Unknown') AS department,
         COALESCE(des.designation_name, 'Unknown') AS designation,
         e.email,
         e.phone
       ${baseQuery}
       ORDER BY hi.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 HEALTH INSURANCE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ════════════════════════════════════════════
   ✅ GET ALL POLICY NUMBERS – GET /health-insurance/policies/list
   Returns list of all policy numbers for dropdown
════════════════════════════════════════════ */
const getAllPolicyNumbers = async (req, res) => {
  try {
    const { orgId } = getSession(req);

    const result = await pool.query(
      `SELECT 
         policy_number, 
         insurance_provider_name,
         status,
         coverage_type,
         insurance_start_date,
         insurance_end_date
       FROM health_insurance
       WHERE org_id = $1 
         AND is_deleted = false 
         AND policy_number IS NOT NULL
         AND policy_number != ''
         AND status = 'Active'
       ORDER BY created_at DESC`,
      [orgId]
    );

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

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

    const result = await pool.query(
      `SELECT
         hi.*,
         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
         COALESCE(d.dept_name, 'Unknown') AS department,
         COALESCE(des.designation_name, 'Unknown') AS designation,
         e.email,
         e.phone
       FROM health_insurance hi
       LEFT JOIN employees e ON hi.employee_id = e.employee_id
       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
       WHERE hi.id = $1 AND hi.is_deleted = false`,
      [id]
    );

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

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

/* ════════════════════════════════════════════
   ✅ GET BY POLICY NUMBER – GET /health-insurance/policy/:policy_number
   Used by Benefits form Step 1 auto-fill
════════════════════════════════════════════ */
const getHealthInsuranceByPolicyNumber = async (req, res) => {
  try {
    const { policy_number } = req.params;
    const { orgId } = getSession(req);

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

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

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

/* ════════════════════════════════════════════
   ✅ GET BY EMPLOYEE – GET /health-insurance/employee/:employee_id
   Returns all policies linked to a specific employee
════════════════════════════════════════════ */
const getHealthInsuranceByEmployee = async (req, res) => {
  try {
    const { employee_id } = req.params;
    const { orgId } = getSession(req);

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

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

/* ════════════════════════════════════════════
   ✅ UPDATE – PUT /health-insurance/:id
════════════════════════════════════════════ */
const updateHealthInsurance = async (req, res) => {
  try {
    const { id } = req.params;
    const { userId } = getSession(req);
    const u = req.body;

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

    const result = await pool.query(
      `UPDATE health_insurance SET
        corporate_id               = COALESCE($1,  corporate_id),
        corporate_policy           = COALESCE($2,  corporate_policy),
        total_amount               = COALESCE($3,  total_amount),
        image                      = COALESCE($4,  image),
        employee_id                = COALESCE($5,  employee_id),
        insurance_provider_name    = COALESCE($6,  insurance_provider_name),
        policy_number              = COALESCE($7,  policy_number),
        coverage_type              = COALESCE($8,  coverage_type),
        coverage_amount            = COALESCE($9,  coverage_amount),
        premium_amount             = COALESCE($10, premium_amount),
        employer_contribution      = COALESCE($11, employer_contribution),
        employee_contribution      = COALESCE($12, employee_contribution),
        insurance_start_date       = COALESCE($13, insurance_start_date),
        insurance_end_date         = COALESCE($14, insurance_end_date),
        dependent_coverage_details = COALESCE($15, dependent_coverage_details),
        status                     = COALESCE($16, status),
        updated_by                 = $17,
        updated_at                 = NOW()
       WHERE id = $18
       RETURNING *`,
      [
        u.corporate_id               || null,
        u.corporate_policy           || null,
        u.total_amount               || null,
        u.image                      || null,
        u.employee_id                || null,
        u.insurance_provider_name    || null,
        u.policy_number              || null,
        u.coverage_type              || null,
        u.coverage_amount            || null,
        u.premium_amount             || null,
        u.employer_contribution      || null,
        u.employee_contribution      || null,
        u.insurance_start_date       || null,
        u.insurance_end_date         || null,
        u.dependent_coverage_details || null,
        u.status                     || null,
        userId,
        id,
      ]
    );

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

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

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

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

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

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

    const result = await pool.query(
      `SELECT
         COUNT(*)                                              AS total_policies,
         COUNT(DISTINCT employee_id)                          AS covered_employees,
         COUNT(CASE WHEN status = 'Active'    THEN 1 END)    AS active_policies,
         COUNT(CASE WHEN status = 'Expired'   THEN 1 END)    AS expired_policies,
         COUNT(CASE WHEN status = 'Cancelled' THEN 1 END)    AS cancelled_policies,
         COUNT(CASE WHEN coverage_type = 'Individual'      THEN 1 END) AS individual_count,
         COUNT(CASE WHEN coverage_type = 'Family Floater'  THEN 1 END) AS family_count,
         COUNT(CASE WHEN coverage_type = 'Group'           THEN 1 END) AS group_count,
         COUNT(CASE WHEN coverage_type = 'Critical Illness' THEN 1 END) AS critical_illness_count,
         SUM(total_amount)                                    AS total_insurance_amount,
         SUM(coverage_amount)                                 AS total_coverage_amount,
         ROUND(AVG(premium_amount), 2)                       AS avg_premium
       FROM health_insurance
       WHERE org_id = $1 AND is_deleted = false`,
      [orgId]
    );

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

/* ════════════════════════════════════════════
   ✅ GET EXPIRING SOON – GET /health-insurance/expiring
   Returns policies expiring within the next N days (default 30)
════════════════════════════════════════════ */
const getExpiringPolicies = async (req, res) => {
  try {
    const { orgId } = getSession(req);
    const { days = 30 } = req.query;

    const result = await pool.query(
      `SELECT
         hi.*,
         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
         e.email,
         (hi.insurance_end_date - CURRENT_DATE) AS days_remaining
       FROM health_insurance hi
       LEFT JOIN employees e ON hi.employee_id = e.employee_id
       WHERE hi.org_id = $1
         AND hi.is_deleted = false
         AND hi.status = 'Active'
         AND hi.insurance_end_date IS NOT NULL
         AND hi.insurance_end_date BETWEEN CURRENT_DATE AND (CURRENT_DATE + $2::INTEGER)
       ORDER BY hi.insurance_end_date ASC`,
      [orgId, days]
    );

    res.json({
      success: true,
      data: result.rows,
      meta: { days_window: parseInt(days), count: result.rows.length },
    });
  } catch (err) {
    console.error("GET EXPIRING POLICIES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  createHealthInsurance,
  getAllHealthInsurance,
  getAllPolicyNumbers,
  getHealthInsuranceById,
  getHealthInsuranceByPolicyNumber,
  getHealthInsuranceByEmployee,
  updateHealthInsurance,
  deleteHealthInsurance,
  getHealthInsuranceSummary,
  getExpiringPolicies,
};