

-- Trading Settings
CREATE TABLE IF NOT EXISTS trading_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL DEFAULT 'BTCUSDT',
    timeframe VARCHAR(10) DEFAULT '15m',
    
    -- Mode Trading
    mode_grid TINYINT(1) DEFAULT 1,
    mode_dca TINYINT(1) DEFAULT 1,
    
    -- Grid Settings
    grid_upper_price DECIMAL(18,8) DEFAULT 0,
    grid_lower_price DECIMAL(18,8) DEFAULT 0,
    grid_levels INT DEFAULT 10,
    grid_investment DECIMAL(18,8) DEFAULT 100,
    grid_dynamic TINYINT(1) DEFAULT 0,
    
    -- DCA Settings
    dca_levels INT DEFAULT 5,
    dca_drop_percentage DECIMAL(5,2) DEFAULT 5.00,
    dca_multiplier DECIMAL(5,2) DEFAULT 1.50,
    dca_max_investment DECIMAL(18,8) DEFAULT 500,
    
    -- Take Profit (dari balance)
    take_profit_enabled TINYINT(1) DEFAULT 1,
    take_profit_percentage DECIMAL(5,2) DEFAULT 2.00,
    
    -- Risk Management
    risk_stop_loss_enabled TINYINT(1) DEFAULT 1,
    risk_stop_loss_percentage DECIMAL(5,2) DEFAULT 5.00,
    risk_max_drawdown_enabled TINYINT(1) DEFAULT 1,
    risk_max_drawdown_percentage DECIMAL(5,2) DEFAULT 10.00,
    risk_daily_loss_enabled TINYINT(1) DEFAULT 1,
    risk_daily_loss_amount DECIMAL(18,8) DEFAULT 50,
    risk_max_positions_enabled TINYINT(1) DEFAULT 1,
    risk_max_positions INT DEFAULT 5,
    risk_trailing_stop_enabled TINYINT(1) DEFAULT 0,
    risk_trailing_stop_percentage DECIMAL(5,2) DEFAULT 3.00,
    
    -- Test/Live Mode
    is_test_mode TINYINT(1) DEFAULT 1,
    test_balance DECIMAL(18,8) DEFAULT 10.00,
    live_balance DECIMAL(18,8) DEFAULT 500.00,
    
    is_active TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Active Trades
CREATE TABLE IF NOT EXISTS active_trades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    side ENUM('buy', 'sell') NOT NULL,
    entry_price DECIMAL(18,8) NOT NULL,
    quantity DECIMAL(18,8) NOT NULL,
    total_amount DECIMAL(18,8) NOT NULL,
    trade_type ENUM('grid', 'dca') NOT NULL,
    grid_level INT DEFAULT NULL,
    dca_level INT DEFAULT NULL,
    parent_trade_id INT DEFAULT NULL,
    status ENUM('open', 'closed', 'partial') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Grid Orders
CREATE TABLE IF NOT EXISTS grid_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    grid_level INT NOT NULL,
    price DECIMAL(18,8) NOT NULL,
    quantity DECIMAL(18,8) NOT NULL,
    side ENUM('buy', 'sell') NOT NULL,
    status ENUM('pending', 'filled', 'cancelled') DEFAULT 'pending',
    order_id VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    filled_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- DCA Orders
CREATE TABLE IF NOT EXISTS dca_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    dca_level INT NOT NULL,
    trigger_price DECIMAL(18,8) NOT NULL,
    quantity DECIMAL(18,8) NOT NULL,
    multiplier DECIMAL(5,2) DEFAULT 1.00,
    status ENUM('pending', 'filled', 'cancelled') DEFAULT 'pending',
    order_id VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    filled_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Trade History
CREATE TABLE IF NOT EXISTS trade_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    side ENUM('buy', 'sell') NOT NULL,
    entry_price DECIMAL(18,8) NOT NULL,
    exit_price DECIMAL(18,8) DEFAULT NULL,
    quantity DECIMAL(18,8) NOT NULL,
    total_amount DECIMAL(18,8) NOT NULL,
    profit_loss DECIMAL(18,8) DEFAULT 0,
    profit_loss_percentage DECIMAL(8,4) DEFAULT 0,
    trade_type ENUM('grid', 'dca') NOT NULL,
    status ENUM('win', 'loss', 'breakeven') DEFAULT 'win',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Daily Statistics
CREATE TABLE IF NOT EXISTS daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    date DATE NOT NULL,
    total_trades INT DEFAULT 0,
    winning_trades INT DEFAULT 0,
    losing_trades INT DEFAULT 0,
    total_profit DECIMAL(18,8) DEFAULT 0,
    total_loss DECIMAL(18,8) DEFAULT 0,
    net_profit DECIMAL(18,8) DEFAULT 0,
    balance_start DECIMAL(18,8) DEFAULT 0,
    balance_end DECIMAL(18,8) DEFAULT 0,
    max_drawdown DECIMAL(8,4) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_date (user_id, date),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Bot Logs
CREATE TABLE IF NOT EXISTS bot_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    level ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    message TEXT NOT NULL,
    context TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- System Settings
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value) VALUES
('bot_version', '1.0.0'),
('bot_status', 'stopped'),
('last_cron_run', NULL);