"""文档归属部门：doc_document 新增 organization_id/organization_name。

Revision ID: 0006_document_organization
Revises: 0005_category_flat_mode
Create Date: 2026-08-14

变更要点：
1. doc_document 新增 organization_id（外键 → sys_organization.id，RESTRICT）
   与 organization_name（冗余字符串，便于检索/展示）。
2. 适用于主案/子案/附件三类文档；与创建者字段独立维护——
   用户调岗不影响历史文档归属。
3. 历史数据回填：按 created_by → sys_user.organization_id 关联填充；
   无创建者或创建者无部门的记录保持 NULL。
4. 新增联合索引 ix_doc_document_organization_active(organization_id, is_deleted)
   支撑按部门过滤的列表查询。
5. 机构改名时由 organization_service.update_organization 级联刷新
   organization_name（与现有 User 冗余字段策略一致）。
"""

from __future__ import annotations

from collections.abc import Sequence

from alembic import op
import sqlalchemy as sa


revision: str = "0006_document_organization"
down_revision: str | None = "0005_category_flat_mode"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


_TABLE = "doc_document"
_INDEX = "ix_doc_document_organization_active"


def _existing_columns(table: str) -> set[str]:
    inspector = sa.inspect(op.get_bind())
    return {item["name"] for item in inspector.get_columns(table)}


def _existing_index_names(table: str) -> set[str]:
    inspector = sa.inspect(op.get_bind())
    return {item["name"] for item in inspector.get_indexes(table)}


def upgrade() -> None:
    columns = _existing_columns(_TABLE)
    if "organization_id" not in columns:
        op.add_column(
            _TABLE,
            sa.Column(
                "organization_id",
                sa.BigInteger(),
                nullable=True,
            ),
        )
        op.create_foreign_key(
            "fk_doc_document_organization_id_sys_organization",
            _TABLE,
            "sys_organization",
            ["organization_id"],
            ["id"],
            ondelete="RESTRICT",
        )
    if "organization_name" not in columns:
        op.add_column(
            _TABLE,
            sa.Column(
                "organization_name",
                sa.String(length=128),
                nullable=True,
            ),
        )

    # 历史回填：按创建者当前部门填充；剩余保持 NULL
    op.execute(
        sa.text(
            "UPDATE doc_document d "
            "JOIN sys_user u ON d.created_by = u.id "
            "SET d.organization_id = u.organization_id, "
            "    d.organization_name = u.organization_name "
            "WHERE d.organization_id IS NULL "
            "  AND u.organization_id IS NOT NULL"
        )
    )

    indexes = _existing_index_names(_TABLE)
    if _INDEX not in indexes:
        op.create_index(
            _INDEX,
            _TABLE,
            ["organization_id", "is_deleted"],
            unique=False,
        )


def downgrade() -> None:
    # 外键存在性检测：MySQL 8 下 fk 名稳定，按名清理
    # MySQL要求先删外键再删其依赖的索引，顺序不可交换
    bind = op.get_bind()
    fk_rows = bind.execute(
        sa.text(
            "SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = 'doc_document' "
            "  AND COLUMN_NAME = 'organization_id' "
            "  AND REFERENCED_TABLE_NAME = 'sys_organization'"
        )
    ).fetchall()
    for row in fk_rows:
        op.drop_constraint(row[0], _TABLE, type_="foreignkey")

    indexes = _existing_index_names(_TABLE)
    if _INDEX in indexes:
        op.drop_index(_INDEX, table_name=_TABLE)

    columns = _existing_columns(_TABLE)
    if "organization_name" in columns:
        op.drop_column(_TABLE, "organization_name")
    if "organization_id" in columns:
        op.drop_column(_TABLE, "organization_id")