<?php
/**
 * Dashboard analytics helpers — date-range resolution, a KPI catalogue, per-role
 * KPI selection and chart series. Consumed by ajax/dashboard_stats.php and
 * dashboard.php. Every metric is computed for the selected window AND the
 * immediately-preceding window of equal length, so the UI can show a trend delta.
 */

if (!function_exists('dashboard_date_range')) {

    /** Does a table exist? (cached) */
    function dash_table_exists(mysqli $conn, string $t): bool
    {
        static $cache = [];
        if (isset($cache[$t])) return $cache[$t];
        $t2 = $conn->real_escape_string($t);
        $r = $conn->query("SHOW TABLES LIKE '{$t2}'");
        return $cache[$t] = ($r && $r->num_rows > 0);
    }

    /** Single scalar helper (returns float). */
    function dash_scalar(mysqli $conn, string $sql): float
    {
        $r = $conn->query($sql);
        if (!$r) return 0.0;
        $row = $r->fetch_row();
        return $row ? (float) $row[0] : 0.0;
    }

    /**
     * Resolve a range key to concrete dates + the previous comparison window.
     * Returns [key,label,from,to,prev_from,prev_to,bucket].
     */
    function dashboard_date_range(mysqli $conn, string $key, ?string $from = null, ?string $to = null): array
    {
        $today = date('Y-m-d');
        $mk = function ($f, $t, $bucket, $label) {
            $len = max(1, (int) ((strtotime($t) - strtotime($f)) / 86400) + 1);
            $pt  = date('Y-m-d', strtotime($f . ' -1 day'));
            $pf  = date('Y-m-d', strtotime($pt . ' -' . ($len - 1) . ' day'));
            return ['from' => $f, 'to' => $t, 'prev_from' => $pf, 'prev_to' => $pt, 'bucket' => $bucket, 'label' => $label];
        };

        switch ($key) {
            case 'today':      $r = $mk($today, $today, 'hour', 'Today'); break;
            case 'yesterday':  $y = date('Y-m-d', strtotime('-1 day')); $r = $mk($y, $y, 'hour', 'Yesterday'); break;
            case 'this_week':  $r = $mk(date('Y-m-d', strtotime('monday this week')), $today, 'day', 'This Week'); break;
            case 'last_week':  $r = $mk(date('Y-m-d', strtotime('monday last week')), date('Y-m-d', strtotime('sunday last week')), 'day', 'Last Week'); break;
            case 'this_month': $r = $mk(date('Y-m-01'), $today, 'day', 'This Month'); break;
            case 'last_month': $r = $mk(date('Y-m-01', strtotime('first day of last month')), date('Y-m-t', strtotime('last day of last month')), 'day', 'Last Month'); break;
            case 'this_year':  $r = $mk(date('Y-01-01'), $today, 'month', 'This Year'); break;
            case 'this_term':
                $term = function_exists('getSettingValue') ? (getSettingValue('current_term') ?: 'First Term') : 'First Term';
                $year = function_exists('getSettingValue') ? (getSettingValue('academic_year') ?: date('Y')) : date('Y');
                $tf = $conn->real_escape_string($term); $yf = $conn->real_escape_string($year);
                $row = null;
                if (dash_table_exists($conn, 'school_terms')) {
                    $q = $conn->query("SELECT start_date, end_date FROM school_terms WHERE term='{$tf}' AND academic_year='{$yf}' ORDER BY (programme_level='All') DESC LIMIT 1");
                    $row = $q ? $q->fetch_assoc() : null;
                }
                $ts = $row['start_date'] ?? date('Y-m-01');
                $te = min($today, $row['end_date'] ?? $today);
                $r = $mk($ts, $te, 'week', $term); break;
            case 'custom':
                $f = ($from && preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) ? $from : date('Y-m-01');
                $t = ($to && preg_match('/^\d{4}-\d{2}-\d{2}$/', $to)) ? $to : $today;
                if ($t < $f) [$f, $t] = [$t, $f];
                $span = (strtotime($t) - strtotime($f)) / 86400;
                $r = $mk($f, $t, $span > 92 ? 'month' : ($span > 31 ? 'week' : 'day'), 'Custom'); break;
            case 'all_time':   $r = $mk('2000-01-01', $today, 'month', 'All Time'); break;
            default:           $r = $mk(date('Y-m-01'), $today, 'day', 'This Month'); $key = 'this_month';
        }
        $r['key'] = $key;
        return $r;
    }

    // ── KPI catalogue ──────────────────────────────────────────────────────
    // Each entry: label, icon, color, format(currency|int|percent), type(period|snapshot),
    // and a fn(conn,from,to) returning the value for that window.
    function dashboard_kpi_catalog(): array
    {
        return [
            'collections' => ['label' => 'Collections', 'icon' => 'fa-hand-holding-dollar', 'color' => 'success', 'format' => 'currency', 'type' => 'period',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COALESCE(SUM(amount_paid),0) FROM payments WHERE payment_date BETWEEN '$f' AND '$t'")],
            'online_collections' => ['label' => 'Online Collections', 'icon' => 'fa-mobile-screen-button', 'color' => 'info', 'format' => 'currency', 'type' => 'period',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COALESCE(SUM(amount_paid),0) FROM payments WHERE payment_method='online' AND payment_date BETWEEN '$f' AND '$t'")],
            'expenses' => ['label' => 'Expenses', 'icon' => 'fa-file-invoice-dollar', 'color' => 'danger', 'format' => 'currency', 'type' => 'period',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN '$f' AND '$t'")],
            'net' => ['label' => 'Net Balance', 'icon' => 'fa-scale-balanced', 'color' => 'primary', 'format' => 'currency', 'type' => 'period',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COALESCE(SUM(amount_paid),0) FROM payments WHERE payment_date BETWEEN '$f' AND '$t'")
                    - dash_scalar($c, "SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN '$f' AND '$t'")],
            'new_payments' => ['label' => 'Payments Recorded', 'icon' => 'fa-receipt', 'color' => 'success', 'format' => 'int', 'type' => 'period',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM payments WHERE payment_date BETWEEN '$f' AND '$t'")],
            'new_admissions' => ['label' => 'New Admissions', 'icon' => 'fa-user-plus', 'color' => 'primary', 'format' => 'int', 'type' => 'period',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM students WHERE DATE(created_at) BETWEEN '$f' AND '$t'")],
            'attendance_rate' => ['label' => 'Attendance Rate', 'icon' => 'fa-user-check', 'color' => 'info', 'format' => 'percent', 'type' => 'period',
                'fn' => function ($c, $f, $t) {
                    $tot = dash_scalar($c, "SELECT COUNT(*) FROM attendance WHERE attendance_date BETWEEN '$f' AND '$t'");
                    if ($tot <= 0) return 0.0;
                    $pre = dash_scalar($c, "SELECT COUNT(*) FROM attendance WHERE status='present' AND attendance_date BETWEEN '$f' AND '$t'");
                    return round($pre / $tot * 100, 1);
                }],
            'active_students' => ['label' => 'Active Students', 'icon' => 'fa-user-graduate', 'color' => 'primary', 'format' => 'int', 'type' => 'snapshot',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM students WHERE status='active'")],
            'staff_count' => ['label' => 'Staff', 'icon' => 'fa-chalkboard-user', 'color' => 'secondary', 'format' => 'int', 'type' => 'snapshot',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM staff WHERE status='active' OR status IS NULL")],
            'pending_approvals' => ['label' => 'Pending Approvals', 'icon' => 'fa-clipboard-check', 'color' => 'warning', 'format' => 'int', 'type' => 'snapshot', 'needs' => 'expense_requests',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM expense_requests WHERE status='pending'")],
            'infirmary_visits' => ['label' => 'Infirmary Visits', 'icon' => 'fa-briefcase-medical', 'color' => 'danger', 'format' => 'int', 'type' => 'period', 'needs' => 'infirmary_visits',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM infirmary_visits WHERE DATE(visit_date) BETWEEN '$f' AND '$t'")],
            'students_seen' => ['label' => 'Students Seen', 'icon' => 'fa-user-nurse', 'color' => 'info', 'format' => 'int', 'type' => 'period', 'needs' => 'infirmary_visits',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(DISTINCT student_id) FROM infirmary_visits WHERE DATE(visit_date) BETWEEN '$f' AND '$t'")],
            'sent_home' => ['label' => 'Sent Home', 'icon' => 'fa-house-medical', 'color' => 'warning', 'format' => 'int', 'type' => 'period', 'needs' => 'infirmary_visits',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM infirmary_visits WHERE outcome='sent_home' AND DATE(visit_date) BETWEEN '$f' AND '$t'")],
            'health_profiles' => ['label' => 'Health Profiles', 'icon' => 'fa-notes-medical', 'color' => 'success', 'format' => 'int', 'type' => 'snapshot', 'needs' => 'student_health',
                'fn' => fn($c, $f, $t) => dash_scalar($c, "SELECT COUNT(*) FROM student_health")],
        ];
    }

    /** Ordered KPI keys per role. */
    function dashboard_role_kpis(string $role): array
    {
        $map = [
            'admin'       => ['collections', 'expenses', 'net', 'attendance_rate', 'new_admissions', 'pending_approvals'],
            'director'    => ['collections', 'expenses', 'net', 'attendance_rate', 'new_admissions', 'active_students'],
            'supervisor'  => ['collections', 'expenses', 'net', 'attendance_rate', 'new_admissions', 'active_students'],
            'accountant'  => ['collections', 'online_collections', 'expenses', 'net', 'new_payments', 'pending_approvals'],
            'cashier'     => ['collections', 'online_collections', 'new_payments', 'expenses'],
            'headteacher' => ['active_students', 'attendance_rate', 'new_admissions', 'collections', 'pending_approvals', 'staff_count'],
            'nurse'       => ['infirmary_visits', 'students_seen', 'sent_home', 'health_profiles'],
            'tutor'       => ['attendance_rate', 'active_students', 'new_admissions'],
            'registrar'   => ['active_students', 'new_admissions', 'staff_count'],
        ];
        return $map[$role] ?? $map['admin'];
    }

    /** Compute the KPI cards for a role + range (value, prev, delta%). */
    function dashboard_compute_kpis(mysqli $conn, array $range, string $role): array
    {
        $catalog = dashboard_kpi_catalog();
        $cards = [];
        foreach (dashboard_role_kpis($role) as $key) {
            if (!isset($catalog[$key])) continue;
            $def = $catalog[$key];
            if (!empty($def['needs']) && !dash_table_exists($conn, $def['needs'])) continue;
            $val  = ($def['fn'])($conn, $range['from'], $range['to']);
            $card = ['key' => $key, 'label' => $def['label'], 'icon' => $def['icon'], 'color' => $def['color'], 'format' => $def['format'], 'type' => $def['type'], 'value' => $val];
            if ($def['type'] === 'period') {
                $prev = ($def['fn'])($conn, $range['prev_from'], $range['prev_to']);
                $card['prev'] = $prev;
                $card['delta'] = ($prev == 0.0) ? null : round(($val - $prev) / abs($prev) * 100, 1);
                // For expenses, a rise is "bad" — flag direction so the UI can colour it.
                $card['good_when_up'] = !in_array($key, ['expenses'], true);
            }
            $cards[] = $card;
        }
        return $cards;
    }

    /** Collections vs expenses series bucketed for the chart. */
    function dashboard_series(mysqli $conn, array $range): array
    {
        $from = $range['from']; $to = $range['to']; $bucket = $range['bucket'];
        // Choose the SQL date expression + a PHP label per bucket.
        switch ($bucket) {
            case 'hour':  $fmt = '%H:00'; $grp = "DATE_FORMAT(%s, '%%Y-%%m-%%d %%H')"; break;
            case 'month': $fmt = '%b';    $grp = "DATE_FORMAT(%s, '%%Y-%%m')"; break;
            case 'week':  $fmt = 'W%v';   $grp = "YEARWEEK(%s, 3)"; break;
            default:      $fmt = '%d %b'; $grp = "DATE(%s)"; break;
        }
        // Build a set of buckets from payments + expenses combined.
        $payGrp = sprintf($grp, 'payment_date');
        $expGrp = sprintf($grp, 'expense_date');
        $labels = []; $collections = []; $expenses = [];

        $pay = [];
        $q = $conn->query("SELECT {$payGrp} k, DATE_FORMAT(MIN(payment_date), '{$fmt}') lbl, COALESCE(SUM(amount_paid),0) v
                           FROM payments WHERE payment_date BETWEEN '$from' AND '$to' GROUP BY k ORDER BY k");
        while ($q && $r = $q->fetch_assoc()) $pay[$r['k']] = ['lbl' => $r['lbl'], 'v' => (float) $r['v']];

        $exp = [];
        $q = $conn->query("SELECT {$expGrp} k, DATE_FORMAT(MIN(expense_date), '{$fmt}') lbl, COALESCE(SUM(amount),0) v
                           FROM expenses WHERE expense_date BETWEEN '$from' AND '$to' GROUP BY k ORDER BY k");
        while ($q && $r = $q->fetch_assoc()) $exp[$r['k']] = ['lbl' => $r['lbl'], 'v' => (float) $r['v']];

        $keys = array_unique(array_merge(array_keys($pay), array_keys($exp)));
        sort($keys);
        foreach ($keys as $k) {
            $labels[]      = $pay[$k]['lbl'] ?? $exp[$k]['lbl'] ?? (string) $k;
            $collections[] = $pay[$k]['v'] ?? 0;
            $expenses[]    = $exp[$k]['v'] ?? 0;
        }
        return ['labels' => $labels, 'collections' => $collections, 'expenses' => $expenses];
    }

    /** A role-appropriate secondary chart (doughnut) for the period. */
    function dashboard_secondary_chart(mysqli $conn, array $range, string $role): array
    {
        $from = $range['from']; $to = $range['to'];
        $palette = ['#2e90fa', '#12b76a', '#f79009', '#f04438', '#7a5af8', '#0ba5ec', '#ee46bc'];

        if ($role === 'nurse' && dash_table_exists($conn, 'infirmary_visits')) {
            $labels = []; $data = [];
            $q = $conn->query("SELECT outcome, COUNT(*) n FROM infirmary_visits WHERE DATE(visit_date) BETWEEN '$from' AND '$to' GROUP BY outcome");
            while ($q && $r = $q->fetch_assoc()) { $labels[] = ucwords(str_replace('_', ' ', $r['outcome'])); $data[] = (int) $r['n']; }
            return ['type' => 'doughnut', 'title' => 'Infirmary Outcomes', 'labels' => $labels, 'data' => $data, 'colors' => $palette, 'currency' => false];
        }
        if ($role === 'tutor') {
            $labels = []; $data = [];
            $q = $conn->query("SELECT status, COUNT(*) n FROM attendance WHERE attendance_date BETWEEN '$from' AND '$to' GROUP BY status");
            while ($q && $r = $q->fetch_assoc()) { $labels[] = ucfirst($r['status']); $data[] = (int) $r['n']; }
            return ['type' => 'doughnut', 'title' => 'Attendance Breakdown', 'labels' => $labels, 'data' => $data, 'colors' => $palette, 'currency' => false];
        }
        // Default (finance/leadership): collections by payment method.
        $labels = []; $data = [];
        $q = $conn->query("SELECT payment_method, COALESCE(SUM(amount_paid),0) v FROM payments WHERE payment_date BETWEEN '$from' AND '$to' GROUP BY payment_method ORDER BY v DESC");
        while ($q && $r = $q->fetch_assoc()) { $labels[] = ucwords(str_replace('_', ' ', $r['payment_method'] ?: 'Other')); $data[] = (float) $r['v']; }
        return ['type' => 'doughnut', 'title' => 'Collections by Method', 'labels' => $labels, 'data' => $data, 'colors' => $palette, 'currency' => true];
    }

    /** Ordered attention-list widget keys per role. */
    function dashboard_role_lists(string $role): array
    {
        $m = [
            'admin'       => ['recent_payments', 'pending_approvals', 'at_risk', 'upcoming_events'],
            'director'    => ['recent_payments', 'pending_approvals', 'at_risk'],
            'supervisor'  => ['recent_payments', 'pending_approvals', 'at_risk'],
            'accountant'  => ['recent_payments', 'pending_approvals'],
            'cashier'     => ['recent_payments'],
            'headteacher' => ['pending_approvals', 'at_risk', 'recent_admissions'],
            'nurse'       => ['recent_infirmary'],
            'tutor'       => ['recent_admissions'],
            'registrar'   => ['recent_admissions'],
        ];
        return $m[$role] ?? $m['admin'];
    }

    /** Build the role's attention-list widgets (compact tables). */
    function dashboard_lists(mysqli $conn, array $range, string $role): array
    {
        $from = $range['from']; $to = $range['to'];
        $out = [];
        foreach (dashboard_role_lists($role) as $key) {
            switch ($key) {
                case 'recent_payments':
                    $rows = [];
                    $q = $conn->query("SELECT p.payment_date, CONCAT(s.first_name,' ',s.last_name) nm, fs.fee_type, p.amount_paid
                        FROM payments p JOIN students s ON s.id=p.student_id LEFT JOIN fee_structures fs ON fs.id=p.fee_structure_id
                        WHERE p.payment_date BETWEEN '$from' AND '$to' ORDER BY p.id DESC LIMIT 8");
                    while ($q && $r = $q->fetch_assoc()) $rows[] = [date('d M', strtotime($r['payment_date'])), $r['nm'], $r['fee_type'] ?: '—', 'GHS ' . number_format((float) $r['amount_paid'], 2)];
                    $out[] = ['key' => $key, 'title' => 'Recent Payments', 'icon' => 'fa-receipt', 'cols' => ['Date', 'Student', 'Fee', 'Amount'], 'rows' => $rows, 'link' => 'fees.php', 'empty' => 'No payments in this period.'];
                    break;
                case 'pending_approvals':
                    if (!dash_table_exists($conn, 'expense_requests')) break;
                    $rows = [];
                    $q = $conn->query("SELECT request_no, category, amount, requester_name FROM expense_requests WHERE status='pending' ORDER BY id DESC LIMIT 8");
                    while ($q && $r = $q->fetch_assoc()) $rows[] = [$r['request_no'], $r['category'], $r['requester_name'], 'GHS ' . number_format((float) $r['amount'], 2)];
                    $out[] = ['key' => $key, 'title' => 'Pending Approvals', 'icon' => 'fa-clipboard-check', 'cols' => ['Ref', 'Category', 'By', 'Amount'], 'rows' => $rows, 'link' => 'finance/approvals.php', 'empty' => 'Nothing awaiting approval.'];
                    break;
                case 'at_risk':
                    if (!dash_table_exists($conn, 'scores')) break;
                    $term = function_exists('getSettingValue') ? (getSettingValue('current_term') ?: 'First Term') : 'First Term';
                    $year = function_exists('getSettingValue') ? (getSettingValue('academic_year') ?: date('Y')) : date('Y');
                    $tf = $conn->real_escape_string($term); $yf = $conn->real_escape_string($year);
                    $rows = [];
                    $q = $conn->query("SELECT CONCAT(s.first_name,' ',s.last_name) nm, s.class, ROUND(AVG(sc.total_score),1) av
                        FROM scores sc JOIN students s ON s.id=sc.student_id
                        WHERE sc.term='$tf' AND sc.academic_year='$yf' GROUP BY sc.student_id HAVING av < 50 ORDER BY av ASC LIMIT 8");
                    while ($q && $r = $q->fetch_assoc()) $rows[] = [$r['nm'], $r['class'], $r['av'] . '%'];
                    $out[] = ['key' => $key, 'title' => 'At-Risk Students', 'icon' => 'fa-triangle-exclamation', 'cols' => ['Student', 'Class', 'Avg'], 'rows' => $rows, 'link' => 'analytics/progress.php', 'empty' => 'No at-risk students this term.'];
                    break;
                case 'upcoming_events':
                    if (!dash_table_exists($conn, 'event_accounts')) break;
                    $today = date('Y-m-d'); $rows = [];
                    $q = $conn->query("SELECT name, event_date, status FROM event_accounts WHERE (event_date >= '$today' OR status != 'closed') ORDER BY event_date IS NULL, event_date ASC LIMIT 6");
                    while ($q && $r = $q->fetch_assoc()) $rows[] = [$r['event_date'] ? date('d M', strtotime($r['event_date'])) : '—', $r['name'], ucfirst($r['status'])];
                    $out[] = ['key' => $key, 'title' => 'Events & Excursions', 'icon' => 'fa-bus', 'cols' => ['Date', 'Event', 'Status'], 'rows' => $rows, 'link' => 'finance/events.php', 'empty' => 'No upcoming events.'];
                    break;
                case 'recent_infirmary':
                    if (!dash_table_exists($conn, 'infirmary_visits')) break;
                    $rows = [];
                    $q = $conn->query("SELECT iv.visit_date, CONCAT(s.first_name,' ',s.last_name) nm, iv.complaint, iv.outcome
                        FROM infirmary_visits iv JOIN students s ON s.id=iv.student_id
                        WHERE DATE(iv.visit_date) BETWEEN '$from' AND '$to' ORDER BY iv.id DESC LIMIT 8");
                    while ($q && $r = $q->fetch_assoc()) $rows[] = [date('d M', strtotime($r['visit_date'])), $r['nm'], $r['complaint'], ucwords(str_replace('_', ' ', $r['outcome']))];
                    $out[] = ['key' => $key, 'title' => 'Recent Infirmary Visits', 'icon' => 'fa-briefcase-medical', 'cols' => ['Date', 'Student', 'Complaint', 'Outcome'], 'rows' => $rows, 'link' => 'health/index.php', 'empty' => 'No visits in this period.'];
                    break;
                case 'recent_admissions':
                    $rows = [];
                    $q = $conn->query("SELECT CONCAT(first_name,' ',last_name) nm, class, created_at FROM students WHERE DATE(created_at) BETWEEN '$from' AND '$to' ORDER BY id DESC LIMIT 8");
                    while ($q && $r = $q->fetch_assoc()) $rows[] = [date('d M', strtotime($r['created_at'])), $r['nm'], $r['class']];
                    $out[] = ['key' => $key, 'title' => 'Recent Admissions', 'icon' => 'fa-user-plus', 'cols' => ['Date', 'Student', 'Class'], 'rows' => $rows, 'link' => 'students.php', 'empty' => 'No admissions in this period.'];
                    break;
            }
        }
        return $out;
    }
}
