<?php
require_once 'auth.php';
ensureAdminAuth();

require_once '../config/database.php';

$database = new Database();
$db = $database->getConnection();

function tableExists(PDO $db, string $table): bool {
    try {
        $s = $db->prepare("SHOW TABLES LIKE ?");
        $s->execute([$table]);
        return (bool)$s->fetchColumn();
    } catch(Exception $e) {
        return false;
    }
}

function fetchAllSafe(PDO $db, string $sql, array $params = []): array {
    try {
        $s = $db->prepare($sql);
        $s->execute($params);
        return $s->fetchAll(PDO::FETCH_ASSOC);
    } catch(Exception $e) {
        return [];
    }
}

$start = $_GET['start_date'] ?? date('Y-m-01');
$end = $_GET['end_date'] ?? date('Y-m-d');
$status = $_GET['status'] ?? '';

$where = 'WHERE DATE(sr.requested_at) BETWEEN :start AND :end';
$params = [
    ':start' => $start,
    ':end' => $end
];

if ($status !== '') {
    $where .= ' AND sr.job_status_id = :status';
    $params[':status'] = (int)$status;
}

$jobStatuses = fetchAllSafe($db, "
    SELECT id, status_name 
    FROM job_statuses 
    ORDER BY id
");

$summary = fetchAllSafe($db, "
    SELECT 
        COUNT(*) total_requests, 
        SUM(CASE WHEN sr.job_status_id = 4 THEN 1 ELSE 0 END) completed_requests, 
        SUM(CASE WHEN sr.job_status_id = 5 THEN 1 ELSE 0 END) cancelled_requests, 
        COUNT(DISTINCT sr.customer_id) unique_customers 
    FROM service_requests sr 
    $where
", $params);

$summary = $summary[0] ?? [
    'total_requests' => 0,
    'completed_requests' => 0,
    'cancelled_requests' => 0,
    'unique_customers' => 0
];

$ticketHistory = fetchAllSafe($db, "
    SELECT 
        sr.request_number, 
        sr.full_name, 
        sr.phone_number, 
        COALESCE(b.name, sr.manual_brand, 'Unknown') brand, 
        COALESCE(a.name, sr.manual_appliance_type, 'Unknown') appliance, 
        COALESCE(js.status_name, 'No Status') status_name, 
        sr.requested_at, 
        sr.completed_date 
    FROM service_requests sr 
    LEFT JOIN brands b ON sr.brand_id = b.id 
    LEFT JOIN appliance_types a ON sr.appliance_type_id = a.id 
    LEFT JOIN job_statuses js ON sr.job_status_id = js.id 
    $where 
    ORDER BY sr.requested_at DESC 
    LIMIT 200
", $params);

$statusChanges = fetchAllSafe($db, "
    SELECT 
        sr.request_number, 
        sr.full_name, 
        COALESCE(js.status_name, 'No Status') status_name, 
        sr.updated_at, 
        sr.requested_at 
    FROM service_requests sr 
    LEFT JOIN job_statuses js ON sr.job_status_id = js.id 
    $where 
    ORDER BY COALESCE(sr.updated_at, sr.requested_at) DESC 
    LIMIT 100
", $params);

$staffActions = [];

if (tableExists($db, 'csr_request_locks')) {
    $staffActions = fetchAllSafe($db, "
        SELECT 
            csr_name staff_name, 
            CONCAT('Managed / locked ticket ID ', request_id) action, 
            updated_at created_at 
        FROM csr_request_locks 
        ORDER BY updated_at DESC 
        LIMIT 100
    ");
}

if (!$staffActions && tableExists($db, 'request_notes')) {
    $staffActions = fetchAllSafe($db, "
        SELECT 
            CONCAT('CSR #', csr_id) staff_name, 
            CONCAT('Added note to ticket ID ', request_id) action, 
            created_at 
        FROM request_notes 
        ORDER BY created_at DESC 
        LIMIT 100
    ");
}

$csrTimeline = [];

if (tableExists($db, 'csr_activity_logs')) {
    $csrTimeline = fetchAllSafe($db, "
        SELECT 
            activity, 
            created_at 
        FROM csr_activity_logs 
        ORDER BY created_at DESC 
        LIMIT 150
    ");
} elseif (tableExists($db, 'csr_activity')) {
    $csrTimeline = fetchAllSafe($db, "
        SELECT 
            CONCAT('CSR #', csr_id, ' ', action, IFNULL(CONCAT(' - ', details), '')) activity, 
            created_at 
        FROM csr_activity 
        ORDER BY created_at DESC 
        LIMIT 150
    ");
} else {
    $csrTimeline = fetchAllSafe($db, "
        SELECT 
            CONCAT('Ticket #', request_number, ' created / status: ', COALESCE(js.status_name, 'No Status')) activity, 
            sr.requested_at created_at 
        FROM service_requests sr 
        LEFT JOIN job_statuses js ON sr.job_status_id = js.id 
        ORDER BY sr.requested_at DESC 
        LIMIT 100
    ");
}

if (isset($_GET['export']) && $_GET['export'] === 'ticket_history') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="ticket_history_report.csv"');

    $out = fopen('php://output', 'w');
    fputcsv($out, ['Request #', 'Customer', 'Phone', 'Brand', 'Appliance', 'Status', 'Requested', 'Completed']);

    foreach ($ticketHistory as $r) {
        fputcsv($out, [
            $r['request_number'],
            $r['full_name'],
            $r['phone_number'],
            $r['brand'],
            $r['appliance'],
            $r['status_name'],
            $r['requested_at'],
            $r['completed_date']
        ]);
    }

    exit;
}

if (isset($_GET['export']) && $_GET['export'] === 'csr_activity') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="csr_activity_timeline.csv"');

    $out = fopen('php://output', 'w');
    fputcsv($out, ['Activity', 'Date']);

    foreach ($csrTimeline as $r) {
        fputcsv($out, [
            $r['activity'],
            $r['created_at']
        ]);
    }

    exit;
}

$queryParams = $_GET;
unset($queryParams['export']);

$ticketExportUrl = '?' . http_build_query(array_merge($queryParams, ['export' => 'ticket_history']));
$csrExportUrl = '?' . http_build_query(array_merge($queryParams, ['export' => 'csr_activity']));
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Reports - Admin</title>

    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Montserrat:wght@400;500;600;700;800;900&display=swap" rel="stylesheet">

    <style>
        :root {
            --admin-bg-1: #080240;
            --admin-bg-2: #093349;
            --admin-card-1: #17295e;
            --admin-card-2: #102448;
            --admin-border: rgba(96, 165, 250, 0.24);
            --admin-border-strong: rgba(56, 189, 248, 0.42);
            --admin-cyan: #17bdd4;
            --admin-blue: #1d9bf0;
            --admin-text: #f8fafc;
            --admin-muted: #aebbd4;
            --admin-soft: #d7e7ff;
            --admin-input: rgba(8, 20, 48, 0.72);
        }

        * {
            box-sizing: border-box;
        }

        body {
            background: linear-gradient(135deg, var(--admin-bg-1) 0%, var(--admin-bg-2) 100%) fixed !important;
            color: var(--admin-text) !important;
            font-family: 'Montserrat', sans-serif !important;
            min-height: 100vh;
            overflow-x: hidden;
        }

        .page-header,
        .report-card,
        .stat-card {
            background: linear-gradient(135deg, var(--admin-card-1), var(--admin-card-2)) !important;
            border: 1px solid rgba(56, 189, 248, 0.24) !important;
            border-radius: 18px !important;
            box-shadow:
                0 10px 24px rgba(0, 0, 0, 0.24),
                inset 0 0 0 1px rgba(255, 255, 255, 0.025) !important;
            color: #ffffff !important;
        }

        .page-header {
            padding: 20px;
            margin-bottom: 22px;
        }

        .page-header h2 {
            color: #ffffff !important;
            font-weight: 900;
        }

        .page-header h2 i,
        .section-title i {
            color: var(--admin-cyan) !important;
        }

        .page-header p,
        .text-muted,
        .text-white-50 {
            color: var(--admin-muted) !important;
        }

        .report-card {
            padding: 18px;
            margin-bottom: 22px;
        }

        .stat-card {
            padding: 18px;
            height: 100%;
            transition: 0.25s ease;
        }

        .stat-card:hover,
        .report-card:hover {
            border-color: rgba(56, 189, 248, 0.40) !important;
        }

        .stat-card:hover {
            transform: translateY(-3px);
        }

        .stat-icon-box {
            background: rgba(56, 189, 248, 0.14) !important;
            border: 1px solid rgba(56, 189, 248, 0.22);
            border-radius: 12px;
            width: 48px;
            height: 48px;
            display: flex;
            align-items: center;
            justify-content: center;
        }

        .stat-icon-box i {
            color: #dff7ff !important;
            font-size: 20px;
        }

        .stat-card h3 {
            font-weight: 900;
            color: #ffffff;
            margin-bottom: 0;
        }

        .section-title {
            font-weight: 900;
            margin-bottom: 14px;
            color: #ffffff;
        }

        .nav-pills {
            gap: 8px;
        }

        .nav-pills .nav-link {
            color: #b9dcff !important;
            border-radius: 14px;
            background: rgba(8, 20, 48, 0.42);
            border: 1px solid rgba(96, 165, 250, 0.20);
            font-weight: 800;
        }

        .nav-pills .nav-link:hover {
            color: #ffffff !important;
            background: rgba(56, 189, 248, 0.16);
        }

        .nav-pills .nav-link.active {
            background: linear-gradient(135deg, #1d9bf0 0%, #17bdd4 100%) !important;
            color: #ffffff !important;
            border-color: transparent;
        }

        .table-responsive {
            border-radius: 14px;
            overflow-x: auto;
            background: transparent !important;
        }

        .table {
            color: var(--admin-text) !important;
            margin-bottom: 0 !important;
        }

        .table thead th {
            font-weight: 900;
            color: #ffffff !important;
            white-space: nowrap;
            background: rgba(8, 20, 48, 0.58) !important;
            border-color: rgba(96, 165, 250, 0.22) !important;
            font-size: 13px;
        }

        .table td {
            vertical-align: middle;
            color: var(--admin-soft) !important;
            border-color: rgba(96, 165, 250, 0.20) !important;
            background: rgba(8, 20, 48, 0.18) !important;
        }

        .table tbody tr:hover td {
            background: rgba(56, 189, 248, 0.10) !important;
            color: #ffffff !important;
        }

        .table strong {
            color: #ffffff !important;
        }

        .timeline {
            position: relative;
            display: grid;
            gap: 12px;
        }

        .timeline-item {
            display: flex;
            gap: 12px;
            background: rgba(8, 20, 48, 0.38);
            border: 1px solid rgba(96, 165, 250, 0.18);
            border-radius: 14px;
            padding: 12px;
            color: var(--admin-soft);
        }

        .timeline-item:hover {
            background: rgba(56, 189, 248, 0.10);
            color: #ffffff;
        }

        .timeline-dot {
            width: 11px;
            height: 11px;
            border-radius: 50%;
            background: var(--admin-cyan);
            box-shadow: 0 0 16px rgba(23, 189, 212, 0.8);
            margin-top: 7px;
            flex: 0 0 auto;
        }

        .form-control,
        .form-select,
        textarea,
        input {
            background-color: var(--admin-input) !important;
            border: 1px solid rgba(96, 165, 250, 0.34) !important;
            color: #ffffff !important;
            border-radius: 10px !important;
            min-height: 42px;
        }

        .form-control::placeholder,
        input::placeholder,
        textarea::placeholder {
            color: #aebbd4 !important;
            opacity: 1;
        }

        .form-control:focus,
        .form-select:focus {
            border-color: rgba(56, 189, 248, 0.62) !important;
            box-shadow: 0 0 0 0.18rem rgba(56, 189, 248, 0.18) !important;
        }

        .form-select option {
            background: #102448;
            color: #ffffff;
        }

        .btn {
            font-weight: 800 !important;
            border-radius: 10px !important;
        }

        .btn-primary,
        .btn-info {
            background: linear-gradient(135deg, #1d9bf0 0%, #17bdd4 100%) !important;
            border: none !important;
            color: #ffffff !important;
        }

        .badge {
            padding: 6px 10px;
            border-radius: 999px;
            font-weight: 900;
        }

        .badge.bg-primary,
        .badge.bg-info {
            background: linear-gradient(135deg, #1d9bf0 0%, #17bdd4 100%) !important;
            color: #ffffff !important;
            border: none !important;
        }

        .badge.bg-secondary {
            background: rgba(148, 163, 184, 0.20) !important;
            border: 1px solid rgba(148, 163, 184, 0.34);
            color: #e2e8f0 !important;
        }

        .empty-state {
            text-align: center;
            color: var(--admin-muted);
            padding: 24px;
            font-weight: 700;
        }

        .sidebar-backdrop {
            display: none;
        }

            .main-content {
                margin-left: 0;
                padding: 16px;
            }

            .table {
                min-width: 850px;
            }
        }

        @media(max-width: 576px) {
            .main-content {
                padding: 12px;
            }

            .page-header h2 {
                font-size: 22px;
            }

            .timeline-item {
                display: block;
            }

            .timeline-dot {
                display: none;
            }

            .report-card {
                padding: 14px;
            }

            .nav-pills .nav-link {
                font-size: 13px;
                padding: 8px 10px;
            }
        }
    </style>

    <link rel="stylesheet" href="../assets/theme.css">
    <link rel="stylesheet" href="../assets/portal-dark.css">

    <style>
        .report-card label,
        .report-card .form-label,
        .main-content label,
        .main-content .form-label,
        form label,
        form .form-label {
            color: #ffffff !important;
            font-weight: 800 !important;
            opacity: 1 !important;
        }

        .report-card input[type="date"]::-webkit-calendar-picker-indicator {
            filter: invert(1);
        }
    </style>
    <link rel="stylesheet" href="../assets/ushs-global-theme.css">
</head>

<body>
    <?php require_once __DIR__ . '/admin_sidebar.php'; render_admin_sidebar(); ?>

    <main class="main-content">
        <div class="page-header d-flex justify-content-between align-items-center flex-wrap">
            <div>
                <h2 class="mb-1">
                    <i class="fas fa-file-alt me-2"></i>
                    Reports Center
                </h2>

                <p class="mb-0">
                    CSR timeline, ticket history, export logs, staff actions, and status changes.
                </p>
            </div>

            <div class="mt-2 mt-lg-0 d-flex gap-2 flex-wrap">
                <a class="btn btn-primary btn-sm" href="<?= htmlspecialchars($ticketExportUrl) ?>">
                    <i class="fas fa-file-csv me-1"></i>
                    Export Tickets
                </a>

                <a class="btn btn-primary btn-sm" href="<?= htmlspecialchars($csrExportUrl) ?>">
                    <i class="fas fa-download me-1"></i>
                    Export CSR Logs
                </a>
            </div>
        </div>

        <div class="report-card">
            <form method="GET" class="row g-3 align-items-end">
                <div class="col-md-3">
                    <label class="form-label">Start Date</label>
                    <input type="date" name="start_date" class="form-control" value="<?= htmlspecialchars($start) ?>">
                </div>

                <div class="col-md-3">
                    <label class="form-label">End Date</label>
                    <input type="date" name="end_date" class="form-control" value="<?= htmlspecialchars($end) ?>">
                </div>

                <div class="col-md-3">
                    <label class="form-label">Status</label>
                    <select name="status" class="form-select">
                        <option value="">All Statuses</option>
                        <?php foreach($jobStatuses as $js): ?>
                            <option value="<?= (int)$js['id'] ?>" <?= $status == $js['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($js['status_name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="col-md-3">
                    <button class="btn btn-primary w-100">
                        <i class="fas fa-search me-1"></i>
                        Generate
                    </button>
                </div>
            </form>
        </div>

        <div class="row g-3 mb-4">
            <div class="col-6 col-lg-3">
                <div class="stat-card">
                    <div class="text-muted">Total Requests</div>
                    <h3><?= (int)$summary['total_requests'] ?></h3>
                </div>
            </div>

            <div class="col-6 col-lg-3">
                <div class="stat-card">
                    <div class="text-muted">Completed</div>
                    <h3><?= (int)$summary['completed_requests'] ?></h3>
                </div>
            </div>

            <div class="col-6 col-lg-3">
                <div class="stat-card">
                    <div class="text-muted">Cancelled</div>
                    <h3><?= (int)$summary['cancelled_requests'] ?></h3>
                </div>
            </div>

            <div class="col-6 col-lg-3">
                <div class="stat-card">
                    <div class="text-muted">Unique Customers</div>
                    <h3><?= (int)$summary['unique_customers'] ?></h3>
                </div>
            </div>
        </div>

        <ul class="nav nav-pills mb-4 flex-wrap" id="reportTabs">
            <li class="nav-item"><a class="nav-link active" href="#csr-activity">CSR Activity Timeline</a></li>
            <li class="nav-item"><a class="nav-link" href="#ticket-history">Ticket History</a></li>
            <li class="nav-item"><a class="nav-link" href="#export-logs">Export Logs</a></li>
            <li class="nav-item"><a class="nav-link" href="#staff-actions">Staff Actions</a></li>
            <li class="nav-item"><a class="nav-link" href="#status-changes">Status Changes</a></li>
        </ul>

        <section id="csr-activity" class="report-card">
            <h4 class="section-title">
                <i class="fas fa-stream me-2"></i>
                CSR Activity Timeline
            </h4>

            <div class="timeline">
                <?php if(empty($csrTimeline)): ?>
                    <div class="empty-state">No CSR activity logs found.</div>
                <?php else: ?>
                    <?php foreach($csrTimeline as $r): ?>
                        <div class="timeline-item">
                            <span class="timeline-dot"></span>
                            <div>
                                <div><?= htmlspecialchars($r['activity'] ?? 'Activity recorded') ?></div>
                                <small class="text-muted">
                                    <?= !empty($r['created_at']) ? htmlspecialchars(date('M d, Y h:i A', strtotime($r['created_at']))) : '' ?>
                                </small>
                            </div>
                        </div>
                    <?php endforeach; ?>
                <?php endif; ?>
            </div>
        </section>

        <section id="ticket-history" class="report-card">
            <h4 class="section-title">
                <i class="fas fa-ticket-alt me-2"></i>
                Ticket History
            </h4>

            <div class="table-responsive">
                <table class="table table-hover align-middle">
                    <thead>
                        <tr>
                            <th>Request #</th>
                            <th>Customer</th>
                            <th>Phone</th>
                            <th>Brand</th>
                            <th>Appliance</th>
                            <th>Status</th>
                            <th>Requested</th>
                            <th>Completed</th>
                        </tr>
                    </thead>

                    <tbody>
                        <?php if(empty($ticketHistory)): ?>
                            <tr>
                                <td colspan="8" class="text-center text-muted py-4">No ticket history found for this filter.</td>
                            </tr>
                        <?php else: ?>
                            <?php foreach($ticketHistory as $r): ?>
                                <tr>
                                    <td><strong><?= htmlspecialchars($r['request_number']) ?></strong></td>
                                    <td><?= htmlspecialchars($r['full_name']) ?></td>
                                    <td><?= htmlspecialchars($r['phone_number']) ?></td>
                                    <td><?= htmlspecialchars($r['brand']) ?></td>
                                    <td><?= htmlspecialchars($r['appliance']) ?></td>
                                    <td><span class="badge bg-secondary"><?= htmlspecialchars($r['status_name']) ?></span></td>
                                    <td><?= !empty($r['requested_at']) ? htmlspecialchars(date('M d, Y h:i A', strtotime($r['requested_at']))) : '-' ?></td>
                                    <td><?= !empty($r['completed_date']) ? htmlspecialchars(date('M d, Y h:i A', strtotime($r['completed_date']))) : '-' ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </section>

        <section id="export-logs" class="report-card">
            <h4 class="section-title">
                <i class="fas fa-file-export me-2"></i>
                Export Logs
            </h4>

            <p class="text-muted">
                Use the export buttons below to download CSV files based on the selected date and status filters.
            </p>

            <div class="d-flex gap-2 flex-wrap">
                <a class="btn btn-primary" href="<?= htmlspecialchars($ticketExportUrl) ?>">
                    <i class="fas fa-file-csv me-1"></i>
                    Download Ticket History CSV
                </a>

                <a class="btn btn-primary" href="<?= htmlspecialchars($csrExportUrl) ?>">
                    <i class="fas fa-download me-1"></i>
                    Download CSR Activity CSV
                </a>
            </div>
        </section>

        <section id="staff-actions" class="report-card">
            <h4 class="section-title">
                <i class="fas fa-user-shield me-2"></i>
                Staff Actions
            </h4>

            <div class="table-responsive">
                <table class="table table-hover align-middle">
                    <thead>
                        <tr>
                            <th>Staff</th>
                            <th>Action</th>
                            <th>Date</th>
                        </tr>
                    </thead>

                    <tbody>
                        <?php if(empty($staffActions)): ?>
                            <tr>
                                <td colspan="3" class="text-center text-muted py-4">No staff action records found yet.</td>
                            </tr>
                        <?php else: ?>
                            <?php foreach($staffActions as $r): ?>
                                <tr>
                                    <td><strong><?= htmlspecialchars($r['staff_name'] ?? 'Staff') ?></strong></td>
                                    <td><?= htmlspecialchars($r['action'] ?? 'Action recorded') ?></td>
                                    <td><?= !empty($r['created_at']) ? htmlspecialchars(date('M d, Y h:i A', strtotime($r['created_at']))) : '' ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </section>

        <section id="status-changes" class="report-card">
            <h4 class="section-title">
                <i class="fas fa-exchange-alt me-2"></i>
                Status Changes
            </h4>

            <div class="table-responsive">
                <table class="table table-hover align-middle">
                    <thead>
                        <tr>
                            <th>Request #</th>
                            <th>Customer</th>
                            <th>Current Status</th>
                            <th>Last Updated / Requested</th>
                        </tr>
                    </thead>

                    <tbody>
                        <?php if(empty($statusChanges)): ?>
                            <tr>
                                <td colspan="4" class="text-center text-muted py-4">No status changes found for this filter.</td>
                            </tr>
                        <?php else: ?>
                            <?php foreach($statusChanges as $r): ?>
                                <tr>
                                    <td><strong><?= htmlspecialchars($r['request_number']) ?></strong></td>
                                    <td><?= htmlspecialchars($r['full_name']) ?></td>
                                    <td><span class="badge bg-info"><?= htmlspecialchars($r['status_name']) ?></span></td>
                                    <td><?= htmlspecialchars(date('M d, Y h:i A', strtotime($r['updated_at'] ?: $r['requested_at']))) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </section>
    </main>

    <script>
        const sidebar = document.getElementById('adminSidebar');
        const backdrop = document.getElementById('sidebarBackdrop');
        const toggle = document.getElementById('menuToggle');

        if (toggle) {
            toggle.addEventListener('click', () => {
                sidebar.classList.toggle('active');
                backdrop.classList.toggle('active');
            });

            backdrop.addEventListener('click', () => {
                sidebar.classList.remove('active');
                backdrop.classList.remove('active');
            });
        }

        document.querySelectorAll('#reportTabs a').forEach(a => {
            a.addEventListener('click', () => {
                document.querySelectorAll('#reportTabs a').forEach(x => x.classList.remove('active'));
                a.classList.add('active');
            });
        });
    </script>
</body>
</html>