-- Koinkamu Initial Schema
-- MySQL / MariaDB
-- Run: mysql -u root koinkamu < migrations/001_initial_schema.sql

CREATE DATABASE IF NOT EXISTS koinkamu
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE koinkamu;

-- Master data koin
CREATE TABLE IF NOT EXISTS coins (
    id VARCHAR(100) PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    categories JSON,
    genesis_date DATE,
    market_cap_rank INT,
    image_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_symbol (symbol)
) ENGINE=InnoDB;

-- Snapshot data pasar & sosial
CREATE TABLE IF NOT EXISTS coin_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL,
    current_price_idr DECIMAL(20,8),
    current_price_usd DECIMAL(20,8),
    market_cap_idr BIGINT,
    total_volume_idr BIGINT,
    circulating_supply DECIMAL(30,8),
    total_supply DECIMAL(30,8),
    max_supply DECIMAL(30,8),
    price_change_1h DECIMAL(10,4),
    price_change_24h DECIMAL(10,4),
    price_change_7d DECIMAL(10,4),
    price_change_30d DECIMAL(10,4),
    ath_idr DECIMAL(20,8),
    ath_date DATETIME,
    atl_idr DECIMAL(20,8),
    atl_date DATETIME,
    twitter_followers INT,
    reddit_subscribers INT,
    telegram_users INT,
    github_stars INT,
    sentiment_votes_up DECIMAL(5,2),
    profile_json JSON,
    fetched_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE,
    INDEX idx_coin_fetched (coin_id, fetched_at DESC)
) ENGINE=InnoDB;

-- Data candle OHLCV (UPSERT by unique key)
CREATE TABLE IF NOT EXISTS ohlcv_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL,
    timeframe ENUM('1m','15m','30m','1h','4h','1D','1W') NOT NULL,
    source VARCHAR(50) NOT NULL,
    timestamp BIGINT NOT NULL,
    datetime_wib DATETIME NOT NULL,
    `open` DECIMAL(20,8) NOT NULL,
    high DECIMAL(20,8) NOT NULL,
    low DECIMAL(20,8) NOT NULL,
    `close` DECIMAL(20,8) NOT NULL,
    volume DECIMAL(30,8) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ohlcv (coin_id, timeframe, source, datetime_wib),
    INDEX idx_coin_tf_dt (coin_id, timeframe, datetime_wib DESC),
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Data exchange/market per koin
CREATE TABLE IF NOT EXISTS market_tickers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL,
    market_name VARCHAR(200),
    pair VARCHAR(50),
    price_idr DECIMAL(20,8),
    volume_idr DECIMAL(30,8),
    spread_pct DECIMAL(10,4),
    trust_score VARCHAR(20),
    trade_url VARCHAR(500),
    fetched_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE,
    INDEX idx_coin_tickers (coin_id, fetched_at DESC)
) ENGINE=InnoDB;

-- Koin prioritas pantauan
CREATE TABLE IF NOT EXISTS watchlist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL UNIQUE,
    display_order INT DEFAULT 0,
    notes TEXT,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Konfigurasi aplikasi (key-value JSON)
CREATE TABLE IF NOT EXISTS app_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value JSON NOT NULL,
    category ENUM('data_source','capital','fees','indicators','display','system') NOT NULL,
    description VARCHAR(500),
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Alokasi modal per koin
CREATE TABLE IF NOT EXISTS capital_allocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL UNIQUE,
    allocated_capital_idr DECIMAL(20,2) DEFAULT 1000000,
    active_capital_pct DECIMAL(5,2) DEFAULT 75.00,
    reserve_capital_pct DECIMAL(5,2) DEFAULT 25.00,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Sinyal trading tergenerate
CREATE TABLE IF NOT EXISTS trading_signals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL,
    signal_type ENUM('BUY','SELL','HOLD') NOT NULL,
    confidence ENUM('High','Medium','Low') NOT NULL,
    score DECIMAL(5,1) NOT NULL,
    recommended_strategy ENUM('scalping','swing','short_term','medium_term','long_term'),
    safety_rating ENUM('SAFE','MODERATE','RISKY','DANGEROUS'),
    entry_price DECIMAL(20,8),
    stop_loss DECIMAL(20,8),
    take_profit_1 DECIMAL(20,8),
    take_profit_2 DECIMAL(20,8),
    take_profit_3 DECIMAL(20,8),
    suggested_size_idr DECIMAL(20,2),
    indicators_json JSON,
    algorithm_versions JSON,
    status ENUM('active','executed','expired','cancelled') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME,
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE,
    INDEX idx_signal_status (status, created_at DESC)
) ENGINE=InnoDB;

-- Antrian beli/jual per hari (Timeline)
CREATE TABLE IF NOT EXISTS prediction_queues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL,
    prediction_date DATE NOT NULL,
    sequence_num INT NOT NULL,
    action ENUM('BUY','SELL') NOT NULL,
    predicted_price DECIMAL(20,8) NOT NULL,
    quantity DECIMAL(20,8) NOT NULL,
    cash_amount DECIMAL(20,2) NOT NULL,
    fee_amount DECIMAL(20,2) NOT NULL,
    profit DECIMAL(20,2),
    accumulated_units DECIMAL(20,8),
    accumulated_cash DECIMAL(20,2),
    confidence DECIMAL(5,2),
    reason VARCHAR(500),
    algorithm_used JSON,
    is_executed BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_prediction (coin_id, prediction_date, sequence_num),
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE,
    INDEX idx_pred_date (coin_id, prediction_date)
) ENGINE=InnoDB;

-- Portfolio tracking
CREATE TABLE IF NOT EXISTS portfolio (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL UNIQUE,
    total_quantity DECIMAL(20,8) DEFAULT 0,
    avg_buy_price DECIMAL(20,8) DEFAULT 0,
    total_invested_idr DECIMAL(20,2) DEFAULT 0,
    realized_pnl DECIMAL(20,2) DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Trade history
CREATE TABLE IF NOT EXISTS trade_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coin_id VARCHAR(100) NOT NULL,
    signal_id INT,
    side ENUM('BUY','SELL') NOT NULL,
    price DECIMAL(20,8) NOT NULL,
    quantity DECIMAL(20,8) NOT NULL,
    total_value_idr DECIMAL(20,2) NOT NULL,
    fee_idr DECIMAL(20,2) NOT NULL,
    net_value_idr DECIMAL(20,2) NOT NULL,
    pnl_idr DECIMAL(20,2),
    is_simulation BOOLEAN DEFAULT TRUE,
    executed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE,
    INDEX idx_trade_coin (coin_id, executed_at DESC)
) ENGINE=InnoDB;