-- ============================================================================
-- MEV Data Pipeline - Database Schema Installation Script
-- Database: poipoeco_mevdata
-- Purpose: Complete schema for Ethereum MEV opportunity detection system
-- Author: MEV Pipeline System
-- Last Modified: 2025-11-15
-- ============================================================================

-- Use the database
USE poipoeco_mevdata;

-- ============================================================================
-- TABLE 1: gas_tracker
-- Purpose: Store real-time gas price data from Etherscan
-- Updated: Every minute via cron
-- ============================================================================
CREATE TABLE IF NOT EXISTS gas_tracker (
    id INT PRIMARY KEY AUTO_INCREMENT,
    timestamp TIMESTAMP UNIQUE DEFAULT CURRENT_TIMESTAMP,
    safe_gas_price INT COMMENT 'Safe gas price in Gwei',
    propose_gas_price INT COMMENT 'Proposed gas price in Gwei',
    fast_gas_price INT COMMENT 'Fast gas price in Gwei',
    base_fee VARCHAR(50) COMMENT 'Base fee from EIP-1559',
    priority_fee VARCHAR(50) COMMENT 'Priority fee (tip)',
    network_utilization DECIMAL(5,2) COMMENT 'Network utilization percentage',
    suggested_base_fee VARCHAR(50) COMMENT 'Suggested base fee',
    gas_used_ratio VARCHAR(50) COMMENT 'Gas used ratio from recent blocks',
    INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Real-time gas price tracking';

-- ============================================================================
-- TABLE 2: token_transfers
-- Purpose: Track token transfers, especially whale movements
-- ============================================================================
CREATE TABLE IF NOT EXISTS token_transfers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tx_hash VARCHAR(66) NOT NULL COMMENT 'Transaction hash',
    block_number INT COMMENT 'Block number',
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    from_address VARCHAR(42) NOT NULL COMMENT 'Sender address',
    to_address VARCHAR(42) NOT NULL COMMENT 'Recipient address',
    token_address VARCHAR(42) NOT NULL COMMENT 'Token contract address',
    token_name VARCHAR(100) COMMENT 'Token name',
    token_symbol VARCHAR(20) COMMENT 'Token symbol',
    value VARCHAR(50) COMMENT 'Transfer amount (raw)',
    value_usd DECIMAL(18,2) COMMENT 'USD value at time of transfer',
    gas_used INT COMMENT 'Gas used for transaction',
    gas_price VARCHAR(50) COMMENT 'Gas price in wei',
    is_whale_movement TINYINT DEFAULT 0 COMMENT '1 if > whale threshold',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tx_hash (tx_hash),
    INDEX idx_block_number (block_number),
    INDEX idx_timestamp (timestamp),
    INDEX idx_from_address (from_address),
    INDEX idx_to_address (to_address),
    INDEX idx_token_address (token_address),
    INDEX idx_is_whale_movement (is_whale_movement)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Token transfer records';

-- ============================================================================
-- TABLE 3: dex_swaps
-- Purpose: Record DEX swap events for arbitrage detection
-- ============================================================================
CREATE TABLE IF NOT EXISTS dex_swaps (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tx_hash VARCHAR(66) UNIQUE NOT NULL COMMENT 'Transaction hash',
    block_number INT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dex_name VARCHAR(50) NOT NULL COMMENT 'DEX name (Uniswap, Sushiswap, etc)',
    dex_address VARCHAR(42) NOT NULL COMMENT 'DEX contract address',
    token_in VARCHAR(42) NOT NULL COMMENT 'Input token address',
    token_out VARCHAR(42) NOT NULL COMMENT 'Output token address',
    amount_in VARCHAR(50) COMMENT 'Input amount (raw)',
    amount_out VARCHAR(50) COMMENT 'Output amount (raw)',
    amount_in_usd DECIMAL(18,2) COMMENT 'Input amount in USD',
    amount_out_usd DECIMAL(18,2) COMMENT 'Output amount in USD',
    trader_address VARCHAR(42) COMMENT 'Trader wallet address',
    price_impact DECIMAL(5,2) COMMENT 'Price impact percentage',
    is_large_swap TINYINT DEFAULT 0 COMMENT '1 if > $50k',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tx_hash (tx_hash),
    INDEX idx_block_number (block_number),
    INDEX idx_timestamp (timestamp),
    INDEX idx_dex_name (dex_name),
    INDEX idx_token_in (token_in),
    INDEX idx_token_out (token_out),
    INDEX idx_is_large_swap (is_large_swap)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DEX swap events';

-- ============================================================================
-- TABLE 4: contract_events
-- Purpose: Store raw contract event logs
-- ============================================================================
CREATE TABLE IF NOT EXISTS contract_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tx_hash VARCHAR(66) COMMENT 'Transaction hash',
    block_number INT,
    log_index INT COMMENT 'Log index in block',
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    contract_address VARCHAR(42) NOT NULL COMMENT 'Contract that emitted event',
    event_name VARCHAR(100) NOT NULL COMMENT 'Event name',
    event_signature VARCHAR(66) COMMENT 'Event signature hash (topic0)',
    topics TEXT COMMENT 'Event topics (JSON array)',
    data TEXT COMMENT 'Event data (hex)',
    decoded_data TEXT COMMENT 'Decoded event data (JSON)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tx_hash (tx_hash),
    INDEX idx_block_number (block_number),
    INDEX idx_timestamp (timestamp),
    INDEX idx_contract_address (contract_address),
    INDEX idx_event_name (event_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Contract event logs';

-- ============================================================================
-- TABLE 5: mev_opportunities
-- Purpose: Detected MEV opportunities (arbitrage, sandwich, liquidation)
-- ============================================================================
CREATE TABLE IF NOT EXISTS mev_opportunities (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    opportunity_type VARCHAR(50) NOT NULL COMMENT 'ARBITRAGE, SANDWICH, LIQUIDATION',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, executed, expired',
    token_addresses TEXT COMMENT 'Involved token addresses (JSON)',
    dex_addresses TEXT COMMENT 'Involved DEX addresses (JSON)',
    expected_profit_usd DECIMAL(18,8) COMMENT 'Expected profit in USD',
    gas_cost_usd DECIMAL(18,8) COMMENT 'Estimated gas cost in USD',
    net_profit_usd DECIMAL(18,8) COMMENT 'Net profit (profit - gas)',
    profit_percentage DECIMAL(5,2) COMMENT 'Profit percentage',
    confidence_score DECIMAL(3,2) COMMENT 'Confidence 0.00-1.00',
    urgency_level VARCHAR(20) COMMENT 'HIGH, MEDIUM, LOW',
    related_tx_hashes TEXT COMMENT 'Related transaction hashes (JSON)',
    strategy_data TEXT COMMENT 'Strategy details (JSON)',
    execution_steps TEXT COMMENT 'Execution steps (JSON)',
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP COMMENT 'Opportunity expiration time',
    executed_at TIMESTAMP COMMENT 'Execution timestamp',
    INDEX idx_opportunity_type (opportunity_type),
    INDEX idx_status (status),
    INDEX idx_detected_at (detected_at),
    INDEX idx_expires_at (expires_at),
    INDEX idx_urgency_level (urgency_level),
    INDEX idx_net_profit (net_profit_usd)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MEV opportunities';

-- ============================================================================
-- TABLE 6: watched_addresses
-- Purpose: Addresses to monitor for whale activity
-- ============================================================================
CREATE TABLE IF NOT EXISTS watched_addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    address VARCHAR(42) UNIQUE NOT NULL COMMENT 'Ethereum address',
    address_type VARCHAR(50) COMMENT 'WHALE, EXCHANGE, PROTOCOL, etc',
    name_tag VARCHAR(100) COMMENT 'Friendly name',
    min_value_usd DECIMAL(18,2) DEFAULT 100000 COMMENT 'Minimum value to track',
    is_active TINYINT DEFAULT 1 COMMENT '1=active, 0=inactive',
    last_activity TIMESTAMP COMMENT 'Last transaction timestamp',
    total_volume_usd DECIMAL(18,2) DEFAULT 0 COMMENT 'Total tracked volume',
    transaction_count INT DEFAULT 0 COMMENT 'Number of tracked transactions',
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_address (address),
    INDEX idx_is_active (is_active),
    INDEX idx_address_type (address_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Watched addresses';

-- ============================================================================
-- TABLE 7: api_call_log
-- Purpose: Log all Etherscan API calls for rate limiting and monitoring
-- ============================================================================
CREATE TABLE IF NOT EXISTS api_call_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    endpoint VARCHAR(200) COMMENT 'API endpoint URL',
    module VARCHAR(50) COMMENT 'Etherscan module (account, gastracker, etc)',
    action VARCHAR(50) COMMENT 'API action',
    parameters TEXT COMMENT 'Request parameters (JSON)',
    response_status INT COMMENT 'HTTP response status',
    response_time_ms INT COMMENT 'Response time in milliseconds',
    rate_limit_remaining INT COMMENT 'Remaining rate limit',
    error_message TEXT COMMENT 'Error message if failed',
    called_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_endpoint (endpoint),
    INDEX idx_called_at (called_at),
    INDEX idx_module (module),
    INDEX idx_response_status (response_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API call log';

-- ============================================================================
-- TABLE 8: system_config
-- Purpose: System configuration key-value store
-- ============================================================================
CREATE TABLE IF NOT EXISTS system_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(100) UNIQUE NOT NULL COMMENT 'Configuration key',
    config_value TEXT COMMENT 'Configuration value',
    config_type VARCHAR(50) DEFAULT 'string' COMMENT 'Data type',
    description TEXT COMMENT 'Configuration description',
    is_active TINYINT DEFAULT 1 COMMENT '1=active, 0=inactive',
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='System configuration';

-- ============================================================================
-- TABLE 9: error_log
-- Purpose: Application error logging
-- ============================================================================
CREATE TABLE IF NOT EXISTS error_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    error_type VARCHAR(50) COMMENT 'Error category',
    error_message TEXT COMMENT 'Error message',
    context_data TEXT COMMENT 'Context data (JSON)',
    severity VARCHAR(20) COMMENT 'DEBUG, INFO, WARNING, ERROR, CRITICAL',
    file_path VARCHAR(255) COMMENT 'File where error occurred',
    line_number INT COMMENT 'Line number',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at),
    INDEX idx_severity (severity),
    INDEX idx_error_type (error_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Error log';

-- ============================================================================
-- SEED DATA: Initial watched addresses
-- ============================================================================
INSERT INTO watched_addresses (address, address_type, name_tag, min_value_usd) VALUES
('0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045', 'WHALE', 'Vitalik Buterin', 100000),
('0x28c6c06298d514db089934071355e5743bf21d60', 'EXCHANGE', 'Binance Hot Wallet 1', 1000000),
('0xBE0eB53F46cd790Cd13851d5EFf43D12404d33E8', 'EXCHANGE', 'Binance Hot Wallet 2', 1000000),
('0x7a250d5630B4cF539739dF2C5dAcb4c659F2488D', 'PROTOCOL', 'Uniswap V2 Router', 50000),
('0xE592427A0AEce92De3Edee1F18E0157C05861564', 'PROTOCOL', 'Uniswap V3 Router', 50000),
('0xd9e1cE17f2641f24aE83637ab66a2cca9C378B9F', 'PROTOCOL', 'Sushiswap Router', 50000)
ON DUPLICATE KEY UPDATE name_tag = VALUES(name_tag);

-- ============================================================================
-- SEED DATA: Initial system configuration
-- ============================================================================
INSERT INTO system_config (config_key, config_value, config_type, description) VALUES
('maintenance_mode', '0', 'boolean', 'System maintenance mode (0=off, 1=on)'),
('min_profit_threshold', '0.5', 'decimal', 'Minimum profit percentage for opportunities'),
('max_gas_price_gwei', '200', 'integer', 'Maximum gas price to execute opportunities'),
('enable_arbitrage_detection', '1', 'boolean', 'Enable arbitrage opportunity detection'),
('enable_sandwich_detection', '1', 'boolean', 'Enable sandwich opportunity detection'),
('enable_liquidation_detection', '1', 'boolean', 'Enable liquidation opportunity detection'),
('whale_threshold_usd', '100000', 'decimal', 'Minimum transaction value for whale alerts'),
('large_swap_threshold_usd', '50000', 'decimal', 'Minimum swap value for large swap alerts'),
('opportunity_expiry_minutes', '5', 'integer', 'Minutes until opportunity expires'),
('data_retention_days', '30', 'integer', 'Days to retain old data'),
('api_timeout_seconds', '30', 'integer', 'API request timeout in seconds')
ON DUPLICATE KEY UPDATE config_value = VALUES(config_value);

-- ============================================================================
-- SUCCESS MESSAGE
-- ============================================================================
SELECT 'Database schema installed successfully!' as Status;
SELECT COUNT(*) as watched_addresses_count FROM watched_addresses;
SELECT COUNT(*) as system_config_count FROM system_config;
