from __future__ import annotations

import json
from contextlib import asynccontextmanager
from typing import Any

import aiomysql

from config import settings


SCHEMA_STATEMENTS = [
    """
    CREATE TABLE IF NOT EXISTS guilds (
        guild_id VARCHAR(32) PRIMARY KEY,
        name VARCHAR(190) NOT NULL,
        icon_url TEXT NULL,
        owner_id VARCHAR(32) NULL,
        bot_joined_at DATETIME NULL,
        last_seen_at DATETIME NULL
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS guild_settings (
        guild_id VARCHAR(32) PRIMARY KEY,
        enabled TINYINT(1) NOT NULL DEFAULT 1,
        aggressive_mode TINYINT(1) NOT NULL DEFAULT 1,
        automatic_backups TINYINT(1) NOT NULL DEFAULT 1,
        backup_interval_hours INT NOT NULL DEFAULT 6,
        backup_keep INT NOT NULL DEFAULT 30,

        channel_delete_threshold INT NOT NULL DEFAULT 3,
        channel_delete_window INT NOT NULL DEFAULT 12,
        role_delete_threshold INT NOT NULL DEFAULT 3,
        role_delete_window INT NOT NULL DEFAULT 12,
        channel_create_threshold INT NOT NULL DEFAULT 6,
        channel_create_window INT NOT NULL DEFAULT 15,
        role_create_threshold INT NOT NULL DEFAULT 6,
        role_create_window INT NOT NULL DEFAULT 15,
        ban_threshold INT NOT NULL DEFAULT 4,
        ban_window INT NOT NULL DEFAULT 15,
        kick_threshold INT NOT NULL DEFAULT 4,
        kick_window INT NOT NULL DEFAULT 15,
        webhook_threshold INT NOT NULL DEFAULT 3,
        webhook_window INT NOT NULL DEFAULT 15,
        dangerous_role_grant_threshold INT NOT NULL DEFAULT 2,
        dangerous_role_grant_window INT NOT NULL DEFAULT 20,
        bot_add_threshold INT NOT NULL DEFAULT 1,
        bot_add_window INT NOT NULL DEFAULT 30
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS trusted_users (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        guild_id VARCHAR(32) NOT NULL,
        user_id VARCHAR(32) NOT NULL,
        label VARCHAR(190) NULL,
        created_at DATETIME NOT NULL,
        UNIQUE KEY uq_trusted (guild_id,user_id),
        INDEX idx_trusted_guild (guild_id)
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS security_events (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        guild_id VARCHAR(32) NOT NULL,
        event_type VARCHAR(64) NOT NULL,
        severity VARCHAR(32) NOT NULL,
        actor_id VARCHAR(32) NULL,
        actor_name VARCHAR(190) NULL,
        target TEXT NULL,
        details_json LONGTEXT NULL,
        blocked TINYINT(1) NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL,
        INDEX idx_events_guild_date (guild_id,created_at),
        INDEX idx_events_type (event_type)
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS backups (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        guild_id VARCHAR(32) NOT NULL,
        reason VARCHAR(100) NOT NULL,
        backup_json LONGTEXT NOT NULL,
        created_by VARCHAR(190) NULL,
        created_at DATETIME NOT NULL,
        INDEX idx_backups_guild_date (guild_id,created_at)
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS commands (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        guild_id VARCHAR(32) NOT NULL,
        command_type VARCHAR(64) NOT NULL,
        payload_json LONGTEXT NULL,
        status ENUM('pending','running','done','error') NOT NULL DEFAULT 'pending',
        created_by_id VARCHAR(32) NULL,
        created_by_name VARCHAR(190) NULL,
        created_at DATETIME NOT NULL,
        started_at DATETIME NULL,
        finished_at DATETIME NULL,
        result_json LONGTEXT NULL,
        error_text TEXT NULL,
        INDEX idx_commands_status (status,id),
        INDEX idx_commands_guild (guild_id,id)
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS lockdown_states (
        guild_id VARCHAR(32) NOT NULL,
        mode VARCHAR(32) NOT NULL,
        state_json LONGTEXT NOT NULL,
        active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL,
        PRIMARY KEY (guild_id,mode)
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS customers (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(190) NOT NULL,
        discord_user_id VARCHAR(32) NULL,
        email VARCHAR(190) NULL,
        notes TEXT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB
    """,
    """
    CREATE TABLE IF NOT EXISTS licenses (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        guild_id VARCHAR(32) NOT NULL,
        customer_id BIGINT UNSIGNED NULL,
        plan ENUM('basic','pro','ultimate') NOT NULL DEFAULT 'pro',
        active TINYINT(1) NOT NULL DEFAULT 1,
        starts_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        expires_at DATETIME NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_license_guild (guild_id),
        INDEX idx_license_active (active,expires_at)
    ) ENGINE=InnoDB
    """,
]


class Database:
    def __init__(self) -> None:
        self.pool: aiomysql.Pool | None = None

    async def ensure_database(self) -> None:
        """
        Crea la base de datos automáticamente si no existe.
        Solo requiere que el usuario MySQL configurado tenga permiso CREATE DATABASE.
        """
        conn = await aiomysql.connect(
            host=settings.mysql_host,
            port=settings.mysql_port,
            user=settings.mysql_user,
            password=settings.mysql_password,
            autocommit=True,
            charset="utf8mb4",
        )
        try:
            async with conn.cursor() as cur:
                safe_db = settings.mysql_database.replace("`", "")
                await cur.execute(
                    f"CREATE DATABASE IF NOT EXISTS `{safe_db}` "
                    "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
                )
        finally:
            conn.close()

    async def connect(self) -> None:
        if self.pool:
            return

        await self.ensure_database()

        self.pool = await aiomysql.create_pool(
            host=settings.mysql_host,
            port=settings.mysql_port,
            user=settings.mysql_user,
            password=settings.mysql_password,
            db=settings.mysql_database,
            autocommit=True,
            minsize=1,
            maxsize=10,
            charset="utf8mb4",
        )

        await self.ensure_schema()

    async def ensure_schema(self) -> None:
        """
        Crea automáticamente todas las tablas necesarias.
        Se ejecuta cada vez que inicia NovaShield y es seguro usar IF NOT EXISTS.
        """
        async with self.cursor(dict_cursor=False) as cur:
            for statement in SCHEMA_STATEMENTS:
                await cur.execute(statement)

        await self.ensure_columns()

    async def ensure_columns(self) -> None:
        """
        Migraciones simples para futuras actualizaciones.
        Si agregamos una columna nueva, se puede registrar aquí sin pedir al usuario SQL manual.
        """
        migrations = [
            ("guild_settings", "aggressive_mode", "TINYINT(1) NOT NULL DEFAULT 1"),
            ("guild_settings", "automatic_backups", "TINYINT(1) NOT NULL DEFAULT 1"),
            ("guild_settings", "backup_interval_hours", "INT NOT NULL DEFAULT 6"),
            ("guild_settings", "backup_keep", "INT NOT NULL DEFAULT 30"),
        ]

        for table, column, definition in migrations:
            row = await self.fetchone(
                """
                SELECT COUNT(*) AS total
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s AND COLUMN_NAME=%s
                """,
                (settings.mysql_database, table, column),
            )
            if not row or int(row.get("total", 0)) == 0:
                async with self.cursor(dict_cursor=False) as cur:
                    await cur.execute(
                        f"ALTER TABLE `{table}` ADD COLUMN `{column}` {definition}"
                    )

    async def close(self) -> None:
        if self.pool:
            self.pool.close()
            await self.pool.wait_closed()
            self.pool = None

    @asynccontextmanager
    async def cursor(self, dict_cursor: bool = True):
        if not self.pool:
            # Durante connect() llamamos ensure_schema() después de crear el pool,
            # así que no debe producir recursión.
            await self.connect()
        assert self.pool is not None
        async with self.pool.acquire() as conn:
            cursor_cls = aiomysql.DictCursor if dict_cursor else aiomysql.Cursor
            async with conn.cursor(cursor_cls) as cur:
                yield cur

    async def execute(self, sql: str, args: tuple[Any, ...] = ()) -> int:
        async with self.cursor() as cur:
            await cur.execute(sql, args)
            return cur.lastrowid

    async def fetchone(self, sql: str, args: tuple[Any, ...] = ()) -> dict[str, Any] | None:
        async with self.cursor() as cur:
            await cur.execute(sql, args)
            return await cur.fetchone()

    async def fetchall(self, sql: str, args: tuple[Any, ...] = ()) -> list[dict[str, Any]]:
        async with self.cursor() as cur:
            await cur.execute(sql, args)
            return list(await cur.fetchall())

    async def upsert_guild(self, guild) -> None:
        await self.execute(
            """
            INSERT INTO guilds (guild_id, name, icon_url, owner_id, bot_joined_at, last_seen_at)
            VALUES (%s,%s,%s,%s,NOW(),NOW())
            ON DUPLICATE KEY UPDATE
                name=VALUES(name),
                icon_url=VALUES(icon_url),
                owner_id=VALUES(owner_id),
                last_seen_at=NOW()
            """,
            (
                str(guild.id),
                guild.name,
                str(guild.icon.url) if guild.icon else "",
                str(guild.owner_id or ""),
            ),
        )
        await self.execute(
            """
            INSERT IGNORE INTO guild_settings (guild_id)
            VALUES (%s)
            """,
            (str(guild.id),),
        )

    async def settings_for(self, guild_id: int) -> dict[str, Any]:
        row = await self.fetchone("SELECT * FROM guild_settings WHERE guild_id=%s", (str(guild_id),))
        return row or {}

    async def is_licensed(self, guild_id: int) -> bool:
        row = await self.fetchone(
            """
            SELECT id FROM licenses
            WHERE guild_id=%s AND active=1
              AND (expires_at IS NULL OR expires_at > NOW())
            LIMIT 1
            """,
            (str(guild_id),),
        )
        return bool(row)

    async def trusted_ids(self, guild_id: int) -> set[int]:
        rows = await self.fetchall("SELECT user_id FROM trusted_users WHERE guild_id=%s", (str(guild_id),))
        result: set[int] = set()
        for row in rows:
            try:
                result.add(int(row["user_id"]))
            except (ValueError, TypeError):
                pass
        return result

    async def add_event(
        self,
        guild_id: int,
        event_type: str,
        severity: str,
        actor_id: int | None,
        actor_name: str,
        target: str,
        details: dict[str, Any] | None = None,
        blocked: bool = False,
    ) -> None:
        await self.execute(
            """
            INSERT INTO security_events
            (guild_id,event_type,severity,actor_id,actor_name,target,details_json,blocked,created_at)
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,NOW())
            """,
            (
                str(guild_id),
                event_type,
                severity,
                str(actor_id) if actor_id else None,
                actor_name,
                target,
                json.dumps(details or {}, ensure_ascii=False),
                1 if blocked else 0,
            ),
        )

    async def create_backup_record(self, guild_id: int, reason: str, data: dict[str, Any], created_by: str) -> int:
        return await self.execute(
            """
            INSERT INTO backups (guild_id,reason,backup_json,created_by,created_at)
            VALUES (%s,%s,%s,%s,NOW())
            """,
            (str(guild_id), reason, json.dumps(data, ensure_ascii=False), created_by),
        )

    async def get_backup(self, guild_id: int, backup_id: int) -> dict[str, Any] | None:
        return await self.fetchone(
            "SELECT * FROM backups WHERE id=%s AND guild_id=%s",
            (backup_id, str(guild_id)),
        )

    async def trim_backups(self, guild_id: int, keep: int) -> None:
        keep = max(1, min(int(keep or 30), 200))
        await self.execute(
            """
            DELETE FROM backups
            WHERE guild_id=%s
              AND id NOT IN (
                  SELECT id FROM (
                      SELECT id FROM backups
                      WHERE guild_id=%s
                      ORDER BY id DESC
                      LIMIT %s
                  ) AS keepers
              )
            """,
            (str(guild_id), str(guild_id), keep),
        )

    async def save_lockdown_state(self, guild_id: int, mode: str, data: dict[str, Any]) -> None:
        await self.execute(
            """
            INSERT INTO lockdown_states (guild_id,mode,state_json,active,created_at)
            VALUES (%s,%s,%s,1,NOW())
            ON DUPLICATE KEY UPDATE state_json=VALUES(state_json),active=1,created_at=NOW()
            """,
            (str(guild_id), mode, json.dumps(data, ensure_ascii=False)),
        )

    async def get_lockdown_state(self, guild_id: int, mode: str) -> dict[str, Any] | None:
        return await self.fetchone(
            "SELECT * FROM lockdown_states WHERE guild_id=%s AND mode=%s AND active=1",
            (str(guild_id), mode),
        )

    async def clear_lockdown_state(self, guild_id: int, mode: str) -> None:
        await self.execute(
            "UPDATE lockdown_states SET active=0 WHERE guild_id=%s AND mode=%s",
            (str(guild_id), mode),
        )


db = Database()