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

import aiosqlite
from pydantic import BaseModel, Field

from src.huesoporro.models import Quote, User
from src.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

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

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

    @abstractmethod
    async def get_by_id(self, obj_id: int | str, auto_commit=True) -> T | None:
        pass

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


class UserRepo(IRepo[User]):
    async def get_by_id(self, obj_id: int | str, auto_commit=True) -> User | None:
        raise NotImplementedError("Not implemented since it's not needed")

    async def create(self, obj: User, auto_commit=True) -> User:
        async with self.get_client(auto_commit=auto_commit) as db:
            await db.execute(
                "INSERT INTO users (user, external_auth) VALUES (?, ?)",
                (obj.user, json.dumps(obj.external_auth)),
            )
            return obj

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

        async with (
            self.get_client(auto_commit=auto_commit) as db,
            db.execute(
                """
                    UPDATE users
                    SET external_auth = ?
                    WHERE user = ?
                    RETURNING *
                    """,
                (json.dumps(obj.external_auth), obj.user),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            return User(
                user=data["user"], external_auth=json.loads(data["external_auth"])
            )

    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 user = ?
                """,
                (obj.user,),
            )

    async def get_by_user(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 user = ?
                    """,
                (user,),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            if not data:
                return None
            return User(
                user=data["user"], external_auth=json.loads(data["external_auth"])
            )

    async def list(
        self, obj: User, offset: int = 0, limit: int = 10, auto_commit=True
    ) -> list[User]:
        raise NotImplementedError("Not implemented since it's not needed")

    async def count(self, obj: User, auto_commit=True):
        raise NotImplementedError("Not implemented since it's not needed")


class QuoteRepo(IRepo[Quote]):
    async def create(self, obj: Quote, auto_commit=True) -> Quote:
        async with self.get_client(auto_commit=auto_commit) as db:
            await db.execute(
                """
                    INSERT INTO quotes (quote, author, channel, created_at, last_updated_at)
                    VALUES (?, ?, ?, ?, ?)
                """,
                (
                    obj.quote,
                    obj.author.user,
                    obj.channel.user,
                    obj.created_at,
                    obj.last_updated_at,
                ),
            )
            return obj

    async def update(self, obj: Quote, auto_commit=True) -> Quote:
        raise NotImplementedError("Not implemented since it's not needed")

    async def delete(self, obj: Quote, auto_commit=True):
        raise NotImplementedError("Not implemented since it's not needed")

    async def get_by_id(self, obj_id: int | str, auto_commit=True) -> Quote | None:
        raise NotImplementedError("Not implemented since it's not needed")

    async def list(
        self, obj: T, offset: int = 0, limit: int = 10, auto_commit=True
    ) -> list[T]:
        raise NotImplementedError("Not implemented since it's not needed")

    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 = ?
                    ORDER BY RANDOM()
                    LIMIT 1
                """,
                (channel_name,),
            ) as cursor,
        ):
            data = await cursor.fetchone()
            if not data:
                return None
            return Quote(
                quote=data["quote"],
                author=User(user=data["author"], external_auth={}),
                channel=User(user=data["channel"], external_auth={}),
                created_at=data["created_at"],
                last_updated_at=data["last_updated_at"],
            )
