-- AI feature operating model: quality, cost, value, and cycle retention
-- Schema version: 1.0.0 | Reference date: 2026-08-01 | Engine: SQLite 3.45+
-- Companion: ../12-ai-feature-economics-quality-retention.md
--
-- Run this fictional reference model on a new/disposable SQLite database.
-- It is intentionally append-ledger oriented, but it is not a production migration.
--
-- Privacy boundary
-- - No names, email addresses, raw provider IDs, prompts, outputs, tool arguments,
--   invoice numbers, or customer documents belong in this database.
-- - External IDs/evidence pointers are 64-character lowercase hexadecimal hashes.
--   In production use a keyed hash when enumeration is plausible, and keep the
--   lookup system outside analytics.
-- - Seed printf() values only demonstrate shape; they are not cryptographic hashes.
--
-- Money boundary
-- - *_micros is one millionth of the row currency (USD 1.00 = 1,000,000).
-- - This executable companion is deliberately base-currency-only. Every stored
--   money row is FK-constrained to model_settings.base_currency; derived usage
--   and allocation money inherits an equally constrained price/invoice parent.
-- - A production multi-currency implementation must retain source amount and
--   source currency, FX rate, FX rate date/source, and book/base amount as
--   separate fields, as specified in the companion chapter. Never sum source
--   currencies directly.
-- - Customer contra revenue and provider credits have different ledgers.
-- - Gross/contract usage cost, invoice payable, and cash settlement stay distinct.
-- - Internal founder/provider time is a management cost, not necessarily cash.

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

CREATE TABLE schema_registry (
  schema_name TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL CHECK (schema_version = '1.0.0'),
  applied_at TEXT NOT NULL CHECK (datetime(applied_at) IS NOT NULL),
  specification_ref TEXT NOT NULL,
  pii_policy_code TEXT NOT NULL CHECK (pii_policy_code = 'HASH_OR_OPAQUE_REF_ONLY')
) STRICT;

CREATE TABLE model_settings (
  singleton_id INTEGER PRIMARY KEY CHECK (singleton_id = 1),
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  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),
  invoice_reconciled_through TEXT NOT NULL CHECK (datetime(invoice_reconciled_through) IS NOT NULL),
  late_threshold_hours INTEGER NOT NULL CHECK (late_threshold_hours > 0),
  reconciliation_tolerance_micros INTEGER NOT NULL CHECK (reconciliation_tolerance_micros>=0),
  metric_definition_version TEXT NOT NULL,
  base_currency TEXT NOT NULL UNIQUE CHECK (length(base_currency) = 3 AND base_currency = upper(base_currency))
) STRICT;

CREATE TABLE accounts (
  account_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  account_ref_hash TEXT NOT NULL UNIQUE,
  segment_code TEXT NOT NULL,
  plan_code TEXT NOT NULL,
  acquired_at TEXT NOT NULL CHECK (datetime(acquired_at) IS NOT NULL),
  commercial_status TEXT NOT NULL CHECK (commercial_status IN ('active','paused','churned','refunded','closed')),
  early_terminal_at TEXT CHECK (early_terminal_at IS NULL OR datetime(early_terminal_at) IS NOT NULL),
  terminal_reason_code TEXT,
  CHECK (length(account_ref_hash)=64 AND account_ref_hash=lower(account_ref_hash) AND account_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (
    (commercial_status IN ('churned','refunded','closed') AND early_terminal_at IS NOT NULL AND terminal_reason_code IS NOT NULL)
    OR (commercial_status IN ('active','paused') AND early_terminal_at IS NULL AND terminal_reason_code IS NULL)
  )
) STRICT;

CREATE TABLE workflow_policies (
  workflow_code TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  natural_cycle_code TEXT NOT NULL,
  planned_review_policy_code TEXT NOT NULL,
  planned_review_allows_independent_value INTEGER NOT NULL CHECK (planned_review_allows_independent_value IN (0,1)),
  founder_rescue_disqualifies_independent_value INTEGER NOT NULL CHECK (founder_rescue_disqualifies_independent_value = 1),
  policy_effective_from TEXT NOT NULL CHECK (datetime(policy_effective_from) IS NOT NULL),
  quality_definitive_early_maturity_allowed INTEGER NOT NULL CHECK (quality_definitive_early_maturity_allowed IN (0,1)),
  value_confirmed_early_maturity_allowed INTEGER NOT NULL CHECK (value_confirmed_early_maturity_allowed IN (0,1))
) STRICT;

-- A required review is satisfied only by a judged, policy-compliant decision.
-- Missing and explicitly unjudged records remain Unknown; they never pass by
-- falling out of an aggregate.
CREATE TABLE workflow_review_requirements (
  review_requirement_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version='1.0.0'),
  workflow_code TEXT NOT NULL,
  review_scope TEXT NOT NULL CHECK (review_scope IN ('presented_output','final_job','tool_effect','policy')),
  required_decision_count INTEGER NOT NULL CHECK (required_decision_count>=1),
  required_for_independent_value INTEGER NOT NULL CHECK (required_for_independent_value IN (0,1)),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_to TEXT CHECK (effective_to IS NULL OR (datetime(effective_to) IS NOT NULL AND effective_to>effective_from)),
  UNIQUE (workflow_code,review_scope,effective_from),
  FOREIGN KEY (workflow_code) REFERENCES workflow_policies(workflow_code) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Predeclared natural-cycle calendar is the source for the randomized follow-up
-- spine. Missing account-cycle rows therefore remain observable as missing; they
-- cannot silently fall out of an ITT denominator.
CREATE TABLE workflow_cycle_calendar (
  workflow_code TEXT NOT NULL,
  cycle_number INTEGER NOT NULL CHECK (cycle_number>=1),
  natural_cycle_start TEXT NOT NULL CHECK (datetime(natural_cycle_start) IS NOT NULL),
  natural_cycle_end TEXT NOT NULL CHECK (datetime(natural_cycle_end) IS NOT NULL),
  on_time_due_at TEXT NOT NULL CHECK (datetime(on_time_due_at) IS NOT NULL),
  maturity_due_at TEXT NOT NULL CHECK (datetime(maturity_due_at) IS NOT NULL),
  PRIMARY KEY (workflow_code,cycle_number),
  CHECK (natural_cycle_end>natural_cycle_start AND on_time_due_at>=natural_cycle_end AND maturity_due_at>=on_time_due_at),
  FOREIGN KEY (workflow_code) REFERENCES workflow_policies(workflow_code) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE experiments (
  experiment_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  feature_code TEXT NOT NULL,
  workflow_code TEXT NOT NULL,
  design_type TEXT NOT NULL CHECK (design_type IN ('randomized','quasi_experimental','observational')),
  assignment_unit TEXT NOT NULL CHECK (assignment_unit='account'),
  hypothesis_ref_hash TEXT NOT NULL,
  starts_at TEXT NOT NULL CHECK (datetime(starts_at) IS NOT NULL),
  ends_at TEXT CHECK (ends_at IS NULL OR (datetime(ends_at) IS NOT NULL AND ends_at>starts_at)),
  status TEXT NOT NULL CHECK (status IN ('draft','running','stopped','completed')),
  CHECK (length(hypothesis_ref_hash)=64 AND hypothesis_ref_hash=lower(hypothesis_ref_hash) AND hypothesis_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (workflow_code) REFERENCES workflow_policies(workflow_code) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE experiment_variants (
  variant_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  experiment_id TEXT NOT NULL,
  variant_code TEXT NOT NULL,
  route_policy_code TEXT NOT NULL,
  intended_weight_ppm INTEGER CHECK (intended_weight_ppm IS NULL OR intended_weight_ppm BETWEEN 0 AND 1000000),
  UNIQUE (experiment_id,variant_code),
  UNIQUE (variant_id,experiment_id),
  FOREIGN KEY (experiment_id) REFERENCES experiments(experiment_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE experiment_assignments (
  assignment_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  experiment_id TEXT NOT NULL,
  variant_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  assignment_ref_hash TEXT NOT NULL UNIQUE,
  eligible_at TEXT NOT NULL CHECK (datetime(eligible_at) IS NOT NULL),
  assigned_at TEXT NOT NULL CHECK (datetime(assigned_at) IS NOT NULL),
  followup_time_zero_at TEXT NOT NULL CHECK (datetime(followup_time_zero_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  assignment_method TEXT NOT NULL CHECK (assignment_method IN ('randomized','rule_based','manual_predeclared')),
  withdrawn_at TEXT CHECK (withdrawn_at IS NULL OR datetime(withdrawn_at) IS NOT NULL),
  withdrawal_reason_code TEXT,
  CHECK (assigned_at>=eligible_at),
  CHECK (length(assignment_ref_hash)=64 AND assignment_ref_hash=lower(assignment_ref_hash) AND assignment_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((withdrawn_at IS NULL AND withdrawal_reason_code IS NULL) OR (withdrawn_at IS NOT NULL AND withdrawal_reason_code IS NOT NULL)),
  UNIQUE (experiment_id,account_id),
  UNIQUE (assignment_id,account_id),
  UNIQUE (assignment_id,experiment_id,account_id),
  FOREIGN KEY (experiment_id) REFERENCES experiments(experiment_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (variant_id,experiment_id) REFERENCES experiment_variants(variant_id,experiment_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Value cycles never carry customer revenue. They are the natural workflow clock.
CREATE TABLE workflow_value_cycles (
  workflow_value_cycle_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  account_id TEXT NOT NULL,
  workflow_code TEXT NOT NULL,
  cycle_number INTEGER NOT NULL CHECK (cycle_number>=1),
  natural_cycle_start TEXT NOT NULL CHECK (datetime(natural_cycle_start) IS NOT NULL),
  natural_cycle_end TEXT NOT NULL CHECK (datetime(natural_cycle_end) IS NOT NULL),
  on_time_due_at TEXT NOT NULL CHECK (datetime(on_time_due_at) IS NOT NULL),
  maturity_due_at TEXT NOT NULL CHECK (datetime(maturity_due_at) IS NOT NULL),
  matured_at TEXT CHECK (matured_at IS NULL OR datetime(matured_at) IS NOT NULL),
  early_terminal_at TEXT CHECK (early_terminal_at IS NULL OR datetime(early_terminal_at) IS NOT NULL),
  terminal_reason_code TEXT,
  reopened_at TEXT CHECK (reopened_at IS NULL OR datetime(reopened_at) IS NOT NULL),
  cycle_state TEXT NOT NULL CHECK (cycle_state IN ('provisional','matured','early_terminal','reopened','excluded')),
  exclusion_reason_code TEXT,
  CHECK (natural_cycle_end>natural_cycle_start AND on_time_due_at>=natural_cycle_end AND maturity_due_at>=on_time_due_at),
  CHECK (
    (cycle_state='provisional' AND matured_at IS NULL AND early_terminal_at IS NULL)
    OR (cycle_state='matured' AND matured_at IS NOT NULL)
    OR (cycle_state='early_terminal' AND matured_at IS NOT NULL AND early_terminal_at IS NOT NULL AND terminal_reason_code IS NOT NULL)
    OR (cycle_state='reopened' AND reopened_at IS NOT NULL)
    OR (cycle_state='excluded' AND exclusion_reason_code IS NOT NULL)
  ),
  CHECK (cycle_state<>'matured' OR matured_at>=maturity_due_at),
  CHECK (cycle_state<>'early_terminal' OR (early_terminal_at<=maturity_due_at AND matured_at>=early_terminal_at)),
  UNIQUE (account_id,workflow_code,cycle_number),
  UNIQUE (workflow_value_cycle_id,account_id),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (workflow_code) REFERENCES workflow_policies(workflow_code) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (workflow_code,cycle_number) REFERENCES workflow_cycle_calendar(workflow_code,cycle_number) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- A workflow cycle is mapped to a concrete assignment before analysis.  Joining
-- cycles to assignments by account alone is forbidden because one account can
-- participate in more than one experiment.
CREATE TABLE workflow_cycle_experiment_assignments (
  cycle_experiment_assignment_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version='1.0.0'),
  workflow_value_cycle_id TEXT NOT NULL,
  experiment_id TEXT NOT NULL,
  assignment_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  mapping_rule_version TEXT NOT NULL,
  mapped_at TEXT NOT NULL CHECK (datetime(mapped_at) IS NOT NULL),
  UNIQUE (workflow_value_cycle_id,experiment_id),
  UNIQUE (workflow_value_cycle_id,assignment_id),
  FOREIGN KEY (workflow_value_cycle_id,account_id) REFERENCES workflow_value_cycles(workflow_value_cycle_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (assignment_id,experiment_id,account_id) REFERENCES experiment_assignments(assignment_id,experiment_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Commercial cycles own revenue exactly once, independent of workflow cadence.
CREATE TABLE account_commercial_cycles (
  account_commercial_cycle_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  account_id TEXT NOT NULL,
  contract_ref_hash TEXT NOT NULL,
  billing_period_start TEXT NOT NULL CHECK (datetime(billing_period_start) IS NOT NULL),
  billing_period_end TEXT NOT NULL CHECK (datetime(billing_period_end) IS NOT NULL),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  commercial_state TEXT NOT NULL CHECK (commercial_state IN ('open','closed','reopened','written_off')),
  closed_at TEXT CHECK (closed_at IS NULL OR datetime(closed_at) IS NOT NULL),
  reopened_at TEXT CHECK (reopened_at IS NULL OR datetime(reopened_at) IS NOT NULL),
  CHECK (length(contract_ref_hash)=64 AND contract_ref_hash=lower(contract_ref_hash) AND contract_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (billing_period_end>billing_period_start),
  CHECK ((commercial_state='open' AND closed_at IS NULL) OR commercial_state<>'open'),
  CHECK (commercial_state<>'reopened' OR reopened_at IS NOT NULL),
  UNIQUE (account_id,contract_ref_hash,billing_period_start),
  UNIQUE (account_commercial_cycle_id,account_id),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE workflow_commercial_cycle_map (
  mapping_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  workflow_value_cycle_id TEXT NOT NULL UNIQUE,
  account_commercial_cycle_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  UNIQUE (workflow_value_cycle_id,account_commercial_cycle_id),
  FOREIGN KEY (workflow_value_cycle_id,account_id) REFERENCES workflow_value_cycles(workflow_value_cycle_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (account_commercial_cycle_id,account_id) REFERENCES account_commercial_cycles(account_commercial_cycle_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- This is the only feature-revenue allocation ledger.  The cadence map above
-- carries no money, so a feature amount cannot be materialized twice.
CREATE TABLE feature_revenue_allocations (
  feature_revenue_allocation_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version='1.0.0'),
  workflow_value_cycle_id TEXT NOT NULL,
  account_commercial_cycle_id TEXT NOT NULL,
  feature_code TEXT NOT NULL,
  allocation_status TEXT NOT NULL CHECK (allocation_status IN ('unallocated','explicit_addon','predeclared_rule')),
  allocated_feature_net_revenue_micros INTEGER,
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  allocation_rule_version TEXT,
  allocated_at TEXT NOT NULL CHECK (datetime(allocated_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (
    (allocation_status='unallocated' AND allocated_feature_net_revenue_micros IS NULL AND allocation_rule_version IS NULL)
    OR (allocation_status<>'unallocated' AND allocated_feature_net_revenue_micros>=0 AND allocation_rule_version IS NOT NULL)
  ),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (workflow_value_cycle_id,feature_code),
  FOREIGN KEY (workflow_value_cycle_id,account_commercial_cycle_id) REFERENCES workflow_commercial_cycle_map(workflow_value_cycle_id,account_commercial_cycle_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- One dispute event owns the principal accounting choice.  Dispute fees may be
-- linked to the same event but are never principal.
CREATE TABLE payment_dispute_events (
  payment_dispute_event_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version='1.0.0'),
  account_commercial_cycle_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  principal_amount_micros INTEGER NOT NULL CHECK (principal_amount_micros>0),
  principal_treatment TEXT NOT NULL CHECK (principal_treatment IN ('contra_revenue','cash_cost')),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  payment_transaction_ref_hash TEXT NOT NULL UNIQUE,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  CHECK (length(payment_transaction_ref_hash)=64 AND payment_transaction_ref_hash=lower(payment_transaction_ref_hash) AND payment_transaction_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (account_commercial_cycle_id,account_id) REFERENCES account_commercial_cycles(account_commercial_cycle_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Contra revenue uses negative amounts. Refunds never enter a job-cost ledger.
CREATE TABLE customer_revenue_ledger (
  customer_revenue_line_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  account_commercial_cycle_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  line_kind TEXT NOT NULL CHECK (line_kind IN ('gross_revenue','discount','customer_refund','credit_note','chargeback_principal','chargeback_recovery')),
  entry_action TEXT NOT NULL DEFAULT 'recognize' CHECK (entry_action IN ('recognize','reverse')),
  amount_micros INTEGER NOT NULL,
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  source_line_ref_hash TEXT NOT NULL UNIQUE,
  payment_dispute_event_id TEXT,
  corrects_line_id TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  CHECK (
    (entry_action='recognize' AND ((line_kind IN ('gross_revenue','chargeback_recovery') AND amount_micros>=0) OR (line_kind NOT IN ('gross_revenue','chargeback_recovery') AND amount_micros<=0)))
    OR (entry_action='reverse' AND ((line_kind IN ('gross_revenue','chargeback_recovery') AND amount_micros<=0) OR (line_kind NOT IN ('gross_revenue','chargeback_recovery') AND amount_micros>=0)))
  ),
  CHECK (entry_action='recognize' OR corrects_line_id IS NOT NULL),
  CHECK ((line_kind IN ('chargeback_principal','chargeback_recovery') AND payment_dispute_event_id IS NOT NULL) OR (line_kind NOT IN ('chargeback_principal','chargeback_recovery') AND payment_dispute_event_id IS NULL)),
  CHECK (length(source_line_ref_hash)=64 AND source_line_ref_hash=lower(source_line_ref_hash) AND source_line_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (customer_revenue_line_id,account_commercial_cycle_id),
  FOREIGN KEY (account_commercial_cycle_id,account_id) REFERENCES account_commercial_cycles(account_commercial_cycle_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (corrects_line_id,account_commercial_cycle_id) REFERENCES customer_revenue_ledger(customer_revenue_line_id,account_commercial_cycle_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (payment_dispute_event_id) REFERENCES payment_dispute_events(payment_dispute_event_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE UNIQUE INDEX uq_revenue_chargeback_principal
  ON customer_revenue_ledger(payment_dispute_event_id)
  WHERE line_kind='chargeback_principal' AND entry_action='recognize';
CREATE UNIQUE INDEX uq_customer_revenue_reversal_target
  ON customer_revenue_ledger(corrects_line_id)
  WHERE entry_action='reverse';

CREATE TABLE eligible_opportunities (
  eligible_opportunity_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version='1.0.0'),
  opportunity_ref_hash TEXT NOT NULL UNIQUE,
  account_id TEXT NOT NULL,
  workflow_value_cycle_id TEXT NOT NULL,
  feature_code TEXT NOT NULL,
  workflow_code TEXT NOT NULL,
  job_type_code TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (length(opportunity_ref_hash)=64 AND opportunity_ref_hash=lower(opportunity_ref_hash) AND opportunity_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (eligible_opportunity_id,account_id,workflow_value_cycle_id),
  FOREIGN KEY (workflow_value_cycle_id,account_id) REFERENCES workflow_value_cycles(workflow_value_cycle_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (workflow_code) REFERENCES workflow_policies(workflow_code) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE jobs (
  job_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  eligible_opportunity_id TEXT NOT NULL UNIQUE,
  opportunity_ref_hash TEXT NOT NULL UNIQUE,
  account_id TEXT NOT NULL,
  workflow_value_cycle_id TEXT NOT NULL,
  assignment_id TEXT NOT NULL,
  feature_code TEXT NOT NULL,
  workflow_code TEXT NOT NULL,
  job_type_code TEXT NOT NULL,
  segment_code TEXT NOT NULL,
  risk_tier TEXT NOT NULL CHECK (risk_tier IN ('low','medium','high','critical')),
  eligible_at TEXT NOT NULL CHECK (datetime(eligible_at) IS NOT NULL),
  offered_at TEXT CHECK (offered_at IS NULL OR datetime(offered_at) IS NOT NULL),
  exposed_at TEXT CHECK (exposed_at IS NULL OR datetime(exposed_at) IS NOT NULL),
  started_at TEXT CHECK (started_at IS NULL OR datetime(started_at) IS NOT NULL),
  execution_due_at TEXT NOT NULL CHECK (datetime(execution_due_at) IS NOT NULL),
  quality_due_at TEXT NOT NULL CHECK (datetime(quality_due_at) IS NOT NULL),
  value_due_at TEXT NOT NULL CHECK (datetime(value_due_at) IS NOT NULL),
  incident_observation_due_at TEXT NOT NULL CHECK (datetime(incident_observation_due_at) IS NOT NULL),
  execution_state TEXT NOT NULL CHECK (execution_state IN ('not_started','completed','no_output','abandoned','stopped_for_safety','expired')),
  acceptance_state TEXT NOT NULL CHECK (acceptance_state IN ('not_assessable','accepted_first_pass','accepted_after_correction','rejected','unknown')),
  value_state TEXT NOT NULL CHECK (value_state IN ('not_due','confirmed','not_confirmed','unknown','reopened')),
  incident_state TEXT NOT NULL CHECK (incident_state IN ('none_observed','open','confirmed','closed','reopened')),
  execution_mature_at TEXT CHECK (execution_mature_at IS NULL OR datetime(execution_mature_at) IS NOT NULL),
  quality_mature_at TEXT CHECK (quality_mature_at IS NULL OR datetime(quality_mature_at) IS NOT NULL),
  value_mature_at TEXT CHECK (value_mature_at IS NULL OR datetime(value_mature_at) IS NOT NULL),
  incident_mature_at TEXT CHECK (incident_mature_at IS NULL OR datetime(incident_mature_at) IS NOT NULL),
  reopened_quality_at TEXT CHECK (reopened_quality_at IS NULL OR datetime(reopened_quality_at) IS NOT NULL),
  reopened_value_at TEXT CHECK (reopened_value_at IS NULL OR datetime(reopened_value_at) IS NOT NULL),
  reopened_incident_at TEXT CHECK (reopened_incident_at IS NULL OR datetime(reopened_incident_at) IS NOT NULL),
  excluded_at TEXT CHECK (excluded_at IS NULL OR datetime(excluded_at) IS NOT NULL),
  exclusion_reason_code TEXT,
  CHECK (length(opportunity_ref_hash)=64 AND opportunity_ref_hash=lower(opportunity_ref_hash) AND opportunity_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (offered_at IS NULL OR offered_at>=eligible_at),
  CHECK (exposed_at IS NULL OR (offered_at IS NOT NULL AND exposed_at>=offered_at)),
  CHECK (started_at IS NULL OR started_at>=eligible_at),
  CHECK (execution_due_at>=eligible_at AND quality_due_at>=execution_due_at AND value_due_at>=quality_due_at AND incident_observation_due_at>=execution_due_at),
  CHECK ((excluded_at IS NULL AND exclusion_reason_code IS NULL) OR (excluded_at IS NOT NULL AND exclusion_reason_code IS NOT NULL)),
  CHECK (execution_state IN ('not_started','expired') OR started_at IS NOT NULL),
  CHECK (acceptance_state='not_assessable' OR started_at IS NOT NULL),
  CHECK (execution_mature_at IS NULL OR execution_state<>'not_started'),
  CHECK (acceptance_state<>'unknown' OR quality_mature_at IS NULL OR quality_mature_at>=quality_due_at),
  CHECK (value_state<>'unknown' OR value_mature_at IS NULL OR value_mature_at>=value_due_at),
  CHECK (incident_state<>'none_observed' OR incident_mature_at IS NULL OR incident_mature_at>=incident_observation_due_at),
  CHECK (reopened_incident_at IS NULL OR incident_mature_at IS NULL OR incident_mature_at>=reopened_incident_at),
  UNIQUE (job_id,workflow_value_cycle_id),
  UNIQUE (job_id,account_id),
  FOREIGN KEY (eligible_opportunity_id,account_id,workflow_value_cycle_id) REFERENCES eligible_opportunities(eligible_opportunity_id,account_id,workflow_value_cycle_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (workflow_value_cycle_id,account_id) REFERENCES workflow_value_cycles(workflow_value_cycle_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (assignment_id,account_id) REFERENCES experiment_assignments(assignment_id,account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (workflow_value_cycle_id,assignment_id) REFERENCES workflow_cycle_experiment_assignments(workflow_value_cycle_id,assignment_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (workflow_code) REFERENCES workflow_policies(workflow_code) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Each opportunity appears exactly once because eligible_opportunity_id is UNIQUE
-- and NOT NULL on jobs. Retries/fallbacks never create another job.
CREATE TABLE job_state_events (
  job_state_event_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  state_axis TEXT NOT NULL CHECK (state_axis IN ('execution','acceptance','value','incident')),
  state_code TEXT NOT NULL,
  event_phase TEXT NOT NULL CHECK (event_phase IN ('provisional','matured','reopened','restated','excluded')),
  reason_code TEXT NOT NULL,
  restates_event_id TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((event_phase='restated' AND restates_event_id IS NOT NULL) OR event_phase<>'restated'),
  UNIQUE (job_id,job_state_event_id),
  FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,restates_event_id) REFERENCES job_state_events(job_id,job_state_event_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE processing_routes (
  route_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  route_kind TEXT NOT NULL CHECK (route_kind IN ('ai','deterministic','human_only')),
  provider_code TEXT,
  product_code TEXT,
  model_snapshot_code TEXT,
  prompt_version TEXT NOT NULL,
  retrieval_version TEXT NOT NULL,
  tool_policy_version TEXT NOT NULL,
  safety_policy_version TEXT NOT NULL,
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_to TEXT CHECK (effective_to IS NULL OR (datetime(effective_to) IS NOT NULL AND effective_to>effective_from)),
  CHECK ((route_kind='ai' AND provider_code IS NOT NULL AND product_code IS NOT NULL AND model_snapshot_code IS NOT NULL) OR (route_kind<>'ai' AND provider_code IS NULL AND product_code IS NULL AND model_snapshot_code IS NULL))
) STRICT;

CREATE TABLE attempts (
  attempt_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  attempt_ordinal INTEGER NOT NULL CHECK (attempt_ordinal>=1),
  parent_attempt_id TEXT,
  attempt_role TEXT NOT NULL CHECK (attempt_role IN ('initial','reroute','human_reentry')),
  reason_code TEXT,
  route_id TEXT NOT NULL,
  idempotency_key_hash TEXT NOT NULL UNIQUE,
  started_at TEXT NOT NULL CHECK (datetime(started_at) IS NOT NULL),
  finished_at TEXT CHECK (finished_at IS NULL OR (datetime(finished_at) IS NOT NULL AND finished_at>=started_at)),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  run_state TEXT NOT NULL CHECK (run_state IN ('running','completed','failed','timeout','budget_exhausted','stopped_safety','abandoned')),
  CHECK ((attempt_ordinal=1 AND attempt_role='initial' AND parent_attempt_id IS NULL AND reason_code IS NULL) OR (attempt_ordinal>1 AND attempt_role<>'initial' AND parent_attempt_id IS NOT NULL AND reason_code IS NOT NULL)),
  CHECK (length(idempotency_key_hash)=64 AND idempotency_key_hash=lower(idempotency_key_hash) AND idempotency_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (job_id,attempt_ordinal),
  UNIQUE (job_id,attempt_id),
  FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,parent_attempt_id) REFERENCES attempts(job_id,attempt_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (route_id) REFERENCES processing_routes(route_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- First presented output is deliberately not attempt 1. Several internal calls can
-- fail/validate/fallback before presentation_ordinal=1 is committed to the customer.
CREATE TABLE presented_outputs (
  presented_output_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  attempt_id TEXT NOT NULL,
  presentation_ordinal INTEGER NOT NULL CHECK (presentation_ordinal>=1),
  content_version_hash TEXT NOT NULL,
  committed_to_customer INTEGER NOT NULL CHECK (committed_to_customer=1),
  presented_at TEXT NOT NULL CHECK (datetime(presented_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (length(content_version_hash)=64 AND content_version_hash=lower(content_version_hash) AND content_version_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (job_id,presentation_ordinal),
  UNIQUE (job_id,presented_output_id),
  FOREIGN KEY (job_id,attempt_id) REFERENCES attempts(job_id,attempt_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE provider_requests (
  provider_request_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  attempt_id TEXT NOT NULL,
  job_request_ordinal INTEGER NOT NULL CHECK (job_request_ordinal>=1),
  attempt_request_ordinal INTEGER NOT NULL CHECK (attempt_request_ordinal>=1),
  request_role TEXT NOT NULL CHECK (request_role IN ('primary','planned_auxiliary','validation')),
  failure_relation_type TEXT NOT NULL CHECK (failure_relation_type IN ('none','app_retry','sdk_retry','fallback','repair')),
  retry_of_request_id TEXT,
  fallback_from_request_id TEXT,
  repair_of_request_id TEXT,
  provider_code TEXT NOT NULL,
  product_code TEXT NOT NULL,
  model_snapshot_code TEXT NOT NULL,
  region_code TEXT NOT NULL DEFAULT 'global',
  service_tier_code TEXT NOT NULL DEFAULT 'standard',
  billing_channel_code TEXT NOT NULL CHECK (billing_channel_code IN ('direct_api','marketplace','reseller','enterprise_contract')),
  batch_mode TEXT NOT NULL DEFAULT 'realtime' CHECK (batch_mode IN ('realtime','batch')),
  context_band TEXT NOT NULL DEFAULT 'standard' CHECK (context_band IN ('standard','extended','not_applicable')),
  provider_request_ref_hash TEXT NOT NULL UNIQUE,
  idempotency_key_hash TEXT NOT NULL UNIQUE,
  sdk_name TEXT NOT NULL,
  sdk_version TEXT NOT NULL,
  sdk_retry_config_ref_hash TEXT NOT NULL,
  request_status TEXT NOT NULL CHECK (request_status IN ('completed','failed','timeout','rate_limited','cancelled','unknown_billed')),
  error_class TEXT,
  started_at TEXT NOT NULL CHECK (datetime(started_at) IS NOT NULL),
  finished_at TEXT CHECK (finished_at IS NULL OR (datetime(finished_at) IS NOT NULL AND finished_at>=started_at)),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  CHECK (length(provider_request_ref_hash)=64 AND provider_request_ref_hash=lower(provider_request_ref_hash) AND provider_request_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(idempotency_key_hash)=64 AND idempotency_key_hash=lower(idempotency_key_hash) AND idempotency_key_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(sdk_retry_config_ref_hash)=64 AND sdk_retry_config_ref_hash=lower(sdk_retry_config_ref_hash) AND sdk_retry_config_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((request_status IN ('completed','unknown_billed') AND error_class IS NULL) OR request_status NOT IN ('completed','unknown_billed')),
  CHECK (
    (failure_relation_type='none' AND retry_of_request_id IS NULL AND fallback_from_request_id IS NULL AND repair_of_request_id IS NULL)
    OR (failure_relation_type IN ('app_retry','sdk_retry') AND retry_of_request_id IS NOT NULL AND fallback_from_request_id IS NULL AND repair_of_request_id IS NULL)
    OR (failure_relation_type='fallback' AND retry_of_request_id IS NULL AND fallback_from_request_id IS NOT NULL AND repair_of_request_id IS NULL)
    OR (failure_relation_type='repair' AND retry_of_request_id IS NULL AND fallback_from_request_id IS NULL AND repair_of_request_id IS NOT NULL)
  ),
  UNIQUE (job_id,job_request_ordinal),
  UNIQUE (attempt_id,attempt_request_ordinal),
  UNIQUE (job_id,provider_request_id),
  FOREIGN KEY (job_id,attempt_id) REFERENCES attempts(job_id,attempt_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,retry_of_request_id) REFERENCES provider_requests(job_id,provider_request_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,fallback_from_request_id) REFERENCES provider_requests(job_id,provider_request_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,repair_of_request_id) REFERENCES provider_requests(job_id,provider_request_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Canonical price-line identity includes commercial channel as well as
-- provider/product/model/SKU, region, tier, batch/context, currency and window.
CREATE TABLE provider_price_book (
  price_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  provider_code TEXT NOT NULL,
  product_code TEXT NOT NULL,
  model_snapshot_code TEXT NOT NULL,
  sku_code TEXT NOT NULL,
  region_code TEXT NOT NULL,
  service_tier_code TEXT NOT NULL,
  billing_channel_code TEXT NOT NULL CHECK (billing_channel_code IN ('direct_api','marketplace','reseller','enterprise_contract')),
  batch_mode TEXT NOT NULL DEFAULT 'realtime' CHECK (batch_mode IN ('realtime','batch')),
  context_band TEXT NOT NULL DEFAULT 'standard' CHECK (context_band IN ('standard','extended','not_applicable')),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  provider_unit_code TEXT NOT NULL,
  unit_scale INTEGER NOT NULL CHECK (unit_scale>0),
  list_unit_rate_micros INTEGER NOT NULL CHECK (list_unit_rate_micros>=0),
  contract_unit_rate_micros INTEGER NOT NULL CHECK (contract_unit_rate_micros>=0),
  rate_card_version TEXT NOT NULL,
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_to TEXT CHECK (effective_to IS NULL OR (datetime(effective_to) IS NOT NULL AND effective_to>effective_from)),
  source_url TEXT NOT NULL CHECK (source_url GLOB 'https://*'),
  accessed_at TEXT NOT NULL CHECK (datetime(accessed_at) IS NOT NULL),
  UNIQUE (provider_code,product_code,model_snapshot_code,sku_code,region_code,service_tier_code,billing_channel_code,batch_mode,context_band,currency,effective_from),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Native telemetry and the provider's billed quantity are deliberately separate.
-- Cost is computed only from billable_quantity/billable_unit_code.  Conversion,
-- inclusion, rounding and minimum-charge lineage remains queryable.
CREATE TABLE provider_usage_lines (
  usage_line_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  provider_request_id TEXT NOT NULL,
  job_id TEXT NOT NULL,
  price_id TEXT NOT NULL,
  usage_component_code TEXT NOT NULL CHECK (usage_component_code IN ('input','cached_input','cache_write','output','reasoning','image','audio','video','tool','storage','session','other')),
  usage_line_ordinal INTEGER NOT NULL CHECK (usage_line_ordinal>=1),
  provider_native_unit_code TEXT NOT NULL,
  native_quantity INTEGER NOT NULL CHECK (native_quantity>=0),
  billable_unit_code TEXT NOT NULL DEFAULT 'token',
  billable_quantity INTEGER NOT NULL DEFAULT 0 CHECK (billable_quantity>=0),
  billing_conversion_rule_code TEXT NOT NULL DEFAULT 'identity-v1',
  inclusion_rule_code TEXT NOT NULL DEFAULT 'billable' CHECK (inclusion_rule_code IN ('billable','included','excluded','bundled')),
  rounding_rule_code TEXT NOT NULL DEFAULT 'provider-reported-v1',
  minimum_rule_code TEXT NOT NULL DEFAULT 'none',
  usage_recorded_at TEXT NOT NULL CHECK (datetime(usage_recorded_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  list_estimated_cost_micros INTEGER NOT NULL CHECK (list_estimated_cost_micros>=0),
  contract_estimated_cost_micros INTEGER NOT NULL CHECK (contract_estimated_cost_micros>=0),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((inclusion_rule_code='billable') OR billable_quantity=0),
  UNIQUE (provider_request_id,usage_component_code,usage_line_ordinal),
  FOREIGN KEY (job_id,provider_request_id) REFERENCES provider_requests(job_id,provider_request_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (price_id) REFERENCES provider_price_book(price_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE tool_effects (
  effect_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  attempt_id TEXT NOT NULL,
  tool_code TEXT NOT NULL,
  effect_class TEXT NOT NULL CHECK (effect_class IN ('read','search','draft','write','send_publish','delete_revoke','purchase_pay')),
  target_ref_hash TEXT NOT NULL,
  arguments_ref_hash TEXT NOT NULL,
  tool_policy_version TEXT NOT NULL,
  effect_fingerprint_hash TEXT NOT NULL,
  idempotency_key_hash TEXT NOT NULL UNIQUE,
  side_effect_level TEXT NOT NULL CHECK (side_effect_level IN ('none','reversible','compensatable','irreversible')),
  risk_tier TEXT NOT NULL CHECK (risk_tier IN ('low','medium','high','critical')),
  approval_required INTEGER NOT NULL CHECK (approval_required IN (0,1)),
  reversal_window_seconds INTEGER,
  verification_due_seconds INTEGER CHECK (verification_due_seconds IS NULL OR verification_due_seconds>0),
  CHECK (length(target_ref_hash)=64 AND target_ref_hash=lower(target_ref_hash) AND target_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(arguments_ref_hash)=64 AND arguments_ref_hash=lower(arguments_ref_hash) AND arguments_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(effect_fingerprint_hash)=64 AND effect_fingerprint_hash=lower(effect_fingerprint_hash) AND effect_fingerprint_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(idempotency_key_hash)=64 AND idempotency_key_hash=lower(idempotency_key_hash) AND idempotency_key_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((side_effect_level='none' AND reversal_window_seconds IS NULL) OR (side_effect_level IN ('reversible','compensatable') AND reversal_window_seconds>0) OR (side_effect_level='irreversible' AND reversal_window_seconds IS NULL)),
  CHECK ((side_effect_level='none' AND verification_due_seconds IS NULL) OR (side_effect_level<>'none' AND verification_due_seconds IS NOT NULL)),
  CHECK (effect_class IN ('read','search','draft') OR approval_required=1),
  UNIQUE (job_id,effect_fingerprint_hash),
  UNIQUE (effect_id,effect_fingerprint_hash),
  FOREIGN KEY (job_id,attempt_id) REFERENCES attempts(job_id,attempt_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- An approval is an exact-action capability: tool + target + argument hash +
-- policy/effect fingerprint, bounded by authority, scope and expiry.
CREATE TABLE tool_effect_approvals (
  approval_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version='1.0.0'),
  effect_id TEXT NOT NULL,
  action_fingerprint_hash TEXT NOT NULL,
  approval_scope TEXT NOT NULL CHECK (approval_scope='exact_action'),
  authority_kind TEXT NOT NULL CHECK (authority_kind IN ('founder','customer')),
  authority_ref_hash TEXT NOT NULL,
  authority_scope_code TEXT NOT NULL,
  approved_at TEXT NOT NULL CHECK (datetime(approved_at) IS NOT NULL),
  expires_at TEXT NOT NULL CHECK (datetime(expires_at) IS NOT NULL),
  approval_ref_hash TEXT NOT NULL UNIQUE,
  CHECK (expires_at>approved_at),
  CHECK (length(action_fingerprint_hash)=64 AND action_fingerprint_hash=lower(action_fingerprint_hash) AND action_fingerprint_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(authority_ref_hash)=64 AND authority_ref_hash=lower(authority_ref_hash) AND authority_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(approval_ref_hash)=64 AND approval_ref_hash=lower(approval_ref_hash) AND approval_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (approval_id,effect_id,action_fingerprint_hash),
  FOREIGN KEY (effect_id,action_fingerprint_hash) REFERENCES tool_effects(effect_id,effect_fingerprint_hash) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE tool_effect_events (
  effect_event_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  effect_id TEXT NOT NULL,
  transition_ordinal INTEGER NOT NULL CHECK (transition_ordinal>=1),
  lifecycle_event TEXT NOT NULL CHECK (lifecycle_event IN ('proposed','approval_requested','approved','rejected','commit_started','committed','verified','failed','rolled_back','expired')),
  actor_side TEXT NOT NULL CHECK (actor_side IN ('system','provider','founder','customer')),
  actor_ref_hash TEXT,
  approval_scope_ref_hash TEXT,
  approval_id TEXT,
  bound_action_fingerprint_hash TEXT,
  external_execution_ref_hash TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  result_code TEXT NOT NULL,
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK ((actor_side IN ('system','provider') AND actor_ref_hash IS NULL) OR (actor_side IN ('founder','customer') AND length(actor_ref_hash)=64 AND actor_ref_hash=lower(actor_ref_hash) AND actor_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (approval_scope_ref_hash IS NULL OR (length(approval_scope_ref_hash)=64 AND approval_scope_ref_hash=lower(approval_scope_ref_hash) AND approval_scope_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (external_execution_ref_hash IS NULL OR (length(external_execution_ref_hash)=64 AND external_execution_ref_hash=lower(external_execution_ref_hash) AND external_execution_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (bound_action_fingerprint_hash IS NULL OR (length(bound_action_fingerprint_hash)=64 AND bound_action_fingerprint_hash=lower(bound_action_fingerprint_hash) AND bound_action_fingerprint_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (lifecycle_event<>'approved' OR (actor_side IN ('founder','customer') AND approval_scope_ref_hash IS NOT NULL AND approval_id IS NOT NULL AND bound_action_fingerprint_hash IS NOT NULL)),
  CHECK (lifecycle_event<>'committed' OR (external_execution_ref_hash IS NOT NULL AND bound_action_fingerprint_hash IS NOT NULL)),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (effect_id,transition_ordinal),
  FOREIGN KEY (effect_id) REFERENCES tool_effects(effect_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (approval_id,effect_id,bound_action_fingerprint_hash) REFERENCES tool_effect_approvals(approval_id,effect_id,action_fingerprint_hash) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (effect_id,bound_action_fingerprint_hash) REFERENCES tool_effects(effect_id,effect_fingerprint_hash) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE quality_reviews (
  quality_review_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  attempt_id TEXT,
  presented_output_id TEXT,
  review_scope TEXT NOT NULL CHECK (review_scope IN ('presented_output','final_job','tool_effect','policy')),
  review_round INTEGER NOT NULL CHECK (review_round>=1),
  reviewer_kind TEXT NOT NULL CHECK (reviewer_kind IN ('customer','human','model_grader','rule')),
  reviewer_ref_hash TEXT NOT NULL,
  grader_version TEXT NOT NULL,
  rubric_version TEXT NOT NULL,
  decision_record INTEGER NOT NULL CHECK (decision_record IN (0,1)),
  judged INTEGER NOT NULL CHECK (judged IN (0,1)),
  accepted INTEGER CHECK (accepted IS NULL OR accepted IN (0,1)),
  material_correction INTEGER NOT NULL CHECK (material_correction IN (0,1)),
  policy_compliant INTEGER CHECK (policy_compliant IS NULL OR policy_compliant IN (0,1)),
  critical_failure INTEGER NOT NULL CHECK (critical_failure IN (0,1)),
  severity TEXT NOT NULL CHECK (severity IN ('none','low','medium','high','critical')),
  reason_code TEXT NOT NULL,
  evidence_ref_hash TEXT NOT NULL,
  reviewed_at TEXT NOT NULL CHECK (datetime(reviewed_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (length(reviewer_ref_hash)=64 AND reviewer_ref_hash=lower(reviewer_ref_hash) AND reviewer_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(evidence_ref_hash)=64 AND evidence_ref_hash=lower(evidence_ref_hash) AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((judged=0 AND accepted IS NULL) OR judged=1),
  CHECK ((critical_failure=1 AND severity='critical') OR critical_failure=0),
  CHECK ((review_scope='presented_output' AND presented_output_id IS NOT NULL) OR review_scope<>'presented_output'),
  UNIQUE (job_id,review_scope,review_round,reviewer_kind,reviewer_ref_hash),
  FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,attempt_id) REFERENCES attempts(job_id,attempt_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,presented_output_id) REFERENCES presented_outputs(job_id,presented_output_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE UNIQUE INDEX uq_quality_decision_round ON quality_reviews(job_id,review_scope,review_round) WHERE decision_record=1;

CREATE TABLE incidents (
  incident_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  origin_code TEXT NOT NULL,
  severity TEXT NOT NULL CHECK (severity IN ('sev0','sev1','sev2','sev3','sev4')),
  affected_account_ref_hash TEXT NOT NULL,
  current_state TEXT NOT NULL CHECK (current_state IN ('open','confirmed','contained','closed','reopened')),
  first_detected_at TEXT NOT NULL CHECK (datetime(first_detected_at) IS NOT NULL),
  last_reopened_at TEXT CHECK (last_reopened_at IS NULL OR datetime(last_reopened_at) IS NOT NULL),
  CHECK (length(affected_account_ref_hash)=64 AND affected_account_ref_hash=lower(affected_account_ref_hash) AND affected_account_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (current_state<>'reopened' OR last_reopened_at IS NOT NULL),
  UNIQUE (job_id,incident_id),
  FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE incident_events (
  incident_event_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  incident_id TEXT NOT NULL,
  transition_ordinal INTEGER NOT NULL CHECK (transition_ordinal>=1),
  event_type TEXT NOT NULL CHECK (event_type IN ('opened','confirmed','contained','closed','reopened')),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  reason_code TEXT NOT NULL,
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (incident_id,transition_ordinal),
  FOREIGN KEY (incident_id) REFERENCES incidents(incident_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE human_work_logs (
  human_work_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  workflow_value_cycle_id TEXT NOT NULL,
  attempt_id TEXT,
  actor_side TEXT NOT NULL CHECK (actor_side IN ('provider','founder','customer')),
  work_class TEXT NOT NULL CHECK (work_class IN ('planned_review','correction','support','incident','founder_rescue','customer_review')),
  worker_role_code TEXT NOT NULL,
  worker_ref_hash TEXT NOT NULL,
  minutes_worked INTEGER NOT NULL CHECK (minutes_worked>0),
  internal_hourly_cost_micros INTEGER,
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (length(worker_ref_hash)=64 AND worker_ref_hash=lower(worker_ref_hash) AND worker_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((actor_side IN ('provider','founder') AND internal_hourly_cost_micros IS NOT NULL AND internal_hourly_cost_micros>=0) OR (actor_side='customer' AND internal_hourly_cost_micros IS NULL)),
  CHECK (work_class<>'founder_rescue' OR actor_side='founder'),
  CHECK (work_class<>'customer_review' OR actor_side='customer'),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (job_id,workflow_value_cycle_id) REFERENCES jobs(job_id,workflow_value_cycle_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,attempt_id) REFERENCES attempts(job_id,attempt_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE value_events (
  value_event_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  job_id TEXT NOT NULL,
  workflow_value_cycle_id TEXT NOT NULL,
  value_kind TEXT NOT NULL CHECK (value_kind IN ('verified_value','repeat_value','independent_value')),
  event_action TEXT NOT NULL CHECK (event_action IN ('confirm','revoke')),
  supersedes_event_id TEXT,
  confirmation_method TEXT NOT NULL CHECK (confirmation_method IN ('customer_confirmation','workflow_log','metric_delta','artifact_audit')),
  baseline_status TEXT NOT NULL CHECK (baseline_status IN ('observed','estimated','not_required','unknown')),
  evidence_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK ((event_action='confirm' AND supersedes_event_id IS NULL) OR (event_action='revoke' AND supersedes_event_id IS 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]*'),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (job_id,value_event_id),
  FOREIGN KEY (job_id,workflow_value_cycle_id) REFERENCES jobs(job_id,workflow_value_cycle_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id,supersedes_event_id) REFERENCES value_events(job_id,value_event_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Non-provider variable cash cost. Customer refunds are forbidden here by enum.
CREATE TABLE variable_cash_cost_ledger (
  variable_cost_line_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  account_commercial_cycle_id TEXT NOT NULL,
  job_id TEXT,
  allocation_scope TEXT NOT NULL CHECK (allocation_scope IN ('job','commercial_cycle')),
  cost_kind TEXT NOT NULL CHECK (cost_kind IN ('variable_infra','payment_fee','variable_support_cash','chargeback_loss','dispute_fee','external_api')),
  amount_micros INTEGER NOT NULL CHECK (amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  source_line_ref_hash TEXT NOT NULL UNIQUE,
  payment_dispute_event_id TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  CHECK (length(source_line_ref_hash)=64 AND source_line_ref_hash=lower(source_line_ref_hash) AND source_line_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((allocation_scope='job' AND job_id IS NOT NULL) OR (allocation_scope='commercial_cycle' AND job_id IS NULL)),
  CHECK ((cost_kind IN ('chargeback_loss','dispute_fee') AND payment_dispute_event_id IS NOT NULL) OR (cost_kind NOT IN ('chargeback_loss','dispute_fee') AND payment_dispute_event_id IS NULL)),
  FOREIGN KEY (account_commercial_cycle_id) REFERENCES account_commercial_cycles(account_commercial_cycle_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (payment_dispute_event_id) REFERENCES payment_dispute_events(payment_dispute_event_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE UNIQUE INDEX uq_cash_chargeback_principal
  ON variable_cash_cost_ledger(payment_dispute_event_id)
  WHERE cost_kind='chargeback_loss';

CREATE TABLE provider_invoice_lines (
  provider_invoice_line_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  provider_code TEXT NOT NULL,
  invoice_ref_hash TEXT NOT NULL,
  line_ref_hash TEXT NOT NULL,
  project_ref_hash TEXT NOT NULL,
  sku_code TEXT NOT NULL,
  economic_class TEXT NOT NULL CHECK (economic_class IN ('usage_charge','shared_charge','tax','provider_service_credit','contract_discount','marketplace_fee','rounding')),
  entry_action TEXT NOT NULL DEFAULT 'recognize' CHECK (entry_action IN ('recognize','reverse')),
  service_period_start TEXT NOT NULL CHECK (datetime(service_period_start) IS NOT NULL),
  service_period_end TEXT NOT NULL CHECK (datetime(service_period_end) IS NOT NULL),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  invoice_amount_micros INTEGER NOT NULL,
  corrects_invoice_line_id TEXT,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (service_period_end>service_period_start),
  CHECK (
    (entry_action='recognize' AND ((economic_class IN ('provider_service_credit','contract_discount') AND invoice_amount_micros<=0) OR (economic_class NOT IN ('provider_service_credit','contract_discount') AND invoice_amount_micros>=0)))
    OR (entry_action='reverse' AND ((economic_class IN ('provider_service_credit','contract_discount') AND invoice_amount_micros>=0) OR (economic_class NOT IN ('provider_service_credit','contract_discount') AND invoice_amount_micros<=0)))
  ),
  CHECK (entry_action='recognize' OR corrects_invoice_line_id IS NOT NULL),
  CHECK (length(invoice_ref_hash)=64 AND invoice_ref_hash=lower(invoice_ref_hash) AND invoice_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(line_ref_hash)=64 AND line_ref_hash=lower(line_ref_hash) AND line_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(project_ref_hash)=64 AND project_ref_hash=lower(project_ref_hash) AND project_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (provider_code,invoice_ref_hash,line_ref_hash),
  FOREIGN KEY (corrects_invoice_line_id) REFERENCES provider_invoice_lines(provider_invoice_line_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE UNIQUE INDEX uq_provider_invoice_reversal_target
  ON provider_invoice_lines(corrects_invoice_line_id)
  WHERE entry_action='reverse';

CREATE TABLE invoice_allocations (
  allocation_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  provider_invoice_line_id TEXT NOT NULL,
  usage_line_id TEXT,
  job_id TEXT,
  allocation_kind TEXT NOT NULL CHECK (allocation_kind IN ('direct_usage','job_shared','unallocated_charge','provider_credit_unallocated','provider_credit_reversal_unallocated','tax_unallocated','rounding_unallocated')),
  amount_micros INTEGER NOT NULL,
  allocation_rule_version TEXT NOT NULL,
  variance_reason_code TEXT,
  variance_evidence_ref_hash TEXT,
  reconciled_at TEXT NOT NULL CHECK (datetime(reconciled_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  source_event_key_hash TEXT NOT NULL UNIQUE,
  CHECK (
    (allocation_kind='direct_usage' AND usage_line_id IS NOT NULL AND job_id IS NULL AND amount_micros>=0)
    OR (allocation_kind='job_shared' AND usage_line_id IS NULL AND job_id IS NOT NULL AND amount_micros>=0)
    OR (allocation_kind IN ('unallocated_charge','tax_unallocated') AND usage_line_id IS NULL AND job_id IS NULL AND amount_micros>=0)
    OR (allocation_kind='provider_credit_unallocated' AND usage_line_id IS NULL AND job_id IS NULL AND amount_micros<=0)
    OR (allocation_kind='provider_credit_reversal_unallocated' AND usage_line_id IS NULL AND job_id IS NULL AND amount_micros>=0)
    OR (allocation_kind='rounding_unallocated' AND usage_line_id IS NULL AND job_id IS NULL)
  ),
  CHECK (length(source_event_key_hash)=64 AND source_event_key_hash=lower(source_event_key_hash) AND source_event_key_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK ((variance_reason_code IS NULL AND variance_evidence_ref_hash IS NULL) OR (variance_reason_code IS NOT NULL AND length(variance_evidence_ref_hash)=64 AND variance_evidence_ref_hash=lower(variance_evidence_ref_hash) AND variance_evidence_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (allocation_kind='direct_usage' OR (variance_reason_code IS NULL AND variance_evidence_ref_hash IS NULL)),
  FOREIGN KEY (provider_invoice_line_id) REFERENCES provider_invoice_lines(provider_invoice_line_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (usage_line_id) REFERENCES provider_usage_lines(usage_line_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

-- Cash application is not unit cost: promo credit and prepaid application are shown.
CREATE TABLE provider_settlements (
  provider_settlement_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  provider_code TEXT NOT NULL,
  invoice_ref_hash TEXT NOT NULL,
  settlement_kind TEXT NOT NULL CHECK (settlement_kind IN ('cash_payment','promotional_credit','prepaid_application')),
  amount_micros INTEGER NOT NULL CHECK (amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  settlement_ref_hash TEXT NOT NULL UNIQUE,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  ingested_at TEXT NOT NULL CHECK (datetime(ingested_at) IS NOT NULL),
  CHECK (length(invoice_ref_hash)=64 AND invoice_ref_hash=lower(invoice_ref_hash) AND invoice_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(settlement_ref_hash)=64 AND settlement_ref_hash=lower(settlement_ref_hash) AND settlement_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE TABLE metric_snapshots (
  metric_snapshot_id TEXT PRIMARY KEY,
  schema_version TEXT NOT NULL DEFAULT '1.0.0' CHECK (schema_version = '1.0.0'),
  metric_definition_version TEXT NOT NULL,
  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),
  invoice_reconciled_through TEXT CHECK (invoice_reconciled_through IS NULL OR datetime(invoice_reconciled_through) IS NOT NULL),
  restates_snapshot_id TEXT,
  restatement_reason_code TEXT,
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  CHECK ((restates_snapshot_id IS NULL AND restatement_reason_code IS NULL) OR (restates_snapshot_id IS NOT NULL AND restatement_reason_code IS NOT NULL)),
  FOREIGN KEY (restates_snapshot_id) REFERENCES metric_snapshots(metric_snapshot_id) ON UPDATE CASCADE ON DELETE RESTRICT
) STRICT;

CREATE INDEX ix_assignments_variant ON experiment_assignments(experiment_id,variant_id,account_id);
CREATE INDEX ix_value_cycles_maturity ON workflow_value_cycles(workflow_code,maturity_due_at,account_id);
CREATE INDEX ix_commercial_cycles_period ON account_commercial_cycles(account_id,billing_period_start);
CREATE INDEX ix_jobs_maturity ON jobs(feature_code,quality_mature_at,value_mature_at);
CREATE INDEX ix_opportunities_cycle ON eligible_opportunities(workflow_value_cycle_id,occurred_at);
CREATE INDEX ix_job_state_event_time ON job_state_events(job_id,state_axis,occurred_at,ingested_at);
CREATE INDEX ix_attempts_job ON attempts(job_id,attempt_ordinal);
CREATE INDEX ix_requests_job ON provider_requests(job_id,job_request_ordinal,request_role,failure_relation_type);
CREATE INDEX ix_usage_request ON provider_usage_lines(job_id,provider_request_id);
CREATE INDEX ix_tool_events ON tool_effect_events(effect_id,transition_ordinal);
CREATE UNIQUE INDEX uq_tool_external_execution_ref ON tool_effect_events(external_execution_ref_hash) WHERE external_execution_ref_hash IS NOT NULL;
CREATE INDEX ix_quality_job ON quality_reviews(job_id,review_scope,decision_record);
CREATE INDEX ix_incidents_job ON incidents(job_id,severity,current_state);
CREATE INDEX ix_human_job ON human_work_logs(job_id,actor_side,work_class);
CREATE INDEX ix_value_event_cycle ON value_events(workflow_value_cycle_id,value_kind,occurred_at);
CREATE INDEX ix_invoice_period ON provider_invoice_lines(provider_code,service_period_start,service_period_end);
CREATE INDEX ix_alloc_usage ON invoice_allocations(usage_line_id);
CREATE UNIQUE INDEX uq_direct_usage_allocation ON invoice_allocations(usage_line_id) WHERE allocation_kind='direct_usage';
CREATE INDEX ix_alloc_job ON invoice_allocations(job_id);

-- -----------------------------------------------------------------------------
-- Fictional seed
-- -----------------------------------------------------------------------------
INSERT INTO schema_registry VALUES ('ai_feature_operating_model','1.0.0','2026-08-01T00:00:00Z','../12-ai-feature-economics-quality-retention.md','HASH_OR_OPAQUE_REF_ONLY');
INSERT INTO model_settings VALUES (1,'1.0.0','2026-08-01T12:00:00Z','2026-08-01T11:45:00Z','2026-08-01T00:20:00Z',24,1000,'ai-econ-v1','USD');

INSERT INTO accounts (account_id,account_ref_hash,segment_code,plan_code,acquired_at,commercial_status,early_terminal_at,terminal_reason_code) VALUES
 ('ACC-01',printf('%064x',1),'agency_small','pilot_100','2026-05-20T00:00:00Z','active',NULL,NULL),
 ('ACC-02',printf('%064x',2),'agency_small','pilot_100','2026-05-21T00:00:00Z','churned','2026-07-20T08:00:00Z','workflow_fit'),
 ('ACC-03',printf('%064x',3),'agency_small','pilot_100','2026-05-22T00:00:00Z','active',NULL,NULL),
 ('ACC-04',printf('%064x',4),'agency_small','pilot_100','2026-05-23T00:00:00Z','active',NULL,NULL),
 ('ACC-05',printf('%064x',5),'agency_small','pilot_100','2026-05-24T00:00:00Z','churned','2026-07-10T08:00:00Z','budget'),
 ('ACC-06',printf('%064x',6),'agency_small','pilot_100','2026-05-25T00:00:00Z','active',NULL,NULL);

INSERT INTO workflow_policies VALUES ('monthly_close','1.0.0','calendar_month','customer_approval_v1',1,1,'2026-06-01T00:00:00Z',1,1);
INSERT INTO workflow_review_requirements VALUES
 ('RR-MONTHLY-PRESENTED','1.0.0','monthly_close','presented_output',1,1,'2026-06-01T00:00:00Z',NULL),
 ('RR-MONTHLY-FINAL','1.0.0','monthly_close','final_job',1,1,'2026-06-01T00:00:00Z',NULL);
INSERT INTO experiments VALUES ('EXP-001','1.0.0','monthly_narrative','monthly_close','randomized','account',printf('%064x',10),'2026-06-01T00:00:00Z','2026-08-31T23:59:59Z','running');
INSERT INTO experiment_variants (variant_id,experiment_id,variant_code,route_policy_code,intended_weight_ppm) VALUES
 ('VAR-AI','EXP-001','ai_assisted','ai_then_fallback',500000),
 ('VAR-RULE','EXP-001','deterministic_control','rules_only',500000);
INSERT INTO experiment_assignments (assignment_id,experiment_id,variant_id,account_id,assignment_ref_hash,eligible_at,assigned_at,followup_time_zero_at,ingested_at,assignment_method) VALUES
 ('ASN-01','EXP-001','VAR-AI','ACC-01',printf('%064x',101),'2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:01:05Z','randomized'),
 ('ASN-02','EXP-001','VAR-AI','ACC-02',printf('%064x',102),'2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:01:05Z','randomized'),
 ('ASN-03','EXP-001','VAR-AI','ACC-03',printf('%064x',103),'2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:01:05Z','randomized'),
 ('ASN-04','EXP-001','VAR-RULE','ACC-04',printf('%064x',104),'2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:01:05Z','randomized'),
 ('ASN-05','EXP-001','VAR-RULE','ACC-05',printf('%064x',105),'2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:01:05Z','randomized'),
 ('ASN-06','EXP-001','VAR-RULE','ACC-06',printf('%064x',106),'2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:00:00Z','2026-06-01T00:01:05Z','randomized');

INSERT INTO workflow_cycle_calendar VALUES
 ('monthly_close',1,'2026-06-01T00:00:00Z','2026-06-30T23:59:58Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z'),
 ('monthly_close',2,'2026-07-01T00:00:00Z','2026-07-31T23:59:58Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z');

WITH cycle_spec(cycle_number,cycle_start,cycle_end,due_at) AS (
  VALUES
    (1,'2026-06-01T00:00:00Z','2026-06-30T23:59:58Z','2026-06-30T23:59:59Z'),
    (2,'2026-07-01T00:00:00Z','2026-07-31T23:59:58Z','2026-07-31T23:59:59Z')
)
INSERT INTO workflow_value_cycles (
  workflow_value_cycle_id,account_id,workflow_code,cycle_number,natural_cycle_start,
  natural_cycle_end,on_time_due_at,maturity_due_at,matured_at,early_terminal_at,
  terminal_reason_code,reopened_at,cycle_state,exclusion_reason_code
)
SELECT
  'WVC-'||a.account_id||'-'||c.cycle_number,a.account_id,'monthly_close',c.cycle_number,
  c.cycle_start,c.cycle_end,c.due_at,c.due_at,
  CASE
    WHEN c.cycle_number=2 AND a.account_id='ACC-02' THEN '2026-07-20T08:00:00Z'
    WHEN c.cycle_number=2 AND a.account_id='ACC-05' THEN '2026-07-10T08:00:00Z'
    WHEN c.cycle_number=2 THEN '2026-08-01T00:00:00Z'
    ELSE c.due_at
  END,
  CASE
    WHEN c.cycle_number=2 AND a.account_id='ACC-02' THEN '2026-07-20T08:00:00Z'
    WHEN c.cycle_number=2 AND a.account_id='ACC-05' THEN '2026-07-10T08:00:00Z'
    ELSE NULL
  END,
  CASE
    WHEN c.cycle_number=2 AND a.account_id='ACC-02' THEN 'workflow_fit'
    WHEN c.cycle_number=2 AND a.account_id='ACC-05' THEN 'budget'
    ELSE NULL
  END,
  NULL,
  CASE WHEN c.cycle_number=2 AND a.account_id IN ('ACC-02','ACC-05') THEN 'early_terminal' ELSE 'matured' END,
  NULL
FROM accounts a CROSS JOIN cycle_spec c;

INSERT INTO workflow_cycle_experiment_assignments (
  cycle_experiment_assignment_id,workflow_value_cycle_id,experiment_id,
  assignment_id,account_id,mapping_rule_version,mapped_at
)
SELECT 'WCX-'||c.workflow_value_cycle_id||'-'||a.experiment_id,
       c.workflow_value_cycle_id,a.experiment_id,a.assignment_id,c.account_id,
       'predeclared-account-assignment-v1','2026-06-01T00:02:00Z'
FROM workflow_value_cycles c
JOIN experiment_assignments a
  ON a.account_id=c.account_id AND a.experiment_id='EXP-001';

WITH cycle_spec(cycle_number,cycle_start,cycle_end) AS (
  VALUES
    (1,'2026-06-01T00:00:00Z','2026-07-01T00:00:00Z'),
    (2,'2026-07-01T00:00:00Z','2026-08-01T00:00:00Z')
)
INSERT INTO account_commercial_cycles (
  account_commercial_cycle_id,account_id,contract_ref_hash,billing_period_start,
  billing_period_end,currency,commercial_state,closed_at,reopened_at
)
SELECT
  'CC-'||a.account_id||'-'||c.cycle_number,a.account_id,
  printf('%064x',800+CAST(substr(a.account_id,-2) AS INTEGER)),
  c.cycle_start,c.cycle_end,'USD','closed',
  CASE
    WHEN c.cycle_number=2 AND a.account_id='ACC-02' THEN '2026-07-20T08:00:00Z'
    WHEN c.cycle_number=2 AND a.account_id='ACC-05' THEN '2026-07-10T08:00:00Z'
    ELSE c.cycle_end
  END,
  NULL
FROM accounts a CROSS JOIN cycle_spec c;

INSERT INTO workflow_commercial_cycle_map (
  mapping_id,workflow_value_cycle_id,account_commercial_cycle_id,account_id
)
SELECT 'MAP-'||workflow_value_cycle_id,workflow_value_cycle_id,
       replace(workflow_value_cycle_id,'WVC-','CC-'),account_id
FROM workflow_value_cycles;

INSERT INTO feature_revenue_allocations (
  feature_revenue_allocation_id,workflow_value_cycle_id,account_commercial_cycle_id,
  feature_code,allocation_status,allocated_feature_net_revenue_micros,currency,
  allocation_rule_version,allocated_at,ingested_at,source_event_key_hash
)
SELECT 'FRA-'||m.workflow_value_cycle_id,m.workflow_value_cycle_id,
       m.account_commercial_cycle_id,'monthly_narrative','unallocated',NULL,'USD',
       NULL,'2026-08-01T00:30:00Z','2026-08-01T00:30:10Z',
       printf('%064x',850+row_number() OVER (ORDER BY m.workflow_value_cycle_id))
FROM workflow_commercial_cycle_map m;

INSERT INTO customer_revenue_ledger (
  customer_revenue_line_id,account_commercial_cycle_id,account_id,line_kind,
  amount_micros,currency,source_line_ref_hash,corrects_line_id,occurred_at,ingested_at
)
SELECT 'REV-GROSS-'||account_commercial_cycle_id,account_commercial_cycle_id,account_id,
       'gross_revenue',100000000,'USD',
       printf('%064x',900+row_number() OVER (ORDER BY account_commercial_cycle_id)),NULL,
       billing_period_start,strftime('%Y-%m-%dT%H:%M:%SZ',billing_period_start,'+1 minute')
FROM account_commercial_cycles;

INSERT INTO customer_revenue_ledger (
  customer_revenue_line_id,account_commercial_cycle_id,account_id,line_kind,
  amount_micros,currency,source_line_ref_hash,corrects_line_id,occurred_at,ingested_at
) VALUES (
  'REV-REFUND-ACC-02-2','CC-ACC-02-2','ACC-02','customer_refund',-20000000,'USD',
  printf('%064x',950),NULL,'2026-07-20T08:00:00Z','2026-07-20T08:01:00Z'
);

INSERT INTO payment_dispute_events (
  payment_dispute_event_id,account_commercial_cycle_id,account_id,
  principal_amount_micros,principal_treatment,currency,payment_transaction_ref_hash,
  occurred_at,ingested_at
) VALUES (
  'DISPUTE-ACC-05-1','CC-ACC-05-1','ACC-05',5000000,'contra_revenue','USD',
  printf('%064x',951),'2026-06-25T10:00:00Z','2026-06-25T10:00:10Z'
);

INSERT INTO customer_revenue_ledger (
  customer_revenue_line_id,account_commercial_cycle_id,account_id,line_kind,
  amount_micros,currency,source_line_ref_hash,payment_dispute_event_id,
  corrects_line_id,occurred_at,ingested_at
) VALUES (
  'REV-CHARGEBACK-ACC-05-1','CC-ACC-05-1','ACC-05','chargeback_principal',
  -5000000,'USD',printf('%064x',952),'DISPUTE-ACC-05-1',NULL,
  '2026-06-25T10:00:00Z','2026-06-25T10:00:10Z'
);

-- Append-only correction pair: gross/contra components remain attributable even
-- though the recognized refund and its later reversal net to zero.
INSERT INTO customer_revenue_ledger (
  customer_revenue_line_id,account_commercial_cycle_id,account_id,line_kind,entry_action,
  amount_micros,currency,source_line_ref_hash,payment_dispute_event_id,
  corrects_line_id,occurred_at,ingested_at
) VALUES
 ('REV-REFUND-TEST','CC-ACC-01-1','ACC-01','customer_refund','recognize',-1000000,'USD',printf('%064x',953),NULL,NULL,'2026-06-10T10:00:00Z','2026-06-10T10:00:10Z'),
 ('REV-REFUND-TEST-REV','CC-ACC-01-1','ACC-01','customer_refund','reverse',1000000,'USD',printf('%064x',954),NULL,'REV-REFUND-TEST','2026-06-11T10:00:00Z','2026-06-11T10:00:10Z');

INSERT INTO eligible_opportunities (
  eligible_opportunity_id,opportunity_ref_hash,account_id,workflow_value_cycle_id,
  feature_code,workflow_code,job_type_code,occurred_at,ingested_at,source_event_key_hash
) VALUES
 ('OPP-01',printf('%064x',1001),'ACC-01','WVC-ACC-01-1','monthly_narrative','monthly_close','narrative_pack','2026-06-03T09:00:00Z','2026-06-03T09:00:10Z',printf('%064x',1051)),
 ('OPP-02',printf('%064x',1002),'ACC-01','WVC-ACC-01-2','monthly_narrative','monthly_close','narrative_pack','2026-07-03T09:00:00Z','2026-07-03T09:00:10Z',printf('%064x',1052)),
 ('OPP-03',printf('%064x',1003),'ACC-02','WVC-ACC-02-1','monthly_narrative','monthly_close','narrative_pack','2026-06-05T09:00:00Z','2026-06-05T09:00:10Z',printf('%064x',1053)),
 ('OPP-04',printf('%064x',1004),'ACC-02','WVC-ACC-02-2','monthly_narrative','monthly_close','narrative_pack','2026-07-05T09:00:00Z','2026-07-05T09:00:10Z',printf('%064x',1054)),
 ('OPP-05',printf('%064x',1005),'ACC-03','WVC-ACC-03-1','monthly_narrative','monthly_close','narrative_pack','2026-06-20T09:00:00Z','2026-06-20T09:00:10Z',printf('%064x',1055)),
 ('OPP-06',printf('%064x',1006),'ACC-03','WVC-ACC-03-2','monthly_narrative','monthly_close','narrative_pack','2026-07-20T09:00:00Z','2026-07-20T09:00:10Z',printf('%064x',1056)),
 ('OPP-07',printf('%064x',1007),'ACC-04','WVC-ACC-04-1','monthly_narrative','monthly_close','narrative_pack','2026-06-04T09:00:00Z','2026-06-04T09:00:10Z',printf('%064x',1057)),
 ('OPP-08',printf('%064x',1008),'ACC-04','WVC-ACC-04-2','monthly_narrative','monthly_close','narrative_pack','2026-07-04T09:00:00Z','2026-07-04T09:00:10Z',printf('%064x',1058)),
 ('OPP-09',printf('%064x',1009),'ACC-05','WVC-ACC-05-1','monthly_narrative','monthly_close','narrative_pack','2026-06-10T09:00:00Z','2026-06-10T09:00:10Z',printf('%064x',1059)),
 ('OPP-10',printf('%064x',1010),'ACC-05','WVC-ACC-05-2','monthly_narrative','monthly_close','narrative_pack','2026-07-05T09:00:00Z','2026-07-05T09:00:10Z',printf('%064x',1060)),
 ('OPP-11',printf('%064x',1011),'ACC-06','WVC-ACC-06-1','monthly_narrative','monthly_close','narrative_pack','2026-06-15T09:00:00Z','2026-06-15T09:00:10Z',printf('%064x',1061)),
 ('OPP-12',printf('%064x',1012),'ACC-06','WVC-ACC-06-2','monthly_narrative','monthly_close','narrative_pack','2026-07-15T09:00:00Z','2026-07-15T09:00:10Z',printf('%064x',1062)),
 ('OPP-13',printf('%064x',1013),'ACC-06','WVC-ACC-06-2','monthly_narrative','monthly_close','narrative_pack','2026-07-18T09:00:00Z','2026-07-18T09:00:10Z',printf('%064x',1063));

INSERT INTO jobs (
  job_id,eligible_opportunity_id,opportunity_ref_hash,account_id,workflow_value_cycle_id,
  assignment_id,feature_code,workflow_code,job_type_code,segment_code,risk_tier,
  eligible_at,offered_at,exposed_at,started_at,execution_due_at,quality_due_at,value_due_at,incident_observation_due_at,
  execution_state,acceptance_state,value_state,incident_state,
  execution_mature_at,quality_mature_at,value_mature_at,incident_mature_at,reopened_quality_at,
  reopened_value_at,reopened_incident_at,excluded_at,exclusion_reason_code
) VALUES
 ('JOB-01','OPP-01',printf('%064x',1001),'ACC-01','WVC-ACC-01-1','ASN-01','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-06-03T09:00:00Z','2026-06-03T09:01:00Z','2026-06-03T09:01:00Z','2026-06-03T09:02:00Z','2026-06-07T23:59:59Z','2026-06-08T23:59:59Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','completed','accepted_first_pass','confirmed','none_observed','2026-06-03T09:10:00Z','2026-06-03T09:10:00Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-02','OPP-02',printf('%064x',1002),'ACC-01','WVC-ACC-01-2','ASN-01','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-07-03T09:00:00Z','2026-07-03T09:01:00Z','2026-07-03T09:01:00Z','2026-07-03T09:02:00Z','2026-07-07T23:59:59Z','2026-07-08T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','completed','accepted_first_pass','confirmed','none_observed','2026-07-03T09:10:00Z','2026-07-03T09:10:00Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-03','OPP-03',printf('%064x',1003),'ACC-02','WVC-ACC-02-1','ASN-02','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-06-05T09:00:00Z','2026-06-05T09:01:00Z','2026-06-05T09:01:00Z','2026-06-05T09:02:00Z','2026-06-09T23:59:59Z','2026-06-10T23:59:59Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','completed','accepted_after_correction','confirmed','none_observed','2026-06-05T09:30:00Z','2026-06-05T09:30:00Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-04','OPP-04',printf('%064x',1004),'ACC-02','WVC-ACC-02-2','ASN-02','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-07-05T09:00:00Z','2026-07-05T09:01:00Z','2026-07-05T09:01:00Z','2026-07-05T09:02:00Z','2026-07-09T23:59:59Z','2026-07-10T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','abandoned','not_assessable','not_confirmed','none_observed','2026-07-05T09:04:00Z','2026-07-10T23:59:59Z','2026-07-20T08:00:00Z','2026-07-31T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-05','OPP-05',printf('%064x',1005),'ACC-03','WVC-ACC-03-1','ASN-03','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-06-20T09:00:00Z','2026-06-20T09:01:00Z','2026-06-20T09:01:00Z','2026-06-20T09:02:00Z','2026-06-24T23:59:59Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','completed','accepted_after_correction','confirmed','closed','2026-06-20T09:30:00Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','2026-07-02T10:30:00Z',NULL,'2026-07-02T10:00:00Z','2026-07-02T10:05:00Z',NULL,NULL),
 ('JOB-06','OPP-06',printf('%064x',1006),'ACC-03','WVC-ACC-03-2','ASN-03','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-07-20T09:00:00Z','2026-07-20T09:01:00Z','2026-07-20T09:01:00Z','2026-07-20T09:02:00Z','2026-07-24T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','completed','accepted_after_correction','confirmed','none_observed','2026-07-20T09:30:00Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z',NULL,'2026-08-01T08:00:00Z',NULL,NULL,NULL),
 ('JOB-07','OPP-07',printf('%064x',1007),'ACC-04','WVC-ACC-04-1','ASN-04','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-06-04T09:00:00Z','2026-06-04T09:01:00Z','2026-06-04T09:01:00Z','2026-06-04T09:02:00Z','2026-06-08T23:59:59Z','2026-06-09T23:59:59Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','completed','accepted_first_pass','confirmed','none_observed','2026-06-04T09:08:00Z','2026-06-04T09:08:00Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-08','OPP-08',printf('%064x',1008),'ACC-04','WVC-ACC-04-2','ASN-04','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-07-04T09:00:00Z','2026-07-04T09:01:00Z','2026-07-04T09:01:00Z','2026-07-04T09:02:00Z','2026-07-08T23:59:59Z','2026-07-09T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','completed','accepted_first_pass','confirmed','none_observed','2026-07-04T09:08:00Z','2026-07-04T09:08:00Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-09','OPP-09',printf('%064x',1009),'ACC-05','WVC-ACC-05-1','ASN-05','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-06-10T09:00:00Z','2026-06-10T09:01:00Z','2026-06-10T09:01:00Z','2026-06-10T09:02:00Z','2026-06-14T23:59:59Z','2026-06-15T23:59:59Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','completed','rejected','not_confirmed','none_observed','2026-06-10T09:20:00Z','2026-06-10T09:20:00Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-10','OPP-10',printf('%064x',1010),'ACC-05','WVC-ACC-05-2','ASN-05','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-07-05T09:00:00Z','2026-07-05T09:01:00Z','2026-07-05T09:01:00Z',NULL,'2026-07-09T23:59:59Z','2026-07-10T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','expired','not_assessable','not_confirmed','none_observed','2026-07-09T23:59:59Z','2026-07-10T23:59:59Z','2026-07-10T08:00:00Z','2026-07-31T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-11','OPP-11',printf('%064x',1011),'ACC-06','WVC-ACC-06-1','ASN-06','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-06-15T09:00:00Z','2026-06-15T09:01:00Z','2026-06-15T09:01:00Z','2026-06-15T09:02:00Z','2026-06-19T23:59:59Z','2026-06-20T23:59:59Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z','completed','accepted_first_pass','confirmed','none_observed','2026-06-15T09:08:00Z','2026-06-15T09:08:00Z','2026-06-30T23:59:59Z','2026-06-30T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-12','OPP-12',printf('%064x',1012),'ACC-06','WVC-ACC-06-2','ASN-06','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-07-15T09:00:00Z','2026-07-15T09:01:00Z','2026-07-15T09:01:00Z',NULL,'2026-07-19T23:59:59Z','2026-07-20T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','expired','not_assessable','not_confirmed','none_observed','2026-07-19T23:59:59Z','2026-07-20T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z',NULL,NULL,NULL,NULL,NULL),
 ('JOB-13','OPP-13',printf('%064x',1013),'ACC-06','WVC-ACC-06-2','ASN-06','monthly_narrative','monthly_close','narrative_pack','agency_small','medium','2026-07-18T09:00:00Z','2026-07-18T09:01:00Z','2026-07-18T09:01:00Z','2026-07-18T09:02:00Z','2026-07-22T23:59:59Z','2026-07-23T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z','completed','unknown','unknown','none_observed','2026-07-18T09:08:00Z','2026-07-23T23:59:59Z','2026-07-31T23:59:59Z','2026-07-31T23:59:59Z',NULL,NULL,NULL,NULL,NULL);

INSERT INTO job_state_events (job_state_event_id,job_id,state_axis,state_code,event_phase,reason_code,restates_event_id,occurred_at,ingested_at,source_event_key_hash)
SELECT 'JSE-X-'||job_id,job_id,'execution',execution_state,'matured','execution_window_closed',NULL,execution_mature_at,strftime('%Y-%m-%dT%H:%M:%SZ',execution_mature_at,'+1 minute'),printf('%064x',1100+row_number() OVER (ORDER BY job_id)) FROM jobs;
INSERT INTO job_state_events (job_state_event_id,job_id,state_axis,state_code,event_phase,reason_code,restates_event_id,occurred_at,ingested_at,source_event_key_hash)
SELECT 'JSE-Q-'||job_id,job_id,'acceptance',acceptance_state,'matured','quality_window_closed',NULL,quality_mature_at,strftime('%Y-%m-%dT%H:%M:%SZ',quality_mature_at,'+1 minute'),printf('%064x',1200+row_number() OVER (ORDER BY job_id)) FROM jobs WHERE job_id NOT IN ('JOB-05','JOB-06');
INSERT INTO job_state_events (job_state_event_id,job_id,state_axis,state_code,event_phase,reason_code,restates_event_id,occurred_at,ingested_at,source_event_key_hash)
SELECT 'JSE-V-'||job_id,job_id,'value',value_state,'matured','value_window_closed',NULL,value_mature_at,strftime('%Y-%m-%dT%H:%M:%SZ',value_mature_at,'+1 minute'),printf('%064x',1300+row_number() OVER (ORDER BY job_id)) FROM jobs WHERE job_id NOT IN ('JOB-05','JOB-06');
INSERT INTO job_state_events (job_state_event_id,job_id,state_axis,state_code,event_phase,reason_code,restates_event_id,occurred_at,ingested_at,source_event_key_hash)
SELECT 'JSE-I-'||job_id,job_id,'incident',incident_state,'matured','incident_observation_window_closed',NULL,incident_mature_at,strftime('%Y-%m-%dT%H:%M:%SZ',incident_mature_at,'+1 minute'),printf('%064x',1400+row_number() OVER (ORDER BY job_id)) FROM jobs WHERE job_id<>'JOB-05';

INSERT INTO job_state_events (job_state_event_id,job_id,state_axis,state_code,event_phase,reason_code,restates_event_id,occurred_at,ingested_at,source_event_key_hash) VALUES
 ('JSE-Q0-JOB-05','JOB-05','acceptance','unknown','matured','review_not_arrived',NULL,'2026-06-30T23:59:59Z','2026-07-01T00:01:00Z',printf('%064x',1251)),
 ('JSE-QR-JOB-05','JOB-05','acceptance','unknown','reopened','late_review_arrived',NULL,'2026-07-02T10:00:00Z','2026-07-02T10:00:30Z',printf('%064x',1252)),
 ('JSE-Q1-JOB-05','JOB-05','acceptance','accepted_after_correction','restated','late_review_restatement','JSE-Q0-JOB-05','2026-07-02T10:01:00Z','2026-07-02T10:01:00Z',printf('%064x',1253)),
 ('JSE-Q0-JOB-06','JOB-06','acceptance','unknown','matured','review_not_arrived',NULL,'2026-07-31T23:59:59Z','2026-08-01T00:01:00Z',printf('%064x',1261)),
 ('JSE-QR-JOB-06','JOB-06','acceptance','unknown','reopened','eventual_review_arrived',NULL,'2026-08-01T08:00:00Z','2026-08-01T08:00:10Z',printf('%064x',1262)),
 ('JSE-Q1-JOB-06','JOB-06','acceptance','accepted_after_correction','restated','eventual_review_restatement','JSE-Q0-JOB-06','2026-08-01T08:00:00Z','2026-08-01T08:00:20Z',printf('%064x',1263)),
 ('JSE-V0-JOB-05','JOB-05','value','unknown','matured','evidence_not_arrived',NULL,'2026-06-30T23:59:59Z','2026-07-01T00:01:00Z',printf('%064x',1351)),
 ('JSE-VR-JOB-05','JOB-05','value','reopened','reopened','late_customer_evidence',NULL,'2026-07-02T10:00:00Z','2026-07-02T10:00:30Z',printf('%064x',1352)),
 ('JSE-V1-JOB-05','JOB-05','value','confirmed','restated','late_event_restatement','JSE-V0-JOB-05','2026-07-02T10:01:00Z','2026-07-02T10:01:00Z',printf('%064x',1353)),
 ('JSE-V0-JOB-06','JOB-06','value','not_confirmed','matured','evidence_not_arrived',NULL,'2026-07-31T23:59:59Z','2026-08-01T00:01:00Z',printf('%064x',1361)),
 ('JSE-VR-JOB-06','JOB-06','value','reopened','reopened','eventual_value_arrived',NULL,'2026-08-01T08:00:00Z','2026-08-01T08:00:10Z',printf('%064x',1362)),
 ('JSE-V1-JOB-06','JOB-06','value','confirmed','restated','eventual_value_restatement','JSE-V0-JOB-06','2026-08-01T08:05:00Z','2026-08-01T08:05:10Z',printf('%064x',1363));

UPDATE jobs SET reopened_quality_at='2026-07-02T10:00:00Z' WHERE job_id='JOB-05';
UPDATE jobs SET reopened_quality_at='2026-08-01T08:00:00Z' WHERE job_id='JOB-06';

INSERT INTO processing_routes (route_id,route_kind,provider_code,product_code,model_snapshot_code,prompt_version,retrieval_version,tool_policy_version,safety_policy_version,effective_from,effective_to) VALUES
 ('ROUTE-OAI','ai','openai','responses','fictional-oai-snapshot','prompt-v3','retrieval-v2','tool-v4','safety-v5','2026-06-01T00:00:00Z',NULL),
 ('ROUTE-ANT','ai','anthropic','messages','fictional-ant-snapshot','prompt-v3','retrieval-v2','tool-v4','safety-v5','2026-06-01T00:00:00Z',NULL),
 ('ROUTE-RULE','deterministic',NULL,NULL,NULL,'rules-v7','none','no-tools','safety-v5','2026-06-01T00:00:00Z',NULL);

INSERT INTO attempts (attempt_id,job_id,attempt_ordinal,parent_attempt_id,attempt_role,reason_code,route_id,idempotency_key_hash,started_at,finished_at,ingested_at,run_state) VALUES
 ('ATT-01','JOB-01',1,NULL,'initial',NULL,'ROUTE-OAI',printf('%064x',2001),'2026-06-03T09:02:00Z','2026-06-03T09:04:00Z','2026-06-03T09:04:10Z','completed'),
 ('ATT-02','JOB-02',1,NULL,'initial',NULL,'ROUTE-OAI',printf('%064x',2002),'2026-07-03T09:02:00Z','2026-07-03T09:04:00Z','2026-07-03T09:04:10Z','completed'),
 ('ATT-03','JOB-03',1,NULL,'initial',NULL,'ROUTE-OAI',printf('%064x',2003),'2026-06-05T09:02:00Z','2026-06-05T09:05:00Z','2026-06-05T09:05:10Z','failed'),
 ('ATT-05','JOB-05',1,NULL,'initial',NULL,'ROUTE-OAI',printf('%064x',2005),'2026-06-20T09:02:00Z','2026-06-20T09:05:00Z','2026-06-20T09:05:10Z','failed'),
 ('ATT-07','JOB-06',1,NULL,'initial',NULL,'ROUTE-OAI',printf('%064x',2007),'2026-07-20T09:02:00Z','2026-07-20T09:08:00Z','2026-07-20T09:08:10Z','completed'),
 ('ATT-08','JOB-07',1,NULL,'initial',NULL,'ROUTE-RULE',printf('%064x',2008),'2026-06-04T09:02:00Z','2026-06-04T09:03:00Z','2026-06-04T09:03:10Z','completed'),
 ('ATT-09','JOB-08',1,NULL,'initial',NULL,'ROUTE-RULE',printf('%064x',2009),'2026-07-04T09:02:00Z','2026-07-04T09:03:00Z','2026-07-04T09:03:10Z','completed'),
 ('ATT-10','JOB-09',1,NULL,'initial',NULL,'ROUTE-RULE',printf('%064x',2010),'2026-06-10T09:02:00Z','2026-06-10T09:03:00Z','2026-06-10T09:03:10Z','completed'),
 ('ATT-11','JOB-11',1,NULL,'initial',NULL,'ROUTE-RULE',printf('%064x',2011),'2026-06-15T09:02:00Z','2026-06-15T09:03:00Z','2026-06-15T09:03:10Z','completed'),
 ('ATT-12','JOB-13',1,NULL,'initial',NULL,'ROUTE-RULE',printf('%064x',2012),'2026-07-18T09:02:00Z','2026-07-18T09:03:00Z','2026-07-18T09:03:10Z','completed');
INSERT INTO attempts (attempt_id,job_id,attempt_ordinal,parent_attempt_id,attempt_role,reason_code,route_id,idempotency_key_hash,started_at,finished_at,ingested_at,run_state) VALUES
 ('ATT-04','JOB-03',2,'ATT-03','reroute','primary_quality_fail','ROUTE-ANT',printf('%064x',2004),'2026-06-05T09:05:10Z','2026-06-05T09:09:00Z','2026-06-05T09:09:10Z','completed'),
 ('ATT-06','JOB-05',2,'ATT-05','reroute','primary_quality_fail','ROUTE-ANT',printf('%064x',2006),'2026-06-20T09:05:10Z','2026-06-20T09:08:00Z','2026-06-20T09:08:10Z','completed');

INSERT INTO presented_outputs (presented_output_id,job_id,attempt_id,presentation_ordinal,content_version_hash,committed_to_customer,presented_at,ingested_at,source_event_key_hash) VALUES
 ('OUT-01-1','JOB-01','ATT-01',1,printf('%064x',2101),1,'2026-06-03T09:05:00Z','2026-06-03T09:05:10Z',printf('%064x',2201)),
 ('OUT-02-1','JOB-02','ATT-02',1,printf('%064x',2102),1,'2026-07-03T09:05:00Z','2026-07-03T09:05:10Z',printf('%064x',2202)),
 ('OUT-03-1','JOB-03','ATT-04',1,printf('%064x',2103),1,'2026-06-05T09:09:00Z','2026-06-05T09:09:10Z',printf('%064x',2203)),
 ('OUT-03-2','JOB-03','ATT-04',2,printf('%064x',2104),1,'2026-06-05T09:25:00Z','2026-06-05T09:25:10Z',printf('%064x',2204)),
 ('OUT-05-1','JOB-05','ATT-06',1,printf('%064x',2105),1,'2026-06-20T09:09:00Z','2026-06-20T09:09:10Z',printf('%064x',2205)),
 ('OUT-05-2','JOB-05','ATT-06',2,printf('%064x',2106),1,'2026-06-29T10:00:00Z','2026-07-02T10:00:00Z',printf('%064x',2206)),
 ('OUT-06-1','JOB-06','ATT-07',1,printf('%064x',2107),1,'2026-07-20T09:09:00Z','2026-07-20T09:09:10Z',printf('%064x',2207)),
 ('OUT-06-2','JOB-06','ATT-07',2,printf('%064x',2108),1,'2026-08-01T08:00:00Z','2026-08-01T08:00:10Z',printf('%064x',2208)),
 ('OUT-07-1','JOB-07','ATT-08',1,printf('%064x',2109),1,'2026-06-04T09:04:00Z','2026-06-04T09:04:10Z',printf('%064x',2209)),
 ('OUT-08-1','JOB-08','ATT-09',1,printf('%064x',2110),1,'2026-07-04T09:04:00Z','2026-07-04T09:04:10Z',printf('%064x',2210)),
 ('OUT-09-1','JOB-09','ATT-10',1,printf('%064x',2111),1,'2026-06-10T09:04:00Z','2026-06-10T09:04:10Z',printf('%064x',2211)),
 ('OUT-11-1','JOB-11','ATT-11',1,printf('%064x',2112),1,'2026-06-15T09:04:00Z','2026-06-15T09:04:10Z',printf('%064x',2212)),
 ('OUT-13-1','JOB-13','ATT-12',1,printf('%064x',2113),1,'2026-07-18T09:04:00Z','2026-07-18T09:04:10Z',printf('%064x',2213));

INSERT INTO provider_requests (
  provider_request_id,job_id,attempt_id,job_request_ordinal,attempt_request_ordinal,
  request_role,failure_relation_type,retry_of_request_id,fallback_from_request_id,repair_of_request_id,provider_code,product_code,
  model_snapshot_code,billing_channel_code,provider_request_ref_hash,idempotency_key_hash,sdk_name,
  sdk_version,sdk_retry_config_ref_hash,request_status,error_class,started_at,finished_at,ingested_at
) VALUES
 ('REQ-01','JOB-01','ATT-01',1,1,'primary','none',NULL,NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2301),printf('%064x',2401),'openai-js','v9',printf('%064x',2501),'completed',NULL,'2026-06-03T09:02:00Z','2026-06-03T09:04:00Z','2026-06-03T09:04:10Z'),
 ('REQ-02','JOB-02','ATT-02',1,1,'primary','none',NULL,NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2302),printf('%064x',2402),'openai-js','v9',printf('%064x',2501),'completed',NULL,'2026-07-03T09:02:00Z','2026-07-03T09:04:00Z','2026-07-03T09:04:10Z'),
 ('REQ-03','JOB-03','ATT-03',1,1,'primary','none',NULL,NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2303),printf('%064x',2403),'openai-js','v9',printf('%064x',2501),'failed','quality_validation','2026-06-05T09:02:00Z','2026-06-05T09:05:00Z','2026-06-05T09:05:10Z'),
 ('REQ-05','JOB-05','ATT-05',1,1,'primary','none',NULL,NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2305),printf('%064x',2405),'openai-js','v9',printf('%064x',2501),'failed','quality_validation','2026-06-20T09:02:00Z','2026-06-20T09:05:00Z','2026-06-20T09:05:10Z'),
 ('REQ-07','JOB-06','ATT-07',1,1,'primary','none',NULL,NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2307),printf('%064x',2407),'openai-js','v9',printf('%064x',2502),'failed','schema_invalid','2026-07-20T09:02:00Z','2026-07-20T09:05:00Z','2026-07-20T09:05:10Z');
INSERT INTO provider_requests (
  provider_request_id,job_id,attempt_id,job_request_ordinal,attempt_request_ordinal,
  request_role,failure_relation_type,retry_of_request_id,fallback_from_request_id,repair_of_request_id,provider_code,product_code,
  model_snapshot_code,billing_channel_code,provider_request_ref_hash,idempotency_key_hash,sdk_name,
  sdk_version,sdk_retry_config_ref_hash,request_status,error_class,started_at,finished_at,ingested_at
) VALUES
 ('REQ-04','JOB-03','ATT-04',2,1,'primary','fallback',NULL,'REQ-03',NULL,'anthropic','messages','fictional-ant-snapshot','direct_api',printf('%064x',2304),printf('%064x',2404),'anthropic-js','v4',printf('%064x',2503),'completed',NULL,'2026-06-05T09:05:10Z','2026-06-05T09:08:00Z','2026-06-05T09:08:10Z'),
 ('REQ-06','JOB-05','ATT-06',2,1,'primary','fallback',NULL,'REQ-05',NULL,'anthropic','messages','fictional-ant-snapshot','direct_api',printf('%064x',2306),printf('%064x',2406),'anthropic-js','v4',printf('%064x',2503),'completed',NULL,'2026-06-20T09:05:10Z','2026-06-20T09:08:00Z','2026-06-20T09:08:10Z'),
 ('REQ-08','JOB-06','ATT-07',2,2,'primary','app_retry','REQ-07',NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2308),printf('%064x',2408),'openai-js','v9',printf('%064x',2502),'completed',NULL,'2026-07-20T09:05:10Z','2026-07-20T09:08:00Z','2026-07-20T09:08:10Z');

-- Planned auxiliary and validation requests are purpose roles, not failure waste.
-- They only enter the waste numerator if failure_relation_type is non-'none'.
INSERT INTO provider_requests (
  provider_request_id,job_id,attempt_id,job_request_ordinal,attempt_request_ordinal,
  request_role,failure_relation_type,retry_of_request_id,fallback_from_request_id,repair_of_request_id,provider_code,product_code,
  model_snapshot_code,billing_channel_code,provider_request_ref_hash,idempotency_key_hash,sdk_name,
  sdk_version,sdk_retry_config_ref_hash,request_status,error_class,started_at,finished_at,ingested_at
) VALUES
 ('REQ-09','JOB-01','ATT-01',2,2,'planned_auxiliary','none',NULL,NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2309),printf('%064x',2409),'openai-js','v9',printf('%064x',2501),'completed',NULL,'2026-06-03T09:02:30Z','2026-06-03T09:03:00Z','2026-06-03T09:03:10Z'),
 ('REQ-10','JOB-02','ATT-02',2,2,'validation','none',NULL,NULL,NULL,'openai','responses','fictional-oai-snapshot','direct_api',printf('%064x',2310),printf('%064x',2410),'openai-js','v9',printf('%064x',2501),'completed',NULL,'2026-07-03T09:02:30Z','2026-07-03T09:03:00Z','2026-07-03T09:03:10Z'),
 ('REQ-11','JOB-03','ATT-04',3,2,'validation','repair',NULL,NULL,'REQ-04','anthropic','messages','fictional-ant-snapshot','direct_api',printf('%064x',2311),printf('%064x',2411),'anthropic-js','v4',printf('%064x',2503),'completed',NULL,'2026-06-05T09:08:00Z','2026-06-05T09:08:50Z','2026-06-05T09:09:00Z');

INSERT INTO provider_price_book (
  price_id,provider_code,product_code,model_snapshot_code,sku_code,region_code,
  service_tier_code,billing_channel_code,currency,provider_unit_code,unit_scale,list_unit_rate_micros,
  contract_unit_rate_micros,rate_card_version,effective_from,effective_to,source_url,accessed_at
) VALUES
 ('PRICE-OAI-IN','openai','responses','fictional-oai-snapshot','input_tokens','global','standard','direct_api','USD','token',1000000,2000000,2000000,'2026-08-01-public-seed','2026-06-01T00:00:00Z',NULL,'https://developers.openai.com/api/docs/pricing','2026-08-01T00:00:00Z'),
 ('PRICE-OAI-OUT','openai','responses','fictional-oai-snapshot','output_tokens','global','standard','direct_api','USD','token',1000000,12000000,12000000,'2026-08-01-public-seed','2026-06-01T00:00:00Z',NULL,'https://developers.openai.com/api/docs/pricing','2026-08-01T00:00:00Z'),
 ('PRICE-ANT-IN','anthropic','messages','fictional-ant-snapshot','input_tokens','global','standard','direct_api','USD','token',1000000,2000000,2000000,'2026-08-01-public-seed','2026-06-01T00:00:00Z','2026-09-01T00:00:00Z','https://platform.claude.com/docs/en/about-claude/pricing','2026-08-01T00:00:00Z'),
 ('PRICE-ANT-OUT','anthropic','messages','fictional-ant-snapshot','output_tokens','global','standard','direct_api','USD','token',1000000,10000000,10000000,'2026-08-01-public-seed','2026-06-01T00:00:00Z','2026-09-01T00:00:00Z','https://platform.claude.com/docs/en/about-claude/pricing','2026-08-01T00:00:00Z');

INSERT INTO provider_usage_lines (
  usage_line_id,provider_request_id,job_id,price_id,usage_component_code,
  usage_line_ordinal,provider_native_unit_code,native_quantity,usage_recorded_at,
  ingested_at,list_estimated_cost_micros,contract_estimated_cost_micros,source_event_key_hash
) VALUES
 ('USE-01','REQ-01','JOB-01','PRICE-OAI-IN','input',1,'input_tokens',100000,'2026-06-03T09:04:00Z','2026-06-03T09:04:10Z',200000,200000,printf('%064x',2601)),
 ('USE-02','REQ-01','JOB-01','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',10000,'2026-06-03T09:04:00Z','2026-06-03T09:04:10Z',120000,120000,printf('%064x',2602)),
 ('USE-03','REQ-02','JOB-02','PRICE-OAI-IN','input',1,'input_tokens',110000,'2026-07-03T09:04:00Z','2026-07-03T09:04:10Z',220000,220000,printf('%064x',2603)),
 ('USE-04','REQ-02','JOB-02','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',11000,'2026-07-03T09:04:00Z','2026-07-03T09:04:10Z',132000,132000,printf('%064x',2604)),
 ('USE-05','REQ-03','JOB-03','PRICE-OAI-IN','input',1,'input_tokens',120000,'2026-06-05T09:05:00Z','2026-06-05T09:05:10Z',240000,240000,printf('%064x',2605)),
 ('USE-06','REQ-03','JOB-03','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',12000,'2026-06-05T09:05:00Z','2026-06-05T09:05:10Z',144000,144000,printf('%064x',2606)),
 ('USE-07','REQ-04','JOB-03','PRICE-ANT-IN','input',1,'input_tokens',50000,'2026-06-05T09:08:00Z','2026-06-05T09:08:10Z',100000,100000,printf('%064x',2607)),
 ('USE-08','REQ-04','JOB-03','PRICE-ANT-OUT','output',1,'output_tokens_includes_thinking',5000,'2026-06-05T09:08:00Z','2026-06-05T09:08:10Z',50000,50000,printf('%064x',2608)),
 ('USE-09','REQ-05','JOB-05','PRICE-OAI-IN','input',1,'input_tokens',80000,'2026-06-20T09:05:00Z','2026-06-20T09:05:10Z',160000,160000,printf('%064x',2609)),
 ('USE-10','REQ-05','JOB-05','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',8000,'2026-06-20T09:05:00Z','2026-06-20T09:05:10Z',96000,96000,printf('%064x',2610)),
 ('USE-11','REQ-06','JOB-05','PRICE-ANT-IN','input',1,'input_tokens',50000,'2026-06-20T09:08:00Z','2026-06-20T09:08:10Z',100000,100000,printf('%064x',2611)),
 ('USE-12','REQ-06','JOB-05','PRICE-ANT-OUT','output',1,'output_tokens_includes_thinking',5000,'2026-06-20T09:08:00Z','2026-06-20T09:08:10Z',50000,50000,printf('%064x',2612)),
 ('USE-13','REQ-07','JOB-06','PRICE-OAI-IN','input',1,'input_tokens',90000,'2026-07-20T09:05:00Z','2026-07-20T09:05:10Z',180000,180000,printf('%064x',2613)),
 ('USE-14','REQ-07','JOB-06','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',9000,'2026-07-20T09:05:00Z','2026-07-20T09:05:10Z',108000,108000,printf('%064x',2614)),
 ('USE-15','REQ-08','JOB-06','PRICE-OAI-IN','input',1,'input_tokens',70000,'2026-07-20T09:08:00Z','2026-07-20T09:08:10Z',140000,140000,printf('%064x',2615)),
 ('USE-16','REQ-08','JOB-06','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',7000,'2026-07-20T09:08:00Z','2026-07-20T09:08:10Z',84000,84000,printf('%064x',2616));

INSERT INTO provider_usage_lines (
  usage_line_id,provider_request_id,job_id,price_id,usage_component_code,
  usage_line_ordinal,provider_native_unit_code,native_quantity,usage_recorded_at,
  ingested_at,list_estimated_cost_micros,contract_estimated_cost_micros,source_event_key_hash
) VALUES
 ('USE-17','REQ-09','JOB-01','PRICE-OAI-IN','input',1,'input_tokens',10000,'2026-06-03T09:03:00Z','2026-06-03T09:03:10Z',20000,20000,printf('%064x',2617)),
 ('USE-18','REQ-09','JOB-01','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',1000,'2026-06-03T09:03:00Z','2026-06-03T09:03:10Z',12000,12000,printf('%064x',2618)),
 ('USE-19','REQ-10','JOB-02','PRICE-OAI-IN','input',1,'input_tokens',5000,'2026-07-03T09:03:00Z','2026-07-03T09:03:10Z',10000,10000,printf('%064x',2619)),
 ('USE-20','REQ-10','JOB-02','PRICE-OAI-OUT','output',1,'output_tokens_includes_reasoning',500,'2026-07-03T09:03:00Z','2026-07-03T09:03:10Z',6000,6000,printf('%064x',2620)),
 ('USE-21','REQ-11','JOB-03','PRICE-ANT-IN','input',1,'input_tokens',10000,'2026-06-05T09:08:50Z','2026-06-05T09:09:00Z',20000,20000,printf('%064x',2621)),
 ('USE-22','REQ-11','JOB-03','PRICE-ANT-OUT','output',1,'output_tokens_includes_thinking',1000,'2026-06-05T09:08:50Z','2026-06-05T09:09:00Z',10000,10000,printf('%064x',2622));

UPDATE provider_usage_lines
SET billable_quantity=native_quantity,
    billable_unit_code=(SELECT p.provider_unit_code FROM provider_price_book p WHERE p.price_id=provider_usage_lines.price_id),
    billing_conversion_rule_code=CASE
      WHEN provider_native_unit_code LIKE '%includes_reasoning' OR provider_native_unit_code LIKE '%includes_thinking'
        THEN 'provider-reported-output-includes-hidden-compute-v1'
      ELSE 'identity-native-to-billable-v1'
    END,
    inclusion_rule_code='billable',
    rounding_rule_code='provider-reported-integer-v1',
    minimum_rule_code='none';

INSERT INTO quality_reviews (
 quality_review_id,job_id,attempt_id,presented_output_id,review_scope,review_round,
 reviewer_kind,reviewer_ref_hash,grader_version,rubric_version,decision_record,judged,
 accepted,material_correction,policy_compliant,critical_failure,severity,reason_code,
 evidence_ref_hash,reviewed_at,ingested_at,source_event_key_hash
) VALUES
 ('QR-01-P','JOB-01','ATT-01','OUT-01-1','presented_output',1,'human',printf('%064x',2701),'human-v1','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2801),'2026-06-03T09:05:00Z','2026-06-03T09:05:10Z',printf('%064x',2901)),
 ('QR-01-F','JOB-01','ATT-01',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2802),'2026-06-03T09:09:00Z','2026-06-03T09:09:10Z',printf('%064x',2902)),
 ('QR-02-P','JOB-02','ATT-02','OUT-02-1','presented_output',1,'human',printf('%064x',2701),'human-v1','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2803),'2026-07-03T09:05:00Z','2026-07-03T09:05:10Z',printf('%064x',2903)),
 ('QR-02-F','JOB-02','ATT-02',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2804),'2026-07-03T09:09:00Z','2026-07-03T09:09:10Z',printf('%064x',2904)),
 ('QR-03-P','JOB-03','ATT-04','OUT-03-1','presented_output',1,'human',printf('%064x',2701),'human-v1','rubric-v3',1,1,0,1,1,0,'medium','unsupported_claim',printf('%064x',2805),'2026-06-05T09:10:00Z','2026-06-05T09:10:10Z',printf('%064x',2905)),
 ('QR-03-F','JOB-03','ATT-04',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,1,1,0,'low','accepted_after_correction',printf('%064x',2806),'2026-06-05T09:25:00Z','2026-06-05T09:25:10Z',printf('%064x',2906)),
 ('QR-05-P','JOB-05','ATT-06','OUT-05-1','presented_output',1,'human',printf('%064x',2701),'human-v1','rubric-v3',1,1,0,1,1,0,'medium','missing_evidence',printf('%064x',2807),'2026-06-20T09:10:00Z','2026-06-20T09:10:10Z',printf('%064x',2907)),
 ('QR-05-F','JOB-05','ATT-06',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,1,1,0,'low','accepted_after_correction',printf('%064x',2808),'2026-06-29T10:00:00Z','2026-07-02T10:00:00Z',printf('%064x',2908)),
 ('QR-06-P','JOB-06','ATT-07','OUT-06-1','presented_output',1,'human',printf('%064x',2701),'human-v1','rubric-v3',1,1,0,1,1,0,'medium','schema_invalid',printf('%064x',2809),'2026-07-20T09:10:00Z','2026-07-20T09:10:10Z',printf('%064x',2909)),
 ('QR-06-F','JOB-06','ATT-07',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,1,1,0,'low','accepted_after_correction',printf('%064x',2810),'2026-08-01T08:00:00Z','2026-08-01T08:00:10Z',printf('%064x',2910)),
 ('QR-07-P','JOB-07','ATT-08','OUT-07-1','presented_output',1,'rule',printf('%064x',2703),'rules-v7','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2811),'2026-06-04T09:04:00Z','2026-06-04T09:04:10Z',printf('%064x',2911)),
 ('QR-07-F','JOB-07','ATT-08',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2812),'2026-06-04T09:07:00Z','2026-06-04T09:07:10Z',printf('%064x',2912)),
 ('QR-08-P','JOB-08','ATT-09','OUT-08-1','presented_output',1,'rule',printf('%064x',2703),'rules-v7','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2813),'2026-07-04T09:04:00Z','2026-07-04T09:04:10Z',printf('%064x',2913)),
 ('QR-08-F','JOB-08','ATT-09',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2814),'2026-07-04T09:07:00Z','2026-07-04T09:07:10Z',printf('%064x',2914)),
 ('QR-09-P','JOB-09','ATT-10','OUT-09-1','presented_output',1,'rule',printf('%064x',2703),'rules-v7','rubric-v3',1,1,0,1,1,0,'high','coverage_gap',printf('%064x',2815),'2026-06-10T09:04:00Z','2026-06-10T09:04:10Z',printf('%064x',2915)),
 ('QR-09-F','JOB-09','ATT-10',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,0,1,1,0,'high','rejected',printf('%064x',2816),'2026-06-10T09:19:00Z','2026-06-10T09:19:10Z',printf('%064x',2916)),
 ('QR-11-P','JOB-11','ATT-11','OUT-11-1','presented_output',1,'rule',printf('%064x',2703),'rules-v7','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2817),'2026-06-15T09:04:00Z','2026-06-15T09:04:10Z',printf('%064x',2917)),
 ('QR-11-F','JOB-11','ATT-11',NULL,'final_job',1,'customer',printf('%064x',2702),'customer-v1','rubric-v3',1,1,1,0,1,0,'none','accepted',printf('%064x',2818),'2026-06-15T09:07:00Z','2026-06-15T09:07:10Z',printf('%064x',2918));

INSERT INTO tool_effects (
 effect_id,job_id,attempt_id,tool_code,effect_class,target_ref_hash,arguments_ref_hash,
 tool_policy_version,effect_fingerprint_hash,idempotency_key_hash,side_effect_level,
 risk_tier,approval_required,reversal_window_seconds,verification_due_seconds
) VALUES
 ('EFFECT-01','JOB-01','ATT-01','document_reader','read',printf('%064x',3001),printf('%064x',3051),'tool-v4',printf('%064x',3101),printf('%064x',3201),'none','low',0,NULL,NULL),
 ('EFFECT-02','JOB-02','ATT-02','report_publisher','send_publish',printf('%064x',3002),printf('%064x',3052),'tool-v4',printf('%064x',3102),printf('%064x',3202),'irreversible','high',1,NULL,3600),
 ('EFFECT-03','JOB-03','ATT-04','document_writer','write',printf('%064x',3003),printf('%064x',3053),'tool-v4',printf('%064x',3103),printf('%064x',3203),'reversible','medium',1,86400,3600),
 ('EFFECT-04','JOB-06','ATT-07','document_writer','write',printf('%064x',3004),printf('%064x',3054),'tool-v4',printf('%064x',3104),printf('%064x',3204),'reversible','medium',1,86400,3600);

INSERT INTO tool_effect_approvals (
 approval_id,effect_id,action_fingerprint_hash,approval_scope,authority_kind,
 authority_ref_hash,authority_scope_code,approved_at,expires_at,approval_ref_hash
) VALUES
 ('APPROVAL-02','EFFECT-02',printf('%064x',3102),'exact_action','customer',printf('%064x',3501),'publish-current-report','2026-07-03T09:06:00Z','2026-07-03T09:16:00Z',printf('%064x',3601)),
 ('APPROVAL-04','EFFECT-04',printf('%064x',3104),'exact_action','founder',printf('%064x',3502),'write-current-document','2026-07-20T09:09:00Z','2026-07-20T10:09:00Z',printf('%064x',3602));

INSERT INTO tool_effect_events (
 effect_event_id,effect_id,transition_ordinal,lifecycle_event,actor_side,actor_ref_hash,
 approval_scope_ref_hash,approval_id,bound_action_fingerprint_hash,external_execution_ref_hash,
 occurred_at,ingested_at,result_code,source_event_key_hash
) VALUES
 ('TEE-01-1','EFFECT-01',1,'proposed','system',NULL,NULL,NULL,NULL,NULL,'2026-06-03T09:03:00Z','2026-06-03T09:03:01Z','proposal_created',printf('%064x',3301)),
 ('TEE-01-2','EFFECT-01',2,'committed','system',NULL,NULL,NULL,printf('%064x',3101),printf('%064x',3401),'2026-06-03T09:03:10Z','2026-06-03T09:03:11Z','read_ok',printf('%064x',3302)),
 ('TEE-02-1','EFFECT-02',1,'proposed','system',NULL,NULL,NULL,NULL,NULL,'2026-07-03T09:04:10Z','2026-07-03T09:04:11Z','proposal_created',printf('%064x',3303)),
 ('TEE-02-2','EFFECT-02',2,'approval_requested','system',NULL,NULL,NULL,NULL,NULL,'2026-07-03T09:04:20Z','2026-07-03T09:04:21Z','approval_requested',printf('%064x',3304)),
 ('TEE-02-3','EFFECT-02',3,'approved','customer',printf('%064x',3501),printf('%064x',3601),'APPROVAL-02',printf('%064x',3102),NULL,'2026-07-03T09:06:00Z','2026-07-03T09:06:01Z','scope_approved',printf('%064x',3305)),
 ('TEE-02-4','EFFECT-02',4,'committed','system',NULL,NULL,'APPROVAL-02',printf('%064x',3102),printf('%064x',3402),'2026-07-03T09:06:10Z','2026-07-03T09:06:11Z','send_ok',printf('%064x',3306)),
 ('TEE-02-5','EFFECT-02',5,'verified','system',NULL,NULL,NULL,NULL,NULL,'2026-07-03T09:06:20Z','2026-07-03T09:06:21Z','postcondition_ok',printf('%064x',3307)),
 ('TEE-03-1','EFFECT-03',1,'proposed','system',NULL,NULL,NULL,NULL,NULL,'2026-06-05T09:08:10Z','2026-06-05T09:08:11Z','proposal_created',printf('%064x',3308)),
 ('TEE-03-2','EFFECT-03',2,'approval_requested','system',NULL,NULL,NULL,NULL,NULL,'2026-06-05T09:08:20Z','2026-06-05T09:08:21Z','approval_requested',printf('%064x',3309)),
 ('TEE-03-3','EFFECT-03',3,'rejected','founder',printf('%064x',3502),NULL,NULL,NULL,NULL,'2026-06-05T09:09:00Z','2026-06-05T09:09:01Z','target_mismatch',printf('%064x',3310)),
 ('TEE-04-1','EFFECT-04',1,'proposed','system',NULL,NULL,NULL,NULL,NULL,'2026-07-20T09:08:10Z','2026-07-20T09:08:11Z','proposal_created',printf('%064x',3311)),
 ('TEE-04-2','EFFECT-04',2,'approval_requested','system',NULL,NULL,NULL,NULL,NULL,'2026-07-20T09:08:20Z','2026-07-20T09:08:21Z','approval_requested',printf('%064x',3312)),
 ('TEE-04-3','EFFECT-04',3,'approved','founder',printf('%064x',3502),printf('%064x',3602),'APPROVAL-04',printf('%064x',3104),NULL,'2026-07-20T09:09:00Z','2026-07-20T09:09:01Z','scope_approved',printf('%064x',3313)),
 ('TEE-04-4','EFFECT-04',4,'committed','system',NULL,NULL,'APPROVAL-04',printf('%064x',3104),printf('%064x',3403),'2026-07-20T09:09:10Z','2026-07-20T09:09:11Z','write_ok',printf('%064x',3314)),
 ('TEE-04-5','EFFECT-04',5,'rolled_back','founder',printf('%064x',3502),NULL,NULL,NULL,NULL,'2026-07-20T09:20:00Z','2026-07-20T09:20:01Z','customer_requested_rollback',printf('%064x',3315));

INSERT INTO incidents (incident_id,job_id,origin_code,severity,affected_account_ref_hash,current_state,first_detected_at,last_reopened_at) VALUES
 ('INC-05','JOB-05','evidence_provenance','sev3',printf('%064x',3),'closed','2026-06-20T09:10:00Z','2026-07-02T10:05:00Z');
INSERT INTO incident_events (incident_event_id,incident_id,transition_ordinal,event_type,occurred_at,ingested_at,reason_code,source_event_key_hash) VALUES
 ('IE-05-1','INC-05',1,'opened','2026-06-20T09:10:00Z','2026-06-20T09:10:10Z','provenance_gap',printf('%064x',3701)),
 ('IE-05-2','INC-05',2,'contained','2026-06-20T09:20:00Z','2026-06-20T09:20:10Z','manual_hold',printf('%064x',3702)),
 ('IE-05-3','INC-05',3,'closed','2026-06-20T10:00:00Z','2026-06-20T10:00:10Z','evidence_added',printf('%064x',3703)),
 ('IE-05-4','INC-05',4,'reopened','2026-07-02T10:05:00Z','2026-07-02T10:05:10Z','late_customer_question',printf('%064x',3704)),
 ('IE-05-5','INC-05',5,'closed','2026-07-02T10:30:00Z','2026-07-02T10:30:10Z','customer_confirmed',printf('%064x',3705));

INSERT INTO job_state_events (job_state_event_id,job_id,state_axis,state_code,event_phase,reason_code,restates_event_id,occurred_at,ingested_at,source_event_key_hash) VALUES
 ('JSE-I0-JOB-05','JOB-05','incident','closed','matured','initial_incident_closed',NULL,'2026-06-20T10:00:00Z','2026-06-20T10:00:10Z',printf('%064x',3711)),
 ('JSE-IR-JOB-05','JOB-05','incident','reopened','reopened','late_customer_question',NULL,'2026-07-02T10:05:00Z','2026-07-02T10:05:10Z',printf('%064x',3712)),
 ('JSE-I1-JOB-05','JOB-05','incident','closed','restated','incident_reclosed','JSE-I0-JOB-05','2026-07-02T10:30:00Z','2026-07-02T10:30:10Z',printf('%064x',3713));

INSERT INTO human_work_logs (
 human_work_id,job_id,workflow_value_cycle_id,attempt_id,actor_side,work_class,
 worker_role_code,worker_ref_hash,minutes_worked,internal_hourly_cost_micros,
 currency,occurred_at,ingested_at,source_event_key_hash
) VALUES
 ('HW-01','JOB-01','WVC-ACC-01-1','ATT-01','founder','planned_review','founder',printf('%064x',3801),3,60000000,'USD','2026-06-03T09:05:00Z','2026-06-03T09:05:10Z',printf('%064x',3901)),
 ('HW-02','JOB-02','WVC-ACC-01-2','ATT-02','founder','planned_review','founder',printf('%064x',3801),2,60000000,'USD','2026-07-03T09:05:00Z','2026-07-03T09:05:10Z',printf('%064x',3902)),
 ('HW-03','JOB-03','WVC-ACC-02-1','ATT-04','founder','planned_review','founder',printf('%064x',3801),5,60000000,'USD','2026-06-05T09:09:00Z','2026-06-05T09:09:10Z',printf('%064x',3903)),
 ('HW-04','JOB-03','WVC-ACC-02-1','ATT-04','founder','correction','founder',printf('%064x',3801),10,60000000,'USD','2026-06-05T09:10:00Z','2026-06-05T09:10:10Z',printf('%064x',3904)),
 ('HW-05','JOB-03','WVC-ACC-02-1','ATT-04','founder','founder_rescue','founder',printf('%064x',3801),5,60000000,'USD','2026-06-05T09:20:00Z','2026-06-05T09:20:10Z',printf('%064x',3905)),
 ('HW-06','JOB-04','WVC-ACC-02-2',NULL,'founder','support','founder',printf('%064x',3801),10,60000000,'USD','2026-07-05T09:05:00Z','2026-07-05T09:05:10Z',printf('%064x',3906)),
 ('HW-07','JOB-05','WVC-ACC-03-1','ATT-06','founder','planned_review','founder',printf('%064x',3801),8,60000000,'USD','2026-06-20T09:09:00Z','2026-06-20T09:09:10Z',printf('%064x',3907)),
 ('HW-08','JOB-05','WVC-ACC-03-1','ATT-06','founder','correction','founder',printf('%064x',3801),12,60000000,'USD','2026-06-20T09:10:00Z','2026-06-20T09:10:10Z',printf('%064x',3908)),
 ('HW-09','JOB-06','WVC-ACC-03-2','ATT-07','founder','planned_review','founder',printf('%064x',3801),5,60000000,'USD','2026-07-20T09:09:00Z','2026-07-20T09:09:10Z',printf('%064x',3909)),
 ('HW-10','JOB-06','WVC-ACC-03-2','ATT-07','founder','correction','founder',printf('%064x',3801),10,60000000,'USD','2026-07-20T09:10:00Z','2026-07-20T09:10:10Z',printf('%064x',3910)),
 ('HW-11','JOB-07','WVC-ACC-04-1','ATT-08','founder','planned_review','founder',printf('%064x',3801),5,60000000,'USD','2026-06-04T09:04:00Z','2026-06-04T09:04:10Z',printf('%064x',3911)),
 ('HW-12','JOB-08','WVC-ACC-04-2','ATT-09','founder','planned_review','founder',printf('%064x',3801),5,60000000,'USD','2026-07-04T09:04:00Z','2026-07-04T09:04:10Z',printf('%064x',3912)),
 ('HW-13','JOB-09','WVC-ACC-05-1','ATT-10','founder','planned_review','founder',printf('%064x',3801),20,60000000,'USD','2026-06-10T09:04:00Z','2026-06-10T09:04:10Z',printf('%064x',3913)),
 ('HW-14','JOB-11','WVC-ACC-06-1','ATT-11','founder','planned_review','founder',printf('%064x',3801),5,60000000,'USD','2026-06-15T09:04:00Z','2026-06-15T09:04:10Z',printf('%064x',3914)),
 ('HW-15','JOB-03','WVC-ACC-02-1','ATT-04','customer','customer_review','customer_operator',printf('%064x',3802),5,NULL,'USD','2026-06-05T09:20:00Z','2026-06-05T09:20:10Z',printf('%064x',3915));

INSERT INTO value_events (
 value_event_id,job_id,workflow_value_cycle_id,value_kind,event_action,supersedes_event_id,
 confirmation_method,baseline_status,evidence_ref_hash,occurred_at,ingested_at,source_event_key_hash
) VALUES
 ('VE-V-01','JOB-01','WVC-ACC-01-1','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4001),'2026-06-03T09:10:00Z','2026-06-03T09:10:10Z',printf('%064x',4101)),
 ('VE-I-01','JOB-01','WVC-ACC-01-1','independent_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4002),'2026-06-03T09:10:00Z','2026-06-03T09:10:10Z',printf('%064x',4102)),
 ('VE-V-02','JOB-02','WVC-ACC-01-2','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4003),'2026-07-03T09:10:00Z','2026-07-03T09:10:10Z',printf('%064x',4103)),
 ('VE-R-02','JOB-02','WVC-ACC-01-2','repeat_value','confirm',NULL,'workflow_log','observed',printf('%064x',4004),'2026-07-03T09:10:00Z','2026-07-03T09:10:10Z',printf('%064x',4104)),
 ('VE-I-02','JOB-02','WVC-ACC-01-2','independent_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4005),'2026-07-03T09:10:00Z','2026-07-03T09:10:10Z',printf('%064x',4105)),
 ('VE-V-03','JOB-03','WVC-ACC-02-1','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4006),'2026-06-05T09:30:00Z','2026-06-05T09:30:10Z',printf('%064x',4106)),
 ('VE-V-05','JOB-05','WVC-ACC-03-1','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4007),'2026-06-29T10:00:00Z','2026-07-02T10:00:00Z',printf('%064x',4107)),
 ('VE-I-05','JOB-05','WVC-ACC-03-1','independent_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4008),'2026-06-29T10:00:00Z','2026-07-02T10:00:00Z',printf('%064x',4108)),
 ('VE-V-06','JOB-06','WVC-ACC-03-2','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4009),'2026-08-01T08:05:00Z','2026-08-01T08:05:10Z',printf('%064x',4109)),
 ('VE-R-06','JOB-06','WVC-ACC-03-2','repeat_value','confirm',NULL,'workflow_log','observed',printf('%064x',4010),'2026-08-01T08:05:00Z','2026-08-01T08:05:10Z',printf('%064x',4110)),
 ('VE-I-06','JOB-06','WVC-ACC-03-2','independent_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4011),'2026-08-01T08:05:00Z','2026-08-01T08:05:10Z',printf('%064x',4111)),
 ('VE-V-07','JOB-07','WVC-ACC-04-1','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4012),'2026-06-04T09:08:00Z','2026-06-04T09:08:10Z',printf('%064x',4112)),
 ('VE-I-07','JOB-07','WVC-ACC-04-1','independent_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4013),'2026-06-04T09:08:00Z','2026-06-04T09:08:10Z',printf('%064x',4113)),
 ('VE-V-08','JOB-08','WVC-ACC-04-2','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4014),'2026-07-04T09:08:00Z','2026-07-04T09:08:10Z',printf('%064x',4114)),
 ('VE-R-08','JOB-08','WVC-ACC-04-2','repeat_value','confirm',NULL,'workflow_log','observed',printf('%064x',4015),'2026-07-04T09:08:00Z','2026-07-04T09:08:10Z',printf('%064x',4115)),
 ('VE-I-08','JOB-08','WVC-ACC-04-2','independent_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4016),'2026-07-04T09:08:00Z','2026-07-04T09:08:10Z',printf('%064x',4116)),
 ('VE-V-11','JOB-11','WVC-ACC-06-1','verified_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4017),'2026-06-15T09:08:00Z','2026-06-15T09:08:10Z',printf('%064x',4117)),
 ('VE-I-11','JOB-11','WVC-ACC-06-1','independent_value','confirm',NULL,'customer_confirmation','observed',printf('%064x',4018),'2026-06-15T09:08:00Z','2026-06-15T09:08:10Z',printf('%064x',4118));

INSERT INTO variable_cash_cost_ledger (
 variable_cost_line_id,account_commercial_cycle_id,job_id,allocation_scope,cost_kind,amount_micros,
 currency,source_line_ref_hash,occurred_at,ingested_at
)
SELECT 'VC-PAY-'||account_commercial_cycle_id,account_commercial_cycle_id,NULL,'commercial_cycle',
       'payment_fee',3000000,'USD',
       printf('%064x',4200+row_number() OVER (ORDER BY account_commercial_cycle_id)),
       billing_period_end,strftime('%Y-%m-%dT%H:%M:%SZ',billing_period_end,'+1 minute')
FROM account_commercial_cycles;

INSERT INTO variable_cash_cost_ledger (
 variable_cost_line_id,account_commercial_cycle_id,job_id,allocation_scope,cost_kind,
 amount_micros,currency,source_line_ref_hash,payment_dispute_event_id,occurred_at,ingested_at
) VALUES (
 'VC-DISPUTE-FEE-ACC-05-1','CC-ACC-05-1',NULL,'commercial_cycle','dispute_fee',
 1000000,'USD',printf('%064x',4250),'DISPUTE-ACC-05-1',
 '2026-06-25T10:00:00Z','2026-06-25T10:00:10Z'
);

-- Job-scoped example: it enters v_job_cost_facts and therefore the mapped
-- commercial cycle through job cost; it is excluded from commercial other_cash.
INSERT INTO variable_cash_cost_ledger (
 variable_cost_line_id,account_commercial_cycle_id,job_id,allocation_scope,cost_kind,
 amount_micros,currency,source_line_ref_hash,payment_dispute_event_id,occurred_at,ingested_at
) VALUES (
 'VC-EXT-JOB-01','CC-ACC-01-1','JOB-01','job','external_api',50000,'USD',
 printf('%064x',4251),NULL,'2026-06-03T09:04:00Z','2026-06-03T09:04:10Z'
);

INSERT INTO provider_invoice_lines (
 provider_invoice_line_id,provider_code,invoice_ref_hash,line_ref_hash,project_ref_hash,
 sku_code,economic_class,service_period_start,service_period_end,currency,
 invoice_amount_micros,corrects_invoice_line_id,recorded_at,ingested_at,source_event_key_hash
) VALUES
 ('INV-OAI-IN','openai',printf('%064x',4301),printf('%064x',4401),printf('%064x',4501),'input_tokens','usage_charge','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',1170000,NULL,'2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4601)),
 ('INV-OAI-OUT','openai',printf('%064x',4301),printf('%064x',4402),printf('%064x',4501),'output_tokens','usage_charge','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',702000,NULL,'2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4602)),
 ('INV-ANT-IN','anthropic',printf('%064x',4302),printf('%064x',4403),printf('%064x',4502),'input_tokens','usage_charge','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',220000,NULL,'2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4603)),
 ('INV-ANT-OUT','anthropic',printf('%064x',4302),printf('%064x',4404),printf('%064x',4502),'output_tokens','usage_charge','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',110000,NULL,'2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4604)),
 ('INV-OAI-STORAGE','openai',printf('%064x',4301),printf('%064x',4405),printf('%064x',4501),'shared_storage','shared_charge','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',50000,NULL,'2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4605)),
 ('INV-OAI-CREDIT','openai',printf('%064x',4301),printf('%064x',4406),printf('%064x',4501),'service_credit','provider_service_credit','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',-10000,NULL,'2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4606));

INSERT INTO provider_invoice_lines (
 provider_invoice_line_id,provider_code,invoice_ref_hash,line_ref_hash,project_ref_hash,
 sku_code,economic_class,entry_action,service_period_start,service_period_end,currency,
 invoice_amount_micros,corrects_invoice_line_id,recorded_at,ingested_at,source_event_key_hash
) VALUES
 ('INV-OAI-DISCOUNT-TEST','openai',printf('%064x',4301),printf('%064x',4407),printf('%064x',4501),'contract_discount','contract_discount','recognize','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',-5000,NULL,'2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4607)),
 ('INV-OAI-DISCOUNT-TEST-REV','openai',printf('%064x',4301),printf('%064x',4408),printf('%064x',4501),'contract_discount','contract_discount','reverse','2026-06-01T00:00:00Z','2026-08-01T00:00:00Z','USD',5000,'INV-OAI-DISCOUNT-TEST','2026-08-01T00:10:00Z','2026-08-01T00:11:00Z',printf('%064x',4608));

INSERT INTO invoice_allocations (
 allocation_id,provider_invoice_line_id,usage_line_id,job_id,allocation_kind,amount_micros,
 allocation_rule_version,reconciled_at,ingested_at,source_event_key_hash
)
SELECT 'ALLOC-'||u.usage_line_id,
 CASE
  WHEN p.provider_code='openai' AND p.sku_code='input_tokens' THEN 'INV-OAI-IN'
  WHEN p.provider_code='openai' AND p.sku_code='output_tokens' THEN 'INV-OAI-OUT'
  WHEN p.provider_code='anthropic' AND p.sku_code='input_tokens' THEN 'INV-ANT-IN'
  WHEN p.provider_code='anthropic' AND p.sku_code='output_tokens' THEN 'INV-ANT-OUT'
 END,
 u.usage_line_id,NULL,'direct_usage',u.contract_estimated_cost_micros,
 'direct-request-sku-v1','2026-08-01T00:20:00Z','2026-08-01T00:20:10Z',
 printf('%064x',4700+row_number() OVER (ORDER BY u.usage_line_id))
FROM provider_usage_lines u JOIN provider_price_book p ON p.price_id=u.price_id;

-- A documented invoice-side rounding reallocation: the invoice SKU total is
-- unchanged, both direct deltas carry evidence, and neither becomes a DQ error.
UPDATE invoice_allocations
SET amount_micros=amount_micros+500,
    variance_reason_code='invoice_rounding_reallocation',
    variance_evidence_ref_hash=printf('%064x',4751)
WHERE allocation_id='ALLOC-USE-01';
UPDATE invoice_allocations
SET amount_micros=amount_micros-500,
    variance_reason_code='invoice_rounding_reallocation',
    variance_evidence_ref_hash=printf('%064x',4752)
WHERE allocation_id='ALLOC-USE-03';

INSERT INTO invoice_allocations (
 allocation_id,provider_invoice_line_id,usage_line_id,job_id,allocation_kind,amount_micros,
 allocation_rule_version,reconciled_at,ingested_at,source_event_key_hash
) VALUES
 ('ALLOC-STORAGE-JOB-01','INV-OAI-STORAGE',NULL,'JOB-01','job_shared',20000,'predeclared-job-shared-v1','2026-08-01T00:20:00Z','2026-08-01T00:20:10Z',printf('%064x',4803)),
 ('ALLOC-STORAGE-UNALLOCATED','INV-OAI-STORAGE',NULL,NULL,'unallocated_charge',30000,'unallocated-session-v1','2026-08-01T00:20:00Z','2026-08-01T00:20:10Z',printf('%064x',4801)),
 ('ALLOC-PROVIDER-CREDIT','INV-OAI-CREDIT',NULL,NULL,'provider_credit_unallocated',-10000,'provider-credit-separate-v1','2026-08-01T00:20:00Z','2026-08-01T00:20:10Z',printf('%064x',4802));

INSERT INTO invoice_allocations (
 allocation_id,provider_invoice_line_id,usage_line_id,job_id,allocation_kind,amount_micros,
 allocation_rule_version,reconciled_at,ingested_at,source_event_key_hash
) VALUES
 ('ALLOC-DISCOUNT-TEST','INV-OAI-DISCOUNT-TEST',NULL,NULL,'provider_credit_unallocated',-5000,'provider-credit-separate-v1','2026-08-01T00:20:00Z','2026-08-01T00:20:10Z',printf('%064x',4804)),
 ('ALLOC-DISCOUNT-TEST-REV','INV-OAI-DISCOUNT-TEST-REV',NULL,NULL,'provider_credit_reversal_unallocated',5000,'provider-credit-reversal-v1','2026-08-01T00:20:00Z','2026-08-01T00:20:10Z',printf('%064x',4805));

INSERT INTO provider_settlements (
 provider_settlement_id,provider_code,invoice_ref_hash,settlement_kind,amount_micros,
 currency,settlement_ref_hash,occurred_at,ingested_at
) VALUES
 ('SETTLE-OAI','openai',printf('%064x',4301),'cash_payment',1912000,'USD',printf('%064x',4901),'2026-08-01T01:00:00Z','2026-08-01T01:01:00Z'),
 ('SETTLE-ANT','anthropic',printf('%064x',4302),'cash_payment',330000,'USD',printf('%064x',4902),'2026-08-01T01:00:00Z','2026-08-01T01:01:00Z');

INSERT INTO metric_snapshots VALUES
 ('SNAP-001','1.0.0','ai-econ-v1','2026-07-01T00:00:00Z','2026-07-01T00:00:00Z',NULL,NULL,NULL,'2026-07-01T00:05:00Z'),
 ('SNAP-002','1.0.0','ai-econ-v1','2026-07-01T00:00:00Z','2026-07-02T10:30:00Z',NULL,'SNAP-001','late_value_and_incident','2026-07-02T10:35:00Z'),
 ('SNAP-003','1.0.0','ai-econ-v1','2026-08-01T12:00:00Z','2026-08-01T11:45:00Z','2026-08-01T00:20:00Z',NULL,NULL,'2026-08-01T12:00:00Z');

-- -----------------------------------------------------------------------------
-- Views
-- -----------------------------------------------------------------------------

-- Reusable bitemporal cutoffs. Operational/snapshot views below use these
-- instead of raw append tables so future or late-unavailable facts cannot leak
-- into the configured event-time/watermark snapshot.
CREATE VIEW v_experiment_assignments_asof AS
SELECT a.* FROM experiment_assignments a CROSS JOIN model_settings s
WHERE a.assigned_at<=s.event_time_as_of AND a.ingested_at<=s.data_watermark_at;

CREATE VIEW v_workflow_cycle_experiment_assignments_asof AS
SELECT x.* FROM workflow_cycle_experiment_assignments x CROSS JOIN model_settings s
WHERE x.mapped_at<=s.event_time_as_of;

CREATE VIEW v_eligible_opportunities_asof AS
SELECT o.* FROM eligible_opportunities o CROSS JOIN model_settings s
WHERE o.occurred_at<=s.event_time_as_of AND o.ingested_at<=s.data_watermark_at;

-- jobs is a materialized projection without its own ingestion timestamp.  The
-- immutable eligible-opportunity event is therefore the visibility gate.
CREATE VIEW v_jobs_asof AS
SELECT j.* FROM jobs j
JOIN v_eligible_opportunities_asof o USING(eligible_opportunity_id)
JOIN v_experiment_assignments_asof a
  ON a.assignment_id=j.assignment_id AND a.account_id=j.account_id
JOIN v_workflow_cycle_experiment_assignments_asof x
  ON x.workflow_value_cycle_id=j.workflow_value_cycle_id
 AND x.assignment_id=j.assignment_id AND x.account_id=j.account_id;

CREATE VIEW v_account_commercial_cycles_asof AS
SELECT c.*,
       CASE
         WHEN c.closed_at IS NULL OR c.closed_at>s.event_time_as_of THEN 'open'
         WHEN c.commercial_state='reopened' AND (c.reopened_at IS NULL OR c.reopened_at>s.event_time_as_of) THEN 'closed'
         ELSE c.commercial_state
       END AS commercial_state_asof
FROM account_commercial_cycles c CROSS JOIN model_settings s
WHERE c.billing_period_start<=s.event_time_as_of;

CREATE VIEW v_attempts_asof AS
SELECT a.* FROM attempts a JOIN v_jobs_asof j USING(job_id) CROSS JOIN model_settings s
WHERE a.started_at<=s.event_time_as_of AND a.ingested_at<=s.data_watermark_at;

CREATE VIEW v_presented_outputs_asof AS
SELECT o.* FROM presented_outputs o
JOIN v_attempts_asof a ON a.job_id=o.job_id AND a.attempt_id=o.attempt_id
CROSS JOIN model_settings s
WHERE o.presented_at<=s.event_time_as_of AND o.ingested_at<=s.data_watermark_at;

CREATE VIEW v_provider_requests_asof AS
SELECT r.* FROM provider_requests r
JOIN v_attempts_asof a ON a.job_id=r.job_id AND a.attempt_id=r.attempt_id
CROSS JOIN model_settings s
WHERE r.started_at<=s.event_time_as_of AND r.ingested_at<=s.data_watermark_at;

CREATE VIEW v_provider_usage_lines_asof AS
SELECT u.* FROM provider_usage_lines u
JOIN v_provider_requests_asof r
  ON r.job_id=u.job_id AND r.provider_request_id=u.provider_request_id
CROSS JOIN model_settings s
WHERE u.usage_recorded_at<=s.event_time_as_of AND u.ingested_at<=s.data_watermark_at;

CREATE VIEW v_quality_reviews_asof AS
SELECT q.* FROM quality_reviews q
JOIN v_jobs_asof j USING(job_id)
LEFT JOIN v_attempts_asof a ON a.job_id=q.job_id AND a.attempt_id=q.attempt_id
LEFT JOIN v_presented_outputs_asof o ON o.job_id=q.job_id AND o.presented_output_id=q.presented_output_id
CROSS JOIN model_settings s
WHERE q.reviewed_at<=s.event_time_as_of AND q.ingested_at<=s.data_watermark_at
  AND (q.attempt_id IS NULL OR a.attempt_id IS NOT NULL)
  AND (q.presented_output_id IS NULL OR o.presented_output_id IS NOT NULL);

CREATE VIEW v_tool_effect_events_asof AS
SELECT e.* FROM tool_effect_events e
JOIN tool_effects t USING(effect_id)
JOIN v_attempts_asof a ON a.job_id=t.job_id AND a.attempt_id=t.attempt_id
CROSS JOIN model_settings s
WHERE e.occurred_at<=s.event_time_as_of AND e.ingested_at<=s.data_watermark_at;

CREATE VIEW v_human_work_logs_asof AS
SELECT h.* FROM human_work_logs h
JOIN v_jobs_asof j USING(job_id)
LEFT JOIN v_attempts_asof a ON a.job_id=h.job_id AND a.attempt_id=h.attempt_id
CROSS JOIN model_settings s
WHERE h.occurred_at<=s.event_time_as_of AND h.ingested_at<=s.data_watermark_at
  AND (h.attempt_id IS NULL OR a.attempt_id IS NOT NULL);

CREATE VIEW v_incident_events_asof AS
SELECT e.* FROM incident_events e
JOIN incidents i USING(incident_id)
JOIN v_jobs_asof j ON j.job_id=i.job_id
CROSS JOIN model_settings s
WHERE e.occurred_at<=s.event_time_as_of AND e.ingested_at<=s.data_watermark_at;

CREATE VIEW v_incidents_asof AS
WITH ranked AS (
  SELECT e.*,row_number() OVER (PARTITION BY incident_id ORDER BY occurred_at DESC,ingested_at DESC,incident_event_id DESC) AS rn
  FROM v_incident_events_asof e
), event_rollup AS (
  SELECT incident_id,MAX(CASE WHEN event_type='reopened' THEN occurred_at END) AS last_reopened_at_asof
  FROM v_incident_events_asof GROUP BY incident_id
)
SELECT i.incident_id,i.schema_version,i.job_id,i.origin_code,i.severity,
       i.affected_account_ref_hash,
       CASE r.event_type WHEN 'opened' THEN 'open' ELSE r.event_type END AS current_state,
       i.first_detected_at,er.last_reopened_at_asof AS last_reopened_at
FROM incidents i CROSS JOIN model_settings s
JOIN ranked r ON r.incident_id=i.incident_id AND r.rn=1
LEFT JOIN event_rollup er USING(incident_id)
WHERE i.first_detected_at<=s.event_time_as_of;

CREATE VIEW v_customer_revenue_ledger_cutoff AS
SELECT r.* FROM customer_revenue_ledger r
JOIN v_account_commercial_cycles_asof c USING(account_commercial_cycle_id)
CROSS JOIN model_settings s
WHERE r.occurred_at<=s.event_time_as_of AND r.ingested_at<=s.data_watermark_at;

-- A reversal is usable only when the corrected original is visible at the same
-- bitemporal cutoff; otherwise both the metric and DQ layer avoid a one-sided net.
CREATE VIEW v_customer_revenue_ledger_asof AS
SELECT r.* FROM v_customer_revenue_ledger_cutoff r
LEFT JOIN v_customer_revenue_ledger_cutoff o ON o.customer_revenue_line_id=r.corrects_line_id
WHERE r.entry_action='recognize' OR o.customer_revenue_line_id IS NOT NULL;

CREATE VIEW v_variable_cash_cost_ledger_asof AS
SELECT v.* FROM variable_cash_cost_ledger v
JOIN v_account_commercial_cycles_asof c USING(account_commercial_cycle_id)
LEFT JOIN v_jobs_asof j ON j.job_id=v.job_id
CROSS JOIN model_settings s
WHERE v.occurred_at<=s.event_time_as_of AND v.ingested_at<=s.data_watermark_at
  AND (v.job_id IS NULL OR j.job_id IS NOT NULL);

CREATE VIEW v_feature_revenue_allocations_asof AS
SELECT f.* FROM feature_revenue_allocations f
JOIN v_account_commercial_cycles_asof c USING(account_commercial_cycle_id)
CROSS JOIN model_settings s
WHERE f.allocated_at<=s.event_time_as_of AND f.ingested_at<=s.data_watermark_at
  AND EXISTS (
    SELECT 1 FROM v_workflow_cycle_experiment_assignments_asof x
    WHERE x.workflow_value_cycle_id=f.workflow_value_cycle_id
  );

CREATE VIEW v_provider_invoice_lines_cutoff AS
SELECT i.* FROM provider_invoice_lines i CROSS JOIN model_settings s
WHERE i.recorded_at<=s.event_time_as_of AND i.ingested_at<=s.data_watermark_at;

CREATE VIEW v_provider_invoice_lines_asof AS
SELECT r.* FROM v_provider_invoice_lines_cutoff r
LEFT JOIN v_provider_invoice_lines_cutoff o ON o.provider_invoice_line_id=r.corrects_invoice_line_id
WHERE r.entry_action='recognize' OR o.provider_invoice_line_id IS NOT NULL;

CREATE VIEW v_invoice_allocations_cutoff AS
SELECT a.* FROM invoice_allocations a CROSS JOIN model_settings s
WHERE a.reconciled_at<=s.invoice_reconciled_through AND a.ingested_at<=s.data_watermark_at;

CREATE VIEW v_invoice_allocations_asof AS
SELECT a.* FROM v_invoice_allocations_cutoff a
JOIN v_provider_invoice_lines_asof i USING(provider_invoice_line_id)
LEFT JOIN v_provider_usage_lines_asof u ON u.usage_line_id=a.usage_line_id
LEFT JOIN v_jobs_asof j ON j.job_id=a.job_id
WHERE (a.usage_line_id IS NULL OR u.usage_line_id IS NOT NULL)
  AND (a.job_id IS NULL OR j.job_id IS NOT NULL);

CREATE VIEW v_provider_settlements_cutoff AS
SELECT p.* FROM provider_settlements p CROSS JOIN model_settings s
WHERE p.occurred_at<=s.event_time_as_of AND p.ingested_at<=s.data_watermark_at;

CREATE VIEW v_provider_settlements_asof AS
SELECT p.* FROM v_provider_settlements_cutoff p
WHERE EXISTS (
  SELECT 1 FROM v_provider_invoice_lines_asof i
  WHERE i.provider_code=p.provider_code AND i.invoice_ref_hash=p.invoice_ref_hash AND i.currency=p.currency
);

CREATE VIEW v_current_value_events AS
WITH ranked AS (
  SELECT v.*,
         row_number() OVER (
           PARTITION BY job_id,value_kind
           ORDER BY occurred_at DESC,ingested_at DESC,value_event_id DESC
         ) AS recency_rank
  FROM value_events v JOIN v_jobs_asof j USING(job_id)
  CROSS JOIN model_settings s
  WHERE v.occurred_at<=s.event_time_as_of AND v.ingested_at<=s.data_watermark_at
)
SELECT * FROM ranked WHERE recency_rank=1 AND event_action='confirm';

CREATE VIEW v_late_events AS
WITH all_events(entity_type,entity_id,job_id,occurred_at,ingested_at) AS (
  SELECT 'eligible_opportunity',o.eligible_opportunity_id,j.job_id,o.occurred_at,o.ingested_at FROM eligible_opportunities o LEFT JOIN jobs j USING(eligible_opportunity_id)
  UNION ALL SELECT 'job_state',job_state_event_id,job_id,occurred_at,ingested_at FROM job_state_events
  UNION ALL SELECT 'presented_output',presented_output_id,job_id,presented_at,ingested_at FROM presented_outputs
  UNION ALL SELECT 'provider_request',provider_request_id,job_id,coalesce(finished_at,started_at),ingested_at FROM provider_requests
  UNION ALL SELECT 'provider_usage',usage_line_id,job_id,usage_recorded_at,ingested_at FROM provider_usage_lines
  UNION ALL SELECT 'quality_review',quality_review_id,job_id,reviewed_at,ingested_at FROM quality_reviews
  UNION ALL SELECT 'incident_event',ie.incident_event_id,i.job_id,ie.occurred_at,ie.ingested_at FROM incident_events ie JOIN incidents i USING(incident_id)
  UNION ALL SELECT 'human_work',human_work_id,job_id,occurred_at,ingested_at FROM human_work_logs
  UNION ALL SELECT 'value_event',value_event_id,job_id,occurred_at,ingested_at FROM value_events
  UNION ALL SELECT 'tool_effect_event',te.effect_event_id,t.job_id,te.occurred_at,te.ingested_at FROM tool_effect_events te JOIN tool_effects t USING(effect_id)
)
SELECT a.*,
       ROUND((julianday(a.ingested_at)-julianday(a.occurred_at))*24.0,2) AS arrival_delay_hours
FROM all_events a CROSS JOIN model_settings s
WHERE a.occurred_at<=s.event_time_as_of
  AND a.ingested_at<=s.data_watermark_at
  AND (julianday(a.ingested_at)-julianday(a.occurred_at))*24.0>s.late_threshold_hours;

CREATE VIEW v_usage_reconciliation AS
WITH direct AS (
  SELECT a.usage_line_id,a.provider_invoice_line_id,a.amount_micros,
         a.variance_reason_code,a.variance_evidence_ref_hash,
         i.provider_code AS invoice_provider_code,i.sku_code AS invoice_sku_code,
         i.currency AS invoice_currency,i.service_period_start,i.service_period_end
  FROM v_invoice_allocations_asof a
  JOIN v_provider_invoice_lines_asof i USING(provider_invoice_line_id)
  WHERE a.allocation_kind='direct_usage'
),
invoice_unallocated AS (
  SELECT provider_invoice_line_id,
         SUM(CASE WHEN allocation_kind IN ('unallocated_charge','tax_unallocated','rounding_unallocated') THEN amount_micros ELSE 0 END) AS unallocated_residual_micros
  FROM v_invoice_allocations_asof GROUP BY provider_invoice_line_id
)
SELECT
  u.usage_line_id,u.job_id,u.provider_request_id,r.request_role,r.failure_relation_type,
  r.retry_of_request_id,r.fallback_from_request_id,r.repair_of_request_id,
  coalesce(r.retry_of_request_id,r.fallback_from_request_id,r.repair_of_request_id) AS failure_parent_request_id,
  r.job_request_ordinal,
  CASE WHEN r.failure_relation_type<>'none' THEN 1 ELSE 0 END AS failure_driven,
  p.provider_code,p.sku_code,p.currency,
  r.region_code AS request_region_code,r.service_tier_code AS request_service_tier_code,
  r.billing_channel_code AS request_billing_channel_code,
  r.batch_mode AS request_batch_mode,r.context_band AS request_context_band,
  p.region_code AS price_region_code,p.service_tier_code AS price_service_tier_code,
  p.billing_channel_code AS price_billing_channel_code,
  p.batch_mode AS price_batch_mode,p.context_band AS price_context_band,
  u.provider_native_unit_code,u.native_quantity,
  u.billable_unit_code,u.billable_quantity,u.billing_conversion_rule_code,
  u.inclusion_rule_code,u.rounding_rule_code,u.minimum_rule_code,
  u.list_estimated_cost_micros,u.contract_estimated_cost_micros,
  d.amount_micros AS reconciled_contract_cost_micros,
  d.amount_micros-u.contract_estimated_cost_micros AS direct_delta_micros,
  CASE WHEN d.variance_reason_code IS NOT NULL THEN d.amount_micros-u.contract_estimated_cost_micros END AS explained_direct_delta_micros,
  d.variance_reason_code,d.variance_evidence_ref_hash,
  d.provider_invoice_line_id,d.invoice_provider_code,d.invoice_sku_code,d.invoice_currency,
  d.service_period_start,d.service_period_end,
  coalesce(iu.unallocated_residual_micros,0) AS invoice_line_unallocated_residual_micros_do_not_sum,
  CASE WHEN d.usage_line_id IS NOT NULL
             AND d.invoice_provider_code=p.provider_code
             AND d.invoice_sku_code=p.sku_code
             AND d.invoice_currency=p.currency
             AND u.usage_recorded_at>=d.service_period_start
             AND u.usage_recorded_at<d.service_period_end
       THEN 1 ELSE 0 END AS invoice_lineage_match,
  CASE
    WHEN d.usage_line_id IS NULL THEN 'missing_allocation'
    WHEN d.invoice_provider_code<>p.provider_code OR d.invoice_sku_code<>p.sku_code
      OR d.invoice_currency<>p.currency OR u.usage_recorded_at<d.service_period_start
      OR u.usage_recorded_at>=d.service_period_end THEN 'invoice_lineage_mismatch'
    WHEN d.amount_micros=u.contract_estimated_cost_micros THEN 'exact'
    WHEN d.variance_reason_code IS NOT NULL THEN 'explained_delta'
    WHEN abs(d.amount_micros-u.contract_estimated_cost_micros)<=s.reconciliation_tolerance_micros THEN 'within_tolerance'
    ELSE 'unexplained_delta'
  END AS reconciliation_status,
  d.amount_micros-u.contract_estimated_cost_micros AS reconciliation_delta_micros
FROM v_provider_usage_lines_asof u
JOIN v_provider_requests_asof r USING(provider_request_id)
JOIN provider_price_book p USING(price_id)
LEFT JOIN direct d USING(usage_line_id)
LEFT JOIN invoice_unallocated iu USING(provider_invoice_line_id)
CROSS JOIN model_settings s;

CREATE VIEW v_tool_effect_facts AS
WITH ranked_events AS (
  SELECT e.*,
         row_number() OVER (
           PARTITION BY effect_id
           ORDER BY occurred_at DESC,transition_ordinal DESC,ingested_at DESC,effect_event_id DESC
         ) AS recency_rank
  FROM v_tool_effect_events_asof e
),
event_rollup AS (
  SELECT
    effect_id,
    MIN(CASE WHEN lifecycle_event='approved' THEN occurred_at END) AS first_approved_at,
    MAX(transition_ordinal) AS transition_count
  FROM v_tool_effect_events_asof GROUP BY effect_id
),
latest_event AS (
  SELECT effect_id,lifecycle_event AS latest_lifecycle_event,occurred_at AS latest_lifecycle_at
  FROM ranked_events WHERE recency_rank=1
),
commit_event_checks AS (
  SELECT c.effect_id,c.effect_event_id,c.occurred_at AS committed_at,c.approval_id,
         c.bound_action_fingerprint_hash,
         CASE
           WHEN e.approval_required=0 AND c.bound_action_fingerprint_hash=e.effect_fingerprint_hash THEN 0
           WHEN e.approval_required=1
            AND a.approval_id IS NOT NULL
            AND c.bound_action_fingerprint_hash=e.effect_fingerprint_hash
            AND a.action_fingerprint_hash=e.effect_fingerprint_hash
            AND a.approval_scope='exact_action'
            AND a.authority_kind IN ('founder','customer')
            AND a.approved_at<=c.occurred_at AND c.occurred_at<a.expires_at
            AND EXISTS (
              SELECT 1 FROM v_tool_effect_events_asof ae
              WHERE ae.lifecycle_event='approved'
                AND ae.approval_id=c.approval_id
                AND ae.effect_id=c.effect_id
                AND ae.bound_action_fingerprint_hash=c.bound_action_fingerprint_hash
                AND ae.actor_side=a.authority_kind
                AND ae.actor_ref_hash=a.authority_ref_hash
                AND ae.approval_scope_ref_hash=a.approval_ref_hash
                AND ae.occurred_at>=a.approved_at AND ae.occurred_at<a.expires_at
                AND ae.occurred_at<=c.occurred_at
            ) THEN 0
           ELSE 1
         END AS commit_approval_violation
  FROM v_tool_effect_events_asof c
  JOIN tool_effects e USING(effect_id)
  LEFT JOIN tool_effect_approvals a
    ON a.approval_id=c.approval_id
   AND a.effect_id=c.effect_id
   AND a.action_fingerprint_hash=c.bound_action_fingerprint_hash
  WHERE c.lifecycle_event='committed'
),
commit_rollup AS (
  SELECT effect_id,MIN(committed_at) AS first_committed_at,MAX(committed_at) AS last_committed_at,
         COUNT(*) AS commit_event_count,
         CASE WHEN COUNT(*)>1 THEN COUNT(*)-1 ELSE 0 END AS duplicate_commit_count,
         group_concat(coalesce(approval_id,'NO_APPROVAL'),'|') AS commit_approval_ids,
         group_concat(bound_action_fingerprint_hash,'|') AS committed_action_fingerprint_hashes,
         MAX(commit_approval_violation) AS approval_violation
  FROM commit_event_checks GROUP BY effect_id
)
SELECT
  e.*,r.first_approved_at,c.first_committed_at,c.last_committed_at,c.commit_event_count,
  coalesce(c.duplicate_commit_count,0) AS duplicate_commit_count,
  c.commit_approval_ids,c.committed_action_fingerprint_hashes,
  CASE WHEN l.latest_lifecycle_event='verified' THEN 1 ELSE 0 END AS verified,
  CASE WHEN l.latest_lifecycle_event IN ('failed','expired') THEN 1 ELSE 0 END AS failed,
  CASE WHEN l.latest_lifecycle_event='rolled_back' THEN 1 ELSE 0 END AS rolled_back,
  l.latest_lifecycle_event,l.latest_lifecycle_at,r.transition_count,
  CASE WHEN c.last_committed_at IS NOT NULL AND e.verification_due_seconds IS NOT NULL
       THEN datetime(c.last_committed_at,'+'||e.verification_due_seconds||' seconds') END AS verification_due_at,
  CASE
    WHEN e.side_effect_level='none' THEN 'not_required'
    WHEN c.last_committed_at IS NULL THEN 'not_committed'
    WHEN l.latest_lifecycle_event='verified' AND l.latest_lifecycle_at>=c.last_committed_at THEN 'verified'
    WHEN l.latest_lifecycle_event='rolled_back' AND l.latest_lifecycle_at>=c.last_committed_at THEN 'rolled_back'
    WHEN l.latest_lifecycle_event IN ('failed','expired') AND l.latest_lifecycle_at>=c.last_committed_at THEN 'failed'
    WHEN datetime(s.event_time_as_of)>=datetime(c.last_committed_at,'+'||e.verification_due_seconds||' seconds') THEN 'overdue'
    ELSE 'pending'
  END AS verification_status,
  coalesce(c.approval_violation,0) AS approval_violation
FROM tool_effects e
LEFT JOIN event_rollup r USING(effect_id)
LEFT JOIN latest_event l USING(effect_id)
LEFT JOIN commit_rollup c USING(effect_id)
CROSS JOIN model_settings s
WHERE r.effect_id IS NOT NULL OR c.effect_id IS NOT NULL;

CREATE VIEW v_job_quality_facts AS
WITH first_output AS (
  SELECT job_id,presented_output_id FROM v_presented_outputs_asof WHERE presentation_ordinal=1
),
first_decisions AS (
  SELECT q.*,
         row_number() OVER (PARTITION BY q.job_id ORDER BY q.review_round DESC,q.reviewed_at DESC,q.quality_review_id DESC) AS rn
  FROM v_quality_reviews_asof q JOIN first_output f
    ON f.job_id=q.job_id AND f.presented_output_id=q.presented_output_id
  WHERE q.review_scope='presented_output' AND q.decision_record=1
),
final_decisions AS (
  SELECT q.*,
         row_number() OVER (PARTITION BY q.job_id ORDER BY q.review_round DESC,q.reviewed_at DESC,q.quality_review_id DESC) AS rn
  FROM v_quality_reviews_asof q
  WHERE q.review_scope='final_job' AND q.decision_record=1
),
quality_rollup AS (
  SELECT job_id,
         MAX(critical_failure) AS any_critical_quality_failure,
         MIN(CASE WHEN judged=1 THEN coalesce(policy_compliant,0) END) AS all_judged_policy_compliant,
         SUM(CASE WHEN judged=1 THEN 1 ELSE 0 END) AS judged_review_count,
         COUNT(*) AS review_count
  FROM v_quality_reviews_asof GROUP BY job_id
),
incident_rollup AS (
  SELECT job_id,
         MAX(CASE WHEN severity IN ('sev0','sev1','sev2') THEN 1 ELSE 0 END) AS any_sev0_to_sev2,
         COUNT(*) AS incident_count,
         SUM(CASE WHEN current_state='reopened' THEN 1 ELSE 0 END) AS currently_reopened_incidents
  FROM v_incidents_asof GROUP BY job_id
),
requirement_status AS (
  SELECT j.job_id,r.review_requirement_id,r.required_decision_count,
         SUM(CASE WHEN q.decision_record=1 AND q.judged=1 AND q.policy_compliant=1 THEN 1 ELSE 0 END) AS compliant_judged_decisions
  FROM v_jobs_asof j
  JOIN workflow_review_requirements r
    ON r.workflow_code=j.workflow_code
   AND r.required_for_independent_value=1
   AND r.effective_from<=j.eligible_at
   AND (r.effective_to IS NULL OR j.eligible_at<r.effective_to)
  LEFT JOIN v_quality_reviews_asof q
    ON q.job_id=j.job_id AND q.review_scope=r.review_scope
  GROUP BY j.job_id,r.review_requirement_id,r.required_decision_count
),
requirement_rollup AS (
  SELECT job_id,COUNT(*) AS required_review_requirement_count,
         SUM(CASE WHEN compliant_judged_decisions>=required_decision_count THEN 1 ELSE 0 END) AS satisfied_review_requirement_count
  FROM requirement_status GROUP BY job_id
)
SELECT
  j.job_id,
  f.presented_output_id AS first_presented_output_id,
  fd.judged AS first_presented_judged,
  fd.accepted AS first_presented_accepted,
  fd.material_correction AS first_presented_material_correction,
  zd.judged AS final_judged,
  zd.accepted AS final_review_accepted,
  zd.material_correction AS final_material_correction,
  coalesce(q.any_critical_quality_failure,0) AS any_critical_quality_failure,
  coalesce(q.all_judged_policy_compliant,0) AS all_judged_policy_compliant,
  coalesce(q.judged_review_count,0) AS judged_review_count,
  coalesce(q.review_count,0) AS review_count,
  coalesce(rr.required_review_requirement_count,0) AS required_review_requirement_count,
  coalesce(rr.satisfied_review_requirement_count,0) AS satisfied_review_requirement_count,
  CASE WHEN coalesce(rr.required_review_requirement_count,0)>0
             AND rr.required_review_requirement_count=rr.satisfied_review_requirement_count THEN 1 ELSE 0 END AS required_review_coverage_complete,
  CASE WHEN coalesce(rr.required_review_requirement_count,0)=0 THEN 'not_configured'
       WHEN rr.required_review_requirement_count=rr.satisfied_review_requirement_count THEN 'complete'
       ELSE 'unknown_missing_or_unjudged' END AS required_review_coverage_state,
  coalesce(i.any_sev0_to_sev2,0) AS any_sev0_to_sev2,
  coalesce(i.incident_count,0) AS incident_count,
  coalesce(i.currently_reopened_incidents,0) AS currently_reopened_incidents
FROM v_jobs_asof j
LEFT JOIN first_output f USING(job_id)
LEFT JOIN first_decisions fd ON fd.job_id=j.job_id AND fd.rn=1
LEFT JOIN final_decisions zd ON zd.job_id=j.job_id AND zd.rn=1
LEFT JOIN quality_rollup q USING(job_id)
LEFT JOIN incident_rollup i USING(job_id)
LEFT JOIN requirement_rollup rr USING(job_id);

CREATE VIEW v_job_cost_facts AS
WITH usage_alloc AS (
  SELECT
    j.job_id,
    COUNT(u.usage_line_id) AS usage_line_count,
    SUM(u.list_estimated_cost_micros) AS list_estimated_provider_cost_micros,
    SUM(u.contract_estimated_cost_micros) AS contract_estimated_provider_cost_micros,
    SUM(CASE WHEN ur.reconciliation_status IN ('exact','explained_delta','within_tolerance') THEN ur.reconciled_contract_cost_micros END) AS reconciled_provider_cost_micros_partial,
    SUM(CASE WHEN ur.reconciliation_status NOT IN ('exact','explained_delta','within_tolerance') THEN 1 ELSE 0 END) AS unreconciled_usage_line_count,
    SUM(CASE WHEN ur.reconciliation_status='explained_delta' THEN ur.explained_direct_delta_micros ELSE 0 END) AS explained_direct_delta_micros,
    SUM(CASE WHEN ur.failure_relation_type<>'none' AND ur.reconciliation_status IN ('exact','explained_delta','within_tolerance') THEN coalesce(ur.reconciled_contract_cost_micros,0) ELSE 0 END) AS failure_driven_reconciled_cost_micros,
    SUM(CASE WHEN ur.request_role='planned_auxiliary' AND ur.reconciliation_status IN ('exact','explained_delta','within_tolerance') THEN coalesce(ur.reconciled_contract_cost_micros,0) ELSE 0 END) AS planned_auxiliary_reconciled_cost_micros,
    SUM(CASE WHEN ur.request_role='validation' AND ur.reconciliation_status IN ('exact','explained_delta','within_tolerance') THEN coalesce(ur.reconciled_contract_cost_micros,0) ELSE 0 END) AS validation_reconciled_cost_micros
  FROM v_jobs_asof j
  LEFT JOIN v_provider_usage_lines_asof u USING(job_id)
  LEFT JOIN v_usage_reconciliation ur USING(usage_line_id)
  GROUP BY j.job_id
),
human AS (
  SELECT
    job_id,
    SUM(CASE WHEN actor_side IN ('provider','founder') THEN minutes_worked ELSE 0 END) AS internal_minutes,
    SUM(CASE WHEN actor_side='customer' THEN minutes_worked ELSE 0 END) AS customer_review_minutes,
    SUM(CASE WHEN work_class IN ('planned_review','customer_review','correction','support') THEN minutes_worked ELSE 0 END) AS human_touch_minutes,
    SUM(CASE WHEN actor_side IN ('provider','founder') THEN CAST(ROUND(minutes_worked*internal_hourly_cost_micros/60.0) AS INTEGER) ELSE 0 END) AS internal_labor_cost_micros,
    SUM(CASE WHEN work_class='planned_review' THEN minutes_worked ELSE 0 END) AS planned_review_minutes,
    SUM(CASE WHEN work_class='correction' THEN minutes_worked ELSE 0 END) AS correction_minutes,
    SUM(CASE WHEN work_class='founder_rescue' THEN minutes_worked ELSE 0 END) AS founder_rescue_minutes,
    SUM(CASE WHEN work_class IN ('correction','incident','founder_rescue') AND actor_side IN ('provider','founder') THEN CAST(ROUND(minutes_worked*internal_hourly_cost_micros/60.0) AS INTEGER) ELSE 0 END) AS human_recovery_cost_micros
  FROM v_human_work_logs_asof GROUP BY job_id
),
other_cash AS (
  SELECT job_id,SUM(amount_micros) AS other_job_variable_cash_cost_micros
  FROM v_variable_cash_cost_ledger_asof WHERE allocation_scope='job' GROUP BY job_id
),
job_shared AS (
  SELECT job_id,SUM(amount_micros) AS job_shared_reconciled_cost_micros
  FROM v_invoice_allocations_asof WHERE allocation_kind='job_shared' GROUP BY job_id
)
SELECT
  j.job_id,
  coalesce(u.usage_line_count,0) AS usage_line_count,
  coalesce(u.list_estimated_provider_cost_micros,0) AS list_estimated_provider_cost_micros,
  coalesce(u.contract_estimated_provider_cost_micros,0) AS contract_estimated_provider_cost_micros,
  CASE WHEN coalesce(u.unreconciled_usage_line_count,0)=0
       THEN coalesce(u.reconciled_provider_cost_micros_partial,0)+coalesce(js.job_shared_reconciled_cost_micros,0)
       ELSE NULL END AS reconciled_provider_cost_micros,
  coalesce(u.unreconciled_usage_line_count,0) AS unreconciled_usage_line_count,
  coalesce(u.explained_direct_delta_micros,0) AS explained_direct_delta_micros,
  coalesce(js.job_shared_reconciled_cost_micros,0) AS job_shared_reconciled_cost_micros,
  coalesce(u.failure_driven_reconciled_cost_micros,0) AS failure_driven_reconciled_cost_micros,
  coalesce(u.planned_auxiliary_reconciled_cost_micros,0) AS planned_auxiliary_reconciled_cost_micros,
  coalesce(u.validation_reconciled_cost_micros,0) AS validation_reconciled_cost_micros,
  coalesce(h.internal_minutes,0) AS internal_minutes,
  coalesce(h.customer_review_minutes,0) AS customer_review_minutes,
  coalesce(h.human_touch_minutes,0) AS human_touch_minutes,
  coalesce(h.internal_labor_cost_micros,0) AS internal_labor_cost_micros,
  coalesce(h.planned_review_minutes,0) AS planned_review_minutes,
  coalesce(h.correction_minutes,0) AS correction_minutes,
  coalesce(h.founder_rescue_minutes,0) AS founder_rescue_minutes,
  coalesce(h.human_recovery_cost_micros,0) AS human_recovery_cost_micros,
  coalesce(o.other_job_variable_cash_cost_micros,0) AS other_job_variable_cash_cost_micros,
  CASE
    WHEN coalesce(u.usage_line_count,0)>0 AND coalesce(u.unreconciled_usage_line_count,0)>0 THEN NULL
    ELSE coalesce(u.reconciled_provider_cost_micros_partial,0)+coalesce(js.job_shared_reconciled_cost_micros,0)+coalesce(o.other_job_variable_cash_cost_micros,0)
  END AS job_variable_delivery_cash_cost_micros,
  CASE
    WHEN coalesce(u.usage_line_count,0)>0 AND coalesce(u.unreconciled_usage_line_count,0)>0 THEN NULL
    ELSE coalesce(u.reconciled_provider_cost_micros_partial,0)+coalesce(js.job_shared_reconciled_cost_micros,0)+coalesce(o.other_job_variable_cash_cost_micros,0)+coalesce(h.internal_labor_cost_micros,0)
  END AS job_fully_loaded_variable_cost_micros
FROM v_jobs_asof j
LEFT JOIN usage_alloc u USING(job_id)
LEFT JOIN human h USING(job_id)
LEFT JOIN other_cash o USING(job_id)
LEFT JOIN job_shared js USING(job_id);

CREATE VIEW v_job_operating_facts AS
WITH attempts_rollup AS (
  SELECT job_id,COUNT(*) AS attempt_count,MAX(attempt_ordinal) AS max_attempt_ordinal FROM v_attempts_asof GROUP BY job_id
),
requests_rollup AS (
  SELECT job_id,COUNT(*) AS provider_request_count,
         SUM(CASE WHEN failure_relation_type<>'none' THEN 1 ELSE 0 END) AS failure_driven_request_count,
         SUM(CASE WHEN request_role='planned_auxiliary' THEN 1 ELSE 0 END) AS planned_auxiliary_request_count,
         SUM(CASE WHEN request_role='validation' THEN 1 ELSE 0 END) AS validation_request_count,
         SUM(CASE WHEN failure_relation_type='fallback' THEN 1 ELSE 0 END) AS fallback_request_count,
         SUM(CASE WHEN failure_relation_type IN ('app_retry','sdk_retry') THEN 1 ELSE 0 END) AS retry_request_count,
         SUM(CASE WHEN failure_relation_type='repair' THEN 1 ELSE 0 END) AS repair_request_count
  FROM v_provider_requests_asof GROUP BY job_id
),
outputs_rollup AS (
  SELECT job_id,COUNT(*) AS presented_output_count FROM v_presented_outputs_asof GROUP BY job_id
),
values_rollup AS (
  SELECT job_id,
         MAX(CASE WHEN value_kind='verified_value' THEN 1 ELSE 0 END) AS verified_value,
         MAX(CASE WHEN value_kind='repeat_value' THEN 1 ELSE 0 END) AS repeat_value,
         MAX(CASE WHEN value_kind='independent_value' THEN 1 ELSE 0 END) AS independent_value
  FROM v_current_value_events GROUP BY job_id
),
tools_rollup AS (
  SELECT job_id,COUNT(*) AS tool_effect_count,
         SUM(approval_violation) AS approval_violation_count,
         SUM(duplicate_commit_count) AS duplicate_tool_commit_count,
         SUM(CASE WHEN verification_status='overdue' THEN 1 ELSE 0 END) AS overdue_verification_count,
         SUM(CASE WHEN verification_status='pending' THEN 1 ELSE 0 END) AS pending_verification_count,
         SUM(CASE WHEN verification_status='failed' THEN 1 ELSE 0 END) AS failed_verification_count,
         SUM(CASE WHEN first_committed_at IS NOT NULL THEN 1 ELSE 0 END) AS committed_tool_effect_count,
         SUM(rolled_back) AS rolled_back_tool_effect_count
  FROM v_tool_effect_facts GROUP BY job_id
),
late_rollup AS (
  SELECT job_id,COUNT(*) AS late_event_count FROM v_late_events GROUP BY job_id
)
SELECT
  j.job_id,j.eligible_opportunity_id,j.account_id,j.workflow_value_cycle_id,
  a.experiment_id,a.variant_id,v.variant_code,e.design_type,
  j.feature_code,j.workflow_code,j.job_type_code,j.segment_code,j.risk_tier,
  j.eligible_at,j.offered_at,j.exposed_at,j.started_at,
  coalesce(sx.state_code,j.execution_state) AS execution_state,
  coalesce(sq.state_code,j.acceptance_state) AS acceptance_state,
  coalesce(sv.state_code,j.value_state) AS value_state,
  coalesce(si.state_code,j.incident_state) AS incident_state,
  CASE WHEN j.execution_mature_at IS NOT NULL AND j.execution_mature_at<=s.event_time_as_of
             AND sx.event_phase IN ('matured','restated')
             AND (j.execution_mature_at>=j.execution_due_at OR coalesce(sx.state_code,j.execution_state) IN ('completed','no_output','abandoned','stopped_for_safety')) THEN 1 ELSE 0 END AS execution_mature,
  CASE WHEN j.quality_mature_at IS NOT NULL AND j.quality_mature_at<=s.event_time_as_of
             AND sq.event_phase IN ('matured','restated')
             AND (j.quality_mature_at>=j.quality_due_at
                  OR (wp.quality_definitive_early_maturity_allowed=1 AND coalesce(sq.state_code,j.acceptance_state) IN ('accepted_first_pass','accepted_after_correction','rejected'))
                  OR (coalesce(sq.state_code,j.acceptance_state)='not_assessable' AND coalesce(sx.state_code,j.execution_state) IN ('no_output','abandoned','stopped_for_safety'))) THEN 1 ELSE 0 END AS quality_mature,
  CASE WHEN j.value_mature_at IS NOT NULL AND j.value_mature_at<=s.event_time_as_of
             AND sv.event_phase IN ('matured','restated')
             AND (j.value_mature_at>=j.value_due_at
                  OR (wp.value_confirmed_early_maturity_allowed=1 AND coalesce(sv.state_code,j.value_state)='confirmed')
                  OR (coalesce(sv.state_code,j.value_state)='not_confirmed' AND wc.cycle_state='early_terminal' AND j.value_mature_at>=wc.early_terminal_at)) THEN 1 ELSE 0 END AS value_mature,
  CASE WHEN j.incident_mature_at IS NOT NULL AND j.incident_mature_at<=s.event_time_as_of
             AND si.event_phase IN ('matured','restated')
             AND (coalesce(si.state_code,j.incident_state)<>'none_observed'
                  OR j.incident_mature_at>=j.incident_observation_due_at) THEN 1 ELSE 0 END AS incident_mature,
  CASE WHEN j.reopened_quality_at IS NOT NULL OR j.reopened_value_at IS NOT NULL OR j.reopened_incident_at IS NOT NULL THEN 1 ELSE 0 END AS was_reopened,
  CASE WHEN j.excluded_at IS NOT NULL THEN 1 ELSE 0 END AS excluded,
  coalesce(ar.attempt_count,0) AS attempt_count,coalesce(ar.max_attempt_ordinal,0) AS max_attempt_ordinal,
  coalesce(rr.provider_request_count,0) AS provider_request_count,
  coalesce(rr.failure_driven_request_count,0) AS failure_driven_request_count,
  coalesce(rr.planned_auxiliary_request_count,0) AS planned_auxiliary_request_count,
  coalesce(rr.validation_request_count,0) AS validation_request_count,
  coalesce(rr.fallback_request_count,0) AS fallback_request_count,
  coalesce(rr.retry_request_count,0) AS retry_request_count,
  coalesce(rr.repair_request_count,0) AS repair_request_count,
  coalesce(oo.presented_output_count,0) AS presented_output_count,
  q.first_presented_judged,q.first_presented_accepted,q.first_presented_material_correction,
  q.final_judged,q.final_review_accepted,q.final_material_correction,
  q.required_review_requirement_count,q.satisfied_review_requirement_count,
  q.required_review_coverage_complete,q.required_review_coverage_state,
  CASE WHEN coalesce(sq.state_code,j.acceptance_state)='accepted_first_pass' THEN 1 ELSE 0 END AS accepted_first_pass,
  CASE WHEN coalesce(sq.state_code,j.acceptance_state) IN ('accepted_first_pass','accepted_after_correction') THEN 1 ELSE 0 END AS final_accepted,
  q.any_critical_quality_failure,q.any_sev0_to_sev2,q.all_judged_policy_compliant,q.judged_review_count,q.review_count,q.incident_count,
  coalesce(vr.verified_value,0) AS verified_value,
  coalesce(vr.repeat_value,0) AS repeat_value,
  coalesce(vr.independent_value,0) AS independent_value,
  CASE WHEN coalesce(vr.verified_value,0)=1 AND coalesce(q.first_presented_accepted,0)=1 AND coalesce(q.first_presented_material_correction,1)=0 THEN 1 ELSE 0 END AS end_to_end_first_pass_value,
  coalesce(tr.tool_effect_count,0) AS tool_effect_count,
  coalesce(tr.approval_violation_count,0) AS approval_violation_count,
  coalesce(tr.duplicate_tool_commit_count,0) AS duplicate_tool_commit_count,
  coalesce(tr.overdue_verification_count,0) AS overdue_verification_count,
  coalesce(tr.pending_verification_count,0) AS pending_verification_count,
  coalesce(tr.failed_verification_count,0) AS failed_verification_count,
  coalesce(tr.committed_tool_effect_count,0) AS committed_tool_effect_count,
  coalesce(tr.rolled_back_tool_effect_count,0) AS rolled_back_tool_effect_count,
  c.usage_line_count,c.list_estimated_provider_cost_micros,c.contract_estimated_provider_cost_micros,
  c.reconciled_provider_cost_micros,c.unreconciled_usage_line_count,
  c.explained_direct_delta_micros,c.job_shared_reconciled_cost_micros,
  c.failure_driven_reconciled_cost_micros,c.planned_auxiliary_reconciled_cost_micros,c.validation_reconciled_cost_micros,
  c.internal_minutes,c.customer_review_minutes,c.human_touch_minutes,
  CASE WHEN c.human_touch_minutes>0 THEN 1 ELSE 0 END AS human_touch,
  c.internal_labor_cost_micros,
  c.planned_review_minutes,c.correction_minutes,c.founder_rescue_minutes,c.human_recovery_cost_micros,
  c.other_job_variable_cash_cost_micros,c.job_variable_delivery_cash_cost_micros,c.job_fully_loaded_variable_cost_micros,
  CASE WHEN q.any_sev0_to_sev2=1 OR q.any_critical_quality_failure=1
              OR coalesce(tr.approval_violation_count,0)>0
              OR coalesce(tr.duplicate_tool_commit_count,0)>0
              OR coalesce(tr.overdue_verification_count,0)>0
              OR coalesce(tr.failed_verification_count,0)>0
       THEN 1 ELSE 0 END AS stop_signal_observed,
  CASE
    WHEN coalesce(vr.independent_value,0)=0 THEN 'not_eligible'
    WHEN (wp.founder_rescue_disqualifies_independent_value=1 AND coalesce(c.founder_rescue_minutes,0)>0)
      OR q.any_sev0_to_sev2=1 OR q.any_critical_quality_failure=1
      OR coalesce(tr.approval_violation_count,0)>0
      OR coalesce(tr.duplicate_tool_commit_count,0)>0
      OR coalesce(tr.overdue_verification_count,0)>0
      OR coalesce(tr.failed_verification_count,0)>0
      OR (coalesce(c.planned_review_minutes,0)>0 AND wp.planned_review_allows_independent_value=0)
      THEN 'failed'
    WHEN NOT (j.incident_mature_at IS NOT NULL AND j.incident_mature_at<=s.event_time_as_of
              AND si.event_phase IN ('matured','restated')
              AND (coalesce(si.state_code,j.incident_state)<>'none_observed' OR j.incident_mature_at>=j.incident_observation_due_at))
      OR q.required_review_coverage_complete=0
      OR coalesce(tr.pending_verification_count,0)>0 THEN 'unknown'
    ELSE 'confirmed'
  END AS safe_independent_value_state,
  CASE
    WHEN coalesce(vr.independent_value,0)=1
     AND (wp.founder_rescue_disqualifies_independent_value=0 OR coalesce(c.founder_rescue_minutes,0)=0)
     AND (coalesce(c.planned_review_minutes,0)=0 OR wp.planned_review_allows_independent_value=1)
     AND j.incident_mature_at IS NOT NULL AND j.incident_mature_at<=s.event_time_as_of
     AND si.event_phase IN ('matured','restated')
     AND (coalesce(si.state_code,j.incident_state)<>'none_observed' OR j.incident_mature_at>=j.incident_observation_due_at)
     AND q.required_review_coverage_complete=1
     AND q.any_sev0_to_sev2=0
     AND q.any_critical_quality_failure=0
     AND coalesce(tr.approval_violation_count,0)=0
     AND coalesce(tr.duplicate_tool_commit_count,0)=0
     AND coalesce(tr.overdue_verification_count,0)=0
     AND coalesce(tr.pending_verification_count,0)=0
     AND coalesce(tr.failed_verification_count,0)=0
    THEN 1 ELSE 0
  END AS safe_independent_value,
  coalesce(lr.late_event_count,0) AS late_event_count
FROM v_jobs_asof j
JOIN v_experiment_assignments_asof a ON a.assignment_id=j.assignment_id
JOIN experiment_variants v ON v.variant_id=a.variant_id
JOIN experiments e ON e.experiment_id=a.experiment_id
JOIN workflow_policies wp ON wp.workflow_code=j.workflow_code
JOIN workflow_value_cycles wc ON wc.workflow_value_cycle_id=j.workflow_value_cycle_id
LEFT JOIN v_current_job_states sx ON sx.job_id=j.job_id AND sx.state_axis='execution'
LEFT JOIN v_current_job_states sq ON sq.job_id=j.job_id AND sq.state_axis='acceptance'
LEFT JOIN v_current_job_states sv ON sv.job_id=j.job_id AND sv.state_axis='value'
LEFT JOIN v_current_job_states si ON si.job_id=j.job_id AND si.state_axis='incident'
CROSS JOIN model_settings s
LEFT JOIN attempts_rollup ar USING(job_id)
LEFT JOIN requests_rollup rr USING(job_id)
LEFT JOIN outputs_rollup oo USING(job_id)
LEFT JOIN values_rollup vr USING(job_id)
LEFT JOIN tools_rollup tr USING(job_id)
LEFT JOIN late_rollup lr USING(job_id)
JOIN v_job_quality_facts q USING(job_id)
JOIN v_job_cost_facts c USING(job_id);

CREATE VIEW v_current_job_states AS
WITH ranked AS (
  SELECT e.*,
         row_number() OVER (
           PARTITION BY e.job_id,e.state_axis
           ORDER BY e.occurred_at DESC,
                    CASE e.event_phase WHEN 'restated' THEN 5 WHEN 'reopened' THEN 4 WHEN 'matured' THEN 3 WHEN 'provisional' THEN 2 ELSE 1 END DESC,
                    e.ingested_at DESC,e.job_state_event_id DESC
         ) AS rn
  FROM job_state_events e CROSS JOIN model_settings s
  WHERE e.occurred_at<=s.event_time_as_of AND e.ingested_at<=s.data_watermark_at
)
SELECT * FROM ranked WHERE rn=1;

CREATE VIEW v_experiment_analysis_validity AS
WITH assignment_checks AS (
  SELECT e.experiment_id,e.design_type,
         SUM(CASE WHEN e.design_type='randomized' AND a.assignment_method<>'randomized' THEN 1 ELSE 0 END) AS nonrandom_assignment_rows,
         SUM(CASE WHEN e.design_type='randomized' AND (a.eligible_at<>a.assigned_at OR a.eligible_at<>a.followup_time_zero_at OR a.followup_time_zero_at<>e.starts_at) THEN 1 ELSE 0 END) AS time_zero_misaligned_rows
  FROM experiments e LEFT JOIN v_experiment_assignments_asof a USING(experiment_id)
  GROUP BY e.experiment_id,e.design_type
),
scope_checks AS (
  SELECT experiment_id,SUM(scope_mismatch) AS scope_mismatch_rows
  FROM (
    SELECT x.experiment_id,
           CASE WHEN c.workflow_code<>e.workflow_code
                       OR c.natural_cycle_start<e.starts_at
                       OR (e.ends_at IS NOT NULL AND c.natural_cycle_start>e.ends_at)
                THEN 1 ELSE 0 END AS scope_mismatch
    FROM v_workflow_cycle_experiment_assignments_asof x
    JOIN workflow_value_cycles c USING(workflow_value_cycle_id)
    JOIN experiments e USING(experiment_id)
    UNION ALL
    SELECT a.experiment_id,
           CASE WHEN j.workflow_code<>e.workflow_code OR j.feature_code<>e.feature_code THEN 1 ELSE 0 END
    FROM v_jobs_asof j JOIN v_experiment_assignments_asof a USING(assignment_id)
    JOIN experiments e USING(experiment_id)
  ) scoped_rows
  GROUP BY experiment_id
)
SELECT a.experiment_id,a.design_type,
       coalesce(a.nonrandom_assignment_rows,0) AS nonrandom_assignment_rows,
       coalesce(a.time_zero_misaligned_rows,0) AS time_zero_misaligned_rows,
       coalesce(s.scope_mismatch_rows,0) AS scope_mismatch_rows,
       CASE
         WHEN a.design_type='randomized'
          AND coalesce(a.nonrandom_assignment_rows,0)=0
          AND coalesce(a.time_zero_misaligned_rows,0)=0
          AND coalesce(s.scope_mismatch_rows,0)=0 THEN 'randomized_itt_valid'
         WHEN a.design_type='randomized' THEN 'invalid_randomized_load_association_only'
         ELSE 'association_only'
       END AS analysis_validity
FROM assignment_checks a LEFT JOIN scope_checks s USING(experiment_id);

CREATE VIEW v_variant_operating_review AS
WITH assigned AS (
  SELECT experiment_id,variant_id,COUNT(DISTINCT account_id) AS assigned_accounts
  FROM v_experiment_assignments_asof GROUP BY experiment_id,variant_id
),
agg AS (
  SELECT
    experiment_id,variant_id,variant_code,design_type,
    COUNT(*) AS all_eligible_jobs,
    SUM(CASE WHEN excluded=0 THEN 1 ELSE 0 END) AS analyzable_eligible_jobs,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 THEN 1 ELSE 0 END) AS execution_mature_eligible_jobs,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 AND exposed_at IS NOT NULL THEN 1 ELSE 0 END) AS execution_mature_exposed_jobs,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 AND started_at IS NOT NULL THEN 1 ELSE 0 END) AS execution_mature_started_jobs,
    SUM(CASE WHEN excluded=0 AND quality_mature=1 AND started_at IS NOT NULL THEN 1 ELSE 0 END) AS quality_mature_started_jobs,
    SUM(CASE WHEN excluded=0 AND quality_mature=1 AND started_at IS NOT NULL AND accepted_first_pass=1 THEN 1 ELSE 0 END) AS accepted_first_pass_jobs,
    SUM(CASE WHEN excluded=0 AND quality_mature=1 AND started_at IS NOT NULL AND final_accepted=1 THEN 1 ELSE 0 END) AS final_accepted_jobs,
    SUM(CASE WHEN excluded=0 AND value_mature=1 THEN 1 ELSE 0 END) AS value_mature_eligible_jobs,
    SUM(CASE WHEN excluded=0 AND value_mature=1 AND end_to_end_first_pass_value=1 THEN 1 ELSE 0 END) AS first_pass_value_jobs,
    SUM(CASE WHEN excluded=0 AND value_mature=1 AND verified_value=1 THEN 1 ELSE 0 END) AS verified_value_jobs,
    SUM(CASE WHEN excluded=0 AND value_mature=1 AND independent_value=1 THEN 1 ELSE 0 END) AS independent_value_jobs,
    SUM(CASE WHEN excluded=0 AND value_mature=1 AND safe_independent_value=1 THEN 1 ELSE 0 END) AS safe_independent_value_jobs,
    SUM(CASE WHEN excluded=0 AND value_mature=1 AND stop_signal_observed=1 THEN 1 ELSE 0 END) AS value_mature_critical_guardrail_jobs,
    SUM(CASE WHEN excluded=0 AND quality_mature=1 AND started_at IS NOT NULL AND human_touch=1 THEN 1 ELSE 0 END) AS quality_mature_human_touch_jobs,
    SUM(CASE WHEN excluded=0 AND value_mature=1 AND was_reopened=1 THEN 1 ELSE 0 END) AS value_mature_reopened_jobs,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 THEN coalesce(reconciled_provider_cost_micros,0) ELSE 0 END) AS execution_mature_reconciled_provider_cost_micros,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 THEN failure_driven_reconciled_cost_micros ELSE 0 END) AS execution_mature_failure_driven_cost_micros,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 THEN planned_auxiliary_reconciled_cost_micros ELSE 0 END) AS execution_mature_planned_auxiliary_cost_micros,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 THEN validation_reconciled_cost_micros ELSE 0 END) AS execution_mature_validation_cost_micros,
    SUM(CASE WHEN excluded=0 AND value_mature=1 THEN internal_labor_cost_micros ELSE 0 END) AS value_mature_internal_labor_cost_micros,
    SUM(CASE WHEN excluded=0 AND value_mature=1 THEN coalesce(job_variable_delivery_cash_cost_micros,0) ELSE 0 END) AS value_mature_variable_delivery_cash_cost_micros,
    SUM(CASE WHEN excluded=0 AND value_mature=1 THEN coalesce(job_fully_loaded_variable_cost_micros,0) ELSE 0 END) AS value_mature_fully_loaded_variable_cost_micros,
    SUM(CASE WHEN excluded=0 AND execution_mature=1 THEN unreconciled_usage_line_count ELSE 0 END) AS execution_mature_unreconciled_usage_lines,
    SUM(CASE WHEN excluded=0 AND value_mature=1 THEN unreconciled_usage_line_count ELSE 0 END) AS value_mature_unreconciled_usage_lines
  FROM v_job_operating_facts GROUP BY experiment_id,variant_id,variant_code,design_type
)
SELECT
  a.experiment_id,a.variant_id,a.variant_code,s.assigned_accounts,
  ev.analysis_validity,
  CASE WHEN ev.analysis_validity='randomized_itt_valid' THEN 'valid randomized assignment; this view remains job-weighted operational, not ITT'
       ELSE 'association_only; randomized/ITT claims prohibited' END AS interpretation,
  a.all_eligible_jobs,a.analyzable_eligible_jobs,a.execution_mature_eligible_jobs,
  a.execution_mature_exposed_jobs,a.execution_mature_started_jobs,
  a.quality_mature_started_jobs,a.accepted_first_pass_jobs,a.final_accepted_jobs,
  a.value_mature_eligible_jobs,a.first_pass_value_jobs,a.verified_value_jobs,
  a.independent_value_jobs,a.safe_independent_value_jobs,a.value_mature_critical_guardrail_jobs,
  a.quality_mature_human_touch_jobs,a.value_mature_reopened_jobs,
  ROUND(100.0*a.execution_mature_exposed_jobs/NULLIF(a.execution_mature_eligible_jobs,0),1) AS exposure_rate_pct,
  ROUND(100.0*a.execution_mature_started_jobs/NULLIF(a.execution_mature_eligible_jobs,0),1) AS activation_per_eligible_pct,
  ROUND(100.0*a.execution_mature_started_jobs/NULLIF(a.execution_mature_exposed_jobs,0),1) AS activation_per_exposed_pct,
  ROUND(100.0*a.accepted_first_pass_jobs/NULLIF(a.quality_mature_started_jobs,0),1) AS first_pass_acceptance_started_pct,
  ROUND(100.0*a.final_accepted_jobs/NULLIF(a.quality_mature_started_jobs,0),1) AS final_acceptance_started_pct,
  ROUND(100.0*a.first_pass_value_jobs/NULLIF(a.value_mature_eligible_jobs,0),1) AS end_to_end_first_pass_value_pct,
  ROUND(100.0*a.verified_value_jobs/NULLIF(a.value_mature_eligible_jobs,0),1) AS value_yield_pct,
  ROUND(100.0*a.safe_independent_value_jobs/NULLIF(a.value_mature_eligible_jobs,0),1) AS safe_independent_value_yield_pct,
  CASE WHEN a.execution_mature_unreconciled_usage_lines=0 THEN a.execution_mature_reconciled_provider_cost_micros END AS execution_mature_reconciled_provider_cost_micros,
  CASE WHEN a.execution_mature_unreconciled_usage_lines=0 THEN a.execution_mature_failure_driven_cost_micros END AS execution_mature_failure_driven_cost_micros,
  CASE WHEN a.execution_mature_unreconciled_usage_lines=0 THEN a.execution_mature_planned_auxiliary_cost_micros END AS execution_mature_planned_auxiliary_cost_micros,
  CASE WHEN a.execution_mature_unreconciled_usage_lines=0 THEN a.execution_mature_validation_cost_micros END AS execution_mature_validation_cost_micros,
  CASE WHEN a.execution_mature_unreconciled_usage_lines=0 THEN ROUND(100.0*a.execution_mature_failure_driven_cost_micros/NULLIF(a.execution_mature_reconciled_provider_cost_micros,0),1) END AS execution_mature_failure_driven_cost_share_pct,
  a.value_mature_internal_labor_cost_micros,
  CASE WHEN a.value_mature_unreconciled_usage_lines=0 THEN a.value_mature_variable_delivery_cash_cost_micros END AS value_mature_variable_delivery_cash_cost_micros,
  CASE WHEN a.value_mature_unreconciled_usage_lines=0 THEN a.value_mature_fully_loaded_variable_cost_micros END AS value_mature_fully_loaded_variable_cost_micros,
  CASE WHEN a.value_mature_unreconciled_usage_lines=0 THEN CAST(ROUND(a.value_mature_fully_loaded_variable_cost_micros*1.0/NULLIF(a.safe_independent_value_jobs,0)) AS INTEGER) END AS value_mature_fully_loaded_cost_per_safe_independent_value_micros,
  a.execution_mature_unreconciled_usage_lines,a.value_mature_unreconciled_usage_lines
FROM agg a
JOIN assigned s USING(experiment_id,variant_id)
JOIN v_experiment_analysis_validity ev USING(experiment_id);

CREATE VIEW v_workflow_cycle_facts AS
WITH cycle_values AS (
  SELECT
    c.workflow_value_cycle_id,
    MAX(CASE WHEN v.value_kind='verified_value' AND v.occurred_at<=c.on_time_due_at THEN 1 ELSE 0 END) AS on_time_verified_value,
    MAX(CASE WHEN v.value_kind='repeat_value' AND v.occurred_at<=c.on_time_due_at THEN 1 ELSE 0 END) AS on_time_repeat_value,
    MAX(CASE WHEN v.value_kind='independent_value' AND v.occurred_at<=c.on_time_due_at THEN 1 ELSE 0 END) AS on_time_independent_value,
    MAX(CASE WHEN v.value_kind='verified_value' THEN 1 ELSE 0 END) AS eventual_verified_value,
    MAX(CASE WHEN v.value_kind='repeat_value' THEN 1 ELSE 0 END) AS eventual_repeat_value,
    MAX(CASE WHEN v.value_kind='independent_value' THEN 1 ELSE 0 END) AS eventual_independent_value
  FROM workflow_value_cycles c
  LEFT JOIN v_current_value_events v USING(workflow_value_cycle_id)
  GROUP BY c.workflow_value_cycle_id
),
job_rollup AS (
  SELECT workflow_value_cycle_id,experiment_id,variant_id,COUNT(*) AS eligible_jobs,
         SUM(CASE WHEN started_at IS NOT NULL THEN 1 ELSE 0 END) AS started_jobs,
         MAX(was_reopened) AS cycle_has_reopened_job,
         MAX(safe_independent_value) AS safe_independent_value
  FROM v_job_operating_facts GROUP BY workflow_value_cycle_id,experiment_id,variant_id
)
SELECT
  c.workflow_value_cycle_id,c.account_id,c.workflow_code,c.cycle_number,
  c.natural_cycle_start,c.natural_cycle_end,c.on_time_due_at,c.maturity_due_at,
  c.cycle_state,c.early_terminal_at,
  x.cycle_experiment_assignment_id,a.experiment_id,a.variant_id,v.variant_code,e.design_type,
  ev.analysis_validity,
  CASE
    WHEN c.cycle_state='excluded' THEN 0
    WHEN c.cycle_state='early_terminal' AND c.early_terminal_at IS NOT NULL
         AND c.early_terminal_at<=s.event_time_as_of AND c.matured_at>=c.early_terminal_at THEN 1
    WHEN c.cycle_state='matured' AND c.matured_at IS NOT NULL
         AND c.matured_at>=c.maturity_due_at AND c.matured_at<=s.event_time_as_of THEN 1
    ELSE 0
  END AS mature_denominator,
  CASE WHEN c.early_terminal_at IS NOT NULL AND c.early_terminal_at<=s.event_time_as_of THEN 1 ELSE 0 END AS early_terminal,
  coalesce(j.eligible_jobs,0) AS eligible_jobs,coalesce(j.started_jobs,0) AS started_jobs,
  coalesce(j.cycle_has_reopened_job,0) AS cycle_has_reopened_job,
  coalesce(cv.on_time_verified_value,0) AS on_time_verified_value,
  coalesce(cv.on_time_repeat_value,0) AS on_time_repeat_value,
  coalesce(cv.on_time_independent_value,0) AS on_time_independent_value,
  coalesce(cv.eventual_verified_value,0) AS eventual_verified_value,
  coalesce(cv.eventual_repeat_value,0) AS eventual_repeat_value,
  coalesce(cv.eventual_independent_value,0) AS eventual_independent_value,
  coalesce(j.safe_independent_value,0) AS safe_independent_value
FROM workflow_value_cycles c
JOIN v_workflow_cycle_experiment_assignments_asof x USING(workflow_value_cycle_id)
JOIN v_experiment_assignments_asof a
  ON a.assignment_id=x.assignment_id AND a.experiment_id=x.experiment_id AND a.account_id=x.account_id
JOIN experiment_variants v ON v.variant_id=a.variant_id
JOIN experiments e ON e.experiment_id=a.experiment_id
JOIN v_experiment_analysis_validity ev ON ev.experiment_id=a.experiment_id
CROSS JOIN model_settings s
LEFT JOIN cycle_values cv USING(workflow_value_cycle_id)
LEFT JOIN job_rollup j
  ON j.workflow_value_cycle_id=c.workflow_value_cycle_id
 AND j.experiment_id=a.experiment_id AND j.variant_id=a.variant_id;

CREATE VIEW v_assignment_expected_cycle_spine AS
SELECT a.experiment_id,a.assignment_id,a.variant_id,v.variant_code,a.account_id,
       e.workflow_code,cal.cycle_number,
       cal.natural_cycle_start AS expected_cycle_start,
       cal.natural_cycle_end AS expected_cycle_end,
       cal.on_time_due_at AS expected_on_time_due_at,
       cal.maturity_due_at AS expected_maturity_due_at,
       c.workflow_value_cycle_id,x.cycle_experiment_assignment_id,
       ev.analysis_validity,
       CASE WHEN c.workflow_value_cycle_id IS NULL OR x.cycle_experiment_assignment_id IS NULL THEN 1 ELSE 0 END AS missing_expected_cycle,
       CASE WHEN c.workflow_value_cycle_id IS NULL OR x.cycle_experiment_assignment_id IS NULL
                  OR ((cal.maturity_due_at<=s.event_time_as_of
                       OR (ac.early_terminal_at IS NOT NULL AND ac.early_terminal_at<=s.event_time_as_of AND ac.early_terminal_at<=cal.maturity_due_at))
                      AND coalesce(f.mature_denominator,0)<>1)
            THEN 1 ELSE 0 END AS expected_cycle_coverage_incomplete,
       CASE WHEN cal.maturity_due_at<=s.event_time_as_of
                  OR (ac.early_terminal_at IS NOT NULL AND ac.early_terminal_at<=s.event_time_as_of AND ac.early_terminal_at<=cal.maturity_due_at)
            THEN 1 ELSE 0 END AS expected_mature_denominator,
       CASE WHEN ac.early_terminal_at IS NOT NULL AND ac.early_terminal_at<=s.event_time_as_of
                  AND ac.early_terminal_at<=cal.maturity_due_at THEN 1 ELSE 0 END AS early_terminal,
       coalesce(f.on_time_verified_value,0) AS on_time_verified_value,
       coalesce(f.eventual_verified_value,0) AS eventual_verified_value,
       coalesce(f.eventual_independent_value,0) AS eventual_independent_value
FROM v_experiment_assignments_asof a
JOIN experiments e USING(experiment_id)
JOIN experiment_variants v USING(variant_id)
JOIN accounts ac USING(account_id)
CROSS JOIN model_settings s
JOIN workflow_cycle_calendar cal
  ON cal.workflow_code=e.workflow_code
 AND cal.natural_cycle_start>=a.followup_time_zero_at
 AND cal.natural_cycle_start<=s.event_time_as_of
 AND (e.ends_at IS NULL OR cal.natural_cycle_start<=e.ends_at)
LEFT JOIN workflow_value_cycles c
  ON c.account_id=a.account_id AND c.workflow_code=e.workflow_code AND c.cycle_number=cal.cycle_number
LEFT JOIN v_workflow_cycle_experiment_assignments_asof x
  ON x.workflow_value_cycle_id=c.workflow_value_cycle_id
 AND x.experiment_id=a.experiment_id AND x.assignment_id=a.assignment_id
LEFT JOIN v_workflow_cycle_facts f
  ON f.workflow_value_cycle_id=c.workflow_value_cycle_id AND f.experiment_id=a.experiment_id
JOIN v_experiment_analysis_validity ev USING(experiment_id);

CREATE VIEW v_cycle_retention AS
SELECT
  experiment_id,variant_id,variant_code,cycle_number,analysis_validity,
  CASE WHEN analysis_validity='randomized_itt_valid' AND SUM(expected_cycle_coverage_incomplete)=0
       THEN 'account-level eligible ITT; cycle-N verified value'
       WHEN analysis_validity='randomized_itt_valid' THEN 'Unknown: expected-cycle coverage incomplete; ITT estimate prohibited'
       ELSE 'association_only; ITT label prohibited' END AS interpretation,
  COUNT(DISTINCT account_id) AS assigned_accounts,
  COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 THEN account_id END) AS mature_accounts,
  COUNT(DISTINCT CASE WHEN missing_expected_cycle=1 THEN account_id END) AS missing_expected_cycle_accounts,
  COUNT(DISTINCT CASE WHEN expected_cycle_coverage_incomplete=1 THEN account_id END) AS incomplete_expected_cycle_accounts,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN expected_cycle_coverage_incomplete=0 THEN account_id END)/NULLIF(COUNT(DISTINCT account_id),0),1) AS expected_cycle_coverage_pct,
  COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 AND early_terminal=1 THEN account_id END) AS early_terminal_accounts,
  COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 AND on_time_verified_value=1 THEN account_id END) AS on_time_verified_value_accounts,
  COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 AND eventual_verified_value=1 THEN account_id END) AS eventual_verified_value_accounts,
  COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 AND eventual_independent_value=1 THEN account_id END) AS eventual_independent_accounts,
  CASE WHEN analysis_validity='randomized_itt_valid' AND SUM(expected_cycle_coverage_incomplete)=0
       THEN ROUND(100.0*COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 AND on_time_verified_value=1 THEN account_id END)/NULLIF(COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 THEN account_id END),0),1) END AS on_time_verified_value_retention_pct,
  CASE WHEN analysis_validity='randomized_itt_valid' AND SUM(expected_cycle_coverage_incomplete)=0
       THEN ROUND(100.0*COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 AND eventual_verified_value=1 THEN account_id END)/NULLIF(COUNT(DISTINCT CASE WHEN expected_mature_denominator=1 THEN account_id END),0),1) END AS eventual_verified_value_retention_pct
FROM v_assignment_expected_cycle_spine
WHERE cycle_number>=2
GROUP BY experiment_id,variant_id,variant_code,cycle_number,analysis_validity;

-- Cohort-relative views are intentionally separate from eligible/ITT retention.
-- Each entry exposes the evidence event and, for independent-value cohorts, the
-- predecessor verified-value evidence that makes the cohort definition auditable.
CREATE VIEW v_value_cohort_entries AS
WITH ranked AS (
  SELECT v.value_event_id,v.evidence_ref_hash,v.value_kind,v.occurred_at,
         j.account_id,j.workflow_code,v.workflow_value_cycle_id,c.cycle_number,
         row_number() OVER (
           PARTITION BY j.account_id,j.workflow_code,v.value_kind
           ORDER BY v.occurred_at,v.value_event_id
         ) AS rn
  FROM v_current_value_events v
  JOIN v_jobs_asof j USING(job_id)
  JOIN workflow_value_cycles c USING(workflow_value_cycle_id)
  WHERE v.value_kind IN ('verified_value','independent_value')
), firsts AS (
  SELECT * FROM ranked WHERE rn=1
)
SELECT f.account_id,f.workflow_code,f.workflow_value_cycle_id AS entry_workflow_value_cycle_id,
       x.experiment_id,a.variant_id,v.variant_code,
       CASE f.value_kind WHEN 'verified_value' THEN 'first_verified_value' ELSE 'first_independent_value' END AS entry_type,
       f.occurred_at AS entry_at,f.cycle_number AS entry_cycle_number,
       f.value_event_id AS entry_evidence_event_id,f.evidence_ref_hash AS entry_evidence_ref_hash,
       CASE WHEN f.value_kind='independent_value' THEN (
         SELECT pv.value_event_id
         FROM v_current_value_events pv JOIN v_jobs_asof pj USING(job_id)
         WHERE pj.account_id=f.account_id AND pj.workflow_code=f.workflow_code
           AND pv.value_kind='verified_value' AND pv.occurred_at<=f.occurred_at
         ORDER BY pv.occurred_at DESC,pv.value_event_id DESC LIMIT 1
       ) END AS predecessor_verified_value_event_id,
       CASE WHEN f.value_kind='independent_value' THEN (
         SELECT pv.evidence_ref_hash
         FROM v_current_value_events pv JOIN v_jobs_asof pj USING(job_id)
         WHERE pj.account_id=f.account_id AND pj.workflow_code=f.workflow_code
           AND pv.value_kind='verified_value' AND pv.occurred_at<=f.occurred_at
         ORDER BY pv.occurred_at DESC,pv.value_event_id DESC LIMIT 1
       ) END AS predecessor_evidence_ref_hash
FROM firsts f
JOIN v_workflow_cycle_experiment_assignments_asof x ON x.workflow_value_cycle_id=f.workflow_value_cycle_id
JOIN v_experiment_assignments_asof a ON a.assignment_id=x.assignment_id AND a.experiment_id=x.experiment_id
JOIN experiment_variants v ON v.variant_id=a.variant_id;

CREATE VIEW v_first_value_cohort_entries AS
SELECT * FROM v_value_cohort_entries WHERE entry_type='first_verified_value';

CREATE VIEW v_independent_value_cohort_entries AS
SELECT * FROM v_value_cohort_entries WHERE entry_type='first_independent_value';

CREATE VIEW v_cohort_relative_value_retention AS
SELECT ce.account_id,ce.experiment_id,ce.variant_id,ce.variant_code,ce.entry_type,
       ce.entry_at,ce.entry_cycle_number,c.cycle_number-ce.entry_cycle_number AS relative_cycle_number,
       ce.entry_evidence_event_id,ce.predecessor_verified_value_event_id,ce.predecessor_evidence_ref_hash,
       CASE WHEN c.cycle_state='early_terminal' AND c.early_terminal_at IS NOT NULL AND c.early_terminal_at<=s.event_time_as_of AND c.matured_at>=c.early_terminal_at THEN 1
            WHEN c.cycle_state='matured' AND c.matured_at IS NOT NULL AND c.matured_at>=c.maturity_due_at AND c.matured_at<=s.event_time_as_of THEN 1
            ELSE 0 END AS mature_denominator,
       MAX(CASE WHEN vv.value_kind='verified_value' THEN 1 ELSE 0 END) AS verified_value_in_relative_cycle,
       MAX(CASE WHEN vv.value_kind='repeat_value' THEN 1 ELSE 0 END) AS repeat_value_in_relative_cycle,
       MAX(CASE WHEN vv.value_kind='independent_value' THEN 1 ELSE 0 END) AS independent_value_in_relative_cycle
FROM v_value_cohort_entries ce
JOIN workflow_value_cycles c ON c.account_id=ce.account_id AND c.workflow_code=ce.workflow_code AND c.cycle_number>=ce.entry_cycle_number
LEFT JOIN v_current_value_events vv USING(workflow_value_cycle_id)
CROSS JOIN model_settings s
GROUP BY ce.account_id,ce.experiment_id,ce.variant_id,ce.variant_code,ce.entry_type,
         ce.entry_at,ce.entry_cycle_number,c.cycle_number,ce.entry_evidence_event_id,
         ce.predecessor_verified_value_event_id,ce.predecessor_evidence_ref_hash,c.cycle_state,
         c.matured_at,c.early_terminal_at,c.maturity_due_at,s.event_time_as_of;

CREATE VIEW v_first_value_cohort_retention AS
SELECT experiment_id,variant_id,variant_code,entry_cycle_number AS cohort_entry_cycle_number,
       relative_cycle_number,'first_verified_value' AS cohort_entry_type,
       COUNT(DISTINCT CASE WHEN mature_denominator=1 THEN account_id END) AS mature_cohort_accounts,
       COUNT(DISTINCT CASE WHEN mature_denominator=1 AND repeat_value_in_relative_cycle=1 THEN account_id END) AS repeat_value_accounts,
       ROUND(100.0*COUNT(DISTINCT CASE WHEN mature_denominator=1 AND repeat_value_in_relative_cycle=1 THEN account_id END)
             /NULLIF(COUNT(DISTINCT CASE WHEN mature_denominator=1 THEN account_id END),0),1) AS repeat_value_retention_pct
FROM v_cohort_relative_value_retention
WHERE entry_type='first_verified_value' AND relative_cycle_number>=1
GROUP BY experiment_id,variant_id,variant_code,entry_cycle_number,relative_cycle_number;

CREATE VIEW v_independent_value_cohort_retention AS
SELECT * FROM v_cohort_relative_value_retention
WHERE entry_type='first_independent_value';

CREATE VIEW v_commercial_cycle_facts AS
WITH revenue AS (
  SELECT account_commercial_cycle_id,
         SUM(CASE WHEN line_kind='gross_revenue' THEN amount_micros ELSE 0 END) AS gross_customer_revenue_micros,
         SUM(CASE WHEN line_kind<>'gross_revenue' THEN amount_micros ELSE 0 END) AS contra_revenue_micros,
         SUM(CASE WHEN line_kind='gross_revenue' AND entry_action='recognize' THEN amount_micros ELSE 0 END) AS recognized_gross_revenue_micros,
         SUM(CASE WHEN line_kind='gross_revenue' AND entry_action='reverse' THEN amount_micros ELSE 0 END) AS gross_revenue_reversal_micros,
         SUM(CASE WHEN line_kind<>'gross_revenue' AND entry_action='recognize' THEN amount_micros ELSE 0 END) AS recognized_contra_revenue_micros,
         SUM(CASE WHEN line_kind<>'gross_revenue' AND entry_action='reverse' THEN amount_micros ELSE 0 END) AS contra_revenue_reversal_micros,
         SUM(amount_micros) AS net_customer_revenue_micros
  FROM v_customer_revenue_ledger_asof GROUP BY account_commercial_cycle_id
),
mapped_jobs AS (
  SELECT m.account_commercial_cycle_id,
         SUM(j.job_variable_delivery_cash_cost_micros) AS job_variable_delivery_cash_cost_micros,
         SUM(j.internal_labor_cost_micros) AS internal_labor_cost_micros,
         SUM(j.unreconciled_usage_line_count) AS unreconciled_usage_lines,
         COUNT(*) AS eligible_jobs
  FROM workflow_commercial_cycle_map m
  JOIN v_job_operating_facts j USING(workflow_value_cycle_id)
  GROUP BY m.account_commercial_cycle_id
),
other_cash AS (
  SELECT account_commercial_cycle_id,SUM(amount_micros) AS other_variable_cash_cost_micros
  FROM v_variable_cash_cost_ledger_asof WHERE allocation_scope='commercial_cycle' GROUP BY account_commercial_cycle_id
),
mapping_status AS (
  SELECT m.account_commercial_cycle_id,
         COUNT(DISTINCT m.workflow_value_cycle_id) AS mapped_workflow_cycles,
         SUM(CASE WHEN f.allocation_status='unallocated' THEN 1 ELSE 0 END) AS unallocated_feature_revenue_mappings,
         SUM(CASE WHEN f.allocation_status<>'unallocated' THEN f.allocated_feature_net_revenue_micros ELSE 0 END) AS allocated_feature_net_revenue_micros
  FROM workflow_commercial_cycle_map m
  LEFT JOIN v_feature_revenue_allocations_asof f
    ON f.workflow_value_cycle_id=m.workflow_value_cycle_id
   AND f.account_commercial_cycle_id=m.account_commercial_cycle_id
  GROUP BY m.account_commercial_cycle_id
),
global_unallocated AS (
  SELECT SUM(CASE WHEN allocation_kind='unallocated_charge' THEN amount_micros ELSE 0 END) AS global_unallocated_provider_charge_micros
  FROM v_invoice_allocations_asof
)
SELECT
  c.account_commercial_cycle_id,c.account_id,c.billing_period_start,c.billing_period_end,c.currency,
  c.commercial_state_asof AS commercial_state,
  coalesce(r.gross_customer_revenue_micros,0) AS gross_customer_revenue_micros,
  coalesce(r.contra_revenue_micros,0) AS contra_revenue_micros,
  coalesce(r.recognized_gross_revenue_micros,0) AS recognized_gross_revenue_micros,
  coalesce(r.gross_revenue_reversal_micros,0) AS gross_revenue_reversal_micros,
  coalesce(r.recognized_contra_revenue_micros,0) AS recognized_contra_revenue_micros,
  coalesce(r.contra_revenue_reversal_micros,0) AS contra_revenue_reversal_micros,
  coalesce(r.net_customer_revenue_micros,0) AS net_customer_revenue_micros,
  coalesce(j.eligible_jobs,0) AS eligible_jobs,
  j.job_variable_delivery_cash_cost_micros,
  coalesce(o.other_variable_cash_cost_micros,0) AS other_variable_cash_cost_micros,
  coalesce(j.internal_labor_cost_micros,0) AS internal_labor_cost_micros,
  coalesce(j.unreconciled_usage_lines,0) AS unreconciled_usage_lines,
  CASE WHEN coalesce(j.unreconciled_usage_lines,0)=0
       THEN coalesce(r.net_customer_revenue_micros,0)-coalesce(j.job_variable_delivery_cash_cost_micros,0)-coalesce(o.other_variable_cash_cost_micros,0)
       ELSE NULL END AS account_cycle_cash_contribution_micros,
  CASE WHEN coalesce(j.unreconciled_usage_lines,0)=0
       THEN coalesce(r.net_customer_revenue_micros,0)-coalesce(j.job_variable_delivery_cash_cost_micros,0)-coalesce(o.other_variable_cash_cost_micros,0)-coalesce(j.internal_labor_cost_micros,0)
       ELSE NULL END AS account_cycle_fully_loaded_contribution_micros,
  coalesce(ms.mapped_workflow_cycles,0) AS mapped_workflow_cycles,
  coalesce(ms.unallocated_feature_revenue_mappings,0) AS unallocated_feature_revenue_mappings,
  coalesce(ms.allocated_feature_net_revenue_micros,0) AS allocated_feature_net_revenue_micros,
  CASE WHEN g.global_unallocated_provider_charge_micros>0 THEN 'partial_global_provider_cost_unallocated' ELSE 'complete' END AS cost_allocation_status,
  g.global_unallocated_provider_charge_micros AS global_unallocated_provider_charge_micros_do_not_sum
FROM v_account_commercial_cycles_asof c
LEFT JOIN revenue r USING(account_commercial_cycle_id)
LEFT JOIN mapped_jobs j USING(account_commercial_cycle_id)
LEFT JOIN other_cash o USING(account_commercial_cycle_id)
LEFT JOIN mapping_status ms USING(account_commercial_cycle_id)
CROSS JOIN global_unallocated g;

CREATE VIEW v_invoice_line_reconciliation AS
SELECT
  i.provider_invoice_line_id,i.provider_code,i.invoice_ref_hash,i.sku_code,i.economic_class,
  i.entry_action,i.corrects_invoice_line_id,i.currency,i.invoice_amount_micros,
  coalesce(SUM(a.amount_micros),0) AS allocated_amount_micros,
  i.invoice_amount_micros-coalesce(SUM(a.amount_micros),0) AS residual_micros,
  SUM(CASE WHEN a.allocation_kind='direct_usage' THEN a.amount_micros ELSE 0 END) AS direct_usage_micros,
  SUM(CASE WHEN a.allocation_kind='job_shared' THEN a.amount_micros ELSE 0 END) AS job_shared_micros,
  SUM(CASE WHEN a.allocation_kind='unallocated_charge' THEN a.amount_micros ELSE 0 END) AS unallocated_charge_micros,
  SUM(CASE WHEN a.allocation_kind='provider_credit_unallocated' THEN a.amount_micros ELSE 0 END) AS recognized_provider_credit_micros,
  SUM(CASE WHEN a.allocation_kind='provider_credit_reversal_unallocated' THEN a.amount_micros ELSE 0 END) AS provider_credit_reversal_micros,
  SUM(CASE WHEN a.allocation_kind IN ('provider_credit_unallocated','provider_credit_reversal_unallocated') THEN a.amount_micros ELSE 0 END) AS provider_credit_micros,
  CASE
    WHEN i.invoice_amount_micros<>coalesce(SUM(a.amount_micros),0) THEN 'delta'
    WHEN SUM(CASE WHEN a.allocation_kind IN ('unallocated_charge','provider_credit_unallocated','provider_credit_reversal_unallocated','tax_unallocated','rounding_unallocated') THEN 1 ELSE 0 END)>0 THEN 'reconciled_with_explicit_unallocated'
    ELSE 'reconciled'
  END AS reconciliation_status
FROM v_provider_invoice_lines_asof i LEFT JOIN v_invoice_allocations_asof a USING(provider_invoice_line_id)
GROUP BY i.provider_invoice_line_id;

CREATE VIEW v_provider_invoice_summary AS
WITH invoices AS (
  SELECT provider_code,invoice_ref_hash,currency,
         SUM(CASE WHEN economic_class IN ('usage_charge','shared_charge','tax','marketplace_fee','rounding') THEN invoice_amount_micros ELSE 0 END) AS gross_invoiced_cost_micros,
         SUM(CASE WHEN economic_class IN ('usage_charge','shared_charge','tax','marketplace_fee','rounding') AND entry_action='recognize' THEN invoice_amount_micros ELSE 0 END) AS recognized_gross_invoiced_cost_micros,
         SUM(CASE WHEN economic_class IN ('usage_charge','shared_charge','tax','marketplace_fee','rounding') AND entry_action='reverse' THEN invoice_amount_micros ELSE 0 END) AS gross_invoiced_cost_reversal_micros,
         SUM(CASE WHEN economic_class IN ('provider_service_credit','contract_discount') THEN invoice_amount_micros ELSE 0 END) AS provider_credit_and_discount_micros,
         SUM(CASE WHEN economic_class IN ('provider_service_credit','contract_discount') AND entry_action='recognize' THEN invoice_amount_micros ELSE 0 END) AS recognized_provider_credit_and_discount_micros,
         SUM(CASE WHEN economic_class IN ('provider_service_credit','contract_discount') AND entry_action='reverse' THEN invoice_amount_micros ELSE 0 END) AS provider_credit_and_discount_reversal_micros,
         SUM(invoice_amount_micros) AS invoice_payable_micros,
         SUM(residual_micros) AS reconciliation_delta_micros,
         SUM(unallocated_charge_micros) AS unallocated_charge_micros
  FROM v_invoice_line_reconciliation
  GROUP BY provider_code,invoice_ref_hash,currency
),
settlements AS (
  SELECT provider_code,invoice_ref_hash,currency,
         SUM(CASE WHEN settlement_kind='cash_payment' THEN amount_micros ELSE 0 END) AS cash_paid_micros,
         SUM(CASE WHEN settlement_kind='promotional_credit' THEN amount_micros ELSE 0 END) AS promotional_credit_applied_micros,
         SUM(CASE WHEN settlement_kind='prepaid_application' THEN amount_micros ELSE 0 END) AS prepaid_applied_micros
  FROM v_provider_settlements_asof GROUP BY provider_code,invoice_ref_hash,currency
)
SELECT i.*,coalesce(s.cash_paid_micros,0) AS cash_paid_micros,
       coalesce(s.promotional_credit_applied_micros,0) AS promotional_credit_applied_micros,
       coalesce(s.prepaid_applied_micros,0) AS prepaid_applied_micros,
       i.invoice_payable_micros-coalesce(s.cash_paid_micros,0)-coalesce(s.promotional_credit_applied_micros,0)-coalesce(s.prepaid_applied_micros,0) AS settlement_delta_micros
FROM invoices i LEFT JOIN settlements s USING(provider_code,invoice_ref_hash,currency);

CREATE VIEW v_data_quality_checks AS
SELECT 'DQ01' AS check_id,'error' AS severity,'mature_execution_missing' AS check_name,
       (SELECT COUNT(*) FROM v_jobs_asof j CROSS JOIN model_settings s WHERE j.excluded_at IS NULL AND j.execution_due_at<=s.event_time_as_of AND j.execution_mature_at IS NULL) AS failing_rows,
       'Execution due but no mature marker' AS detail_code
UNION ALL SELECT 'DQ02','error','mature_quality_missing',
       (SELECT COUNT(*) FROM v_jobs_asof j CROSS JOIN model_settings s WHERE j.excluded_at IS NULL AND j.quality_due_at<=s.event_time_as_of AND j.quality_mature_at IS NULL),
       'Quality due but no quality_mature_at'
UNION ALL SELECT 'DQ03','error','mature_value_missing',
       (SELECT COUNT(*) FROM v_jobs_asof j CROSS JOIN model_settings s WHERE j.excluded_at IS NULL AND j.value_due_at<=s.event_time_as_of AND j.value_mature_at IS NULL),
       'Value due but no value_mature_at'
UNION ALL SELECT 'DQ04','error','projection_event_state_mismatch',
       (SELECT COUNT(*) FROM v_current_job_states x JOIN jobs j USING(job_id)
        WHERE x.state_code<>CASE x.state_axis WHEN 'execution' THEN j.execution_state WHEN 'acceptance' THEN j.acceptance_state WHEN 'value' THEN j.value_state WHEN 'incident' THEN j.incident_state END),
       'Current projection differs from latest ingested state event'
UNION ALL SELECT 'DQ05','error','first_pass_projection_review_mismatch',
       (SELECT COUNT(*) FROM v_job_operating_facts
        WHERE accepted_first_pass=1 AND (coalesce(first_presented_judged,0)<>1 OR coalesce(first_presented_accepted,0)<>1 OR coalesce(first_presented_material_correction,1)<>0)),
       'accepted_first_pass must match first presented output, not attempt 1'
UNION ALL SELECT 'DQ06','error','final_acceptance_review_mismatch',
       (SELECT COUNT(*) FROM v_job_operating_facts
        WHERE (acceptance_state IN ('accepted_first_pass','accepted_after_correction') AND coalesce(final_review_accepted,0)<>1)
           OR (acceptance_state='rejected' AND coalesce(final_review_accepted,1)<>0)),
       'Acceptance projection disagrees with decision review'
UNION ALL SELECT 'DQ07','warning','mature_output_job_missing_final_review',
       (SELECT COUNT(*) FROM v_job_operating_facts
        WHERE quality_mature=1 AND acceptance_state IN ('accepted_first_pass','accepted_after_correction','rejected') AND coalesce(final_judged,0)<>1),
       'Resolved acceptance projections need one final decision; deadline-mature Unknown remains a valid denominator'
UNION ALL SELECT 'DQ08','error','independent_value_with_founder_rescue',
       (SELECT COUNT(*) FROM v_job_operating_facts WHERE independent_value=1 AND founder_rescue_minutes>0),
       'Nonstandard founder rescue disqualifies independent value'
UNION ALL SELECT 'DQ09','error','independent_value_without_verified_value',
       (SELECT COUNT(*) FROM v_job_operating_facts WHERE independent_value=1 AND verified_value=0),
       'Independent value cannot exist without verified value'
UNION ALL SELECT 'DQ10','error','provider_request_job_ordinal_gap',
       (SELECT COUNT(*) FROM (
          SELECT job_id FROM provider_requests GROUP BY job_id
          HAVING MIN(job_request_ordinal)<>1 OR MAX(job_request_ordinal)<>COUNT(*)
        )),
       'Request ordinals must be contiguous within job'
UNION ALL SELECT 'DQ11','error','provider_request_attempt_ordinal_gap',
       (SELECT COUNT(*) FROM (
          SELECT attempt_id FROM provider_requests GROUP BY attempt_id
          HAVING MIN(attempt_request_ordinal)<>1 OR MAX(attempt_request_ordinal)<>COUNT(*)
        )),
       'Request ordinals must be contiguous within attempt'
UNION ALL SELECT 'DQ12','warning','provider_request_error_without_class',
       (SELECT COUNT(*) FROM provider_requests WHERE request_status NOT IN ('completed','unknown_billed') AND error_class IS NULL),
       'Failure mode is required for retry policy review'
UNION ALL SELECT 'DQ13','error','usage_provider_or_model_mismatch',
       (SELECT COUNT(*) FROM provider_usage_lines u JOIN provider_requests r USING(provider_request_id) JOIN provider_price_book p USING(price_id)
        WHERE r.provider_code<>p.provider_code OR r.product_code<>p.product_code OR r.model_snapshot_code<>p.model_snapshot_code
           OR r.region_code<>p.region_code OR r.service_tier_code<>p.service_tier_code
           OR r.billing_channel_code<>p.billing_channel_code
           OR r.batch_mode<>p.batch_mode OR r.context_band<>p.context_band),
       'Every price-defining request dimension must match the historical price-book row'
UNION ALL SELECT 'DQ14','error','usage_price_outside_effective_window',
       (SELECT COUNT(*) FROM provider_usage_lines u JOIN provider_price_book p USING(price_id)
        WHERE u.usage_recorded_at<p.effective_from OR (p.effective_to IS NOT NULL AND u.usage_recorded_at>=p.effective_to)),
       'Historical usage must use the then-effective price row'
UNION ALL SELECT 'DQ15','error','usage_estimate_formula_mismatch',
       (SELECT COUNT(*) FROM provider_usage_lines u JOIN provider_price_book p USING(price_id)
        WHERE u.list_estimated_cost_micros<>CAST(ROUND(u.billable_quantity*p.list_unit_rate_micros*1.0/p.unit_scale) AS INTEGER)
           OR u.contract_estimated_cost_micros<>CAST(ROUND(u.billable_quantity*p.contract_unit_rate_micros*1.0/p.unit_scale) AS INTEGER)),
       'Materialized estimate must use billable, never native, quantity'
UNION ALL SELECT 'DQ16','error','usage_reconciliation_unacceptable',
       (SELECT COUNT(*) FROM v_usage_reconciliation WHERE reconciliation_status NOT IN ('exact','explained_delta','within_tolerance')),
       'Only missing, lineage-mismatched, or unexplained beyond-tolerance direct allocation is an error'
UNION ALL SELECT 'DQ17','error','invoice_line_residual',
       (SELECT COUNT(*) FROM v_invoice_line_reconciliation WHERE residual_micros<>0),
       'Invoice line is not fully explained by direct or explicit unallocated allocation'
UNION ALL SELECT 'DQ18','error','provider_settlement_delta',
       (SELECT COUNT(*) FROM v_provider_invoice_summary WHERE settlement_delta_micros<>0),
       'Invoice payable differs from cash plus promo/prepaid application'
UNION ALL SELECT 'DQ19','error','provider_credit_wrong_allocation_class',
       (SELECT COUNT(*) FROM v_provider_invoice_lines_asof i JOIN v_invoice_allocations_asof a USING(provider_invoice_line_id)
        WHERE (i.economic_class IN ('provider_service_credit','contract_discount')
               AND ((i.entry_action='recognize' AND a.allocation_kind<>'provider_credit_unallocated')
                    OR (i.entry_action='reverse' AND a.allocation_kind<>'provider_credit_reversal_unallocated')))
           OR (i.economic_class NOT IN ('provider_service_credit','contract_discount')
               AND a.allocation_kind IN ('provider_credit_unallocated','provider_credit_reversal_unallocated'))),
       'Provider credits/discounts and their reversals remain separate from job usage cost'
UNION ALL SELECT 'DQ20','error','tool_effect_approval_violation',
       (SELECT COUNT(*) FROM v_tool_effect_facts WHERE approval_violation=1),
       'Approval-required side effect committed before scoped human/customer approval'
UNION ALL SELECT 'DQ21','warning','committed_effect_without_verify_or_rollback',
       (SELECT COUNT(*) FROM v_tool_effect_facts WHERE verification_status='overdue'),
       'Alert only after the effect-specific verification due time has passed'
UNION ALL SELECT 'DQ22','error','high_severity_incident_not_contained',
       (SELECT COUNT(*) FROM v_incidents_asof WHERE severity IN ('sev0','sev1','sev2') AND current_state NOT IN ('contained','closed')),
       'Sev0-Sev2 remains uncontained'
UNION ALL SELECT 'DQ23','error','value_cycle_missing_commercial_map',
       (SELECT COUNT(*) FROM workflow_value_cycles c LEFT JOIN workflow_commercial_cycle_map m USING(workflow_value_cycle_id) WHERE m.mapping_id IS NULL),
       'Each workflow value cycle maps once to a commercial cycle'
UNION ALL SELECT 'DQ24','error','commercial_revenue_currency_mismatch',
       (SELECT COUNT(*) FROM customer_revenue_ledger r JOIN account_commercial_cycles c USING(account_commercial_cycle_id) WHERE r.currency<>c.currency),
       'Revenue source and commercial-cycle currency differ'
UNION ALL SELECT 'DQ25','warning','workflow_cycle_without_eligible_job',
       (SELECT COUNT(*) FROM workflow_value_cycles c LEFT JOIN jobs j USING(workflow_value_cycle_id) WHERE j.job_id IS NULL AND c.cycle_state<>'excluded'),
       'Seed policy expects one eligible job per natural opportunity cycle'
UNION ALL SELECT 'DQ26','warning','explicit_unallocated_provider_charge',
       (SELECT COUNT(*) FROM v_invoice_allocations_asof WHERE allocation_kind='unallocated_charge' AND amount_micros<>0),
       'Keep visible; do not smear across successful jobs'
UNION ALL SELECT 'DQ27','info','bundle_feature_revenue_unallocated',
       (SELECT COUNT(*) FROM v_feature_revenue_allocations_asof WHERE allocation_status='unallocated'),
       'Expected for bundled core feature; use account-commercial-cycle contribution'
UNION ALL SELECT 'DQ28','info','late_events_present',
       (SELECT COUNT(*) FROM v_late_events),
       'Restate by occurred_at and retain prior metric snapshot'
UNION ALL SELECT 'DQ29','info','reopened_jobs_present',
       (SELECT COUNT(*) FROM v_job_operating_facts WHERE was_reopened=1),
       'Review restatements before interpreting trend'
UNION ALL SELECT 'DQ30','error','eligible_opportunity_without_job',
       (SELECT COUNT(*) FROM eligible_opportunities o LEFT JOIN jobs j USING(eligible_opportunity_id) WHERE j.job_id IS NULL),
       'Standard grain requires eligible opportunity 1:1 job at occurrence time'
UNION ALL SELECT 'DQ31','error','eligible_opportunity_job_snapshot_mismatch',
       (SELECT COUNT(*) FROM eligible_opportunities o JOIN jobs j USING(eligible_opportunity_id)
        WHERE o.opportunity_ref_hash<>j.opportunity_ref_hash OR o.account_id<>j.account_id
           OR o.workflow_value_cycle_id<>j.workflow_value_cycle_id OR o.feature_code<>j.feature_code
           OR o.workflow_code<>j.workflow_code OR o.job_type_code<>j.job_type_code OR o.occurred_at<>j.eligible_at),
       'Embedded eligible-job projection must match opportunity event'
UNION ALL SELECT 'DQ32','error','nonfailure_request_marked_failure_driven',
       (SELECT COUNT(*) FROM v_usage_reconciliation WHERE failure_relation_type='none' AND failure_driven<>0),
       'Purpose role alone must not enter failure-driven waste'
UNION ALL SELECT 'DQ33','error','failure_cost_aggregation_mismatch',
       (SELECT COUNT(*) FROM v_job_cost_facts j
        WHERE j.failure_driven_reconciled_cost_micros<>
          coalesce((SELECT SUM(u.reconciled_contract_cost_micros)
                    FROM v_usage_reconciliation u
                    WHERE u.job_id=j.job_id AND u.failure_relation_type<>'none'),0)),
       'Failure-driven cost must use lineage type, not request purpose role'
UNION ALL SELECT 'DQ34','info','planned_auxiliary_or_validation_nonfailure_requests',
       (SELECT COUNT(*) FROM v_provider_requests_asof WHERE request_role IN ('planned_auxiliary','validation') AND failure_relation_type='none'),
       'Expected planned work; included in total cost but excluded from failure-waste numerator'
UNION ALL SELECT 'DQ35','info','purpose_role_requests_with_failure_lineage',
       (SELECT COUNT(*) FROM v_provider_requests_asof WHERE request_role IN ('planned_auxiliary','validation') AND failure_relation_type<>'none'),
       'Included in failure waste because explicit retry/fallback/repair lineage exists'
UNION ALL SELECT 'DQ36','error','incident_observation_maturity_missing',
       (SELECT COUNT(*) FROM v_jobs_asof j CROSS JOIN model_settings s
        WHERE j.excluded_at IS NULL AND j.incident_observation_due_at<=s.event_time_as_of AND j.incident_mature_at IS NULL),
       'Incident-free safety cannot mature merely because no incident row exists'
UNION ALL SELECT 'DQ37','info','deadline_mature_unknown_acceptance',
       (SELECT COUNT(*) FROM v_job_operating_facts WHERE quality_mature=1 AND acceptance_state='unknown'),
       'Expected valid denominator state; Unknown contributes zero to acceptance numerators'
UNION ALL SELECT 'DQ38','error','funnel_stage_order_or_exposure_missing',
       (SELECT COUNT(*) FROM jobs
        WHERE (started_at IS NOT NULL AND exposed_at IS NULL)
           OR (exposed_at IS NOT NULL AND (offered_at IS NULL OR exposed_at<offered_at))
           OR (started_at IS NOT NULL AND started_at<exposed_at)),
       'Funnel order is eligible, offered, exposed, started'
UNION ALL SELECT 'DQ39','error','billable_unit_price_unit_mismatch',
       (SELECT COUNT(*) FROM provider_usage_lines u JOIN provider_price_book p USING(price_id)
        WHERE u.billable_unit_code<>p.provider_unit_code
           OR (u.inclusion_rule_code<>'billable' AND u.billable_quantity<>0)),
       'Native telemetry may differ; billed quantity must be compatible with the price-book unit'
UNION ALL SELECT 'DQ40','error','direct_invoice_lineage_mismatch',
       (SELECT COUNT(*) FROM v_usage_reconciliation WHERE invoice_lineage_match<>1),
       'Direct allocation must match provider, SKU, currency, and invoice service period'
UNION ALL SELECT 'DQ41','error','approval_event_authority_mismatch',
       (SELECT COUNT(*) FROM tool_effect_events e JOIN tool_effect_approvals a USING(approval_id)
        WHERE e.lifecycle_event='approved'
          AND (e.effect_id<>a.effect_id OR e.bound_action_fingerprint_hash<>a.action_fingerprint_hash
               OR e.actor_side<>a.authority_kind OR e.actor_ref_hash<>a.authority_ref_hash
               OR e.approval_scope_ref_hash<>a.approval_ref_hash
               OR e.occurred_at<a.approved_at OR e.occurred_at>=a.expires_at)),
       'Approval event must bind the exact action and declared authority window'
UNION ALL SELECT 'DQ42','error','incident_reopen_not_rematured',
       (SELECT COUNT(*) FROM jobs
        WHERE reopened_incident_at IS NOT NULL AND (incident_mature_at IS NULL OR incident_mature_at<reopened_incident_at)),
       'A reopened incident window must mature again before safety can be confirmed'
UNION ALL SELECT 'DQ43','error','safe_independent_without_mature_coverage',
       (SELECT COUNT(*) FROM v_job_operating_facts
        WHERE safe_independent_value=1
          AND (incident_mature<>1 OR required_review_coverage_complete<>1 OR stop_signal_observed<>0)),
       'Safe independent value requires mature incident observation and complete judged/compliant review coverage'
UNION ALL SELECT 'DQ44','error','variable_cash_scope_not_exactly_once',
       (SELECT CASE WHEN
          coalesce((SELECT SUM(amount_micros) FROM v_variable_cash_cost_ledger_asof),0)<>
          coalesce((SELECT SUM(other_job_variable_cash_cost_micros) FROM v_job_cost_facts),0)+
          coalesce((SELECT SUM(other_variable_cash_cost_micros) FROM v_commercial_cycle_facts),0)
        THEN 1 ELSE 0 END),
       'Job-scope and commercial-cycle-scope partitions must exhaust the ledger exactly once'
UNION ALL SELECT 'DQ45','error','job_cash_scope_cycle_mismatch',
       (SELECT COUNT(*) FROM variable_cash_cost_ledger v
        JOIN jobs j USING(job_id)
        JOIN workflow_commercial_cycle_map m USING(workflow_value_cycle_id)
        WHERE v.allocation_scope='job' AND v.account_commercial_cycle_id<>m.account_commercial_cycle_id),
       'A job-scoped cash line must use the commercial cycle mapped from that job workflow cycle'
UNION ALL SELECT 'DQ46','error','job_shared_allocation_missing_from_job_cost',
       (SELECT CASE WHEN
          coalesce((SELECT SUM(amount_micros) FROM v_invoice_allocations_asof WHERE allocation_kind='job_shared'),0)<>
          coalesce((SELECT SUM(job_shared_reconciled_cost_micros) FROM v_job_cost_facts),0)
        THEN 1 ELSE 0 END),
       'Job-shared invoice allocations enter provider/job cost exactly once and never disappear'
UNION ALL SELECT 'DQ47','error','job_provider_cost_total_mismatch',
       (SELECT CASE WHEN
          coalesce((SELECT SUM(reconciled_provider_cost_micros) FROM v_job_cost_facts),0)<>
          coalesce((SELECT SUM(amount_micros) FROM v_invoice_allocations_asof WHERE allocation_kind IN ('direct_usage','job_shared')),0)
        THEN 1 ELSE 0 END),
       'Reconciled job provider cost equals direct usage plus job-shared allocations only'
UNION ALL SELECT 'DQ48','error','job_cycle_assignment_mapping_mismatch',
       (SELECT COUNT(*) FROM jobs j
        LEFT JOIN workflow_cycle_experiment_assignments x
          ON x.workflow_value_cycle_id=j.workflow_value_cycle_id AND x.assignment_id=j.assignment_id
        WHERE x.cycle_experiment_assignment_id IS NULL),
       'Jobs must use the predeclared cycle-to-experiment assignment identity'
UNION ALL SELECT 'DQ49','error','workflow_fact_mapping_cardinality_mismatch',
       (SELECT CASE WHEN
          (SELECT COUNT(*) FROM v_workflow_cycle_facts)<>
          (SELECT COUNT(*) FROM v_workflow_cycle_experiment_assignments_asof)
        THEN 1 ELSE 0 END),
       'Workflow facts produce one row per explicit cycle-experiment mapping, never per account join fanout'
UNION ALL SELECT 'DQ50','error','variant_funnel_cohort_numerator_exceeds_denominator',
       (SELECT COUNT(*) FROM v_variant_operating_review
        WHERE execution_mature_exposed_jobs>execution_mature_eligible_jobs
           OR execution_mature_started_jobs>execution_mature_exposed_jobs
           OR accepted_first_pass_jobs>quality_mature_started_jobs
           OR safe_independent_value_jobs>value_mature_eligible_jobs),
       'Every funnel/rate numerator must be a subset of its named mature, non-excluded cohort'
UNION ALL SELECT 'DQ51','error','randomized_itt_integrity_invalid',
       (SELECT COUNT(*) FROM v_experiment_analysis_validity
        WHERE design_type='randomized' AND analysis_validity<>'randomized_itt_valid'),
       'Invalid randomized loads are labeled association-only and must not be reported as ITT'
UNION ALL SELECT 'DQ52','error','non_base_currency_row',
       (SELECT COUNT(*) FROM (
          SELECT currency FROM account_commercial_cycles UNION ALL
          SELECT currency FROM customer_revenue_ledger UNION ALL
          SELECT currency FROM feature_revenue_allocations UNION ALL
          SELECT currency FROM payment_dispute_events UNION ALL
          SELECT currency FROM variable_cash_cost_ledger UNION ALL
          SELECT currency FROM human_work_logs UNION ALL
          SELECT currency FROM provider_price_book UNION ALL
          SELECT currency FROM provider_invoice_lines UNION ALL
          SELECT currency FROM provider_settlements
        ) money CROSS JOIN model_settings s WHERE money.currency<>s.base_currency),
       'This model intentionally uses one base currency; no mixed-currency sum is permitted'
UNION ALL SELECT 'DQ53','error','feature_revenue_allocation_exceeds_net_revenue',
       (SELECT COUNT(*) FROM (
          SELECT c.account_commercial_cycle_id,
                 coalesce(SUM(CASE WHEN f.allocation_status<>'unallocated' THEN f.allocated_feature_net_revenue_micros ELSE 0 END),0) AS allocated,
                 coalesce((SELECT SUM(r.amount_micros) FROM v_customer_revenue_ledger_asof r WHERE r.account_commercial_cycle_id=c.account_commercial_cycle_id),0) AS net_revenue
          FROM v_account_commercial_cycles_asof c LEFT JOIN v_feature_revenue_allocations_asof f USING(account_commercial_cycle_id)
          GROUP BY c.account_commercial_cycle_id
        ) x WHERE x.allocated>max(x.net_revenue,0)),
       'Grouped feature allocations from the single ledger cannot exceed commercial-cycle net revenue'
UNION ALL SELECT 'DQ54','error','feature_revenue_decision_missing',
       (SELECT COUNT(*) FROM workflow_commercial_cycle_map m
        LEFT JOIN v_feature_revenue_allocations_asof f
          ON f.workflow_value_cycle_id=m.workflow_value_cycle_id AND f.account_commercial_cycle_id=m.account_commercial_cycle_id
        WHERE f.feature_revenue_allocation_id IS NULL),
       'Every mapped workflow cycle needs an explicit allocated or unallocated decision row'
UNION ALL SELECT 'DQ55','error','chargeback_principal_treatment_mismatch',
       (SELECT COUNT(*) FROM (
          SELECT d.payment_dispute_event_id,d.principal_amount_micros,d.principal_treatment,
                 coalesce((SELECT SUM(abs(r.amount_micros)) FROM customer_revenue_ledger r WHERE r.payment_dispute_event_id=d.payment_dispute_event_id AND r.line_kind='chargeback_principal'),0) AS contra_principal,
                 coalesce((SELECT SUM(v.amount_micros) FROM variable_cash_cost_ledger v WHERE v.payment_dispute_event_id=d.payment_dispute_event_id AND v.cost_kind='chargeback_loss'),0) AS cash_principal
          FROM payment_dispute_events d
        ) x WHERE (x.principal_treatment='contra_revenue' AND (x.contra_principal<>x.principal_amount_micros OR x.cash_principal<>0))
                  OR (x.principal_treatment='cash_cost' AND (x.cash_principal<>x.principal_amount_micros OR x.contra_principal<>0))),
       'Chargeback principal is recognized once; linked dispute_fee remains a separate cash cost'
UNION ALL SELECT 'DQ56','error','independent_cohort_missing_verified_predecessor',
       (SELECT COUNT(*) FROM v_value_cohort_entries
        WHERE entry_type='first_independent_value'
          AND (predecessor_verified_value_event_id IS NULL OR predecessor_evidence_ref_hash IS NULL)),
       'Independent-value cohort entry carries predecessor verified-value evidence'
UNION ALL SELECT 'DQ57','warning','independent_value_review_coverage_unknown',
       (SELECT COUNT(*) FROM v_job_operating_facts
        WHERE independent_value=1 AND required_review_coverage_state<>'complete'),
       'Missing or unjudged required review remains Unknown, never implicitly compliant'
UNION ALL SELECT 'DQ58','error','planned_review_policy_not_applied',
       (SELECT COUNT(*) FROM v_job_operating_facts j JOIN workflow_policies p USING(workflow_code)
        WHERE j.safe_independent_value=1 AND j.planned_review_minutes>0 AND p.planned_review_allows_independent_value=0),
       'Planned review is compatible with independent value only when the workflow policy says so'
UNION ALL SELECT 'DQ59','info','explained_direct_reconciliation_deltas',
       (SELECT COUNT(*) FROM v_usage_reconciliation WHERE reconciliation_status='explained_delta'),
       'Explained estimate-to-invoice deltas are surfaced but are not automatic errors'
UNION ALL SELECT 'DQ60','info','invoice_unallocated_residual_rows',
       (SELECT COUNT(*) FROM v_invoice_line_reconciliation WHERE unallocated_charge_micros<>0),
       'Explicit unallocated invoice residual stays visible and out of successful job unit cost'
UNION ALL SELECT 'DQ61','error','payment_dispute_linkage_mismatch',
       (SELECT COUNT(*) FROM (
          SELECT r.payment_dispute_event_id,r.account_commercial_cycle_id,r.account_id,r.currency
          FROM customer_revenue_ledger r WHERE r.payment_dispute_event_id IS NOT NULL
          UNION ALL
          SELECT v.payment_dispute_event_id,v.account_commercial_cycle_id,c.account_id,v.currency
          FROM variable_cash_cost_ledger v
          JOIN account_commercial_cycles c USING(account_commercial_cycle_id)
          WHERE v.payment_dispute_event_id IS NOT NULL
        ) x JOIN payment_dispute_events d USING(payment_dispute_event_id)
        WHERE x.account_commercial_cycle_id<>d.account_commercial_cycle_id
           OR x.account_id<>d.account_id OR x.currency<>d.currency),
       'Contra principal, cash principal, and separate dispute fee must link to the same transaction/cycle/currency'
UNION ALL SELECT 'DQ62','error','variant_mature_cohort_projection_mismatch',
       (SELECT COUNT(*) FROM v_variant_operating_review v
        WHERE v.quality_mature_started_jobs<>(
                SELECT SUM(CASE WHEN j.excluded=0 AND j.quality_mature=1 AND j.started_at IS NOT NULL THEN 1 ELSE 0 END)
                FROM v_job_operating_facts j WHERE j.experiment_id=v.experiment_id AND j.variant_id=v.variant_id)
           OR v.execution_mature_reconciled_provider_cost_micros<>
              coalesce((SELECT SUM(coalesce(j.reconciled_provider_cost_micros,0))
                        FROM v_job_operating_facts j
                        WHERE j.experiment_id=v.experiment_id AND j.variant_id=v.variant_id
                          AND j.excluded=0 AND j.execution_mature=1),0)
           OR v.value_mature_fully_loaded_variable_cost_micros<>
              coalesce((SELECT SUM(coalesce(j.job_fully_loaded_variable_cost_micros,0))
                        FROM v_job_operating_facts j
                        WHERE j.experiment_id=v.experiment_id AND j.variant_id=v.variant_id
                          AND j.excluded=0 AND j.value_mature=1),0)),
       'Variant denominators and cost numerators must project from the same named non-excluded mature cohorts'
UNION ALL SELECT 'DQ63','error','first_value_cohort_repeat_retention_invalid',
       (SELECT COUNT(*) FROM v_first_value_cohort_retention
        WHERE relative_cycle_number<1
           OR repeat_value_accounts>mature_cohort_accounts
           OR repeat_value_retention_pct<0 OR repeat_value_retention_pct>100),
       'First-value cohort retention is cycle-relative repeat value; eligible/ITT retention remains verified value'
UNION ALL SELECT 'DQ64','error','duplicate_tool_effect_commit',
       (SELECT COUNT(*) FROM v_tool_effect_facts WHERE duplicate_commit_count>0),
       'Exactly one committed transition is allowed per effect/idempotency key'
UNION ALL SELECT 'DQ65','error','human_touch_work_class_mismatch',
       (SELECT COUNT(*) FROM v_job_operating_facts j
        WHERE j.human_touch<>CASE WHEN EXISTS (
          SELECT 1 FROM v_human_work_logs_asof h
          WHERE h.job_id=j.job_id AND h.work_class IN ('planned_review','customer_review','correction','support')
        ) THEN 1 ELSE 0 END),
       'Human touch is driven only by planned review, customer review, correction, or support work classes'
UNION ALL SELECT 'DQ66','error','expected_assignment_cycle_missing',
       (SELECT COUNT(*) FROM v_assignment_expected_cycle_spine WHERE expected_cycle_coverage_incomplete=1),
       'Predeclared assignment-by-calendar rows and due maturity facts may not silently disappear from cycle retention'
UNION ALL SELECT 'DQ67','error','workflow_cycle_calendar_projection_mismatch',
       (SELECT COUNT(*) FROM workflow_value_cycles c JOIN workflow_cycle_calendar cal USING(workflow_code,cycle_number)
        WHERE c.natural_cycle_start<>cal.natural_cycle_start OR c.natural_cycle_end<>cal.natural_cycle_end
           OR c.on_time_due_at<>cal.on_time_due_at OR c.maturity_due_at<>cal.maturity_due_at),
       'Account workflow-cycle clocks must exactly project the predeclared natural-cycle calendar'
UNION ALL SELECT 'DQ68','error','customer_revenue_reversal_lineage_mismatch',
       (SELECT COUNT(*) FROM customer_revenue_ledger r
        LEFT JOIN customer_revenue_ledger o ON o.customer_revenue_line_id=r.corrects_line_id
        WHERE (r.entry_action='recognize' AND r.corrects_line_id IS NOT NULL)
           OR (r.entry_action='reverse' AND (
                o.customer_revenue_line_id IS NULL OR o.entry_action<>'recognize'
                OR r.account_commercial_cycle_id<>o.account_commercial_cycle_id
                OR r.account_id<>o.account_id OR r.line_kind<>o.line_kind OR r.currency<>o.currency
                OR r.amount_micros<>-o.amount_micros OR r.occurred_at<o.occurred_at
              ))),
       'Revenue reversals are one-to-one, opposite-signed corrections of an earlier same-class account/currency line'
UNION ALL SELECT 'DQ69','error','provider_invoice_reversal_lineage_mismatch',
       (SELECT COUNT(*) FROM provider_invoice_lines r
        LEFT JOIN provider_invoice_lines o ON o.provider_invoice_line_id=r.corrects_invoice_line_id
        WHERE (r.entry_action='recognize' AND r.corrects_invoice_line_id IS NOT NULL)
           OR (r.entry_action='reverse' AND (
                o.provider_invoice_line_id IS NULL OR o.entry_action<>'recognize'
                OR r.provider_code<>o.provider_code OR r.invoice_ref_hash<>o.invoice_ref_hash
                OR r.project_ref_hash<>o.project_ref_hash OR r.sku_code<>o.sku_code
                OR r.economic_class<>o.economic_class OR r.currency<>o.currency
                OR r.service_period_start<>o.service_period_start OR r.service_period_end<>o.service_period_end
                OR r.invoice_amount_micros<>-o.invoice_amount_micros OR r.recorded_at<o.recorded_at
              ))),
       'Provider invoice reversals preserve invoice economic identity and negate an earlier recognized line'
UNION ALL SELECT 'DQ70','error','provider_request_failure_parent_not_earlier',
       (SELECT COUNT(*) FROM provider_requests c
        LEFT JOIN provider_requests p
          ON p.provider_request_id=coalesce(c.retry_of_request_id,c.fallback_from_request_id,c.repair_of_request_id)
        WHERE c.failure_relation_type<>'none'
          AND (p.provider_request_id IS NULL OR p.job_id<>c.job_id OR p.job_request_ordinal>=c.job_request_ordinal)),
       'Retry, fallback, and repair lineage must point to a strictly lower request ordinal in the same job'
UNION ALL SELECT 'DQ71','error','job_state_clock_maturity_invalid',
       (SELECT COUNT(*) FROM v_jobs_asof j
        JOIN workflow_policies p USING(workflow_code)
        JOIN workflow_value_cycles c USING(workflow_value_cycle_id)
        LEFT JOIN v_current_job_states sx ON sx.job_id=j.job_id AND sx.state_axis='execution'
        LEFT JOIN v_current_job_states sq ON sq.job_id=j.job_id AND sq.state_axis='acceptance'
        LEFT JOIN v_current_job_states sv ON sv.job_id=j.job_id AND sv.state_axis='value'
        LEFT JOIN v_current_job_states si ON si.job_id=j.job_id AND si.state_axis='incident'
        WHERE (j.execution_mature_at IS NOT NULL
               AND NOT (j.execution_mature_at>=j.execution_due_at
                        OR coalesce(sx.state_code,j.execution_state) IN ('completed','no_output','abandoned','stopped_for_safety')))
           OR (j.quality_mature_at IS NOT NULL
               AND NOT (j.quality_mature_at>=j.quality_due_at
                        OR (p.quality_definitive_early_maturity_allowed=1 AND coalesce(sq.state_code,j.acceptance_state) IN ('accepted_first_pass','accepted_after_correction','rejected'))
                        OR (coalesce(sq.state_code,j.acceptance_state)='not_assessable' AND coalesce(sx.state_code,j.execution_state) IN ('no_output','abandoned','stopped_for_safety'))))
           OR (j.value_mature_at IS NOT NULL
               AND NOT (j.value_mature_at>=j.value_due_at
                        OR (p.value_confirmed_early_maturity_allowed=1 AND coalesce(sv.state_code,j.value_state)='confirmed')
                        OR (coalesce(sv.state_code,j.value_state)='not_confirmed' AND c.cycle_state='early_terminal' AND j.value_mature_at>=c.early_terminal_at)))
           OR (j.incident_mature_at IS NOT NULL AND coalesce(si.state_code,j.incident_state)='none_observed'
               AND j.incident_mature_at<j.incident_observation_due_at)),
       'Unknown/negative maturity waits for its clock; only policy-defined definitive or explicit terminal states may mature early'
UNION ALL SELECT 'DQ72','error','workflow_cycle_clock_maturity_invalid',
       (SELECT COUNT(*) FROM workflow_value_cycles c CROSS JOIN model_settings s
        WHERE (c.cycle_state='matured' AND (c.matured_at IS NULL OR c.matured_at<c.maturity_due_at))
           OR (c.cycle_state='early_terminal' AND (c.early_terminal_at IS NULL OR c.early_terminal_at>c.maturity_due_at OR c.matured_at<c.early_terminal_at))
           OR (c.cycle_state NOT IN ('matured','early_terminal','excluded') AND c.maturity_due_at<=s.event_time_as_of)),
       'Normal workflow cycles mature at due; early terminal cycles require an explicit terminal event; overdue provisional/reopened cycles remain errors'
UNION ALL SELECT 'DQ73','error','commercial_cycle_close_clock_invalid',
       (SELECT COUNT(*) FROM account_commercial_cycles c JOIN accounts a USING(account_id)
        WHERE c.commercial_state IN ('closed','written_off')
          AND (c.closed_at IS NULL OR (c.closed_at<c.billing_period_end
               AND NOT (a.early_terminal_at IS NOT NULL AND c.closed_at>=a.early_terminal_at)))),
       'A commercial period closes at period end unless an explicit account terminal event closes it early'
UNION ALL SELECT 'DQ74','error','incomplete_itt_cycle_rate_not_null',
       (SELECT COUNT(*) FROM v_cycle_retention
        WHERE (analysis_validity<>'randomized_itt_valid' OR incomplete_expected_cycle_accounts>0)
          AND (on_time_verified_value_retention_pct IS NOT NULL OR eventual_verified_value_retention_pct IS NOT NULL)),
       'ITT rates are NULL whenever randomized validity or expected assignment-cycle coverage is incomplete'
UNION ALL SELECT 'DQ75','error','current_job_state_axis_missing',
       (SELECT COUNT(*) FROM (
          SELECT j.job_id,COUNT(DISTINCT s.state_axis) AS state_axis_count
          FROM v_jobs_asof j LEFT JOIN v_current_job_states s USING(job_id)
          GROUP BY j.job_id HAVING COUNT(DISTINCT s.state_axis)<>4
        )),
       'Every visible job requires execution, acceptance, value, and incident state events at the snapshot cutoff'
UNION ALL SELECT 'DQ76','error','asof_invoice_allocation_without_visible_parent',
       (SELECT COUNT(*) FROM v_invoice_allocations_cutoff a
        LEFT JOIN v_provider_invoice_lines_asof i USING(provider_invoice_line_id)
        WHERE i.provider_invoice_line_id IS NULL),
       'A reconciled allocation cannot enter a snapshot before its invoice line is event-time and watermark visible'
UNION ALL SELECT 'DQ77','error','asof_reversal_without_visible_original',
       (SELECT COUNT(*) FROM (
          SELECT r.customer_revenue_line_id AS reversal_id
          FROM v_customer_revenue_ledger_cutoff r
          LEFT JOIN v_customer_revenue_ledger_cutoff o ON o.customer_revenue_line_id=r.corrects_line_id
          WHERE r.entry_action='reverse' AND o.customer_revenue_line_id IS NULL
          UNION ALL
          SELECT r.provider_invoice_line_id
          FROM v_provider_invoice_lines_cutoff r
          LEFT JOIN v_provider_invoice_lines_cutoff o ON o.provider_invoice_line_id=r.corrects_invoice_line_id
          WHERE r.entry_action='reverse' AND o.provider_invoice_line_id IS NULL
        )),
       'A reversal is withheld as invalid if its corrected original is unavailable at the same snapshot cutoff'
UNION ALL SELECT 'DQ78','error','safe_independent_tool_gate_mismatch',
       (SELECT COUNT(*) FROM v_job_operating_facts
        WHERE independent_value=1 AND (
          ((approval_violation_count>0 OR duplicate_tool_commit_count>0 OR overdue_verification_count>0 OR failed_verification_count>0)
             AND (safe_independent_value_state<>'failed' OR safe_independent_value<>0))
          OR (approval_violation_count=0 AND duplicate_tool_commit_count=0 AND overdue_verification_count=0
              AND failed_verification_count=0 AND pending_verification_count>0
              AND (safe_independent_value_state<>'unknown' OR safe_independent_value<>0))
        )),
       'Failed/overdue/duplicate/unauthorized tool effects force failed; pending verification forces Unknown'
UNION ALL SELECT 'DQ79','error','invoice_allocation_ingested_before_reconciled',
       (SELECT COUNT(*) FROM invoice_allocations WHERE ingested_at<reconciled_at),
       'Invoice allocation availability cannot precede its reconciliation event time'
UNION ALL SELECT 'DQ80','error','snapshot_cutoff_order_invalid',
       (SELECT COUNT(*) FROM model_settings
        WHERE invoice_reconciled_through>data_watermark_at OR data_watermark_at>event_time_as_of),
       'Invoice reconciliation cutoff must be no later than the data watermark, which must be no later than event-time as-of'
UNION ALL SELECT 'DQ81','error','asof_invoice_allocation_without_visible_child_target',
       (SELECT COUNT(*) FROM v_invoice_allocations_cutoff a
        LEFT JOIN v_provider_usage_lines_asof u ON u.usage_line_id=a.usage_line_id
        LEFT JOIN v_jobs_asof j ON j.job_id=a.job_id
        WHERE (a.usage_line_id IS NOT NULL AND u.usage_line_id IS NULL)
           OR (a.job_id IS NOT NULL AND j.job_id IS NULL)),
       'Direct-usage and job-shared allocations cannot enter before their usage/job target is snapshot-visible'
UNION ALL SELECT 'DQ82','error','asof_settlement_without_visible_invoice',
       (SELECT COUNT(*) FROM v_provider_settlements_cutoff p
        WHERE NOT EXISTS (
          SELECT 1 FROM v_provider_invoice_lines_asof i
          WHERE i.provider_code=p.provider_code AND i.invoice_ref_hash=p.invoice_ref_hash AND i.currency=p.currency
        )),
       'A settlement cannot enter the invoice snapshot before an invoice line for the same provider/ref/currency is visible'
UNION ALL SELECT 'DQ83','error','bitemporal_child_visible_before_parent',
       (SELECT COUNT(*) FROM (
          SELECT a.attempt_id AS child_id
          FROM attempts a CROSS JOIN model_settings s
          LEFT JOIN v_jobs_asof j USING(job_id)
          WHERE a.started_at<=s.event_time_as_of AND a.ingested_at<=s.data_watermark_at AND j.job_id IS NULL
          UNION ALL
          SELECT o.presented_output_id
          FROM presented_outputs o CROSS JOIN model_settings s
          LEFT JOIN v_attempts_asof a ON a.job_id=o.job_id AND a.attempt_id=o.attempt_id
          WHERE o.presented_at<=s.event_time_as_of AND o.ingested_at<=s.data_watermark_at AND a.attempt_id IS NULL
          UNION ALL
          SELECT r.provider_request_id
          FROM provider_requests r CROSS JOIN model_settings s
          LEFT JOIN v_attempts_asof a ON a.job_id=r.job_id AND a.attempt_id=r.attempt_id
          WHERE r.started_at<=s.event_time_as_of AND r.ingested_at<=s.data_watermark_at AND a.attempt_id IS NULL
          UNION ALL
          SELECT u.usage_line_id
          FROM provider_usage_lines u CROSS JOIN model_settings s
          LEFT JOIN v_provider_requests_asof r ON r.job_id=u.job_id AND r.provider_request_id=u.provider_request_id
          WHERE u.usage_recorded_at<=s.event_time_as_of AND u.ingested_at<=s.data_watermark_at AND r.provider_request_id IS NULL
          UNION ALL
          SELECT q.quality_review_id
          FROM quality_reviews q CROSS JOIN model_settings s
          LEFT JOIN v_jobs_asof j USING(job_id)
          LEFT JOIN v_attempts_asof a ON a.job_id=q.job_id AND a.attempt_id=q.attempt_id
          LEFT JOIN v_presented_outputs_asof o ON o.job_id=q.job_id AND o.presented_output_id=q.presented_output_id
          WHERE q.reviewed_at<=s.event_time_as_of AND q.ingested_at<=s.data_watermark_at
            AND (j.job_id IS NULL OR (q.attempt_id IS NOT NULL AND a.attempt_id IS NULL)
                 OR (q.presented_output_id IS NOT NULL AND o.presented_output_id IS NULL))
          UNION ALL
          SELECT h.human_work_id
          FROM human_work_logs h CROSS JOIN model_settings s
          LEFT JOIN v_jobs_asof j USING(job_id)
          LEFT JOIN v_attempts_asof a ON a.job_id=h.job_id AND a.attempt_id=h.attempt_id
          WHERE h.occurred_at<=s.event_time_as_of AND h.ingested_at<=s.data_watermark_at
            AND (j.job_id IS NULL OR (h.attempt_id IS NOT NULL AND a.attempt_id IS NULL))
          UNION ALL
          SELECT e.value_event_id
          FROM value_events e CROSS JOIN model_settings s
          LEFT JOIN v_jobs_asof j USING(job_id)
          WHERE e.occurred_at<=s.event_time_as_of AND e.ingested_at<=s.data_watermark_at AND j.job_id IS NULL
        )),
       'Timestamp-visible child events are withheld and surfaced when their immutable parent is not visible at the same cutoff';

COMMIT;

-- -----------------------------------------------------------------------------
-- Query pack. Use: sqlite3 -header -column /tmp/model.sqlite < this_file.sql
-- -----------------------------------------------------------------------------

PRAGMA foreign_key_check;

SELECT
  job_id,variant_code,execution_state,acceptance_state,value_state,incident_state,
  attempt_count,provider_request_count,failure_driven_request_count,
  planned_auxiliary_request_count,validation_request_count,presented_output_count,
  verified_value,independent_value,safe_independent_value,
  reconciled_provider_cost_micros,internal_labor_cost_micros,
  job_fully_loaded_variable_cost_micros,late_event_count,was_reopened
FROM v_job_operating_facts
ORDER BY job_id;

SELECT * FROM v_variant_operating_review ORDER BY variant_code;
SELECT * FROM v_cycle_retention ORDER BY cycle_number,variant_code;

SELECT
  account_commercial_cycle_id,net_customer_revenue_micros,
  recognized_gross_revenue_micros,gross_revenue_reversal_micros,
  recognized_contra_revenue_micros,contra_revenue_reversal_micros,
  job_variable_delivery_cash_cost_micros,other_variable_cash_cost_micros,
  internal_labor_cost_micros,account_cycle_cash_contribution_micros,
  account_cycle_fully_loaded_contribution_micros,cost_allocation_status
FROM v_commercial_cycle_facts
ORDER BY account_commercial_cycle_id;

SELECT
  provider_code,gross_invoiced_cost_micros,provider_credit_and_discount_micros,
  recognized_gross_invoiced_cost_micros,gross_invoiced_cost_reversal_micros,
  recognized_provider_credit_and_discount_micros,provider_credit_and_discount_reversal_micros,
  invoice_payable_micros,cash_paid_micros,unallocated_charge_micros,
  reconciliation_delta_micros,settlement_delta_micros
FROM v_provider_invoice_summary
ORDER BY provider_code;

SELECT * FROM v_data_quality_checks
ORDER BY CASE severity WHEN 'error' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END,check_id;
