"""SiteAdminSupportService — cross-tenant support ticket queue (INDL-57).

This is the ONLY code path in the Support Tickets module allowed to query
across tenants (no `tenant_id` filter). It is reachable exclusively through
`require_site_admin` (see `src/apps/site_admin/router.py`) — never import
this service from `src/apps/support/` (the tenant-scoped module).
"""
from __future__ import annotations

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

from sqlalchemy import and_, func, or_, 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.apps.tenants.models.account import Account
from src.core.constants import TicketStatus, UserRole
from src.core.exceptions import ConflictError, NotFoundError

SITE_ADMIN_DISPLAY_NAME = "INDELIS Support"


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

    async def list(
        self,
        skip: int = 0,
        limit: int = 20,
        plan_filter: Optional[str] = None,
        status_filter: Optional[str] = None,
        q: Optional[str] = None,
    ) -> Tuple[list[dict], int]:
        """All tenants' tickets, joined to accounts (organization_name +
        plan) and to users (submitted_by). Following EnquiryService.list's
        ilike/or_ search pattern — parameterized, no raw SQL interpolation."""
        conditions = []
        if plan_filter:
            conditions.append(Account.plan == plan_filter.lower())
        if status_filter:
            conditions.append(SupportTicket.status == status_filter.lower())
        if q:
            term = f"%{q.lower()}%"
            conditions.append(
                or_(
                    func.lower(Account.organization_name).like(term),
                    func.lower(SupportTicket.ticket_number).like(term),
                    func.lower(SupportTicket.subject).like(term),
                )
            )

        base = select(SupportTicket, Account, User).join(
            Account, SupportTicket.tenant_id == Account.id
        ).outerjoin(User, SupportTicket.created_by_user_id == User.id)

        if conditions:
            base = base.where(and_(*conditions))

        count_stmt = select(func.count()).select_from(base.subquery())
        total = (await self.db.execute(count_stmt)).scalar_one()

        stmt = base.order_by(SupportTicket.created_at.desc()).offset(skip).limit(limit)
        rows = (await self.db.execute(stmt)).all()

        items = []
        for ticket, account, user in rows:
            submitted_by = f"{user.first_name} {user.last_name}".strip() if user else ""
            items.append(
                {
                    "id": ticket.id,
                    "ticket_number": ticket.ticket_number,
                    "subject": ticket.subject,
                    "organization_name": account.organization_name,
                    "submitted_by": submitted_by,
                    "plan": account.plan,
                    "status": ticket.status,
                    "created_at": ticket.created_at,
                }
            )
        return items, total

    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 get_by_id(self, ticket_id: UUID) -> dict:
        """Any tenant's ticket + full thread + organization/plan info. No
        tenant_id filter — intentional, this is the cross-tenant service."""
        result = await self.db.execute(
            select(SupportTicket, Account)
            .join(Account, SupportTicket.tenant_id == Account.id)
            .options(selectinload(SupportTicket.messages))
            .where(SupportTicket.id == ticket_id)
        )
        row = result.first()
        if not row:
            raise NotFoundError("Support ticket not found")
        ticket, account = row

        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,
            "organization_name": account.organization_name,
            "plan": account.plan,
            "messages": [self._serialize_message(m, author_names) for m in messages],
        }

    async def add_message(self, ticket_id: UUID, site_admin_user, body: str) -> SupportTicketMessage:
        """Appends a support-team reply on any tenant's ticket and bumps
        last_message_at. Always stamped author_role_snapshot='site_admin'."""
        result = await self.db.execute(
            select(SupportTicket).where(SupportTicket.id == ticket_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=ticket.tenant_id,
            ticket_id=ticket.id,
            author_user_id=site_admin_user.id,
            author_role_snapshot=UserRole.SITE_ADMIN.value,
            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

    async def update_status(self, ticket_id: UUID, new_status: str) -> SupportTicket:
        """Transitions ticket status. Only reachable via require_site_admin —
        tenants have no endpoint that accepts a status field.

        Closed is a terminal state: once a ticket's status is 'closed', it is
        frozen — no further status transitions are permitted (including
        re-closing it or reopening it), matching add_message's identical
        freeze on replies."""
        result = await self.db.execute(
            select(SupportTicket).where(SupportTicket.id == ticket_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 its status can no longer be changed.")

        ticket.status = new_status
        await self.db.flush()
        await self.db.refresh(ticket)
        return ticket