<?php
require_once '../auth.php';

restore_login_from_cookie();

if (!isset($_SESSION['user_id']) || ($_SESSION['role'] ?? '') !== 'admin') {
    header("Location: ../index.php");
    exit();
}

if (!function_exists('e')) {
    function e($value) {
        return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
    }
}

$search = trim($_GET['search'] ?? '');
$rows = [];

$sql = "
    SELECT
        p.id,
        p.card_number,
        p.mobile,
        latest_visit.member_name,
        latest_visit.visit_date
    FROM agh_patients p
    LEFT JOIN (
        SELECT
            m.patient_id,
            m.member_name,
            v.visit_date
        FROM agh_visits v
        JOIN agh_family_members m ON m.id = v.member_id
        JOIN (
            SELECT
                m2.patient_id,
                MAX(v2.id) AS latest_visit_id
            FROM agh_visits v2
            JOIN agh_family_members m2 ON m2.id = v2.member_id
            GROUP BY m2.patient_id
        ) last_rows ON last_rows.latest_visit_id = v.id
    ) latest_visit ON latest_visit.patient_id = p.id
";

$params = [];
$types = "";

if ($search !== "") {
    $like = '%' . $search . '%';
    $sql .= "
        WHERE
            p.card_number LIKE ?
            OR p.mobile LIKE ?
            OR EXISTS (
                SELECT 1
                FROM agh_family_members sm
                WHERE sm.patient_id = p.id
                AND sm.member_name LIKE ?
            )
    ";
    $types = "sss";
    $params = [$like, $like, $like];
}

$sql .= "
    ORDER BY
        latest_visit.visit_date IS NULL ASC,
        latest_visit.visit_date DESC,
        p.id DESC
";

$stmt = $conn->prepare($sql);

if ($stmt) {
    if ($types !== "") {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $result = $stmt->get_result();

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    }

    $stmt->close();
}

$total_cards = count($rows);
$visited_cards = 0;

foreach ($rows as $row) {
    if (!empty($row['visit_date'])) {
        $visited_cards++;
    }
}

$not_visited_cards = $total_cards - $visited_cards;
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Total Report - AGH</title>
    <link rel="icon" type="image/png" href="../favicon.png">

    <script src="https://cdn.tailwindcss.com"></script>
    <link href="https://fonts.googleapis.com/css2?family=Noto+Sans+Bengali:wght@300;400;500;600;700;800&family=Poppins:wght@400;500;600;700;800&display=swap" rel="stylesheet">

    <style>
        body { font-family: 'Noto Sans Bengali', 'Poppins', sans-serif; }

        .page-bg {
            background:
                radial-gradient(circle at top left, rgba(14, 165, 233, 0.20), transparent 34%),
                radial-gradient(circle at bottom right, rgba(16, 185, 129, 0.20), transparent 34%),
                linear-gradient(135deg, #f8fafc 0%, #eff6ff 45%, #ecfdf5 100%);
        }

        .glass {
            background: rgba(255, 255, 255, 0.78);
            backdrop-filter: blur(18px);
            -webkit-backdrop-filter: blur(18px);
        }

        .panel {
            border: 1px solid rgba(226, 232, 240, 0.92);
            background: rgba(255, 255, 255, 0.92);
            box-shadow: 0 24px 70px rgba(15, 23, 42, 0.08);
        }

        .premium-card {
            border: 1px solid rgba(255, 255, 255, 0.7);
            background: linear-gradient(145deg, rgba(255,255,255,0.95), rgba(248,250,252,0.82));
            box-shadow: 0 22px 55px rgba(15, 23, 42, 0.08);
        }
    </style>
</head>

<body class="page-bg min-h-screen text-slate-800">

    <nav class="sticky top-0 z-50 border-b border-white/70 glass shadow-sm">
        <div class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8 py-4">
            <div class="flex items-center justify-between gap-4">
                <div class="flex items-center gap-6">
                    <a href="index.php" class="flex items-center gap-3">
                        <div class="h-12 w-12 rounded-2xl bg-gradient-to-br from-sky-700 to-emerald-500 text-white flex items-center justify-center font-extrabold shadow-lg shadow-sky-200">
                            AGH
                        </div>
                        <div>
                            <h1 class="text-lg sm:text-xl font-extrabold text-slate-900">AGH Admin</h1>
                            <p class="text-xs text-slate-500">Health Card Management</p>
                        </div>
                    </a>

                    <div class="hidden md:flex items-center gap-2">
                        <a href="index.php" class="rounded-full bg-white border border-slate-100 text-slate-600 hover:text-sky-700 hover:bg-sky-50 px-4 py-2 text-sm font-bold transition">
                            রোগী সার্চ
                        </a>
                        <a href="users.php" class="rounded-full bg-white border border-slate-100 text-slate-600 hover:text-sky-700 hover:bg-sky-50 px-4 py-2 text-sm font-bold transition">
                            অফিসার ম্যানেজমেন্ট
                        </a>
                        <a href="total_report.php" class="rounded-full bg-sky-600 text-white px-4 py-2 text-sm font-bold shadow-lg shadow-sky-100">
                            Total Report
                        </a>
                    </div>
                </div>

                <a href="../logout.php" class="inline-flex items-center gap-2 rounded-full bg-red-500 hover:bg-red-600 text-white px-4 py-2 text-sm font-bold transition shadow-lg shadow-red-100">
                    লগআউট
                </a>
            </div>
        </div>
    </nav>

    <main class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8 py-8">

        <section class="relative overflow-hidden rounded-[2rem] bg-gradient-to-br from-sky-900 via-blue-700 to-emerald-600 text-white p-6 sm:p-8 lg:p-10 shadow-2xl shadow-sky-200 mb-8">
            <div class="absolute inset-0 opacity-15 bg-[linear-gradient(135deg,white_1px,transparent_1px)] bg-[length:24px_24px]"></div>

            <div class="relative z-10 grid lg:grid-cols-[1fr_430px] gap-8 items-end">
                <div>
                    <p class="text-sm font-semibold uppercase tracking-[0.28em] text-sky-100">Total Report</p>
                    <h2 class="mt-4 text-3xl sm:text-5xl font-extrabold leading-tight">কার্ড অনুযায়ী শেষ ভিজিট</h2>
                    <p class="mt-4 max-w-2xl text-sky-50 leading-8">
                        প্রতিটি কার্ডের অধীনে সর্বশেষ কোন রোগী doctor দেখিয়েছে এবং কখন visit করেছে।
                    </p>
                </div>

                <form action="" method="GET" class="rounded-3xl bg-white/15 border border-white/20 p-4 shadow-xl">
                    <label for="search" class="block text-sm font-bold text-sky-50 mb-2">Search</label>
                    <div class="flex flex-col sm:flex-row gap-3">
                        <input
                            id="search"
                            type="text"
                            name="search"
                            value="<?php echo e($search); ?>"
                            placeholder="Card / mobile / patient name"
                            class="flex-1 rounded-2xl border border-white/30 bg-white/95 px-5 py-4 text-slate-900 placeholder:text-slate-400 outline-none focus:ring-4 focus:ring-white/20">

                        <button type="submit" class="inline-flex items-center justify-center rounded-2xl bg-slate-950 hover:bg-slate-900 px-6 py-4 font-extrabold text-white transition active:scale-95">
                            সার্চ
                        </button>
                    </div>

                    <?php if ($search !== ''): ?>
                        <a href="total_report.php" class="mt-3 inline-flex text-sm font-bold text-white hover:text-sky-100">
                            সব রিপোর্ট দেখুন
                        </a>
                    <?php endif; ?>
                </form>
            </div>
        </section>

        <section class="grid grid-cols-1 sm:grid-cols-3 gap-5 mb-8">
            <div class="premium-card rounded-3xl p-6">
                <p class="text-sm font-bold text-slate-500">মোট কার্ড</p>
                <h3 class="mt-3 text-4xl font-extrabold text-sky-600"><?php echo e($total_cards); ?></h3>
            </div>

            <div class="premium-card rounded-3xl p-6">
                <p class="text-sm font-bold text-slate-500">Visit হয়েছে</p>
                <h3 class="mt-3 text-4xl font-extrabold text-emerald-600"><?php echo e($visited_cards); ?></h3>
            </div>

            <div class="premium-card rounded-3xl p-6">
                <p class="text-sm font-bold text-slate-500">এখনো visit নেই</p>
                <h3 class="mt-3 text-4xl font-extrabold text-amber-600"><?php echo e($not_visited_cards); ?></h3>
            </div>
        </section>

        <section class="panel rounded-[2rem] overflow-hidden">
            <div class="p-6 sm:p-7 border-b border-slate-100 flex flex-col sm:flex-row sm:items-center sm:justify-between gap-3">
                <div>
                    <p class="text-sm font-bold text-sky-600">All Cards</p>
                    <h3 class="mt-1 text-2xl font-extrabold text-slate-900">
                        <?php echo $search !== '' ? 'সার্চ রেজাল্ট' : 'Total Report'; ?>
                    </h3>
                </div>

                <span class="inline-flex items-center justify-center rounded-full bg-slate-100 text-slate-700 px-4 py-2 text-sm font-extrabold">
                    <?php echo e(count($rows)); ?>টি কার্ড
                </span>
            </div>

            <div class="overflow-x-auto">
                <table class="w-full text-left">
                    <thead>
                        <tr class="bg-slate-50 text-slate-500 text-xs uppercase tracking-wider">
                            <th class="px-6 py-4 font-extrabold">Serial</th>
                            <th class="px-6 py-4 font-extrabold">Card Number</th>
                            <th class="px-6 py-4 font-extrabold">Last Patient</th>
                            <th class="px-6 py-4 font-extrabold">Last Visit</th>
                            <th class="px-6 py-4 font-extrabold">Status</th>
                        </tr>
                    </thead>

                    <tbody class="divide-y divide-slate-100">
                        <?php if (count($rows) > 0): ?>
                            <?php foreach ($rows as $index => $row): ?>
                                <tr class="hover:bg-sky-50/60 transition">
                                    <td class="px-6 py-5 font-extrabold text-slate-700">
                                        <?php echo e($index + 1); ?>
                                    </td>

                                    <td class="px-6 py-5">
                                        <p class="font-extrabold text-sky-700">
                                            <?php echo e($row['card_number']); ?>
                                        </p>
                                        <p class="mt-1 text-xs font-semibold text-slate-400">
                                            <?php echo e($row['mobile']); ?>
                                        </p>
                                    </td>

                                    <td class="px-6 py-5 font-bold text-slate-800">
                                        <?php echo !empty($row['member_name']) ? e($row['member_name']) : '<span class="text-slate-400">এখনো visit নেই</span>'; ?>
                                    </td>

                                    <td class="px-6 py-5 font-semibold text-slate-600">
                                        <?php echo !empty($row['visit_date']) ? e(date('d M, Y | h:i A', strtotime($row['visit_date']))) : '<span class="text-slate-400">N/A</span>'; ?>
                                    </td>

                                    <td class="px-6 py-5">
                                        <?php if (!empty($row['visit_date'])): ?>
                                            <span class="inline-flex rounded-full bg-emerald-50 text-emerald-700 border border-emerald-100 px-4 py-2 text-xs font-extrabold">
                                                Visited
                                            </span>
                                        <?php else: ?>
                                            <span class="inline-flex rounded-full bg-amber-50 text-amber-700 border border-amber-100 px-4 py-2 text-xs font-extrabold">
                                                No Visit
                                            </span>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="5" class="px-6 py-16 text-center">
                                    <h4 class="text-lg font-extrabold text-slate-700">কোনো রিপোর্ট পাওয়া যায়নি</h4>
                                    <p class="mt-2 text-sm text-slate-400">অন্য card number, mobile অথবা patient name দিয়ে search করুন।</p>
                                </td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </section>

    </main>

</body>
</html>