"""Unify service requests, ticket attribution, feedback history and SLA clocks.""" from __future__ import annotations import uuid import sqlalchemy as sa from alembic import op from app.core.migration_helpers import id_type_for revision = "527" down_revision = "526" branch_labels = None depends_on = None def upgrade() -> None: identity_id = id_type_for("identity_persons") support_id = id_type_for("support_tickets") op.create_table( "support_service_requests", sa.Column("id", support_id, primary_key=True), sa.Column("requester_id", identity_id, nullable=True), sa.Column("origin", sa.String(32), nullable=False, server_default="web"), sa.Column("status", sa.String(24), nullable=False, server_default="active"), sa.Column("outcome", sa.String(32), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint(["requester_id"], ["identity_persons.id"], name="fk_support_request_requester"), ) op.create_index("ix_support_requests_requester_created", "support_service_requests", ["requester_id", "created_at"]) op.create_index("ix_support_requests_origin_status", "support_service_requests", ["origin", "status", "created_at"]) op.add_column("support_tickets", sa.Column("service_request_id", support_id, nullable=True)) op.add_column("support_tickets", sa.Column("intent_initiator", sa.String(24), nullable=False, server_default="employee")) op.add_column("support_tickets", sa.Column("creation_actor", sa.String(24), nullable=False, server_default="employee")) op.add_column("support_tickets", sa.Column("creation_mode", sa.String(32), nullable=False, server_default="employee_direct")) op.add_column("support_tickets", sa.Column("origin_channel", sa.String(32), nullable=False, server_default="web")) op.add_column("support_tickets", sa.Column("employee_confirmed_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("support_tickets", sa.Column("first_response_due_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("support_tickets", sa.Column("resolution_due_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("support_tickets", sa.Column("sla_paused_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("support_tickets", sa.Column("sla_paused_seconds", sa.BigInteger(), nullable=False, server_default="0")) op.add_column("support_tickets", sa.Column("last_agent_reminded_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("support_tickets", sa.Column("agent_reminder_count", sa.SmallInteger(), nullable=False, server_default="0")) op.add_column("support_tickets", sa.Column("last_requester_reminded_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("support_tickets", sa.Column("requester_reminder_count", sa.SmallInteger(), nullable=False, server_default="0")) op.create_foreign_key("fk_support_ticket_service_request", "support_tickets", "support_service_requests", ["service_request_id"], ["id"]) op.create_index("ix_support_ticket_service_request", "support_tickets", ["service_request_id"]) op.create_index("ix_support_ticket_first_response_due", "support_tickets", ["status", "first_response_due_at"]) op.create_index("ix_support_ticket_resolution_due", "support_tickets", ["status", "resolution_due_at"]) op.add_column("support_deflection_events", sa.Column("service_request_id", support_id, nullable=True)) op.create_foreign_key("fk_support_deflection_service_request", "support_deflection_events", "support_service_requests", ["service_request_id"], ["id"]) op.create_index("ix_support_deflection_service_request", "support_deflection_events", ["service_request_id"]) op.add_column("assistant_conversations", sa.Column("service_request_id", support_id, nullable=True)) op.create_foreign_key("fk_assistant_conversation_service_request", "assistant_conversations", "support_service_requests", ["service_request_id"], ["id"]) op.create_index("ix_assistant_conversation_service_request", "assistant_conversations", ["service_request_id"]) op.add_column("assistant_messages", sa.Column("service_request_id", support_id, nullable=True)) op.create_foreign_key("fk_assistant_message_service_request", "assistant_messages", "support_service_requests", ["service_request_id"], ["id"]) op.create_index("ix_assistant_message_service_request", "assistant_messages", ["service_request_id"]) op.add_column("assistant_message_feedback", sa.Column("reason_code", sa.String(32), nullable=True)) op.add_column("assistant_message_feedback", sa.Column("classification", sa.String(32), nullable=True)) op.create_table( "assistant_message_feedback_events", sa.Column("id", support_id, primary_key=True), sa.Column("message_id", support_id, nullable=False), sa.Column("person_id", identity_id, nullable=False), sa.Column("verdict", sa.String(16), nullable=False), sa.Column("reason_code", sa.String(32), nullable=True), sa.Column("classification", sa.String(32), nullable=True), sa.Column("trace_id", sa.String(64), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.ForeignKeyConstraint(["message_id"], ["assistant_messages.id"], name="fk_assistant_feedback_event_message", ondelete="CASCADE"), sa.ForeignKeyConstraint(["person_id"], ["identity_persons.id"], name="fk_assistant_feedback_event_person"), ) op.create_index("ix_assistant_feedback_event_message", "assistant_message_feedback_events", ["message_id", "created_at"]) op.create_index("ix_assistant_feedback_event_verdict", "assistant_message_feedback_events", ["verdict", "created_at"]) op.create_table( "support_external_ticket_links", sa.Column("id", support_id, primary_key=True), sa.Column("service_request_id", support_id, nullable=False), sa.Column("source", sa.String(32), nullable=False), sa.Column("external_ticket_id", sa.String(128), nullable=False), sa.Column("linked_by_person_id", identity_id, nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.ForeignKeyConstraint(["service_request_id"], ["support_service_requests.id"], name="fk_support_external_request"), sa.ForeignKeyConstraint(["linked_by_person_id"], ["identity_persons.id"], name="fk_support_external_linker"), ) op.create_index("ux_support_external_ticket", "support_external_ticket_links", ["source", "external_ticket_id"], unique=True) op.create_index("ix_support_external_request", "support_external_ticket_links", ["service_request_id"]) bind = op.get_bind() bind.execute(sa.text(""" INSERT INTO support_service_requests (id, requester_id, origin, status, outcome, created_at, updated_at, resolved_at) SELECT t.id, t.requester_id, CASE WHEN t.source='ai' THEN 'assistant' WHEN t.source='mobile' THEN 'mobile' ELSE 'web' END, CASE WHEN t.status IN ('closed','cancelled') THEN 'closed' WHEN t.status='resolved' THEN 'resolved' ELSE 'escalated' END, CASE WHEN t.status IN ('resolved','closed') THEN 'human_solved' WHEN t.status='cancelled' THEN 'cancelled' ELSE NULL END, t.created_at, t.updated_at, t.resolved_at FROM support_tickets t WHERE NOT EXISTS (SELECT 1 FROM support_service_requests r WHERE r.id=t.id) """)) bind.execute(sa.text("UPDATE support_tickets SET service_request_id=id WHERE service_request_id IS NULL")) bind.execute(sa.text(""" INSERT INTO support_service_requests (id, requester_id, origin, status, outcome, created_at, updated_at, resolved_at) SELECT d.id, d.person_id, 'support_copilot', CASE WHEN d.outcome='deflected' THEN 'self_resolved' WHEN d.outcome='expired' THEN 'abandoned' ELSE 'active' END, CASE WHEN d.outcome='deflected' THEN 'self_solved' WHEN d.outcome='expired' THEN 'unknown' ELSE NULL END, d.created_at, d.created_at, d.resolved_at FROM support_deflection_events d WHERE d.ticket_id IS NULL AND NOT EXISTS (SELECT 1 FROM support_service_requests r WHERE r.id=d.id) """)) bind.execute(sa.text(""" UPDATE support_deflection_events SET service_request_id=CASE WHEN ticket_id IS NOT NULL THEN ticket_id ELSE id END WHERE service_request_id IS NULL """)) bind.execute(sa.text(""" INSERT INTO support_service_requests (id, requester_id, origin, status, outcome, created_at, updated_at, resolved_at) SELECT c.id, c.person_id, 'assistant', 'active', NULL, c.started_at, c.last_message_at, NULL FROM assistant_conversations c WHERE NOT EXISTS (SELECT 1 FROM support_service_requests r WHERE r.id=c.id) """)) bind.execute(sa.text("UPDATE assistant_conversations SET service_request_id=id WHERE service_request_id IS NULL")) bind.execute(sa.text(""" UPDATE assistant_messages m JOIN assistant_conversations c ON c.id=m.conversation_id SET m.service_request_id=c.service_request_id WHERE m.service_request_id IS NULL """)) if not bind.execute(sa.text("SELECT 1 FROM scheduled_jobs WHERE name=:n LIMIT 1"), {"n": "support.ticket_sla_reminder"}).scalar(): 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, 'cron', NULL, '*/15 * * * *', 'Asia/Shanghai', 1, :config, 0, 0, NOW(), NOW()) """), { "id": str(uuid.uuid4()), "name": "support.ticket_sla_reminder", "display_name": "工单 · SLA 与员工待办催办", "description": "每 15 分钟检查未认领、首响/解决超时及等待员工的工单，按责任方自动提醒。", "handler_key": "support.ticket_sla_reminder", "config": '{"cooldown_hours": 8, "requester_cooldown_hours": 24, "unassigned_after_hours": 1, "requester_wait_hours": 48, "resolved_confirm_hours": 72}', }) bind.execute(sa.text(""" UPDATE scheduled_jobs SET config=JSON_SET(COALESCE(config, JSON_OBJECT()), '$.assistant_abandon_days', 7) WHERE name='support.ticket_autoclose' """)) def downgrade() -> None: bind = op.get_bind() bind.execute(sa.text(""" UPDATE scheduled_jobs SET config=JSON_REMOVE(COALESCE(config, JSON_OBJECT()), '$.assistant_abandon_days') WHERE name='support.ticket_autoclose' """)) bind.execute(sa.text("DELETE FROM scheduled_jobs WHERE name='support.ticket_sla_reminder'")) op.drop_table("support_external_ticket_links") op.drop_table("assistant_message_feedback_events") op.drop_column("assistant_message_feedback", "classification") op.drop_column("assistant_message_feedback", "reason_code") op.drop_constraint("fk_assistant_message_service_request", "assistant_messages", type_="foreignkey") op.drop_index("ix_assistant_message_service_request", table_name="assistant_messages") op.drop_column("assistant_messages", "service_request_id") op.drop_constraint("fk_assistant_conversation_service_request", "assistant_conversations", type_="foreignkey") op.drop_index("ix_assistant_conversation_service_request", table_name="assistant_conversations") op.drop_column("assistant_conversations", "service_request_id") op.drop_constraint("fk_support_deflection_service_request", "support_deflection_events", type_="foreignkey") op.drop_index("ix_support_deflection_service_request", table_name="support_deflection_events") op.drop_column("support_deflection_events", "service_request_id") op.drop_index("ix_support_ticket_resolution_due", table_name="support_tickets") op.drop_index("ix_support_ticket_first_response_due", table_name="support_tickets") op.drop_constraint("fk_support_ticket_service_request", "support_tickets", type_="foreignkey") op.drop_index("ix_support_ticket_service_request", table_name="support_tickets") for name in ( "requester_reminder_count", "last_requester_reminded_at", "agent_reminder_count", "last_agent_reminded_at", "sla_paused_seconds", "sla_paused_at", "resolution_due_at", "first_response_due_at", "employee_confirmed_at", "origin_channel", "creation_mode", "creation_actor", "intent_initiator", "service_request_id", ): op.drop_column("support_tickets", name) op.drop_table("support_service_requests")