"""SQLite schema（可迁移设计）。时间戳存 UTC ms。"""

from __future__ import annotations

import sqlite3
from pathlib import Path

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS option_quotes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts_ms INTEGER NOT NULL,
    exchange TEXT NOT NULL,
    underlying TEXT NOT NULL,
    inst_id TEXT NOT NULL,
    expiry_ymd TEXT NOT NULL,
    strike REAL NOT NULL,
    side TEXT NOT NULL,
    index_px REAL NOT NULL,
    ask REAL,
    bid REAL,
    ask_sz REAL,
    bid_sz REAL,
    leverage REAL,
    created_at_ms INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_oq_ts ON option_quotes(ts_ms);
CREATE INDEX IF NOT EXISTS idx_oq_side_ts ON option_quotes(side, ts_ms);
CREATE INDEX IF NOT EXISTS idx_oq_expiry ON option_quotes(expiry_ymd);

CREATE TABLE IF NOT EXISTS index_ticks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts_ms INTEGER NOT NULL,
    exchange TEXT NOT NULL,
    underlying TEXT NOT NULL,
    index_px REAL NOT NULL,
    created_at_ms INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_it_ts ON index_ticks(ts_ms);
CREATE INDEX IF NOT EXISTS idx_it_u_ts ON index_ticks(underlying, ts_ms);

CREATE TABLE IF NOT EXISTS expiry_settlements (
    expiry_ymd TEXT PRIMARY KEY,
    settle_ts_ms INTEGER NOT NULL,
    settle_index_px REAL NOT NULL,
    exchange TEXT NOT NULL,
    underlying TEXT NOT NULL,
    created_at_ms INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS collector_heartbeat (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    last_ok_ts_ms INTEGER,
    last_error TEXT,
    last_error_ts_ms INTEGER,
    consecutive_failures INTEGER NOT NULL DEFAULT 0,
    meta_json TEXT
);
"""


def init_db(db_path: str | Path) -> sqlite3.Connection:
    path = Path(db_path)
    path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(path), check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")
    conn.executescript(SCHEMA_SQL)
    conn.execute(
        "INSERT OR IGNORE INTO collector_heartbeat (id, consecutive_failures) VALUES (1, 0)"
    )
    conn.commit()
    return conn