<?php

namespace App\Models;

use CodeIgniter\Model;

/**
 * Enrollment Collection V1 dashboard KPIs (from metabase_raw_queries.csv).
 */
class EnrollmentCollectionV1DashboardModel extends Model
{
    protected $DBGroup = 'default';

    /**
     * Metabase question id => PHP method name.
     */
    public const KPI_MAP = [
        115 => 'originally_enrolled',
        116 => 'added_subsequently',
        117 => 'gender_split',
        119 => 'enrollment_relationship',
        122 => 'average_age_by_enrollment_month',
        123 => 'original_base_premium',
        124 => 'overall_active',
        125 => 'enrollment_age_group',
        126 => 'additions_premium',
        127 => 'net_premium',
    ];

    /**
     * Human-readable KPI labels (Metabase name).
     */
    public const KPI_LABELS = [
        'originally_enrolled' => 'Originally Enrolled',
        'added_subsequently' => 'Added Subsequently',
        'gender_split' => 'Gender Split',
        'enrollment_relationship' => 'Relationship',
        'average_age_by_enrollment_month' => 'Average age by Enrolment month',
        'original_base_premium' => 'Original Base Premium',
        'overall_active' => 'Overall Active',
        'enrollment_age_group' => 'Age Group',
        'additions_premium' => 'Additions Premium',
        'net_premium' => 'Net Premium',
    ];

    protected function runKpiQuery(string $sql, int $policyId): array
    {
        $sql = str_replace(['\\t', '\\n', '\\r'], ["\t", "\n", "\r"], $sql);

        $db    = \Config\Database::connect($this->DBGroup);
        $query = $db->query($sql, ['client_policy_id' => $policyId]);

        return $query->getResultArray();
    }

    public function getKpi(string $method, int $policyId): array
    {
        if (! method_exists($this, $method)) {
            return [];
        }

        return $this->{$method}($policyId);
    }

    public function getAllKpis(int $policyId): array
    {
        $out = [];
        foreach (self::KPI_MAP as $id => $method) {
            $out[$method] = [
                'id'    => (int) $id,
                'label' => self::KPI_LABELS[$method] ?? $method,
                'rows'  => $this->getKpi($method, $policyId),
            ];
        }

        return $out;
    }

    /** Metabase #115: Originally Enrolled */
    public function originally_enrolled(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    COUNT(DISTINCT ep.employee_id) AS `Originally Enrolled`
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id AND e.change_event = 'Inception'
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status = 'Active'
  AND e.emp_status = 'Active'
  AND e.is_active = 1;
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #116: Added Subsequently */
    public function added_subsequently(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    COUNT(DISTINCT ep.employee_id) AS `Subsequent Additions`
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id
    AND LOWER(e.change_event) IN ('dependent addition', 'addition', 'missed inception')
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status = 'Active'
  AND e.emp_status = 'Active'
  AND e.is_active = 1;
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #117: Gender Split */
    public function gender_split(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    e.gender,
    COUNT(DISTINCT ep.employee_id) AS member_count,
    ROUND((COUNT(DISTINCT ep.employee_id) * 100.0) /
          SUM(COUNT(DISTINCT ep.employee_id)) OVER(), 2) AS percentage
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status != 'Truncated'
  AND e.is_active = 1
  AND e.emp_status != 'Truncated'
  AND e.gender IS NOT NULL
GROUP BY e.gender
ORDER BY member_count DESC;
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #119: Relationship */
    public function enrollment_relationship(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    e.relationship,
    COUNT(DISTINCT ep.employee_id) AS member_count,
    ROUND((COUNT(DISTINCT ep.employee_id) * 100.0) /
          SUM(COUNT(DISTINCT ep.employee_id)) OVER(), 2) AS percentage
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status != 'Truncated'
  AND e.is_active = 1
  AND e.emp_status != 'Truncated'
  AND e.relationship IS NOT NULL
GROUP BY e.relationship
ORDER BY member_count DESC;
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #122: Average age by Enrolment month */
    public function average_age_by_enrollment_month(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    DATE_FORMAT(ep.date_coverage, '%Y-%m') AS enrollment_month,
    DATE_FORMAT(ep.date_coverage, '%b %Y') AS month_label,
    ROUND(AVG(TIMESTAMPDIFF(YEAR, e.dob, ep.date_coverage)), 2) AS average_age,
    COUNT(DISTINCT ep.employee_id) AS enrollments
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND e.is_active = 1
  AND e.dob IS NOT NULL
  AND ep.date_coverage IS NOT NULL
GROUP BY DATE_FORMAT(ep.date_coverage, '%Y-%m')
ORDER BY enrollment_month;
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #123: Original Base Premium */
    public function original_base_premium(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    ROUND(IFNULL(SUM(ep.rata_premimum + COALESCE(ep.gst, 0)), 0), 2) AS opening_premium
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id AND e.change_event = 'Inception'
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status = 'Active'
  AND e.emp_status = 'Active'
  AND e.is_active = 1;
--    AND e.relationship = 'Self';
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #124: Overall Active */
    public function overall_active(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    COUNT(DISTINCT ep.employee_id) AS `Overall Active`
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status = 'Active'
  AND e.emp_status = 'Active'
  AND e.is_active = 1;
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #125: Age Group */
    public function enrollment_age_group(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    CASE
        WHEN TIMESTAMPDIFF(YEAR, e.dob, CURDATE()) BETWEEN 0 AND 18 THEN '0-18'
        WHEN TIMESTAMPDIFF(YEAR, e.dob, CURDATE()) BETWEEN 19 AND 30 THEN '19-30'
        WHEN TIMESTAMPDIFF(YEAR, e.dob, CURDATE()) BETWEEN 31 AND 40 THEN '31-40'
        WHEN TIMESTAMPDIFF(YEAR, e.dob, CURDATE()) BETWEEN 41 AND 50 THEN '41-50'
        WHEN TIMESTAMPDIFF(YEAR, e.dob, CURDATE()) BETWEEN 51 AND 60 THEN '51-60'
        ELSE '60+'
    END AS age_group,
    COUNT(DISTINCT ep.employee_id) AS member_count,
    ROUND((COUNT(DISTINCT ep.employee_id) * 100.0) /
          SUM(COUNT(DISTINCT ep.employee_id)) OVER(), 2) AS percentage
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status != 'Truncated'
  AND e.is_active = 1
  AND e.emp_status != 'Truncated'
  AND e.dob IS NOT NULL
GROUP BY age_group
ORDER BY age_group;
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #126: Additions Premium */
    public function additions_premium(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    ROUND(IFNULL(SUM(ep.rata_premimum + COALESCE(ep.gst, 0)), 0), 2) AS additions_premium
FROM employee_polices ep
INNER JOIN employees e ON ep.employee_id = e.id
    AND LOWER(e.change_event) IN ('dependent addition', 'addition', 'missed inception')
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status = 'Active'
  AND e.is_active = 1
  AND e.emp_status = 'Active';
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }

    /** Metabase #127: Net Premium */
    public function net_premium(int $policyId): array
    {
        $sql = <<<'SQL'
SELECT
    ROUND(IFNULL(SUM(ep.rata_premimum + COALESCE(ep.gst, 0)), 0), 2) AS net_premium
  --  ROUND(SUM(ep.rata_premimum), 2) as base_premium,
   -- ROUND(SUM(COALESCE(ep.gst, 0)), 2) as total_gst
FROM employee_polices ep
WHERE ep.client_policy_id = :client_policy_id:
  AND ep.is_active = 1
  AND ep.status = 'Active';
SQL;

        return $this->runKpiQuery($sql, $policyId);
    }
}