<?php

use PHPUnit\Framework\TestCase;

/**
 * DatabaseExtendedTest — Integration tests for Database methods that require
 * a real SQLite DB but are pure-logic-like in nature.
 *
 * Each test resets data via TestDatabase::resetData() then seeds what it needs.
 */
class DatabaseExtendedTest extends TestCase
{
    private Database $db;

    protected function setUp(): void
    {
        TestDatabase::resetData();
        $this->db = TestDatabase::getInstance();
    }

    // ── escapeLikeString (private, tested via buildSearchConditions) ──────────

    public function testEscapeLikeStringViaSearchConditions(): void
    {
        // Build conditions with special LIKE characters in query
        // Use reflection to call the private method
        $ref = new ReflectionMethod(Database::class, 'escapeLikeString');

        $this->assertSame('\\\\', $ref->invoke($this->db, '\\'));
        $this->assertSame('\\%', $ref->invoke($this->db, '%'));
        $this->assertSame('\\_', $ref->invoke($this->db, '_'));
        $this->assertSame('hello', $ref->invoke($this->db, 'hello'));
        $this->assertSame('50\\% off\\_sale \\\\done', $ref->invoke($this->db, '50% off_sale \\done'));
    }

    // ── buildSearchConditions (private, tested via reflection) ────────────────

    public function testBuildSearchConditionsEmptyParams(): void
    {
        $ref = new ReflectionMethod(Database::class, 'buildSearchConditions');

        [$conditions, $bindings] = $ref->invoke($this->db, []);

        $this->assertCount(1, $conditions);
        $this->assertStringContainsString('is_published', $conditions[0]);
        $this->assertEmpty($bindings);
    }

    public function testBuildSearchConditionsWithQuery(): void
    {
        $ref = new ReflectionMethod(Database::class, 'buildSearchConditions');

        [$conditions, $bindings] = $ref->invoke($this->db, ['query' => 'test']);

        $this->assertGreaterThan(1, count($conditions));
        $this->assertArrayHasKey(':query', $bindings);
        $this->assertStringContainsString('%test%', $bindings[':query']);
    }

    public function testBuildSearchConditionsWithYear(): void
    {
        $ref = new ReflectionMethod(Database::class, 'buildSearchConditions');

        [$conditions, $bindings] = $ref->invoke($this->db, ['year' => 2024]);

        $this->assertContains('vp.year = :year', $conditions);
        $this->assertSame(2024, $bindings[':year']);
    }

    public function testBuildSearchConditionsWithAllFilters(): void
    {
        $ref = new ReflectionMethod(Database::class, 'buildSearchConditions');

        [$conditions, $bindings] = $ref->invoke($this->db, [
            'query'       => 'art',
            'year'        => 2023,
            'orientation' => 'BD',
            'keyword'     => 'design',
            'language'    => 'français',
            'format'      => 'Vidéo',
        ]);

        $this->assertGreaterThan(5, count($conditions));
        $this->assertArrayHasKey(':query', $bindings);
        $this->assertArrayHasKey(':year', $bindings);
        $this->assertArrayHasKey(':orientation', $bindings);
        $this->assertArrayHasKey(':keyword', $bindings);
        $this->assertArrayHasKey(':language', $bindings);
        $this->assertArrayHasKey(':format', $bindings);
    }

    // ── findDuplicateThesis ───────────────────────────────────────────────────

    public function testFindDuplicateThesisExactMatch(): void
    {
        [$authorId] = TestDatabase::seedBasicThesis('My Unique Thesis', 'Jane Doe', 2024);

        $dup = $this->db->findDuplicateThesis('My Unique Thesis', ['Jane Doe'], 2024);
        $this->assertNotNull($dup);
        $this->assertSame(2024, $dup['year']);
        $this->assertStringContainsString('My Unique Thesis', $dup['title']);
    }

    public function testFindDuplicateThesisMissesDifferentTitle(): void
    {
        [$authorId] = TestDatabase::seedBasicThesis('Thesis Alpha', 'John Smith', 2024);

        $dup = $this->db->findDuplicateThesis('Completely Different', ['John Smith'], 2024);
        $this->assertNull($dup);
    }

    public function testFindDuplicateThesisMissesDifferentYear(): void
    {
        [$authorId] = TestDatabase::seedBasicThesis('Shared Title', 'Alice', 2023);

        $dup = $this->db->findDuplicateThesis('Shared Title', ['Alice'], 2024);
        $this->assertNull($dup);
    }

    public function testFindDuplicateThesisEmptyAuthorNamesReturnsNull(): void
    {
        TestDatabase::seedBasicThesis('Test', 'Author', 2024);

        $dup = $this->db->findDuplicateThesis('Test', [], 2024);
        $this->assertNull($dup);
    }

    public function testFindDuplicateThesisEmptyTable(): void
    {
        $dup = $this->db->findDuplicateThesis('Anything', ['Anyone'], 2024);
        $this->assertNull($dup);
    }

    public function testFindDuplicateThesisNearDuplicateByLevenshtein(): void
    {
        TestDatabase::seedBasicThesis('My Thesis About Art', 'Bob', 2024);

        // One character typo should match via Levenshtein
        $dup = $this->db->findDuplicateThesis('My Thesis About Arty', ['Bob'], 2024);
        $this->assertNotNull($dup);
    }

    // ── generateThesisIdentifier ──────────────────────────────────────────────

    public function testGenerateThesisIdentifierFirstInYear(): void
    {
        $id = $this->db->generateThesisIdentifier(2025);
        $this->assertSame('2025-001', $id);
    }

    public function testGenerateThesisIdentifierIncrementsCorrectly(): void
    {
        // Insert a thesis with identifier 2025-001
        $pdo = TestDatabase::getPDO();
        $pdo->prepare("INSERT INTO theses (title, year, identifier, objet) VALUES (?, ?, ?, 'tfe')")
            ->execute(['First', 2025, '2025-001']);

        $id = $this->db->generateThesisIdentifier(2025);
        $this->assertSame('2025-002', $id);
    }

    public function testGenerateThesisIdentifierUsesMaxNotCount(): void
    {
        $pdo = TestDatabase::getPDO();

        // Insert 2025-001, then "delete" it (set deleted_at), insert 2025-005
        $pdo->prepare("INSERT INTO theses (title, year, identifier, objet, deleted_at) VALUES (?, ?, ?, 'tfe', datetime('now'))")
            ->execute(['Deleted', 2025, '2025-001']);
        $pdo->prepare("INSERT INTO theses (title, year, identifier, objet) VALUES (?, ?, ?, 'tfe')")
            ->execute(['Alive', 2025, '2025-005']);

        $id = $this->db->generateThesisIdentifier(2025);
        // MAX is 5, so next should be 6 (not 3 from COUNT)
        $this->assertSame('2025-006', $id);
    }

    // ── getCoverPathsForTheses ────────────────────────────────────────────────

    public function testGetCoverPathsForThesesReturnsPaths(): void
    {
        [$authorId, $thesisId] = TestDatabase::seedBasicThesis('Cover Test', 'Author', 2024);

        $paths = $this->db->getCoverPathsForTheses([$thesisId]);

        $this->assertArrayHasKey($thesisId, $paths);
        $this->assertStringContainsString('cover.jpg', $paths[$thesisId]);
    }

    public function testGetCoverPathsForThesesReturnsEmptyForUnknownIds(): void
    {
        $paths = $this->db->getCoverPathsForTheses([999]);
        $this->assertEmpty($paths);
    }

    public function testGetCoverPathsForThesesEmptyInputReturnsEmpty(): void
    {
        $paths = $this->db->getCoverPathsForTheses([]);
        $this->assertEmpty($paths);
    }

    public function testGetCoverPathsForThesesMultipleTheses(): void
    {
        [$a1, $t1] = TestDatabase::seedBasicThesis('T1', 'A1', 2024);
        [$a2, $t2] = TestDatabase::seedBasicThesis('T2', 'A2', 2025);

        $paths = $this->db->getCoverPathsForTheses([$t1, $t2]);

        $this->assertCount(2, $paths);
        $this->assertArrayHasKey($t1, $paths);
        $this->assertArrayHasKey($t2, $paths);
    }

    // ── findOrCreateAuthor ────────────────────────────────────────────────────

    public function testFindOrCreateAuthorCreatesNew(): void
    {
        $id = $this->db->findOrCreateAuthor('New Author');
        $this->assertGreaterThan(0, (int)$id);
    }

    public function testFindOrCreateAuthorIdempotent(): void
    {
        $id1 = $this->db->findOrCreateAuthor('Same Author');
        $id2 = $this->db->findOrCreateAuthor('Same Author');

        $this->assertEquals($id1, $id2);
    }

    public function testFindOrCreateAuthorWithEmail(): void
    {
        $id1 = $this->db->findOrCreateAuthor('Email Author', 'test@example.com');
        $id2 = $this->db->findOrCreateAuthor('Different Name', 'test@example.com');

        // Same email → should return the same author ID
        $this->assertEquals($id1, $id2);
    }

    public function testFindOrCreateAuthorRejectsCSVArtefacts(): void
    {
        // 'NON' and 'OUI' are treated as null emails
        $id1 = $this->db->findOrCreateAuthor('CSV Author', 'NON');
        $id2 = $this->db->findOrCreateAuthor('CSV Author', 'OUI');

        // Same name, no email → same author
        $this->assertEquals($id1, $id2);
    }

    // ── Language operations ───────────────────────────────────────────────────

    public function testDeduplicateLanguagesMergesCaseInsensitiveDupes(): void
    {
        $pdo = TestDatabase::getPDO();

        // Count seed languages first (français, anglais, néerlandais, italian)
        $seedCount = (int)$pdo->query('SELECT COUNT(*) FROM languages WHERE deleted_at IS NULL')->fetchColumn();

        // Insert two languages that differ only by case
        $pdo->prepare("INSERT INTO languages (name) VALUES ('TestLang')")->execute();
        $pdo->prepare("INSERT INTO languages (name) VALUES ('testlang')")->execute();

        // Both seed + 2 new should exist before dedup
        $before = $pdo->query('SELECT COUNT(*) FROM languages WHERE deleted_at IS NULL')->fetchColumn();
        $this->assertSame($seedCount + 2, (int)$before);

        $this->db->deduplicateLanguages();

        // One of the dupes should be soft-deleted
        $after = $pdo->query('SELECT COUNT(*) FROM languages WHERE deleted_at IS NULL')->fetchColumn();
        $this->assertSame($seedCount + 1, (int)$after);
    }

    public function testRenameLanguageUpdatesName(): void
    {
        $pdo = TestDatabase::getPDO();
        $pdo->prepare("INSERT INTO languages (name) VALUES ('OldName')")->execute();
        $langId = (int)$pdo->lastInsertId();

        $this->db->renameLanguage($langId, 'NewName');

        $name = $pdo->query("SELECT name FROM languages WHERE id = $langId")->fetchColumn();
        $this->assertSame('NewName', $name);
    }

    public function testMergeLanguageReassignsTheses(): void
    {
        $pdo = TestDatabase::getPDO();

        // Create two languages
        $pdo->prepare("INSERT INTO languages (name) VALUES ('French')")->execute();
        $frenchId = (int)$pdo->lastInsertId();
        $pdo->prepare("INSERT INTO languages (name) VALUES ('Français')")->execute();
        $francaisId = (int)$pdo->lastInsertId();

        // Create a thesis linked to 'Français'
        [$authorId, $thesisId] = TestDatabase::seedBasicThesis('Merge Test', 'Author', 2024);
        $pdo->prepare('INSERT INTO thesis_languages (thesis_id, language_id) VALUES (?, ?)')
            ->execute([$thesisId, $francaisId]);

        // Merge Français → French
        $this->db->mergeLanguage($francaisId, $frenchId);

        // Check the thesis is now linked to French
        $links = $pdo->query("SELECT language_id FROM thesis_languages WHERE thesis_id = $thesisId")->fetchAll(PDO::FETCH_COLUMN);
        $this->assertContains($frenchId, array_map('intval', $links));
        $this->assertNotContains($francaisId, array_map('intval', $links));

        // Source language should be soft-deleted
        $deleted = $pdo->query("SELECT deleted_at FROM languages WHERE id = $francaisId")->fetchColumn();
        $this->assertNotNull($deleted);
    }

    // ── Tag operations ────────────────────────────────────────────────────────

    public function testRenameTagUpdatesName(): void
    {
        $pdo = TestDatabase::getPDO();
        $pdo->prepare("INSERT INTO tags (name) VALUES ('OldTag')")->execute();
        $tagId = (int)$pdo->lastInsertId();

        $this->db->renameTag($tagId, 'NewTag');

        $name = $pdo->query("SELECT name FROM tags WHERE id = $tagId")->fetchColumn();
        $this->assertSame('NewTag', $name);
    }

    public function testMergeTagReassignsTheses(): void
    {
        $pdo = TestDatabase::getPDO();

        $pdo->prepare("INSERT INTO tags (name) VALUES ('TagA')")->execute();
        $tagA = (int)$pdo->lastInsertId();
        $pdo->prepare("INSERT INTO tags (name) VALUES ('TagB')")->execute();
        $tagB = (int)$pdo->lastInsertId();

        [$authorId, $thesisId] = TestDatabase::seedBasicThesis('Tag Merge', 'Author', 2024);
        $pdo->prepare('INSERT INTO thesis_tags (tag_id, thesis_id) VALUES (?, ?)')
            ->execute([$tagB, $thesisId]);

        $this->db->mergeTag($tagB, $tagA);

        $links = $pdo->query("SELECT tag_id FROM thesis_tags WHERE thesis_id = $thesisId")->fetchAll(PDO::FETCH_COLUMN);
        $this->assertContains($tagA, array_map('intval', $links));
        $this->assertNotContains($tagB, array_map('intval', $links));

        $deleted = $pdo->query("SELECT deleted_at FROM tags WHERE id = $tagB")->fetchColumn();
        $this->assertNotNull($deleted);
    }
}