from datetime import datetime
from typing import List

from environs import Env
from sqlalchemy import create_engine, desc, distinct, func, text
from sqlalchemy.exc import OperationalError, TimeoutError
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import QueuePool
from tenacity import retry, retry_if_exception_type, stop_after_attempt, wait_random

from exceptions import InvalidParameters
from models import Application, Interaction

env = Env()
env.read_env()

DATABASE_URL = env("DATABASE_URL")

engine = create_engine(DATABASE_URL, poolclass=QueuePool, pool_size=10, max_overflow=20)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


def ping_db():
    with SessionLocal() as session:
        session.execute("SELECT 1")


# not tested
# @retry(stop=stop_after_attempt(3), wait=wait_random(min=1, max=2), retry=retry_if_exception_type((OperationalError, TimeoutError)))
# def list_applications(offset: int = 0, limit: int = 10, sort_by: str = "updated_at", descend: bool = True) -> List[Application]:
#     if descend:
#         sort_by = desc(sort_by)

#     with SessionLocal() as session:
#         return session.query(Application).order_by(sort_by).offset(offset).limit(limit).all()


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def get_application(application_id: str) -> Application:
    with SessionLocal() as session:
        return (
            session.query(Application).filter(Application.id == application_id).first()
        )


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def create_application(application: Application) -> Application:
    with SessionLocal() as session:
        session.add(application)
        session.commit()
        return application


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def update_application(application: Application) -> Application:
    app_dict = application.__dict__
    with SessionLocal() as session:
        if app_dict.get("_sa_instance_state"):
            app_dict.pop("_sa_instance_state")  # this is a cache of sqlalchemy
        session.query(Application).filter(Application.id == application.id).update(
            application.__dict__, synchronize_session=False
        )
        session.commit()
        return application


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def delete_application(application_id: str) -> bool:
    with SessionLocal() as session:
        application = (
            session.query(Application).filter(Application.id == application_id).first()
        )
        if application:
            session.delete(application)
            session.commit()
            return True
        else:
            return False


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def filter_applications(
    offset: int = 0,
    limit: int = 10,
    sort_by: str = "updated_at",
    descend: bool = True,
    **kwargs,
) -> List[Application]:
    if descend:
        sort_by = desc(sort_by)

    # kwargs["custom_fields"] = {"description": kwargs.pop("description")}

    # check if kwargs.keys() in column
    if not set(kwargs.keys()).issubset(Application.__table__.columns.keys()):
        raise InvalidParameters(
            set(kwargs.keys()) - set(Application.__table__.columns.keys())
        )

    filters = []
    for key, value in kwargs.items():
        if value is not None:
            if key != "custom_fields":
                if isinstance(value, str):
                    filters.append(getattr(Application, key).ilike(f"%{value}%"))
                else:
                    filters.append(getattr(Application, key) == value)
            # elif key == "custom_fields" and value.get("description") is not None:
            #     filters.append(
            #         text(
            #             f"JSON_EXTRACT(custom_fields, '$.description') LIKE :description"
            #         ).bindparams(description=f"%{value['description']}%")
            #     )

    with SessionLocal() as session:
        subquery = (
            session.query(
                Interaction.application_id,
                func.max(Interaction.created_at).label("latest_called_at"),
            )
            .group_by(Interaction.application_id)
            .subquery()
        )
        query = (
            session.query(Application, subquery.c.latest_called_at)
            .outerjoin(subquery, subquery.c.application_id == Application.id)
            .filter(*filters)
            .order_by(sort_by)
            .offset(offset)
            .limit(limit)
        )
        if len(filters) == 0:
            total = session.query(Application).count()
        else:
            total = session.query(Application).filter(*filters).count()
        result = [
            {
                **app.__dict__,
                "latest_called_at": int(latest_called_at.timestamp())
                if latest_called_at
                else None,
            }
            for app, latest_called_at in query.all()
        ]
        return result, total


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def create_interaction(interaction: Interaction) -> Interaction:
    with SessionLocal() as session:
        session.add(interaction)
        session.commit()
        return interaction


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def update_interaction(interaction: Interaction) -> Interaction:
    with SessionLocal() as session:
        session.query(Interaction).filter(Interaction.id == interaction.id).update(
            {
                "data": interaction.data,
                "error": interaction.error,
                "usage": interaction.usage,
            },
            synchronize_session=False,
        )
        session.commit()
        return interaction


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def get_interaction(interaction_id: str) -> Interaction:
    with SessionLocal() as session:
        return (
            session.query(Interaction).filter(Interaction.id == interaction_id).first()
        )


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def get_interactions(
    application_id: str, _from: datetime, _to: datetime
) -> List[Interaction]:
    if _from > _to:
        raise InvalidParameters("from > to")
    _from = _from if _from else datetime(1970, 1, 1)
    _to = _to if _to else datetime.utcnow()
    with SessionLocal() as session:
        return (
            session.query(Interaction)
            .filter(
                Interaction.application_id == application_id,
                Interaction.created_at >= _from,
                Interaction.created_at <= _to,
            )
            .all()
        )


@retry(
    stop=stop_after_attempt(3),
    wait=wait_random(min=1, max=2),
    retry=retry_if_exception_type((OperationalError, TimeoutError)),
)
def delete_interactions(application_id: str) -> bool:
    with SessionLocal() as session:
        interactions = (
            session.query(Interaction)
            .filter(Interaction.application_id == application_id)
            .all()
        )
        if interactions:
            for interaction in interactions:
                session.delete(interaction)
            session.commit()
            return True
        else:
            return False