<?php
/**
 * Script to create a test database with sample data
 * Run this script once to set up test.db for development
 */

$dbPath = __DIR__ . '/../test.db';

// Remove existing database if it exists
if (file_exists($dbPath)) {
    unlink($dbPath);
    echo "Removed existing test database\n";
}

try {
    // Create database connection
    $pdo = new PDO('sqlite:' . $dbPath);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Created new database: $dbPath\n";

    // Read and execute schema
    $schemaPath = __DIR__ . '/../schema.sql';
    if (!file_exists($schemaPath)) {
        throw new Exception("Schema file not found: $schemaPath");
    }

    $schema = file_get_contents($schemaPath);
    $pdo->exec($schema);
    echo "Schema created successfully\n";

    // Insert sample authors
    $authors = [
        ['name' => 'Marie Dubois', 'email' => 'marie.dubois@example.com'],
        ['name' => 'Jean Martin', 'email' => 'jean.martin@example.com'],
        ['name' => 'Sophie Bernard', 'email' => 'sophie.bernard@example.com'],
        ['name' => 'Lucas Petit', 'email' => 'lucas.petit@example.com'],
        ['name' => 'Emma Leroy', 'email' => 'emma.leroy@example.com'],
    ];

    foreach ($authors as $author) {
        $stmt = $pdo->prepare("INSERT INTO authors (name, email) VALUES (:name, :email)");
        $stmt->execute($author);
    }
    echo "Inserted " . count($authors) . " sample authors\n";

    // Insert sample supervisors
    $supervisors = [
        ['name' => 'Prof. Claire Fontaine'],
        ['name' => 'Dr. Thomas Moreau'],
        ['name' => 'Prof. Anne Laurent'],
    ];

    foreach ($supervisors as $supervisor) {
        $stmt = $pdo->prepare("INSERT INTO supervisors (name) VALUES (:name)");
        $stmt->execute($supervisor);
    }
    echo "Inserted " . count($supervisors) . " sample supervisors\n";

    // Insert sample tags (formerly keywords)
    $sampleKeywords = [
        'spéculation', 'narration', 'urbanisme', 'patrimoine', 'intime',
        'collectivité', 'film', 'cinéma', 'sociologie', 'anthropologie',
        'éphémérité', 'queer', 'écriture', 'poésie', 'écologie',
        'technologies', 'design', 'performance', 'installation', 'art numérique'
    ];

    foreach ($sampleKeywords as $keyword) {
        $stmt = $pdo->prepare("INSERT INTO tags (name) VALUES (:name)");
        $stmt->execute(['name' => $keyword]);
    }
    echo "Inserted " . count($sampleKeywords) . " sample tags\n";

    // Insert sample theses
    $theses = [
        [
            'identifier' => '2024-001',
            'title' => 'Espaces Urbains et Narration Collective',
            'subtitle' => 'Une exploration des récits de la ville',
            'year' => 2024,
            'is_doctoral' => 0,
            'orientation_id' => 1, // Arts Numériques
            'ap_program_id' => 1, // Narration Spéculative
            'finality_id' => 1, // Approfondi
            'synopsis' => 'Ce travail explore la manière dont les espaces urbains génèrent des récits collectifs. À travers une série d\'installations vidéo et sonores, je documente les histoires cachées des quartiers en transformation. Le projet interroge la mémoire collective et la façon dont l\'architecture influence nos récits personnels et communautaires.',
            'access_type_id' => 1, // Libre
            'is_published' => 1,
        ],
        [
            'identifier' => '2024-002',
            'title' => 'Corps et Technologies',
            'subtitle' => 'Interfaces sensorielles',
            'year' => 2024,
            'is_doctoral' => 0,
            'orientation_id' => 4, // Installation-Performance
            'ap_program_id' => 2, // Design et Politique du Multiple
            'finality_id' => 1, // Approfondi
            'synopsis' => 'Cette recherche artistique examine la relation entre le corps humain et les technologies numériques. À travers des performances interactives, j\'explore comment les interfaces technologiques transforment notre perception corporelle et créent de nouvelles formes de présence. Le projet questionne l\'hybridation entre organique et numérique.',
            'access_type_id' => 1, // Libre
            'is_published' => 1,
        ],
        [
            'identifier' => '2024-003',
            'title' => 'Poétiques du Quotidien',
            'subtitle' => NULL,
            'year' => 2024,
            'is_doctoral' => 0,
            'orientation_id' => 6, // Photographie
            'ap_program_id' => 3, // Atelier Pratiques Situées
            'finality_id' => 2, // Enseignement
            'synopsis' => 'Ce projet photographique documente les gestes ordinaires et les moments éphémères du quotidien. En utilisant une approche documentaire mêlée de fiction, je cherche à révéler la poésie cachée dans les rituels banals. Le travail questionne notre rapport au temps et à l\'attention dans un monde accéléré.',
            'access_type_id' => 1, // Libre
            'is_published' => 1,
        ],
        [
            'identifier' => '2023-015',
            'title' => 'Écologies Affectives',
            'subtitle' => 'Cartographies sensibles des liens',
            'year' => 2023,
            'is_doctoral' => 0,
            'orientation_id' => 9, // Graphisme
            'ap_program_id' => 4, // LIENS
            'finality_id' => 1, // Approfondi
            'synopsis' => 'Ce travail de design graphique développe une méthodologie visuelle pour cartographier les relations affectives et les réseaux de soin. À travers des visualisations de données sensibles et des éditions expérimentales, le projet explore comment représenter l\'invisible des liens humains et des solidarités.',
            'access_type_id' => 1, // Libre
            'is_published' => 1,
        ],
        [
            'identifier' => '2023-020',
            'title' => 'Mémoires Spéculatives',
            'subtitle' => 'Archives du futur',
            'year' => 2023,
            'is_doctoral' => 0,
            'orientation_id' => 10, // Typographie
            'ap_program_id' => 1, // Narration Spéculative
            'finality_id' => 3, // Spécialisé
            'synopsis' => 'Un projet éditorial qui imagine des archives futures à partir de traces présentes. En utilisant la typographie comme outil de spéculation temporelle, je crée des documents fictionnels qui interrogent notre rapport à l\'histoire et à la transmission. Le travail questionne la matérialité de la mémoire.',
            'access_type_id' => 2, // Interne
            'is_published' => 1,
        ],
        [
            'identifier' => '2025-002',
            'title' => 'Performance et Politique du Geste',
            'subtitle' => NULL,
            'year' => 2025,
            'is_doctoral' => 0,
            'orientation_id' => 4, // Installation-Performance
            'ap_program_id' => 3, // Atelier Pratiques Situées
            'finality_id' => 1, // Approfondi
            'synopsis' => 'Cette recherche performative explore la dimension politique des gestes quotidiens. À travers une série de performances filmées, j\'examine comment les micro-actions peuvent constituer des formes de résistance. Le projet s\'intéresse aux corps en mouvement et à leur capacité à transformer l\'espace public.',
            'access_type_id' => 1, // Libre
            'is_published' => 1,
        ],
    ];

    foreach ($theses as $thesis) {
        $columns = implode(', ', array_keys($thesis));
        $placeholders = ':' . implode(', :', array_keys($thesis));
        $stmt = $pdo->prepare("INSERT INTO theses ($columns) VALUES ($placeholders)");
        $stmt->execute($thesis);
    }
    echo "Inserted " . count($theses) . " sample theses\n";

    // Link authors to theses
    $thesisAuthors = [
        ['thesis_id' => 1, 'author_id' => 1, 'author_order' => 1],
        ['thesis_id' => 2, 'author_id' => 2, 'author_order' => 1],
        ['thesis_id' => 3, 'author_id' => 3, 'author_order' => 1],
        ['thesis_id' => 4, 'author_id' => 4, 'author_order' => 1],
        ['thesis_id' => 5, 'author_id' => 5, 'author_order' => 1],
        ['thesis_id' => 6, 'author_id' => 1, 'author_order' => 1],
    ];

    foreach ($thesisAuthors as $link) {
        $stmt = $pdo->prepare("INSERT INTO thesis_authors (thesis_id, author_id, author_order) VALUES (:thesis_id, :author_id, :author_order)");
        $stmt->execute($link);
    }
    echo "Linked authors to theses\n";

    // Link supervisors to theses
    $thesisSupervisors = [
        ['thesis_id' => 1, 'supervisor_id' => 1, 'supervisor_order' => 1],
        ['thesis_id' => 2, 'supervisor_id' => 2, 'supervisor_order' => 1],
        ['thesis_id' => 3, 'supervisor_id' => 1, 'supervisor_order' => 1],
        ['thesis_id' => 4, 'supervisor_id' => 3, 'supervisor_order' => 1],
        ['thesis_id' => 5, 'supervisor_id' => 2, 'supervisor_order' => 1],
        ['thesis_id' => 6, 'supervisor_id' => 3, 'supervisor_order' => 1],
    ];

    foreach ($thesisSupervisors as $link) {
        $stmt = $pdo->prepare("INSERT INTO thesis_supervisors (thesis_id, supervisor_id, supervisor_order) VALUES (:thesis_id, :supervisor_id, :supervisor_order)");
        $stmt->execute($link);
    }
    echo "Linked supervisors to theses\n";

    // Link tags to theses (thesis_tags junction)
    $thesisKeywords = [
        ['thesis_id' => 1, 'tag_id' => 3], // urbanisme
        ['thesis_id' => 1, 'tag_id' => 2], // narration
        ['thesis_id' => 1, 'tag_id' => 6], // collectivité
        ['thesis_id' => 2, 'tag_id' => 16], // technologies
        ['thesis_id' => 2, 'tag_id' => 18], // performance
        ['thesis_id' => 2, 'tag_id' => 20], // art numérique
        ['thesis_id' => 3, 'tag_id' => 14], // poésie
        ['thesis_id' => 3, 'tag_id' => 11], // éphémérité
        ['thesis_id' => 3, 'tag_id' => 5], // intime
        ['thesis_id' => 4, 'tag_id' => 15], // écologie
        ['thesis_id' => 4, 'tag_id' => 17], // design
        ['thesis_id' => 5, 'tag_id' => 1], // spéculation
        ['thesis_id' => 5, 'tag_id' => 4], // patrimoine
        ['thesis_id' => 6, 'tag_id' => 18], // performance
        ['thesis_id' => 6, 'tag_id' => 9], // sociologie
    ];

    foreach ($thesisKeywords as $link) {
        $stmt = $pdo->prepare("INSERT OR IGNORE INTO thesis_tags (tag_id, thesis_id) VALUES (:tag_id, :thesis_id)");
        $stmt->execute($link);
    }
    echo "Linked tags to theses\n";

    // Link languages to theses (all in French)
    for ($i = 1; $i <= 6; $i++) {
        $stmt = $pdo->prepare("INSERT INTO thesis_languages (thesis_id, language_id) VALUES (:thesis_id, 1)");
        $stmt->execute(['thesis_id' => $i]);
    }
    echo "Linked languages to theses\n";

    // Link formats to theses
    $thesisFormats = [
        ['thesis_id' => 1, 'format_id' => 3], // Vidéo
        ['thesis_id' => 1, 'format_id' => 6], // Installation
        ['thesis_id' => 2, 'format_id' => 4], // Performance
        ['thesis_id' => 3, 'format_id' => 5], // Objet éditorial
        ['thesis_id' => 4, 'format_id' => 1], // Site web
        ['thesis_id' => 4, 'format_id' => 5], // Objet éditorial
        ['thesis_id' => 5, 'format_id' => 5], // Objet éditorial
        ['thesis_id' => 6, 'format_id' => 4], // Performance
        ['thesis_id' => 6, 'format_id' => 3], // Vidéo
    ];

    foreach ($thesisFormats as $link) {
        $stmt = $pdo->prepare("INSERT INTO thesis_formats (thesis_id, format_id) VALUES (:thesis_id, :format_id)");
        $stmt->execute($link);
    }
    echo "Linked formats to theses\n";

    echo "\n✅ Test database created successfully!\n";
    echo "Database location: $dbPath\n";
    echo "\nYou can now test the search feature at: http://localhost/front-backend/repertoire.php\n";

} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    exit(1);
}
