CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ------------------------------------------------------------
-- users and access control
-- ------------------------------------------------------------

CREATE TABLE app_user (
    id                  BIGSERIAL PRIMARY KEY,
    username            VARCHAR(64) NOT NULL UNIQUE,
    email               VARCHAR(320) UNIQUE,
    password_hash       TEXT NOT NULL,
    is_admin            BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_login_at       TIMESTAMPTZ
);

CREATE TABLE library (
    id                  BIGSERIAL PRIMARY KEY,
    owner_user_id       BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    description         TEXT,
    visibility          VARCHAR(16) NOT NULL DEFAULT 'private'
                        CHECK (visibility IN ('private', 'shared', 'public')),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (owner_user_id, name)
);

CREATE TABLE library_member (
    id                  BIGSERIAL PRIMARY KEY,
    library_id          BIGINT NOT NULL REFERENCES library(id) ON DELETE CASCADE,
    user_id             BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    role                VARCHAR(16) NOT NULL
                        CHECK (role IN ('viewer', 'editor', 'manager')),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (library_id, user_id)
);

-- ------------------------------------------------------------
-- media metadata
-- ------------------------------------------------------------

CREATE TABLE genre (
    id                  BIGSERIAL PRIMARY KEY,
    parent_genre_id     BIGINT REFERENCES genre(id) ON DELETE SET NULL,
    name                VARCHAR(128) NOT NULL UNIQUE,
    description         TEXT
);

CREATE TABLE content_rating (
    id                  BIGSERIAL PRIMARY KEY,
    system_name         VARCHAR(64) NOT NULL,
    code                VARCHAR(32) NOT NULL,
    description         TEXT,
    min_age             INTEGER,
    UNIQUE (system_name, code)
);

CREATE TABLE media_source (
    id                  BIGSERIAL PRIMARY KEY,
    library_id          BIGINT NOT NULL REFERENCES library(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    source_type         VARCHAR(32) NOT NULL
                        CHECK (source_type IN (
                            'local_directory',
                            'network_share',
                            'manual_import',
                            'playlist',
                            'stream',
                            'api_feed'
                        )),
    uri                 TEXT NOT NULL,
    recursive_scan      BOOLEAN NOT NULL DEFAULT TRUE,
    include_commercials BOOLEAN NOT NULL DEFAULT FALSE,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    scan_interval_minutes INTEGER,
    last_scanned_at     TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (library_id, name)
);

CREATE TABLE media_source_rule (
    id                  BIGSERIAL PRIMARY KEY,
    media_source_id     BIGINT NOT NULL REFERENCES media_source(id) ON DELETE CASCADE,
    rule_type           VARCHAR(24) NOT NULL
                        CHECK (rule_type IN ('include_glob', 'exclude_glob', 'include_regex', 'exclude_regex')),
    rule_value          TEXT NOT NULL,
    sort_order          INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE series (
    id                  BIGSERIAL PRIMARY KEY,
    title               VARCHAR(255) NOT NULL,
    description         TEXT,
    release_year        INTEGER,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE media_item (
    id                  BIGSERIAL PRIMARY KEY,
    media_source_id     BIGINT NOT NULL REFERENCES media_source(id) ON DELETE CASCADE,
    series_id           BIGINT REFERENCES series(id) ON DELETE SET NULL,
    title               VARCHAR(255) NOT NULL,
    sort_title          VARCHAR(255),
    description         TEXT,
    item_kind           VARCHAR(24) NOT NULL
                        CHECK (item_kind IN (
                            'movie',
                            'episode',
                            'special',
                            'music_video',
                            'bumper',
                            'interstitial',
                            'commercial'
                        )),
    season_number       INTEGER,
    episode_number      INTEGER,
    release_year        INTEGER,
    runtime_seconds     INTEGER NOT NULL CHECK (runtime_seconds > 0),
    file_path           TEXT NOT NULL,
    file_hash           VARCHAR(128),
    thumbnail_path      TEXT,
    language_code       VARCHAR(16),
    content_rating_id   BIGINT REFERENCES content_rating(id) ON DELETE SET NULL,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    date_added_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata_json       JSONB NOT NULL DEFAULT '{}'::JSONB,
    UNIQUE (media_source_id, file_path)
);

CREATE INDEX idx_media_item_series_id           ON media_item(series_id);
CREATE INDEX idx_media_item_item_kind           ON media_item(item_kind);
CREATE INDEX idx_media_item_rating_id           ON media_item(content_rating_id);
CREATE INDEX idx_media_item_source_id           ON media_item(media_source_id);

CREATE TABLE media_item_genre (
    media_item_id       BIGINT NOT NULL REFERENCES media_item(id) ON DELETE CASCADE,
    genre_id            BIGINT NOT NULL REFERENCES genre(id) ON DELETE CASCADE,
    PRIMARY KEY (media_item_id, genre_id)
);

CREATE TABLE media_collection (
    id                  BIGSERIAL PRIMARY KEY,
    library_id          BIGINT NOT NULL REFERENCES library(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    collection_type     VARCHAR(24) NOT NULL
                        CHECK (collection_type IN ('manual', 'smart')),
    definition_json     JSONB NOT NULL DEFAULT '{}'::JSONB,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (library_id, name)
);

CREATE TABLE media_collection_item (
    media_collection_id BIGINT NOT NULL REFERENCES media_collection(id) ON DELETE CASCADE,
    media_item_id       BIGINT NOT NULL REFERENCES media_item(id) ON DELETE CASCADE,
    sort_order          INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (media_collection_id, media_item_id)
);

-- ------------------------------------------------------------
-- channels and programming policy
-- ------------------------------------------------------------

CREATE TABLE channel (
    id                  BIGSERIAL PRIMARY KEY,
    owner_user_id       BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    library_id          BIGINT NOT NULL REFERENCES library(id) ON DELETE CASCADE,
    default_genre_id    BIGINT REFERENCES genre(id) ON DELETE SET NULL,
    name                VARCHAR(255) NOT NULL,
    slug                VARCHAR(64) NOT NULL UNIQUE,
    channel_number      INTEGER,
    description         TEXT,
    timezone_name       VARCHAR(64) NOT NULL DEFAULT 'UTC',
    visibility          VARCHAR(16) NOT NULL DEFAULT 'private'
                        CHECK (visibility IN ('private', 'shared', 'public')),
    scheduling_mode     VARCHAR(24) NOT NULL DEFAULT 'template_driven'
                        CHECK (scheduling_mode IN ('template_driven', 'algorithmic', 'mixed')),
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (library_id, name),
    UNIQUE (library_id, channel_number)
);

CREATE TABLE channel_member (
    id                  BIGSERIAL PRIMARY KEY,
    channel_id          BIGINT NOT NULL REFERENCES channel(id) ON DELETE CASCADE,
    user_id             BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    role                VARCHAR(16) NOT NULL
                        CHECK (role IN ('viewer', 'editor', 'manager')),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (channel_id, user_id)
);

CREATE TABLE channel_source_rule (
    id                  BIGSERIAL PRIMARY KEY,
    channel_id          BIGINT NOT NULL REFERENCES channel(id) ON DELETE CASCADE,
    media_source_id     BIGINT REFERENCES media_source(id) ON DELETE CASCADE,
    media_collection_id BIGINT REFERENCES media_collection(id) ON DELETE CASCADE,
    rule_mode           VARCHAR(24) NOT NULL
                        CHECK (rule_mode IN ('allow', 'prefer', 'avoid', 'block')),
    weight              NUMERIC(10,4) NOT NULL DEFAULT 1.0 CHECK (weight >= 0),
    max_items_per_day   INTEGER,
    max_runs_per_day    INTEGER,
    min_repeat_gap_hours INTEGER,
    active_from         TIMESTAMPTZ,
    active_to           TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CHECK (
        (media_source_id IS NOT NULL AND media_collection_id IS NULL)
        OR
        (media_source_id IS NULL AND media_collection_id IS NOT NULL)
    )
);

CREATE TABLE commercial_policy (
    id                  BIGSERIAL PRIMARY KEY,
    name                VARCHAR(255) NOT NULL UNIQUE,
    mode                VARCHAR(24) NOT NULL
                        CHECK (mode IN ('none', 'replace_breaks', 'fill_to_target', 'probabilistic')),
    target_break_seconds INTEGER,
    max_break_seconds   INTEGER,
    allow_same_ad_back_to_back BOOLEAN NOT NULL DEFAULT FALSE,
    description         TEXT
);

CREATE TABLE channel_branding (
    id                  BIGSERIAL PRIMARY KEY,
    channel_id          BIGINT NOT NULL UNIQUE REFERENCES channel(id) ON DELETE CASCADE,
    commercial_policy_id BIGINT REFERENCES commercial_policy(id) ON DELETE SET NULL,
    logo_path           TEXT,
    bumper_collection_id BIGINT REFERENCES media_collection(id) ON DELETE SET NULL,
    fallback_fill_collection_id BIGINT REFERENCES media_collection(id) ON DELETE SET NULL,
    station_id_audio_path TEXT,
    config_json         JSONB NOT NULL DEFAULT '{}'::JSONB
);

-- ------------------------------------------------------------
-- schedule templates: recurring editorial structure
-- ------------------------------------------------------------

CREATE TABLE schedule_template (
    id                  BIGSERIAL PRIMARY KEY,
    channel_id          BIGINT NOT NULL REFERENCES channel(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    description         TEXT,
    timezone_name       VARCHAR(64) NOT NULL,
    valid_from_date     DATE,
    valid_to_date       DATE,
    priority            INTEGER NOT NULL DEFAULT 0,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (channel_id, name)
);

CREATE TABLE schedule_block (
    id                  BIGSERIAL PRIMARY KEY,
    schedule_template_id BIGINT NOT NULL REFERENCES schedule_template(id) ON DELETE CASCADE,
    name                VARCHAR(255) NOT NULL,
    block_type          VARCHAR(24) NOT NULL
                        CHECK (block_type IN ('programming', 'commercial', 'filler', 'off_air', 'special_event')),
    start_local_time    TIME NOT NULL,
    end_local_time      TIME NOT NULL,
    day_of_week_mask    SMALLINT NOT NULL CHECK (day_of_week_mask BETWEEN 1 AND 127),
    spills_past_midnight BOOLEAN NOT NULL DEFAULT FALSE,
    default_genre_id    BIGINT REFERENCES genre(id) ON DELETE SET NULL,
    min_content_rating_id BIGINT REFERENCES content_rating(id) ON DELETE SET NULL,
    max_content_rating_id BIGINT REFERENCES content_rating(id) ON DELETE SET NULL,
    preferred_collection_id BIGINT REFERENCES media_collection(id) ON DELETE SET NULL,
    preferred_source_id BIGINT REFERENCES media_source(id) ON DELETE SET NULL,
    rotation_strategy   VARCHAR(24) NOT NULL DEFAULT 'shuffle'
                        CHECK (rotation_strategy IN ('shuffle', 'sequential', 'least_recent', 'weighted_random')),
    pad_strategy        VARCHAR(24) NOT NULL DEFAULT 'fill_with_interstitials'
                        CHECK (pad_strategy IN ('hard_stop', 'truncate', 'fill_with_interstitials', 'allow_overrun')),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CHECK (
        end_local_time > start_local_time OR spills_past_midnight = TRUE
    )
);

CREATE TABLE block_slot (
    id                  BIGSERIAL PRIMARY KEY,
    schedule_block_id   BIGINT NOT NULL REFERENCES schedule_block(id) ON DELETE CASCADE,
    slot_order          INTEGER NOT NULL,
    slot_kind           VARCHAR(24) NOT NULL
                        CHECK (slot_kind IN ('fixed_item', 'dynamic_pick', 'commercial_break', 'bumper', 'station_id')),
    media_item_id       BIGINT REFERENCES media_item(id) ON DELETE SET NULL,
    media_collection_id BIGINT REFERENCES media_collection(id) ON DELETE SET NULL,
    expected_duration_seconds INTEGER,
    max_duration_seconds INTEGER,
    is_mandatory        BOOLEAN NOT NULL DEFAULT TRUE,
    selection_rule_json JSONB NOT NULL DEFAULT '{}'::JSONB,
    UNIQUE (schedule_block_id, slot_order),
    CHECK (
        slot_kind <> 'fixed_item' OR media_item_id IS NOT NULL
    )
);

-- ------------------------------------------------------------
-- concrete schedule: actual planned / generated airtimes
-- ------------------------------------------------------------

CREATE TABLE airing (
    id                  BIGSERIAL PRIMARY KEY,
    channel_id          BIGINT NOT NULL REFERENCES channel(id) ON DELETE CASCADE,
    schedule_template_id BIGINT REFERENCES schedule_template(id) ON DELETE SET NULL,
    schedule_block_id   BIGINT REFERENCES schedule_block(id) ON DELETE SET NULL,
    media_item_id       BIGINT NOT NULL REFERENCES media_item(id) ON DELETE RESTRICT,
    starts_at           TIMESTAMPTZ NOT NULL,
    ends_at             TIMESTAMPTZ NOT NULL,
    slot_kind           VARCHAR(24) NOT NULL
                        CHECK (slot_kind IN ('program', 'commercial', 'bumper', 'interstitial', 'station_id')),
    status              VARCHAR(24) NOT NULL DEFAULT 'scheduled'
                        CHECK (status IN ('scheduled', 'playing', 'played', 'skipped', 'interrupted', 'cancelled')),
    source_reason       VARCHAR(24) NOT NULL
                        CHECK (source_reason IN ('template', 'autofill', 'manual', 'recovery')),
    generation_batch_uuid UUID NOT NULL DEFAULT gen_random_uuid(),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CHECK (ends_at > starts_at)
);

CREATE INDEX idx_airing_channel_time      ON airing(channel_id, starts_at, ends_at);
CREATE INDEX idx_airing_media_item_time   ON airing(media_item_id, starts_at);
CREATE INDEX idx_airing_status            ON airing(status);

CREATE TABLE airing_event (
    id                  BIGSERIAL PRIMARY KEY,
    airing_id           BIGINT NOT NULL REFERENCES airing(id) ON DELETE CASCADE,
    event_type          VARCHAR(24) NOT NULL
                        CHECK (event_type IN ('scheduled', 'started', 'ended', 'skipped', 'interrupted', 'resumed', 'cancelled')),
    event_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    details_json        JSONB NOT NULL DEFAULT '{}'::JSONB
);

-- ------------------------------------------------------------
-- viewer state and history
-- ------------------------------------------------------------

CREATE TABLE user_channel_state (
    id                  BIGSERIAL PRIMARY KEY,
    user_id             BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    channel_id          BIGINT NOT NULL REFERENCES channel(id) ON DELETE CASCADE,
    is_favorite         BOOLEAN NOT NULL DEFAULT FALSE,
    last_tuned_at       TIMESTAMPTZ,
    last_known_airing_id BIGINT REFERENCES airing(id) ON DELETE SET NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, channel_id)
);

CREATE TABLE watch_session (
    id                  BIGSERIAL PRIMARY KEY,
    user_id             BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    channel_id          BIGINT REFERENCES channel(id) ON DELETE SET NULL,
    airing_id           BIGINT REFERENCES airing(id) ON DELETE SET NULL,
    started_at          TIMESTAMPTZ NOT NULL,
    ended_at            TIMESTAMPTZ,
    position_seconds    INTEGER NOT NULL DEFAULT 0 CHECK (position_seconds >= 0),
    client_id           VARCHAR(128),
    session_metadata    JSONB NOT NULL DEFAULT '{}'::JSONB,
    CHECK (ended_at IS NULL OR ended_at >= started_at)
);

CREATE TABLE media_resume_point (
    id                  BIGSERIAL PRIMARY KEY,
    user_id             BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    media_item_id       BIGINT NOT NULL REFERENCES media_item(id) ON DELETE CASCADE,
    resume_seconds      INTEGER NOT NULL CHECK (resume_seconds >= 0),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, media_item_id)
);

-- ------------------------------------------------------------
-- useful integrity helpers
-- ------------------------------------------------------------

CREATE INDEX idx_channel_source_rule_channel      ON channel_source_rule(channel_id);
CREATE INDEX idx_schedule_template_channel        ON schedule_template(channel_id, is_active);
CREATE INDEX idx_schedule_block_template          ON schedule_block(schedule_template_id, day_of_week_mask);
CREATE INDEX idx_block_slot_block_order           ON block_slot(schedule_block_id, slot_order);
CREATE INDEX idx_user_channel_state_user          ON user_channel_state(user_id);
CREATE INDEX idx_watch_session_user_time          ON watch_session(user_id, started_at);