-- ============================================================
-- Optiqo Binary Options Trading Platform
-- Complete Database Schema - MySQL
-- Includes: Core, OTC Market, Wallet Rules & Trading Engine
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- Drop All Tables (reverse dependency order)
-- ============================================================
DROP TABLE IF EXISTS `schema_versions`;
DROP TABLE IF EXISTS `admin_logs`;
DROP TABLE IF EXISTS `trade_exposure`;
DROP TABLE IF EXISTS `bonus_transactions`;
DROP TABLE IF EXISTS `bonus_conversions`;
DROP TABLE IF EXISTS `trades`;
DROP TABLE IF EXISTS `withdrawals`;
DROP TABLE IF EXISTS `deposits`;
DROP TABLE IF EXISTS `transactions`;
DROP TABLE IF EXISTS `market_prices`;
DROP TABLE IF EXISTS `candle_data`;
DROP TABLE IF EXISTS `otc_market_prices`;
DROP TABLE IF EXISTS `otc_candles`;
DROP TABLE IF EXISTS `otc_trends`;
DROP TABLE IF EXISTS `otc_market_state`;
DROP TABLE IF EXISTS `otc_assets`;
DROP TABLE IF EXISTS `payout_rules`;
DROP TABLE IF EXISTS `wallet_balances`;
DROP TABLE IF EXISTS `wallet_rules`;
DROP TABLE IF EXISTS `bonus_rules`;
DROP TABLE IF EXISTS `risk_settings`;
DROP TABLE IF EXISTS `assets`;
DROP TABLE IF EXISTS `wallets`;
DROP TABLE IF EXISTS `password_reset_tokens`;
DROP TABLE IF EXISTS `personal_access_tokens`;
DROP TABLE IF EXISTS `users`;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- Users Table
-- ============================================================
CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `uuid` CHAR(36) NOT NULL UNIQUE,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
    `status` ENUM('active', 'suspended', 'banned') NOT NULL DEFAULT 'active',
    `phone` VARCHAR(50) NULL,
    `country` VARCHAR(100) NULL,
    `remember_token` VARCHAR(100) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    INDEX `idx_email` (`email`),
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_status` (`status`),
    INDEX `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Personal Access Tokens (Laravel Sanctum)
-- ============================================================
CREATE TABLE `personal_access_tokens` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `tokenable_type` VARCHAR(255) NOT NULL,
    `tokenable_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `token` VARCHAR(64) NOT NULL UNIQUE,
    `abilities` TEXT NULL,
    `last_used_at` TIMESTAMP NULL DEFAULT NULL,
    `expires_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_tokenable` (`tokenable_type`, `tokenable_id`),
    INDEX `idx_token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Password Reset Tokens
-- ============================================================
CREATE TABLE `password_reset_tokens` (
    `email` VARCHAR(255) NOT NULL PRIMARY KEY,
    `token` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Wallets Table
-- ============================================================
CREATE TABLE `wallets` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `balance` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `locked_balance` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    UNIQUE KEY `unique_user_currency` (`user_id`, `currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Assets Table (Market Trading Pairs)
-- ============================================================
CREATE TABLE `assets` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(20) NOT NULL UNIQUE COMMENT 'BTCUSDT, ETHUSDT, etc.',
    `name` VARCHAR(100) NOT NULL COMMENT 'Bitcoin, Ethereum, etc.',
    `base_currency` VARCHAR(10) NOT NULL COMMENT 'BTC, ETH, etc.',
    `quote_currency` VARCHAR(10) NOT NULL DEFAULT 'USDT',
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `payout_percentage` DECIMAL(5, 2) NOT NULL DEFAULT 85.00 COMMENT 'Win payout percentage',
    `min_trade_amount` DECIMAL(10, 2) NOT NULL DEFAULT 1.00,
    `max_trade_amount` DECIMAL(10, 2) NOT NULL DEFAULT 10000.00,
    `min_expiry_seconds` INT NOT NULL DEFAULT 60 COMMENT 'Minimum 1 minute',
    `max_expiry_seconds` INT NOT NULL DEFAULT 3600 COMMENT 'Maximum 1 hour',
    `binance_stream` VARCHAR(100) NULL COMMENT 'btcusdt@kline_1s',
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_symbol` (`symbol`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Wallet Balances Table (Multi-Wallet System)
-- ============================================================
CREATE TABLE `wallet_balances` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `uuid` CHAR(36) NOT NULL UNIQUE COMMENT 'UUID for external references',
    `user_id` BIGINT UNSIGNED NOT NULL,
    `wallet_type` ENUM('demo', 'bonus', 'real') NOT NULL,
    `balance` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `locked_balance` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `bonus_balance` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000 COMMENT 'Separate bonus balance for bonus wallet',
    `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_user_wallet_type` (`user_id`, `wallet_type`),
    INDEX `idx_wallet_type` (`wallet_type`),
    INDEX `idx_is_active` (`is_active`),
    UNIQUE KEY `unique_user_wallet_type` (`user_id`, `wallet_type`, `currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Wallet Rules Table (Rule Engine Configuration)
-- ============================================================
CREATE TABLE `wallet_rules` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `wallet_type` ENUM('demo', 'bonus', 'real') NOT NULL,
    `rule_name` VARCHAR(100) NOT NULL,
    `rule_key` VARCHAR(100) NOT NULL COMMENT 'can_trade_crypto, can_trade_otc, can_withdraw, etc.',
    `rule_value` TEXT NOT NULL COMMENT 'JSON or boolean value',
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `priority` INT NOT NULL DEFAULT 0 COMMENT 'Higher priority rules override lower',
    `description` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_wallet_type` (`wallet_type`),
    INDEX `idx_rule_key` (`rule_key`),
    INDEX `idx_is_active` (`is_active`),
    UNIQUE KEY `unique_wallet_rule` (`wallet_type`, `rule_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Bonus Rules Table (Bonus System Configuration)
-- ============================================================
CREATE TABLE `bonus_rules` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `bonus_percentage` DECIMAL(5, 2) NOT NULL DEFAULT 100.00 COMMENT 'Bonus % on deposit (100 = 100%)',
    `turnover_multiplier` DECIMAL(5, 2) NOT NULL DEFAULT 30.00 COMMENT 'Required turnover multiplier',
    `min_deposit_amount` DECIMAL(10, 2) NOT NULL DEFAULT 10.00,
    `max_bonus_amount` DECIMAL(10, 2) NULL COMMENT 'Maximum bonus amount, NULL = unlimited',
    `min_trade_count` INT NOT NULL DEFAULT 50 COMMENT 'Minimum number of trades required',
    `min_trade_amount` DECIMAL(10, 2) NOT NULL DEFAULT 1.00 COMMENT 'Minimum amount per trade',
    `min_active_days` INT NOT NULL DEFAULT 0 COMMENT 'Minimum days of activity',
    `expiry_days` INT NULL COMMENT 'Bonus expiry in days, NULL = no expiry',
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `priority` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Risk Settings Table (Global Risk Configuration)
-- ============================================================
CREATE TABLE `risk_settings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT NOT NULL COMMENT 'JSON value',
    `description` TEXT NULL,
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_setting_key` (`setting_key`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- OTC Assets Table (OTC Trading Pairs Configuration)
-- ============================================================
CREATE TABLE `otc_assets` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `symbol` VARCHAR(20) NOT NULL COMMENT 'Trading pair symbol (e.g., EURUSD_OTC)',
    `base_currency` VARCHAR(10) NOT NULL COMMENT 'Base currency (e.g., EUR)',
    `quote_currency` VARCHAR(10) NOT NULL COMMENT 'Quote currency (e.g., USD)',
    `display_name` VARCHAR(50) NOT NULL COMMENT 'Display name (e.g., EUR/USD (OTC))',
    `current_price` DECIMAL(20, 8) NOT NULL DEFAULT '0.00000000' COMMENT 'Current market price',
    `base_price` DECIMAL(20, 8) NOT NULL COMMENT 'Starting reference price',
    `min_price` DECIMAL(20, 8) NOT NULL COMMENT 'Minimum allowed price',
    `max_price` DECIMAL(20, 8) NOT NULL COMMENT 'Maximum allowed price',
    `precision` INT NOT NULL DEFAULT '5' COMMENT 'Decimal places for price display',
    `payout_percentage` DECIMAL(5, 2) NOT NULL DEFAULT '85.00' COMMENT 'Payout percentage for winning trades',
    `is_active` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'Asset is active',
    `is_tradeable` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'Asset is tradeable',
    `market_type` ENUM('forex', 'crypto') NOT NULL DEFAULT 'forex' COMMENT 'Market type',
    `tick_interval_seconds` INT NOT NULL DEFAULT '1' COMMENT 'Price update interval in seconds',
    `trading_hours` JSON DEFAULT NULL COMMENT 'Optional trading hours configuration',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `otc_assets_symbol_unique` (`symbol`),
    KEY `otc_assets_is_active_is_tradeable_index` (`is_active`, `is_tradeable`),
    KEY `otc_assets_market_type_index` (`market_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OTC trading pairs configuration';

-- ============================================================
-- OTC Market Prices Table (Real-time Price Ticks)
-- ============================================================
CREATE TABLE `otc_market_prices` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `otc_asset_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to otc_assets',
    `price` DECIMAL(20, 8) NOT NULL COMMENT 'Current price',
    `bid` DECIMAL(20, 8) DEFAULT NULL COMMENT 'Bid price',
    `ask` DECIMAL(20, 8) DEFAULT NULL COMMENT 'Ask price',
    `change_24h` DECIMAL(10, 4) NOT NULL DEFAULT '0.0000' COMMENT '24h price change percentage',
    `high_24h` DECIMAL(20, 8) DEFAULT NULL COMMENT '24h high price',
    `low_24h` DECIMAL(20, 8) DEFAULT NULL COMMENT '24h low price',
    `volume_24h` DECIMAL(20, 8) NOT NULL DEFAULT '0.00000000' COMMENT 'Synthetic 24h volume',
    `price_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Price timestamp',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `otc_market_prices_otc_asset_id_foreign` (`otc_asset_id`),
    KEY `otc_market_prices_otc_asset_id_price_time_index` (`otc_asset_id`, `price_time`),
    KEY `otc_market_prices_price_time_index` (`price_time`),
    CONSTRAINT `otc_market_prices_otc_asset_id_foreign` FOREIGN KEY (`otc_asset_id`) REFERENCES `otc_assets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Real-time price ticks';

-- ============================================================
-- OTC Candles Table (OHLCV Candle Data)
-- ============================================================
CREATE TABLE `otc_candles` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `otc_asset_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to otc_assets',
    `timeframe` ENUM('1m', '5m', '15m', '30m', '1h', '4h', '1d') NOT NULL DEFAULT '1m' COMMENT 'Candle timeframe',
    `open` DECIMAL(20, 8) NOT NULL COMMENT 'Opening price',
    `high` DECIMAL(20, 8) NOT NULL COMMENT 'Highest price',
    `low` DECIMAL(20, 8) NOT NULL COMMENT 'Lowest price',
    `close` DECIMAL(20, 8) NOT NULL COMMENT 'Closing price',
    `volume` DECIMAL(20, 8) NOT NULL DEFAULT '0.00000000' COMMENT 'Synthetic volume',
    `tick_count` INT NOT NULL DEFAULT '0' COMMENT 'Number of ticks in this candle',
    `candle_time` TIMESTAMP NOT NULL COMMENT 'Candle start time',
    `close_time` TIMESTAMP NULL DEFAULT NULL COMMENT 'Candle end time',
    `is_closed` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Candle is closed',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `otc_candles_otc_asset_id_timeframe_candle_time_unique` (`otc_asset_id`, `timeframe`, `candle_time`),
    KEY `otc_candles_otc_asset_id_foreign` (`otc_asset_id`),
    KEY `otc_candles_otc_asset_id_timeframe_candle_time_index` (`otc_asset_id`, `timeframe`, `candle_time`),
    KEY `otc_candles_candle_time_is_closed_index` (`candle_time`, `is_closed`),
    CONSTRAINT `otc_candles_otc_asset_id_foreign` FOREIGN KEY (`otc_asset_id`) REFERENCES `otc_assets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OHLCV candle data for multiple timeframes';

-- ============================================================
-- OTC Trends Table (Active Market Trends)
-- ============================================================
CREATE TABLE `otc_trends` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `otc_asset_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to otc_assets',
    `direction` ENUM('bullish', 'bearish', 'sideways') NOT NULL DEFAULT 'sideways' COMMENT 'Trend direction',
    `strength` INT NOT NULL DEFAULT '50' COMMENT 'Trend strength (0-100)',
    `duration_seconds` INT NOT NULL DEFAULT '0' COMMENT 'Current trend duration in seconds',
    `target_duration_seconds` INT NOT NULL DEFAULT '300' COMMENT 'Target trend duration (default 5 minutes)',
    `start_price` DECIMAL(20, 8) NOT NULL COMMENT 'Price when trend started',
    `target_price` DECIMAL(20, 8) DEFAULT NULL COMMENT 'Target price for this trend',
    `current_price` DECIMAL(20, 8) NOT NULL COMMENT 'Current price in trend',
    `started_at` TIMESTAMP NOT NULL COMMENT 'Trend start time',
    `expected_end_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Expected trend end time',
    `is_active` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'Trend is currently active',
    `metadata` JSON DEFAULT NULL COMMENT 'Additional trend metadata',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `otc_trends_otc_asset_id_foreign` (`otc_asset_id`),
    KEY `otc_trends_otc_asset_id_is_active_index` (`otc_asset_id`, `is_active`),
    KEY `otc_trends_started_at_is_active_index` (`started_at`, `is_active`),
    CONSTRAINT `otc_trends_otc_asset_id_foreign` FOREIGN KEY (`otc_asset_id`) REFERENCES `otc_assets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Market trend tracking';

-- ============================================================
-- OTC Market State Table (Volatility & Admin Controls)
-- ============================================================
CREATE TABLE `otc_market_state` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `otc_asset_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to otc_assets',
    `volatility_level` ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium' COMMENT 'Current volatility level',
    `volatility_percentage` DECIMAL(5, 2) NOT NULL DEFAULT '0.50' COMMENT 'Volatility percentage (0.20-2.50%)',
    `market_phase` ENUM('accumulation', 'markup', 'distribution', 'markdown') NOT NULL DEFAULT 'accumulation' COMMENT 'Current market phase',
    `phase_progress` INT NOT NULL DEFAULT '0' COMMENT 'Phase progress percentage (0-100)',
    `allow_spikes` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'Allow sudden price spikes',
    `allow_reversals` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'Allow trend reversals',
    `spike_probability` INT NOT NULL DEFAULT '5' COMMENT 'Spike probability percentage (0-100)',
    `reversal_probability` INT NOT NULL DEFAULT '10' COMMENT 'Reversal probability percentage (0-100)',
    `support_level` DECIMAL(20, 8) DEFAULT NULL COMMENT 'Current support level',
    `resistance_level` DECIMAL(20, 8) DEFAULT NULL COMMENT 'Current resistance level',
    `is_paused` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Market generation is paused',
    `admin_mode` ENUM('auto', 'force_bullish', 'force_bearish', 'force_sideways') NOT NULL DEFAULT 'auto' COMMENT 'Admin control mode',
    `state_metadata` JSON DEFAULT NULL COMMENT 'Additional state information',
    `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Last state update time',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `otc_market_state_otc_asset_id_unique` (`otc_asset_id`),
    KEY `otc_market_state_is_paused_admin_mode_index` (`is_paused`, `admin_mode`),
    CONSTRAINT `otc_market_state_otc_asset_id_foreign` FOREIGN KEY (`otc_asset_id`) REFERENCES `otc_assets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Market state and volatility configuration';

-- ============================================================
-- Transactions Table (All Financial Operations)
-- ============================================================
CREATE TABLE `transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `uuid` CHAR(36) NOT NULL UNIQUE,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `wallet_id` BIGINT UNSIGNED NOT NULL,
    `type` ENUM('deposit', 'withdrawal', 'trade_open', 'trade_win', 'trade_loss', 'refund', 'bonus', 'commission') NOT NULL,
    `amount` DECIMAL(20, 8) NOT NULL,
    `balance_before` DECIMAL(20, 8) NOT NULL,
    `balance_after` DECIMAL(20, 8) NOT NULL,
    `status` ENUM('pending', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    `reference_id` BIGINT UNSIGNED NULL COMMENT 'Related trade_id, deposit_id, etc.',
    `reference_type` VARCHAR(50) NULL COMMENT 'Trade, Deposit, Withdrawal',
    `description` TEXT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_wallet_id` (`wallet_id`),
    INDEX `idx_type` (`type`),
    INDEX `idx_status` (`status`),
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_reference` (`reference_type`, `reference_id`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Deposits Table
-- ============================================================
CREATE TABLE `deposits` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `uuid` CHAR(36) NOT NULL UNIQUE,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `wallet_id` BIGINT UNSIGNED NOT NULL,
    `amount` DECIMAL(20, 8) NOT NULL,
    `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
    `payment_method` VARCHAR(50) NOT NULL COMMENT 'card, crypto, bank_transfer, etc.',
    `payment_gateway` VARCHAR(50) NULL COMMENT 'stripe, paypal, coinbase, etc.',
    `transaction_id` VARCHAR(255) NULL COMMENT 'External payment gateway transaction ID',
    `status` ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    `metadata` JSON NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_wallet_id` (`wallet_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Withdrawals Table
-- ============================================================
CREATE TABLE `withdrawals` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `uuid` CHAR(36) NOT NULL UNIQUE,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `wallet_id` BIGINT UNSIGNED NOT NULL,
    `amount` DECIMAL(20, 8) NOT NULL,
    `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
    `withdrawal_method` VARCHAR(50) NOT NULL COMMENT 'card, crypto, bank_transfer, etc.',
    `withdrawal_address` TEXT NULL COMMENT 'Bank account, crypto address, etc.',
    `status` ENUM('pending', 'processing', 'approved', 'completed', 'rejected', 'cancelled') NOT NULL DEFAULT 'pending',
    `admin_note` TEXT NULL,
    `processed_by` BIGINT UNSIGNED NULL COMMENT 'Admin user ID who processed',
    `processed_at` TIMESTAMP NULL DEFAULT NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`processed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_wallet_id` (`wallet_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Trades Table (Binary Options Trades)
-- ============================================================
CREATE TABLE `trades` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `uuid` CHAR(36) NOT NULL UNIQUE,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `wallet_id` BIGINT UNSIGNED NOT NULL,
    `asset_id` BIGINT UNSIGNED NOT NULL,
    `direction` ENUM('up', 'down') NOT NULL COMMENT 'Trade direction',
    `amount` DECIMAL(20, 8) NOT NULL COMMENT 'Trade investment amount',
    `entry_price` DECIMAL(20, 8) NOT NULL COMMENT 'Price when trade opened',
    `exit_price` DECIMAL(20, 8) NULL COMMENT 'Price when trade closed',
    `payout_percentage` DECIMAL(5, 2) NOT NULL COMMENT 'Payout % at trade time',
    `potential_profit` DECIMAL(20, 8) NOT NULL COMMENT 'Calculated potential profit',
    `actual_profit` DECIMAL(20, 8) NULL COMMENT 'Actual profit/loss',
    `status` ENUM('pending', 'active', 'won', 'lost', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending',
    `expiry_seconds` INT NOT NULL COMMENT 'Trade duration in seconds',
    `opened_at` TIMESTAMP NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `closed_at` TIMESTAMP NULL DEFAULT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_wallet_id` (`wallet_id`),
    INDEX `idx_asset_id` (`asset_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_expires_at` (`expires_at`),
    INDEX `idx_opened_at` (`opened_at`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Market Prices Table (Real-time Price Tracking)
-- ============================================================
CREATE TABLE `market_prices` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `asset_id` BIGINT UNSIGNED NOT NULL,
    `symbol` VARCHAR(20) NOT NULL,
    `price` DECIMAL(20, 8) NOT NULL,
    `timestamp` BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
    INDEX `idx_asset_id` (`asset_id`),
    INDEX `idx_symbol` (`symbol`),
    INDEX `idx_timestamp` (`timestamp`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Candle Data Table (OHLCV Data for Charts)
-- ============================================================
CREATE TABLE `candle_data` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `asset_id` BIGINT UNSIGNED NOT NULL,
    `symbol` VARCHAR(20) NOT NULL,
    `interval` VARCHAR(10) NOT NULL COMMENT '1s, 1m, 5m, 15m, 1h, etc.',
    `open_time` BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
    `close_time` BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
    `open_price` DECIMAL(20, 8) NOT NULL,
    `high_price` DECIMAL(20, 8) NOT NULL,
    `low_price` DECIMAL(20, 8) NOT NULL,
    `close_price` DECIMAL(20, 8) NOT NULL,
    `volume` DECIMAL(20, 8) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
    INDEX `idx_asset_id` (`asset_id`),
    INDEX `idx_symbol` (`symbol`),
    INDEX `idx_interval` (`interval`),
    INDEX `idx_open_time` (`open_time`),
    UNIQUE KEY `unique_candle` (`asset_id`, `interval`, `open_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Admin Logs Table (Audit Trail)
-- ============================================================
CREATE TABLE `admin_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `admin_id` BIGINT UNSIGNED NOT NULL,
    `action` VARCHAR(100) NOT NULL COMMENT 'user_suspended, withdrawal_approved, etc.',
    `target_type` VARCHAR(50) NULL COMMENT 'User, Trade, Withdrawal, etc.',
    `target_id` BIGINT UNSIGNED NULL,
    `description` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_admin_id` (`admin_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_target` (`target_type`, `target_id`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Bonus Conversions Table (Track Bonus Progress)
-- ============================================================
CREATE TABLE `bonus_conversions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `wallet_balance_id` BIGINT UNSIGNED NOT NULL,
    `bonus_rule_id` BIGINT UNSIGNED NOT NULL,
    `initial_deposit_amount` DECIMAL(20, 8) NOT NULL,
    `bonus_amount` DECIMAL(20, 8) NOT NULL,
    `required_turnover` DECIMAL(20, 8) NOT NULL COMMENT 'Total turnover required',
    `current_turnover` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `trade_count` INT NOT NULL DEFAULT 0,
    `active_days` INT NOT NULL DEFAULT 0,
    `status` ENUM('active', 'completed', 'expired', 'cancelled') NOT NULL DEFAULT 'active',
    `converted_amount` DECIMAL(20, 8) NULL COMMENT 'Amount converted to real balance',
    `started_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`wallet_balance_id`) REFERENCES `wallet_balances`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`bonus_rule_id`) REFERENCES `bonus_rules`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_expires_at` (`expires_at`),
    INDEX `idx_status_expires` (`status`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Bonus Transactions Table (Track Bonus Activity)
-- ============================================================
CREATE TABLE `bonus_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `bonus_conversion_id` BIGINT UNSIGNED NOT NULL,
    `trade_id` BIGINT UNSIGNED NULL,
    `transaction_type` ENUM('deposit', 'trade', 'conversion', 'reset', 'expiry') NOT NULL,
    `amount` DECIMAL(20, 8) NOT NULL,
    `turnover_contribution` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `description` TEXT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`bonus_conversion_id`) REFERENCES `bonus_conversions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE SET NULL,
    INDEX `idx_bonus_conversion_id` (`bonus_conversion_id`),
    INDEX `idx_transaction_type` (`transaction_type`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Payout Rules Table (Dynamic Payout Configuration)
-- ============================================================
CREATE TABLE `payout_rules` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `wallet_type` ENUM('demo', 'bonus', 'real') NOT NULL,
    `market_type` ENUM('crypto', 'otc') NOT NULL,
    `asset_id` BIGINT UNSIGNED NULL COMMENT 'NULL = applies to all assets',
    `base_payout_percentage` DECIMAL(5, 2) NOT NULL DEFAULT 85.00,
    `min_payout_percentage` DECIMAL(5, 2) NOT NULL DEFAULT 70.00,
    `max_payout_percentage` DECIMAL(5, 2) NOT NULL DEFAULT 95.00,
    `volatility_adjustment` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Adjust payout based on volatility',
    `exposure_adjustment` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Adjust payout based on exposure',
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `priority` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
    INDEX `idx_wallet_type` (`wallet_type`),
    INDEX `idx_market_type` (`market_type`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Trade Exposure Table (Risk Management)
-- ============================================================
CREATE TABLE `trade_exposure` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `asset_id` BIGINT UNSIGNED NOT NULL,
    `market_type` ENUM('crypto', 'otc') NOT NULL,
    `wallet_type` ENUM('demo', 'bonus', 'real') NOT NULL,
    `direction` ENUM('up', 'down') NOT NULL,
    `total_amount` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `trade_count` INT NOT NULL DEFAULT 0,
    `potential_payout` DECIMAL(20, 8) NOT NULL DEFAULT 0.00000000,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
    INDEX `idx_asset_market` (`asset_id`, `market_type`),
    INDEX `idx_wallet_type` (`wallet_type`),
    INDEX `idx_timestamp` (`timestamp`),
    UNIQUE KEY `unique_exposure` (`asset_id`, `market_type`, `wallet_type`, `direction`, `timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Schema Versions Table
-- ============================================================
CREATE TABLE `schema_versions` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `component` VARCHAR(50) NOT NULL,
    `version` VARCHAR(20) NOT NULL,
    `applied_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Additional Performance Indexes
-- ============================================================
CREATE INDEX idx_otc_prices_asset_time ON otc_market_prices(otc_asset_id, price_time DESC);
CREATE INDEX idx_otc_candles_asset_timeframe ON otc_candles(otc_asset_id, timeframe, candle_time DESC);
CREATE INDEX idx_otc_trends_active ON otc_trends(otc_asset_id, is_active, started_at DESC);

-- ============================================================
-- Views
-- ============================================================

CREATE OR REPLACE VIEW v_otc_latest_prices AS
SELECT
    a.id AS asset_id,
    a.symbol,
    a.display_name,
    a.market_type,
    p.price,
    p.bid,
    p.ask,
    p.change_24h,
    p.high_24h,
    p.low_24h,
    p.volume_24h,
    p.price_time
FROM otc_assets a
LEFT JOIN otc_market_prices p ON a.id = p.otc_asset_id
WHERE p.id = (
    SELECT id FROM otc_market_prices
    WHERE otc_asset_id = a.id
    ORDER BY price_time DESC
    LIMIT 1
)
AND a.is_active = 1
AND a.is_tradeable = 1;

CREATE OR REPLACE VIEW v_otc_active_trends AS
SELECT
    a.id AS asset_id,
    a.symbol,
    a.display_name,
    t.direction,
    t.strength,
    t.duration_seconds,
    t.target_duration_seconds,
    ROUND((t.duration_seconds / t.target_duration_seconds) * 100, 2) AS progress_percentage,
    t.start_price,
    t.current_price,
    t.started_at
FROM otc_assets a
INNER JOIN otc_trends t ON a.id = t.otc_asset_id
WHERE t.is_active = 1
AND a.is_active = 1;

CREATE OR REPLACE VIEW v_otc_market_summary AS
SELECT
    a.id AS asset_id,
    a.symbol,
    a.display_name,
    a.current_price,
    s.volatility_level,
    s.market_phase,
    s.is_paused,
    s.admin_mode,
    t.direction AS trend_direction,
    t.strength AS trend_strength
FROM otc_assets a
LEFT JOIN otc_market_state s ON a.id = s.otc_asset_id
LEFT JOIN otc_trends t ON a.id = t.otc_asset_id AND t.is_active = 1
WHERE a.is_active = 1;

-- ============================================================
-- Stored Procedures
-- ============================================================

DELIMITER //

CREATE PROCEDURE sp_get_otc_candles(
    IN p_asset_id BIGINT,
    IN p_timeframe VARCHAR(10),
    IN p_limit INT
)
BEGIN
    SELECT
        UNIX_TIMESTAMP(candle_time) AS time,
        open,
        high,
        low,
        close,
        volume
    FROM otc_candles
    WHERE otc_asset_id = p_asset_id
    AND timeframe = p_timeframe
    AND is_closed = 1
    ORDER BY candle_time DESC
    LIMIT p_limit;
END //

CREATE PROCEDURE sp_get_otc_price_history(
    IN p_asset_id BIGINT,
    IN p_limit INT
)
BEGIN
    SELECT
        price,
        UNIX_TIMESTAMP(price_time) AS timestamp
    FROM otc_market_prices
    WHERE otc_asset_id = p_asset_id
    ORDER BY price_time DESC
    LIMIT p_limit;
END //

CREATE PROCEDURE sp_archive_old_otc_prices()
BEGIN
    DECLARE rows_deleted INT;
    DELETE FROM otc_market_prices
    WHERE price_time < DATE_SUB(NOW(), INTERVAL 7 DAY);
    SET rows_deleted = ROW_COUNT();
    SELECT CONCAT('Archived ', rows_deleted, ' old price records') AS result;
END //

DELIMITER ;

-- ============================================================
-- Triggers
-- ============================================================

DELIMITER //

CREATE TRIGGER trg_update_asset_price
AFTER INSERT ON otc_market_prices
FOR EACH ROW
BEGIN
    UPDATE otc_assets
    SET current_price = NEW.price,
        updated_at = NOW()
    WHERE id = NEW.otc_asset_id;
END //

DELIMITER ;

-- ============================================================
-- Seed: Default Admin User
-- Password: admin123 (bcrypt hashed)
-- ============================================================
INSERT INTO `users` (`uuid`, `name`, `email`, `email_verified_at`, `password`, `role`, `status`) VALUES
(UUID(), 'Admin User', 'admin@optiqo.com', NOW(), '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5lE7zJQfQXwHC', 'admin', 'active');

-- ============================================================
-- Seed: Market Assets (20 Crypto Trading Pairs)
-- ============================================================
INSERT INTO `assets` (`symbol`, `name`, `base_currency`, `quote_currency`, `is_active`, `payout_percentage`, `min_trade_amount`, `max_trade_amount`, `min_expiry_seconds`, `max_expiry_seconds`, `binance_stream`, `sort_order`) VALUES
('BTCUSDT', 'Bitcoin', 'BTC', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'btcusdt@kline_1s', 1),
('ETHUSDT', 'Ethereum', 'ETH', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'ethusdt@kline_1s', 2),
('BNBUSDT', 'Binance Coin', 'BNB', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'bnbusdt@kline_1s', 3),
('SOLUSDT', 'Solana', 'SOL', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'solusdt@kline_1s', 4),
('XRPUSDT', 'Ripple', 'XRP', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'xrpusdt@kline_1s', 5),
('DOGEUSDT', 'Dogecoin', 'DOGE', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'dogeusdt@kline_1s', 6),
('ADAUSDT', 'Cardano', 'ADA', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'adausdt@kline_1s', 7),
('AVAXUSDT', 'Avalanche', 'AVAX', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'avaxusdt@kline_1s', 8),
('LINKUSDT', 'Chainlink', 'LINK', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'linkusdt@kline_1s', 9),
('LTCUSDT', 'Litecoin', 'LTC', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'ltcusdt@kline_1s', 10),
('TRXUSDT', 'Tron', 'TRX', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'trxusdt@kline_1s', 11),
('DOTUSDT', 'Polkadot', 'DOT', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'dotusdt@kline_1s', 12),
('BCHUSDT', 'Bitcoin Cash', 'BCH', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'bchusdt@kline_1s', 13),
('MATICUSDT', 'Polygon', 'MATIC', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'maticusdt@kline_1s', 14),
('UNIUSDT', 'Uniswap', 'UNI', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'uniusdt@kline_1s', 15),
('ETCUSDT', 'Ethereum Classic', 'ETC', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'etcusdt@kline_1s', 16),
('FILUSDT', 'Filecoin', 'FIL', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'filusdt@kline_1s', 17),
('APTUSDT', 'Aptos', 'APT', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'aptusdt@kline_1s', 18),
('ATOMUSDT', 'Cosmos', 'ATOM', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'atomusdt@kline_1s', 19),
('NEARUSDT', 'NEAR Protocol', 'NEAR', 'USDT', 1, 85.00, 1.00, 10000.00, 60, 3600, 'nearusdt@kline_1s', 20);

-- ============================================================
-- Seed: OTC Assets (8 Forex + 2 Crypto Pairs)
-- ============================================================
INSERT INTO `otc_assets` (`symbol`, `base_currency`, `quote_currency`, `display_name`, `current_price`, `base_price`, `min_price`, `max_price`, `precision`, `payout_percentage`, `is_active`, `is_tradeable`, `market_type`, `tick_interval_seconds`, `created_at`, `updated_at`) VALUES
('EURUSD_OTC', 'EUR', 'USD', 'EUR/USD (OTC)', 1.08500000, 1.08500000, 1.00000000, 1.20000000, 5, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('GBPUSD_OTC', 'GBP', 'USD', 'GBP/USD (OTC)', 1.26500000, 1.26500000, 1.15000000, 1.40000000, 5, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('USDJPY_OTC', 'USD', 'JPY', 'USD/JPY (OTC)', 149.50000000, 149.50000000, 130.00000000, 160.00000000, 3, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('AUDUSD_OTC', 'AUD', 'USD', 'AUD/USD (OTC)', 0.65500000, 0.65500000, 0.60000000, 0.75000000, 5, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('USDCAD_OTC', 'USD', 'CAD', 'USD/CAD (OTC)', 1.36500000, 1.36500000, 1.25000000, 1.45000000, 5, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('EURJPY_OTC', 'EUR', 'JPY', 'EUR/JPY (OTC)', 162.25000000, 162.25000000, 145.00000000, 180.00000000, 3, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('GBPJPY_OTC', 'GBP', 'JPY', 'GBP/JPY (OTC)', 189.15000000, 189.15000000, 170.00000000, 210.00000000, 3, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('EURGBP_OTC', 'EUR', 'GBP', 'EUR/GBP (OTC)', 0.85750000, 0.85750000, 0.80000000, 0.92000000, 5, 85.00, 1, 1, 'forex', 1, NOW(), NOW()),
('BTCUSD_OTC', 'BTC', 'USD', 'BTC/USD (OTC)', 43500.00000000, 43500.00000000, 20000.00000000, 70000.00000000, 2, 82.00, 1, 1, 'crypto', 1, NOW(), NOW()),
('ETHUSD_OTC', 'ETH', 'USD', 'ETH/USD (OTC)', 2280.00000000, 2280.00000000, 1000.00000000, 5000.00000000, 2, 82.00, 1, 1, 'crypto', 1, NOW(), NOW());

-- ============================================================
-- Seed: Wallet Rules
-- ============================================================
INSERT INTO `wallet_rules` (`wallet_type`, `rule_name`, `rule_key`, `rule_value`, `is_active`, `priority`, `description`) VALUES
('demo', 'Can Trade Crypto', 'can_trade_crypto', 'true', 1, 100, 'Demo users can trade real crypto markets'),
('demo', 'Can Trade OTC', 'can_trade_otc', 'true', 1, 100, 'Demo users can trade OTC markets'),
('demo', 'Can Withdraw', 'can_withdraw', 'false', 1, 100, 'Demo users cannot withdraw funds'),
('demo', 'Fair Trading', 'fair_trading', 'true', 1, 100, 'Demo account uses fair market behavior'),
('demo', 'Max Trade Amount', 'max_trade_amount', '10000', 1, 100, 'Maximum trade amount for demo'),
('demo', 'Initial Balance', 'initial_balance', '10000', 1, 100, 'Initial demo balance'),
('bonus', 'Can Trade Crypto', 'can_trade_crypto', 'false', 1, 100, 'Bonus users cannot trade real crypto markets'),
('bonus', 'Can Trade OTC', 'can_trade_otc', 'true', 1, 100, 'Bonus users can only trade OTC markets'),
('bonus', 'Can Withdraw', 'can_withdraw', 'false', 1, 100, 'Bonus balance cannot be withdrawn directly'),
('bonus', 'Beginner Friendly', 'beginner_friendly', 'true', 1, 100, 'OTC behavior is beginner friendly'),
('bonus', 'Win Probability Boost', 'win_probability_boost', '0.15', 1, 100, '15% boost to favorable outcomes'),
('bonus', 'Max Trade Amount', 'max_trade_amount', '1000', 1, 100, 'Maximum trade amount for bonus'),
('real', 'Can Trade Crypto', 'can_trade_crypto', 'true', 1, 100, 'Real users can trade real crypto markets'),
('real', 'Can Trade OTC', 'can_trade_otc', 'true', 1, 100, 'Real users can trade OTC markets'),
('real', 'Can Withdraw', 'can_withdraw', 'true', 1, 100, 'Real users can withdraw funds'),
('real', 'Dynamic Payout', 'dynamic_payout', 'true', 1, 100, 'Use dynamic payout calculation'),
('real', 'Risk Management', 'risk_management', 'true', 1, 100, 'Apply risk management rules'),
('real', 'Max Trade Amount', 'max_trade_amount', '50000', 1, 100, 'Maximum trade amount for real');

-- ============================================================
-- Seed: Bonus Rules
-- ============================================================
INSERT INTO `bonus_rules` (`name`, `bonus_percentage`, `turnover_multiplier`, `min_deposit_amount`, `max_bonus_amount`, `min_trade_count`, `min_trade_amount`, `min_active_days`, `expiry_days`, `is_active`, `priority`) VALUES
('Standard Bonus', 100.00, 30.00, 10.00, 1000.00, 50, 1.00, 0, 30, 1, 100),
('VIP Bonus', 150.00, 25.00, 100.00, 5000.00, 40, 5.00, 0, 60, 1, 200),
('Welcome Bonus', 200.00, 35.00, 20.00, 500.00, 60, 1.00, 0, 15, 1, 50);

-- ============================================================
-- Seed: Payout Rules
-- ============================================================
INSERT INTO `payout_rules` (`wallet_type`, `market_type`, `asset_id`, `base_payout_percentage`, `min_payout_percentage`, `max_payout_percentage`, `volatility_adjustment`, `exposure_adjustment`, `is_active`, `priority`) VALUES
('demo', 'crypto', NULL, 85.00, 85.00, 85.00, 0, 0, 1, 100),
('demo', 'otc', NULL, 85.00, 85.00, 85.00, 0, 0, 1, 100),
('bonus', 'otc', NULL, 88.00, 85.00, 92.00, 0, 0, 1, 100),
('real', 'crypto', NULL, 85.00, 75.00, 90.00, 1, 1, 1, 100),
('real', 'otc', NULL, 85.00, 75.00, 90.00, 1, 1, 1, 100);

-- ============================================================
-- Seed: Risk Settings
-- ============================================================
INSERT INTO `risk_settings` (`setting_key`, `setting_value`, `description`, `is_active`) VALUES
('max_exposure_per_asset', '{"amount": 100000, "percentage": 20}', 'Maximum exposure per asset (amount or % of total)', 1),
('max_exposure_per_direction', '{"percentage": 60}', 'Maximum exposure in one direction', 1),
('exposure_rebalancing', '{"enabled": true, "threshold": 70}', 'Auto-rebalance when exposure exceeds threshold', 1),
('payout_adjustment_range', '{"min": -5, "max": 5}', 'Payout adjustment range based on exposure', 1),
('volatility_thresholds', '{"low": 0.5, "medium": 1.5, "high": 3.0}', 'Volatility classification thresholds', 1),
('beginner_friendly_settings', '{"win_boost": 0.15, "volatility_reduction": 0.3}', 'Beginner friendly OTC settings', 1);

-- ============================================================
-- Seed: Schema Version
-- ============================================================
INSERT INTO `schema_versions` (`component`, `version`) VALUES ('OPTIQO_FULL', '1.0.0');
