<?php
$pageTitle = 'Rapports & Statistiques';
require_once __DIR__ . '/includes/header.php';
$db = getDB();

$report = $_GET['report'] ?? '';

// Generate specific report
if ($report) {
    switch ($report) {
        case 'revenus_programme':
            $data = $db->query("
                SELECT p.nom, p.type, p.reference, 
                    COALESCE(SUM(pa.montant),0) as revenus,
                    (SELECT COALESCE(SUM(d.montant),0) FROM depenses d WHERE d.programme_id=p.id) as depenses,
                    COUNT(DISTINCT i.id) as nb_inscrits
                FROM programmes p 
                LEFT JOIN inscriptions i ON i.programme_id=p.id AND i.status != 'annule'
                LEFT JOIN paiements pa ON pa.inscription_id=i.id
                GROUP BY p.id ORDER BY revenus DESC
            ")->fetchAll();
?>
            <a href="<?= APP_URL ?>/rapports.php" class="btn btn-outline-secondary mb-4"><i class="bi bi-arrow-left me-1"></i>Retour</a>
            <div class="card table-card">
                <div class="card-header d-flex justify-content-between">
                    <span><i class="bi bi-bar-chart me-2"></i>Revenus par programme</span>
                    <button onclick="window.print()" class="btn btn-sm btn-outline-primary no-print"><i class="bi bi-printer me-1"></i>Imprimer</button>
                </div>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead><tr><th>Programme</th><th>Type</th><th>Inscrits</th><th>Revenus</th><th>Dépenses</th><th>Bénéfice</th><th>Marge</th></tr></thead>
                        <tbody>
                        <?php $totalR=0; $totalD=0; foreach ($data as $r): $benef=$r['revenus']-$r['depenses']; $marge=$r['revenus']>0?round($benef/$r['revenus']*100):0; $totalR+=$r['revenus']; $totalD+=$r['depenses']; ?>
                            <tr>
                                <td><strong><?= sanitize($r['nom']) ?></strong><br><small class="text-muted"><?= $r['reference'] ?></small></td>
                                <td><span class="badge bg-<?= $r['type']=='hajj'?'success':($r['type']=='omra'?'info':'primary') ?>"><?= ucfirst($r['type']) ?></span></td>
                                <td><?= $r['nb_inscrits'] ?></td>
                                <td class="text-success fw-bold"><?= formatMoney($r['revenus']) ?></td>
                                <td class="text-danger"><?= formatMoney($r['depenses']) ?></td>
                                <td class="fw-bold <?= $benef>=0?'text-success':'text-danger' ?>"><?= formatMoney($benef) ?></td>
                                <td><span class="badge <?= $marge>=0?'bg-success':'bg-danger' ?>"><?= $marge ?>%</span></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                        <tfoot class="table-light">
                            <tr class="fw-bold"><td>TOTAL</td><td></td><td></td><td class="text-success"><?= formatMoney($totalR) ?></td><td class="text-danger"><?= formatMoney($totalD) ?></td><td class="<?= ($totalR-$totalD)>=0?'text-success':'text-danger' ?>"><?= formatMoney($totalR-$totalD) ?></td><td></td></tr>
                        </tfoot>
                    </table>
                </div>
            </div>
<?php       break;

        case 'pelerins_saison':
            $data = $db->query("
                SELECT p.type, strftime('%Y', p.date_depart) as annee, 
                    COUNT(DISTINCT i.id) as nb_inscrits,
                    COALESCE(SUM(i.prix_total),0) as total_prix,
                    COALESCE(SUM(i.montant_paye),0) as total_paye
                FROM inscriptions i 
                JOIN programmes p ON i.programme_id=p.id
                WHERE i.status != 'annule'
                GROUP BY p.type, annee ORDER BY annee DESC, p.type
            ")->fetchAll();
?>
            <a href="<?= APP_URL ?>/rapports.php" class="btn btn-outline-secondary mb-4"><i class="bi bi-arrow-left me-1"></i>Retour</a>
            <div class="card table-card">
                <div class="card-header d-flex justify-content-between">
                    <span><i class="bi bi-calendar-range me-2"></i>Pèlerins par saison</span>
                    <button onclick="window.print()" class="btn btn-sm btn-outline-primary no-print"><i class="bi bi-printer me-1"></i>Imprimer</button>
                </div>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead><tr><th>Année</th><th>Type</th><th>Nombre d'inscrits</th><th>Total facturé</th><th>Total payé</th><th>Reste</th></tr></thead>
                        <tbody>
                        <?php foreach ($data as $r): ?>
                            <tr>
                                <td><strong><?= $r['annee'] ?></strong></td>
                                <td><span class="badge bg-<?= $r['type']=='hajj'?'success':($r['type']=='omra'?'info':'primary') ?>"><?= ucfirst($r['type']) ?></span></td>
                                <td><h5 class="mb-0"><?= $r['nb_inscrits'] ?></h5></td>
                                <td><?= formatMoney($r['total_prix']) ?></td>
                                <td class="text-success"><?= formatMoney($r['total_paye']) ?></td>
                                <td class="text-danger"><?= formatMoney($r['total_prix'] - $r['total_paye']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
<?php       break;

        case 'clients_fideles':
            $data = $db->query("
                SELECT v.id, v.nom, v.prenom, v.telephone, v.ville, v.reference,
                    COUNT(i.id) as nb_voyages,
                    COALESCE(SUM(i.prix_total),0) as total_depense,
                    COALESCE(SUM(i.montant_paye),0) as total_paye,
                    MAX(p.date_depart) as dernier_voyage
                FROM voyageurs v 
                JOIN inscriptions i ON i.voyageur_id=v.id AND i.status != 'annule'
                JOIN programmes p ON i.programme_id=p.id
                WHERE v.status='actif'
                GROUP BY v.id
                HAVING nb_voyages >= 1
                ORDER BY nb_voyages DESC, total_depense DESC
                LIMIT 50
            ")->fetchAll();
?>
            <a href="<?= APP_URL ?>/rapports.php" class="btn btn-outline-secondary mb-4"><i class="bi bi-arrow-left me-1"></i>Retour</a>
            <div class="card table-card">
                <div class="card-header d-flex justify-content-between">
                    <span><i class="bi bi-star me-2"></i>Clients fidèles</span>
                    <button onclick="window.print()" class="btn btn-sm btn-outline-primary no-print"><i class="bi bi-printer me-1"></i>Imprimer</button>
                </div>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead><tr><th>#</th><th>Client</th><th>Ville</th><th>Voyages</th><th>Total dépensé</th><th>Dernier voyage</th></tr></thead>
                        <tbody>
                        <?php foreach ($data as $i => $r): ?>
                            <tr>
                                <td><?= $i+1 ?></td>
                                <td><a href="<?= APP_URL ?>/voyageurs.php?action=view&id=<?= $r['id'] ?>"><strong><?= sanitize($r['prenom'].' '.$r['nom']) ?></strong></a><br><small class="text-muted"><?= sanitize($r['telephone'] ?: '') ?></small></td>
                                <td><?= sanitize($r['ville'] ?: '-') ?></td>
                                <td><span class="badge bg-primary"><?= $r['nb_voyages'] ?></span></td>
                                <td class="fw-bold"><?= formatMoney($r['total_paye']) ?></td>
                                <td><?= formatDate($r['dernier_voyage']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
<?php       break;

        case 'paiements_mensuel':
            $year = $_GET['year'] ?? date('Y');
            $data = $db->prepare("
                SELECT strftime('%m', pa.date_paiement) as mois,
                    pa.mode_paiement,
                    COUNT(*) as nb,
                    SUM(pa.montant) as total
                FROM paiements pa
                WHERE strftime('%Y', pa.date_paiement) = ?
                GROUP BY mois, pa.mode_paiement
                ORDER BY mois
            ");
            $data->execute([$year]);
            $data = $data->fetchAll();
            
            $moisNoms = ['01'=>'Janvier','02'=>'Février','03'=>'Mars','04'=>'Avril','05'=>'Mai','06'=>'Juin','07'=>'Juillet','08'=>'Août','09'=>'Septembre','10'=>'Octobre','11'=>'Novembre','12'=>'Décembre'];
            $grouped = [];
            foreach ($data as $r) {
                $grouped[$r['mois']][$r['mode_paiement']] = $r;
                if (!isset($grouped[$r['mois']]['total'])) $grouped[$r['mois']]['total'] = 0;
                $grouped[$r['mois']]['total'] += $r['total'];
            }
?>
            <a href="<?= APP_URL ?>/rapports.php" class="btn btn-outline-secondary mb-4"><i class="bi bi-arrow-left me-1"></i>Retour</a>
            <div class="card table-card">
                <div class="card-header d-flex justify-content-between">
                    <span><i class="bi bi-cash-stack me-2"></i>Rapport paiements mensuels - <?= $year ?></span>
                    <button onclick="window.print()" class="btn btn-sm btn-outline-primary no-print"><i class="bi bi-printer me-1"></i>Imprimer</button>
                </div>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead><tr><th>Mois</th><th>Espèces</th><th>Virement</th><th>Chèque</th><th>Carte</th><th>Total</th></tr></thead>
                        <tbody>
                        <?php $grandTotal = 0; foreach ($grouped as $mois => $modes): $grandTotal += $modes['total']; ?>
                            <tr>
                                <td><strong><?= $moisNoms[$mois] ?? $mois ?></strong></td>
                                <td><?= formatMoney($modes['especes']['total'] ?? 0) ?></td>
                                <td><?= formatMoney($modes['virement']['total'] ?? 0) ?></td>
                                <td><?= formatMoney($modes['cheque']['total'] ?? 0) ?></td>
                                <td><?= formatMoney($modes['carte']['total'] ?? 0) ?></td>
                                <td class="fw-bold text-success"><?= formatMoney($modes['total']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                        <tfoot class="table-light"><tr class="fw-bold"><td>TOTAL ANNUEL</td><td colspan="4"></td><td class="text-success"><?= formatMoney($grandTotal) ?></td></tr></tfoot>
                    </table>
                </div>
            </div>
<?php       break;

        case 'depenses_categorie':
            $data = $db->query("
                SELECT d.categorie, 
                    COUNT(*) as nb,
                    SUM(d.montant) as total,
                    p.nom as programme_nom, p.type as programme_type
                FROM depenses d
                LEFT JOIN programmes p ON d.programme_id=p.id
                GROUP BY d.categorie, d.programme_id
                ORDER BY total DESC
            ")->fetchAll();
?>
            <a href="<?= APP_URL ?>/rapports.php" class="btn btn-outline-secondary mb-4"><i class="bi bi-arrow-left me-1"></i>Retour</a>
            <div class="card table-card">
                <div class="card-header d-flex justify-content-between">
                    <span><i class="bi bi-wallet2 me-2"></i>Dépenses par catégorie</span>
                    <button onclick="window.print()" class="btn btn-sm btn-outline-primary no-print"><i class="bi bi-printer me-1"></i>Imprimer</button>
                </div>
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead><tr><th>Catégorie</th><th>Programme</th><th>Opérations</th><th>Total</th></tr></thead>
                        <tbody>
                        <?php $grandTotal = 0; foreach ($data as $r): $grandTotal += $r['total']; ?>
                            <tr>
                                <td><strong><?= ucfirst($r['categorie']) ?></strong></td>
                                <td><?= sanitize($r['programme_nom'] ?: 'Général') ?></td>
                                <td><?= $r['nb'] ?></td>
                                <td class="fw-bold text-danger"><?= formatMoney($r['total']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                        <tfoot class="table-light"><tr class="fw-bold"><td>TOTAL</td><td></td><td></td><td class="text-danger"><?= formatMoney($grandTotal) ?></td></tr></tfoot>
                    </table>
                </div>
            </div>
<?php       break;
    }
    require_once __DIR__ . '/includes/footer.php';
    exit;
}

// Reports dashboard
?>

<div class="row g-3">
    <div class="col-md-6 col-lg-4">
        <a href="<?= APP_URL ?>/rapports.php?report=revenus_programme" class="text-decoration-none">
            <div class="card stat-card h-100">
                <div class="card-body text-center p-4">
                    <div class="stat-icon bg-success bg-opacity-10 text-success mx-auto mb-3"><i class="bi bi-bar-chart-fill"></i></div>
                    <h6 class="fw-bold">Revenus par programme</h6>
                    <p class="text-muted small mb-0">Analyse de la rentabilité par programme et destination</p>
                </div>
            </div>
        </a>
    </div>
    <div class="col-md-6 col-lg-4">
        <a href="<?= APP_URL ?>/rapports.php?report=pelerins_saison" class="text-decoration-none">
            <div class="card stat-card h-100">
                <div class="card-body text-center p-4">
                    <div class="stat-icon bg-primary bg-opacity-10 text-primary mx-auto mb-3"><i class="bi bi-calendar-range"></i></div>
                    <h6 class="fw-bold">Pèlerins par saison</h6>
                    <p class="text-muted small mb-0">Nombre de pèlerins Hajj/Omra/Tourisme par année</p>
                </div>
            </div>
        </a>
    </div>
    <div class="col-md-6 col-lg-4">
        <a href="<?= APP_URL ?>/rapports.php?report=paiements_mensuel" class="text-decoration-none">
            <div class="card stat-card h-100">
                <div class="card-body text-center p-4">
                    <div class="stat-icon bg-warning bg-opacity-10 text-warning mx-auto mb-3"><i class="bi bi-cash-stack"></i></div>
                    <h6 class="fw-bold">Paiements mensuels</h6>
                    <p class="text-muted small mb-0">Suivi mensuel des encaissements par mode de paiement</p>
                </div>
            </div>
        </a>
    </div>
    <div class="col-md-6 col-lg-4">
        <a href="<?= APP_URL ?>/rapports.php?report=depenses_categorie" class="text-decoration-none">
            <div class="card stat-card h-100">
                <div class="card-body text-center p-4">
                    <div class="stat-icon bg-danger bg-opacity-10 text-danger mx-auto mb-3"><i class="bi bi-wallet2"></i></div>
                    <h6 class="fw-bold">Dépenses par catégorie</h6>
                    <p class="text-muted small mb-0">Analyse des charges par catégorie et programme</p>
                </div>
            </div>
        </a>
    </div>
    <div class="col-md-6 col-lg-4">
        <a href="<?= APP_URL ?>/rapports.php?report=clients_fideles" class="text-decoration-none">
            <div class="card stat-card h-100">
                <div class="card-body text-center p-4">
                    <div class="stat-icon bg-info bg-opacity-10 text-info mx-auto mb-3"><i class="bi bi-star-fill"></i></div>
                    <h6 class="fw-bold">Clients fidèles</h6>
                    <p class="text-muted small mb-0">Top des clients avec le plus de voyages</p>
                </div>
            </div>
        </a>
    </div>
</div>

<?php require_once __DIR__ . '/includes/footer.php'; ?>
