<?php
declare(strict_types=1);

define('FCPATH', __DIR__ . '/../public/');
chdir(FCPATH);

require FCPATH . '../app/Config/Paths.php';
$paths = new Config\Paths();
require rtrim($paths->systemDirectory, '\\/ ') . DIRECTORY_SEPARATOR . 'bootstrap.php';
require_once SYSTEMPATH . 'Config/DotEnv.php';
(new CodeIgniter\Config\DotEnv(ROOTPATH))->load();

defined('ENVIRONMENT') || define('ENVIRONMENT', env('CI_ENVIRONMENT', 'development'));
$boot = APPPATH . 'Config/Boot/' . ENVIRONMENT . '.php';
if (is_file($boot)) {
    require_once $boot;
}

use App\Models\ClaimsCollectionV2DashboardModel;
use Config\Database;

$policyId = isset($argv[1]) ? (int) $argv[1] : 4687;
$model    = new ClaimsCollectionV2DashboardModel();
$db       = Database::connect();

echo "=== premium_as_on_date debug (policy_id={$policyId}) ===" . PHP_EOL . PHP_EOL;

try {
    $new = $model->premium_as_on_date($policyId);
    echo 'NEW premium_as_on_date: ' . json_encode($new, JSON_UNESCAPED_UNICODE) . PHP_EOL;
} catch (Throwable $e) {
    echo 'NEW ERROR: ' . $e->getMessage() . PHP_EOL;
}

try {
    $old = $model->premium_as_on_date_legacy($policyId);
    echo 'LEGACY premium_as_on_date: ' . json_encode($old, JSON_UNESCAPED_UNICODE) . PHP_EOL;
} catch (Throwable $e) {
    echo 'LEGACY ERROR: ' . $e->getMessage() . PHP_EOL;
}

echo PHP_EOL . '=== Active filter counts ===' . PHP_EOL;
$checks = [
    'all is_active ep' => "SELECT COUNT(*) c FROM employee_polices ep WHERE ep.client_policy_id = ? AND ep.is_active = 1",
    "ep.status = 'active'" => "SELECT COUNT(*) c FROM employee_polices ep JOIN employees e ON e.id = ep.employee_id WHERE ep.client_policy_id = ? AND ep.is_active = 1 AND ep.status = 'active' AND e.is_active = 1 AND e.emp_status = 'active'",
    "ep.status = 'Active'" => "SELECT COUNT(*) c FROM employee_polices ep JOIN employees e ON e.id = ep.employee_id WHERE ep.client_policy_id = ? AND ep.is_active = 1 AND ep.status = 'Active' AND e.is_active = 1 AND e.emp_status = 'Active'",
    'distinct ep.status' => "SELECT DISTINCT ep.status FROM employee_polices ep WHERE ep.client_policy_id = ? AND ep.is_active = 1 LIMIT 10",
    'distinct e.emp_status' => "SELECT DISTINCT e.emp_status FROM employee_polices ep JOIN employees e ON e.id = ep.employee_id WHERE ep.client_policy_id = ? AND ep.is_active = 1 LIMIT 10",
    'with date_of_exit' => "SELECT COUNT(*) c FROM employee_polices ep JOIN employees e ON e.id = ep.employee_id WHERE ep.client_policy_id = ? AND ep.is_active = 1 AND ep.date_of_exit IS NOT NULL AND ep.date_of_exit != ''",
];

foreach ($checks as $label => $sql) {
    try {
        $q = $db->query($sql, [$policyId]);
        $rows = $q->getResultArray();
        if (count($rows) === 1 && isset($rows[0]['c'])) {
            echo "{$label}: {$rows[0]['c']}" . PHP_EOL;
        } else {
            echo "{$label}: " . json_encode($rows) . PHP_EOL;
        }
    } catch (Throwable $e) {
        echo "{$label}: ERROR " . $e->getMessage() . PHP_EOL;
    }
}

echo PHP_EOL . '=== Sum breakdown (new logic parts) ===' . PHP_EOL;
$breakdown = <<<'SQL'
SELECT
    COUNT(*) AS member_rows,
    SUM(ep.rata_premimum + COALESCE(ep.gst, 0)) AS gross_sum,
    SUM(
        CASE
            WHEN ep.date_of_exit IS NOT NULL
             AND ep.date_of_exit != ''
             AND (ep.claim_status = 0 OR ep.claim_status IS NULL)
            THEN (ep.rata_premimum + COALESCE(ep.gst, 0))
                 * (DATEDIFF(ep.policy_end_date, ep.date_of_exit) + 1) / 365
            ELSE 0
        END
    ) AS remaining_deducted,
    SUM(
        CASE
            WHEN ep.date_of_exit IS NOT NULL
             AND ep.date_of_exit != ''
             AND (ep.claim_status = 0 OR ep.claim_status IS NULL)
            THEN (ep.rata_premimum + COALESCE(ep.gst, 0))
                 - ((ep.rata_premimum + COALESCE(ep.gst, 0))
                    * (DATEDIFF(ep.policy_end_date, ep.date_of_exit) + 1) / 365)
            ELSE (ep.rata_premimum + COALESCE(ep.gst, 0))
        END
    ) AS net_sum
FROM employee_polices ep
JOIN employees e ON e.id = ep.employee_id
WHERE ep.client_policy_id = ?
  AND ep.is_active = 1
  AND ep.status = 'active'
  AND e.is_active = 1
  AND e.emp_status = 'active'
SQL;

try {
    $row = $db->query($breakdown, [$policyId])->getRowArray();
    echo json_encode($row, JSON_PRETTY_PRINT) . PHP_EOL;
} catch (Throwable $e) {
    echo 'BREAKDOWN ERROR: ' . $e->getMessage() . PHP_EOL;
}

// Same breakdown with Active casing
$breakdownActive = str_replace(["ep.status = 'active'", "e.emp_status = 'active'"], ["ep.status = 'Active'", "e.emp_status = 'Active'"], $breakdown);
try {
    $row = $db->query($breakdownActive, [$policyId])->getRowArray();
    echo PHP_EOL . 'With Active casing:' . PHP_EOL;
    echo json_encode($row, JSON_PRETTY_PRINT) . PHP_EOL;
} catch (Throwable $e) {
    echo 'ACTIVE CASING ERROR: ' . $e->getMessage() . PHP_EOL;
}

echo PHP_EOL . '=== Exited members (any active flag) ===' . PHP_EOL;
$exitSql = <<<'SQL'
SELECT
    ep.is_active,
    ep.status,
    e.emp_status,
    COUNT(*) AS c,
    SUM(ep.rata_premimum + COALESCE(ep.gst, 0)) AS premium_sum
FROM employee_polices ep
JOIN employees e ON e.id = ep.employee_id
WHERE ep.client_policy_id = ?
  AND ep.date_of_exit IS NOT NULL
  AND ep.date_of_exit != ''
GROUP BY ep.is_active, ep.status, e.emp_status
SQL;
try {
    foreach ($db->query($exitSql, [$policyId])->getResultArray() as $row) {
        echo json_encode($row) . PHP_EOL;
    }
} catch (Throwable $e) {
    echo 'EXIT ERROR: ' . $e->getMessage() . PHP_EOL;
}

echo PHP_EOL . '=== Compare enrollment net_premium ===' . PHP_EOL;
try {
    $enroll = new \App\Models\EnrollmentCollectionV1DashboardModel();
    echo json_encode($enroll->net_premium($policyId), JSON_UNESCAPED_UNICODE) . PHP_EOL;
} catch (Throwable $e) {
    echo 'ENROLL ERROR: ' . $e->getMessage() . PHP_EOL;
}

echo PHP_EOL . '=== earned_premium (#203) uses old premium subquery ===' . PHP_EOL;
try {
    echo json_encode($model->earned_premium($policyId), JSON_UNESCAPED_UNICODE) . PHP_EOL;
    $oldPrem = $db->query(<<<'SQL'
SELECT COALESCE(SUM(
    CASE
        WHEN e2.change_event = 'inception' THEN ep2.rata_premimum
        WHEN e2.change_event != 'inception' AND (ep2.date_of_exit IS NULL OR ep2.date_of_exit = '') THEN ep2.rata_premimum
        WHEN ep2.date_of_exit IS NOT NULL AND ep2.date_of_exit != '' AND ep2.claim_status = 0 THEN -ep2.rata_premimum
        ELSE 0
    END
), 0) AS old_subquery_premium
FROM employee_polices ep2
JOIN employees e2 ON e2.id = ep2.employee_id
WHERE ep2.client_policy_id = ?
  AND ep2.is_active = 1
SQL, [$policyId])->getRowArray();
    echo 'old subquery base (rata only, no gst): ' . json_encode($oldPrem) . PHP_EOL;
} catch (Throwable $e) {
    echo 'EARNED ERROR: ' . $e->getMessage() . PHP_EOL;
}

echo PHP_EOL . '=== Find policy with exits for exit-logic test ===' . PHP_EOL;
$findExit = <<<'SQL'
SELECT ep.client_policy_id,
       SUM(CASE WHEN ep.is_active=1 AND ep.status='active' AND e.is_active=1 AND e.emp_status='active' THEN 1 ELSE 0 END) AS active_both,
       SUM(CASE WHEN ep.is_active=1 AND ep.status='active' AND e.is_active=1 AND e.emp_status='active'
                 AND ep.date_of_exit IS NOT NULL AND ep.date_of_exit != '' THEN 1 ELSE 0 END) AS active_with_exit
FROM employee_polices ep
JOIN employees e ON e.id = ep.employee_id
GROUP BY ep.client_policy_id
HAVING active_with_exit > 0
ORDER BY active_with_exit DESC
LIMIT 5
SQL;
try {
    $policies = $db->query($findExit)->getResultArray();
    echo json_encode($policies, JSON_PRETTY_PRINT) . PHP_EOL;
    if (! empty($policies[0]['client_policy_id'])) {
        $testPid = (int) $policies[0]['client_policy_id'];
        echo PHP_EOL . "Testing policy {$testPid} with exits..." . PHP_EOL;
        echo 'NEW: ' . json_encode($model->premium_as_on_date($testPid), JSON_UNESCAPED_UNICODE) . PHP_EOL;
        echo 'net_premium: ' . json_encode((new \App\Models\EnrollmentCollectionV1DashboardModel())->net_premium($testPid), JSON_UNESCAPED_UNICODE) . PHP_EOL;

        $diag = $db->query(<<<'SQL'
SELECT
    COUNT(*) AS total_ep,
    SUM(CASE WHEN ep.is_active=1 AND ep.status='active' AND e.is_active=1 AND e.emp_status='active' THEN 1 ELSE 0 END) AS active_both,
    SUM(CASE WHEN ep.date_of_exit IS NOT NULL AND ep.date_of_exit != '' THEN 1 ELSE 0 END) AS has_exit,
    SUM(CASE WHEN ep.is_active=1 AND ep.status='active' AND e.is_active=1 AND e.emp_status='active'
              AND ep.date_of_exit IS NOT NULL AND ep.date_of_exit != '' THEN 1 ELSE 0 END) AS active_with_exit
FROM employee_polices ep
JOIN employees e ON e.id = ep.employee_id
WHERE ep.client_policy_id = ?
SQL, [$testPid])->getRowArray();
        echo 'policy diag: ' . json_encode($diag) . PHP_EOL;
    }
} catch (Throwable $e) {
    echo 'FIND EXIT ERROR: ' . $e->getMessage() . PHP_EOL;
}