"""
Repository for test catalog operations (categories, tests, parameters, and ranges).
"""

from datetime import UTC, datetime
from uuid import UUID

from sqlalchemy import ColumnElement, func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.test import Test, TestCategory, TestParameter, TestParameterRange
from app.schemas.test import (
    TestCategoryCreate,
    TestCategoryUpdate,
    TestCreate,
    TestParameterCreate,
    TestParameterRangeCreate,
    TestParameterRangeUpdate,
    TestParameterUpdate,
    TestUpdate,
)


class TestCatalogRepository:
    """Handles database queries and persistence for test catalog entities."""

    # ── Category Operations ────────────────────────────────────────────────────

    async def get_category_by_id(self, db: AsyncSession, category_id: UUID) -> TestCategory | None:
        stmt = select(TestCategory).where(
            TestCategory.id == category_id,
            TestCategory.deleted_at.is_(None),
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_category_by_name(self, db: AsyncSession, lab_id: UUID, name: str) -> TestCategory | None:
        stmt = select(TestCategory).where(
            TestCategory.lab_id == lab_id,
            TestCategory.name == name,
            TestCategory.deleted_at.is_(None),
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_category_by_name_including_deleted(
        self, db: AsyncSession, lab_id: UUID, name: str
    ) -> TestCategory | None:
        stmt = select(TestCategory).where(
            TestCategory.lab_id == lab_id,
            TestCategory.name == name,
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def restore_category_from_create(
        self, db: AsyncSession, category: TestCategory, data: TestCategoryCreate
    ) -> TestCategory:
        category.deleted_at = None
        category.description = data.description
        category.modality = data.modality
        category.is_active = data.is_active
        await db.flush()
        await db.refresh(category)
        return category

    async def list_categories(
        self,
        db: AsyncSession,
        limit: int = 50,
        offset: int = 0,
        lab_id: UUID | None = None,
    ) -> tuple[list[TestCategory], int]:
        filters: list[ColumnElement[bool]] = [TestCategory.deleted_at.is_(None)]
        if lab_id is not None:
            filters.append(TestCategory.lab_id == lab_id)

        count_stmt = select(func.count()).select_from(TestCategory).where(*filters)
        total: int = (await db.execute(count_stmt)).scalar_one()

        data_stmt = (
            select(TestCategory).where(*filters).order_by(TestCategory.name.asc()).offset(offset).limit(limit)
        )
        res = await db.execute(data_stmt)
        return list(res.scalars().all()), total

    async def create_category(self, db: AsyncSession, data: TestCategoryCreate) -> TestCategory:
        category = TestCategory(
            lab_id=data.lab_id,
            name=data.name,
            description=data.description,
            modality=data.modality,
            is_active=data.is_active,
        )
        db.add(category)
        await db.flush()
        await db.refresh(category)
        return category

    async def update_category(
        self, db: AsyncSession, category: TestCategory, data: TestCategoryUpdate
    ) -> TestCategory:
        if data.name is not None:
            category.name = data.name
        if data.description is not None:
            category.description = data.description
        if data.modality is not None:
            category.modality = data.modality
        if data.is_active is not None:
            category.is_active = data.is_active

        await db.flush()
        await db.refresh(category)
        return category

    async def delete_category(self, db: AsyncSession, category: TestCategory) -> None:
        category.deleted_at = datetime.now(UTC)
        await db.flush()

    # ── Test Operations ────────────────────────────────────────────────────────

    async def get_test_by_id(
        self, db: AsyncSession, test_id: UUID, lab_id: UUID | None = None
    ) -> Test | None:
        stmt = select(Test).where(Test.id == test_id, Test.deleted_at.is_(None))
        if lab_id is not None:
            stmt = stmt.where(Test.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_test_with_parameters(self, db: AsyncSession, test_id: UUID) -> Test | None:
        stmt = (
            select(Test)
            .where(Test.id == test_id, Test.deleted_at.is_(None))
            .options(selectinload(Test.parameters))
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_test_with_parameters_and_ranges(self, db: AsyncSession, test_id: UUID) -> Test | None:
        stmt = (
            select(Test)
            .where(Test.id == test_id, Test.deleted_at.is_(None))
            .options(selectinload(Test.parameters).selectinload(TestParameter.ranges))
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_test_by_code(self, db: AsyncSession, lab_id: UUID, code: str) -> Test | None:
        stmt = select(Test).where(
            Test.lab_id == lab_id,
            Test.code == code,
            Test.deleted_at.is_(None),
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_test_by_code_including_deleted(
        self, db: AsyncSession, lab_id: UUID, code: str
    ) -> Test | None:
        stmt = select(Test).where(Test.lab_id == lab_id, Test.code == code)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def restore_test_from_create(self, db: AsyncSession, test: Test, data: TestCreate) -> Test:
        """Re-activate a soft-deleted test and apply catalog fields from create payload."""
        test.deleted_at = None
        test.category_id = data.category_id
        test.name = data.name
        test.description = data.description
        test.price = data.price
        test.result_type = data.result_type
        test.is_active = data.is_active
        await db.flush()
        await db.refresh(test)
        return test

    async def list_tests(
        self,
        db: AsyncSession,
        limit: int = 50,
        offset: int = 0,
        lab_id: UUID | None = None,
        category_id: UUID | None = None,
        q: str | None = None,
    ) -> tuple[list[Test], int]:
        filters: list[ColumnElement[bool]] = [Test.deleted_at.is_(None)]
        if lab_id is not None:
            filters.append(Test.lab_id == lab_id)
        if category_id is not None:
            filters.append(Test.category_id == category_id)
        if q:
            term = f"%{q.strip()}%"
            filters.append(or_(Test.name.ilike(term), Test.code.ilike(term)))

        count_stmt = select(func.count()).select_from(Test).where(*filters)
        total: int = (await db.execute(count_stmt)).scalar_one()

        data_stmt = (
            select(Test)
            .options(selectinload(Test.parameters), selectinload(Test.category))
            .where(*filters)
            .order_by(Test.name.asc())
            .offset(offset)
            .limit(limit)
        )
        res = await db.execute(data_stmt)
        return list(res.scalars().all()), total

    async def create_test(self, db: AsyncSession, data: TestCreate) -> Test:
        test = Test(
            lab_id=data.lab_id,
            category_id=data.category_id,
            name=data.name,
            code=data.code,
            description=data.description,
            price=data.price,
            result_type=data.result_type,
            is_active=data.is_active,
        )
        db.add(test)
        await db.flush()
        await db.refresh(test)
        return test

    async def update_test(self, db: AsyncSession, test: Test, data: TestUpdate) -> Test:
        if data.category_id is not None:
            test.category_id = data.category_id
        if data.name is not None:
            test.name = data.name
        if data.code is not None:
            test.code = data.code
        if data.description is not None:
            test.description = data.description
        if data.price is not None:
            test.price = data.price
        if data.result_type is not None:
            test.result_type = data.result_type
        if data.is_active is not None:
            test.is_active = data.is_active

        await db.flush()
        await db.refresh(test)
        return test

    async def get_test_order_count(self, db: AsyncSession, test_id: UUID) -> int:
        from app.models.order import OrderTest

        stmt = select(func.count()).select_from(OrderTest).where(OrderTest.test_id == test_id)
        return (await db.execute(stmt)).scalar_one()

    async def hard_delete_test(self, db: AsyncSession, test: Test) -> None:
        from app.models.package import TestPackageItem

        pkg_stmt = select(TestPackageItem).where(TestPackageItem.test_id == test.id)
        pkg_res = await db.execute(pkg_stmt)
        for item in pkg_res.scalars().all():
            await db.delete(item)

        param_stmt = select(TestParameter).where(TestParameter.test_id == test.id)
        param_res = await db.execute(param_stmt)
        for param in param_res.scalars().all():
            range_stmt = select(TestParameterRange).where(TestParameterRange.parameter_id == param.id)
            range_res = await db.execute(range_stmt)
            for r in range_res.scalars().all():
                await db.delete(r)
            await db.delete(param)

        await db.delete(test)
        await db.flush()

    async def delete_test(self, db: AsyncSession, test: Test) -> None:
        test.deleted_at = datetime.now(UTC)
        await db.flush()

    # ── Parameter Operations ──────────────────────────────────────────────────

    async def list_parameters(self, db: AsyncSession, test_id: UUID) -> list[TestParameter]:
        stmt = (
            select(TestParameter)
            .where(TestParameter.test_id == test_id, TestParameter.deleted_at.is_(None))
            .order_by(TestParameter.sort_order.asc(), TestParameter.created_at.asc())
        )
        res = await db.execute(stmt)
        return list(res.scalars().all())

    async def get_parameter_by_id(self, db: AsyncSession, param_id: UUID) -> TestParameter | None:
        stmt = select(TestParameter).where(
            TestParameter.id == param_id,
            TestParameter.deleted_at.is_(None),
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def create_parameter(
        self, db: AsyncSession, test_id: UUID, data: TestParameterCreate
    ) -> TestParameter:
        param = TestParameter(
            test_id=test_id,
            name=data.name,
            unit=data.unit,
            price=data.price,
            decimal_places=data.decimal_places,
            calc_factor=data.calc_factor,
            method_note=data.method_note,
            sort_order=data.sort_order,
            is_active=data.is_active,
        )
        db.add(param)
        await db.flush()
        await db.refresh(param)
        return param

    async def update_parameter(
        self, db: AsyncSession, param: TestParameter, data: TestParameterUpdate
    ) -> TestParameter:
        if data.name is not None:
            param.name = data.name
        if data.unit is not None:
            param.unit = data.unit
        if data.price is not None:
            param.price = data.price
        if data.decimal_places is not None:
            param.decimal_places = data.decimal_places
        if data.calc_factor is not None:
            param.calc_factor = data.calc_factor
        if data.method_note is not None:
            param.method_note = data.method_note
        if data.sort_order is not None:
            param.sort_order = data.sort_order
        if data.is_active is not None:
            param.is_active = data.is_active

        await db.flush()
        await db.refresh(param)
        return param

    async def delete_parameter(self, db: AsyncSession, param: TestParameter) -> None:
        param.deleted_at = datetime.now(UTC)
        await db.flush()

    # ── Reference Range Operations ────────────────────────────────────────────

    async def list_ranges(self, db: AsyncSession, param_id: UUID) -> list[TestParameterRange]:
        stmt = (
            select(TestParameterRange)
            .where(TestParameterRange.parameter_id == param_id)
            .order_by(TestParameterRange.created_at.asc())
        )
        res = await db.execute(stmt)
        return list(res.scalars().all())

    async def get_range_by_id(self, db: AsyncSession, range_id: UUID) -> TestParameterRange | None:
        stmt = select(TestParameterRange).where(TestParameterRange.id == range_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def create_range(
        self, db: AsyncSession, param_id: UUID, data: TestParameterRangeCreate
    ) -> TestParameterRange:
        rng = TestParameterRange(
            parameter_id=param_id,
            gender=data.gender,
            age_min=data.age_min,
            age_max=data.age_max,
            numeric_low=data.numeric_low,
            numeric_high=data.numeric_high,
            text_normal=data.text_normal,
        )
        db.add(rng)
        await db.flush()
        await db.refresh(rng)
        return rng

    async def update_range(
        self, db: AsyncSession, rng: TestParameterRange, data: TestParameterRangeUpdate
    ) -> TestParameterRange:
        if data.gender is not None:
            rng.gender = data.gender
        if data.age_min is not None:
            rng.age_min = data.age_min
        if data.age_max is not None:
            rng.age_max = data.age_max
        if data.numeric_low is not None:
            rng.numeric_low = data.numeric_low
        if data.numeric_high is not None:
            rng.numeric_high = data.numeric_high
        if data.text_normal is not None:
            rng.text_normal = data.text_normal

        await db.flush()
        await db.refresh(rng)
        return rng

    async def delete_range(self, db: AsyncSession, rng: TestParameterRange) -> None:
        await db.delete(rng)
        await db.flush()