import json
from abc import ABC, abstractmethod
from contextlib import asynccontextmanager
from typing import Generic, TypeVar
from uuid import UUID

import aiosqlite
from pydantic import BaseModel, Field

from huesoporro import utils
from huesoporro.models import Chatbot, Quote, User
from huesoporro.settings import Settings

T = TypeVar("T", bound=BaseModel)


class IRepo(BaseModel, ABC, Generic[T]):
    s: Settings = Field(default_factory=Settings.get)

    @asynccontextmanager
    async def get_client(self, auto_commit=True):
        async with aiosqlite.connect(self.s.db_filepath) as db:
            db.row_factory = aiosqlite.Row
            yield db
            if auto_commit:
                await db.commit()

    @abstractmethod
    async def create(self, obj: T, auto_commit=True) -> T:
        pass  # pragma: no cover

    @abstractmethod
    async def update(self, obj: T, auto_commit=True) -> T:
        pass  # pragma: no cover

    @abstractmethod
    async def delete(self, obj: T, auto_commit=True):
        pass  # pragma: no cover

    @abstractmethod
    async def get_by_id(self, obj_id: UUID, auto_commit=True) -> T | None:
        pass  # pragma: no cover

    @abstractmethod
    async def list(self, offset: int = 0, limit: int = 10, auto_commit=True) -> list[T]:
        pass  # pragma: no cover


class UserRepo(IRepo[User]):
    @staticmethod
    def _deserialize(data: dict) -> User:
        return User(
            id=UUID(data["id"]),
            username=data["username"],
            created_at=data["created_at"],
            last_updated_at=data["last_updated_at"],
            external_auth=json.loads(data["external_auth"]),
        )

    async def get_by_id(self, obj_id: UUID, auto_commit=True) -> User | None:
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            await db.execute(
                """
                SELECT *
                FROM users
                WHERE id = ?
                """,
                (obj_id.hex,),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            if not data:
                return None
            return self._deserialize(data)

    async def create(self, obj: User, auto_commit=True) -> User:
        if await self.get_by_username(obj.username):
            raise ValueError(f"User {obj.username} already exists")
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            await db.execute(
                """INSERT INTO users (id, username, external_auth, created_at, last_updated_at)
                   VALUES (?, ?, ?, ?, ?) RETURNING *
                """,
                (
                    obj.id.hex,
                    obj.username,
                    obj.serialize_external_auth(),
                    obj.created_at,
                    obj.last_updated_at,
                ),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            return self._deserialize(data)

    async def update(self, obj: User, auto_commit=True) -> User:
        if not await self.get_by_id(obj.id):
            raise ValueError(f"User {obj.username} does not exist")

        async with (
            self.get_client(auto_commit=auto_commit) as db,
            db.execute(
                """
                UPDATE users
                SET username        = ?,
                    external_auth   = ?,
                    last_updated_at = ?
                WHERE id = ? RETURNING *
                """,
                (
                    obj.username,
                    obj.serialize_external_auth(),
                    obj.last_updated_at,
                    obj.id.hex,
                ),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            return self._deserialize(data)

    async def delete(self, obj: User, auto_commit=True):
        async with self.get_client(auto_commit=auto_commit) as db:
            await db.execute(
                """
                DELETE
                FROM users
                WHERE id = ?
                """,
                (obj.id.hex,),
            )

    async def get_by_username(self, user: str, auto_commit=True) -> User | None:
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            db.execute(
                """
                SELECT *
                FROM users
                WHERE username = ?
                """,
                (user,),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            if not data:
                return None
            return User(
                id=UUID(data["id"]),
                username=data["username"],
                created_at=data["created_at"],
                last_updated_at=data["last_updated_at"],
                external_auth=json.loads(data["external_auth"]),
            )

    async def list(  # type: ignore[empty-body]
        self, offset: int = 0, limit: int = 10, auto_commit=True
    ) -> list[User]:
        pass  # pragma: no cover

    async def count(self, obj: User, auto_commit=True):
        pass  # pragma: no cover


class QuoteRepo(IRepo[Quote]):
    @staticmethod
    def _deserialize(data: dict) -> Quote:
        return Quote(
            id=UUID(data["id"]),
            quote=data["quote"],
            author=data["author"],
            channel_name=data["channel"],
            created_at=data["created_at"],
            last_updated_at=data["last_updated_at"],
            is_active=data["is_active"],
        )

    async def create(self, obj: Quote, auto_commit=True) -> Quote:
        if await self.get_by_quote(obj.quote):
            raise ValueError(f"Quote {obj.quote} already exists")
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            await db.execute(
                """
                INSERT INTO quotes (id, quote, author, channel, created_at, is_active, last_updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *
                """,
                (
                    obj.id.hex,
                    obj.quote,
                    obj.author,
                    obj.channel_name,
                    obj.created_at,
                    obj.is_active,
                    obj.last_updated_at,
                ),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            return self._deserialize(data)

    async def update(self, obj: Quote, auto_commit=True) -> Quote:  # type: ignore[empty-body]
        if not await self.get_by_id(obj.id):
            raise ValueError(f"Quote {obj.id} does not exist")
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            await db.execute(
                """
                UPDATE quotes
                SET quote           = ?,
                    author          = ?,
                    channel         = ?,
                    is_active       = ?,
                    last_updated_at = ?
                WHERE id = ? RETURNING *
                """,
                (
                    obj.quote,
                    obj.author,
                    obj.channel_name,
                    obj.is_active,
                    utils.get_utc_now(),
                    obj.id.hex,
                ),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            return self._deserialize(data)

    async def delete(self, obj: Quote, auto_commit=True):
        async with self.get_client(auto_commit=auto_commit) as db:
            await db.execute(
                """
                DELETE
                FROM quotes
                WHERE id = ?
                """,
                (obj.id.hex,),
            )

    async def get_by_id(self, obj_id: UUID, auto_commit=True) -> Quote | None:  # type: ignore[empty-body]
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            db.execute(
                """
                SELECT *
                FROM quotes
                WHERE id = ?
                """,
                (obj_id.hex,),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            if not data:
                return None
            return self._deserialize(data)

    async def get_by_quote(self, quote: str, auto_commit=True) -> Quote | None:
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            db.execute(
                """
                SELECT *
                FROM quotes
                WHERE quote = ?
                """,
                (quote,),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            if not data:
                return None
            return self._deserialize(data)

    async def list(  # type: ignore[empty-body]
        self, offset: int = 0, limit: int = 10, auto_commit=True
    ) -> list[Quote]:
        async with self.get_client() as db:
            db.row_factory = aiosqlite.Row
            async with db.execute(
                "SELECT * FROM quotes LIMIT ? OFFSET ?", (limit, offset)
            ) as cursor:
                results = await cursor.fetchall()
        return [self._deserialize(result) for result in results]

    async def get_random(self, channel_name: str, auto_commit=True) -> Quote | None:
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            db.execute(
                """
                SELECT *
                FROM quotes
                WHERE channel = ?
                  AND is_active = 1
                ORDER BY RANDOM() LIMIT 1
                """,
                (channel_name,),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            if not data:
                return None
            return self._deserialize(data)


class ChatbotRepo(IRepo[Chatbot]):
    @staticmethod
    def _deserialize(data: dict) -> Chatbot:
        return Chatbot(
            id=UUID(data["id"]),
            user_id=data["user_id"],
            automatic_generation_timer=data["automatic_generation_timer"],
            automatic_quote_timer=data["automatic_quote_timer"],
            mods=data["mods"].split(","),
            last_updated_at=data["last_updated_at"],
            created_at=data["created_at"],
        )

    async def create(self, obj: Chatbot, auto_commit=True) -> Chatbot:
        if await self.get_by_user_id(obj.user_id):
            raise ValueError(f"Chatbot {obj.user_id} already exists")
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            await db.execute(
                """INSERT INTO chatbot (id,
                                        user_id,
                                        automatic_generation_timer,
                                        automatic_quote_timer,
                                        mods,
                                        created_at,
                                        last_updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *
                """,
                (
                    obj.id.hex,
                    obj.user_id.hex,
                    obj.automatic_generation_timer,
                    obj.automatic_quote_timer,
                    obj.mods_as_string,
                    obj.created_at,
                    obj.last_updated_at,
                ),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            return self._deserialize(data)

    async def update(self, obj: Chatbot, auto_commit=True) -> Chatbot:
        if not await self.get_by_user_id(obj.user_id):
            raise ValueError(f"Chatbot {obj.user_id} does not exist")
        async with (
            self.get_client(auto_commit=auto_commit) as db,
            await db.execute(
                """UPDATE chatbot
                   SET automatic_generation_timer = ?,
                       automatic_quote_timer      = ?,
                       mods                       = ?,
                       last_updated_at            = ?
                   WHERE user_id = ? RETURNING *
                """,
                (
                    obj.automatic_generation_timer,
                    obj.automatic_quote_timer,
                    obj.mods_as_string,
                    utils.get_utc_now(),
                    obj.user_id.hex,
                ),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            return self._deserialize(data)

    async def delete(self, obj: Chatbot, auto_commit=True):
        if not await self.get_by_id(obj.id):
            raise ValueError(f"Chatbot {obj.id} does not exist")
        async with self.get_client() as db:
            await db.execute("DELETE FROM chatbot WHERE id = ?", (obj.id.hex,))

    async def get_by_id(self, obj_id: UUID, auto_commit=True) -> Chatbot | None:  # type: ignore[empty-body]
        async with self.get_client() as db:
            db.row_factory = aiosqlite.Row
            async with db.execute(
                "SELECT * FROM chatbot WHERE id = ?", (obj_id.hex,)
            ) as cursor:
                result = await cursor.fetchone()
        if not result:
            return None
        return self._deserialize(result)

    async def get_by_user_id(self, user_id: UUID) -> Chatbot | None:
        async with self.get_client() as db:
            db.row_factory = aiosqlite.Row
            async with db.execute(
                "SELECT * FROM chatbot WHERE user_id = ?", (user_id.hex,)
            ) as cursor:
                result = await cursor.fetchone()
        if not result:
            return None
        return self._deserialize(result)

    async def list(  # type: ignore[empty-body]
        self, offset: int = 0, limit: int = 10, auto_commit=True
    ) -> list[Chatbot]:
        async with self.get_client() as db:
            db.row_factory = aiosqlite.Row
            async with db.execute(
                "SELECT * FROM chatbot LIMIT ? OFFSET ?", (limit, offset)
            ) as cursor:
                results = await cursor.fetchall()
        return [self._deserialize(result) for result in results]
