<?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();
    $mode   = $_GET['mode'] ?? '';

    // Shared expiry calculation fragment
    $expiry_days = "DATEDIFF(DATE_ADD(STR_TO_DATE(SUBSTRING(a.date,1,10),'%Y-%m-%d'), INTERVAL CAST(ass.expiry AS UNSIGNED) MONTH), CURDATE())";
    $expiry_date = "DATE_ADD(STR_TO_DATE(SUBSTRING(a.date,1,10),'%Y-%m-%d'), INTERVAL CAST(ass.expiry AS UNSIGNED) MONTH)";
    $t_expiry_days = "DATEDIFF(DATE_ADD(STR_TO_DATE(SUBSTRING(t.date,1,10),'%Y-%m-%d'), INTERVAL CAST(tst.expiry AS UNSIGNED) MONTH), CURDATE())";
    $t_expiry_date = "DATE_ADD(STR_TO_DATE(SUBSTRING(t.date,1,10),'%Y-%m-%d'), INTERVAL CAST(tst.expiry AS UNSIGNED) MONTH)";

    switch ($mode) {

        // ── Employee report ───────────────────────────────────────────────
        case 'employee':
            $search = '%' . trim($_GET['search'] ?? '') . '%';
            // Find matching employees
            [$ecw, $ecp] = client_where($user, 'e.clients_id');
            $emp_stmt = $db->prepare(
                "SELECT e.record_id, e.client_employees_name, e.surname, e.i_doc_passport,
                        e.occupation, e.company_number, e.cell, e.clients_id, c.clients_name
                 FROM client_employees e
                 LEFT JOIN clients c ON c.record_id = e.clients_id
                 WHERE ($ecw)
                   AND (e.client_employees_name LIKE ? OR e.surname LIKE ? OR e.i_doc_passport LIKE ?)
                 ORDER BY e.surname, e.client_employees_name
                 LIMIT 50"
            );
            $emp_stmt->execute(array_merge($ecp, [$search, $search, $search]));
            $employees = $emp_stmt->fetchAll();

            foreach ($employees as &$emp) {
                $eid = $emp['record_id'];
                // Assessments
                $a = $db->prepare(
                    "SELECT a.record_id, a.assesses_name AS name, 'assessment' AS rec_type,
                            a.date, a.results, a.current_mark, a.passmark, a.booking, a.vehicle_model,
                            ass.expiry AS expiry_months,
                            $expiry_date AS expiry_date,
                            $expiry_days AS days_left,
                            u.safesure_users_name AS assessor
                     FROM assesses a
                     LEFT JOIN assessments ass ON ass.record_id = a.assessments_id
                     LEFT JOIN safesure_users u ON u.record_id = a.safesure_users_id
                     WHERE a.client_employees_id = ?
                     ORDER BY a.record_id DESC"
                );
                $a->execute([$eid]);
                $emp['records'] = $a->fetchAll();

                // Tests
                $t = $db->prepare(
                    "SELECT t.record_id, t.test_name AS name, 'test' AS rec_type,
                            t.date, t.results, t.current_mark, t.passmark, t.booking, t.vehicle_model,
                            tst.expiry AS expiry_months,
                            $t_expiry_date AS expiry_date,
                            $t_expiry_days AS days_left,
                            u.safesure_users_name AS assessor
                     FROM tests_sec t
                     LEFT JOIN tests tst ON tst.record_id = t.test_id
                     LEFT JOIN safesure_users u ON u.record_id = t.safesure_users_id
                     WHERE t.client_employees_id = ?
                     ORDER BY t.record_id DESC"
                );
                $t->execute([$eid]);
                $emp['records'] = array_merge($emp['records'], $t->fetchAll());
                usort($emp['records'], fn($a,$b) => strcmp($b['date']??'', $a['date']??''));
            }
            json_success(['employees' => $employees]);
            break;

        // ── Client report ─────────────────────────────────────────────────
        case 'client':
            $cid = (int)($_GET['clients_id'] ?? 0);
            if (!$cid) json_error('clients_id required');
            // Verify user has access to this client
            $allowed = allowed_clients($user);
            if ($allowed !== null && !in_array($cid, $allowed)) json_error('Access denied to this client', 403);

            $emp_stmt = $db->prepare(
                "SELECT e.record_id, e.client_employees_name, e.surname, e.i_doc_passport,
                        e.occupation, e.company_number, c.clients_name
                 FROM client_employees e
                 LEFT JOIN clients c ON c.record_id = e.clients_id
                 WHERE e.clients_id = ?
                 ORDER BY e.surname, e.client_employees_name"
            );
            $emp_stmt->execute([$cid]);
            $employees = $emp_stmt->fetchAll();

            foreach ($employees as &$emp) {
                $eid = $emp['record_id'];
                $a = $db->prepare(
                    "SELECT a.record_id, a.assesses_name AS name, 'assessment' AS rec_type,
                            a.date, a.results, a.current_mark, a.passmark, a.vehicle_model,
                            u.safesure_users_name AS assessor,
                            $expiry_date AS expiry_date, $expiry_days AS days_left
                     FROM assesses a
                     LEFT JOIN assessments ass ON ass.record_id = a.assessments_id
                     LEFT JOIN safesure_users u ON u.record_id = a.safesure_users_id
                     WHERE a.client_employees_id = ?
                     ORDER BY a.record_id DESC LIMIT 20"
                );
                $a->execute([$eid]);
                $t = $db->prepare(
                    "SELECT t.record_id, t.test_name AS name, 'test' AS rec_type,
                            t.date, t.results, t.current_mark, t.passmark, t.vehicle_model,
                            u.safesure_users_name AS assessor,
                            $t_expiry_date AS expiry_date, $t_expiry_days AS days_left
                     FROM tests_sec t
                     LEFT JOIN tests tst ON tst.record_id = t.test_id
                     LEFT JOIN safesure_users u ON u.record_id = t.safesure_users_id
                     WHERE t.client_employees_id = ?
                     ORDER BY t.record_id DESC LIMIT 20"
                );
                $t->execute([$eid]);
                $recs = array_merge($a->fetchAll(), $t->fetchAll());
                usort($recs, fn($a,$b) => strcmp($b['date']??'', $a['date']??''));
                $emp['records'] = $recs;
            }
            json_success(['employees' => $employees]);
            break;

        // ── Date range report ─────────────────────────────────────────────
        case 'daterange':
            $from = $_GET['from'] ?? date('Y-m-01');
            $to   = $_GET['to']   ?? date('Y-m-d');
            $cid  = (int)($_GET['clients_id'] ?? 0);
            [$drcw, $drcp] = client_where($user, 'a.clients_id');
            [$drtcw, $drtcp] = client_where($user, 't.clients_id');
            $where_c = ($drcw !== '1=1' ? "AND ($drcw) " : '') . ($cid ? "AND a.clients_id = $cid" : '');
            $where_t = ($drtcw !== '1=1' ? "AND ($drtcw) " : '') . ($cid ? "AND t.clients_id = $cid" : '');

            $stmt = $db->prepare(
                "SELECT 'assessment' AS rec_type, a.record_id,
                        a.assesses_name AS name, a.date, a.results, a.current_mark, a.passmark, a.vehicle_model,
                        e.client_employees_name, e.surname, e.i_doc_passport, e.occupation,
                        c.clients_name, cs.client_sites_name,
                        u.safesure_users_name AS assessor,
                        b.booking_number,
                        $expiry_date AS expiry_date, $expiry_days AS days_left
                 FROM assesses a
                 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
                 LEFT JOIN safesure_users u ON u.record_id = a.safesure_users_id
                 LEFT JOIN bookings b ON b.record_id = a.booking
                 LEFT JOIN client_sites cs ON cs.record_id = CAST(TRIM(SUBSTRING_INDEX(b.client_sites,',',2)) AS UNSIGNED)
                 WHERE SUBSTRING(a.date,1,10) BETWEEN ? AND ? $where_c

                 UNION ALL

                 SELECT 'test' AS rec_type, t.record_id,
                        t.test_name AS name, t.date, t.results, t.current_mark, t.passmark, t.vehicle_model,
                        e.client_employees_name, e.surname, e.i_doc_passport, e.occupation,
                        c.clients_name, cs.client_sites_name,
                        u.safesure_users_name AS assessor,
                        b.booking_number,
                        $t_expiry_date AS expiry_date, $t_expiry_days AS days_left
                 FROM tests_sec t
                 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
                 LEFT JOIN safesure_users u ON u.record_id = t.safesure_users_id
                 LEFT JOIN bookings b ON b.record_id = t.booking
                 LEFT JOIN client_sites cs ON cs.record_id = CAST(TRIM(SUBSTRING_INDEX(b.client_sites,',',2)) AS UNSIGNED)
                 WHERE SUBSTRING(t.date,1,10) BETWEEN ? AND ? $where_t
                 ORDER BY date DESC LIMIT 2000"
            );
            $stmt->execute(array_merge($drcp, [$from, $to], $drtcp, [$from, $to]));
            $rows = $stmt->fetchAll();

            // Group by client
            $grouped = [];
            foreach ($rows as $row) {
                $cn = $row['clients_name'] ?: 'Unknown';
                $grouped[$cn][] = $row;
            }
            ksort($grouped);
            json_success(['grouped' => $grouped, 'total' => count($rows)]);
            break;

        // ── Site report ───────────────────────────────────────────────────────
        case 'site':
            $site_id = (int)($_GET['client_sites_id'] ?? 0);
            if (!$site_id) json_error('client_sites_id required');

            // Verify site is in an allowed client
            $sc_stmt = $db->prepare('SELECT clients_id, client_sites_name FROM client_sites WHERE record_id=?');
            $sc_stmt->execute([$site_id]);
            $sc = $sc_stmt->fetch();
            $allowed_s = allowed_clients($user);
            if ($sc && $allowed_s !== null && !in_array((int)$sc['clients_id'], $allowed_s)) {
                json_error('Access denied to this site', 403);
            }

            // Step 1: find all bookings containing this site and parse positional CSVs
            // to build pairs of (booking_id, employee_id) that were at this site
            $b_stmt = $db->prepare(
                "SELECT record_id, client_employees, client_sites FROM bookings
                 WHERE client_sites LIKE ?"
            );
            $b_stmt->execute(["%,$site_id,%"]);
            $booking_rows = $b_stmt->fetchAll();

            $pairs = []; // [[booking_id, emp_id], ...]
            foreach ($booking_rows as $bk) {
                $emp_parts  = explode(',', $bk['client_employees'] ?? '');
                $site_parts = explode(',', $bk['client_sites']     ?? '');
                foreach ($emp_parts as $i => $raw) {
                    $eid = (int)trim($raw);
                    $sid = (int)trim($site_parts[$i] ?? '');
                    if ($eid > 0 && $sid === $site_id) {
                        $pairs[] = [$bk['record_id'], $eid];
                    }
                }
            }

            if (empty($pairs)) {
                json_success(['rows' => [], 'site' => $sc, 'total' => 0]);
                break;
            }

            // Step 2: build OR conditions for (booking=X AND client_employees_id=Y)
            $or_clauses = implode(' OR ', array_fill(0, count($pairs), '(a.booking=? AND a.client_employees_id=?)'));
            $params_a   = array_merge(...array_map(fn($p) => [$p[0], $p[1]], $pairs));

            $ass_stmt = $db->prepare(
                "SELECT 'assessment' AS rec_type,
                        a.record_id, a.assesses_name AS name, a.date, a.results,
                        a.current_mark, a.passmark, a.vehicle_model,
                        a.nqf_level, a.credits,
                        e.client_employees_name, e.surname, e.i_doc_passport, e.occupation,
                        c.clients_name,
                        u.safesure_users_name AS assessor,
                        b.booking_number,
                        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
                 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
                 LEFT JOIN safesure_users u    ON u.record_id  = a.safesure_users_id
                 LEFT JOIN bookings b          ON b.record_id  = a.booking
                 WHERE ($or_clauses)
                 ORDER BY a.date DESC"
            );
            $ass_stmt->execute($params_a);
            $ass_rows = $ass_stmt->fetchAll();

            // Same for tests_sec
            $or_t   = implode(' OR ', array_fill(0, count($pairs), '(t.booking=? AND t.client_employees_id=?)'));
            $params_t = $params_a;

            $tst_stmt = $db->prepare(
                "SELECT 'test' AS rec_type,
                        t.record_id, t.test_name AS name, t.date, t.results,
                        t.current_mark, t.passmark, t.vehicle_model,
                        t.nqf_level, t.credits,
                        e.client_employees_name, e.surname, e.i_doc_passport, e.occupation,
                        c.clients_name,
                        u.safesure_users_name AS assessor,
                        b.booking_number,
                        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
                 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
                 LEFT JOIN safesure_users u   ON u.record_id  = t.safesure_users_id
                 LEFT JOIN bookings b         ON b.record_id  = t.booking
                 WHERE ($or_t)
                 ORDER BY t.date DESC"
            );
            $tst_stmt->execute($params_t);
            $tst_rows = $tst_stmt->fetchAll();

            $all_rows = array_merge($ass_rows, $tst_rows);
            // Sort by date desc
            usort($all_rows, fn($a, $b) => strcmp($b['date'] ?? '', $a['date'] ?? ''));

            json_success(['rows' => $all_rows, 'site' => $sc, 'total' => count($all_rows)]);
            break;


        default:
            json_error('Specify mode: employee | client | daterange | site');
    }

} catch (Throwable $e) {
    json_error('reports.php [' . $e->getLine() . ']: ' . $e->getMessage(), 500);
}