"""cleanup and finalize

Revision ID: 0011
Revises: 0010
Create Date: 2026-02-20

架構收尾與清理：
1. 移除廢棄表：business_units, employee_identities
2. 為 tenant_role_rights 新增 is_active
3. 重新命名觸發器：org_* → tenant_*
"""
from alembic import op
import sqlalchemy as sa


revision = '0011'
down_revision = '0010'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # =========================================================
    # Phase 1: 移除廢棄表（先移除外鍵約束）
    # =========================================================

    # 1.1 先移除依賴 business_units 的外鍵
    # employee_identities.business_unit_id FK
    op.drop_constraint('employee_identities_business_unit_id_fkey', 'employee_identities', type_='foreignkey')

    # tenant_email_accounts.business_unit_id FK（如果存在）
    try:
        op.drop_constraint('fk_email_accounts_business_unit', 'tenant_email_accounts', type_='foreignkey')
    except:
        pass  # 可能不存在

    # 1.2 移除 employee_identities 表（已被 tenant_emp_setting 取代）
    op.drop_table('employee_identities')

    # 1.3 移除 business_units 表（已被 tenant_departments 取代）
    op.drop_table('business_units')

    # =========================================================
    # Phase 2: 為 tenant_role_rights 新增 is_active
    # =========================================================

    op.add_column('tenant_role_rights', sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true', comment='是否啟用'))

    # =========================================================
    # Phase 3: 重新命名觸發器（org_* → tenant_*）
    # =========================================================

    # 3.1 tenant_departments
    op.execute("""
        DROP TRIGGER IF EXISTS trigger_org_departments_seq_no ON tenant_departments;

        CREATE TRIGGER trigger_tenant_departments_seq_no
        BEFORE INSERT ON tenant_departments
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_seq_no();
    """)

    # 3.2 tenant_employees
    op.execute("""
        DROP TRIGGER IF EXISTS trigger_org_employees_seq_no ON tenant_employees;

        CREATE TRIGGER trigger_tenant_employees_seq_no
        BEFORE INSERT ON tenant_employees
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_seq_no();
    """)

    # 3.3 tenant_user_roles
    op.execute("""
        DROP TRIGGER IF EXISTS trigger_org_user_roles_seq_no ON tenant_user_roles;

        CREATE TRIGGER trigger_tenant_user_roles_seq_no
        BEFORE INSERT ON tenant_user_roles
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_seq_no();
    """)


def downgrade() -> None:
    # 恢復觸發器名稱
    op.execute("DROP TRIGGER IF EXISTS trigger_tenant_user_roles_seq_no ON tenant_user_roles")
    op.execute("""
        CREATE TRIGGER trigger_org_user_roles_seq_no
        BEFORE INSERT ON tenant_user_roles
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_seq_no()
    """)

    op.execute("DROP TRIGGER IF EXISTS trigger_tenant_employees_seq_no ON tenant_employees")
    op.execute("""
        CREATE TRIGGER trigger_org_employees_seq_no
        BEFORE INSERT ON tenant_employees
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_seq_no()
    """)

    op.execute("DROP TRIGGER IF EXISTS trigger_tenant_departments_seq_no ON tenant_departments")
    op.execute("""
        CREATE TRIGGER trigger_org_departments_seq_no
        BEFORE INSERT ON tenant_departments
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_seq_no()
    """)

    # 移除 is_active
    op.drop_column('tenant_role_rights', 'is_active')

    # 恢復廢棄表（不實現，downgrade 不支援重建複雜資料）
    # op.create_table('employee_identities', ...)
    # op.create_table('business_units', ...)