-- Solo-developer portfolio allocation, dependency, and sunset model
-- Schema version: 1.0.0 | Reference date: 2026-08-02 | Engine: SQLite 3.40+
-- Companion: ../15-founder-portfolio-allocation-and-sunset.md
-- Template: ../templates/15-portfolio-and-sunset-pack.md
--
-- Run on a new/disposable database:
--   sqlite3 :memory: < indie-developer-playbook/research/15-portfolio-sunset-model.sql
--
-- Every fixture is fictional. Allocation bounds are explicitly founder-set
-- guardrails for one fixture period, never a universal benchmark. Allocation
-- role and lifecycle are independent axes; reserve is a period-level buffer.
--
-- Privacy boundary
-- - No customer name, email, address, raw contract, personal data, credential,
--   prompt, source document, or free-form customer text belongs here.
-- - Evidence and obligation references are opaque 64-character lowercase hashes.
-- - Fixture printf() values demonstrate shape only; they are not cryptographic.
--
-- Money boundary
-- - *_micros is one millionth of the row currency (JPY 1 = 1,000,000 micros).
-- - Contribution exposure, planned spend, refunds, and funded sunset liability
--   remain separate. Founder time is a management cost, not necessarily cash.
-- - Failure-domain exposure deduplicates an item inside a shared domain. It does
--   not estimate, add, or imply outage probabilities.

PRAGMA foreign_keys = ON;
PRAGMA recursive_triggers = ON;
PRAGMA trusted_schema = OFF;
BEGIN IMMEDIATE;

CREATE TABLE schema_registry (
  schema_id INTEGER PRIMARY KEY CHECK (schema_id = 1),
  schema_name TEXT NOT NULL UNIQUE CHECK (schema_name = 'portfolio_sunset'),
  schema_version TEXT NOT NULL CHECK (schema_version = '1.0.0'),
  engine_floor TEXT NOT NULL CHECK (engine_floor = 'SQLite 3.40+'),
  applied_at TEXT NOT NULL CHECK (datetime(applied_at) IS NOT NULL),
  data_policy_code TEXT NOT NULL
    CHECK (data_policy_code = 'NO_PERSONAL_DATA_OPAQUE_EVIDENCE_ONLY'),
  allocation_policy_code TEXT NOT NULL
    CHECK (allocation_policy_code = 'USER_SET_GUARDRAILS_NOT_BENCHMARKS')
) STRICT;

CREATE TABLE model_settings (
  singleton_id INTEGER PRIMARY KEY CHECK (singleton_id = 1),
  event_time_as_of TEXT NOT NULL CHECK (datetime(event_time_as_of) IS NOT NULL),
  data_watermark_at TEXT NOT NULL CHECK (datetime(data_watermark_at) IS NOT NULL),
  base_currency TEXT NOT NULL UNIQUE
    CHECK (length(base_currency) = 3 AND base_currency = upper(base_currency)),
  CHECK (datetime(data_watermark_at) <= datetime(event_time_as_of))
) STRICT;

-- These dimensions are deliberately separate. No role-to-lifecycle mapping table
-- exists because role changes and lifecycle changes require independent evidence.
CREATE TABLE allocation_role_dim (
  allocation_role_code TEXT PRIMARY KEY CHECK (allocation_role_code IN (
    'cash_engine','validated_core','new_option','unfunded'
  )),
  purpose_text TEXT NOT NULL
) STRICT;

CREATE TABLE lifecycle_dim (
  lifecycle_code TEXT PRIMARY KEY CHECK (lifecycle_code IN (
    'explore','growth','operate','maintenance',
    'sunset_planned','sunset_active','closed'
  )),
  purpose_text TEXT NOT NULL
) STRICT;

CREATE TABLE decision_action_dim (
  action_code TEXT PRIMARY KEY CHECK (action_code IN (
    'FUND','HOLD','HARVEST','MERGE','TRANSFER','SELL',
    'SUNSET','STOP','PAUSE','UNKNOWN'
  ))
) STRICT;

CREATE TABLE gate_state_dim (
  gate_state_code TEXT PRIMARY KEY CHECK (gate_state_code IN (
    'STOP','PAUSE','UNKNOWN','READY_TO_DECIDE'
  )),
  priority_order INTEGER NOT NULL UNIQUE CHECK (priority_order BETWEEN 1 AND 4)
) STRICT;

CREATE TABLE gate_action_policy (
  gate_state_code TEXT NOT NULL,
  action_code TEXT NOT NULL,
  action_allowed INTEGER NOT NULL CHECK (action_allowed IN (0,1)),
  PRIMARY KEY (gate_state_code,action_code),
  FOREIGN KEY (gate_state_code) REFERENCES gate_state_dim(gate_state_code),
  FOREIGN KEY (action_code) REFERENCES decision_action_dim(action_code)
) STRICT;

CREATE TABLE gate_domain_dim (
  gate_domain_code TEXT PRIMARY KEY CHECK (gate_domain_code IN (
    'safety_legal','contract_customer','cash_capacity','allocation_integrity',
    'option_evidence','dependency_recovery','sunset_funding','data_exit'
  )),
  fail_effect TEXT NOT NULL CHECK (fail_effect IN ('STOP','PAUSE')),
  required_by_default INTEGER NOT NULL CHECK (required_by_default = 1)
) STRICT;

CREATE TABLE sunset_liability_domain_dim (
  liability_domain_code TEXT PRIMARY KEY CHECK (liability_domain_code IN (
    'legal','contract','refund_credit','migration_export','data_deletion',
    'record_retention','infrastructure','security'
  )),
  required_review INTEGER NOT NULL CHECK (required_review = 1)
) STRICT;

CREATE TABLE portfolio_period (
  portfolio_period_id TEXT PRIMARY KEY,
  period_start TEXT NOT NULL CHECK (datetime(period_start) IS NOT NULL),
  period_end TEXT NOT NULL CHECK (datetime(period_end) IS NOT NULL),
  time_zone TEXT NOT NULL,
  total_portfolio_capacity_minutes INTEGER NOT NULL
    CHECK (total_portfolio_capacity_minutes > 0),
  common_admin_compliance_minutes INTEGER NOT NULL
    CHECK (common_admin_compliance_minutes >= 0),
  contracted_delivery_support_minutes INTEGER NOT NULL
    CHECK (contracted_delivery_support_minutes >= 0),
  known_incident_remediation_minutes INTEGER NOT NULL
    CHECK (known_incident_remediation_minutes >= 0),
  continuity_reserve_minutes INTEGER NOT NULL
    CHECK (continuity_reserve_minutes >= 0),
  active_sunset_minutes INTEGER NOT NULL
    CHECK (active_sunset_minutes >= 0),
  discretionary_founder_minutes INTEGER NOT NULL
    CHECK (discretionary_founder_minutes >= 0),
  actual_business_cash_micros INTEGER NOT NULL
    CHECK (actual_business_cash_micros > 0),
  tax_social_internal_restriction_micros INTEGER NOT NULL
    CHECK (tax_social_internal_restriction_micros >= 0),
  customer_obligations_micros INTEGER NOT NULL
    CHECK (customer_obligations_micros >= 0),
  confirmed_payables_vendor_spend_micros INTEGER NOT NULL
    CHECK (confirmed_payables_vendor_spend_micros >= 0),
  absolute_minimum_cash_micros INTEGER NOT NULL
    CHECK (absolute_minimum_cash_micros >= 0),
  continuity_reserve_cash_micros INTEGER NOT NULL
    CHECK (continuity_reserve_cash_micros >= 0),
  open_sunset_funding_micros INTEGER NOT NULL
    CHECK (open_sunset_funding_micros >= 0),
  discretionary_cash_micros INTEGER NOT NULL
    CHECK (discretionary_cash_micros >= 0),
  currency TEXT NOT NULL,
  capacity_evidence_ref_hash TEXT NOT NULL,
  cash_evidence_ref_hash TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(period_end) > datetime(period_start)),
  CHECK (total_portfolio_capacity_minutes
         - common_admin_compliance_minutes
         - contracted_delivery_support_minutes
         - known_incident_remediation_minutes
         - continuity_reserve_minutes
         - active_sunset_minutes
         = discretionary_founder_minutes),
  CHECK (actual_business_cash_micros
         - tax_social_internal_restriction_micros
         - customer_obligations_micros
         - confirmed_payables_vendor_spend_micros
         - absolute_minimum_cash_micros
         - continuity_reserve_cash_micros
         - open_sunset_funding_micros
         = discretionary_cash_micros),
  CHECK (length(capacity_evidence_ref_hash)=64
         AND capacity_evidence_ref_hash=lower(capacity_evidence_ref_hash)
         AND capacity_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(cash_evidence_ref_hash)=64
         AND cash_evidence_ref_hash=lower(cash_evidence_ref_hash)
         AND cash_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency)
) STRICT;

-- Each waterfall deduction has one source event. The UNIQUE constraint prevents
-- one event from being counted in two time/cash buckets. A source containing
-- several economic components must be split upstream into distinct event keys.
CREATE TABLE waterfall_component_evidence (
  waterfall_component_id TEXT PRIMARY KEY,
  portfolio_period_id TEXT NOT NULL,
  resource_kind TEXT NOT NULL CHECK (resource_kind IN ('time','cash')),
  component_code TEXT NOT NULL,
  amount_units INTEGER NOT NULL CHECK (amount_units >= 0),
  source_event_ref_hash TEXT NOT NULL UNIQUE,
  exclusion_scope_code TEXT NOT NULL,
  CHECK (
    (resource_kind='time' AND component_code IN (
      'common_admin_compliance','contracted_delivery_support',
      'known_incident_remediation','continuity_reserve','active_sunset'
    )) OR
    (resource_kind='cash' AND component_code IN (
      'tax_social_internal_restriction','customer_obligations',
      'confirmed_payables_vendor_spend','absolute_minimum_cash',
      'continuity_reserve','open_sunset_funding'
    ))
  ),
  CHECK (length(source_event_ref_hash)=64
         AND source_event_ref_hash=lower(source_event_ref_hash)
         AND source_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (portfolio_period_id,resource_kind,component_code),
  FOREIGN KEY (portfolio_period_id) REFERENCES portfolio_period(portfolio_period_id)
) STRICT;

CREATE TABLE allocation_guardrail_set (
  guardrail_set_id TEXT PRIMARY KEY,
  portfolio_period_id TEXT NOT NULL UNIQUE,
  guardrail_version TEXT NOT NULL,
  source_code TEXT NOT NULL
    CHECK (source_code = 'FOUNDER_SET_NOT_BENCHMARK'),
  reserve_founder_minutes_min INTEGER NOT NULL
    CHECK (reserve_founder_minutes_min >= 0),
  reserve_founder_minutes_max INTEGER NOT NULL
    CHECK (reserve_founder_minutes_max >= reserve_founder_minutes_min),
  reserve_cash_micros_min INTEGER NOT NULL
    CHECK (reserve_cash_micros_min >= 0),
  reserve_cash_micros_max INTEGER NOT NULL
    CHECK (reserve_cash_micros_max >= reserve_cash_micros_min),
  adopted_by_role TEXT NOT NULL,
  adopted_at TEXT NOT NULL CHECK (datetime(adopted_at) IS NOT NULL),
  assumption_evidence_ref_hash TEXT NOT NULL,
  CHECK (length(assumption_evidence_ref_hash)=64
         AND assumption_evidence_ref_hash=lower(assumption_evidence_ref_hash)
         AND assumption_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (portfolio_period_id) REFERENCES portfolio_period(portfolio_period_id)
) STRICT;

CREATE TABLE role_allocation_guardrail (
  role_guardrail_id TEXT PRIMARY KEY,
  guardrail_set_id TEXT NOT NULL,
  allocation_role_code TEXT NOT NULL,
  time_min_ppm INTEGER NOT NULL CHECK (time_min_ppm BETWEEN 0 AND 1000000),
  time_max_ppm INTEGER NOT NULL CHECK (time_max_ppm BETWEEN 0 AND 1000000),
  cash_min_ppm INTEGER NOT NULL CHECK (cash_min_ppm BETWEEN 0 AND 1000000),
  cash_max_ppm INTEGER NOT NULL CHECK (cash_max_ppm BETWEEN 0 AND 1000000),
  rationale_evidence_ref_hash TEXT NOT NULL,
  CHECK (time_min_ppm <= time_max_ppm),
  CHECK (cash_min_ppm <= cash_max_ppm),
  CHECK (length(rationale_evidence_ref_hash)=64
         AND rationale_evidence_ref_hash=lower(rationale_evidence_ref_hash)
         AND rationale_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (guardrail_set_id,allocation_role_code),
  FOREIGN KEY (guardrail_set_id) REFERENCES allocation_guardrail_set(guardrail_set_id),
  FOREIGN KEY (allocation_role_code)
    REFERENCES allocation_role_dim(allocation_role_code)
) STRICT;

CREATE TABLE portfolio_item (
  item_id TEXT PRIMARY KEY,
  item_type TEXT NOT NULL CHECK (item_type IN (
    'product','service','internal_asset','experiment','obligation'
  )),
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  owner_role TEXT NOT NULL
) STRICT;

CREATE TABLE portfolio_item_snapshot (
  item_snapshot_id TEXT PRIMARY KEY,
  item_id TEXT NOT NULL,
  portfolio_period_id TEXT NOT NULL,
  snapshot_sequence INTEGER NOT NULL CHECK (snapshot_sequence>=1),
  decision_at TEXT NOT NULL CHECK (datetime(decision_at) IS NOT NULL),
  supersedes_item_snapshot_id TEXT,
  allocation_role_code TEXT NOT NULL,
  lifecycle_code TEXT NOT NULL,
  closure_event_id TEXT,
  role_decision_event_ref_hash TEXT NOT NULL,
  lifecycle_decision_event_ref_hash TEXT NOT NULL,
  dependency_applicability TEXT NOT NULL
    CHECK (dependency_applicability IN ('required','not_applicable')),
  dependency_na_reason TEXT,
  dependency_na_evidence_ref_hash TEXT,
  dependency_na_decided_at TEXT CHECK (
    dependency_na_decided_at IS NULL OR datetime(dependency_na_decided_at) IS NOT NULL
  ),
  dependency_na_valid_until TEXT CHECK (
    dependency_na_valid_until IS NULL OR datetime(dependency_na_valid_until) IS NOT NULL
  ),
  dependency_na_recheck_trigger TEXT,
  dependency_na_recheck_triggered_at TEXT CHECK (
    dependency_na_recheck_triggered_at IS NULL
    OR datetime(dependency_na_recheck_triggered_at) IS NOT NULL
  ),
  trailing_monthly_cash_contribution_micros INTEGER,
  active_paying_customers INTEGER CHECK (
    active_paying_customers IS NULL OR active_paying_customers >= 0
  ),
  contracted_future_delivery_micros INTEGER CHECK (
    contracted_future_delivery_micros IS NULL
    OR contracted_future_delivery_micros >= 0
  ),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(recorded_at)>=datetime(decision_at)),
  CHECK (length(role_decision_event_ref_hash)=64
         AND role_decision_event_ref_hash=lower(role_decision_event_ref_hash)
         AND role_decision_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(lifecycle_decision_event_ref_hash)=64
         AND lifecycle_decision_event_ref_hash=lower(lifecycle_decision_event_ref_hash)
         AND lifecycle_decision_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (
    (dependency_applicability='required'
      AND dependency_na_reason IS NULL
      AND dependency_na_evidence_ref_hash IS NULL
      AND dependency_na_decided_at IS NULL
      AND dependency_na_valid_until IS NULL
      AND dependency_na_recheck_trigger IS NULL
      AND dependency_na_recheck_triggered_at IS NULL)
    OR
    (dependency_applicability='not_applicable'
      AND dependency_na_reason IS NOT NULL
      AND length(dependency_na_evidence_ref_hash)=64
      AND dependency_na_evidence_ref_hash=lower(dependency_na_evidence_ref_hash)
      AND dependency_na_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'
      AND dependency_na_decided_at IS NOT NULL
      AND (dependency_na_valid_until IS NOT NULL
           OR dependency_na_recheck_trigger IS NOT NULL))
  ),
  CHECK (dependency_na_valid_until IS NULL OR dependency_na_decided_at IS NULL
         OR datetime(dependency_na_valid_until)>=datetime(dependency_na_decided_at)),
  CHECK (dependency_na_recheck_triggered_at IS NULL
         OR dependency_na_recheck_trigger IS NOT NULL),
  CHECK ((snapshot_sequence=1 AND supersedes_item_snapshot_id IS NULL)
         OR (snapshot_sequence>1 AND supersedes_item_snapshot_id IS NOT NULL)),
  CHECK ((lifecycle_code='closed' AND closure_event_id IS NOT NULL)
         OR (lifecycle_code<>'closed' AND closure_event_id IS NULL)),
  UNIQUE (item_id,portfolio_period_id,snapshot_sequence),
  UNIQUE (item_id,portfolio_period_id,decision_at),
  UNIQUE (item_snapshot_id,item_id,portfolio_period_id),
  UNIQUE (portfolio_period_id,item_snapshot_id),
  FOREIGN KEY (item_id) REFERENCES portfolio_item(item_id),
  FOREIGN KEY (portfolio_period_id) REFERENCES portfolio_period(portfolio_period_id),
  FOREIGN KEY (allocation_role_code)
    REFERENCES allocation_role_dim(allocation_role_code),
  FOREIGN KEY (lifecycle_code) REFERENCES lifecycle_dim(lifecycle_code)
  ,FOREIGN KEY (supersedes_item_snapshot_id,item_id,portfolio_period_id)
    REFERENCES portfolio_item_snapshot(item_snapshot_id,item_id,portfolio_period_id)
  ,FOREIGN KEY (closure_event_id)
    REFERENCES sunset_close_event(sunset_close_event_id)
) STRICT;

CREATE TABLE portfolio_resource_allocation (
  allocation_id TEXT PRIMARY KEY,
  portfolio_period_id TEXT NOT NULL,
  item_snapshot_id TEXT NOT NULL,
  allocation_phase TEXT NOT NULL CHECK (allocation_phase IN ('plan','actual')),
  founder_minutes INTEGER NOT NULL CHECK (founder_minutes >= 0),
  cash_micros INTEGER NOT NULL CHECK (cash_micros >= 0),
  source_event_ref_hash TEXT NOT NULL UNIQUE,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (length(source_event_ref_hash)=64
         AND source_event_ref_hash=lower(source_event_ref_hash)
         AND source_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (portfolio_period_id,item_snapshot_id,allocation_phase),
  FOREIGN KEY (portfolio_period_id,item_snapshot_id)
    REFERENCES portfolio_item_snapshot(portfolio_period_id,item_snapshot_id)
) STRICT;

CREATE TABLE failure_domain (
  failure_domain_id TEXT PRIMARY KEY,
  domain_kind TEXT NOT NULL CHECK (domain_kind IN (
    'cloud_runtime','identity','payment','data','model','channel',
    'dns_domain','communication','key_person','legal_permission','other'
  )),
  boundary_code TEXT NOT NULL,
  recovery_owner_role TEXT NOT NULL,
  recovery_target_minutes INTEGER NOT NULL CHECK (recovery_target_minutes > 0),
  last_recovery_test_at TEXT CHECK (
    last_recovery_test_at IS NULL OR datetime(last_recovery_test_at) IS NOT NULL
  ),
  recovery_test_state TEXT NOT NULL CHECK (
    recovery_test_state IN ('verified','failed','not_tested','unknown')
  ),
  recovery_test_evidence_ref_hash TEXT,
  CHECK (
    (recovery_test_state IN ('verified','failed')
      AND last_recovery_test_at IS NOT NULL
      AND length(recovery_test_evidence_ref_hash)=64
      AND recovery_test_evidence_ref_hash=lower(recovery_test_evidence_ref_hash)
      AND recovery_test_evidence_ref_hash NOT GLOB '*[^0-9a-f]*')
    OR
    (recovery_test_state IN ('not_tested','unknown')
      AND recovery_test_evidence_ref_hash IS NULL)
  )
) STRICT;

CREATE TABLE dependency_snapshot (
  dependency_snapshot_id TEXT PRIMARY KEY,
  dependency_code TEXT NOT NULL,
  provider_code TEXT NOT NULL,
  dependency_kind TEXT NOT NULL CHECK (dependency_kind IN (
    'vendor','platform','identity','payment','data','model','domain','key_person','other'
  )),
  failure_domain_id TEXT NOT NULL,
  criticality TEXT NOT NULL CHECK (criticality IN ('low','medium','high','critical')),
  observed_state TEXT NOT NULL CHECK (
    observed_state IN ('healthy','degraded','failed','unknown')
  ),
  fallback_state TEXT NOT NULL CHECK (
    fallback_state IN ('verified','documented','unverified','none','unknown')
  ),
  observed_at TEXT CHECK (observed_at IS NULL OR datetime(observed_at) IS NOT NULL),
  max_evidence_age_days INTEGER NOT NULL CHECK (max_evidence_age_days > 0),
  status_evidence_ref_hash TEXT,
  recovery_target_minutes INTEGER NOT NULL CHECK (recovery_target_minutes > 0),
  CHECK (
    (observed_state='unknown' AND observed_at IS NULL
      AND status_evidence_ref_hash IS NULL)
    OR
    (observed_state<>'unknown' AND observed_at IS NOT NULL
      AND length(status_evidence_ref_hash)=64
      AND status_evidence_ref_hash=lower(status_evidence_ref_hash)
      AND status_evidence_ref_hash NOT GLOB '*[^0-9a-f]*')
  ),
  UNIQUE (dependency_code,observed_at),
  FOREIGN KEY (failure_domain_id) REFERENCES failure_domain(failure_domain_id)
) STRICT;

CREATE TABLE item_dependency (
  item_dependency_id TEXT PRIMARY KEY,
  item_snapshot_id TEXT NOT NULL,
  dependency_snapshot_id TEXT NOT NULL,
  required_operation_code TEXT NOT NULL,
  data_class_code TEXT NOT NULL,
  evidence_ref_hash TEXT NOT NULL,
  CHECK (length(evidence_ref_hash)=64
         AND evidence_ref_hash=lower(evidence_ref_hash)
         AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (item_snapshot_id,dependency_snapshot_id,required_operation_code,data_class_code),
  FOREIGN KEY (item_snapshot_id) REFERENCES portfolio_item_snapshot(item_snapshot_id),
  FOREIGN KEY (dependency_snapshot_id)
    REFERENCES dependency_snapshot(dependency_snapshot_id)
) STRICT;

CREATE TABLE sunset_plan (
  sunset_plan_id TEXT PRIMARY KEY,
  item_snapshot_id TEXT NOT NULL UNIQUE,
  service_end_at TEXT NOT NULL CHECK (datetime(service_end_at) IS NOT NULL),
  decision_event_ref_hash TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (length(decision_event_ref_hash)=64
         AND decision_event_ref_hash=lower(decision_event_ref_hash)
         AND decision_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (item_snapshot_id) REFERENCES portfolio_item_snapshot(item_snapshot_id)
) STRICT;

-- Plan status and funding are immutable events. Closure is deliberately absent
-- from this vocabulary and is represented only by a validated close event.
CREATE TABLE sunset_plan_status_event (
  sunset_plan_status_event_id TEXT PRIMARY KEY,
  sunset_plan_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>=1),
  plan_status TEXT NOT NULL CHECK (plan_status IN (
    'proposed','approved','executing','closure_review'
  )),
  funded_cash_micros INTEGER NOT NULL CHECK (funded_cash_micros>=0),
  reserved_founder_minutes INTEGER NOT NULL CHECK (reserved_founder_minutes>=0),
  status_evidence_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  supersedes_status_event_id TEXT,
  CHECK (datetime(recorded_at)>=datetime(occurred_at)),
  CHECK ((event_sequence=1 AND supersedes_status_event_id IS NULL)
         OR (event_sequence>1 AND supersedes_status_event_id IS NOT NULL)),
  CHECK (length(status_evidence_ref_hash)=64
         AND status_evidence_ref_hash=lower(status_evidence_ref_hash)
         AND status_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (sunset_plan_id,event_sequence),
  UNIQUE (sunset_plan_status_event_id,sunset_plan_id),
  FOREIGN KEY (sunset_plan_id) REFERENCES sunset_plan(sunset_plan_id),
  FOREIGN KEY (supersedes_status_event_id,sunset_plan_id)
    REFERENCES sunset_plan_status_event(sunset_plan_status_event_id,sunset_plan_id)
) STRICT;

CREATE TABLE sunset_liability (
  sunset_liability_id TEXT PRIMARY KEY,
  liability_stream_id TEXT NOT NULL,
  sunset_plan_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>=1),
  supersedes_liability_id TEXT,
  liability_domain_code TEXT NOT NULL,
  obligation_ref_hash TEXT NOT NULL,
  liability_status TEXT NOT NULL CHECK (liability_status IN (
    'open','blocked','complete','transferred','unknown','not_applicable'
  )),
  severity TEXT NOT NULL CHECK (severity IN ('low','medium','high','critical')),
  breach_state TEXT NOT NULL CHECK (breach_state IN ('yes','no','unknown')),
  owner_role TEXT NOT NULL,
  due_at TEXT CHECK (due_at IS NULL OR datetime(due_at) IS NOT NULL),
  completed_at TEXT CHECK (completed_at IS NULL OR datetime(completed_at) IS NOT NULL),
  estimated_cash_micros INTEGER CHECK (
    estimated_cash_micros IS NULL OR estimated_cash_micros >= 0
  ),
  estimated_founder_minutes INTEGER CHECK (
    estimated_founder_minutes IS NULL OR estimated_founder_minutes >= 0
  ),
  completion_evidence_ref_hash TEXT,
  na_reason TEXT,
  na_decided_at TEXT CHECK (na_decided_at IS NULL OR datetime(na_decided_at) IS NOT NULL),
  na_valid_until TEXT CHECK (na_valid_until IS NULL OR datetime(na_valid_until) IS NOT NULL),
  na_recheck_trigger TEXT,
  na_recheck_triggered_at TEXT CHECK (
    na_recheck_triggered_at IS NULL OR datetime(na_recheck_triggered_at) IS NOT NULL
  ),
  legal_basis_ref_hash TEXT,
  retain_until TEXT CHECK (retain_until IS NULL OR datetime(retain_until) IS NOT NULL),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (length(obligation_ref_hash)=64
         AND obligation_ref_hash=lower(obligation_ref_hash)
         AND obligation_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (completion_evidence_ref_hash IS NULL OR
         (length(completion_evidence_ref_hash)=64
          AND completion_evidence_ref_hash=lower(completion_evidence_ref_hash)
          AND completion_evidence_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (legal_basis_ref_hash IS NULL OR
         (length(legal_basis_ref_hash)=64
          AND legal_basis_ref_hash=lower(legal_basis_ref_hash)
          AND legal_basis_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (
    (liability_status IN ('complete','transferred') AND completed_at IS NOT NULL
      AND completion_evidence_ref_hash IS NOT NULL AND na_reason IS NULL
      AND na_decided_at IS NULL AND na_valid_until IS NULL
      AND na_recheck_trigger IS NULL AND na_recheck_triggered_at IS NULL)
    OR
    (liability_status='not_applicable' AND completed_at IS NULL
      AND completion_evidence_ref_hash IS NOT NULL AND na_reason IS NOT NULL
      AND na_decided_at IS NOT NULL
      AND (na_valid_until IS NOT NULL OR na_recheck_trigger IS NOT NULL))
    OR
    (liability_status IN ('open','blocked','unknown')
      AND completed_at IS NULL AND na_reason IS NULL
      AND na_decided_at IS NULL AND na_valid_until IS NULL
      AND na_recheck_trigger IS NULL AND na_recheck_triggered_at IS NULL)
  ),
  CHECK (na_valid_until IS NULL OR na_decided_at IS NULL
         OR datetime(na_valid_until)>=datetime(na_decided_at)),
  CHECK (na_recheck_triggered_at IS NULL OR na_recheck_trigger IS NOT NULL),
  CHECK (datetime(recorded_at)>=datetime(occurred_at)),
  CHECK ((event_sequence=1 AND supersedes_liability_id IS NULL)
         OR (event_sequence>1 AND supersedes_liability_id IS NOT NULL)),
  CHECK (breach_state<>'yes' OR
         (liability_status IN ('open','blocked') AND severity IN ('high','critical'))),
  CHECK (liability_domain_code<>'record_retention' OR
         (legal_basis_ref_hash IS NOT NULL AND retain_until IS NOT NULL)),
  UNIQUE (sunset_plan_id,liability_stream_id,event_sequence),
  UNIQUE (sunset_plan_id,sunset_liability_id),
  FOREIGN KEY (sunset_plan_id) REFERENCES sunset_plan(sunset_plan_id),
  FOREIGN KEY (sunset_plan_id,supersedes_liability_id)
    REFERENCES sunset_liability(sunset_plan_id,sunset_liability_id),
  FOREIGN KEY (liability_domain_code)
    REFERENCES sunset_liability_domain_dim(liability_domain_code)
) STRICT;

CREATE TABLE sunset_close_event (
  sunset_close_event_id TEXT PRIMARY KEY,
  sunset_plan_id TEXT NOT NULL UNIQUE,
  final_manifest_ref_hash TEXT NOT NULL,
  closure_decision_evidence_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(recorded_at)>=datetime(occurred_at)),
  CHECK (length(final_manifest_ref_hash)=64
         AND final_manifest_ref_hash=lower(final_manifest_ref_hash)
         AND final_manifest_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(closure_decision_evidence_ref_hash)=64
         AND closure_decision_evidence_ref_hash=lower(closure_decision_evidence_ref_hash)
         AND closure_decision_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (sunset_plan_id) REFERENCES sunset_plan(sunset_plan_id)
) STRICT;

CREATE TABLE portfolio_review (
  portfolio_review_id TEXT PRIMARY KEY,
  portfolio_period_id TEXT NOT NULL,
  item_snapshot_id TEXT NOT NULL,
  review_sequence INTEGER NOT NULL CHECK (review_sequence>=1),
  supersedes_review_id TEXT,
  gate_policy_version TEXT NOT NULL,
  evidence_as_of TEXT NOT NULL CHECK (datetime(evidence_as_of) IS NOT NULL),
  data_watermark_at TEXT NOT NULL CHECK (datetime(data_watermark_at) IS NOT NULL),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  review_owner_role TEXT NOT NULL,
  CHECK (datetime(data_watermark_at) <= datetime(evidence_as_of)),
  CHECK (datetime(evidence_as_of) <= datetime(occurred_at)),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK ((review_sequence=1 AND supersedes_review_id IS NULL)
         OR (review_sequence>1 AND supersedes_review_id IS NOT NULL)),
  UNIQUE (item_snapshot_id,review_sequence),
  UNIQUE (item_snapshot_id,occurred_at),
  UNIQUE (portfolio_review_id,item_snapshot_id),
  FOREIGN KEY (portfolio_period_id,item_snapshot_id)
    REFERENCES portfolio_item_snapshot(portfolio_period_id,item_snapshot_id),
  FOREIGN KEY (supersedes_review_id,item_snapshot_id)
    REFERENCES portfolio_review(portfolio_review_id,item_snapshot_id)
) STRICT;

CREATE TABLE review_gate_evidence (
  review_gate_evidence_id TEXT PRIMARY KEY,
  portfolio_review_id TEXT NOT NULL,
  gate_domain_code TEXT NOT NULL,
  result_code TEXT NOT NULL CHECK (result_code IN (
    'PASS','FAIL','UNKNOWN','NOT_APPLICABLE'
  )),
  evidence_ref_hash TEXT,
  confirmed_at TEXT CHECK (confirmed_at IS NULL OR datetime(confirmed_at) IS NOT NULL),
  valid_until TEXT CHECK (valid_until IS NULL OR datetime(valid_until) IS NOT NULL),
  reason_code TEXT,
  na_decided_at TEXT CHECK (na_decided_at IS NULL OR datetime(na_decided_at) IS NOT NULL),
  na_recheck_trigger TEXT,
  na_recheck_triggered_at TEXT CHECK (
    na_recheck_triggered_at IS NULL OR datetime(na_recheck_triggered_at) IS NOT NULL
  ),
  CHECK (evidence_ref_hash IS NULL OR
         (length(evidence_ref_hash)=64
          AND evidence_ref_hash=lower(evidence_ref_hash)
          AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (
    (result_code IN ('PASS','FAIL') AND evidence_ref_hash IS NOT NULL
      AND confirmed_at IS NOT NULL AND valid_until IS NOT NULL
      AND reason_code IS NULL AND na_decided_at IS NULL
      AND na_recheck_trigger IS NULL AND na_recheck_triggered_at IS NULL)
    OR
    (result_code='UNKNOWN' AND reason_code IS NOT NULL
      AND na_decided_at IS NULL AND na_recheck_trigger IS NULL
      AND na_recheck_triggered_at IS NULL)
    OR
    (result_code='NOT_APPLICABLE' AND evidence_ref_hash IS NOT NULL
      AND confirmed_at IS NOT NULL AND reason_code IS NOT NULL
      AND na_decided_at IS NOT NULL
      AND (valid_until IS NOT NULL OR na_recheck_trigger IS NOT NULL))
  ),
  CHECK (valid_until IS NULL OR confirmed_at IS NULL
         OR datetime(valid_until) >= datetime(confirmed_at)),
  CHECK (na_recheck_triggered_at IS NULL OR na_recheck_trigger IS NOT NULL),
  UNIQUE (portfolio_review_id,gate_domain_code),
  FOREIGN KEY (portfolio_review_id) REFERENCES portfolio_review(portfolio_review_id),
  FOREIGN KEY (gate_domain_code) REFERENCES gate_domain_dim(gate_domain_code)
) STRICT;

CREATE TABLE portfolio_decision (
  portfolio_decision_id TEXT PRIMARY KEY,
  portfolio_review_id TEXT NOT NULL UNIQUE,
  action_code TEXT NOT NULL,
  exact_scope_code TEXT NOT NULL,
  effective_at TEXT NOT NULL CHECK (datetime(effective_at) IS NOT NULL),
  expires_at TEXT NOT NULL CHECK (datetime(expires_at) IS NOT NULL),
  decision_owner_role TEXT NOT NULL,
  decision_evidence_ref_hash TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(expires_at) > datetime(effective_at)),
  CHECK (datetime(recorded_at) >= datetime(effective_at)),
  CHECK (length(decision_evidence_ref_hash)=64
         AND decision_evidence_ref_hash=lower(decision_evidence_ref_hash)
         AND decision_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (portfolio_review_id) REFERENCES portfolio_review(portfolio_review_id),
  FOREIGN KEY (action_code) REFERENCES decision_action_dim(action_code)
) STRICT;

-- -----------------------------------------------------------------------------
-- Derived allocation, dependency, sunset, and fail-closed views
-- -----------------------------------------------------------------------------

CREATE VIEW v_latest_item_snapshot AS
WITH ranked AS (
  SELECT s.*,
         row_number() OVER (
           PARTITION BY s.item_id,s.portfolio_period_id
           ORDER BY s.snapshot_sequence DESC,datetime(s.decision_at) DESC,
                    datetime(s.recorded_at) DESC,s.item_snapshot_id DESC
         ) AS latest_rank
  FROM portfolio_item_snapshot s
)
SELECT * FROM ranked WHERE latest_rank=1;

CREATE VIEW v_latest_portfolio_review AS
WITH ranked AS (
  SELECT r.*,s.item_id,s.snapshot_sequence,
         row_number() OVER (
           PARTITION BY s.item_id,r.portfolio_period_id
           ORDER BY datetime(r.occurred_at) DESC,s.snapshot_sequence DESC,
                    r.review_sequence DESC,datetime(r.recorded_at) DESC,
                    r.portfolio_review_id DESC
         ) AS latest_rank
  FROM portfolio_review r
  JOIN portfolio_item_snapshot s
    ON s.portfolio_period_id=r.portfolio_period_id
   AND s.item_snapshot_id=r.item_snapshot_id
)
SELECT * FROM ranked WHERE latest_rank=1;

CREATE VIEW v_latest_review_by_snapshot AS
WITH ranked AS (
  SELECT r.*,
         row_number() OVER (
           PARTITION BY r.item_snapshot_id
           ORDER BY r.review_sequence DESC,datetime(r.occurred_at) DESC,
                    datetime(r.recorded_at) DESC,r.portfolio_review_id DESC
         ) AS latest_rank
  FROM portfolio_review r
)
SELECT * FROM ranked WHERE latest_rank=1;

CREATE VIEW v_period_role_plan AS
WITH plan_by_role AS (
  SELECT a.portfolio_period_id,s.allocation_role_code,
         count(*) AS allocation_line_count,
         sum(a.founder_minutes) AS planned_founder_minutes,
         sum(a.cash_micros) AS planned_cash_micros
  FROM portfolio_resource_allocation a
  JOIN v_latest_item_snapshot s
    ON s.portfolio_period_id=a.portfolio_period_id
   AND s.item_snapshot_id=a.item_snapshot_id
  WHERE a.allocation_phase='plan'
  GROUP BY a.portfolio_period_id,s.allocation_role_code
)
SELECT p.portfolio_period_id,r.allocation_role_code,
       g.guardrail_set_id,
       rg.time_min_ppm,rg.time_max_ppm,rg.cash_min_ppm,rg.cash_max_ppm,
       coalesce(x.allocation_line_count,0) AS allocation_line_count,
       coalesce(x.planned_founder_minutes,0) AS planned_founder_minutes,
       coalesce(x.planned_cash_micros,0) AS planned_cash_micros,
       CASE WHEN p.discretionary_founder_minutes > 0
            THEN (coalesce(x.planned_founder_minutes,0) * 1000000)
                 / p.discretionary_founder_minutes END AS time_share_ppm,
       CASE WHEN p.discretionary_cash_micros > 0
            THEN (coalesce(x.planned_cash_micros,0) * 1000000)
                 / p.discretionary_cash_micros END AS cash_share_ppm
FROM portfolio_period p
CROSS JOIN allocation_role_dim r
LEFT JOIN allocation_guardrail_set g
  ON g.portfolio_period_id=p.portfolio_period_id
LEFT JOIN role_allocation_guardrail rg
  ON rg.guardrail_set_id=g.guardrail_set_id
 AND rg.allocation_role_code=r.allocation_role_code
LEFT JOIN plan_by_role x
  ON x.portfolio_period_id=p.portfolio_period_id
 AND x.allocation_role_code=r.allocation_role_code;

CREATE VIEW v_waterfall_evidence_reconciliation AS
WITH evidence_pivot AS (
  SELECT portfolio_period_id,count(*) AS component_count,
         max(CASE WHEN resource_kind='time' AND component_code='common_admin_compliance'
                  THEN amount_units END) AS common_admin_compliance_minutes,
         max(CASE WHEN resource_kind='time' AND component_code='contracted_delivery_support'
                  THEN amount_units END) AS contracted_delivery_support_minutes,
         max(CASE WHEN resource_kind='time' AND component_code='known_incident_remediation'
                  THEN amount_units END) AS known_incident_remediation_minutes,
         max(CASE WHEN resource_kind='time' AND component_code='continuity_reserve'
                  THEN amount_units END) AS continuity_reserve_minutes,
         max(CASE WHEN resource_kind='time' AND component_code='active_sunset'
                  THEN amount_units END) AS active_sunset_minutes,
         max(CASE WHEN resource_kind='cash' AND component_code='tax_social_internal_restriction'
                  THEN amount_units END) AS tax_social_internal_restriction_micros,
         max(CASE WHEN resource_kind='cash' AND component_code='customer_obligations'
                  THEN amount_units END) AS customer_obligations_micros,
         max(CASE WHEN resource_kind='cash' AND component_code='confirmed_payables_vendor_spend'
                  THEN amount_units END) AS confirmed_payables_vendor_spend_micros,
         max(CASE WHEN resource_kind='cash' AND component_code='absolute_minimum_cash'
                  THEN amount_units END) AS absolute_minimum_cash_micros,
         max(CASE WHEN resource_kind='cash' AND component_code='continuity_reserve'
                  THEN amount_units END) AS continuity_reserve_cash_micros,
         max(CASE WHEN resource_kind='cash' AND component_code='open_sunset_funding'
                  THEN amount_units END) AS open_sunset_funding_micros
  FROM waterfall_component_evidence
  GROUP BY portfolio_period_id
)
SELECT p.portfolio_period_id,coalesce(e.component_count,0) AS component_count,
       CASE WHEN e.portfolio_period_id IS NULL OR e.component_count<>11 THEN 1 ELSE 0 END
         AS missing_component_failure,
       CASE WHEN e.portfolio_period_id IS NULL THEN 0 ELSE
         (p.common_admin_compliance_minutes IS NOT e.common_admin_compliance_minutes)
         +(p.contracted_delivery_support_minutes IS NOT e.contracted_delivery_support_minutes)
         +(p.known_incident_remediation_minutes IS NOT e.known_incident_remediation_minutes)
         +(p.continuity_reserve_minutes IS NOT e.continuity_reserve_minutes)
         +(p.active_sunset_minutes IS NOT e.active_sunset_minutes)
         +(p.tax_social_internal_restriction_micros IS NOT e.tax_social_internal_restriction_micros)
         +(p.customer_obligations_micros IS NOT e.customer_obligations_micros)
         +(p.confirmed_payables_vendor_spend_micros IS NOT e.confirmed_payables_vendor_spend_micros)
         +(p.absolute_minimum_cash_micros IS NOT e.absolute_minimum_cash_micros)
         +(p.continuity_reserve_cash_micros IS NOT e.continuity_reserve_cash_micros)
         +(p.open_sunset_funding_micros IS NOT e.open_sunset_funding_micros)
       END AS amount_mismatch_count
FROM portfolio_period p
LEFT JOIN evidence_pivot e USING (portfolio_period_id);

CREATE VIEW v_period_allocation_integrity AS
WITH item_counts AS (
  SELECT p.portfolio_period_id,
         count(DISTINCT s.item_snapshot_id) AS snapshot_count,
         count(DISTINCT CASE WHEN a.allocation_phase='plan'
                             THEN a.item_snapshot_id END) AS planned_snapshot_count,
         coalesce(sum(CASE WHEN a.allocation_phase='plan'
                           THEN a.founder_minutes ELSE 0 END),0) AS planned_minutes,
         coalesce(sum(CASE WHEN a.allocation_phase='plan'
                           THEN a.cash_micros ELSE 0 END),0) AS planned_cash_micros
  FROM portfolio_period p
  LEFT JOIN v_latest_item_snapshot s
    ON s.portfolio_period_id=p.portfolio_period_id
  LEFT JOIN portfolio_resource_allocation a
    ON a.portfolio_period_id=s.portfolio_period_id
   AND a.item_snapshot_id=s.item_snapshot_id
  GROUP BY p.portfolio_period_id
), role_checks AS (
  SELECT portfolio_period_id,
         count(*) AS expected_role_count,
         sum(CASE WHEN guardrail_set_id IS NOT NULL
                       AND time_min_ppm IS NOT NULL AND time_max_ppm IS NOT NULL
                       AND cash_min_ppm IS NOT NULL AND cash_max_ppm IS NOT NULL
                  THEN 1 ELSE 0 END) AS complete_guardrail_roles,
         sum(CASE WHEN allocation_line_count > 0 THEN 1 ELSE 0 END)
           AS explicitly_allocated_roles,
         sum(CASE WHEN time_min_ppm IS NOT NULL
                       AND (time_share_ppm < time_min_ppm
                            OR time_share_ppm > time_max_ppm)
                  THEN 1 ELSE 0 END) AS time_guardrail_breaches,
         sum(CASE WHEN cash_min_ppm IS NOT NULL
                       AND (cash_share_ppm < cash_min_ppm
                            OR cash_share_ppm > cash_max_ppm)
                  THEN 1 ELSE 0 END) AS cash_guardrail_breaches
  FROM v_period_role_plan
  GROUP BY portfolio_period_id
)
SELECT p.portfolio_period_id,
       i.snapshot_count,i.planned_snapshot_count,
       r.expected_role_count,r.complete_guardrail_roles,r.explicitly_allocated_roles,
       i.planned_minutes,p.discretionary_founder_minutes,
       p.discretionary_founder_minutes-i.planned_minutes
         AS unallocated_founder_minutes,
       i.planned_cash_micros,p.discretionary_cash_micros,
       p.discretionary_cash_micros-i.planned_cash_micros
         AS unallocated_cash_micros,
       p.total_portfolio_capacity_minutes,p.continuity_reserve_minutes,
       p.actual_business_cash_micros,p.continuity_reserve_cash_micros,
       w.component_count,w.missing_component_failure,w.amount_mismatch_count,
       r.time_guardrail_breaches,r.cash_guardrail_breaches,
       CASE
         WHEN r.complete_guardrail_roles<>r.expected_role_count
           OR r.explicitly_allocated_roles<>r.expected_role_count
           OR i.planned_snapshot_count<>i.snapshot_count
           OR w.missing_component_failure>0
         THEN 'UNKNOWN'
         WHEN w.amount_mismatch_count>0
           OR p.continuity_reserve_minutes<g.reserve_founder_minutes_min
           OR p.continuity_reserve_minutes>g.reserve_founder_minutes_max
           OR p.continuity_reserve_cash_micros<g.reserve_cash_micros_min
           OR p.continuity_reserve_cash_micros>g.reserve_cash_micros_max
           OR i.planned_minutes>p.discretionary_founder_minutes
           OR i.planned_cash_micros>p.discretionary_cash_micros
           OR r.time_guardrail_breaches>0 OR r.cash_guardrail_breaches>0
         THEN 'PAUSE'
         ELSE 'READY_TO_DECIDE'
       END AS allocation_gate_state
FROM portfolio_period p
JOIN item_counts i USING (portfolio_period_id)
JOIN role_checks r USING (portfolio_period_id)
JOIN v_waterfall_evidence_reconciliation w USING (portfolio_period_id)
LEFT JOIN allocation_guardrail_set g USING (portfolio_period_id);

-- One row per item and shared failure domain. Repeated direct dependencies inside
-- the same domain increase link count, but never duplicate item exposure.
CREATE VIEW v_item_failure_domain AS
SELECT s.portfolio_period_id,s.item_snapshot_id,s.item_id,
       d.failure_domain_id,
       count(DISTINCT id.item_dependency_id) AS direct_dependency_links,
       max(s.trailing_monthly_cash_contribution_micros)
         AS trailing_monthly_cash_contribution_micros,
       max(s.active_paying_customers) AS active_paying_customers,
       max(d.recovery_target_minutes) AS max_dependency_recovery_target_minutes
FROM v_latest_item_snapshot s
JOIN item_dependency id ON id.item_snapshot_id=s.item_snapshot_id
JOIN dependency_snapshot d
  ON d.dependency_snapshot_id=id.dependency_snapshot_id
GROUP BY s.portfolio_period_id,s.item_snapshot_id,s.item_id,d.failure_domain_id;

CREATE VIEW v_failure_domain_exposure AS
SELECT x.portfolio_period_id,x.failure_domain_id,f.domain_kind,
       count(*) AS distinct_exposed_items,
       sum(x.direct_dependency_links) AS direct_dependency_links,
       sum(x.trailing_monthly_cash_contribution_micros)
         AS contribution_exposure_micros,
       sum(x.active_paying_customers) AS active_customer_exposure,
       max(max_dependency_recovery_target_minutes)
         AS max_dependency_recovery_target_minutes
FROM v_item_failure_domain x
JOIN failure_domain f USING (failure_domain_id)
GROUP BY x.portfolio_period_id,x.failure_domain_id,f.domain_kind;

CREATE VIEW v_item_dependency_readiness AS
WITH dependency_facts AS (
  SELECT s.item_snapshot_id,s.dependency_applicability,
         CASE WHEN s.dependency_applicability='not_applicable' AND (
                    s.dependency_na_reason IS NULL
                    OR s.dependency_na_evidence_ref_hash IS NULL
                    OR s.dependency_na_decided_at IS NULL
                    OR (s.dependency_na_valid_until IS NULL
                        AND s.dependency_na_recheck_trigger IS NULL)
                    OR (s.dependency_na_valid_until IS NOT NULL
                        AND datetime(s.dependency_na_valid_until)<datetime(m.event_time_as_of))
                    OR (s.dependency_na_recheck_triggered_at IS NOT NULL
                        AND datetime(s.dependency_na_recheck_triggered_at)<=datetime(m.event_time_as_of))
                  ) THEN 1 ELSE 0 END AS na_unknown,
         count(d.dependency_snapshot_id) AS dependency_count,
         sum(CASE WHEN d.criticality='critical' AND d.observed_state='failed'
                       AND d.fallback_state<>'verified'
                  THEN 1 ELSE 0 END) AS stop_failures,
         sum(CASE WHEN d.dependency_snapshot_id IS NOT NULL
                       AND NOT (d.criticality='critical'
                                AND d.observed_state='failed'
                                AND d.fallback_state<>'verified')
                       AND (d.observed_state='failed'
                            OR (d.criticality='critical'
                                AND (d.observed_state='degraded'
                                     OR d.fallback_state IN ('none','unverified'))))
                  THEN 1 ELSE 0 END) AS pause_findings,
         sum(CASE WHEN d.dependency_snapshot_id IS NOT NULL
                       AND (d.observed_state='unknown'
                            OR d.fallback_state='unknown'
                            OR d.observed_at IS NULL
                            OR julianday(m.event_time_as_of)-julianday(d.observed_at)
                               > d.max_evidence_age_days)
                  THEN 1 ELSE 0 END) AS unknown_findings
  FROM portfolio_item_snapshot s
  CROSS JOIN model_settings m
  LEFT JOIN item_dependency id ON id.item_snapshot_id=s.item_snapshot_id
  LEFT JOIN dependency_snapshot d
    ON d.dependency_snapshot_id=id.dependency_snapshot_id
  GROUP BY s.item_snapshot_id,s.dependency_applicability,
           s.dependency_na_reason,s.dependency_na_evidence_ref_hash,
           s.dependency_na_decided_at,s.dependency_na_valid_until,
           s.dependency_na_recheck_trigger,s.dependency_na_recheck_triggered_at
)
SELECT item_snapshot_id,dependency_applicability,dependency_count,
       stop_failures,pause_findings,unknown_findings,na_unknown,
       CASE
         WHEN dependency_applicability='not_applicable' AND na_unknown=1 THEN 'UNKNOWN'
         WHEN dependency_applicability='not_applicable' THEN 'READY_TO_DECIDE'
         WHEN stop_failures>0 THEN 'STOP'
         WHEN pause_findings>0 THEN 'PAUSE'
         WHEN dependency_count=0 OR unknown_findings>0 THEN 'UNKNOWN'
         ELSE 'READY_TO_DECIDE'
       END AS dependency_gate_state
FROM dependency_facts;

CREATE VIEW v_current_sunset_plan_status_event AS
WITH ranked AS (
  SELECT e.*,
         row_number() OVER (
           PARTITION BY e.sunset_plan_id
           ORDER BY e.event_sequence DESC,datetime(e.occurred_at) DESC,
                    datetime(e.recorded_at) DESC,e.sunset_plan_status_event_id DESC
         ) AS latest_rank
  FROM sunset_plan_status_event e
)
SELECT * FROM ranked WHERE latest_rank=1;

CREATE VIEW v_current_sunset_liability AS
WITH ranked AS (
  SELECT l.*,
         row_number() OVER (
           PARTITION BY l.sunset_plan_id,l.liability_stream_id
           ORDER BY l.event_sequence DESC,datetime(l.occurred_at) DESC,
                    datetime(l.recorded_at) DESC,l.sunset_liability_id DESC
         ) AS latest_rank
  FROM sunset_liability l
)
SELECT * FROM ranked WHERE latest_rank=1;

CREATE VIEW v_sunset_readiness AS
WITH liability_summary AS (
  SELECT p.sunset_plan_id,p.item_snapshot_id,e.plan_status,
         e.funded_cash_micros,e.reserved_founder_minutes,
         count(DISTINCT l.liability_domain_code) AS represented_domains,
         sum(CASE WHEN l.liability_status='unknown' THEN 1 ELSE 0 END)
           AS unknown_status_count,
         sum(CASE WHEN l.liability_status IN ('open','blocked')
                       AND (l.estimated_cash_micros IS NULL
                            OR l.estimated_founder_minutes IS NULL
                            OR l.due_at IS NULL)
                  THEN 1 ELSE 0 END) AS unknown_estimate_count,
         sum(CASE WHEN l.liability_status='open' THEN 1 ELSE 0 END)
           AS open_count,
         sum(CASE WHEN l.liability_status='blocked' THEN 1 ELSE 0 END)
           AS blocked_count,
         sum(CASE WHEN l.liability_status NOT IN (
                       'complete','transferred','not_applicable'
                     ) THEN 1 ELSE 0 END) AS unresolved_count,
         sum(CASE WHEN l.liability_status IN ('complete','transferred','not_applicable')
                       AND l.completion_evidence_ref_hash IS NULL
                  THEN 1 ELSE 0 END) AS missing_final_evidence_count,
         sum(CASE WHEN l.liability_status='not_applicable' AND (
                       l.na_decided_at IS NULL
                       OR (l.na_valid_until IS NULL AND l.na_recheck_trigger IS NULL)
                       OR (l.na_valid_until IS NOT NULL
                           AND datetime(l.na_valid_until)<datetime(m.event_time_as_of))
                       OR (l.na_recheck_triggered_at IS NOT NULL
                           AND datetime(l.na_recheck_triggered_at)<=datetime(m.event_time_as_of))
                     ) THEN 1 ELSE 0 END) AS stale_na_count,
         sum(CASE WHEN l.liability_status IN ('open','blocked')
                       AND l.due_at IS NOT NULL
                       AND datetime(l.due_at)<datetime(m.event_time_as_of)
                  THEN 1 ELSE 0 END) AS overdue_count,
         sum(CASE WHEN l.breach_state='yes' AND l.severity='critical'
                  THEN 1 ELSE 0 END) AS active_critical_breach_count,
         sum(CASE WHEN l.breach_state='yes' AND l.severity='high'
                  THEN 1 ELSE 0 END) AS active_high_breach_count,
         coalesce(sum(CASE WHEN l.liability_status IN ('open','blocked')
                           THEN l.estimated_cash_micros ELSE 0 END),0)
           AS known_open_cash_micros,
         coalesce(sum(CASE WHEN l.liability_status IN ('open','blocked')
                           THEN l.estimated_founder_minutes ELSE 0 END),0)
           AS known_open_founder_minutes
  FROM sunset_plan p
  CROSS JOIN model_settings m
  LEFT JOIN v_current_sunset_plan_status_event e
    ON e.sunset_plan_id=p.sunset_plan_id
  LEFT JOIN v_current_sunset_liability l
    ON l.sunset_plan_id=p.sunset_plan_id
  GROUP BY p.sunset_plan_id,p.item_snapshot_id,e.plan_status,
           e.funded_cash_micros,e.reserved_founder_minutes
)
SELECT s.*,
       (SELECT count(*) FROM sunset_liability_domain_dim)-represented_domains
         AS missing_domain_count,
       max(known_open_cash_micros-funded_cash_micros,0)
         AS unfunded_cash_micros,
       max(known_open_founder_minutes-reserved_founder_minutes,0)
         AS unreserved_founder_minutes,
       CASE
         WHEN active_critical_breach_count>0 THEN 'STOP'
         WHEN active_high_breach_count>0 OR blocked_count>0 OR overdue_count>0
           OR known_open_cash_micros>funded_cash_micros
           OR known_open_founder_minutes>reserved_founder_minutes
           OR open_count>0
         THEN 'PAUSE'
         WHEN plan_status IS NULL
           OR represented_domains<>(SELECT count(*) FROM sunset_liability_domain_dim)
           OR unknown_status_count>0 OR unknown_estimate_count>0
           OR stale_na_count>0 OR missing_final_evidence_count>0
         THEN 'UNKNOWN'
         ELSE 'READY_TO_DECIDE'
       END AS sunset_gate_state
FROM liability_summary s;

CREATE VIEW v_review_gate_summary AS
WITH evidence_matrix AS (
  SELECT r.portfolio_review_id,g.gate_domain_code,g.fail_effect,
         e.result_code,e.evidence_ref_hash,e.valid_until,
         e.na_decided_at,e.na_recheck_trigger,e.na_recheck_triggered_at,
         CASE WHEN e.review_gate_evidence_id IS NULL
                   OR e.result_code='UNKNOWN'
                   OR (e.result_code IN ('PASS','FAIL')
                       AND datetime(e.valid_until)<datetime(r.evidence_as_of))
                   OR (e.result_code='NOT_APPLICABLE' AND (
                         e.na_decided_at IS NULL
                         OR (e.valid_until IS NULL AND e.na_recheck_trigger IS NULL)
                         OR (e.valid_until IS NOT NULL
                             AND datetime(e.valid_until)<datetime(r.evidence_as_of))
                         OR (e.na_recheck_triggered_at IS NOT NULL
                             AND datetime(e.na_recheck_triggered_at)<=datetime(r.evidence_as_of))
                       ))
              THEN 1 ELSE 0 END AS is_unknown,
         CASE WHEN e.result_code='FAIL'
                   AND datetime(e.valid_until)>=datetime(r.evidence_as_of)
                   AND g.fail_effect='STOP'
              THEN 1 ELSE 0 END AS is_stop_failure,
         CASE WHEN e.result_code='FAIL'
                   AND datetime(e.valid_until)>=datetime(r.evidence_as_of)
                   AND g.fail_effect='PAUSE'
              THEN 1 ELSE 0 END AS is_pause_failure
  FROM portfolio_review r
  CROSS JOIN gate_domain_dim g
  LEFT JOIN review_gate_evidence e
    ON e.portfolio_review_id=r.portfolio_review_id
   AND e.gate_domain_code=g.gate_domain_code
)
SELECT portfolio_review_id,count(*) AS expected_gate_count,
       sum(is_unknown) AS unknown_gate_count,
       sum(is_stop_failure) AS stop_failure_count,
       sum(is_pause_failure) AS pause_failure_count
FROM evidence_matrix
GROUP BY portfolio_review_id;

-- Historical review state is frozen above at evidence_as_of. Current actions and
-- closure must re-evaluate evidence expiry and N/A recheck events at the model's
-- explicit event-time watermark rather than inheriting that historical result.
CREATE VIEW v_current_review_gate_summary AS
WITH evidence_matrix AS (
  SELECT r.portfolio_review_id,m.event_time_as_of AS freshness_as_of,
         g.gate_domain_code,g.fail_effect,e.result_code,
         CASE WHEN e.review_gate_evidence_id IS NULL
                   OR e.result_code='UNKNOWN'
                   OR (e.result_code IN ('PASS','FAIL','NOT_APPLICABLE')
                       AND (e.confirmed_at IS NULL
                            OR datetime(e.confirmed_at)
                               >datetime(m.event_time_as_of)))
                   OR (e.result_code IN ('PASS','FAIL')
                       AND datetime(e.valid_until)<datetime(m.event_time_as_of))
                   OR (e.result_code='NOT_APPLICABLE' AND (
                         e.na_decided_at IS NULL
                         OR datetime(e.na_decided_at)>datetime(m.event_time_as_of)
                         OR (e.valid_until IS NULL AND e.na_recheck_trigger IS NULL)
                         OR (e.valid_until IS NOT NULL
                             AND datetime(e.valid_until)<datetime(m.event_time_as_of))
                         OR (e.na_recheck_triggered_at IS NOT NULL
                             AND datetime(e.na_recheck_triggered_at)
                                 <=datetime(m.event_time_as_of))
                       ))
              THEN 1 ELSE 0 END AS is_unknown,
         CASE WHEN e.result_code='FAIL'
                   AND datetime(e.confirmed_at)<=datetime(m.event_time_as_of)
                   AND datetime(e.valid_until)>=datetime(m.event_time_as_of)
                   AND g.fail_effect='STOP'
              THEN 1 ELSE 0 END AS is_stop_failure,
         CASE WHEN e.result_code='FAIL'
                   AND datetime(e.confirmed_at)<=datetime(m.event_time_as_of)
                   AND datetime(e.valid_until)>=datetime(m.event_time_as_of)
                   AND g.fail_effect='PAUSE'
              THEN 1 ELSE 0 END AS is_pause_failure
  FROM portfolio_review r
  CROSS JOIN model_settings m
  CROSS JOIN gate_domain_dim g
  LEFT JOIN review_gate_evidence e
    ON e.portfolio_review_id=r.portfolio_review_id
   AND e.gate_domain_code=g.gate_domain_code
)
SELECT portfolio_review_id,max(freshness_as_of) AS freshness_as_of,
       count(*) AS expected_gate_count,
       sum(is_unknown) AS unknown_gate_count,
       sum(is_stop_failure) AS stop_failure_count,
       sum(is_pause_failure) AS pause_failure_count
FROM evidence_matrix
GROUP BY portfolio_review_id;

CREATE VIEW v_sunset_close_readiness AS
SELECT z.sunset_plan_id,z.item_snapshot_id,z.plan_status,p.service_end_at,
       z.represented_domains,z.missing_domain_count,z.unresolved_count,
       z.missing_final_evidence_count,z.stale_na_count,
       z.active_critical_breach_count,z.active_high_breach_count,
       lr.portfolio_review_id AS latest_portfolio_review_id,
       lr.occurred_at AS latest_review_occurred_at,
       lr.recorded_at AS latest_review_recorded_at,
       g.freshness_as_of AS gate_freshness_as_of,
       g.stop_failure_count,g.pause_failure_count,g.unknown_gate_count,
       CASE WHEN z.plan_status='closure_review'
                   AND z.missing_domain_count=0
                   AND coalesce(z.unresolved_count,0)=0
                   AND coalesce(z.missing_final_evidence_count,0)=0
                   AND coalesce(z.stale_na_count,0)=0
                   AND coalesce(z.active_critical_breach_count,0)=0
                   AND coalesce(z.active_high_breach_count,0)=0
                   AND lr.portfolio_review_id IS NOT NULL
                   AND coalesce(g.stop_failure_count,1)=0
                   AND coalesce(g.pause_failure_count,1)=0
                   AND coalesce(g.unknown_gate_count,1)=0
            THEN 1 ELSE 0 END AS closure_ready
FROM v_sunset_readiness z
JOIN sunset_plan p
  ON p.sunset_plan_id=z.sunset_plan_id
 AND p.item_snapshot_id=z.item_snapshot_id
JOIN portfolio_item_snapshot s ON s.item_snapshot_id=z.item_snapshot_id
LEFT JOIN v_latest_review_by_snapshot lr
  ON lr.item_snapshot_id=s.item_snapshot_id
LEFT JOIN v_current_review_gate_summary g
  ON g.portfolio_review_id=lr.portfolio_review_id;

CREATE VIEW v_portfolio_review_readiness AS
WITH joined AS (
  SELECT r.portfolio_review_id,r.portfolio_period_id,r.item_snapshot_id,
         s.item_id,s.allocation_role_code,s.lifecycle_code,
         g.expected_gate_count,g.unknown_gate_count,
         g.stop_failure_count,g.pause_failure_count,
         a.allocation_gate_state,d.dependency_gate_state,
         CASE WHEN s.lifecycle_code IN ('sunset_planned','sunset_active','closed')
              THEN coalesce(z.sunset_gate_state,'UNKNOWN')
              ELSE 'READY_TO_DECIDE' END AS applicable_sunset_gate_state
  FROM portfolio_review r
  JOIN portfolio_item_snapshot s
    ON s.portfolio_period_id=r.portfolio_period_id
   AND s.item_snapshot_id=r.item_snapshot_id
  JOIN v_current_review_gate_summary g USING (portfolio_review_id)
  JOIN v_period_allocation_integrity a USING (portfolio_period_id)
  JOIN v_item_dependency_readiness d USING (item_snapshot_id)
  LEFT JOIN v_sunset_readiness z ON z.item_snapshot_id=s.item_snapshot_id
)
SELECT j.*,
       CASE
         WHEN stop_failure_count>0 OR dependency_gate_state='STOP'
           OR applicable_sunset_gate_state='STOP'
         THEN 'STOP'
         WHEN pause_failure_count>0 OR allocation_gate_state='PAUSE'
           OR dependency_gate_state='PAUSE'
           OR applicable_sunset_gate_state='PAUSE'
         THEN 'PAUSE'
         WHEN unknown_gate_count>0 OR allocation_gate_state='UNKNOWN'
           OR dependency_gate_state='UNKNOWN'
           OR applicable_sunset_gate_state='UNKNOWN'
         THEN 'UNKNOWN'
         ELSE 'READY_TO_DECIDE'
       END AS derived_gate_state
FROM joined j;

CREATE VIEW v_review_action_compatibility AS
SELECT r.portfolio_review_id,r.item_snapshot_id,r.derived_gate_state,
       a.action_code,p.action_allowed
FROM v_portfolio_review_readiness r
CROSS JOIN decision_action_dim a
JOIN gate_action_policy p
  ON p.gate_state_code=r.derived_gate_state
 AND p.action_code=a.action_code;

CREATE VIEW v_recorded_decision_compatibility AS
SELECT d.portfolio_decision_id,d.portfolio_review_id,d.action_code,
       c.derived_gate_state,c.action_allowed AS recorded_decision_compatible
FROM portfolio_decision d
JOIN v_review_action_compatibility c
  ON c.portfolio_review_id=d.portfolio_review_id
 AND c.action_code=d.action_code;

-- -----------------------------------------------------------------------------
-- Write-time guards
-- -----------------------------------------------------------------------------

CREATE TRIGGER validate_sunset_plan_lifecycle_before_insert
BEFORE INSERT ON sunset_plan
BEGIN
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM portfolio_item_snapshot s
    WHERE s.item_snapshot_id=NEW.item_snapshot_id
      AND s.lifecycle_code IN ('sunset_planned','sunset_active')
  ) THEN RAISE(ABORT,'sunset plan requires planned or active sunset lifecycle') END;
END;

CREATE TRIGGER validate_item_snapshot_sequence_before_insert
BEFORE INSERT ON portfolio_item_snapshot
BEGIN
  SELECT CASE WHEN NEW.snapshot_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM portfolio_item_snapshot p
    WHERE p.item_snapshot_id=NEW.supersedes_item_snapshot_id
      AND p.item_id=NEW.item_id
      AND p.portfolio_period_id=NEW.portfolio_period_id
      AND p.snapshot_sequence=NEW.snapshot_sequence-1
      AND datetime(p.decision_at)<=datetime(NEW.decision_at)
      AND datetime(p.recorded_at)<=datetime(NEW.decision_at)
  ) THEN RAISE(ABORT,'item snapshot sequence or predecessor invalid') END;
END;

CREATE TRIGGER validate_closed_lifecycle_before_insert
BEFORE INSERT ON portfolio_item_snapshot
WHEN NEW.lifecycle_code='closed'
BEGIN
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1
    FROM sunset_close_event c
    JOIN sunset_plan p ON p.sunset_plan_id=c.sunset_plan_id
    JOIN portfolio_item_snapshot source
      ON source.item_snapshot_id=p.item_snapshot_id
    WHERE c.sunset_close_event_id=NEW.closure_event_id
      AND source.item_id=NEW.item_id
      AND datetime(c.occurred_at)<=datetime(NEW.decision_at)
      AND datetime(c.recorded_at)<=datetime(NEW.decision_at)
  ) THEN RAISE(ABORT,'closed lifecycle requires validated prior close event') END;
END;

CREATE TRIGGER validate_sunset_plan_status_sequence_before_insert
BEFORE INSERT ON sunset_plan_status_event
BEGIN
  SELECT CASE WHEN NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM sunset_plan_status_event p
    WHERE p.sunset_plan_status_event_id=NEW.supersedes_status_event_id
      AND p.sunset_plan_id=NEW.sunset_plan_id
      AND p.event_sequence=NEW.event_sequence-1
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.occurred_at)
  ) THEN RAISE(ABORT,'sunset plan status sequence or predecessor invalid') END;
END;

CREATE TRIGGER validate_sunset_liability_sequence_before_insert
BEFORE INSERT ON sunset_liability
BEGIN
  SELECT CASE WHEN NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM sunset_liability p
    WHERE p.sunset_liability_id=NEW.supersedes_liability_id
      AND p.sunset_plan_id=NEW.sunset_plan_id
      AND p.liability_stream_id=NEW.liability_stream_id
      AND p.liability_domain_code=NEW.liability_domain_code
      AND p.obligation_ref_hash=NEW.obligation_ref_hash
      AND p.event_sequence=NEW.event_sequence-1
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.occurred_at)
  ) THEN RAISE(ABORT,'sunset liability sequence or predecessor invalid') END;
END;

CREATE TRIGGER validate_sunset_close_event_before_insert
BEFORE INSERT ON sunset_close_event
BEGIN
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM model_settings m
    WHERE datetime(NEW.occurred_at)<=datetime(m.event_time_as_of)
  ) THEN RAISE(ABORT,'sunset close event exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM sunset_plan p
    WHERE p.sunset_plan_id=NEW.sunset_plan_id
      AND datetime(NEW.occurred_at)>=datetime(p.service_end_at)
  ) THEN RAISE(ABORT,'sunset close event predates service end') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM v_sunset_close_readiness r
    WHERE r.sunset_plan_id=NEW.sunset_plan_id AND r.closure_ready=1
  ) THEN RAISE(ABORT,'sunset closure gates not ready') END;
  SELECT CASE WHEN EXISTS (
    SELECT 1 FROM v_current_sunset_plan_status_event e
    WHERE e.sunset_plan_id=NEW.sunset_plan_id
      AND datetime(e.recorded_at)>datetime(NEW.occurred_at)
  ) OR EXISTS (
    SELECT 1 FROM v_current_sunset_liability l
    WHERE l.sunset_plan_id=NEW.sunset_plan_id
      AND datetime(l.recorded_at)>datetime(NEW.occurred_at)
  ) OR EXISTS (
    SELECT 1
    FROM sunset_plan p
    JOIN v_latest_review_by_snapshot r
      ON r.item_snapshot_id=p.item_snapshot_id
    WHERE p.sunset_plan_id=NEW.sunset_plan_id
      AND (datetime(r.occurred_at)>datetime(NEW.occurred_at)
           OR datetime(r.recorded_at)>datetime(NEW.occurred_at))
  ) THEN RAISE(ABORT,'sunset close event predates closure evidence') END;
END;

CREATE TRIGGER validate_unfunded_allocation_before_insert
BEFORE INSERT ON portfolio_resource_allocation
BEGIN
  SELECT CASE WHEN (NEW.founder_minutes<>0 OR NEW.cash_micros<>0) AND EXISTS (
    SELECT 1 FROM portfolio_item_snapshot s
    WHERE s.portfolio_period_id=NEW.portfolio_period_id
      AND s.item_snapshot_id=NEW.item_snapshot_id
      AND s.allocation_role_code='unfunded'
  ) THEN RAISE(ABORT,'unfunded item allocation must be explicitly zero') END;
END;

CREATE TRIGGER validate_review_gate_time_before_insert
BEFORE INSERT ON review_gate_evidence
BEGIN
  SELECT CASE WHEN NEW.confirmed_at IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM portfolio_review r
    WHERE r.portfolio_review_id=NEW.portfolio_review_id
      AND datetime(NEW.confirmed_at)<=datetime(r.evidence_as_of)
  ) THEN RAISE(ABORT,'gate evidence confirmed after review cutoff') END;
  SELECT CASE WHEN NEW.result_code='NOT_APPLICABLE' AND NOT EXISTS (
    SELECT 1 FROM portfolio_review r
    WHERE r.portfolio_review_id=NEW.portfolio_review_id
      AND datetime(NEW.na_decided_at)<=datetime(r.evidence_as_of)
      AND (NEW.na_recheck_triggered_at IS NULL
           OR datetime(NEW.na_recheck_triggered_at)>=datetime(NEW.na_decided_at))
  ) THEN RAISE(ABORT,'N/A decision or recheck time invalid') END;
END;

CREATE TRIGGER validate_portfolio_review_sequence_before_insert
BEFORE INSERT ON portfolio_review
BEGIN
  SELECT CASE WHEN NEW.review_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM portfolio_review p
    WHERE p.portfolio_review_id=NEW.supersedes_review_id
      AND p.item_snapshot_id=NEW.item_snapshot_id
      AND p.review_sequence=NEW.review_sequence-1
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.occurred_at)
  ) THEN RAISE(ABORT,'portfolio review sequence or predecessor invalid') END;
END;

CREATE TRIGGER validate_portfolio_decision_before_insert
BEFORE INSERT ON portfolio_decision
BEGIN
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM portfolio_review r
    WHERE r.portfolio_review_id=NEW.portfolio_review_id
      AND datetime(NEW.effective_at)>=datetime(r.occurred_at)
  ) THEN RAISE(ABORT,'decision predates review') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM v_review_action_compatibility c
    WHERE c.portfolio_review_id=NEW.portfolio_review_id
      AND c.action_code=NEW.action_code
      AND c.action_allowed=1
  ) THEN RAISE(ABORT,'action incompatible with fail-closed gate state') END;
END;

-- Critical vocabularies, snapshots, evidence, and decisions are append-only.
CREATE TRIGGER immutable_allocation_role_update BEFORE UPDATE ON allocation_role_dim
BEGIN SELECT RAISE(ABORT,'allocation role vocabulary is immutable'); END;
CREATE TRIGGER immutable_allocation_role_delete BEFORE DELETE ON allocation_role_dim
BEGIN SELECT RAISE(ABORT,'allocation role vocabulary is immutable'); END;
CREATE TRIGGER immutable_lifecycle_update BEFORE UPDATE ON lifecycle_dim
BEGIN SELECT RAISE(ABORT,'lifecycle vocabulary is immutable'); END;
CREATE TRIGGER immutable_lifecycle_delete BEFORE DELETE ON lifecycle_dim
BEGIN SELECT RAISE(ABORT,'lifecycle vocabulary is immutable'); END;
CREATE TRIGGER immutable_item_snapshot_update BEFORE UPDATE ON portfolio_item_snapshot
BEGIN SELECT RAISE(ABORT,'portfolio item snapshots are append-only'); END;
CREATE TRIGGER immutable_item_snapshot_delete BEFORE DELETE ON portfolio_item_snapshot
BEGIN SELECT RAISE(ABORT,'portfolio item snapshots are append-only'); END;
CREATE TRIGGER immutable_portfolio_review_update BEFORE UPDATE ON portfolio_review
BEGIN SELECT RAISE(ABORT,'portfolio reviews are append-only'); END;
CREATE TRIGGER immutable_portfolio_review_delete BEFORE DELETE ON portfolio_review
BEGIN SELECT RAISE(ABORT,'portfolio reviews are append-only'); END;
CREATE TRIGGER immutable_review_gate_update BEFORE UPDATE ON review_gate_evidence
BEGIN SELECT RAISE(ABORT,'review gate evidence is append-only'); END;
CREATE TRIGGER immutable_review_gate_delete BEFORE DELETE ON review_gate_evidence
BEGIN SELECT RAISE(ABORT,'review gate evidence is append-only'); END;
CREATE TRIGGER immutable_decision_update BEFORE UPDATE ON portfolio_decision
BEGIN SELECT RAISE(ABORT,'portfolio decisions are append-only'); END;
CREATE TRIGGER immutable_decision_delete BEFORE DELETE ON portfolio_decision
BEGIN SELECT RAISE(ABORT,'portfolio decisions are append-only'); END;
CREATE TRIGGER immutable_sunset_plan_update BEFORE UPDATE ON sunset_plan
BEGIN SELECT RAISE(ABORT,'sunset plans are append-only'); END;
CREATE TRIGGER immutable_sunset_plan_delete BEFORE DELETE ON sunset_plan
BEGIN SELECT RAISE(ABORT,'sunset plans are append-only'); END;
CREATE TRIGGER immutable_sunset_plan_status_update BEFORE UPDATE ON sunset_plan_status_event
BEGIN SELECT RAISE(ABORT,'sunset plan status events are append-only'); END;
CREATE TRIGGER immutable_sunset_plan_status_delete BEFORE DELETE ON sunset_plan_status_event
BEGIN SELECT RAISE(ABORT,'sunset plan status events are append-only'); END;
CREATE TRIGGER immutable_sunset_liability_update BEFORE UPDATE ON sunset_liability
BEGIN SELECT RAISE(ABORT,'sunset liability events are append-only'); END;
CREATE TRIGGER immutable_sunset_liability_delete BEFORE DELETE ON sunset_liability
BEGIN SELECT RAISE(ABORT,'sunset liability events are append-only'); END;
CREATE TRIGGER immutable_sunset_close_update BEFORE UPDATE ON sunset_close_event
BEGIN SELECT RAISE(ABORT,'sunset close events are append-only'); END;
CREATE TRIGGER immutable_sunset_close_delete BEFORE DELETE ON sunset_close_event
BEGIN SELECT RAISE(ABORT,'sunset close events are append-only'); END;

-- -----------------------------------------------------------------------------
-- Synthetic fixtures
-- -----------------------------------------------------------------------------

INSERT INTO schema_registry VALUES (
  1,'portfolio_sunset','1.0.0','SQLite 3.40+',
  '2026-08-02T00:00:00Z','NO_PERSONAL_DATA_OPAQUE_EVIDENCE_ONLY',
  'USER_SET_GUARDRAILS_NOT_BENCHMARKS'
);

INSERT INTO model_settings VALUES (
  1,'2026-08-02T23:59:00Z','2026-08-02T23:30:00Z','JPY'
);

INSERT INTO allocation_role_dim VALUES
  ('cash_engine','protect current cash contribution'),
  ('validated_core','invest in paid and validated core value'),
  ('new_option','buy bounded learning and a right to expand'),
  ('unfunded','make a visible decision to provide no discretionary funding');

INSERT INTO lifecycle_dim VALUES
  ('explore','reduce problem, demand, and route uncertainty'),
  ('growth','increase investment inside validated scope'),
  ('operate','repeat the promised outcome'),
  ('maintenance','fulfil existing safety, contract, support, and compatibility obligations'),
  ('sunset_planned','fund and sequence an approved exit'),
  ('sunset_active','execute notice, settlement, migration, and closure'),
  ('closed','retain only evidenced residual obligations and records');

INSERT INTO decision_action_dim VALUES
  ('FUND'),('HOLD'),('HARVEST'),('MERGE'),('TRANSFER'),('SELL'),
  ('SUNSET'),('STOP'),('PAUSE'),('UNKNOWN');

INSERT INTO gate_state_dim VALUES
  ('STOP',1),('PAUSE',2),('UNKNOWN',3),('READY_TO_DECIDE',4);

INSERT INTO gate_action_policy(gate_state_code,action_code,action_allowed)
SELECT s.gate_state_code,a.action_code,
       CASE
         WHEN s.gate_state_code='STOP' AND a.action_code='STOP' THEN 1
         WHEN s.gate_state_code='PAUSE'
              AND a.action_code IN ('PAUSE','STOP') THEN 1
         WHEN s.gate_state_code='UNKNOWN'
              AND a.action_code IN ('UNKNOWN','PAUSE','STOP') THEN 1
         WHEN s.gate_state_code='READY_TO_DECIDE'
              AND a.action_code<>'UNKNOWN' THEN 1
         ELSE 0
       END
FROM gate_state_dim s
CROSS JOIN decision_action_dim a;

INSERT INTO gate_domain_dim VALUES
  ('safety_legal','STOP',1),
  ('contract_customer','STOP',1),
  ('cash_capacity','PAUSE',1),
  ('allocation_integrity','PAUSE',1),
  ('option_evidence','PAUSE',1),
  ('dependency_recovery','PAUSE',1),
  ('sunset_funding','STOP',1),
  ('data_exit','STOP',1);

INSERT INTO sunset_liability_domain_dim VALUES
  ('legal',1),('contract',1),('refund_credit',1),('migration_export',1),
  ('data_deletion',1),('record_retention',1),('infrastructure',1),('security',1);

INSERT INTO portfolio_period VALUES (
  'PERIOD-2026-07','2026-07-01T00:00:00Z','2026-08-01T00:00:00Z',
  'Asia/Tokyo',
  12000,600,1200,240,960,1080,7920,
  3000000000000,300000000000,200000000000,200000000000,
  300000000000,200000000000,100000000000,1700000000000,'JPY',
  printf('%064x',1001),printf('%064x',1002),'2026-06-30T10:00:00Z'
);

INSERT INTO waterfall_component_evidence VALUES
  ('WF-T-ADMIN','PERIOD-2026-07','time','common_admin_compliance',600,printf('%064x',1003),'exclusive-time-admin'),
  ('WF-T-CONTRACT','PERIOD-2026-07','time','contracted_delivery_support',1200,printf('%064x',1004),'exclusive-time-contract'),
  ('WF-T-INCIDENT','PERIOD-2026-07','time','known_incident_remediation',240,printf('%064x',1005),'exclusive-time-incident'),
  ('WF-T-RESERVE','PERIOD-2026-07','time','continuity_reserve',960,printf('%064x',1006),'exclusive-time-reserve'),
  ('WF-T-SUNSET','PERIOD-2026-07','time','active_sunset',1080,printf('%064x',1007),'exclusive-time-sunset'),
  ('WF-C-TAX','PERIOD-2026-07','cash','tax_social_internal_restriction',300000000000,printf('%064x',1008),'exclusive-cash-tax'),
  ('WF-C-CUSTOMER','PERIOD-2026-07','cash','customer_obligations',200000000000,printf('%064x',1009),'exclusive-cash-customer'),
  ('WF-C-PAYABLE','PERIOD-2026-07','cash','confirmed_payables_vendor_spend',200000000000,printf('%064x',1011),'exclusive-cash-payables'),
  ('WF-C-MINIMUM','PERIOD-2026-07','cash','absolute_minimum_cash',300000000000,printf('%064x',1012),'exclusive-cash-minimum'),
  ('WF-C-RESERVE','PERIOD-2026-07','cash','continuity_reserve',200000000000,printf('%064x',1013),'exclusive-cash-reserve'),
  ('WF-C-SUNSET','PERIOD-2026-07','cash','open_sunset_funding',100000000000,printf('%064x',1014),'exclusive-cash-sunset');

-- The following bounds are fictional founder choices for this one period.
INSERT INTO allocation_guardrail_set VALUES (
  'GR-2026-07','PERIOD-2026-07','fixture-v1','FOUNDER_SET_NOT_BENCHMARK',
  800,1200,150000000000,250000000000,
  'founder','2026-06-30T11:00:00Z',printf('%064x',1010)
);

INSERT INTO role_allocation_guardrail VALUES
  ('RG-CASH','GR-2026-07','cash_engine',400000,500000,350000,500000,printf('%064x',1021)),
  ('RG-CORE','GR-2026-07','validated_core',250000,350000,250000,350000,printf('%064x',1022)),
  ('RG-OPTION','GR-2026-07','new_option',100000,150000,100000,150000,printf('%064x',1023)),
  ('RG-UNFUNDED','GR-2026-07','unfunded',0,0,0,0,printf('%064x',1024));

INSERT INTO portfolio_item VALUES
  ('ITEM-CASH','product','2025-01-01T00:00:00Z','founder'),
  ('ITEM-CORE','service','2025-06-01T00:00:00Z','founder'),
  ('ITEM-OPTION','experiment','2026-06-01T00:00:00Z','founder'),
  ('ITEM-MAINT','product','2025-01-01T00:00:00Z','founder'),
  ('ITEM-LEGACY','product','2024-01-01T00:00:00Z','founder');

-- cash_engine × maintenance, new_option × operate, and unfunded × sunset_active
-- intentionally demonstrate that the two item-classification axes are independent.
INSERT INTO portfolio_item_snapshot(
  item_snapshot_id,item_id,portfolio_period_id,snapshot_sequence,decision_at,
  supersedes_item_snapshot_id,allocation_role_code,lifecycle_code,closure_event_id,
  role_decision_event_ref_hash,lifecycle_decision_event_ref_hash,
  dependency_applicability,dependency_na_reason,dependency_na_evidence_ref_hash,
  dependency_na_decided_at,dependency_na_valid_until,dependency_na_recheck_trigger,
  dependency_na_recheck_triggered_at,trailing_monthly_cash_contribution_micros,
  active_paying_customers,contracted_future_delivery_micros,recorded_at
) VALUES
  ('SNAP-CASH-OLD','ITEM-CASH','PERIOD-2026-07',1,'2026-06-01T08:00:00Z',
   NULL,'cash_engine','operate',NULL,printf('%064x',1091),printf('%064x',1092),
   'not_applicable','historical snapshot predates dependency inventory',printf('%064x',1093),
   '2026-06-01T08:00:00Z','2026-07-10T00:00:00Z',NULL,NULL,
   800000000000,10,150000000000,'2026-06-01T09:00:00Z'),
  ('SNAP-CASH','ITEM-CASH','PERIOD-2026-07',2,'2026-06-30T11:00:00Z',
   'SNAP-CASH-OLD','cash_engine','operate',NULL,printf('%064x',1101),printf('%064x',1102),
   'required',NULL,NULL,NULL,NULL,NULL,NULL,
   900000000000,12,150000000000,'2026-06-30T12:00:00Z'),
  ('SNAP-CORE','ITEM-CORE','PERIOD-2026-07',1,'2026-06-30T11:01:00Z',
   NULL,'validated_core','growth',NULL,printf('%064x',1111),printf('%064x',1112),
   'required',NULL,NULL,NULL,NULL,NULL,NULL,
   300000000000,4,100000000000,'2026-06-30T12:01:00Z'),
  ('SNAP-OPTION','ITEM-OPTION','PERIOD-2026-07',1,'2026-06-30T11:02:00Z',
   NULL,'new_option','operate',NULL,printf('%064x',1121),printf('%064x',1122),
   'required',NULL,NULL,NULL,NULL,NULL,NULL,
   -100000000000,1,0,'2026-06-30T12:02:00Z'),
  ('SNAP-MAINT','ITEM-MAINT','PERIOD-2026-07',1,'2026-06-30T11:02:30Z',
   NULL,'cash_engine','maintenance',NULL,printf('%064x',1125),printf('%064x',1126),
   'required',NULL,NULL,NULL,NULL,NULL,NULL,
   200000000000,2,50000000000,'2026-06-30T12:02:30Z'),
  ('SNAP-LEGACY','ITEM-LEGACY','PERIOD-2026-07',1,'2026-06-30T11:03:00Z',
   NULL,'unfunded','sunset_active',NULL,printf('%064x',1131),printf('%064x',1132),
   'required',NULL,NULL,NULL,NULL,NULL,NULL,
   100000000000,3,120000000000,'2026-06-30T12:03:00Z');

INSERT INTO portfolio_resource_allocation VALUES
  ('ALLOC-CASH','PERIOD-2026-07','SNAP-CASH','plan',3600,700000000000,printf('%064x',1201),'2026-06-30T13:00:00Z'),
  ('ALLOC-CORE','PERIOD-2026-07','SNAP-CORE','plan',2400,500000000000,printf('%064x',1202),'2026-06-30T13:00:00Z'),
  ('ALLOC-OPTION','PERIOD-2026-07','SNAP-OPTION','plan',960,200000000000,printf('%064x',1203),'2026-06-30T13:00:00Z'),
  ('ALLOC-MAINT','PERIOD-2026-07','SNAP-MAINT','plan',0,0,printf('%064x',1204),'2026-06-30T13:00:00Z'),
  ('ALLOC-LEGACY','PERIOD-2026-07','SNAP-LEGACY','plan',0,0,printf('%064x',1205),'2026-06-30T13:00:00Z');

INSERT INTO failure_domain VALUES
  ('FD-EDGE','cloud_runtime','shared edge account and control plane','founder',120,
   '2026-07-20T10:00:00Z','verified',printf('%064x',1301)),
  ('FD-PAY','payment','single merchant payment rail','founder',240,
   '2026-07-18T10:00:00Z','verified',printf('%064x',1302)),
  ('FD-MODEL','model','single external model API route','founder',180,
   NULL,'unknown',NULL),
  ('FD-LEGACY-ID','identity','legacy application identity control plane','founder',60,
   '2026-07-31T08:00:00Z','failed',printf('%064x',1304));

-- Runtime and DNS are two direct dependencies but share FD-EDGE.
INSERT INTO dependency_snapshot VALUES
  ('DS-EDGE-RUNTIME','EDGE-RUNTIME','EDGECO','platform','FD-EDGE','critical',
   'healthy','verified','2026-07-30T08:00:00Z',14,printf('%064x',1311),120),
  ('DS-EDGE-DNS','EDGE-DNS','EDGECO','domain','FD-EDGE','critical',
   'healthy','verified','2026-07-30T08:05:00Z',14,printf('%064x',1312),90),
  ('DS-PAY','PAYMENT-RAIL','PAYCO','payment','FD-PAY','high',
   'healthy','documented','2026-07-29T08:00:00Z',14,printf('%064x',1313),240),
  ('DS-MODEL','MODEL-API','MODELCO','model','FD-MODEL','critical',
   'unknown','unknown',NULL,7,NULL,180),
  ('DS-LEGACY-ID','LEGACY-IDP','LEGACYCO','identity','FD-LEGACY-ID','critical',
   'failed','none','2026-07-31T08:00:00Z',3,printf('%064x',1315),60);

INSERT INTO item_dependency VALUES
  ('ID-CASH-RUNTIME','SNAP-CASH','DS-EDGE-RUNTIME','serve_http','operational',printf('%064x',1401)),
  ('ID-CASH-DNS','SNAP-CASH','DS-EDGE-DNS','resolve_domain','public',printf('%064x',1402)),
  ('ID-CASH-PAY','SNAP-CASH','DS-PAY','collect_payment','billing',printf('%064x',1403)),
  ('ID-CORE-RUNTIME','SNAP-CORE','DS-EDGE-RUNTIME','deliver_output','operational',printf('%064x',1404)),
  ('ID-OPTION-MODEL','SNAP-OPTION','DS-MODEL','generate_draft','restricted',printf('%064x',1405)),
  ('ID-LEGACY-ID','SNAP-LEGACY','DS-LEGACY-ID','authenticate_customer','confidential',printf('%064x',1406)),
  ('ID-MAINT-RUNTIME','SNAP-MAINT','DS-EDGE-RUNTIME','serve_existing_customer','operational',printf('%064x',1407));

INSERT INTO sunset_plan VALUES (
  'SUNSET-LEGACY','SNAP-LEGACY','2026-07-31T15:00:00Z',
  printf('%064x',1501),'2026-07-01T09:00:00Z'
);

INSERT INTO sunset_plan_status_event VALUES
  ('SPS-LEGACY-1','SUNSET-LEGACY',1,'proposed',0,0,
   printf('%064x',1502),'2026-07-01T09:05:00Z','2026-07-01T09:06:00Z',NULL),
  ('SPS-LEGACY-2','SUNSET-LEGACY',2,'executing',100000000000,1080,
   printf('%064x',1503),'2026-07-01T10:00:00Z','2026-07-01T10:01:00Z','SPS-LEGACY-1');

-- All eight required liability domains are represented. One active critical
-- refund breach intentionally forces STOP even though another row is UNKNOWN.
INSERT INTO sunset_liability(
  sunset_liability_id,liability_stream_id,sunset_plan_id,event_sequence,
  supersedes_liability_id,liability_domain_code,obligation_ref_hash,
  liability_status,severity,breach_state,owner_role,due_at,completed_at,
  estimated_cash_micros,estimated_founder_minutes,completion_evidence_ref_hash,
  na_reason,na_decided_at,na_valid_until,na_recheck_trigger,
  na_recheck_triggered_at,legal_basis_ref_hash,retain_until,occurred_at,recorded_at
) VALUES
  ('SL-LEGAL','SLS-LEGAL','SUNSET-LEGACY',1,NULL,'legal',printf('%064x',1511),
   'complete','high','no','legal_reviewer','2026-07-10T00:00:00Z','2026-07-09T08:00:00Z',0,60,
   printf('%064x',1521),NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-09T08:00:00Z','2026-07-09T08:01:00Z'),
  ('SL-CONTRACT','SLS-CONTRACT','SUNSET-LEGACY',1,NULL,'contract',printf('%064x',1512),
   'complete','high','no','founder','2026-07-15T00:00:00Z','2026-07-14T08:00:00Z',0,90,
   printf('%064x',1522),NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-14T08:00:00Z','2026-07-14T08:01:00Z'),
  ('SL-REFUND','SLS-REFUND','SUNSET-LEGACY',1,NULL,'refund_credit',printf('%064x',1513),
   'blocked','critical','yes','founder','2026-07-25T00:00:00Z',NULL,120000000000,120,
   NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-25T01:00:00Z','2026-07-25T01:01:00Z'),
  ('SL-MIGRATION','SLS-MIGRATION','SUNSET-LEGACY',1,NULL,'migration_export',printf('%064x',1514),
   'open','high','no','founder','2026-08-05T00:00:00Z',NULL,10000000000,180,
   NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-20T01:00:00Z','2026-07-20T01:01:00Z'),
  ('SL-DATA','SLS-DATA','SUNSET-LEGACY',1,NULL,'data_deletion',printf('%064x',1515),
   'unknown','high','unknown','privacy_owner',NULL,NULL,NULL,NULL,
   NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-20T01:01:00Z','2026-07-20T01:02:00Z'),
  ('SL-RECORD','SLS-RECORD','SUNSET-LEGACY',1,NULL,'record_retention',printf('%064x',1516),
   'open','medium','no','records_owner','2033-07-31T00:00:00Z',NULL,0,30,
   NULL,NULL,NULL,NULL,NULL,NULL,printf('%064x',1536),'2033-07-31T00:00:00Z',
   '2026-07-20T01:02:00Z','2026-07-20T01:03:00Z'),
  ('SL-INFRA','SLS-INFRA','SUNSET-LEGACY',1,NULL,'infrastructure',printf('%064x',1517),
   'open','medium','no','founder','2026-08-10T00:00:00Z',NULL,20000000000,60,
   NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-20T01:03:00Z','2026-07-20T01:04:00Z'),
  ('SL-SECURITY','SLS-SECURITY','SUNSET-LEGACY',1,NULL,'security',printf('%064x',1518),
   'open','critical','no','security_owner','2026-08-10T00:00:00Z',NULL,5000000000,60,
   NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-20T01:04:00Z','2026-07-20T01:05:00Z'),
  ('SL-LEGAL-2','SLS-LEGAL','SUNSET-LEGACY',2,'SL-LEGAL','legal',printf('%064x',1511),
   'complete','high','no','legal_reviewer','2026-07-10T00:00:00Z','2026-07-10T08:00:00Z',0,60,
   printf('%064x',1523),NULL,NULL,NULL,NULL,NULL,NULL,NULL,
   '2026-07-10T08:00:00Z','2026-07-10T08:01:00Z'),
  ('SL-LEGAL-NA','SLS-LEGAL-NA','SUNSET-LEGACY',1,NULL,'legal',printf('%064x',1519),
   'not_applicable','low','no','legal_reviewer',NULL,NULL,0,0,
   printf('%064x',1524),'scope excluded by fixture decision','2026-07-01T08:00:00Z',
   '2026-07-10T00:00:00Z',NULL,NULL,NULL,NULL,
   '2026-07-01T08:00:00Z','2026-07-01T08:01:00Z');

INSERT INTO portfolio_review(
  portfolio_review_id,portfolio_period_id,item_snapshot_id,review_sequence,
  supersedes_review_id,gate_policy_version,evidence_as_of,data_watermark_at,
  occurred_at,recorded_at,review_owner_role
) VALUES
  ('REV-CASH-OLD','PERIOD-2026-07','SNAP-CASH-OLD',1,NULL,'fixture-gates-v1',
   '2026-07-15T23:00:00Z','2026-07-15T22:30:00Z','2026-07-16T09:00:00Z','2026-07-16T09:05:00Z','founder'),
  ('REV-CASH','PERIOD-2026-07','SNAP-CASH',1,NULL,'fixture-gates-v1',
   '2026-07-31T23:00:00Z','2026-07-31T22:30:00Z','2026-08-01T09:00:00Z','2026-08-01T09:05:00Z','founder'),
  ('REV-CORE','PERIOD-2026-07','SNAP-CORE',1,NULL,'fixture-gates-v1',
   '2026-07-31T23:00:00Z','2026-07-31T22:30:00Z','2026-08-01T09:01:00Z','2026-08-01T09:06:00Z','founder'),
  ('REV-OPTION','PERIOD-2026-07','SNAP-OPTION',1,NULL,'fixture-gates-v1',
   '2026-07-31T23:00:00Z','2026-07-31T22:30:00Z','2026-08-01T09:02:00Z','2026-08-01T09:07:00Z','founder'),
  ('REV-LEGACY','PERIOD-2026-07','SNAP-LEGACY',1,NULL,'fixture-gates-v1',
   '2026-07-31T23:00:00Z','2026-07-31T22:30:00Z','2026-08-01T09:03:00Z','2026-08-01T09:08:00Z','founder'),
  ('REV-MAINT','PERIOD-2026-07','SNAP-MAINT',1,NULL,'fixture-gates-v1',
   '2026-07-31T23:00:00Z','2026-07-31T22:30:00Z','2026-08-01T09:04:00Z','2026-08-01T09:09:00Z','founder');

WITH classified AS (
  SELECT r.portfolio_review_id,g.gate_domain_code,
         row_number() OVER (
           ORDER BY r.portfolio_review_id,g.gate_domain_code
         ) AS evidence_ordinal,
         CASE
           WHEN r.portfolio_review_id='REV-CORE'
                AND g.gate_domain_code='cash_capacity' THEN 'FAIL'
           WHEN r.portfolio_review_id='REV-OPTION'
                AND g.gate_domain_code='dependency_recovery' THEN 'UNKNOWN'
           WHEN r.portfolio_review_id='REV-LEGACY'
                AND g.gate_domain_code IN ('sunset_funding','data_exit') THEN 'FAIL'
           WHEN r.portfolio_review_id='REV-CASH-OLD'
                AND g.gate_domain_code='dependency_recovery' THEN 'NOT_APPLICABLE'
           WHEN g.gate_domain_code='option_evidence'
                AND r.portfolio_review_id<>'REV-OPTION' THEN 'NOT_APPLICABLE'
         WHEN g.gate_domain_code IN ('sunset_funding','data_exit')
                AND r.portfolio_review_id<>'REV-LEGACY' THEN 'NOT_APPLICABLE'
           ELSE 'PASS'
         END AS result_code
  FROM portfolio_review r
  CROSS JOIN gate_domain_dim g
)
INSERT INTO review_gate_evidence(
  review_gate_evidence_id,portfolio_review_id,gate_domain_code,result_code,
  evidence_ref_hash,confirmed_at,valid_until,reason_code,
  na_decided_at,na_recheck_trigger,na_recheck_triggered_at
)
SELECT 'GE-'||portfolio_review_id||'-'||gate_domain_code,
       portfolio_review_id,gate_domain_code,result_code,
       CASE WHEN result_code='UNKNOWN' THEN NULL
            ELSE printf('%064x',1600+evidence_ordinal) END,
       CASE WHEN result_code='UNKNOWN' THEN NULL
            WHEN portfolio_review_id='REV-CASH-OLD' THEN '2026-07-01T12:00:00Z'
            ELSE '2026-07-31T12:00:00Z' END,
       CASE WHEN result_code IN ('PASS','FAIL') THEN '2026-08-31T12:00:00Z'
            WHEN result_code='NOT_APPLICABLE'
                 AND portfolio_review_id='REV-CASH-OLD'
            THEN '2026-07-10T12:00:00Z'
            WHEN result_code='NOT_APPLICABLE' THEN '2026-08-31T12:00:00Z'
            ELSE NULL END,
       CASE WHEN result_code='UNKNOWN' THEN 'MISSING_CURRENT_PROVIDER_EVIDENCE'
            WHEN result_code='NOT_APPLICABLE' THEN 'NOT_APPLICABLE_FOR_CURRENT_SCOPE'
            ELSE NULL END,
       CASE WHEN result_code='NOT_APPLICABLE'
                 AND portfolio_review_id='REV-CASH-OLD'
            THEN '2026-07-01T12:00:00Z'
            WHEN result_code='NOT_APPLICABLE' THEN '2026-07-31T12:00:00Z'
            ELSE NULL END,
       CASE WHEN result_code='NOT_APPLICABLE'
                 AND portfolio_review_id<>'REV-CASH-OLD'
            THEN 'SCOPE_OR_LIFECYCLE_CHANGE' ELSE NULL END,
       CASE WHEN result_code='NOT_APPLICABLE'
                  AND gate_domain_code='option_evidence'
                  AND portfolio_review_id IN ('REV-LEGACY','REV-MAINT')
            THEN '2026-07-31T23:30:00Z' ELSE NULL END
FROM classified;

-- The decision trigger consults the derived state and rejects unsafe actions.
INSERT INTO portfolio_decision VALUES
  ('DEC-CASH','REV-CASH','HOLD','cash-item-current-period',
   '2026-08-01T09:30:00Z','2026-09-01T00:00:00Z','founder',printf('%064x',1701),'2026-08-01T10:00:00Z'),
  ('DEC-CORE','REV-CORE','PAUSE','core-new-commitments',
   '2026-08-01T09:31:00Z','2026-09-01T00:00:00Z','founder',printf('%064x',1702),'2026-08-01T10:00:00Z'),
  ('DEC-OPTION','REV-OPTION','UNKNOWN','option-provider-exposure',
   '2026-08-01T09:32:00Z','2026-09-01T00:00:00Z','founder',printf('%064x',1703),'2026-08-01T10:00:00Z'),
  ('DEC-LEGACY','REV-LEGACY','STOP','legacy-auth-and-new-exposure',
   '2026-08-01T09:33:00Z','2026-09-01T00:00:00Z','founder',printf('%064x',1704),'2026-08-01T10:00:00Z'),
  ('DEC-MAINT','REV-MAINT','UNKNOWN','maintenance-obligation',
   '2026-08-01T09:34:00Z','2026-09-01T00:00:00Z','founder',printf('%064x',1705),'2026-08-01T10:00:00Z');

-- -----------------------------------------------------------------------------
-- Assertions
-- -----------------------------------------------------------------------------

CREATE TEMP VIEW v_assertion_results AS
SELECT 'DQ01' AS check_id,'error' AS severity,'allocation_role_vocabulary' AS check_name,
       CASE WHEN (SELECT count(*) FROM allocation_role_dim)=4 THEN 0 ELSE 1 END
         AS failure_count,
       'Exactly four chapter-authoritative allocation roles exist' AS expectation
UNION ALL SELECT 'DQ02','error','lifecycle_vocabulary',
       CASE WHEN (SELECT count(*) FROM lifecycle_dim)=7 THEN 0 ELSE 1 END,
       'Lifecycle is a separate seven-value axis including maintenance and closed'
UNION ALL SELECT 'DQ03','error','role_lifecycle_axes_not_mapped',
       (SELECT count(*) FROM sqlite_schema
        WHERE type='table' AND lower(name) LIKE '%role%lifecycle%'),
       'No table infers lifecycle from allocation role'
UNION ALL SELECT 'DQ04','error','independent_axis_fixtures',
       CASE WHEN EXISTS (
              SELECT 1 FROM portfolio_item_snapshot
              WHERE allocation_role_code='cash_engine' AND lifecycle_code='maintenance'
            ) AND EXISTS (
              SELECT 1 FROM portfolio_item_snapshot
              WHERE allocation_role_code='new_option' AND lifecycle_code='operate'
            ) AND EXISTS (
              SELECT 1 FROM portfolio_item_snapshot
              WHERE allocation_role_code='unfunded' AND lifecycle_code='sunset_active'
            ) THEN 0 ELSE 1 END,
       'Fixtures demonstrate combinations without cross-axis inference'
UNION ALL SELECT 'DQ05','error','guardrails_are_user_set',
       (SELECT count(*) FROM allocation_guardrail_set
        WHERE source_code<>'FOUNDER_SET_NOT_BENCHMARK'),
       'Every allocation boundary declares founder-set, non-benchmark provenance'
UNION ALL SELECT 'DQ06','error','four_guardrail_rows_complete',
       CASE WHEN (SELECT count(*) FROM role_allocation_guardrail)=4
              AND (SELECT count(DISTINCT allocation_role_code)
                   FROM role_allocation_guardrail)=4
            THEN 0 ELSE 1 END,
       'The fixture period has an explicit guardrail for every allocation role'
UNION ALL SELECT 'DQ07','error','allocation_integrity_fixture',
       (SELECT CASE WHEN allocation_gate_state='READY_TO_DECIDE'
                          AND planned_minutes=6960
                          AND discretionary_founder_minutes=7920
                          AND unallocated_founder_minutes=960
                          AND planned_cash_micros=1400000000000
                          AND discretionary_cash_micros=1700000000000
                          AND unallocated_cash_micros=300000000000
                          AND time_guardrail_breaches=0
                          AND cash_guardrail_breaches=0
                    THEN 0 ELSE 1 END
        FROM v_period_allocation_integrity
        WHERE portfolio_period_id='PERIOD-2026-07'),
       'Planned allocations stay below discretionary capacity and leave unallocated visible'
UNION ALL SELECT 'DQ08','error','waterfall_and_reserve_are_explicit',
       CASE WHEN EXISTS (
         SELECT 1 FROM portfolio_period
         WHERE continuity_reserve_minutes=960
           AND continuity_reserve_cash_micros=200000000000
       ) AND NOT EXISTS (
         SELECT 1 FROM portfolio_item WHERE item_type='reserve'
       ) AND EXISTS (
         SELECT 1 FROM v_waterfall_evidence_reconciliation
         WHERE component_count=11 AND missing_component_failure=0
           AND amount_mismatch_count=0
       ) THEN 0 ELSE 1 END,
       'Reserve is a reconciled waterfall deduction, never an item or allocation role'
UNION ALL SELECT 'DQ09','error','failure_domain_item_deduplication',
       (SELECT CASE WHEN distinct_exposed_items=3
                          AND direct_dependency_links=4
                          AND contribution_exposure_micros=1400000000000
                    THEN 0 ELSE 1 END
        FROM v_failure_domain_exposure
        WHERE portfolio_period_id='PERIOD-2026-07'
          AND failure_domain_id='FD-EDGE'),
       'Four direct links across three items count each item-domain exposure once'
UNION ALL SELECT 'DQ10','error','dependency_probability_not_invented',
       (SELECT count(*) FROM pragma_table_info('failure_domain')
        WHERE lower(name) LIKE '%probab%')
       +(SELECT count(*) FROM pragma_table_info('dependency_snapshot')
        WHERE lower(name) LIKE '%probab%'),
       'The model records exposure and recovery evidence, not invented probabilities'
UNION ALL SELECT 'DQ11','error','dependency_state_fixtures',
       (WITH expected(item_snapshot_id,expected_state) AS (VALUES
          ('SNAP-CASH','READY_TO_DECIDE'),('SNAP-CORE','READY_TO_DECIDE'),
          ('SNAP-OPTION','UNKNOWN'),('SNAP-LEGACY','STOP'),
          ('SNAP-MAINT','READY_TO_DECIDE')
        )
        SELECT count(*) FROM expected e
        LEFT JOIN v_item_dependency_readiness a USING (item_snapshot_id)
        WHERE a.dependency_gate_state IS NOT e.expected_state),
       'Healthy, unknown, failed, and not-applicable dependency states fail closed'
UNION ALL SELECT 'DQ12','error','sunset_domain_coverage',
       (SELECT CASE WHEN represented_domains=8 AND missing_domain_count=0
                    THEN 0 ELSE 1 END
        FROM v_sunset_readiness WHERE sunset_plan_id='SUNSET-LEGACY'),
       'Legal, contract, refund, migration, data, records, infra, and security are present'
UNION ALL SELECT 'DQ13','error','sunset_stop_precedence',
       (SELECT CASE WHEN active_critical_breach_count=1
                          AND unknown_status_count=1
                          AND sunset_gate_state='STOP'
                    THEN 0 ELSE 1 END
        FROM v_sunset_readiness WHERE sunset_plan_id='SUNSET-LEGACY'),
       'An active critical breach stays STOP even when another liability is UNKNOWN'
UNION ALL SELECT 'DQ14','error','sunset_funding_gap_visible',
       (SELECT CASE WHEN unfunded_cash_micros>0 THEN 0 ELSE 1 END
        FROM v_sunset_readiness WHERE sunset_plan_id='SUNSET-LEGACY'),
       'Known future settlement work is compared with funded cash and calendar capacity'
UNION ALL SELECT 'DQ15','error','record_retention_basis_and_end',
       (SELECT count(*) FROM sunset_liability
        WHERE liability_domain_code='record_retention'
          AND (legal_basis_ref_hash IS NULL OR retain_until IS NULL)),
       'Retained records have an explicit basis and review/end date'
UNION ALL SELECT 'DQ16','error','all_review_gates_materialized',
       CASE WHEN (SELECT count(*) FROM portfolio_review)=6
              AND (SELECT count(*) FROM review_gate_evidence)=48
              AND NOT EXISTS (
                SELECT 1 FROM v_review_gate_summary WHERE expected_gate_count<>8
              ) THEN 0 ELSE 1 END,
       'Each item review evaluates every required gate domain'
UNION ALL SELECT 'DQ17','error','portfolio_review_state_fixtures',
        (WITH expected(portfolio_review_id,expected_state) AS (VALUES
          ('REV-CASH','READY_TO_DECIDE'),('REV-CORE','PAUSE'),
          ('REV-OPTION','UNKNOWN'),('REV-LEGACY','STOP'),
          ('REV-MAINT','UNKNOWN')
        )
        SELECT count(*) FROM expected e
        LEFT JOIN v_portfolio_review_readiness a USING (portfolio_review_id)
        WHERE a.derived_gate_state IS NOT e.expected_state),
       'STOP, PAUSE, UNKNOWN, and READY fixtures derive with veto priority'
UNION ALL SELECT 'DQ18','error','fail_closed_action_policy_cardinality',
       (WITH expected(gate_state_code,allowed_count) AS (VALUES
          ('STOP',1),('PAUSE',2),('UNKNOWN',3),('READY_TO_DECIDE',9)
        ), actual AS (
          SELECT gate_state_code,sum(action_allowed) AS allowed_count
          FROM gate_action_policy GROUP BY gate_state_code
        )
        SELECT count(*) FROM expected e LEFT JOIN actual a USING (gate_state_code)
        WHERE a.allowed_count IS NOT e.allowed_count),
       'STOP permits STOP; UNKNOWN records UNKNOWN or chooses PAUSE/STOP without exposure'
UNION ALL SELECT 'DQ19','error','unsafe_fail_closed_action_absent',
       (SELECT count(*) FROM gate_action_policy
        WHERE action_allowed=1
          AND ((gate_state_code='STOP' AND action_code<>'STOP')
            OR (gate_state_code='PAUSE'
                AND action_code NOT IN ('PAUSE','STOP'))
            OR (gate_state_code='UNKNOWN'
                AND action_code NOT IN ('UNKNOWN','PAUSE','STOP')))),
       'Funding, transfer, sale, harvest, and sunset cannot bypass a closed gate'
UNION ALL SELECT 'DQ20','error','recorded_decisions_compatible',
       (SELECT count(*) FROM v_recorded_decision_compatibility
        WHERE recorded_decision_compatible<>1),
       'Every fixture decision is compatible with its current-as-of derived state'
UNION ALL SELECT 'DQ21','error','decision_trigger_present',
       CASE WHEN EXISTS (
         SELECT 1 FROM sqlite_schema
         WHERE type='trigger' AND name='validate_portfolio_decision_before_insert'
           AND instr(sql,'action incompatible with fail-closed gate state')>0
       ) THEN 0 ELSE 1 END,
       'Unsafe recorded actions are rejected at write time'
UNION ALL SELECT 'DQ22','error','sunset_lifecycle_write_guard_present',
       CASE WHEN EXISTS (
         SELECT 1 FROM sqlite_schema
         WHERE type='trigger' AND name='validate_sunset_plan_lifecycle_before_insert'
       ) THEN 0 ELSE 1 END,
       'A sunset plan cannot attach to an unrelated lifecycle snapshot'
UNION ALL SELECT 'DQ23','error','gate_evidence_cutoff_integrity',
       (SELECT count(*) FROM review_gate_evidence e
        JOIN portfolio_review r USING (portfolio_review_id)
        WHERE e.confirmed_at IS NOT NULL
          AND datetime(e.confirmed_at)>datetime(r.evidence_as_of)),
       'Review evidence never arrives after its frozen cutoff'
UNION ALL SELECT 'DQ24','error','foreign_key_integrity',
       (SELECT count(*) FROM pragma_foreign_key_check),
       'Every cross-axis, period, dependency, liability, and decision reference resolves'
UNION ALL SELECT 'DQ25','error','null_assertion_normalization_failure',
       (WITH probe(failure_count) AS (VALUES(NULL),(0),(2))
        SELECT CASE WHEN sum(
          CASE WHEN failure_count IS NULL OR failure_count<>0 THEN 1 ELSE 0 END
        )=2 THEN 0 ELSE 1 END FROM probe),
       'NULL and nonzero assertion results both fail the final guard'
UNION ALL SELECT 'DQ26','error','append_only_decision_guards',
       CASE WHEN (SELECT count(*) FROM sqlite_schema
                  WHERE type='trigger' AND name IN (
                    'immutable_item_snapshot_update','immutable_item_snapshot_delete',
                    'immutable_review_gate_update','immutable_review_gate_delete',
                    'immutable_decision_update','immutable_decision_delete'
                  ))=6 THEN 0 ELSE 1 END,
       'Snapshots, gate evidence, and decisions reject retroactive mutation'
UNION ALL SELECT 'DQ27','error','waterfall_source_event_deduplication',
       CASE WHEN (SELECT count(*) FROM waterfall_component_evidence)=11
              AND (SELECT count(DISTINCT source_event_ref_hash)
                   FROM waterfall_component_evidence)=11
              AND EXISTS (
                SELECT 1 FROM sqlite_schema
                WHERE type='table' AND name='waterfall_component_evidence'
                  AND instr(lower(sql),'source_event_ref_hash text not null unique')>0
              ) THEN 0 ELSE 1 END,
       'A time or cash source event cannot be counted in two waterfall deductions'
UNION ALL SELECT 'DQ28','error','unfunded_is_not_budget_bucket',
       CASE WHEN NOT EXISTS (
              SELECT 1 FROM portfolio_resource_allocation a
              JOIN portfolio_item_snapshot s
                ON s.portfolio_period_id=a.portfolio_period_id
               AND s.item_snapshot_id=a.item_snapshot_id
              WHERE s.allocation_role_code='unfunded'
                AND (a.founder_minutes<>0 OR a.cash_micros<>0)
            ) AND EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='trigger'
                AND name='validate_unfunded_allocation_before_insert'
            ) THEN 0 ELSE 1 END,
       'Unfunded is an item role with an explicit zero plan, never a spend bucket'
UNION ALL SELECT 'DQ29','error','item_snapshot_history_and_latest_selection',
       CASE WHEN (SELECT count(*) FROM portfolio_item_snapshot
                  WHERE item_id='ITEM-CASH' AND portfolio_period_id='PERIOD-2026-07')=2
              AND EXISTS (
                SELECT 1 FROM v_latest_item_snapshot
                WHERE item_id='ITEM-CASH' AND portfolio_period_id='PERIOD-2026-07'
                  AND item_snapshot_id='SNAP-CASH' AND snapshot_sequence=2
              ) AND (SELECT count(*) FROM v_latest_item_snapshot
                     WHERE item_id='ITEM-CASH'
                       AND portfolio_period_id='PERIOD-2026-07')=1
            THEN 0 ELSE 1 END,
       'Multiple immutable snapshots coexist and latest selection is deterministic'
UNION ALL SELECT 'DQ30','error','review_history_and_expired_na',
       CASE WHEN (SELECT count(*) FROM portfolio_review r
                  JOIN portfolio_item_snapshot s USING (item_snapshot_id)
                  WHERE s.item_id='ITEM-CASH'
                    AND r.portfolio_period_id='PERIOD-2026-07')=2
              AND EXISTS (
                SELECT 1 FROM v_latest_portfolio_review
                WHERE item_id='ITEM-CASH' AND portfolio_period_id='PERIOD-2026-07'
                  AND portfolio_review_id='REV-CASH'
              ) AND EXISTS (
                SELECT 1 FROM v_review_gate_summary
                WHERE portfolio_review_id='REV-CASH-OLD' AND unknown_gate_count>0
              )
            THEN 0 ELSE 1 END,
       'Historical reviews remain queryable and expired N/A becomes UNKNOWN'
UNION ALL SELECT 'DQ31','error','snapshot_review_sequence_guards',
       CASE WHEN (SELECT count(*) FROM sqlite_schema
                  WHERE type='trigger' AND name IN (
                    'validate_item_snapshot_sequence_before_insert',
                    'validate_portfolio_review_sequence_before_insert'
                  ))=2 THEN 0 ELSE 1 END,
       'Snapshot and review predecessor chronology is enforced at write time'
UNION ALL SELECT 'DQ32','error','append_only_plan_status_current',
       (SELECT CASE WHEN sunset_plan_status_event_id='SPS-LEGACY-2'
                          AND event_sequence=2 AND plan_status='executing'
                    THEN 0 ELSE 1 END
        FROM v_current_sunset_plan_status_event
        WHERE sunset_plan_id='SUNSET-LEGACY'),
       'Latest plan state derives from immutable monotone status events'
UNION ALL SELECT 'DQ33','error','append_only_liability_current',
       CASE WHEN EXISTS (
              SELECT 1 FROM v_current_sunset_liability
              WHERE sunset_plan_id='SUNSET-LEGACY'
                AND liability_stream_id='SLS-LEGAL'
                AND sunset_liability_id='SL-LEGAL-2' AND event_sequence=2
            ) AND (SELECT count(*) FROM v_current_sunset_liability
                   WHERE sunset_plan_id='SUNSET-LEGACY')=9
            THEN 0 ELSE 1 END,
       'Latest liability status derives once per immutable obligation stream'
UNION ALL SELECT 'DQ34','error','sunset_and_review_immutability_guards',
       CASE WHEN (SELECT count(*) FROM sqlite_schema
                  WHERE type='trigger' AND name IN (
                    'immutable_portfolio_review_update','immutable_portfolio_review_delete',
                    'immutable_sunset_plan_update','immutable_sunset_plan_delete',
                    'immutable_sunset_plan_status_update','immutable_sunset_plan_status_delete',
                    'immutable_sunset_liability_update','immutable_sunset_liability_delete',
                    'immutable_sunset_close_update','immutable_sunset_close_delete'
                  ))=10 THEN 0 ELSE 1 END,
       'Reviews, plan identity, status, liabilities, and closure reject UPDATE/DELETE'
UNION ALL SELECT 'DQ35','error','closed_removed_from_plan_status',
       CASE WHEN EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='table' AND name='sunset_plan_status_event'
                AND instr(lower(sql),'''closed''')=0
            ) AND (SELECT count(*) FROM sunset_close_event)=0
            THEN 0 ELSE 1 END,
       'Closed is not a mutable plan status and requires a separate close event'
UNION ALL SELECT 'DQ36','error','unsafe_close_fixture_rejected_by_model',
       CASE WHEN EXISTS (
              SELECT 1 FROM v_sunset_close_readiness
              WHERE sunset_plan_id='SUNSET-LEGACY' AND closure_ready=0
            ) AND EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='trigger' AND name='validate_sunset_close_event_before_insert'
                AND instr(sql,'sunset closure gates not ready')>0
            ) THEN 0 ELSE 1 END,
       'The unresolved legacy plan is an explicit negative close fixture'
UNION ALL SELECT 'DQ37','error','closed_lifecycle_requires_close_event',
       CASE WHEN EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='trigger' AND name='validate_closed_lifecycle_before_insert'
                AND instr(sql,'closed lifecycle requires validated prior close event')>0
            ) AND NOT EXISTS (
              SELECT 1 FROM portfolio_item_snapshot WHERE lifecycle_code='closed'
            ) THEN 0 ELSE 1 END,
       'A lifecycle snapshot cannot claim closed without a validated prior close event'
UNION ALL SELECT 'DQ38','error','na_evidence_contract_and_staleness',
       CASE WHEN NOT EXISTS (
              SELECT 1 FROM review_gate_evidence
              WHERE result_code='NOT_APPLICABLE'
                AND (reason_code IS NULL OR na_decided_at IS NULL
                     OR evidence_ref_hash IS NULL
                     OR (valid_until IS NULL AND na_recheck_trigger IS NULL))
            ) AND NOT EXISTS (
              SELECT 1 FROM portfolio_item_snapshot
              WHERE dependency_applicability='not_applicable'
                AND (dependency_na_reason IS NULL OR dependency_na_decided_at IS NULL
                     OR dependency_na_evidence_ref_hash IS NULL
                     OR (dependency_na_valid_until IS NULL
                         AND dependency_na_recheck_trigger IS NULL))
            ) AND NOT EXISTS (
              SELECT 1 FROM sunset_liability
              WHERE liability_status='not_applicable'
                AND (na_reason IS NULL OR na_decided_at IS NULL
                     OR completion_evidence_ref_hash IS NULL
                     OR (na_valid_until IS NULL AND na_recheck_trigger IS NULL))
            ) AND EXISTS (
              SELECT 1 FROM v_sunset_readiness
              WHERE sunset_plan_id='SUNSET-LEGACY' AND stale_na_count=1
            ) THEN 0 ELSE 1 END,
       'Every N/A has decision evidence and expiry/recheck; expired N/A is UNKNOWN'
UNION ALL SELECT 'DQ39','error','closure_exact_snapshot_binding',
       CASE WHEN NOT EXISTS (
              SELECT 1
              FROM v_sunset_close_readiness c
              JOIN sunset_plan p USING (sunset_plan_id)
              LEFT JOIN v_latest_review_by_snapshot r
                ON r.item_snapshot_id=p.item_snapshot_id
              WHERE c.item_snapshot_id IS NOT p.item_snapshot_id
                 OR c.latest_portfolio_review_id IS NOT r.portfolio_review_id
            ) AND EXISTS (
              SELECT 1
              FROM v_latest_review_by_snapshot exact_review
              JOIN portfolio_item_snapshot s
                ON s.item_snapshot_id=exact_review.item_snapshot_id
              JOIN v_latest_portfolio_review item_period_review
                ON item_period_review.item_id=s.item_id
               AND item_period_review.portfolio_period_id=s.portfolio_period_id
              WHERE exact_review.item_snapshot_id='SNAP-CASH-OLD'
                AND exact_review.portfolio_review_id='REV-CASH-OLD'
                AND item_period_review.portfolio_review_id='REV-CASH'
                AND exact_review.portfolio_review_id
                    <>item_period_review.portfolio_review_id
            ) THEN 0 ELSE 1 END,
       'Closure binds to the latest review of its exact immutable snapshot'
UNION ALL SELECT 'DQ40','error','current_na_recheck_changes_decision_state',
       CASE WHEN EXISTS (
              SELECT 1
              FROM review_gate_evidence e
              JOIN portfolio_review r USING (portfolio_review_id)
              CROSS JOIN model_settings m
              WHERE e.review_gate_evidence_id='GE-REV-MAINT-option_evidence'
                AND e.result_code='NOT_APPLICABLE'
                AND datetime(e.valid_until)>datetime(m.event_time_as_of)
                AND datetime(e.na_recheck_triggered_at)>datetime(r.evidence_as_of)
                AND datetime(e.na_recheck_triggered_at)<=datetime(m.event_time_as_of)
            ) AND EXISTS (
              SELECT 1 FROM v_review_gate_summary
              WHERE portfolio_review_id='REV-MAINT' AND unknown_gate_count=0
            ) AND EXISTS (
              SELECT 1 FROM v_current_review_gate_summary g
              CROSS JOIN model_settings m
              WHERE g.portfolio_review_id='REV-MAINT'
                AND g.unknown_gate_count=1
                AND datetime(g.freshness_as_of)=datetime(m.event_time_as_of)
            ) AND EXISTS (
              SELECT 1 FROM v_portfolio_review_readiness
              WHERE portfolio_review_id='REV-MAINT'
                AND derived_gate_state='UNKNOWN'
            ) THEN 0 ELSE 1 END,
       'A future-valid N/A rechecked after review becomes UNKNOWN for a current decision'
UNION ALL SELECT 'DQ41','error','closure_uses_current_gate_freshness',
       CASE WHEN EXISTS (
              SELECT 1 FROM v_review_gate_summary
              WHERE portfolio_review_id='REV-LEGACY' AND unknown_gate_count=0
            ) AND EXISTS (
              SELECT 1 FROM v_current_review_gate_summary
              WHERE portfolio_review_id='REV-LEGACY' AND unknown_gate_count=1
            ) AND EXISTS (
              SELECT 1 FROM v_sunset_close_readiness c
              CROSS JOIN model_settings m
              WHERE c.sunset_plan_id='SUNSET-LEGACY'
                AND c.latest_portfolio_review_id='REV-LEGACY'
                AND c.unknown_gate_count=1
                AND datetime(c.gate_freshness_as_of)=datetime(m.event_time_as_of)
                AND c.closure_ready=0
            ) THEN 0 ELSE 1 END,
       'Closure consumes current-as-of gate freshness, not the frozen review result'
UNION ALL SELECT 'DQ42','error','closure_review_event_cutoff',
       CASE WHEN EXISTS (
              SELECT 1
              FROM v_sunset_close_readiness c
              JOIN sunset_plan p USING (sunset_plan_id)
              JOIN portfolio_review r
                ON r.portfolio_review_id=c.latest_portfolio_review_id
               AND r.item_snapshot_id=p.item_snapshot_id
              WHERE c.sunset_plan_id='SUNSET-LEGACY'
                AND c.latest_review_occurred_at=r.occurred_at
                AND c.latest_review_recorded_at=r.recorded_at
                AND datetime(c.latest_review_occurred_at)
                    >datetime('2026-08-01T09:02:59Z')
                AND datetime(c.latest_review_recorded_at)
                    >datetime('2026-08-01T09:04:00Z')
            ) AND EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='trigger'
                AND name='validate_sunset_close_event_before_insert'
                AND instr(sql,'datetime(r.occurred_at)>datetime(NEW.occurred_at)')>0
                AND instr(sql,'datetime(r.recorded_at)>datetime(NEW.occurred_at)')>0
            ) THEN 0 ELSE 1 END,
       'Close time cannot precede occurrence or recording of the exact-snapshot review'
UNION ALL SELECT 'DQ43','error','closure_service_end_cutoff',
       CASE WHEN EXISTS (
              SELECT 1
              FROM v_sunset_close_readiness c
              JOIN sunset_plan p USING (sunset_plan_id)
              WHERE c.sunset_plan_id='SUNSET-LEGACY'
                AND c.service_end_at=p.service_end_at
                AND datetime('2026-07-31T14:59:59Z')<datetime(c.service_end_at)
                AND datetime('2026-07-31T15:00:00Z')>=datetime(c.service_end_at)
            ) AND EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='trigger'
                AND name='validate_sunset_close_event_before_insert'
                AND instr(sql,'sunset close event predates service end')>0
                AND instr(sql,'datetime(NEW.occurred_at)>=datetime(p.service_end_at)')>0
            ) THEN 0 ELSE 1 END,
       'A close event is rejected before the immutable plan service end'
UNION ALL SELECT 'DQ44','error','current_gate_future_evidence_is_unknown',
       CASE WHEN NOT EXISTS (
              SELECT 1 FROM portfolio_review r CROSS JOIN model_settings m
              WHERE datetime(r.recorded_at)>datetime(m.event_time_as_of)
            ) AND EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='view' AND name='v_current_review_gate_summary'
                AND instr(sql,'datetime(e.confirmed_at)')>0
                AND instr(sql,'>datetime(m.event_time_as_of)')>0
                AND instr(sql,'datetime(e.na_decided_at)>datetime(m.event_time_as_of)')>0
            ) THEN 0 ELSE 1 END,
       'Current freshness cannot consume evidence confirmed or N/A-decided in the future'
UNION ALL SELECT 'DQ45','error','close_event_model_as_of_cutoff',
       CASE WHEN EXISTS (
              SELECT 1 FROM model_settings m
              WHERE NOT EXISTS (
                SELECT 1 FROM portfolio_review r
                WHERE datetime(r.recorded_at)>datetime(m.event_time_as_of)
              )
            ) AND EXISTS (
              SELECT 1 FROM sqlite_schema
              WHERE type='trigger'
                AND name='validate_sunset_close_event_before_insert'
                AND instr(sql,'sunset close event exceeds model as-of')>0
                AND instr(sql,'datetime(NEW.occurred_at)<=datetime(m.event_time_as_of)')>0
            ) THEN 0 ELSE 1 END,
       'Closure cannot out-run the model current time used for gate freshness';

CREATE TEMP VIEW v_assertion_report AS
SELECT check_id,severity,check_name,failure_count,
       CASE WHEN failure_count=0 THEN 'PASS' ELSE 'FAIL' END AS status,
       expectation
FROM v_assertion_results;

-- A failed or NULL error assertion violates this CHECK and returns nonzero.
CREATE TABLE assertion_guard (
  guard_id INTEGER PRIMARY KEY CHECK (guard_id=1),
  error_failure_count INTEGER NOT NULL CHECK (error_failure_count=0)
) STRICT;

INSERT INTO assertion_guard(guard_id,error_failure_count)
SELECT 1,coalesce(sum(
  CASE WHEN failure_count IS NULL OR failure_count<>0 THEN 1 ELSE 0 END
),0)
FROM v_assertion_results
WHERE severity='error';

COMMIT;

-- -----------------------------------------------------------------------------
-- Bounded query pack
-- -----------------------------------------------------------------------------

PRAGMA foreign_key_check;

SELECT check_id,severity,check_name,failure_count,status
FROM v_assertion_report
ORDER BY check_id;

SELECT count(*) AS assertion_failure_rows
FROM v_assertion_report
WHERE status='FAIL';

SELECT item_id,portfolio_period_id,item_snapshot_id,snapshot_sequence,
       supersedes_item_snapshot_id,decision_at,recorded_at
FROM v_latest_item_snapshot
ORDER BY item_id,portfolio_period_id;

SELECT item_id,portfolio_period_id,portfolio_review_id,item_snapshot_id,
       review_sequence,supersedes_review_id,occurred_at,recorded_at
FROM v_latest_portfolio_review
ORDER BY item_id,portfolio_period_id;

SELECT item_snapshot_id,portfolio_review_id,review_sequence,
       supersedes_review_id,occurred_at,recorded_at
FROM v_latest_review_by_snapshot
ORDER BY item_snapshot_id;

SELECT portfolio_period_id,allocation_role_code,
       planned_founder_minutes,time_share_ppm,time_min_ppm,time_max_ppm,
       planned_cash_micros,cash_share_ppm,cash_min_ppm,cash_max_ppm
FROM v_period_role_plan
ORDER BY allocation_role_code;

SELECT portfolio_period_id,allocation_gate_state,
       planned_minutes,discretionary_founder_minutes,unallocated_founder_minutes,
       planned_cash_micros,discretionary_cash_micros,unallocated_cash_micros,
       time_guardrail_breaches,cash_guardrail_breaches
FROM v_period_allocation_integrity;

SELECT portfolio_period_id,failure_domain_id,domain_kind,
       distinct_exposed_items,direct_dependency_links,
       contribution_exposure_micros,active_customer_exposure,
       max_dependency_recovery_target_minutes
FROM v_failure_domain_exposure
ORDER BY failure_domain_id;

SELECT item_snapshot_id,dependency_count,stop_failures,pause_findings,
       unknown_findings,dependency_gate_state
FROM v_item_dependency_readiness
ORDER BY item_snapshot_id;

SELECT sunset_plan_id,represented_domains,missing_domain_count,
       active_critical_breach_count,unknown_status_count,open_count,blocked_count,
       known_open_cash_micros,funded_cash_micros,unfunded_cash_micros,
       known_open_founder_minutes,reserved_founder_minutes,
       unreserved_founder_minutes,sunset_gate_state
FROM v_sunset_readiness;

SELECT sunset_plan_id,sunset_plan_status_event_id,event_sequence,
       supersedes_status_event_id,plan_status,funded_cash_micros,
       reserved_founder_minutes,occurred_at,recorded_at
FROM v_current_sunset_plan_status_event
ORDER BY sunset_plan_id;

SELECT sunset_plan_id,liability_stream_id,sunset_liability_id,event_sequence,
       supersedes_liability_id,liability_domain_code,liability_status,
       completion_evidence_ref_hash,na_decided_at,na_valid_until,
       na_recheck_trigger,na_recheck_triggered_at,occurred_at,recorded_at
FROM v_current_sunset_liability
ORDER BY sunset_plan_id,liability_stream_id;

SELECT sunset_plan_id,item_snapshot_id,plan_status,represented_domains,
       missing_domain_count,unresolved_count,missing_final_evidence_count,
       stale_na_count,active_critical_breach_count,active_high_breach_count,
       latest_portfolio_review_id,stop_failure_count,pause_failure_count,
       unknown_gate_count,closure_ready
FROM v_sunset_close_readiness
ORDER BY sunset_plan_id;

SELECT portfolio_review_id,item_snapshot_id,allocation_role_code,lifecycle_code,
       allocation_gate_state,dependency_gate_state,applicable_sunset_gate_state,
       stop_failure_count,pause_failure_count,unknown_gate_count,derived_gate_state
FROM v_portfolio_review_readiness
ORDER BY portfolio_review_id;

SELECT portfolio_decision_id,portfolio_review_id,action_code,
       derived_gate_state,recorded_decision_compatible
FROM v_recorded_decision_compatibility
ORDER BY portfolio_decision_id;
