-- =====================================================================
-- TradeForge — SQLite schema (v0.1 / MVP)
-- Options research & portfolio management platform
-- Owner: Jan Erik | Stack: PHP + SQLite + JS | Broker: Charles Schwab
--
-- Conventions:
--   * All monetary values are stored as INTEGER cents (avoids float
--     rounding errors in P/L math). Divide by 100 for display.
--   * All timestamps are TEXT in ISO-8601 ("YYYY-MM-DD HH:MM:SS", UTC).
--     This format sorts lexically and maps cleanly onto MySQL
--     DATETIME if the app is ever migrated to a multi-user MySQL setup.
--   * Every account/user-scoped table carries user_id and/or
--     broker_account_id from day one, even though the app is single-user
--     today (user_id will always be 1). This is what makes a future
--     SQLite -> MySQL multi-user migration a data-copy exercise instead
--     of a schema rewrite.
--   * Tables marked [PHASE 2] support features that are lower priority
--     for the MVP (Backtesting Engine, saved Screener/Trade Lab
--     presets) — safe to create now, fine to leave empty until built.
-- =====================================================================

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------
-- Identity & broker connections
-- ---------------------------------------------------------------------

CREATE TABLE users (
    id            INTEGER PRIMARY KEY,
    username      TEXT NOT NULL UNIQUE,
    email         TEXT,
    password_hash TEXT,                         -- nullable: not needed until multi-user
    created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);

CREATE TABLE broker_accounts (
    id             INTEGER PRIMARY KEY,
    user_id        INTEGER NOT NULL REFERENCES users(id),
    broker         TEXT NOT NULL DEFAULT 'schwab',
    account_number TEXT NOT NULL,
    nickname       TEXT,
    account_type   TEXT,                         -- margin | cash | ira
    is_active      INTEGER NOT NULL DEFAULT 1,
    created_at     TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')),
    UNIQUE (user_id, broker, account_number)
);

-- Access/refresh tokens. Encrypt access_token/refresh_token at the
-- application layer before writing (e.g. libsodium) — the DB file
-- itself is not a secrets vault.
CREATE TABLE broker_oauth_tokens (
    id                INTEGER PRIMARY KEY,
    broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id),
    access_token      TEXT NOT NULL,
    refresh_token     TEXT NOT NULL,
    expires_at        TEXT NOT NULL,
    updated_at        TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);

-- ---------------------------------------------------------------------
-- Reference / market data cache
-- ---------------------------------------------------------------------

CREATE TABLE underlyings (
    id         INTEGER PRIMARY KEY,
    symbol     TEXT NOT NULL UNIQUE,
    name       TEXT,
    sector     TEXT,
    industry   TEXT,
    asset_type TEXT NOT NULL DEFAULT 'equity',   -- equity | etf | index
    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);

-- Polled quote/IV snapshots (Schwab REST, not push streaming). Feeds
-- IV Rank/Percentile history and the Market Intelligence module.
CREATE TABLE quote_snapshots (
    id            INTEGER PRIMARY KEY,
    underlying_id INTEGER NOT NULL REFERENCES underlyings(id),
    captured_at   TEXT NOT NULL,
    last_price    INTEGER,                       -- cents
    bid           INTEGER,
    ask           INTEGER,
    volume        INTEGER,
    iv_rank       REAL,
    iv_percentile REAL,
    hv_30         REAL,
    beta          REAL
);
CREATE INDEX idx_quote_snapshots_underlying_time ON quote_snapshots(underlying_id, captured_at);

CREATE TABLE earnings_calendar (
    id            INTEGER PRIMARY KEY,
    underlying_id INTEGER NOT NULL REFERENCES underlyings(id),
    earnings_date TEXT NOT NULL,
    time_of_day   TEXT,                           -- BMO | AMC | unknown
    confirmed     INTEGER NOT NULL DEFAULT 0,
    source        TEXT
);
CREATE INDEX idx_earnings_underlying_date ON earnings_calendar(underlying_id, earnings_date);

CREATE TABLE dividend_calendar (
    id            INTEGER PRIMARY KEY,
    underlying_id INTEGER NOT NULL REFERENCES underlyings(id),
    ex_date       TEXT NOT NULL,
    amount        INTEGER,                        -- cents/share
    pay_date      TEXT
);
CREATE INDEX idx_dividends_underlying_date ON dividend_calendar(underlying_id, ex_date);

-- ---------------------------------------------------------------------
-- Positions & raw trade fills (synced from Schwab)
-- ---------------------------------------------------------------------

CREATE TABLE positions (
    id                 INTEGER PRIMARY KEY,
    broker_account_id  INTEGER NOT NULL REFERENCES broker_accounts(id),
    underlying_id      INTEGER NOT NULL REFERENCES underlyings(id),
    instrument_type    TEXT NOT NULL CHECK (instrument_type IN ('equity','option')),
    option_type        TEXT CHECK (option_type IN ('call','put') OR option_type IS NULL),
    strike             INTEGER,                    -- cents, NULL for equity
    expiration         TEXT,                        -- date, NULL for equity
    quantity           REAL NOT NULL,               -- signed: + long, - short
    avg_cost           INTEGER,                     -- cents per share/contract
    current_price      INTEGER,                     -- cents, cached from last sync
    delta              REAL,
    gamma              REAL,
    theta              REAL,
    vega               REAL,
    rho                REAL,
    broker_position_id TEXT,                        -- external id, for reconciliation
    updated_at         TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);
CREATE INDEX idx_positions_account ON positions(broker_account_id);
CREATE INDEX idx_positions_underlying ON positions(underlying_id);

CREATE TABLE trades (
    id                  INTEGER PRIMARY KEY,
    broker_account_id   INTEGER NOT NULL REFERENCES broker_accounts(id),
    underlying_id       INTEGER NOT NULL REFERENCES underlyings(id),
    instrument_type     TEXT NOT NULL CHECK (instrument_type IN ('equity','option')),
    option_type         TEXT CHECK (option_type IN ('call','put') OR option_type IS NULL),
    strike              INTEGER,
    expiration          TEXT,
    action              TEXT NOT NULL CHECK (action IN (
                            'buy_to_open','sell_to_open','buy_to_close','sell_to_close',
                            'assignment','exercise','expiration')),
    quantity             INTEGER NOT NULL,
    price                INTEGER NOT NULL DEFAULT 0,  -- cents per share/contract
    fees                 INTEGER NOT NULL DEFAULT 0,
    commission            INTEGER NOT NULL DEFAULT 0,
    executed_at          TEXT NOT NULL,
    broker_order_id      TEXT,
    broker_execution_id  TEXT UNIQUE,                 -- idempotency key for re-imports
    created_at           TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);
CREATE INDEX idx_trades_account_time ON trades(broker_account_id, executed_at);
CREATE INDEX idx_trades_underlying ON trades(underlying_id);

CREATE TABLE import_log (
    id                 INTEGER PRIMARY KEY,
    broker_account_id  INTEGER NOT NULL REFERENCES broker_accounts(id),
    run_at             TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')),
    status             TEXT NOT NULL CHECK (status IN ('success','partial','failed')),
    trades_imported    INTEGER NOT NULL DEFAULT 0,
    error_message      TEXT
);

-- ---------------------------------------------------------------------
-- Strategy Classification Engine + Trade Journal
-- ---------------------------------------------------------------------

CREATE TABLE strategy_types (
    id            INTEGER PRIMARY KEY,
    code          TEXT NOT NULL UNIQUE,     -- CSP, CC, WHEEL, PMCC, IRON_CONDOR, IRON_FLY,
                                             -- BUTTERFLY, VERTICAL, STRANGLE, STRADDLE,
                                             -- CALENDAR, DIAGONAL, COVERED_STRANGLE,
                                             -- JADE_LIZARD, CUSTOM ...
    label         TEXT NOT NULL,
    leg_count_min INTEGER,
    leg_count_max INTEGER
);

-- One row per identified "trade" in the trade-journal sense: a group
-- of one or more legs that together form a strategy occurrence.
CREATE TABLE strategy_instances (
    id                 INTEGER PRIMARY KEY,
    broker_account_id  INTEGER NOT NULL REFERENCES broker_accounts(id),
    underlying_id      INTEGER NOT NULL REFERENCES underlyings(id),
    strategy_type_id   INTEGER NOT NULL REFERENCES strategy_types(id),
    status             TEXT NOT NULL DEFAULT 'open'
                          CHECK (status IN ('open','closed','assigned','expired','rolled')),
    opened_at          TEXT NOT NULL,
    closed_at          TEXT,
    capital_at_risk    INTEGER,               -- cents
    max_profit         INTEGER,               -- cents, NULL if undefined (naked call, etc.)
    max_loss           INTEGER,               -- cents, NULL if undefined
    realized_pl        INTEGER,               -- cents, set on close
    roc                REAL,                  -- realized_pl / capital_at_risk
    annualized_roc     REAL,
    dte_at_open        INTEGER,
    notes              TEXT,
    tags               TEXT,                  -- comma-separated
    classified_by      TEXT NOT NULL DEFAULT 'auto' CHECK (classified_by IN ('auto','manual')),
    created_at         TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')),
    updated_at         TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);
CREATE INDEX idx_strategy_instances_account ON strategy_instances(broker_account_id, status);
CREATE INDEX idx_strategy_instances_underlying ON strategy_instances(underlying_id);
CREATE INDEX idx_strategy_instances_type ON strategy_instances(strategy_type_id);

-- Join table: which raw trade fills make up which strategy instance.
CREATE TABLE strategy_instance_legs (
    id                    INTEGER PRIMARY KEY,
    strategy_instance_id  INTEGER NOT NULL REFERENCES strategy_instances(id),
    trade_id              INTEGER NOT NULL REFERENCES trades(id),
    leg_role              TEXT,                -- e.g. 'short_put', 'long_call'
    UNIQUE (strategy_instance_id, trade_id)
);
CREATE INDEX idx_instance_legs_trade ON strategy_instance_legs(trade_id);

-- ---------------------------------------------------------------------
-- Wheel Tracker (the flagship niche module)
-- ---------------------------------------------------------------------

CREATE TABLE wheel_cycles (
    id                     INTEGER PRIMARY KEY,
    broker_account_id      INTEGER NOT NULL REFERENCES broker_accounts(id),
    underlying_id          INTEGER NOT NULL REFERENCES underlyings(id),
    cycle_number           INTEGER NOT NULL,       -- increments per ticker
    status                 TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','completed')),
    started_at             TEXT NOT NULL,
    completed_at           TEXT,
    shares_assigned        INTEGER NOT NULL DEFAULT 0,
    assignment_price       INTEGER,                 -- cents/share
    adjusted_basis         INTEGER,                 -- cents/share, after premium offsets
    total_premium_collected INTEGER NOT NULL DEFAULT 0,  -- cents
    total_pl               INTEGER,                 -- cents
    rotations              INTEGER NOT NULL DEFAULT 0,   -- # of CSP/CC rounds in this cycle
    UNIQUE (underlying_id, broker_account_id, cycle_number)
);
CREATE INDEX idx_wheel_cycles_underlying ON wheel_cycles(underlying_id, status);

CREATE TABLE wheel_cycle_strategy_instances (
    id                    INTEGER PRIMARY KEY,
    wheel_cycle_id        INTEGER NOT NULL REFERENCES wheel_cycles(id),
    strategy_instance_id  INTEGER NOT NULL REFERENCES strategy_instances(id),
    UNIQUE (wheel_cycle_id, strategy_instance_id)
);

-- ---------------------------------------------------------------------
-- Portfolio Hub (account-level snapshots for equity curve / risk history)
-- ---------------------------------------------------------------------

CREATE TABLE portfolio_snapshots (
    id                   INTEGER PRIMARY KEY,
    broker_account_id    INTEGER NOT NULL REFERENCES broker_accounts(id),
    snapshot_at          TEXT NOT NULL,
    net_liq              INTEGER,     -- cents
    cash                 INTEGER,
    buying_power         INTEGER,
    margin_used          INTEGER,
    portfolio_delta      REAL,
    portfolio_gamma      REAL,
    portfolio_theta      REAL,
    portfolio_vega       REAL,
    beta_weighted_delta  REAL,
    UNIQUE (broker_account_id, snapshot_at)
);
CREATE INDEX idx_portfolio_snapshots_account_time ON portfolio_snapshots(broker_account_id, snapshot_at);

-- ---------------------------------------------------------------------
-- Watchlists
-- ---------------------------------------------------------------------

CREATE TABLE watchlists (
    id         INTEGER PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id),
    name       TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')),
    UNIQUE (user_id, name)
);

CREATE TABLE watchlist_items (
    id            INTEGER PRIMARY KEY,
    watchlist_id  INTEGER NOT NULL REFERENCES watchlists(id),
    underlying_id INTEGER NOT NULL REFERENCES underlyings(id),
    added_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')),
    notes         TEXT,
    UNIQUE (watchlist_id, underlying_id)
);

-- ---------------------------------------------------------------------
-- Opportunity Screener & Trade Lab presets  [PHASE 2]
-- ---------------------------------------------------------------------

CREATE TABLE screener_filters (
    id               INTEGER PRIMARY KEY,
    user_id          INTEGER NOT NULL REFERENCES users(id),
    name             TEXT NOT NULL,
    strategy_type_id INTEGER REFERENCES strategy_types(id),
    filter_json      TEXT NOT NULL,   -- {delta_min, delta_max, dte_min, dte_max, iv_rank_min, roc_min, ...}
    created_at       TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);

CREATE TABLE trade_lab_scenarios (
    id             INTEGER PRIMARY KEY,
    user_id        INTEGER NOT NULL REFERENCES users(id),
    underlying_id  INTEGER NOT NULL REFERENCES underlyings(id),
    name           TEXT,
    legs_json      TEXT NOT NULL,     -- [{type, strike, expiration, side, quantity}, ...]
    iv_shift       REAL NOT NULL DEFAULT 0,     -- simulation parameter, e.g. +0.10
    date_shift_days INTEGER NOT NULL DEFAULT 0, -- simulation parameter, e.g. +21
    created_at     TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);

-- ---------------------------------------------------------------------
-- Backtesting Engine  [PHASE 2]
-- ---------------------------------------------------------------------

CREATE TABLE backtest_runs (
    id               INTEGER PRIMARY KEY,
    user_id          INTEGER NOT NULL REFERENCES users(id),
    underlying_id    INTEGER REFERENCES underlyings(id),
    strategy_type_id INTEGER REFERENCES strategy_types(id),
    params_json      TEXT NOT NULL,   -- {delta_target, dte_target, date_from, date_to, ...}
    status           TEXT NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending','running','completed','failed')),
    started_at       TEXT,
    completed_at     TEXT,
    results_json     TEXT             -- {cagr, max_drawdown, win_rate, assignments, roc, ...}
);

-- ---------------------------------------------------------------------
-- Seed data: strategy type lookup table
-- ---------------------------------------------------------------------

INSERT INTO strategy_types (code, label, leg_count_min, leg_count_max) VALUES
    ('CSP',               'Cash-Secured Put',      1, 1),
    ('CC',                'Covered Call',           1, 1),
    ('WHEEL',              'Wheel',                  1, NULL),
    ('PMCC',                'Poor Man''s Covered Call', 2, 2),
    ('COVERED_STRANGLE',    'Covered Strangle',       2, 2),
    ('VERTICAL',            'Vertical Spread',        2, 2),
    ('IRON_CONDOR',         'Iron Condor',            4, 4),
    ('IRON_FLY',            'Iron Butterfly',         4, 4),
    ('BUTTERFLY',           'Butterfly',              3, 4),
    ('CALENDAR',            'Calendar Spread',        2, 2),
    ('DIAGONAL',            'Diagonal Spread',        2, 2),
    ('STRANGLE',            'Strangle',               2, 2),
    ('STRADDLE',            'Straddle',               2, 2),
    ('JADE_LIZARD',         'Jade Lizard',            3, 3),
    ('CUSTOM',              'Custom / Unclassified',  1, NULL);

INSERT INTO users (id, username) VALUES (1, 'jan_erik');
