"""飞书服务台工单镜像表.

One row per Feishu helpdesk ticket, upserted from the Helpdesk API by the
``feishu.helpdesk_sync`` cron. Durations are stored verbatim as the API
returns them (seconds for ``*_duration`` / ``*_time`` deltas; raw epoch-ms
for the timestamp fields, converted to tz-aware UTC datetimes here). A
value of ``-1`` from the API (not-applicable / unfinished) is normalised to
NULL on the typed columns so analytics ``AVG()`` ignores it.

No hard FK to ``integrations`` / ``identity_persons`` — this is an external
mirror (cross-domain coupling + TiDB CHAR/VARCHAR FK strictness both argue
against it); the join columns are plain indexed UUIDs.
"""
from __future__ import annotations

import uuid
from datetime import UTC, datetime

from sqlalchemy import (
    JSON,
    BigInteger,
    DateTime,
    Float,
    Index,
    Integer,
    String,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.core.database import Base
from app.core.sql_types import GUID as Uuid


def _utcnow() -> datetime:
    return datetime.now(UTC)


class FeishuHelpdeskTicket(Base):
    """A single Feishu helpdesk ticket (mirror)."""

    __tablename__ = "feishu_helpdesk_tickets"

    # Feishu ``ticket_id`` (snowflake string, globally unique) is the PK.
    ticket_id: Mapped[str] = mapped_column(String(64), primary_key=True)

    # Which 服务台 (integration row) this ticket belongs to.
    integration_id: Mapped[uuid.UUID] = mapped_column(Uuid(), nullable=False)
    helpdesk_id: Mapped[str] = mapped_column(String(32), nullable=False)
    helpdesk_name: Mapped[str | None] = mapped_column(String(255), nullable=True)

    # ── Categorical state (Feishu numeric enums; kept raw, labelled in FE) ──
    # status (real prod codes, confirmed in #1331 — see service.OPEN_STATUSES):
    #   1=待处理 2=处理中 (open/unresolved) · 50=已解决 51/52=已关闭 (terminal).
    # NOTE: solved is 50, NOT 3 — predicates must use the service.py constants.
    status: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # channel: 0=飞书 ... (Feishu numeric)
    channel: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # ticket_type: 1=机器人 2=人工 ...
    ticket_type: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # solve: 1=已解决 2=未解决
    solve: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # score: satisfaction rating (1..3 / 1..5 depending on tenant config); NULL = 未评价
    score: Mapped[int | None] = mapped_column(Integer, nullable=True)
    language: Mapped[str | None] = mapped_column(String(16), nullable=True)

    # ── People ──
    guest_open_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    guest_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    guest_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    # Resolved Helix person (by work_email) for department-dimension reports.
    guest_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid(), nullable=True)

    agent_owner_open_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    agent_owner_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    agent_owner_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    agent_group: Mapped[str | None] = mapped_column(String(255), nullable=True)

    # ── SLA / duration metrics (verbatim from API) ──
    # Seconds to first agent response (API ``agent_first_response_duration``).
    first_response_seconds: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # Seconds to resolution (API ``agent_resolution_time``); NULL when unfinished.
    resolution_seconds: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # Agent active handling time (API ``agent_service_duration``; minutes, float).
    agent_service_minutes: Mapped[float | None] = mapped_column(Float, nullable=True)
    # Bot handling time before human pickup (API ``bot_service_duration``).
    bot_service_minutes: Mapped[float | None] = mapped_column(Float, nullable=True)
    # API ``actual_processing_time`` (seconds); NULL when -1.
    processing_seconds: Mapped[int | None] = mapped_column(Integer, nullable=True)

    # ── Timestamps (from epoch-ms) ──
    created_at_feishu: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True,
    )
    updated_at_feishu: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True,
    )
    closed_at_feishu: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True,
    )
    # Raw epoch-ms watermark used by the incremental sync (== updated_at_feishu
    # in ms). Kept as an int so the cron can MAX() it without tz coercion.
    update_time_ms: Mapped[int | None] = mapped_column(BigInteger, nullable=True)

    # Full API payload (capped) for fields not promoted to columns.
    raw: Mapped[dict] = mapped_column(JSON, nullable=False, default=dict)

    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, default=_utcnow,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), default=_utcnow,
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        default=_utcnow,
    )

    __table_args__ = (
        Index("ix_fh_tickets_integration", "integration_id"),
        Index("ix_fh_tickets_helpdesk", "helpdesk_id"),
        Index("ix_fh_tickets_created", "created_at_feishu"),
        Index("ix_fh_tickets_updated_ms", "integration_id", "update_time_ms"),
        Index("ix_fh_tickets_status", "integration_id", "status"),
        Index("ix_fh_tickets_guest_person", "guest_person_id"),
    )