"""Add permission-scoped cloud resource report delivery.

Revision ID: 534
Revises: 533
"""
from __future__ import annotations

import json
import uuid

import sqlalchemy as sa

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

revision = "534"
down_revision = "533"
branch_labels = None
depends_on = None

_JOB = "cmdb.resource_report_dispatch"


def upgrade() -> None:
    person_id = id_type_for("identity_persons")
    resource_id = id_type_for("cmdb_cloud_resources")
    op.create_table(
        "cmdb_resource_report_subscriptions",
        sa.Column("id", resource_id, primary_key=True),
        sa.Column("person_id", person_id, nullable=False),
        sa.Column("frequency", sa.String(16), nullable=False, server_default="off"),
        sa.Column("channels", sa.JSON),
        sa.Column("timezone", sa.String(64), nullable=False, server_default="Asia/Shanghai"),
        sa.Column("delivery_hour", sa.Integer, nullable=False, server_default="9"),
        sa.Column("weekly_day", sa.Integer, nullable=False, server_default="0"),
        sa.Column("monthly_day", sa.Integer, nullable=False, server_default="1"),
        sa.Column("include_ai", sa.Boolean, nullable=False, server_default=sa.false()),
        sa.Column("scope_config", sa.JSON),
        sa.Column("next_run_at", sa.DateTime(timezone=True)),
        sa.Column("last_sent_at", sa.DateTime(timezone=True)),
        sa.Column("version", sa.Integer, nullable=False, server_default="1"),
        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.ForeignKeyConstraint(["person_id"], ["identity_persons.id"], ondelete="CASCADE"),
        sa.UniqueConstraint("person_id", name="uq_cmdb_resource_report_subscription_person"),
    )
    op.create_index(
        "ix_cmdb_resource_report_due", "cmdb_resource_report_subscriptions",
        ["frequency", "next_run_at"],
    )
    op.create_table(
        "cmdb_resource_report_runs",
        sa.Column("id", resource_id, primary_key=True),
        sa.Column("subscription_id", resource_id),
        sa.Column("recipient_person_id", person_id, nullable=False),
        sa.Column("trigger", sa.String(16), nullable=False),
        sa.Column("status", sa.String(24), nullable=False, server_default="generated"),
        sa.Column("window_start", sa.Date, nullable=False),
        sa.Column("window_end", sa.Date, nullable=False),
        sa.Column("scope_snapshot", sa.JSON),
        sa.Column("report_data", sa.JSON, nullable=False),
        sa.Column("interpretation", sa.Text),
        sa.Column("ai_interpretation", sa.Text),
        sa.Column("ai_status", sa.String(24)),
        sa.Column("fact_hash", sa.String(64), nullable=False),
        sa.Column("permission_fingerprint", sa.String(64), nullable=False),
        sa.Column("error_message", sa.Text),
        sa.Column("generated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("delivered_at", sa.DateTime(timezone=True)),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.ForeignKeyConstraint(
            ["subscription_id"], ["cmdb_resource_report_subscriptions.id"], ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["recipient_person_id"], ["identity_persons.id"], ondelete="CASCADE",
        ),
    )
    op.create_index(
        "ix_cmdb_resource_report_runs_person_at", "cmdb_resource_report_runs",
        ["recipient_person_id", "created_at"],
    )
    op.create_index(
        "ix_cmdb_resource_report_runs_subscription_at", "cmdb_resource_report_runs",
        ["subscription_id", "created_at"],
    )
    op.create_index(
        "ix_cmdb_resource_report_runs_created_at", "cmdb_resource_report_runs",
        ["created_at"],
    )
    op.create_index(
        "ix_cmdb_resource_report_runs_person_fact_at", "cmdb_resource_report_runs",
        ["recipient_person_id", "fact_hash", "generated_at"],
    )
    bind = op.get_bind()
    if not bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name=:name LIMIT 1"), {"name": _JOB},
    ).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, '云资源目录报表投递',
                    '每小时检查到期订阅，发送前重新计算权限范围并写冻结报表；不修改云资源。',
                    :name, 'cron', NULL, '17 * * * *', 'UTC', 1, :config,
                    0, 0, UTC_TIMESTAMP(), UTC_TIMESTAMP())"""
        ), {"id": str(uuid.uuid4()), "name": _JOB, "config": json.dumps({})})


def downgrade() -> None:
    op.get_bind().execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name=:name"), {"name": _JOB},
    )
    op.drop_table("cmdb_resource_report_runs")
    op.drop_table("cmdb_resource_report_subscriptions")