"""SupportTicketService — tenant-scoped support ticket operations (INDL-57).

Tenant isolation rule (same as every other module): every query here filters
`SupportTicket.tenant_id == tenant_id` / `SupportTicketMessage.tenant_id ==
tenant_id`, sourced from the caller's resolved tenant (request.state.tenant_id
via current_user.tenant_id), never from the URL or request body. A mismatch
returns 404 (never 403 — a 403 would confirm the resource exists to a caller
who has no business knowing that).
"""
from __future__ import annotations

from datetime import datetime, timezone
from typing import Tuple
from uuid import UUID

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

from src.apps.auth.models.user import User
from src.apps.support.models.support_ticket import SupportTicket
from src.apps.support.models.support_ticket_message import SupportTicketMessage
from src.core.constants import TicketStatus, UserRole
from src.core.exceptions import ConflictError, NotFoundError

SITE_ADMIN_DISPLAY_NAME = "INDELIS Support"


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

    async def _next_ticket_number(self) -> str:
        """Reads the platform-wide `support_ticket_seq` sequence (migration
        0074) and formats it as the human-readable ticket number, e.g.
        "SUP-000001". The sequence — not the ORM — is the source of
        uniqueness, so this is safe under concurrent ticket creation."""
        result = await self.db.execute(select(func.nextval("support_ticket_seq")))
        n = result.scalar_one()
        return f"SUP-{n:06d}"

    async def _resolve_author_names(self, author_ids: set[UUID]) -> dict[UUID, str]:
        if not author_ids:
            return {}
        result = await self.db.execute(
            select(User.id, User.first_name, User.last_name).where(User.id.in_(author_ids))
        )
        return {
            row.id: f"{row.first_name} {row.last_name}".strip()
            for row in result
        }

    def _serialize_message(self, message: SupportTicketMessage, author_names: dict[UUID, str]) -> dict:
        if message.author_role_snapshot == UserRole.SITE_ADMIN.value:
            author_name = SITE_ADMIN_DISPLAY_NAME
        else:
            author_name = author_names.get(message.author_user_id, "")
        return {
            "id": message.id,
            "author_user_id": message.author_user_id,
            "author_role_snapshot": message.author_role_snapshot,
            "author_name": author_name,
            "body": message.body,
            "created_at": message.created_at,
        }

    async def list(
        self,
        tenant_id: UUID,
        page: int = 1,
        page_size: int = 20,
    ) -> Tuple[list[SupportTicket], int]:
        """Tenant's own tickets, paginated, newest first."""
        where_clause = SupportTicket.tenant_id == tenant_id

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

        offset = (page - 1) * page_size
        result = await self.db.execute(
            select(SupportTicket)
            .where(where_clause)
            .order_by(SupportTicket.created_at.desc())
            .offset(offset)
            .limit(page_size)
        )
        tickets = result.scalars().all()
        return tickets, total

    async def get_by_id(self, tenant_id: UUID, ticket_id: UUID) -> dict:
        """Single ticket + full thread, oldest message first.

        404 (never 403) if the ticket does not belong to this tenant.
        """
        result = await self.db.execute(
            select(SupportTicket)
            .options(selectinload(SupportTicket.messages))
            .where(SupportTicket.id == ticket_id, SupportTicket.tenant_id == tenant_id)
        )
        ticket = result.scalar_one_or_none()
        if not ticket:
            raise NotFoundError("Support ticket not found")

        messages = sorted(ticket.messages, key=lambda m: m.created_at)
        author_names = await self._resolve_author_names(
            {m.author_user_id for m in messages if m.author_role_snapshot != UserRole.SITE_ADMIN.value}
        )

        return {
            "id": ticket.id,
            "ticket_number": ticket.ticket_number,
            "subject": ticket.subject,
            "status": ticket.status,
            "created_at": ticket.created_at,
            "messages": [self._serialize_message(m, author_names) for m in messages],
        }

    async def create(self, tenant_id: UUID, current_user, subject: str, description: str) -> SupportTicket:
        """Creates a ticket (status=open) + its first message in one
        transaction. The description is stored as message #1."""
        ticket_number = await self._next_ticket_number()
        now = datetime.now(timezone.utc)

        ticket = SupportTicket(
            tenant_id=tenant_id,
            ticket_number=ticket_number,
            subject=subject,
            status=TicketStatus.OPEN.value,
            created_by_user_id=current_user.id,
            last_message_at=now,
        )
        self.db.add(ticket)
        await self.db.flush()  # populate ticket.id without committing

        first_message = SupportTicketMessage(
            tenant_id=tenant_id,
            ticket_id=ticket.id,
            author_user_id=current_user.id,
            author_role_snapshot=current_user.role,
            body=description,
        )
        self.db.add(first_message)
        await self.db.flush()
        await self.db.refresh(ticket)
        return ticket

    async def add_message(
        self,
        tenant_id: UUID,
        ticket_id: UUID,
        current_user,
        body: str,
    ) -> SupportTicketMessage:
        """Appends a tenant-side reply to the tenant's own ticket and bumps
        last_message_at. 404 if the ticket does not belong to this tenant."""
        result = await self.db.execute(
            select(SupportTicket).where(
                SupportTicket.id == ticket_id, SupportTicket.tenant_id == tenant_id
            )
        )
        ticket = result.scalar_one_or_none()
        if not ticket:
            raise NotFoundError("Support ticket not found")
        if ticket.status == TicketStatus.CLOSED.value:
            raise ConflictError("This ticket is closed and no longer accepts replies.")

        message = SupportTicketMessage(
            tenant_id=tenant_id,
            ticket_id=ticket.id,
            author_user_id=current_user.id,
            author_role_snapshot=current_user.role,
            body=body,
        )
        self.db.add(message)

        ticket.last_message_at = datetime.now(timezone.utc)

        await self.db.flush()
        await self.db.refresh(message)
        return message