<?php
require_once __DIR__ . '/core/auth.php';
$user = require_login();
$pdo = pdo();
$agents = get_accessible_agents($pdo, $user);

$filters = [
    'date_from' => $_GET['date_from'] ?? date('Y-m-01'),
    'date_to' => $_GET['date_to'] ?? date('Y-m-d'),
    'agent_name' => trim($_GET['agent_name'] ?? ''),
    'status' => trim($_GET['status'] ?? ''),
];

$where = ['1=1'];
$params = [];
if ($filters['date_from']) {
    $where[] = 'DATE(vr.date_of_visit) >= :date_from';
    $params[':date_from'] = $filters['date_from'];
}
if ($filters['date_to']) {
    $where[] = 'DATE(vr.date_of_visit) <= :date_to';
    $params[':date_to'] = $filters['date_to'];
}
if ($filters['agent_name']) {
    $where[] = 'vr.agent_name = :agent_name';
    $params[':agent_name'] = $filters['agent_name'];
}
if ($filters['status']) {
    $where[] = 'vr.status = :status';
    $params[':status'] = $filters['status'];
}
if ($user['role'] !== 'admin') {
    $where[] = "vr.agent_name IN (
        SELECT agent_name FROM user_agent_assignments WHERE user_id = :uid
        UNION
        SELECT agent_name FROM agentsmrd WHERE rsm_name = :full_name
    )";
    $params[':uid'] = $user['id'];
    $params[':full_name'] = $user['full_name'];
}
$whereSql = implode(' AND ', $where);

$stmt = $pdo->prepare("SELECT vr.*, u.full_name
    FROM visit_reports vr
    LEFT JOIN users u ON u.id = vr.user_id
    WHERE {$whereSql}
    ORDER BY vr.date_of_visit DESC, vr.id DESC");
$stmt->execute($params);
$rows = $stmt->fetchAll();

$stmt = $pdo->prepare("SELECT DATE(vr.date_of_visit) report_day, COUNT(*) total_reports
    FROM visit_reports vr
    WHERE {$whereSql}
    GROUP BY DATE(vr.date_of_visit)
    ORDER BY report_day DESC
    LIMIT 15");
$stmt->execute($params);
$dailySummary = $stmt->fetchAll();

$stmt = $pdo->prepare("SELECT DATE_FORMAT(vr.date_of_visit, '%Y-%m') report_month, COUNT(*) total_reports
    FROM visit_reports vr
    WHERE {$whereSql}
    GROUP BY DATE_FORMAT(vr.date_of_visit, '%Y-%m')
    ORDER BY report_month DESC
    LIMIT 12");
$stmt->execute($params);
$monthlySummary = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Reports - <?= e(app_name()) ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="assets/css/app.css" rel="stylesheet">
</head>
<body>
<nav class="navbar navbar-expand-lg bg-white border-bottom sticky-top">
    <div class="container-fluid px-3">
        <a class="navbar-brand fw-bold" href="dashboard.php"><?= e(app_name()) ?></a>
        <div class="d-flex gap-2">
            <a href="report_wizard.php" class="btn btn-primary btn-sm">New Report</a>
            <a href="logout.php" class="btn btn-outline-danger btn-sm">Logout</a>
        </div>
    </div>
</nav>
<div class="container py-4">
    <div class="d-flex justify-content-between align-items-center mb-3 flex-wrap gap-2">
        <div>
            <h1 class="h3 mb-1">Reports</h1>
            <p class="text-muted mb-0">Daily log, monthly log and detailed report browsing.</p>
        </div>
    </div>

    <div class="card shadow-sm border-0 mb-4">
        <div class="card-body">
            <form class="row g-3">
                <div class="col-6 col-md-3">
                    <label class="form-label">From</label>
                    <input type="date" name="date_from" value="<?= e($filters['date_from']) ?>" class="form-control">
                </div>
                <div class="col-6 col-md-3">
                    <label class="form-label">To</label>
                    <input type="date" name="date_to" value="<?= e($filters['date_to']) ?>" class="form-control">
                </div>
                <div class="col-12 col-md-3">
                    <label class="form-label">DB Name</label>
                    <select name="agent_name" class="form-select">
                        <option value="">All</option>
                        <?php foreach ($agents as $agent): ?>
                            <option value="<?= e($agent) ?>" <?= $filters['agent_name'] === $agent ? 'selected' : '' ?>><?= e($agent) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-12 col-md-2">
                    <label class="form-label">Status</label>
                    <select name="status" class="form-select">
                        <option value="">All</option>
                        <option value="draft" <?= $filters['status'] === 'draft' ? 'selected' : '' ?>>Draft</option>
                        <option value="completed" <?= $filters['status'] === 'completed' ? 'selected' : '' ?>>Completed</option>
                    </select>
                </div>
                <div class="col-12 col-md-1 d-grid align-items-end">
                    <button class="btn btn-primary mt-md-4">Filter</button>
                </div>
            </form>
        </div>
    </div>

    <div class="row g-3 mb-4">
        <div class="col-12 col-lg-6">
            <div class="card shadow-sm border-0 h-100">
                <div class="card-header bg-white"><h2 class="h5 mb-0">Daily Log View</h2></div>
                <div class="table-responsive">
                    <table class="table table-sm align-middle mb-0">
                        <thead class="table-light"><tr><th>Date</th><th class="text-end">Reports</th></tr></thead>
                        <tbody>
                        <?php foreach ($dailySummary as $row): ?>
                            <tr><td><?= e($row['report_day']) ?></td><td class="text-end"><?= (int) $row['total_reports'] ?></td></tr>
                        <?php endforeach; ?>
                        <?php if (!$dailySummary): ?><tr><td colspan="2" class="text-center text-muted py-3">No daily data</td></tr><?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <div class="col-12 col-lg-6">
            <div class="card shadow-sm border-0 h-100">
                <div class="card-header bg-white"><h2 class="h5 mb-0">Monthly Log View</h2></div>
                <div class="table-responsive">
                    <table class="table table-sm align-middle mb-0">
                        <thead class="table-light"><tr><th>Month</th><th class="text-end">Reports</th></tr></thead>
                        <tbody>
                        <?php foreach ($monthlySummary as $row): ?>
                            <tr><td><?= e($row['report_month']) ?></td><td class="text-end"><?= (int) $row['total_reports'] ?></td></tr>
                        <?php endforeach; ?>
                        <?php if (!$monthlySummary): ?><tr><td colspan="2" class="text-center text-muted py-3">No monthly data</td></tr><?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="card shadow-sm border-0">
        <div class="card-header bg-white"><h2 class="h5 mb-0">Detailed View</h2></div>
        <div class="table-responsive">
            <table class="table table-hover align-middle mb-0">
                <thead class="table-light">
                    <tr>
                        <th>ID</th>
                        <th>Visit Date</th>
                        <th>DB Name</th>
                        <th>Type</th>
                        <th>Status</th>
                        <th>Prepared By</th>
                        <th>Started</th>
                        <th>Ended</th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                <?php foreach ($rows as $row): ?>
                    <tr>
                        <td>#<?= (int) $row['id'] ?></td>
                        <td><?= e($row['date_of_visit']) ?></td>
                        <td><?= e($row['agent_name']) ?></td>
                        <td><?= e($row['visited_type']) ?></td>
                        <td><span class="badge <?= $row['status'] === 'completed' ? 'text-bg-success' : 'text-bg-warning' ?>"><?= e(ucfirst($row['status'])) ?></span></td>
                        <td><?= e($row['full_name'] ?? '-') ?></td>
                        <td><?= e($row['started_at'] ?: '-') ?></td>
                        <td><?= e($row['ended_at'] ?: '-') ?></td>
                        <td class="text-end text-nowrap">
                            <a href="report_view.php?id=<?= (int) $row['id'] ?>" class="btn btn-sm btn-outline-secondary">View</a>
                            <a href="report_wizard.php?id=<?= (int) $row['id'] ?>" class="btn btn-sm btn-outline-primary">Edit</a>
                        </td>
                    </tr>
                <?php endforeach; ?>
                <?php if (!$rows): ?><tr><td colspan="9" class="text-center text-muted py-4">No reports found for the selected filter.</td></tr><?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>
