create extension if not exists pgcrypto;

create table if not exists media_entity (
    entity_id           bigserial primary key,
    entity_type         text not null check (entity_type in ('song', 'work', 'recording')),
    root_song_id        bigint,
    parent_entity_id    bigint,
    biz_key             text,
    title               text not null,
    artist_name         text,
    entity_status       text not null default 'active',
    metadata_json       jsonb not null default '{}'::jsonb,
    created_at          timestamptz not null default now(),
    updated_at          timestamptz not null default now(),
    constraint fk_media_entity_root_song
        foreign key (root_song_id) references media_entity(entity_id),
    constraint fk_media_entity_parent
        foreign key (parent_entity_id) references media_entity(entity_id)
);

create unique index if not exists uq_media_entity_song_biz_key
    on media_entity(entity_type, biz_key)
    where biz_key is not null;

create index if not exists idx_media_entity_root_song
    on media_entity(root_song_id);

create table if not exists audio_object (
    object_id             bigserial primary key,
    object_type           text not null check (object_type in ('asset', 'window')),
    song_id               bigint not null references media_entity(entity_id),
    parent_object_id      bigint references audio_object(object_id),
    source_type           text,
    storage_uri           text,
    storage_scheme        text,
    checksum              text,
    codec                 text,
    sample_rate           integer,
    channels              integer,
    duration_ms           integer,
    start_ms              integer,
    end_ms                integer,
    object_status         text not null default 'ready',
    metadata_json         jsonb not null default '{}'::jsonb,
    created_at            timestamptz not null default now(),
    updated_at            timestamptz not null default now(),
    constraint ck_audio_object_window_parent
        check (
            (object_type = 'asset' and parent_object_id is null)
            or (object_type = 'window' and parent_object_id is not null)
        )
);

create index if not exists idx_audio_object_song_type
    on audio_object(song_id, object_type);

create index if not exists idx_audio_object_parent
    on audio_object(parent_object_id);

create unique index if not exists uq_audio_object_asset_checksum
    on audio_object(song_id, checksum)
    where object_type = 'asset' and checksum is not null;

create unique index if not exists uq_audio_object_window_range
    on audio_object(parent_object_id, start_ms, end_ms)
    where object_type = 'window';

create table if not exists feature_fact (
    feature_id            bigserial primary key,
    feature_type          text not null check (feature_type in ('fingerprint', 'embedding')),
    object_id             bigint not null references audio_object(object_id),
    song_id               bigint not null references media_entity(entity_id),
    model_name            text not null,
    model_version         text not null,
    feature_set_name      text not null,
    feature_schema_ver    text not null default 'v1',
    embedding_dim         integer,
    fingerprint_value     text,
    embedding_uri         text,
    vector_table_name     text,
    checksum              text,
    feature_status        text not null default 'ready',
    metadata_json         jsonb not null default '{}'::jsonb,
    created_at            timestamptz not null default now(),
    updated_at            timestamptz not null default now(),
    constraint ck_feature_payload
        check (
            (feature_type = 'fingerprint' and fingerprint_value is not null)
            or (feature_type = 'embedding' and (embedding_uri is not null or vector_table_name is not null))
        )
);

create index if not exists idx_feature_fact_object_type
    on feature_fact(object_id, feature_type);

create index if not exists idx_feature_fact_song_type
    on feature_fact(song_id, feature_type);

create unique index if not exists uq_feature_fact_embedding
    on feature_fact(object_id, model_name, model_version, feature_set_name, feature_type)
    where feature_type = 'embedding';

create unique index if not exists uq_feature_fact_fingerprint
    on feature_fact(object_id, model_name, model_version, feature_set_name, feature_type)
    where feature_type = 'fingerprint';

create table if not exists set_membership (
    membership_id         bigserial primary key,
    set_type              text not null check (set_type in ('reference_set', 'eval_set', 'hot_set')),
    set_name              text not null,
    member_type           text not null check (member_type in ('song', 'asset', 'window', 'feature')),
    member_id             bigint not null,
    song_id               bigint references media_entity(entity_id),
    is_active             boolean not null default true,
    priority              integer not null default 100,
    metadata_json         jsonb not null default '{}'::jsonb,
    created_at            timestamptz not null default now(),
    updated_at            timestamptz not null default now()
);

create unique index if not exists uq_set_membership_unique
    on set_membership(set_type, set_name, member_type, member_id);

create index if not exists idx_set_membership_set_lookup
    on set_membership(set_type, set_name, is_active, priority);