-- Montrade5 MT5 Forex Monitoring System Database v2
-- CREATE DATABASE montrade5 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- SOURCE db/montrade5.sql;

USE montrade5;

-- =============================================
-- EA identity table
-- =============================================
CREATE TABLE ea (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_ea VARCHAR(100) NOT NULL,
    versi VARCHAR(20) NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_ea_versi (nama_ea, versi)
) ENGINE=InnoDB;

INSERT INTO ea (nama_ea, versi, deskripsi) VALUES
('EA5-SmartDynamic', '1.0', 'SmartDynamic EA Version 1.0 for MT5');

-- =============================================
-- Accounts table (trading accounts on VPS)
-- =============================================
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    mt_login VARCHAR(50) NOT NULL,
    broker_name VARCHAR(100) NOT NULL DEFAULT '',
    owner_name VARCHAR(100) NOT NULL DEFAULT '',
    vps_name VARCHAR(100) NOT NULL,
    ea_id INT NULL,
    ea_version VARCHAR(20),
    status ENUM('active', 'paused', 'stopped') DEFAULT 'active',
    last_ping TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    equity DOUBLE DEFAULT 0,
    balance DOUBLE DEFAULT 0,
    margin DOUBLE DEFAULT 0,
    free_margin DOUBLE DEFAULT 0,
    floating DOUBLE DEFAULT 0,
    dd_percent DOUBLE DEFAULT 0,
    total_profit DOUBLE DEFAULT 0,
    daily_baseline_equity DOUBLE DEFAULT 0,
    daily_progress DOUBLE DEFAULT 0,
    settings_json JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_api_key (api_key),
    INDEX idx_status (status),
    INDEX idx_last_ping (last_ping),
    FOREIGN KEY (ea_id) REFERENCES ea(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- Live positions (updated realtime by EA)
-- =============================================
CREATE TABLE positions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    ticket INT NOT NULL,
    symbol VARCHAR(20) NOT NULL DEFAULT '',
    type ENUM('buy', 'sell') NOT NULL,
    lots DOUBLE NOT NULL,
    open_price DOUBLE NOT NULL,
    sl DOUBLE,
    tp DOUBLE,
    current_price DOUBLE NOT NULL,
    profit DOUBLE NOT NULL,
    swap DOUBLE DEFAULT 0,
    commission DOUBLE DEFAULT 0,
    magic INT,
    comment VARCHAR(255) DEFAULT '',
    open_time TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    INDEX idx_account_ticket (account_id, ticket),
    INDEX idx_account_time (account_id, open_time)
) ENGINE=InnoDB;

-- =============================================
-- Transaction history
-- =============================================
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    ticket INT NOT NULL,
    type ENUM('open', 'close', 'modify') NOT NULL,
    order_type ENUM('buy', 'sell') NOT NULL,
    symbol VARCHAR(20) NOT NULL DEFAULT '',
    lots DOUBLE NOT NULL,
    price DOUBLE NOT NULL,
    profit DOUBLE DEFAULT 0,
    balance_after DOUBLE,
    comment VARCHAR(255),
    exec_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    INDEX idx_account_time (account_id, exec_time),
    INDEX idx_ticket (ticket)
) ENGINE=InnoDB;

-- =============================================
-- Admin commands (polled by EA)
-- =============================================
CREATE TABLE commands (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    command VARCHAR(50) NOT NULL,
    params_json JSON,
    status ENUM('pending', 'executed', 'failed') DEFAULT 'pending',
    executed_at TIMESTAMP NULL,
    result_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id),
    INDEX idx_account_status (account_id, status),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- =============================================
-- Equity snapshots (daily baseline tracking)
-- =============================================
CREATE TABLE equity_snapshots (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    equity DOUBLE NOT NULL,
    balance DOUBLE NOT NULL,
    floating DOUBLE DEFAULT 0,
    dd_percent DOUBLE DEFAULT 0,
    snapshot_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    UNIQUE KEY uk_account_date (account_id, snapshot_date),
    INDEX idx_account_date (account_id, snapshot_date)
) ENGINE=InnoDB;

-- =============================================
-- Users table for authentication & RBAC
-- =============================================
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'operator', 'member') NOT NULL DEFAULT 'member',
    status ENUM('pending', 'approved', 'blocked') DEFAULT 'pending',
    approved_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_role (role),
    FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- =============================================
-- User permissions (RBAC)
-- =============================================
CREATE TABLE user_permissions (
    id INT AUTO_INCREMENT,
    user_id INT NOT NULL,
    resource VARCHAR(50) NOT NULL,
    action ENUM('read', 'write', 'delete', 'approve') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_user_resource_action (user_id, resource, action),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_resource (user_id, resource)
) ENGINE=InnoDB;

-- =============================================
-- Akun member: links user <-> account <-> EA
-- =============================================
CREATE TABLE akun_member (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    account_id INT NOT NULL,
    ea_id INT NULL,
    is_active TINYINT(1) DEFAULT 1,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (ea_id) REFERENCES ea(id) ON DELETE SET NULL,
    UNIQUE KEY uk_user_account (user_id, account_id),
    INDEX idx_user (user_id),
    INDEX idx_account (account_id)
) ENGINE=InnoDB;

-- =============================================
-- Seed data
-- =============================================

-- Initial admin user (password: admin123)
INSERT INTO users (username, email, password_hash, role, status) VALUES 
('admin', 'admin@montrade5.com', '$2y$10$8QdaIAzUtTi9Dy7T5bmi2e/P5TKKI/OgvI7arh9vqop4yk0JtK4NW', 'admin', 'approved');

-- Default admin permissions
INSERT INTO user_permissions (user_id, resource, action) VALUES
(1, 'accounts', 'read'), (1, 'accounts', 'write'), (1, 'accounts', 'delete'),
(1, 'commands', 'read'), (1, 'commands', 'write'), (1, 'commands', 'delete'),
(1, 'reports', 'read'), (1, 'reports', 'write'),
(1, 'users', 'read'), (1, 'users', 'write'), (1, 'users', 'delete'), (1, 'users', 'approve'),
(1, 'ea', 'read'), (1, 'ea', 'write'), (1, 'ea', 'delete');

-- Sample account
INSERT INTO accounts (api_key, mt_login, broker_name, owner_name, vps_name, ea_id) VALUES 
('demo5_key_123', '12345678', 'Exness', 'John Demo', 'Demo VPS 1', 1);

-- Link admin to account
INSERT INTO akun_member (user_id, account_id, ea_id) VALUES
(1, 1, 1);