-- =====================================================================
-- Security, fraud engine, and logging support tables.
-- Run AFTER schema.sql and schema_provider_modules.sql.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- fraud_rules — configurable, weighted risk factors. `code` is the
-- stable identifier FraudEngine's risk-factor classes reference;
-- `weight` and `is_active` are editable at runtime without a deploy.
-- ---------------------------------------------------------------------
CREATE TABLE fraud_rules (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    code        VARCHAR(60)  NOT NULL COMMENT 'e.g. device_fingerprint_new, impossible_travel, velocity_transfer',
    name        VARCHAR(150) NOT NULL,
    category    ENUM('device','network','identity','behavioral','transaction','crypto') NOT NULL,
    weight      SMALLINT     NOT NULL DEFAULT 10 COMMENT 'points added to the risk score when this rule fires',
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    config      JSON         NULL COMMENT 'rule-specific thresholds, e.g. {"max_km_per_hour": 800}',
    created_at  DATETIME     NOT NULL,
    updated_at  DATETIME     NOT NULL,
    UNIQUE KEY uq_fraud_rules_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- fraud_assessments — one row per risk evaluation. `signals` is the
-- full breakdown (which rules fired, points each contributed) kept
-- for audit and for tuning rule weights later.
-- ---------------------------------------------------------------------
CREATE TABLE fraud_assessments (
    id            CHAR(36)     NOT NULL PRIMARY KEY,
    user_id       CHAR(36)     NULL,
    subject_type  ENUM('login','registration','transfer','withdrawal','deposit','card_funding','kyc') NOT NULL,
    subject_id    CHAR(36)     NULL COMMENT 'the transactions.id / users.id / kyc.id this assessment covers',
    score         INT          NOT NULL DEFAULT 0,
    level         ENUM('low','medium','high','critical') NOT NULL DEFAULT 'low',
    action        ENUM('allow','otp','require_kyc','hold','manual_review','reject','freeze_wallet','lock_account') NOT NULL DEFAULT 'allow',
    signals       JSON         NOT NULL COMMENT 'array of {code, weight, triggered, detail}',
    ip_address    VARCHAR(45)  NULL,
    device_fingerprint VARCHAR(64) NULL,
    created_at    DATETIME     NOT NULL,
    KEY idx_fraud_assessments_user (user_id, created_at),
    KEY idx_fraud_assessments_subject (subject_type, subject_id),
    KEY idx_fraud_assessments_level (level),
    CONSTRAINT fk_fraud_assessments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- device_fingerprints — richer than `devices` (which is session/login
-- bookkeeping); this tracks trust state used specifically by the
-- fraud engine's device-risk signal.
-- ---------------------------------------------------------------------
CREATE TABLE device_fingerprints (
    id                  CHAR(36)     NOT NULL PRIMARY KEY,
    user_id             CHAR(36)     NOT NULL,
    fingerprint_hash    CHAR(64)     NOT NULL COMMENT 'sha256 of device+browser fingerprint components',
    browser_fingerprint VARCHAR(191) NULL,
    device_details      JSON         NULL COMMENT 'platform, screen, timezone, language, gpu, etc.',
    is_emulator         TINYINT(1)   NOT NULL DEFAULT 0,
    is_rooted           TINYINT(1)   NOT NULL DEFAULT 0,
    is_trusted          TINYINT(1)   NOT NULL DEFAULT 0,
    first_seen_at       DATETIME     NOT NULL,
    last_seen_at        DATETIME     NOT NULL,
    UNIQUE KEY uq_device_fingerprints_user_hash (user_id, fingerprint_hash),
    KEY idx_device_fingerprints_hash (fingerprint_hash),
    CONSTRAINT fk_device_fingerprints_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- ip_reputation_cache — caches VPN/proxy/datacenter/geo lookups so the
-- fraud engine doesn't hit an external IP intel service on every call.
-- ---------------------------------------------------------------------
CREATE TABLE ip_reputation_cache (
    id            CHAR(36)     NOT NULL PRIMARY KEY,
    ip_address    VARCHAR(45)  NOT NULL,
    is_vpn        TINYINT(1)   NOT NULL DEFAULT 0,
    is_proxy      TINYINT(1)   NOT NULL DEFAULT 0,
    is_datacenter TINYINT(1)   NOT NULL DEFAULT 0,
    is_tor        TINYINT(1)   NOT NULL DEFAULT 0,
    country       CHAR(2)      NULL,
    latitude      DECIMAL(9,6) NULL,
    longitude     DECIMAL(9,6) NULL,
    risk_score    SMALLINT     NOT NULL DEFAULT 0,
    checked_at    DATETIME     NOT NULL,
    expires_at    DATETIME     NOT NULL,
    UNIQUE KEY uq_ip_reputation_ip (ip_address),
    KEY idx_ip_reputation_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- disposable_email_domains — seed/deny list for the disposable-email signal
-- ---------------------------------------------------------------------
CREATE TABLE disposable_email_domains (
    id         CHAR(36)    NOT NULL PRIMARY KEY,
    domain     VARCHAR(191) NOT NULL,
    created_at DATETIME    NOT NULL,
    UNIQUE KEY uq_disposable_email_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- login_locations — last-known geo per user, feeds the impossible-
-- travel signal (distance/time between consecutive logins).
-- ---------------------------------------------------------------------
CREATE TABLE login_locations (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    user_id     CHAR(36)     NOT NULL,
    ip_address  VARCHAR(45)  NOT NULL,
    country     CHAR(2)      NULL,
    latitude    DECIMAL(9,6) NULL,
    longitude   DECIMAL(9,6) NULL,
    created_at  DATETIME     NOT NULL,
    KEY idx_login_locations_user (user_id, created_at),
    CONSTRAINT fk_login_locations_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- idempotency_keys — general-purpose idempotency cache for endpoints
-- that don't already have a domain-specific idempotency column
-- (transactions/wallet_ledger have their own). Middleware-level.
-- ---------------------------------------------------------------------
CREATE TABLE idempotency_keys (
    id            CHAR(36)     NOT NULL PRIMARY KEY,
    `key`         VARCHAR(191) NOT NULL,
    user_id       CHAR(36)     NULL,
    endpoint      VARCHAR(150) NOT NULL,
    request_hash  CHAR(64)     NOT NULL COMMENT 'sha256 of the request body, to detect key reuse with different params',
    response_body MEDIUMTEXT   NULL,
    status_code   SMALLINT     NULL,
    created_at    DATETIME     NOT NULL,
    expires_at    DATETIME     NOT NULL,
    UNIQUE KEY uq_idempotency_key (`key`, endpoint),
    KEY idx_idempotency_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- request_logs — one row per inbound API request (sampled/pruned in
-- production; see RequestLoggingMiddleware).
-- ---------------------------------------------------------------------
CREATE TABLE request_logs (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    method      VARCHAR(10)  NOT NULL,
    path        VARCHAR(255) NOT NULL,
    user_id     CHAR(36)     NULL,
    api_key_id  CHAR(36)     NULL,
    ip_address  VARCHAR(45)  NULL,
    status_code SMALLINT     NULL,
    duration_ms INT UNSIGNED NULL,
    created_at  DATETIME     NOT NULL,
    KEY idx_request_logs_created (created_at),
    KEY idx_request_logs_user (user_id),
    KEY idx_request_logs_path (path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- error_logs — unhandled exceptions, captured centrally
-- ---------------------------------------------------------------------
CREATE TABLE error_logs (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    level      ENUM('debug','info','warning','error','critical') NOT NULL DEFAULT 'error',
    message    TEXT         NOT NULL,
    context    JSON         NULL,
    file       VARCHAR(255) NULL,
    line       INT          NULL,
    request_path VARCHAR(255) NULL,
    user_id    CHAR(36)     NULL,
    created_at DATETIME     NOT NULL,
    KEY idx_error_logs_created (created_at),
    KEY idx_error_logs_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- Seed: baseline fraud rules (weights are starting points — tune via
-- the admin fraud endpoints, not a deploy).
-- =====================================================================
INSERT INTO fraud_rules (id, code, name, category, weight, is_active, config, created_at, updated_at) VALUES
    (UUID(), 'new_device', 'New/unrecognized device fingerprint', 'device', 10, 1, NULL, NOW(), NOW()),
    (UUID(), 'emulator_detected', 'Emulator detected', 'device', 35, 1, NULL, NOW(), NOW()),
    (UUID(), 'root_detected', 'Rooted/jailbroken device detected', 'device', 30, 1, NULL, NOW(), NOW()),
    (UUID(), 'vpn_detected', 'VPN usage detected', 'network', 15, 1, NULL, NOW(), NOW()),
    (UUID(), 'proxy_detected', 'Proxy usage detected', 'network', 15, 1, NULL, NOW(), NOW()),
    (UUID(), 'tor_detected', 'Tor exit node detected', 'network', 40, 1, NULL, NOW(), NOW()),
    (UUID(), 'datacenter_ip', 'Datacenter/hosting IP', 'network', 20, 1, NULL, NOW(), NOW()),
    (UUID(), 'impossible_travel', 'Impossible travel between logins', 'behavioral', 40, 1, '{"max_km_per_hour": 900}', NOW(), NOW()),
    (UUID(), 'velocity_transactions', 'Too many transactions in a short window', 'behavioral', 25, 1, '{"window_minutes": 10, "max_count": 5}', NOW(), NOW()),
    (UUID(), 'velocity_login', 'Too many login attempts in a short window', 'behavioral', 20, 1, '{"window_minutes": 10, "max_count": 8}', NOW(), NOW()),
    (UUID(), 'duplicate_account', 'Duplicate account signals (device/IP shared with other accounts)', 'identity', 25, 1, NULL, NOW(), NOW()),
    (UUID(), 'disposable_email', 'Disposable/temporary email domain', 'identity', 15, 1, NULL, NOW(), NOW()),
    (UUID(), 'failed_pin_attempts', 'Repeated failed transaction PIN attempts', 'behavioral', 20, 1, '{"threshold": 3}', NOW(), NOW()),
    (UUID(), 'failed_otp_attempts', 'Repeated failed OTP attempts', 'behavioral', 20, 1, '{"threshold": 3}', NOW(), NOW()),
    (UUID(), 'failed_kyc', 'Failed KYC verification', 'identity', 25, 1, NULL, NOW(), NOW()),
    (UUID(), 'high_value_transfer', 'Transfer amount far above account norm', 'transaction', 20, 1, '{"multiplier_of_avg": 5, "min_amount_minor": 50000000}', NOW(), NOW()),
    (UUID(), 'crypto_anomaly', 'Crypto deposit/swap anomaly (rate mismatch, rapid repeat)', 'crypto', 30, 1, NULL, NOW(), NOW()),
    (UUID(), 'wallet_abuse', 'Wallet abuse pattern (rapid credit/debit cycling)', 'transaction', 25, 1, '{"window_minutes": 15, "max_cycles": 4}', NOW(), NOW());