CREATE TABLE crypto_custody_scope_decisions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    product_scope text NOT NULL CHECK (product_scope IN ('disabled', 'simulated_only', 'real_custody')),
    decision_status text NOT NULL DEFAULT 'draft' CHECK (decision_status IN ('draft', 'approved', 'rejected')),
    real_movement_enabled boolean NOT NULL DEFAULT false,
    jurisdiction_scope text[] NOT NULL DEFAULT ARRAY[]::text[],
    rationale text NOT NULL,
    decided_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    decided_at timestamptz,
    evidence_reference text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX crypto_custody_scope_decisions_status_idx
    ON crypto_custody_scope_decisions (decision_status, created_at DESC);

CREATE TRIGGER crypto_custody_scope_decisions_set_updated_at
BEFORE UPDATE ON crypto_custody_scope_decisions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE crypto_legal_memos (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    jurisdiction text NOT NULL,
    activity text NOT NULL,
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'in_review', 'approved', 'rejected', 'expired')),
    licensing_required boolean,
    registration_required boolean,
    counsel_name text,
    memo_reference text,
    summary text NOT NULL,
    valid_from date,
    valid_until date,
    approved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    approved_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (jurisdiction, activity)
);

CREATE INDEX crypto_legal_memos_status_idx ON crypto_legal_memos (status, jurisdiction);

CREATE TRIGGER crypto_legal_memos_set_updated_at
BEFORE UPDATE ON crypto_legal_memos
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE crypto_custody_provider_configs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL UNIQUE,
    provider_type text NOT NULL CHECK (provider_type IN ('local_simulator', 'custodian', 'exchange', 'self_custody', 'disabled')),
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'sandbox', 'contracted', 'disabled', 'terminated')),
    real_movement_enabled boolean NOT NULL DEFAULT false,
    contract_reference text,
    api_environment text NOT NULL DEFAULT 'sandbox' CHECK (api_environment IN ('local', 'sandbox', 'production')),
    webhook_signing_enabled boolean NOT NULL DEFAULT false,
    key_management_model text NOT NULL DEFAULT 'provider',
    notes text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO crypto_custody_provider_configs (
    provider, provider_type, status, real_movement_enabled, api_environment,
    webhook_signing_enabled, key_management_model, notes
) VALUES (
    'local_custody', 'local_simulator', 'sandbox', false, 'local',
    false, 'local_simulator', 'Development-only custody simulator; real movement disabled.'
) ON CONFLICT (provider) DO NOTHING;

CREATE TRIGGER crypto_custody_provider_configs_set_updated_at
BEFORE UPDATE ON crypto_custody_provider_configs
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE crypto_address_screenings (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    crypto_wallet_id uuid REFERENCES crypto_wallets(id) ON DELETE SET NULL,
    crypto_asset_id uuid REFERENCES crypto_assets(id) ON DELETE SET NULL,
    address text NOT NULL,
    address_tag text,
    network text NOT NULL,
    context text NOT NULL DEFAULT 'transaction' CHECK (context IN ('deposit', 'withdrawal', 'transaction', 'manual_review')),
    provider text NOT NULL,
    external_screening_id text,
    decision text NOT NULL CHECK (decision IN ('clear', 'review', 'block')),
    risk_score integer NOT NULL CHECK (risk_score BETWEEN 0 AND 100),
    category text NOT NULL DEFAULT 'none',
    matched boolean NOT NULL DEFAULT false,
    details jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX crypto_address_screenings_address_idx
    ON crypto_address_screenings (network, address, created_at DESC);

CREATE INDEX crypto_address_screenings_decision_idx
    ON crypto_address_screenings (decision, risk_score DESC, created_at DESC);

CREATE TABLE crypto_chain_transactions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    crypto_wallet_id uuid NOT NULL REFERENCES crypto_wallets(id) ON DELETE RESTRICT,
    crypto_asset_id uuid NOT NULL REFERENCES crypto_assets(id) ON DELETE RESTRICT,
    crypto_address_id uuid REFERENCES crypto_addresses(id) ON DELETE SET NULL,
    address_screening_id uuid REFERENCES crypto_address_screenings(id) ON DELETE SET NULL,
    direction text NOT NULL CHECK (direction IN ('deposit', 'withdrawal')),
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'failed', 'reversed')),
    network text NOT NULL,
    address text NOT NULL,
    address_tag text,
    tx_hash text,
    amount_base_units numeric(78, 0) NOT NULL CHECK (amount_base_units > 0),
    fee_base_units numeric(78, 0) NOT NULL DEFAULT 0 CHECK (fee_base_units >= 0),
    confirmations integer NOT NULL DEFAULT 0 CHECK (confirmations >= 0),
    required_confirmations integer NOT NULL DEFAULT 1 CHECK (required_confirmations >= 0),
    provider text NOT NULL DEFAULT 'local_custody',
    provider_reference text,
    travel_rule_required boolean NOT NULL DEFAULT false,
    travel_rule_status text NOT NULL DEFAULT 'not_required' CHECK (travel_rule_status IN ('not_required', 'pending', 'submitted', 'accepted', 'rejected', 'expired')),
    failure_reason text,
    reversal_of_transaction_id uuid REFERENCES crypto_chain_transactions(id) ON DELETE SET NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_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(),
    confirmed_at timestamptz,
    failed_at timestamptz,
    reversed_at timestamptz
);

CREATE UNIQUE INDEX crypto_chain_transactions_provider_reference_uniq
    ON crypto_chain_transactions (provider, provider_reference)
    WHERE provider_reference IS NOT NULL;

CREATE UNIQUE INDEX crypto_chain_transactions_network_tx_hash_uniq
    ON crypto_chain_transactions (network, tx_hash)
    WHERE tx_hash IS NOT NULL;

CREATE INDEX crypto_chain_transactions_user_created_idx
    ON crypto_chain_transactions (user_id, created_at DESC);

CREATE INDEX crypto_chain_transactions_status_idx
    ON crypto_chain_transactions (status, direction, created_at DESC);

CREATE TRIGGER crypto_chain_transactions_set_updated_at
BEFORE UPDATE ON crypto_chain_transactions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE crypto_chain_transaction_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    crypto_chain_transaction_id uuid NOT NULL REFERENCES crypto_chain_transactions(id) ON DELETE RESTRICT,
    event_type text NOT NULL,
    status_before text,
    status_after text,
    provider text,
    provider_reference text,
    details jsonb NOT NULL DEFAULT '{}'::jsonb,
    actor_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX crypto_chain_transaction_events_transaction_idx
    ON crypto_chain_transaction_events (crypto_chain_transaction_id, created_at DESC);

CREATE TABLE crypto_travel_rule_transfers (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    crypto_chain_transaction_id uuid NOT NULL UNIQUE REFERENCES crypto_chain_transactions(id) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('not_required', 'pending', 'submitted', 'accepted', 'rejected', 'expired')),
    originator_name text,
    originator_country char(2),
    originator_wallet_provider text,
    beneficiary_name text,
    beneficiary_country char(2),
    beneficiary_wallet_provider text,
    beneficiary_wallet_type text NOT NULL DEFAULT 'unknown' CHECK (beneficiary_wallet_type IN ('hosted', 'unhosted', 'unknown')),
    provider text NOT NULL DEFAULT 'manual',
    external_reference text,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    rejection_reason text,
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    decided_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX crypto_travel_rule_transfers_status_idx
    ON crypto_travel_rule_transfers (status, created_at DESC);

CREATE TRIGGER crypto_travel_rule_transfers_set_updated_at
BEFORE UPDATE ON crypto_travel_rule_transfers
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE crypto_custody_responsibility_matrix (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    custody_model text NOT NULL CHECK (custody_model IN ('omnibus', 'segregated', 'mpc', 'self_custody', 'non_custodial')),
    key_owner text NOT NULL CHECK (key_owner IN ('provider', 'platform', 'customer', 'shared_mpc')),
    signing_authority text NOT NULL,
    backup_owner text NOT NULL,
    recovery_owner text NOT NULL,
    hsm_or_kms text NOT NULL,
    policy_document_reference text NOT NULL,
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'approved', 'retired')),
    approved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    approved_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, custody_model)
);

CREATE INDEX crypto_custody_responsibility_matrix_status_idx
    ON crypto_custody_responsibility_matrix (status, provider);

CREATE TRIGGER crypto_custody_responsibility_matrix_set_updated_at
BEFORE UPDATE ON crypto_custody_responsibility_matrix
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE stablecoin_issuer_monitoring (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    crypto_asset_id uuid NOT NULL UNIQUE REFERENCES crypto_assets(id) ON DELETE RESTRICT,
    issuer_name text NOT NULL,
    peg_currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    peg_status text NOT NULL DEFAULT 'unknown' CHECK (peg_status IN ('on_peg', 'watch', 'depegged', 'unknown')),
    risk_rating text NOT NULL DEFAULT 'medium' CHECK (risk_rating IN ('low', 'medium', 'high', 'critical')),
    reserve_attestation_url text,
    reserve_attestation_at timestamptz,
    next_review_at timestamptz,
    notes text NOT NULL DEFAULT '',
    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()
);

CREATE INDEX stablecoin_issuer_monitoring_rating_idx
    ON stablecoin_issuer_monitoring (risk_rating, peg_status, next_review_at);

CREATE TRIGGER stablecoin_issuer_monitoring_set_updated_at
BEFORE UPDATE ON stablecoin_issuer_monitoring
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE stablecoin_network_controls (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    crypto_asset_id uuid NOT NULL REFERENCES crypto_assets(id) ON DELETE RESTRICT,
    network text NOT NULL,
    deposits_enabled boolean NOT NULL DEFAULT false,
    withdrawals_enabled boolean NOT NULL DEFAULT false,
    min_confirmations integer NOT NULL DEFAULT 12 CHECK (min_confirmations >= 0),
    contract_address text,
    status text NOT NULL DEFAULT 'disabled' CHECK (status IN ('active', 'disabled', 'sunset')),
    notes text NOT NULL DEFAULT '',
    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(),
    UNIQUE (crypto_asset_id, network)
);

CREATE INDEX stablecoin_network_controls_status_idx
    ON stablecoin_network_controls (status, network);

CREATE TRIGGER stablecoin_network_controls_set_updated_at
BEFORE UPDATE ON stablecoin_network_controls
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO stablecoin_issuer_monitoring (
    crypto_asset_id, issuer_name, peg_currency, peg_status, risk_rating, notes
)
SELECT id,
    CASE symbol WHEN 'USDC' THEN 'Circle' WHEN 'USDT' THEN 'Tether' ELSE 'Unknown issuer' END,
    'USD',
    'unknown',
    'medium',
    'Seeded monitoring placeholder; replace with reviewed issuer/reserve evidence before real production use.'
FROM crypto_assets
WHERE asset_type = 'stablecoin'
ON CONFLICT (crypto_asset_id) DO NOTHING;

INSERT INTO stablecoin_network_controls (
    crypto_asset_id, network, deposits_enabled, withdrawals_enabled, min_confirmations,
    contract_address, status, notes
)
SELECT id, network, true, false,
    CASE network WHEN 'ethereum' THEN 12 ELSE 1 END,
    contract_address,
    'active',
    'Deposits enabled for development; withdrawals disabled until custody/legal approval.'
FROM crypto_assets
WHERE asset_type = 'stablecoin'
ON CONFLICT (crypto_asset_id, network) DO NOTHING;

INSERT INTO crypto_custody_scope_decisions (
    product_scope, decision_status, real_movement_enabled, jurisdiction_scope, rationale, evidence_reference
) VALUES (
    'simulated_only', 'approved', false, ARRAY['development'],
    'Crypto wallets are enabled only with local simulated custody until legal, licensing and provider approvals are complete.',
    'system-seed-2026-06'
) ON CONFLICT 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
    ('crypto_chain_transactions', '*', 'crypto', 'restricted', 'contract/legal_obligation', 'crypto_records_default', 'customer_region', false, 'crypto', 'Crypto chain transaction facts and provider references'),
    ('crypto_address_screenings', '*', 'crypto', 'restricted', 'legal_obligation/security', 'crypto_records_default', 'customer_region', false, 'compliance', 'Blockchain analytics address-screening results'),
    ('crypto_travel_rule_transfers', '*', 'crypto', 'restricted', 'legal_obligation', 'crypto_records_default', 'customer_region', false, 'compliance', 'Travel rule originator and beneficiary transfer records'),
    ('stablecoin_issuer_monitoring', '*', 'crypto', 'confidential', 'legal_obligation/risk_management', 'crypto_records_default', 'default', false, 'crypto', 'Stablecoin issuer monitoring and reserve evidence metadata')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;