-- LittleDrops (Multi-Account) — Database Schema
-- Run this once in your cPanel MySQL database

CREATE TABLE IF NOT EXISTS users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    last_seen       DATETIME,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert the two users (default passwords: user1=littledrops1  user2=littledrops2)
-- CHANGE THESE PASSWORDS after first login.
INSERT IGNORE INTO users (username, password_hash) VALUES
('user1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'),
('user2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- One row per MT5 account. Auto-registered the first time that account's EA
-- sends a heartbeat — no manual setup needed per account.
CREATE TABLE IF NOT EXISTS accounts (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    account_number  VARCHAR(30) NOT NULL UNIQUE,
    account_name    VARCHAR(100),
    firm_name       VARCHAR(100),
    balance         DECIMAL(15,2) DEFAULT 0,
    equity          DECIMAL(15,2) DEFAULT 0,
    floating_pl     DECIMAL(15,2) DEFAULT 0,
    today_pl        DECIMAL(15,2) DEFAULT 0,
    last_heartbeat  DATETIME,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Per-account, per-user consensus vote. Both users must vote 'allow' on a
-- given account for trading to be permitted on THAT account specifically.
CREATE TABLE IF NOT EXISTS account_votes (
    account_id      INT NOT NULL,
    user_id         INT NOT NULL,
    consensus_vote  ENUM('allow','block') NOT NULL DEFAULT 'allow',
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (account_id, user_id),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS positions (
    ticket          BIGINT NOT NULL,
    account_id      INT NOT NULL,
    type            ENUM('buy','sell') NOT NULL,
    symbol          VARCHAR(20) NOT NULL,
    lots            DECIMAL(10,2),
    open_price      DECIMAL(15,5),
    current_price   DECIMAL(15,5),
    sl              DECIMAL(15,5) DEFAULT 0,
    tp              DECIMAL(15,5) DEFAULT 0,
    floating_pl     DECIMAL(15,2),
    open_time       DATETIME,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (ticket, account_id),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS commands (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    account_id      INT NOT NULL,
    command         VARCHAR(50) NOT NULL,
    params          TEXT,
    created_by      INT,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    executed_at     DATETIME,
    status          ENUM('pending','done','failed') DEFAULT 'pending',
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);