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

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

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') DEFAULT 'user',
    plan ENUM('free', 'basic', 'pro') DEFAULT 'free',
    status ENUM('active', 'blocked') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 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
);

-- Brokers table (API keys stored encrypted)
CREATE TABLE IF NOT EXISTS brokers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    broker_name ENUM('zerodha', 'upstox') NOT NULL,
    api_key TEXT NOT NULL,
    api_secret TEXT NOT NULL,
    access_token TEXT,
    is_active TINYINT(1) DEFAULT 1,
    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
);

-- 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 default admin user (password: Admin@123)
INSERT INTO users (name, email, password, role, plan, status) VALUES
('Super Admin', 'admin@tradingsignals.in', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'pro', 'active');

-- 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