CREATE TABLE provider_integrations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_type text NOT NULL CHECK (provider_type IN ('bank_ledger', 'card_issuer', 'identity', 'sanctions', 'custody', 'blockchain_analytics', 'market_data', 'payment_rail')),
    provider_name text NOT NULL,
    mode text NOT NULL DEFAULT 'sandbox' CHECK (mode IN ('local', 'sandbox', 'contracted', 'disabled')),
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'configured', 'approved', 'disabled', 'failed')),
    environment text NOT NULL DEFAULT 'staging' CHECK (environment IN ('development', 'test', 'ci', 'staging', 'preprod', 'production')),
    owner_team text NOT NULL DEFAULT 'operations' CHECK (owner_team IN ('operations', 'finance', 'compliance', 'risk', 'support', 'security', 'engineering')),
    contract_reference text NOT NULL DEFAULT '',
    credentials_reference text NOT NULL DEFAULT '',
    webhook_secret_reference text NOT NULL DEFAULT '',
    api_base_url text NOT NULL DEFAULT '',
    timeout_ms integer NOT NULL DEFAULT 3000 CHECK (timeout_ms BETWEEN 100 AND 30000),
    retry_max_attempts integer NOT NULL DEFAULT 3 CHECK (retry_max_attempts BETWEEN 1 AND 5),
    circuit_failure_threshold integer NOT NULL DEFAULT 5 CHECK (circuit_failure_threshold BETWEEN 1 AND 100),
    circuit_cooldown_seconds integer NOT NULL DEFAULT 60 CHECK (circuit_cooldown_seconds BETWEEN 1 AND 3600),
    degraded_mode text NOT NULL DEFAULT 'fail_closed' CHECK (degraded_mode IN ('fail_closed', 'read_only', 'manual_review')),
    sandbox_enabled boolean NOT NULL DEFAULT true,
    production_enabled boolean NOT NULL DEFAULT false,
    last_verified_at timestamptz,
    next_review_at timestamptz,
    evidence_reference text NOT NULL DEFAULT '',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    approved_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(),
    UNIQUE (provider_type, provider_name, environment),
    CHECK (status <> 'approved' OR evidence_reference <> ''),
    CHECK (mode <> 'contracted' OR status <> 'approved' OR credentials_reference <> ''),
    CHECK (production_enabled = false OR (mode = 'contracted' AND status = 'approved' AND evidence_reference <> '' AND credentials_reference <> ''))
);

CREATE INDEX provider_integrations_type_status_idx
    ON provider_integrations (provider_type, status, updated_at DESC);

CREATE INDEX provider_integrations_production_risk_idx
    ON provider_integrations (production_enabled, mode, status)
    WHERE production_enabled = true;

CREATE TRIGGER provider_integrations_set_updated_at
BEFORE UPDATE ON provider_integrations
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE provider_outbound_calls (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_type text NOT NULL CHECK (provider_type IN ('bank_ledger', 'card_issuer', 'identity', 'sanctions', 'custody', 'blockchain_analytics', 'market_data', 'payment_rail', 'unknown')),
    provider_name text NOT NULL,
    operation text NOT NULL,
    idempotency_key text NOT NULL,
    status text NOT NULL CHECK (status IN ('success', 'error', 'timeout', 'circuit_open')),
    attempts integer NOT NULL DEFAULT 1 CHECK (attempts >= 0),
    duration_ms bigint NOT NULL DEFAULT 0 CHECK (duration_ms >= 0),
    request_hash text NOT NULL DEFAULT '',
    redacted_request jsonb NOT NULL DEFAULT '{}'::jsonb,
    redacted_response jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_code text NOT NULL DEFAULT '',
    error_message text NOT NULL DEFAULT '',
    degraded_mode text NOT NULL DEFAULT 'fail_closed',
    circuit_state text NOT NULL DEFAULT 'closed',
    started_at timestamptz NOT NULL,
    finished_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (finished_at >= started_at)
);

CREATE INDEX provider_outbound_calls_provider_created_idx
    ON provider_outbound_calls (provider_type, provider_name, created_at DESC);

CREATE INDEX provider_outbound_calls_status_created_idx
    ON provider_outbound_calls (status, created_at DESC);

CREATE INDEX provider_outbound_calls_idempotency_idx
    ON provider_outbound_calls (provider_type, provider_name, operation, idempotency_key, created_at DESC);

CREATE TABLE provider_webhook_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_type text NOT NULL CHECK (provider_type IN ('bank_ledger', 'card_issuer', 'identity', 'sanctions', 'custody', 'blockchain_analytics', 'market_data', 'payment_rail')),
    provider_name text NOT NULL,
    external_event_id text NOT NULL,
    event_type text NOT NULL,
    sequence_number bigint,
    signature_status text NOT NULL CHECK (signature_status IN ('verified', 'invalid', 'missing')),
    replay_status text NOT NULL DEFAULT 'accepted' CHECK (replay_status IN ('accepted', 'duplicate')),
    ordering_status text NOT NULL DEFAULT 'no_sequence' CHECK (ordering_status IN ('in_order', 'no_sequence', 'gap', 'regression')),
    processing_status text NOT NULL DEFAULT 'received' CHECK (processing_status IN ('received', 'processing', 'processed', 'quarantined', 'failed', 'ignored')),
    payload_hash text NOT NULL,
    redacted_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    received_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider_type, provider_name, external_event_id)
);

CREATE INDEX provider_webhook_events_provider_received_idx
    ON provider_webhook_events (provider_type, provider_name, received_at DESC);

CREATE INDEX provider_webhook_events_status_idx
    ON provider_webhook_events (signature_status, replay_status, ordering_status, processing_status, created_at DESC);

CREATE INDEX provider_webhook_events_sequence_idx
    ON provider_webhook_events (provider_type, provider_name, event_type, sequence_number DESC)
    WHERE sequence_number IS NOT NULL;

CREATE TABLE provider_sandbox_test_runs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_type text NOT NULL CHECK (provider_type IN ('bank_ledger', 'card_issuer', 'identity', 'sanctions', 'custody', 'blockchain_analytics', 'market_data', 'payment_rail')),
    provider_name text NOT NULL,
    suite_name text NOT NULL,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'passed', 'failed', 'waived')),
    passed_checks integer NOT NULL DEFAULT 0 CHECK (passed_checks >= 0),
    failed_checks integer NOT NULL DEFAULT 0 CHECK (failed_checks >= 0),
    skipped_checks integer NOT NULL DEFAULT 0 CHECK (skipped_checks >= 0),
    evidence_reference text NOT NULL DEFAULT '',
    summary text NOT NULL DEFAULT '',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    started_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz,
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (completed_at IS NULL OR completed_at >= started_at),
    CHECK (status <> 'passed' OR evidence_reference <> '')
);

CREATE INDEX provider_sandbox_test_runs_provider_created_idx
    ON provider_sandbox_test_runs (provider_type, provider_name, created_at DESC);

CREATE INDEX provider_sandbox_test_runs_status_idx
    ON provider_sandbox_test_runs (status, created_at DESC);

INSERT INTO provider_integrations (
    provider_type, provider_name, mode, status, environment, owner_team,
    timeout_ms, retry_max_attempts, circuit_failure_threshold, circuit_cooldown_seconds,
    degraded_mode, sandbox_enabled, production_enabled, evidence_reference, metadata
) VALUES
    ('bank_ledger', 'local_bank_ledger', 'local', 'configured', 'development', 'finance', 3000, 3, 5, 60, 'fail_closed', true, false, 'local-development-adapter', '{"real_provider_required_for_production": true}'::jsonb),
    ('card_issuer', 'local_card_issuer', 'local', 'configured', 'development', 'operations', 3000, 3, 5, 60, 'fail_closed', true, false, 'local-development-adapter', '{"real_provider_required_for_production": true}'::jsonb),
    ('identity', 'local_identity', 'local', 'configured', 'development', 'compliance', 3000, 3, 5, 60, 'manual_review', true, false, 'local-development-adapter', '{"real_provider_required_for_production": true}'::jsonb),
    ('sanctions', 'local_sanctions', 'local', 'configured', 'development', 'compliance', 3000, 3, 5, 60, 'manual_review', true, false, 'local-development-adapter', '{"real_provider_required_for_production": true}'::jsonb),
    ('custody', 'local_custody', 'local', 'configured', 'development', 'operations', 3000, 3, 5, 60, 'fail_closed', true, false, 'local-development-adapter', '{"real_crypto_movement_enabled": false}'::jsonb),
    ('blockchain_analytics', 'local_blockchain_analytics', 'local', 'configured', 'development', 'compliance', 3000, 3, 5, 60, 'manual_review', true, false, 'local-development-adapter', '{"real_provider_required_for_real_crypto": true}'::jsonb),
    ('market_data', 'local_market_data', 'local', 'configured', 'development', 'finance', 3000, 3, 5, 60, 'manual_review', true, false, 'local-development-adapter', '{"real_provider_required_for_production_fx": true}'::jsonb),
    ('payment_rail', 'local_sepa_simulator', 'local', 'configured', 'development', 'operations', 3000, 3, 5, 60, 'fail_closed', true, false, 'local-development-adapter', '{"real_provider_required_for_production": true}'::jsonb)
ON CONFLICT (provider_type, provider_name, environment) DO NOTHING;

INSERT INTO provider_sandbox_test_runs (
    provider_type, provider_name, suite_name, status, skipped_checks, summary, metadata
) VALUES
    ('bank_ledger', 'local_bank_ledger', 'contract-readiness', 'waived', 4, 'Local development adapter; replace with contracted sandbox certification evidence.', '{"required_checks":["account_issuance","balance_report","webhook_signature","reconciliation_feed"]}'::jsonb),
    ('card_issuer', 'local_card_issuer', 'contract-readiness', 'waived', 5, 'Local development adapter; replace with processor sandbox evidence.', '{"required_checks":["card_create","status_sync","auth_webhook","clearing","dispute"]}'::jsonb),
    ('identity', 'local_identity', 'contract-readiness', 'waived', 4, 'Local development adapter; replace with identity-provider sandbox evidence.', '{"required_checks":["verification_start","evidence_fetch","decision_webhook","replay_protection"]}'::jsonb),
    ('sanctions', 'local_sanctions', 'contract-readiness', 'waived', 4, 'Local development adapter; replace with sanctions/PEP/adverse-media sandbox evidence.', '{"required_checks":["person_screening","ongoing_monitoring","false_positive","provider_outage"]}'::jsonb),
    ('custody', 'local_custody', 'contract-readiness', 'waived', 5, 'Local development adapter; real crypto movement disabled unless contracted custody is approved.', '{"required_checks":["wallet_create","address_create","deposit_webhook","withdrawal","travel_rule"]}'::jsonb)
ON CONFLICT 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
    ('provider_integrations', 'operational', 2555, 365, 'cold_storage_export', 'provider-control-archive', true, false, true, 'active', 'operations'),
    ('provider_outbound_calls', 'provider_report', 730, 90, 'delete_after_export', 'provider-call-archive', true, true, true, 'active', 'security'),
    ('provider_webhook_events', 'webhook', 730, 90, 'delete_after_export', 'provider-webhook-archive', true, true, true, 'active', 'operations'),
    ('provider_sandbox_test_runs', 'operational', 2555, 365, 'cold_storage_export', 'provider-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
    ('provider_integrations', '*', 'operational', 'confidential', 'risk_management/contract', 'operational_records_default', 'default', false, 'operations', 'Provider contract, credential-reference and readiness metadata; no raw secrets stored'),
    ('provider_outbound_calls', '*', 'audit', 'confidential', 'legal_obligation/risk_management', 'audit_logs_default', 'default', false, 'security', 'Redacted provider request/response audit trail with idempotency keys and timings'),
    ('provider_webhook_events', '*', 'audit', 'confidential', 'legal_obligation/risk_management', 'audit_logs_default', 'default', false, 'operations', 'Redacted provider webhook inbox with signature, replay and ordering decisions'),
    ('provider_sandbox_test_runs', '*', 'audit', 'confidential', 'risk_management', 'audit_logs_default', 'default', false, 'operations', 'Provider sandbox certification evidence records')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;