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