from typing import Dict, List, Optional, Union
import math
import logging
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from mysql.connector import Error
from pathlib import Path
from src.config import Config
from flask import current_app
from typing import Dict, List, Union, Optional


def create_database():
    sql_str_path = Path(current_app.root_path).joinpath("database", "evdb.sql")

    if not sql_str_path.exists():
        return {'error': True, 'message': f"El archivo no existe ({sql_str_path})"}

    try:
        with open(sql_str_path, 'r', encoding='utf-8') as sql_script:
            with mysql.connector.connect(
                host=Config.DB_HOST,
                user=Config.DB_USER,
                password=Config.DB_PASS,
                autocommit=True
            ) as conn:
                cursor = conn.cursor()
                for statement in sql_script.read().split(";"):
                    statement = statement.strip()
                    if statement:
                        cursor.execute(statement)

        return {'error': False, 'message': 'Database created successfully!'}
    except Error as e:
        logging.error(f"MySQL Error: {e.msg}")
        return {
            'error': True,
            'message': e.msg,
            'error_code': e.errno,
            'sqlstate': e.sqlstate
        }
    except Exception as e:
        logging.error(f"Error inesperado: {e}")
        return {'error': True, 'message': f"Error inesperado: {str(e)}"}


# Configurar logging
logging.basicConfig(
    level=logging.INFO,
    format='[%(levelname)s] %(asctime)s - %(message)s'
)


class DatabaseResponse:
    """
    Clase que estandariza las respuestas de la base de datos
    """

    def __init__(self):
        self.error: bool = False
        self.data: Union[Dict, List] = []
        self.message: str = ""
        self.total_record: int = 0
        self.total_pages: int = 0
        self.sql_error_message: str = ""
        self.sql_error_code: int = 0
        self.lastrowid: int = 0

    def todict(self):
        return self.__dict__


class Database:
    """
    Librería profesional de MySQL con Context Manager, logging y paginación
    """

    def __init__(self, host=Config.DB_HOST, user=Config.DB_USER, password=Config.DB_PASS, database=Config.DB_NAME, autocommit=True):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.autocommit = autocommit
        self.conn: Optional[mysql.connector.connection.MySQLConnection] = None
        self.cursor: Optional[MySQLCursor] = None
        self.connect()

    # === Context manager ===
    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        self.close()

    # === Conexión ===
    def connect(self):
        try:
            self.conn = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                autocommit=self.autocommit
            )
            self.cursor = self.conn.cursor(dictionary=True)
            logging.info("Conexión a MySQL establecida")
        except Error as e:
            logging.error(f"Error al conectar a la base de datos: {e}")
            raise

    def build_query_select(self, table, **kvargs):

        fields = kvargs.pop('fields', '*')
        limit = int(kvargs.pop('limit', 50))
        skip = kvargs.pop('skip', 0)
        orderby = kvargs.pop('orderby', None)
        order = kvargs.pop('order', 'DESC')

        where_clauses = []
        params = []

        for k, v in kvargs.items():
            if isinstance(v, int):
                where_clauses.append(f"{k}=%s")
                params.append(v)
            else:
                if '*' in str(v):
                    v = str(v).replace('*', '%')
                    where_clauses.append(f"{k} LIKE %s")
                    params.append(v)
                else:
                    where_clauses.append(f"{k}=%s")
                    params.append(v)

        where_sql = f"WHERE {' AND '.join(where_clauses)}" if where_clauses else ""

        order_sql = f"ORDER BY {orderby} {order}" if orderby else ""
        limit_sql = f"LIMIT %s OFFSET %s"
        params.extend([limit, skip])

        query = f"SELECT {fields} FROM {table} {where_sql} {order_sql} {limit_sql}"
        return query, params

    # === Ejecutar query ===
    def execute(self, query: str, params: Optional[tuple] = None, commit: bool = False) -> DatabaseResponse:
        resp = DatabaseResponse()
        try:
            logging.info(f"Ejecutando query: {query} | Params: {params}")
            self.cursor.execute(query, params or ())
            if commit or self.autocommit:
                self.conn.commit()
                resp.lastrowid = self.cursor.lastrowid
            if self.cursor.description:
                resp.data = self.cursor.fetchall()
                resp.total_record = self.cursor.rowcount
        except Error as e:
            resp.error = True
            resp.message = str(e)
            resp.sql_error_code = e.errno if hasattr(e, "errno") else 0
            resp.sql_error_message = str(e)
            logging.error(
                f"SQL Error {resp.sql_error_code}: {resp.sql_error_message}")
        return resp

    # === Fetch ===
    def fetchone(self, query: str, params: Optional[tuple] = None) -> DatabaseResponse:
        resp = DatabaseResponse()
        try:
            logging.info(f"fetchone | Query: {query} | Params: {params}")
            self.cursor.execute(query, params or ())
            resp.data = self.cursor.fetchone()
            resp.total_record = 1 if resp.data else 0
        except Error as e:
            resp.error = True
            resp.message = str(e)
        return resp

    def fetchall(self, query: str, params: Optional[tuple] = None, page: int = 1, per_page: int = 0) -> DatabaseResponse:
        """
        per_page > 0 activa paginación
        """
        resp = DatabaseResponse()
        try:
            final_query = query
            if per_page > 0:
                offset = (page - 1) * per_page
                final_query += f" LIMIT {per_page} OFFSET {offset}"

            logging.info(f"fetchall | Query: {final_query} | Params: {params}")
            self.cursor.execute(final_query, params or ())
            resp.data = self.cursor.fetchall()
            resp.total_record = self.cursor.rowcount

            if per_page > 0:
                # Contar total de registros
                count_query = f"SELECT COUNT(*) as total FROM ({query}) AS subquery"
                self.cursor.execute(count_query, params or ())
                total = self.cursor.fetchone().get("total", 0)
                resp.total_pages = math.ceil(total / per_page)
        except Error as e:
            resp.error = True
            resp.message = str(e)
        return resp

    # === CRUD ===
    def save(self, table: str, data: Dict) -> DatabaseResponse:
        resp = DatabaseResponse()
        try:
            cols = ", ".join(data.keys())
            placeholders = ", ".join(["%s"] * len(data))
            values = tuple(data.values())
            query = f"INSERT INTO {table} ({cols}) VALUES ({placeholders})"
            logging.info(f"save | {query} | Values: {values}")
            self.cursor.execute(query, values)
            self.conn.commit()
            resp.lastrowid = self.cursor.lastrowid
            resp.message = "Registro insertado correctamente"
        except Error as e:
            resp.error = True
            resp.message = str(e)
            logging.error(resp.message)
        return resp

    def get_by_id(self, table: str, id: int, **kvargs) -> DatabaseResponse:
        resp = DatabaseResponse()
        try:
            fields = kvargs.get("fields", "*")
            query = f"SELECT {fields} FROM {table} WHERE id=%s"
            return self.fetchone(query=query, params=(id,))
        except Error as e:
            resp.error = True
            resp.message = str(e)
        return resp

    def search(self, table: str, **kvargs) -> DatabaseResponse:
        resp = DatabaseResponse()
        try:
            fields = kvargs.pop('fields', '*')
            limit = int(kvargs.pop('limit', 50))
            skip = kvargs.pop('skip', 0)
            orderby = kvargs.pop('orderby', None)
            order = kvargs.pop('order', 'DESC')

            where_clauses = []
            params = []

            for k, v in kvargs.items():
                if isinstance(v, int):
                    where_clauses.append(f"{k}=%s")
                    params.append(v)
                else:
                    if '*' in str(v):
                        v = str(v).replace('*', '%')
                        where_clauses.append(f"{k} LIKE %s")
                        params.append(v)
                    else:
                        where_clauses.append(f"{k}=%s")
                        params.append(v)

            where_sql = f"WHERE {' OR '.join(where_clauses)}" if where_clauses else ""

            order_sql = f"ORDER BY {orderby} {order}" if orderby else ""
            limit_sql = f"LIMIT %s OFFSET %s"
            params.extend([limit, skip])

            query = f"SELECT {fields} FROM {table} {where_sql} {order_sql} {limit_sql}"
            return self.fetchall(query, tuple(params))
        except Error as e:
            resp.error = True
            resp.message = str(e)
        return resp

    def update(self, table: str, data: Dict, conditions: Dict) -> DatabaseResponse:
        resp = DatabaseResponse()
        try:
            set_clause = ", ".join([f"{k}=%s" for k in data.keys()])
            where_clause = " AND ".join([f"{k}=%s" for k in conditions.keys()])
            values = tuple(data.values()) + tuple(conditions.values())
            query = f"UPDATE {table} SET {set_clause} WHERE {where_clause}"
            logging.info(f"update | {query} | Values: {values}")
            self.cursor.execute(query, values)
            self.conn.commit()
            resp.total_record = self.cursor.rowcount
            resp.message = "Registro(s) actualizado(s)"
        except Error as e:
            resp.error = True
            resp.message = str(e)
            logging.error(resp.message)
        return resp

    def delete(self, table: str, conditions: Dict) -> DatabaseResponse:
        resp = DatabaseResponse()
        try:
            where_clause = " AND ".join([f"{k}=%s" for k in conditions.keys()])
            values = tuple(conditions.values())
            query = f"DELETE FROM {table} WHERE {where_clause}"
            logging.info(f"delete | {query} | Values: {values}")
            self.cursor.execute(query, values)
            self.conn.commit()
            resp.total_record = self.cursor.rowcount
            resp.message = "Registro(s) eliminado(s)"
        except Error as e:
            resp.error = True
            resp.message = str(e)
            logging.error(resp.message)
        return resp

    # === Cerrar conexión ===
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
            logging.info("Conexión a MySQL cerrada")