import os
import json
import time
import sqlite3
import pymysql
import pymysql.cursors
from datetime import datetime, date
from app.config import Config

class DatabaseManager:
    def __init__(self):
        self.active_driver = "mysql"  # 'mysql' ou 'sqlite'
        self.last_error = None
        self.connected = False
        self.sqlite_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), "duobi_data.sqlite3")

    def _json_serial(self, obj):
        """Serializador de data/datetime para JSON."""
        if isinstance(obj, (datetime, date)):
            return obj.isoformat()
        raise TypeError(f"Type {type(obj)} not serializable")

    def get_mysql_connection(self, create_db_if_not_exists=True):
        """Tenta conectar ao servidor MySQL."""
        try:
            if create_db_if_not_exists:
                # Conecta sem banco para garantir que a base exista
                conn_server = pymysql.connect(
                    host=Config.DB_HOST,
                    port=Config.DB_PORT,
                    user=Config.DB_USERNAME,
                    password=Config.DB_PASSWORD,
                    charset="utf8mb4",
                    connect_timeout=3
                )
                with conn_server.cursor() as cur:
                    cur.execute(f"CREATE DATABASE IF NOT EXISTS `{Config.DB_DATABASE}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
                conn_server.close()

            # Conecta ao banco de dados específico
            conn = pymysql.connect(
                host=Config.DB_HOST,
                port=Config.DB_PORT,
                user=Config.DB_USERNAME,
                password=Config.DB_PASSWORD,
                database=Config.DB_DATABASE,
                charset="utf8mb4",
                cursorclass=pymysql.cursors.DictCursor,
                connect_timeout=3,
                autocommit=True
            )
            return conn
        except Exception as e:
            self.last_error = str(e)
            return None

    def get_sqlite_connection(self):
        """Conexão fallback com SQLite local."""
        conn = sqlite3.connect(self.sqlite_path, check_same_thread=False)
        conn.row_factory = sqlite3.Row
        return conn

    def init_database(self):
        """Inicializa tabelas e schema no banco ativo."""
        start_time = time.time()
        mysql_conn = self.get_mysql_connection()

        if mysql_conn:
            self.active_driver = "mysql"
            self.connected = True
            self.last_error = None
            print(f"[DB] Conectado com sucesso ao MySQL 8.0 em {Config.DB_HOST}:{Config.DB_PORT}/{Config.DB_DATABASE}")
            self._create_tables_mysql(mysql_conn)
            mysql_conn.close()
        else:
            self.active_driver = "sqlite"
            self.connected = True
            print(f"[DB WARN] MySQL indisponível ({self.last_error}). Ativando modo local SQLite em {self.sqlite_path}")
            sqlite_conn = self.get_sqlite_connection()
            self._create_tables_sqlite(sqlite_conn)
            sqlite_conn.close()

    def _create_tables_mysql(self, conn):
        """Cria as tabelas e sincroniza colunas no MySQL."""
        with conn.cursor() as cur:
            # Tabela de Atendimentos (Schema Oficial)
            cur.execute("""
            CREATE TABLE IF NOT EXISTS atendimentos (
                id INT AUTO_INCREMENT PRIMARY KEY,
                id_duotalk VARCHAR(100),
                id_conversa VARCHAR(100),
                nome_chat VARCHAR(255),
                telefone VARCHAR(50),
                email VARCHAR(255),
                canal VARCHAR(100),
                origem VARCHAR(100),
                intencao VARCHAR(150),
                operador_id VARCHAR(100),
                operador_nome VARCHAR(255),
                operador_email VARCHAR(255),
                key_entity VARCHAR(150),
                tipo_integracao VARCHAR(100),
                qualificacao_lead VARCHAR(100),
                motivo_fechamento VARCHAR(255),
                operador_fechamento VARCHAR(255),
                data_fechamento DATETIME,
                payload_bruto JSON,
                criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """)

            # Sincroniza colunas se a tabela existia anteriormente com schema reduzido
            cur.execute("DESCRIBE atendimentos")
            existing_cols = {row["Field"] for row in cur.fetchall()}
            
            required_cols = [
                ("id_conversa", "VARCHAR(100)"),
                ("telefone", "VARCHAR(50)"),
                ("email", "VARCHAR(255)"),
                ("canal", "VARCHAR(100)"),
                ("intencao", "VARCHAR(150)"),
                ("operador_nome", "VARCHAR(255)"),
                ("operador_email", "VARCHAR(255)"),
                ("key_entity", "VARCHAR(150)"),
                ("tipo_integracao", "VARCHAR(100)"),
                ("qualificacao_lead", "VARCHAR(100)"),
                ("motivo_fechamento", "VARCHAR(255)"),
                ("operador_fechamento", "VARCHAR(255)"),
                ("data_fechamento", "DATETIME")
            ]

            for col_name, col_type in required_cols:
                if col_name not in existing_cols:
                    try:
                        cur.execute(f"ALTER TABLE atendimentos ADD COLUMN {col_name} {col_type}")
                    except Exception as e:
                        print(f"[MIGRATION] Falha ao adicionar coluna {col_name}: {e}")

            # Tabela de Usuários
            cur.execute("""
            CREATE TABLE IF NOT EXISTS usuarios (
                id INT AUTO_INCREMENT PRIMARY KEY,
                nome VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                senha_hash VARCHAR(255) NOT NULL,
                cargo VARCHAR(100) DEFAULT 'Analista de BI',
                role VARCHAR(50) DEFAULT 'admin',
                avatar VARCHAR(255) DEFAULT '',
                ativo BOOLEAN DEFAULT TRUE,
                criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """)

            # Tabela de Logs do Sistema
            cur.execute("""
            CREATE TABLE IF NOT EXISTS system_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                nivel VARCHAR(20) DEFAULT 'INFO',
                modulo VARCHAR(100),
                mensagem TEXT,
                detalhes JSON,
                criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """)

    def _create_tables_sqlite(self, conn):
        """Cria as tabelas no SQLite local."""
        cur = conn.cursor()
        cur.execute("""
        CREATE TABLE IF NOT EXISTS atendimentos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            id_duotalk TEXT,
            id_conversa TEXT,
            nome_chat TEXT,
            telefone TEXT,
            email TEXT,
            canal TEXT,
            origem TEXT,
            intencao TEXT,
            operador_id TEXT,
            operador_nome TEXT,
            operador_email TEXT,
            key_entity TEXT,
            tipo_integracao TEXT,
            qualificacao_lead TEXT,
            motivo_fechamento TEXT,
            operador_fechamento TEXT,
            data_fechamento TEXT,
            payload_bruto TEXT,
            criado_em TEXT DEFAULT (datetime('now', 'localtime'))
        );
        """)

        cur.execute("""
        CREATE TABLE IF NOT EXISTS usuarios (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nome TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            senha_hash TEXT NOT NULL,
            cargo TEXT DEFAULT 'Analista de BI',
            role TEXT DEFAULT 'admin',
            avatar TEXT DEFAULT '',
            ativo INTEGER DEFAULT 1,
            criado_em TEXT DEFAULT (datetime('now', 'localtime'))
        );
        """)

        cur.execute("""
        CREATE TABLE IF NOT EXISTS system_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nivel TEXT DEFAULT 'INFO',
            modulo TEXT,
            mensagem TEXT,
            detalhes TEXT,
            criado_em TEXT DEFAULT (datetime('now', 'localtime'))
        );
        """)
        conn.commit()

    def execute_query(self, sql, params=None, fetch="all"):
        """Executa consultas SELECT retornando listas de dicionários."""
        params = params or ()
        if self.active_driver == "mysql":
            conn = self.get_mysql_connection(create_db_if_not_exists=False)
            if not conn:
                # Falha temporária do MySQL, tenta reconectar ou cair para SQLite
                self.init_database()
                if self.active_driver == "sqlite":
                    return self._execute_query_sqlite(sql, params, fetch)
                conn = self.get_mysql_connection(create_db_if_not_exists=False)
                if not conn:
                    raise Exception(f"Erro de conexão com MySQL: {self.last_error}")

            try:
                with conn.cursor() as cur:
                    if params:
                        cur.execute(sql.replace("?", "%s"), params)
                    else:
                        cur.execute(sql)
                    if fetch == "one":
                        row = cur.fetchone()
                        return self._format_row(row) if row else None
                    rows = cur.fetchall()
                    return [self._format_row(r) for r in rows]
            finally:
                conn.close()
        else:
            return self._execute_query_sqlite(sql, params, fetch)

    def _execute_query_sqlite(self, sql, params, fetch):
        """Execução no SQLite com conversão de tipos."""
        # Adequa sintaxe caso necessário
        sql_sqlite = sql.replace("%s", "?")
        # Substitui funções MySQL específicas se houver
        sql_sqlite = sql_sqlite.replace("NOW()", "datetime('now', 'localtime')")
        sql_sqlite = sql_sqlite.replace("CURRENT_TIMESTAMP", "datetime('now', 'localtime')")
        
        conn = self.get_sqlite_connection()
        try:
            cur = conn.cursor()
            if params:
                cur.execute(sql_sqlite, params)
            else:
                cur.execute(sql_sqlite)
            if fetch == "one":
                row = cur.fetchone()
                return self._format_row(dict(row)) if row else None
            rows = cur.fetchall()
            return [self._format_row(dict(r)) for r in rows]
        finally:
            conn.close()

    def execute_insert(self, sql, params=None):
        """Executa INSERT e retorna o ID inserido."""
        if self.active_driver == "mysql":
            conn = self.get_mysql_connection(create_db_if_not_exists=False)
            if not conn:
                self.init_database()
                if self.active_driver == "sqlite":
                    return self._execute_insert_sqlite(sql, params)
                conn = self.get_mysql_connection(create_db_if_not_exists=False)

            try:
                with conn.cursor() as cur:
                    if params:
                        cur.execute(sql.replace("?", "%s"), params)
                    else:
                        cur.execute(sql)
                    return cur.lastrowid
            finally:
                conn.close()
        else:
            return self._execute_insert_sqlite(sql, params)

    def _execute_insert_sqlite(self, sql, params):
        sql_sqlite = sql.replace("%s", "?")
        conn = self.get_sqlite_connection()
        try:
            cur = conn.cursor()
            if params:
                cur.execute(sql_sqlite, params)
            else:
                cur.execute(sql_sqlite)
            conn.commit()
            return cur.lastrowid
        finally:
            conn.close()

    def execute_many(self, sql, seq_of_params):
        """Executa múltiplas inserções/atualizações em uma única conexão."""
        if not seq_of_params:
            return 0
        if self.active_driver == "mysql":
            conn = self.get_mysql_connection(create_db_if_not_exists=False)
            if not conn:
                self.init_database()
                if self.active_driver == "sqlite":
                    return self._execute_many_sqlite(sql, seq_of_params)
                conn = self.get_mysql_connection(create_db_if_not_exists=False)

            try:
                with conn.cursor() as cur:
                    count = cur.executemany(sql.replace("?", "%s"), seq_of_params)
                    return count
            finally:
                conn.close()
        else:
            return self._execute_many_sqlite(sql, seq_of_params)

    def _execute_many_sqlite(self, sql, seq_of_params):
        sql_sqlite = sql.replace("%s", "?")
        conn = self.get_sqlite_connection()
        try:
            cur = conn.cursor()
            cur.executemany(sql_sqlite, seq_of_params)
            conn.commit()
            return cur.rowcount
        finally:
            conn.close()

    def execute_non_query(self, sql, params=None):
        """Executa UPDATE/DELETE/DDL."""
        if self.active_driver == "mysql":
            conn = self.get_mysql_connection(create_db_if_not_exists=False)
            if not conn:
                self.init_database()
                if self.active_driver == "sqlite":
                    return self._execute_non_query_sqlite(sql, params)
                conn = self.get_mysql_connection(create_db_if_not_exists=False)

            try:
                with conn.cursor() as cur:
                    if params:
                        return cur.execute(sql.replace("?", "%s"), params)
                    else:
                        return cur.execute(sql)
            finally:
                conn.close()
        else:
            return self._execute_non_query_sqlite(sql, params)

    def _execute_non_query_sqlite(self, sql, params):
        sql_sqlite = sql.replace("%s", "?")
        conn = self.get_sqlite_connection()
        try:
            cur = conn.cursor()
            res = cur.execute(sql_sqlite, params)
            conn.commit()
            return res.rowcount
        finally:
            conn.close()

    def _format_row(self, row):
        """Formata campos JSON e datas do registro de forma segura."""
        if not row:
            return row
        formatted = {}
        for k, v in row.items():
            try:
                if isinstance(v, (datetime, date)):
                    formatted[k] = v.strftime("%Y-%m-%d %H:%M:%S")
                elif k == "payload_bruto":
                    if isinstance(v, str):
                        try:
                            formatted[k] = json.loads(v)
                        except Exception:
                            formatted[k] = v
                    else:
                        formatted[k] = v
                else:
                    formatted[k] = v
            except Exception as format_err:
                print(f"[DB FORMAT ROW WARN] Erro ao formatar coluna '{k}' ({v}): {format_err}")
                formatted[k] = str(v) if v is not None else None
        return formatted

    def log_event(self, nivel, modulo, mensagem, detalhes=None):
        """Registra logs de sistema no banco."""
        try:
            detalhes_str = json.dumps(detalhes or {}, default=self._json_serial)
            if self.active_driver == "mysql":
                self.execute_non_query(
                    "INSERT INTO system_logs (nivel, modulo, mensagem, detalhes) VALUES (%s, %s, %s, %s)",
                    (nivel, modulo, mensagem, detalhes_str)
                )
            else:
                self.execute_non_query(
                    "INSERT INTO system_logs (nivel, modulo, mensagem, detalhes) VALUES (?, ?, ?, ?)",
                    (nivel, modulo, mensagem, detalhes_str)
                )
        except Exception as e:
            print(f"[LOG ERROR] {e}")

    def sync_payload_fields(self):
        """Sincroniza e preenche colunas nulas da tabela atendimentos com dados extraídos do payload_bruto."""
        try:
            rows = self.execute_query("""
                SELECT id, nome_chat, canal, origem, intencao, operador_nome, 
                       operador_email, operador_id, telefone, email, key_entity, 
                       tipo_integracao, qualificacao_lead, payload_bruto 
                FROM atendimentos 
                WHERE payload_bruto IS NOT NULL 
                  AND (nome_chat IS NULL OR canal IS NULL OR origem IS NULL OR operador_nome IS NULL OR intencao IS NULL)
                LIMIT 500
            """)
            if not rows:
                return

            updated_count = 0
            for r in rows:
                p = r.get("payload_bruto")
                if not isinstance(p, dict):
                    continue

                updates = {}
                # nome_chat (Chat Duotalk do Estado/Bot, ex: 'Pós-vendas MT', 'Pós-vendas PA')
                bot_chat = p.get("nomeChatbot") or p.get("nome_chat")
                if not r.get("nome_chat") and bot_chat:
                    updates["nome_chat"] = str(bot_chat)

                # canal (ex: 'WhatsApp 360', 'WhatsApp', 'Webchat')
                ch = p.get("canal") or (p.get("interaction", {}).get("channel") if isinstance(p.get("interaction"), dict) else None)
                if not r.get("canal") and ch:
                    updates["canal"] = str(ch)

                # origem (ex: 'Inbound', 'Outbound', 'Campanha')
                orig = p.get("origem") or (p.get("interaction", {}).get("origin") if isinstance(p.get("interaction"), dict) else None)
                if not r.get("origem") and orig:
                    updates["origem"] = str(orig)

                # intencao (ex: 'Rec. Toyota PA', 'Rec. Nissan & Chery')
                intent = p.get("intencao") or (p.get("interaction", {}).get("intention") if isinstance(p.get("interaction"), dict) else None)
                if not r.get("intencao") and intent:
                    updates["intencao"] = str(intent)

                # operador_nome
                op_name = p.get("operador") or (p.get("operator", {}).get("name") if isinstance(p.get("operator"), dict) else None)
                if not r.get("operador_nome") and op_name:
                    updates["operador_nome"] = str(op_name)

                # operador_email
                op_email = p.get("operadorEmail") or (p.get("operator", {}).get("email") if isinstance(p.get("operator"), dict) else None)
                if not r.get("operador_email") and op_email:
                    updates["operador_email"] = str(op_email)

                # operador_id
                op_id = p.get("operadorId") or (p.get("operator", {}).get("id") if isinstance(p.get("operator"), dict) else None)
                if not r.get("operador_id") and op_id:
                    updates["operador_id"] = str(op_id)

                # telefone
                phone = p.get("telefone") or (p.get("customer", {}).get("phone") if isinstance(p.get("customer"), dict) else None)
                if not r.get("telefone") and phone:
                    updates["telefone"] = str(phone)

                # email
                mail = p.get("email") or (p.get("customer", {}).get("email") if isinstance(p.get("customer"), dict) else None)
                if not r.get("email") and mail:
                    updates["email"] = str(mail)

                # key_entity
                k_entity = p.get("keyEntity") or p.get("locationTitle") or p.get("Marca") or (p.get("interaction", {}).get("target_product") if isinstance(p.get("interaction"), dict) else None)
                if not r.get("key_entity") and k_entity:
                    updates["key_entity"] = str(k_entity)

                # tipo_integracao
                tp_int = p.get("tipoIntegracao") or p.get("intermediario") or p.get("tipo_integracao")
                if not r.get("tipo_integracao") and tp_int:
                    updates["tipo_integracao"] = str(tp_int)

                # qualificacao_lead
                qual = p.get("qualificacaoLead") or (p.get("outcome", {}).get("qualification") if isinstance(p.get("outcome"), dict) else None)
                if not r.get("qualificacao_lead") and qual:
                    updates["qualificacao_lead"] = str(qual)

                if updates:
                    set_clause = ", ".join([f"{k} = %s" for k in updates.keys()])
                    self.execute_non_query(f"UPDATE atendimentos SET {set_clause} WHERE id = %s", tuple(list(updates.values()) + [r["id"]]))
                    updated_count += 1

            if updated_count > 0:
                print(f"[DB SYNC] {updated_count} atendimentos sincronizados a partir do payload_bruto.")
        except Exception as e:
            print(f"[DB SYNC WARN] Erro ao sincronizar colunas do payload: {e}")

    def get_status(self):
        """Retorna o status completo da conexão com o banco de dados."""
        start = time.time()
        is_healthy = False
        total_records = 0
        total_users = 0
        try:
            res_rec = self.execute_query("SELECT COUNT(*) as total FROM atendimentos", fetch="one")
            total_records = res_rec.get("total", 0) if res_rec else 0
            
            res_usr = self.execute_query("SELECT COUNT(*) as total FROM usuarios", fetch="one")
            total_users = res_usr.get("total", 0) if res_usr else 0
            is_healthy = True
        except Exception as e:
            self.last_error = str(e)
            is_healthy = False

        latency_ms = round((time.time() - start) * 1000, 2)

        return {
            "driver": self.active_driver,
            "host": Config.DB_HOST if self.active_driver == "mysql" else "Local SQLite Engine",
            "port": Config.DB_PORT if self.active_driver == "mysql" else "N/A",
            "database": Config.DB_DATABASE if self.active_driver == "mysql" else "duobi_data.sqlite3",
            "username": Config.DB_USERNAME if self.active_driver == "mysql" else "N/A",
            "connected": is_healthy,
            "latency_ms": latency_ms,
            "total_atendimentos": total_records,
            "total_usuarios": total_users,
            "last_error": self.last_error
        }

db = DatabaseManager()
