-- 74-exam-marker-layout.sql
-- S5 exam-marker layout layer + AI/manual seam provenance.
--
-- Adds the per-page layout projection that the docling auto-map pipeline writes to, plus
-- provenance columns needed to distinguish unconfirmed AI suggestions from teacher/manual data.
-- Safe/idempotent on top of 72-exam-marker.sql + 73-exam-marker-regions.sql.

--==========================================================================================
-- 1. exam_template_layout: stage-2 per-page layout layer
--==========================================================================================
create table if not exists public.exam_template_layout (
    id uuid primary key default gen_random_uuid(),
    template_id uuid not null references public.exam_templates(id) on delete cascade,
    page_index int not null,
    role text,
    margin_left numeric,
    margin_right numeric,
    margin_top numeric,
    margin_bottom numeric,
    margins_enabled boolean not null default true,
    source text not null default 'manual' check (source in ('manual','ai')),
    confirmed boolean not null default true,
    confidence numeric,
    derivation text,
    meta jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default timezone('utc', now()),
    updated_at timestamptz not null default timezone('utc', now()),
    unique (template_id, page_index)
);

comment on table public.exam_template_layout is
  'Per-template/per-page layout projection for exam-marker auto-map review: page role, nullable margins, provenance and future layout-profile metadata.';
comment on column public.exam_template_layout.role is
  'Page role such as cover|question|continuation|blank|appendix; nullable when unknown/manual draft.';
comment on column public.exam_template_layout.meta is
  'Forward-compatible layout metadata: columns, front-matter, appendix/blank markers, future profile_id linkage, etc.';

create index if not exists idx_exam_template_layout_template on public.exam_template_layout(template_id);
create index if not exists idx_exam_template_layout_source_confirmed on public.exam_template_layout(template_id, source, confirmed);

drop trigger if exists handle_exam_template_layout_updated_at on public.exam_template_layout;
create trigger handle_exam_template_layout_updated_at before update on public.exam_template_layout
  for each row execute function public.handle_updated_at();

--==========================================================================================
-- 2. AI seam/provenance columns on existing physical template tables
--==========================================================================================
-- Existing/manual rows default to authoritative manual data. Auto-map rows must explicitly set
-- source='ai' and confirmed=false so reruns can refresh only unconfirmed AI suggestions.
alter table public.exam_questions add column if not exists source text not null default 'manual' check (source in ('manual','ai'));
alter table public.exam_questions add column if not exists confirmed boolean not null default true;
alter table public.exam_questions add column if not exists confidence numeric;
alter table public.exam_questions add column if not exists derivation text;

alter table public.exam_response_areas add column if not exists mark_subtype text;
alter table public.exam_response_areas add column if not exists derivation text;

alter table public.exam_response_areas drop constraint if exists exam_response_areas_mark_subtype_check;
alter table public.exam_response_areas add constraint exam_response_areas_mark_subtype_check
  check (
    mark_subtype is null
    or (kind = 'mark_area' and mark_subtype in ('part_marks','question_total','grader_box'))
  );

alter table public.exam_boundaries add column if not exists confidence numeric;
alter table public.exam_boundaries add column if not exists derivation text;

-- Confidence values are normalized model/layout confidence scores when present.
alter table public.exam_template_layout drop constraint if exists exam_template_layout_confidence_check;
alter table public.exam_template_layout add constraint exam_template_layout_confidence_check
  check (confidence is null or (confidence >= 0 and confidence <= 1));
alter table public.exam_questions drop constraint if exists exam_questions_confidence_check;
alter table public.exam_questions add constraint exam_questions_confidence_check
  check (confidence is null or (confidence >= 0 and confidence <= 1));
alter table public.exam_response_areas drop constraint if exists exam_response_areas_confidence_check;
alter table public.exam_response_areas add constraint exam_response_areas_confidence_check
  check (confidence is null or (confidence >= 0 and confidence <= 1));
alter table public.exam_boundaries drop constraint if exists exam_boundaries_confidence_check;
alter table public.exam_boundaries add constraint exam_boundaries_confidence_check
  check (confidence is null or (confidence >= 0 and confidence <= 1));

comment on column public.exam_questions.derivation is
  'Lightweight provenance such as ai|manual|boundary_derived|margin_derived|layout_derived.';
comment on column public.exam_response_areas.mark_subtype is
  'Only meaningful for kind=mark_area: part_marks|question_total|grader_box. Grader boxes are persisted only when explicitly detected/provided.';
comment on column public.exam_response_areas.derivation is
  'Lightweight provenance such as ai|manual|detected|layout_derived.';
comment on column public.exam_boundaries.derivation is
  'Lightweight provenance such as ai|manual|band_derived.';

create index if not exists idx_exam_questions_source_confirmed on public.exam_questions(template_id, source, confirmed);
create index if not exists idx_exam_regions_source_confirmed on public.exam_response_areas(template_id, source, confirmed);
create index if not exists idx_exam_boundaries_source_confirmed on public.exam_boundaries(template_id, source, confirmed);

--==========================================================================================
-- 3. RLS — mirror exam_templates ownership/institute scope via the owning template
--==========================================================================================
alter table public.exam_template_layout enable row level security;

drop policy if exists exam_template_layout_service on public.exam_template_layout;
create policy exam_template_layout_service on public.exam_template_layout
  using (auth.role() = 'service_role');

drop policy if exists exam_template_layout_all on public.exam_template_layout;
create policy exam_template_layout_all on public.exam_template_layout for all to authenticated
  using (exists (select 1 from public.exam_templates t
                 where t.id = exam_template_layout.template_id
                   and t.institute_id in (select public.user_institute_ids())))
  with check (exists (select 1 from public.exam_templates t
                 where t.id = exam_template_layout.template_id
                   and t.teacher_id = auth.uid()
                   and t.institute_id in (select public.user_institute_ids())));
