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

// Vérifier la connexion
if (!isset($_SESSION['gestionnaire_id'])) {
    header('Location: login.php');
    exit;
}

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

// 🔐 Services autorisés
$services_autorises = ($role === 'admin') ? [1, 2] : [$service_id];

// 🔁 Éviter l'injection SQL
$placeholders = str_repeat('?,', count($services_autorises) - 1) . '?';

// 🔧 PAGINATION - Paramètres
$limit = 10; // Nombre de dossiers par page
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
if ($page < 1) $page = 1;
$offset = ($page - 1) * $limit;

// Récupérer le nombre total de dossiers (pour la pagination)
$stmt_count = $pdo->prepare("
    SELECT COUNT(*) as total 
    FROM dossiers d 
    WHERE d.service_cible_id IN ($placeholders)
");
$stmt_count->execute($services_autorises);
$total_dossiers = $stmt_count->fetch(PDO::FETCH_ASSOC)['total'];
$total_pages = ceil($total_dossiers / $limit);

// Ajuster la page si elle dépasse le nombre total
if ($page > $total_pages && $total_pages > 0) {
    $page = $total_pages;
    $offset = ($page - 1) * $limit;
}

// Récupérer TOUS les dossiers pour les statistiques (non paginés)
$stmt_stats = $pdo->prepare("
    SELECT 
        d.id, d.numero_dossier, d.titre, d.mode_depot,
        tt.libelle AS type_libelle,
        ts.libelle AS situation_libelle,
        st.libelle AS statut_libelle,
        d.date_creation, d.date_maj,
        s.nom_service AS service_cible_nom
    FROM dossiers d
    JOIN types_dossier tt ON d.type_id = tt.id
    LEFT JOIN types_situation ts ON d.type_situation_id = ts.id
    JOIN statuts st ON d.statut_id = st.id
    LEFT JOIN services s ON d.service_cible_id = s.id
    WHERE d.service_cible_id IN ($placeholders)
    ORDER BY d.date_creation DESC
");
$stmt_stats->execute($services_autorises);
$dossiers_stats = $stmt_stats->fetchAll(PDO::FETCH_ASSOC);

// 🔧 CORRECTION : Requête compatible avec ONLY_FULL_GROUP_BY
$sql = "
    SELECT 
        d.id, 
        d.numero_dossier, 
        d.titre, 
        d.mode_depot,
        tt.libelle AS type_libelle,
        ts.libelle AS situation_libelle,
        st.libelle AS statut_libelle,
        d.date_creation, 
        d.date_maj,
        s.nom_service AS service_cible_nom,
        -- 🔍 Sous-requête pour compter les pièces jointes
        (SELECT COUNT(*) FROM pieces_jointes pj WHERE pj.dossier_id = d.id) AS nb_pieces_jointes
    FROM dossiers d
    JOIN types_dossier tt ON d.type_id = tt.id
    LEFT JOIN types_situation ts ON d.type_situation_id = ts.id
    JOIN statuts st ON d.statut_id = st.id
    LEFT JOIN services s ON d.service_cible_id = s.id
    WHERE d.service_cible_id IN ($placeholders)
    ORDER BY d.date_creation DESC
    LIMIT ? OFFSET ?
";

$stmt = $pdo->prepare($sql);

// 🔧 Binder les paramètres avec leurs types
$param_index = 1;
foreach ($services_autorises as $service_id) {
    $stmt->bindValue($param_index, $service_id, PDO::PARAM_INT);
    $param_index++;
}
$stmt->bindValue($param_index++, $limit, PDO::PARAM_INT);
$stmt->bindValue($param_index, $offset, PDO::PARAM_INT);

$stmt->execute();
$dossiers = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Calculer les stats sur TOUS les dossiers (non paginés)
$total = count($dossiers_stats);
$total_attente = 0;
$total_en_cours = 0;
$total_clotures = 0;
$total_non_retenu = 0;

foreach ($dossiers_stats as $d) {
    switch ($d['statut_libelle']) {
        case 'Reçu':
            $total_attente++;
            break;
        case 'En cours d\'analyse':
            $total_en_cours++;
            break;
        case 'Clôturé – action entreprise':
            $total_clotures++;
            break;
        case 'Clôturé – non retenu':
            $total_non_retenu++;
            break;
    }
}

// Calculer les pourcentages
$pourcentage_attente = $total > 0 ? round(($total_attente / $total) * 100, 1) : 0;
$pourcentage_en_cours = $total > 0 ? round(($total_en_cours / $total) * 100, 1) : 0;
$pourcentage_clotures = $total > 0 ? round(($total_clotures / $total) * 100, 1) : 0;
$pourcentage_non_retenu = $total > 0 ? round(($total_non_retenu / $total) * 100, 1) : 0;
$total_clotures_combined = $total_clotures + $total_non_retenu;
$pourcentage_clotures_combined = $pourcentage_clotures + $pourcentage_non_retenu;
?>

<!-- Inclure le header admin -->
<?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</h1>
            <nav aria-label="breadcrumb">
                <ol class="breadcrumb mb-0">
                    <li class="breadcrumb-item"><a href="dashboard.php">Accueil</a></li>
                    <li class="breadcrumb-item active">Dashboard</li>
                </ol>
            </nav>
        </div>
        <div class="d-flex gap-2">
            <a href="stats.php" class="btn btn-outline-primary btn-sm">
                <i class="bi bi-bar-chart me-1"></i>Statistiques
            </a>
            <a href="logout.php" class="btn btn-outline-secondary btn-sm">
                <i class="bi bi-box-arrow-right me-1"></i>Déconnexion
            </a>
        </div>
    </div>

    <!-- Message de bienvenue -->
    <div class="alert alert-info mb-4">
        <div class="d-flex align-items-center">
            <div class="flex-shrink-0">
                <i class="bi bi-info-circle-fill fs-5"></i>
            </div>
            <div class="flex-grow-1 ms-3">
                <h6 class="alert-heading mb-1">Bonjour, <?= htmlspecialchars($_SESSION['nom_complet']) ?></h6>
                <p class="mb-0">Voici un aperçu de vos dossiers en cours.</p>
            </div>
        </div>
    </div>

    <!-- Cartes de statistiques -->
    <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>Tous les dossiers
                            </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"><?= $total_attente ?></h2>
                            <small class="text-muted">
                                <i class="bi bi-clock me-1"></i><?= $pourcentage_attente ?>% du total
                            </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>

        <!-- En cours -->
        <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 cours</h6>
                            <h2 class="text-info mb-0"><?= $total_en_cours ?></h2>
                            <small class="text-muted">
                                <i class="bi bi-gear me-1"></i><?= $pourcentage_en_cours ?>% du total
                            </small>
                        </div>
                        <div class="flex-shrink-0">
                            <div class="bg-info bg-opacity-10 text-info rounded p-3">
                                <i class="bi bi-arrow-repeat 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_clotures_combined ?></h2>
                            <small class="text-muted">
                                <i class="bi bi-check-circle me-1"></i><?= $pourcentage_clotures_combined ?>% du total
                            </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>
    </div>

    <!-- Tableau des dossiers -->
    <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">Dossiers récents</h5>
                <span class="badge bg-primary"><?= count($dossiers) ?> dossiers</span>
            </div>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-hover mb-0">
                    <thead class="table-light">
                        <tr>
                            <th>N° Dossier</th>
                            <th>Type</th>
                            <th>Catégorie</th>
                            <th>Titre</th>
                            <th>Mode</th>
                            <th>Statut</th>
                            <th>Pièces</th>
                            <th>Date</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (count($dossiers) > 0): ?>
                            <?php foreach ($dossiers as $d): ?>
                                <tr>
                                    <td class="fw-bold"><?= htmlspecialchars($d['numero_dossier']) ?></td>
                                    <td>
                                        <span class="badge bg-info"><?= htmlspecialchars($d['type_libelle']) ?></span>
                                    </td>
                                    <td>
                                        <?php if (!empty($d['situation_libelle'])): ?>
                                            <small class="text-muted"><?= htmlspecialchars($d['situation_libelle']) ?></small>
                                        <?php else: ?>
                                            <span class="text-muted">-</span>
                                        <?php endif; ?>
                                    </td>
                                    <td class="text-truncate" style="max-width: 200px;" title="<?= htmlspecialchars($d['titre']) ?>">
                                        <?= htmlspecialchars($d['titre']) ?>
                                    </td>
                                    <td>
                                        <span class="badge bg-<?= $d['mode_depot'] === 'anonyme' ? 'secondary' : 'success' ?>">
                                            <?= ucfirst($d['mode_depot']) ?>
                                        </span>
                                    </td>
                                    <td>
                                        <span class="badge bg-<?= 
                                            $d['statut_libelle'] === 'Reçu' ? 'info' :
                                            ($d['statut_libelle'] === 'En cours d\'analyse' ? 'warning' :
                                            ($d['statut_libelle'] === 'Clôturé – action entreprise' ? 'success' :
                                            ($d['statut_libelle'] === 'Clôturé – non retenu' ? 'dark' : 'secondary')))
                                        ?>">
                                            <?= htmlspecialchars($d['statut_libelle']) ?>
                                        </span>
                                    </td>
                                    <td>
                                        <!-- 🔍 Indicateur de pièces jointes -->
                                        <?php if ($d['nb_pieces_jointes'] > 0): ?>
                                            <span class="badge bg-success" title="<?= $d['nb_pieces_jointes'] ?> pièce(s) jointe(s)">
                                                <i class="bi bi-paperclip me-1"></i><?= $d['nb_pieces_jointes'] ?>
                                            </span>
                                        <?php else: ?>
                                            <span class="text-muted" title="Aucune pièce jointe">
                                                <i class="bi bi-paperclip"></i>
                                            </span>
                                        <?php endif; ?>
                                    </td>
                                    <td>
                                        <small><?= (new DateTime($d['date_creation']))->format('d/m/Y') ?></small>
                                    </td>
                                    <td>
                                        <div class="btn-group btn-group-sm">
                                            <a href="traitement.php?id=<?= $d['id'] ?>" class="btn btn-outline-primary">
                                                <i class="bi bi-eye"></i>
                                            </a>
                                        </div>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="9" class="text-center py-5 text-muted">
                                    <i class="bi bi-inbox display-4 d-block mb-3"></i>
                                    <h5>Aucun dossier trouvé</h5>
                                    <p class="mb-0">Aucun dossier n'est actuellement disponible.</p>
                                </td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
        <?php if (count($dossiers) > 0): ?>
        <div class="card-footer bg-white border-top">
            <div class="d-flex justify-content-between align-items-center">
                <div class="text-muted small">
                    Affichage de <?= $offset + 1 ?> à <?= min($offset + $limit, $total_dossiers) ?> sur <?= $total_dossiers ?> dossiers
                </div>
                <?php if ($total_pages > 1): ?>
                <nav aria-label="Navigation des pages">
                    <ul class="pagination pagination-sm mb-0">
                        <li class="page-item <?= $page <= 1 ? 'disabled' : '' ?>">
                            <a class="page-link" href="?page=<?= $page - 1 ?>" tabindex="-1">Précédent</a>
                        </li>
                        <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                            <li class="page-item <?= $i == $page ? 'active' : '' ?>">
                                <a class="page-link" href="?page=<?= $i ?>"><?= $i ?></a>
                            </li>
                        <?php endfor; ?>
                        <li class="page-item <?= $page >= $total_pages ? 'disabled' : '' ?>">
                            <a class="page-link" href="?page=<?= $page + 1 ?>">Suivant</a>
                        </li>
                    </ul>
                </nav>
                <?php endif; ?>
            </div>
        </div>
        <?php endif; ?>
    </div>

    <!-- Actions rapides -->
    <div class="row mt-4">
        <div class="col-12">
            <div class="card border-0 shadow-sm">
                <div class="card-body">
                    <h6 class="card-title mb-3">Actions rapides</h6>
                    <div class="d-flex gap-2 flex-wrap">
                        <a href="stats.php" class="btn btn-outline-info btn-sm">
                            <i class="bi bi-bar-chart me-1"></i>Statistiques détaillées
                        </a>
                        <a href="rapport.php" class="btn btn-outline-success btn-sm" target="_blank">
                            <i class="bi bi-file-pdf me-1"></i>Générer rapport
                        </a>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

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