-- Terra Vita Hub v172 - Global Deployment Database Readiness
-- Purpose: prepare the Supabase/Postgres database for global, multi-programme deployment.
-- Status: idempotent hardening foundation. Review and run in STAGING before production.
-- Safety: creates governance/readiness tables, audit registers, background-job queue structures,
--         views, indexes, and RLS policies. It does not drop data or alter existing business logic.

begin;

create extension if not exists pgcrypto;

-- ---------------------------------------------------------------------------
-- 1. Admin helper
-- ---------------------------------------------------------------------------
create or replace function public.tvh_current_user_is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.profiles p
    where p.id = auth.uid()
      and coalesce(p.role, '') in ('admin', 'super_admin')
  );
$$;

revoke all on function public.tvh_current_user_is_admin() from public;
grant execute on function public.tvh_current_user_is_admin() to authenticated;
grant execute on function public.tvh_current_user_is_admin() to service_role;

create or replace function public.tvh_set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- ---------------------------------------------------------------------------
-- 2. Global deployment control register
-- ---------------------------------------------------------------------------
create table if not exists public.global_deployment_controls (
  id uuid primary key default gen_random_uuid(),
  control_key text not null unique,
  domain text not null,
  control_name text not null,
  assurance_target text not null,
  status text not null default 'not_started'
    check (status in ('not_started','in_progress','ready','blocked','deferred','accepted_risk')),
  priority text not null default 'high'
    check (priority in ('critical','high','medium','low')),
  required_before_global_launch boolean not null default true,
  owner_role text,
  evidence_expected text,
  evidence_url text,
  notes text,
  due_at timestamptz,
  reviewed_at timestamptz,
  reviewed_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

drop trigger if exists trg_global_deployment_controls_updated_at on public.global_deployment_controls;
create trigger trg_global_deployment_controls_updated_at
before update on public.global_deployment_controls
for each row execute function public.tvh_set_updated_at();

alter table public.global_deployment_controls enable row level security;

drop policy if exists "global deployment controls admin read" on public.global_deployment_controls;
create policy "global deployment controls admin read"
on public.global_deployment_controls
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "global deployment controls admin write" on public.global_deployment_controls;
create policy "global deployment controls admin write"
on public.global_deployment_controls
for all
using (public.tvh_current_user_is_admin())
with check (public.tvh_current_user_is_admin());

create index if not exists idx_gdc_domain_status_priority
  on public.global_deployment_controls(domain, status, priority);

create index if not exists idx_gdc_required_status
  on public.global_deployment_controls(required_before_global_launch, status);

-- Seed minimum global-readiness controls.
insert into public.global_deployment_controls
(control_key, domain, control_name, assurance_target, status, priority, required_before_global_launch, owner_role, evidence_expected)
values
('prod_staging_separation','Environment separation','Production and staging Supabase projects are separated','Risky fixes and migrations are tested outside production','not_started','critical',true,'admin / technical owner','Production and staging project IDs recorded; environment variables separated'),
('rls_coverage_audit','Access control','RLS coverage audit completed for sensitive tables','Programme, organisation, user, investor, DFI, and admin roles cannot cross boundaries','not_started','critical',true,'admin / security owner','RLS audit output and role-test results'),
('critical_indexes_verified','Performance','Critical tenant, programme, document, funding, MRV, and audit indexes verified','Core pages remain responsive under institutional load','not_started','high',true,'technical owner','Index inventory and slow-query review'),
('connection_pooling_review','Connection management','Serverless connection pooling and API usage reviewed','Netlify/serverless functions do not exhaust database connections','not_started','high',true,'technical owner','Connection profile and Supavisor/pooling notes'),
('heavy_jobs_queued','Background processing','Heavy AI/export/satellite/TV-CRI tasks moved to controlled job queue','Page loads do not run expensive synthesis or export generation synchronously','not_started','high',true,'technical owner','Queue design, job runner responsibility, and retry policy'),
('pitr_backup_restore_drill','Backup and restore','PITR/backups configured and restore drill completed','Production data can be restored within the accepted recovery posture','not_started','critical',true,'admin / technical owner','Backup setting, restore test note, RPO/RTO acceptance'),
('monitoring_alerting','Monitoring','Database, function, auth, storage, and error monitoring configured','Operational issues are detected before they become programme failures','not_started','high',true,'technical owner','Monitoring dashboard and alert recipients'),
('data_sovereignty_memo','Data sovereignty','Data-residency and export-control memo approved per deployment','Jurisdictional requirements are documented before onboarding sensitive programmes','not_started','critical',true,'programme owner / legal','Approved sovereignty memo per deployment'),
('load_test_profile','Load testing','Pilot, programme, institutional, and global load scenarios tested','Capacity decisions are evidence-based, not assumed','not_started','high',true,'technical owner','Load-test results and scaling thresholds'),
('incident_response_path','Continuity','Incident response and continuity route confirmed','Ministries/DFIs know who acts during downtime, breach, or access failure','not_started','high',true,'admin / governance owner','Escalation contacts and continuity drill note')
on conflict (control_key) do update
set
  domain = excluded.domain,
  control_name = excluded.control_name,
  assurance_target = excluded.assurance_target,
  priority = excluded.priority,
  required_before_global_launch = excluded.required_before_global_launch,
  owner_role = excluded.owner_role,
  evidence_expected = excluded.evidence_expected,
  updated_at = now();

-- ---------------------------------------------------------------------------
-- 3. Data sovereignty and export controls
-- ---------------------------------------------------------------------------
create table if not exists public.data_sovereignty_configurations (
  id uuid primary key default gen_random_uuid(),
  deployment_key text not null unique,
  country_code text,
  jurisdiction text,
  hosting_region text,
  data_residency_requirement text,
  sensitive_data_classes text[] not null default '{}',
  export_control_rules jsonb not null default '{}'::jsonb,
  retention_policy_key text,
  status text not null default 'draft'
    check (status in ('draft','under_review','approved','blocked','retired')),
  approved_by uuid references auth.users(id) on delete set null,
  approved_at timestamptz,
  evidence_url text,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

drop trigger if exists trg_data_sovereignty_configurations_updated_at on public.data_sovereignty_configurations;
create trigger trg_data_sovereignty_configurations_updated_at
before update on public.data_sovereignty_configurations
for each row execute function public.tvh_set_updated_at();

alter table public.data_sovereignty_configurations enable row level security;

drop policy if exists "data sovereignty admin read" on public.data_sovereignty_configurations;
create policy "data sovereignty admin read"
on public.data_sovereignty_configurations
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "data sovereignty admin write" on public.data_sovereignty_configurations;
create policy "data sovereignty admin write"
on public.data_sovereignty_configurations
for all
using (public.tvh_current_user_is_admin())
with check (public.tvh_current_user_is_admin());

create index if not exists idx_data_sovereignty_status_region
  on public.data_sovereignty_configurations(status, hosting_region);

create table if not exists public.data_export_audit_log (
  id uuid primary key default gen_random_uuid(),
  created_at timestamptz not null default now(),
  user_id uuid references auth.users(id) on delete set null,
  actor_email text,
  export_type text not null,
  target_scope text not null,
  programme_id uuid,
  project_code text,
  organization_id uuid,
  destination text,
  legal_basis text,
  export_control_result text not null default 'not_evaluated'
    check (export_control_result in ('not_evaluated','approved','blocked','exception')),
  metadata jsonb not null default '{}'::jsonb
);

alter table public.data_export_audit_log enable row level security;

drop policy if exists "data export audit admin read" on public.data_export_audit_log;
create policy "data export audit admin read"
on public.data_export_audit_log
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "data export audit authenticated insert own" on public.data_export_audit_log;
create policy "data export audit authenticated insert own"
on public.data_export_audit_log
for insert
with check (auth.uid() = user_id);

create index if not exists idx_data_export_created
  on public.data_export_audit_log(created_at desc);

create index if not exists idx_data_export_scope
  on public.data_export_audit_log(target_scope, export_type, created_at desc);

create index if not exists idx_data_export_project
  on public.data_export_audit_log(project_code, created_at desc);

create or replace function public.record_data_export_audit(
  p_export_type text,
  p_target_scope text,
  p_destination text default null,
  p_legal_basis text default null,
  p_metadata jsonb default '{}'::jsonb,
  p_project_code text default null,
  p_programme_id uuid default null,
  p_organization_id uuid default null
)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_id uuid;
  v_actor_email text;
begin
  select p.email into v_actor_email
  from public.profiles p
  where p.id = auth.uid();

  insert into public.data_export_audit_log (
    user_id, actor_email, export_type, target_scope, destination, legal_basis,
    metadata, project_code, programme_id, organization_id
  )
  values (
    auth.uid(), v_actor_email, p_export_type, p_target_scope, p_destination, p_legal_basis,
    coalesce(p_metadata, '{}'::jsonb), p_project_code, p_programme_id, p_organization_id
  )
  returning id into v_id;

  return v_id;
end;
$$;

grant execute on function public.record_data_export_audit(text, text, text, text, jsonb, text, uuid, uuid) to authenticated;
grant execute on function public.record_data_export_audit(text, text, text, text, jsonb, text, uuid, uuid) to service_role;

-- ---------------------------------------------------------------------------
-- 4. Background job readiness for heavy work
-- ---------------------------------------------------------------------------
create table if not exists public.platform_background_jobs (
  id uuid primary key default gen_random_uuid(),
  job_type text not null,
  scope text not null default 'platform',
  programme_id uuid,
  project_code text,
  organization_id uuid,
  status text not null default 'queued'
    check (status in ('queued','running','succeeded','failed','cancelled','deferred')),
  priority integer not null default 50,
  payload jsonb not null default '{}'::jsonb,
  result jsonb not null default '{}'::jsonb,
  attempts integer not null default 0,
  max_attempts integer not null default 3,
  scheduled_at timestamptz not null default now(),
  started_at timestamptz,
  completed_at timestamptz,
  locked_by text,
  last_error text,
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

drop trigger if exists trg_platform_background_jobs_updated_at on public.platform_background_jobs;
create trigger trg_platform_background_jobs_updated_at
before update on public.platform_background_jobs
for each row execute function public.tvh_set_updated_at();

alter table public.platform_background_jobs enable row level security;

drop policy if exists "platform background jobs admin read" on public.platform_background_jobs;
create policy "platform background jobs admin read"
on public.platform_background_jobs
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "platform background jobs admin write" on public.platform_background_jobs;
create policy "platform background jobs admin write"
on public.platform_background_jobs
for all
using (public.tvh_current_user_is_admin())
with check (public.tvh_current_user_is_admin());

create index if not exists idx_platform_jobs_claim
  on public.platform_background_jobs(status, priority, scheduled_at)
  where status = 'queued';

create index if not exists idx_platform_jobs_scope_created
  on public.platform_background_jobs(scope, created_at desc);

create index if not exists idx_platform_jobs_project
  on public.platform_background_jobs(project_code, status, created_at desc);

create table if not exists public.platform_background_job_events (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references public.platform_background_jobs(id) on delete cascade,
  event_type text not null,
  message text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

alter table public.platform_background_job_events enable row level security;

drop policy if exists "platform background job events admin read" on public.platform_background_job_events;
create policy "platform background job events admin read"
on public.platform_background_job_events
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "platform background job events admin write" on public.platform_background_job_events;
create policy "platform background job events admin write"
on public.platform_background_job_events
for all
using (public.tvh_current_user_is_admin())
with check (public.tvh_current_user_is_admin());

create index if not exists idx_platform_job_events_job_created
  on public.platform_background_job_events(job_id, created_at desc);

-- ---------------------------------------------------------------------------
-- 5. Risk assurance, continuity, and MRV quality registers
-- ---------------------------------------------------------------------------
create table if not exists public.risk_assurance_register (
  id uuid primary key default gen_random_uuid(),
  artifact_key text not null,
  audience text not null,
  domain text not null,
  assurance_level text,
  residual_risk text,
  mitigation_summary text,
  status text not null default 'draft'
    check (status in ('draft','in_review','approved','retired')),
  artifact_url text,
  reviewed_by uuid references auth.users(id) on delete set null,
  reviewed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (artifact_key, audience)
);

drop trigger if exists trg_risk_assurance_register_updated_at on public.risk_assurance_register;
create trigger trg_risk_assurance_register_updated_at
before update on public.risk_assurance_register
for each row execute function public.tvh_set_updated_at();

alter table public.risk_assurance_register enable row level security;

drop policy if exists "risk assurance register admin read" on public.risk_assurance_register;
create policy "risk assurance register admin read"
on public.risk_assurance_register
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "risk assurance register admin write" on public.risk_assurance_register;
create policy "risk assurance register admin write"
on public.risk_assurance_register
for all
using (public.tvh_current_user_is_admin())
with check (public.tvh_current_user_is_admin());

insert into public.risk_assurance_register
(artifact_key, audience, domain, assurance_level, residual_risk, mitigation_summary, status, artifact_url)
values
('risk_assurance_note_dfi_grade','DFIs / climate funds / oversight bodies','Fiduciary and governance assurance','High','Low to Moderate','Governance-boundary briefing, reviewer discipline, role mapping, and committee endorsement','approved','/portal/governance/artifacts/risk-assurance-note-dfi-grade.pdf'),
('governance_risk_posture_brief_ministries','Ministries / public reviewers','Public trust and statutory boundary','Medium-High','Moderate','Public summary with protected-workspace escalation to full framework','approved','/docs/governance-risk-posture-brief.html'),
('risk_management_annex_donor_submission','EU / GCF / MDB donors','Donor submission risk management','High','Low to Moderate','Appended to donor submissions with evidence/export posture','approved','/portal/exports/artifacts/risk-management-annex-donor-submission.pdf'),
('institutional_safeguards_compliance_framework','Ministries / DFIs / auditors','Safeguards and compliance','High','Low to Moderate','Safeguards, grievance, consultation, MRV, and export controls bound to governance spine','approved','/portal/governance/artifacts/institutional-safeguards-compliance-framework.pdf')
on conflict (artifact_key, audience) do update
set domain = excluded.domain,
    assurance_level = excluded.assurance_level,
    residual_risk = excluded.residual_risk,
    mitigation_summary = excluded.mitigation_summary,
    status = excluded.status,
    artifact_url = excluded.artifact_url,
    updated_at = now();

create table if not exists public.operational_continuity_tests (
  id uuid primary key default gen_random_uuid(),
  deployment_key text not null,
  test_type text not null,
  test_status text not null default 'planned'
    check (test_status in ('planned','passed','failed','waived')),
  tested_at timestamptz,
  tested_by uuid references auth.users(id) on delete set null,
  rto_minutes integer,
  rpo_minutes integer,
  findings text,
  evidence_url text,
  next_test_due_at timestamptz,
  created_at timestamptz not null default now()
);

alter table public.operational_continuity_tests enable row level security;

drop policy if exists "operational continuity tests admin read" on public.operational_continuity_tests;
create policy "operational continuity tests admin read"
on public.operational_continuity_tests
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "operational continuity tests admin write" on public.operational_continuity_tests;
create policy "operational continuity tests admin write"
on public.operational_continuity_tests
for all
using (public.tvh_current_user_is_admin())
with check (public.tvh_current_user_is_admin());

create index if not exists idx_continuity_deployment_status
  on public.operational_continuity_tests(deployment_key, test_status, tested_at desc);

create table if not exists public.mrv_quality_checks (
  id uuid primary key default gen_random_uuid(),
  programme_id uuid,
  project_code text,
  indicator_key text not null,
  check_status text not null default 'pending'
    check (check_status in ('pending','passed','conditional','failed','waived')),
  evidence_requirement text,
  evidence_url text,
  reviewer_id uuid references auth.users(id) on delete set null,
  reviewer_notes text,
  checked_at timestamptz,
  created_at timestamptz not null default now()
);

alter table public.mrv_quality_checks enable row level security;

drop policy if exists "mrv quality checks admin read" on public.mrv_quality_checks;
create policy "mrv quality checks admin read"
on public.mrv_quality_checks
for select
using (public.tvh_current_user_is_admin());

drop policy if exists "mrv quality checks admin write" on public.mrv_quality_checks;
create policy "mrv quality checks admin write"
on public.mrv_quality_checks
for all
using (public.tvh_current_user_is_admin())
with check (public.tvh_current_user_is_admin());

create index if not exists idx_mrv_quality_project_indicator
  on public.mrv_quality_checks(project_code, indicator_key, check_status, created_at desc);

-- ---------------------------------------------------------------------------
-- 6. Conditional indexes for existing high-use tables
-- ---------------------------------------------------------------------------
create or replace function public.tvh_create_index_if_columns(
  p_schema text,
  p_table text,
  p_index_name text,
  p_required_columns text[],
  p_index_expression text
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_has_all boolean;
begin
  if to_regclass(format('%I.%I', p_schema, p_table)) is null then
    return;
  end if;

  select bool_and(exists (
    select 1 from information_schema.columns c
    where c.table_schema = p_schema
      and c.table_name = p_table
      and c.column_name = col.column_name
  ))
  into v_has_all
  from unnest(p_required_columns) as col(column_name);

  if coalesce(v_has_all, false) then
    execute format('create index if not exists %I on %I.%I (%s)', p_index_name, p_schema, p_table, p_index_expression);
  end if;
end;
$$;

select public.tvh_create_index_if_columns('public','projects','idx_tvh_projects_programme_status_created', array['programme_id','status','created_at'], 'programme_id, status, created_at desc');
select public.tvh_create_index_if_columns('public','projects','idx_tvh_projects_project_code', array['project_code'], 'project_code');
select public.tvh_create_index_if_columns('public','project_documents','idx_tvh_project_documents_project_created', array['project_id','created_at'], 'project_id, created_at desc');
select public.tvh_create_index_if_columns('public','project_documents','idx_tvh_project_documents_code_created', array['project_code','created_at'], 'project_code, created_at desc');
select public.tvh_create_index_if_columns('public','document_workflows','idx_tvh_document_workflows_project_status', array['project_code','status','updated_at'], 'project_code, status, updated_at desc');
select public.tvh_create_index_if_columns('public','document_publications','idx_tvh_document_publications_project_status', array['project_code','status','created_at'], 'project_code, status, created_at desc');
select public.tvh_create_index_if_columns('public','workspace_access','idx_tvh_workspace_access_user_status', array['user_id','status'], 'user_id, status');
select public.tvh_create_index_if_columns('public','workspace_access','idx_tvh_workspace_access_project_role', array['project_code','role'], 'project_code, role');
select public.tvh_create_index_if_columns('public','blue_evidence_items','idx_tvh_blue_evidence_module_status_created', array['module_key','status','created_at'], 'module_key, status, created_at desc');
select public.tvh_create_index_if_columns('public','blue_evidence_reviews','idx_tvh_blue_reviews_item_created', array['evidence_item_id','created_at'], 'evidence_item_id, created_at desc');
select public.tvh_create_index_if_columns('public','funding_milestones','idx_tvh_funding_milestones_program_status', array['programme_id','status'], 'programme_id, status');
select public.tvh_create_index_if_columns('public','funding_tranches','idx_tvh_funding_tranches_program_status_created', array['programme_id','status','created_at'], 'programme_id, status, created_at desc');
select public.tvh_create_index_if_columns('public','funding_release_log','idx_tvh_funding_release_project_created', array['project_code','created_at'], 'project_code, created_at desc');
select public.tvh_create_index_if_columns('public','mrv_measurements','idx_tvh_mrv_measurements_project_recorded', array['project_code','recorded_at'], 'project_code, recorded_at desc');
select public.tvh_create_index_if_columns('public','mrv_measurements','idx_tvh_mrv_measurements_pilot_recorded', array['pilot_id','recorded_at'], 'pilot_id, recorded_at desc');
select public.tvh_create_index_if_columns('public','mrv_reports','idx_tvh_mrv_reports_project_created', array['project_code','created_at'], 'project_code, created_at desc');
select public.tvh_create_index_if_columns('public','satellite_observations','idx_tvh_satellite_project_observed', array['project_code','observed_at'], 'project_code, observed_at desc');
select public.tvh_create_index_if_columns('public','satellite_refresh_queue','idx_tvh_satellite_refresh_status_scheduled', array['status','scheduled_at'], 'status, scheduled_at');
select public.tvh_create_index_if_columns('public','notifications','idx_tvh_notifications_user_read_created', array['user_id','read_at','created_at'], 'user_id, read_at, created_at desc');
select public.tvh_create_index_if_columns('public','audit_log','idx_tvh_audit_log_created', array['created_at'], 'created_at desc');
select public.tvh_create_index_if_columns('public','audit_logs','idx_tvh_audit_logs_created', array['created_at'], 'created_at desc');
select public.tvh_create_index_if_columns('public','platform_audit_events','idx_tvh_platform_audit_created', array['created_at'], 'created_at desc');
select public.tvh_create_index_if_columns('public','carh_project_actions','idx_tvh_carh_actions_status_due', array['status','due_date'], 'status, due_date');
select public.tvh_create_index_if_columns('public','carh_budget_lines','idx_tvh_carh_budget_project_category', array['project_code','category'], 'project_code, category');

-- ---------------------------------------------------------------------------
-- 7. Readiness views for admin review
-- ---------------------------------------------------------------------------
create or replace view public.global_deployment_readiness_summary as
select
  domain,
  count(*) as total_controls,
  count(*) filter (where status = 'ready') as ready_controls,
  count(*) filter (where status in ('not_started','in_progress','blocked')) as open_controls,
  count(*) filter (where required_before_global_launch and status <> 'ready') as required_open_controls,
  count(*) filter (where priority = 'critical' and status <> 'ready') as critical_open_controls
from public.global_deployment_controls
group by domain;

create or replace view public.global_rls_table_audit as
select
  n.nspname as schema_name,
  c.relname as table_name,
  c.relrowsecurity as rls_enabled,
  c.relforcerowsecurity as rls_forced
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind in ('r','p')
order by c.relname;

comment on table public.global_deployment_controls is 'Global deployment readiness controls for Terra Vita Hub production hardening.';
comment on table public.data_sovereignty_configurations is 'Jurisdiction, hosting, residency, export-control, and retention configuration by deployment.';
comment on table public.data_export_audit_log is 'Audit log for governed data exports and evidence/artifact movements.';
comment on table public.platform_background_jobs is 'Controlled queue for heavy work such as AI Advice, exports, TV-CRI recalculations, and satellite interpretation.';
comment on table public.risk_assurance_register is 'Register of institutional risk-assurance artifacts and review posture.';
comment on table public.operational_continuity_tests is 'Continuity, backup, restore, and resilience test record.';
comment on table public.mrv_quality_checks is 'MRV evidence-quality and indicator-lineage check record.';

commit;
