-- Nanga Kathai — initial schema.
-- Every song is hidden from listeners until an admin approves it (status = 'approved').

create extension if not exists pg_trgm;

-- ---------------------------------------------------------------- types
create type user_role      as enum ('listener', 'artist', 'admin');
create type track_status   as enum ('draft', 'pending', 'needs_changes', 'approved', 'rejected', 'taken_down');
create type rights_status  as enum ('legacy_unverified', 'artist_approved', 'licensed');
create type track_tier     as enum ('free', 'premium');
create type lyrics_script  as enum ('tamil', 'latin', 'kannada', 'english');

-- ---------------------------------------------------------------- users
create table profiles (
  id           uuid primary key references auth.users on delete cascade,
  display_name text,
  role         user_role not null default 'listener',
  created_at   timestamptz not null default now()
);

create function handle_new_user() returns trigger
language plpgsql security definer set search_path = public as $$
begin
  insert into profiles (id, display_name) values (new.id, split_part(new.email, '@', 1));
  return new;
end $$;

create trigger on_auth_user_created after insert on auth.users
  for each row execute function handle_new_user();

create function is_admin() returns boolean
language sql stable security definer set search_path = public as $$
  select exists (select 1 from profiles where id = auth.uid() and role = 'admin');
$$;

-- ---------------------------------------------------------------- catalog
create table hattis (
  id          uuid primary key default gen_random_uuid(),
  name_latin  text not null unique,
  name_tamil  text,
  lat         double precision,
  lng         double precision
);

create table artists (
  id                 uuid primary key default gen_random_uuid(),
  name_latin         text not null,
  name_tamil         text,
  hatti_id           uuid references hattis,
  bio                text,
  photo_url          text,
  photo_permission   boolean not null default false,
  verified           boolean not null default false,
  claimed_by         uuid references profiles,
  created_at         timestamptz not null default now(),
  -- A singer photo is only stored once permission is on record.
  constraint photo_needs_permission check (photo_url is null or photo_permission)
);

create table albums (
  id           uuid primary key default gen_random_uuid(),
  title_latin  text not null,
  title_tamil  text,
  year         int,
  cover_url    text
);

create table tracks (
  id                uuid primary key default gen_random_uuid(),
  title_latin       text not null,
  title_tamil       text,
  title_en_meaning  text,
  album_id          uuid references albums,
  occasion          text not null default 'folk'
                    check (occasion in ('huttu','habba','madhuvae','devotional','folk','saavu','instrumental')),
  year              int,
  duration_ms       int,
  audio_key_64      text,   -- object keys in the R2 bucket
  audio_key_128     text,
  audio_key_256     text,
  loudness_lufs     real,
  cover_url         text,
  tier              track_tier    not null default 'free',
  status            track_status  not null default 'pending',
  rights_status     rights_status not null default 'legacy_unverified',
  source_note       text,          -- e.g. "Old collection, cassette 12 side A"
  reviewed_by       uuid references profiles,
  reviewed_at       timestamptz,
  review_note       text,
  created_at        timestamptz not null default now(),
  constraint approved_needs_audio check (status <> 'approved' or audio_key_128 is not null)
);

create index tracks_status_idx on tracks (status);
create index tracks_occasion_idx on tracks (occasion) where status = 'approved';
create index tracks_title_trgm on tracks using gin (title_latin gin_trgm_ops);

create table track_artists (
  track_id   uuid references tracks on delete cascade,
  artist_id  uuid references artists on delete cascade,
  role       text not null default 'singer' check (role in ('singer','lyricist','composer','musician')),
  position   int  not null default 0,
  primary key (track_id, artist_id, role)
);

create table lyrics (
  track_id    uuid references tracks on delete cascade,
  script      lyrics_script not null,
  body        text not null,
  synced_lrc  text,
  primary key (track_id, script)
);

create table festivals (
  id           uuid primary key default gen_random_uuid(),
  name         text not null,
  next_date    date,
  description  text
);

-- ---------------------------------------------------------------- rights
create table consents (
  id            uuid primary key default gen_random_uuid(),
  artist_id     uuid references artists on delete cascade,
  track_id      uuid references tracks on delete cascade,
  consent_type  text not null check (consent_type in ('song_distribution','photo','bio')),
  given_by      text not null,   -- singer or family member's name
  evidence_url  text,            -- signed form / message screenshot in private storage
  recorded_by   uuid references profiles,
  created_at    timestamptz not null default now()
);

create table takedown_requests (
  id          uuid primary key default gen_random_uuid(),
  track_id    uuid references tracks on delete cascade,
  requester   text not null,
  contact     text not null,
  reason      text not null,
  status      text not null default 'open' check (status in ('open','actioned','dismissed')),
  created_at  timestamptz not null default now()
);

-- ---------------------------------------------------------------- listener data
create table user_likes (
  user_id    uuid references profiles on delete cascade,
  track_id   uuid references tracks on delete cascade,
  created_at timestamptz not null default now(),
  primary key (user_id, track_id)
);

create table playlists (
  id             uuid primary key default gen_random_uuid(),
  owner_id       uuid references profiles on delete cascade,
  title          text not null,
  is_public      boolean not null default false,
  is_editorial   boolean not null default false,
  created_at     timestamptz not null default now()
);

create table playlist_items (
  playlist_id  uuid references playlists on delete cascade,
  track_id     uuid references tracks on delete cascade,
  position     int not null,
  added_at     timestamptz not null default now(),
  primary key (playlist_id, track_id)
);

create table play_events (
  id          bigint generated always as identity primary key,
  user_id     uuid references profiles on delete set null,
  track_id    uuid references tracks on delete cascade,
  started_at  timestamptz not null default now(),
  ms_played   int,
  source      text,
  offline     boolean not null default false
);

-- ---------------------------------------------------------------- row level security
alter table profiles          enable row level security;
alter table hattis            enable row level security;
alter table artists           enable row level security;
alter table albums            enable row level security;
alter table tracks            enable row level security;
alter table track_artists     enable row level security;
alter table lyrics            enable row level security;
alter table festivals         enable row level security;
alter table consents          enable row level security;
alter table takedown_requests enable row level security;
alter table user_likes        enable row level security;
alter table playlists         enable row level security;
alter table playlist_items    enable row level security;
alter table play_events       enable row level security;

-- Profiles: read and edit your own; admins see all. Role changes are admin-only.
create policy own_profile_read   on profiles for select using (id = auth.uid() or is_admin());
create policy own_profile_update on profiles for update using (id = auth.uid())
  with check (id = auth.uid() and role = (select role from profiles where id = auth.uid()));
create policy admin_profiles     on profiles for all using (is_admin()) with check (is_admin());

-- Public catalog: anyone (including guests) reads approved songs only.
create policy public_read on hattis    for select using (true);
create policy public_read on artists   for select using (true);
create policy public_read on albums    for select using (true);
create policy public_read on festivals for select using (true);
create policy approved_read on tracks for select using (status = 'approved' or is_admin());
create policy approved_read on track_artists for select
  using (exists (select 1 from tracks t where t.id = track_id and (t.status = 'approved' or is_admin())));
create policy approved_read on lyrics for select
  using (exists (select 1 from tracks t where t.id = track_id and (t.status = 'approved' or is_admin())));

-- Admins manage the catalog and rights records.
create policy admin_write on hattis        for all using (is_admin()) with check (is_admin());
create policy admin_write on artists       for all using (is_admin()) with check (is_admin());
create policy admin_write on albums        for all using (is_admin()) with check (is_admin());
create policy admin_write on tracks        for all using (is_admin()) with check (is_admin());
create policy admin_write on track_artists for all using (is_admin()) with check (is_admin());
create policy admin_write on lyrics        for all using (is_admin()) with check (is_admin());
create policy admin_write on festivals     for all using (is_admin()) with check (is_admin());
create policy admin_only  on consents      for all using (is_admin()) with check (is_admin());

-- Anyone may file a takedown request; only admins read them.
create policy file_takedown on takedown_requests for insert with check (true);
create policy admin_takedowns on takedown_requests for select using (is_admin());
create policy admin_takedowns_update on takedown_requests for update using (is_admin());

-- Listener data belongs to the listener.
create policy own_likes on user_likes for all using (user_id = auth.uid()) with check (user_id = auth.uid());
create policy own_or_public_playlists on playlists for select using (owner_id = auth.uid() or is_public or is_admin());
create policy own_playlists on playlists for all using (owner_id = auth.uid()) with check (owner_id = auth.uid() and not is_editorial);
create policy admin_playlists on playlists for all using (is_admin()) with check (is_admin());
create policy playlist_items_read on playlist_items for select
  using (exists (select 1 from playlists p where p.id = playlist_id and (p.owner_id = auth.uid() or p.is_public or is_admin())));
create policy playlist_items_write on playlist_items for all
  using (exists (select 1 from playlists p where p.id = playlist_id and (p.owner_id = auth.uid() or is_admin())))
  with check (exists (select 1 from playlists p where p.id = playlist_id and (p.owner_id = auth.uid() or is_admin())));
create policy log_own_plays on play_events for insert with check (user_id = auth.uid());
create policy admin_plays on play_events for select using (is_admin());