import logging
from typing import Optional
from uuid import UUID

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

from src.apps.plots.models.plot import Plot
from src.apps.plots.models.plot_status import PlotStatusCatalog
from src.core.exceptions import ConflictError, NotFoundError

logger = logging.getLogger(__name__)

# The 4 defaults every tenant gets, keyed to the canonical plots.status enum.
# (name, color_hex, status_key, sort_order)
DEFAULT_STATUSES = [
    ("Available", "#16a34a", "vacant", 0),
    ("Reserved", "#d97706", "reserved", 1),
    ("Interred", "#4f46e5", "occupied", 2),
    ("On hold", "#64748b", "hold", 3),
]


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

    async def _seed_defaults(self, tenant_id: UUID) -> None:
        """Create the 4 default statuses for a tenant that has none yet (lazy seed).

        Guarantees every tenant exposes the standard catalog even if it was
        provisioned before this feature shipped, without a provisioning-job change.
        """
        for name, color_hex, status_key, sort_order in DEFAULT_STATUSES:
            self.db.add(
                PlotStatusCatalog(
                    tenant_id=tenant_id,
                    name=name,
                    color_hex=color_hex,
                    status_key=status_key,
                    is_default=True,
                    sort_order=sort_order,
                )
            )
        await self.db.flush()

    async def list_or_seed(self, tenant_id: UUID) -> list[PlotStatusCatalog]:
        result = await self.db.execute(
            select(PlotStatusCatalog)
            .where(PlotStatusCatalog.tenant_id == tenant_id)
            .order_by(PlotStatusCatalog.sort_order.asc(), PlotStatusCatalog.name.asc())
        )
        rows = result.scalars().all()
        if not rows:
            await self._seed_defaults(tenant_id)
            result = await self.db.execute(
                select(PlotStatusCatalog)
                .where(PlotStatusCatalog.tenant_id == tenant_id)
                .order_by(
                    PlotStatusCatalog.sort_order.asc(), PlotStatusCatalog.name.asc()
                )
            )
            rows = result.scalars().all()
        return rows

    async def get_by_id(
        self, status_id: UUID, tenant_id: UUID
    ) -> Optional[PlotStatusCatalog]:
        result = await self.db.execute(
            select(PlotStatusCatalog).where(
                and_(
                    PlotStatusCatalog.id == status_id,
                    PlotStatusCatalog.tenant_id == tenant_id,
                )
            )
        )
        return result.scalar_one_or_none()

    async def create(self, tenant_id: UUID, data: dict) -> PlotStatusCatalog:
        existing = await self.db.execute(
            select(PlotStatusCatalog).where(
                and_(
                    PlotStatusCatalog.tenant_id == tenant_id,
                    func.lower(PlotStatusCatalog.name) == data["name"].lower(),
                )
            )
        )
        if existing.scalar_one_or_none():
            raise ConflictError(
                f"A status named '{data['name']}' already exists in this cemetery"
            )

        # is_default is server-controlled: clients may only create custom statuses
        # (status_key stays NULL — never wired to the canonical enum) (API3).
        status = PlotStatusCatalog(
            tenant_id=tenant_id,
            name=data["name"],
            color_hex=data["color_hex"],
            status_key=None,
            is_default=False,
            sort_order=data.get("sort_order", 0),
        )
        self.db.add(status)
        await self.db.flush()
        return status

    async def update(
        self, status_id: UUID, tenant_id: UUID, data: dict
    ) -> PlotStatusCatalog:
        status = await self.get_by_id(status_id, tenant_id)
        if not status:
            raise NotFoundError("Status not found")

        new_name = data.get("name")
        if new_name and new_name.lower() != status.name.lower():
            clash = await self.db.execute(
                select(PlotStatusCatalog).where(
                    and_(
                        PlotStatusCatalog.tenant_id == tenant_id,
                        func.lower(PlotStatusCatalog.name) == new_name.lower(),
                        PlotStatusCatalog.id != status_id,
                    )
                )
            )
            if clash.scalar_one_or_none():
                raise ConflictError(
                    f"A status named '{new_name}' already exists in this cemetery"
                )

        for field in ("name", "color_hex", "sort_order"):
            if data.get(field) is not None:
                setattr(status, field, data[field])

        await self.db.flush()
        return status

    async def delete(self, status_id: UUID, tenant_id: UUID) -> None:
        status = await self.get_by_id(status_id, tenant_id)
        if not status:
            raise NotFoundError("Status not found")

        # Blocked while any plot uses this status (referential integrity / SAC-05).
        # A catalog row is "referenced" when a plot carries its status_key.
        if status.status_key is not None:
            ref = await self.db.execute(
                select(func.count(Plot.id)).where(
                    and_(
                        Plot.tenant_id == tenant_id,
                        Plot.status == status.status_key,
                    )
                )
            )
            if int(ref.scalar_one()) > 0:
                raise ConflictError(
                    "This status is in use by one or more plots and cannot be deleted."
                )

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