<?php

declare(strict_types=1);

require_once __DIR__ . '/../../includes/bootstrap.php';

$config = require __DIR__ . '/../../config/config.php';

auth_start_session();
auth_require_role(['admin']);

$user = auth_user();
$title = 'Reports';

$from = trim((string)($_GET['from'] ?? ''));
$to = trim((string)($_GET['to'] ?? ''));

$kpi = [
    'tasks_total' => 0,
    'tasks_open' => 0,
    'tasks_completed' => 0,
    'payments_pending' => 0.0,
    'payments_received' => 0.0,
    'complaints_open' => 0,
    'complaints_closed' => 0,
    'staff_available' => 0,
    'staff_total' => 0,
];

$topServices = [];
$recentTasks = [];
$taskChecklists = [];
$error = '';

$hasRange = ($from !== '' && $to !== '');

try {
    $pdo = Database::pdo();

    // Tasks KPI
    if ($hasRange) {
        $stmt = $pdo->prepare(
            "SELECT
                COUNT(*) AS tasks_total,
                SUM(CASE WHEN status IN ('assigned','accepted','started','rescheduled') THEN 1 ELSE 0 END) AS tasks_open,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS tasks_completed
             FROM tasks
             WHERE scheduled_date BETWEEN :from AND :to"
        );
        $stmt->execute(['from' => $from, 'to' => $to]);
        $row = $stmt->fetch();
    } else {
        $row = $pdo->query(
            "SELECT
                COUNT(*) AS tasks_total,
                SUM(CASE WHEN status IN ('assigned','accepted','started','rescheduled') THEN 1 ELSE 0 END) AS tasks_open,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS tasks_completed
             FROM tasks"
        )->fetch();
    }

    if ($row) {
        $kpi['tasks_total'] = (int)($row['tasks_total'] ?? 0);
        $kpi['tasks_open'] = (int)($row['tasks_open'] ?? 0);
        $kpi['tasks_completed'] = (int)($row['tasks_completed'] ?? 0);
    }

    // Payments KPI
    if ($hasRange) {
        $stmt = $pdo->prepare(
            "SELECT
                SUM(CASE WHEN payment_status IN ('pending','processing') THEN amount ELSE 0 END) AS payments_pending,
                SUM(CASE WHEN payment_status = 'completed' THEN amount ELSE 0 END) AS payments_received
             FROM payments
             WHERE due_date BETWEEN :from AND :to"
        );
        $stmt->execute(['from' => $from, 'to' => $to]);
        $row = $stmt->fetch();
    } else {
        $row = $pdo->query(
            "SELECT
                SUM(CASE WHEN payment_status IN ('pending','processing') THEN amount ELSE 0 END) AS payments_pending,
                SUM(CASE WHEN payment_status = 'completed' THEN amount ELSE 0 END) AS payments_received
             FROM payments"
        )->fetch();
    }

    if ($row) {
        $kpi['payments_pending'] = (float)($row['payments_pending'] ?? 0);
        $kpi['payments_received'] = (float)($row['payments_received'] ?? 0);
    }

    // Complaints KPI
    if ($hasRange) {
        $stmt = $pdo->prepare(
            "SELECT
                SUM(CASE WHEN status IN ('pending','in_review') THEN 1 ELSE 0 END) AS complaints_open,
                SUM(CASE WHEN status IN ('resolved','closed') THEN 1 ELSE 0 END) AS complaints_closed
             FROM complaints
             WHERE DATE(created_at) BETWEEN :from AND :to"
        );
        $stmt->execute(['from' => $from, 'to' => $to]);
        $row = $stmt->fetch();
    } else {
        $row = $pdo->query(
            "SELECT
                SUM(CASE WHEN status IN ('pending','in_review') THEN 1 ELSE 0 END) AS complaints_open,
                SUM(CASE WHEN status IN ('resolved','closed') THEN 1 ELSE 0 END) AS complaints_closed
             FROM complaints"
        )->fetch();
    }

    if ($row) {
        $kpi['complaints_open'] = (int)($row['complaints_open'] ?? 0);
        $kpi['complaints_closed'] = (int)($row['complaints_closed'] ?? 0);
    }

    // Staff availability KPI
    $row = $pdo->query(
        "SELECT
            COUNT(*) AS staff_total,
            SUM(CASE WHEN sd.is_available = 1 THEN 1 ELSE 0 END) AS staff_available
         FROM users u
         JOIN staff_details sd ON sd.user_id = u.user_id
         WHERE u.user_type = 'staff' AND u.is_active = 1"
    )->fetch();

    if ($row) {
        $kpi['staff_total'] = (int)($row['staff_total'] ?? 0);
        $kpi['staff_available'] = (int)($row['staff_available'] ?? 0);
    }

    // Top services (by requests)
    if ($hasRange) {
        $stmt = $pdo->prepare(
            "SELECT s.service_name, COUNT(*) AS cnt
             FROM service_requests sr
             JOIN services s ON sr.service_id = s.service_id
             WHERE DATE(sr.created_at) BETWEEN :from AND :to
             GROUP BY s.service_name
             ORDER BY cnt DESC
             LIMIT 8"
        );
        $stmt->execute(['from' => $from, 'to' => $to]);
        $topServices = $stmt->fetchAll();
    } else {
        $topServices = $pdo->query(
            "SELECT s.service_name, COUNT(*) AS cnt
             FROM service_requests sr
             JOIN services s ON sr.service_id = s.service_id
             GROUP BY s.service_name
             ORDER BY cnt DESC
             LIMIT 8"
        )->fetchAll();
    }

    // Recent tasks (from view if available)
    try {
        if ($hasRange) {
            $stmt = $pdo->prepare(
                "SELECT task_id, scheduled_date, scheduled_time, task_status, staff_name, customer_name, service_name
                 FROM admin_all_tasks
                 WHERE scheduled_date BETWEEN :from AND :to
                 ORDER BY scheduled_date DESC, scheduled_time DESC
                 LIMIT 10"
            );
            $stmt->execute(['from' => $from, 'to' => $to]);
            $recentTasks = $stmt->fetchAll();
        } else {
            $recentTasks = $pdo->query(
                "SELECT task_id, scheduled_date, scheduled_time, task_status, staff_name, customer_name, service_name
                 FROM admin_all_tasks
                 ORDER BY scheduled_date DESC, scheduled_time DESC
                 LIMIT 10"
            )->fetchAll();
        }
    } catch (Throwable $e) {
        $recentTasks = [];
    }

    if ($recentTasks) {
        try {
            TaskChecklistService::ensureTable($pdo);
            $ids = [];
            foreach ($recentTasks as $rt) {
                $tid = (int)($rt['task_id'] ?? 0);
                if ($tid > 0) {
                    $ids[] = $tid;
                }
            }
            $ids = array_values(array_unique($ids));

            if ($ids) {
                $placeholders = implode(',', array_fill(0, count($ids), '?'));
                $stmtChk = $pdo->prepare(
                    'SELECT task_id, fumigation_completed, bait_box_checked, glue_box_checked, efk_checked, ilt_checked, others_checked, others_text
                     FROM task_checklists
                     WHERE task_id IN (' . $placeholders . ')'
                );
                $stmtChk->execute($ids);
                foreach ($stmtChk->fetchAll() as $c) {
                    $taskChecklists[(int)$c['task_id']] = $c;
                }
            }
        } catch (Throwable $e) {
            $taskChecklists = [];
        }
    }
} catch (Throwable $e) {
    $error = $e->getMessage();
}

$nav = [
    ['label' => 'Overview', 'href' => '/pest/public/admin/index.php', 'active' => false],
    ['label' => 'Customers', 'href' => '/pest/public/admin/customers.php', 'active' => false],
    ['label' => 'Staff', 'href' => '/pest/public/admin/staff.php', 'active' => false],
    ['label' => 'Staff Locations', 'href' => '/pest/public/admin/staff_locations.php', 'active' => false],
    ['label' => 'Services', 'href' => '/pest/public/admin/services.php', 'active' => false],
    ['label' => 'Task Assignment', 'href' => '/pest/public/admin/task_assignment.php', 'active' => false],
    ['label' => 'Payments', 'href' => '/pest/public/admin/payments.php', 'active' => false],
    ['label' => 'Complaints', 'href' => '/pest/public/admin/complaints.php', 'active' => false],
    ['label' => 'Reports', 'href' => '/pest/public/admin/reports.php', 'active' => true],
];

ob_start();
?>
<?php if ($error !== ''): ?>
    <div class="rounded-xl border border-red-200 bg-red-50 p-4 text-sm text-red-700">
        <?= e($error) ?>
    </div>
<?php endif; ?>

<div class="rounded-xl border bg-white p-6">
    <div class="flex flex-col gap-4 sm:flex-row sm:items-center sm:justify-between">
        <div>
            <h1 class="text-xl font-semibold">Reports</h1>
            <p class="mt-1 text-sm text-slate-600">Quick analytics for tasks, payments, complaints, and staff availability.</p>
        </div>
        <div class="flex flex-wrap items-end justify-end gap-2">
            <a class="rounded-lg border px-4 py-2 text-sm hover:bg-slate-50" href="/pest/public/admin/customer_reports_upload.php">Upload Customer Reports</a>
            <form class="flex flex-wrap items-end gap-2" method="get" action="">
            <div>
                <label class="block text-xs font-medium text-slate-600">From</label>
                <input class="mt-1 rounded-lg border px-3 py-2 text-sm" type="date" name="from" value="<?= e($from) ?>">
            </div>
            <div>
                <label class="block text-xs font-medium text-slate-600">To</label>
                <input class="mt-1 rounded-lg border px-3 py-2 text-sm" type="date" name="to" value="<?= e($to) ?>">
            </div>
            <button class="rounded-lg border px-4 py-2 text-sm hover:bg-slate-50" type="submit">Apply</button>
            <a class="rounded-lg border px-4 py-2 text-sm hover:bg-slate-50" href="/pest/public/admin/reports.php">Reset</a>
            </form>
        </div>
    </div>

    <div class="mt-6 grid grid-cols-1 gap-4 md:grid-cols-2 xl:grid-cols-4">
        <div class="rounded-xl border bg-slate-50 p-4">
            <div class="text-xs font-medium text-slate-600">Tasks (total)</div>
            <div class="mt-1 text-2xl font-semibold"><?= e((string)$kpi['tasks_total']) ?></div>
            <div class="mt-2 text-xs text-slate-600">Open: <span class="font-medium"><?= e((string)$kpi['tasks_open']) ?></span> | Completed: <span class="font-medium"><?= e((string)$kpi['tasks_completed']) ?></span></div>
        </div>
        <div class="rounded-xl border bg-slate-50 p-4">
            <div class="text-xs font-medium text-slate-600">Payments (pending)</div>
            <div class="mt-1 text-2xl font-semibold"><?= e(number_format((float)$kpi['payments_pending'], 2)) ?></div>
            <div class="mt-2 text-xs text-slate-600">Received: <span class="font-medium"><?= e(number_format((float)$kpi['payments_received'], 2)) ?></span></div>
        </div>
        <div class="rounded-xl border bg-slate-50 p-4">
            <div class="text-xs font-medium text-slate-600">Complaints (open)</div>
            <div class="mt-1 text-2xl font-semibold"><?= e((string)$kpi['complaints_open']) ?></div>
            <div class="mt-2 text-xs text-slate-600">Closed: <span class="font-medium"><?= e((string)$kpi['complaints_closed']) ?></span></div>
        </div>
        <div class="rounded-xl border bg-slate-50 p-4">
            <div class="text-xs font-medium text-slate-600">Staff Available</div>
            <div class="mt-1 text-2xl font-semibold"><?= e((string)$kpi['staff_available']) ?> / <?= e((string)$kpi['staff_total']) ?></div>
            <div class="mt-2 text-xs text-slate-600">Active staff availability</div>
        </div>
    </div>

    <div class="mt-6 grid grid-cols-1 gap-6 lg:grid-cols-2">
        <div class="rounded-xl border bg-white p-4">
            <div class="flex items-center justify-between">
                <div class="text-sm font-semibold">Top Services (by requests)</div>
                <a class="text-xs text-slate-600 hover:underline" href="/pest/public/admin/services.php">Manage Services</a>
            </div>
            <div class="mt-3 overflow-x-auto">
                <table class="min-w-full text-sm">
                    <thead class="text-left text-slate-600">
                        <tr class="border-b">
                            <th class="py-2 pr-4">Service</th>
                            <th class="py-2">Requests</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!$topServices): ?>
                            <tr class="border-b"><td class="py-3 text-slate-500" colspan="2">No data.</td></tr>
                        <?php else: ?>
                            <?php foreach ($topServices as $s): ?>
                                <tr class="border-b last:border-0">
                                    <td class="py-3 pr-4 font-medium"><?= e((string)$s['service_name']) ?></td>
                                    <td class="py-3"><?= e((string)$s['cnt']) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <div class="rounded-xl border bg-white p-4">
            <div class="flex items-center justify-between">
                <div class="text-sm font-semibold">Recent Tasks</div>
                <a class="text-xs text-slate-600 hover:underline" href="/pest/public/admin/task_assignment.php">Task Assignment</a>
            </div>
            <div class="mt-3 overflow-x-auto">
                <table class="min-w-full text-sm">
                    <thead class="text-left text-slate-600">
                        <tr class="border-b">
                            <th class="py-2 pr-4">Task</th>
                            <th class="py-2 pr-4">Schedule</th>
                            <th class="py-2 pr-4">Staff</th>
                            <th class="py-2 pr-4">Checklist</th>
                            <th class="py-2">Status</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!$recentTasks): ?>
                            <tr class="border-b"><td class="py-3 text-slate-500" colspan="5">No data.</td></tr>
                        <?php else: ?>
                            <?php foreach ($recentTasks as $t): ?>
                                <?php
                                    $tid = (int)($t['task_id'] ?? 0);
                                    $c = $tid > 0 ? ($taskChecklists[$tid] ?? null) : null;
                                    $checks = [];
                                    if ($c) {
                                        if (!empty($c['fumigation_completed'])) $checks[] = 'Fumigation';
                                        if (!empty($c['bait_box_checked'])) $checks[] = 'Bait';
                                        if (!empty($c['glue_box_checked'])) $checks[] = 'Glue';
                                        if (!empty($c['efk_checked'])) $checks[] = 'EFK';
                                        if (!empty($c['ilt_checked'])) $checks[] = 'ILT';
                                        if (!empty($c['others_checked'])) $checks[] = 'Other';
                                    }
                                    $otherText = $c ? trim((string)($c['others_text'] ?? '')) : '';
                                    $checkText = $checks ? implode(', ', $checks) : '-';
                                ?>
                                <tr class="border-b last:border-0">
                                    <td class="py-3 pr-4 font-medium">#<?= e((string)$t['task_id']) ?></td>
                                    <td class="py-3 pr-4"><?= e((string)$t['scheduled_date']) ?> <?= e((string)$t['scheduled_time']) ?></td>
                                    <td class="py-3 pr-4"><?= e((string)($t['staff_name'] ?? '')) ?></td>
                                    <td class="py-3 pr-4">
                                        <div class="text-xs text-slate-700"><?= e($checkText) ?></div>
                                        <?php if ($otherText !== ''): ?>
                                            <div class="mt-0.5 text-[11px] text-slate-500 truncate"><?= e($otherText) ?></div>
                                        <?php endif; ?>
                                        <?php if ($tid > 0): ?>
                                            <a class="mt-1 inline-block text-[11px] text-blue-700 hover:underline" href="/pest/public/admin/task_details.php?id=<?= e((string)$tid) ?>">View</a>
                                        <?php endif; ?>
                                    </td>
                                    <td class="py-3"><?= e((string)$t['task_status']) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
<?php
$content = ob_get_clean();

require __DIR__ . '/../../includes/dashboard_layout.php';
