-- Partner delegation and channel economics evidence model
-- Schema version: 1.0.0 | Reference date: 2026-08-02 | Engine: SQLite 3.40+
-- Companion: ../18-outsourcing-hiring-channel-partner-economics.md
-- Template: ../templates/18-partner-and-delegation-operating-pack.md
--
-- Run only on a new/disposable database:
--   sqlite3 :memory: < indie-developer-playbook/research/18-partner-delegation-economics-model.sql
--
-- Every party, account, policy, agreement, authority, lead, order, amount,
-- timestamp, outcome, and evidence pointer below is synthetic. Fixtures show
-- evidence shapes and fail-closed controls; they are not legal conclusions,
-- market benchmarks, commission promises, accounting rules, or live receipts.
--
-- Interpretation boundaries
-- - Motion is independent from responsibility and authority. REFERRAL does not
--   imply who contracts, invoices, controls data, or supports a customer.
-- - WHITE_LABEL is a presentation right; OEM is an embedding/distribution right;
--   neither silently assigns merchant-of-record or data-controller duties.
-- - UNKNOWN is missing evidence, NA is evidenced inapplicability, and measured
--   zero is a known value. They are never interchangeable.
-- - Booking, invoicing, recognized revenue, collected cash, commission accrual,
--   reversal, and bank-reconciled payout are separate event classes.
-- - Economics outputs use the founder/principal perspective. Commercial events
--   carry payer/payee; bank direction is relative to account_owner_party_id;
--   cash is never reoriented from the motion label.
-- - work_event.cash_cost_micros is authoritative for exact delivery work.
--   Non-founder cost_event cash is overhead and cannot reference a work_order;
--   this prevents one delivery expense from entering both ledgers.
-- - Attribution records program-rule credit. Only a preassigned experiment can
--   support an incrementality estimate; attribution never sets causal_claim=1.
-- - *_micros is one millionth of the row currency. Founder-time value is an
--   internal decision cost and not necessarily a cash expense.
-- - Raw personal data, credentials, contracts, bank details, customer payloads,
--   and free text remain in restricted systems. *_ref_hash is only an opaque
--   synthetic pointer and does not prove consent, identity, or authenticity.
-- - OUTSOURCING intentionally covers both bounded task contractors and managed
--   vendors in v1. Exact task/service shape belongs in agreement evidence and
--   work_order.service_scope_code; it is not inferred from the motion label.

PRAGMA foreign_keys = ON;
PRAGMA recursive_triggers = ON;
PRAGMA trusted_schema = OFF;
BEGIN IMMEDIATE;
-- Fixture loading is one atomic transaction; references are resolved before the
-- assertion guard and COMMIT. Runtime transactions should insert parents first.
PRAGMA defer_foreign_keys = ON;

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

CREATE TABLE model_setting (
  singleton_id INTEGER PRIMARY KEY CHECK (singleton_id=1),
  reference_time TEXT NOT NULL CHECK (datetime(reference_time) IS NOT NULL),
  default_snapshot_as_of TEXT NOT NULL
    CHECK (datetime(default_snapshot_as_of) IS NOT NULL),
  base_currency TEXT NOT NULL
    CHECK (length(base_currency)=3 AND base_currency=upper(base_currency)),
  founder_rate_micros_per_minute INTEGER NOT NULL
    CHECK (founder_rate_micros_per_minute>0),
  CHECK (datetime(default_snapshot_as_of)<=datetime(reference_time))
) STRICT;

CREATE TABLE evidence_class_dim (
  evidence_class TEXT PRIMARY KEY CHECK (evidence_class IN (
    'E0_INFERENCE','E1_MANUAL','E2_PROGRAM_CONTROL_PLANE',
    'E3_PROVIDER_EVENT','E4_FIRST_PARTY_AUTHORITATIVE',
    'E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
  )),
  strength_rank INTEGER NOT NULL UNIQUE CHECK (strength_rank BETWEEN 0 AND 5)
) STRICT;

CREATE TABLE motion_dim (
  motion_code TEXT PRIMARY KEY CHECK (motion_code IN (
    'OUTSOURCING','EMPLOYMENT','REFERRAL','AFFILIATE','SALES_AGENT',
    'RESELLER','IMPLEMENTATION_SUPPORT','WHITE_LABEL','OEM'
  )),
  motion_family TEXT NOT NULL CHECK (motion_family IN (
    'WORKFORCE','CHANNEL','SERVICE','PRESENTATION','LICENSE'
  )),
  is_workforce INTEGER NOT NULL CHECK (is_workforce IN (0,1)),
  is_channel INTEGER NOT NULL CHECK (is_channel IN (0,1)),
  is_service INTEGER NOT NULL CHECK (is_service IN (0,1)),
  is_license INTEGER NOT NULL CHECK (is_license IN (0,1)),
  CHECK (is_workforce+is_channel+is_service+is_license>=1
         OR motion_code='WHITE_LABEL')
) STRICT;

CREATE TABLE canonical_stage_dim (
  stage_code TEXT PRIMARY KEY CHECK (stage_code IN (
    'POLICY_SNAPSHOTTED','PARTNER_ELIGIBLE','AGREEMENT_EFFECTIVE',
    'ENABLED_OR_CERTIFIED','LEAD_SOURCED','CONSENT_DISCLOSURE_PASS',
    'LEAD_REGISTERED','ACCEPTED_AND_DEDUPED','ATTRIBUTED','QUALIFIED',
    'INVOLVEMENT_PROVED','QUOTE_OR_ORDER_ACCEPTED',
    'CUSTOMER_CONTRACT_EFFECTIVE','REQUIRED_REVENUE_BASE_OBSERVED',
    'ENTITLED_OR_DELIVERED','OUTCOME_ACCEPTED',
    'REFUND_CLAWBACK_WINDOW_MATURE','COMMISSION_OR_MARGIN_ACCRUED',
    'PAYOUT_BANK_RECONCILED','RENEWED','TERMINATED_HANDOFF_CLOSED'
  )),
  display_order INTEGER NOT NULL UNIQUE CHECK (display_order BETWEEN 10 AND 210),
  stage_domain TEXT NOT NULL CHECK (stage_domain IN (
    'POLICY','AGREEMENT','ENABLEMENT','LEAD','ATTRIBUTION','SALES',
    'CONTRACT','CASH','DELIVERY','QUALITY','ECONOMICS','RETENTION','OFFBOARDING'
  ))
) STRICT;

CREATE TABLE responsibility_domain_dim (
  responsibility_code TEXT PRIMARY KEY CHECK (responsibility_code IN (
    'PRODUCT_CONTRACT','SERVICE_CONTRACT','INVOICE_ISSUER','CASH_COLLECTOR',
    'MERCHANT_OF_RECORD','PRODUCT_DATA_CONTROLLER','LEAD_DATA_CONTROLLER',
    'DATA_PROCESSOR','SUPPORT_L1','SUPPORT_L2','SUPPORT_L3','FULFILLMENT',
    'REFUND_OWNER','DISPUTE_OWNER','SECURITY_INCIDENT_OWNER',
    'DATA_EXPORT_DELETE_OWNER','RENEWAL_HANDOFF_OWNER'
  )),
  category_code TEXT NOT NULL CHECK (category_code IN (
    'CONTRACT','MONEY','DATA','SUPPORT','OPERATIONS','OFFBOARDING'
  ))
) STRICT;

CREATE TABLE authority_code_dim (
  authority_code TEXT PRIMARY KEY CHECK (authority_code IN (
    'INTRODUCE_LEAD','PUBLISH_TRACKED_PROMOTION','SOLICIT_CUSTOMER',
    'NEGOTIATE_TERMS','BIND_PRINCIPAL','SET_DOWNSTREAM_PRICE',
    'INVOICE_CUSTOMER','COLLECT_CUSTOMER_CASH','DELIVER_SERVICE',
    'ACCESS_CUSTOMER_SYSTEM','USE_PRINCIPAL_BRAND','USE_WHITE_LABEL_BRAND',
    'SUBLICENSE_PRODUCT','BUNDLE_PRODUCT','CONTROL_WORK_MANNER'
  ))
) STRICT;

CREATE TABLE work_class_dim (
  work_class_code TEXT PRIMARY KEY CHECK (work_class_code IN (
    'STANDARD_DELIVERY','PLANNED_REVIEW','PARTNER_REWORK','SUPPORT',
    'INCIDENT','FOUNDER_RESCUE','PARTNER_ENABLEMENT','LEAD_REVIEW',
    'DEAL_RESCUE','DELIVERY_QA','COMMISSION_DISPUTE','COLLECTIONS','OFFBOARDING'
  )),
  quality_effect TEXT NOT NULL CHECK (quality_effect IN (
    'PLANNED','REWORK','RESCUE','INCIDENT','OVERHEAD'
  ))
) STRICT;

CREATE TABLE handoff_item_dim (
  item_code TEXT PRIMARY KEY CHECK (item_code IN (
    'ACCESS_REVOKED','DATA_DISPOSITION','CUSTOMER_NOTICE','OPEN_SUPPORT',
    'OPEN_INCIDENTS','OPEN_REFUNDS','RENEWAL_OWNER','ENTITLEMENT_CONTINUITY',
    'COMMISSION_TAIL','IP_AND_ASSET_RETURN','PAYROLL_OR_FINAL_INVOICE'
  )),
  required_for_all INTEGER NOT NULL CHECK (required_for_all IN (0,1))
) STRICT;

CREATE TABLE party (
  party_id TEXT PRIMARY KEY,
  party_kind TEXT NOT NULL CHECK (party_kind IN (
    'PRINCIPAL','PARTNER','CUSTOMER','WORKER','PROVIDER'
  )),
  legal_entity_ref_hash TEXT NOT NULL UNIQUE CHECK (
    length(legal_entity_ref_hash)=64
    AND legal_entity_ref_hash=lower(legal_entity_ref_hash)
    AND legal_entity_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  country_code TEXT NOT NULL CHECK (
    length(country_code)=2 AND country_code=upper(country_code)
  ),
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(created_at)<=datetime(recorded_at))
) STRICT;

CREATE TABLE commercial_account (
  account_id TEXT PRIMARY KEY,
  customer_party_id TEXT,
  account_ref_hash TEXT NOT NULL UNIQUE CHECK (
    length(account_ref_hash)=64
    AND account_ref_hash=lower(account_ref_hash)
    AND account_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  segment_code TEXT NOT NULL,
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(created_at)<=datetime(recorded_at)),
  FOREIGN KEY (customer_party_id) REFERENCES party(party_id)
) STRICT;

CREATE TABLE evidence_record (
  evidence_id TEXT PRIMARY KEY,
  evidence_class TEXT NOT NULL,
  evidence_domain TEXT NOT NULL CHECK (evidence_domain IN (
    'POLICY','LEGAL','AUTHORITY','RESPONSIBILITY','PRIVACY','SECURITY',
    'PAYMENT','WORK','QUALITY','LEAD_CONSENT','LEAD_DISCLOSURE',
    'PROVIDER_ACCEPTANCE','BANK','EXPERIMENT','TERMINATION','HANDOFF','TIER'
  )),
  evidence_status TEXT NOT NULL CHECK (evidence_status IN (
    'CONFIRMED','REFUTED','UNKNOWN','STALE'
  )),
  source_ref_hash TEXT NOT NULL CHECK (
    length(source_ref_hash)=64
    AND source_ref_hash=lower(source_ref_hash)
    AND source_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  valid_until TEXT CHECK (valid_until IS NULL OR datetime(valid_until) IS NOT NULL),
  reviewed_by_role TEXT NOT NULL,
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  CHECK (valid_until IS NULL OR datetime(occurred_at)<datetime(valid_until))
) STRICT;

CREATE TABLE policy_snapshot (
  policy_snapshot_id TEXT PRIMARY KEY,
  provider_party_id TEXT NOT NULL,
  program_code TEXT NOT NULL,
  policy_version TEXT NOT NULL,
  source_url TEXT NOT NULL CHECK (source_url LIKE 'https://%'),
  source_content_hash TEXT NOT NULL CHECK (
    length(source_content_hash)=64
    AND source_content_hash=lower(source_content_hash)
    AND source_content_hash NOT GLOB '*[^0-9a-f]*'
  ),
  availability_state TEXT NOT NULL CHECK (availability_state IN (
    'PUBLIC','PORTAL_ONLY','PRIVATE_ADDENDUM','UNKNOWN'
  )),
  snapshot_status TEXT NOT NULL CHECK (snapshot_status IN (
    'CURRENT','UPCOMING','DEPRECATED','CONFLICTING','UNKNOWN'
  )),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  captured_at TEXT NOT NULL CHECK (datetime(captured_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (effective_until IS NULL
         OR datetime(effective_from)<datetime(effective_until)),
  UNIQUE (provider_party_id,program_code,policy_version),
  FOREIGN KEY (provider_party_id) REFERENCES party(party_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE agreement_version (
  agreement_version_id TEXT PRIMARY KEY,
  agreement_stream_id TEXT NOT NULL,
  version_sequence INTEGER NOT NULL CHECK (version_sequence>0),
  supersedes_agreement_version_id TEXT,
  motion_code TEXT NOT NULL,
  principal_party_id TEXT NOT NULL,
  partner_party_id TEXT NOT NULL,
  policy_snapshot_id TEXT,
  governing_law_code TEXT NOT NULL,
  territory_code TEXT NOT NULL,
  commercial_status TEXT NOT NULL CHECK (commercial_status IN (
    'DRAFT','PILOT','LIVE','PAUSE','TERMINATED'
  )),
  legal_review_status TEXT NOT NULL CHECK (legal_review_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  authority_review_status TEXT NOT NULL CHECK (authority_review_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  worker_classification_status TEXT NOT NULL CHECK (
    worker_classification_status IN ('PASS','FAIL','UNKNOWN','NA')
  ),
  privacy_review_status TEXT NOT NULL CHECK (privacy_review_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  security_review_status TEXT NOT NULL CHECK (security_review_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  payment_review_status TEXT NOT NULL CHECK (payment_review_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  ip_license_review_status TEXT NOT NULL CHECK (ip_license_review_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  termination_review_status TEXT NOT NULL CHECK (termination_review_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  agreement_ref_hash TEXT NOT NULL CHECK (
    length(agreement_ref_hash)=64
    AND agreement_ref_hash=lower(agreement_ref_hash)
    AND agreement_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  approved_at TEXT NOT NULL CHECK (datetime(approved_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (principal_party_id<>partner_party_id),
  CHECK (effective_until IS NULL
         OR datetime(effective_from)<datetime(effective_until)),
  CHECK (datetime(approved_at)<=datetime(recorded_at)),
  UNIQUE (agreement_stream_id,version_sequence),
  FOREIGN KEY (supersedes_agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (motion_code) REFERENCES motion_dim(motion_code),
  FOREIGN KEY (principal_party_id) REFERENCES party(party_id),
  FOREIGN KEY (partner_party_id) REFERENCES party(party_id),
  FOREIGN KEY (policy_snapshot_id) REFERENCES policy_snapshot(policy_snapshot_id)
) STRICT;

CREATE TRIGGER validate_agreement_version_before_insert
BEFORE INSERT ON agreement_version
BEGIN
  SELECT RAISE(ABORT,'agreement version must append exact predecessor')
  WHERE (NEW.version_sequence=1 AND NEW.supersedes_agreement_version_id IS NOT NULL)
     OR (NEW.version_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM agreement_version p
       WHERE p.agreement_version_id=NEW.supersedes_agreement_version_id
         AND p.agreement_stream_id=NEW.agreement_stream_id
         AND p.version_sequence=NEW.version_sequence-1
     ));
  SELECT RAISE(ABORT,'LIVE requires known passing legal and control reviews')
  WHERE NEW.commercial_status='LIVE' AND (
    NEW.legal_review_status<>'PASS'
    OR NEW.authority_review_status NOT IN ('PASS','NA')
    OR NEW.privacy_review_status NOT IN ('PASS','NA')
    OR NEW.security_review_status NOT IN ('PASS','NA')
    OR NEW.payment_review_status NOT IN ('PASS','NA')
    OR NEW.ip_license_review_status NOT IN ('PASS','NA')
    OR NEW.termination_review_status NOT IN ('PASS','NA')
  );
  SELECT RAISE(ABORT,'workforce LIVE requires classification review PASS')
  WHERE NEW.commercial_status='LIVE'
    AND NEW.motion_code IN ('OUTSOURCING','EMPLOYMENT')
    AND NEW.worker_classification_status<>'PASS';
  SELECT RAISE(ABORT,'channel and license agreements require a policy snapshot')
  WHERE NEW.commercial_status='LIVE'
    AND NEW.motion_code IN (
      'REFERRAL','AFFILIATE','SALES_AGENT','RESELLER','WHITE_LABEL','OEM'
    ) AND NEW.policy_snapshot_id IS NULL;
  SELECT RAISE(ABORT,'OEM and white-label LIVE require IP review PASS')
  WHERE NEW.commercial_status='LIVE'
    AND NEW.motion_code IN ('OEM','WHITE_LABEL')
    AND NEW.ip_license_review_status<>'PASS';
END;

CREATE TABLE agreement_evidence_link (
  agreement_version_id TEXT NOT NULL,
  review_domain TEXT NOT NULL CHECK (review_domain IN (
    'LEGAL','AUTHORITY','WORKER_CLASSIFICATION','PRIVACY','SECURITY',
    'PAYMENT','IP_LICENSE','TERMINATION','POLICY'
  )),
  evidence_id TEXT NOT NULL,
  link_status TEXT NOT NULL CHECK (link_status IN ('SUPPORTS','CONTRADICTS','UNKNOWN')),
  PRIMARY KEY (agreement_version_id,review_domain,evidence_id),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE motion_stage_contract (
  motion_code TEXT NOT NULL,
  stage_code TEXT NOT NULL,
  applicability TEXT NOT NULL CHECK (applicability IN ('REQUIRED','OPTIONAL','NA')),
  minimum_evidence_class TEXT,
  source_of_truth_code TEXT,
  PRIMARY KEY (motion_code,stage_code),
  CHECK ((applicability='NA' AND minimum_evidence_class IS NULL
          AND source_of_truth_code IS NULL)
      OR (applicability<>'NA' AND minimum_evidence_class IS NOT NULL
          AND source_of_truth_code IS NOT NULL)),
  FOREIGN KEY (motion_code) REFERENCES motion_dim(motion_code),
  FOREIGN KEY (stage_code) REFERENCES canonical_stage_dim(stage_code),
  FOREIGN KEY (minimum_evidence_class)
    REFERENCES evidence_class_dim(evidence_class)
) STRICT;

CREATE TABLE motion_responsibility_requirement (
  motion_code TEXT NOT NULL,
  responsibility_code TEXT NOT NULL,
  allow_na INTEGER NOT NULL CHECK (allow_na IN (0,1)),
  PRIMARY KEY (motion_code,responsibility_code),
  FOREIGN KEY (motion_code) REFERENCES motion_dim(motion_code),
  FOREIGN KEY (responsibility_code)
    REFERENCES responsibility_domain_dim(responsibility_code)
) STRICT;

CREATE TABLE responsibility_assignment (
  agreement_version_id TEXT NOT NULL,
  scope_code TEXT NOT NULL,
  responsibility_code TEXT NOT NULL,
  disposition TEXT NOT NULL CHECK (disposition IN ('CONFIRMED','UNKNOWN','NA')),
  owner_party_id TEXT,
  evidence_id TEXT,
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  PRIMARY KEY (agreement_version_id,scope_code,responsibility_code),
  CHECK ((disposition='CONFIRMED' AND owner_party_id IS NOT NULL
          AND evidence_id IS NOT NULL)
      OR (disposition='UNKNOWN' AND owner_party_id IS NULL)
      OR (disposition='NA' AND owner_party_id IS NULL
          AND evidence_id IS NOT NULL)),
  CHECK (effective_until IS NULL
         OR datetime(effective_from)<datetime(effective_until)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (responsibility_code)
    REFERENCES responsibility_domain_dim(responsibility_code),
  FOREIGN KEY (owner_party_id) REFERENCES party(party_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE motion_authority_requirement (
  motion_code TEXT NOT NULL,
  authority_code TEXT NOT NULL,
  requirement_code TEXT NOT NULL CHECK (requirement_code IN (
    'MUST_GRANT','MUST_DENY','MUST_DISPOSE'
  )),
  PRIMARY KEY (motion_code,authority_code),
  FOREIGN KEY (motion_code) REFERENCES motion_dim(motion_code),
  FOREIGN KEY (authority_code) REFERENCES authority_code_dim(authority_code)
) STRICT;

CREATE TABLE authority_grant (
  agreement_version_id TEXT NOT NULL,
  authority_code TEXT NOT NULL,
  scope_code TEXT NOT NULL,
  territory_code TEXT NOT NULL,
  disposition TEXT NOT NULL CHECK (disposition IN (
    'GRANTED','DENIED','UNKNOWN','NA'
  )),
  evidence_id TEXT,
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  PRIMARY KEY (agreement_version_id,authority_code,scope_code,territory_code),
  CHECK ((disposition IN ('GRANTED','DENIED','NA') AND evidence_id IS NOT NULL)
      OR disposition='UNKNOWN'),
  CHECK (effective_until IS NULL
         OR datetime(effective_from)<datetime(effective_until)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (authority_code) REFERENCES authority_code_dim(authority_code),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE tier_rule_version (
  tier_rule_version_id TEXT PRIMARY KEY,
  tier_rule_stream_id TEXT NOT NULL,
  rule_sequence INTEGER NOT NULL CHECK (rule_sequence>0),
  supersedes_tier_rule_version_id TEXT,
  policy_snapshot_id TEXT NOT NULL,
  tier_code TEXT NOT NULL,
  metric_code TEXT NOT NULL,
  threshold_state TEXT NOT NULL CHECK (threshold_state IN (
    'KNOWN','PORTAL_ONLY','UNKNOWN','NA'
  )),
  threshold_value INTEGER,
  window_days INTEGER CHECK (window_days IS NULL OR window_days>=0),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  evidence_id TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((threshold_state='KNOWN' AND threshold_value IS NOT NULL)
      OR (threshold_state<>'KNOWN' AND threshold_value IS NULL)),
  CHECK (effective_until IS NULL
         OR datetime(effective_from)<datetime(effective_until)),
  UNIQUE (tier_rule_stream_id,rule_sequence),
  FOREIGN KEY (supersedes_tier_rule_version_id)
    REFERENCES tier_rule_version(tier_rule_version_id),
  FOREIGN KEY (policy_snapshot_id) REFERENCES policy_snapshot(policy_snapshot_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE enablement_evidence (
  enablement_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  enablement_type TEXT NOT NULL CHECK (enablement_type IN (
    'TRAINING','CERTIFICATION','ASSESSMENT','CUSTOMER_REFERENCE','PLAYBOOK'
  )),
  result_status TEXT NOT NULL CHECK (result_status IN (
    'PASS','FAIL','UNKNOWN','EXPIRED'
  )),
  evidence_id TEXT NOT NULL,
  achieved_at TEXT NOT NULL CHECK (datetime(achieved_at) IS NOT NULL),
  expires_at TEXT CHECK (expires_at IS NULL OR datetime(expires_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (expires_at IS NULL OR datetime(achieved_at)<datetime(expires_at)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_tier_rule_version_before_insert
BEFORE INSERT ON tier_rule_version
BEGIN
  SELECT RAISE(ABORT,'tier rule must append exact predecessor')
  WHERE (NEW.rule_sequence=1 AND NEW.supersedes_tier_rule_version_id IS NOT NULL)
     OR (NEW.rule_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM tier_rule_version p
       WHERE p.tier_rule_version_id=NEW.supersedes_tier_rule_version_id
         AND p.tier_rule_stream_id=NEW.tier_rule_stream_id
         AND p.rule_sequence=NEW.rule_sequence-1
     ));
END;

-- Access is a scoped event stream. A later revoke expires only the exact stream.
CREATE TABLE access_event (
  access_event_id TEXT PRIMARY KEY,
  access_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>0),
  supersedes_access_event_id TEXT,
  agreement_version_id TEXT NOT NULL,
  account_id TEXT,
  grantee_party_id TEXT NOT NULL,
  system_scope_ref_hash TEXT NOT NULL CHECK (
    length(system_scope_ref_hash)=64
    AND system_scope_ref_hash=lower(system_scope_ref_hash)
    AND system_scope_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  data_class_code TEXT NOT NULL CHECK (data_class_code IN (
    'SYNTHETIC','INTERNAL_NONPERSONAL','CUSTOMER_CONFIDENTIAL',
    'PERSONAL_DATA','SPECIAL_CATEGORY_PROHIBITED'
  )),
  privilege_code TEXT NOT NULL CHECK (privilege_code IN (
    'READ','DRAFT','REVERSIBLE_WRITE','ADMIN','SUPPORT_SESSION'
  )),
  event_type TEXT NOT NULL CHECK (event_type IN (
    'GRANTED','REVOKED','EXPIRED'
  )),
  customer_authorization_status TEXT NOT NULL CHECK (
    customer_authorization_status IN ('PASS','FAIL','UNKNOWN','NA')
  ),
  least_privilege_status TEXT NOT NULL CHECK (
    least_privilege_status IN ('PASS','FAIL','UNKNOWN','NA')
  ),
  effective_at TEXT NOT NULL CHECK (datetime(effective_at) IS NOT NULL),
  expires_at TEXT CHECK (expires_at IS NULL OR datetime(expires_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (expires_at IS NULL OR datetime(effective_at)<datetime(expires_at)),
  CHECK (datetime(effective_at)<=datetime(recorded_at)),
  UNIQUE (access_stream_id,event_sequence),
  FOREIGN KEY (supersedes_access_event_id) REFERENCES access_event(access_event_id),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (account_id) REFERENCES commercial_account(account_id),
  FOREIGN KEY (grantee_party_id) REFERENCES party(party_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_access_event_before_insert
BEFORE INSERT ON access_event
BEGIN
  SELECT RAISE(ABORT,'access event must append exact predecessor')
  WHERE (NEW.event_sequence=1 AND NEW.supersedes_access_event_id IS NOT NULL)
     OR (NEW.event_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM access_event p
       WHERE p.access_event_id=NEW.supersedes_access_event_id
         AND p.access_stream_id=NEW.access_stream_id
         AND p.event_sequence=NEW.event_sequence-1
         AND p.agreement_version_id=NEW.agreement_version_id
         AND p.grantee_party_id=NEW.grantee_party_id
         AND p.system_scope_ref_hash=NEW.system_scope_ref_hash
     ));
  SELECT RAISE(ABORT,'access grant requires customer authorization and least privilege PASS')
  WHERE NEW.event_type='GRANTED' AND (
    NEW.customer_authorization_status<>'PASS'
    OR NEW.least_privilege_status<>'PASS'
    OR NEW.expires_at IS NULL
  );
  SELECT RAISE(ABORT,'revocation or expiry must follow an active grant')
  WHERE NEW.event_type IN ('REVOKED','EXPIRED') AND NOT EXISTS (
    SELECT 1 FROM access_event p
    WHERE p.access_event_id=NEW.supersedes_access_event_id
      AND p.event_type='GRANTED'
  );
  SELECT RAISE(ABORT,'terminated agreement cannot grant new access')
  WHERE NEW.event_type='GRANTED' AND EXISTS (
    SELECT 1 FROM agreement_version a
    WHERE a.agreement_version_id=NEW.agreement_version_id
      AND a.commercial_status='TERMINATED'
  );
END;

CREATE TABLE work_order (
  work_order_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  service_scope_code TEXT NOT NULL,
  acceptance_method_code TEXT NOT NULL CHECK (acceptance_method_code IN (
    'CUSTOMER_SIGNOFF','OBJECTIVE_TEST','SLA_PERIOD','MANAGER_APPROVAL'
  )),
  contracted_fee_micros INTEGER NOT NULL CHECK (contracted_fee_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  planned_start_at TEXT NOT NULL CHECK (datetime(planned_start_at) IS NOT NULL),
  planned_end_at TEXT NOT NULL CHECK (datetime(planned_end_at) IS NOT NULL),
  work_order_ref_hash TEXT NOT NULL CHECK (
    length(work_order_ref_hash)=64
    AND work_order_ref_hash=lower(work_order_ref_hash)
    AND work_order_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(planned_start_at)<datetime(planned_end_at)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (account_id) REFERENCES commercial_account(account_id)
) STRICT;

CREATE TABLE work_event (
  work_event_id TEXT PRIMARY KEY,
  work_order_id TEXT NOT NULL,
  actor_party_id TEXT NOT NULL,
  cost_bearer_party_id TEXT NOT NULL,
  actor_role_code TEXT NOT NULL CHECK (actor_role_code IN (
    'PARTNER','WORKER','FOUNDER','CUSTOMER','PRINCIPAL'
  )),
  work_class_code TEXT NOT NULL,
  minutes INTEGER NOT NULL CHECK (minutes>0),
  cash_cost_micros INTEGER NOT NULL CHECK (cash_cost_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  evidence_id TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  FOREIGN KEY (work_order_id) REFERENCES work_order(work_order_id),
  FOREIGN KEY (actor_party_id) REFERENCES party(party_id),
  FOREIGN KEY (cost_bearer_party_id) REFERENCES party(party_id),
  FOREIGN KEY (work_class_code) REFERENCES work_class_dim(work_class_code),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_work_event_before_insert
BEFORE INSERT ON work_event
BEGIN
  SELECT RAISE(ABORT,'founder work must be borne by the agreement principal')
  WHERE NEW.actor_role_code='FOUNDER'
    AND EXISTS (
      SELECT 1 FROM work_order w
      JOIN agreement_version a USING (agreement_version_id)
      WHERE w.work_order_id=NEW.work_order_id
    )
    AND NOT EXISTS (
    SELECT 1 FROM work_order w
    JOIN agreement_version a USING (agreement_version_id)
    WHERE w.work_order_id=NEW.work_order_id
      AND NEW.actor_party_id=a.principal_party_id
      AND NEW.cost_bearer_party_id=a.principal_party_id
      AND NEW.currency=a.currency
  );
  SELECT RAISE(ABORT,'rework or rescue cannot be appended after first-pass acceptance')
  WHERE NEW.work_class_code IN ('PARTNER_REWORK','FOUNDER_RESCUE') AND EXISTS (
    SELECT 1 FROM acceptance_event a
    WHERE a.work_order_id=NEW.work_order_id
      AND a.outcome_code='ACCEPTED_FIRST_PASS'
      AND datetime(a.occurred_at)<=datetime(NEW.occurred_at)
      AND NOT EXISTS (
        SELECT 1 FROM acceptance_event later
        WHERE later.acceptance_stream_id=a.acceptance_stream_id
          AND later.event_sequence>a.event_sequence
          AND datetime(later.recorded_at)<=datetime(NEW.recorded_at)
      )
  );
END;

CREATE TABLE acceptance_event (
  acceptance_event_id TEXT PRIMARY KEY,
  acceptance_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>0),
  supersedes_acceptance_event_id TEXT,
  work_order_id TEXT NOT NULL,
  outcome_code TEXT NOT NULL CHECK (outcome_code IN (
    'PENDING','ACCEPTED_FIRST_PASS','ACCEPTED_AFTER_PARTNER_REWORK',
    'ACCEPTED_AFTER_FOUNDER_RESCUE','REJECTED','INCIDENT'
  )),
  evidence_status TEXT NOT NULL CHECK (evidence_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  evidence_id TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((outcome_code LIKE 'ACCEPTED_%' AND evidence_status='PASS'
          AND evidence_id IS NOT NULL)
      OR (outcome_code NOT LIKE 'ACCEPTED_%')),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  UNIQUE (acceptance_stream_id,event_sequence),
  FOREIGN KEY (supersedes_acceptance_event_id)
    REFERENCES acceptance_event(acceptance_event_id),
  FOREIGN KEY (work_order_id) REFERENCES work_order(work_order_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_acceptance_event_before_insert
BEFORE INSERT ON acceptance_event
BEGIN
  SELECT RAISE(ABORT,'acceptance event must append exact predecessor')
  WHERE (NEW.event_sequence=1 AND NEW.supersedes_acceptance_event_id IS NOT NULL)
     OR (NEW.event_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM acceptance_event p
       WHERE p.acceptance_event_id=NEW.supersedes_acceptance_event_id
         AND p.acceptance_stream_id=NEW.acceptance_stream_id
         AND p.event_sequence=NEW.event_sequence-1
         AND p.work_order_id=NEW.work_order_id
     ));
  SELECT RAISE(ABORT,'first-pass acceptance cannot coexist with rework or founder rescue')
  WHERE NEW.outcome_code='ACCEPTED_FIRST_PASS' AND EXISTS (
    SELECT 1 FROM work_event w
    WHERE w.work_order_id=NEW.work_order_id
      AND w.work_class_code IN ('PARTNER_REWORK','FOUNDER_RESCUE')
      AND datetime(w.occurred_at)<=datetime(NEW.occurred_at)
  );
  SELECT RAISE(ABORT,'after-rework acceptance requires partner rework evidence')
  WHERE NEW.outcome_code='ACCEPTED_AFTER_PARTNER_REWORK' AND NOT EXISTS (
    SELECT 1 FROM work_event w
    WHERE w.work_order_id=NEW.work_order_id
      AND w.work_class_code='PARTNER_REWORK'
      AND datetime(w.occurred_at)<=datetime(NEW.occurred_at)
  );
  SELECT RAISE(ABORT,'after-rescue acceptance requires founder rescue evidence')
  WHERE NEW.outcome_code='ACCEPTED_AFTER_FOUNDER_RESCUE' AND NOT EXISTS (
    SELECT 1 FROM work_event w
    WHERE w.work_order_id=NEW.work_order_id
      AND w.work_class_code='FOUNDER_RESCUE'
      AND datetime(w.occurred_at)<=datetime(NEW.occurred_at)
  );
END;

CREATE TABLE lead_source_event (
  lead_source_event_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  source_kind TEXT NOT NULL CHECK (source_kind IN (
    'WARM_INTRO','TRACKED_LINK','TRACKED_CODE','AGENT_OUTREACH',
    'PARTNER_DISCOVERY','CUSTOMER_REQUEST'
  )),
  consent_status TEXT NOT NULL CHECK (consent_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  disclosure_status TEXT NOT NULL CHECK (disclosure_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  consent_evidence_id TEXT,
  disclosure_evidence_id TEXT,
  source_ref_hash TEXT NOT NULL CHECK (
    length(source_ref_hash)=64
    AND source_ref_hash=lower(source_ref_hash)
    AND source_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((consent_status='PASS' AND consent_evidence_id IS NOT NULL)
      OR consent_status<>'PASS'),
  CHECK ((disclosure_status='PASS' AND disclosure_evidence_id IS NOT NULL)
      OR disclosure_status<>'PASS'),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (account_id) REFERENCES commercial_account(account_id),
  FOREIGN KEY (consent_evidence_id) REFERENCES evidence_record(evidence_id),
  FOREIGN KEY (disclosure_evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE lead_registration_event (
  registration_event_id TEXT PRIMARY KEY,
  registration_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>0),
  supersedes_registration_event_id TEXT,
  lead_source_event_id TEXT NOT NULL,
  agreement_version_id TEXT NOT NULL,
  policy_snapshot_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  product_scope_code TEXT NOT NULL,
  territory_code TEXT NOT NULL,
  registration_status TEXT NOT NULL CHECK (registration_status IN (
    'SUBMITTED','ACCEPTED','REJECTED','EXPIRED'
  )),
  provider_registration_ref_hash TEXT NOT NULL CHECK (
    length(provider_registration_ref_hash)=64
    AND provider_registration_ref_hash=lower(provider_registration_ref_hash)
    AND provider_registration_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  exclusivity_until TEXT CHECK (
    exclusivity_until IS NULL OR datetime(exclusivity_until) IS NOT NULL
  ),
  provider_evidence_id TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((registration_status='ACCEPTED' AND provider_evidence_id IS NOT NULL)
      OR registration_status<>'ACCEPTED'),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  UNIQUE (registration_stream_id,event_sequence),
  FOREIGN KEY (supersedes_registration_event_id)
    REFERENCES lead_registration_event(registration_event_id),
  FOREIGN KEY (lead_source_event_id)
    REFERENCES lead_source_event(lead_source_event_id),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (policy_snapshot_id) REFERENCES policy_snapshot(policy_snapshot_id),
  FOREIGN KEY (account_id) REFERENCES commercial_account(account_id),
  FOREIGN KEY (provider_evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_lead_registration_before_insert
BEFORE INSERT ON lead_registration_event
BEGIN
  SELECT RAISE(ABORT,'registration event must append exact predecessor')
  WHERE (NEW.event_sequence=1 AND NEW.supersedes_registration_event_id IS NOT NULL)
     OR (NEW.event_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM lead_registration_event p
       WHERE p.registration_event_id=NEW.supersedes_registration_event_id
         AND p.registration_stream_id=NEW.registration_stream_id
         AND p.event_sequence=NEW.event_sequence-1
         AND p.lead_source_event_id=NEW.lead_source_event_id
     ));
  SELECT RAISE(ABORT,'accepted registration requires consent PASS')
  WHERE NEW.registration_status='ACCEPTED' AND NOT EXISTS (
    SELECT 1 FROM lead_source_event s
    WHERE s.lead_source_event_id=NEW.lead_source_event_id
      AND s.account_id=NEW.account_id
      AND s.agreement_version_id=NEW.agreement_version_id
      AND s.consent_status='PASS'
  );
  SELECT RAISE(ABORT,'affiliate acceptance requires disclosure PASS')
  WHERE NEW.registration_status='ACCEPTED' AND EXISTS (
    SELECT 1 FROM agreement_version a
    JOIN lead_source_event s
      ON s.lead_source_event_id=NEW.lead_source_event_id
    WHERE a.agreement_version_id=NEW.agreement_version_id
      AND a.motion_code='AFFILIATE'
      AND s.disclosure_status<>'PASS'
  );
END;

CREATE TABLE bank_evidence (
  bank_evidence_id TEXT PRIMARY KEY,
  evidence_type TEXT NOT NULL CHECK (evidence_type IN (
    'CUSTOMER_COLLECTION','SERVICE_COLLECTION','PARTNER_PAYOUT',
    'UPSTREAM_PAYMENT','REFUND_SETTLEMENT','ROYALTY_PAYMENT'
  )),
  direction_code TEXT NOT NULL CHECK (direction_code IN ('INFLOW','OUTFLOW')),
  account_owner_party_id TEXT NOT NULL,
  counterparty_party_id TEXT NOT NULL,
  amount_micros INTEGER NOT NULL CHECK (amount_micros>0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  reconciliation_status TEXT NOT NULL CHECK (reconciliation_status IN (
    'RECONCILED','UNRECONCILED','UNKNOWN'
  )),
  bank_ref_hash TEXT NOT NULL UNIQUE CHECK (
    length(bank_ref_hash)=64
    AND bank_ref_hash=lower(bank_ref_hash)
    AND bank_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at)<=datetime(recorded_at))
  ,CHECK (account_owner_party_id<>counterparty_party_id)
  ,FOREIGN KEY (account_owner_party_id) REFERENCES party(party_id)
  ,FOREIGN KEY (counterparty_party_id) REFERENCES party(party_id)
) STRICT;

CREATE TABLE commercial_event (
  commercial_event_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  event_type TEXT NOT NULL CHECK (event_type IN (
    'BOOKING','INVOICE','RECOGNIZED_REVENUE','CUSTOMER_CASH','SERVICE_CASH',
    'REFUND','CHARGEBACK','DOWNGRADE','RENEWAL','WHOLESALE_OBLIGATION',
    'WHOLESALE_CASH','ROYALTY_CASH'
  )),
  payer_party_id TEXT NOT NULL,
  payee_party_id TEXT NOT NULL,
  amount_micros INTEGER NOT NULL CHECK (amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  bank_evidence_id TEXT,
  adjusts_commercial_event_id TEXT,
  source_ref_hash TEXT NOT NULL CHECK (
    length(source_ref_hash)=64
    AND source_ref_hash=lower(source_ref_hash)
    AND source_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  CHECK (payer_party_id<>payee_party_id),
  CHECK ((event_type IN ('REFUND','CHARGEBACK','DOWNGRADE')
          AND adjusts_commercial_event_id IS NOT NULL)
      OR (event_type NOT IN ('REFUND','CHARGEBACK','DOWNGRADE')
          AND adjusts_commercial_event_id IS NULL)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (account_id) REFERENCES commercial_account(account_id),
  FOREIGN KEY (payer_party_id) REFERENCES party(party_id),
  FOREIGN KEY (payee_party_id) REFERENCES party(party_id),
  FOREIGN KEY (bank_evidence_id) REFERENCES bank_evidence(bank_evidence_id)
  ,FOREIGN KEY (adjusts_commercial_event_id)
    REFERENCES commercial_event(commercial_event_id)
) STRICT;

CREATE UNIQUE INDEX one_commercial_cash_event_per_bank_evidence
ON commercial_event(bank_evidence_id)
WHERE bank_evidence_id IS NOT NULL;

CREATE TRIGGER validate_commercial_event_before_insert
BEFORE INSERT ON commercial_event
BEGIN
  SELECT RAISE(ABORT,'commercial adjustment must reference an earlier exact base')
  WHERE NEW.event_type IN ('REFUND','CHARGEBACK','DOWNGRADE') AND NOT EXISTS (
    SELECT 1 FROM commercial_event b
    WHERE b.commercial_event_id=NEW.adjusts_commercial_event_id
      AND b.agreement_version_id=NEW.agreement_version_id
      AND b.account_id=NEW.account_id
      AND b.currency=NEW.currency
      AND b.event_type IN (
        'BOOKING','INVOICE','RECOGNIZED_REVENUE','CUSTOMER_CASH','SERVICE_CASH'
      )
      AND datetime(b.occurred_at)<=datetime(NEW.occurred_at)
  );
  SELECT RAISE(ABORT,'cash event requires matching reconciled bank evidence')
  WHERE NEW.event_type IN (
    'CUSTOMER_CASH','SERVICE_CASH','WHOLESALE_CASH','ROYALTY_CASH'
  ) AND NOT EXISTS (
    SELECT 1 FROM bank_evidence b
    WHERE b.bank_evidence_id=NEW.bank_evidence_id
      AND b.reconciliation_status='RECONCILED'
      AND b.amount_micros=NEW.amount_micros
      AND b.currency=NEW.currency
      AND datetime(b.occurred_at)=datetime(NEW.occurred_at)
      AND ((b.direction_code='INFLOW'
            AND b.account_owner_party_id=NEW.payee_party_id
            AND b.counterparty_party_id=NEW.payer_party_id)
        OR (b.direction_code='OUTFLOW'
            AND b.account_owner_party_id=NEW.payer_party_id
            AND b.counterparty_party_id=NEW.payee_party_id))
      AND ((NEW.event_type='CUSTOMER_CASH'
            AND b.evidence_type='CUSTOMER_COLLECTION')
        OR (NEW.event_type='SERVICE_CASH'
            AND b.evidence_type='SERVICE_COLLECTION')
        OR (NEW.event_type='WHOLESALE_CASH'
            AND b.evidence_type='UPSTREAM_PAYMENT')
        OR (NEW.event_type='ROYALTY_CASH'
            AND b.evidence_type='ROYALTY_PAYMENT'))
  );
  SELECT RAISE(ABORT,'banked refund requires exact payer or payee cash orientation')
  WHERE NEW.event_type='REFUND' AND NEW.bank_evidence_id IS NOT NULL
    AND NOT EXISTS (
      SELECT 1 FROM bank_evidence b
      WHERE b.bank_evidence_id=NEW.bank_evidence_id
        AND b.evidence_type='REFUND_SETTLEMENT'
        AND b.reconciliation_status='RECONCILED'
        AND b.amount_micros=NEW.amount_micros AND b.currency=NEW.currency
        AND datetime(b.occurred_at)=datetime(NEW.occurred_at)
        AND ((b.direction_code='INFLOW'
              AND b.account_owner_party_id=NEW.payee_party_id
              AND b.counterparty_party_id=NEW.payer_party_id)
          OR (b.direction_code='OUTFLOW'
              AND b.account_owner_party_id=NEW.payer_party_id
              AND b.counterparty_party_id=NEW.payee_party_id))
    );
  SELECT RAISE(ABORT,'noncash commercial event cannot carry bank evidence')
  WHERE NEW.event_type NOT IN (
    'CUSTOMER_CASH','SERVICE_CASH','WHOLESALE_CASH','ROYALTY_CASH','REFUND'
  ) AND NEW.bank_evidence_id IS NOT NULL;
END;

CREATE TABLE attribution_rule_version (
  attribution_rule_version_id TEXT PRIMARY KEY,
  rule_stream_id TEXT NOT NULL,
  rule_sequence INTEGER NOT NULL CHECK (rule_sequence>0),
  supersedes_rule_version_id TEXT,
  policy_snapshot_id TEXT NOT NULL,
  winner_rule_code TEXT NOT NULL CHECK (winner_rule_code IN (
    'FIRST_ACCEPTED','PROVIDER_DECISION','CUSTOMER_POI'
  )),
  lookback_days INTEGER NOT NULL CHECK (lookback_days>=0),
  duplicate_window_days INTEGER NOT NULL CHECK (duplicate_window_days>=0),
  affiliate_partner_mutually_exclusive INTEGER NOT NULL CHECK (
    affiliate_partner_mutually_exclusive IN (0,1)
  ),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  evidence_id TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (effective_until IS NULL
         OR datetime(effective_from)<datetime(effective_until)),
  UNIQUE (rule_stream_id,rule_sequence),
  FOREIGN KEY (supersedes_rule_version_id)
    REFERENCES attribution_rule_version(attribution_rule_version_id),
  FOREIGN KEY (policy_snapshot_id) REFERENCES policy_snapshot(policy_snapshot_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_attribution_rule_before_insert
BEFORE INSERT ON attribution_rule_version
BEGIN
  SELECT RAISE(ABORT,'attribution rule must append exact predecessor')
  WHERE (NEW.rule_sequence=1 AND NEW.supersedes_rule_version_id IS NOT NULL)
     OR (NEW.rule_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM attribution_rule_version p
       WHERE p.attribution_rule_version_id=NEW.supersedes_rule_version_id
         AND p.rule_stream_id=NEW.rule_stream_id
         AND p.rule_sequence=NEW.rule_sequence-1
     ));
END;

CREATE TABLE attribution_decision (
  attribution_decision_id TEXT PRIMARY KEY,
  commercial_event_id TEXT NOT NULL,
  attribution_rule_version_id TEXT NOT NULL,
  registration_event_id TEXT NOT NULL,
  attribution_role TEXT NOT NULL CHECK (attribution_role IN (
    'WINNER','FIRST_TOUCH','ASSIST'
  )),
  causal_claim INTEGER NOT NULL CHECK (causal_claim=0),
  decision_evidence_id TEXT NOT NULL,
  decided_at TEXT NOT NULL CHECK (datetime(decided_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(decided_at)<=datetime(recorded_at)),
  FOREIGN KEY (commercial_event_id)
    REFERENCES commercial_event(commercial_event_id),
  FOREIGN KEY (attribution_rule_version_id)
    REFERENCES attribution_rule_version(attribution_rule_version_id),
  FOREIGN KEY (registration_event_id)
    REFERENCES lead_registration_event(registration_event_id),
  FOREIGN KEY (decision_evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE UNIQUE INDEX one_attribution_winner_per_rule
ON attribution_decision(commercial_event_id,attribution_rule_version_id)
WHERE attribution_role='WINNER';

CREATE TRIGGER validate_attribution_decision_before_insert
BEFORE INSERT ON attribution_decision
BEGIN
  SELECT RAISE(ABORT,'winner attribution requires accepted current registration')
  WHERE NEW.attribution_role='WINNER' AND NOT EXISTS (
    SELECT 1
    FROM lead_registration_event r
    JOIN commercial_event c
      ON c.commercial_event_id=NEW.commercial_event_id
    JOIN attribution_rule_version ar
      ON ar.attribution_rule_version_id=NEW.attribution_rule_version_id
    WHERE r.registration_event_id=NEW.registration_event_id
      AND r.registration_status='ACCEPTED'
      AND r.account_id=c.account_id
      AND datetime(r.occurred_at)<=datetime(c.occurred_at)
      AND julianday(c.occurred_at)-julianday(r.occurred_at)<=ar.lookback_days
      AND datetime(ar.effective_from)<=datetime(NEW.decided_at)
      AND (ar.effective_until IS NULL
           OR datetime(NEW.decided_at)<datetime(ar.effective_until))
      AND NOT EXISTS (
        SELECT 1 FROM lead_registration_event later
        WHERE later.registration_stream_id=r.registration_stream_id
          AND later.event_sequence>r.event_sequence
          AND datetime(later.recorded_at)<=datetime(NEW.decided_at)
      )
  );
END;

CREATE TABLE commission_rule_version (
  commission_rule_version_id TEXT PRIMARY KEY,
  commission_rule_stream_id TEXT NOT NULL,
  rule_sequence INTEGER NOT NULL CHECK (rule_sequence>0),
  supersedes_rule_version_id TEXT,
  policy_snapshot_id TEXT NOT NULL,
  eligible_motion_code TEXT NOT NULL,
  base_code TEXT NOT NULL CHECK (base_code IN (
    'BOOKING','INVOICED','RECOGNIZED','COLLECTED_NET'
  )),
  calculation_code TEXT NOT NULL CHECK (calculation_code IN ('RATE','FIXED')),
  rate_ppm INTEGER CHECK (rate_ppm IS NULL OR rate_ppm BETWEEN 0 AND 1000000),
  fixed_amount_micros INTEGER CHECK (
    fixed_amount_micros IS NULL OR fixed_amount_micros>=0
  ),
  net_deduction_code TEXT NOT NULL CHECK (net_deduction_code IN (
    'NONE','REFUNDS_CREDITS_TAXES','PROGRAM_DEFINED','UNKNOWN'
  )),
  locking_days INTEGER NOT NULL CHECK (locking_days>=0),
  clawback_days INTEGER NOT NULL CHECK (clawback_days>=0),
  renewal_months INTEGER CHECK (renewal_months IS NULL OR renewal_months>=0),
  tail_days INTEGER CHECK (tail_days IS NULL OR tail_days>=0),
  payout_threshold_micros INTEGER NOT NULL CHECK (payout_threshold_micros>=0),
  payout_cadence_code TEXT NOT NULL CHECK (payout_cadence_code IN (
    'MONTHLY','TWICE_MONTHLY','QUARTERLY','PROGRAM_DEFINED','UNKNOWN'
  )),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  evidence_id TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((calculation_code='RATE' AND rate_ppm IS NOT NULL
          AND fixed_amount_micros IS NULL)
      OR (calculation_code='FIXED' AND fixed_amount_micros IS NOT NULL
          AND rate_ppm IS NULL)),
  CHECK (effective_until IS NULL
         OR datetime(effective_from)<datetime(effective_until)),
  UNIQUE (commission_rule_stream_id,rule_sequence),
  FOREIGN KEY (supersedes_rule_version_id)
    REFERENCES commission_rule_version(commission_rule_version_id),
  FOREIGN KEY (policy_snapshot_id) REFERENCES policy_snapshot(policy_snapshot_id),
  FOREIGN KEY (eligible_motion_code) REFERENCES motion_dim(motion_code),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_commission_rule_before_insert
BEFORE INSERT ON commission_rule_version
BEGIN
  SELECT RAISE(ABORT,'commission rule must append exact predecessor')
  WHERE (NEW.rule_sequence=1 AND NEW.supersedes_rule_version_id IS NOT NULL)
     OR (NEW.rule_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM commission_rule_version p
       WHERE p.commission_rule_version_id=NEW.supersedes_rule_version_id
         AND p.commission_rule_stream_id=NEW.commission_rule_stream_id
         AND p.rule_sequence=NEW.rule_sequence-1
     ));
END;

CREATE TABLE termination_case (
  termination_case_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  notice_at TEXT NOT NULL CHECK (datetime(notice_at) IS NOT NULL),
  effective_at TEXT NOT NULL CHECK (datetime(effective_at) IS NOT NULL),
  initiated_by_party_id TEXT NOT NULL,
  contractual_tail_status TEXT NOT NULL CHECK (contractual_tail_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  governing_law_tail_status TEXT NOT NULL CHECK (governing_law_tail_status IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  evidence_id TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(notice_at)<=datetime(effective_at)),
  CHECK (datetime(notice_at)<=datetime(recorded_at)),
  UNIQUE (agreement_version_id),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (initiated_by_party_id) REFERENCES party(party_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE commission_event (
  commission_event_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  commission_rule_version_id TEXT NOT NULL,
  attribution_decision_id TEXT,
  event_type TEXT NOT NULL CHECK (event_type IN (
    'ESTIMATED','ACCRUED','REVERSED','PAID','FORFEITED'
  )),
  base_commercial_event_id TEXT,
  source_accrual_event_id TEXT,
  bank_evidence_id TEXT,
  eligible_base_micros INTEGER NOT NULL CHECK (eligible_base_micros>=0),
  amount_micros INTEGER NOT NULL CHECK (amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  tail_eligible INTEGER NOT NULL CHECK (tail_eligible IN (0,1)),
  reason_code TEXT NOT NULL,
  evidence_id TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((event_type='ACCRUED' AND base_commercial_event_id IS NOT NULL
          AND source_accrual_event_id IS NULL AND bank_evidence_id IS NULL)
      OR (event_type='ESTIMATED' AND source_accrual_event_id IS NULL
          AND bank_evidence_id IS NULL)
      OR (event_type IN ('REVERSED','FORFEITED')
          AND source_accrual_event_id IS NOT NULL AND bank_evidence_id IS NULL)
      OR (event_type='PAID' AND source_accrual_event_id IS NOT NULL
          AND bank_evidence_id IS NOT NULL)),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (commission_rule_version_id)
    REFERENCES commission_rule_version(commission_rule_version_id),
  FOREIGN KEY (attribution_decision_id)
    REFERENCES attribution_decision(attribution_decision_id),
  FOREIGN KEY (base_commercial_event_id)
    REFERENCES commercial_event(commercial_event_id),
  FOREIGN KEY (source_accrual_event_id)
    REFERENCES commission_event(commission_event_id),
  FOREIGN KEY (bank_evidence_id) REFERENCES bank_evidence(bank_evidence_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE UNIQUE INDEX one_commission_payout_per_bank_evidence
ON commission_event(bank_evidence_id)
WHERE bank_evidence_id IS NOT NULL;

CREATE TRIGGER validate_commission_event_before_insert
BEFORE INSERT ON commission_event
BEGIN
  SELECT RAISE(ABORT,'commission rule motion and currency must match agreement')
  WHERE NOT EXISTS (
    SELECT 1 FROM commission_rule_version r
    JOIN agreement_version a
      ON a.agreement_version_id=NEW.agreement_version_id
    WHERE r.commission_rule_version_id=NEW.commission_rule_version_id
      AND r.eligible_motion_code=a.motion_code
      AND r.currency=NEW.currency
      AND datetime(r.effective_from)<=datetime(NEW.occurred_at)
      AND (r.effective_until IS NULL
           OR datetime(NEW.occurred_at)<datetime(r.effective_until))
  );
  SELECT RAISE(ABORT,'accrual requires winning attribution for the same agreement')
  WHERE NEW.event_type='ACCRUED' AND NOT EXISTS (
    SELECT 1 FROM attribution_decision d
    JOIN commercial_event c ON c.commercial_event_id=NEW.base_commercial_event_id
    WHERE d.attribution_decision_id=NEW.attribution_decision_id
      AND d.attribution_role='WINNER'
      AND d.commercial_event_id=NEW.base_commercial_event_id
      AND c.agreement_version_id=NEW.agreement_version_id
  );
  SELECT RAISE(ABORT,'commission accrual base does not match versioned rule')
  WHERE NEW.event_type='ACCRUED' AND NOT EXISTS (
    SELECT 1 FROM commission_rule_version r
    JOIN commercial_event c
      ON c.commercial_event_id=NEW.base_commercial_event_id
    WHERE r.commission_rule_version_id=NEW.commission_rule_version_id
      AND c.amount_micros=NEW.eligible_base_micros
      AND c.currency=NEW.currency
      AND ((r.base_code='BOOKING' AND c.event_type='BOOKING')
        OR (r.base_code='INVOICED' AND c.event_type='INVOICE')
        OR (r.base_code='RECOGNIZED' AND c.event_type='RECOGNIZED_REVENUE')
        OR (r.base_code='COLLECTED_NET' AND c.event_type='CUSTOMER_CASH'
            AND EXISTS (
              SELECT 1 FROM bank_evidence b
              WHERE b.bank_evidence_id=c.bank_evidence_id
                AND b.reconciliation_status='RECONCILED'
                AND b.evidence_type='CUSTOMER_COLLECTION'
            )))
  );
  SELECT RAISE(ABORT,'commission amount does not match versioned calculation')
  WHERE NEW.event_type='ACCRUED' AND NOT EXISTS (
    SELECT 1 FROM commission_rule_version r
    WHERE r.commission_rule_version_id=NEW.commission_rule_version_id
      AND ((r.calculation_code='RATE'
            AND NEW.amount_micros=(NEW.eligible_base_micros*r.rate_ppm)/1000000)
        OR (r.calculation_code='FIXED'
            AND NEW.amount_micros=r.fixed_amount_micros))
  );
  SELECT RAISE(ABORT,'adjustment must reference a prior accrual in the same scope')
  WHERE NEW.event_type IN ('REVERSED','FORFEITED','PAID') AND NOT EXISTS (
    SELECT 1 FROM commission_event a
    WHERE a.commission_event_id=NEW.source_accrual_event_id
      AND a.event_type='ACCRUED'
      AND a.agreement_version_id=NEW.agreement_version_id
      AND a.commission_rule_version_id=NEW.commission_rule_version_id
      AND a.currency=NEW.currency
      AND datetime(a.occurred_at)<=datetime(NEW.occurred_at)
  );
  SELECT RAISE(ABORT,'cumulative reversal forfeiture and payout exceed accrual')
  WHERE NEW.event_type IN ('REVERSED','FORFEITED','PAID') AND NEW.amount_micros+coalesce((
    SELECT sum(e.amount_micros) FROM commission_event e
    WHERE e.source_accrual_event_id=NEW.source_accrual_event_id
      AND e.event_type IN ('REVERSED','FORFEITED','PAID')
  ),0)>(
    SELECT a.amount_micros FROM commission_event a
    WHERE a.commission_event_id=NEW.source_accrual_event_id
  );
  SELECT RAISE(ABORT,'paid commission requires matching reconciled bank payout')
  WHERE NEW.event_type='PAID' AND NOT EXISTS (
    SELECT 1 FROM bank_evidence b
    WHERE b.bank_evidence_id=NEW.bank_evidence_id
      AND b.evidence_type='PARTNER_PAYOUT'
      AND b.direction_code='OUTFLOW'
      AND b.reconciliation_status='RECONCILED'
      AND b.amount_micros=NEW.amount_micros
      AND b.currency=NEW.currency
      AND datetime(b.occurred_at)=datetime(NEW.occurred_at)
      AND EXISTS (
        SELECT 1 FROM agreement_version a
        WHERE a.agreement_version_id=NEW.agreement_version_id
          AND b.account_owner_party_id=a.principal_party_id
          AND b.counterparty_party_id=a.partner_party_id
      )
  );
  SELECT RAISE(ABORT,'post-termination commission requires reviewed in-window tail')
  WHERE EXISTS (
    SELECT 1 FROM termination_case t
    WHERE t.agreement_version_id=NEW.agreement_version_id
      AND datetime(NEW.occurred_at)>datetime(t.effective_at)
  ) AND (
    NEW.tail_eligible<>1 OR NOT EXISTS (
      SELECT 1 FROM termination_case t
      JOIN commission_rule_version r
        ON r.commission_rule_version_id=NEW.commission_rule_version_id
      WHERE t.agreement_version_id=NEW.agreement_version_id
        AND t.contractual_tail_status='PASS'
        AND t.governing_law_tail_status IN ('PASS','NA')
        AND r.tail_days IS NOT NULL
        AND datetime(NEW.occurred_at)<=datetime(t.effective_at,
          '+' || r.tail_days || ' days')
    )
  );
END;

CREATE TABLE reseller_order (
  reseller_order_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  downstream_booking_event_id TEXT NOT NULL,
  downstream_cash_event_id TEXT,
  upstream_obligation_event_id TEXT NOT NULL,
  upstream_cash_event_id TEXT,
  entitlement_status TEXT NOT NULL CHECK (entitlement_status IN (
    'PENDING','ACTIVE','SUSPENDED','TRANSFERRED','EXPIRED'
  )),
  customer_credit_risk_owner_party_id TEXT NOT NULL,
  refund_pass_through_status TEXT NOT NULL CHECK (
    refund_pass_through_status IN ('PASS','FAIL','UNKNOWN','NA')
  ),
  evidence_id TEXT NOT NULL,
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(created_at)<=datetime(recorded_at)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (account_id) REFERENCES commercial_account(account_id),
  FOREIGN KEY (downstream_booking_event_id)
    REFERENCES commercial_event(commercial_event_id),
  FOREIGN KEY (downstream_cash_event_id)
    REFERENCES commercial_event(commercial_event_id),
  FOREIGN KEY (upstream_obligation_event_id)
    REFERENCES commercial_event(commercial_event_id),
  FOREIGN KEY (upstream_cash_event_id)
    REFERENCES commercial_event(commercial_event_id),
  FOREIGN KEY (customer_credit_risk_owner_party_id) REFERENCES party(party_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_reseller_order_before_insert
BEFORE INSERT ON reseller_order
BEGIN
  SELECT RAISE(ABORT,'reseller order requires reseller agreement and exact events')
  WHERE NOT EXISTS (
    SELECT 1 FROM agreement_version a
    JOIN commercial_event b
      ON b.commercial_event_id=NEW.downstream_booking_event_id
    JOIN commercial_event u
      ON u.commercial_event_id=NEW.upstream_obligation_event_id
    WHERE a.agreement_version_id=NEW.agreement_version_id
      AND a.motion_code='RESELLER'
      AND b.agreement_version_id=a.agreement_version_id
      AND b.account_id=NEW.account_id AND b.event_type='BOOKING'
      AND u.agreement_version_id=a.agreement_version_id
      AND u.account_id=NEW.account_id AND u.event_type='WHOLESALE_OBLIGATION'
  );
  SELECT RAISE(ABORT,'reseller downstream cash link must be customer cash')
  WHERE NEW.downstream_cash_event_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM commercial_event c
    WHERE c.commercial_event_id=NEW.downstream_cash_event_id
      AND c.agreement_version_id=NEW.agreement_version_id
      AND c.account_id=NEW.account_id AND c.event_type='CUSTOMER_CASH'
  );
  SELECT RAISE(ABORT,'reseller upstream cash link must be wholesale cash')
  WHERE NEW.upstream_cash_event_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM commercial_event c
    WHERE c.commercial_event_id=NEW.upstream_cash_event_id
      AND c.agreement_version_id=NEW.agreement_version_id
      AND c.account_id=NEW.account_id AND c.event_type='WHOLESALE_CASH'
  );
END;

CREATE TABLE cost_event (
  cost_event_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  account_id TEXT,
  work_order_id TEXT,
  cost_bearer_party_id TEXT NOT NULL,
  cost_class TEXT NOT NULL CHECK (cost_class IN (
    'PARTNER_RECRUITING','ENABLEMENT','LEAD_REVIEW','DEAL_RESCUE',
    'CONTENT_OR_TOOL','DELIVERY_CASH','SUPPORT_ESCALATION',
    'COMMISSION_DISPUTE','COLLECTIONS','BAD_DEBT','FINANCING','OFFBOARDING',
    'FOUNDER_TIME'
  )),
  cash_cost_micros INTEGER,
  founder_minutes INTEGER,
  founder_time_component TEXT CHECK (founder_time_component IS NULL OR
    founder_time_component IN (
      'RESIDUAL_EXECUTION','SOURCING_DILIGENCE_CONTRACTING',
      'BRIEFING_COORDINATION_REVIEW','REWORK','RESCUE','ADMIN_OFFBOARDING'
    )
  ),
  source_work_event_id TEXT UNIQUE,
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  evidence_id TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((cost_class='FOUNDER_TIME' AND cash_cost_micros IS NULL
          AND founder_minutes IS NOT NULL AND founder_minutes>0
          AND founder_time_component IS NOT NULL)
      OR (cost_class<>'FOUNDER_TIME' AND cash_cost_micros IS NOT NULL
          AND cash_cost_micros>=0 AND founder_minutes IS NULL
          AND founder_time_component IS NULL AND source_work_event_id IS NULL)),
  CHECK (source_work_event_id IS NULL OR cost_class='FOUNDER_TIME'),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (account_id) REFERENCES commercial_account(account_id),
  FOREIGN KEY (work_order_id) REFERENCES work_order(work_order_id),
  FOREIGN KEY (cost_bearer_party_id) REFERENCES party(party_id),
  FOREIGN KEY (source_work_event_id) REFERENCES work_event(work_event_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_cost_event_before_insert
BEFORE INSERT ON cost_event
BEGIN
  SELECT RAISE(ABORT,'founder time must be borne by the agreement principal')
  WHERE NEW.cost_class='FOUNDER_TIME'
    AND EXISTS (
      SELECT 1 FROM agreement_version a
      WHERE a.agreement_version_id=NEW.agreement_version_id
    )
    AND NOT EXISTS (
    SELECT 1 FROM agreement_version a
    WHERE a.agreement_version_id=NEW.agreement_version_id
      AND NEW.cost_bearer_party_id=a.principal_party_id
      AND NEW.currency=a.currency
  );
  SELECT RAISE(ABORT,'work cash belongs only in work_event; cost_event is overhead')
  WHERE NEW.cost_class<>'FOUNDER_TIME' AND NEW.work_order_id IS NOT NULL;
  SELECT RAISE(ABORT,'founder work time must map one-to-one to its operational event')
  WHERE NEW.source_work_event_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM work_event w
    WHERE w.work_event_id=NEW.source_work_event_id
      AND w.work_order_id=NEW.work_order_id
      AND w.actor_role_code='FOUNDER'
      AND w.minutes=NEW.founder_minutes
      AND datetime(w.occurred_at)=datetime(NEW.occurred_at)
      AND ((w.work_class_code IN ('STANDARD_DELIVERY','SUPPORT')
            AND NEW.founder_time_component='RESIDUAL_EXECUTION')
        OR (w.work_class_code IN ('PLANNED_REVIEW','DELIVERY_QA')
            AND NEW.founder_time_component='BRIEFING_COORDINATION_REVIEW')
        OR (w.work_class_code='PARTNER_REWORK'
            AND NEW.founder_time_component='REWORK')
        OR (w.work_class_code IN ('FOUNDER_RESCUE','DEAL_RESCUE')
            AND NEW.founder_time_component='RESCUE')
        OR (w.work_class_code='OFFBOARDING'
            AND NEW.founder_time_component='ADMIN_OFFBOARDING'))
  );
  SELECT RAISE(ABORT,'work-linked founder components require a source work event')
  WHERE NEW.cost_class='FOUNDER_TIME'
    AND NEW.founder_time_component IN (
      'RESIDUAL_EXECUTION','BRIEFING_COORDINATION_REVIEW','REWORK','RESCUE'
    ) AND NEW.work_order_id IS NOT NULL AND NEW.source_work_event_id IS NULL;
END;

CREATE TABLE partner_experiment (
  experiment_id TEXT PRIMARY KEY,
  motion_code TEXT NOT NULL,
  hypothesis_ref_hash TEXT NOT NULL CHECK (
    length(hypothesis_ref_hash)=64
    AND hypothesis_ref_hash=lower(hypothesis_ref_hash)
    AND hypothesis_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  assignment_unit_code TEXT NOT NULL CHECK (assignment_unit_code IN (
    'ACCOUNT_CLUSTER','PARTNER','TERRITORY'
  )),
  eligibility_rule_version TEXT NOT NULL,
  assignment_seed_ref_hash TEXT NOT NULL CHECK (
    length(assignment_seed_ref_hash)=64
    AND assignment_seed_ref_hash=lower(assignment_seed_ref_hash)
    AND assignment_seed_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  primary_outcome_code TEXT NOT NULL CHECK (primary_outcome_code IN (
    'BANK_CONTRIBUTION','ACCEPTED_OUTCOME','RENEWAL'
  )),
  starts_at TEXT NOT NULL CHECK (datetime(starts_at) IS NOT NULL),
  exposure_cutoff_at TEXT NOT NULL CHECK (datetime(exposure_cutoff_at) IS NOT NULL),
  maturity_at TEXT NOT NULL CHECK (datetime(maturity_at) IS NOT NULL),
  snapshot_as_of TEXT NOT NULL CHECK (datetime(snapshot_as_of) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(starts_at)<datetime(exposure_cutoff_at)),
  CHECK (datetime(exposure_cutoff_at)<=datetime(maturity_at)),
  CHECK (datetime(maturity_at)<=datetime(snapshot_as_of)),
  FOREIGN KEY (motion_code) REFERENCES motion_dim(motion_code),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE experiment_assignment (
  assignment_id TEXT PRIMARY KEY,
  experiment_id TEXT NOT NULL,
  assignment_unit_ref_hash TEXT NOT NULL CHECK (
    length(assignment_unit_ref_hash)=64
    AND assignment_unit_ref_hash=lower(assignment_unit_ref_hash)
    AND assignment_unit_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  arm_code TEXT NOT NULL CHECK (arm_code IN ('TREATMENT','CONTROL')),
  assigned_at TEXT NOT NULL CHECK (datetime(assigned_at) IS NOT NULL),
  first_exposure_at TEXT CHECK (
    first_exposure_at IS NULL OR datetime(first_exposure_at) IS NOT NULL
  ),
  contamination_status TEXT NOT NULL CHECK (contamination_status IN (
    'CLEAN','CONTAMINATED','UNKNOWN'
  )),
  assignment_evidence_id TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (experiment_id,assignment_unit_ref_hash),
  CHECK ((arm_code='CONTROL' AND first_exposure_at IS NULL)
      OR arm_code='TREATMENT'),
  CHECK (first_exposure_at IS NULL
      OR datetime(assigned_at)<=datetime(first_exposure_at)),
  FOREIGN KEY (experiment_id) REFERENCES partner_experiment(experiment_id),
  FOREIGN KEY (assignment_evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_experiment_assignment_before_insert
BEFORE INSERT ON experiment_assignment
BEGIN
  SELECT RAISE(ABORT,'experiment assignment must precede experiment start and exposure')
  WHERE NOT EXISTS (
    SELECT 1 FROM partner_experiment e
    WHERE e.experiment_id=NEW.experiment_id
      AND datetime(NEW.assigned_at)<datetime(e.starts_at)
      AND (NEW.first_exposure_at IS NULL
           OR datetime(NEW.first_exposure_at)>=datetime(e.starts_at))
  );
END;

CREATE TABLE experiment_outcome (
  experiment_outcome_id TEXT PRIMARY KEY,
  assignment_id TEXT NOT NULL UNIQUE,
  maturity_state TEXT NOT NULL CHECK (maturity_state IN (
    'MEASURED_VALUE','MEASURED_ZERO','IMMATURE','UNKNOWN'
  )),
  outcome_value_micros INTEGER,
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  evidence_id TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((maturity_state='MEASURED_VALUE' AND outcome_value_micros>0
          AND evidence_id IS NOT NULL)
      OR (maturity_state='MEASURED_ZERO' AND outcome_value_micros=0
          AND evidence_id IS NOT NULL)
      OR (maturity_state IN ('IMMATURE','UNKNOWN')
          AND outcome_value_micros IS NULL)),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  FOREIGN KEY (assignment_id) REFERENCES experiment_assignment(assignment_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_experiment_outcome_before_insert
BEFORE INSERT ON experiment_outcome
BEGIN
  SELECT RAISE(ABORT,'measured experiment outcome requires maturity')
  WHERE NEW.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO') AND NOT EXISTS (
    SELECT 1 FROM experiment_assignment a
    JOIN partner_experiment e ON e.experiment_id=a.experiment_id
    WHERE a.assignment_id=NEW.assignment_id
      AND datetime(NEW.occurred_at)>=datetime(e.maturity_at)
      AND datetime(NEW.recorded_at)<=datetime(e.snapshot_as_of)
  );
END;

CREATE TABLE handoff_item_event (
  handoff_item_event_id TEXT PRIMARY KEY,
  handoff_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>0),
  supersedes_handoff_item_event_id TEXT,
  termination_case_id TEXT NOT NULL,
  item_code TEXT NOT NULL,
  item_status TEXT NOT NULL CHECK (item_status IN ('OPEN','PASS','UNKNOWN','NA')),
  owner_party_id TEXT,
  evidence_id TEXT,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((item_status='PASS' AND owner_party_id IS NOT NULL
          AND evidence_id IS NOT NULL)
      OR (item_status='NA' AND evidence_id IS NOT NULL)
      OR item_status IN ('OPEN','UNKNOWN')),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  UNIQUE (handoff_stream_id,event_sequence),
  UNIQUE (termination_case_id,item_code,event_sequence),
  FOREIGN KEY (supersedes_handoff_item_event_id)
    REFERENCES handoff_item_event(handoff_item_event_id),
  FOREIGN KEY (termination_case_id)
    REFERENCES termination_case(termination_case_id),
  FOREIGN KEY (item_code) REFERENCES handoff_item_dim(item_code),
  FOREIGN KEY (owner_party_id) REFERENCES party(party_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_handoff_item_event_before_insert
BEFORE INSERT ON handoff_item_event
BEGIN
  SELECT RAISE(ABORT,'handoff item event must append exact predecessor')
  WHERE (NEW.event_sequence=1 AND NEW.supersedes_handoff_item_event_id IS NOT NULL)
     OR (NEW.event_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM handoff_item_event p
       WHERE p.handoff_item_event_id=NEW.supersedes_handoff_item_event_id
         AND p.handoff_stream_id=NEW.handoff_stream_id
         AND p.event_sequence=NEW.event_sequence-1
         AND p.termination_case_id=NEW.termination_case_id
         AND p.item_code=NEW.item_code
     ));
END;

CREATE TABLE termination_event (
  termination_event_id TEXT PRIMARY KEY,
  termination_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>0),
  supersedes_termination_event_id TEXT,
  termination_case_id TEXT NOT NULL,
  state_code TEXT NOT NULL CHECK (state_code IN (
    'NOTICE_RECORDED','TERMINATED','HANDOFF_CLOSED'
  )),
  evidence_id TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  UNIQUE (termination_stream_id,event_sequence),
  FOREIGN KEY (supersedes_termination_event_id)
    REFERENCES termination_event(termination_event_id),
  FOREIGN KEY (termination_case_id)
    REFERENCES termination_case(termination_case_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_termination_event_before_insert
BEFORE INSERT ON termination_event
BEGIN
  SELECT RAISE(ABORT,'termination event must append exact predecessor')
  WHERE (NEW.event_sequence=1 AND NEW.supersedes_termination_event_id IS NOT NULL)
     OR (NEW.event_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM termination_event p
       WHERE p.termination_event_id=NEW.supersedes_termination_event_id
         AND p.termination_stream_id=NEW.termination_stream_id
         AND p.event_sequence=NEW.event_sequence-1
         AND p.termination_case_id=NEW.termination_case_id
     ));
  SELECT RAISE(ABORT,'termination effective event cannot predate effective date')
  WHERE NEW.state_code IN ('TERMINATED','HANDOFF_CLOSED') AND NOT EXISTS (
    SELECT 1 FROM termination_case t
    WHERE t.termination_case_id=NEW.termination_case_id
      AND datetime(NEW.occurred_at)>=datetime(t.effective_at)
  );
  SELECT RAISE(ABORT,'offboard closure blocked by open scoped access')
  WHERE NEW.state_code='HANDOFF_CLOSED' AND EXISTS (
    SELECT 1 FROM access_event a
    JOIN termination_case t
      ON t.agreement_version_id=a.agreement_version_id
    WHERE t.termination_case_id=NEW.termination_case_id
      AND a.event_type='GRANTED'
      AND datetime(a.effective_at)<=datetime(NEW.occurred_at)
      AND (a.expires_at IS NULL OR datetime(a.expires_at)>datetime(NEW.occurred_at))
      AND NOT EXISTS (
        SELECT 1 FROM access_event later
        WHERE later.access_stream_id=a.access_stream_id
          AND later.event_sequence>a.event_sequence
          AND later.event_type IN ('REVOKED','EXPIRED')
          AND datetime(later.effective_at)<=datetime(NEW.occurred_at)
      )
  );
  SELECT RAISE(ABORT,'offboard closure requires every handoff item PASS or evidenced NA')
  WHERE NEW.state_code='HANDOFF_CLOSED' AND EXISTS (
    SELECT 1 FROM handoff_item_dim d
    WHERE d.required_for_all=1 AND NOT EXISTS (
      SELECT 1 FROM handoff_item_event h
      WHERE h.termination_case_id=NEW.termination_case_id
        AND h.item_code=d.item_code
        AND h.item_status IN ('PASS','NA')
        AND NOT EXISTS (
          SELECT 1 FROM handoff_item_event later
          WHERE later.handoff_stream_id=h.handoff_stream_id
            AND later.event_sequence>h.event_sequence
            AND datetime(later.recorded_at)<=datetime(NEW.recorded_at)
        )
    )
  );
END;

CREATE TABLE decision_snapshot (
  decision_snapshot_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  decision_sequence INTEGER NOT NULL CHECK (decision_sequence>0),
  supersedes_decision_snapshot_id TEXT,
  decision_code TEXT NOT NULL CHECK (decision_code IN (
    'LIVE','PILOT','MAINTAIN','PAUSE','STOP','OFFBOARD'
  )),
  action_code TEXT NOT NULL CHECK (action_code IN (
    'EXPAND','CHANGE','PAUSE','STOP','INCONCLUSIVE'
  )),
  evidence_cutoff_at TEXT NOT NULL CHECK (datetime(evidence_cutoff_at) IS NOT NULL),
  snapshot_as_of TEXT NOT NULL CHECK (datetime(snapshot_as_of) IS NOT NULL),
  legal_state TEXT NOT NULL CHECK (legal_state IN ('PASS','FAIL','UNKNOWN','NA')),
  responsibility_state TEXT NOT NULL CHECK (
    responsibility_state IN ('PASS','FAIL','UNKNOWN','NA')
  ),
  authority_state TEXT NOT NULL CHECK (authority_state IN ('PASS','FAIL','UNKNOWN','NA')),
  access_state TEXT NOT NULL CHECK (access_state IN ('PASS','FAIL','UNKNOWN','NA')),
  quality_state TEXT NOT NULL CHECK (quality_state IN ('PASS','FAIL','UNKNOWN','NA')),
  cash_state TEXT NOT NULL CHECK (cash_state IN ('PASS','FAIL','UNKNOWN','NA')),
  incrementality_state TEXT NOT NULL CHECK (
    incrementality_state IN ('PASS','FAIL','INCONCLUSIVE','UNKNOWN','NA')
  ),
  termination_state TEXT NOT NULL CHECK (
    termination_state IN ('PASS','FAIL','UNKNOWN','NA')
  ),
  decision_evidence_id TEXT NOT NULL,
  decided_by_role TEXT NOT NULL,
  decided_at TEXT NOT NULL CHECK (datetime(decided_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(evidence_cutoff_at)<=datetime(snapshot_as_of)),
  CHECK (datetime(snapshot_as_of)<=datetime(decided_at)),
  CHECK (datetime(decided_at)<=datetime(recorded_at)),
  UNIQUE (agreement_version_id,decision_sequence),
  FOREIGN KEY (supersedes_decision_snapshot_id)
    REFERENCES decision_snapshot(decision_snapshot_id),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (decision_evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TRIGGER validate_decision_snapshot_before_insert
BEFORE INSERT ON decision_snapshot
BEGIN
  SELECT RAISE(ABORT,'decision snapshot must append exact predecessor')
  WHERE (NEW.decision_sequence=1 AND NEW.supersedes_decision_snapshot_id IS NOT NULL)
     OR (NEW.decision_sequence>1 AND NOT EXISTS (
       SELECT 1 FROM decision_snapshot p
       WHERE p.decision_snapshot_id=NEW.supersedes_decision_snapshot_id
         AND p.agreement_version_id=NEW.agreement_version_id
         AND p.decision_sequence=NEW.decision_sequence-1
     ));
  SELECT RAISE(ABORT,'LIVE snapshot requires all operating gates PASS or evidenced NA')
  WHERE NEW.decision_code='LIVE' AND (
    NEW.legal_state<>'PASS'
    OR NEW.responsibility_state<>'PASS'
    OR NEW.authority_state<>'PASS'
    OR NEW.access_state NOT IN ('PASS','NA')
    OR NEW.quality_state NOT IN ('PASS','NA')
    OR NEW.cash_state NOT IN ('PASS','NA')
    OR NEW.termination_state NOT IN ('PASS','NA')
    OR NEW.incrementality_state IN ('FAIL','UNKNOWN')
  );
  SELECT RAISE(ABORT,'LIVE snapshot requires LIVE agreement and current policy where applicable')
  WHERE NEW.decision_code='LIVE' AND NOT EXISTS (
    SELECT 1 FROM agreement_version a
    LEFT JOIN policy_snapshot p ON p.policy_snapshot_id=a.policy_snapshot_id
    WHERE a.agreement_version_id=NEW.agreement_version_id
      AND a.commercial_status='LIVE'
      AND a.legal_review_status='PASS'
      AND (a.policy_snapshot_id IS NULL OR (
        p.snapshot_status='CURRENT'
        AND datetime(p.effective_from)<=datetime(NEW.snapshot_as_of)
        AND (p.effective_until IS NULL
             OR datetime(NEW.snapshot_as_of)<datetime(p.effective_until))
      ))
  );
  SELECT RAISE(ABORT,'LIVE snapshot requires complete explicit responsibility matrix')
  WHERE NEW.decision_code='LIVE' AND EXISTS (
    SELECT 1 FROM agreement_version a
    JOIN motion_responsibility_requirement r ON r.motion_code=a.motion_code
    WHERE a.agreement_version_id=NEW.agreement_version_id
      AND NOT EXISTS (
        SELECT 1 FROM responsibility_assignment x
        WHERE x.agreement_version_id=a.agreement_version_id
          AND x.scope_code='GLOBAL'
          AND x.responsibility_code=r.responsibility_code
          AND (x.disposition='CONFIRMED'
               OR (x.disposition='NA' AND r.allow_na=1))
          AND datetime(x.effective_from)<=datetime(NEW.snapshot_as_of)
          AND (x.effective_until IS NULL
               OR datetime(NEW.snapshot_as_of)<datetime(x.effective_until))
      )
  );
  SELECT RAISE(ABORT,'LIVE snapshot requires motion-specific authority grants')
  WHERE NEW.decision_code='LIVE' AND EXISTS (
    SELECT 1 FROM agreement_version a
    JOIN motion_authority_requirement r ON r.motion_code=a.motion_code
    WHERE a.agreement_version_id=NEW.agreement_version_id
      AND NOT EXISTS (
        SELECT 1 FROM authority_grant g
        WHERE g.agreement_version_id=a.agreement_version_id
          AND g.authority_code=r.authority_code
          AND g.scope_code='GLOBAL'
          AND g.territory_code IN (a.territory_code,'ALL')
          AND ((r.requirement_code='MUST_GRANT' AND g.disposition='GRANTED')
            OR (r.requirement_code='MUST_DENY' AND g.disposition='DENIED')
            OR (r.requirement_code='MUST_DISPOSE'
                AND g.disposition IN ('GRANTED','DENIED','NA')))
          AND datetime(g.effective_from)<=datetime(NEW.snapshot_as_of)
          AND (g.effective_until IS NULL
               OR datetime(NEW.snapshot_as_of)<datetime(g.effective_until))
      )
  );
  SELECT RAISE(ABORT,'PAUSE snapshot requires an explicit unknown or paused agreement')
  WHERE NEW.decision_code='PAUSE'
    AND NEW.legal_state<>'UNKNOWN'
    AND NEW.responsibility_state<>'UNKNOWN'
    AND NEW.authority_state<>'UNKNOWN'
    AND NEW.access_state<>'UNKNOWN'
    AND NEW.quality_state<>'UNKNOWN'
    AND NEW.cash_state<>'UNKNOWN'
    AND NEW.incrementality_state<>'UNKNOWN'
    AND NEW.termination_state<>'UNKNOWN'
    AND NOT EXISTS (
      SELECT 1 FROM agreement_version a
      WHERE a.agreement_version_id=NEW.agreement_version_id
        AND a.commercial_status='PAUSE'
    );
  SELECT RAISE(ABORT,'operating state and action are contradictory')
  WHERE (NEW.decision_code='PAUSE'
          AND NEW.action_code NOT IN ('PAUSE','CHANGE','INCONCLUSIVE'))
     OR (NEW.decision_code IN ('STOP','OFFBOARD') AND NEW.action_code<>'STOP')
     OR (NEW.action_code='EXPAND' AND NEW.decision_code NOT IN ('LIVE','MAINTAIN'))
     OR (NEW.action_code='EXPAND' AND (
       NEW.legal_state<>'PASS' OR NEW.responsibility_state<>'PASS'
       OR NEW.authority_state<>'PASS'
       OR NEW.access_state NOT IN ('PASS','NA')
       OR NEW.quality_state NOT IN ('PASS','NA')
       OR NEW.cash_state NOT IN ('PASS','NA')
       OR NEW.incrementality_state<>'PASS'
       OR NEW.termination_state NOT IN ('PASS','NA')
     ));
END;

CREATE TABLE decision_snapshot_metric (
  decision_snapshot_id TEXT NOT NULL,
  metric_code TEXT NOT NULL CHECK (metric_code IN (
    'BANK_REVENUE_MICROS','BANK_COST_MICROS','FOUNDER_MINUTES',
    'FULLY_LOADED_CONTRIBUTION_MICROS','FIRST_PASS_RATE_PPM',
    'RESCUE_RATE_PPM','INCREMENTAL_VALUE_MICROS','OPEN_HANDOFF_ITEMS'
  )),
  metric_state TEXT NOT NULL CHECK (metric_state IN (
    'MEASURED','MEASURED_ZERO','UNKNOWN','NA','INCONCLUSIVE'
  )),
  metric_value INTEGER,
  evidence_id TEXT,
  PRIMARY KEY (decision_snapshot_id,metric_code),
  CHECK ((metric_state IN ('MEASURED','MEASURED_ZERO')
          AND metric_value IS NOT NULL AND evidence_id IS NOT NULL)
      OR (metric_state IN ('UNKNOWN','NA','INCONCLUSIVE')
          AND metric_value IS NULL)),
  CHECK (metric_state<>'MEASURED_ZERO' OR metric_value=0),
  FOREIGN KEY (decision_snapshot_id)
    REFERENCES decision_snapshot(decision_snapshot_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE delegation_baseline (
  delegation_baseline_id TEXT PRIMARY KEY,
  agreement_version_id TEXT NOT NULL,
  period_code TEXT NOT NULL,
  baseline_founder_minutes INTEGER NOT NULL CHECK (baseline_founder_minutes>=0),
  baseline_scope_ref_hash TEXT NOT NULL CHECK (
    length(baseline_scope_ref_hash)=64
    AND baseline_scope_ref_hash=lower(baseline_scope_ref_hash)
    AND baseline_scope_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  evidence_id TEXT NOT NULL,
  cutoff_at TEXT NOT NULL CHECK (datetime(cutoff_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (agreement_version_id,period_code),
  FOREIGN KEY (agreement_version_id)
    REFERENCES agreement_version(agreement_version_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

-- -----------------------------------------------------------------------------
-- Current-state and decision output views
-- -----------------------------------------------------------------------------

CREATE VIEW v_current_agreement AS
SELECT a.*
FROM agreement_version a
WHERE NOT EXISTS (
  SELECT 1 FROM agreement_version later
  WHERE later.agreement_stream_id=a.agreement_stream_id
    AND later.version_sequence>a.version_sequence
);

CREATE VIEW v_current_access AS
WITH ranked AS (
  SELECT a.*,
         row_number() OVER (
           PARTITION BY access_stream_id
           ORDER BY event_sequence DESC
         ) AS rn
  FROM access_event a
)
SELECT access_event_id,access_stream_id,agreement_version_id,account_id,
       grantee_party_id,system_scope_ref_hash,data_class_code,privilege_code,
       event_type,effective_at,expires_at,
       CASE
         WHEN event_type<>'GRANTED' THEN 'CLOSED'
         WHEN expires_at IS NOT NULL AND datetime(expires_at)<=datetime(
           (SELECT reference_time FROM model_setting WHERE singleton_id=1)
         ) THEN 'EXPIRED'
         ELSE 'OPEN'
       END AS access_state
FROM ranked WHERE rn=1;

CREATE VIEW v_current_lead_registration AS
WITH ranked AS (
  SELECT r.*,
         row_number() OVER (
           PARTITION BY registration_stream_id
           ORDER BY event_sequence DESC
         ) AS rn
  FROM lead_registration_event r
)
SELECT * FROM ranked WHERE rn=1;

CREATE VIEW v_current_acceptance AS
WITH ranked AS (
  SELECT a.*,
         row_number() OVER (
           PARTITION BY acceptance_stream_id
           ORDER BY event_sequence DESC
         ) AS rn
  FROM acceptance_event a
)
SELECT * FROM ranked WHERE rn=1;

CREATE VIEW v_current_handoff_item AS
WITH ranked AS (
  SELECT h.*,
         row_number() OVER (
           PARTITION BY handoff_stream_id
           ORDER BY event_sequence DESC
         ) AS rn
  FROM handoff_item_event h
)
SELECT * FROM ranked WHERE rn=1;

CREATE VIEW v_responsibility_matrix AS
SELECT a.agreement_version_id,a.motion_code,r.scope_code,
       r.responsibility_code,r.disposition,r.owner_party_id,
       r.effective_from,r.effective_until
FROM agreement_version a
JOIN responsibility_assignment r USING (agreement_version_id);

CREATE VIEW v_agreement_readiness AS
WITH responsibility_gap AS (
  SELECT a.agreement_version_id,count(*) AS gap_count
  FROM agreement_version a
  JOIN motion_responsibility_requirement r ON r.motion_code=a.motion_code
  WHERE NOT EXISTS (
    SELECT 1 FROM responsibility_assignment x
    WHERE x.agreement_version_id=a.agreement_version_id
      AND x.scope_code='GLOBAL'
      AND x.responsibility_code=r.responsibility_code
      AND (x.disposition='CONFIRMED' OR (x.disposition='NA' AND r.allow_na=1))
  )
  GROUP BY a.agreement_version_id
), authority_gap AS (
  SELECT a.agreement_version_id,count(*) AS gap_count
  FROM agreement_version a
  JOIN motion_authority_requirement r ON r.motion_code=a.motion_code
  WHERE NOT EXISTS (
    SELECT 1 FROM authority_grant g
    WHERE g.agreement_version_id=a.agreement_version_id
      AND g.authority_code=r.authority_code
      AND g.scope_code='GLOBAL'
      AND g.territory_code IN (a.territory_code,'ALL')
      AND ((r.requirement_code='MUST_GRANT' AND g.disposition='GRANTED')
        OR (r.requirement_code='MUST_DENY' AND g.disposition='DENIED')
        OR (r.requirement_code='MUST_DISPOSE'
            AND g.disposition IN ('GRANTED','DENIED','NA')))
  )
  GROUP BY a.agreement_version_id
)
SELECT a.agreement_version_id,a.motion_code,a.commercial_status,
       coalesce(r.gap_count,0) AS responsibility_gap_count,
       coalesce(g.gap_count,0) AS authority_gap_count,
       CASE
         WHEN a.legal_review_status='FAIL'
           OR a.authority_review_status='FAIL'
           OR a.privacy_review_status='FAIL'
           OR a.security_review_status='FAIL'
           OR a.payment_review_status='FAIL'
           OR a.ip_license_review_status='FAIL'
           OR a.termination_review_status='FAIL' THEN 'STOP'
         WHEN a.legal_review_status='UNKNOWN'
           OR a.authority_review_status='UNKNOWN'
           OR a.privacy_review_status='UNKNOWN'
           OR a.security_review_status='UNKNOWN'
           OR a.payment_review_status='UNKNOWN'
           OR a.ip_license_review_status='UNKNOWN'
           OR a.termination_review_status='UNKNOWN'
           OR coalesce(r.gap_count,0)>0 OR coalesce(g.gap_count,0)>0 THEN 'PAUSE'
         WHEN a.commercial_status='LIVE' THEN 'READY_LIVE'
         WHEN a.commercial_status='PILOT' THEN 'READY_PILOT'
         ELSE a.commercial_status
       END AS derived_gate
FROM v_current_agreement a
LEFT JOIN responsibility_gap r USING (agreement_version_id)
LEFT JOIN authority_gap g USING (agreement_version_id);

CREATE VIEW v_work_quality_economics AS
SELECT w.work_order_id,w.agreement_version_id,w.account_id,
       a.outcome_code,
       coalesce(sum(CASE WHEN e.work_class_code='STANDARD_DELIVERY'
                         THEN e.minutes ELSE 0 END),0) AS standard_minutes,
       coalesce(sum(CASE WHEN e.work_class_code='PARTNER_REWORK'
                         THEN e.minutes ELSE 0 END),0) AS partner_rework_minutes,
       coalesce(sum(CASE WHEN e.work_class_code='FOUNDER_RESCUE'
                         THEN e.minutes ELSE 0 END),0) AS operational_rescue_minutes,
       coalesce(sum(e.cash_cost_micros),0) AS work_cash_cost_micros,
       coalesce((SELECT sum(c.founder_minutes) FROM cost_event c
                 WHERE c.work_order_id=w.work_order_id
                   AND c.cost_class='FOUNDER_TIME'),0)
         AS total_founder_delegated_work_minutes,
       CASE WHEN a.outcome_code='ACCEPTED_FIRST_PASS' THEN 1 ELSE 0 END
         AS accepted_first_pass,
       CASE WHEN a.outcome_code='ACCEPTED_AFTER_FOUNDER_RESCUE' THEN 1 ELSE 0 END
         AS accepted_after_founder_rescue
FROM work_order w
LEFT JOIN work_event e USING (work_order_id)
LEFT JOIN v_current_acceptance a USING (work_order_id)
GROUP BY w.work_order_id,w.agreement_version_id,w.account_id,a.outcome_code;

CREATE VIEW v_commission_ledger AS
SELECT a.commission_event_id,a.agreement_version_id,a.event_type,
       a.source_accrual_event_id,a.eligible_base_micros,a.amount_micros,
       a.currency,a.tail_eligible,a.occurred_at,
       CASE WHEN a.event_type='ACCRUED' THEN a.amount_micros ELSE 0 END
         AS accrued_micros,
       CASE WHEN a.event_type IN ('REVERSED','FORFEITED') THEN a.amount_micros ELSE 0 END
         AS reversed_or_forfeited_micros,
       CASE WHEN a.event_type='PAID' THEN a.amount_micros ELSE 0 END
         AS paid_micros
FROM commission_event a;

CREATE VIEW v_commission_settlement AS
WITH base_deduction AS (
  SELECT adjusts_commercial_event_id AS base_commercial_event_id,
         sum(amount_micros) AS deduction_micros
  FROM commercial_event
  WHERE event_type IN ('REFUND','CHARGEBACK','DOWNGRADE')
  GROUP BY adjusts_commercial_event_id
), commission_adjustment AS (
  SELECT source_accrual_event_id,
         sum(CASE WHEN event_type IN ('REVERSED','FORFEITED')
                  THEN amount_micros ELSE 0 END) AS reversed_or_forfeited_micros,
         sum(CASE WHEN event_type='PAID' THEN amount_micros ELSE 0 END)
           AS paid_micros
  FROM commission_event
  WHERE source_accrual_event_id IS NOT NULL
  GROUP BY source_accrual_event_id
)
SELECT a.commission_event_id AS accrual_event_id,a.agreement_version_id,
       a.base_commercial_event_id,a.eligible_base_micros AS initial_eligible_base_micros,
       coalesce(d.deduction_micros,0) AS exact_base_deduction_micros,
       max(a.eligible_base_micros-coalesce(d.deduction_micros,0),0)
         AS settled_net_base_micros,
       CASE WHEN r.calculation_code='RATE'
         THEN max(a.eligible_base_micros-coalesce(d.deduction_micros,0),0)
              *r.rate_ppm/1000000
         ELSE r.fixed_amount_micros END AS settled_rule_commission_micros,
       a.amount_micros AS initially_accrued_micros,
       coalesce(x.reversed_or_forfeited_micros,0)
         AS reversed_or_forfeited_micros,
       a.amount_micros-coalesce(x.reversed_or_forfeited_micros,0)
         AS final_earned_commission_micros,
       coalesce(x.paid_micros,0) AS paid_micros,
       a.currency
FROM commission_event a
JOIN commission_rule_version r USING (commission_rule_version_id)
LEFT JOIN base_deduction d USING (base_commercial_event_id)
LEFT JOIN commission_adjustment x
  ON x.source_accrual_event_id=a.commission_event_id
WHERE a.event_type='ACCRUED';

CREATE VIEW v_channel_funnel AS
WITH lead_count AS (
  SELECT agreement_version_id,count(*) AS sourced_leads
  FROM lead_source_event GROUP BY agreement_version_id
), registration_count AS (
  SELECT s.agreement_version_id,
         count(*) FILTER (WHERE r.registration_status='ACCEPTED')
           AS accepted_registrations
  FROM lead_source_event s
  JOIN v_current_lead_registration r
    ON r.lead_source_event_id=s.lead_source_event_id
  GROUP BY s.agreement_version_id
), attribution_count AS (
  SELECT r.agreement_version_id,
         count(*) FILTER (WHERE d.attribution_role='WINNER') AS won_attributions
  FROM attribution_decision d
  JOIN lead_registration_event r
    ON r.registration_event_id=d.registration_event_id
  GROUP BY r.agreement_version_id
), cash_by_event_id AS (
  SELECT agreement_version_id,sum(amount_micros) AS customer_cash_observed_micros
  FROM commercial_event WHERE event_type='CUSTOMER_CASH'
  GROUP BY agreement_version_id
), paid_by_event_id AS (
  SELECT agreement_version_id,sum(amount_micros) AS paid_commission_micros
  FROM commission_event WHERE event_type='PAID'
  GROUP BY agreement_version_id
)
SELECT a.agreement_version_id,a.motion_code,
       coalesce(l.sourced_leads,0) AS sourced_leads,
       coalesce(r.accepted_registrations,0) AS accepted_registrations,
       coalesce(d.won_attributions,0) AS won_attributions,
       coalesce(c.customer_cash_observed_micros,0)
         AS customer_cash_observed_micros,
       coalesce(p.paid_commission_micros,0) AS paid_commission_micros
FROM agreement_version a
LEFT JOIN lead_count l USING (agreement_version_id)
LEFT JOIN registration_count r USING (agreement_version_id)
LEFT JOIN attribution_count d USING (agreement_version_id)
LEFT JOIN cash_by_event_id c USING (agreement_version_id)
LEFT JOIN paid_by_event_id p USING (agreement_version_id);

CREATE VIEW v_agreement_cash_economics AS
WITH commercial_bank AS (
  SELECT a.agreement_version_id,
         coalesce(sum(CASE
           WHEN b.account_owner_party_id=a.principal_party_id
             AND b.direction_code='INFLOW'
             AND c.payee_party_id=a.principal_party_id
           THEN c.amount_micros ELSE 0 END),0) AS principal_bank_revenue_micros,
         coalesce(sum(CASE
           WHEN b.account_owner_party_id=a.principal_party_id
             AND b.direction_code='OUTFLOW'
             AND c.payer_party_id=a.principal_party_id
           THEN c.amount_micros ELSE 0 END),0) AS principal_commercial_outflow_micros,
         coalesce(sum(CASE
           WHEN b.account_owner_party_id<>a.principal_party_id
           THEN c.amount_micros ELSE 0 END),0) AS nonprincipal_bank_cash_micros
  FROM agreement_version a
  LEFT JOIN commercial_event c USING (agreement_version_id)
  LEFT JOIN bank_evidence b USING (bank_evidence_id)
  GROUP BY a.agreement_version_id
), commission_cost AS (
  SELECT a.agreement_version_id,
         coalesce(sum(CASE
           WHEN e.event_type='PAID'
             AND b.account_owner_party_id=a.principal_party_id
             AND b.direction_code='OUTFLOW'
           THEN e.amount_micros ELSE 0 END),0) AS paid_commission_cost_micros
  FROM agreement_version a
  LEFT JOIN commission_event e USING (agreement_version_id)
  LEFT JOIN bank_evidence b USING (bank_evidence_id)
  GROUP BY a.agreement_version_id
), other_cost AS (
  SELECT a.agreement_version_id,
         coalesce((SELECT sum(x.cash_cost_micros) FROM cost_event x
                   WHERE x.agreement_version_id=a.agreement_version_id
                     AND x.cost_bearer_party_id=a.principal_party_id
                     AND x.cash_cost_micros IS NOT NULL),0)
       + coalesce((SELECT sum(w.cash_cost_micros)
                   FROM work_event w JOIN work_order o USING (work_order_id)
                   WHERE o.agreement_version_id=a.agreement_version_id
                     AND w.cost_bearer_party_id=a.principal_party_id),0)
           AS principal_other_cash_cost_micros,
         coalesce((SELECT sum(c.amount_micros) FROM commercial_event c
                   WHERE c.agreement_version_id=a.agreement_version_id
                     AND c.payer_party_id=a.principal_party_id
                     AND c.bank_evidence_id IS NULL
                     AND c.event_type IN (
                       'REFUND','CHARGEBACK','WHOLESALE_OBLIGATION'
                     ) AND NOT EXISTS (
                       SELECT 1 FROM reseller_order ro
                       WHERE ro.upstream_obligation_event_id=c.commercial_event_id
                         AND ro.upstream_cash_event_id IS NOT NULL
                     )),0) AS principal_unsettled_cost_micros,
         coalesce((SELECT sum(c.amount_micros) FROM commercial_event c
                   WHERE c.agreement_version_id=a.agreement_version_id
                     AND c.payee_party_id=a.principal_party_id
                     AND c.bank_evidence_id IS NULL
                     AND c.event_type IN ('INVOICE','WHOLESALE_OBLIGATION')
                     AND NOT EXISTS (
                       SELECT 1 FROM reseller_order ro
                       WHERE ro.upstream_obligation_event_id=c.commercial_event_id
                         AND ro.upstream_cash_event_id IS NOT NULL
                     )),
                  0) AS principal_unsettled_receivable_micros,
         coalesce((SELECT sum(x.founder_minutes) FROM cost_event x
                   WHERE x.agreement_version_id=a.agreement_version_id
                     AND x.cost_bearer_party_id=a.principal_party_id
                     AND x.cost_class='FOUNDER_TIME'),0)
           AS total_founder_delegated_work_minutes
  FROM agreement_version a
), baseline AS (
  SELECT agreement_version_id,sum(baseline_founder_minutes)
           AS baseline_founder_minutes
  FROM delegation_baseline GROUP BY agreement_version_id
)
SELECT a.agreement_version_id,a.motion_code,a.currency,
       bflow.principal_bank_revenue_micros AS bank_revenue_micros,
       bflow.principal_commercial_outflow_micros
         +cc.paid_commission_cost_micros
         +oc.principal_other_cash_cost_micros AS bank_cost_micros,
       bflow.principal_commercial_outflow_micros
         +cc.paid_commission_cost_micros
         +oc.principal_other_cash_cost_micros
         +oc.principal_unsettled_cost_micros AS economic_cash_cost_micros,
       cc.paid_commission_cost_micros,
       bflow.nonprincipal_bank_cash_micros,
       oc.principal_unsettled_receivable_micros,
       oc.total_founder_delegated_work_minutes,
       coalesce(b.baseline_founder_minutes,0) AS baseline_founder_minutes,
       coalesce(b.baseline_founder_minutes,0)
         -oc.total_founder_delegated_work_minutes AS net_founder_minutes_released,
       oc.total_founder_delegated_work_minutes
         *(SELECT founder_rate_micros_per_minute FROM model_setting
           WHERE singleton_id=1) AS founder_time_cost_micros,
       bflow.principal_bank_revenue_micros
         -bflow.principal_commercial_outflow_micros
         -cc.paid_commission_cost_micros
         -oc.principal_other_cash_cost_micros AS bank_cash_contribution_micros,
       bflow.principal_bank_revenue_micros
         -bflow.principal_commercial_outflow_micros
         -cc.paid_commission_cost_micros
         -oc.principal_other_cash_cost_micros
         -oc.principal_unsettled_cost_micros
         -oc.total_founder_delegated_work_minutes
          *(SELECT founder_rate_micros_per_minute FROM model_setting
            WHERE singleton_id=1) AS fully_loaded_contribution_micros
FROM agreement_version a
JOIN commercial_bank bflow USING (agreement_version_id)
JOIN commission_cost cc USING (agreement_version_id)
JOIN other_cost oc USING (agreement_version_id)
LEFT JOIN baseline b USING (agreement_version_id);

CREATE VIEW v_experiment_readout AS
SELECT e.experiment_id,e.motion_code,
       sum(CASE WHEN a.arm_code='TREATMENT'
                 AND o.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO')
                THEN 1 ELSE 0 END) AS treatment_n,
       sum(CASE WHEN a.arm_code='CONTROL'
                 AND o.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO')
                THEN 1 ELSE 0 END) AS control_n,
       avg(CASE WHEN a.arm_code='TREATMENT'
                 AND o.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO')
                THEN o.outcome_value_micros END) AS treatment_mean_micros,
       avg(CASE WHEN a.arm_code='CONTROL'
                 AND o.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO')
                THEN o.outcome_value_micros END) AS control_mean_micros,
       avg(CASE WHEN a.arm_code='TREATMENT'
                 AND o.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO')
                THEN o.outcome_value_micros END)
       -avg(CASE WHEN a.arm_code='CONTROL'
                  AND o.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO')
                 THEN o.outcome_value_micros END) AS difference_micros,
       CASE
         WHEN sum(CASE WHEN a.contamination_status<>'CLEAN' THEN 1 ELSE 0 END)>0
           THEN 'INCONCLUSIVE_CONTAMINATION'
         WHEN sum(CASE WHEN o.maturity_state IN ('IMMATURE','UNKNOWN') THEN 1 ELSE 0 END)>0
           THEN 'INCONCLUSIVE_MISSING'
         WHEN sum(CASE WHEN a.arm_code='TREATMENT' THEN 1 ELSE 0 END)<5
           OR sum(CASE WHEN a.arm_code='CONTROL' THEN 1 ELSE 0 END)<5
           THEN 'INCONCLUSIVE_SMALL_N'
         ELSE 'ESTIMABLE'
       END AS causal_state
FROM partner_experiment e
JOIN experiment_assignment a USING (experiment_id)
LEFT JOIN experiment_outcome o USING (assignment_id)
GROUP BY e.experiment_id,e.motion_code;

CREATE VIEW v_termination_readiness AS
SELECT t.termination_case_id,t.agreement_version_id,
       coalesce((SELECT count(*) FROM v_current_access a
                 WHERE a.agreement_version_id=t.agreement_version_id
                   AND a.access_state='OPEN'),0) AS open_access_count,
       coalesce((SELECT count(*) FROM handoff_item_dim d
                 WHERE d.required_for_all=1 AND NOT EXISTS (
                   SELECT 1 FROM v_current_handoff_item h
                   WHERE h.termination_case_id=t.termination_case_id
                     AND h.item_code=d.item_code
                     AND h.item_status IN ('PASS','NA')
                 )),0) AS unresolved_handoff_count,
       CASE WHEN coalesce((SELECT count(*) FROM v_current_access a
                           WHERE a.agreement_version_id=t.agreement_version_id
                             AND a.access_state='OPEN'),0)=0
                  AND coalesce((SELECT count(*) FROM handoff_item_dim d
                                WHERE d.required_for_all=1 AND NOT EXISTS (
                                  SELECT 1 FROM v_current_handoff_item h
                                  WHERE h.termination_case_id=t.termination_case_id
                                    AND h.item_code=d.item_code
                                    AND h.item_status IN ('PASS','NA')
                                )),0)=0
            THEN 'READY_TO_CLOSE' ELSE 'PAUSE' END AS closure_gate
FROM termination_case t;

CREATE VIEW v_current_decision_snapshot AS
SELECT d.*
FROM decision_snapshot d
WHERE NOT EXISTS (
  SELECT 1 FROM decision_snapshot later
  WHERE later.agreement_version_id=d.agreement_version_id
    AND later.decision_sequence>d.decision_sequence
);

-- -----------------------------------------------------------------------------
-- Evidence ledgers and snapshots are append-only.
-- -----------------------------------------------------------------------------

CREATE TRIGGER immutable_party_update BEFORE UPDATE ON party
BEGIN SELECT RAISE(ABORT,'party is immutable'); END;
CREATE TRIGGER immutable_party_delete BEFORE DELETE ON party
BEGIN SELECT RAISE(ABORT,'party is immutable'); END;
CREATE TRIGGER immutable_account_update BEFORE UPDATE ON commercial_account
BEGIN SELECT RAISE(ABORT,'commercial account is immutable'); END;
CREATE TRIGGER immutable_account_delete BEFORE DELETE ON commercial_account
BEGIN SELECT RAISE(ABORT,'commercial account is immutable'); END;
CREATE TRIGGER immutable_evidence_update BEFORE UPDATE ON evidence_record
BEGIN SELECT RAISE(ABORT,'evidence record is immutable'); END;
CREATE TRIGGER immutable_evidence_delete BEFORE DELETE ON evidence_record
BEGIN SELECT RAISE(ABORT,'evidence record is immutable'); END;
CREATE TRIGGER immutable_policy_update BEFORE UPDATE ON policy_snapshot
BEGIN SELECT RAISE(ABORT,'policy snapshot is immutable'); END;
CREATE TRIGGER immutable_policy_delete BEFORE DELETE ON policy_snapshot
BEGIN SELECT RAISE(ABORT,'policy snapshot is immutable'); END;
CREATE TRIGGER immutable_agreement_update BEFORE UPDATE ON agreement_version
BEGIN SELECT RAISE(ABORT,'agreement version is immutable'); END;
CREATE TRIGGER immutable_agreement_delete BEFORE DELETE ON agreement_version
BEGIN SELECT RAISE(ABORT,'agreement version is immutable'); END;
CREATE TRIGGER immutable_agreement_evidence_update BEFORE UPDATE ON agreement_evidence_link
BEGIN SELECT RAISE(ABORT,'agreement evidence link is immutable'); END;
CREATE TRIGGER immutable_agreement_evidence_delete BEFORE DELETE ON agreement_evidence_link
BEGIN SELECT RAISE(ABORT,'agreement evidence link is immutable'); END;
CREATE TRIGGER immutable_stage_contract_update BEFORE UPDATE ON motion_stage_contract
BEGIN SELECT RAISE(ABORT,'motion stage contract is immutable'); END;
CREATE TRIGGER immutable_stage_contract_delete BEFORE DELETE ON motion_stage_contract
BEGIN SELECT RAISE(ABORT,'motion stage contract is immutable'); END;
CREATE TRIGGER immutable_responsibility_requirement_update BEFORE UPDATE ON motion_responsibility_requirement
BEGIN SELECT RAISE(ABORT,'responsibility requirement is immutable'); END;
CREATE TRIGGER immutable_responsibility_requirement_delete BEFORE DELETE ON motion_responsibility_requirement
BEGIN SELECT RAISE(ABORT,'responsibility requirement is immutable'); END;
CREATE TRIGGER immutable_responsibility_update BEFORE UPDATE ON responsibility_assignment
BEGIN SELECT RAISE(ABORT,'responsibility assignment is immutable'); END;
CREATE TRIGGER immutable_responsibility_delete BEFORE DELETE ON responsibility_assignment
BEGIN SELECT RAISE(ABORT,'responsibility assignment is immutable'); END;
CREATE TRIGGER immutable_authority_requirement_update BEFORE UPDATE ON motion_authority_requirement
BEGIN SELECT RAISE(ABORT,'authority requirement is immutable'); END;
CREATE TRIGGER immutable_authority_requirement_delete BEFORE DELETE ON motion_authority_requirement
BEGIN SELECT RAISE(ABORT,'authority requirement is immutable'); END;
CREATE TRIGGER immutable_authority_update BEFORE UPDATE ON authority_grant
BEGIN SELECT RAISE(ABORT,'authority grant is immutable'); END;
CREATE TRIGGER immutable_authority_delete BEFORE DELETE ON authority_grant
BEGIN SELECT RAISE(ABORT,'authority grant is immutable'); END;
CREATE TRIGGER immutable_tier_update BEFORE UPDATE ON tier_rule_version
BEGIN SELECT RAISE(ABORT,'tier rule version is immutable'); END;
CREATE TRIGGER immutable_tier_delete BEFORE DELETE ON tier_rule_version
BEGIN SELECT RAISE(ABORT,'tier rule version is immutable'); END;
CREATE TRIGGER immutable_enablement_update BEFORE UPDATE ON enablement_evidence
BEGIN SELECT RAISE(ABORT,'enablement evidence is immutable'); END;
CREATE TRIGGER immutable_enablement_delete BEFORE DELETE ON enablement_evidence
BEGIN SELECT RAISE(ABORT,'enablement evidence is immutable'); END;
CREATE TRIGGER immutable_access_update BEFORE UPDATE ON access_event
BEGIN SELECT RAISE(ABORT,'access event is immutable'); END;
CREATE TRIGGER immutable_access_delete BEFORE DELETE ON access_event
BEGIN SELECT RAISE(ABORT,'access event is immutable'); END;
CREATE TRIGGER immutable_work_order_update BEFORE UPDATE ON work_order
BEGIN SELECT RAISE(ABORT,'work order is immutable'); END;
CREATE TRIGGER immutable_work_order_delete BEFORE DELETE ON work_order
BEGIN SELECT RAISE(ABORT,'work order is immutable'); END;
CREATE TRIGGER immutable_work_event_update BEFORE UPDATE ON work_event
BEGIN SELECT RAISE(ABORT,'work event is immutable'); END;
CREATE TRIGGER immutable_work_event_delete BEFORE DELETE ON work_event
BEGIN SELECT RAISE(ABORT,'work event is immutable'); END;
CREATE TRIGGER immutable_acceptance_update BEFORE UPDATE ON acceptance_event
BEGIN SELECT RAISE(ABORT,'acceptance event is immutable'); END;
CREATE TRIGGER immutable_acceptance_delete BEFORE DELETE ON acceptance_event
BEGIN SELECT RAISE(ABORT,'acceptance event is immutable'); END;
CREATE TRIGGER immutable_lead_source_update BEFORE UPDATE ON lead_source_event
BEGIN SELECT RAISE(ABORT,'lead source event is immutable'); END;
CREATE TRIGGER immutable_lead_source_delete BEFORE DELETE ON lead_source_event
BEGIN SELECT RAISE(ABORT,'lead source event is immutable'); END;
CREATE TRIGGER immutable_lead_registration_update BEFORE UPDATE ON lead_registration_event
BEGIN SELECT RAISE(ABORT,'lead registration event is immutable'); END;
CREATE TRIGGER immutable_lead_registration_delete BEFORE DELETE ON lead_registration_event
BEGIN SELECT RAISE(ABORT,'lead registration event is immutable'); END;
CREATE TRIGGER immutable_bank_update BEFORE UPDATE ON bank_evidence
BEGIN SELECT RAISE(ABORT,'bank evidence is immutable'); END;
CREATE TRIGGER immutable_bank_delete BEFORE DELETE ON bank_evidence
BEGIN SELECT RAISE(ABORT,'bank evidence is immutable'); END;
CREATE TRIGGER immutable_commercial_update BEFORE UPDATE ON commercial_event
BEGIN SELECT RAISE(ABORT,'commercial event is immutable'); END;
CREATE TRIGGER immutable_commercial_delete BEFORE DELETE ON commercial_event
BEGIN SELECT RAISE(ABORT,'commercial event is immutable'); END;
CREATE TRIGGER immutable_attribution_rule_update BEFORE UPDATE ON attribution_rule_version
BEGIN SELECT RAISE(ABORT,'attribution rule version is immutable'); END;
CREATE TRIGGER immutable_attribution_rule_delete BEFORE DELETE ON attribution_rule_version
BEGIN SELECT RAISE(ABORT,'attribution rule version is immutable'); END;
CREATE TRIGGER immutable_attribution_update BEFORE UPDATE ON attribution_decision
BEGIN SELECT RAISE(ABORT,'attribution decision is immutable'); END;
CREATE TRIGGER immutable_attribution_delete BEFORE DELETE ON attribution_decision
BEGIN SELECT RAISE(ABORT,'attribution decision is immutable'); END;
CREATE TRIGGER immutable_commission_rule_update BEFORE UPDATE ON commission_rule_version
BEGIN SELECT RAISE(ABORT,'commission rule version is immutable'); END;
CREATE TRIGGER immutable_commission_rule_delete BEFORE DELETE ON commission_rule_version
BEGIN SELECT RAISE(ABORT,'commission rule version is immutable'); END;
CREATE TRIGGER immutable_termination_case_update BEFORE UPDATE ON termination_case
BEGIN SELECT RAISE(ABORT,'termination case is immutable'); END;
CREATE TRIGGER immutable_termination_case_delete BEFORE DELETE ON termination_case
BEGIN SELECT RAISE(ABORT,'termination case is immutable'); END;
CREATE TRIGGER immutable_commission_event_update BEFORE UPDATE ON commission_event
BEGIN SELECT RAISE(ABORT,'commission event is immutable'); END;
CREATE TRIGGER immutable_commission_event_delete BEFORE DELETE ON commission_event
BEGIN SELECT RAISE(ABORT,'commission event is immutable'); END;
CREATE TRIGGER immutable_reseller_order_update BEFORE UPDATE ON reseller_order
BEGIN SELECT RAISE(ABORT,'reseller order is immutable'); END;
CREATE TRIGGER immutable_reseller_order_delete BEFORE DELETE ON reseller_order
BEGIN SELECT RAISE(ABORT,'reseller order is immutable'); END;
CREATE TRIGGER immutable_cost_update BEFORE UPDATE ON cost_event
BEGIN SELECT RAISE(ABORT,'cost event is immutable'); END;
CREATE TRIGGER immutable_cost_delete BEFORE DELETE ON cost_event
BEGIN SELECT RAISE(ABORT,'cost event is immutable'); END;
CREATE TRIGGER immutable_experiment_update BEFORE UPDATE ON partner_experiment
BEGIN SELECT RAISE(ABORT,'partner experiment is immutable'); END;
CREATE TRIGGER immutable_experiment_delete BEFORE DELETE ON partner_experiment
BEGIN SELECT RAISE(ABORT,'partner experiment is immutable'); END;
CREATE TRIGGER immutable_assignment_update BEFORE UPDATE ON experiment_assignment
BEGIN SELECT RAISE(ABORT,'experiment assignment is immutable'); END;
CREATE TRIGGER immutable_assignment_delete BEFORE DELETE ON experiment_assignment
BEGIN SELECT RAISE(ABORT,'experiment assignment is immutable'); END;
CREATE TRIGGER immutable_outcome_update BEFORE UPDATE ON experiment_outcome
BEGIN SELECT RAISE(ABORT,'experiment outcome is immutable'); END;
CREATE TRIGGER immutable_outcome_delete BEFORE DELETE ON experiment_outcome
BEGIN SELECT RAISE(ABORT,'experiment outcome is immutable'); END;
CREATE TRIGGER immutable_handoff_item_update BEFORE UPDATE ON handoff_item_event
BEGIN SELECT RAISE(ABORT,'handoff item event is immutable'); END;
CREATE TRIGGER immutable_handoff_item_delete BEFORE DELETE ON handoff_item_event
BEGIN SELECT RAISE(ABORT,'handoff item event is immutable'); END;
CREATE TRIGGER immutable_termination_event_update BEFORE UPDATE ON termination_event
BEGIN SELECT RAISE(ABORT,'termination event is immutable'); END;
CREATE TRIGGER immutable_termination_event_delete BEFORE DELETE ON termination_event
BEGIN SELECT RAISE(ABORT,'termination event is immutable'); END;
CREATE TRIGGER immutable_decision_update BEFORE UPDATE ON decision_snapshot
BEGIN SELECT RAISE(ABORT,'decision snapshot is immutable'); END;
CREATE TRIGGER immutable_decision_delete BEFORE DELETE ON decision_snapshot
BEGIN SELECT RAISE(ABORT,'decision snapshot is immutable'); END;
CREATE TRIGGER immutable_decision_metric_update BEFORE UPDATE ON decision_snapshot_metric
BEGIN SELECT RAISE(ABORT,'decision snapshot metric is immutable'); END;
CREATE TRIGGER immutable_decision_metric_delete BEFORE DELETE ON decision_snapshot_metric
BEGIN SELECT RAISE(ABORT,'decision snapshot metric is immutable'); END;
CREATE TRIGGER immutable_baseline_update BEFORE UPDATE ON delegation_baseline
BEGIN SELECT RAISE(ABORT,'delegation baseline is immutable'); END;
CREATE TRIGGER immutable_baseline_delete BEFORE DELETE ON delegation_baseline
BEGIN SELECT RAISE(ABORT,'delegation baseline is immutable'); END;

-- -----------------------------------------------------------------------------
-- Fully synthetic fixture
-- -----------------------------------------------------------------------------

INSERT INTO schema_registry(
  schema_id,schema_name,schema_version,engine_floor,applied_at,data_policy_code
) VALUES (
  1,'partner_delegation_economics','1.0.0','SQLite 3.40+',
  '2026-08-02T00:00:00Z','SYNTHETIC_ONLY_NO_RAW_PII_OPAQUE_REFERENCES'
);

INSERT INTO model_setting(
  singleton_id,reference_time,default_snapshot_as_of,base_currency,
  founder_rate_micros_per_minute
) VALUES (
  1,'2026-08-02T23:59:59Z','2026-08-02T12:00:00Z','JPY',100000000
);

INSERT INTO evidence_class_dim(evidence_class,strength_rank) VALUES
  ('E0_INFERENCE',0),('E1_MANUAL',1),('E2_PROGRAM_CONTROL_PLANE',2),
  ('E3_PROVIDER_EVENT',3),('E4_FIRST_PARTY_AUTHORITATIVE',4),
  ('E5_CUSTOMER_OR_BANK_AUTHORITATIVE',5);

INSERT INTO motion_dim(
  motion_code,motion_family,is_workforce,is_channel,is_service,is_license
) VALUES
  ('OUTSOURCING','WORKFORCE',1,0,1,0),
  ('EMPLOYMENT','WORKFORCE',1,0,1,0),
  ('REFERRAL','CHANNEL',0,1,0,0),
  ('AFFILIATE','CHANNEL',0,1,0,0),
  ('SALES_AGENT','CHANNEL',0,1,0,0),
  ('RESELLER','CHANNEL',0,1,0,1),
  ('IMPLEMENTATION_SUPPORT','SERVICE',0,0,1,0),
  ('WHITE_LABEL','PRESENTATION',0,0,0,0),
  ('OEM','LICENSE',0,1,0,1);

INSERT INTO canonical_stage_dim(stage_code,display_order,stage_domain) VALUES
  ('POLICY_SNAPSHOTTED',10,'POLICY'),
  ('PARTNER_ELIGIBLE',20,'POLICY'),
  ('AGREEMENT_EFFECTIVE',30,'AGREEMENT'),
  ('ENABLED_OR_CERTIFIED',40,'ENABLEMENT'),
  ('LEAD_SOURCED',50,'LEAD'),
  ('CONSENT_DISCLOSURE_PASS',60,'LEAD'),
  ('LEAD_REGISTERED',70,'LEAD'),
  ('ACCEPTED_AND_DEDUPED',80,'ATTRIBUTION'),
  ('ATTRIBUTED',90,'ATTRIBUTION'),
  ('QUALIFIED',100,'SALES'),
  ('INVOLVEMENT_PROVED',110,'SALES'),
  ('QUOTE_OR_ORDER_ACCEPTED',120,'CONTRACT'),
  ('CUSTOMER_CONTRACT_EFFECTIVE',130,'CONTRACT'),
  ('REQUIRED_REVENUE_BASE_OBSERVED',140,'CASH'),
  ('ENTITLED_OR_DELIVERED',150,'DELIVERY'),
  ('OUTCOME_ACCEPTED',160,'QUALITY'),
  ('REFUND_CLAWBACK_WINDOW_MATURE',170,'ECONOMICS'),
  ('COMMISSION_OR_MARGIN_ACCRUED',180,'ECONOMICS'),
  ('PAYOUT_BANK_RECONCILED',190,'CASH'),
  ('RENEWED',200,'RETENTION'),
  ('TERMINATED_HANDOFF_CLOSED',210,'OFFBOARDING');

INSERT INTO responsibility_domain_dim(responsibility_code,category_code) VALUES
  ('PRODUCT_CONTRACT','CONTRACT'),('SERVICE_CONTRACT','CONTRACT'),
  ('INVOICE_ISSUER','MONEY'),('CASH_COLLECTOR','MONEY'),
  ('MERCHANT_OF_RECORD','MONEY'),('PRODUCT_DATA_CONTROLLER','DATA'),
  ('LEAD_DATA_CONTROLLER','DATA'),('DATA_PROCESSOR','DATA'),
  ('SUPPORT_L1','SUPPORT'),('SUPPORT_L2','SUPPORT'),('SUPPORT_L3','SUPPORT'),
  ('FULFILLMENT','OPERATIONS'),('REFUND_OWNER','MONEY'),
  ('DISPUTE_OWNER','MONEY'),('SECURITY_INCIDENT_OWNER','OPERATIONS'),
  ('DATA_EXPORT_DELETE_OWNER','OFFBOARDING'),
  ('RENEWAL_HANDOFF_OWNER','OFFBOARDING');

INSERT INTO authority_code_dim(authority_code) VALUES
  ('INTRODUCE_LEAD'),('PUBLISH_TRACKED_PROMOTION'),('SOLICIT_CUSTOMER'),
  ('NEGOTIATE_TERMS'),('BIND_PRINCIPAL'),('SET_DOWNSTREAM_PRICE'),
  ('INVOICE_CUSTOMER'),('COLLECT_CUSTOMER_CASH'),('DELIVER_SERVICE'),
  ('ACCESS_CUSTOMER_SYSTEM'),('USE_PRINCIPAL_BRAND'),
  ('USE_WHITE_LABEL_BRAND'),('SUBLICENSE_PRODUCT'),('BUNDLE_PRODUCT'),
  ('CONTROL_WORK_MANNER');

INSERT INTO work_class_dim(work_class_code,quality_effect) VALUES
  ('STANDARD_DELIVERY','PLANNED'),('PLANNED_REVIEW','PLANNED'),
  ('PARTNER_REWORK','REWORK'),('SUPPORT','PLANNED'),('INCIDENT','INCIDENT'),
  ('FOUNDER_RESCUE','RESCUE'),('PARTNER_ENABLEMENT','OVERHEAD'),
  ('LEAD_REVIEW','OVERHEAD'),('DEAL_RESCUE','RESCUE'),
  ('DELIVERY_QA','PLANNED'),('COMMISSION_DISPUTE','OVERHEAD'),
  ('COLLECTIONS','OVERHEAD'),('OFFBOARDING','OVERHEAD');

INSERT INTO handoff_item_dim(item_code,required_for_all) VALUES
  ('ACCESS_REVOKED',1),('DATA_DISPOSITION',1),('CUSTOMER_NOTICE',1),
  ('OPEN_SUPPORT',1),('OPEN_INCIDENTS',1),('OPEN_REFUNDS',1),
  ('RENEWAL_OWNER',1),('ENTITLEMENT_CONTINUITY',1),('COMMISSION_TAIL',1),
  ('IP_AND_ASSET_RETURN',1),('PAYROLL_OR_FINAL_INVOICE',1);

-- Every motion receives an explicit disposition for every canonical stage.
INSERT INTO motion_stage_contract(
  motion_code,stage_code,applicability,minimum_evidence_class,source_of_truth_code
)
SELECT m.motion_code,s.stage_code,
       CASE
         WHEN s.stage_code IN (
           'LEAD_SOURCED','CONSENT_DISCLOSURE_PASS','LEAD_REGISTERED',
           'ACCEPTED_AND_DEDUPED','ATTRIBUTED','QUALIFIED','INVOLVEMENT_PROVED'
         ) AND m.motion_code IN ('OUTSOURCING','EMPLOYMENT') THEN 'NA'
         WHEN s.stage_code IN (
           'LEAD_REGISTERED','ACCEPTED_AND_DEDUPED','ATTRIBUTED'
         ) AND m.motion_code IN (
           'IMPLEMENTATION_SUPPORT','WHITE_LABEL'
         ) THEN 'OPTIONAL'
         WHEN s.stage_code IN (
           'COMMISSION_OR_MARGIN_ACCRUED','PAYOUT_BANK_RECONCILED'
         ) AND m.motion_code IN ('OUTSOURCING','EMPLOYMENT') THEN 'OPTIONAL'
         WHEN s.stage_code='ENABLED_OR_CERTIFIED' THEN 'OPTIONAL'
         ELSE 'REQUIRED'
       END AS applicability,
       CASE
         WHEN s.stage_code IN (
           'LEAD_SOURCED','CONSENT_DISCLOSURE_PASS','LEAD_REGISTERED',
           'ACCEPTED_AND_DEDUPED','ATTRIBUTED','QUALIFIED','INVOLVEMENT_PROVED'
         ) AND m.motion_code IN ('OUTSOURCING','EMPLOYMENT') THEN NULL
         WHEN s.stage_code IN ('PAYOUT_BANK_RECONCILED','REQUIRED_REVENUE_BASE_OBSERVED')
           THEN 'E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
         WHEN s.stage_code IN ('ACCEPTED_AND_DEDUPED','ATTRIBUTED')
           THEN 'E3_PROVIDER_EVENT'
         WHEN s.stage_code IN ('OUTCOME_ACCEPTED','TERMINATED_HANDOFF_CLOSED')
           THEN 'E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
         ELSE 'E2_PROGRAM_CONTROL_PLANE'
       END AS minimum_evidence_class,
       CASE
         WHEN s.stage_code IN (
           'LEAD_SOURCED','CONSENT_DISCLOSURE_PASS','LEAD_REGISTERED',
           'ACCEPTED_AND_DEDUPED','ATTRIBUTED','QUALIFIED','INVOLVEMENT_PROVED'
         ) AND m.motion_code IN ('OUTSOURCING','EMPLOYMENT') THEN NULL
         WHEN s.stage_code='POLICY_SNAPSHOTTED' THEN 'POLICY_SNAPSHOT'
         WHEN s.stage_code IN ('ACCEPTED_AND_DEDUPED','ATTRIBUTED')
           THEN 'PROVIDER_REGISTRATION_LEDGER'
         WHEN s.stage_code='PAYOUT_BANK_RECONCILED' THEN 'BANK_LEDGER'
         WHEN s.stage_code='OUTCOME_ACCEPTED' THEN 'CUSTOMER_ACCEPTANCE'
         WHEN s.stage_code='TERMINATED_HANDOFF_CLOSED' THEN 'HANDOFF_LEDGER'
         ELSE 'AGREEMENT_OR_EVENT_LEDGER'
       END AS source_of_truth_code
FROM motion_dim m CROSS JOIN canonical_stage_dim s;

-- All owner axes require an explicit disposition. NA is allowed only where the
-- motion can genuinely lack that layer; UNKNOWN never satisfies readiness.
INSERT INTO motion_responsibility_requirement(
  motion_code,responsibility_code,allow_na
)
SELECT m.motion_code,r.responsibility_code,
       CASE
         WHEN r.responsibility_code IN (
           'SERVICE_CONTRACT','LEAD_DATA_CONTROLLER','DATA_PROCESSOR',
           'SUPPORT_L2','SUPPORT_L3'
         ) THEN 1
         WHEN m.motion_code IN ('OUTSOURCING','EMPLOYMENT')
           AND r.responsibility_code IN (
             'PRODUCT_CONTRACT','MERCHANT_OF_RECORD','PRODUCT_DATA_CONTROLLER',
             'REFUND_OWNER','DISPUTE_OWNER','RENEWAL_HANDOFF_OWNER'
           ) THEN 1
         ELSE 0
       END AS allow_na
FROM motion_dim m CROSS JOIN responsibility_domain_dim r;

INSERT INTO motion_authority_requirement(
  motion_code,authority_code,requirement_code
) VALUES
  ('OUTSOURCING','DELIVER_SERVICE','MUST_GRANT'),
  ('OUTSOURCING','ACCESS_CUSTOMER_SYSTEM','MUST_DISPOSE'),
  ('EMPLOYMENT','CONTROL_WORK_MANNER','MUST_GRANT'),
  ('EMPLOYMENT','BIND_PRINCIPAL','MUST_DISPOSE'),
  ('REFERRAL','INTRODUCE_LEAD','MUST_GRANT'),
  ('REFERRAL','BIND_PRINCIPAL','MUST_DENY'),
  ('AFFILIATE','PUBLISH_TRACKED_PROMOTION','MUST_GRANT'),
  ('AFFILIATE','BIND_PRINCIPAL','MUST_DENY'),
  ('SALES_AGENT','SOLICIT_CUSTOMER','MUST_GRANT'),
  ('SALES_AGENT','NEGOTIATE_TERMS','MUST_GRANT'),
  ('SALES_AGENT','BIND_PRINCIPAL','MUST_DISPOSE'),
  ('RESELLER','SET_DOWNSTREAM_PRICE','MUST_GRANT'),
  ('RESELLER','INVOICE_CUSTOMER','MUST_GRANT'),
  ('RESELLER','COLLECT_CUSTOMER_CASH','MUST_GRANT'),
  ('RESELLER','BIND_PRINCIPAL','MUST_DENY'),
  ('IMPLEMENTATION_SUPPORT','DELIVER_SERVICE','MUST_GRANT'),
  ('IMPLEMENTATION_SUPPORT','ACCESS_CUSTOMER_SYSTEM','MUST_GRANT'),
  ('WHITE_LABEL','USE_WHITE_LABEL_BRAND','MUST_GRANT'),
  ('OEM','USE_WHITE_LABEL_BRAND','MUST_GRANT'),
  ('OEM','SUBLICENSE_PRODUCT','MUST_GRANT'),
  ('OEM','BUNDLE_PRODUCT','MUST_GRANT');

INSERT INTO party(
  party_id,party_kind,legal_entity_ref_hash,country_code,created_at,recorded_at
)
WITH f(party_id,party_kind,n,country_code) AS (VALUES
  ('SYN_PROVIDER','PROVIDER',1001,'US'),
  ('SYN_PRINCIPAL','PRINCIPAL',1002,'JP'),
  ('SYN_PARTNER_OUT','PARTNER',1003,'JP'),
  ('SYN_WORKER_EMP','WORKER',1004,'JP'),
  ('SYN_PARTNER_REF','PARTNER',1005,'JP'),
  ('SYN_PARTNER_AFF','PARTNER',1006,'JP'),
  ('SYN_PARTNER_AGENT','PARTNER',1007,'JP'),
  ('SYN_PARTNER_RES','PARTNER',1008,'JP'),
  ('SYN_PARTNER_IMPL','PARTNER',1009,'JP'),
  ('SYN_PARTNER_WHITE','PARTNER',1010,'JP'),
  ('SYN_PARTNER_OEM','PARTNER',1011,'JP'),
  ('SYN_CUSTOMER_REF','CUSTOMER',1012,'JP'),
  ('SYN_CUSTOMER_IMPL','CUSTOMER',1013,'JP'),
  ('SYN_CUSTOMER_RES','CUSTOMER',1014,'JP'),
  ('SYN_CUSTOMER_CONTROL','CUSTOMER',1015,'JP')
)
SELECT party_id,party_kind,printf('%064x',n),country_code,
       '2026-06-01T00:00:00Z','2026-06-01T00:05:00Z'
FROM f;

INSERT INTO agreement_evidence_link(
  agreement_version_id,review_domain,evidence_id,link_status
) VALUES
  ('SYN_AGR_REF_1','LEGAL','SYN_E_LEGAL','SUPPORTS'),
  ('SYN_AGR_REF_1','AUTHORITY','SYN_E_AUTH','SUPPORTS'),
  ('SYN_AGR_REF_1','PRIVACY','SYN_E_PRIVACY','SUPPORTS'),
  ('SYN_AGR_REF_1','SECURITY','SYN_E_SECURITY','SUPPORTS'),
  ('SYN_AGR_REF_1','PAYMENT','SYN_E_PAYMENT','SUPPORTS'),
  ('SYN_AGR_REF_1','TERMINATION','SYN_E_TERMINATION','SUPPORTS'),
  ('SYN_AGR_REF_1','POLICY','SYN_E_POLICY','SUPPORTS'),
  ('SYN_AGR_IMPL_1','LEGAL','SYN_E_LEGAL','SUPPORTS'),
  ('SYN_AGR_IMPL_1','AUTHORITY','SYN_E_AUTH','SUPPORTS'),
  ('SYN_AGR_IMPL_1','PRIVACY','SYN_E_PRIVACY','SUPPORTS'),
  ('SYN_AGR_IMPL_1','SECURITY','SYN_E_SECURITY','SUPPORTS'),
  ('SYN_AGR_IMPL_1','PAYMENT','SYN_E_PAYMENT','SUPPORTS'),
  ('SYN_AGR_IMPL_1','IP_LICENSE','SYN_E_IP','SUPPORTS'),
  ('SYN_AGR_IMPL_1','TERMINATION','SYN_E_TERMINATION','SUPPORTS'),
  ('SYN_AGR_OEM_1','LEGAL','SYN_E_LEGAL_UNKNOWN','UNKNOWN'),
  ('SYN_AGR_OEM_1','AUTHORITY','SYN_E_PAUSE','UNKNOWN'),
  ('SYN_AGR_OEM_1','POLICY','SYN_E_POLICY_UNKNOWN','UNKNOWN');

INSERT INTO responsibility_assignment(
  agreement_version_id,scope_code,responsibility_code,disposition,
  owner_party_id,evidence_id,effective_from,effective_until,recorded_at
)
SELECT 'SYN_AGR_REF_1','GLOBAL',r.responsibility_code,
       CASE WHEN r.responsibility_code IN ('SERVICE_CONTRACT','DATA_PROCESSOR')
            THEN 'NA' ELSE 'CONFIRMED' END,
       CASE
         WHEN r.responsibility_code IN ('SERVICE_CONTRACT','DATA_PROCESSOR') THEN NULL
         WHEN r.responsibility_code='LEAD_DATA_CONTROLLER' THEN 'SYN_PARTNER_REF'
         ELSE 'SYN_PRINCIPAL'
       END,
       'SYN_E_RESP','2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'
FROM responsibility_domain_dim r;

INSERT INTO responsibility_assignment(
  agreement_version_id,scope_code,responsibility_code,disposition,
  owner_party_id,evidence_id,effective_from,effective_until,recorded_at
)
SELECT 'SYN_AGR_IMPL_1','GLOBAL',r.responsibility_code,
       CASE WHEN r.responsibility_code='LEAD_DATA_CONTROLLER'
            THEN 'NA' ELSE 'CONFIRMED' END,
       CASE
         WHEN r.responsibility_code='LEAD_DATA_CONTROLLER' THEN NULL
         WHEN r.responsibility_code IN (
           'PRODUCT_CONTRACT','PRODUCT_DATA_CONTROLLER','SUPPORT_L3',
           'SECURITY_INCIDENT_OWNER','DATA_EXPORT_DELETE_OWNER'
         ) THEN 'SYN_PRINCIPAL'
         ELSE 'SYN_PARTNER_IMPL'
       END,
       'SYN_E_RESP','2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'
FROM responsibility_domain_dim r;

INSERT INTO authority_grant(
  agreement_version_id,authority_code,scope_code,territory_code,disposition,
  evidence_id,effective_from,effective_until,recorded_at
) VALUES
  ('SYN_AGR_REF_1','INTRODUCE_LEAD','GLOBAL','JP','GRANTED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_REF_1','BIND_PRINCIPAL','GLOBAL','JP','DENIED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_IMPL_1','DELIVER_SERVICE','GLOBAL','JP','GRANTED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_IMPL_1','ACCESS_CUSTOMER_SYSTEM','GLOBAL','JP','GRANTED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_RES_1','SET_DOWNSTREAM_PRICE','GLOBAL','JP','GRANTED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_RES_1','INVOICE_CUSTOMER','GLOBAL','JP','GRANTED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_RES_1','COLLECT_CUSTOMER_CASH','GLOBAL','JP','GRANTED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_RES_1','BIND_PRINCIPAL','GLOBAL','JP','DENIED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_AFF_1','PUBLISH_TRACKED_PROMOTION','GLOBAL','JP','GRANTED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_AFF_1','BIND_PRINCIPAL','GLOBAL','JP','DENIED','SYN_E_AUTH',
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_OEM_1','USE_WHITE_LABEL_BRAND','GLOBAL','JP','UNKNOWN',NULL,
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_OEM_1','SUBLICENSE_PRODUCT','GLOBAL','JP','UNKNOWN',NULL,
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z'),
  ('SYN_AGR_OEM_1','BUNDLE_PRODUCT','GLOBAL','JP','UNKNOWN',NULL,
   '2026-07-01T00:00:00Z',NULL,'2026-06-20T00:10:00Z');

INSERT INTO tier_rule_version(
  tier_rule_version_id,tier_rule_stream_id,rule_sequence,
  supersedes_tier_rule_version_id,policy_snapshot_id,tier_code,metric_code,
  threshold_state,threshold_value,window_days,effective_from,effective_until,
  evidence_id,recorded_at
) VALUES
  ('SYN_TIER_1','SYN_TIER_STREAM',1,NULL,'SYN_POLICY_CURRENT','SYN_PARTNER',
   'SYN_CERTIFIED_PEOPLE','KNOWN',2,365,'2026-07-01T00:00:00Z',NULL,
   'SYN_E_TIER','2026-06-20T00:15:00Z'),
  ('SYN_TIER_OEM_UNKNOWN','SYN_TIER_OEM_STREAM',1,NULL,
   'SYN_POLICY_OEM_UNKNOWN','SYN_PREMIER','SYN_REVENUE_THRESHOLD',
   'PORTAL_ONLY',NULL,365,'2026-07-01T00:00:00Z',NULL,
   'SYN_E_POLICY_UNKNOWN','2026-06-20T00:15:00Z');

INSERT INTO enablement_evidence(
  enablement_id,agreement_version_id,enablement_type,result_status,evidence_id,
  achieved_at,expires_at,recorded_at
) VALUES
  ('SYN_ENABLE_IMPL_CERT','SYN_AGR_IMPL_1','CERTIFICATION','PASS','SYN_E_TIER',
   '2026-06-25T00:00:00Z','2027-06-25T00:00:00Z','2026-06-25T00:05:00Z'),
  ('SYN_ENABLE_OEM_UNKNOWN','SYN_AGR_OEM_1','ASSESSMENT','UNKNOWN',
   'SYN_E_POLICY_UNKNOWN','2026-06-25T00:00:00Z',NULL,'2026-06-25T00:05:00Z');

INSERT INTO access_event(
  access_event_id,access_stream_id,event_sequence,supersedes_access_event_id,
  agreement_version_id,account_id,grantee_party_id,system_scope_ref_hash,
  data_class_code,privilege_code,event_type,customer_authorization_status,
  least_privilege_status,effective_at,expires_at,evidence_id,recorded_at
) VALUES (
  'SYN_ACCESS_IMPL_GRANT','SYN_ACCESS_IMPL',1,NULL,'SYN_AGR_IMPL_1',
  'SYN_ACC_IMPL','SYN_PARTNER_IMPL',printf('%064x',1501),
  'CUSTOMER_CONFIDENTIAL','REVERSIBLE_WRITE','GRANTED','PASS','PASS',
  '2026-07-02T00:00:00Z','2026-12-31T00:00:00Z','SYN_E_PRIVACY',
  '2026-07-02T00:05:00Z'
);

INSERT INTO work_order(
  work_order_id,agreement_version_id,account_id,service_scope_code,
  acceptance_method_code,contracted_fee_micros,currency,planned_start_at,
  planned_end_at,work_order_ref_hash,recorded_at
) VALUES
  ('SYN_WO_IMPL_PASS','SYN_AGR_IMPL_1','SYN_ACC_IMPL','SYN_STANDARD_SETUP',
   'CUSTOMER_SIGNOFF',2000000000,'JPY','2026-07-03T00:00:00Z',
   '2026-07-10T00:00:00Z',printf('%064x',1511),'2026-07-02T00:10:00Z'),
  ('SYN_WO_IMPL_RESCUE','SYN_AGR_IMPL_1','SYN_ACC_IMPL','SYN_STANDARD_MIGRATION',
   'CUSTOMER_SIGNOFF',3000000000,'JPY','2026-07-04T00:00:00Z',
   '2026-07-15T00:00:00Z',printf('%064x',1512),'2026-07-02T00:10:00Z');

INSERT INTO work_event(
  work_event_id,work_order_id,actor_party_id,cost_bearer_party_id,
  actor_role_code,work_class_code,
  minutes,cash_cost_micros,currency,evidence_id,occurred_at,recorded_at
) VALUES
  ('SYN_WORK_PASS_STANDARD','SYN_WO_IMPL_PASS','SYN_PARTNER_IMPL',
   'SYN_PARTNER_IMPL','PARTNER',
   'STANDARD_DELIVERY',60,500000000,'JPY','SYN_E_WORK',
   '2026-07-05T00:00:00Z','2026-07-05T00:05:00Z'),
  ('SYN_WORK_RESCUE_STANDARD','SYN_WO_IMPL_RESCUE','SYN_PARTNER_IMPL',
   'SYN_PARTNER_IMPL','PARTNER',
   'STANDARD_DELIVERY',120,700000000,'JPY','SYN_E_WORK',
   '2026-07-06T00:00:00Z','2026-07-06T00:05:00Z'),
  ('SYN_WORK_PARTNER_REWORK','SYN_WO_IMPL_RESCUE','SYN_PARTNER_IMPL',
   'SYN_PARTNER_IMPL','PARTNER',
   'PARTNER_REWORK',30,100000000,'JPY','SYN_E_WORK',
   '2026-07-08T00:00:00Z','2026-07-08T00:05:00Z'),
  ('SYN_WORK_FOUNDER_REVIEW','SYN_WO_IMPL_RESCUE','SYN_PRINCIPAL',
   'SYN_PRINCIPAL','FOUNDER',
   'PLANNED_REVIEW',10,0,'JPY','SYN_E_WORK',
   '2026-07-09T00:00:00Z','2026-07-09T00:05:00Z'),
  ('SYN_WORK_FOUNDER_RESIDUAL','SYN_WO_IMPL_RESCUE','SYN_PRINCIPAL',
   'SYN_PRINCIPAL','FOUNDER',
   'SUPPORT',20,0,'JPY','SYN_E_WORK',
   '2026-07-10T00:00:00Z','2026-07-10T00:05:00Z'),
  ('SYN_WORK_FOUNDER_RESCUE','SYN_WO_IMPL_RESCUE','SYN_PRINCIPAL',
   'SYN_PRINCIPAL','FOUNDER',
   'FOUNDER_RESCUE',15,0,'JPY','SYN_E_WORK',
   '2026-07-11T00:00:00Z','2026-07-11T00:05:00Z');

INSERT INTO acceptance_event(
  acceptance_event_id,acceptance_stream_id,event_sequence,
  supersedes_acceptance_event_id,work_order_id,outcome_code,evidence_status,
  evidence_id,occurred_at,recorded_at
) VALUES
  ('SYN_ACCEPT_PASS','SYN_ACCEPT_PASS_STREAM',1,NULL,'SYN_WO_IMPL_PASS',
   'ACCEPTED_FIRST_PASS','PASS','SYN_E_ACCEPT',
   '2026-07-10T00:00:00Z','2026-07-10T00:05:00Z'),
  ('SYN_ACCEPT_RESCUE','SYN_ACCEPT_RESCUE_STREAM',1,NULL,'SYN_WO_IMPL_RESCUE',
   'ACCEPTED_AFTER_FOUNDER_RESCUE','PASS','SYN_E_ACCEPT',
   '2026-07-12T00:00:00Z','2026-07-12T00:05:00Z');

INSERT INTO delegation_baseline(
  delegation_baseline_id,agreement_version_id,period_code,
  baseline_founder_minutes,baseline_scope_ref_hash,evidence_id,cutoff_at,recorded_at
) VALUES
  ('SYN_BASELINE_IMPL','SYN_AGR_IMPL_1','SYN_2026_07',240,printf('%064x',1521),
   'SYN_E_WORK','2026-07-31T23:59:59Z','2026-08-01T00:05:00Z'),
  ('SYN_BASELINE_REF','SYN_AGR_REF_1','SYN_2026_07',60,printf('%064x',1522),
   'SYN_E_WORK','2026-07-31T23:59:59Z','2026-08-01T00:05:00Z');

INSERT INTO cost_event(
  cost_event_id,agreement_version_id,account_id,work_order_id,
  cost_bearer_party_id,cost_class,
  cash_cost_micros,founder_minutes,founder_time_component,source_work_event_id,
  currency,evidence_id,occurred_at,recorded_at
) VALUES
  ('SYN_COST_REF_TOOL','SYN_AGR_REF_1','SYN_ACC_REF',NULL,'SYN_PRINCIPAL',
   'CONTENT_OR_TOOL',
   200000000,NULL,NULL,NULL,'JPY','SYN_E_WORK',
   '2026-07-01T00:00:00Z','2026-07-01T00:05:00Z'),
  ('SYN_TIME_REF_SOURCE','SYN_AGR_REF_1','SYN_ACC_REF',NULL,'SYN_PRINCIPAL',
   'FOUNDER_TIME',
   NULL,30,'SOURCING_DILIGENCE_CONTRACTING',NULL,'JPY','SYN_E_WORK',
   '2026-07-01T00:00:00Z','2026-07-01T00:05:00Z'),
  ('SYN_TIME_IMPL_SOURCE','SYN_AGR_IMPL_1','SYN_ACC_IMPL',NULL,'SYN_PRINCIPAL',
   'FOUNDER_TIME',
   NULL,30,'SOURCING_DILIGENCE_CONTRACTING',NULL,'JPY','SYN_E_WORK',
   '2026-06-20T00:00:00Z','2026-06-20T00:05:00Z'),
  ('SYN_TIME_IMPL_REVIEW','SYN_AGR_IMPL_1','SYN_ACC_IMPL','SYN_WO_IMPL_RESCUE',
   'SYN_PRINCIPAL',
   'FOUNDER_TIME',NULL,10,'BRIEFING_COORDINATION_REVIEW',
   'SYN_WORK_FOUNDER_REVIEW','JPY','SYN_E_WORK',
   '2026-07-09T00:00:00Z','2026-07-09T00:05:00Z'),
  ('SYN_TIME_IMPL_RESIDUAL','SYN_AGR_IMPL_1','SYN_ACC_IMPL','SYN_WO_IMPL_RESCUE',
   'SYN_PRINCIPAL',
   'FOUNDER_TIME',NULL,20,'RESIDUAL_EXECUTION','SYN_WORK_FOUNDER_RESIDUAL',
   'JPY','SYN_E_WORK','2026-07-10T00:00:00Z','2026-07-10T00:05:00Z'),
  ('SYN_TIME_IMPL_RESCUE','SYN_AGR_IMPL_1','SYN_ACC_IMPL','SYN_WO_IMPL_RESCUE',
   'SYN_PRINCIPAL',
   'FOUNDER_TIME',NULL,15,'RESCUE','SYN_WORK_FOUNDER_RESCUE','JPY','SYN_E_WORK',
   '2026-07-11T00:00:00Z','2026-07-11T00:05:00Z'),
  ('SYN_TIME_IMPL_ADMIN','SYN_AGR_IMPL_1','SYN_ACC_IMPL',NULL,'SYN_PRINCIPAL',
   'FOUNDER_TIME',
   NULL,5,'ADMIN_OFFBOARDING',NULL,'JPY','SYN_E_WORK',
   '2026-07-25T00:00:00Z','2026-07-25T00:05:00Z');

INSERT INTO commercial_account(
  account_id,customer_party_id,account_ref_hash,segment_code,created_at,recorded_at
) VALUES
  ('SYN_ACC_REF','SYN_CUSTOMER_REF',printf('%064x',1101),'SYN_SMB',
   '2026-06-10T00:00:00Z','2026-06-10T00:05:00Z'),
  ('SYN_ACC_IMPL','SYN_CUSTOMER_IMPL',printf('%064x',1102),'SYN_SMB',
   '2026-06-10T00:00:00Z','2026-06-10T00:05:00Z'),
  ('SYN_ACC_RES','SYN_CUSTOMER_RES',printf('%064x',1103),'SYN_MID',
   '2026-06-10T00:00:00Z','2026-06-10T00:05:00Z'),
  ('SYN_ACC_CONTROL','SYN_CUSTOMER_CONTROL',printf('%064x',1104),'SYN_SMB',
   '2026-06-10T00:00:00Z','2026-06-10T00:05:00Z');

INSERT INTO evidence_record(
  evidence_id,evidence_class,evidence_domain,evidence_status,source_ref_hash,
  occurred_at,recorded_at,valid_until,reviewed_by_role
)
WITH f(evidence_id,evidence_class,evidence_domain,evidence_status,n,valid_until) AS (VALUES
  ('SYN_E_POLICY','E2_PROGRAM_CONTROL_PLANE','POLICY','CONFIRMED',1201,'2027-01-01T00:00:00Z'),
  ('SYN_E_POLICY_UNKNOWN','E1_MANUAL','POLICY','UNKNOWN',1202,'2026-09-01T00:00:00Z'),
  ('SYN_E_LEGAL','E4_FIRST_PARTY_AUTHORITATIVE','LEGAL','CONFIRMED',1203,'2027-01-01T00:00:00Z'),
  ('SYN_E_LEGAL_UNKNOWN','E1_MANUAL','LEGAL','UNKNOWN',1204,'2026-09-01T00:00:00Z'),
  ('SYN_E_AUTH','E4_FIRST_PARTY_AUTHORITATIVE','AUTHORITY','CONFIRMED',1205,'2027-01-01T00:00:00Z'),
  ('SYN_E_RESP','E4_FIRST_PARTY_AUTHORITATIVE','RESPONSIBILITY','CONFIRMED',1206,'2027-01-01T00:00:00Z'),
  ('SYN_E_PRIVACY','E4_FIRST_PARTY_AUTHORITATIVE','PRIVACY','CONFIRMED',1207,'2027-01-01T00:00:00Z'),
  ('SYN_E_SECURITY','E4_FIRST_PARTY_AUTHORITATIVE','SECURITY','CONFIRMED',1208,'2027-01-01T00:00:00Z'),
  ('SYN_E_PAYMENT','E4_FIRST_PARTY_AUTHORITATIVE','PAYMENT','CONFIRMED',1209,'2027-01-01T00:00:00Z'),
  ('SYN_E_IP','E4_FIRST_PARTY_AUTHORITATIVE','AUTHORITY','CONFIRMED',1210,'2027-01-01T00:00:00Z'),
  ('SYN_E_TERMINATION','E4_FIRST_PARTY_AUTHORITATIVE','TERMINATION','CONFIRMED',1211,'2027-01-01T00:00:00Z'),
  ('SYN_E_CLASS','E4_FIRST_PARTY_AUTHORITATIVE','LEGAL','CONFIRMED',1212,'2027-01-01T00:00:00Z'),
  ('SYN_E_CONSENT','E5_CUSTOMER_OR_BANK_AUTHORITATIVE','LEAD_CONSENT','CONFIRMED',1213,'2027-01-01T00:00:00Z'),
  ('SYN_E_DISCLOSURE','E4_FIRST_PARTY_AUTHORITATIVE','LEAD_DISCLOSURE','CONFIRMED',1214,'2027-01-01T00:00:00Z'),
  ('SYN_E_PROVIDER_ACCEPT','E3_PROVIDER_EVENT','PROVIDER_ACCEPTANCE','CONFIRMED',1215,'2027-01-01T00:00:00Z'),
  ('SYN_E_BANK','E5_CUSTOMER_OR_BANK_AUTHORITATIVE','BANK','CONFIRMED',1216,'2027-01-01T00:00:00Z'),
  ('SYN_E_WORK','E4_FIRST_PARTY_AUTHORITATIVE','WORK','CONFIRMED',1217,'2027-01-01T00:00:00Z'),
  ('SYN_E_ACCEPT','E5_CUSTOMER_OR_BANK_AUTHORITATIVE','QUALITY','CONFIRMED',1218,'2027-01-01T00:00:00Z'),
  ('SYN_E_TIER','E2_PROGRAM_CONTROL_PLANE','TIER','CONFIRMED',1219,'2027-01-01T00:00:00Z'),
  ('SYN_E_EXPERIMENT','E4_FIRST_PARTY_AUTHORITATIVE','EXPERIMENT','CONFIRMED',1220,'2027-01-01T00:00:00Z'),
  ('SYN_E_HANDOFF','E4_FIRST_PARTY_AUTHORITATIVE','HANDOFF','CONFIRMED',1221,'2027-01-01T00:00:00Z'),
  ('SYN_E_PAUSE','E1_MANUAL','AUTHORITY','UNKNOWN',1222,'2026-09-01T00:00:00Z')
)
SELECT evidence_id,evidence_class,evidence_domain,evidence_status,
       printf('%064x',n),'2026-06-15T00:00:00Z','2026-06-15T00:05:00Z',
       valid_until,'SYN_REVIEWER'
FROM f;

INSERT INTO policy_snapshot(
  policy_snapshot_id,provider_party_id,program_code,policy_version,source_url,
  source_content_hash,availability_state,snapshot_status,effective_from,
  effective_until,captured_at,evidence_id
) VALUES
  ('SYN_POLICY_CURRENT','SYN_PROVIDER','SYN_PARTNER_PROGRAM','2026-07-01',
   'https://example.invalid/synthetic-partner-policy',printf('%064x',1301),
   'PUBLIC','CURRENT','2026-07-01T00:00:00Z',NULL,
   '2026-06-15T00:00:00Z','SYN_E_POLICY'),
  ('SYN_POLICY_OEM_UNKNOWN','SYN_PROVIDER','SYN_OEM_PROGRAM','UNKNOWN',
   'https://example.invalid/synthetic-oem-policy',printf('%064x',1302),
   'UNKNOWN','UNKNOWN','2026-07-01T00:00:00Z',NULL,
   '2026-06-15T00:00:00Z','SYN_E_POLICY_UNKNOWN');

INSERT INTO agreement_version(
  agreement_version_id,agreement_stream_id,version_sequence,
  supersedes_agreement_version_id,motion_code,principal_party_id,partner_party_id,
  policy_snapshot_id,governing_law_code,territory_code,commercial_status,
  legal_review_status,authority_review_status,worker_classification_status,
  privacy_review_status,security_review_status,payment_review_status,
  ip_license_review_status,termination_review_status,effective_from,
  effective_until,currency,agreement_ref_hash,approved_at,recorded_at
)
WITH f(id,stream,motion,partner,policy,status,legal,authority,worker,privacy,
       security,payment,ip,termination,n) AS (VALUES
  ('SYN_AGR_OUT_1','SYN_AGR_OUT','OUTSOURCING','SYN_PARTNER_OUT',NULL,
   'PILOT','PASS','PASS','PASS','PASS','PASS','PASS','PASS','PASS',1401),
  ('SYN_AGR_EMP_1','SYN_AGR_EMP','EMPLOYMENT','SYN_WORKER_EMP',NULL,
   'PILOT','PASS','PASS','PASS','PASS','PASS','PASS','PASS','PASS',1402),
  ('SYN_AGR_REF_1','SYN_AGR_REF','REFERRAL','SYN_PARTNER_REF','SYN_POLICY_CURRENT',
   'LIVE','PASS','PASS','NA','PASS','PASS','PASS','NA','PASS',1403),
  ('SYN_AGR_AFF_1','SYN_AGR_AFF','AFFILIATE','SYN_PARTNER_AFF','SYN_POLICY_CURRENT',
   'PILOT','PASS','PASS','NA','PASS','PASS','PASS','NA','PASS',1404),
  ('SYN_AGR_AGENT_1','SYN_AGR_AGENT','SALES_AGENT','SYN_PARTNER_AGENT','SYN_POLICY_CURRENT',
   'PAUSE','PASS','UNKNOWN','NA','PASS','PASS','PASS','NA','PASS',1405),
  ('SYN_AGR_RES_1','SYN_AGR_RES','RESELLER','SYN_PARTNER_RES','SYN_POLICY_CURRENT',
   'PILOT','PASS','PASS','NA','PASS','PASS','PASS','PASS','PASS',1406),
  ('SYN_AGR_IMPL_1','SYN_AGR_IMPL','IMPLEMENTATION_SUPPORT','SYN_PARTNER_IMPL',NULL,
   'LIVE','PASS','PASS','NA','PASS','PASS','PASS','PASS','PASS',1407),
  ('SYN_AGR_WHITE_1','SYN_AGR_WHITE','WHITE_LABEL','SYN_PARTNER_WHITE','SYN_POLICY_CURRENT',
   'PILOT','PASS','PASS','NA','PASS','PASS','PASS','PASS','PASS',1408),
  ('SYN_AGR_OEM_1','SYN_AGR_OEM','OEM','SYN_PARTNER_OEM','SYN_POLICY_OEM_UNKNOWN',
   'PAUSE','UNKNOWN','UNKNOWN','NA','UNKNOWN','PASS','UNKNOWN','UNKNOWN','UNKNOWN',1409)
)
SELECT id,stream,1,NULL,motion,'SYN_PRINCIPAL',partner,policy,'SYN_LAW','JP',status,
       legal,authority,worker,privacy,security,payment,ip,termination,
       '2026-07-01T00:00:00Z',NULL,'JPY',printf('%064x',n),
       '2026-06-20T00:00:00Z','2026-06-20T00:05:00Z'
FROM f;

INSERT INTO lead_source_event(
  lead_source_event_id,agreement_version_id,account_id,source_kind,
  consent_status,disclosure_status,consent_evidence_id,disclosure_evidence_id,
  source_ref_hash,occurred_at,recorded_at
) VALUES
  ('SYN_LEAD_REF','SYN_AGR_REF_1','SYN_ACC_REF','WARM_INTRO','PASS','NA',
   'SYN_E_CONSENT',NULL,printf('%064x',1601),
   '2026-07-03T00:00:00Z','2026-07-03T00:05:00Z'),
  ('SYN_LEAD_AFF_UNKNOWN','SYN_AGR_AFF_1','SYN_ACC_CONTROL','TRACKED_LINK',
   'PASS','UNKNOWN','SYN_E_CONSENT',NULL,printf('%064x',1602),
   '2026-07-03T00:00:00Z','2026-07-03T00:05:00Z');

INSERT INTO lead_registration_event(
  registration_event_id,registration_stream_id,event_sequence,
  supersedes_registration_event_id,lead_source_event_id,agreement_version_id,
  policy_snapshot_id,account_id,product_scope_code,territory_code,
  registration_status,provider_registration_ref_hash,exclusivity_until,
  provider_evidence_id,occurred_at,recorded_at
) VALUES
  ('SYN_REG_REF_SUBMITTED','SYN_REG_REF',1,NULL,'SYN_LEAD_REF','SYN_AGR_REF_1',
   'SYN_POLICY_CURRENT','SYN_ACC_REF','SYN_PRODUCT','JP','SUBMITTED',
   printf('%064x',1611),NULL,NULL,
   '2026-07-04T00:00:00Z','2026-07-04T00:05:00Z'),
  ('SYN_REG_REF_ACCEPTED','SYN_REG_REF',2,'SYN_REG_REF_SUBMITTED',
   'SYN_LEAD_REF','SYN_AGR_REF_1','SYN_POLICY_CURRENT','SYN_ACC_REF',
   'SYN_PRODUCT','JP','ACCEPTED',printf('%064x',1611),
   '2026-08-31T00:00:00Z','SYN_E_PROVIDER_ACCEPT',
   '2026-07-05T00:00:00Z','2026-07-05T00:05:00Z'),
  ('SYN_REG_AFF_PENDING','SYN_REG_AFF',1,NULL,'SYN_LEAD_AFF_UNKNOWN',
   'SYN_AGR_AFF_1','SYN_POLICY_CURRENT','SYN_ACC_CONTROL','SYN_PRODUCT','JP',
   'SUBMITTED',printf('%064x',1612),NULL,NULL,
   '2026-07-05T00:00:00Z','2026-07-05T00:05:00Z');

INSERT INTO attribution_rule_version(
  attribution_rule_version_id,rule_stream_id,rule_sequence,
  supersedes_rule_version_id,policy_snapshot_id,winner_rule_code,lookback_days,
  duplicate_window_days,affiliate_partner_mutually_exclusive,effective_from,
  effective_until,evidence_id,recorded_at
) VALUES (
  'SYN_ATTR_RULE_1','SYN_ATTR_RULE',1,NULL,'SYN_POLICY_CURRENT',
  'PROVIDER_DECISION',180,60,1,'2026-07-01T00:00:00Z',NULL,
  'SYN_E_POLICY','2026-06-20T00:20:00Z'
);

INSERT INTO commission_rule_version(
  commission_rule_version_id,commission_rule_stream_id,rule_sequence,
  supersedes_rule_version_id,policy_snapshot_id,eligible_motion_code,base_code,
  calculation_code,rate_ppm,fixed_amount_micros,net_deduction_code,locking_days,
  clawback_days,renewal_months,tail_days,payout_threshold_micros,
  payout_cadence_code,effective_from,effective_until,currency,evidence_id,recorded_at
) VALUES
  ('SYN_COMM_RULE_REF','SYN_COMM_RULE_REF_STREAM',1,NULL,'SYN_POLICY_CURRENT',
   'REFERRAL','COLLECTED_NET','RATE',200000,NULL,'REFUNDS_CREDITS_TAXES',0,
   60,12,30,100000000,'MONTHLY','2026-07-01T00:00:00Z',NULL,'JPY',
   'SYN_E_POLICY','2026-06-20T00:20:00Z'),
  ('SYN_COMM_RULE_AGENT','SYN_COMM_RULE_AGENT_STREAM',1,NULL,'SYN_POLICY_CURRENT',
   'SALES_AGENT','COLLECTED_NET','RATE',100000,NULL,'PROGRAM_DEFINED',30,
   90,12,30,100000000,'QUARTERLY','2026-07-01T00:00:00Z',NULL,'JPY',
   'SYN_E_POLICY','2026-06-20T00:20:00Z');

INSERT INTO bank_evidence(
  bank_evidence_id,evidence_type,direction_code,account_owner_party_id,
  counterparty_party_id,amount_micros,currency,
  reconciliation_status,bank_ref_hash,occurred_at,recorded_at
) VALUES
  ('SYN_BANK_REF_CUSTOMER','CUSTOMER_COLLECTION','INFLOW','SYN_PRINCIPAL',
   'SYN_CUSTOMER_REF',10000000000,'JPY',
   'RECONCILED',printf('%064x',1701),'2026-07-20T00:00:00Z',
   '2026-07-20T00:05:00Z'),
  ('SYN_BANK_REF_PAYOUT','PARTNER_PAYOUT','OUTFLOW','SYN_PRINCIPAL',
   'SYN_PARTNER_REF',1500000000,'JPY',
   'RECONCILED',printf('%064x',1702),'2026-07-31T00:00:00Z',
   '2026-07-31T00:05:00Z'),
  ('SYN_BANK_RES_DOWN','CUSTOMER_COLLECTION','INFLOW','SYN_PARTNER_RES',
   'SYN_CUSTOMER_RES',6000000000,'JPY',
   'RECONCILED',printf('%064x',1703),'2026-07-22T00:00:00Z',
   '2026-07-22T00:05:00Z'),
  ('SYN_BANK_RES_UP','UPSTREAM_PAYMENT','INFLOW','SYN_PRINCIPAL',
   'SYN_PARTNER_RES',4000000000,'JPY',
   'RECONCILED',printf('%064x',1704),'2026-07-10T00:00:00Z',
   '2026-07-10T00:05:00Z'),
  ('SYN_BANK_IMPL_SERVICE','SERVICE_COLLECTION','INFLOW','SYN_PARTNER_IMPL',
   'SYN_CUSTOMER_IMPL',5000000000,'JPY',
   'RECONCILED',printf('%064x',1705),'2026-07-20T00:00:00Z',
   '2026-07-20T00:05:00Z');

INSERT INTO commercial_event(
  commercial_event_id,agreement_version_id,account_id,event_type,
  payer_party_id,payee_party_id,amount_micros,
  currency,bank_evidence_id,adjusts_commercial_event_id,source_ref_hash,
  occurred_at,recorded_at
) VALUES
  ('SYN_CE_REF_CASH','SYN_AGR_REF_1','SYN_ACC_REF','CUSTOMER_CASH',
   'SYN_CUSTOMER_REF','SYN_PRINCIPAL',
   10000000000,'JPY','SYN_BANK_REF_CUSTOMER',NULL,printf('%064x',1711),
   '2026-07-20T00:00:00Z','2026-07-20T00:05:00Z'),
  ('SYN_CE_REF_REFUND','SYN_AGR_REF_1','SYN_ACC_REF','REFUND',
   'SYN_PRINCIPAL','SYN_CUSTOMER_REF',2500000000,
   'JPY',NULL,'SYN_CE_REF_CASH',printf('%064x',1712),'2026-07-25T00:00:00Z',
   '2026-07-25T00:05:00Z'),
  ('SYN_CE_RES_BOOK','SYN_AGR_RES_1','SYN_ACC_RES','BOOKING',
   'SYN_CUSTOMER_RES','SYN_PARTNER_RES',7000000000,
   'JPY',NULL,NULL,printf('%064x',1713),'2026-07-06T00:00:00Z',
   '2026-07-06T00:05:00Z'),
  ('SYN_CE_RES_DOWN_CASH','SYN_AGR_RES_1','SYN_ACC_RES','CUSTOMER_CASH',
   'SYN_CUSTOMER_RES','SYN_PARTNER_RES',
   6000000000,'JPY','SYN_BANK_RES_DOWN',NULL,printf('%064x',1714),
   '2026-07-22T00:00:00Z','2026-07-22T00:05:00Z'),
  ('SYN_CE_RES_UP_OBL','SYN_AGR_RES_1','SYN_ACC_RES','WHOLESALE_OBLIGATION',
   'SYN_PARTNER_RES','SYN_PRINCIPAL',
   4000000000,'JPY',NULL,NULL,printf('%064x',1715),'2026-07-07T00:00:00Z',
   '2026-07-07T00:05:00Z'),
  ('SYN_CE_RES_UP_CASH','SYN_AGR_RES_1','SYN_ACC_RES','WHOLESALE_CASH',
   'SYN_PARTNER_RES','SYN_PRINCIPAL',
   4000000000,'JPY','SYN_BANK_RES_UP',NULL,printf('%064x',1716),
   '2026-07-10T00:00:00Z','2026-07-10T00:05:00Z'),
  ('SYN_CE_IMPL_CASH','SYN_AGR_IMPL_1','SYN_ACC_IMPL','SERVICE_CASH',
   'SYN_CUSTOMER_IMPL','SYN_PARTNER_IMPL',
   5000000000,'JPY','SYN_BANK_IMPL_SERVICE',NULL,printf('%064x',1717),
   '2026-07-20T00:00:00Z','2026-07-20T00:05:00Z');

INSERT INTO attribution_decision(
  attribution_decision_id,commercial_event_id,attribution_rule_version_id,
  registration_event_id,attribution_role,causal_claim,decision_evidence_id,
  decided_at,recorded_at
) VALUES (
  'SYN_ATTR_REF_WIN','SYN_CE_REF_CASH','SYN_ATTR_RULE_1',
  'SYN_REG_REF_ACCEPTED','WINNER',0,'SYN_E_PROVIDER_ACCEPT',
  '2026-07-20T00:10:00Z','2026-07-20T00:15:00Z'
);

INSERT INTO commission_event(
  commission_event_id,agreement_version_id,commission_rule_version_id,
  attribution_decision_id,event_type,base_commercial_event_id,
  source_accrual_event_id,bank_evidence_id,eligible_base_micros,amount_micros,
  currency,tail_eligible,reason_code,evidence_id,occurred_at,recorded_at
) VALUES
  ('SYN_COMM_REF_ACCRUAL','SYN_AGR_REF_1','SYN_COMM_RULE_REF',
   'SYN_ATTR_REF_WIN','ACCRUED','SYN_CE_REF_CASH',NULL,NULL,
   10000000000,2000000000,'JPY',0,'SYN_VALID_ACCRUAL','SYN_E_PROVIDER_ACCEPT',
   '2026-07-21T00:00:00Z','2026-07-21T00:05:00Z'),
  ('SYN_COMM_REF_REVERSAL','SYN_AGR_REF_1','SYN_COMM_RULE_REF',NULL,
   'REVERSED',NULL,'SYN_COMM_REF_ACCRUAL',NULL,10000000000,500000000,
   'JPY',0,'SYN_REFUND_CLAWBACK','SYN_E_PROVIDER_ACCEPT',
   '2026-07-26T00:00:00Z','2026-07-26T00:05:00Z'),
  ('SYN_COMM_REF_PAID','SYN_AGR_REF_1','SYN_COMM_RULE_REF',NULL,
   'PAID',NULL,'SYN_COMM_REF_ACCRUAL','SYN_BANK_REF_PAYOUT',10000000000,
   1500000000,'JPY',0,'SYN_BANK_PAYOUT','SYN_E_BANK',
   '2026-07-31T00:00:00Z','2026-07-31T00:05:00Z');

INSERT INTO reseller_order(
  reseller_order_id,agreement_version_id,account_id,downstream_booking_event_id,
  downstream_cash_event_id,upstream_obligation_event_id,upstream_cash_event_id,
  entitlement_status,customer_credit_risk_owner_party_id,
  refund_pass_through_status,evidence_id,created_at,recorded_at
) VALUES (
  'SYN_RES_ORDER_1','SYN_AGR_RES_1','SYN_ACC_RES','SYN_CE_RES_BOOK',
  'SYN_CE_RES_DOWN_CASH','SYN_CE_RES_UP_OBL','SYN_CE_RES_UP_CASH','ACTIVE',
  'SYN_PARTNER_RES','PASS','SYN_E_PAYMENT','2026-07-07T00:00:00Z',
  '2026-07-07T00:05:00Z'
);

INSERT INTO partner_experiment(
  experiment_id,motion_code,hypothesis_ref_hash,assignment_unit_code,
  eligibility_rule_version,assignment_seed_ref_hash,primary_outcome_code,
  starts_at,exposure_cutoff_at,maturity_at,snapshot_as_of,evidence_id,recorded_at
) VALUES (
  'SYN_EXP_REF','REFERRAL',printf('%064x',1801),'ACCOUNT_CLUSTER',
  'SYN_ELIG_V1',printf('%064x',1802),'BANK_CONTRIBUTION',
  '2026-07-01T00:00:00Z','2026-07-15T00:00:00Z',
  '2026-07-31T00:00:00Z','2026-08-02T12:00:00Z','SYN_E_EXPERIMENT',
  '2026-06-25T00:05:00Z'
);

INSERT INTO experiment_assignment(
  assignment_id,experiment_id,assignment_unit_ref_hash,arm_code,assigned_at,
  first_exposure_at,contamination_status,assignment_evidence_id,recorded_at
) VALUES
  ('SYN_EXP_ASSIGN_T','SYN_EXP_REF',printf('%064x',1811),'TREATMENT',
   '2026-06-25T00:00:00Z','2026-07-01T00:00:00Z','CLEAN',
   'SYN_E_EXPERIMENT','2026-06-25T00:05:00Z'),
  ('SYN_EXP_ASSIGN_C','SYN_EXP_REF',printf('%064x',1812),'CONTROL',
   '2026-06-25T00:00:00Z',NULL,'CLEAN','SYN_E_EXPERIMENT',
   '2026-06-25T00:05:00Z');

INSERT INTO experiment_outcome(
  experiment_outcome_id,assignment_id,maturity_state,outcome_value_micros,
  currency,evidence_id,occurred_at,recorded_at
) VALUES
  ('SYN_EXP_OUT_T','SYN_EXP_ASSIGN_T','MEASURED_VALUE',1300000000,'JPY',
   'SYN_E_EXPERIMENT','2026-07-31T00:00:00Z','2026-08-01T00:05:00Z'),
  ('SYN_EXP_OUT_C','SYN_EXP_ASSIGN_C','MEASURED_ZERO',0,'JPY',
   'SYN_E_EXPERIMENT','2026-07-31T00:00:00Z','2026-08-01T00:05:00Z');

INSERT INTO termination_case(
  termination_case_id,agreement_version_id,notice_at,effective_at,
  initiated_by_party_id,contractual_tail_status,governing_law_tail_status,
  evidence_id,recorded_at
) VALUES
  ('SYN_TERM_OUT','SYN_AGR_OUT_1','2026-07-10T00:00:00Z',
   '2026-07-20T00:00:00Z','SYN_PRINCIPAL','NA','NA','SYN_E_TERMINATION',
   '2026-07-10T00:05:00Z'),
  ('SYN_TERM_IMPL_OPEN','SYN_AGR_IMPL_1','2026-07-25T00:00:00Z',
   '2026-07-31T00:00:00Z','SYN_PRINCIPAL','NA','NA','SYN_E_TERMINATION',
   '2026-07-25T00:05:00Z');

INSERT INTO handoff_item_event(
  handoff_item_event_id,handoff_stream_id,event_sequence,
  supersedes_handoff_item_event_id,termination_case_id,item_code,item_status,
  owner_party_id,evidence_id,occurred_at,recorded_at
)
SELECT 'SYN_HO_OUT_'||item_code,'SYN_HO_OUT_'||item_code,1,NULL,
       'SYN_TERM_OUT',item_code,'PASS','SYN_PRINCIPAL','SYN_E_HANDOFF',
       '2026-07-20T00:00:00Z','2026-07-20T00:05:00Z'
FROM handoff_item_dim WHERE required_for_all=1;

INSERT INTO handoff_item_event(
  handoff_item_event_id,handoff_stream_id,event_sequence,
  supersedes_handoff_item_event_id,termination_case_id,item_code,item_status,
  owner_party_id,evidence_id,occurred_at,recorded_at
) VALUES (
  'SYN_HO_IMPL_DATA_OPEN','SYN_HO_IMPL_DATA',1,NULL,'SYN_TERM_IMPL_OPEN',
  'DATA_DISPOSITION','OPEN',NULL,NULL,'2026-07-31T00:00:00Z',
  '2026-07-31T00:05:00Z'
);

INSERT INTO termination_event(
  termination_event_id,termination_stream_id,event_sequence,
  supersedes_termination_event_id,termination_case_id,state_code,evidence_id,
  occurred_at,recorded_at
) VALUES
  ('SYN_TERM_OUT_NOTICE','SYN_TERM_OUT_STREAM',1,NULL,'SYN_TERM_OUT',
   'NOTICE_RECORDED','SYN_E_TERMINATION','2026-07-10T00:00:00Z',
   '2026-07-10T00:05:00Z'),
  ('SYN_TERM_OUT_EFFECTIVE','SYN_TERM_OUT_STREAM',2,'SYN_TERM_OUT_NOTICE',
   'SYN_TERM_OUT','TERMINATED','SYN_E_TERMINATION','2026-07-20T00:00:00Z',
   '2026-07-20T00:05:00Z'),
  ('SYN_TERM_OUT_CLOSED','SYN_TERM_OUT_STREAM',3,'SYN_TERM_OUT_EFFECTIVE',
   'SYN_TERM_OUT','HANDOFF_CLOSED','SYN_E_HANDOFF','2026-07-21T00:00:00Z',
   '2026-07-21T00:05:00Z'),
  ('SYN_TERM_IMPL_NOTICE','SYN_TERM_IMPL_STREAM',1,NULL,'SYN_TERM_IMPL_OPEN',
   'NOTICE_RECORDED','SYN_E_TERMINATION','2026-07-25T00:00:00Z',
   '2026-07-25T00:05:00Z');

INSERT INTO decision_snapshot(
  decision_snapshot_id,agreement_version_id,decision_sequence,
  supersedes_decision_snapshot_id,decision_code,action_code,
  evidence_cutoff_at,snapshot_as_of,
  legal_state,responsibility_state,authority_state,access_state,quality_state,
  cash_state,incrementality_state,termination_state,decision_evidence_id,
  decided_by_role,decided_at,recorded_at
) VALUES
  ('SYN_DECISION_REF_LIVE','SYN_AGR_REF_1',1,NULL,'LIVE','INCONCLUSIVE',
   '2026-07-31T23:59:59Z','2026-08-01T00:00:00Z','PASS','PASS','PASS','NA',
   'NA','PASS','INCONCLUSIVE','NA','SYN_E_PROVIDER_ACCEPT','SYN_FOUNDER',
   '2026-08-01T01:00:00Z','2026-08-01T01:05:00Z'),
  ('SYN_DECISION_OEM_PAUSE','SYN_AGR_OEM_1',1,NULL,'PAUSE','PAUSE',
   '2026-07-31T23:59:59Z','2026-08-01T00:00:00Z','UNKNOWN','UNKNOWN',
   'UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','SYN_E_PAUSE',
   'SYN_FOUNDER','2026-08-01T01:00:00Z','2026-08-01T01:05:00Z');

INSERT INTO decision_snapshot_metric(
  decision_snapshot_id,metric_code,metric_state,metric_value,evidence_id
) VALUES
  ('SYN_DECISION_REF_LIVE','BANK_REVENUE_MICROS','MEASURED',10000000000,
   'SYN_E_BANK'),
  ('SYN_DECISION_REF_LIVE','FOUNDER_MINUTES','MEASURED',30,'SYN_E_WORK'),
  ('SYN_DECISION_REF_LIVE','FULLY_LOADED_CONTRIBUTION_MICROS','MEASURED',
   2800000000,'SYN_E_WORK'),
  ('SYN_DECISION_REF_LIVE','INCREMENTAL_VALUE_MICROS','INCONCLUSIVE',NULL,NULL),
  ('SYN_DECISION_OEM_PAUSE','BANK_REVENUE_MICROS','UNKNOWN',NULL,NULL),
  ('SYN_DECISION_OEM_PAUSE','FOUNDER_MINUTES','UNKNOWN',NULL,NULL),
  ('SYN_DECISION_OEM_PAUSE','INCREMENTAL_VALUE_MICROS','UNKNOWN',NULL,NULL);

-- Equal-value cash fixtures prove that stable event IDs, not SUM(DISTINCT value),
-- define the commercial denominator.
INSERT INTO bank_evidence(
  bank_evidence_id,evidence_type,direction_code,account_owner_party_id,
  counterparty_party_id,amount_micros,currency,reconciliation_status,
  bank_ref_hash,occurred_at,recorded_at
) VALUES
  ('SYN_BANK_AFF_EQUAL_1','CUSTOMER_COLLECTION','INFLOW','SYN_PRINCIPAL',
   'SYN_CUSTOMER_CONTROL',1000000000,'JPY','RECONCILED',printf('%064x',1901),
   '2026-07-27T00:00:00Z','2026-07-27T00:05:00Z'),
  ('SYN_BANK_AFF_EQUAL_2','CUSTOMER_COLLECTION','INFLOW','SYN_PRINCIPAL',
   'SYN_CUSTOMER_CONTROL',1000000000,'JPY','RECONCILED',printf('%064x',1902),
   '2026-07-28T00:00:00Z','2026-07-28T00:05:00Z');

INSERT INTO commercial_event(
  commercial_event_id,agreement_version_id,account_id,event_type,
  payer_party_id,payee_party_id,amount_micros,currency,bank_evidence_id,
  adjusts_commercial_event_id,source_ref_hash,occurred_at,recorded_at
) VALUES
  ('SYN_CE_AFF_EQUAL_1','SYN_AGR_AFF_1','SYN_ACC_CONTROL','CUSTOMER_CASH',
   'SYN_CUSTOMER_CONTROL','SYN_PRINCIPAL',1000000000,'JPY',
   'SYN_BANK_AFF_EQUAL_1',NULL,printf('%064x',1911),
   '2026-07-27T00:00:00Z','2026-07-27T00:05:00Z'),
  ('SYN_CE_AFF_EQUAL_2','SYN_AGR_AFF_1','SYN_ACC_CONTROL','CUSTOMER_CASH',
   'SYN_CUSTOMER_CONTROL','SYN_PRINCIPAL',1000000000,'JPY',
   'SYN_BANK_AFF_EQUAL_2',NULL,printf('%064x',1912),
   '2026-07-28T00:00:00Z','2026-07-28T00:05:00Z');

-- -----------------------------------------------------------------------------
-- Data-quality and invariant assertions
-- -----------------------------------------------------------------------------

CREATE TEMP VIEW v_assertion_results AS
SELECT 'DQ01' AS check_id,'error' AS severity,'foreign_keys_resolve' AS check_name,
       (SELECT count(*) FROM pragma_foreign_key_check) AS failure_count,
       'Every synthetic reference resolves before COMMIT' AS expectation
UNION ALL SELECT 'DQ02','error','integrity_check_ok',
       (SELECT count(*) FROM pragma_integrity_check WHERE integrity_check<>'ok'),
       'SQLite reports a structurally sound database'
UNION ALL SELECT 'DQ03','error','nine_motion_taxonomy',
       CASE WHEN (SELECT count(*) FROM motion_dim)=9
              AND (SELECT count(*) FROM motion_dim WHERE motion_code IN (
                'OUTSOURCING','EMPLOYMENT','REFERRAL','AFFILIATE','SALES_AGENT',
                'RESELLER','IMPLEMENTATION_SUPPORT','WHITE_LABEL','OEM'
              ))=9 THEN 0 ELSE 1 END,
       'The v1 taxonomy contains exactly nine explicit motions'
UNION ALL SELECT 'DQ04','error','fixture_covers_every_motion',
       (SELECT count(*) FROM motion_dim m WHERE NOT EXISTS (
          SELECT 1 FROM agreement_version a WHERE a.motion_code=m.motion_code
        )),
       'Every modeled motion has a synthetic agreement fixture'
UNION ALL SELECT 'DQ05','error','stage_contract_complete',
       (SELECT count(*) FROM motion_dim m CROSS JOIN canonical_stage_dim s
        WHERE NOT EXISTS (
          SELECT 1 FROM motion_stage_contract c
          WHERE c.motion_code=m.motion_code AND c.stage_code=s.stage_code
        )),
       'Every motion explicitly marks every canonical stage required optional or NA'
UNION ALL SELECT 'DQ06','error','stage_na_shape',
       (SELECT count(*) FROM motion_stage_contract
        WHERE (applicability='NA' AND (
          minimum_evidence_class IS NOT NULL OR source_of_truth_code IS NOT NULL
        )) OR (applicability<>'NA' AND (
          minimum_evidence_class IS NULL OR source_of_truth_code IS NULL
        ))),
       'NA never masquerades as missing evidence and applicable stages name a source'
UNION ALL SELECT 'DQ07','error','responsibility_profile_complete',
       (SELECT count(*) FROM motion_dim m CROSS JOIN responsibility_domain_dim d
        WHERE NOT EXISTS (
          SELECT 1 FROM motion_responsibility_requirement r
          WHERE r.motion_code=m.motion_code
            AND r.responsibility_code=d.responsibility_code
        )),
       'Every motion profiles every contract money data support and handoff owner axis'
UNION ALL SELECT 'DQ08','error','authority_profile_present',
       (SELECT count(*) FROM motion_dim m WHERE NOT EXISTS (
          SELECT 1 FROM motion_authority_requirement r
          WHERE r.motion_code=m.motion_code
        )),
       'Every motion has at least one explicit authority requirement'
UNION ALL SELECT 'DQ09','error','append_only_evidence_tables',
       (SELECT count(*) FROM (
          SELECT 'party' AS table_name UNION ALL SELECT 'commercial_account'
          UNION ALL SELECT 'evidence_record' UNION ALL SELECT 'policy_snapshot'
          UNION ALL SELECT 'agreement_version' UNION ALL SELECT 'agreement_evidence_link'
          UNION ALL SELECT 'responsibility_assignment' UNION ALL SELECT 'authority_grant'
          UNION ALL SELECT 'tier_rule_version' UNION ALL SELECT 'enablement_evidence'
          UNION ALL SELECT 'access_event' UNION ALL SELECT 'work_order'
          UNION ALL SELECT 'work_event' UNION ALL SELECT 'acceptance_event'
          UNION ALL SELECT 'lead_source_event' UNION ALL SELECT 'lead_registration_event'
          UNION ALL SELECT 'bank_evidence' UNION ALL SELECT 'commercial_event'
          UNION ALL SELECT 'attribution_rule_version'
          UNION ALL SELECT 'attribution_decision'
          UNION ALL SELECT 'commission_rule_version' UNION ALL SELECT 'commission_event'
          UNION ALL SELECT 'reseller_order' UNION ALL SELECT 'cost_event'
          UNION ALL SELECT 'partner_experiment' UNION ALL SELECT 'experiment_assignment'
          UNION ALL SELECT 'experiment_outcome' UNION ALL SELECT 'termination_case'
          UNION ALL SELECT 'handoff_item_event' UNION ALL SELECT 'termination_event'
          UNION ALL SELECT 'decision_snapshot' UNION ALL SELECT 'decision_snapshot_metric'
          UNION ALL SELECT 'delegation_baseline'
        ) required
        WHERE NOT EXISTS (
          SELECT 1 FROM sqlite_schema s WHERE s.type='trigger'
            AND s.tbl_name=required.table_name
            AND s.name LIKE 'immutable_%_update'
        ) OR NOT EXISTS (
          SELECT 1 FROM sqlite_schema s WHERE s.type='trigger'
            AND s.tbl_name=required.table_name
            AND s.name LIKE 'immutable_%_delete'
        )),
       'All material masters versions events and snapshots reject UPDATE and DELETE'
UNION ALL SELECT 'DQ10','error','required_output_views_exist',
       (SELECT count(*) FROM (
          SELECT 'v_current_agreement' AS view_name UNION ALL SELECT 'v_current_access'
          UNION ALL SELECT 'v_agreement_readiness' UNION ALL SELECT 'v_work_quality_economics'
          UNION ALL SELECT 'v_commission_settlement' UNION ALL SELECT 'v_channel_funnel'
          UNION ALL SELECT 'v_agreement_cash_economics'
          UNION ALL SELECT 'v_experiment_readout'
          UNION ALL SELECT 'v_termination_readiness'
          UNION ALL SELECT 'v_current_decision_snapshot'
        ) required WHERE NOT EXISTS (
          SELECT 1 FROM sqlite_schema s
          WHERE s.type='view' AND s.name=required.view_name
        )),
       'The bounded operating query pack is present'
UNION ALL SELECT 'DQ11','error','material_tables_are_strict',
       (SELECT count(*) FROM pragma_table_list
        WHERE schema='main' AND name IN (
          'agreement_version','responsibility_assignment','authority_grant',
          'access_event','work_event','acceptance_event','lead_registration_event',
          'commercial_event','commission_event','experiment_assignment',
          'termination_event','decision_snapshot'
        ) AND strict<>1),
       'Material evidence tables use SQLite STRICT typing'
UNION ALL SELECT 'DQ12','error','no_raw_pii_or_secret_columns',
       (SELECT count(*) FROM sqlite_schema
        WHERE type='table' AND (
          instr(lower(sql),'email_address')>0 OR instr(lower(sql),'phone_number')>0
          OR instr(lower(sql),'person_name')>0 OR instr(lower(sql),'password')>0
          OR instr(lower(sql),'oauth_token')>0 OR instr(lower(sql),'bank_account')>0
        )),
       'The public model stores no obvious raw identity credential or bank-detail field'
UNION ALL SELECT 'DQ13','error','no_future_event_or_recording',
       (SELECT count(*) FROM (
          SELECT occurred_at,recorded_at FROM evidence_record
          UNION ALL SELECT effective_at,recorded_at FROM access_event
          UNION ALL SELECT occurred_at,recorded_at FROM work_event
          UNION ALL SELECT occurred_at,recorded_at FROM acceptance_event
          UNION ALL SELECT occurred_at,recorded_at FROM lead_source_event
          UNION ALL SELECT occurred_at,recorded_at FROM lead_registration_event
          UNION ALL SELECT occurred_at,recorded_at FROM bank_evidence
          UNION ALL SELECT occurred_at,recorded_at FROM commercial_event
          UNION ALL SELECT decided_at,recorded_at FROM attribution_decision
          UNION ALL SELECT occurred_at,recorded_at FROM commission_event
          UNION ALL SELECT occurred_at,recorded_at FROM cost_event
          UNION ALL SELECT occurred_at,recorded_at FROM experiment_outcome
          UNION ALL SELECT occurred_at,recorded_at FROM handoff_item_event
          UNION ALL SELECT occurred_at,recorded_at FROM termination_event
          UNION ALL SELECT decided_at,recorded_at FROM decision_snapshot
        ) x CROSS JOIN model_setting m
        WHERE datetime(x.occurred_at)>datetime(m.reference_time)
           OR datetime(x.recorded_at)>datetime(m.reference_time)),
       'No fixture event or ingest time exceeds the model reference time'
UNION ALL SELECT 'DQ14','error','current_policy_has_confirmed_evidence',
       (SELECT count(*) FROM policy_snapshot p
        JOIN evidence_record e USING (evidence_id)
        WHERE p.snapshot_status='CURRENT' AND e.evidence_status<>'CONFIRMED'),
       'A CURRENT policy snapshot is backed by confirmed evidence'
UNION ALL SELECT 'DQ15','error','live_channel_policy_effective',
       (SELECT count(*) FROM agreement_version a
        LEFT JOIN policy_snapshot p USING (policy_snapshot_id)
        WHERE a.commercial_status='LIVE'
          AND a.motion_code IN (
            'REFERRAL','AFFILIATE','SALES_AGENT','RESELLER','WHITE_LABEL','OEM'
          ) AND (p.policy_snapshot_id IS NULL OR p.snapshot_status<>'CURRENT'
            OR datetime(p.effective_from)>datetime(a.effective_from)
            OR (p.effective_until IS NOT NULL
                AND datetime(a.effective_from)>=datetime(p.effective_until)))),
       'Every live channel or license agreement binds an effective CURRENT policy'
UNION ALL SELECT 'DQ16','error','live_reviews_fail_closed',
       (SELECT count(*) FROM agreement_version
        WHERE commercial_status='LIVE' AND (
          legal_review_status<>'PASS'
          OR authority_review_status NOT IN ('PASS','NA')
          OR privacy_review_status NOT IN ('PASS','NA')
          OR security_review_status NOT IN ('PASS','NA')
          OR payment_review_status NOT IN ('PASS','NA')
          OR ip_license_review_status NOT IN ('PASS','NA')
          OR termination_review_status NOT IN ('PASS','NA')
        )),
       'LIVE never contains FAIL or UNKNOWN legal and control reviews'
UNION ALL SELECT 'DQ17','error','live_review_evidence_domains',
       (SELECT count(*) FROM agreement_version a
        WHERE a.commercial_status='LIVE' AND EXISTS (
          SELECT 1 FROM (
            SELECT 'LEGAL' AS domain_code UNION ALL SELECT 'AUTHORITY'
            UNION ALL SELECT 'PRIVACY' UNION ALL SELECT 'SECURITY'
            UNION ALL SELECT 'PAYMENT' UNION ALL SELECT 'TERMINATION'
          ) required WHERE NOT EXISTS (
            SELECT 1 FROM agreement_evidence_link l
            WHERE l.agreement_version_id=a.agreement_version_id
              AND l.review_domain=required.domain_code
              AND l.link_status='SUPPORTS'
          )
        )),
       'Every live review summary has domain-level supporting evidence'
UNION ALL SELECT 'DQ18','error','agreement_revision_chain',
       (SELECT count(*) FROM agreement_version a
        WHERE (a.version_sequence=1 AND a.supersedes_agreement_version_id IS NOT NULL)
           OR (a.version_sequence>1 AND NOT EXISTS (
             SELECT 1 FROM agreement_version p
             WHERE p.agreement_version_id=a.supersedes_agreement_version_id
               AND p.agreement_stream_id=a.agreement_stream_id
               AND p.version_sequence=a.version_sequence-1
           ))),
       'Agreement versions follow an exact append-only predecessor chain'
UNION ALL SELECT 'DQ19','error','tier_threshold_state_shape',
       (SELECT count(*) FROM tier_rule_version
        WHERE (threshold_state='KNOWN' AND threshold_value IS NULL)
           OR (threshold_state<>'KNOWN' AND threshold_value IS NOT NULL)),
       'Known tier values are numeric; portal-only and unknown thresholds stay NULL'
UNION ALL SELECT 'DQ20','error','portal_only_threshold_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM tier_rule_version
          WHERE tier_rule_version_id='SYN_TIER_OEM_UNKNOWN'
            AND threshold_state='PORTAL_ONLY' AND threshold_value IS NULL
        ) THEN 0 ELSE 1 END,
       'A non-public OEM threshold remains explicit and unguessed'
UNION ALL SELECT 'DQ21','error','enablement_expiry_visible',
       CASE WHEN EXISTS (
          SELECT 1 FROM enablement_evidence e CROSS JOIN model_setting m
          WHERE e.enablement_id='SYN_ENABLE_IMPL_CERT' AND e.result_status='PASS'
            AND datetime(e.expires_at)>datetime(m.reference_time)
        ) THEN 0 ELSE 1 END,
       'Passing enablement evidence is current at the decision time'
UNION ALL SELECT 'DQ22','error','oem_unknown_is_pause',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_agreement_readiness
          WHERE agreement_version_id='SYN_AGR_OEM_1' AND derived_gate='PAUSE'
        ) THEN 0 ELSE 1 END,
       'Unknown OEM legal policy and sublicensing evidence fails closed to PAUSE'
UNION ALL SELECT 'DQ23','error','live_responsibility_matrix_complete',
       (SELECT count(*) FROM v_agreement_readiness
        WHERE commercial_status='LIVE' AND responsibility_gap_count<>0),
       'Every live fixture has a complete explicit responsibility matrix'
UNION ALL SELECT 'DQ24','error','live_responsibility_has_no_unknown',
       (SELECT count(*) FROM responsibility_assignment r
        JOIN agreement_version a USING (agreement_version_id)
        WHERE a.commercial_status='LIVE' AND r.disposition='UNKNOWN'),
       'UNKNOWN owner dispositions cannot survive in a live agreement'
UNION ALL SELECT 'DQ25','error','na_allowed_by_motion_profile',
       (SELECT count(*) FROM responsibility_assignment r
        JOIN agreement_version a USING (agreement_version_id)
        JOIN motion_responsibility_requirement q
          ON q.motion_code=a.motion_code
         AND q.responsibility_code=r.responsibility_code
        WHERE r.disposition='NA' AND q.allow_na<>1),
       'NA is accepted only where the exact motion profile permits inapplicability'
UNION ALL SELECT 'DQ26','error','confirmed_owner_has_evidence',
       (SELECT count(*) FROM responsibility_assignment
        WHERE (disposition='CONFIRMED' AND (owner_party_id IS NULL OR evidence_id IS NULL))
           OR (disposition<>'CONFIRMED' AND owner_party_id IS NOT NULL)),
       'Confirmed owners have evidence; UNKNOWN and NA never invent an owner'
UNION ALL SELECT 'DQ27','error','referral_responsibility_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM responsibility_assignment
          WHERE agreement_version_id='SYN_AGR_REF_1'
            AND responsibility_code='PRODUCT_CONTRACT'
            AND owner_party_id='SYN_PRINCIPAL' AND disposition='CONFIRMED'
        ) AND EXISTS (
          SELECT 1 FROM responsibility_assignment
          WHERE agreement_version_id='SYN_AGR_REF_1'
            AND responsibility_code='LEAD_DATA_CONTROLLER'
            AND owner_party_id='SYN_PARTNER_REF' AND disposition='CONFIRMED'
        ) THEN 0 ELSE 1 END,
       'Referral product contract and lead-data roles remain separate'
UNION ALL SELECT 'DQ28','error','implementation_support_split',
       CASE WHEN EXISTS (
          SELECT 1 FROM responsibility_assignment
          WHERE agreement_version_id='SYN_AGR_IMPL_1'
            AND responsibility_code='SUPPORT_L1'
            AND owner_party_id='SYN_PARTNER_IMPL'
        ) AND EXISTS (
          SELECT 1 FROM responsibility_assignment
          WHERE agreement_version_id='SYN_AGR_IMPL_1'
            AND responsibility_code='SUPPORT_L3'
            AND owner_party_id='SYN_PRINCIPAL'
        ) THEN 0 ELSE 1 END,
       'Implementation L1 and product L3 support are independently assigned'
UNION ALL SELECT 'DQ29','error','merchant_of_record_explicit_for_live',
       (SELECT count(*) FROM agreement_version a
        WHERE a.commercial_status='LIVE' AND NOT EXISTS (
          SELECT 1 FROM responsibility_assignment r
          WHERE r.agreement_version_id=a.agreement_version_id
            AND r.responsibility_code='MERCHANT_OF_RECORD'
            AND r.disposition='CONFIRMED'
        )),
       'Brand or motion never substitutes for an explicit merchant-of-record owner'
UNION ALL SELECT 'DQ30','error','data_roles_explicit_for_live',
       (SELECT count(*) FROM agreement_version a
        WHERE a.commercial_status='LIVE' AND EXISTS (
          SELECT 1 FROM (
            SELECT 'PRODUCT_DATA_CONTROLLER' AS code
            UNION ALL SELECT 'LEAD_DATA_CONTROLLER'
            UNION ALL SELECT 'DATA_PROCESSOR'
          ) required WHERE NOT EXISTS (
            SELECT 1 FROM responsibility_assignment r
            WHERE r.agreement_version_id=a.agreement_version_id
              AND r.responsibility_code=required.code
              AND r.disposition IN ('CONFIRMED','NA')
          )
        )),
       'Product lead and processor data roles each have an explicit disposition'
UNION ALL SELECT 'DQ31','error','live_authority_profile_complete',
       (SELECT count(*) FROM v_agreement_readiness
        WHERE commercial_status='LIVE' AND authority_gap_count<>0),
       'Every live motion satisfies its grant deny or dispose authority profile'
UNION ALL SELECT 'DQ32','error','referrer_cannot_bind_principal',
       CASE WHEN EXISTS (
          SELECT 1 FROM authority_grant
          WHERE agreement_version_id='SYN_AGR_REF_1'
            AND authority_code='INTRODUCE_LEAD' AND disposition='GRANTED'
        ) AND EXISTS (
          SELECT 1 FROM authority_grant
          WHERE agreement_version_id='SYN_AGR_REF_1'
            AND authority_code='BIND_PRINCIPAL' AND disposition='DENIED'
        ) THEN 0 ELSE 1 END,
       'A referral introduction grant does not create agency authority'
UNION ALL SELECT 'DQ33','error','reseller_commercial_rights_explicit',
       CASE WHEN (SELECT count(*) FROM authority_grant
          WHERE agreement_version_id='SYN_AGR_RES_1'
            AND authority_code IN (
              'SET_DOWNSTREAM_PRICE','INVOICE_CUSTOMER','COLLECT_CUSTOMER_CASH'
            ) AND disposition='GRANTED')=3 THEN 0 ELSE 1 END,
       'Reseller price invoice and collection rights are explicit separate grants'
UNION ALL SELECT 'DQ34','error','oem_rights_not_inferred',
       CASE WHEN (SELECT count(*) FROM authority_grant
          WHERE agreement_version_id='SYN_AGR_OEM_1'
            AND authority_code IN (
              'USE_WHITE_LABEL_BRAND','SUBLICENSE_PRODUCT','BUNDLE_PRODUCT'
            ) AND disposition='UNKNOWN')=3 THEN 0 ELSE 1 END,
       'White-label sublicensing and bundling rights remain independently unknown'
UNION ALL SELECT 'DQ35','error','sales_agent_unknown_authority_pause',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_agreement_readiness
          WHERE agreement_version_id='SYN_AGR_AGENT_1' AND derived_gate='PAUSE'
        ) THEN 0 ELSE 1 END,
       'An agent label cannot bypass missing negotiation and binding authority'
UNION ALL SELECT 'DQ36','error','access_grants_are_scoped_and_authorized',
       (SELECT count(*) FROM access_event
        WHERE event_type='GRANTED' AND (
          customer_authorization_status<>'PASS' OR least_privilege_status<>'PASS'
          OR expires_at IS NULL OR length(system_scope_ref_hash)<>64
        )),
       'Every access grant is customer-authorized least-privilege scoped and expiring'
UNION ALL SELECT 'DQ37','error','access_revision_chain',
       (SELECT count(*) FROM access_event a
        WHERE (a.event_sequence=1 AND a.supersedes_access_event_id IS NOT NULL)
           OR (a.event_sequence>1 AND NOT EXISTS (
             SELECT 1 FROM access_event p
             WHERE p.access_event_id=a.supersedes_access_event_id
               AND p.access_stream_id=a.access_stream_id
               AND p.event_sequence=a.event_sequence-1
           ))),
       'Access revoke or expiry follows the exact grant stream'
UNION ALL SELECT 'DQ38','error','prohibited_special_category_access_absent',
       (SELECT count(*) FROM access_event
        WHERE event_type='GRANTED' AND data_class_code='SPECIAL_CATEGORY_PROHIBITED'),
       'The synthetic live model grants no prohibited special-category access'
UNION ALL SELECT 'DQ39','error','implementation_access_is_visible',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_current_access
          WHERE access_stream_id='SYN_ACCESS_IMPL' AND access_state='OPEN'
        ) THEN 0 ELSE 1 END,
       'Current scoped implementation access is visible rather than inferred'
UNION ALL SELECT 'DQ40','error','open_access_blocks_offboarding',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_termination_readiness
          WHERE termination_case_id='SYN_TERM_IMPL_OPEN'
            AND open_access_count=1 AND unresolved_handoff_count>0
            AND closure_gate='PAUSE'
        ) THEN 0 ELSE 1 END,
       'Open access and data disposition keep the implementation handoff paused'
UNION ALL SELECT 'DQ41','error','acceptance_revision_chain',
       (SELECT count(*) FROM acceptance_event a
        WHERE (a.event_sequence=1 AND a.supersedes_acceptance_event_id IS NOT NULL)
           OR (a.event_sequence>1 AND NOT EXISTS (
             SELECT 1 FROM acceptance_event p
             WHERE p.acceptance_event_id=a.supersedes_acceptance_event_id
               AND p.acceptance_stream_id=a.acceptance_stream_id
               AND p.event_sequence=a.event_sequence-1
           ))),
       'Acceptance corrections append to an exact work-order stream'
UNION ALL SELECT 'DQ42','error','first_pass_has_no_rework_or_rescue_history',
       (SELECT count(*) FROM acceptance_event a
        WHERE a.outcome_code='ACCEPTED_FIRST_PASS' AND EXISTS (
          SELECT 1 FROM work_event w
          WHERE w.work_order_id=a.work_order_id
            AND w.work_class_code IN ('PARTNER_REWORK','FOUNDER_RESCUE')
        )),
       'First-pass never coexists with rework or rescue in either insert order'
UNION ALL SELECT 'DQ43','error','after_rescue_has_rescue_history',
       (SELECT count(*) FROM acceptance_event a
        WHERE a.outcome_code='ACCEPTED_AFTER_FOUNDER_RESCUE' AND NOT EXISTS (
          SELECT 1 FROM work_event w
          WHERE w.work_order_id=a.work_order_id
            AND w.work_class_code='FOUNDER_RESCUE'
        )),
       'Accepted-after-rescue is supported by an explicit founder rescue event'
UNION ALL SELECT 'DQ44','error','reverse_order_quality_trigger_present',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='trigger'
            AND name='validate_work_event_before_insert'
            AND instr(sql,'rework or rescue cannot be appended after first-pass acceptance')>0
        ) THEN 0 ELSE 1 END,
       'A rescue appended after first-pass is rejected, closing the order loophole'
UNION ALL SELECT 'DQ45','error','quality_fixture_outcomes',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_current_acceptance
          WHERE work_order_id='SYN_WO_IMPL_PASS'
            AND outcome_code='ACCEPTED_FIRST_PASS'
        ) AND EXISTS (
          SELECT 1 FROM v_current_acceptance
          WHERE work_order_id='SYN_WO_IMPL_RESCUE'
            AND outcome_code='ACCEPTED_AFTER_FOUNDER_RESCUE'
        ) THEN 0 ELSE 1 END,
       'Fixtures preserve both independent and founder-rescued delivery outcomes'
UNION ALL SELECT 'DQ46','error','founder_work_has_one_economic_time_row',
       (SELECT count(*) FROM work_event w
        WHERE w.actor_role_code='FOUNDER' AND (
          SELECT count(*) FROM cost_event c
          WHERE c.source_work_event_id=w.work_event_id
        )<>1),
       'Every founder operational event enters the founder-time ledger exactly once'
UNION ALL SELECT 'DQ47','error','founder_source_event_not_duplicated',
       (SELECT count(*) FROM (
          SELECT source_work_event_id,count(*) AS n FROM cost_event
          WHERE source_work_event_id IS NOT NULL
          GROUP BY source_work_event_id HAVING n<>1
        )),
       'One founder work event cannot be charged twice'
UNION ALL SELECT 'DQ48','error','founder_component_matches_work_class',
       (SELECT count(*) FROM cost_event c JOIN work_event w
          ON w.work_event_id=c.source_work_event_id
        WHERE c.source_work_event_id IS NOT NULL AND NOT (
          (w.work_class_code IN ('STANDARD_DELIVERY','SUPPORT')
            AND c.founder_time_component='RESIDUAL_EXECUTION')
          OR (w.work_class_code IN ('PLANNED_REVIEW','DELIVERY_QA')
            AND c.founder_time_component='BRIEFING_COORDINATION_REVIEW')
          OR (w.work_class_code='PARTNER_REWORK'
            AND c.founder_time_component='REWORK')
          OR (w.work_class_code IN ('FOUNDER_RESCUE','DEAL_RESCUE')
            AND c.founder_time_component='RESCUE')
          OR (w.work_class_code='OFFBOARDING'
            AND c.founder_time_component='ADMIN_OFFBOARDING')
        )),
       'Founder time components agree with their operational work classes'
UNION ALL SELECT 'DQ49','error','founder_time_borne_by_principal',
       (SELECT count(*) FROM cost_event c
        JOIN agreement_version a USING (agreement_version_id)
        WHERE c.cost_class='FOUNDER_TIME'
          AND c.cost_bearer_party_id<>a.principal_party_id),
       'Founder-time value is always measured from the principal perspective'
UNION ALL SELECT 'DQ50','error','total_delegated_founder_minutes_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_agreement_cash_economics
          WHERE agreement_version_id='SYN_AGR_IMPL_1'
            AND total_founder_delegated_work_minutes=80
        ) THEN 0 ELSE 1 END,
       'Implementation total includes all delegated-work founder time exactly once'
UNION ALL SELECT 'DQ51','error','residual_execution_is_included',
       CASE WHEN (SELECT coalesce(sum(founder_minutes),0) FROM cost_event
          WHERE agreement_version_id='SYN_AGR_IMPL_1'
            AND founder_time_component='RESIDUAL_EXECUTION')=20
         THEN 0 ELSE 1 END,
       'Residual founder execution remains inside total delegated-work minutes'
UNION ALL SELECT 'DQ52','error','sourcing_and_offboarding_are_included',
       CASE WHEN (SELECT coalesce(sum(founder_minutes),0) FROM cost_event
          WHERE agreement_version_id='SYN_AGR_IMPL_1'
            AND founder_time_component='SOURCING_DILIGENCE_CONTRACTING')=30
         AND (SELECT coalesce(sum(founder_minutes),0) FROM cost_event
          WHERE agreement_version_id='SYN_AGR_IMPL_1'
            AND founder_time_component='ADMIN_OFFBOARDING')=5
         THEN 0 ELSE 1 END,
       'Sourcing diligence contracting admin and offboarding are not omitted'
UNION ALL SELECT 'DQ53','error','net_founder_minutes_released_formula',
       (SELECT count(*) FROM v_agreement_cash_economics
        WHERE net_founder_minutes_released<>
          baseline_founder_minutes-total_founder_delegated_work_minutes),
       'Net founder minutes released equals baseline minus the one total'
UNION ALL SELECT 'DQ54','error','founder_time_cost_once',
       (SELECT count(*) FROM v_agreement_cash_economics e
        CROSS JOIN model_setting m
        WHERE e.founder_time_cost_micros<>
          e.total_founder_delegated_work_minutes*m.founder_rate_micros_per_minute),
       'The total founder time is converted to internal cost exactly once'
UNION ALL SELECT 'DQ55','error','work_quality_rescue_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_work_quality_economics
          WHERE work_order_id='SYN_WO_IMPL_RESCUE'
            AND partner_rework_minutes=30 AND operational_rescue_minutes=15
            AND total_founder_delegated_work_minutes=45
            AND accepted_after_founder_rescue=1
        ) THEN 0 ELSE 1 END,
       'Work output exposes partner rework operational rescue and founder total separately'
UNION ALL SELECT 'DQ56','error','fully_loaded_contribution_reconciles',
       (SELECT count(*) FROM v_agreement_cash_economics
        WHERE bank_cash_contribution_micros<>bank_revenue_micros-bank_cost_micros
           OR fully_loaded_contribution_micros<>
             bank_revenue_micros-economic_cash_cost_micros-founder_time_cost_micros),
       'Bank and fully-loaded contribution reconcile without hidden time or cash'
UNION ALL SELECT 'DQ57','error','implementation_cash_owner_orientation',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_agreement_cash_economics
          WHERE agreement_version_id='SYN_AGR_IMPL_1'
            AND bank_revenue_micros=0 AND bank_cost_micros=0
            AND nonprincipal_bank_cash_micros=5000000000
            AND founder_time_cost_micros=8000000000
            AND fully_loaded_contribution_micros=-8000000000
        ) THEN 0 ELSE 1 END,
       'Partner-collected service cash is not silently treated as principal revenue'
UNION ALL SELECT 'DQ58','error','work_and_overhead_cash_do_not_double_count',
       (SELECT count(*) FROM cost_event
        WHERE cash_cost_micros IS NOT NULL
          AND (work_order_id IS NOT NULL OR source_work_event_id IS NOT NULL)),
       'Exact work cash lives in work_event; non-founder cost_event cash is overhead only'
UNION ALL SELECT 'DQ59','error','economics_sums_only_founder_time_ledger',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view'
            AND name='v_agreement_cash_economics'
            AND instr(lower(sql),'sum(x.founder_minutes)')>0
            AND instr(lower(sql),'sum(w.minutes)')=0
        ) THEN 0 ELSE 1 END,
       'Economics never adds operational founder minutes a second time'
UNION ALL SELECT 'DQ60','error','accepted_lead_has_consent',
       (SELECT count(*) FROM lead_registration_event r
        JOIN lead_source_event s USING (lead_source_event_id)
        WHERE r.registration_status='ACCEPTED' AND s.consent_status<>'PASS'),
       'An accepted referral registration requires permissioned provenance'
UNION ALL SELECT 'DQ61','error','affiliate_acceptance_has_disclosure',
       (SELECT count(*) FROM lead_registration_event r
        JOIN lead_source_event s USING (lead_source_event_id)
        JOIN agreement_version a ON a.agreement_version_id=r.agreement_version_id
        WHERE r.registration_status='ACCEPTED' AND a.motion_code='AFFILIATE'
          AND s.disclosure_status<>'PASS'),
       'Affiliate acceptance fails closed without material-connection disclosure'
UNION ALL SELECT 'DQ62','error','registration_revision_chain',
       (SELECT count(*) FROM lead_registration_event r
        WHERE (r.event_sequence=1 AND r.supersedes_registration_event_id IS NOT NULL)
           OR (r.event_sequence>1 AND NOT EXISTS (
             SELECT 1 FROM lead_registration_event p
             WHERE p.registration_event_id=r.supersedes_registration_event_id
               AND p.registration_stream_id=r.registration_stream_id
               AND p.event_sequence=r.event_sequence-1
           ))),
       'Lead registration status changes append to an exact provider stream'
UNION ALL SELECT 'DQ63','error','current_referral_registration_accepted',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_current_lead_registration
          WHERE registration_stream_id='SYN_REG_REF'
            AND registration_status='ACCEPTED'
        ) THEN 0 ELSE 1 END,
       'The live referral fixture has one current provider-accepted registration'
UNION ALL SELECT 'DQ64','error','one_winner_per_event_rule',
       (SELECT count(*) FROM (
          SELECT commercial_event_id,attribution_rule_version_id,count(*) AS n
          FROM attribution_decision WHERE attribution_role='WINNER'
          GROUP BY commercial_event_id,attribution_rule_version_id HAVING n>1
        )),
       'Each commercial event and rule version has at most one commission winner'
UNION ALL SELECT 'DQ65','error','winner_uses_current_accepted_registration',
       (SELECT count(*) FROM attribution_decision d
        LEFT JOIN v_current_lead_registration r
          ON r.registration_event_id=d.registration_event_id
        WHERE d.attribution_role='WINNER'
          AND (r.registration_event_id IS NULL OR r.registration_status<>'ACCEPTED')),
       'Winning credit binds a current accepted registration, not a stale submission'
UNION ALL SELECT 'DQ66','error','attribution_is_not_causality',
       (SELECT count(*) FROM attribution_decision WHERE causal_claim<>0),
       'Program attribution never asserts incremental causal effect'
UNION ALL SELECT 'DQ67','error','registration_precedes_outcome_within_lookback',
       (SELECT count(*) FROM attribution_decision d
        JOIN lead_registration_event r USING (registration_event_id)
        JOIN commercial_event c USING (commercial_event_id)
        JOIN attribution_rule_version ar USING (attribution_rule_version_id)
        WHERE datetime(r.occurred_at)>datetime(c.occurred_at)
           OR julianday(c.occurred_at)-julianday(r.occurred_at)>ar.lookback_days),
       'No retroactive or out-of-window registration wins credit'
UNION ALL SELECT 'DQ68','error','affiliate_unknown_stays_pending',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_current_lead_registration r
          JOIN lead_source_event s USING (lead_source_event_id)
          WHERE r.registration_stream_id='SYN_REG_AFF'
            AND r.registration_status='SUBMITTED'
            AND s.disclosure_status='UNKNOWN'
        ) THEN 0 ELSE 1 END,
       'Unknown affiliate disclosure remains pending rather than accepted'
UNION ALL SELECT 'DQ69','error','equal_value_cash_events_both_count',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_channel_funnel
          WHERE agreement_version_id='SYN_AGR_AFF_1'
            AND customer_cash_observed_micros=2000000000
        ) THEN 0 ELSE 1 END,
       'Two legitimate equal-value events contribute twice by stable event ID'
UNION ALL SELECT 'DQ70','error','no_value_based_sum_distinct',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view' AND name='v_channel_funnel'
            AND instr(lower(sql),'sum(distinct')=0
        ) THEN 0 ELSE 1 END,
       'Funnel aggregation never deduplicates by amount value'
UNION ALL SELECT 'DQ71','error','commercial_bank_cash_orientation',
       (SELECT count(*) FROM commercial_event c JOIN bank_evidence b USING (bank_evidence_id)
        WHERE b.amount_micros<>c.amount_micros OR b.currency<>c.currency
           OR datetime(b.occurred_at)<>datetime(c.occurred_at)
           OR NOT ((b.direction_code='INFLOW'
                    AND b.account_owner_party_id=c.payee_party_id
                    AND b.counterparty_party_id=c.payer_party_id)
                OR (b.direction_code='OUTFLOW'
                    AND b.account_owner_party_id=c.payer_party_id
                    AND b.counterparty_party_id=c.payee_party_id))),
       'Each bank cash fact names its account owner and counterparty orientation'
UNION ALL SELECT 'DQ72','error','commission_payout_is_principal_cost',
       (SELECT count(*) FROM commission_event e
        JOIN bank_evidence b USING (bank_evidence_id)
        JOIN agreement_version a USING (agreement_version_id)
        WHERE e.event_type='PAID' AND (
          b.direction_code<>'OUTFLOW'
          OR b.account_owner_party_id<>a.principal_party_id
          OR b.counterparty_party_id<>a.partner_party_id
        )),
       'Paid referral affiliate or agent commission is a principal bank outflow'
UNION ALL SELECT 'DQ73','error','commission_base_matches_rule',
       (SELECT count(*) FROM commission_event e
        JOIN commission_rule_version r USING (commission_rule_version_id)
        JOIN commercial_event c ON c.commercial_event_id=e.base_commercial_event_id
        WHERE e.event_type='ACCRUED' AND NOT (
          (r.base_code='BOOKING' AND c.event_type='BOOKING')
          OR (r.base_code='INVOICED' AND c.event_type='INVOICE')
          OR (r.base_code='RECOGNIZED' AND c.event_type='RECOGNIZED_REVENUE')
          OR (r.base_code='COLLECTED_NET' AND c.event_type='CUSTOMER_CASH')
        )),
       'Booking invoiced recognized and collected-cash rules consume exact event classes'
UNION ALL SELECT 'DQ74','error','collected_base_has_reconciled_customer_cash',
       (SELECT count(*) FROM commission_event e
        JOIN commission_rule_version r USING (commission_rule_version_id)
        JOIN commercial_event c ON c.commercial_event_id=e.base_commercial_event_id
        LEFT JOIN bank_evidence b USING (bank_evidence_id)
        WHERE e.event_type='ACCRUED' AND r.base_code='COLLECTED_NET'
          AND (c.event_type<>'CUSTOMER_CASH' OR b.reconciliation_status<>'RECONCILED'
            OR b.evidence_type<>'CUSTOMER_COLLECTION')),
       'Collected-net accrual starts only from reconciled customer cash'
UNION ALL SELECT 'DQ75','error','commission_calculation_reconciles',
       (SELECT count(*) FROM commission_event e
        JOIN commission_rule_version r USING (commission_rule_version_id)
        WHERE e.event_type='ACCRUED' AND NOT (
          (r.calculation_code='RATE'
            AND e.amount_micros=e.eligible_base_micros*r.rate_ppm/1000000)
          OR (r.calculation_code='FIXED'
            AND e.amount_micros=r.fixed_amount_micros)
        )),
       'Initial accrual matches its immutable calculation rule'
UNION ALL SELECT 'DQ76','error','adjustments_never_exceed_accrual',
       (SELECT count(*) FROM commission_event a
        WHERE a.event_type='ACCRUED' AND coalesce((
          SELECT sum(x.amount_micros) FROM commission_event x
          WHERE x.source_accrual_event_id=a.commission_event_id
            AND x.event_type IN ('REVERSED','FORFEITED','PAID')
        ),0)>a.amount_micros),
       'Reversal forfeiture and payout cannot consume more than accrued commission'
UNION ALL SELECT 'DQ77','error','paid_has_exact_bank_evidence',
       (SELECT count(*) FROM commission_event e
        LEFT JOIN bank_evidence b USING (bank_evidence_id)
        WHERE e.event_type='PAID' AND (
          b.bank_evidence_id IS NULL OR b.evidence_type<>'PARTNER_PAYOUT'
          OR b.reconciliation_status<>'RECONCILED'
          OR b.amount_micros<>e.amount_micros OR b.currency<>e.currency
        )),
       'PAID means an exact bank-reconciled partner payout, never an estimate'
UNION ALL SELECT 'DQ78','error','refund_reversal_settlement_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_commission_settlement
          WHERE accrual_event_id='SYN_COMM_REF_ACCRUAL'
            AND initial_eligible_base_micros=10000000000
            AND exact_base_deduction_micros=2500000000
            AND settled_net_base_micros=7500000000
            AND settled_rule_commission_micros=1500000000
            AND initially_accrued_micros=2000000000
            AND reversed_or_forfeited_micros=500000000
            AND final_earned_commission_micros=1500000000
            AND paid_micros=1500000000
        ) THEN 0 ELSE 1 END,
       'The exact refund converts gross event-time accrual to settled net commission'
UNION ALL SELECT 'DQ79','error','gross_and_net_bases_have_distinct_names',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view'
            AND name='v_commission_settlement'
            AND instr(sql,'initial_eligible_base_micros')>0
            AND instr(sql,'settled_net_base_micros')>0
            AND instr(sql,'exact_base_deduction_micros')>0
        ) THEN 0 ELSE 1 END,
       'The view never labels gross collected cash as post-refund net cash'
UNION ALL SELECT 'DQ80','error','bank_evidence_not_reused_across_ledgers',
       (SELECT count(*) FROM commercial_event c JOIN commission_event e
          ON e.bank_evidence_id=c.bank_evidence_id
        WHERE c.bank_evidence_id IS NOT NULL),
       'One bank receipt cannot be both customer commerce and partner payout'
UNION ALL SELECT 'DQ81','error','reseller_principal_cash_orientation',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_agreement_cash_economics
          WHERE agreement_version_id='SYN_AGR_RES_1'
            AND bank_revenue_micros=4000000000
            AND nonprincipal_bank_cash_micros=6000000000
            AND bank_cost_micros=0 AND economic_cash_cost_micros=0
            AND principal_unsettled_receivable_micros=0
            AND fully_loaded_contribution_micros=4000000000
        ) THEN 0 ELSE 1 END,
       'Supplier revenue is wholesale cash; reseller downstream cash is not principal revenue'
UNION ALL SELECT 'DQ82','error','reseller_obligation_and_cash_separate',
       CASE WHEN EXISTS (
          SELECT 1 FROM reseller_order r
          JOIN commercial_event o
            ON o.commercial_event_id=r.upstream_obligation_event_id
          JOIN commercial_event c
            ON c.commercial_event_id=r.upstream_cash_event_id
          WHERE r.reseller_order_id='SYN_RES_ORDER_1'
            AND o.event_type='WHOLESALE_OBLIGATION'
            AND c.event_type='WHOLESALE_CASH'
            AND r.customer_credit_risk_owner_party_id='SYN_PARTNER_RES'
        ) THEN 0 ELSE 1 END,
       'Upstream obligation survives independently from downstream collection and settlement'
UNION ALL SELECT 'DQ83','error','experiment_assignment_precedes_exposure',
       (SELECT count(*) FROM experiment_assignment a
        JOIN partner_experiment e USING (experiment_id)
        WHERE datetime(a.assigned_at)>=datetime(e.starts_at)
           OR (a.first_exposure_at IS NOT NULL
               AND datetime(a.assigned_at)>datetime(a.first_exposure_at))),
       'Holdout assignment is immutable and precedes treatment exposure'
UNION ALL SELECT 'DQ84','error','control_is_unexposed_and_unique',
       (SELECT count(*) FROM experiment_assignment
        WHERE arm_code='CONTROL' AND first_exposure_at IS NOT NULL)
       +(SELECT count(*) FROM (
          SELECT experiment_id,assignment_unit_ref_hash,count(*) AS n
          FROM experiment_assignment
          GROUP BY experiment_id,assignment_unit_ref_hash HAVING n>1
        )),
       'A cluster enters one arm and control receives no treatment exposure'
UNION ALL SELECT 'DQ85','error','experiment_outcomes_mature',
       (SELECT count(*) FROM experiment_outcome o
        JOIN experiment_assignment a USING (assignment_id)
        JOIN partner_experiment e USING (experiment_id)
        WHERE o.maturity_state IN ('MEASURED_VALUE','MEASURED_ZERO')
          AND (datetime(o.occurred_at)<datetime(e.maturity_at)
            OR datetime(o.recorded_at)>datetime(e.snapshot_as_of))),
       'Measured values and measured zeros appear only after the frozen maturity window'
UNION ALL SELECT 'DQ86','error','small_holdout_is_inconclusive',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_experiment_readout
          WHERE experiment_id='SYN_EXP_REF' AND treatment_n=1 AND control_n=1
            AND difference_micros=1300000000
            AND causal_state='INCONCLUSIVE_SMALL_N'
        ) THEN 0 ELSE 1 END,
       'Attribution revenue is not promoted to lift when the holdout is underpowered'
UNION ALL SELECT 'DQ87','error','completed_handoff_ready',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_termination_readiness
          WHERE termination_case_id='SYN_TERM_OUT' AND open_access_count=0
            AND unresolved_handoff_count=0 AND closure_gate='READY_TO_CLOSE'
        ) THEN 0 ELSE 1 END,
       'The completed outsourcing handoff resolves every required item'
UNION ALL SELECT 'DQ88','error','closed_event_only_when_ready',
       (SELECT count(*) FROM termination_event e
        JOIN v_termination_readiness r USING (termination_case_id)
        WHERE e.state_code='HANDOFF_CLOSED' AND r.closure_gate<>'READY_TO_CLOSE'),
       'A recorded handoff closure has no open access data support refund or renewal item'
UNION ALL SELECT 'DQ89','error','open_implementation_not_closed',
       CASE WHEN NOT EXISTS (
          SELECT 1 FROM termination_event
          WHERE termination_case_id='SYN_TERM_IMPL_OPEN'
            AND state_code='HANDOFF_CLOSED'
        ) THEN 0 ELSE 1 END,
       'The pause fixture cannot claim closure while access and data remain open'
UNION ALL SELECT 'DQ90','error','one_handoff_stream_per_case_item',
       (SELECT count(*) FROM (
          SELECT termination_case_id,item_code,count(DISTINCT handoff_stream_id) AS n
          FROM handoff_item_event GROUP BY termination_case_id,item_code HAVING n>1
        )),
       'Parallel PASS and OPEN streams cannot coexist for one case and item'
UNION ALL SELECT 'DQ91','error','live_decision_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_current_decision_snapshot
          WHERE decision_snapshot_id='SYN_DECISION_REF_LIVE'
            AND decision_code='LIVE' AND action_code='INCONCLUSIVE'
            AND legal_state='PASS' AND responsibility_state='PASS'
            AND authority_state='PASS' AND cash_state='PASS'
        ) THEN 0 ELSE 1 END,
       'A live operating state can carry an explicit inconclusive growth action'
UNION ALL SELECT 'DQ92','error','pause_decision_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_current_decision_snapshot
          WHERE decision_snapshot_id='SYN_DECISION_OEM_PAUSE'
            AND decision_code='PAUSE' AND action_code='PAUSE'
            AND legal_state='UNKNOWN' AND authority_state='UNKNOWN'
        ) THEN 0 ELSE 1 END,
       'Unknown OEM evidence produces a pause operating state and pause action'
UNION ALL SELECT 'DQ93','error','decision_action_present_and_valid',
       (SELECT count(*) FROM decision_snapshot
        WHERE action_code IS NULL OR action_code NOT IN (
          'EXPAND','CHANGE','PAUSE','STOP','INCONCLUSIVE'
        )),
       'Every decision separates operating state from one chapter action'
UNION ALL SELECT 'DQ94','error','decision_action_compatibility',
       (SELECT count(*) FROM decision_snapshot d
        WHERE (d.decision_code='PAUSE'
                AND d.action_code NOT IN ('PAUSE','CHANGE','INCONCLUSIVE'))
           OR (d.decision_code IN ('STOP','OFFBOARD') AND d.action_code<>'STOP')
           OR (d.action_code='EXPAND' AND (
             d.decision_code NOT IN ('LIVE','MAINTAIN')
             OR d.legal_state<>'PASS' OR d.responsibility_state<>'PASS'
             OR d.authority_state<>'PASS' OR d.incrementality_state<>'PASS'
           ))),
       'Obvious operating-state and action contradictions fail closed'
UNION ALL SELECT 'DQ95','error','decision_metrics_reconcile_to_economics',
       (SELECT count(*) FROM decision_snapshot_metric m
        JOIN decision_snapshot d USING (decision_snapshot_id)
        JOIN v_agreement_cash_economics e USING (agreement_version_id)
        WHERE d.decision_snapshot_id='SYN_DECISION_REF_LIVE'
          AND ((m.metric_code='BANK_REVENUE_MICROS'
                AND m.metric_value<>e.bank_revenue_micros)
            OR (m.metric_code='FOUNDER_MINUTES'
                AND m.metric_value<>e.total_founder_delegated_work_minutes)
            OR (m.metric_code='FULLY_LOADED_CONTRIBUTION_MICROS'
                AND m.metric_value<>e.fully_loaded_contribution_micros))),
       'Materialized decision metrics reconcile to the bounded economics view'
UNION ALL SELECT 'DQ96','error','decision_snapshot_time_order',
       (SELECT count(*) FROM decision_snapshot
        WHERE datetime(evidence_cutoff_at)>datetime(snapshot_as_of)
           OR datetime(snapshot_as_of)>datetime(decided_at)
           OR datetime(decided_at)>datetime(recorded_at)),
       'Decision evidence cutoff snapshot decision and ingest times are ordered'
UNION ALL SELECT 'DQ97','error','unknown_metric_is_not_zero',
       (SELECT count(*) FROM decision_snapshot_metric
        WHERE metric_state IN ('UNKNOWN','NA','INCONCLUSIVE')
          AND metric_value IS NOT NULL),
       'UNKNOWN NA and INCONCLUSIVE never silently become numeric zero'
UNION ALL SELECT 'DQ98','error','economics_orientation_not_motion_based',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view'
            AND name='v_agreement_cash_economics'
            AND instr(sql,'account_owner_party_id')>0
            AND instr(sql,'principal_party_id')>0
            AND instr(lower(sql),'when a.motion_code')=0
        ) THEN 0 ELSE 1 END,
       'Principal economics follows payer payee and bank owner, never the motion label'
UNION ALL SELECT 'DQ99','error','principal_referral_economics_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_agreement_cash_economics
          WHERE agreement_version_id='SYN_AGR_REF_1'
            AND bank_revenue_micros=10000000000
            AND paid_commission_cost_micros=1500000000
            AND bank_cost_micros=1700000000
            AND economic_cash_cost_micros=4200000000
            AND founder_time_cost_micros=3000000000
            AND fully_loaded_contribution_micros=2800000000
        ) THEN 0 ELSE 1 END,
       'Referral customer cash commission refund overhead and founder time reconcile once';

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

CREATE TABLE assertion_guard (
  guard_id INTEGER PRIMARY KEY CHECK (guard_id=1),
  error_failure_count INTEGER NOT NULL CHECK (error_failure_count=0)
) STRICT;

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

COMMIT;

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

PRAGMA foreign_key_check;
PRAGMA integrity_check;

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

SELECT count(*) AS assertion_count,
       sum(CASE WHEN status='PASS' THEN 1 ELSE 0 END) AS assertion_pass_count,
       sum(CASE WHEN status='FAIL' THEN 1 ELSE 0 END) AS assertion_fail_count
FROM v_assertion_report;

SELECT agreement_version_id,motion_code,commercial_status,
       responsibility_gap_count,authority_gap_count,derived_gate
FROM v_agreement_readiness ORDER BY agreement_version_id;

SELECT agreement_version_id,motion_code,bank_revenue_micros,bank_cost_micros,
       paid_commission_cost_micros,nonprincipal_bank_cash_micros,
       total_founder_delegated_work_minutes,baseline_founder_minutes,
       net_founder_minutes_released,fully_loaded_contribution_micros
FROM v_agreement_cash_economics ORDER BY agreement_version_id;

SELECT * FROM v_commission_settlement ORDER BY accrual_event_id;
SELECT * FROM v_experiment_readout ORDER BY experiment_id;
SELECT * FROM v_termination_readiness ORDER BY termination_case_id;
SELECT decision_snapshot_id,agreement_version_id,decision_code,action_code,
       legal_state,responsibility_state,authority_state,cash_state,
       incrementality_state
FROM v_current_decision_snapshot ORDER BY agreement_version_id;
