<?php
$pageTitle = 'Tableau de Bord';
require_once __DIR__ . '/includes/header.php';
$db = getDB();

// Stats
$stats['voyageurs'] = $db->query("SELECT COUNT(*) FROM voyageurs WHERE status='actif'")->fetchColumn();
$stats['programmes_actifs'] = $db->query("SELECT COUNT(*) FROM programmes WHERE status='en_cours'")->fetchColumn();
$stats['inscriptions_actives'] = $db->query("SELECT COUNT(*) FROM inscriptions WHERE status='en_cours'")->fetchColumn();
$stats['services_actifs'] = $db->query("SELECT COUNT(*) FROM services WHERE statut_service='en_cours'")->fetchColumn();
$stats['chiffre_affaires'] = $db->query("SELECT COALESCE(SUM(montant),0) FROM paiements")->fetchColumn();
$omraEnCours = $db->query("SELECT COUNT(*) FROM inscriptions i JOIN programmes p ON i.programme_id=p.id WHERE p.type='omra' AND i.status='en_cours'")->fetchColumn();

$totalRecus = $db->query("SELECT COALESCE(SUM(montant),0) FROM paiements")->fetchColumn();
$totalEnAttente = $db->query("SELECT COALESCE(SUM(prix_total - montant_paye),0) FROM inscriptions WHERE status='en_cours' AND statut_paiement != 'paye'")->fetchColumn();
$totalDepenses = $db->query("SELECT COALESCE(SUM(montant),0) FROM depenses")->fetchColumn();
$hajjEnCours = $db->query("SELECT COUNT(*) FROM inscriptions i JOIN programmes p ON i.programme_id=p.id WHERE p.type='hajj' AND i.status='en_cours'")->fetchColumn();
$chiffreAffaires = $totalRecus;

// Monthly revenue for chart
$monthlyRevenue = $db->query("
    SELECT strftime('%m', date_paiement) as mois, SUM(montant) as total 
    FROM paiements 
    WHERE strftime('%Y', date_paiement) = strftime('%Y', 'now')
    GROUP BY mois ORDER BY mois
")->fetchAll();

// Revenue by type
$revenueByType = $db->query("
    SELECT p.type, COALESCE(SUM(pa.montant),0) as total 
    FROM paiements pa 
    JOIN inscriptions i ON pa.inscription_id=i.id 
    JOIN programmes p ON i.programme_id=p.id 
    GROUP BY p.type
")->fetchAll();

// Alerts
$passportsExpiring = $db->query("
    SELECT COUNT(*) FROM voyageurs 
    WHERE date_expiration_passeport IS NOT NULL 
    AND date_expiration_passeport <= date('now', '+3 months') 
    AND status='actif'
")->fetchColumn();

$paiementsRetard = $db->query("
    SELECT COUNT(*) FROM inscriptions i 
    JOIN programmes p ON i.programme_id=p.id 
    WHERE i.statut_paiement='impaye' OR (i.statut_paiement='partiel' AND p.date_depart <= date('now', '+30 days'))
")->fetchColumn();

$visasEnAttente = $db->query("SELECT COUNT(*) FROM inscriptions WHERE statut_visa='en_attente' AND status='en_cours'")->fetchColumn();

// Recent inscriptions
$recentInscriptions = $db->query("
    SELECT i.*, v.nom, v.prenom, p.nom as programme_nom, p.type as programme_type
    FROM inscriptions i
    JOIN voyageurs v ON i.voyageur_id=v.id
    JOIN programmes p ON i.programme_id=p.id
    ORDER BY i.created_at DESC LIMIT 5
")->fetchAll();

// Recent paiements
$recentPaiements = $db->query("
    SELECT pa.*, v.nom, v.prenom, p.nom as programme_nom
    FROM paiements pa
    JOIN voyageurs v ON pa.voyageur_id=v.id
    JOIN inscriptions i ON pa.inscription_id=i.id
    JOIN programmes p ON i.programme_id=p.id
    ORDER BY pa.created_at DESC LIMIT 5
")->fetchAll();

$moisLabels = ['Jan','Fév','Mar','Avr','Mai','Juin','Juil','Aoû','Sep','Oct','Nov','Déc'];
$monthlyData = array_fill(0, 12, 0);
foreach ($monthlyRevenue as $m) {
    $monthlyData[intval($m['mois'])-1] = $m['total'];
}

$typeLabels = [];
$typeData = [];
foreach ($revenueByType as $r) {
    $typeLabels[] = ucfirst($r['type']);
    $typeData[] = $r['total'];
}
?>

<!-- Stat Cards -->
<div class="row g-3 mb-4">
    <div class="col-6 col-lg-3">
        <div class="card stat-card">
            <div class="card-body">
                <div class="d-flex align-items-center justify-content-between">
                    <div>
                        <div class="text-muted small mb-1">Voyageurs</div>
                        <h3 class="mb-0 fw-bold"><?= $stats['voyageurs'] ?></h3>
                    </div>
                    <div class="stat-icon bg-primary bg-opacity-10 text-primary"><i class="bi bi-people-fill"></i></div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-6 col-lg-3">
        <div class="card stat-card">
            <div class="card-body">
                <div class="d-flex align-items-center justify-content-between">
                    <div>
                        <div class="text-muted small mb-1">Dossiers Hajj</div>
                        <h3 class="mb-0 fw-bold"><?= $hajjEnCours ?></h3>
                    </div>
                    <div class="stat-icon bg-success bg-opacity-10 text-success"><i class="bi bi-moon-stars-fill"></i></div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-6 col-lg-3">
        <div class="card stat-card">
            <div class="card-body">
                <div class="d-flex align-items-center justify-content-between">
                    <div>
                        <div class="text-muted small mb-1">Dossiers Omra</div>
                        <h3 class="mb-0 fw-bold"><?= $omraEnCours ?></h3>
                    </div>
                    <div class="stat-icon bg-info bg-opacity-10 text-info"><i class="bi bi-star-fill"></i></div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-6 col-lg-3">
        <div class="card stat-card">
            <div class="card-body">
                <div class="d-flex align-items-center justify-content-between">
                    <div>
                        <div class="text-muted small mb-1">Programmes actifs</div>
                        <h3 class="mb-0 fw-bold"><?= $stats['programmes_actifs'] ?></h3>
                    </div>
                    <div class="stat-icon bg-warning bg-opacity-10 text-warning"><i class="bi bi-calendar-event-fill"></i></div>
                </div>
            </div>
        </div>
    </div>
        <div class="col-6 col-lg-3">
        <div class="card stat-card">
            <div class="card-body">
                <div class="d-flex align-items-center justify-content-between">
                    <div>
                        <div class="text-muted small mb-1">Services en cours</div>
                        <h3 class="mb-0 fw-bold"><?= $stats['services_actifs'] ?></h3>
                    </div>
                    <div class="stat-icon bg-secondary bg-opacity-10 text-secondary"><i class="bi bi-briefcase-fill"></i></div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Financial Stats -->
<div class="row g-3 mb-4">
    <div class="col-6 col-lg-3">
        <div class="card stat-card border-start border-success border-4">
            <div class="card-body">
                <div class="text-muted small mb-1">Paiements reçus</div>
                <h5 class="mb-0 fw-bold text-success"><?= formatMoney($totalRecus) ?></h5>
            </div>
        </div>
    </div>
    <div class="col-6 col-lg-3">
        <div class="card stat-card border-start border-danger border-4">
            <div class="card-body">
                <div class="text-muted small mb-1">Créances (Restes)</div>
                <h5 class="mb-0 fw-bold text-danger"><?= formatMoney($totalEnAttente) ?></h5>
            </div>
        </div>
    </div>
    <div class="col-6 col-lg-3">
        <div class="card stat-card border-start border-warning border-4">
            <div class="card-body">
                <div class="text-muted small mb-1">Dépenses (Charges)</div>
                <h5 class="mb-0 fw-bold text-warning"><?= formatMoney($totalDepenses) ?></h5>
            </div>
        </div>
    </div>
    <div class="col-6 col-lg-3">
        <div class="card stat-card border-start border-primary border-4">
            <div class="card-body">
                <div class="text-muted small mb-1">Total Facturé (TTC)</div>
                <?php $totalFacture = $db->query("SELECT COALESCE(SUM(montant_ttc),0) FROM factures WHERE statut!='annulee'")->fetchColumn(); ?>
                <h5 class="mb-0 fw-bold text-primary"><?= formatMoney($totalFacture) ?></h5>
            </div>
        </div>
    </div>
</div>

<!-- Alerts -->
<?php if ($passportsExpiring > 0 || $paiementsRetard > 0 || $visasEnAttente > 0): ?>
<div class="row g-3 mb-4">
    <?php if ($passportsExpiring > 0): ?>
    <div class="col-md-4">
        <div class="alert alert-card alert-warning mb-0 d-flex align-items-center gap-3">
            <i class="bi bi-exclamation-triangle-fill fs-4"></i>
            <div>
                <strong><?= $passportsExpiring ?> passeport(s)</strong><br>
                <small>expirent dans moins de 3 mois</small>
            </div>
        </div>
    </div>
    <?php endif; ?>
    <?php if ($paiementsRetard > 0): ?>
    <div class="col-md-4">
        <div class="alert alert-card alert-danger mb-0 d-flex align-items-center gap-3">
            <i class="bi bi-cash-coin fs-4"></i>
            <div>
                <strong><?= $paiementsRetard ?> paiement(s)</strong><br>
                <small>en retard ou impayés</small>
            </div>
        </div>
    </div>
    <?php endif; ?>
    <?php if ($visasEnAttente > 0): ?>
    <div class="col-md-4">
        <div class="alert alert-card alert-info mb-0 d-flex align-items-center gap-3">
            <i class="bi bi-passport-fill fs-4"></i>
            <div>
                <strong><?= $visasEnAttente ?> visa(s)</strong><br>
                <small>en attente de confirmation</small>
            </div>
        </div>
    </div>
    <?php endif; ?>
</div>
<?php endif; ?>

<!-- Charts -->
<div class="row g-3 mb-4">
    <div class="col-lg-8">
        <div class="card table-card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <span><i class="bi bi-graph-up me-2"></i>Revenus mensuels <?= date('Y') ?></span>
            </div>
            <div class="card-body p-3">
                <canvas id="revenueChart" height="100"></canvas>
            </div>
        </div>
    </div>
    <div class="col-lg-4">
        <div class="card table-card">
            <div class="card-header">
                <i class="bi bi-pie-chart me-2"></i>Revenus par type
            </div>
            <div class="card-body p-3 d-flex align-items-center justify-content-center">
                <canvas id="typeChart" height="200"></canvas>
            </div>
        </div>
    </div>
</div>

<!-- Recent Data -->
<div class="row g-3 mb-4">
    <!-- Dernières inscriptions -->
    <div class="col-lg-7">
        <div class="card table-card h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <span><i class="bi bi-journal-check me-2"></i>Dernières inscriptions</span>
                <a href="<?= APP_URL ?>/inscriptions.php" class="btn btn-sm btn-outline-primary">Voir tout</a>
            </div>
            <div class="table-responsive">
                <table class="table table-hover">
                    <thead><tr>
                        <th>Voyageur</th><th>Programme</th><th>Chambre</th><th>Visa</th><th>Paiement</th>
                    </tr></thead>
                    <tbody>
                    <?php foreach ($recentInscriptions as $ins): ?>
                        <tr>
                            <td><strong><?= sanitize($ins['nom'].' '.$ins['prenom']) ?></strong></td>
                            <td>
                                <span class="badge bg-<?= $ins['programme_type']=='hajj'?'success':($ins['programme_type']=='omra'?'info':'primary') ?> bg-opacity-10 text-<?= $ins['programme_type']=='hajj'?'success':($ins['programme_type']=='omra'?'info':'primary') ?>">
                                    <?= ucfirst($ins['programme_type']) ?>
                                </span>
                                <small class="d-block text-muted"><?= sanitize($ins['programme_nom']) ?></small>
                            </td>
                            <td><?= ucfirst($ins['formule']) ?></td>
                            <td><?= getStatusBadge($ins['statut_visa']) ?></td>
                            <td><?= getStatusBadge($ins['statut_paiement']) ?></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    <!-- Derniers services -->
    <div class="col-lg-5">
        <div class="card table-card h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <span><i class="bi bi-briefcase me-2"></i>Derniers services</span>
                <a href="<?= APP_URL ?>/services.php" class="btn btn-sm btn-outline-primary">Voir tout</a>
            </div>
            <div class="table-responsive">
                <table class="table table-hover mb-0">
                    <thead><tr>
                        <th>Service</th><th>Bénéficiaire</th><th>Statut</th>
                    </tr></thead>
                    <tbody>
                    <?php
                    $recentServices = $db->query("SELECT s.*, v.nom, v.prenom FROM services s JOIN voyageurs v ON s.voyageur_id = v.id ORDER BY s.created_at DESC LIMIT 5")->fetchAll();
                    foreach ($recentServices as $svc): 
                        $types = ['billet'=>'Billet', 'visa'=>'Visa', 'assurance'=>'Assurance', 'transfert'=>'Transfert', 'hotel'=>'Hôtel', 'autre'=>'Autre'];
                    ?>
                        <tr>
                            <td>
                                <strong><?= sanitize($svc['titre']) ?></strong><br>
                                <span class="badge bg-secondary"><?= $types[$svc['type_service']] ?? 'Autre' ?></span>
                            </td>
                            <td><?= sanitize($svc['prenom'].' '.$svc['nom']) ?></td>
                            <td><?= getStatusBadge($svc['statut_service']) ?></td>
                        </tr>
                    <?php endforeach; ?>
                    <?php if (empty($recentServices)): ?><tr><td colspan="3" class="text-center py-3 text-muted">Aucun service</td></tr><?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="row g-3">
    <div class="col-12">
        <div class="card table-card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <span><i class="bi bi-cash-stack me-2"></i>Derniers paiements reçus</span>
                <a href="<?= APP_URL ?>/paiements.php" class="btn btn-sm btn-outline-primary">Voir tout</a>
            </div>
            <div class="table-responsive">
                <table class="table table-hover">
                    <thead><tr>
                        <th>Date</th><th>Type</th><th>Client</th><th>Référence</th><th>Montant</th><th>Mode</th>
                    </tr></thead>
                    <tbody>
                    <?php foreach ($recentPaiements as $p): ?>
                        <tr>
                            <td><?= formatDate($p['date_paiement']) ?></td>
                            <td>
                                <?php if ($p['service_id']): ?>
                                    <span class="badge bg-secondary"><i class="bi bi-briefcase me-1"></i>Service</span>
                                <?php else: ?>
                                    <span class="badge bg-primary"><i class="bi bi-journal-check me-1"></i>Programme</span>
                                <?php endif; ?>
                            </td>
                            <td><strong><?= sanitize($p['nom'].' '.$p['prenom']) ?></strong></td>
                            <td><?= sanitize($p['reference_paiement'] ?: '-') ?></td>
                            <td class="fw-bold text-success"><?= formatMoney($p['montant']) ?></td>
                            <td><span class="badge bg-light text-dark"><?= ucfirst($p['mode_paiement']) ?></span></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<?php
$extraScripts = "
<script>
// Revenue Chart
new Chart(document.getElementById('revenueChart'), {
    type: 'bar',
    data: {
        labels: " . json_encode($moisLabels) . ",
        datasets: [{
            label: 'Revenus (MAD)',
            data: " . json_encode($monthlyData) . ",
            backgroundColor: 'rgba(6, 95, 70, 0.7)',
            borderColor: 'rgba(6, 95, 70, 1)',
            borderWidth: 1,
            borderRadius: 6,
        }]
    },
    options: {
        responsive: true,
        plugins: { legend: { display: false } },
        scales: {
            y: { beginAtZero: true, ticks: { callback: v => (v/1000)+'K' } }
        }
    }
});

// Type Chart
new Chart(document.getElementById('typeChart'), {
    type: 'doughnut',
    data: {
        labels: " . json_encode($typeLabels) . ",
        datasets: [{
            data: " . json_encode($typeData) . ",
            backgroundColor: ['#065f46', '#0891b2', '#7c3aed', '#ea580c'],
            borderWidth: 2,
        }]
    },
    options: {
        responsive: true,
        plugins: { legend: { position: 'bottom' } }
    }
});
</script>";
require_once __DIR__ . '/includes/footer.php';
?>
