import logging
from typing import Optional
from uuid import UUID

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

from src.apps.crew_members.models.crew_member import CrewMember
from src.core.exceptions import ConflictError, NotFoundError

logger = logging.getLogger(__name__)


class CrewMemberService:
    """Business logic for the crew members roster (INDL-33).

    Every query is scoped to the caller's tenant AND filtered to non-deleted
    rows (`deleted_at IS NULL`). PII (name/email/phone/address) is never logged —
    only the crew member UUID is referenced in log lines (CIS 3 / ASVS V7.1.1).
    """

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

    def _search_conditions(self, search: str):
        """Parameterised ILIKE across name, email, phone (AC-11)."""
        like = f"%{search}%"
        return or_(
            CrewMember.name.ilike(like),
            CrewMember.email.ilike(like),
            CrewMember.phone.ilike(like),
        )

    async def list(
        self,
        tenant_id: UUID,
        search: Optional[str] = None,
        skip: int = 0,
        limit: int = 20,
    ) -> tuple[list[CrewMember], int, int]:
        """Return (items, total, available_count) for the tenant.

        Both counts honour the current search filter so the summary line stays
        consistent with the paginated table (e.g. "Crew · 2 members, 1 available"
        when a search narrows the set). `available_count` spans all matching
        pages, not just the current one.
        """
        conditions = [
            CrewMember.tenant_id == tenant_id,
            CrewMember.deleted_at.is_(None),
        ]
        if search:
            conditions.append(self._search_conditions(search))

        where_clause = and_(*conditions)

        total = (
            await self.db.execute(select(func.count(CrewMember.id)).where(where_clause))
        ).scalar_one()

        available_count = (
            await self.db.execute(
                select(func.count(CrewMember.id)).where(
                    and_(where_clause, CrewMember.is_available.is_(True))
                )
            )
        ).scalar_one()

        result = await self.db.execute(
            select(CrewMember)
            .where(where_clause)
            .order_by(CrewMember.name.asc())
            .offset(skip)
            .limit(limit)
        )
        return list(result.scalars().all()), total, available_count

    async def get_by_id(self, tenant_id: UUID, crew_member_id: UUID) -> Optional[CrewMember]:
        result = await self.db.execute(
            select(CrewMember).where(
                and_(
                    CrewMember.id == crew_member_id,
                    CrewMember.tenant_id == tenant_id,
                    CrewMember.deleted_at.is_(None),
                )
            )
        )
        return result.scalar_one_or_none()

    async def _email_exists(
        self, tenant_id: UUID, email: str, exclude_id: Optional[UUID] = None
    ) -> bool:
        conditions = [
            CrewMember.tenant_id == tenant_id,
            func.lower(CrewMember.email) == email.lower(),
            CrewMember.deleted_at.is_(None),
        ]
        if exclude_id is not None:
            conditions.append(CrewMember.id != exclude_id)
        existing = (
            await self.db.execute(select(CrewMember.id).where(and_(*conditions)))
        ).scalar_one_or_none()
        return existing is not None

    async def create(self, tenant_id: UUID, data: dict) -> CrewMember:
        if await self._email_exists(tenant_id, data["email"]):
            raise ConflictError("A crew member with this email already exists")

        member = CrewMember(
            tenant_id=tenant_id,
            name=data["name"],
            email=data["email"],
            phone=data["phone"],
            address=data["address"],
        )
        self.db.add(member)
        await self.db.flush()
        await self.db.refresh(member)
        logger.info("crew_member.created id=%s tenant=%s", member.id, tenant_id)
        return member

    async def update(self, tenant_id: UUID, crew_member_id: UUID, data: dict) -> CrewMember:
        member = await self.get_by_id(tenant_id, crew_member_id)
        if not member:
            raise NotFoundError("Crew member not found")

        if "email" in data and await self._email_exists(
            tenant_id, data["email"], exclude_id=crew_member_id
        ):
            raise ConflictError("A crew member with this email already exists")

        for field in ("name", "email", "phone", "address"):
            if field in data:
                setattr(member, field, data[field])

        await self.db.flush()
        await self.db.refresh(member)
        logger.info("crew_member.updated id=%s tenant=%s", member.id, tenant_id)
        return member

    async def toggle_availability(self, tenant_id: UUID, crew_member_id: UUID) -> CrewMember:
        member = await self.get_by_id(tenant_id, crew_member_id)
        if not member:
            raise NotFoundError("Crew member not found")

        member.is_available = not member.is_available
        await self.db.flush()
        await self.db.refresh(member)
        logger.info(
            "crew_member.availability_toggled id=%s tenant=%s available=%s",
            member.id,
            tenant_id,
            member.is_available,
        )
        return member

    async def delete(self, tenant_id: UUID, crew_member_id: UUID) -> None:
        member = await self.get_by_id(tenant_id, crew_member_id)
        if not member:
            raise NotFoundError("Crew member not found")

        member.soft_delete()
        await self.db.flush()
        logger.info("crew_member.deleted id=%s tenant=%s", crew_member_id, tenant_id)