<?php
// stats.php
require_once __DIR__ . '/../../src/includes/config.php';

if (!isset($_SESSION['gestionnaire_id'])) {
    header('Location: login.php');
    exit;
}

$role = $_SESSION['role'];
$service_id = $_SESSION['service_id'];

// 🔐 Services autorisés pour le filtre
$services_autorises = ($role === 'admin') ? [1, 2] : [$service_id];
$placeholders_services = str_repeat('?,', count($services_autorises) - 1) . '?';

$annee = $_GET['annee'] ?? date('Y');
$mois = $_GET['mois'] ?? '';
$trimestre = $_GET['trimestre'] ?? '';

// Construire la condition avec alias `d`
$where = "YEAR(d.date_creation) = ? AND d.service_cible_id IN ($placeholders_services)";
$params = array_merge([$annee], $services_autorises);

if ($mois) {
    $where .= " AND MONTH(d.date_creation) = ?";
    $params[] = $mois;
}
if ($trimestre) {
    $debut = (($trimestre - 1) * 3) + 1;
    $fin = $debut + 2;
    $where .= " AND MONTH(d.date_creation) BETWEEN ? AND ?";
    $params[] = $debut;
    $params[] = $fin;
}

try {
    // Total
    $stmt = $pdo->prepare("SELECT COUNT(*) as total FROM dossiers d WHERE $where");
    $stmt->execute($params);
    $total = $stmt->fetch()['total'];

    // Par type
    $stmt = $pdo->prepare("
        SELECT tt.libelle, COUNT(*) as count 
        FROM dossiers d 
        JOIN types_dossier tt ON d.type_id = tt.id 
        WHERE $where 
        GROUP BY tt.libelle
    ");
    $stmt->execute($params);
    $by_type = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

    // Par statut
    $stmt = $pdo->prepare("
        SELECT st.libelle, COUNT(*) as count 
        FROM dossiers d 
        JOIN statuts st ON d.statut_id = st.id 
        WHERE $where 
        GROUP BY st.libelle
    ");
    $stmt->execute($params);
    $by_status = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

    // Par mode
    $stmt = $pdo->prepare("
        SELECT mode_depot, COUNT(*) as count 
        FROM dossiers d 
        WHERE $where 
        GROUP BY mode_depot
    ");
    $stmt->execute($params);
    $by_mode = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

    // Par type de situation
    $stmt = $pdo->prepare("
        SELECT ts.libelle, COUNT(*) as count 
        FROM dossiers d 
        LEFT JOIN types_situation ts ON d.type_situation_id = ts.id 
        WHERE $where AND d.type_situation_id IS NOT NULL
        GROUP BY ts.libelle
    ");
    $stmt->execute($params);
    $by_situation = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

    // Par service cible
    $stmt = $pdo->prepare("
        SELECT s.nom_service, COUNT(*) as count 
        FROM dossiers d 
        JOIN services s ON d.service_cible_id = s.id 
        WHERE $where 
        GROUP BY s.nom_service
    ");
    $stmt->execute($params);
    $by_service = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

    // Évolution mensuelle
    $monthly_params = array_merge([$annee], $services_autorises);
    $stmt = $pdo->prepare("
        SELECT 
            MONTH(date_creation) as mois, 
            COUNT(*) as count 
        FROM dossiers 
        WHERE YEAR(date_creation) = ? AND service_cible_id IN ($placeholders_services)
        GROUP BY mois ORDER BY mois
    ");
    $stmt->execute($monthly_params);
    $monthly_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $months = ['Jan', 'Fév', 'Mar', 'Avr', 'Mai', 'Jun', 'Jul', 'Aoû', 'Sep', 'Oct', 'Nov', 'Déc'];
    $monthly_counts = array_fill(0, 12, 0);
    foreach ($monthly_data as $row) {
        $monthly_counts[$row['mois'] - 1] = $row['count'];
    }

    // Temps moyen de traitement (pour les dossiers clôturés)
    $temps_params = array_merge([$annee], $services_autorises);
    $stmt = $pdo->prepare("
        SELECT AVG(DATEDIFF(date_maj, date_creation)) as temps_moyen 
        FROM dossiers 
        WHERE statut_id IN (4,5) AND YEAR(date_creation) = ? AND service_cible_id IN ($placeholders_services)
    ");
    $stmt->execute($temps_params);
    $temps_moyen = $stmt->fetch()['temps_moyen'] ?? 0;

    // Statistiques par jour de la semaine
    $day_params = array_merge([$annee], $services_autorises);
    $stmt = $pdo->prepare("
        SELECT 
            DAYNAME(date_creation) as jour,
            COUNT(*) as count
        FROM dossiers 
        WHERE YEAR(date_creation) = ? AND service_cible_id IN ($placeholders_services)
        GROUP BY DAYOFWEEK(date_creation), jour
        ORDER BY DAYOFWEEK(date_creation)
    ");
    $stmt->execute($day_params);
    $by_day = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

} catch (Exception $e) {
    die("Erreur dans les requêtes statistiques : " . $e->getMessage());
}

// Calcul des taux
$total_clos = ($by_status['Clôturé – action entreprise'] ?? 0) + ($by_status['Clôturé – non retenu'] ?? 0);
$taux_resolution = $total > 0 ? round(($total_clos / $total) * 100, 1) : 0;
$taux_anonyme = $total > 0 ? round((($by_mode['anonyme'] ?? 0) / $total) * 100, 1) : 0;
?>

<!-- Inclure le header -->
<?php include 'admin_header.php'; ?>

<div class="container-fluid p-4">
    <!-- En-tête de page -->
    <div class="d-flex justify-content-between align-items-center mb-4">
        <div>
            <h1 class="h3 mb-1">📊 Tableau de bord statistique</h1>
            <nav aria-label="breadcrumb">
                <ol class="breadcrumb mb-0">
                    <li class="breadcrumb-item"><a href="dashboard.php">Tableau de bord</a></li>
                    <li class="breadcrumb-item active">Statistiques</li>
                </ol>
            </nav>
        </div>
        <div class="d-flex gap-2">
            <a href="rapport.php?annee=<?= $annee ?>" class="btn btn-primary btn-sm" target="_blank">
                <i class="bi bi-file-pdf me-1"></i>Exporter PDF
            </a>
            <a href="export.php?annee=<?= $annee ?>" class="btn btn-success btn-sm">
                <i class="bi bi-file-excel me-1"></i>Exporter Excel
            </a>
        </div>
    </div>

    <!-- Filtre par période -->
    <div class="card border-0 shadow-sm mb-4">
        <div class="card-body">
            <h5 class="card-title mb-3">Filtrer par période</h5>
            <form method="GET" class="row g-3 align-items-end">
                <div class="col-md-3">
                    <label class="form-label fw-semibold">Année</label>
                    <select name="annee" class="form-select" onchange="this.form.submit()">
                        <?php for ($y = 2023; $y <= date('Y'); $y++): ?>
                            <option value="<?= $y ?>" <?= $y == $annee ? 'selected' : '' ?>><?= $y ?></option>
                        <?php endfor; ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label fw-semibold">Mois</label>
                    <select name="mois" class="form-select" onchange="this.form.submit()">
                        <option value="">Tous les mois</option>
                        <?php for ($m = 1; $m <= 12; $m++): ?>
                            <option value="<?= $m ?>" <?= $m == $mois ? 'selected' : '' ?>>
                                <?= $months[$m - 1] ?>
                            </option>
                        <?php endfor; ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label fw-semibold">Trimestre</label>
                    <select name="trimestre" class="form-select" onchange="this.form.submit()">
                        <option value="">Tous les trimestres</option>
                        <option value="1" <?= $trimestre == '1' ? 'selected' : '' ?>>Q1 (Jan-Mar)</option>
                        <option value="2" <?= $trimestre == '2' ? 'selected' : '' ?>>Q2 (Avr-Jun)</option>
                        <option value="3" <?= $trimestre == '3' ? 'selected' : '' ?>>Q3 (Jul-Sep)</option>
                        <option value="4" <?= $trimestre == '4' ? 'selected' : '' ?>>Q4 (Oct-Déc)</option>
                    </select>
                </div>
                <div class="col-md-3">
                    <a href="stats.php" class="btn btn-outline-secondary w-100">Réinitialiser</a>
                </div>
            </form>
        </div>
    </div>

    <!-- Cartes de résumé principales -->
    <div class="row g-3 mb-4">
        <!-- Total dossiers -->
        <div class="col-xl-3 col-md-6">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-body">
                    <div class="d-flex align-items-center">
                        <div class="flex-grow-1">
                            <h6 class="card-title text-muted mb-2">Total dossiers</h6>
                            <h2 class="text-primary mb-0"><?= $total ?></h2>
                            <small class="text-muted">
                                <i class="bi bi-archive me-1"></i>Sur la période
                            </small>
                        </div>
                        <div class="flex-shrink-0">
                            <div class="bg-primary bg-opacity-10 text-primary rounded p-3">
                                <i class="bi bi-folder fs-4"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- En attente -->
        <div class="col-xl-3 col-md-6">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-body">
                    <div class="d-flex align-items-center">
                        <div class="flex-grow-1">
                            <h6 class="card-title text-muted mb-2">En attente</h6>
                            <h2 class="text-warning mb-0"><?= $by_status['Reçu'] ?? 0 ?></h2>
                            <small class="text-muted">
                                <i class="bi bi-clock me-1"></i>À traiter
                            </small>
                        </div>
                        <div class="flex-shrink-0">
                            <div class="bg-warning bg-opacity-10 text-warning rounded p-3">
                                <i class="bi bi-hourglass-split fs-4"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Clôturés -->
        <div class="col-xl-3 col-md-6">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-body">
                    <div class="d-flex align-items-center">
                        <div class="flex-grow-1">
                            <h6 class="card-title text-muted mb-2">Clôturés</h6>
                            <h2 class="text-success mb-0"><?= $total_clos ?></h2>
                            <small class="text-muted">
                                <i class="bi bi-check-circle me-1"></i>Résolus
                            </small>
                        </div>
                        <div class="flex-shrink-0">
                            <div class="bg-success bg-opacity-10 text-success rounded p-3">
                                <i class="bi bi-check-all fs-4"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Temps moyen -->
        <div class="col-xl-3 col-md-6">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-body">
                    <div class="d-flex align-items-center">
                        <div class="flex-grow-1">
                            <h6 class="card-title text-muted mb-2">Temps moyen</h6>
                            <h2 class="text-info mb-0"><?= round($temps_moyen) ?>j</h2>
                            <small class="text-muted">
                                <i class="bi bi-speedometer2 me-1"></i>Traitement
                            </small>
                        </div>
                        <div class="flex-shrink-0">
                            <div class="bg-info bg-opacity-10 text-info rounded p-3">
                                <i class="bi bi-clock-history fs-4"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Première ligne de graphiques -->
    <div class="row g-4 mb-4">
        <!-- Graphique évolution mensuelle -->
        <div class="col-xl-8">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-header bg-white border-bottom">
                    <div class="d-flex justify-content-between align-items-center">
                        <h5 class="card-title mb-0">Évolution des dépôts - <?= $annee ?></h5>
                        <div class="badge bg-primary"><?= $total ?> dossiers</div>
                    </div>
                </div>
                <div class="card-body">
                    <canvas id="chartEvolution" height="300"></canvas>
                </div>
            </div>
        </div>

        <!-- Répartition par type -->
        <div class="col-xl-4">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-header bg-white border-bottom">
                    <div class="d-flex justify-content-between align-items-center">
                        <h5 class="card-title mb-0">Répartition par type</h5>
                        <i class="bi bi-pie-chart text-muted"></i>
                    </div>
                </div>
                <div class="card-body">
                    <?php if (!empty($by_type)): ?>
                        <canvas id="chartType" height="300"></canvas>
                    <?php else: ?>
                        <div class="text-center text-muted py-5">
                            <i class="bi bi-pie-chart display-4 d-block mb-2"></i>
                            Aucune donnée disponible
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>
    </div>

    <!-- Deuxième ligne de graphiques -->
    <div class="row g-4 mb-4">
        <!-- Répartition par statut -->
        <div class="col-xl-4">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-header bg-white border-bottom">
                    <div class="d-flex justify-content-between align-items-center">
                        <h5 class="card-title mb-0">Statut des dossiers</h5>
                        <i class="bi bi-activity text-muted"></i>
                    </div>
                </div>
                <div class="card-body">
                    <?php if (!empty($by_status)): ?>
                        <canvas id="chartStatus" height="250"></canvas>
                    <?php else: ?>
                        <div class="text-center text-muted py-4">
                            <i class="bi bi-activity display-4 d-block mb-2"></i>
                            Aucune donnée disponible
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- Répartition par mode -->
        <div class="col-xl-4">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-header bg-white border-bottom">
                    <div class="d-flex justify-content-between align-items-center">
                        <h5 class="card-title mb-0">Mode de dépôt</h5>
                        <i class="bi bi-person-check text-muted"></i>
                    </div>
                </div>
                <div class="card-body">
                    <?php if (!empty($by_mode)): ?>
                        <canvas id="chartMode" height="250"></canvas>
                    <?php else: ?>
                        <div class="text-center text-muted py-4">
                            <i class="bi bi-person-check display-4 d-block mb-2"></i>
                            Aucune donnée disponible
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- Taux de résolution -->
        <div class="col-xl-4">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-header bg-white border-bottom">
                    <div class="d-flex justify-content-between align-items-center">
                        <h5 class="card-title mb-0">Indicateurs clés</h5>
                        <i class="bi bi-graph-up text-muted"></i>
                    </div>
                </div>
                <div class="card-body">
                    <div class="text-center mb-4">
                        <div class="display-4 fw-bold text-success mb-2"><?= $taux_resolution ?>%</div>
                        <p class="text-muted mb-0">Taux de résolution</p>
                    </div>
                    <div class="row text-center">
                        <div class="col-6">
                            <div class="h4 fw-bold text-primary"><?= $taux_anonyme ?>%</div>
                            <small class="text-muted">Dépôts anonymes</small>
                        </div>
                        <div class="col-6">
                            <div class="h4 fw-bold text-warning"><?= round($temps_moyen) ?>j</div>
                            <small class="text-muted">Délai moyen</small>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Tableau récapitulatif -->
    <div class="row g-4">
        <div class="col-12">
            <div class="card border-0 shadow-sm">
                <div class="card-header bg-white border-bottom">
                    <div class="d-flex justify-content-between align-items-center">
                        <h5 class="card-title mb-0">Détails par catégorie</h5>
                        <span class="badge bg-primary">Période : <?= $annee ?></span>
                    </div>
                </div>
                <div class="card-body">
                    <div class="row">
                        <div class="col-md-3">
                            <h6 class="fw-semibold mb-3">Types de dossier</h6>
                            <div class="list-group list-group-flush">
                                <?php if (!empty($by_type)): ?>
                                    <?php foreach ($by_type as $type => $count): ?>
                                        <div class="list-group-item d-flex justify-content-between align-items-center px-0">
                                            <span><?= htmlspecialchars($type) ?></span>
                                            <span class="badge bg-primary rounded-pill"><?= $count ?></span>
                                        </div>
                                    <?php endforeach; ?>
                                <?php else: ?>
                                    <div class="text-muted text-center py-2">Aucun type de dossier</div>
                                <?php endif; ?>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <h6 class="fw-semibold mb-3">Statuts</h6>
                            <div class="list-group list-group-flush">
                                <?php if (!empty($by_status)): ?>
                                    <?php foreach ($by_status as $statut => $count): ?>
                                        <div class="list-group-item d-flex justify-content-between align-items-center px-0">
                                            <span><?= htmlspecialchars($statut) ?></span>
                                            <span class="badge bg-secondary rounded-pill"><?= $count ?></span>
                                        </div>
                                    <?php endforeach; ?>
                                <?php else: ?>
                                    <div class="text-muted text-center py-2">Aucun statut</div>
                                <?php endif; ?>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <h6 class="fw-semibold mb-3">Modes de dépôt</h6>
                            <div class="list-group list-group-flush">
                                <?php if (!empty($by_mode)): ?>
                                    <?php foreach ($by_mode as $mode => $count): ?>
                                        <div class="list-group-item d-flex justify-content-between align-items-center px-0">
                                            <span><?= ucfirst(htmlspecialchars($mode)) ?></span>
                                            <span class="badge bg-info rounded-pill"><?= $count ?></span>
                                        </div>
                                    <?php endforeach; ?>
                                <?php else: ?>
                                    <div class="text-muted text-center py-2">Aucun mode de dépôt</div>
                                <?php endif; ?>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <h6 class="fw-semibold mb-3">Types de situation</h6>
                            <div class="list-group list-group-flush">
                                <?php if (!empty($by_situation)): ?>
                                    <?php foreach ($by_situation as $situation => $count): ?>
                                        <div class="list-group-item d-flex justify-content-between align-items-center px-0">
                                            <span><?= htmlspecialchars($situation) ?></span>
                                            <span class="badge bg-success rounded-pill"><?= $count ?></span>
                                        </div>
                                    <?php endforeach; ?>
                                <?php else: ?>
                                    <div class="text-muted text-center py-2">Aucune situation</div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Inclure le footer -->
<?php include 'admin_footer.php'; ?>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
    // Graphique d'évolution
    const ctxEvolution = document.getElementById('chartEvolution');
    if (ctxEvolution) {
        new Chart(ctxEvolution, {
            type: 'line',
            data: {
                labels: <?= json_encode($months) ?>,
                datasets: [{
                    label: 'Nombre de dépôts',
                    data: <?= json_encode($monthly_counts) ?>,
                    borderColor: '#F57C00',
                    backgroundColor: 'rgba(245, 124, 0, 0.1)',
                    borderWidth: 3,
                    tension: 0.4,
                    fill: true,
                    pointBackgroundColor: '#F57C00',
                    pointBorderColor: '#fff',
                    pointBorderWidth: 2,
                    pointRadius: 5
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        display: false
                    },
                    tooltip: {
                        backgroundColor: 'rgba(15, 23, 42, 0.9)',
                        titleFont: { size: 14 },
                        bodyFont: { size: 13 },
                        padding: 12
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        grid: {
                            color: 'rgba(15, 23, 42, 0.1)'
                        },
                        ticks: {
                            stepSize: 1
                        }
                    },
                    x: {
                        grid: {
                            color: 'rgba(15, 23, 42, 0.1)'
                        }
                    }
                }
            }
        });
    }

    // Graphique par type
    const ctxType = document.getElementById('chartType');
    if (ctxType) {
        new Chart(ctxType, {
            type: 'doughnut',
            data: {
                labels: <?= json_encode(array_keys($by_type)) ?>,
                datasets: [{
                    data: <?= json_encode(array_values($by_type)) ?>,
                    backgroundColor: [
                        '#2E7D32', '#F57C00', '#0D6EFD', '#6F42C1', '#20C997', '#FD7E14', '#6610F2', '#D63384'
                    ],
                    borderWidth: 2,
                    borderColor: '#fff'
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom',
                        labels: {
                            padding: 20,
                            usePointStyle: true
                        }
                    }
                },
                cutout: '60%'
            }
        });
    }

    // Graphique par statut
    const ctxStatus = document.getElementById('chartStatus');
    if (ctxStatus) {
        new Chart(ctxStatus, {
            type: 'pie',
            data: {
                labels: <?= json_encode(array_keys($by_status)) ?>,
                datasets: [{
                    data: <?= json_encode(array_values($by_status)) ?>,
                    backgroundColor: [
                        '#0D6EFD', '#F57C00', '#2E7D32', '#6C757D', '#20C997', '#FD7E14'
                    ],
                    borderWidth: 2,
                    borderColor: '#fff'
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom',
                        labels: {
                            padding: 15,
                            usePointStyle: true
                        }
                    }
                }
            }
        });
    }

    // Graphique par mode
    const ctxMode = document.getElementById('chartMode');
    if (ctxMode) {
        new Chart(ctxMode, {
            type: 'bar',
            data: {
                labels: <?= json_encode(array_keys($by_mode)) ?>,
                datasets: [{
                    label: 'Nombre de dossiers',
                    data: <?= json_encode(array_values($by_mode)) ?>,
                    backgroundColor: [
                        '#2E7D32', '#F57C00'
                    ],
                    borderWidth: 0,
                    borderRadius: 8,
                    barPercentage: 0.6
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        display: false
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        grid: {
                            color: 'rgba(15, 23, 42, 0.1)'
                        },
                        ticks: {
                            stepSize: 1
                        }
                    },
                    x: {
                        grid: {
                            display: false
                        }
                    }
                }
            }
        });
    }
</script>