-- Custom types
CREATE TYPE MEDIA_TYPE AS ENUM (
    'movie', 'series', 'episode', 'music', 'album', 
    'audiobook', 'podcast', 'photo', 'other'
);

CREATE TYPE MEDIA_STATUS AS ENUM (
    'unavailable', 'searching', 'downloading', 'importing',
    'available', 'upgrading', 'failed'
);

CREATE TYPE QUEUE_STATUS AS ENUM (
    'pending', 'downloading', 'imported', 'failed', 
    'blacklisted', 'cancelled'
);

-- Quality profiles
CREATE TABLE quality_profiles (
    id                SERIAL PRIMARY KEY,
    name              TEXT NOT NULL,
    media_types       MEDIA_TYPE[] NOT NULL,
    cutoff_quality    JSONB NOT NULL,
    allowed_qualities JSONB NOT NULL DEFAULT '[]',
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Root folders
CREATE TABLE root_folders (
    id          SERIAL PRIMARY KEY,
    path        TEXT NOT NULL UNIQUE,
    media_type  MEDIA_TYPE NOT NULL,
    free_space  BIGINT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Tags
CREATE TABLE tags (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL UNIQUE,
    color   TEXT DEFAULT '#6366f1'
);

-- Scheduled tasks
CREATE TABLE scheduled_tasks (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    cron_expr   TEXT NOT NULL,
    last_run_at TIMESTAMPTZ,
    next_run_at TIMESTAMPTZ,
    enabled     BOOLEAN DEFAULT true,
    retention_days INTEGER DEFAULT 7
);

-- Indexers
CREATE TABLE indexers (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL UNIQUE,
    implementation  TEXT NOT NULL,
    url             TEXT NOT NULL,
    api_key         TEXT,
    categories      JSONB DEFAULT '[]',
    settings        JSONB DEFAULT '{}',
    enabled         BOOLEAN DEFAULT true,
    priority        INTEGER DEFAULT 0,
    last_success_at TIMESTAMPTZ,
    failure_count   INTEGER DEFAULT 0,
    disabled_until  TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Download clients
CREATE TABLE download_clients (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    implementation  TEXT NOT NULL,
    host            TEXT NOT NULL,
    port            INTEGER NOT NULL,
    username        TEXT,
    password        TEXT,
    settings        JSONB DEFAULT '{}',
    enabled         BOOLEAN DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Unified media table (partitioned by type)
CREATE TABLE media (
    id                  BIGSERIAL,
    media_type          MEDIA_TYPE NOT NULL,
    title               TEXT NOT NULL,
    sort_title          TEXT NOT NULL,
    original_title      TEXT,
    overview            TEXT,
    year                INTEGER,
    status              MEDIA_STATUS NOT NULL DEFAULT 'unavailable',
    monitored           BOOLEAN NOT NULL DEFAULT false,
    external_ids        JSONB NOT NULL DEFAULT '{}',
    metadata            JSONB NOT NULL DEFAULT '{}',
    images              JSONB NOT NULL DEFAULT '[]',
    quality_profile_id  INTEGER REFERENCES quality_profiles(id),
    root_folder_id      INTEGER REFERENCES root_folders(id),
    current_quality     JSONB,
    desired_quality     JSONB,
    added_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_search_at      TIMESTAMPTZ,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at          TIMESTAMPTZ,
    PRIMARY KEY (id, media_type)
) PARTITION BY LIST (media_type);

CREATE TABLE media_movie PARTITION OF media FOR VALUES IN ('movie');
CREATE TABLE media_series PARTITION OF media FOR VALUES IN ('series');
CREATE TABLE media_episode PARTITION OF media FOR VALUES IN ('episode');
CREATE TABLE media_music PARTITION OF media FOR VALUES IN ('music');
CREATE TABLE media_album PARTITION OF media FOR VALUES IN ('album');
CREATE TABLE media_audiobook PARTITION OF media FOR VALUES IN ('audiobook');
CREATE TABLE media_podcast PARTITION OF media FOR VALUES IN ('podcast');
CREATE TABLE media_photo PARTITION OF media FOR VALUES IN ('photo');
CREATE TABLE media_other PARTITION OF media FOR VALUES IN ('other');

-- Media indexes
CREATE INDEX idx_media_title ON media USING gin (to_tsvector('english', coalesce(title, '')));
CREATE INDEX idx_media_monitored ON media (monitored) WHERE monitored = true;
CREATE INDEX idx_media_status ON media (status, media_type);
CREATE INDEX idx_media_external_ids ON media USING gin (external_ids);

-- Media relations (series->episodes, album->tracks)
CREATE TABLE media_relations (
    id          BIGSERIAL PRIMARY KEY,
    parent_id   BIGINT NOT NULL,
    child_id    BIGINT NOT NULL,
    relation    TEXT NOT NULL,
    position    INTEGER,
    season      INTEGER,
    UNIQUE(parent_id, child_id, relation)
);

-- Media tags
CREATE TABLE media_tags (
    media_id BIGINT NOT NULL,
    media_type MEDIA_TYPE NOT NULL,
    tag_id   INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (media_id, media_type, tag_id)
);

-- Unified file tracking
CREATE TABLE media_files (
    id              BIGSERIAL PRIMARY KEY,
    media_id        BIGINT NOT NULL,
    media_type      MEDIA_TYPE NOT NULL,
    path            TEXT NOT NULL,
    original_path   TEXT,
    file_name       TEXT NOT NULL,
    file_size       BIGINT NOT NULL DEFAULT 0,
    quality         JSONB NOT NULL DEFAULT '{}',
    codec           TEXT,
    resolution      TEXT,
    source          TEXT,
    is_hardlinked   BOOLEAN DEFAULT false,
    checksum        TEXT,
    transcode_status TEXT DEFAULT 'none',
    transcode_preset TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ,
    UNIQUE(media_id, media_type, path)
);

CREATE INDEX idx_media_files_media ON media_files (media_id, media_type);
CREATE INDEX idx_media_files_transcode ON media_files (transcode_status) WHERE transcode_status != 'done';

-- Download queue
CREATE TABLE download_queue (
    id              BIGSERIAL PRIMARY KEY,
    media_id        BIGINT NOT NULL,
    media_type      MEDIA_TYPE NOT NULL,
    release_title   TEXT NOT NULL,
    release_url     TEXT,
    indexer         TEXT NOT NULL,
    download_client TEXT NOT NULL,
    quality         JSONB NOT NULL DEFAULT '{}',
    size            BIGINT,
    protocol        TEXT NOT NULL DEFAULT 'torrent',
    status          QUEUE_STATUS NOT NULL DEFAULT 'pending',
    progress        REAL DEFAULT 0,
    error_message   TEXT,
    batch_id        UUID,
    priority        INTEGER DEFAULT 0,
    retry_count     INTEGER DEFAULT 0,
    max_retries     INTEGER DEFAULT 3,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at      TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_queue_status ON download_queue (status, priority DESC);
CREATE INDEX idx_queue_batch ON download_queue (batch_id) WHERE batch_id IS NOT NULL;
CREATE INDEX idx_queue_media ON download_queue (media_id, media_type);

-- Blocklist
CREATE TABLE blocklist (
    id              BIGSERIAL PRIMARY KEY,
    release_title   TEXT NOT NULL,
    source_title    TEXT,
    quality         JSONB DEFAULT '{}',
    indexer         TEXT,
    protocol        TEXT DEFAULT 'torrent',
    torrent_hash    TEXT,
    size            BIGINT,
    message         TEXT,
    media_id        BIGINT,
    media_type      MEDIA_TYPE,
    block_reason    TEXT DEFAULT 'manual',
    auto_expires_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_blocklist_media ON blocklist (media_id, media_type);
CREATE INDEX idx_blocklist_expires ON blocklist (auto_expires_at) WHERE auto_expires_at IS NOT NULL;

-- Task execution log
CREATE TABLE task_executions (
    id          BIGSERIAL PRIMARY KEY,
    task_id     INTEGER NOT NULL REFERENCES scheduled_tasks(id) ON DELETE CASCADE,
    status      TEXT NOT NULL DEFAULT 'running',
    started_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ended_at    TIMESTAMPTZ,
    duration_ms INTEGER,
    result      JSONB,
    error       TEXT
);

CREATE INDEX idx_task_exec_task ON task_executions (task_id, started_at DESC);

-- Download history (partitioned for auto-cleanup)
CREATE TABLE download_history (
    id            BIGSERIAL,
    media_id      BIGINT NOT NULL,
    media_type    MEDIA_TYPE NOT NULL,
    action        TEXT NOT NULL,
    release_title TEXT,
    quality       JSONB DEFAULT '{}',
    indexer       TEXT,
    client        TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE download_history_current PARTITION OF download_history
    FOR VALUES FROM (CURRENT_DATE - INTERVAL '90 days') TO (MAXVALUE);