from typing import Optional
from uuid import UUID

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

from src.apps.plots.models.plot import Plot
from src.core.constants import PlotStatus
from src.core.exceptions import ConflictError, NotFoundError, ValidationError


class PlotService:
    def __init__(self, db: AsyncSession):
        self.db = db

    async def list_plots(
        self,
        tenant_id: UUID,
        skip: int = 0,
        limit: int = 50,
        section_id: Optional[UUID] = None,
        status: Optional[str] = None,
        search: Optional[str] = None,
    ) -> tuple[list, int]:
        conditions = [Plot.tenant_id == tenant_id]

        if section_id is not None:
            conditions.append(Plot.section_id == section_id)

        if status is not None:
            conditions.append(Plot.status == status)

        if search is not None:
            conditions.append(Plot.plot_ref.ilike(f"%{search}%"))

        count_q = await self.db.execute(
            select(func.count(Plot.id)).where(and_(*conditions))
        )
        total = count_q.scalar_one()

        result = await self.db.execute(
            select(Plot)
            .options(
                selectinload(Plot.section),
                selectinload(Plot.plot_type),
            )
            .where(and_(*conditions))
            .order_by(Plot.plot_ref.asc())
            .offset(skip)
            .limit(limit)
        )
        return result.scalars().all(), total

    async def get_by_id(self, plot_id: UUID, tenant_id: UUID) -> Optional[Plot]:
        result = await self.db.execute(
            select(Plot)
            .options(
                selectinload(Plot.section),
                selectinload(Plot.plot_type),
            )
            .where(
                and_(
                    Plot.id == plot_id,
                    Plot.tenant_id == tenant_id,
                )
            )
        )
        return result.scalar_one_or_none()

    async def create(self, tenant_id: UUID, data: dict) -> Plot:
        # Enforce plot_ref uniqueness per tenant
        existing = await self.db.execute(
            select(Plot).where(
                and_(
                    Plot.tenant_id == tenant_id,
                    Plot.plot_ref == data["plot_ref"],
                )
            )
        )
        if existing.scalar_one_or_none():
            raise ConflictError(
                f"A plot with reference '{data['plot_ref']}' already exists in this cemetery"
            )

        plot = Plot(
            tenant_id=tenant_id,
            plot_ref=data["plot_ref"],
            section_id=data.get("section_id"),
            plot_type_id=data.get("plot_type_id"),
            status=data.get("status", PlotStatus.VACANT.value),
            latitude=data.get("latitude"),
            longitude=data.get("longitude"),
            price_override=data.get("price_override"),
            notes=data.get("notes"),
            is_veteran_section=data.get("is_veteran_section", False),
        )
        self.db.add(plot)
        await self.db.flush()
        return await self.get_by_id(plot.id, tenant_id)

    async def update(self, plot_id: UUID, tenant_id: UUID, data: dict) -> Plot:
        plot = await self.get_by_id(plot_id, tenant_id)
        if not plot:
            raise NotFoundError("Plot not found")

        for field, value in data.items():
            if hasattr(plot, field):
                setattr(plot, field, value)

        await self.db.flush()
        return await self.get_by_id(plot_id, tenant_id)

    async def change_status(
        self, plot_id: UUID, tenant_id: UUID, status: str
    ) -> Plot:
        plot = await self.get_by_id(plot_id, tenant_id)
        if not plot:
            raise NotFoundError("Plot not found")

        valid_statuses = {s.value for s in PlotStatus}
        if status not in valid_statuses:
            raise ValidationError(
                f"Invalid status '{status}'. Must be one of: {', '.join(sorted(valid_statuses))}"
            )

        plot.status = status
        await self.db.flush()
        return await self.get_by_id(plot_id, tenant_id)

    async def delete(self, plot_id: UUID, tenant_id: UUID) -> None:
        plot = await self.get_by_id(plot_id, tenant_id)
        if not plot:
            raise NotFoundError("Plot not found")

        if plot.status != PlotStatus.VACANT.value:
            raise ValidationError(
                "Only vacant plots can be deleted. "
                f"This plot has status '{plot.status}'."
            )

        await self.db.delete(plot)
        await self.db.flush()