"""
BizNode Database Adapter
========================
Transparent adapter layer that supports SQLite, MySQL, and PostgreSQL.
All SQL is translated on the fly so memory/database.py needs zero changes
beyond delegating get_connection() here.

Env vars (read at import time):
  DB_TYPE     sqlite | mysql | postgresql   (default: sqlite)
  DB_HOST     hostname                       (default: localhost)
  DB_PORT     port number                    (default: 3306 for mysql, 5432 for pg)
  DB_NAME     database / schema name         (default: biznode)
  DB_USER     login username                 (default: root)
  DB_PASSWORD login password                 (default: "")
  SQLITE_PATH path to .db file               (default: memory/biznode.db)
"""

import os
import re
import sqlite3

_PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

# Read once at import time; _refresh_config() re-reads if env changes later.
DB_TYPE     = os.getenv("DB_TYPE", "sqlite").lower()
DB_HOST     = os.getenv("DB_HOST", "localhost")
DB_PORT_RAW = os.getenv("DB_PORT", "")
DB_NAME     = os.getenv("DB_NAME", "biznode")
DB_USER     = os.getenv("DB_USER", "root")
DB_PASSWORD = os.getenv("DB_PASSWORD", "")
SQLITE_PATH = os.getenv("SQLITE_PATH", os.path.join(_PROJECT_ROOT, "memory", "biznode.db"))


def _refresh_config():
    """Re-read DB config from os.environ. Called by get_connection() to pick up
    env vars that were loaded after this module was first imported."""
    global DB_TYPE, DB_HOST, DB_PORT_RAW, DB_NAME, DB_USER, DB_PASSWORD, SQLITE_PATH
    DB_TYPE     = os.getenv("DB_TYPE", "sqlite").lower()
    DB_HOST     = os.getenv("DB_HOST", "localhost")
    DB_PORT_RAW = os.getenv("DB_PORT", "")
    DB_NAME     = os.getenv("DB_NAME", "biznode")
    DB_USER     = os.getenv("DB_USER", "root")
    DB_PASSWORD = os.getenv("DB_PASSWORD", "")
    SQLITE_PATH = os.getenv("SQLITE_PATH", os.path.join(_PROJECT_ROOT, "memory", "biznode.db"))


def _default_port(db_type: str) -> int:
    return 5432 if db_type == "postgresql" else 3306


def _get_port() -> int:
    if DB_PORT_RAW:
        try:
            return int(DB_PORT_RAW)
        except ValueError:
            pass
    return _default_port(DB_TYPE)


# ---------------------------------------------------------------------------
# SQL translation
# ---------------------------------------------------------------------------

# Regex to convert SQLite AUTOINCREMENT upserts to MySQL ON DUPLICATE KEY
# Matches: ON CONFLICT(col) DO UPDATE SET col2=excluded.col2, col3=excluded.col3
_CONFLICT_RE = re.compile(
    r"ON\s+CONFLICT\s*\([^)]+\)\s*DO\s+UPDATE\s+SET\s+(.*?)(?=\s*(?:WHERE|$))",
    re.IGNORECASE | re.DOTALL,
)
_EXCLUDED_COL_RE = re.compile(r"(\w+)\s*=\s*excluded\.(\w+)", re.IGNORECASE)


def _translate_sql(sql: str, db_type: str) -> str:
    """Translate SQLite SQL dialect to MySQL or PostgreSQL as needed."""
    if db_type == "sqlite":
        return sql

    # Strip lines that are SQLite PRAGMA statements
    lines = []
    for line in sql.splitlines():
        stripped = line.strip()
        if stripped.upper().startswith("PRAGMA"):
            continue
        lines.append(line)
    sql = "\n".join(lines)

    # Replace ? placeholders with %s (MySQL and PostgreSQL both use %s)
    sql = sql.replace("?", "%s")

    if db_type == "mysql":
        # AUTOINCREMENT → AUTO_INCREMENT
        sql = re.sub(r"\bAUTOINCREMENT\b", "AUTO_INCREMENT", sql, flags=re.IGNORECASE)
        # INTEGER PRIMARY KEY AUTO_INCREMENT → INT AUTO_INCREMENT PRIMARY KEY
        sql = re.sub(
            r"\bINTEGER\s+PRIMARY\s+KEY\s+AUTO_INCREMENT\b",
            "INT AUTO_INCREMENT PRIMARY KEY",
            sql, flags=re.IGNORECASE,
        )
        # Convert ON CONFLICT upsert → ON DUPLICATE KEY UPDATE
        sql = _convert_upsert_mysql(sql)

    elif db_type == "postgresql":
        # INTEGER PRIMARY KEY AUTOINCREMENT → SERIAL PRIMARY KEY
        sql = re.sub(
            r"\bINTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT\b",
            "SERIAL PRIMARY KEY",
            sql, flags=re.IGNORECASE,
        )
        # DATETIME → TIMESTAMP (SQLite type, not valid in PostgreSQL)
        sql = re.sub(r"\bDATETIME\b", "TIMESTAMP", sql, flags=re.IGNORECASE)
        # ALTER TABLE t ADD COLUMN col → ADD COLUMN IF NOT EXISTS col
        # Prevents "column already exists" error that aborts the whole PG transaction.
        sql = re.sub(
            r"\bALTER\s+TABLE\s+(\S+)\s+ADD\s+COLUMN\s+(?!IF\s+NOT\s+EXISTS\s+)(\w+)",
            r"ALTER TABLE \1 ADD COLUMN IF NOT EXISTS \2",
            sql, flags=re.IGNORECASE,
        )
        # BOOLEAN DEFAULT 0/1 → BOOLEAN DEFAULT FALSE/TRUE
        sql = re.sub(r"\bBOOLEAN\s+DEFAULT\s+0\b", "BOOLEAN DEFAULT FALSE", sql, flags=re.IGNORECASE)
        sql = re.sub(r"\bBOOLEAN\s+DEFAULT\s+1\b", "BOOLEAN DEFAULT TRUE", sql, flags=re.IGNORECASE)
        # PostgreSQL supports ON CONFLICT ... DO UPDATE SET excluded.x natively —
        # no translation needed.

    return sql


def _convert_upsert_mysql(sql: str) -> str:
    """
    Convert SQLite ON CONFLICT(...) DO UPDATE SET col=excluded.col
    to MySQL ON DUPLICATE KEY UPDATE col=VALUES(col).
    """
    def _replacer(m):
        assignments = m.group(1)
        # Convert each  col = excluded.col  →  col = VALUES(col)
        converted = _EXCLUDED_COL_RE.sub(
            lambda a: f"{a.group(1)} = VALUES({a.group(2)})", assignments
        )
        return f"ON DUPLICATE KEY UPDATE {converted}"

    return _CONFLICT_RE.sub(_replacer, sql)


# ---------------------------------------------------------------------------
# CompatCursor
# ---------------------------------------------------------------------------

class CompatCursor:
    """
    Wraps a native DB cursor.
    - Translates SQL before execution.
    - Ensures fetchone() / fetchall() always return plain dicts.
    - lastrowid works for all three backends.
    """

    def __init__(self, native_cursor, db_type: str):
        self._cur = native_cursor
        self._db_type = db_type
        self._last_insert_id: int = 0

    # ── execution ─────────────────────────────────────────────────────

    def execute(self, sql: str, params=None):
        translated = _translate_sql(sql, self._db_type)
        if params is None:
            self._cur.execute(translated)
        else:
            self._cur.execute(translated, params)
        if self._db_type == "postgresql":
            # Cache lastrowid after INSERTs — only if a sequence was used
            stmt = translated.strip().upper()
            if stmt.startswith("INSERT"):
                try:
                    # Use a savepoint so that a LASTVAL() failure
                    # does not abort the outer transaction.
                    self._cur.execute("SAVEPOINT _lastval_check")
                    self._cur.execute("SELECT LASTVAL()")
                    row = self._cur.fetchone()
                    self._last_insert_id = row[0] if row else 0
                    self._cur.execute("RELEASE SAVEPOINT _lastval_check")
                except Exception:
                    self._last_insert_id = 0
                    try:
                        self._cur.execute("ROLLBACK TO SAVEPOINT _lastval_check")
                    except Exception:
                        pass
        return self

    def executemany(self, sql: str, seq_of_params):
        translated = _translate_sql(sql, self._db_type)
        self._cur.executemany(translated, seq_of_params)
        return self

    # ── fetch ──────────────────────────────────────────────────────────

    def _to_dict(self, row):
        if row is None:
            return None
        if isinstance(row, dict):
            return dict(row)
        # sqlite3.Row — has keys()
        if hasattr(row, "keys"):
            return dict(zip(row.keys(), tuple(row)))
        # Fallback: use cursor description
        if self._cur.description:
            cols = [d[0] for d in self._cur.description]
            return dict(zip(cols, row))
        return row

    def fetchone(self):
        return self._to_dict(self._cur.fetchone())

    def fetchall(self):
        rows = self._cur.fetchall()
        return [self._to_dict(r) for r in rows] if rows else []

    # ── lastrowid ──────────────────────────────────────────────────────

    @property
    def lastrowid(self) -> int:
        if self._db_type == "postgresql":
            return self._last_insert_id
        return self._cur.lastrowid or 0

    # ── rowcount ───────────────────────────────────────────────────────

    @property
    def rowcount(self) -> int:
        return self._cur.rowcount

    # ── description ────────────────────────────────────────────────────

    @property
    def description(self):
        return self._cur.description

    # ── context manager ────────────────────────────────────────────────

    def __enter__(self):
        return self

    def __exit__(self, *_):
        try:
            self._cur.close()
        except Exception:
            pass

    def close(self):
        try:
            self._cur.close()
        except Exception:
            pass


# ---------------------------------------------------------------------------
# CompatConnection
# ---------------------------------------------------------------------------

class CompatConnection:
    """
    Wraps a native DB connection.
    cursor() returns a CompatCursor configured for the right dict-row style.
    commit() / rollback() / close() delegate directly.
    """

    def __init__(self, native_conn, db_type: str):
        self._conn = native_conn
        self._db_type = db_type

    def cursor(self):
        if self._db_type == "mysql":
            native = self._conn.cursor(dictionary=True)
        elif self._db_type == "postgresql":
            import psycopg2.extras
            native = self._conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        else:
            native = self._conn.cursor()
        return CompatCursor(native, self._db_type)

    def commit(self):
        self._conn.commit()

    def rollback(self):
        self._conn.rollback()

    def close(self):
        self._conn.close()

    def execute(self, sql: str, params=None):
        """Convenience pass-through for conn.execute() (SQLite pattern)."""
        cur = self.cursor()
        cur.execute(sql, params)
        return cur

    def __enter__(self):
        return self

    def __exit__(self, exc_type, *_):
        if exc_type:
            self.rollback()
        else:
            self.commit()
        self.close()


# ---------------------------------------------------------------------------
# Factory
# ---------------------------------------------------------------------------

def get_connection() -> CompatConnection:
    """
    Return a CompatConnection for the configured DB_TYPE.
    The caller can use it exactly like a sqlite3 connection.
    """
    _refresh_config()
    db_type = DB_TYPE

    if db_type == "sqlite":
        path = SQLITE_PATH
        os.makedirs(os.path.dirname(os.path.abspath(path)), exist_ok=True)
        conn = sqlite3.connect(path, timeout=30.0)
        conn.row_factory = sqlite3.Row
        # Apply SQLite PRAGMAs
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")
        conn.execute("PRAGMA busy_timeout=30000;")
        conn.execute("PRAGMA cache_size=-64000;")
        return CompatConnection(conn, "sqlite")

    elif db_type == "mysql":
        try:
            import mysql.connector
        except ImportError:
            raise ImportError(
                "pymysql / mysql-connector-python is required for MySQL support. "
                "Run: pip install pymysql"
            )
        port = _get_port()
        conn = mysql.connector.connect(
            host=DB_HOST,
            port=port,
            database=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            connection_timeout=30,
            autocommit=False,
        )
        return CompatConnection(conn, "mysql")

    elif db_type == "postgresql":
        try:
            import psycopg2
        except ImportError:
            raise ImportError(
                "psycopg2-binary is required for PostgreSQL support. "
                "Run: pip install psycopg2-binary"
            )
        port = _get_port()
        conn = psycopg2.connect(
            host=DB_HOST,
            port=port,
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            connect_timeout=30,
        )
        conn.autocommit = False
        return CompatConnection(conn, "postgresql")

    else:
        raise ValueError(f"Unsupported DB_TYPE: {db_type!r}. Use sqlite, mysql, or postgresql.")


# ---------------------------------------------------------------------------
# Auto-detect
# ---------------------------------------------------------------------------

def detect_available_databases() -> dict:
    """
    Probe localhost for SQLite, MySQL, and PostgreSQL.
    Returns a dict with keys 'sqlite', 'mysql', 'postgresql'.
    Each value: { available: bool, host: str, port: int, has_data: bool (sqlite only) }
    """
    result = {}

    # SQLite — always available
    sqlite_path = SQLITE_PATH
    result["sqlite"] = {
        "available": True,
        "path": sqlite_path,
        "has_data": os.path.isfile(sqlite_path),
    }

    # MySQL
    mysql_port = 3306
    try:
        import mysql.connector
        conn = mysql.connector.connect(
            host=DB_HOST,
            port=mysql_port,
            user=DB_USER,
            password=DB_PASSWORD,
            connection_timeout=3,
        )
        conn.close()
        mysql_avail = True
    except Exception:
        mysql_avail = False
    result["mysql"] = {
        "available": mysql_avail,
        "host": DB_HOST,
        "port": mysql_port,
    }

    # PostgreSQL
    pg_port = 5432
    try:
        import psycopg2
        conn = psycopg2.connect(
            host=DB_HOST,
            port=pg_port,
            user=DB_USER,
            password=DB_PASSWORD,
            connect_timeout=3,
        )
        conn.close()
        pg_avail = True
    except Exception:
        pg_avail = False
    result["postgresql"] = {
        "available": pg_avail,
        "host": DB_HOST,
        "port": pg_port,
    }

    return result
