-- ============================================================
-- config/schema_additions.sql
-- Add this to your existing database for auto-signal features
-- Run: mysql -u root -p trading_platform < config/schema_additions.sql
-- ============================================================

USE trading_platform;

-- Trending stocks real-time table
CREATE TABLE IF NOT EXISTS trending_stocks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(30) NOT NULL UNIQUE,
    price DECIMAL(12,2) NOT NULL,
    change_amt DECIMAL(10,2) DEFAULT 0,
    change_pct DECIMAL(6,2) DEFAULT 0,
    volume BIGINT DEFAULT 0,
    rsi DECIMAL(6,2) DEFAULT 50,
    ema20 DECIMAL(12,2) DEFAULT 0,
    ema50 DECIMAL(12,2) DEFAULT 0,
    vwap DECIMAL(12,2) DEFAULT 0,
    signal_type ENUM('BUY_CE','BUY_PE','HOLD') DEFAULT 'HOLD',
    confidence TINYINT DEFAULT 0,
    trend_score DECIMAL(6,1) DEFAULT 0,
    trend_dir ENUM('bullish','bearish','neutral') DEFAULT 'neutral',
    atr DECIMAL(10,2) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_trend_score (trend_score),
    INDEX idx_change_pct (change_pct),
    INDEX idx_signal (signal_type, confidence)
);

-- Signal scan log (track auto-scan runs)
CREATE TABLE IF NOT EXISTS signal_scans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scan_type ENUM('auto','manual','cron') DEFAULT 'cron',
    symbols_scanned INT DEFAULT 0,
    signals_generated INT DEFAULT 0,
    signals_skipped INT DEFAULT 0,
    duration_ms INT DEFAULT 0,
    market_open TINYINT(1) DEFAULT 0,
    errors TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add source column to signals (track where signal came from)
ALTER TABLE signals
    ADD COLUMN IF NOT EXISTS source ENUM('manual','auto','cron') DEFAULT 'manual',
    ADD COLUMN IF NOT EXISTS volume BIGINT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS atr DECIMAL(10,2) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS scan_factors JSON DEFAULT NULL;

-- Add last_signal_at to help dedup
ALTER TABLE signals
    ADD INDEX IF NOT EXISTS idx_symbol_time (symbol, created_at);

-- Watchlist per user (for custom symbol tracking)
CREATE TABLE IF NOT EXISTS watchlists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    symbol VARCHAR(30) NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_symbol (user_id, symbol),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Auto-signal scheduler config per admin
CREATE TABLE IF NOT EXISTS scheduler_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_enabled TINYINT(1) DEFAULT 1,
    scan_interval_minutes INT DEFAULT 5,
    min_confidence INT DEFAULT 55,
    symbols_enabled TEXT DEFAULT NULL, -- JSON array, NULL = all
    notify_users TINYINT(1) DEFAULT 1,
    last_run_at TIMESTAMP NULL,
    next_run_at TIMESTAMP NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT IGNORE INTO scheduler_config (is_enabled, scan_interval_minutes, min_confidence)
VALUES (1, 5, 55);

-- Seed some trending stocks with fallback data
INSERT IGNORE INTO trending_stocks
    (symbol, price, change_pct, volume, rsi, ema20, ema50, vwap, signal_type, confidence, trend_score, trend_dir)
VALUES
    ('NIFTY50',    22180, 0.48, 890000, 62.5, 22150, 22080, 22120, 'BUY_CE', 78, 42.3, 'bullish'),
    ('BANKNIFTY',  47720, -0.33, 420000, 38.2, 47800, 48100, 47950, 'BUY_PE', 65, -31.7, 'bearish'),
    ('RELIANCE',   2890,  0.72, 3200000, 64.1, 2875, 2840, 2860, 'BUY_CE', 72, 38.5, 'bullish'),
    ('HDFCBANK',   1680, -0.18, 2800000, 47.3, 1688, 1702, 1692, 'HOLD',   41, -8.2, 'neutral'),
    ('TCS',        3940,  1.05, 1900000, 67.8, 3910, 3870, 3895, 'BUY_CE', 81, 55.1, 'bullish'),
    ('INFY',       1820, -0.55, 2100000, 41.2, 1835, 1852, 1838, 'BUY_PE', 61, -27.4, 'bearish'),
    ('ICICIBANK',  1240,  0.89, 4500000, 66.3, 1228, 1210, 1220, 'BUY_CE', 75, 44.8, 'bullish'),
    ('SBIN',        790,  0.25, 6800000, 55.4, 787,  779,  783, 'BUY_CE', 58, 18.3, 'bullish'),
    ('FINNIFTY',  22300,  0.31, 310000, 59.1, 22270, 22200, 22240, 'BUY_CE', 63, 29.7, 'bullish'),
    ('BAJFINANCE', 7100, -0.88, 980000, 36.7, 7145, 7220, 7180, 'BUY_PE', 68, -39.2, 'bearish');