-- ============================================================
-- AI Trading Signal Platform - Database Schema
-- ============================================================

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

-- Subscriptions table
CREATE TABLE IF NOT EXISTS subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan ENUM('free', 'basic', 'pro') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Signals table
CREATE TABLE IF NOT EXISTS signals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(50) NOT NULL,
    signal_type ENUM('BUY_CE', 'BUY_PE', 'HOLD') NOT NULL,
    confidence TINYINT UNSIGNED NOT NULL DEFAULT 0,
    ema20 DECIMAL(10,2),
    ema50 DECIMAL(10,2),
    vwap DECIMAL(10,2),
    rsi DECIMAL(5,2),
    current_price DECIMAL(10,2),
    expiry DATE,
    strike_price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trades table
CREATE TABLE IF NOT EXISTS trades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    signal_id INT,
    symbol VARCHAR(50) NOT NULL,
    trade_type ENUM('BUY_CE', 'BUY_PE') NOT NULL,
    mode ENUM('auto', 'manual') DEFAULT 'manual',
    quantity INT NOT NULL DEFAULT 1,
    entry_price DECIMAL(10,2) NOT NULL,
    exit_price DECIMAL(10,2),
    stop_loss DECIMAL(10,2),
    target DECIMAL(10,2),
    pnl DECIMAL(10,2),
    status ENUM('open', 'closed', 'cancelled') DEFAULT 'open',
    broker VARCHAR(50),
    order_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (signal_id) REFERENCES signals(id) ON DELETE SET NULL
);

-- Payments table
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan ENUM('basic', 'pro') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    upi_ref VARCHAR(100),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    screenshot_path VARCHAR(255),
    admin_note TEXT,
    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
);

-- Risk settings table
CREATE TABLE IF NOT EXISTS risk_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    stop_loss_pct DECIMAL(5,2) DEFAULT 2.00,
    target_pct DECIMAL(5,2) DEFAULT 4.00,
    trailing_sl TINYINT(1) DEFAULT 0,
    max_trades_day INT DEFAULT 3,
    cooldown_minutes INT DEFAULT 15,
    daily_loss_limit DECIMAL(10,2) DEFAULT 5000.00,
    auto_trading 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
);

-- System logs table
CREATE TABLE IF NOT EXISTS system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(255) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Insert sample signals
INSERT INTO signals (symbol, signal_type, confidence, ema20, ema50, vwap, rsi, current_price, strike_price) VALUES
('NIFTY50', 'BUY_CE', 78, 22150.50, 22080.20, 22120.30, 62.5, 22180.00, 22200.00),
('BANKNIFTY', 'BUY_PE', 65, 47800.00, 48100.50, 47950.00, 38.2, 47720.00, 47700.00),
('NIFTY50', 'HOLD', 45, 22100.00, 22090.00, 22095.00, 50.1, 22098.00, NULL);

-- Plan pricing reference (not stored in DB, managed in config)
-- FREE: ₹0/month
-- BASIC: ₹999/month  
-- PRO: ₹2499/month
