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