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