<?php
session_start();
require_once 'config.php';
require_once 'functions.php';
requireLogin();
$user = getConnectedUser($pdo);
requireAdmin($user);

$format = $_GET['format'] ?? 'view';

// Récupérer toutes les tables
try {
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
} catch (Exception $e) { die("Erreur: " . $e->getMessage()); }

$schema = [];
foreach ($tables as $table) {
    try {
        $cols = $pdo->query("SHOW FULL COLUMNS FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);
        $schema[$table] = $cols;
    } catch (Exception $e) { $schema[$table] = []; }
}

// ── FORMAT TXT ──────────────────────────────────────────────
if ($format === 'txt') {
    header('Content-Type: text/plain; charset=utf-8');
    header('Content-Disposition: attachment; filename="schema_bdd_' . date('Ymd') . '.txt"');
    echo "STRUCTURE DE LA BASE DE DONNÉES — JeTeGageQue\n";
    echo "Généré le " . date('Y-m-d H:i:s') . "\n";
    echo str_repeat('=', 70) . "\n\n";
    foreach ($schema as $table => $cols) {
        echo "TABLE: {$table} (" . count($cols) . " colonnes)\n";
        echo str_repeat('-', 50) . "\n";
        foreach ($cols as $col) {
            $null = $col['Null'] === 'YES' ? 'NULL' : 'NOT NULL';
            $def  = $col['Default'] !== null ? " DEFAULT '{$col['Default']}'" : '';
            $key  = $col['Key'] ? " [{$col['Key']}]" : '';
            $extra = $col['Extra'] ? " {$col['Extra']}" : '';
            echo "  {$col['Field']}: {$col['Type']} {$null}{$def}{$extra}{$key}\n";
            if ($col['Comment']) echo "    → {$col['Comment']}\n";
        }
        echo "\n";
    }
    exit;
}

// ── FORMAT CSV ──────────────────────────────────────────────
if ($format === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="schema_bdd_' . date('Ymd') . '.csv"');
    $out = fopen('php://output', 'w');
    fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF)); // BOM UTF-8
    fputcsv($out, ['Table','Colonne','Type','Null','Defaut','Cle','Extra','Commentaire']);
    foreach ($schema as $table => $cols) {
        foreach ($cols as $col) {
            fputcsv($out, [
                $table,
                $col['Field'],
                $col['Type'],
                $col['Null'],
                $col['Default'] ?? '',
                $col['Key'] ?? '',
                $col['Extra'] ?? '',
                $col['Comment'] ?? '',
            ]);
        }
    }
    fclose($out);
    exit;
}

// ── FORMAT XLS (HTML table téléchargeable) ──────────────────
if ($format === 'xls') {
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="schema_bdd_' . date('Ymd') . '.xls"');
    echo '<html><head><meta charset="UTF-8"></head><body>';
    echo '<table border="1">';
    echo '<tr style="background:#003366;color:#fff;font-weight:bold;">';
    echo '<td>Table</td><td>Colonne</td><td>Type</td><td>Null</td><td>Defaut</td><td>Cle</td><td>Extra</td><td>Commentaire</td></tr>';
    $i = 0;
    foreach ($schema as $table => $cols) {
        foreach ($cols as $col) {
            $bg = ($i % 2 === 0) ? '#f0f4ff' : '#ffffff';
            echo '<tr style="background:'.$bg.';">';
            echo '<td>'.$table.'</td>';
            echo '<td><strong>'.$col['Field'].'</strong></td>';
            echo '<td>'.$col['Type'].'</td>';
            echo '<td>'.($col['Null']==='YES'?'OUI':'NON').'</td>';
            echo '<td>'.htmlspecialchars($col['Default']??'').'</td>';
            echo '<td>'.htmlspecialchars($col['Key']??'').'</td>';
            echo '<td>'.htmlspecialchars($col['Extra']??'').'</td>';
            echo '<td>'.htmlspecialchars($col['Comment']??'').'</td>';
            echo '</tr>';
            $i++;
        }
    }
    echo '</table></body></html>';
    exit;
}

// ── FORMAT VIEW (affichage en ligne) ───────────────────────
include 'header.php';
?>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css">
<div class="container-fluid mt-3 mb-5">
    <div class="d-flex align-items-center gap-3 mb-3">
        <a href="admin_config.php" class="btn btn-outline-secondary btn-sm">← Config</a>
        <h5 class="oracle-text text-info mb-0">Structure de la base de données</h5>
        <div class="ms-auto d-flex gap-2">
            <a href="?format=txt" class="btn btn-outline-info btn-sm"><i class="fa-solid fa-file-lines me-1"></i>.txt</a>
            <a href="?format=csv" class="btn btn-outline-warning btn-sm"><i class="fa-solid fa-file-csv me-1"></i>.csv</a>
            <a href="?format=xls" class="btn btn-outline-success btn-sm"><i class="fa-solid fa-file-excel me-1"></i>.xls</a>
        </div>
    </div>
    <div style="font-size:0.65rem;color:#999;margin-bottom:16px;"><?= count($schema) ?> tables — Généré le <?= date('d/m/Y H:i') ?></div>

    <?php foreach ($schema as $table => $cols): ?>
    <div class="card bg-dark border-secondary shadow mb-3">
        <div class="card-header py-2 d-flex justify-content-between" style="background:#050520;">
            <span class="text-info fw-bold" style="font-size:0.75rem;letter-spacing:1px;">
                <i class="fa-solid fa-table fa-xs me-2"></i><?= htmlspecialchars($table) ?>
            </span>
            <span class="text-muted" style="font-size:0.62rem;"><?= count($cols) ?> colonnes</span>
        </div>
        <div class="card-body p-0" style="overflow-x:auto;">
            <table class="table table-dark table-sm mb-0">
                <thead>
                    <tr style="background:#0a0a1a;">
                        <th style="font-size:0.6rem;color:#888;padding:6px 10px;">Colonne</th>
                        <th style="font-size:0.6rem;color:#888;">Type</th>
                        <th style="font-size:0.6rem;color:#888;">Null</th>
                        <th style="font-size:0.6rem;color:#888;">Défaut</th>
                        <th style="font-size:0.6rem;color:#888;">Clé</th>
                        <th style="font-size:0.6rem;color:#888;">Extra</th>
                    </tr>
                </thead>
                <tbody>
                <?php foreach ($cols as $col):
                    $isPk = $col['Key'] === 'PRI';
                    $isUk = $col['Key'] === 'UNI';
                    $isMul= $col['Key'] === 'MUL';
                    $colColor = $isPk ? '#ffc107' : ($isUk ? '#0dcaf0' : ($isMul ? '#a78bfa' : '#ddd'));
                ?>
                <tr style="border-bottom:1px solid #0d0d0d;">
                    <td style="font-size:0.7rem;color:<?= $colColor ?>;font-weight:<?= $isPk?'700':'400' ?>;padding:5px 10px;">
                        <?= htmlspecialchars($col['Field']) ?>
                        <?php if ($isPk): ?><span style="font-size:0.5rem;color:#ffc107;"> PK</span><?php endif; ?>
                        <?php if ($isUk): ?><span style="font-size:0.5rem;color:#0dcaf0;"> UNI</span><?php endif; ?>
                    </td>
                    <td style="font-size:0.68rem;color:#777;"><?= htmlspecialchars($col['Type']) ?></td>
                    <td style="font-size:0.65rem;color:<?= $col['Null']==='YES'?'#888':'#555' ?>;"><?= $col['Null'] ?></td>
                    <td style="font-size:0.65rem;color:#aaa;"><?= htmlspecialchars($col['Default'] ?? '—') ?></td>
                    <td style="font-size:0.65rem;">
                        <?php if ($isPk): ?><span style="color:#ffc107;">PRI</span>
                        <?php elseif ($isUk): ?><span style="color:#0dcaf0;">UNI</span>
                        <?php elseif ($isMul): ?><span style="color:#a78bfa;">IDX</span>
                        <?php else: ?><span style="color:#777;">—</span><?php endif; ?>
                    </td>
                    <td style="font-size:0.65rem;color:#999;"><?= htmlspecialchars($col['Extra'] ?? '—') ?></td>
                </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    <?php endforeach; ?>
</div>
<?php include 'footer.php'; ?>
