from __future__ import annotations
import logging
from contextlib import contextmanager
from pathlib import Path
from typing import Any, Dict, Generator, List, Optional, Tuple
from core.config import logger as app_logger
from core.db_mysql import SchemaManager

logger = logging.getLogger(__name__)

class BaseDBAdapter:
    """
    [AI_DOC]
    Tum veritabani suruculerini (pymysql, mysql-connector-python, mariadb vb.)
    tek bir standart arayuz arkasina gizleyen Ust Sinif (Base Class).
    Golder'daki ORM yapisi (BaseManager) dogrudan bu sinifin metodlarini kullanir.
    Icerisinde otomatik transaction yonetimi ve SQL QMark (?) / PyFormat (%s) ceviricileri vardir.
    [/AI_DOC]
    """
    def __init__(self, db_config: Dict[str, Any], db_name: str = "Goldr") -> None:
        raw_config: Dict[str, Any] = dict(db_config)
        self._runtime_options: Dict[str, Any] = {
            k: raw_config.pop(k)
            for k in list(raw_config.keys())
            if str(k).startswith("_")
        }
        self.db_config: Dict[str, Any] = raw_config
        self.db_config["database"] = db_name
        self._driver_name: Optional[str] = None
        self._driver_module: Any = None
        self._pool: Any = None 
        self._connected: bool = False
        self._table_columns_cache: Dict[str, set] = {}
        self._manual_tx_conn: Any = None
        self._manual_tx_cursor: Any = None

    def _import_mysql_connector(self) -> Optional[Any]:
        try:
            import mysql.connector  
            import mysql.connector.pooling  
            return mysql.connector
        except Exception:
            return None

    def _import_pymysql(self) -> Optional[Any]:
        try:
            import pymysql  
            return pymysql
        except Exception:
            return None

    def _import_mariadb_driver(self) -> Optional[Any]:
        try:
            import mariadb  
            if not hasattr(mariadb, "connect"):
                return None
            return mariadb
        except Exception:
            return None

    def _select_driver(self, preferred: Tuple[str, ...]) -> None:
        """Listede verilen suruculeri sirayla dener ve ilk buldugunu yukler."""
        for name in preferred:
            if name == "mysql-connector-python":
                mod = self._import_mysql_connector()
                if mod is not None:
                    self._driver_name = name
                    self._driver_module = mod
                    return
            elif name == "pymysql":
                mod = self._import_pymysql()
                if mod is not None:
                    self._driver_name = name
                    self._driver_module = mod
                    return
            elif name == "mariadb":
                mod = self._import_mariadb_driver()
                if mod is not None:
                    self._driver_name = name
                    self._driver_module = mod
                    return
            else:
                raise RuntimeError(f"Bilinmeyen veritabani surucusu tercihi: {name}")

        raise ImportError(
            "Hicbir MySQL/MariaDB surucusu bulunamadi. Lutfen birini kurun:\n"
            "  pip install pymysql\n"
            "  pip install mysql-connector-python\n"
        )

    def _driver_connect_kwargs(self, include_database: bool = True) -> Dict[str, Any]:
        """Secili surucuye uygun connection argumanlarini hazirlar."""
        cfg = dict(self.db_config)
        if not include_database:
            cfg.pop("database", None)

        internal_keys = [k for k in cfg.keys() if str(k).startswith("_")]
        for key in internal_keys:
            cfg.pop(key, None)

        pool_keys = ("pool_name", "pool_size", "pool_reset_session")
        if self._driver_name != "mysql-connector-python":
            for k in pool_keys:
                cfg.pop(k, None)

        if self._driver_name == "pymysql":
            if "allow_local_infile" in cfg and "local_infile" not in cfg:
                cfg["local_infile"] = bool(cfg.pop("allow_local_infile"))
            cfg.pop("auth_plugin", None)
            ssl_disabled = bool(cfg.pop("ssl_disabled", True))
            cfg.pop("ssl_verify_cert", None)
            cfg.pop("ssl_verify_identity", None)
            if ssl_disabled:
                cfg.pop("ssl", None)

        if self._driver_name == "mariadb":
            for key in pool_keys + ("auth_plugin", "allow_local_infile", "ssl_disabled", "ssl_verify_cert", "ssl_verify_identity", "ssl"):
                cfg.pop(key, None)

        return cfg

    def _translate_pyformat_to_qmark(self, sql: str) -> str:
        """
        [AI_DOC]
        Python tarafinda yazilan SQL sorgularindaki '%s' formatini,
        MariaDB Connector gereksinimi olan '?' (qmark) formatina donusturur.
        Tirnak ici ve yorum satirlarini (String Literals) guvenle atlar.
        [/AI_DOC]
        """
        out: List[str] = []
        i = 0
        in_single = False
        in_double = False
        in_backtick = False
        in_line_comment = False
        in_block_comment = False

        while i < len(sql):
            ch = sql[i]
            nxt = sql[i + 1] if i + 1 < len(sql) else ""

            if in_line_comment:
                out.append(ch)
                if ch == "\n":
                    in_line_comment = False
                i += 1
                continue

            if in_block_comment:
                out.append(ch)
                if ch == "*" and nxt == "/":
                    out.append(nxt)
                    i += 2
                    in_block_comment = False
                    continue
                i += 1
                continue

            if in_single:
                out.append(ch)
                if ch == "\\" and nxt:
                    out.append(nxt)
                    i += 2
                    continue
                if ch == "'":
                    in_single = False
                i += 1
                continue

            if in_double:
                out.append(ch)
                if ch == "\\" and nxt:
                    out.append(nxt)
                    i += 2
                    continue
                if ch == '"':
                    in_double = False
                i += 1
                continue

            if in_backtick:
                out.append(ch)
                if ch == "`":
                    in_backtick = False
                i += 1
                continue

            if ch == "'":
                in_single = True
                out.append(ch)
                i += 1
                continue

            if ch == '"':
                in_double = True
                out.append(ch)
                i += 1
                continue

            if ch == "`":
                in_backtick = True
                out.append(ch)
                i += 1
                continue

            if ch == "-" and nxt == "-":
                nxt2 = sql[i + 2] if i + 2 < len(sql) else ""
                if nxt2 in ("", " ", "\t", "\r", "\n"):
                    in_line_comment = True
                    out.append(ch)
                    out.append(nxt)
                    i += 2
                    continue

            if ch == "#":
                in_line_comment = True
                out.append(ch)
                i += 1
                continue

            if ch == "/" and nxt == "*":
                in_block_comment = True
                out.append(ch)
                out.append(nxt)
                i += 2
                continue

            if ch == "%" and nxt == "s":
                out.append("?")
                i += 2
                continue

            out.append(ch)
            i += 1

        return "".join(out)

    def _adapt_query(self, query: str) -> str:
        """Surucuye uygun SQL formatina cevirir."""
        if self._driver_name == "mariadb":
            return self._translate_pyformat_to_qmark(query)
        return query

    def _ensure_database_exists(self) -> None:
        """Veritabani yoksa olusturur."""
        db_name = str(self.db_config.get("database") or "")
        if not db_name:
            raise ValueError("Config icinde database adi eksik!")
            
        sql = f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        
        if self._driver_name == "mysql-connector-python":
            mysql = self._driver_module
            tmp_cfg = self._driver_connect_kwargs(include_database=False)
            for k in ("pool_name", "pool_size", "pool_reset_session"):
                tmp_cfg.pop(k, None)
                
            cnx = mysql.connect(**tmp_cfg)
            try:
                cur = cnx.cursor()
                cur.execute(sql)
                cur.close()
                cnx.commit()
            finally:
                cnx.close()
            return

        conn = self._driver_module.connect(**self._driver_connect_kwargs(include_database=False))
        try:
            cur = conn.cursor()
            cur.execute(sql)
            cur.close()
            if hasattr(conn, "commit"):
                conn.commit()
        finally:
            if hasattr(conn, "close"):
                conn.close()

    def _initialize_backend(self) -> None:
        """Connection Pool veya Native Driver baslatilir."""
        self._ensure_database_exists()
        if self._driver_name == "mysql-connector-python":
            mysql = self._driver_module
            self._pool = mysql.pooling.MySQLConnectionPool(**self.db_config)
        else:
            self._pool = None

    def connect(self) -> None:
        """Alt siniflar bu metodu ezmelidir."""
        if self._connected:
            return
        raise NotImplementedError("BaseDBAdapter.connect must be implemented")

    def close(self) -> None:
        """Tum baglantilari ve havuzu kapatir."""
        self._connected = False
        self._pool = None

    @contextmanager
    def get_connection(self) -> Generator[Any, None, None]:
        if not self._connected:
            self.connect()

        if self._driver_name == "mysql-connector-python":
            conn = self._pool.get_connection()
            try:
                yield conn
            finally:
                try:
                    conn.close()
                except Exception:
                    pass
            return

        conn = self._driver_module.connect(**self._driver_connect_kwargs(include_database=True))
        try:
            yield conn
        finally:
            try:
                conn.close()
            except Exception:
                pass

    def _cursor_for_connection(self, conn: Any, dictionary: bool = True) -> Any:
        if self._driver_name == "mysql-connector-python":
            return conn.cursor(dictionary=dictionary, buffered=True)
        if self._driver_name == "pymysql":
            pymysql = self._driver_module
            if dictionary:
                return conn.cursor(pymysql.cursors.DictCursor)
            return conn.cursor()
        if self._driver_name == "mariadb":
            try:
                return conn.cursor(dictionary=dictionary)  
            except Exception:
                return conn.cursor()
        return conn.cursor()

    @contextmanager
    def get_cursor(self, dictionary: bool = True) -> Generator[Any, None, None]:
        """Tek seferlik sorgular icin Cursor verir (Auto Commit)."""
        with self.get_connection() as conn:
            cur = self._cursor_for_connection(conn, dictionary=dictionary)
            try:
                yield cur
            finally:
                try:
                    cur.close()
                except Exception:
                    pass

    @contextmanager
    def transaction_scope(self) -> Generator[Any, None, None]:
        """
        [AI_DOC]
        Guvenli Transaction (Islem) blogu saglar.
        Python'daki 'with db.transaction_scope() as cursor:' yapisi sayesinde,
        iceride bir hata (Exception) olursa Rollback (geri alma) yapilir.
        Sorunsuz biterse Commit edilir.
        [/AI_DOC]
        """
        conn = None
        cur = None
        try:
            if not self._connected:
                self.connect()

            if self._driver_name == "mysql-connector-python":
                conn = self._pool.get_connection()
                if hasattr(conn, "is_connected") and not conn.is_connected():
                    try:
                        conn.reconnect(attempts=3, delay=1)
                    except Exception as rec_err:
                        raise ConnectionError("Veritabani baglantisi koptu ve yeniden saglanamadi.") from rec_err
                try:
                    conn.start_transaction()
                except Exception:
                    pass
            else:
                conn = self._driver_module.connect(**self._driver_connect_kwargs(include_database=True))
                try:
                    if hasattr(conn, "ping"):
                        conn.ping(reconnect=True)
                except Exception:
                    pass
                try:
                    if hasattr(conn, "begin"):
                        conn.begin()
                except Exception:
                    pass

            cur = self._cursor_for_connection(conn, dictionary=True)
            yield cur
            
            try:
                conn.commit()
            except Exception:
                pass

        except Exception as e:
            try:
                if conn:
                    conn.rollback()
            except Exception:
                pass
            raise e

        finally:
            try:
                if cur:
                    cur.close()
            except Exception:
                pass
            try:
                if conn:
                    conn.close()
            except Exception:
                pass

    def execute(self, query: str, params: Optional[Tuple[Any, ...]] = None) -> int:
        q = self._adapt_query(query)
        with self.transaction_scope() as cur:
            cur.execute(q, params or ())
            return int(getattr(cur, "rowcount", 0) or 0)

    def execute_non_query(self, query: str, params: Optional[Tuple[Any, ...]] = None) -> int:
        return self.execute(query, params=params)

    def fetch_all(self, query: str, params: Optional[Tuple[Any, ...]] = None) -> List[Dict[str, Any]]:
        q = self._adapt_query(query)
        with self.get_cursor(dictionary=True) as cur:
            cur.execute(q, params or ())
            rows = cur.fetchall()
            return rows or []

    def fetch_one(self, query: str, params: Optional[Tuple[Any, ...]] = None) -> Optional[Dict[str, Any]]:
        q = self._adapt_query(query)
        with self.get_cursor(dictionary=True) as cur:
            cur.execute(q, params or ())
            return cur.fetchone()

    def invalidate_table_columns_cache(self, table_name: Optional[str] = None) -> None:
        if table_name:
            self._table_columns_cache.pop(table_name, None)
        else:
            self._table_columns_cache.clear()

    def get_table_columns(self, table_name: str) -> set:
        """Tablonun kolonlarini bulur ve memory cache'e alir."""
        if not table_name:
            return set()
        cached = self._table_columns_cache.get(table_name)
        if cached is not None:
            return cached

        try:
            rows = self.fetch_all(
                """
                SELECT COLUMN_NAME
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s
                """,
                (table_name,),
            )
            cols = {r.get("COLUMN_NAME") for r in (rows or []) if r and r.get("COLUMN_NAME")}
        except Exception as e:
            app_logger.warning(f"Kolon listesi alinamadi ({table_name}): {e}")
            cols = set()

        self._table_columns_cache[table_name] = cols
        return cols

    def has_column(self, table_name: str, column_name: str) -> bool:
        return column_name in self.get_table_columns(table_name)

    def delete_profile(self, profile_id: str) -> int:
        """
        Bir profile ait tum verileri ve profili veritabanindan kalici olarak siler.
        Foreign key hatalarini onlemek icin bagimlilik (priority) sirasina gore siler.
        """
        if not profile_id:
            raise ValueError("profile_id gerekli")

        with self.transaction_scope() as cur:
            cur.execute(
                """
                SELECT DISTINCT c.TABLE_NAME
                FROM INFORMATION_SCHEMA.COLUMNS c
                JOIN INFORMATION_SCHEMA.TABLES t
                  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
                WHERE c.TABLE_SCHEMA = DATABASE()
                  AND c.COLUMN_NAME = 'profile_id'
                  AND t.TABLE_TYPE = 'BASE TABLE'
                """
            )
            rows = cur.fetchall() or []
            tables = [
                r.get("TABLE_NAME")
                for r in rows
                if isinstance(r, dict) and isinstance(r.get("TABLE_NAME"), str)
            ]
            
            tables_set = {t for t in tables if t and t not in {"profiles", "deleted_profiles"}}
            priority = ["financial_transactions", "financial_accounts", "service_jobs", "service_definitions"]
            ordered: List[str] = []
            
            for t in priority:
                if t in tables_set:
                    ordered.append(t)
                    tables_set.remove(t)
                    
            users_present = "users" in tables_set
            if users_present:
                tables_set.remove("users")
                
            ordered.extend(sorted(tables_set))
            
            if users_present:
                ordered.append("users")

            for table_name in ordered:
                if not table_name.replace("_", "").isalnum():
                    raise ValueError(f"Beklenmeyen tablo adi: {table_name}")
                cur.execute(f"DELETE FROM `{table_name}` WHERE profile_id = %s", (profile_id,))
                
            cur.execute("DELETE FROM `profiles` WHERE id = %s", (profile_id,))
            return int(getattr(cur, "rowcount", 0) or 0)


class MariaDBAdapter(BaseDBAdapter):
    """
    [AI_DOC]
    Windows platformlari icin ozel olarak gelistirilmis MariaDB surucusu.
    core.db_embedded modulu uzerinden Portable MariaDB'yi baslatir ve 
    Config dosyasini okuyarak baglanir.
    [/AI_DOC]
    """
    def __init__(self, db_name: str = "Goldr", project_root: Optional[Path] = None) -> None:
        from config.database import get_unified_db_config
        cfg = get_unified_db_config()
        super().__init__(cfg, db_name=db_name)
        
        self._preferred_drivers: Tuple[str, ...] = ("mysql-connector-python", "pymysql", "mariadb")
        self._embedded_db_server: Any = None
        self._embedded_started: bool = False
        self._project_root = project_root or Path(__file__).resolve().parent.parent

    def connect(self) -> None:
        if self._connected:
            return
            
        if not self._embedded_started:
            try:
                from core.db_embedded import EmbeddedDBManager
                # Runtime klasorundeki (main.py/exe yani) mariadb dizini kullanilir.
                self._embedded_db_server = EmbeddedDBManager()
                app_logger.info("[DB] Backend: Windows Gomulu MariaDB Baslatiliyor...")
                self._embedded_db_server.start()
                self._embedded_started = True
            except Exception as e:
                runtime_mariadb_dir = getattr(self._embedded_db_server, "mariadb_dir", self._project_root / "mariadb")
                raise RuntimeError(
                    f"Gomulu MariaDB baslatilamadi. {runtime_mariadb_dir} klasorunu kontrol edin."
                ) from e
                
        self._select_driver(self._preferred_drivers)
        self._initialize_backend()
        self._connected = True
        
        try:
            SchemaManager(self).init_schema()
        except Exception:
            try:
                self.close()
            except Exception:
                pass
            raise

    def close(self) -> None:
        try:
            super().close()
        finally:
            if self._embedded_db_server is not None:
                try:
                    self._embedded_db_server.stop()
                except Exception:
                    pass
            self._embedded_db_server = None
            self._embedded_started = False


class MySQLAdapter(BaseDBAdapter):
    """
    [AI_DOC]
    macOS platformlari (Homebrew MySQL) veya disa acik genel MySQL sunuculari icin kullanilir.
    [/AI_DOC]
    """
    def __init__(self, db_config: Dict[str, Any], db_name: str = "Goldr") -> None:
        super().__init__(db_config, db_name=db_name)
        self._preferred_drivers: Tuple[str, ...] = ("mysql-connector-python", "pymysql")

    def connect(self) -> None:
        if self._connected:
            return
            
        self._select_driver(self._preferred_drivers)
        self._initialize_backend()
        self._connected = True
        
        try:
            cloud_mode = bool(self._runtime_options.get("_cloud_mode", False))
            if cloud_mode and self._is_cloud_schema_ready():
                app_logger.info("[DB] Bulut semasi tespit edildi. Hizli baslatma devrede.")
                return
            SchemaManager(self).init_schema()
        except Exception:
            try:
                self.close()
            except Exception:
                pass
            raise

    def _is_cloud_schema_ready(self) -> bool:
        try:
            row = self.fetch_one(
                """
                SELECT COUNT(*) AS table_count
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME IN ('profiles', 'app_settings', 'users', 'stock_items')
                """
            )
            count = int((row or {}).get("table_count") or 0)
            return count >= 4
        except Exception:
            return False




