-- Crypto Trading Bot — Database Initialisation
-- Run: mysql -u root -p cryptobot < init_db.sql

CREATE DATABASE IF NOT EXISTS cryptobot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cryptobot;

-- ─────────────────────────────────────────────
-- TRADES
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS trades (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    pair                VARCHAR(20)  NOT NULL,
    mode                ENUM('REAL','SIMULATION') NOT NULL DEFAULT 'SIMULATION',
    signal              ENUM('BUY','SELL') NOT NULL,
    entry_price         DOUBLE       NOT NULL,
    current_price       DOUBLE,
    stop_loss           DOUBLE       NOT NULL,
    take_profit_levels  JSON         NOT NULL DEFAULT ('[]'),
    tp_hits             JSON         NOT NULL DEFAULT ('[false,false]'),
    quantity            DOUBLE,
    opened_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at           DATETIME,
    status              ENUM('OPEN','CLOSED') NOT NULL DEFAULT 'OPEN',
    result              ENUM('WIN','LOSS','BREAKEVEN'),
    pnl_percentage      DOUBLE,
    pnl_amount          DOUBLE,
    max_drawdown        DOUBLE DEFAULT 0,
    strategy_snapshot   JSON,
    INDEX idx_pair      (pair),
    INDEX idx_status    (status),
    INDEX idx_mode      (mode),
    INDEX idx_opened_at (opened_at)
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- STRATEGIES
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS strategies (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(100) NOT NULL UNIQUE,
    enabled          BOOLEAN      NOT NULL DEFAULT TRUE,
    weight           DOUBLE       NOT NULL DEFAULT 1.0,
    win_rate         DOUBLE                DEFAULT 0.0,
    total_signals    INT          NOT NULL DEFAULT 0,
    winning_signals  INT          NOT NULL DEFAULT 0,
    params           JSON,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- PERFORMANCE LOGS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS performance_logs (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    period_type    ENUM('DAILY','WEEKLY','MONTHLY') NOT NULL,
    period_start   DATETIME NOT NULL,
    period_end     DATETIME NOT NULL,
    total_trades   INT      NOT NULL DEFAULT 0,
    win_rate       DOUBLE,
    total_pnl      DOUBLE,
    max_drawdown   DOUBLE,
    sharpe_ratio   DOUBLE,
    profit_factor  DOUBLE,
    expectancy     DOUBLE,
    pair           VARCHAR(20),
    strategy_name  VARCHAR(100),
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_period_type  (period_type),
    INDEX idx_period_start (period_start)
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- DEFAULT SEEDS
-- ─────────────────────────────────────────────

-- Default strategies
INSERT INTO strategies (name, enabled, weight, params) VALUES
('RSI', TRUE, 1.0, JSON_OBJECT(
    'rsi_period', 14,
    'oversold', 30,
    'overbought', 70,
    'timeframes', JSON_ARRAY('15m', '1h')
)),
('EMA_Crossover', TRUE, 1.0, JSON_OBJECT(
    'fast_period', 9,
    'slow_period', 21,
    'timeframes', JSON_ARRAY('15m', '1h')
))
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;
