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,
    poradi INT NOT NULL
);

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[],
    poradi INT NOT NULL
);

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);