-- Trellis platform: core schema.
-- Implements the domain model from Trellis_Technical_Build_Plan section 1.4,
-- with tenant isolation enforced by Postgres row-level security (section 8.4, 14.2)
-- rather than left to application-layer filtering alone.

create extension if not exists pgcrypto;

-- A non-owner application role. RLS is FORCEd below specifically because an
-- owner-level role silently bypasses RLS unless forced to respect it
-- (the exact failure mode named in build-plan section 14.2). Creating this
-- role requires CREATEROLE/superuser, which a typical shared-hosting cPanel
-- database user does not have — tolerated here (warn, don't fail) rather
-- than hard-failing the whole migration, so this schema still deploys on a
-- constrained host. FORCE ROW LEVEL SECURITY still protects the tenant
-- boundary even when the app connects as the table-owning user directly,
-- since FORCE applies to the owner too (just not to a superuser or a role
-- with BYPASSRLS) — the two-role split is defense in depth on top of that,
-- not the only thing standing between a bug and cross-tenant data.
do $$
begin
  if not exists (select 1 from pg_roles where rolname = 'app_user') then
    create role app_user login password 'app_user_dev_only_change_in_real_deploy';
  end if;
exception when insufficient_privilege then
  raise notice 'Skipping app_user role creation: current role lacks CREATEROLE privilege (expected on shared hosting). Grants below to app_user will then apply to whatever role runs this migration instead — confirm that role is not a superuser, or FORCE ROW LEVEL SECURITY will be silently bypassed.';
end
$$;

-- ---------------------------------------------------------------------------
-- Reference data: not tenant-scoped. JurisdictionProfile parameterises every
-- jurisdiction-configurable requirement named in build-plan section 5.1.
-- ---------------------------------------------------------------------------
create table jurisdiction_profiles (
  id uuid primary key default gen_random_uuid(),
  code text unique not null,                -- e.g. 'UG', 'KE'
  name text not null,
  data_protection_regime text not null,
  objection_response_sla_days int not null check (objection_response_sla_days > 0),
  cross_border_transfer_allowed boolean not null default false,
  residency_region text,                    -- required cloud region for this jurisdiction, if any
  compliance_suspended boolean not null default false,  -- jurisdiction-scoped suspension, section 5.1
  created_at timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Tenant (anchor) configuration. TenantConfiguration entity, section 1.4/8.4.
-- ---------------------------------------------------------------------------
create table tenants (
  id uuid primary key default gen_random_uuid(),
  slug text unique not null,
  name text not null,
  archetype text not null check (archetype in (
    'perishable_goods_anchor', 'trade_credit_anchor',
    'payment_rail_anchor', 'platform_dispatched_work_anchor'
  )),
  jurisdiction_id uuid not null references jurisdiction_profiles(id),
  deduction_modality text not null default 'deduction_at_source'
    check (deduction_modality in ('deduction_at_source', 'revenue_based_repayment')),
  active boolean not null default true,
  created_at timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Internal actors: section 1.2 archetypes + section 5.6 RBAC.
-- Global staff (tenant_id null) hold cross-tenant roles (independent
-- verification function, data governance lead); tenant-scoped staff cannot
-- see another tenant's data at all, enforced by RLS below.
-- ---------------------------------------------------------------------------
create table internal_users (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid references tenants(id),   -- null = platform-level role
  email text unique not null,
  password_hash text not null,
  role text not null check (role in (
    'data_underwriting_lead', 'engineering', 'field_operations_lead',
    'data_governance_lead', 'independent_verification', 'product_lead',
    'anchor_finance_staff', 'anchor_operations_staff'
  )),
  created_at timestamptz not null default now()
);

-- Every PartnerAdapter integration gets its own scoped, revocable credential
-- (section 5.6, Stripe Connect restricted-key pattern).
create table partner_adapters (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  partner_name text not null,
  partner_type text not null check (partner_type in ('lender', 'insurer', 'payment_rail', 'coop_core_banking')),
  integration_mode text not null default 'sandbox' check (integration_mode in ('sandbox', 'staging', 'live')),
  created_at timestamptz not null default now()
);

create table api_credentials (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  partner_adapter_id uuid not null references partner_adapters(id) on delete cascade,
  key_prefix text not null,        -- shown to the partner, safe to log
  key_hash text not null,          -- sha256 of the actual key; the key itself is never stored
  scopes text[] not null default '{}',   -- e.g. {'obligations:read','deduction_events:write'}
  revoked_at timestamptz,
  created_at timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Suppliers, groups, consent. Sections 1.4, 2.4, 5.2.
-- ---------------------------------------------------------------------------
create table suppliers (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  external_ref text,                 -- anchor's own supplier/farmer id, if any
  display_name text not null,
  phone text,
  preferred_language text,
  created_at timestamptz not null default now()
);

create table groups (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  name text not null,
  group_type text not null check (group_type in ('cooperative', 'marketing_group', 'savings_association', 'out_grower_scheme')),
  created_at timestamptz not null default now()
);

create table supplier_group_memberships (
  supplier_id uuid not null references suppliers(id) on delete cascade,
  group_id uuid not null references groups(id) on delete cascade,
  tenant_id uuid not null references tenants(id),
  primary key (supplier_id, group_id)
);

-- ConsentRecord: purpose-bound, separately revocable per purpose (section 5.2).
create table consent_records (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  supplier_id uuid not null references suppliers(id),
  purpose text not null check (purpose in ('verify_capture', 'finance_scoring', 'grow_standing', 'climate_finance')),
  granted_at timestamptz not null default now(),
  revoked_at timestamptz,
  comprehension_check_passed boolean
);

-- ---------------------------------------------------------------------------
-- Verify: CollectionEvent / VerificationEvent. Section 2.
-- Structural rule (2.5): verified_by must differ from captured_by, enforced
-- below by trigger, not only by convention.
-- ---------------------------------------------------------------------------
create table collection_events (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  supplier_id uuid not null references suppliers(id),
  captured_by uuid not null references internal_users(id),
  capture_mode text not null check (capture_mode in ('sensor', 'field_agent', 'digital_confirmation', 'payment_rail_log')),
  quantity numeric(14,3) not null check (quantity >= 0),
  quality_grade text,
  unit_price numeric(14,4) not null check (unit_price >= 0),
  occurred_at timestamptz not null default now(),
  created_at timestamptz not null default now()
);

create table verification_events (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  collection_event_id uuid not null references collection_events(id),
  verified_by uuid not null references internal_users(id),
  method text not null check (method in ('physical_spot_audit', 'remote_sensing_cross_check', 'independent_recount')),
  outcome text not null check (outcome in ('confirmed', 'discrepancy_flagged', 'inconclusive')),
  notes text,
  created_at timestamptz not null default now()
);

create or replace function enforce_independent_verification() returns trigger as $$
declare
  captor uuid;
begin
  select captured_by into captor from collection_events where id = new.collection_event_id;
  if captor = new.verified_by then
    raise exception 'VerificationEvent.verified_by must differ from CollectionEvent.captured_by (build-plan section 2.5: a verification loop cannot verify itself)';
  end if;
  return new;
end;
$$ language plpgsql;

create trigger trg_enforce_independent_verification
  before insert or update on verification_events
  for each row execute function enforce_independent_verification();

-- ---------------------------------------------------------------------------
-- Finance: CreditProfile / Obligation / DeductionEvent. Section 3.
-- ---------------------------------------------------------------------------
create table credit_profiles (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  supplier_id uuid not null references suppliers(id),
  -- numeric(7,3): scores are bounded [0, 1000] by application logic
  -- (finance.ts caps at Math.min(1000, score)); numeric(6,3) looked
  -- sufficient but its actual max is 999.999, one short of the real
  -- ceiling - found by a test that genuinely drove a score to exactly
  -- 1000 and got a real "numeric field overflow" error back, not assumed
  -- safe from reading the application code's own cap alone.
  score numeric(7,3) not null,
  model_version text not null,            -- section 3.6: every scoring decision is reproducible
  reason_codes text[] not null default '{}',  -- section 3.6: feature-attribution reason codes
  decision_classification text not null default 'solely_automated'
    check (decision_classification in ('solely_automated', 'human_reviewed')),
  created_at timestamptz not null default now()
);

create table obligations (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  supplier_id uuid not null references suppliers(id),
  partner_adapter_id uuid references partner_adapters(id),
  amount_due numeric(14,2) not null check (amount_due >= 0),
  repayment_modality text not null default 'deduction_at_source'
    check (repayment_modality in ('deduction_at_source', 'revenue_based_repayment')),
  reconciliation_status text not null default 'pending'
    check (reconciliation_status in ('pending', 'matched', 'exception')),
  reconciliation_notes text,  -- set alongside a partner-reported 'exception', explaining the mismatch
  created_at timestamptz not null default now()
);

create table deduction_events (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  obligation_id uuid not null references obligations(id),
  collection_event_id uuid references collection_events(id),
  amount numeric(14,2) not null check (amount >= 0),
  status text not null check (status in ('deducted', 'skipped_insufficient_balance')),
  created_at timestamptz not null default now()
);

-- Ledger-safety backstop (section 14.3's ledger-correctness discipline, scoped
-- to what a single-service MVP can enforce): the sum of non-skipped
-- DeductionEvents against one Obligation may never exceed that Obligation's
-- amount_due. This is a database-level invariant, not only an application check.
create or replace function enforce_deduction_ceiling() returns trigger as $$
declare
  already_deducted numeric(14,2);
  owed numeric(14,2);
begin
  if new.status = 'skipped_insufficient_balance' then
    return new;
  end if;
  select coalesce(sum(amount), 0) into already_deducted
    from deduction_events
    where obligation_id = new.obligation_id and status = 'deducted';
  select amount_due into owed from obligations where id = new.obligation_id;
  if already_deducted + new.amount > owed then
    raise exception 'DeductionEvent would exceed Obligation.amount_due (ledger-safety invariant, build-plan section 14.3)';
  end if;
  return new;
end;
$$ language plpgsql;

create trigger trg_enforce_deduction_ceiling
  before insert on deduction_events
  for each row execute function enforce_deduction_ceiling();

-- ---------------------------------------------------------------------------
-- Grow: StandingScore / ReviewCase. Sections 3.4, 4.3.
-- ---------------------------------------------------------------------------
create table standing_scores (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  supplier_id uuid references suppliers(id),
  group_id uuid references groups(id),
  score numeric(7,3) not null,  -- see credit_profiles.score's comment above: [0,1000] needs numeric(7,3), not (6,3)
  sample_size int not null check (sample_size >= 0),
  materiality_floor_met boolean not null default false,
  created_at timestamptz not null default now(),
  check (supplier_id is not null or group_id is not null)
);

create table review_cases (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  supplier_id uuid not null references suppliers(id),
  credit_profile_id uuid references credit_profiles(id),
  reason text not null,
  status text not null default 'open' check (status in ('open', 'in_review', 'resolved')),
  response_due_at timestamptz not null,
  resolved_at timestamptz,
  resolution_notes text,
  created_at timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Row-level security: every tenant-scoped table is isolated by tenant_id,
-- read against the session's app.current_tenant_id setting. FORCE ROW LEVEL
-- SECURITY so even the owning role cannot silently bypass it (section 14.2).
-- A null app.current_tenant_id (platform-level/global staff) sees every row,
-- gated instead at the application layer by role (section 5.6).
-- ---------------------------------------------------------------------------
do $$
declare
  t text;
begin
  foreach t in array array[
    'internal_users', 'partner_adapters', 'api_credentials',
    'suppliers', 'groups', 'supplier_group_memberships', 'consent_records',
    'collection_events', 'verification_events', 'credit_profiles',
    'obligations', 'deduction_events', 'standing_scores', 'review_cases'
  ]
  loop
    execute format('alter table %I enable row level security', t);
    execute format('alter table %I force row level security', t);
    -- nullif(..., '') matters here, not just current_setting(...) is null:
    -- once a pooled connection has had app.current_tenant_id SET LOCAL even
    -- once, Postgres reverts it to '' (empty string), not NULL, once that
    -- transaction ends — confirmed directly against this exact schema during
    -- build. A bare "is null" check silently stops granting global access on
    -- any connection that has ever carried a tenant context, which is every
    -- connection in a pool after normal traffic. This is exactly the kind of
    -- pooled-connection RLS failure mode named in build-plan section 14.2.
    execute format(
      'create policy tenant_isolation_%1$s on %1$I using (
         nullif(current_setting(''app.current_tenant_id'', true), '''') is null
         or tenant_id::text = current_setting(''app.current_tenant_id'', true)
       )', t
    );
    -- Only grant to app_user if that role actually exists (see the
    -- CREATEROLE-tolerant block above) — on a host where it could not be
    -- created, the migration-running user already owns these tables and
    -- so already has full DML rights on them without an explicit grant.
    if exists (select 1 from pg_roles where rolname = 'app_user') then
      execute format('grant select, insert, update, delete on %I to app_user', t);
    end if;
  end loop;
end
$$;

-- tenants and jurisdiction_profiles themselves: tenants row is keyed by id,
-- not tenant_id, so scope a tenant's own row directly by id instead.
alter table tenants enable row level security;
alter table tenants force row level security;
create policy tenant_isolation_tenants on tenants using (
  nullif(current_setting('app.current_tenant_id', true), '') is null
  or id::text = current_setting('app.current_tenant_id', true)
);
do $$
begin
  if exists (select 1 from pg_roles where rolname = 'app_user') then
    grant select, insert, update, delete on tenants to app_user;
    -- jurisdiction_profiles stays admin-managed reference data (no INSERT/
    -- DELETE, no UPDATE on the fields an admin seeds it with), but
    -- compliance_suspended is a legitimate application-level action a
    -- data_governance_lead takes through the API (section 5.1's
    -- jurisdiction-scoped suspension) - a genuinely different actor and
    -- action from platform onboarding, so it gets its own, narrower grant
    -- rather than reusing or widening the admin-data grant above.
    grant select on jurisdiction_profiles to app_user;
    grant update (compliance_suspended) on jurisdiction_profiles to app_user;
    grant usage on schema public to app_user;
  end if;
end
$$;