from collections.abc import Sequence
from typing import override

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from template_project.adapters.data_gateways.tables import (
    resume_education_table,
    resume_experience_table,
    resume_prediction_table,
    resume_project_table,
    resume_table,
)
from template_project.application.resume.data_gateway import (
    ResumeDataGateway,
    ResumeEducationDataGateway,
    ResumeExperienceDataGateway,
    ResumePredictionDataGateway,
    ResumeProjectDataGateway,
)
from template_project.application.resume.entity import (
    Resume,
    ResumeEducation,
    ResumeExperience,
    ResumeId,
    ResumePrediction,
    ResumeProject,
)
from template_project.application.user.entity import UserId


class DefaultResumeDataGateway(ResumeDataGateway):
    def __init__(self, session: AsyncSession) -> None:
        self._session = session

    @override
    async def load_by_resume_id(self, resume_id: ResumeId) -> Resume | None:
        return await self._session.get(Resume, resume_id)

    @override
    async def list_by_user_id(self, user_id: UserId, limit: int, offset: int) -> Sequence[Resume]:
        statement = select(Resume).where(resume_table.c.user_id == user_id).limit(limit).offset(offset)
        result = await self._session.execute(statement)
        return result.scalars().all()

    @override
    async def list_latest_by_user_id(self, user_id: UserId, limit: int, offset: int) -> Sequence[Resume]:
        statement = (
            select(Resume)
            .where(resume_table.c.user_id == user_id)
            .where(resume_table.c.up_resume_id.is_(None))
            .limit(limit)
            .offset(offset)
        )
        result = await self._session.execute(statement)
        return result.scalars().all()

    @override
    async def get_history(self, resume_id: ResumeId) -> Sequence[Resume]:
        # TODO: N+1
        history: list[Resume] = []
        current_resume = await self.load_by_resume_id(resume_id)
        if current_resume is None:
            return history
        history.append(current_resume)

        while current_resume.down_resume_id is not None:
            current_resume = await self.load_by_resume_id(current_resume.down_resume_id)
            if current_resume is None:
                break
            history.append(current_resume)

        return history


class DefaultResumePredictionDataGateway(ResumePredictionDataGateway):
    def __init__(self, session: AsyncSession) -> None:
        self._session = session

    @override
    async def load_by_resume_id(self, resume_id: ResumeId) -> ResumePrediction | None:
        statement = select(ResumePrediction).where(resume_prediction_table.c.resume_id == resume_id)
        result = await self._session.execute(statement)
        return result.scalar()


class DefaultResumeExperienceDataGateway(ResumeExperienceDataGateway):
    def __init__(self, session: AsyncSession) -> None:
        self._session = session

    @override
    async def load_by_resume_id(self, resume_id: ResumeId) -> Sequence[ResumeExperience]:
        statement = select(ResumeExperience).where(resume_experience_table.c.resume_id == resume_id)
        result = await self._session.execute(statement)
        return result.scalars().all()


class DefaultResumeEducationDataGateway(ResumeEducationDataGateway):
    def __init__(self, session: AsyncSession) -> None:
        self._session = session

    @override
    async def load_by_resume_id(self, resume_id: ResumeId) -> Sequence[ResumeEducation]:
        statement = select(ResumeEducation).where(resume_education_table.c.resume_id == resume_id)
        result = await self._session.execute(statement)
        return result.scalars().all()


class DefaultResumeProjectDataGateway(ResumeProjectDataGateway):
    def __init__(self, session: AsyncSession) -> None:
        self._session = session

    @override
    async def load_by_resume_id(self, resume_id: ResumeId) -> Sequence[ResumeProject]:
        statement = select(ResumeProject).where(resume_project_table.c.resume_id == resume_id)
        result = await self._session.execute(statement)
        return result.scalars().all()
