import logging
from collections.abc import Sequence

import structlog
from sqlalchemy import Select, text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import with_loader_criteria

from csirtmu.self_service_backend import models
from csirtmu.self_service_backend.config import DatabaseConfig

logger: logging.Logger = structlog.getLogger()


STEP_LIST = [models.TextStep, models.LinkStep, models.FormStep, models.NetboxSearchStep, models.OutlineSearchStep]
type STEP_TYPES = (
    models.TextStep | models.LinkStep | models.FormStep | models.NetboxSearchStep | models.OutlineSearchStep
)
type SELECT_TYPE = (
    Select[tuple[models.TextStep]]
    | Select[tuple[models.LinkStep]]
    | Select[tuple[models.FormStep]]
    | Select[tuple[models.NetboxSearchStep]]
    | Select[tuple[models.OutlineSearchStep]]
)


class StepRepository:
    MALFORMED_DB_ERROR = "Malformed database: Found multiple initial steps."
    UPDATE_STEP_NEXT_ID_VALUE_QUERY = "SELECT setval('step_id_seq', (SELECT MAX(id) FROM step))"

    def __init__(self, config: DatabaseConfig) -> None:
        self.session_factory = self.build_session_factory(config)

    @staticmethod
    def build_session_factory(
        config: DatabaseConfig,
    ) -> async_sessionmaker[AsyncSession]:
        return async_sessionmaker(create_async_engine(config.engine_url))

    @staticmethod
    def build_queries(language: str | None, prev_id: int | None = -1) -> Sequence[SELECT_TYPE]:
        queries = (
            [step_type.build_query() for step_type in STEP_LIST]
            if prev_id == -1
            else [step_type.build_query().where(step_type.prev_id == prev_id) for step_type in STEP_LIST]
        )
        if language:
            queries = [
                q.options(
                    with_loader_criteria(
                        models.Translation,
                        models.Translation.language == language,
                        include_aliases=True,
                    )
                )
                for q in queries
            ]
        for query in queries:
            logger.debug(query.compile())
        return queries

    async def _get_all_steps(self, queries: Sequence[SELECT_TYPE]) -> Sequence[models.Step]:
        steps: list[models.Step] = []
        async with self.session_factory() as session:
            for query in queries:
                steps += list((await session.execute(query)).scalars().all())
        return steps

    async def get_all_steps(self, language: str | None) -> Sequence[models.Step]:
        return await self._get_all_steps(self.build_queries(language))

    async def insert_steps(self, steps: Sequence[models.Step]) -> None:
        async with self.session_factory() as session:
            session.add_all(steps)
            await session.commit()
            await session.execute(text(self.UPDATE_STEP_NEXT_ID_VALUE_QUERY))

    async def get_next_steps(self, language: str, step_id: int) -> Sequence[models.Step]:
        return await self._get_all_steps(self.build_queries(language, step_id))

    async def get_first_step(self, language: str) -> models.Step | None:
        steps = await self._get_all_steps(self.build_queries(language, None))
        if len(steps) == 1:
            return steps[0]
        if len(steps) > 1:
            raise RuntimeError(self.MALFORMED_DB_ERROR)
        return None

    async def add_step(self, step: models.Step) -> None:
        async with self.session_factory() as session:
            session.add(step)
            await session.commit()

    @staticmethod
    def _build_text(text_en: str, text_cz: str) -> models.Text:
        return models.Text(
            translations=[
                models.Translation(
                    language=models.Language.EN,
                    contents=text_en,
                ),
                models.Translation(
                    language=models.Language.CZ,
                    contents=text_cz,
                ),
            ]
        )
