from __future__ import annotations

import importlib.util
from pathlib import Path

from alembic.config import Config
from alembic.script import ScriptDirectory


BACKEND_ROOT = Path(__file__).resolve().parents[2]
MIGRATIONS_ROOT = BACKEND_ROOT / "migrations"
REVISION_PATH = MIGRATIONS_ROOT / "versions" / "0001_initial_schema.py"
RESTORE_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0002_add_restore_audit_action.py"
)
Q3_REVISION_PATH = (
    MIGRATIONS_ROOT
    / "versions"
    / "0003_q3_business_alignment_and_content_search.py"
)
REORDER_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0009_reorder_audit_action.py"
)
ATTACHMENT_TYPE_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0010_attachment_type_redefine.py"
)
REORDER_CATEGORY_REVISION_PATH = (
    MIGRATIONS_ROOT
    / "versions"
    / "0011_reorder_category_audit_action.py"
)
ROLE_MANAGEMENT_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0012_role_management.py"
)
DOCUMENT_PASSWORD_LOCK_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0013_document_password_lock.py"
)
DOCUMENT_RELATIONS_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0014_document_relations.py"
)
LOGIN_IP_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0015_login_ip_restriction.py"
)
PORTAL_USER_PROVISION_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0016_portal_user_provision.py"
)
TYPE_DISPLAY_NAME_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0017_category_type_display_name.py"
)
SUB_PLAN_BINDING_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0018_sub_plan_binding.py"
)
CONTENT_EXTRACT_ERROR_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0019_content_extract_error.py"
)
REMOVE_ASSIGNED_SCOPE_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0021_remove_assigned_scope.py"
)
LOGIN_LOCKOUT_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0022_login_lockout.py"
)
ADMIN_PERMISSIONS_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0023_admin_permissions.py"
)
MANAGE_PERMISSION_MATRIX_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0024_manage_permission_matrix.py"
)
MOVE_AUDIT_ACTIONS_REVISION_PATH = (
    MIGRATIONS_ROOT / "versions" / "0025_move_audit_actions.py"
)


def _load_revision_module():
    spec = importlib.util.spec_from_file_location("dms_initial_migration", REVISION_PATH)
    assert spec is not None and spec.loader is not None
    module = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(module)
    return module


def test_alembic_config_and_head_are_valid():
    config = Config(str(MIGRATIONS_ROOT / "alembic.ini"))
    script = ScriptDirectory.from_config(config)
    assert script.get_current_head() == "0035_drop_audit_action_check"


def test_initial_migration_exports_upgrade_and_downgrade():
    module = _load_revision_module()
    assert callable(module.upgrade)
    assert callable(module.downgrade)


def test_initial_migration_contains_seven_tables_and_generated_columns():
    source = REVISION_PATH.read_text(encoding="utf-8")
    for table_name in (
        "sys_organization",
        "sys_user",
        "doc_category",
        "doc_document",
        "doc_attachment_binding",
        "doc_permission",
        "sys_audit_log",
    ):
        assert f'"{table_name}"' in source
    assert source.count('sa.Computed("IF(is_deleted = 0, 1, NULL)"') == 2
    assert "uq_doc_attachment_binding_active" in source
    assert "uq_doc_permission_active" in source


def test_initial_migration_has_no_cascade():
    source = REVISION_PATH.read_text(encoding="utf-8")
    assert 'ondelete="CASCADE"' not in source
    assert source.count('ondelete="RESTRICT"') >= 8


def test_downgrade_uses_dependency_safe_order():
    source = REVISION_PATH.read_text(encoding="utf-8")
    positions = [
        source.index(f'op.drop_table("{name}")')
        for name in (
            "sys_audit_log",
            "doc_permission",
            "doc_attachment_binding",
            "doc_document",
            "doc_category",
            "sys_user",
            "sys_organization",
        )
    ]
    assert positions == sorted(positions)


def test_restore_migration_is_scoped_and_reversible():
    source = RESTORE_REVISION_PATH.read_text(encoding="utf-8")
    assert 'down_revision: str | None = "0001_initial_schema"' in source
    assert "RESTORE_DOCUMENT" in source
    assert "ix_doc_document_recycle_deleted" in source
    assert '["is_deleted", "deleted_at", "id"]' in source
    assert "drop_index" in source
    assert source.count("create_check_constraint") == 2
    assert "create_table" not in source
    assert "add_column" not in source


def test_q3_migration_is_single_scoped_revision_with_safe_downgrade():
    source = Q3_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0002_add_restore_audit_action"'
        in source
    )
    assert "content_text" in source
    assert "content_extract_status" in source
    assert "content_extracted_at" in source
    assert "LONGTEXT" in source
    assert "auth_version = auth_version + 1" in source
    assert "role_code = 'USER'" in source
    assert "role_code IN ('USER', 'ADMIN')" in source
    assert "RuntimeError" in source
    assert "create_table" not in source


def test_reorder_audit_migration_is_scoped_and_reversible():
    source = REORDER_REVISION_PATH.read_text(encoding="utf-8")
    assert 'down_revision: str | None = "0008_document_sort_no"' in source
    assert "REORDER_DOCUMENT" in source
    assert "REORDER_ATTACHMENT" in source
    assert "audit_action_type" in source
    assert "RuntimeError" in source
    assert "create_table" not in source
    assert "add_column" not in source


def test_attachment_type_redefine_migration_maps_legacy_values():
    source = ATTACHMENT_TYPE_REVISION_PATH.read_text(encoding="utf-8")
    assert 'down_revision: str | None = "0009_reorder_audit_action"' in source
    assert "SUPPORT_ACHIEVEMENT" in source
    assert "TRAINING_MATERIAL" in source
    assert "TYPICAL_CASE" in source
    assert "document_attachment_type" in source
    assert "UPDATE doc_document SET attachment_type = 'SUPPORT_ACHIEVEMENT'" in source
    assert "RuntimeError" in source
    assert "create_table" not in source
    assert "add_column" not in source


def test_reorder_category_audit_migration_is_scoped_and_reversible():
    source = REORDER_CATEGORY_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0010_attachment_type_redefine"'
        in source
    )
    assert "REORDER_CATEGORY" in source
    assert "audit_action_type" in source
    assert "RuntimeError" in source
    assert "create_table" not in source
    assert "add_column" not in source


def test_role_management_migration_is_scoped_and_reversible():
    source = ROLE_MANAGEMENT_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0011_reorder_category_audit_action"'
        in source
    )
    for table_name in ("sys_role", "sys_role_dept", "sys_role_permission"):
        assert f'"{table_name}"' in source
    assert "uq_sys_role_default_active" in source
    assert "IF(is_default = 1 AND is_deleted = 0, 1, NULL)" in source
    assert "can_config_permission" in source
    assert "can_bind_attachment" in source
    assert "can_unbind_attachment" in source
    assert "fk_sys_user_role_id_sys_role" in source
    assert "CHANGE_ROLE_PERMISSION" in source
    assert "'ROLE'" in source
    assert "RuntimeError" in source
    assert source.count("op.create_table") == 3
    # 降级按依赖序先删矩阵/部门，再删角色本体
    positions = [
        source.index(f'op.drop_table("{name}")')
        for name in ("sys_role_permission", "sys_role_dept", "sys_role")
    ]
    assert positions == sorted(positions)


def test_document_password_lock_migration_is_scoped_and_reversible():
    source = DOCUMENT_PASSWORD_LOCK_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0012_role_management"' in source
    )
    assert '"password_hash"' in source
    for column in (
        "protect_view",
        "protect_download",
        "protect_edit",
        "protect_delete",
        "protect_config_permission",
        "protect_bind_attachment",
        "protect_unbind_attachment",
    ):
        assert f'"{column}"' in source
    assert "document_password_main_only" in source
    assert "(password_hash IS NULL) OR (document_type = 'MAIN')" in source
    assert "DOCUMENT_PASSWORD_SET" in source
    assert "DOCUMENT_PASSWORD_REMOVE" in source
    assert "RuntimeError" in source
    assert "create_table" not in source


def test_document_relations_migration_is_scoped_and_reversible():
    source = DOCUMENT_RELATIONS_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0013_document_password_lock"' in source
    )
    assert 'op.create_table(\n        "doc_document_relation"' in source
    assert '"relation_type"' in source
    assert "sa.String(length=64)" in source
    assert "uq_doc_document_relation_active" in source
    assert "IF(is_deleted = 0, 1, NULL)" in source
    assert "document_relation_no_self" in source
    assert "document_id <> related_document_id" in source
    assert source.count("ondelete=\"RESTRICT\"") == 2
    # 审计复用EDIT_DOCUMENT，不扩展sys_audit_log的CHECK约束
    assert "audit_action_type" not in source
    assert "RuntimeError" in source
    assert source.count("op.create_table") == 1
    assert "add_column" not in source


def test_login_ip_restriction_migration_is_scoped_and_reversible():
    source = LOGIN_IP_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0014_document_relations"' in source
    )
    for table_name in ("sys_ip_entry", "sys_org_ip_assignment"):
        assert f'"{table_name}"' in source
    assert '"ip_value"' in source
    assert "uq_sys_ip_entry_ip_value_active" in source
    assert "uq_sys_org_ip_assignment_active" in source
    assert "IF(is_deleted = 0, 1, NULL)" in source
    assert "fk_sys_org_ip_assignment_organization_id_sys_organization" in source
    assert "fk_sys_org_ip_assignment_ip_entry_id_sys_ip_entry" in source
    assert "CREATE_IP_ENTRY" in source
    assert "ASSIGN_ORG_IP" in source
    assert '"IP_ENTRY"' in source
    assert "RuntimeError" in source
    assert source.count("op.create_table") == 2
    # 降级按依赖序先删分配表，再删IP条目表
    positions = [
        source.index(f'op.drop_table("{name}")')
        for name in ("sys_org_ip_assignment", "sys_ip_entry")
    ]
    assert positions == sorted(positions)


def test_portal_user_provision_migration_is_scoped_and_reversible():
    source = PORTAL_USER_PROVISION_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0015_login_ip_restriction"' in source
    )
    assert '"external_id"' in source
    assert "sa.String(length=128)" in source
    assert "uq_sys_user_external_id_active" in source
    assert "ix_sys_user_external_active" in source
    assert "IF(is_deleted = 0, 1, NULL)" in source
    # 降级保护：存在门户用户时拒绝有损降级
    assert "external_id IS NOT NULL" in source
    assert "RuntimeError" in source
    assert "create_table" not in source


def test_category_type_display_name_migration_is_scoped_and_idempotent():
    source = TYPE_DISPLAY_NAME_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0016_portal_user_provision"' in source
    )
    assert '_COLUMN = "type_display_name"' in source
    assert "sa.String(length=128)" in source
    # 幂等：按现存列集合判断，重复执行不重复加列
    assert "_existing_columns" in source
    assert "drop_column" in source
    assert "create_table" not in source


def test_sub_plan_binding_migration_is_scoped_and_reversible():
    source = SUB_PLAN_BINDING_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0017_category_type_display_name"'
        in source
    )
    assert '"doc_sub_plan_binding"' in source
    assert "uq_doc_sub_plan_binding_active" in source
    assert "IF(is_deleted = 0, 1, NULL)" in source
    assert "ix_doc_sub_plan_binding_main_active_sort" in source
    assert "ix_doc_sub_plan_binding_sub_active" in source
    assert source.count('ondelete="RESTRICT"') == 2
    # 审计复用 BIND/UNBIND_ATTACHMENT，不扩展 sys_audit_log 的 CHECK 约束
    assert "audit_action_type" not in source
    # 降级保护：已产生挂载数据时拒绝有损降级
    assert "RuntimeError" in source
    assert source.count("op.create_table") == 1
    assert "add_column" not in source


def test_content_extract_error_migration_is_scoped_and_idempotent():
    source = CONTENT_EXTRACT_ERROR_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0018_sub_plan_binding"' in source
    )
    assert '_TABLE = "doc_document"' in source
    assert '_COLUMN = "content_extract_error"' in source
    assert "sa.String(512)" in source
    # 幂等：按现存列集合判断，重复执行不重复加列
    assert "_existing_columns" in source
    assert "add_column" in source
    assert "drop_column" in source
    assert "create_table" not in source
    # 降级保护：已产生失败记录时拒绝有损降级
    assert "RuntimeError" in source
    assert "IS NOT NULL" in source


def test_remove_assigned_scope_migration_is_scoped_and_idempotent():
    source = REMOVE_ASSIGNED_SCOPE_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0020_sub_plan_to_attachment"' in source
    )
    # 存量 ASSIGNED 行物理删除（含软删行）
    assert "DELETE FROM sys_role_permission WHERE dept_scope = 'ASSIGNED'" in source
    # CHECK 约束按名幂等换名收敛：旧删新加
    assert "ck_sys_role_permission_role_permission_dept_scope" in source
    assert "ck_sys_role_permission_dept_scope_v2" in source
    assert "dept_scope IN ('OWN', 'OTHER')" in source
    # 角色可访问部门表下线
    assert "DROP TABLE IF EXISTS sys_role_dept" in source
    # 降级：重建 sys_role_dept 空表并补种 ASSIGNED 默认全否行
    assert 'op.create_table(\n        "sys_role_dept"' in source
    assert "'ASSIGNED'" in source


def test_login_lockout_migration_is_scoped_and_idempotent():
    source = LOGIN_LOCKOUT_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0021_remove_assigned_scope"' in source
    )
    assert '_TABLE = "sys_user"' in source
    assert "failed_login_count" in source
    assert "locked_until" in source
    assert "sa.Integer()" in source
    assert "sa.DateTime()" in source
    assert 'server_default=sa.text("0")' in source
    # 幂等：按现存列集合判断，重复执行不重复加列
    assert "_existing_columns" in source
    assert "add_column" in source
    assert "drop_column" in source
    assert "create_table" not in source


def test_admin_permissions_migration_is_scoped_and_idempotent():
    source = ADMIN_PERMISSIONS_REVISION_PATH.read_text(encoding="utf-8")
    assert 'down_revision: str | None = "0022_login_lockout"' in source
    assert '_TABLE = "sys_user"' in source
    # 三权限位 + 管理范围锚点
    assert "perm_category_edit" in source
    assert "perm_org_edit" in source
    assert "perm_user_edit" in source
    assert '"managed_org_id"' in source
    assert "sa.Boolean()" in source
    assert "sa.BigInteger()" in source
    assert 'server_default=sa.text("0")' in source
    # 存量初始化：ADMIN 三权限开启；admin 锚点保持 NULL，其余锚点=所在部门
    assert "role_code = 'ADMIN'" in source
    assert "username <> 'admin'" in source
    assert "managed_org_id = organization_id" in source
    # 幂等：按现存列集合判断，重复执行不重复加列
    assert "_existing_columns" in source
    assert "add_column" in source
    assert "drop_column" in source
    assert "create_table" not in source


def test_manage_permission_matrix_migration_moves_flags_to_roles():
    source = MANAGE_PERMISSION_MATRIX_REVISION_PATH.read_text(encoding="utf-8")
    assert 'revision: str = "0024_manage_permission_matrix"' in source
    assert 'down_revision: str | None = "0023_admin_permissions"' in source
    # 角色侧新增管理位三布尔 + 锚点；用户侧四个旧列下线
    assert '"manage_category_edit"' in source
    assert '"manage_org_edit"' in source
    assert '"manage_user_edit"' in source
    assert '"managed_org_id"' in source
    assert '"perm_category_edit"' in source
    # 存量收敛：唯一占用直接置位、多组占用克隆角色并复制权限矩阵
    assert "row_version = row_version + 1" in source
    assert "INSERT INTO sys_role_permission" in source
    assert "-M{suffix}" in source
    # 幂等：列探测 + 以 sys_user 旧列存在为数据迁移前提
    assert "_columns_of" in source
    assert 'if "perm_category_edit" in user_columns' in source
    assert "drop_column" in source


def test_move_audit_actions_migration_extends_action_check():
    source = MOVE_AUDIT_ACTIONS_REVISION_PATH.read_text(encoding="utf-8")
    assert (
        'down_revision: str | None = "0024_manage_permission_matrix"' in source
    )
    # 0015 全集 + 跨页移动两个动作 = 38 项，V7 为降级目标集
    assert "MOVE_DOCUMENT" in source
    assert "MOVE_ATTACHMENT" in source
    assert "AUDIT_ACTIONS_V7 = AUDIT_ACTIONS_V8[:-2]" in source
    assert "audit_action_type" in source
    # 达梦兼容：约束名统一小写比较后按原始逻辑名删除
    assert ".lower()" in source
    # 降级保护：已存在 MOVE 审计记录时拒绝有损降级
    assert "RuntimeError" in source
    assert "create_table" not in source
    assert "add_column" not in source