-- =========================
-- ASI DATABASE SCHEMA
-- =========================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- =========================
-- USERS
-- =========================
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(191) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
) ENGINE=InnoDB;


-- =========================
-- ACCOUNTS (PROP CONTRACT PROFILE)
-- =========================
CREATE TABLE accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,

    account_label VARCHAR(100) NOT NULL,
    firm_name VARCHAR(100) NOT NULL,

    account_size DECIMAL(12,2) NOT NULL,
    max_drawdown_pct DECIMAL(5,2) NOT NULL,
    daily_drawdown_pct DECIMAL(5,2) NOT NULL,
    profit_target_pct DECIMAL(5,2) NOT NULL,

    time_limit_days INT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_accounts_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_accounts_user
ON accounts(user_id);


-- =========================
-- ASI SNAPSHOTS (CORE RESULT)
-- =========================
CREATE TABLE asi_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,

    asi_score TINYINT UNSIGNED NOT NULL,
    risk_class ENUM('STABLE','FRAGILE','HIGH_RISK','UNVIABLE') NOT NULL,

    failure_mode VARCHAR(100) NOT NULL,
    survival_days_min INT NOT NULL,
    survival_days_max INT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_asi_account
        FOREIGN KEY (account_id)
        REFERENCES accounts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_snapshots_account
ON asi_snapshots(account_id);


-- =========================
-- ASI INPUT STATE (RISK + PSYCHOLOGY)
-- =========================
CREATE TABLE asi_inputs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    snapshot_id BIGINT UNSIGNED NOT NULL,

    risk_per_trade_pct DECIMAL(5,2) NOT NULL,
    avg_trades_per_day INT NOT NULL,
    avg_rr DECIMAL(5,2) NOT NULL,
    stop_loss_r DECIMAL(5,2) NOT NULL,

    mental_load_score TINYINT UNSIGNED NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_inputs_snapshot
        FOREIGN KEY (snapshot_id)
        REFERENCES asi_snapshots(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_inputs_snapshot
ON asi_inputs(snapshot_id);


-- =========================
-- OPTIONAL: ASI CHECK RITUAL LOG
-- =========================
CREATE TABLE asi_checks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    asi_score TINYINT UNSIGNED NOT NULL,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_checks_account
        FOREIGN KEY (account_id)
        REFERENCES accounts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_checks_account
ON asi_checks(account_id);
