"""
TradeLog — SQLite journal of OF trades (paper + live).
======================================================
One row per opened position; updated on close. Lets us compare live execution
(fill price, slippage, fee, realized PnL) against the screener's paper ticket.
"""

import logging
import os
import sqlite3

from src.config import DATA_DIR

logger = logging.getLogger("trade_log")
DB_PATH = os.path.join(DATA_DIR, "of_trades.db")


class TradeLog:
    def __init__(self):
        os.makedirs(DATA_DIR, exist_ok=True)
        self.db = sqlite3.connect(DB_PATH)
        self.db.execute("""
            CREATE TABLE IF NOT EXISTS of_trades (
                signal_id   INTEGER,
                symbol      TEXT,
                direction   TEXT,
                entry       REAL,
                tp          REAL,
                sl          REAL,
                qty         REAL,
                notional    REAL,
                dry         INTEGER,
                opened_at   TEXT DEFAULT (datetime('now')),
                closed_at   TEXT,
                close_reason TEXT,
                pnl         REAL,
                PRIMARY KEY (signal_id, symbol)
            )
        """)
        self.db.commit()

    def record(self, ev):
        typ = ev.get("type")
        try:
            if typ == "open":
                self.db.execute(
                    "INSERT OR REPLACE INTO of_trades "
                    "(signal_id, symbol, direction, entry, tp, sl, qty, notional, dry) "
                    "VALUES (?,?,?,?,?,?,?,?,?)",
                    (ev["id"], ev["symbol"], ev["direction"], ev["entry"], ev["tp"],
                     ev["sl"], ev["qty"], ev.get("notional", 0), 1 if ev.get("dry") else 0),
                )
                self.db.commit()
            elif typ == "close":
                self.db.execute(
                    "UPDATE of_trades SET closed_at=datetime('now'), close_reason=?, pnl=? "
                    "WHERE signal_id=? AND symbol=?",
                    (ev.get("reason"), ev.get("pnl", 0.0), ev["id"], ev["symbol"]),
                )
                self.db.commit()
            # 'skip' events are not journalled (noise)
        except sqlite3.Error as e:
            logger.error(f"Journal error ({typ}): {e}")

    def close_orphans(self, reason="orphan_restart"):
        """Mark dangling open rows closed. Called at startup right after
        nuclear_cleanup flattens the account — any still-open journal row is a
        leftover from a prior process (its real close was never recorded)."""
        try:
            cur = self.db.execute(
                "UPDATE of_trades SET closed_at=datetime('now'), close_reason=? "
                "WHERE close_reason IS NULL", (reason,))
            self.db.commit()
            return cur.rowcount
        except sqlite3.Error as e:
            logger.error(f"close_orphans error: {e}")
            return 0

    def stats(self):
        cur = self.db.execute(
            "SELECT COUNT(*) n, SUM(close_reason IS NOT NULL) closed, "
            "SUM(pnl) tot_pnl FROM of_trades"
        ).fetchone()
        return {"trades": cur[0], "closed": cur[1] or 0, "total_pnl": cur[2] or 0.0}