import json
import os
import sqlite3
import threading
from datetime import datetime
from datetime import timedelta

from .config_manager import ConfigManager
from .logger_manager import get_logger


class DatabaseManager:
    def __init__(self, config: ConfigManager):
        self.config = config
        self.engine = os.environ.get("IA_SAV_DATABASE_ENGINE") or self.config.get("DATABASE", "engine", fallback="sqlite")
        self.engine = self.engine.lower()
        self.lock = threading.Lock()
        self.connection = self._connect()
        self.create_tables()
        self.logger = get_logger()

    def _connect(self):
        if self.engine == "mysql":
            try:
                import pymysql
                from pymysql.cursors import DictCursor
            except ImportError as exc:
                raise RuntimeError("Le paquet PyMySQL est requis pour DATABASE.engine=mysql") from exc

            return pymysql.connect(
                host=self.config.get("DATABASE", "mysql_host", fallback="localhost"),
                port=self.config.getint("DATABASE", "mysql_port", fallback=3306),
                user=self.config.get("DATABASE", "mysql_user"),
                password=self.config.get("DATABASE", "mysql_password"),
                database=self.config.get("DATABASE", "mysql_database"),
                charset="utf8mb4",
                cursorclass=DictCursor,
                autocommit=False,
            )

        self.db_path = self.config.get_database_path()
        if self.db_path != ":memory:":
            self.db_path.parent.mkdir(parents=True, exist_ok=True)
        connection = sqlite3.connect(self.db_path, check_same_thread=False)
        connection.row_factory = sqlite3.Row
        return connection

    @property
    def placeholder(self):
        return "%s" if self.engine == "mysql" else "?"

    def _ensure_connection(self):
        if self.engine == "mysql":
            self.connection.ping(reconnect=True)

    def create_tables(self):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            if self.engine == "mysql":
                self._create_mysql_tables(cursor)
            else:
                self._create_sqlite_tables(cursor)
            self.connection.commit()

    def _create_sqlite_tables(self, cursor):
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS messages_whatsapp (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                whatsapp_message_id TEXT UNIQUE,
                source TEXT DEFAULT 'whatsapp',
                subject TEXT,
                attachment_path TEXT,
                attachment_url TEXT,
                phone TEXT,
                contact_name TEXT,
                message TEXT,
                received_at TEXT,
                processed INTEGER DEFAULT 0,
                created_at TEXT
            )
            """
        )
        self._ensure_sqlite_column(cursor, "messages_whatsapp", "source", "TEXT DEFAULT 'whatsapp'")
        self._ensure_sqlite_column(cursor, "messages_whatsapp", "subject", "TEXT")
        self._ensure_sqlite_column(cursor, "messages_whatsapp", "attachment_path", "TEXT")
        self._ensure_sqlite_column(cursor, "messages_whatsapp", "attachment_url", "TEXT")
        self._create_conversation_states_table(cursor)
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS ai_analysis (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                message_id INTEGER,
                categorie TEXT,
                urgence TEXT,
                resume TEXT,
                response_json TEXT,
                created_at TEXT,
                FOREIGN KEY(message_id) REFERENCES messages_whatsapp(id)
            )
            """
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS tickets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                message_id INTEGER,
                odoo_ticket_id TEXT,
                title TEXT,
                priority INTEGER,
                status TEXT,
                created_at TEXT,
                FOREIGN KEY(message_id) REFERENCES messages_whatsapp(id)
            )
            """
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                level TEXT,
                source TEXT,
                message TEXT,
                created_at TEXT
            )
            """
        )

    def _create_mysql_tables(self, cursor):
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS messages_whatsapp (
                id INT AUTO_INCREMENT PRIMARY KEY,
                whatsapp_message_id VARCHAR(191) UNIQUE,
                source VARCHAR(50) DEFAULT 'whatsapp',
                subject VARCHAR(255),
                attachment_path TEXT,
                attachment_url TEXT,
                phone VARCHAR(191),
                contact_name VARCHAR(255),
                message LONGTEXT,
                received_at DATETIME,
                processed TINYINT DEFAULT 0,
                created_at DATETIME,
                INDEX idx_messages_created_at (created_at)
            ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
            """
        )
        self._ensure_mysql_column(cursor, "messages_whatsapp", "source", "VARCHAR(50) DEFAULT 'whatsapp'")
        self._ensure_mysql_column(cursor, "messages_whatsapp", "subject", "VARCHAR(255)")
        self._ensure_mysql_column(cursor, "messages_whatsapp", "attachment_path", "TEXT")
        self._ensure_mysql_column(cursor, "messages_whatsapp", "attachment_url", "TEXT")
        self._create_conversation_states_table(cursor)
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS ai_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                message_id INT,
                categorie VARCHAR(100),
                urgence VARCHAR(50),
                resume TEXT,
                response_json LONGTEXT,
                created_at DATETIME,
                INDEX idx_ai_message_id (message_id)
            ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
            """
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS tickets (
                id INT AUTO_INCREMENT PRIMARY KEY,
                message_id INT,
                odoo_ticket_id VARCHAR(100),
                title VARCHAR(255),
                priority INT,
                status VARCHAR(50),
                created_at DATETIME,
                INDEX idx_tickets_message_id (message_id),
                INDEX idx_tickets_created_at (created_at)
            ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
            """
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                level VARCHAR(50),
                source VARCHAR(100),
                message TEXT,
                created_at DATETIME,
                INDEX idx_logs_created_at (created_at)
            ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
            """
        )

    def insert_whatsapp_message(self, whatsapp_message_id, phone, contact_name, message, received_at):
        return self.insert_support_request(
            source="whatsapp",
            external_id=whatsapp_message_id,
            requester_contact=phone,
            contact_name=contact_name,
            message=message,
            received_at=received_at,
        )

    def insert_support_request(self, source, external_id, requester_contact, contact_name, message, received_at, subject="", attachment_path="", attachment_url=""):
        created_at = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
        insert_prefix = "INSERT IGNORE" if self.engine == "mysql" else "INSERT OR IGNORE"
        sql = f"""
            {insert_prefix} INTO messages_whatsapp
            (whatsapp_message_id, source, subject, attachment_path, attachment_url, phone, contact_name, message, received_at, created_at)
            VALUES ({self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder})
        """
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                sql,
                (external_id, source, subject, attachment_path, attachment_url, requester_contact, contact_name, message, received_at, created_at),
            )
            self.connection.commit()
            return cursor.lastrowid

    def message_exists(self, whatsapp_message_id):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"SELECT 1 FROM messages_whatsapp WHERE whatsapp_message_id = {self.placeholder}",
                (whatsapp_message_id,),
            )
            return cursor.fetchone() is not None

    def mark_message_processed(self, message_id):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"UPDATE messages_whatsapp SET processed = 1 WHERE id = {self.placeholder}",
                (message_id,),
            )
            self.connection.commit()

    def insert_ai_analysis(self, message_id, categorie, urgence, resume, response_json):
        created_at = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"""
                INSERT INTO ai_analysis (message_id, categorie, urgence, resume, response_json, created_at)
                VALUES ({self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder})
                """,
                (message_id, categorie, urgence, resume, json.dumps(response_json, ensure_ascii=False), created_at),
            )
            self.connection.commit()

    def insert_ticket(self, message_id, odoo_ticket_id, title, priority, status):
        created_at = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"""
                INSERT INTO tickets (message_id, odoo_ticket_id, title, priority, status, created_at)
                VALUES ({self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder})
                """,
                (message_id, str(odoo_ticket_id), title, priority, status, created_at),
            )
            self.connection.commit()

    def insert_log(self, level, source, message):
        created_at = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"INSERT INTO logs (level, source, message, created_at) VALUES ({self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder})",
                (level, source, message, created_at),
            )
            self.connection.commit()

    def fetch_recent_messages(self, limit=100):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"""
                SELECT m.id, m.whatsapp_message_id, m.source, m.subject, m.attachment_path, m.attachment_url, m.phone, m.contact_name, m.message,
                       m.received_at, m.processed, a.categorie, a.urgence, a.resume
                FROM messages_whatsapp m
                LEFT JOIN ai_analysis a ON a.message_id = m.id
                ORDER BY m.created_at DESC
                LIMIT {self.placeholder}
                """,
                (limit,),
            )
            return cursor.fetchall()

    def fetch_tickets(self, limit=100):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(f"SELECT * FROM tickets ORDER BY created_at DESC LIMIT {self.placeholder}", (limit,))
            return cursor.fetchall()

    def fetch_logs(self, limit=200):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(f"SELECT * FROM logs ORDER BY created_at DESC LIMIT {self.placeholder}", (limit,))
            return cursor.fetchall()

    def fetch_message_by_id(self, message_id):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(f"SELECT * FROM messages_whatsapp WHERE id = {self.placeholder}", (message_id,))
            return cursor.fetchone()

    def get_conversation_state(self, source, requester_contact):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"""
                SELECT * FROM conversation_states
                WHERE source = {self.placeholder} AND requester_contact = {self.placeholder}
                LIMIT 1
                """,
                (source, requester_contact),
            )
            return cursor.fetchone()

    def set_conversation_state(self, source, requester_contact, state, odoo_ticket_id="", category="", created_message=""):
        now = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
        existing = self.get_conversation_state(source, requester_contact)
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            if existing:
                cursor.execute(
                    f"""
                    UPDATE conversation_states
                    SET state = {self.placeholder}, odoo_ticket_id = {self.placeholder}, category = {self.placeholder},
                        created_message = {self.placeholder}, updated_at = {self.placeholder}
                    WHERE source = {self.placeholder} AND requester_contact = {self.placeholder}
                    """,
                    (state, str(odoo_ticket_id), category, created_message, now, source, requester_contact),
                )
            else:
                cursor.execute(
                    f"""
                    INSERT INTO conversation_states
                    (source, requester_contact, state, odoo_ticket_id, category, created_message, updated_at)
                    VALUES ({self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder}, {self.placeholder})
                    """,
                    (source, requester_contact, state, str(odoo_ticket_id), category, created_message, now),
                )
            self.connection.commit()

    def clear_conversation_state(self, source, requester_contact):
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"DELETE FROM conversation_states WHERE source = {self.placeholder} AND requester_contact = {self.placeholder}",
                (source, requester_contact),
            )
            self.connection.commit()

    def fetch_recent_ticket_for_contact(self, source, requester_contact, minutes=60):
        cutoff = (datetime.utcnow() - timedelta(minutes=minutes)).strftime("%Y-%m-%d %H:%M:%S")
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"""
                SELECT t.*, m.phone, m.source
                FROM tickets t
                JOIN messages_whatsapp m ON m.id = t.message_id
                WHERE m.source = {self.placeholder}
                  AND m.phone = {self.placeholder}
                  AND t.status IN ('created', 'updated')
                  AND t.odoo_ticket_id IS NOT NULL
                  AND t.odoo_ticket_id <> ''
                  AND t.odoo_ticket_id <> 'None'
                  AND t.created_at >= {self.placeholder}
                ORDER BY t.created_at DESC
                LIMIT 1
                """,
                (source, requester_contact, cutoff),
            )
            return cursor.fetchone()

    def fetch_recent_analysis_for_contact(self, source, requester_contact, current_message_id, minutes=60):
        cutoff = (datetime.utcnow() - timedelta(minutes=minutes)).strftime("%Y-%m-%d %H:%M:%S")
        with self.lock:
            self._ensure_connection()
            cursor = self.connection.cursor()
            cursor.execute(
                f"""
                SELECT m.id, m.message, m.subject, m.contact_name, m.phone, m.source,
                       a.categorie, a.urgence, a.resume, a.response_json, a.created_at
                FROM messages_whatsapp m
                JOIN ai_analysis a ON a.message_id = m.id
                WHERE m.source = {self.placeholder}
                  AND m.phone = {self.placeholder}
                  AND m.id <> {self.placeholder}
                  AND m.created_at >= {self.placeholder}
                ORDER BY m.created_at DESC
                LIMIT 1
                """,
                (source, requester_contact, current_message_id, cutoff),
            )
            return cursor.fetchone()

    def _ensure_sqlite_column(self, cursor, table, column, definition):
        cursor.execute(f"PRAGMA table_info({table})")
        existing = {row["name"] for row in cursor.fetchall()}
        if column not in existing:
            cursor.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")

    def _ensure_mysql_column(self, cursor, table, column, definition):
        cursor.execute(f"SHOW COLUMNS FROM {table} LIKE {self.placeholder}", (column,))
        if cursor.fetchone() is None:
            cursor.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")

    def _create_conversation_states_table(self, cursor):
        if self.engine == "mysql":
            cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS conversation_states (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    source VARCHAR(50),
                    requester_contact VARCHAR(191),
                    state VARCHAR(100),
                    odoo_ticket_id VARCHAR(100),
                    category VARCHAR(100),
                    created_message LONGTEXT,
                    updated_at DATETIME,
                    UNIQUE KEY uniq_conversation_state (source, requester_contact)
                ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
                """
            )
            return

        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS conversation_states (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                source TEXT,
                requester_contact TEXT,
                state TEXT,
                odoo_ticket_id TEXT,
                category TEXT,
                created_message TEXT,
                updated_at TEXT,
                UNIQUE(source, requester_contact)
            )
            """
        )
