CREATE TABLE database_release_migration_plans (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    release_version text NOT NULL,
    environment text NOT NULL CHECK (environment IN ('staging', 'preprod', 'production')),
    migration_plan_reference text NOT NULL,
    migration_plan_checksum text NOT NULL DEFAULT '',
    dry_run_status text NOT NULL DEFAULT 'pending' CHECK (dry_run_status IN ('pending', 'passed', 'failed', 'waived')),
    dry_run_completed_at timestamptz,
    rollback_plan text NOT NULL,
    rollback_verified boolean NOT NULL DEFAULT false,
    rollback_evidence_reference text NOT NULL DEFAULT '',
    risk_level text NOT NULL DEFAULT 'medium' CHECK (risk_level IN ('low', 'medium', 'high', 'critical')),
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'reviewed', 'approved', 'rejected', 'executed')),
    owner text NOT NULL,
    approver text NOT NULL DEFAULT '',
    approved_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (release_version, environment),
    CHECK (status NOT IN ('approved', 'executed') OR (dry_run_status IN ('passed', 'waived') AND rollback_plan <> '' AND rollback_verified = true AND approver <> '' AND approved_at IS NOT NULL))
);

CREATE INDEX database_release_plans_status_idx
    ON database_release_migration_plans (environment, status, created_at DESC);

CREATE TRIGGER database_release_migration_plans_set_updated_at
BEFORE UPDATE ON database_release_migration_plans
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE database_restore_drill_evidence (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    drill_reference text NOT NULL UNIQUE,
    environment text NOT NULL CHECK (environment IN ('staging', 'preprod', 'production')),
    backup_reference text NOT NULL,
    restored_database_reference text NOT NULL,
    backup_captured_at timestamptz NOT NULL,
    restore_started_at timestamptz NOT NULL,
    restore_completed_at timestamptz NOT NULL,
    rpo_seconds integer NOT NULL CHECK (rpo_seconds >= 0),
    rto_seconds integer NOT NULL CHECK (rto_seconds >= 0),
    validation_status text NOT NULL CHECK (validation_status IN ('pass', 'fail')),
    validation_summary text NOT NULL,
    evidence_reference text NOT NULL,
    operator text NOT NULL,
    approver text NOT NULL DEFAULT '',
    approved_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (restore_completed_at >= restore_started_at),
    CHECK (validation_status <> 'pass' OR (approver <> '' AND approved_at IS NOT NULL))
);

CREATE INDEX database_restore_drills_env_created_idx
    ON database_restore_drill_evidence (environment, created_at DESC);

CREATE TABLE database_index_reviews (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    review_reference text NOT NULL UNIQUE,
    table_name text NOT NULL,
    index_name text NOT NULL DEFAULT '',
    query_pattern text NOT NULL,
    observed_plan_reference text NOT NULL DEFAULT '',
    expected_volume text NOT NULL,
    risk_level text NOT NULL DEFAULT 'medium' CHECK (risk_level IN ('low', 'medium', 'high', 'critical')),
    decision text NOT NULL CHECK (decision IN ('keep', 'add', 'drop', 'defer')),
    decision_reason text NOT NULL,
    reviewed_by text NOT NULL,
    reviewed_at timestamptz NOT NULL DEFAULT now(),
    next_review_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX database_index_reviews_table_idx
    ON database_index_reviews (table_name, decision, reviewed_at DESC);

CREATE TABLE data_archival_policies (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name text NOT NULL UNIQUE,
    data_category text NOT NULL CHECK (data_category IN ('audit', 'ledger', 'webhook', 'session', 'notification', 'provider_report', 'operational')),
    retention_days integer NOT NULL CHECK (retention_days >= 0),
    archive_after_days integer NOT NULL CHECK (archive_after_days >= 0),
    archive_strategy text NOT NULL CHECK (archive_strategy IN ('partition_archive', 'cold_storage_export', 'delete_after_export', 'retain_online')),
    archive_destination text NOT NULL DEFAULT '',
    legal_hold_supported boolean NOT NULL DEFAULT true,
    delete_after_archive boolean NOT NULL DEFAULT false,
    evidence_required boolean NOT NULL DEFAULT true,
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'retired')),
    owner text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (archive_after_days <= retention_days OR retention_days = 0)
);

CREATE TRIGGER data_archival_policies_set_updated_at
BEFORE UPDATE ON data_archival_policies
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE financial_state_machine_transitions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    machine_name text NOT NULL,
    from_status text NOT NULL,
    to_status text NOT NULL,
    active boolean NOT NULL DEFAULT true,
    description text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (machine_name, from_status, to_status)
);

CREATE INDEX financial_state_machine_transitions_active_idx
    ON financial_state_machine_transitions (machine_name, from_status, to_status)
    WHERE active = true;

CREATE OR REPLACE FUNCTION enforce_financial_status_transition()
RETURNS trigger AS $$
DECLARE
    machine text := TG_ARGV[0];
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status THEN
        IF NOT EXISTS (
            SELECT 1
            FROM financial_state_machine_transitions
            WHERE machine_name = machine
                AND from_status = OLD.status
                AND to_status = NEW.status
                AND active = true
        ) THEN
            RAISE EXCEPTION 'invalid % status transition from % to %', machine, OLD.status, NEW.status
                USING ERRCODE = '23514';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

INSERT INTO financial_state_machine_transitions (machine_name, from_status, to_status, description) VALUES
    ('transfer', 'pending', 'processing', 'Submit pending payment to settlement rail'),
    ('transfer', 'processing', 'completed', 'Provider confirmed successful settlement'),
    ('transfer', 'processing', 'pending', 'Temporary failure retry'),
    ('transfer', 'processing', 'failed', 'Provider failed and refund posted'),
    ('transfer', 'pending', 'review_held', 'Risk or manual review hold'),
    ('transfer', 'review_held', 'pending', 'Review released'),
    ('transfer', 'review_held', 'rejected', 'Review rejected and refund posted'),
    ('transfer', 'completed', 'reversed', 'Completed payment reversal'),
    ('transfer', 'failed', 'reversed', 'Failed payment reversal'),
    ('payment_review_case', 'open', 'released', 'Manual review released'),
    ('payment_review_case', 'open', 'rejected', 'Manual review rejected'),
    ('payment_review_case', 'open', 'canceled', 'Manual review canceled'),
    ('payment_review_case', 'released', 'open', 'Manual review reopened after release'),
    ('payment_review_case', 'rejected', 'open', 'Manual review reopened after rejection'),
    ('payment_review_case', 'canceled', 'open', 'Manual review reopened after cancellation'),
    ('wallet_adjustment_request', 'pending', 'approved', 'Four-eyes approval applied'),
    ('wallet_adjustment_request', 'pending', 'rejected', 'Four-eyes approval rejected'),
    ('wallet_adjustment_request', 'pending', 'canceled', 'Requester canceled pending request'),
    ('savings_goal', 'active', 'paused', 'Pause contributions'),
    ('savings_goal', 'paused', 'active', 'Resume contributions'),
    ('savings_goal', 'active', 'completed', 'Target reached'),
    ('savings_goal', 'completed', 'active', 'Target raised or funds withdrawn'),
    ('savings_goal', 'active', 'closed', 'Close active goal'),
    ('savings_goal', 'paused', 'closed', 'Close paused goal'),
    ('savings_goal', 'completed', 'closed', 'Close completed goal'),
    ('virtual_card', 'active', 'frozen', 'Freeze active card'),
    ('virtual_card', 'frozen', 'active', 'Unfreeze card'),
    ('virtual_card', 'active', 'canceled', 'Cancel active card'),
    ('virtual_card', 'frozen', 'canceled', 'Cancel frozen card'),
    ('card_authorization', 'approved', 'cleared', 'Clear approved authorization'),
    ('card_authorization', 'approved', 'reversed', 'Reverse approved authorization'),
    ('card_authorization', 'approved', 'expired', 'Expire approved authorization hold'),
    ('card_authorization', 'approved', 'disputed', 'Dispute approved authorization'),
    ('card_authorization', 'cleared', 'reversed', 'Reverse cleared authorization'),
    ('card_authorization', 'cleared', 'disputed', 'Dispute cleared authorization'),
    ('card_authorization', 'disputed', 'reversed', 'Reverse disputed authorization'),
    ('card_dispute', 'open', 'reviewing', 'Dispute review started'),
    ('card_dispute', 'open', 'submitted', 'Dispute submitted to processor'),
    ('card_dispute', 'open', 'won', 'Dispute won'),
    ('card_dispute', 'open', 'lost', 'Dispute lost'),
    ('card_dispute', 'open', 'canceled', 'Dispute canceled'),
    ('card_dispute', 'reviewing', 'submitted', 'Dispute submitted after review'),
    ('card_dispute', 'reviewing', 'won', 'Dispute won after review'),
    ('card_dispute', 'reviewing', 'lost', 'Dispute lost after review'),
    ('card_dispute', 'reviewing', 'canceled', 'Dispute canceled after review'),
    ('card_dispute', 'submitted', 'won', 'Processor accepted dispute'),
    ('card_dispute', 'submitted', 'lost', 'Processor rejected dispute'),
    ('card_dispute', 'submitted', 'canceled', 'Submitted dispute canceled'),
    ('fx_quote', 'pending', 'accepted', 'Quote consumed by conversion'),
    ('fx_quote', 'pending', 'expired', 'Quote expired'),
    ('fx_quote', 'pending', 'canceled', 'Quote canceled'),
    ('fx_conversion', 'completed', 'reversed', 'FX conversion reversed'),
    ('crypto_chain_transaction', 'pending', 'confirmed', 'Chain transaction confirmed'),
    ('crypto_chain_transaction', 'pending', 'failed', 'Chain transaction failed'),
    ('crypto_chain_transaction', 'confirmed', 'reversed', 'Confirmed chain transaction reversed'),
    ('crypto_travel_rule_transfer', 'pending', 'submitted', 'Travel rule submitted'),
    ('crypto_travel_rule_transfer', 'pending', 'accepted', 'Travel rule accepted'),
    ('crypto_travel_rule_transfer', 'pending', 'rejected', 'Travel rule rejected'),
    ('crypto_travel_rule_transfer', 'pending', 'expired', 'Travel rule expired'),
    ('crypto_travel_rule_transfer', 'pending', 'not_required', 'Travel rule deemed not required'),
    ('crypto_travel_rule_transfer', 'submitted', 'accepted', 'Submitted travel rule accepted'),
    ('crypto_travel_rule_transfer', 'submitted', 'rejected', 'Submitted travel rule rejected'),
    ('crypto_travel_rule_transfer', 'submitted', 'expired', 'Submitted travel rule expired'),
    ('inbound_payment', 'received', 'completed', 'Inbound payment matched and credited'),
    ('inbound_payment', 'received', 'failed', 'Inbound payment rejected')
ON CONFLICT (machine_name, from_status, to_status) DO NOTHING;

DROP TRIGGER IF EXISTS payment_review_cases_financial_state_transition ON payment_review_cases;
CREATE TRIGGER payment_review_cases_financial_state_transition
BEFORE UPDATE OF status ON payment_review_cases
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('payment_review_case');

DROP TRIGGER IF EXISTS wallet_adjustment_requests_financial_state_transition ON wallet_balance_adjustment_requests;
CREATE TRIGGER wallet_adjustment_requests_financial_state_transition
BEFORE UPDATE OF status ON wallet_balance_adjustment_requests
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('wallet_adjustment_request');

DROP TRIGGER IF EXISTS savings_goals_financial_state_transition ON savings_goals;
CREATE TRIGGER savings_goals_financial_state_transition
BEFORE UPDATE OF status ON savings_goals
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('savings_goal');

DROP TRIGGER IF EXISTS virtual_cards_financial_state_transition ON virtual_cards;
CREATE TRIGGER virtual_cards_financial_state_transition
BEFORE UPDATE OF status ON virtual_cards
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('virtual_card');

DROP TRIGGER IF EXISTS card_authorizations_financial_state_transition ON card_authorizations;
CREATE TRIGGER card_authorizations_financial_state_transition
BEFORE UPDATE OF status ON card_authorizations
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('card_authorization');

DROP TRIGGER IF EXISTS card_disputes_financial_state_transition ON card_disputes;
CREATE TRIGGER card_disputes_financial_state_transition
BEFORE UPDATE OF status ON card_disputes
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('card_dispute');

DROP TRIGGER IF EXISTS fx_quotes_financial_state_transition ON fx_quotes;
CREATE TRIGGER fx_quotes_financial_state_transition
BEFORE UPDATE OF status ON fx_quotes
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('fx_quote');

DROP TRIGGER IF EXISTS fx_conversions_financial_state_transition ON fx_conversions;
CREATE TRIGGER fx_conversions_financial_state_transition
BEFORE UPDATE OF status ON fx_conversions
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('fx_conversion');

DROP TRIGGER IF EXISTS crypto_chain_transactions_financial_state_transition ON crypto_chain_transactions;
CREATE TRIGGER crypto_chain_transactions_financial_state_transition
BEFORE UPDATE OF status ON crypto_chain_transactions
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('crypto_chain_transaction');

DROP TRIGGER IF EXISTS crypto_travel_rule_transfers_financial_state_transition ON crypto_travel_rule_transfers;
CREATE TRIGGER crypto_travel_rule_transfers_financial_state_transition
BEFORE UPDATE OF status ON crypto_travel_rule_transfers
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('crypto_travel_rule_transfer');

DROP TRIGGER IF EXISTS inbound_payments_financial_state_transition ON inbound_payments;
CREATE TRIGGER inbound_payments_financial_state_transition
BEFORE UPDATE OF status ON inbound_payments
FOR EACH ROW EXECUTE FUNCTION enforce_financial_status_transition('inbound_payment');

CREATE INDEX IF NOT EXISTS transfers_status_created_at_idx
    ON transfers (status, created_at DESC);

CREATE INDEX IF NOT EXISTS transfers_user_status_created_at_idx
    ON transfers (user_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS transfers_settlement_due_cover_idx
    ON transfers (status, settlement_next_attempt_at, settlement_attempts, created_at)
    WHERE transfer_type = 'sepa';

CREATE INDEX IF NOT EXISTS sepa_provider_report_items_report_processed_idx
    ON sepa_provider_report_items (report_id, processed, matched, created_at);

CREATE INDEX IF NOT EXISTS inbound_payments_status_created_at_idx
    ON inbound_payments (status, created_at DESC);

CREATE INDEX IF NOT EXISTS audit_events_type_created_at_idx
    ON audit_events (event_type, created_at DESC);

CREATE INDEX IF NOT EXISTS audit_events_target_created_at_idx
    ON audit_events (target_type, target_id, created_at DESC);

CREATE INDEX IF NOT EXISTS auth_sessions_active_expiry_idx
    ON auth_sessions (expires_at, user_id)
    WHERE revoked_at IS NULL;

CREATE INDEX IF NOT EXISTS card_authorizations_status_created_at_idx
    ON card_authorizations (status, created_at DESC);

CREATE INDEX IF NOT EXISTS card_issuer_webhook_events_processed_at_idx
    ON card_issuer_webhook_events (processed_at DESC);

CREATE INDEX IF NOT EXISTS crypto_chain_transactions_wallet_status_idx
    ON crypto_chain_transactions (crypto_wallet_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS payment_status_events_status_created_at_idx
    ON payment_status_events (to_status, created_at DESC);

INSERT INTO database_index_reviews (
    review_reference, table_name, index_name, query_pattern, expected_volume, risk_level, decision, decision_reason, reviewed_by, next_review_at
) VALUES
    ('index-review-transfers-status-created-2026-06-24', 'transfers', 'transfers_status_created_at_idx', 'admin/payment dashboards filter by status and newest first', 'high write, high read', 'high', 'add', 'Avoid sequential scans for settlement and admin transfer queues', 'engineering', now() + interval '90 days'),
    ('index-review-audit-events-target-2026-06-24', 'audit_events', 'audit_events_target_created_at_idx', 'audit evidence packages fetch target timeline', 'append-only high volume', 'high', 'add', 'Audit export and evidence package paths need target/time lookup', 'engineering', now() + interval '90 days'),
    ('index-review-auth-sessions-expiry-2026-06-24', 'auth_sessions', 'auth_sessions_active_expiry_idx', 'session cleanup and active session lists by expiry', 'medium write, high read', 'medium', 'add', 'Supports session expiry cleanup without scanning revoked sessions', 'engineering', now() + interval '90 days'),
    ('index-review-webhooks-processed-2026-06-24', 'card_issuer_webhook_events', 'card_issuer_webhook_events_processed_at_idx', 'webhook retention and replay investigations by processed time', 'high append', 'medium', 'add', 'Supports retention/archive scans and incident reviews', 'engineering', now() + interval '90 days')
ON CONFLICT (review_reference) 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
    ('audit_events', 'audit', 2555, 365, 'cold_storage_export', 'worm-audit-archive', true, false, true, 'active', 'security'),
    ('ledger_journal_entries', 'ledger', 3650, 730, 'cold_storage_export', 'finance-ledger-archive', true, false, true, 'active', 'finance'),
    ('ledger_journal_lines', 'ledger', 3650, 730, 'cold_storage_export', 'finance-ledger-archive', true, false, true, 'active', 'finance'),
    ('card_issuer_webhook_events', 'webhook', 730, 90, 'delete_after_export', 'webhook-archive', true, true, true, 'active', 'cards'),
    ('sepa_provider_reports', 'provider_report', 2555, 365, 'cold_storage_export', 'provider-report-archive', true, false, true, 'active', 'operations'),
    ('sepa_provider_report_items', 'provider_report', 2555, 365, 'cold_storage_export', 'provider-report-archive', true, false, true, 'active', 'operations'),
    ('auth_sessions', 'session', 180, 30, 'delete_after_export', 'security-session-archive', false, true, true, 'active', 'security'),
    ('payment_notifications', 'notification', 730, 180, 'delete_after_export', 'notification-archive', true, true, true, 'active', 'support'),
    ('cardholder_notifications', 'notification', 730, 180, 'delete_after_export', 'notification-archive', true, true, true, 'active', 'cards')
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
    ('database_release_migration_plans', '*', 'audit', 'confidential', 'legal_obligation/risk_management', 'audit_logs_default', 'default', false, 'engineering', 'Release migration dry-run and rollback approval records'),
    ('database_restore_drill_evidence', '*', 'audit', 'confidential', 'legal_obligation/risk_management', 'audit_logs_default', 'default', false, 'platform', 'Backup restore drill evidence and validation summaries'),
    ('database_index_reviews', '*', 'operational', 'confidential', 'risk_management', 'operational_records_default', 'default', false, 'engineering', 'High-volume query/index review evidence'),
    ('data_archival_policies', '*', 'audit', 'confidential', 'legal_obligation/risk_management', 'audit_logs_default', 'default', false, 'privacy', 'Archival policy configuration and evidence requirements'),
    ('financial_state_machine_transitions', '*', 'audit', 'confidential', 'risk_management', 'audit_logs_default', 'default', false, 'engineering', 'Allowed financial state transition registry')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;