-- ============================================================
-- HeavyHaul Agent — Moderator Dashboard beyond the MVP (moderator dashboard
-- article, sections 3, 5, 8, 17-19, 21-24). Adds what the review console
-- needs to run the full workflow with today's data:
--
-- 1. review_tickets: AI-side triggers (§5), the asker's language (§11),
--    answer response time (§11), duplicate grouping (§23), close reason
--    (§19), resolution timestamp (§21 average resolution time), the
--    knowledge-update type (§17) and internal Q&A visibility class (§18).
-- 2. developer_issues: technical problems raised from a ticket with the
--    full context developers need (§24). Integration with an external
--    tracker is future work; the record is real now.
-- 3. moderation_settings: admin-configurable feedback categories and
--    state → reviewer assignments (§3, §22). Key/value jsonb.
-- ============================================================

alter table public.review_tickets
  drop constraint if exists review_tickets_trigger_check;
alter table public.review_tickets
  add constraint review_tickets_trigger_check
  check (trigger in ('thumbs_down', 'reported', 'low_confidence_repeat', 'no_source'));

alter table public.review_tickets
  add column if not exists language text,
  add column if not exists response_ms integer,
  add column if not exists duplicate_of uuid references public.review_tickets (id) on delete set null,
  add column if not exists close_reason text,
  add column if not exists resolved_at timestamptz,
  add column if not exists update_type text
    check (update_type is null or update_type in
      ('state_note', 'qa_entry', 'prompt_logic', 'retrieval', 'ocr_parsing', 'product_ui')),
  add column if not exists qa_visibility text
    check (qa_visibility is null or qa_visibility in
      ('internal_only', 'broker_facing', 'carrier_facing', 'driver_facing',
       'processing_team_only', 'training_only', 'do_not_use'));
create index if not exists review_tickets_duplicate_of_idx on public.review_tickets (duplicate_of);

comment on column public.review_tickets.resolved_at is
  'Set when the ticket reaches a terminal status (closed, no change needed, knowledge updated, duplicate). Drives average resolution time.';
comment on column public.review_tickets.duplicate_of is
  'Master ticket this one was merged into (§23 repeated-issue grouping).';

create table if not exists public.developer_issues (
  id uuid primary key default gen_random_uuid(),
  ticket_id uuid not null references public.review_tickets (id) on delete cascade,
  error_category text not null,
  summary text,
  context jsonb,
  status text not null default 'open' check (status in ('open', 'done')),
  created_by text,
  created_at timestamptz not null default now()
);
create index if not exists developer_issues_ticket_idx on public.developer_issues (ticket_id);

create table if not exists public.moderation_settings (
  key text primary key,
  value jsonb not null,
  updated_by text,
  updated_at timestamptz not null default now()
);

alter table public.developer_issues enable row level security;
alter table public.moderation_settings enable row level security;