"""
Repository for the shared unit-of-measure catalog.
"""

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

from app.models.unit import UnitOfMeasure
from app.schemas.unit import UnitCreate, UnitUpdate


class UnitRepository:
    """Handles database queries and persistence for measurement units."""

    async def list_units(self, db: AsyncSession, active_only: bool = True) -> list[UnitOfMeasure]:
        stmt = select(UnitOfMeasure).order_by(UnitOfMeasure.name.asc())
        if active_only:
            stmt = stmt.where(UnitOfMeasure.is_active.is_(True))
        res = await db.execute(stmt)
        return list(res.scalars().all())

    async def get_by_id(self, db: AsyncSession, unit_id: object) -> UnitOfMeasure | None:
        res = await db.execute(select(UnitOfMeasure).where(UnitOfMeasure.id == unit_id))
        return res.scalar_one_or_none()

    async def get_by_name(self, db: AsyncSession, name: str) -> UnitOfMeasure | None:
        """Case-insensitive lookup — 'MG/DL' and 'mg/dL' are the same unit."""
        res = await db.execute(
            select(UnitOfMeasure).where(func.lower(UnitOfMeasure.name) == name.strip().lower())
        )
        return res.scalar_one_or_none()

    async def create_unit(self, db: AsyncSession, data: UnitCreate, is_system: bool = False) -> UnitOfMeasure:
        unit = UnitOfMeasure(
            name=data.name.strip(),
            description=data.description,
            is_active=data.is_active,
            is_system=is_system,
        )
        db.add(unit)
        await db.flush()
        await db.refresh(unit)
        return unit

    async def update_unit(self, db: AsyncSession, unit: UnitOfMeasure, data: UnitUpdate) -> UnitOfMeasure:
        if data.name is not None:
            unit.name = data.name.strip()
        if data.description is not None:
            unit.description = data.description
        if data.is_active is not None:
            unit.is_active = data.is_active
        await db.flush()
        await db.refresh(unit)
        return unit

    async def delete_unit(self, db: AsyncSession, unit: UnitOfMeasure) -> None:
        await db.delete(unit)
        await db.flush()

    async def seed_defaults(self, db: AsyncSession, names: list[str]) -> int:
        """Insert missing standard units as system rows. Returns rows added."""
        added = 0
        for name in names:
            if await self.get_by_name(db, name) is None:
                db.add(UnitOfMeasure(name=name, is_system=True, is_active=True))
                added += 1
        if added:
            await db.flush()
        return added