"""模拟资金 SQLite 表初始化与种子余额."""

from __future__ import annotations

import os
import sqlite3
from datetime import datetime


def _env_float(key: str, default: float) -> float:
    try:
        return float(os.getenv(key) if os.getenv(key) not in (None, "") else default)
    except (TypeError, ValueError):
        return float(default)


def _ensure_sim_wallet_coin_columns(conn: sqlite3.Connection) -> None:
    """旧库补齐币本位 ETH/BTC 列."""
    cols = {
        str(r[1])
        for r in conn.execute("PRAGMA table_info(sim_wallets)").fetchall()
    }
    for col in ("funding_eth", "trading_eth", "funding_btc", "trading_btc"):
        if col not in cols:
            conn.execute(
                f"ALTER TABLE sim_wallets ADD COLUMN {col} REAL NOT NULL DEFAULT 0"
            )


def init_sim_tables(conn: sqlite3.Connection) -> None:
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS sim_wallets (
            id INTEGER PRIMARY KEY CHECK (id = 1),
            funding_usdt REAL NOT NULL DEFAULT 0,
            trading_usdt REAL NOT NULL DEFAULT 0,
            funding_usdc REAL NOT NULL DEFAULT 0,
            trading_usdc REAL NOT NULL DEFAULT 0,
            funding_eth REAL NOT NULL DEFAULT 0,
            trading_eth REAL NOT NULL DEFAULT 0,
            funding_btc REAL NOT NULL DEFAULT 0,
            trading_btc REAL NOT NULL DEFAULT 0,
            updated_at TEXT
        )
        """
    )
    _ensure_sim_wallet_coin_columns(conn)
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS sim_perp_positions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            symbol TEXT NOT NULL,
            direction TEXT NOT NULL,
            contracts REAL NOT NULL,
            entry_px REAL NOT NULL,
            leverage INTEGER NOT NULL DEFAULT 1,
            margin_usdt REAL NOT NULL DEFAULT 0,
            contract_size REAL NOT NULL DEFAULT 1,
            updated_at TEXT,
            UNIQUE(symbol, direction)
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS sim_option_positions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            inst_id TEXT NOT NULL UNIQUE,
            side TEXT NOT NULL DEFAULT 'buy',
            sheets REAL NOT NULL,
            entry_px REAL NOT NULL,
            ct_mult REAL NOT NULL DEFAULT 0.01,
            premium_paid_usdc REAL NOT NULL DEFAULT 0,
            updated_at TEXT
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS sim_ledger_entries (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kind TEXT NOT NULL,
            amount REAL NOT NULL,
            ccy TEXT NOT NULL,
            account TEXT NOT NULL,
            balance_after REAL,
            note TEXT,
            ts TEXT
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS sim_option_orders (
            ord_id TEXT PRIMARY KEY,
            inst_id TEXT NOT NULL,
            side TEXT NOT NULL,
            sheets REAL NOT NULL,
            avg_px REAL NOT NULL,
            state TEXT NOT NULL DEFAULT 'filled',
            acc_fill_sz REAL NOT NULL,
            created_at TEXT
        )
        """
    )
    row = conn.execute("SELECT id FROM sim_wallets WHERE id=1").fetchone()
    if row is None:
        equity = max(0.0, _env_float("SIM_INITIAL_EQUITY_USDT", 10000.0))
        usdc = max(0.0, _env_float("SIM_INITIAL_USDC", 0.0))
        now = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
        conn.execute(
            """
            INSERT INTO sim_wallets(
                id, funding_usdt, trading_usdt, funding_usdc, trading_usdc, updated_at
            ) VALUES (1, ?, 0, ?, 0, ?)
            """,
            (equity, usdc, now),
        )
        conn.execute(
            """
            INSERT INTO sim_ledger_entries(kind, amount, ccy, account, balance_after, note, ts)
            VALUES ('seed', ?, 'USDT', 'funding', ?, 'initial equity', ?)
            """,
            (equity, equity, now),
        )
        if usdc > 0:
            conn.execute(
                """
                INSERT INTO sim_ledger_entries(kind, amount, ccy, account, balance_after, note, ts)
                VALUES ('seed', ?, 'USDC', 'funding', ?, 'initial usdc', ?)
                """,
                (usdc, usdc, now),
            )
    conn.commit()