"""Lightweight, additive schema migrations.

`Base.metadata.create_all` creates missing tables but never adds columns to
existing ones. This module adds any missing columns so existing SQLite/MySQL
databases upgrade in place without data loss. Additive-only by design.
"""
import logging

from sqlalchemy import inspect, text
from sqlalchemy.engine import Engine

logger = logging.getLogger(__name__)

# table -> {column: DDL type}
_ADDITIVE_COLUMNS: dict[str, dict[str, str]] = {
    "users": {
        "role": "VARCHAR(8) DEFAULT 'user'",
    },
    "signals": {
        "signal_type": "VARCHAR(8) DEFAULT 'options'",
    },
    "paper_trades": {
        "mfe_r": "FLOAT DEFAULT 0",
        "mae_r": "FLOAT DEFAULT 0",
        "initial_stop": "FLOAT",
        "scaled_out": "INTEGER DEFAULT 0",
        "instrument": "VARCHAR(8) DEFAULT 'shares'",
        "option_contract": "JSON",
        "option_entry_premium": "FLOAT",
        "option_last_premium": "FLOAT",
        "option_contracts": "INTEGER DEFAULT 0",
        "option_pnl": "FLOAT DEFAULT 0",
    },
}


def run_migrations(engine: Engine) -> None:
    inspector = inspect(engine)
    tables = set(inspector.get_table_names())
    for table, columns in _ADDITIVE_COLUMNS.items():
        if table not in tables:
            continue  # create_all will build it fresh with all columns
        existing = {c["name"] for c in inspector.get_columns(table)}
        missing = {name: ddl for name, ddl in columns.items() if name not in existing}
        if not missing:
            continue
        with engine.begin() as conn:
            for name, ddl in missing.items():
                conn.execute(text(f"ALTER TABLE {table} ADD COLUMN {name} {ddl}"))
                logger.info("Migrated: added %s.%s", table, name)