-- Personal report persistence for self-hosted PostgreSQL (staging).
-- Mirrors supabase/migrations/20260806010000_personal_reports.sql
-- one-to-one in table shape, constraints, RLS and grants.
--
-- Ownership model: rows are owned by auth.users(id) (the business-auth
-- mirror kept in sync by identity.sync_user_to_business_auth). Normal
-- application sessions connect as app_runtime (member of authenticated) and
-- set local role authenticated; they may select/delete only their own rows
-- through RLS plus the explicit owner grants below, and can never
-- insert/update (generation and status writes are performed exclusively
-- through service_role, which has BYPASSRLS and full table privileges).
-- admin_runtime has no direct access to report bodies (least privilege);
-- server-side generation runs through service_role, which admin_runtime may
-- SET ROLE to.
--
-- Idempotency: unique (user_id, request_id) is the primary lock; replay of a
-- known requestId requires the same request_fingerprint (a sha256 of the
-- caller's request intent), so a different payload under the same requestId
-- surfaces as request_conflict instead of silently overwriting. Failed
-- retries are not implicitly upserted here; callers either reuse the failed
-- record via a new requestId or surface the stable failure.
--
-- No birth details, report bodies, model prompts or exception stacks are ever
-- written to logs, index columns or audit events; failure_code is a stable
-- enum shared with frontend/src/lib/personal-report-service.ts and
-- scripts/personal_report_contract.py.

create table if not exists public.personal_reports (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  session_id uuid,
  chart_profile_id uuid,
  request_id uuid not null,
  request_fingerprint text not null check (request_fingerprint ~ '^[0-9a-f]{64}$'),
  report_type text not null check (report_type in ('personal_full', 'personal_thematic')),
  status text not null check (status in ('generating', 'ready', 'failed')),
  schema_version text not null check (schema_version = 'report_document.v1'),
  presentation_mode text not null check (presentation_mode in ('default', 'research')),
  requested_themes text[] not null default '{}'::text[],
  report_document jsonb,
  calculation_hash text check (calculation_hash is null or calculation_hash ~ '^[0-9a-f]{64}$'),
  evidence_hash text check (evidence_hash is null or evidence_hash ~ '^[0-9a-f]{64}$'),
  skill_source_commit text check (skill_source_commit is null or skill_source_commit ~ '^[0-9a-f]{40}$'),
  skill_snapshot_sha256 text not null check (skill_snapshot_sha256 ~ '^[0-9a-f]{64}$'),
  failure_code text check (failure_code in (
    'profile_incomplete',
    'birth_time_not_usable',
    'report_generation_in_progress',
    'report_rate_limited',
    'calculation_unavailable',
    'model_unavailable',
    'report_schema_invalid',
    'report_guard_rejected',
    'report_not_found'
  )),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  completed_at timestamptz,
  check ((status = 'ready') = (report_document is not null)),
  check ((status = 'ready') = (completed_at is not null)),
  check ((status = 'ready') = (calculation_hash is not null)),
  check ((status = 'ready') = (evidence_hash is not null)),
  check ((status = 'failed') = (failure_code is not null)),
  unique (user_id, request_id)
);

create index if not exists personal_reports_user_created_idx
  on public.personal_reports (user_id, created_at desc);

-- One in-flight generation per user, enforced by the database so a second
-- request cannot start while the first is still generating.
create unique index if not exists personal_reports_one_generating_per_user
  on public.personal_reports (user_id)
  where status = 'generating';

alter table public.personal_reports enable row level security;

revoke all on table public.personal_reports from public, anon, authenticated, service_role;
revoke all on table public.personal_reports from app_runtime, admin_runtime, migration_runner, backup_reader;

drop policy if exists personal_reports_select_own on public.personal_reports;
create policy personal_reports_select_own
  on public.personal_reports
  for select
  to authenticated
  using (auth.uid() = user_id);

drop policy if exists personal_reports_delete_own on public.personal_reports;
create policy personal_reports_delete_own
  on public.personal_reports
  for delete
  to authenticated
  using (auth.uid() = user_id);

-- Normal users can never insert or update rows: creating a generating record
-- and moving it to ready/failed are server-side operations only. RLS
-- policies alone do not grant table privileges, so the owner read/delete
-- grants below are required for the policies to be reachable.
grant select, delete on table public.personal_reports to authenticated;

-- admin_runtime intentionally has no direct access to report bodies (least
-- privilege); server-side generation runs through service_role, which
-- admin_runtime may SET ROLE to.
grant select, insert, update, delete on table public.personal_reports to service_role;