-- ============================================================
-- HeavyHaul Agent — broker defaults (intake doc §22-23) + moderation
-- review tickets (moderator dashboard doc, 2026-09-07).
--
-- 1. Broker default permit-handling rules: preselect/lock the broker's
--    order form and drive the public intake page. 'ask' = choose per trip.
--
-- 2. Review tickets: every negative feedback (thumbs down / report / ask for
--    human review) creates a ticket with FULL context — "feedback is a
--    review trigger, not truth". Data model is day-one complete (§33):
--    source versions, channel, audience, correction workflow fields,
--    confidence review, source alignment. AI-backend fields (sources,
--    source_versions, model/prompt versions) fill for real later.
-- ============================================================

alter table public.broker_pages
  add column default_permit_policy text not null default 'ask'
    check (default_permit_policy in ('synchron_required', 'upload_allowed', 'ask')),
  add column default_payment_party text not null default 'ask'
    check (default_payment_party in ('broker', 'carrier', 'ask'));

create table public.review_tickets (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  message_id uuid not null references public.chat_messages (id) on delete cascade,
  question_message_id uuid references public.chat_messages (id) on delete set null,
  user_id uuid,
  user_role text,
  state_code text,
  permit_id uuid references public.permits (id) on delete set null,
  confidence text,
  trigger text not null default 'thumbs_down' check (trigger in ('thumbs_down', 'reported')),
  feedback_reason text,
  user_comment text,
  category text,
  priority text check (priority in ('high', 'medium', 'low')),
  status text not null default 'new' check (status in (
    'new', 'in_review', 'needs_more_context', 'escalated', 'correction_proposed',
    'approved_for_knowledge_update', 'knowledge_updated', 'no_change_needed',
    'closed', 'duplicate'
  )),
  assigned_to text,
  moderator_notes text,
  decision text,
  -- Correction workflow (§16)
  correction text,
  correction_explanation text,
  correction_source text,
  correction_state text,
  correction_topic text,
  applies_to text check (applies_to in ('this_permit', 'all_permits')),
  applies_states text check (applies_states in ('one_state', 'multiple_states')),
  update_state_notes boolean not null default false,
  create_qa boolean not null default false,
  update_prompt_logic boolean not null default false,
  escalate_developer boolean not null default false,
  -- Confidence appropriateness (§15)
  confidence_review text check (confidence_review in (
    'appropriate', 'too_high', 'too_low', 'should_have_escalated', 'missing'
  )),
  -- Source alignment (§14) — manual in the MVP, auto-detected later
  source_alignment text check (source_alignment in (
    'permit_matches_provision', 'permit_conflicts_provision', 'notes_add_exception',
    'internal_qa_used', 'no_permit_available', 'low_source_confidence'
  )),
  -- Context captured with the answer (§6; AI backend fills the rest)
  request_reference text,
  sources jsonb,
  channel text not null default 'text',
  audience text,
  ui_context text,
  source_versions jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index review_tickets_status_idx on public.review_tickets (status, created_at);
create index review_tickets_trip_idx on public.review_tickets (trip_id);

-- Audit trail: moderators never erase what happened (§20).
create table public.ticket_events (
  id uuid primary key default gen_random_uuid(),
  ticket_id uuid not null references public.review_tickets (id) on delete cascade,
  actor text,
  action text not null,
  detail jsonb,
  created_at timestamptz not null default now()
);
create index ticket_events_ticket_idx on public.ticket_events (ticket_id, created_at);

alter table public.review_tickets enable row level security;
alter table public.ticket_events enable row level security;