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