CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS songs (
    song_id TEXT PRIMARY KEY,
    title TEXT,
    artist TEXT,
    version_id TEXT,
    source_dataset TEXT,
    license TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS references (
    reference_id BIGSERIAL PRIMARY KEY,
    song_id TEXT NOT NULL REFERENCES songs(song_id) ON DELETE CASCADE,
    audio_uri TEXT NOT NULL,
    duration_sec DOUBLE PRECISION NOT NULL,
    sample_rate INTEGER DEFAULT 16000,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS segments (
    segment_id BIGSERIAL PRIMARY KEY,
    song_id TEXT NOT NULL REFERENCES songs(song_id) ON DELETE CASCADE,
    audio_uri TEXT NOT NULL,
    offset_sec DOUBLE PRECISION DEFAULT 0,
    duration_sec DOUBLE PRECISION NOT NULL,
    split TEXT,
    type TEXT NOT NULL,
    segment_type TEXT,
    source_dataset TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS reference_embeddings (
    embedding_id BIGSERIAL PRIMARY KEY,
    reference_id BIGINT NOT NULL REFERENCES references(reference_id) ON DELETE CASCADE,
    song_id TEXT NOT NULL REFERENCES songs(song_id) ON DELETE CASCADE,
    embedding vector(192) NOT NULL,
    model_version TEXT NOT NULL,
    data_version TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS query_embeddings (
    embedding_id BIGSERIAL PRIMARY KEY,
    segment_id BIGINT NOT NULL REFERENCES segments(segment_id) ON DELETE CASCADE,
    song_id TEXT NOT NULL REFERENCES songs(song_id) ON DELETE CASCADE,
    embedding vector(192) NOT NULL,
    model_version TEXT NOT NULL,
    data_version TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_segments_song_id ON segments(song_id);
CREATE INDEX IF NOT EXISTS idx_references_song_id ON references(song_id);
CREATE INDEX IF NOT EXISTS idx_reference_embeddings_song_id ON reference_embeddings(song_id);
CREATE INDEX IF NOT EXISTS idx_query_embeddings_song_id ON query_embeddings(song_id);

CREATE INDEX IF NOT EXISTS idx_reference_embeddings_vector_cosine
ON reference_embeddings USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

CREATE INDEX IF NOT EXISTS idx_query_embeddings_vector_cosine
ON query_embeddings USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);