"""Feishu helpdesk ticket mirror + 12h sync job.

Revision ID: 316
Revises: 315
Create Date: 2026-06-22

Creates ``feishu_helpdesk_tickets`` (external mirror of Feishu 服务台
工单, for the 服务台分析 reports) and seeds the ``feishu.helpdesk_sync``
scheduled job at a 12-hour interval. The job is seeded ENABLED — it
no-ops harmlessly until a ``helpdesk``/``feishu`` integration row exists.
No hard FKs (external mirror; TiDB CHAR/VARCHAR FK strictness).
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "316"
down_revision = "315"
branch_labels = None
depends_on = None


_JOB_NAME = "feishu.helpdesk_sync"


def upgrade() -> None:
    op.create_table(
        "feishu_helpdesk_tickets",
        sa.Column("ticket_id", sa.String(64), primary_key=True),
        sa.Column("integration_id", sa.CHAR(36), nullable=False),
        sa.Column("helpdesk_id", sa.String(32), nullable=False),
        sa.Column("helpdesk_name", sa.String(255), nullable=True),
        sa.Column("status", sa.Integer(), nullable=True),
        sa.Column("channel", sa.Integer(), nullable=True),
        sa.Column("ticket_type", sa.Integer(), nullable=True),
        sa.Column("solve", sa.Integer(), nullable=True),
        sa.Column("score", sa.Integer(), nullable=True),
        sa.Column("language", sa.String(16), nullable=True),
        sa.Column("guest_open_id", sa.String(128), nullable=True),
        sa.Column("guest_name", sa.String(255), nullable=True),
        sa.Column("guest_email", sa.String(255), nullable=True),
        sa.Column("guest_person_id", sa.CHAR(36), nullable=True),
        sa.Column("agent_owner_open_id", sa.String(128), nullable=True),
        sa.Column("agent_owner_name", sa.String(255), nullable=True),
        sa.Column("agent_owner_email", sa.String(255), nullable=True),
        sa.Column("agent_group", sa.String(255), nullable=True),
        sa.Column("first_response_seconds", sa.Integer(), nullable=True),
        sa.Column("resolution_seconds", sa.Integer(), nullable=True),
        sa.Column("agent_service_minutes", sa.Float(), nullable=True),
        sa.Column("bot_service_minutes", sa.Float(), nullable=True),
        sa.Column("processing_seconds", sa.Integer(), nullable=True),
        sa.Column("created_at_feishu", sa.DateTime(timezone=True), nullable=True),
        sa.Column("updated_at_feishu", sa.DateTime(timezone=True), nullable=True),
        sa.Column("closed_at_feishu", sa.DateTime(timezone=True), nullable=True),
        sa.Column("update_time_ms", sa.BigInteger(), nullable=True),
        sa.Column("raw", sa.JSON(), nullable=False),
        sa.Column("synced_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
    )
    op.create_index(
        "ix_fh_tickets_integration", "feishu_helpdesk_tickets", ["integration_id"],
    )
    op.create_index(
        "ix_fh_tickets_helpdesk", "feishu_helpdesk_tickets", ["helpdesk_id"],
    )
    op.create_index(
        "ix_fh_tickets_created", "feishu_helpdesk_tickets", ["created_at_feishu"],
    )
    op.create_index(
        "ix_fh_tickets_updated_ms", "feishu_helpdesk_tickets",
        ["integration_id", "update_time_ms"],
    )
    op.create_index(
        "ix_fh_tickets_status", "feishu_helpdesk_tickets",
        ["integration_id", "status"],
    )
    op.create_index(
        "ix_fh_tickets_guest_person", "feishu_helpdesk_tickets", ["guest_person_id"],
    )

    bind = op.get_bind()
    job_exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": _JOB_NAME},
    ).scalar()
    if not job_exists:
        bind.execute(
            sa.text(
                """
                INSERT INTO scheduled_jobs
                    (id, name, display_name, description, handler_key,
                     trigger_type, interval_seconds, cron_expr, timezone,
                     enabled, config, run_count, failure_count,
                     created_at, updated_at)
                VALUES
                    (:id, :name, :display_name, :description, :handler_key,
                     'interval', 43200, NULL, 'UTC',
                     1, '{}', 0, 0, NOW(), NOW())
                """
            ),
            {
                # Pre-mint UUID — TiDB's UUID() is unreliable (see
                # 077_seed_jira_integration.py / scheduled_jobs no-dash bug).
                "id": str(uuid.uuid4()),
                "name": _JOB_NAME,
                "display_name": "飞书 · 服务台工单同步",
                "description": (
                    "每 12 小时增量拉取飞书服务台工单到镜像表,供服务台分析报表使用。"
                    "无服务台连接器时优雅空跑。"
                ),
                "handler_key": _JOB_NAME,
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name = :n"),
        {"n": _JOB_NAME},
    )
    op.drop_table("feishu_helpdesk_tickets")