"""
Crea le tabelle per la pipeline live (agente formazioni, snapshot quote,
predizioni) come da design in docs/agent-01-formazioni-design.md.
Idempotente: usa CREATE TABLE IF NOT EXISTS.

Uso:
    python scripts/create_live_tables.py
"""

import argparse
import sys

import pymysql

import config

SCHEMA = """
CREATE TABLE IF NOT EXISTS odds_snapshots (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id    INT UNSIGNED NOT NULL,
    taken_at    DATETIME     NOT NULL,
    stage       VARCHAR(10)  NOT NULL,          -- 'S1', 'S2', 'closing'
    source      VARCHAR(30)  NOT NULL,          -- 'football-data', 'odds-api', ...
    odds_home   DECIMAL(7,3) NULL,
    odds_draw   DECIMAL(7,3) NULL,
    odds_away   DECIMAL(7,3) NULL,
    prob_home   DECIMAL(6,4) NULL,              -- normalizzate, senza margine
    prob_draw   DECIMAL(6,4) NULL,
    prob_away   DECIMAL(6,4) NULL,
    KEY idx_match_stage (match_id, stage),
    CONSTRAINT fk_snap_match FOREIGN KEY (match_id) REFERENCES matches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agent_signals (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id          INT UNSIGNED NOT NULL,
    agent             VARCHAR(30)  NOT NULL,     -- 'formazioni'
    stage             VARCHAR(10)  NOT NULL,     -- 'S1', 'S2'
    checked_at        DATETIME     NOT NULL,
    payload           JSON         NULL,         -- output JSON grezzo dell'LLM
    delta_lambda_home DECIMAL(6,4) NULL,         -- aggiustamento log-gol-attesi
    delta_lambda_away DECIMAL(6,4) NULL,
    confidence        DECIMAL(4,3) NULL,
    model_version     VARCHAR(40)  NULL,         -- versione prompt/pipeline
    KEY idx_match_agent (match_id, agent, stage),
    CONSTRAINT fk_signal_match FOREIGN KEY (match_id) REFERENCES matches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS predictions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id    INT UNSIGNED NOT NULL,
    made_at     DATETIME     NOT NULL,
    stage       VARCHAR(10)  NOT NULL,           -- 'S1', 'S2'
    source      ENUM('model', 'market', 'blend', 'agent_adj') NOT NULL,
    prob_home   DECIMAL(6,4) NOT NULL,
    prob_draw   DECIMAL(6,4) NOT NULL,
    prob_away   DECIMAL(6,4) NOT NULL,
    details     JSON         NULL,               -- es. segnali usati, pesi
    KEY idx_match_source (match_id, source, stage),
    CONSTRAINT fk_pred_match FOREIGN KEY (match_id) REFERENCES matches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""


def main() -> int:
    parser = argparse.ArgumentParser(description="Crea le tabelle della pipeline live")
    config.add_db_args(parser)
    args = parser.parse_args()

    conn = pymysql.connect(host=args.host, port=args.port, user=args.user,
                           password=args.password, database="tigertips")
    cur = conn.cursor()
    for statement in SCHEMA.split(";"):
        if statement.strip():
            cur.execute(statement)
    conn.commit()

    cur.execute("SHOW TABLES")
    print("Tabelle nel database tigertips:")
    for (name,) in cur.fetchall():
        print(f"  {name}")
    conn.close()
    return 0


if __name__ == "__main__":
    sys.exit(main())