"""Track direct-upload grants and seed orphan cleanup.

Revision ID: 444
Revises: 443
Create Date: 2026-08-02
"""
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, table_kw_for

revision = "444"
down_revision = "443"
branch_labels = None
depends_on = None

TABLE = "storage_upload_grants"
HANDLER = "storage.orphan_upload_cleanup"


def upgrade() -> None:
    person_t = id_type_for("identity_persons")
    op.create_table(
        TABLE,
        sa.Column("id", person_t, primary_key=True),
        sa.Column("person_id", person_t, nullable=False),
        sa.Column("purpose", sa.String(64), nullable=False),
        sa.Column("object_key", sa.String(512), nullable=False),
        sa.Column("content_type", sa.String(128), nullable=False),
        sa.Column("declared_size_bytes", sa.BigInteger(), nullable=False),
        sa.Column("checksum_sha256", sa.String(44), nullable=False),
        sa.Column("status", sa.String(24), nullable=False, server_default="issued"),
        sa.Column("referenced_resource_type", sa.String(64), nullable=True),
        sa.Column("referenced_resource_id", sa.String(128), nullable=True),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("referenced_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("cleanup_attempted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("cleanup_attempts", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.ForeignKeyConstraint(
            ["person_id"], ["identity_persons.id"],
            name="fk_storage_upload_grants_person",
        ),
        sa.UniqueConstraint("object_key", name="uq_storage_upload_grants_object_key"),
        **table_kw_for("identity_persons"),
    )
    op.create_index(
        "ix_storage_upload_grants_person_created", TABLE, ["person_id", "created_at"],
    )
    op.create_index(
        "ix_storage_upload_grants_status_created", TABLE, ["status", "created_at"],
    )
    op.create_index(
        "ix_storage_upload_grants_cleanup", TABLE,
        ["status", "cleanup_attempted_at", "created_at"],
    )
    op.execute(sa.text(
        "INSERT INTO scheduled_jobs "
        "(id, name, display_name, description, handler_key, trigger_type, "
        " interval_seconds, timezone, enabled, config, run_count, failure_count, "
        " created_at, updated_at) "
        "SELECT :id, :name, :display, :description, :handler, 'interval', "
        "3600, 'UTC', 1, :config, 0, 0, NOW(), NOW() FROM DUAL "
        "WHERE NOT EXISTS (SELECT 1 FROM scheduled_jobs WHERE handler_key=:handler)"
    ).bindparams(
        id=str(uuid.uuid4()),
        name=HANDLER,
        display="对象存储 · 孤儿上传清理",
        description="每小时清理超过 24 小时仍未被业务对象引用的直传文件。",
        handler=HANDLER,
        config=json.dumps({"retention_hours": 24, "batch_size": 100}),
    ))


def downgrade() -> None:
    op.execute(sa.text(
        "DELETE FROM scheduled_jobs WHERE handler_key=:handler"
    ).bindparams(handler=HANDLER))
    op.drop_index("ix_storage_upload_grants_cleanup", table_name=TABLE)
    op.drop_index("ix_storage_upload_grants_status_created", table_name=TABLE)
    op.drop_index("ix_storage_upload_grants_person_created", table_name=TABLE)
    op.drop_table(TABLE)