CREATE TABLE IF NOT EXISTS movie (
    id                TEXT PRIMARY KEY,
    name              TEXT NOT NULL,
    aka               TEXT,
    nationality       TEXT,
    age               INTEGER,
    shoot_location    TEXT,
    shoot_date        TEXT,
    duration_seconds  INTEGER,
    description       TEXT,
    rating            REAL,
    web_url           TEXT,
    thumbnail         TEXT,
    published         TEXT,
    updated           TEXT,

    created_at        TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified_at       TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

    ocr_status        TEXT NOT NULL DEFAULT 'pending',
    ocr_raw_text      TEXT,
    ocr_error         TEXT,
    ocr_processed_at  TEXT,

    CHECK (age IS NULL OR age >= 0),
    CHECK (rating IS NULL OR rating BETWEEN 0 AND 10),
    CHECK (duration_seconds IS NULL OR duration_seconds >= 0)
);

CREATE TABLE IF NOT EXISTS movie_history (
    history_id INTEGER PRIMARY KEY AUTOINCREMENT,

    movie_id TEXT NOT NULL,

    name TEXT NOT NULL,
    aka TEXT,
    nationality TEXT,
    age INTEGER,
    shoot_location TEXT,
    shoot_date TEXT,
    duration_seconds INTEGER,
    description TEXT,
    rating REAL,
    web_url TEXT,
    thumbnail TEXT,
    published TEXT,
    updated TEXT,

    created_at TEXT,
    modified_at TEXT,

    ocr_status TEXT,
    ocr_raw_text TEXT,
    ocr_error TEXT,
    ocr_processed_at TEXT,

    archived_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    change_source TEXT NOT NULL DEFAULT 'site_import',
    change_summary TEXT NOT NULL,

    FOREIGN KEY (movie_id) REFERENCES movie(id)
);

CREATE INDEX IF NOT EXISTS idx_movie_history_movie_id
    ON movie_history(movie_id);

CREATE INDEX IF NOT EXISTS idx_movie_history_archived_at
    ON movie_history(archived_at);

CREATE TABLE IF NOT EXISTS movie_name_alias (
    alias_id INTEGER PRIMARY KEY AUTOINCREMENT,

    movie_id TEXT NOT NULL,
    alias TEXT NOT NULL,
    normalized_alias TEXT NOT NULL,
    source TEXT NOT NULL DEFAULT 'manual',

    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (movie_id) REFERENCES movie(id),

    UNIQUE (movie_id, normalized_alias)
);

CREATE INDEX IF NOT EXISTS idx_movie_name_alias_movie_id
    ON movie_name_alias(movie_id);

CREATE INDEX IF NOT EXISTS idx_movie_name_alias_normalized
    ON movie_name_alias(normalized_alias);