<?php
$ROOT = dirname(__DIR__, 2);
require_once $ROOT . '/config/config.php';
require_once $ROOT . '/src/lib/session.php';
require_auth();

$pdo = getDbConnection();

// Forcer Excel + UTF-8
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=Business_Plan_Data.xls");
echo "\xEF\xBB\xBF"; // BOM UTF-8

// ====== 1. KPIs ======
$coutRotation = (float) $pdo->query("SELECT AVG(amount) FROM port_costs")->fetchColumn();
$seuilRentabilite = (float) $pdo->query("SELECT SUM(volume) FROM demands WHERE unit IN ('tonne','tonnes')")->fetchColumn();
$prixMoyenConcurrent = (float) $pdo->query("SELECT AVG(price) FROM competitor_prices")->fetchColumn();
$portsEnquetes = (int) $pdo->query("SELECT COUNT(DISTINCT p.id) FROM ports p JOIN port_costs pc ON pc.port_id = p.id")->fetchColumn();
$clientsEnquetes = (int) $pdo->query("SELECT COUNT(DISTINCT c.id) FROM clients c JOIN demands d ON d.client_id = c.id")->fetchColumn();
$concurrents = (int) $pdo->query("SELECT COUNT(*) FROM competitors")->fetchColumn();

echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>KPIs Calculés</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'><th>KPI</th><th>Valeur</th></tr>";
$kpis = [
    ['Coût rotation estimé (FCFA)', number_format($coutRotation, 0, ',', ' ')],
    ['Seuil rentabilité (tonnes)', number_format($seuilRentabilite, 0, ',', ' ')],
    ['Prix moyen concurrent (FCFA/t)', number_format($prixMoyenConcurrent, 0, ',', ' ')],
    ['Ports enquêtés', $portsEnquetes],
    ['Clients enquêtés', $clientsEnquetes],
    ['Concurrents', $concurrents],
];
foreach ($kpis as $row) {
    echo "<tr><td>{$row[0]}</td><td style='text-align:right;'>{$row[1]}</td></tr>";
}
echo "</table><br>";

// ====== 2. Liste des Ports ======
$ports = $pdo->query("SELECT id, NAME, CODE, location, draft_limit FROM ports")->fetchAll(PDO::FETCH_ASSOC);
echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>Ports</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'><th>ID</th><th>Nom</th><th>Code</th><th>Localisation</th><th>Tirant d'eau max</th></tr>";
foreach ($ports as $p) {
    echo "<tr>
            <td>{$p['id']}</td>
            <td>{$p['NAME']}</td>
            <td>{$p['CODE']}</td>
            <td>{$p['location']}</td>
            <td>{$p['draft_limit']}</td>
          </tr>";
}
echo "</table><br>";

// ====== 3. Clients & Demandes ======
$clients = $pdo->query("
    SELECT c.id, c.NAME, c.contact, COUNT(d.id) AS nb_demandes, SUM(d.volume) AS total_volume
    FROM clients c
    LEFT JOIN demands d ON d.client_id = c.id
    GROUP BY c.id
")->fetchAll(PDO::FETCH_ASSOC);
echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>Clients & Demandes</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'><th>ID</th><th>Nom</th><th>Contact</th><th>Nb Demandes</th><th>Volume total</th></tr>";
foreach ($clients as $c) {
    echo "<tr>
            <td>{$c['id']}</td>
            <td>{$c['NAME']}</td>
            <td>{$c['contact']}</td>
            <td>{$c['nb_demandes']}</td>
            <td>{$c['total_volume']}</td>
          </tr>";
}
echo "</table><br>";

// ====== 4. Concurrents & Prix ======
$competitors = $pdo->query("
    SELECT comp.id, comp.NAME, COUNT(cp.id) AS nb_prix, AVG(cp.price) AS prix_moyen
    FROM competitors comp
    LEFT JOIN competitor_prices cp ON cp.competitor_id = comp.id
    GROUP BY comp.id
")->fetchAll(PDO::FETCH_ASSOC);
echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>Concurrents</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'><th>ID</th><th>Nom</th><th>Nb Prix</th><th>Prix moyen</th></tr>";
foreach ($competitors as $comp) {
    echo "<tr>
            <td>{$comp['id']}</td>
            <td>{$comp['NAME']}</td>
            <td>{$comp['nb_prix']}</td>
            <td>" . number_format($comp['prix_moyen'], 0, ',', ' ') . "</td>
          </tr>";
}
echo "</table><br>";

// ====== 5. OPEX Navire ======
$opex = $pdo->query("SELECT * FROM vessel_opex")->fetchAll(PDO::FETCH_ASSOC);
echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>OPEX Navire</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'><th>Navire</th><th>Coût hebdo</th><th>Prix fuel/tonne</th></tr>";
foreach ($opex as $o) {
    echo "<tr>
            <td>{$o['vessel_name']}</td>
            <td>" . number_format($o['weekly_cost'], 0, ',', ' ') . "</td>
            <td>" . number_format($o['fuel_price_per_ton'], 0, ',', ' ') . "</td>
          </tr>";
}
echo "</table><br>";

// ====== 6. Réglementaire ======
$regs = $pdo->query("SELECT * FROM regulations")->fetchAll(PDO::FETCH_ASSOC);
echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>Réglementaire</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'><th>ID</th><th>Description</th><th>Status</th></tr>";
foreach ($regs as $r) {
    echo "<tr>
            <td>{$r['id']}</td>
            <td>{$r['description']}</td>
            <td>{$r['STATUS']}</td>
          </tr>";
}
echo "</table><br>";

// ====== 7. Journal Pilote ======
$journal = $pdo->query("SELECT * FROM pilot_journal")->fetchAll(PDO::FETCH_ASSOC);
echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>Journal Pilote</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'><th>ID</th><th>Route</th><th>Début</th><th>Fin</th><th>Notes</th></tr>";
foreach ($journal as $j) {
    echo "<tr>
            <td>{$j['id']}</td>
            <td>{$j['route_desc']}</td>
                        <td>{$j['date_start']}</td>
            <td>{$j['date_end']}</td>
            <td>{$j['notes']}</td>
          </tr>";
}
echo "</table><br>";

// ====== 8. KPI Calculés (table kpis) ======
$kpisTable = $pdo->query("SELECT * FROM kpis ORDER BY calculated_at DESC")->fetchAll(PDO::FETCH_ASSOC);
echo "<h2 style='background:#38bdf8;color:white;padding:5px;'>Historique KPI Calculés</h2>";
echo "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse; font-family:Arial;'>";
echo "<tr style='background:#38bdf8;color:white;font-weight:bold;'>
        <th>ID</th>
        <th>Coût rotation</th>
        <th>Seuil rentabilité</th>
        <th>Prix moyen concurrent</th>
        <th>Score faisabilité</th>
        <th>Date calcul</th>
      </tr>";
foreach ($kpisTable as $k) {
    echo "<tr>
            <td>{$k['id']}</td>
            <td>" . number_format($k['cost_rotation'], 0, ',', ' ') . "</td>
            <td>" . number_format($k['breakeven_volume'], 0, ',', ' ') . "</td>
            <td>" . number_format($k['competitor_avg_price'], 0, ',', ' ') . "</td>
            <td>{$k['feasibility_score']}</td>
            <td>{$k['calculated_at']}</td>
          </tr>";
}
echo "</table><br>";

// ====== FIN EXPORT ======
exit;
