create extension if not exists pg_trgm;

create table if not exists lyrics (
  id bigserial primary key,
  record_id text not null unique,
  source_path text not null,
  title text,
  artist text,
  raw_text text not null,
  normalized_text text not null,
  primary_text text not null,
  translation_text text,
  exact_hash text not null,
  split_confidence text,
  split_reason text,
  line_count integer not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz
);

create index if not exists lyrics_exact_hash_idx
on lyrics (exact_hash)
where deleted_at is null;

create index if not exists lyrics_primary_text_trgm_idx
on lyrics using gin (primary_text gin_trgm_ops);

create table if not exists lyric_lines (
  lyric_id bigint not null references lyrics(id) on delete cascade,
  role text not null,
  line_no integer not null,
  normalized_line text not null,
  line_hash text not null,
  primary key (lyric_id, role, line_no)
);

create index if not exists lyric_lines_hash_idx
on lyric_lines (line_hash);

create index if not exists lyric_lines_lyric_id_idx
on lyric_lines (lyric_id);

create extension if not exists vector;

create table if not exists composition_feature (
  id bigserial primary key,
  song_id bigint not null unique,
  feature_vector vector(1536) not null,
  created_at timestamptz not null default now()
);

create index if not exists composition_feature_hnsw_idx
on composition_feature
using hnsw (feature_vector vector_cosine_ops)
with (m = 16, ef_construction = 64);

create table if not exists dejavu_fingerprints (
  id      bigserial primary key,
  song_id bigint not null references composition_feature(song_id) on delete cascade,
  hash    bytea not null,
  "offset"  int not null
);

create index if not exists idx_fingerprints_hash
on dejavu_fingerprints (hash);

create index if not exists idx_fingerprints_hash_song_offset
on dejavu_fingerprints (hash, song_id, "offset");

create index if not exists idx_fingerprints_song_id
on dejavu_fingerprints (song_id);