"""Give negative assistant feedback an auditable work-item lifecycle."""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for

revision = "526"
down_revision = "525"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column("assistant_message_feedback", sa.Column("gap_status", sa.String(20), nullable=False, server_default="open"))
    op.add_column("assistant_message_feedback", sa.Column("assigned_person_id", id_type_for("identity_persons"), nullable=True))
    op.add_column("assistant_message_feedback", sa.Column("resolved_by_person_id", id_type_for("identity_persons"), nullable=True))
    op.add_column("assistant_message_feedback", sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True))
    op.add_column("assistant_message_feedback", sa.Column("resolution_note", sa.Text(), nullable=True))
    op.add_column("assistant_message_feedback", sa.Column("last_reminded_at", sa.DateTime(timezone=True), nullable=True))
    op.add_column("assistant_message_feedback", sa.Column("reminder_count", sa.Integer(), nullable=False, server_default="0"))
    op.create_foreign_key("fk_assistant_gap_assignee", "assistant_message_feedback", "identity_persons", ["assigned_person_id"], ["id"])
    op.create_foreign_key("fk_assistant_gap_resolver", "assistant_message_feedback", "identity_persons", ["resolved_by_person_id"], ["id"])
    op.create_index("ix_assistant_feedback_gap_status", "assistant_message_feedback", ["gap_status", "created_at"])
    bind = op.get_bind()
    if not bind.execute(sa.text("SELECT 1 FROM scheduled_jobs WHERE name=:n LIMIT 1"), {"n": "assistant.knowledge_gap_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, '0 * * * *', 'Asia/Shanghai', 1, '{}', 0, 0, NOW(), NOW())
        """), {"id": str(uuid.uuid4()), "name": "assistant.knowledge_gap_reminder",
               "display_name": "助手 · 知识缺口催办",
               "description": "每小时检查超过 24 小时未推进的知识缺口，按 48 小时频率提醒 IT。",
               "handler_key": "assistant.knowledge_gap_reminder"})


def downgrade() -> None:
    op.drop_index("ix_assistant_feedback_gap_status", table_name="assistant_message_feedback")
    op.drop_constraint("fk_assistant_gap_resolver", "assistant_message_feedback", type_="foreignkey")
    op.drop_constraint("fk_assistant_gap_assignee", "assistant_message_feedback", type_="foreignkey")
    for name in ("reminder_count", "last_reminded_at", "resolution_note", "resolved_at", "resolved_by_person_id", "assigned_person_id", "gap_status"):
        op.drop_column("assistant_message_feedback", name)