-- VLERA: të gjitha migrimet në një skedar (0001_init.sql … 0010_questions.sql).
-- GJENERUAR nga backend/scripts/build-combined.mjs: mos e redakto me dorë.
-- Përdorim: projekt Supabase i RI dhe i zbrazët. Ngjite te SQL Editor dhe ekzekutoje një herë.
-- Nëse jep gabim, mos e ekzekuto përsëri: ndalo dhe dërgo tekstin e plotë të gabimit.
-- Nuk është i sigurt për projekt që ka tashmë këto objekte (migrimet nuk janë idempotente).

-- ======================================================================
-- 0001_init.sql
-- ======================================================================
-- 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;

-- ======================================================================
-- 0002_new_user.sql
-- ======================================================================
-- Çdo përdorues i ri në auth.users merr automatikisht rreshtin në public.accounts.
-- Kështu llogaria krijohet edhe kur Supabase kërkon konfirmim emaili (nuk ka ende sesion te klienti).
-- Pranimi i privatësisë vjen nga options.data.accepted_privacy_version gjatë signUp.

create function public.handle_new_user() returns trigger
language plpgsql security definer set search_path = public, pg_temp as $$
begin
  insert into public.accounts (id, accepted_privacy_at, privacy_version)
  values (
    new.id,
    case when new.raw_user_meta_data ? 'accepted_privacy_version' then now() end,
    nullif(new.raw_user_meta_data ->> 'accepted_privacy_version', '')
  )
  on conflict (id) do nothing;
  return new;
end $$;

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

-- ======================================================================
-- 0003_storage.sql
-- ======================================================================
-- Ruajtja e skedarëve.
--   covers: bucket publik (kopertinat nuk janë të ndjeshme)
--   audio:  bucket PRIVAT; klienti merr vetëm URL të nënshkruara me jetë të shkurtër
-- content_items.audio_path / cover_path ruajnë rrugën brenda bucket-it (pa emrin e bucket-it).
-- Kufiri i madhësisë së skedarit varet nga plani i Supabase; verifikoje para ngarkimit të librave të gjatë.

insert into storage.buckets (id, name, public, allowed_mime_types)
values
  ('covers', 'covers', true, array['image/jpeg', 'image/png', 'image/webp']),
  ('audio', 'audio', false, array['audio/mpeg', 'audio/mp4', 'audio/x-m4a', 'audio/aac'])
on conflict (id) do nothing;

-- Audio: lexohet vetëm kur titulli është i publikuar (ose nga stafi). Drafti dhe titujt e arkivuar nuk nënshkruhen.
create policy audio_read_published on storage.objects for select to authenticated
  using (
    bucket_id = 'audio'
    and (
      public.is_staff()
      or exists (
        select 1 from public.content_items c
        where c.audio_path = storage.objects.name and c.status = 'published'
      )
    )
  );

create policy media_staff_read on storage.objects for select to authenticated
  using (bucket_id in ('audio', 'covers') and public.is_staff());

create policy media_staff_insert on storage.objects for insert to authenticated
  with check (bucket_id in ('audio', 'covers') and public.is_staff());

create policy media_staff_update on storage.objects for update to authenticated
  using (bucket_id in ('audio', 'covers') and public.is_staff())
  with check (bucket_id in ('audio', 'covers') and public.is_staff());

create policy media_admin_delete on storage.objects for delete to authenticated
  using (bucket_id in ('audio', 'covers') and public.is_admin());

-- ======================================================================
-- 0004_staff_aal2.sql
-- ======================================================================
-- 2FA e detyrueshme për stafin, e zbatuar në bazën e të dhënave (jo vetëm në ndërfaqe).
-- Pa këtë, një fjalëkalim i vjedhur do të mjaftonte për të thirrur API-n drejtpërdrejt.
-- Supabase vendos "aal" në JWT: aal1 = vetëm fjalëkalim, aal2 = fjalëkalim + kod TOTP.
-- Lexim i rreshtit të vet në staff_users mbetet i lejuar në aal1 (politika staff_self_read nuk përdor is_staff),
-- që paneli të dijë nëse duhet regjistruar ose kërkuar kodi.

create or replace function public.is_staff() returns boolean
language sql stable security definer set search_path = public, pg_temp as $$
  select coalesce(auth.jwt() ->> 'aal', '') = 'aal2'
    and exists (select 1 from public.staff_users where user_id = auth.uid() and active)
$$;

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

-- ======================================================================
-- 0005_rights_trigger.sql
-- ======================================================================
-- Konfirmimi i të drejtave nuk mund të falsifikohet nga klienti:
-- kur një anëtar i stafit vendos rights_confirmed_at, serveri vendos kohën dhe identitetin e tij (auth.uid()).
-- Gjithashtu vendos published_at herën e parë që titulli publikohet.
create function public.content_stamp() returns trigger
language plpgsql set search_path = public, pg_temp as $$
begin
  if auth.uid() is not null then
    if new.rights_confirmed_at is not null
       and (tg_op = 'INSERT' or new.rights_confirmed_at is distinct from old.rights_confirmed_at
            or new.rights_confirmed_by is distinct from old.rights_confirmed_by) then
      new.rights_confirmed_at := now();
      new.rights_confirmed_by := auth.uid();
    end if;
    if new.rights_confirmed_at is null then
      new.rights_confirmed_by := null;
    end if;
  end if;
  if new.status = 'published' and new.published_at is null then
    new.published_at := now();
  end if;
  new.updated_at := now();
  return new;
end $$;

create trigger content_stamp before insert or update on public.content_items
  for each row execute function public.content_stamp();

-- ======================================================================
-- 0006_parental_control.sql
-- ======================================================================
-- 0006: kontrolli prindëror i sigurt (Seanca 4)
-- Problemet që zgjidh:
--  1) set_parent_pin lejonte këdo me sesionin e llogarisë (p.sh. fëmijën) ta ndërronte PIN-in pa njohur të vjetrin.
--  2) profile_limits shkruhej drejtpërdrejt nga klienti: PIN-i ishte vetëm portë në ndërfaqe.
--  3) Funksionet nuk duhet të hedhin gabim për PIN të gabuar: hedhja e gabimit prish transaksionin
--     dhe humbet numrimin e përpjekjeve (kështu kyçja pas 5 provave anashkalohej).

-- Kontrolli i brendshëm i PIN-it (me kyçje 15 min pas 5 provash). Nuk thirret nga klienti.
create function public.check_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;
  -- Kyçja e skaduar zeron numrimin (përndryshe një gabim i vetëm pas 15 minutash do kyçte përsëri).
  update public.parental_pin set failed_attempts = 0, locked_until = null
    where account_id = auth.uid() and locked_until is not null and locked_until <= now();
  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 $$;
revoke all on function public.check_parent_pin(text) from public, anon, authenticated;

create or replace function public.verify_parent_pin(p_pin text) returns text
language sql security definer set search_path = public, extensions, pg_temp as $$
  select public.check_parent_pin(p_pin)
$$;

create function public.has_parent_pin() returns boolean
language sql stable security definer set search_path = public, pg_temp as $$
  select exists (select 1 from public.parental_pin where account_id = auth.uid())
$$;

-- Vendos PIN-in e parë (p_old_pin i panevojshëm) ose e ndërron (p_old_pin i detyrueshëm).
-- Kthen 'ok' | 'wrong' | 'locked'. Formati i gabuar hedh gabim (nuk ndryshon asgjë, pra nuk humbet numrim).
drop function public.set_parent_pin(text);
create function public.set_parent_pin(p_pin text, p_old_pin text default null) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  existing boolean;
  verdict text;
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;
  select exists (select 1 from public.parental_pin where account_id = auth.uid()) into existing;
  if existing then
    if p_old_pin is null then return 'wrong'; end if;
    verdict := public.check_parent_pin(p_old_pin);
    if verdict <> 'ok' then return verdict; end if;
  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;
  return 'ok';
end $$;

-- Limitat shkruhen vetëm përmes funksionit që verifikon PIN-in në server.
drop policy limits_own on public.profile_limits;
create policy limits_own_read on public.profile_limits for select to authenticated
  using (public.owns_profile(profile_id));
revoke insert, update, delete on public.profile_limits from anon, authenticated;

create function public.set_profile_limits(
  p_profile uuid, p_minutes integer, p_end_content uuid, p_pin text
) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  verdict text;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if not public.owns_profile(p_profile) then raise exception 'profile_not_found'; end if;
  if p_minutes is not null and p_minutes not between 5 and 600 then raise exception 'invalid_limit'; end if;
  if p_end_content is not null and not exists (
    select 1 from public.content_items c
    join public.profiles p on p.id = p_profile
    where c.id = p_end_content and c.status = 'published' and p.age_group = any (c.age_groups)
  ) then raise exception 'invalid_end_content'; end if;

  verdict := public.check_parent_pin(p_pin);
  if verdict <> 'ok' then return verdict; end if;

  insert into public.profile_limits (profile_id, daily_limit_minutes, end_content_id)
  values (p_profile, p_minutes, p_end_content)
  on conflict (profile_id) do update
    set daily_limit_minutes = excluded.daily_limit_minutes, end_content_id = excluded.end_content_id;
  return 'ok';
end $$;

-- Sekondat e dëgjuara sot (zona e Tiranës), për numërimin mbrapsht me saktësi sekonde.
create function public.seconds_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 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
$$;

-- Sekondat e dëgjuara sot për një titull të caktuar (për librin përfundimtar: lejohet një herë në ditë).
create function public.seconds_listened_today_for_content(p_profile uuid, p_content 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 end
  from public.listening_events e
  where e.profile_id = p_profile
    and e.content_id = p_content
    and (e.started_at at time zone p_tz)::date = (now() at time zone p_tz)::date
$$;

-- ======================================================================
-- 0007_profile_pins.sql
-- ======================================================================
-- 0007: PIN për profil (Seanca 4b)
-- Rregulli i produktit: ndërrimi i profilit nuk është i lirë.
--  * Profili mund të ketë PIN-in e vet (opsional, vendoset nga prindi).
--  * PIN-i i prindit hap çdo profil.
-- Kjo është portë në ndërfaqe (si Netflix): serveri verifikon PIN-in dhe numëron përpjekjet,
-- por të dhënat e llogarisë mbeten të lexueshme me sesionin e llogarisë (RLS sipas llogarisë, jo sipas profilit).

create table public.profile_pins (
  profile_id uuid primary key references public.profiles (id) on delete cascade,
  pin_hash text not null,
  failed_attempts integer not null default 0,
  locked_until timestamptz
);
alter table public.profile_pins enable row level security;  -- pa politika: asnjë qasje e drejtpërdrejtë
revoke all on public.profile_pins from anon, authenticated;

-- Cilët profile të llogarisë kanë PIN (pa zbuluar asgjë tjetër).
create function public.protected_profiles() returns setof uuid
language sql stable security definer set search_path = public, pg_temp as $$
  select pp.profile_id
  from public.profile_pins pp
  join public.profiles p on p.id = pp.profile_id
  where p.account_id = auth.uid()
$$;

-- Vendos, ndërron (p_new_pin) ose heq (p_new_pin = null) PIN-in e profilit. Kërkon PIN-in e prindit.
-- Kthen 'ok' | 'wrong' | 'locked' | 'not_set' (nuk ka PIN prindëror).
create function public.set_profile_pin(p_profile uuid, p_new_pin text, p_parent_pin text) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  verdict text;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if not public.owns_profile(p_profile) then raise exception 'profile_not_found'; end if;
  if p_new_pin is not null and p_new_pin !~ '^[0-9]{4,6}$' then raise exception 'invalid_pin_format'; end if;

  verdict := public.check_parent_pin(p_parent_pin);
  if verdict <> 'ok' then return verdict; end if;

  if p_new_pin is null then
    delete from public.profile_pins where profile_id = p_profile;
  else
    insert into public.profile_pins (profile_id, pin_hash)
    values (p_profile, crypt(p_new_pin, gen_salt('bf')))
    on conflict (profile_id) do update
      set pin_hash = excluded.pin_hash, failed_attempts = 0, locked_until = null;
  end if;
  return 'ok';
end $$;

-- Hap (ose lë) një profil: pranon PIN-in e vetë profilit OSE PIN-in e prindit.
-- Përpjekja e gabuar numërohet në të dyja (kyçja 15 min pas 5), që të mos provohet PIN-i i prindit pa kufi
-- përmes profilit. Kthen 'ok' | 'wrong' | 'locked' | 'not_set' (as profili as prindi s'kanë PIN).
-- Gabimet nuk hidhen: hedhja e gabimit prish transaksionin dhe humbet numrimin.
create function public.unlock_profile(p_profile uuid, p_pin text) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  par public.parental_pin;
  pr public.profile_pins;
  par_exists boolean;
  pr_exists boolean;
  par_avail boolean;
  pr_avail boolean;
  par_locks boolean;
  pr_locks boolean;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if not public.owns_profile(p_profile) then raise exception 'profile_not_found'; end if;

  -- Kyçja e skaduar zeron numrimin (përndryshe një gabim i vetëm pas 15 minutash do kyçte përsëri).
  update public.parental_pin set failed_attempts = 0, locked_until = null
    where account_id = auth.uid() and locked_until is not null and locked_until <= now();
  update public.profile_pins set failed_attempts = 0, locked_until = null
    where profile_id = p_profile and locked_until is not null and locked_until <= now();

  select * into par from public.parental_pin where account_id = auth.uid() for update;
  par_exists := found;
  select * into pr from public.profile_pins where profile_id = p_profile for update;
  pr_exists := found;

  if not par_exists and not pr_exists then return 'not_set'; end if;

  par_avail := par_exists and (par.locked_until is null or par.locked_until <= now());
  pr_avail := pr_exists and (pr.locked_until is null or pr.locked_until <= now());
  if not par_avail and not pr_avail then return 'locked'; end if;

  if par_avail and par.pin_hash = crypt(p_pin, par.pin_hash) then
    update public.parental_pin set failed_attempts = 0, locked_until = null where account_id = auth.uid();
    return 'ok';
  end if;
  if pr_avail and pr.pin_hash = crypt(p_pin, pr.pin_hash) then
    update public.profile_pins set failed_attempts = 0, locked_until = null where profile_id = p_profile;
    return 'ok';
  end if;

  if par_avail then
    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();
  end if;
  if pr_avail then
    update public.profile_pins
      set failed_attempts = failed_attempts + 1,
          locked_until = case when failed_attempts + 1 >= 5 then now() + interval '15 minutes' else null end
      where profile_id = p_profile;
  end if;

  par_locks := not par_avail or (par_exists and par.failed_attempts + 1 >= 5);
  pr_locks := not pr_avail or (pr_exists and pr.failed_attempts + 1 >= 5);
  -- 'not exists' llogaritet si "nuk ndihmon më": nëse çdo rrugë tjetër është e kyçur ose mungon, kthe 'locked'.
  if (not par_exists or par_locks) and (not pr_exists or pr_locks) then return 'locked'; end if;
  return 'wrong';
end $$;

-- ======================================================================
-- 0008_profile_management.sql
-- ======================================================================
-- 0008: menaxhimi i profileve me PIN në server, fshirja e llogarisë, zhanret, raportet (Seanca 5)
-- Pse: grupmosha e profilit vendos çfarë dëgjon fëmija. Nëse klienti mund ta ndryshojë ose të krijojë profil 14+ pa PIN,
-- kontrolli prindëror anashkalohet. Prandaj krijimi/ndryshimi/fshirja kalojnë përmes funksioneve që verifikojnë PIN-in.
-- Rregull: sa kohë llogaria s'ka PIN prindëror (regjistrimi i ri), s'ka çfarë të mbrohet dhe funksionet lejohen.

-- Porta e përbashkët: 'ok' (PIN i saktë ose s'ka PIN të vendosur), 'wrong', 'locked'.
-- Me PIN të vendosur, mungesa e PIN-it është 'wrong' pa u numëruar si provë.
create function public.parent_gate(p_pin text) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  verdict text;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if not exists (select 1 from public.parental_pin where account_id = auth.uid()) then return 'ok'; end if;
  if p_pin is null then return 'wrong'; end if;
  verdict := public.check_parent_pin(p_pin);
  return case when verdict = 'not_set' then 'ok' else verdict end;
end $$;
revoke all on function public.parent_gate(text) from public, anon, authenticated;

-- Profilet: vetëm lexim i drejtpërdrejtë; shkrimi përmes funksioneve.
drop policy profiles_own on public.profiles;
create policy profiles_own_read on public.profiles for select to authenticated
  using (account_id = auth.uid());
revoke insert, update, delete on public.profiles from anon, authenticated;

-- Krijon profil. Kthen {"verdict":"ok","profile":{…}} ose {"verdict":"wrong"|"locked"}.
create function public.create_profile(
  p_name text, p_age public.age_group, p_avatar text, p_pin text default null
) returns jsonb
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  verdict text;
  row_ public.profiles;
begin
  verdict := public.parent_gate(p_pin);
  if verdict <> 'ok' then return jsonb_build_object('verdict', verdict); end if;
  insert into public.profiles (account_id, display_name, age_group, avatar)
  values (auth.uid(), p_name, p_age, coalesce(p_avatar, 'default'))
  returning * into row_;
  return jsonb_build_object('verdict', 'ok', 'profile', jsonb_build_object(
    'id', row_.id, 'account_id', row_.account_id, 'display_name', row_.display_name,
    'age_group', row_.age_group, 'avatar', row_.avatar));
end $$;

-- Ndryshon emrin, grupmoshën, avatarin. Kthen 'ok' | 'wrong' | 'locked'.
create function public.update_profile(
  p_profile uuid, p_name text, p_age public.age_group, p_avatar text, p_pin text
) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  verdict text;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if not public.owns_profile(p_profile) then raise exception 'profile_not_found'; end if;
  verdict := public.parent_gate(p_pin);
  if verdict <> 'ok' then return verdict; end if;
  update public.profiles
    set display_name = p_name, age_group = p_age, avatar = coalesce(p_avatar, avatar)
    where id = p_profile;
  -- Libri përfundimtar nuk duhet të mbetet nëse nuk i përshtatet më grupmoshës së re.
  update public.profile_limits l set end_content_id = null
    where l.profile_id = p_profile and l.end_content_id is not null
      and not exists (select 1 from public.content_items c
                      where c.id = l.end_content_id and c.status = 'published' and p_age = any (c.age_groups));
  -- Zhanret zgjidhen vetëm për 14+.
  if p_age <> 'g14_plus' then delete from public.profile_genres where profile_id = p_profile; end if;
  return 'ok';
end $$;

-- Fshin profilin me gjithçka të lidhur (progres, dëgjime, kufij, PIN). Kthen 'ok' | 'wrong' | 'locked'.
create function public.delete_profile(p_profile uuid, p_pin text) returns text
language plpgsql security definer set search_path = public, extensions, pg_temp as $$
declare
  verdict text;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if not public.owns_profile(p_profile) then raise exception 'profile_not_found'; end if;
  verdict := public.parent_gate(p_pin);
  if verdict <> 'ok' then return verdict; end if;
  delete from public.profiles where id = p_profile;
  return 'ok';
end $$;

-- Fshin llogarinë dhe çdo të dhënë të saj (kaskadë nga auth.users). Kthen 'ok' | 'wrong' | 'locked'.
-- Në Supabase i hostuar kërkon që pronari i funksionit të ketë të drejtë fshirjeje në auth.users (kontrollohet me projektin e vërtetë).
create function public.delete_my_account(p_pin text) returns text
language plpgsql security definer set search_path = public, extensions, auth, pg_temp as $$
declare
  verdict text;
begin
  verdict := public.parent_gate(p_pin);
  if verdict <> 'ok' then return verdict; end if;
  delete from auth.users where id = auth.uid();
  return 'ok';
end $$;

-- ---------- Zhanret ----------
insert into public.genres (slug, name_sq) values
  ('aventure', 'Aventurë'), ('fantazi', 'Fantazi'), ('mister', 'Mister'), ('histori', 'Histori'),
  ('shkence', 'Shkencë'), ('komedi', 'Komedi'), ('poezi', 'Poezi')
on conflict (slug) do nothing;

-- Deri në 3 zhanre për profil.
create function public.enforce_max_genres() returns trigger
language plpgsql as $$
begin
  if (select count(*) from public.profile_genres where profile_id = new.profile_id) >= 3 then
    raise exception 'max_genres_reached' using errcode = 'P0001';
  end if;
  return new;
end $$;
create trigger profile_genres_max_three before insert on public.profile_genres
  for each row execute function public.enforce_max_genres();

-- Eksporti përfshin edhe zhanret e zgjedhura (hash-et e PIN-eve nuk eksportohen kurrë).
create or replace 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),
    'profile_genres', coalesce((select jsonb_agg(to_jsonb(pg)) from public.profile_genres pg
                                join public.profiles p on p.id = pg.profile_id 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)
  )
$$;

-- ---------- Raportet për stafin (të agreguara, pa identifikues) ----------
-- Vetëm staf me 2FA. Asnjë llogari, profil ose emër nuk del jashtë: vetëm numra për titull dhe grupmoshë.
create function public.report_listening(p_from date, p_to date, p_tz text default 'Europe/Tirane')
returns table (content_id uuid, title text, type public.content_type, age_group public.age_group,
               total_seconds bigint, listeners integer, sessions integer)
language plpgsql stable security definer set search_path = public, pg_temp as $$
begin
  if not public.is_staff() then raise exception 'not_staff'; end if;
  if p_from is null or p_to is null or p_to < p_from or p_to - p_from > 366 then raise exception 'invalid_range'; end if;
  return query
    select c.id, c.title, c.type, p.age_group,
           sum(e.seconds_listened)::bigint,
           count(distinct e.profile_id)::integer,
           count(distinct e.session_id)::integer
    from public.listening_events e
    join public.profiles p on p.id = e.profile_id
    join public.content_items c on c.id = e.content_id
    where (e.started_at at time zone p_tz)::date between p_from and p_to
    group by c.id, c.title, c.type, p.age_group
    order by sum(e.seconds_listened) desc, c.title;
end $$;

create function public.report_listening_daily(p_from date, p_to date, p_tz text default 'Europe/Tirane')
returns table (day date, total_seconds bigint, active_profiles integer)
language plpgsql stable security definer set search_path = public, pg_temp as $$
begin
  if not public.is_staff() then raise exception 'not_staff'; end if;
  if p_from is null or p_to is null or p_to < p_from or p_to - p_from > 366 then raise exception 'invalid_range'; end if;
  return query
    select (e.started_at at time zone p_tz)::date, sum(e.seconds_listened)::bigint, count(distinct e.profile_id)::integer
    from public.listening_events e
    where (e.started_at at time zone p_tz)::date between p_from and p_to
    group by 1
    order by 1;
end $$;

-- ======================================================================
-- 0009_hardening.sql
-- ======================================================================
-- 0009: forcim sigurie (Seanca 6)
--  1) Prindërit nuk lexojnë më `content_items` drejtpërdrejt: kolonat rights_* (të drejtat, kontratat, shënimet) janë të brendshme.
--     Aplikacioni merr vetëm kolonat e nevojshme përmes `catalog_for_profile`.
--  2) Rivendosja e PIN-it prindëror kërkon hyrje të fresket me fjalëkalim (seancë e krijuar brenda 5 minutave).
--  3) Skedarët jetimë (që asnjë titull nuk i përdor më) mund t'i fshijë çdo staf; të përdorurit vetëm admini.

-- ---------- 1) Katalogu pa kolona të brendshme ----------
drop policy content_read on public.content_items;
create policy content_read on public.content_items for select to authenticated
  using (public.is_staff());

drop function public.catalog_for_profile(uuid);
create function public.catalog_for_profile(p_profile uuid)
returns table (
  id uuid, type public.content_type, title text, description text, age_groups public.age_group[],
  genre_id uuid, duration_seconds integer, cover_path text, audio_path text
)
language sql stable security definer set search_path = public, pg_temp as $$
  select c.id, c.type, c.title, c.description, c.age_groups, c.genre_id, c.duration_seconds, c.cover_path, c.audio_path
  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
$$;
revoke execute on function public.catalog_for_profile(uuid) from public, anon;
grant execute on function public.catalog_for_profile(uuid) to authenticated;

-- Politika e audios nuk mund të lexojë më `content_items` si prind: kontrolli bëhet nga funksion i brendshëm.
create function public.published_audio_exists(p_path text) returns boolean
language sql stable security definer set search_path = public, pg_temp as $$
  select exists (select 1 from public.content_items c where c.audio_path = p_path and c.status = 'published')
$$;

drop policy audio_read_published on storage.objects;
create policy audio_read_published on storage.objects for select to authenticated
  using (bucket_id = 'audio' and (public.is_staff() or public.published_audio_exists(name)));

-- ---------- 2) Rivendosja e PIN-it prindëror ----------
-- Kthen 'ok' | 'stale'. 'stale' = seanca nuk është krijuar brenda 5 minutave (nuk ka hyrje të fresket me fjalëkalim).
-- Rifreskimi i token-it nuk e ndryshon kohën e krijimit të seancës, pra një sesion i vjetër i hapur te fëmija nuk mjafton.
-- Formati i gabuar hedh gabim (nuk ndryshon asgjë).
create function public.reset_parent_pin(p_new_pin text) returns text
language plpgsql security definer set search_path = public, extensions, auth, pg_temp as $$
declare
  sid uuid;
  created timestamptz;
begin
  if auth.uid() is null then raise exception 'not_authenticated'; end if;
  if p_new_pin !~ '^[0-9]{4,6}$' then raise exception 'invalid_pin_format'; end if;
  begin
    sid := nullif(auth.jwt() ->> 'session_id', '')::uuid;
  exception when others then
    sid := null;
  end;
  if sid is null then return 'stale'; end if;
  select s.created_at into created from auth.sessions s where s.id = sid and s.user_id = auth.uid();
  if created is null or created < now() - interval '5 minutes' then return 'stale'; end if;

  insert into public.parental_pin (account_id, pin_hash)
  values (auth.uid(), crypt(p_new_pin, gen_salt('bf')))
  on conflict (account_id) do update
    set pin_hash = excluded.pin_hash, failed_attempts = 0, locked_until = null;
  return 'ok';
end $$;
revoke execute on function public.reset_parent_pin(text) from public, anon;
grant execute on function public.reset_parent_pin(text) to authenticated;

-- ---------- 3) Skedarët jetimë ----------
create function public.media_in_use(p_bucket text, p_path text) returns boolean
language sql stable security definer set search_path = public, pg_temp as $$
  select exists (
    select 1 from public.content_items c
    where (p_bucket = 'audio' and c.audio_path = p_path) or (p_bucket = 'covers' and c.cover_path = p_path)
  )
$$;

create policy media_staff_delete_orphan on storage.objects for delete to authenticated
  using (bucket_id in ('audio', 'covers') and public.is_staff() and not public.media_in_use(bucket_id, name));

-- ======================================================================
-- 0010_questions.sql
-- ======================================================================
-- 0010: pyetjet pas librit (Seanca 8)
-- Çdo libër mund të ketë deri në 10 pyetje me 2–4 zgjedhje. Aplikacioni i shfaq pasi libri mbaron; përgjigjet e fëmijës NUK ruhen (D-047).
-- Leximi: stafi drejtpërdrejt (aal2), aplikacioni vetëm përmes `questions_for_content` (titull i botuar, grupmosha e profilit të vetë përdoruesit).
-- Shkrimi: vetëm përmes `save_content_questions` (zëvendësim atomik, validim i plotë në DB).

create table public.content_questions (
  id uuid primary key default gen_random_uuid(),
  content_id uuid not null references public.content_items (id) on delete cascade,
  pos integer not null check (pos between 1 and 10),
  prompt text not null check (length(prompt) between 1 and 300),
  options text[] not null check (cardinality(options) between 2 and 4),
  correct_index integer not null,
  explanation text check (explanation is null or length(explanation) between 1 and 300),
  created_at timestamptz not null default now(),
  unique (content_id, pos),
  check (correct_index >= 0 and correct_index < cardinality(options))
);

alter table public.content_questions enable row level security;
create policy questions_staff_read on public.content_questions for select to authenticated using (public.is_staff());
revoke insert, update, delete on public.content_questions from authenticated, anon;
revoke all on public.content_questions from anon;

-- Zëvendëson pyetjet e një libri. p_questions: [{"prompt": "...", "options": ["..",".."], "correct": 0, "explanation": "..."|null}, ...]
-- Gabimet (kode në mesazh): forbidden, content_not_found, questions_only_books, questions_too_many, question_invalid.
create function public.save_content_questions(p_content uuid, p_questions jsonb) returns integer
language plpgsql security definer set search_path = public, pg_temp as $$
declare
  ctype public.content_type;
  q jsonb;
  i integer := 0;
  opts text[];
  prompt text;
  expl text;
  correct integer;
begin
  if not public.is_staff() then raise exception 'forbidden'; end if;
  select type into ctype from public.content_items where id = p_content;
  if ctype is null then raise exception 'content_not_found'; end if;
  if ctype <> 'book' then raise exception 'questions_only_books'; end if;
  if p_questions is null or jsonb_typeof(p_questions) <> 'array' then raise exception 'question_invalid'; end if;
  if jsonb_array_length(p_questions) > 10 then raise exception 'questions_too_many'; end if;

  -- Validimi para çdo ndryshimi: një pyetje e keqe nuk fshin ato ekzistuese.
  for q in select * from jsonb_array_elements(p_questions) loop
    if jsonb_typeof(q) <> 'object'
       or jsonb_typeof(q -> 'prompt') is distinct from 'string'
       or jsonb_typeof(q -> 'options') is distinct from 'array'
       or jsonb_typeof(q -> 'correct') is distinct from 'number' then
      raise exception 'question_invalid';
    end if;
    if exists (select 1 from jsonb_array_elements(q -> 'options') o where jsonb_typeof(o) <> 'string') then
      raise exception 'question_invalid';
    end if;
    prompt := btrim(q ->> 'prompt');
    select array_agg(btrim(o) order by ord) into opts from jsonb_array_elements_text(q -> 'options') with ordinality as t(o, ord);
    expl := nullif(btrim(coalesce(q ->> 'explanation', '')), '');
    if (q ->> 'correct') !~ '^[0-9]+$' then raise exception 'question_invalid'; end if;
    correct := (q ->> 'correct')::integer;
    if length(prompt) not between 1 and 300
       or cardinality(opts) not between 2 and 4
       or exists (select 1 from unnest(opts) x where length(x) not between 1 and 120)
       or correct >= cardinality(opts)
       or (expl is not null and length(expl) > 300) then
      raise exception 'question_invalid';
    end if;
  end loop;

  delete from public.content_questions where content_id = p_content;
  for q in select * from jsonb_array_elements(p_questions) loop
    i := i + 1;
    insert into public.content_questions (content_id, pos, prompt, options, correct_index, explanation)
    values (
      p_content, i, btrim(q ->> 'prompt'),
      (select array_agg(btrim(o) order by ord) from jsonb_array_elements_text(q -> 'options') with ordinality as t(o, ord)),
      (q ->> 'correct')::integer,
      nullif(btrim(coalesce(q ->> 'explanation', '')), '')
    );
  end loop;
  insert into public.audit_log (actor, action, entity, entity_id, details)
  values (auth.uid(), 'SAVE_QUESTIONS', 'content_items', p_content::text, jsonb_build_object('count', i));
  return i;
end $$;
revoke execute on function public.save_content_questions(uuid, jsonb) from public, anon;
grant execute on function public.save_content_questions(uuid, jsonb) to authenticated;

-- Pyetjet për aplikacionin: vetëm për titull të botuar që i përshtatet grupmoshës së profilit të vetë përdoruesit.
create function public.questions_for_content(p_profile uuid, p_content uuid)
returns table (id uuid, pos integer, prompt text, options text[], correct_index integer, explanation text)
language sql stable security definer set search_path = public, pg_temp as $$
  select q.id, q.pos, q.prompt, q.options, q.correct_index, q.explanation
  from public.content_questions q
  join public.content_items c on c.id = q.content_id and c.status = 'published' and c.type = 'book'
  join public.profiles p on p.id = p_profile and p.account_id = auth.uid() and p.age_group = any (c.age_groups)
  where q.content_id = p_content
  order by q.pos
$$;
revoke execute on function public.questions_for_content(uuid, uuid) from public, anon;
grant execute on function public.questions_for_content(uuid, uuid) to authenticated;