"""EnquiryService — CRUD operations on website_enquiries for site admin."""
from typing import Optional
from uuid import UUID

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

from src.apps.public.models.website_enquiry import WebsiteEnquiry
from src.core.exceptions import NotFoundError, ValidationError

VALID_STATUSES = {"new", "contacted", "qualified", "closed"}


class EnquiryService:
    def __init__(self, db: AsyncSession) -> None:
        self.db = db

    async def list(
        self,
        skip: int = 0,
        limit: int = 20,
        type_filter: Optional[str] = None,
        status_filter: Optional[str] = None,
        q: Optional[str] = None,
    ) -> tuple[list[WebsiteEnquiry], int]:
        """Paginated list of enquiries with optional filters and search."""
        conditions = []

        if type_filter:
            conditions.append(WebsiteEnquiry.type == type_filter)
        if status_filter:
            conditions.append(WebsiteEnquiry.status == status_filter)
        if q:
            term = f"%{q.lower()}%"
            conditions.append(
                or_(
                    func.lower(WebsiteEnquiry.name).like(term),
                    func.lower(WebsiteEnquiry.email).like(term),
                    func.lower(WebsiteEnquiry.organization).like(term),
                    func.lower(WebsiteEnquiry.message).like(term),
                )
            )

        base_query = select(WebsiteEnquiry)
        if conditions:
            from sqlalchemy import and_
            base_query = base_query.where(and_(*conditions))

        count_query = select(func.count(WebsiteEnquiry.id))
        if conditions:
            from sqlalchemy import and_
            count_query = count_query.where(and_(*conditions))

        count_result = await self.db.execute(count_query)
        total = count_result.scalar_one()

        query = (
            base_query
            .order_by(WebsiteEnquiry.created_at.desc())
            .offset(skip)
            .limit(limit)
        )
        result = await self.db.execute(query)
        items = list(result.scalars().all())

        return items, total

    async def get_by_id(self, enquiry_id: UUID) -> Optional[WebsiteEnquiry]:
        """Return a single enquiry by ID or None."""
        result = await self.db.execute(
            select(WebsiteEnquiry).where(WebsiteEnquiry.id == enquiry_id)
        )
        return result.scalar_one_or_none()

    async def update_status(self, enquiry_id: UUID, status: str) -> WebsiteEnquiry:
        """Update enquiry status. Raises NotFoundError if not found, ValidationError if invalid status."""
        if status not in VALID_STATUSES:
            raise ValidationError(f"status must be one of: {', '.join(sorted(VALID_STATUSES))}")

        enquiry = await self.get_by_id(enquiry_id)
        if not enquiry:
            raise NotFoundError("Enquiry not found")

        enquiry.status = status
        await self.db.flush()
        await self.db.refresh(enquiry)
        return enquiry

    async def count_new(self) -> int:
        """Return count of enquiries with status='new' for the bell badge."""
        result = await self.db.execute(
            select(func.count(WebsiteEnquiry.id)).where(WebsiteEnquiry.status == "new")
        )
        return result.scalar_one()