/**
 * TestUtilities
 * Creates and resets the PostgreSQL test schema used by backend integration and
 * repository tests, including helper seed data for common atlas fixtures.
 */
const { Pool } = require('pg');

class TestUtilities {
    constructor() {
        this.pool = new Pool({
            user: 'postgres',
            host: 'localhost',
            database: 'postgres_test',
            password: 'betka',
            port: 5432,
        });
    }

    async setupTestDatabase() {
        // Drop tables if they exist
        await this.pool.query(`
            DROP TABLE IF EXISTS model_trait_selections CASCADE;
            DROP TABLE IF EXISTS annotations CASCADE;
            DROP TABLE IF EXISTS models CASCADE;
            DROP TABLE IF EXISTS trait_options CASCADE;
            DROP TABLE IF EXISTS traits CASCADE;
            DROP TABLE IF EXISTS trait_categories CASCADE;
            DROP TABLE IF EXISTS atlases CASCADE;
            DROP TABLE IF EXISTS user_contributed_images CASCADE;
        `);

        // Create tables
        await this.pool.query(`
            CREATE TABLE IF NOT EXISTS atlases (
                id TEXT PRIMARY KEY,
                nazev_cs TEXT NOT NULL,
                nazev_en TEXT NOT NULL,
                popis_cs TEXT,
                popis_en TEXT,
                je_vychozi BOOLEAN DEFAULT false,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS trait_categories (
                id SERIAL PRIMARY KEY,
                atlas_id TEXT REFERENCES atlases(id) ON DELETE CASCADE,
                nazev_cs TEXT NOT NULL,
                nazev_en TEXT NOT NULL,
                poradi INT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS traits (
                id TEXT PRIMARY KEY,
                category_id INT REFERENCES trait_categories(id) ON DELETE CASCADE,
                label_cs TEXT NOT NULL,
                label_en TEXT NOT NULL,
                popis_cs TEXT,
                popis_en TEXT
            );

            CREATE TABLE IF NOT EXISTS trait_options (
                id SERIAL PRIMARY KEY,
                trait_id TEXT REFERENCES traits(id) ON DELETE CASCADE,
                label_cs TEXT NOT NULL,
                label_en TEXT NOT NULL,
                images TEXT[],
                drawings TEXT[]
            );

            CREATE TABLE IF NOT EXISTS models (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                external_id TEXT NOT NULL,
                file_hash TEXT UNIQUE,
                model_path TEXT,
                mtl_path TEXT,
                texture_path TEXT,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS annotations (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                model_id UUID REFERENCES models(id),
                annotator_name TEXT NOT NULL,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                atlas_id TEXT REFERENCES atlases(id),
                is_checked BOOLEAN DEFAULT FALSE,
                checker_name TEXT
            );

            CREATE TABLE IF NOT EXISTS model_trait_selections (
                annotation_id UUID REFERENCES annotations(id) ON DELETE CASCADE,
                trait_id TEXT REFERENCES traits(id) ON DELETE CASCADE,
                option_id INT REFERENCES trait_options(id) ON DELETE CASCADE,
                PRIMARY KEY (annotation_id, trait_id)
            );

            CREATE TABLE IF NOT EXISTS user_contributed_images (
                id SERIAL PRIMARY KEY,
                option_id INT REFERENCES trait_options(id) ON DELETE CASCADE,
                image_path TEXT NOT NULL,
                is_drawing BOOLEAN DEFAULT false,
                original_filename TEXT,
                image_hash TEXT,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_annotations_model_id ON annotations(model_id);
            CREATE INDEX IF NOT EXISTS idx_selections_annotation_id ON model_trait_selections(annotation_id);
            CREATE INDEX IF NOT EXISTS idx_user_images_option_id ON user_contributed_images(option_id);
        `);
    }

    async insertTestData() {
        // Insert test atlas
        await this.pool.query(`
            INSERT INTO atlases (id, nazev_cs, nazev_en, popis_cs, popis_en, je_vychozi) VALUES
            ('test-test-atlas', 'Test Atlas', 'Test Atlas EN', 'Popis', 'Description', true)
        `);

        // Insert test category
        const catRes = await this.pool.query(`
            INSERT INTO trait_categories (atlas_id, nazev_cs, nazev_en, poradi) VALUES
            ('test-test-atlas', 'Kategorie 1', 'Category 1', 1) RETURNING id
        `);
        const categoryId = catRes.rows[0].id;

        // Insert test trait
        await this.pool.query(`
            INSERT INTO traits (id, category_id, label_cs, label_en, popis_cs, popis_en) VALUES
            ('test_trait', $1, 'Znak 1', 'Trait 1', 'Popis znaku', 'Trait description')
        `, [categoryId]);

        // Insert test options
        await this.pool.query(`
            INSERT INTO trait_options (trait_id, label_cs, label_en, images, drawings) VALUES
            ('test_trait', 'Možnost 1', 'Option 1', ARRAY['/images/1.jpg'], ARRAY['/drawings/1.jpg']),
            ('test_trait', 'Možnost 2', 'Option 2', ARRAY['/images/2.jpg'], ARRAY['/drawings/2.jpg'])
        `);

        // Insert test model
        await this.pool.query(`
            INSERT INTO models (external_id, file_hash, model_path, mtl_path, texture_path) VALUES
            ('test_model', 'testhash123', '/models/test.obj', '/models/test.mtl', '/models/test.jpg')
        `);
    }

    async teardownTestDatabase() {
        await this.pool.query(`
            DROP TABLE IF EXISTS model_trait_selections CASCADE;
            DROP TABLE IF EXISTS annotations CASCADE;
            DROP TABLE IF EXISTS models CASCADE;
            DROP TABLE IF EXISTS trait_options CASCADE;
            DROP TABLE IF EXISTS traits CASCADE;
            DROP TABLE IF EXISTS trait_categories CASCADE;
            DROP TABLE IF EXISTS atlases CASCADE;
            DROP TABLE IF EXISTS user_contributed_images CASCADE;
        `);
        await this.pool.end();
    }

    async truncateTables() {
        await this.pool.query(`
            TRUNCATE TABLE model_trait_selections, annotations, models, trait_options, traits, trait_categories, atlases, user_contributed_images RESTART IDENTITY CASCADE;
        `);
    }
}

module.exports = TestUtilities;