"""
Repository for health test packages and package items.
"""

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.package import TestPackage, TestPackageItem
from app.schemas.package import TestPackageCreate, TestPackageUpdate


class PackageRepository:
    """Repository handling TestPackage and TestPackageItem persistence."""

    async def get_by_id(
        self, db: AsyncSession, package_id: UUID, lab_id: UUID | None = None
    ) -> TestPackage | None:
        """Fetch a non-deleted package by its ID."""
        stmt = select(TestPackage).where(
            TestPackage.id == package_id,
            TestPackage.deleted_at.is_(None),
        )
        if lab_id is not None:
            stmt = stmt.where(TestPackage.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_with_items(
        self, db: AsyncSession, package_id: UUID, lab_id: UUID | None = None
    ) -> TestPackage | None:
        """Fetch a package with all its included test items."""
        stmt = (
            select(TestPackage)
            .where(TestPackage.id == package_id, TestPackage.deleted_at.is_(None))
            .options(
                selectinload(TestPackage.items).selectinload(TestPackageItem.test),
                selectinload(TestPackage.items).selectinload(TestPackageItem.parameter),
            )
        )
        if lab_id is not None:
            stmt = stmt.where(TestPackage.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_by_code(self, db: AsyncSession, lab_id: UUID | None, code: str) -> TestPackage | None:
        """Fetch a package by lab and code."""
        stmt = select(TestPackage).where(
            TestPackage.lab_id == lab_id,
            TestPackage.code == code,
            TestPackage.deleted_at.is_(None),
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def list_packages(
        self,
        db: AsyncSession,
        limit: int = 50,
        offset: int = 0,
        lab_id: UUID | None = None,
        is_active: bool | None = None,
        q: str | None = None,
    ) -> tuple[list[TestPackage], int]:
        """Fetch paginated packages with optional filtering."""
        filters: list[ColumnElement[bool]] = [TestPackage.deleted_at.is_(None)]

        if lab_id is not None:
            filters.append(TestPackage.lab_id == lab_id)
        if is_active is not None:
            filters.append(TestPackage.is_active == is_active)
        if q:
            term = f"%{q.strip()}%"
            filters.append(or_(TestPackage.name.ilike(term), TestPackage.code.ilike(term)))

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

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

    async def create(self, db: AsyncSession, data: TestPackageCreate) -> TestPackage:
        """Create and persist a new health package."""
        pkg = TestPackage(
            lab_id=data.lab_id,
            category_id=data.category_id,
            name=data.name,
            code=data.code,
            description=data.description,
            price=data.price,
            is_active=data.is_active,
        )
        db.add(pkg)
        await db.flush()
        await db.refresh(pkg)
        return pkg

    async def update(self, db: AsyncSession, pkg: TestPackage, data: TestPackageUpdate) -> TestPackage:
        """Update fields on a package."""
        fields_set = data.model_fields_set
        if "category_id" in fields_set:
            pkg.category_id = data.category_id
        if "name" in fields_set and data.name is not None:
            pkg.name = data.name
        if "code" in fields_set:
            pkg.code = data.code
        if "description" in fields_set:
            pkg.description = data.description
        if "price" in fields_set and data.price is not None:
            pkg.price = data.price
        if "is_active" in fields_set and data.is_active is not None:
            pkg.is_active = data.is_active

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

    async def delete(self, db: AsyncSession, pkg: TestPackage) -> None:
        """Soft-delete a health package."""
        pkg.deleted_at = datetime.now(UTC)
        await db.flush()

    async def get_by_code_including_deleted(
        self, db: AsyncSession, lab_id: UUID | None, code: str
    ) -> TestPackage | None:
        """Fetch a package by lab and code, including soft-deleted rows."""
        stmt = select(TestPackage).where(
            TestPackage.lab_id == lab_id,
            TestPackage.code == code,
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def restore_from_create(
        self, db: AsyncSession, pkg: TestPackage, data: TestPackageCreate
    ) -> TestPackage:
        """Re-activate a soft-deleted package and apply fields from create payload."""
        pkg.deleted_at = None
        pkg.category_id = data.category_id
        pkg.name = data.name
        pkg.description = data.description
        pkg.price = data.price
        pkg.is_active = data.is_active
        await db.flush()
        await db.refresh(pkg)
        return pkg

    async def get_package_order_count(self, db: AsyncSession, package_id: UUID) -> int:
        """Count orders referencing this package (history that must be preserved)."""
        from app.models.order import OrderPackage

        stmt = select(func.count()).select_from(OrderPackage).where(OrderPackage.package_id == package_id)
        return (await db.execute(stmt)).scalar_one()

    async def hard_delete_package(self, db: AsyncSession, pkg: TestPackage) -> None:
        """Permanently remove a never-ordered package and its items."""
        items_stmt = select(TestPackageItem).where(TestPackageItem.package_id == pkg.id)
        items_res = await db.execute(items_stmt)
        for item in items_res.scalars().all():
            await db.delete(item)
        await db.delete(pkg)
        await db.flush()

    # ── Item Operations ───────────────────────────────────────────────────────

    async def add_item(
        self,
        db: AsyncSession,
        package_id: UUID,
        test_id: UUID,
        parameter_id: UUID | None = None,
        sort_order: int = 0,
    ) -> TestPackageItem:
        """Add a test or specific analyte to a package."""
        item = TestPackageItem(
            package_id=package_id,
            test_id=test_id,
            parameter_id=parameter_id,
            sort_order=sort_order,
        )
        db.add(item)
        await db.flush()
        await db.refresh(item)
        return item

    async def get_item(self, db: AsyncSession, package_id: UUID, item_id: UUID) -> TestPackageItem | None:
        """Fetch a specific package item by primary key."""
        stmt = (
            select(TestPackageItem)
            .where(
                TestPackageItem.id == item_id,
                TestPackageItem.package_id == package_id,
            )
            .options(
                selectinload(TestPackageItem.test),
                selectinload(TestPackageItem.parameter),
            )
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_item_by_test(
        self,
        db: AsyncSession,
        package_id: UUID,
        test_id: UUID,
        parameter_id: UUID | None = None,
    ) -> TestPackageItem | None:
        """Check if a test/analyte is already in the package."""
        filters = [
            TestPackageItem.package_id == package_id,
            TestPackageItem.test_id == test_id,
        ]
        if parameter_id is None:
            filters.append(TestPackageItem.parameter_id.is_(None))
        else:
            filters.append(TestPackageItem.parameter_id == parameter_id)

        stmt = select(TestPackageItem).where(*filters)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def remove_item(self, db: AsyncSession, item: TestPackageItem) -> None:
        """Hard delete a test from a package."""
        await db.delete(item)
        await db.flush()