"""
Discount rule repository for database operations.
"""

from datetime import UTC, datetime
from uuid import UUID

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

from app.models.billing import DiscountRule
from app.schemas.billing import DiscountRuleCreate, DiscountRuleUpdate


class DiscountRepository:
    """Repository handling DiscountRule persistence and queries."""

    async def get_by_id(
        self,
        db: AsyncSession,
        rule_id: UUID,
        lab_id: UUID | None = None,
    ) -> DiscountRule | None:
        """Fetch a non-deleted discount rule by ID, optionally scoped to lab."""
        stmt = select(DiscountRule).where(
            DiscountRule.id == rule_id,
            DiscountRule.deleted_at.is_(None),
        )
        if lab_id is not None:
            stmt = stmt.where(DiscountRule.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_by_name(
        self,
        db: AsyncSession,
        lab_id: UUID,
        name: str,
    ) -> DiscountRule | None:
        """Fetch an active discount rule by exact/case-insensitive name within a lab branch."""
        stmt = select(DiscountRule).where(
            DiscountRule.lab_id == lab_id,
            func.lower(DiscountRule.name) == name.strip().lower(),
            DiscountRule.deleted_at.is_(None),
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def list_rules(
        self,
        db: AsyncSession,
        lab_id: UUID,
        is_active: bool | None = None,
        search: str | None = None,
        offset: int = 0,
        limit: int = 50,
    ) -> tuple[list[DiscountRule], int]:
        """List discount rules scoped to a lab with filtering, search, and pagination."""
        base_filters: list[ColumnElement[bool]] = [
            DiscountRule.lab_id == lab_id,
            DiscountRule.deleted_at.is_(None),
        ]

        if is_active is not None:
            base_filters.append(DiscountRule.is_active == is_active)

        if search:
            s = f"%{search.strip()}%"
            base_filters.append(
                or_(
                    DiscountRule.name.ilike(s),
                    DiscountRule.code.ilike(s),
                )
            )

        # Count total
        count_stmt = select(func.count(DiscountRule.id)).where(*base_filters)
        total_res = await db.execute(count_stmt)
        total = total_res.scalar_one()

        # Query records
        query_stmt = (
            select(DiscountRule)
            .where(*base_filters)
            .order_by(DiscountRule.created_at.desc())
            .offset(offset)
            .limit(limit)
        )
        query_res = await db.execute(query_stmt)
        items = list(query_res.scalars().all())

        return items, total

    async def create(
        self,
        db: AsyncSession,
        lab_id: UUID,
        obj_in: DiscountRuleCreate,
        *,
        commit: bool = True,
    ) -> DiscountRule:
        """Create and persist a new discount rule."""
        rule = DiscountRule(
            lab_id=lab_id,
            name=obj_in.name.strip(),
            code=obj_in.code.strip() if obj_in.code else None,
            discount_type=obj_in.discount_type,
            discount_value=obj_in.discount_value,
            is_active=obj_in.is_active,
            description=obj_in.description.strip() if obj_in.description else None,
        )
        db.add(rule)
        if commit:
            await db.commit()
            await db.refresh(rule)
        else:
            await db.flush()
        return rule

    async def update(
        self,
        db: AsyncSession,
        db_obj: DiscountRule,
        obj_in: DiscountRuleUpdate,
        *,
        commit: bool = True,
    ) -> DiscountRule:
        """Update an existing discount rule."""
        update_data = obj_in.model_dump(exclude_unset=True)
        for field, value in update_data.items():
            if isinstance(value, str):
                value = value.strip()
            setattr(db_obj, field, value)

        if commit:
            await db.commit()
            await db.refresh(db_obj)
        else:
            await db.flush()
        return db_obj

    async def soft_delete(self, db: AsyncSession, db_obj: DiscountRule, *, commit: bool = True) -> None:
        """Soft-delete a discount rule by setting deleted_at."""
        db_obj.deleted_at = datetime.now(UTC)
        if commit:
            await db.commit()
        else:
            await db.flush()