CREATE TABLE payment_limit_rules (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES users(id) ON DELETE CASCADE,
    operation text NOT NULL CHECK (operation IN ('transfer_internal', 'transfer_sepa', 'fx_conversion', 'card_authorization')),
    currency char(3) REFERENCES currencies(code) ON DELETE RESTRICT,
    single_transaction_limit_cents bigint NOT NULL DEFAULT 0 CHECK (single_transaction_limit_cents >= 0),
    daily_limit_cents bigint NOT NULL DEFAULT 0 CHECK (daily_limit_cents >= 0),
    monthly_limit_cents bigint NOT NULL DEFAULT 0 CHECK (monthly_limit_cents >= 0),
    daily_count_limit integer NOT NULL DEFAULT 0 CHECK (daily_count_limit >= 0),
    monthly_count_limit integer NOT NULL DEFAULT 0 CHECK (monthly_count_limit >= 0),
    active boolean NOT NULL DEFAULT true,
    description text NOT NULL DEFAULT '',
    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(),
    CHECK (
        single_transaction_limit_cents > 0
        OR daily_limit_cents > 0
        OR monthly_limit_cents > 0
        OR daily_count_limit > 0
        OR monthly_count_limit > 0
    )
);

CREATE UNIQUE INDEX payment_limit_rules_platform_active_uniq
    ON payment_limit_rules (operation, COALESCE(TRIM(currency)::text, ''))
    WHERE user_id IS NULL AND active = true;

CREATE UNIQUE INDEX payment_limit_rules_user_active_uniq
    ON payment_limit_rules (user_id, operation, COALESCE(TRIM(currency)::text, ''))
    WHERE user_id IS NOT NULL AND active = true;

CREATE INDEX payment_limit_rules_lookup_idx
    ON payment_limit_rules (operation, user_id, currency, active);

CREATE TABLE risk_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    operation text NOT NULL CHECK (operation IN ('transfer_internal', 'transfer_sepa', 'fx_conversion', 'card_authorization')),
    source_type text NOT NULL,
    source_id text NOT NULL DEFAULT '',
    account_id uuid REFERENCES accounts(id) ON DELETE SET NULL,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    decision text NOT NULL CHECK (decision IN ('allow', 'review', 'block')),
    severity text NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    reason text NOT NULL,
    limit_rule_id uuid REFERENCES payment_limit_rules(id) ON DELETE SET NULL,
    details jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX risk_events_created_at_idx ON risk_events (created_at DESC);
CREATE INDEX risk_events_user_created_at_idx ON risk_events (user_id, created_at DESC);
CREATE INDEX risk_events_decision_created_at_idx ON risk_events (decision, created_at DESC);
CREATE INDEX risk_events_operation_created_at_idx ON risk_events (operation, created_at DESC);

CREATE TRIGGER payment_limit_rules_set_updated_at
BEFORE UPDATE ON payment_limit_rules
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO payment_limit_rules (
    operation, currency, single_transaction_limit_cents, daily_limit_cents, monthly_limit_cents,
    daily_count_limit, monthly_count_limit, description
) VALUES
    ('transfer_internal', NULL, 5000000, 10000000, 50000000, 50, 500, 'Default internal transfer limit'),
    ('transfer_sepa', NULL, 1000000, 2500000, 20000000, 20, 200, 'Default SEPA transfer limit'),
    ('fx_conversion', NULL, 2500000, 5000000, 30000000, 25, 250, 'Default FX conversion limit'),
    ('card_authorization', NULL, 500000, 1000000, 5000000, 100, 2000, 'Default card authorization limit');