"""AegisTrade AI Database Session and Query Layer."""
from __future__ import annotations

import os
import sqlite3
import threading
from pathlib import Path
from typing import Any, Optional

from backend.app.audit.logger import audit_logger
from backend.app.config.settings import settings

INIT_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'operator',
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS system_state (
    key TEXT PRIMARY KEY,
    value_json TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS models (
    id TEXT PRIMARY KEY,
    version TEXT UNIQUE NOT NULL,
    algorithm TEXT NOT NULL,
    status TEXT NOT NULL,
    brier_score REAL NOT NULL,
    val_accuracy REAL NOT NULL,
    metrics_json TEXT NOT NULL,
    artifact_path TEXT NOT NULL,
    trained_at TEXT NOT NULL,
    promoted_at TEXT
);

CREATE TABLE IF NOT EXISTS signals (
    id TEXT PRIMARY KEY,
    model_id TEXT NOT NULL,
    symbol TEXT NOT NULL,
    timeframe TEXT NOT NULL,
    action TEXT NOT NULL,
    raw_probability REAL NOT NULL,
    calibrated_probability REAL NOT NULL,
    expected_return REAL NOT NULL,
    expected_risk REAL NOT NULL,
    expected_value REAL NOT NULL,
    regime TEXT NOT NULL,
    reason TEXT NOT NULL,
    timestamp TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS risk_decisions (
    id TEXT PRIMARY KEY,
    signal_id TEXT,
    approved INTEGER NOT NULL,
    rejection_reason TEXT,
    equity_at_decision TEXT NOT NULL,
    risk_budget TEXT NOT NULL,
    approved_quantity TEXT NOT NULL,
    entry_price TEXT NOT NULL,
    stop_loss_price TEXT NOT NULL,
    take_profit_price TEXT NOT NULL,
    max_loss_estimate TEXT NOT NULL,
    timestamp TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS orders (
    id TEXT PRIMARY KEY,
    exchange_order_id TEXT,
    risk_decision_id TEXT,
    mode TEXT NOT NULL,
    symbol TEXT NOT NULL,
    side TEXT NOT NULL,
    order_type TEXT NOT NULL,
    quantity TEXT NOT NULL,
    price TEXT NOT NULL,
    stop_loss TEXT,
    take_profit TEXT,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS order_executions (
    id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL,
    exec_qty TEXT NOT NULL,
    exec_price TEXT NOT NULL,
    exec_fee TEXT NOT NULL,
    fee_asset TEXT NOT NULL,
    exec_time TEXT NOT NULL,
    FOREIGN KEY(order_id) REFERENCES orders(id)
);

CREATE TABLE IF NOT EXISTS positions (
    id TEXT PRIMARY KEY,
    symbol TEXT NOT NULL,
    mode TEXT NOT NULL,
    side TEXT NOT NULL,
    quantity TEXT NOT NULL,
    entry_price TEXT NOT NULL,
    current_price TEXT NOT NULL,
    stop_loss TEXT NOT NULL,
    take_profit TEXT NOT NULL,
    unrealized_pnl TEXT NOT NULL,
    realized_pnl TEXT NOT NULL,
    is_open INTEGER NOT NULL,
    opened_at TEXT NOT NULL,
    closed_at TEXT
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id TEXT PRIMARY KEY,
    user_id TEXT,
    action TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    details_json TEXT NOT NULL,
    ip_address TEXT,
    timestamp TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_orders_symbol ON orders(symbol);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_positions_symbol ON positions(symbol);
CREATE INDEX IF NOT EXISTS idx_signals_timestamp ON signals(timestamp);
"""


class DatabaseManager:
    """Manages SQLite / PostgreSQL connections and transactions."""

    def __init__(self, db_url: Optional[str] = None) -> None:
        self.db_url = db_url or settings.DATABASE_URL
        self._lock = threading.Lock()
        self._db_path = self._extract_sqlite_path(self.db_url)
        self.init_db()

    def _extract_sqlite_path(self, url: str) -> str:
        if url.startswith("sqlite:///"):
            path_str = url.replace("sqlite:///", "")
            path = Path(path_str)
            path.parent.mkdir(parents=True, exist_ok=True)
            return str(path)
        # Default fallback
        path = Path("./data/aegistrade.db")
        path.parent.mkdir(parents=True, exist_ok=True)
        return str(path)

    def get_connection(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self._db_path, check_same_thread=False)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys = ON;")
        return conn

    def init_db(self) -> None:
        """Execute migration / table creation scripts."""
        with self._lock:
            with self.get_connection() as conn:
                conn.executescript(INIT_SCHEMA_SQL)
                conn.commit()
                audit_logger.info("Database initialized successfully at %s", self._db_path)

    def execute_write(self, sql: str, params: tuple = ()) -> None:
        with self._lock:
            with self.get_connection() as conn:
                conn.execute(sql, params)
                conn.commit()

    def execute_read_one(self, sql: str, params: tuple = ()) -> Optional[dict[str, Any]]:
        with self.get_connection() as conn:
            cur = conn.execute(sql, params)
            row = cur.fetchone()
            if row:
                return dict(row)
            return None

    def execute_read_all(self, sql: str, params: tuple = ()) -> list[dict[str, Any]]:
        with self.get_connection() as conn:
            cur = conn.execute(sql, params)
            rows = cur.fetchall()
            return [dict(row) for row in rows]

    def set_system_state(self, key: str, value_json: str) -> None:
        import datetime
        now = datetime.datetime.now(datetime.timezone.utc).isoformat()
        sql = """
        INSERT INTO system_state (key, value_json, updated_at)
        VALUES (?, ?, ?)
        ON CONFLICT(key) DO UPDATE SET value_json = excluded.value_json, updated_at = excluded.updated_at
        """
        self.execute_write(sql, (key, value_json, now))

    def get_system_state(self, key: str) -> Optional[str]:
        sql = "SELECT value_json FROM system_state WHERE key = ?"
        row = self.execute_read_one(sql, (key,))
        if row:
            return row["value_json"]
        return None


db_manager = DatabaseManager()