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);