from datetime import date
from typing import List, Optional
from uuid import UUID

from api.ecoindex.models.responses import ApiEcoindex
from api.helper import new_uuid
from api.models.enums import Version
from ecoindex_scraper.models import Result
from sqlalchemy import func
from sqlalchemy.ext.asyncio.session import AsyncSession
from sqlalchemy.sql.expression import asc
from sqlmodel import select

from db.helper import date_filter


async def save_ecoindex_result_db(
    session: AsyncSession,
    ecoindex_result: Result,
    version: Optional[Version] = Version.v1,
) -> ApiEcoindex:
    ranking = await get_rank_analysis_db(
        ecoindex=ecoindex_result, session=session, version=version
    )
    total_results = await get_count_analysis_db(session=session, version=version)
    db_ecoindex = ApiEcoindex(
        id=new_uuid(),
        date=ecoindex_result.date,
        url=ecoindex_result.url,
        host=ecoindex_result.url.host,
        width=ecoindex_result.width,
        height=ecoindex_result.height,
        size=ecoindex_result.size,
        nodes=ecoindex_result.nodes,
        requests=ecoindex_result.requests,
        grade=ecoindex_result.grade,
        score=ecoindex_result.score,
        ges=ecoindex_result.ges,
        water=ecoindex_result.water,
        page_type=ecoindex_result.page_type,
        version=version.get_version_number(),
        initial_ranking=ranking if ranking else total_results + 1,
        initial_total_results=total_results + 1,
    )
    session.add(db_ecoindex)
    await session.commit()
    await session.refresh(db_ecoindex)

    return db_ecoindex


async def get_count_analysis_db(
    session: AsyncSession,
    version: Optional[Version] = Version.v1,
    host: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
) -> int:
    statement = f"SELECT count(*) FROM apiecoindex WHERE version = {version.get_version_number()}"

    if host:
        statement += f" AND host = '{host}'"

    if date_from:
        statement += f" AND date >= '{date_from}'"

    if date_to:
        statement += f" AND date <= '{date_to}'"

    result = await session.execute(statement=statement)

    return result.scalar()


async def get_rank_analysis_db(
    ecoindex: Result, session: AsyncSession, version: Optional[Version] = Version.v1
) -> Optional[int]:
    result = await session.execute(
        (
            "SELECT ranking FROM ("
            "SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) ranking "
            "FROM apiecoindex "
            f"WHERE version={version.get_version_number()} "
            "ORDER BY score DESC) t "
            f"WHERE score <= {ecoindex.score} "
            "LIMIT 1;"
        )
    )

    return result.scalar()


async def get_ecoindex_result_list_db(
    session: AsyncSession,
    version: Optional[Version] = Version.v1,
    host: Optional[str] = None,
    date_from: Optional[date] = None,
    date_to: Optional[date] = None,
    page: Optional[int] = 1,
    size: Optional[int] = 50,
) -> List[ApiEcoindex]:
    statement = (
        select(ApiEcoindex)
        .where(ApiEcoindex.version == version.get_version_number())
        .offset((page - 1) * size)
        .limit(size)
    )

    if host:
        statement = statement.where(ApiEcoindex.host == host)
    statement = date_filter(statement=statement, date_from=date_from, date_to=date_to)

    ecoindexes = await session.execute(statement.order_by(asc("date")))

    return ecoindexes.scalars().all()


async def get_ecoindex_result_by_id_db(
    session: AsyncSession, id: UUID, version: Optional[Version] = Version.v1
) -> ApiEcoindex:
    statement = (
        select(ApiEcoindex)
        .where(ApiEcoindex.id == id)
        .where(ApiEcoindex.version == version.get_version_number())
    )
    ecoindex = await session.execute(statement)

    return ecoindex.scalar_one_or_none()


async def get_count_daily_request_per_host(session: AsyncSession, host: str) -> int:
    statement = select(ApiEcoindex).where(
        func.date(ApiEcoindex.date) == date.today(), ApiEcoindex.host == host
    )
    results = await session.execute(statement)
    return len(results.all())

sqlalchemy.sql.expression.asc api.helper.new_uuid db.helper.date_filter sqlalchemy.func.date sqlmodel.select datetime.date.today
[((3331, 3401), 'db.helper.date_filter', 'date_filter', ([], {'statement': 'statement', 'date_from': 'date_from', 'date_to': 'date_to'}), '(statement=statement, date_from=date_from, date_to=date_to)\n', (3342, 3401), False, 'from db.helper import date_filter\n'), ((833, 843), 'api.helper.new_uuid', 'new_uuid', ([], {}), '()\n', (841, 843), False, 'from api.helper import new_uuid\n'), ((4001, 4020), 'sqlmodel.select', 'select', (['ApiEcoindex'], {}), '(ApiEcoindex)\n', (4007, 4020), False, 'from sqlmodel import select\n'), ((4036, 4063), 'sqlalchemy.func.date', 'func.date', (['ApiEcoindex.date'], {}), '(ApiEcoindex.date)\n', (4045, 4063), False, 'from sqlalchemy import func\n'), ((4067, 4079), 'datetime.date.today', 'date.today', ([], {}), '()\n', (4077, 4079), False, 'from datetime import date\n'), ((3461, 3472), 'sqlalchemy.sql.expression.asc', 'asc', (['"""date"""'], {}), "('date')\n", (3464, 3472), False, 'from sqlalchemy.sql.expression import asc\n'), ((3677, 3696), 'sqlmodel.select', 'select', (['ApiEcoindex'], {}), '(ApiEcoindex)\n', (3683, 3696), False, 'from sqlmodel import select\n'), ((3089, 3108), 'sqlmodel.select', 'select', (['ApiEcoindex'], {}), '(ApiEcoindex)\n', (3095, 3108), False, 'from sqlmodel import select\n')]