-- VLERA: skema fillestare (Postgres / Supabase). Seanca 0.
-- Supozon: schema auth me auth.users dhe auth.uid() (Supabase). pgcrypto për PIN.
-- Storage (audio private + URL të nënshkruara) konfigurohet në Seancën 1, jo këtu.

create extension if not exists pgcrypto;

-- ---------- Llojet ----------
create type public.age_group as enum ('g3_6', 'g6_9', 'g9_14', 'g14_plus');
create type public.content_type as enum ('book', 'song', 'lesson', 'quiz', 'game');
create type public.content_status as enum ('draft', 'published', 'archived');
create type public.owner_kind as enum ('vlera', 'partner');
create type public.rights_basis as enum ('own', 'public_domain', 'licensed');
create type public.voice_kind as enum ('human', 'synthetic', 'mixed');
create type public.subscription_status as enum ('pilot', 'trialing', 'active', 'past_due', 'canceled', 'expired');
create type public.staff_role as enum ('admin', 'editor');

-- ---------- Ndihmës ----------
create function public.set_updated_at() returns trigger
language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end $$;

-- ---------- Stafi ----------
create table public.staff_users (
  user_id uuid primary key references auth.users (id) on delete cascade,
  role public.staff_role not null,
  active boolean not null default true,
  created_at timestamptz not null default now()
);

create function public.is_staff() returns boolean
language sql stable security definer set search_path = public, pg_temp as $$
  select exists (select 1 from public.staff_users where user_id = auth.uid() and active)
$$;

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

-- ---------- Pronarët e përmbajtjes (VLERA ose partner) ----------
create table public.owners (
  id uuid primary key default gen_random_uuid(),
  kind public.owner_kind not null,
  name text not null check (length(name) between 1 and 120),
  created_at timestamptz not null default now()
);
insert into public.owners (kind, name) values ('vlera', 'VLERA');

-- ---------- Llogaritë dhe profilet ----------
create table public.accounts (
  id uuid primary key references auth.users (id) on delete cascade,
  locale text not null default 'sq',
  accepted_privacy_at timestamptz,
  privacy_version text,
  created_at timestamptz not null default now()
);

create table public.profiles (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts (id) on delete cascade,
  display_name text not null check (length(btrim(display_name)) between 1 and 24),
  age_group public.age_group not null,
  avatar text not null default 'default',
  gender text check (gender in ('female', 'male')),  -- opsionale
  created_at timestamptz not null default now()
);
create index profiles_account_idx on public.profiles (account_id);

create function public.enforce_max_profiles() returns trigger
language plpgsql as $$
begin
  perform pg_advisory_xact_lock(hashtext(new.account_id::text));
  if (select count(*) from public.profiles where account_id = new.account_id) >= 4 then
    raise exception 'max_profiles_reached' using errcode = 'P0001';
  end if;
  return new;
end $$;
create trigger profiles_max_four before insert on public.profiles
  for each row execute function public.enforce_max_profiles();

-- ---------- Zhanret ----------
create table public.genres (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  name_sq text not null
);
create table public.profile_genres (
  profile_id uuid not null references public.profiles (id) on delete cascade,
  genre_id uuid not null references public.genres (id) on delete cascade,
  primary key (profile_id, genre_id)
);

-- ---------- Përmbajtja ----------
create table public.content_items (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references public.owners (id),
  type public.content_type not null,
  title text not null check (length(btrim(title)) between 1 and 160),
  description text,
  age_groups public.age_group[] not null check (cardinality(age_groups) >= 1),
  genre_id uuid references public.genres (id),
  language text not null default 'sq',
  voice_kind public.voice_kind,
  duration_seconds integer check (duration_seconds is null or duration_seconds > 0),
  audio_path text,
  cover_path text,
  status public.content_status not null default 'draft',
  sort_order integer not null default 0,
  -- të drejtat (Q29)
  rights_basis public.rights_basis,
  rights_holder text,
  rights_license_ref text,
  rights_note text,
  rights_confirmed_at timestamptz,
  rights_confirmed_by uuid references auth.users (id),
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  -- Publikimi kërkon audio, kopertinë dhe të drejta të konfirmuara.
  constraint publish_requires_assets check (
    status <> 'published' or (
      cover_path is not null
      and (type not in ('book', 'song') or (audio_path is not null and duration_seconds is not null))
      and rights_basis is not null
      and rights_confirmed_at is not null
      and rights_confirmed_by is not null
    )
  ),
  constraint licensed_requires_ref check (
    status <> 'published' or rights_basis <> 'licensed' or rights_license_ref is not null
  ),
  -- Këngët: kompozimi dhe regjistrimi shpjegohen në rights_note.
  constraint song_requires_note check (
    status <> 'published' or type <> 'song' or rights_note is not null
  )
);
create index content_items_status_idx on public.content_items (status, sort_order);
create trigger content_items_updated before update on public.content_items
  for each row execute function public.set_updated_at();

-- ---------- Progresi dhe dëgjimet ----------
create table public.playback_progress (
  profile_id uuid not null references public.profiles (id) on delete cascade,
  content_id uuid not null references public.content_items (id) on delete cascade,
  position_seconds integer not null default 0 check (position_seconds >= 0),
  completed boolean not null default false,
  updated_at timestamptz not null default now(),
  primary key (profile_id, content_id)
);

create table public.listening_events (
  id bigint generated always as identity primary key,
  profile_id uuid not null references public.profiles (id) on delete cascade,
  content_id uuid not null references public.content_items (id) on delete cascade,
  session_id uuid not null,
  started_at timestamptz not null default now(),
  seconds_listened integer not null check (seconds_listened between 0 and 86400)
);
create index listening_events_profile_idx on public.listening_events (profile_id, started_at);
create index listening_events_content_idx on public.listening_events (content_id, started_at);

-- ---------- Kontrolli prindëror ----------
-- PIN-i ruhet vetëm si hash, në tabelë pa asnjë politikë; qasje vetëm përmes funksioneve.
create table public.parental_pin (
  account_id uuid primary key references public.accounts (id) on delete cascade,
  pin_hash text not null,
  failed_attempts integer not null default 0,
  locked_until timestamptz
);

create table public.profile_limits (
  profile_id uuid primary key references public.profiles (id) on delete cascade,
  daily_limit_minutes integer check (daily_limit_minutes is null or daily_limit_minutes between 5 and 600),
  end_content_id uuid references public.content_items (id) on delete set null,
  updated_at timestamptz not null default now()
);
create trigger profile_limits_updated before update on public.profile_limits
  for each row execute function public.set_updated_at();

-- ---------- Abonimi (joaktiv në pilot) ----------
create table public.subscriptions (
  account_id uuid primary key references public.accounts (id) on delete cascade,
  status public.subscription_status not null default 'pilot',
  trial_ends_at timestamptz,
  plan text,
  store text check (store in ('apple', 'google')),
  store_ref text,
  updated_at timestamptz not null default now()
);

-- Çdo llogari e re merr rresht abonimi 'pilot'.
create function public.init_subscription() returns trigger
language plpgsql security definer set search_path = public, pg_temp as $$
begin
  insert into public.subscriptions (account_id) values (new.id) on conflict do nothing;
  return new;
end $$;
create trigger accounts_init_subscription after insert on public.accounts
  for each row execute function public.init_subscription();

-- ---------- Audit ----------
create table public.audit_log (
  id bigint generated always as identity primary key,
  actor uuid,
  action text not null,
  entity text not null,
  entity_id text,
  details jsonb not null default '{}'::jsonb,
  at timestamptz not null default now()
);

create function public.audit_content() returns trigger
language plpgsql security definer set search_path = public, pg_temp as $$
begin
  insert into public.audit_log (actor, action, entity, entity_id, details)
  values (
    auth.uid(), tg_op, 'content_items', coalesce(new.id, old.id)::text,
    jsonb_build_object('title', coalesce(new.title, old.title), 'status', coalesce(new.status, old.status)::text)
  );
  return coalesce(new, old);
end $$;
create trigger content_items_audit after insert or update or delete on public.content_items
  for each row execute function public.audit_content();

-- ---------- Funksione për aplikacionin ----------
create function public.owns_profile(p_profile uuid) returns boolean
language sql stable security definer set search_path = public, pg_temp as $$
  select exists (select 1 from public.profiles where id = p_profile and account_id = auth.uid())
$$;

-- Katalogu për një profil: vetëm të publikuara dhe për grupmoshën e profilit.
create function public.catalog_for_profile(p_profile uuid)
returns setof public.content_items
language sql stable security invoker set search_path = public, pg_temp as $$
  select c.*
  from public.content_items c
  join public.profiles p on p.id = p_profile and p.account_id = auth.uid()
  where c.status = 'published' and p.age_group = any (c.age_groups)
  order by c.sort_order, c.title
$$;

-- Minutat e dëgjuara sot (sipas zonës kohore), për kohëmatësin.
create function public.minutes_listened_today(p_profile uuid, p_tz text default 'Europe/Tirane')
returns integer
language sql stable security definer set search_path = public, pg_temp as $$
  select case when not public.owns_profile(p_profile) then null
    else coalesce(sum(e.seconds_listened), 0)::integer / 60 end
  from public.listening_events e
  where e.profile_id = p_profile
    and (e.started_at at time zone p_tz)::date = (now() at time zone p_tz)::date
$$;

-- PIN: vendos dhe verifiko. Verifikimi kyçet 15 minuta pas 5 provash të gabuara.
create function public.set_parent_pin(p_pin text) returns void
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if p_pin !~ '^[0-9]{4,6}$' then raise exception 'invalid_pin_format'; end if;
  insert into public.parental_pin (account_id, pin_hash)
  values (auth.uid(), crypt(p_pin, gen_salt('bf')))
  on conflict (account_id) do update
    set pin_hash = excluded.pin_hash, failed_attempts = 0, locked_until = null;
end $$;

create function public.verify_parent_pin(p_pin text) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  r public.parental_pin;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  select * into r from public.parental_pin where account_id = auth.uid() for update;
  if not found then return 'not_set'; end if;
  if r.locked_until is not null and r.locked_until > now() then return 'locked'; end if;
  if r.pin_hash = crypt(p_pin, r.pin_hash) then
    update public.parental_pin set failed_attempts = 0, locked_until = null where account_id = auth.uid();
    return 'ok';
  end if;
  update public.parental_pin
    set failed_attempts = failed_attempts + 1,
        locked_until = case when failed_attempts + 1 >= 5 then now() + interval '15 minutes' else null end
    where account_id = auth.uid();
  return case when r.failed_attempts + 1 >= 5 then 'locked' else 'wrong' end;
end $$;

-- Eksport i të dhënave të llogarisë (e drejta e qasjes).
create function public.export_my_data() returns jsonb
language sql stable security definer set search_path = public, pg_temp as $$
  select jsonb_build_object(
    'account', (select to_jsonb(a) from public.accounts a where a.id = auth.uid()),
    'subscription', (select to_jsonb(s) from public.subscriptions s where s.account_id = auth.uid()),
    'profiles', coalesce((select jsonb_agg(to_jsonb(p)) from public.profiles p where p.account_id = auth.uid()), '[]'::jsonb),
    'limits', coalesce((select jsonb_agg(to_jsonb(l)) from public.profile_limits l
                        join public.profiles p on p.id = l.profile_id where p.account_id = auth.uid()), '[]'::jsonb),
    'progress', coalesce((select jsonb_agg(to_jsonb(g)) from public.playback_progress g
                          join public.profiles p on p.id = g.profile_id where p.account_id = auth.uid()), '[]'::jsonb),
    'listening_events', coalesce((select jsonb_agg(to_jsonb(e)) from public.listening_events e
                                  join public.profiles p on p.id = e.profile_id where p.account_id = auth.uid()), '[]'::jsonb)
  )
$$;

-- ---------- RLS ----------
alter table public.staff_users enable row level security;
alter table public.owners enable row level security;
alter table public.accounts enable row level security;
alter table public.profiles enable row level security;
alter table public.genres enable row level security;
alter table public.profile_genres enable row level security;
alter table public.content_items enable row level security;
alter table public.playback_progress enable row level security;
alter table public.listening_events enable row level security;
alter table public.parental_pin enable row level security;     -- pa politika: asnjë qasje e drejtpërdrejtë
alter table public.profile_limits enable row level security;
alter table public.subscriptions enable row level security;
alter table public.audit_log enable row level security;

revoke all on public.parental_pin from anon, authenticated;
revoke all on public.audit_log from anon;
revoke all on public.staff_users from anon;
revoke all on public.subscriptions from anon;
revoke all on public.accounts from anon;
revoke all on public.profiles from anon;

create policy staff_self_read on public.staff_users for select to authenticated
  using (user_id = auth.uid() or public.is_admin());
create policy staff_admin_write on public.staff_users for all to authenticated
  using (public.is_admin()) with check (public.is_admin());

create policy owners_read on public.owners for select to authenticated using (true);
create policy owners_admin_write on public.owners for all to authenticated
  using (public.is_admin()) with check (public.is_admin());

create policy accounts_own on public.accounts for all to authenticated
  using (id = auth.uid()) with check (id = auth.uid());

create policy profiles_own on public.profiles for all to authenticated
  using (account_id = auth.uid()) with check (account_id = auth.uid());

create policy genres_read on public.genres for select to authenticated using (true);
create policy genres_staff_write on public.genres for all to authenticated
  using (public.is_staff()) with check (public.is_staff());

create policy profile_genres_own on public.profile_genres for all to authenticated
  using (public.owns_profile(profile_id)) with check (public.owns_profile(profile_id));

create policy content_read on public.content_items for select to authenticated
  using (status = 'published' or public.is_staff());
create policy content_staff_insert on public.content_items for insert to authenticated
  with check (public.is_staff());
create policy content_staff_update on public.content_items for update to authenticated
  using (public.is_staff()) with check (public.is_staff());
create policy content_admin_delete on public.content_items for delete to authenticated
  using (public.is_admin());

create policy progress_own on public.playback_progress for all to authenticated
  using (public.owns_profile(profile_id)) with check (public.owns_profile(profile_id));

create policy events_own_read on public.listening_events for select to authenticated
  using (public.owns_profile(profile_id));
create policy events_own_insert on public.listening_events for insert to authenticated
  with check (public.owns_profile(profile_id));

create policy limits_own on public.profile_limits for all to authenticated
  using (public.owns_profile(profile_id)) with check (public.owns_profile(profile_id));

create policy subscriptions_own_read on public.subscriptions for select to authenticated
  using (account_id = auth.uid());
revoke insert, update, delete on public.subscriptions from authenticated;  -- vetëm backend/service role

create policy audit_admin_read on public.audit_log for select to authenticated
  using (public.is_admin());
revoke insert, update, delete on public.audit_log from authenticated;