<?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');
    }
}

function report_bind_params($stmt, $types, $params) {
    if ($types === '') return;

    $refs = [$types];
    foreach ($params as $key => $value) {
        $refs[] = &$params[$key];
    }

    call_user_func_array([$stmt, 'bind_param'], $refs);
}

function report_fetch_all($conn, $sql, $types = '', $params = []) {
    $stmt = $conn->prepare($sql);
    if (!$stmt) return [];

    report_bind_params($stmt, $types, $params);
    $stmt->execute();

    $result = $stmt->get_result();
    $rows = [];

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

    $stmt->close();
    return $rows;
}

function report_fetch_one($conn, $sql, $types = '', $params = []) {
    $rows = report_fetch_all($conn, $sql, $types, $params);
    return $rows[0] ?? [];
}

$today = date('Y-m-d');
$date_from = trim($_GET['date_from'] ?? '');
$date_to = trim($_GET['date_to'] ?? '');
$officer_id = (int) ($_GET['officer_id'] ?? 0);

if ($date_from !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) {
    $date_from = '';
}

if ($date_to !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to)) {
    $date_to = '';
}

$period_label = 'সব সময়';
if ($date_from !== '' && $date_to !== '') {
    $period_label = date('d M, Y', strtotime($date_from)) . ' থেকে ' . date('d M, Y', strtotime($date_to));
} elseif ($date_from !== '') {
    $period_label = date('d M, Y', strtotime($date_from)) . ' থেকে আজ পর্যন্ত';
} elseif ($date_to !== '') {
    $period_label = date('d M, Y', strtotime($date_to)) . ' পর্যন্ত';
}

$officers = [];
$officer_result = $conn->query("
    SELECT id, full_name
    FROM agh_users
    WHERE role = 'officer'
    ORDER BY full_name ASC
");

if ($officer_result) {
    while ($row = $officer_result->fetch_assoc()) {
        $officers[] = $row;
    }
}

$patient_conditions = [];
$patient_types = '';
$patient_params = [];

if ($officer_id > 0) {
    $patient_conditions[] = 'p.marketing_officer_id = ?';
    $patient_types .= 'i';
    $patient_params[] = $officer_id;
}

if ($date_from !== '') {
    $patient_conditions[] = 'DATE(p.reg_date) >= ?';
    $patient_types .= 's';
    $patient_params[] = $date_from;
}

if ($date_to !== '') {
    $patient_conditions[] = 'DATE(p.reg_date) <= ?';
    $patient_types .= 's';
    $patient_params[] = $date_to;
}

$patient_where = count($patient_conditions) ? 'WHERE ' . implode(' AND ', $patient_conditions) : '';

$summary = report_fetch_one($conn, "
    SELECT
        COUNT(DISTINCT p.id) AS total_cards,
        COUNT(DISTINCT m.id) AS total_patients
    FROM agh_patients p
    LEFT JOIN agh_family_members m ON m.patient_id = p.id
    $patient_where
", $patient_types, $patient_params);

$total_cards = (int) ($summary['total_cards'] ?? 0);
$total_patients = (int) ($summary['total_patients'] ?? 0);

$visit_conditions = [];
$visit_types = '';
$visit_params = [];

if ($officer_id > 0) {
    $visit_conditions[] = 'p.marketing_officer_id = ?';
    $visit_types .= 'i';
    $visit_params[] = $officer_id;
}

if ($date_from !== '') {
    $visit_conditions[] = 'DATE(v.visit_date) >= ?';
    $visit_types .= 's';
    $visit_params[] = $date_from;
}

if ($date_to !== '') {
    $visit_conditions[] = 'DATE(v.visit_date) <= ?';
    $visit_types .= 's';
    $visit_params[] = $date_to;
}

$visit_where = count($visit_conditions) ? 'WHERE ' . implode(' AND ', $visit_conditions) : '';

$visit_summary = report_fetch_one($conn, "
    SELECT
        COUNT(v.id) AS total_visits,
        COUNT(DISTINCT v.member_id) AS visited_patients
    FROM agh_visits v
    JOIN agh_family_members m ON m.id = v.member_id
    JOIN agh_patients p ON p.id = m.patient_id
    $visit_where
", $visit_types, $visit_params);

$total_visits = (int) ($visit_summary['total_visits'] ?? 0);
$visited_patients = (int) ($visit_summary['visited_patients'] ?? 0);

$officer_filter_sql = $officer_id > 0 ? 'AND u.id = ?' : '';
$officer_filter_types = $officer_id > 0 ? 'i' : '';
$officer_filter_params = $officer_id > 0 ? [$officer_id] : [];

$date_patient_sql = '';
$date_patient_types = '';
$date_patient_params = [];

if ($date_from !== '') {
    $date_patient_sql .= ' AND DATE(p.reg_date) >= ?';
    $date_patient_types .= 's';
    $date_patient_params[] = $date_from;
}

if ($date_to !== '') {
    $date_patient_sql .= ' AND DATE(p.reg_date) <= ?';
    $date_patient_types .= 's';
    $date_patient_params[] = $date_to;
}

$date_visit_sql = '';
$date_visit_types = '';
$date_visit_params = [];

if ($date_from !== '') {
    $date_visit_sql .= ' AND DATE(v.visit_date) >= ?';
    $date_visit_types .= 's';
    $date_visit_params[] = $date_from;
}

if ($date_to !== '') {
    $date_visit_sql .= ' AND DATE(v.visit_date) <= ?';
    $date_visit_types .= 's';
    $date_visit_params[] = $date_to;
}

$officer_stats_types = $date_patient_types . $date_visit_types . $officer_filter_types;
$officer_stats_params = array_merge($date_patient_params, $date_visit_params, $officer_filter_params);

$officer_stats = report_fetch_all($conn, "
    SELECT
        u.id,
        u.full_name,
        COALESCE(patient_data.total_cards, 0) AS total_cards,
        COALESCE(patient_data.total_patients, 0) AS total_patients,
        COALESCE(visit_data.total_visits, 0) AS total_visits,
        COALESCE(visit_data.visited_patients, 0) AS visited_patients
    FROM agh_users u
    LEFT JOIN (
        SELECT
            p.marketing_officer_id,
            COUNT(DISTINCT p.id) AS total_cards,
            COUNT(DISTINCT m.id) AS total_patients
        FROM agh_patients p
        LEFT JOIN agh_family_members m ON m.patient_id = p.id
        WHERE 1=1 $date_patient_sql
        GROUP BY p.marketing_officer_id
    ) patient_data ON patient_data.marketing_officer_id = u.id
    LEFT JOIN (
        SELECT
            p.marketing_officer_id,
            COUNT(v.id) AS total_visits,
            COUNT(DISTINCT v.member_id) AS visited_patients
        FROM agh_visits v
        JOIN agh_family_members m ON m.id = v.member_id
        JOIN agh_patients p ON p.id = m.patient_id
        WHERE 1=1 $date_visit_sql
        GROUP BY p.marketing_officer_id
    ) visit_data ON visit_data.marketing_officer_id = u.id
    WHERE u.role = 'officer' $officer_filter_sql
    ORDER BY total_patients DESC, total_cards DESC, u.full_name ASC
", $officer_stats_types, $officer_stats_params);

$card_rows = report_fetch_all($conn, "
    SELECT
        p.id,
        p.card_number,
        p.mobile,
        p.reg_date,
        COALESCE(u.full_name, 'সিস্টেম এডমিন') AS officer_name,
        COUNT(DISTINCT m.id) AS total_patients,
        COALESCE(visit_data.total_visits, 0) AS total_visits,
        COALESCE(visit_data.visited_patients, 0) AS visited_patients,
        visit_data.last_visit
    FROM agh_patients p
    LEFT JOIN agh_users u ON u.id = p.marketing_officer_id
    LEFT JOIN agh_family_members m ON m.patient_id = p.id
    LEFT JOIN (
        SELECT
            m.patient_id,
            COUNT(v.id) AS total_visits,
            COUNT(DISTINCT v.member_id) AS visited_patients,
            MAX(v.visit_date) AS last_visit
        FROM agh_family_members m
        JOIN agh_visits v ON v.member_id = m.id
        WHERE 1=1 $date_visit_sql
        GROUP BY m.patient_id
    ) visit_data ON visit_data.patient_id = p.id
    $patient_where
    GROUP BY p.id, p.card_number, p.mobile, p.reg_date, u.full_name, visit_data.total_visits, visit_data.visited_patients, visit_data.last_visit
    ORDER BY p.id DESC
    LIMIT 300
", $date_visit_types . $patient_types, array_merge($date_visit_params, $patient_params));
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Patient 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.18), transparent 34%),
                radial-gradient(circle at bottom right, rgba(16, 185, 129, 0.18), transparent 34%),
                linear-gradient(135deg, #f8fafc 0%, #eff6ff 48%, #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.9);
            background: rgba(255, 255, 255, 0.9);
            box-shadow: 0 24px 70px rgba(15, 23, 42, 0.08);
        }

        .field {
            width: 100%;
            border-radius: 1rem;
            border: 1px solid #e2e8f0;
            background: rgba(248, 250, 252, 0.95);
            padding: 0.85rem 1rem;
            color: #0f172a;
            outline: none;
            transition: 0.2s ease;
        }

        .field:focus {
            border-color: #0ea5e9;
            background: #ffffff;
            box-shadow: 0 0 0 4px rgba(14, 165, 233, 0.13);
        }

        .label {
            display: block;
            margin-bottom: 0.45rem;
            font-size: 0.875rem;
            font-weight: 700;
            color: #475569;
        }
    </style>
</head>

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

    <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">Patient Analytics</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="patient_report.php" class="rounded-full bg-sky-600 text-white px-4 py-2 text-sm font-bold shadow-lg shadow-sky-100">
                            রিপোর্ট
                        </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>
                    </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-800 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 flex flex-col lg:flex-row lg:items-end lg:justify-between gap-6">
                <div>
                    <p class="text-sm font-semibold uppercase tracking-[0.28em] text-sky-100">Admin 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">
                        সময় অথবা অফিসার অনুযায়ী কতটি কার্ড, কতজন রোগী/সদস্য এবং কতটি ভিজিট হয়েছে দেখুন।
                    </p>
                </div>

                <div class="rounded-3xl bg-white/15 border border-white/20 p-5 min-w-full sm:min-w-[280px]">
                    <p class="text-sm font-bold text-sky-100">রিপোর্ট সময়</p>
                    <p class="mt-2 text-xl font-extrabold"><?php echo e($period_label); ?></p>
                </div>
            </div>
        </section>

        <form action="" method="GET" class="panel rounded-[2rem] p-5 sm:p-6 mb-8">
            <div class="grid grid-cols-1 md:grid-cols-4 gap-4 items-end">
                <div>
                    <label for="date_from" class="label">শুরুর তারিখ</label>
                    <input id="date_from" type="date" name="date_from" value="<?php echo e($date_from); ?>" class="field">
                </div>

                <div>
                    <label for="date_to" class="label">শেষ তারিখ</label>
                    <input id="date_to" type="date" name="date_to" value="<?php echo e($date_to); ?>" max="<?php echo e($today); ?>" class="field">
                </div>

                <div>
                    <label for="officer_id" class="label">অফিসার</label>
                    <select id="officer_id" name="officer_id" class="field">
                        <option value="0">সব অফিসার</option>
                        <?php foreach ($officers as $officer): ?>
                            <option value="<?php echo e($officer['id']); ?>" <?php echo $officer_id === (int) $officer['id'] ? 'selected' : ''; ?>>
                                <?php echo e($officer['full_name']); ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="flex gap-3">
                    <button type="submit" class="flex-1 inline-flex items-center justify-center rounded-2xl bg-sky-600 hover:bg-sky-700 text-white px-5 py-4 font-extrabold transition">
                        ফিল্টার
                    </button>
                    <a href="patient_report.php" class="inline-flex items-center justify-center rounded-2xl bg-slate-100 hover:bg-slate-200 text-slate-700 px-5 py-4 font-extrabold transition">
                        রিসেট
                    </a>
                </div>
            </div>
        </form>

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

            <div class="panel rounded-3xl p-6">
                <p class="text-sm font-semibold text-slate-500">কার্ডের অধীনে মোট রোগী/সদস্য</p>
                <h3 class="mt-3 text-4xl font-extrabold text-emerald-600"><?php echo e($total_patients); ?></h3>
            </div>

            <div class="panel rounded-3xl p-6">
                <p class="text-sm font-semibold text-slate-500">মোট ভিজিট</p>
                <h3 class="mt-3 text-4xl font-extrabold text-amber-600"><?php echo e($total_visits); ?></h3>
            </div>

            <div class="panel rounded-3xl p-6">
                <p class="text-sm font-semibold text-slate-500">ভিজিট করা রোগী</p>
                <h3 class="mt-3 text-4xl font-extrabold text-indigo-600"><?php echo e($visited_patients); ?></h3>
            </div>
        </section>

        <section class="panel rounded-[2rem] overflow-hidden mb-8">
            <div class="p-6 sm:p-7 border-b border-slate-100">
                <p class="text-sm font-bold text-sky-600">Officer Wise</p>
                <h3 class="mt-1 text-2xl font-extrabold text-slate-900">অফিসার অনুযায়ী রিপোর্ট</h3>
            </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">অফিসার</th>
                            <th class="px-6 py-4 font-extrabold">কার্ড</th>
                            <th class="px-6 py-4 font-extrabold">রোগী/সদস্য</th>
                            <th class="px-6 py-4 font-extrabold">ভিজিট করা রোগী</th>
                            <th class="px-6 py-4 font-extrabold">মোট ভিজিট</th>
                        </tr>
                    </thead>

                    <tbody class="divide-y divide-slate-100">
                        <?php foreach ($officer_stats as $row): ?>
                            <tr class="hover:bg-sky-50/60 transition">
                                <td class="px-6 py-5 font-extrabold text-slate-900"><?php echo e($row['full_name']); ?></td>
                                <td class="px-6 py-5 font-bold text-sky-700"><?php echo e($row['total_cards']); ?></td>
                                <td class="px-6 py-5 font-bold text-emerald-700"><?php echo e($row['total_patients']); ?></td>
                                <td class="px-6 py-5 font-bold text-indigo-700"><?php echo e($row['visited_patients']); ?></td>
                                <td class="px-6 py-5 font-bold text-amber-700"><?php echo e($row['total_visits']); ?></td>
                            </tr>
                        <?php endforeach; ?>

                        <?php if (count($officer_stats) === 0): ?>
                            <tr>
                                <td colspan="5" class="px-6 py-14 text-center">
                                    <h4 class="text-lg font-extrabold text-slate-700">কোনো রিপোর্ট পাওয়া যায়নি</h4>
                                </td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </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">Card Wise</p>
                    <h3 class="mt-1 text-2xl font-extrabold text-slate-900">কার্ড অনুযায়ী রোগী ও ভিজিট</h3>
                    <p class="mt-2 text-sm text-slate-500">সর্বশেষ ৩০০টি কার্ড দেখানো হচ্ছে।</p>
                </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($card_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">কার্ড</th>
                            <th class="px-6 py-4 font-extrabold">অফিসার</th>
                            <th class="px-6 py-4 font-extrabold">মোবাইল</th>
                            <th class="px-6 py-4 font-extrabold">রোগী/সদস্য</th>
                            <th class="px-6 py-4 font-extrabold">ভিজিট করা রোগী</th>
                            <th class="px-6 py-4 font-extrabold">মোট ভিজিট</th>
                            <th class="px-6 py-4 font-extrabold">শেষ ভিজিট</th>
                        </tr>
                    </thead>

                    <tbody class="divide-y divide-slate-100">
                        <?php foreach ($card_rows as $row): ?>
                            <tr class="hover:bg-sky-50/60 transition">
                                <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 text-slate-400">
                                        <?php echo !empty($row['reg_date']) ? e(date('d M, Y', strtotime($row['reg_date']))) : 'N/A'; ?>
                                    </p>
                                </td>
                                <td class="px-6 py-5 font-bold text-slate-700"><?php echo e($row['officer_name']); ?></td>
                                <td class="px-6 py-5 font-bold text-slate-700"><?php echo e($row['mobile']); ?></td>
                                <td class="px-6 py-5 font-bold text-emerald-700"><?php echo e($row['total_patients']); ?></td>
                                <td class="px-6 py-5 font-bold text-indigo-700"><?php echo e($row['visited_patients']); ?></td>
                                <td class="px-6 py-5 font-bold text-amber-700"><?php echo e($row['total_visits']); ?></td>
                                <td class="px-6 py-5 text-slate-500">
                                    <?php echo !empty($row['last_visit']) ? e(date('d M, Y | h:i A', strtotime($row['last_visit']))) : 'N/A'; ?>
                                </td>
                            </tr>
                        <?php endforeach; ?>

                        <?php if (count($card_rows) === 0): ?>
                            <tr>
                                <td colspan="7" class="px-6 py-14 text-center">
                                    <h4 class="text-lg font-extrabold text-slate-700">কোনো কার্ড পাওয়া যায়নি</h4>
                                </td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </section>

    </main>

</body>
</html>
