<?php

require_once __DIR__ . '/../../bootstrap.php';
require_once __DIR__ . '/../../src/AdminAuth.php';
AdminAuth::requireLogin();

if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

$pageTitle = 'Liste des TFE';
require_once __DIR__ . '/../../src/Database.php';

// ── CSV Import (inline, submitted to this same page) ─────────────────────────
$importMessage = '';
$importErrors  = [];
$importResults = [];
$importDone    = false;

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['csv_file'])) {
    if (!isset($_POST['csrf_token']) || !hash_equals($_SESSION['csrf_token'], $_POST['csrf_token'])) {
        $importErrors[] = 'Erreur de sécurité : token invalide.';
    } else {
        $importedCount = 0;
        $skippedCount  = 0;
        try {
            $importDb  = new Database();
            $importPdo = $importDb->getPDO();

            if ($_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
                throw new Exception('Erreur lors du téléversement du fichier.');
            }

            $handle = fopen($_FILES['csv_file']['tmp_name'], 'r');
            if (!$handle) {
                throw new Exception("Impossible d'ouvrir le fichier CSV.");
            }

            // Scan up to 8 rows looking for a header row with known column names.
            // Build colIdx[name] → position map; fall back to positional if header not found.
            // Matching uses prefix + variant logic so "contact.visible" matches "contact",
            // "promoteur·ice(s)" matches "promoteur", "Licence" matches "license", etc.
            require_once APP_ROOT . '/src/Controllers/ExportController.php';
            $csvCols    = ExportController::CSV_COLUMNS;
            $csvHeaders = ExportController::csvHeaders();
            $colCount   = count($csvCols);

            $colIdx = null;
            $headerRowNum = 0;
            // Build knownHeaders from CSV_COLUMNS import_key column (index 2)
            $knownHeaders = array_unique(array_column($csvCols, 2));
            // Add licence/license cross-match aliases
            $knownHeaders[] = 'license';
            for ($scan = 0; $scan < 8; $scan++) {
                $hrow = fgetcsv($handle, 0, ',', '"', '');
                if ($hrow === false) {
                    break;
                }
                $headerRowNum++;
                $normRow = array_map(fn ($s) => strtolower(trim((string)$s)), $hrow);
                $hits = 0;
                $map  = [];
                $used = [];
                foreach ($knownHeaders as $h) {
                    foreach ($normRow as $pos => $cell) {
                        if (isset($used[$pos])) {
                            continue;
                        }
                        // Exact match
                        if ($cell === $h) {
                            $hits++;
                            $map[$h] = $pos;
                            $used[$pos] = true;
                            break;
                        }
                        // Licence/License cross-match
                        if (($h === 'licence' && $cell === 'license') || ($h === 'license' && $cell === 'licence')) {
                            $hits++;
                            $map[$h] = $pos;
                            $used[$pos] = true;
                            break;
                        }
                        // Prefix match (for compound headers like "contact.visible")
                        $hlen = strlen($h);
                        if ($hlen >= 4 && str_starts_with($cell, $h)) {
                            // Avoid short prefixes matching unrelated words
                            if ($hlen >= 5 || $cell === $h) {
                                $hits++;
                                $map[$h] = $pos;
                                $used[$pos] = true;
                                break;
                            }
                        }
                        // Substring match for short distinguishers (ulb, externe)
                        if ($hlen >= 3 && $hlen <= 7 && str_contains($cell, $h)) {
                            $hits++;
                            $map[$h] = $pos;
                            $used[$pos] = true;
                            break;
                        }
                    }
                }
                // Require at least 11 known headers to trust the row.
                if ($hits >= 11) {
                    $colIdx = $map;
                    break;
                }
            }
            // If no header row found, rewind and fall back to positional (skip 4 rows).
            if ($colIdx === null) {
                rewind($handle);
                $headerRowNum = 4;
                for ($i = 0; $i < 4; $i++) {
                    fgetcsv($handle);
                }
            } else {
                // Consume blank/instruction/template rows between header and data.
                // Stops when a row has a non-empty identifiant column that is not a
                // template placeholder (e.g. "Column1") or instruction snippet.
                $idPos = $colIdx['identifiant'] ?? 0;
                $peekRow = null;
                while (true) {
                    $peek = fgetcsv($handle, 0, ',', '"', '');
                    if ($peek === false) {
                        break;
                    }
                    $headerRowNum++;
                    $val = trim((string)($peek[$idPos] ?? ''));
                    if ($val === '' || str_starts_with(strtolower($val), 'column')
                                  || str_contains(strtolower($val), 'éparer')) {
                        continue; // metadata row, skip
                    }
                    $peekRow = $peek;
                    break;
                }
            }

            // Build positional fallback from CSV_COLUMNS (keyed by import_key → position)
            $fallbackPositions = [];
            foreach ($csvCols as $pos => $col) {
                $key = $col[2];
                if (!isset($fallbackPositions[$key])) {
                    $fallbackPositions[$key] = $pos;
                }
            }

            // Helper: get cell value by column name.
            // When header was found: use mapped column.
            // When header was found but key is missing: use positional fallback.
            // When no header found: use positional fallback.
            $cell = function (array $row, string $name) use ($colIdx, $fallbackPositions): string {
                if ($colIdx !== null) {
                    $pos = $colIdx[$name] ?? null;
                    if ($pos === null) {
                        // Try licence/license cross-lookup
                        if ($name === 'license') {
                            $pos = $colIdx['licence'] ?? null;
                        } elseif ($name === 'licence') {
                            $pos = $colIdx['license'] ?? null;
                        }
                    }
                    // Fall back to positional if header didn't have this key
                    if ($pos === null) {
                        $pos = $fallbackPositions[$name] ?? null;
                    }
                    if ($pos === null) {
                        return '';
                    }
                } else {
                    $pos = $fallbackPositions[$name] ?? null;
                    if ($pos === null) {
                        return '';
                    }
                }
                return isset($row[$pos]) ? trim((string)$row[$pos]) : '';
            };

            // Helper: parse "Oui"/"Non"/1/0 strings to int
            $parseBool = function (string $raw): int {
                $raw = strtolower(trim($raw));
                return ($raw === 'oui' || $raw === '1' || $raw === 'yes' || $raw === 'true') ? 1 : 0;
            };

            // Helper: parse a bulk-import date cell into a UTC 'Y-m-d H:i:s' string.
            // The cell is authored in Brussels local time (e.g. "12/05/2026",
            // "12/05/2026 14:30", ISO "2026-05-12"). We interpret it in the
            // Europe/Brussels timezone and convert to UTC for storage, matching
            // the rest of the DB (SQLite CURRENT_TIMESTAMP) and the db_datetime()
            // display helper which converts UTC → Brussels on read.
            $parseBulkDate = function (string $raw): ?string {
                $raw = trim($raw);
                if ($raw === '') {
                    return null;
                }

                // Try a set of formats (d/m/Y first — the CSV convention).
                // Date-only formats (no time component) are anchored to midnight
                // local time; PHP otherwise fills a missing time with *now*.
                $formats = [
                    'd/m/Y H:i:s', 'd/m/Y H:i', 'd/m/Y',
                    'Y-m-d H:i:s', 'Y-m-d H:i', 'Y-m-d',
                    'd.m.Y H:i',   'd.m.Y',
                ];
                foreach ($formats as $fmt) {
                    $dt = DateTime::createFromFormat($fmt, $raw, new DateTimeZone('Europe/Brussels'));
                    if ($dt === false) {
                        continue;
                    }
                    // Reject partial parses (e.g. "12/05/20xx" residual garbage).
                    $errors = DateTime::getLastErrors();
                    if ($errors && ($errors['warning_count'] > 0 || $errors['error_count'] > 0)) {
                        continue;
                    }
                    // Anchor date-only values to midnight local time.
                    if (!str_contains($fmt, 'H') && !str_contains($fmt, ':')) {
                        $dt->setTime(0, 0, 0);
                    }
                    $dt->setTimezone(new DateTimeZone('UTC'));
                    return $dt->format('Y-m-d H:i:s');
                }

                // ISO fallback via strtotime (assumes Brussels for naive strings).
                $ts = strtotime($raw . ' Europe/Brussels');
                if ($ts !== false) {
                    return gmdate('Y-m-d H:i:s', $ts);
                }

                return null;
            };

            // Code → canonical name (legacy short-code CSV format)
            $orientationCodeMap = [
                'SC' => 'Sculpture','VI' => 'Vidéographie','CA' => "Cinéma d'animation",
                'IP' => 'Installation-Performance','PE' => 'Peinture','PH' => 'Photographie',
                'DE' => 'Dessin','AN' => 'Arts Numériques','GR' => 'Graphisme',
                'TY' => 'Typographie','DN' => 'Design Numérique','IL' => 'Illustration',
                'BD' => 'Bande-Dessinée','SE' => 'Sérigraphie','GV' => 'Gravure',
            ];

            // Alias map: normalise known variant spellings → canonical DB name.
            // Keys are lowercased+stripped for comparison.
            $orientationAliases = [
                'arts numériques'         => 'Arts Numériques',
                'design numérique'        => 'Design Numérique',
                'installation/performance' => 'Installation-Performance',
                'installation performance' => 'Installation-Performance',
                'cinema d\'animation'     => "Cinéma d'animation",
                'cinéma d\'animation'     => "Cinéma d'animation",
                'bande dessinée'          => 'Bande-Dessinée',
            ];

            // Resolve an orientation string (code or full name) → canonical DB name.
            $resolveOrientation = function (string $raw) use ($orientationCodeMap, $orientationAliases, $importPdo): ?int {
                $raw = trim($raw);
                if ($raw === '') {
                    return null;
                }

                // 1. Try legacy short code
                if (isset($orientationCodeMap[$raw])) {
                    $raw = $orientationCodeMap[$raw];
                }

                // 2. Try alias map (lowercase key)
                $key = strtolower($raw);
                if (isset($orientationAliases[$key])) {
                    $raw = $orientationAliases[$key];
                }

                // 3. Exact DB match
                $s = $importPdo->prepare('SELECT id FROM orientations WHERE name = ?');
                $s->execute([$raw]);
                $r = $s->fetch();
                if ($r) {
                    return (int)$r['id'];
                }

                // 4. Case-insensitive DB match
                $s = $importPdo->prepare('SELECT id FROM orientations WHERE LOWER(name) = LOWER(?)');
                $s->execute([$raw]);
                $r = $s->fetch();
                return $r ? (int)$r['id'] : null;
            };

            // AP alias map: variant spellings → canonical code.
            $apAliases = [
                'l.i.e.n.s.'                => 'LIENS',
                'liens'                     => 'LIENS',
                'lieux, interdisciplinarités, écologie, nécessité, systèmes'
                                            => 'LIENS',
                'récits et expérimentation' => 'NS',
                'recits et experimentation' => 'NS',
                'narraion spéculative'      => 'NS',
                'narration spéculative'     => 'NS',
                'atelier pratiques situées' => 'APS',
                'design & politique du multiple' => 'DPM',
                'design et politique du multiple' => 'DPM',
                'pacs'                      => 'PACS',
                'pratique de l\'art'          => 'PACS',
                'pratiques artistiques & complexité scientifique' => 'PACS',
            ];

            // Resolve an AP string (code or full name) → ap_program id.
            $resolveAP = function (string $raw) use ($apAliases, $importPdo): ?int {
                $raw = trim($raw);
                if ($raw === '') {
                    return null;
                }

                // 1. Try alias map (lowercase key) → canonical code
                $key = strtolower($raw);
                if (isset($apAliases[$key])) {
                    $raw = $apAliases[$key];
                }

                // 2. Match by code
                $s = $importPdo->prepare('SELECT id FROM ap_programs WHERE code = ?');
                $s->execute([$raw]);
                $r = $s->fetch();
                if ($r) {
                    return (int)$r['id'];
                }

                // 3. Code match (legacy)
                $s = $importPdo->prepare('SELECT id FROM ap_programs WHERE code = ?');
                $s->execute([$raw]);
                $r = $s->fetch();
                if ($r) {
                    return (int)$r['id'];
                }

                // 4. Case-insensitive name match
                $s = $importPdo->prepare('SELECT id FROM ap_programs WHERE LOWER(name) = LOWER(?)');
                $s->execute([$raw]);
                $r = $s->fetch();
                return $r ? (int)$r['id'] : null;
            };

            $lineNumber = $headerRowNum;
            $usePeek     = isset($peekRow) && $peekRow !== null;
            while (true) {
                if ($usePeek) {
                    $row = $peekRow;
                    $usePeek = false;
                } else {
                    $row = fgetcsv($handle, 0, ',', '"', '');
                    if ($row === false) {
                        break;
                    }
                }
                $lineNumber++;
                if (empty($row[0]) && empty($row[1])) {
                    continue;
                }
                try {
                    $importDb->beginTransaction();

                    $identifier      = $cell($row, 'identifiant');
                    $title           = $cell($row, 'titre');
                    $subtitle        = $cell($row, 'sous-titre');
                    $authorsRaw      = $cell($row, 'auteur');
                    $contact         = $cell($row, 'contact');
                    // Normalise CSV artefacts: OUI/NON → empty (not a valid email)
                    if ($contact !== '' && in_array(strtoupper(trim($contact)), ['NON', 'OUI'], true)) {
                        $contact = '';
                    }
                    $supervisorsRaw       = $cell($row, 'promoteur');
                    $lecteursInternesRaw  = $cell($row, 'lecteur');   // first "lecteur" col = interne
                    $lecteursExternesRaw  = $cell($row, 'externe');   // contains "externe"
                    $promoteursUlbRaw     = $cell($row, 'ulb');       // contains "ulb"
                    $formatsRaw      = $cell($row, 'format');
                    $yearRaw         = $cell($row, 'année');
                    $year            = $yearRaw !== '' ? intval($yearRaw) : 0;
                    // Fallback: derive year from identifier (e.g. "2024-003" → 2024)
                    if ($year === 0 && $identifier !== '' && preg_match('/^(\d{4})-/', $identifier, $m)) {
                        $year = (int)$m[1];
                    }
                    $apCode          = $cell($row, 'ap');
                    $orientationCode = $cell($row, 'orientation');
                    $finalityName    = $cell($row, 'finalité');
                    $keywordsRaw     = $cell($row, 'mots-clés');
                    $synopsis        = $cell($row, 'synopsis');
                    $context         = $cell($row, 'contexte');
                    $remarks         = $cell($row, 'remarques');
                    $languageRaw     = $cell($row, 'langue');
                    $access          = $cell($row, 'autorisation');
                    $license         = $cell($row, 'licence');
                    $juryPointsRaw   = $cell($row, 'points');
                    $juryPoints      = $juryPointsRaw !== '' ? floatval($juryPointsRaw) : null;
                    $baiuLink        = $cell($row, 'lien baiu');
                    $cc2rRaw         = $cell($row, 'cc2r');
                    $cc2r            = $parseBool($cc2rRaw);
                    $exempBaiuRaw    = $cell($row, 'exemplaire');       // first "exemplaire" col = BAIU
                    $exempBaiu       = $parseBool($exempBaiuRaw);
                    $exempErgRaw     = $cell($row, 'exemplaire-erg');    // second "exemplaire", key won't match header → positional
                    $exempErg        = $parseBool($exempErgRaw);
                    $durationPagesRaw   = $cell($row, 'pages');
                    $durationPages      = $durationPagesRaw !== '' ? (int)$durationPagesRaw : null;
                    $durationMinutesRaw = $cell($row, 'durée');
                    $durationMinutes    = $durationMinutesRaw !== '' ? (int)$durationMinutesRaw : null;
                    $hasAnnexesRaw      = $cell($row, 'annexes');
                    $hasAnnexes         = $parseBool($hasAnnexesRaw);
                    $licenseCustom      = $cell($row, 'licence-perso');   // second "licence", key won't match header → positional
                    $contactVisible     = $cell($row, 'contact-visible'); // second "contact", key won't match header → positional
                    $objetRaw           = $cell($row, 'objet');
                    $objet              = in_array($objetRaw, ['tfe', 'thèse', 'frart'], true) ? $objetRaw : 'tfe';
                    $dateDepotRaw       = $cell($row, 'dépôt');
                    $dateDepot          = parseBulkDate($dateDepotRaw); // Brussels-local → UTC (Y-m-d H:i:s) or null

                    if ($title === '' || $year === 0) {
                        $missing = [];
                        if ($title === '') {
                            $missing[] = 'titre';
                        }
                        if ($year === 0) {
                            $missing[] = 'année';
                        }
                        throw new Exception('Champ(s) requis manquant(s) : ' . implode(', ', $missing)
                            . ' (id="' . ($identifier ?: '?') . '", titre="' . substr($title, 0, 80) . '")');
                    }

                    $orientationId = $resolveOrientation($orientationCode);
                    $apProgramId   = $resolveAP($apCode);

                    $finalityId = null;
                    if (!empty($finalityName)) {
                        $s = $importPdo->prepare('SELECT id FROM finality_types WHERE name = ?');
                        $s->execute([$finalityName]);
                        $r = $s->fetch();
                        $finalityId = $r ? $r['id'] : null;
                    }

                    $accessTypeId = null;
                    if (!empty($access)) {
                        $s = $importPdo->prepare('SELECT id FROM access_types WHERE name = ?');
                        $s->execute([ucfirst(strtolower($access))]);
                        $r = $s->fetch();
                        $accessTypeId = $r ? $r['id'] : null;
                    }
                    if ($accessTypeId === null) {
                        $accessTypeId = 1;
                    }

                    if (!empty($identifier)) {
                        $s = $importPdo->prepare('SELECT id FROM theses WHERE identifier = ?');
                        $s->execute([$identifier]);
                        if ($s->fetch()) {
                            $importDb->rollback();
                            $skippedCount++;
                            $importResults[] = ['type' => 'skip', 'msg' => "Ligne $lineNumber: identifiant \"$identifier\" déjà présent, ignoré."];
                            continue;
                        }
                    }

                    // Resolve license name → license_id
                    $licenseId = null;
                    if (!empty($license)) {
                        $ls = $importPdo->prepare('SELECT id FROM license_types WHERE LOWER(name) = LOWER(?)');
                        $ls->execute([trim($license)]);
                        $lr = $ls->fetch();
                        $licenseId = $lr ? (int)$lr['id'] : null;
                    }
                    // If a custom license string is provided and no DB license matched, store as custom
                    if ($licenseId === null && !empty($license) && empty($licenseCustom)) {
                        $licenseCustom = $license;
                    }

                    $s = $importPdo->prepare("
                        INSERT INTO theses (
                            identifier, title, subtitle, year,
                            orientation_id, ap_program_id, finality_id,
                            synopsis, context_note, remarks,
                            jury_points, baiu_link,
                            access_type_id, license_id, license_custom,
                            cc2r, exemplaire_baiu, exemplaire_erg,
                            objet, contact_visible,
                            duration_pages, duration_minutes, has_annexes,
                            is_published, status, submitted_at, date_depot
                        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,'active',CURRENT_TIMESTAMP,?)
                    ");
                    $s->execute([
                        !empty($identifier) ? $identifier : null, $title,
                        !empty($subtitle) ? $subtitle : null, $year,
                        $orientationId, $apProgramId, $finalityId,
                        !empty($synopsis) ? $synopsis : null,
                        !empty($context) ? $context : null,
                        !empty($remarks) ? $remarks : null,
                        $juryPoints,
                        !empty($baiuLink) ? $baiuLink : null,
                        $accessTypeId,
                        $licenseId,
                        !empty($licenseCustom) ? $licenseCustom : null,
                        $cc2r,
                        $exempBaiu,
                        $exempErg,
                        $objet,
                        !empty($contactVisible) ? $contactVisible : null,
                        $durationPages,
                        $durationMinutes,
                        $hasAnnexes,
                        $dateDepot,
                    ]);
                    $thesisId = $importPdo->lastInsertId();

                    if (!empty($authorsRaw)) {
                        foreach (array_map('trim', explode(',', $authorsRaw)) as $idx => $name) {
                            if ($name) {
                                $aId = $importDb->findOrCreateAuthor($name, $idx === 0 ? $contact : null);
                                $s = $importPdo->prepare('INSERT INTO thesis_authors (thesis_id, author_id, author_order) VALUES (?,?,?)');
                                $s->execute([$thesisId, $aId, $idx + 1]);
                            }
                        }
                    }
                    // Insert supervisors with proper role/is_external/is_ulb flags
                    $juryOrder = 0;
                    // Promoteurs internes
                    if (!empty($supervisorsRaw)) {
                        foreach (array_map('trim', explode(',', $supervisorsRaw)) as $name) {
                            if ($name) {
                                $juryOrder++;
                                $sId = $importDb->findOrCreateSupervisor($name);
                                $stmt = $importPdo->prepare('INSERT INTO thesis_supervisors (thesis_id, supervisor_id, role, is_external, is_ulb, supervisor_order) VALUES (?,?,?,?,?,?)');
                                $stmt->execute([$thesisId, $sId, 'promoteur', 0, 0, $juryOrder]);
                            }
                        }
                    }
                    // Lecteurs internes
                    if (!empty($lecteursInternesRaw)) {
                        foreach (array_map('trim', explode(',', $lecteursInternesRaw)) as $name) {
                            if ($name) {
                                $juryOrder++;
                                $sId = $importDb->findOrCreateSupervisor($name);
                                $stmt = $importPdo->prepare('INSERT INTO thesis_supervisors (thesis_id, supervisor_id, role, is_external, is_ulb, supervisor_order) VALUES (?,?,?,?,?,?)');
                                $stmt->execute([$thesisId, $sId, 'lecteur', 0, 0, $juryOrder]);
                            }
                        }
                    }
                    // Lecteurs externes
                    if (!empty($lecteursExternesRaw)) {
                        foreach (array_map('trim', explode(',', $lecteursExternesRaw)) as $name) {
                            if ($name) {
                                $juryOrder++;
                                $sId = $importDb->findOrCreateSupervisor($name);
                                $stmt = $importPdo->prepare('INSERT INTO thesis_supervisors (thesis_id, supervisor_id, role, is_external, is_ulb, supervisor_order) VALUES (?,?,?,?,?,?)');
                                $stmt->execute([$thesisId, $sId, 'lecteur', 1, 0, $juryOrder]);
                            }
                        }
                    }
                    // Promoteurs ULB
                    if (!empty($promoteursUlbRaw)) {
                        foreach (array_map('trim', explode(',', $promoteursUlbRaw)) as $name) {
                            if ($name) {
                                $juryOrder++;
                                $sId = $importDb->findOrCreateSupervisor($name);
                                $stmt = $importPdo->prepare('INSERT INTO thesis_supervisors (thesis_id, supervisor_id, role, is_external, is_ulb, supervisor_order) VALUES (?,?,?,?,?,?)');
                                $stmt->execute([$thesisId, $sId, 'promoteur', 1, 1, $juryOrder]);
                            }
                        }
                    }
                    if (!empty($keywordsRaw)) {
                        $normalizeTag = fn (string $t): string => strtolower(trim(preg_replace('/\s+/', ' ', $t)));
                        $tags = array_values(array_unique(array_map($normalizeTag, explode(',', $keywordsRaw))));
                        $tags = array_filter($tags, fn ($t) => $t !== '');
                        foreach (array_slice($tags, 0, 10) as $kw) {
                            $tId = $importDb->findOrCreateTag($kw);
                            if ($tId) {
                                $s = $importPdo->prepare('INSERT INTO thesis_tags (thesis_id, tag_id) VALUES (?,?)');
                                $s->execute([$thesisId, $tId]);
                            }
                        }
                    }
                    if (!empty($languageRaw)) {
                        foreach (array_map('trim', explode(',', $languageRaw)) as $langName) {
                            $langName = strtolower($langName);
                            if ($langName === '') {
                                continue;
                            }
                            // Lookup case-insensitively; insert if missing (stored lowercase).
                            $s = $importPdo->prepare('SELECT id FROM languages WHERE LOWER(name) = LOWER(?) AND deleted_at IS NULL');
                            $s->execute([$langName]);
                            $r = $s->fetch();
                            $langId = $r ? (int)$r['id'] : null;
                            if ($langId === null) {
                                $importPdo->prepare('INSERT INTO languages (name) VALUES (?)')->execute([$langName]);
                                $langId = (int)$importPdo->lastInsertId();
                            }
                            $s2 = $importPdo->prepare('INSERT INTO thesis_languages (thesis_id, language_id) VALUES (?,?)');
                            $s2->execute([$thesisId, $langId]);
                        }
                    }
                    if (!empty($formatsRaw)) {
                        foreach (array_map('trim', explode(',', $formatsRaw)) as $fmt) {
                            if ($fmt) {
                                $s = $importPdo->prepare('SELECT id FROM format_types WHERE name = ?');
                                $s->execute([ucfirst(strtolower($fmt))]);
                                $r = $s->fetch();
                                if ($r) {
                                    $s2 = $importPdo->prepare('INSERT INTO thesis_formats (thesis_id, format_id) VALUES (?,?)');
                                    $s2->execute([$thesisId, $r['id']]);
                                }
                            }
                        }
                    }

                    $importDb->commit();
                    $importedCount++;
                    $importResults[] = ['type' => 'ok', 'msg' => "\"$title\" (ID: $thesisId)"];

                } catch (Exception $e) {
                    $importDb->rollback();
                    $skippedCount++;
                    $importResults[] = ['type' => 'error', 'msg' => "Ligne $lineNumber: " . $e->getMessage()];
                    error_log("Import error on line $lineNumber: " . $e->getMessage());
                }
            }
            fclose($handle);
            $importMessage = "Import terminé : $importedCount TFE importés, $skippedCount ignorés.";
            $importDone = true;
        } catch (Exception $e) {
            $importErrors[] = $e->getMessage();
            error_log('CSV import error: ' . $e->getMessage());
        }
    }
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

try {
    $db = new Database();
    $searchQuery       = isset($_GET['search']) ? trim($_GET['search']) : '';
    $yearFilter        = isset($_GET['year']) ? intval($_GET['year']) : null;
    $orientationFilter = isset($_GET['orientation']) ? intval($_GET['orientation']) : null;
    $apFilter          = isset($_GET['ap']) ? intval($_GET['ap']) : null;

    $sortCol  = isset($_GET['sort']) ? trim($_GET['sort']) : 'submitted_at';
    $sortDir  = isset($_GET['dir']) ? trim($_GET['dir']) : 'desc';

    $filters = [];
    if ($searchQuery) {
        $filters['search']      = $searchQuery;
    }
    if ($yearFilter) {
        $filters['year']        = $yearFilter;
    }
    if ($orientationFilter) {
        $filters['orientation'] = $orientationFilter;
    }
    if ($apFilter) {
        $filters['ap']          = $apFilter;
    }
    $filters['sort'] = $sortCol;
    $filters['dir']  = $sortDir;

    $theses       = $db->getThesesList($filters, 0, 0);
    $totalCount   = count($theses);
    $stats        = $db->getThesesStats();
    $years        = $db->getAllYears();
    $orientations = $db->getAllOrientations();
    $apPrograms   = $db->getAllAPPrograms();
    $trashCount   = $db->countTrashedTheses();
    $tab          = $_GET['tab'] ?? 'list';
    $trashedTheses = ($tab === 'trash') ? $db->getTrashedTheses() : [];

    // ── Tmp file stats ───────────────────────────────────────────────────
    // Abandoned FilePond uploads are purged automatically by cron, so the badge
    // only reflects the manually-managed trash.
    $trashDir    = STORAGE_ROOT . '/tmp/_trash';
    $tmpTrashCount    = 0;
    $tmpTotalCount    = 0;
    if (is_dir($trashDir)) {
        $items = @scandir($trashDir);
        if ($items !== false) {
            foreach ($items as $item) {
                if ($item !== '.' && $item !== '..') {
                    $tmpTrashCount++;
                }
            }
        }
    }
    $tmpTotalCount = $tmpTrashCount;
} catch (Exception $e) {
    error_log('Error loading theses list: ' . $e->getMessage());
    die('Erreur lors du chargement de la liste.');
}

$isHtmx = ($_SERVER['HTTP_HX_REQUEST'] ?? '') === 'true';
$isAdmin = true;
$bodyClass = 'admin-body';
if ($isHtmx) {
    if ($tab === 'trash') {
        include APP_ROOT . '/templates/admin/index-trash.php';
    } else {
        include APP_ROOT . '/templates/admin/index-table.php';
    }
} else {
    $extraCssAdmin = ['/assets/css/filepond.min.css', '/assets/css/filepond-plugin-image-preview.min.css'];
    $extraJs  = ['/assets/js/vendor/filepond.min.js', '/assets/js/vendor/filepond-plugin-file-validate-type.min.js', '/assets/js/vendor/filepond-plugin-file-validate-size.min.js', '/assets/js/vendor/filepond-plugin-image-preview.min.js', '/assets/js/vendor/filepond-plugin-image-exif-orientation.min.js', '/assets/dist/admin.min.js', '/assets/js/app/file-upload-filepond.js'];
    require_once APP_ROOT . '/templates/head.php';
    include APP_ROOT . '/templates/header.php';
    if ($tab === 'trash') {
        include APP_ROOT . '/templates/admin/index-trash.php';
    } else {
        include APP_ROOT . '/templates/admin/index.php';
    }
    require_once APP_ROOT . '/templates/admin/footer.php';
}