"""Support ticket satisfaction (CSAT) + stale-resolved auto-close.

Adds the requester satisfaction columns to ``support_tickets`` — a
3-point scale aligned with the Feishu-helpdesk mirror caliber
(1=不满意 / 2=一般 / 3=满意; NULL = not rated) so the two ticket
entrances stay directly comparable during the并行期 — and seeds the
``support.ticket_autoclose`` scheduled job: a resolved ticket the
requester neither confirms nor reopens within N days (job config
``resolved_days``, default 7) closes automatically, which both ends the
previous "parked in resolved forever" state and bounds the rating
window.

Revision ID: 487
Revises: 486
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "487"
down_revision = "486"
branch_labels = None
depends_on = None

_JOB_NAME = "support.ticket_autoclose"


def upgrade() -> None:
    op.add_column(
        "support_tickets",
        sa.Column("satisfaction_score", sa.SmallInteger(), nullable=True),
    )
    op.add_column(
        "support_tickets",
        sa.Column("satisfaction_at", sa.DateTime(timezone=True), nullable=True),
    )

    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', 21600, NULL, 'UTC',
                     1, :config, 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": (
                    "resolved 状态超过 N 天(config.resolved_days,默认 7)员工"
                    "未确认也未重开的工单自动关闭,记 system 时间线消息 + 审计。"
                    "同时为满意度回评窗口收口。"
                ),
                "handler_key": _JOB_NAME,
                "config": '{"resolved_days": 7}',
            },
        )


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