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