<?php
ob_start();
require_once __DIR__ . '/../config/auth.php';

if (!function_exists('is_admin')) {
    function is_admin(array $user): bool { return (int)$user['user_type_id'] === 1; }
}
if (!function_exists('allowed_clients')) {
    function allowed_clients(array $user): ?array {
        if (is_admin($user)) return null;
        $ids = array_values(array_filter(array_map('intval', explode(',', $user['clients_multi'] ?? ''))));
        return $ids ?: [-1];
    }
}
if (!function_exists('client_where')) {
    function client_where(array $user, string $col = 'clients_id'): array {
        $ids = allowed_clients($user);
        if ($ids === null) return ['1=1', []];
        $ph = implode(',', array_fill(0, count($ids), '?'));
        return ["$col IN ($ph)", $ids];
    }
}

$user = require_auth();

try {
    $db        = db();
    $action    = $_GET['action'] ?? 'list';
    $client_id = (int)($_GET['clients_id'] ?? 0);
    $search    = trim($_GET['search'] ?? '');
    $like      = '%' . $search . '%';
    $days_warn = (int)($_GET['days_warn'] ?? 90); // warn within 90 days

    // ── Core query: latest assessment per employee+assessment combo ────────
    // Uses DATE_ADD to calculate expiry from date + months
    $base_sql = "
        SELECT
            'assessment' AS rec_type,
            a.record_id, a.assesses_name AS name, a.date AS done_date,
            a.results, a.current_mark, a.passmark,
            a.client_employees_id, a.clients_id, a.booking,
            e.client_employees_name, e.surname, e.i_doc_passport,
            c.clients_name,
            ass.expiry AS expiry_months,
            DATE_ADD(STR_TO_DATE(SUBSTRING(a.date,1,10),'%Y-%m-%d'),
                     INTERVAL CAST(ass.expiry AS UNSIGNED) MONTH) AS expiry_date,
            DATEDIFF(DATE_ADD(STR_TO_DATE(SUBSTRING(a.date,1,10),'%Y-%m-%d'),
                     INTERVAL CAST(ass.expiry AS UNSIGNED) MONTH), CURDATE()) AS days_left
        FROM assesses a
        JOIN (
            SELECT client_employees_id, assessments_id, MAX(record_id) AS latest_id
            FROM assesses
            WHERE results IS NOT NULL AND results != ''
            GROUP BY client_employees_id, assessments_id
        ) latest ON latest.latest_id = a.record_id
        LEFT JOIN client_employees e ON e.record_id = a.client_employees_id
        LEFT JOIN clients c ON c.record_id = a.clients_id
        LEFT JOIN assessments ass ON ass.record_id = a.assessments_id
        WHERE ass.expiry IS NOT NULL AND ass.expiry != '' AND CAST(ass.expiry AS UNSIGNED) > 0

        UNION ALL

        SELECT
            'test' AS rec_type,
            t.record_id, t.test_name AS name, t.date AS done_date,
            t.results, t.current_mark, t.passmark,
            t.client_employees_id, t.clients_id, t.booking,
            e.client_employees_name, e.surname, e.i_doc_passport,
            c.clients_name,
            tst.expiry AS expiry_months,
            DATE_ADD(STR_TO_DATE(SUBSTRING(t.date,1,10),'%Y-%m-%d'),
                     INTERVAL CAST(tst.expiry AS UNSIGNED) MONTH) AS expiry_date,
            DATEDIFF(DATE_ADD(STR_TO_DATE(SUBSTRING(t.date,1,10),'%Y-%m-%d'),
                     INTERVAL CAST(tst.expiry AS UNSIGNED) MONTH), CURDATE()) AS days_left
        FROM tests_sec t
        JOIN (
            SELECT client_employees_id, test_id, MAX(record_id) AS latest_id
            FROM tests_sec
            WHERE results IS NOT NULL AND results != ''
            GROUP BY client_employees_id, test_id
        ) latest ON latest.latest_id = t.record_id
        LEFT JOIN client_employees e ON e.record_id = t.client_employees_id
        LEFT JOIN clients c ON c.record_id = t.clients_id
        LEFT JOIN tests tst ON tst.record_id = t.test_id
        WHERE tst.expiry IS NOT NULL AND tst.expiry != '' AND CAST(tst.expiry AS UNSIGNED) > 0
    ";

    switch ($action) {

        case 'list':
            [$cw, $cp] = client_where($user, 'clients_id');
            $where_parts = ["days_left <= $days_warn"];
            $params      = [];
            if ($cw !== '1=1') { $where_parts[] = "($cw)"; $params = array_merge($params, $cp); }
            if ($client_id) { $where_parts[] = 'clients_id = ?'; $params[] = $client_id; }
            if ($search !== '') {
                $where_parts[] = '(client_employees_name LIKE ? OR surname LIKE ? OR i_doc_passport LIKE ? OR clients_name LIKE ? OR name LIKE ?)';
                $params        = array_merge($params, [$like, $like, $like, $like, $like]);
            }

            $where = 'WHERE ' . implode(' AND ', $where_parts);
            $stmt  = $db->prepare("SELECT * FROM ($base_sql) sub $where ORDER BY days_left ASC LIMIT 1000");
            $stmt->execute($params);
            $rows = $stmt->fetchAll();

            // Group by client
            $grouped = [];
            foreach ($rows as $row) {
                $cn = $row['clients_name'] ?: 'Unknown';
                $grouped[$cn][] = $row;
            }
            ksort($grouped);

            json_success(['rows' => $rows, 'grouped' => $grouped, 'total' => count($rows)]);
            break;

        case 'dashboard':
            // Compact version for dashboard — expired + expiring within 60 days, limit 200
            [$dcw, $dcp] = client_where($user, 'clients_id');
            $d_where = $dcw !== '1=1' ? "days_left <= 60 AND ($dcw)" : 'days_left <= 60';
            $stmt = $db->prepare("SELECT * FROM ($base_sql) sub WHERE $d_where ORDER BY days_left ASC LIMIT 200");
            $stmt->execute($dcp);
            $rows = $stmt->fetchAll();
            $grouped = [];
            foreach ($rows as $row) {
                $cn = $row['clients_name'] ?: 'Unknown';
                $grouped[$cn][] = $row;
            }
            ksort($grouped);
            json_success(['grouped' => $grouped, 'total' => count($rows)]);
            break;

        default:
            json_error('Unknown action');
    }

} catch (Throwable $e) {
    json_error('expiry.php [' . $e->getLine() . ']: ' . $e->getMessage(), 500);
}