CREATE TABLE go_live_gate_controls (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    gate_number integer NOT NULL UNIQUE CHECK (gate_number BETWEEN 133 AND 151),
    phase text NOT NULL CHECK (phase IN ('staging_launch', 'closed_beta_test_money', 'real_money_production')),
    gate_key text NOT NULL UNIQUE,
    title text NOT NULL,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'ready', 'approved', 'blocked', 'failed', 'waived')),
    priority text NOT NULL DEFAULT 'P0' CHECK (priority IN ('P0', 'P1')),
    owner_team text NOT NULL CHECK (owner_team IN ('engineering', 'security', 'operations', 'compliance', 'legal', 'risk', 'finance', 'support', 'product', 'executive')),
    decision_summary text NOT NULL,
    evidence_reference text NOT NULL DEFAULT '',
    runbook_reference text NOT NULL DEFAULT '',
    approver_name text NOT NULL DEFAULT '',
    due_at timestamptz,
    approved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    approved_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    updated_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (
        (gate_number BETWEEN 133 AND 136 AND phase = 'staging_launch')
        OR (gate_number BETWEEN 137 AND 142 AND phase = 'closed_beta_test_money')
        OR (gate_number BETWEEN 143 AND 151 AND phase = 'real_money_production')
    ),
    CHECK (status NOT IN ('ready', 'approved', 'waived') OR evidence_reference <> ''),
    CHECK (status <> 'approved' OR approver_name <> ''),
    CHECK (approved_at IS NULL OR status IN ('approved', 'waived'))
);

CREATE INDEX go_live_gate_controls_phase_status_idx
    ON go_live_gate_controls (phase, status, gate_number);

CREATE INDEX go_live_gate_controls_owner_status_idx
    ON go_live_gate_controls (owner_team, status, updated_at DESC);

CREATE INDEX go_live_gate_controls_due_idx
    ON go_live_gate_controls (due_at)
    WHERE due_at IS NOT NULL;

CREATE TRIGGER go_live_gate_controls_set_updated_at
BEFORE UPDATE ON go_live_gate_controls
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO go_live_gate_controls (
    gate_number, phase, gate_key, title, status, priority, owner_team,
    decision_summary, runbook_reference, metadata
) VALUES
    (133, 'staging_launch', 'staging-non-default-secrets', 'Staging uses non-default secrets.', 'in_progress', 'P0', 'security', 'Production-like staging must prove non-default JWT, card, webhook, MFA, provider and secret-manager references before any beta traffic.', 'docs/infrastructure-runtime.md', '{"requires":["secret_rotation","managed_secret_store","config_load_evidence"]}'::jsonb),
    (134, 'staging_launch', 'provider-webhook-e2e', 'Test provider webhooks are verified end to end.', 'in_progress', 'P0', 'engineering', 'Provider webhook signature, replay, ordering, idempotency and downstream processing must be verified in staging.', 'docs/provider-integrations.md', '{"requires":["card_webhook","kyc_webhook","provider_webhook_inbox"]}'::jsonb),
    (135, 'staging_launch', 'staging-observability-evidence', 'Live staging dashboards and alerts are deployed and evidence is attached.', 'in_progress', 'P0', 'security', 'Grafana, Prometheus and alert routing must be deployed against live staging and evidence must be attached.', 'docs/runbooks/major-incident-notification.md', '{"requires":["dashboard_pack","alert_rules","on_call_route"]}'::jsonb),
    (136, 'staging_launch', 'seeded-test-users', 'Seeded test users cover customer, admin, KYC, AML, wallet, cards, SEPA, crypto, savings, and FX flows.', 'in_progress', 'P0', 'engineering', 'Staging seed evidence must show role and product coverage across all critical customer and admin flows.', 'backend/cmd/seed-staging', '{"requires":["customer","admin","kyc","aml","wallet","cards","sepa","crypto","savings","fx"]}'::jsonb),
    (137, 'closed_beta_test_money', 'gate-a-complete', 'All Gate A items complete.', 'pending', 'P0', 'operations', 'Closed beta cannot start until all staging launch gates are approved or formally waived with evidence.', 'docs/go-live-gates.md', '{"depends_on":[133,134,135,136]}'::jsonb),
    (138, 'closed_beta_test_money', 'cicd-release-rollback-exercise', 'CI/CD release and rollback process is exercised.', 'pending', 'P0', 'engineering', 'Release, migration approval, rollback image and rollback runbooks must be exercised and evidenced before beta.', 'docs/runbooks/rollback.md', '{"requires":["release_notes","migration_plan","rollback_exercise"]}'::jsonb),
    (139, 'closed_beta_test_money', 'security-scanning-active', 'Security scanning is active.', 'pending', 'P0', 'security', 'Dependency, vulnerability, secret and container scans must be active in CI/CD with current evidence.', 'SECURITY.md', '{"requires":["govulncheck","npm_audit","gitleaks","trivy","dast"]}'::jsonb),
    (140, 'closed_beta_test_money', 'beta-legal-wording', 'Legal confirms beta wording does not imply real banking services.', 'pending', 'P0', 'legal', 'Legal must approve beta wording, restricted terms and test-money disclaimers before inviting users.', 'docs/licensing-legal-market-access.md', '{"requires":["restricted_terms","customer_disclosures","test_money_disclaimer"]}'::jsonb),
    (141, 'closed_beta_test_money', 'support-incident-channels', 'Customer support and incident channels exist.', 'pending', 'P0', 'support', 'Support, complaint, security incident and major incident channels must be ready before customer beta.', 'docs/support-playbooks.md', '{"requires":["support_queue","incident_channel","complaints_channel"]}'::jsonb),
    (142, 'closed_beta_test_money', 'known-risk-register', 'Known-risk register is approved by product, engineering, security, and compliance.', 'pending', 'P0', 'risk', 'Known risks, mitigations and accountable owners must be accepted before closed beta.', 'docs/threat-model.md', '{"requires":["product_approval","engineering_approval","security_approval","compliance_approval"]}'::jsonb),
    (143, 'real_money_production', 'legal-licensing-model', 'Legal/licensing model approved for every target jurisdiction.', 'blocked', 'P0', 'legal', 'Real-money launch is blocked until counsel-approved legal/licensing evidence exists for every target jurisdiction.', 'docs/licensing-legal-market-access.md', '{"depends_on":["legal_market_access_controls"]}'::jsonb),
    (144, 'real_money_production', 'real-providers-approved', 'Real providers integrated and contractually approved for production traffic.', 'blocked', 'P0', 'operations', 'Real-money traffic is blocked until contracted providers, credentials, certification packs and production approvals are attached.', 'docs/provider-integrations.md', '{"depends_on":["provider_integrations"]}'::jsonb),
    (145, 'real_money_production', 'reconciliation-break-management-live', 'Reconciliation and break management are live.', 'blocked', 'P0', 'finance', 'Daily reconciliation, break ownership, SLA, finance reporting and provider balance feeds must be live.', 'docs/runbooks/reconciliation-break.md', '{"depends_on":["reconciliation_runs","provider_balances","backoffice_queues"]}'::jsonb),
    (146, 'real_money_production', 'security-review-pentest-remediation', 'Security review, penetration test, and remediation are complete.', 'blocked', 'P0', 'security', 'External security review, penetration test, remediation and retest evidence must be complete before real money.', 'docs/production-readiness-checklist.md', '{"depends_on":["penetration_tests","dast","key_rotation","redaction"]}'::jsonb),
    (147, 'real_money_production', 'kyc-aml-fraud-ops-live', 'KYC/AML/sanctions/fraud operations are live with accountable owners.', 'blocked', 'P0', 'compliance', 'KYC, sanctions, PEP, adverse media, monitoring, fraud operations and escalation owners must be live.', 'docs/compliance-operations-procedures.md', '{"depends_on":["identity_provider","sanctions_provider","aml_cases","regulatory_reports"]}'::jsonb),
    (148, 'real_money_production', 'dora-ict-risk-resilience', 'Formal DORA-style ICT risk, incident reporting, third-party risk, and resilience controls are approved if EU financial regulation applies.', 'blocked', 'P0', 'security', 'DORA-style ICT risk, incident reporting, third-party risk, resilience testing and owner signoff must be approved where applicable.', 'docs/runbooks/business-continuity-disaster-recovery.md', '{"depends_on":["incident_runbooks","third_party_risk","bcdr_test","chaos_test"]}'::jsonb),
    (149, 'real_money_production', 'pci-dss-scope-validation', 'PCI DSS scope and validation path are approved if cardholder data is handled.', 'blocked', 'P0', 'security', 'Cardholder-data handling requires PCI DSS scope, validation path and issuer/acquirer/QSA-aligned evidence where applicable.', 'docs/card-data-scope.md', '{"depends_on":["pci_scope_assessments","tokenization_policies","card_processor_configs"]}'::jsonb),
    (150, 'real_money_production', 'bcdr-restore-incident-evidence', 'Backups, restore drills, DR plan, and incident runbooks are tested with production-like evidence.', 'blocked', 'P0', 'operations', 'Backup encryption, restore drill, DR plan and incident runbooks must be tested with production-like evidence.', 'docs/runbooks/business-continuity-disaster-recovery.md', '{"depends_on":["restore_drill","backup_attestation","incident_runbooks"]}'::jsonb),
    (151, 'real_money_production', 'executive-go-no-go', 'Executive go/no-go signoff is recorded.', 'blocked', 'P0', 'executive', 'Executive launch decision must record accountable approver, risk acceptance, launch scope and evidence pack.', 'docs/go-live-gates.md', '{"depends_on":[143,144,145,146,147,148,149,150]}'::jsonb)
ON CONFLICT (gate_number) DO NOTHING;

INSERT INTO data_archival_policies (
    table_name, data_category, retention_days, archive_after_days, archive_strategy, archive_destination,
    legal_hold_supported, delete_after_archive, evidence_required, status, owner
) VALUES
    ('go_live_gate_controls', 'operational', 3650, 365, 'cold_storage_export', 'go-live-evidence-archive', true, false, true, 'active', 'operations')
ON CONFLICT (table_name) DO NOTHING;

INSERT INTO privacy_data_inventory (
    table_name, field_name, data_category, classification, lawful_basis, retention_policy, residency_scope, encrypted_at_rest, owner, notes
) VALUES
    ('go_live_gate_controls', '*', 'audit', 'confidential', 'legal_obligation/risk_management', 'audit_logs_default', 'default', false, 'operations', 'Go-live gate decisions, evidence references, approval names, operational readiness metadata and audit pointers; no raw secrets or contracts stored')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;