"""multi-tenant architecture refactor

Revision ID: 0005
Revises: 0004
Create Date: 2026-02-19 00:00:00.000000

重構內容:
1. departments 改為統一樹狀結構 (新增 parent_id, email_domain, depth)
2. business_units 資料遷移為 departments 第一層節點，廢棄 business_units 表
3. 新增 department_members 表 (員工多部門歸屬)
4. employee_identities 資料遷移至 department_members，標記廢棄
5. employees 新增 keycloak_user_id (唯一 SSO 識別碼)
6. tenants 新增 keycloak_realm (= tenant_code)
7. 新增 RBAC: system_functions_cache, roles, role_rights, user_role_assignments
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa


revision: str = '0005'
down_revision: Union[str, None] = '0004'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # =========================================================
    # A-1: 修改 departments 表 - 新增樹狀結構欄位
    # =========================================================
    op.add_column('departments', sa.Column('parent_id', sa.Integer(), nullable=True))
    op.add_column('departments', sa.Column('email_domain', sa.String(100), nullable=True))
    op.add_column('departments', sa.Column('depth', sa.Integer(), server_default='1', nullable=False))

    # 新增自我參照外鍵 (parent_id → departments.id)
    op.create_foreign_key(
        'fk_departments_parent',
        'departments', 'departments',
        ['parent_id'], ['id'],
        ondelete='CASCADE'
    )

    # 新增索引
    op.create_index('idx_departments_parent', 'departments', ['parent_id'])
    op.create_index('idx_departments_depth', 'departments', ['depth'])

    # =========================================================
    # A-2: 將 business_units 資料遷移為 departments 第一層節點
    # =========================================================

    # 先將 business_unit_id 改為 nullable (新插入的第一層節點不需要此欄位)
    op.alter_column('departments', 'business_unit_id', nullable=True)

    # 先將現有 departments 的 depth 設為 1 (它們都是原本的子部門)
    op.execute("UPDATE departments SET depth = 1")

    # 將 business_units 插入 departments 為第一層節點 (depth=0, parent_id=NULL)
    op.execute("""
        INSERT INTO departments (tenant_id, code, name, email_domain, parent_id, depth, is_active, created_at, email_address, email_quota_mb)
        SELECT
            bu.tenant_id,
            bu.code,
            bu.name,
            bu.email_domain,
            NULL,
            0,
            bu.is_active,
            bu.created_at,
            bu.email_address,
            bu.email_quota_mb
        FROM business_units bu
    """)

    # 更新原有子部門，parent_id 指向剛插入的第一層節點
    op.execute("""
        UPDATE departments AS d
        SET parent_id = top.id
        FROM departments AS top
        JOIN business_units bu ON bu.code = top.code AND bu.tenant_id = top.tenant_id
        WHERE d.business_unit_id = bu.id
          AND top.depth = 0
          AND d.depth = 1
    """)

    # 更新 unique constraint (移除舊的，建立新的)
    op.drop_constraint('uq_department_bu_code', 'departments', type_='unique')
    op.create_unique_constraint(
        'uq_tenant_parent_dept_code',
        'departments',
        ['tenant_id', 'parent_id', 'code']
    )

    # 移除 departments.business_unit_id 外鍵和欄位
    op.drop_constraint('departments_business_unit_id_fkey', 'departments', type_='foreignkey')
    op.drop_index('ix_departments_business_unit_id', table_name='departments')
    op.drop_column('departments', 'business_unit_id')

    # 重建 tenant 索引 (原名 idx_departments_tenant 已存在，建立新名稱)
    op.create_index('idx_dept_tenant_id', 'departments', ['tenant_id'])

    # =========================================================
    # A-3: 新增 department_members 表
    # =========================================================
    op.create_table(
        'department_members',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=False, comment='租戶 ID'),
        sa.Column('employee_id', sa.Integer(), nullable=False, comment='員工 ID'),
        sa.Column('department_id', sa.Integer(), nullable=False, comment='部門 ID'),
        sa.Column('position', sa.String(100), nullable=True, comment='在該部門的職稱'),
        sa.Column('membership_type', sa.String(50), server_default='permanent', nullable=False,
                  comment='成員類型: permanent/temporary/project'),
        sa.Column('is_active', sa.Boolean(), server_default='true', nullable=False),
        sa.Column('joined_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.Column('ended_at', sa.DateTime(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['department_id'], ['departments.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('employee_id', 'department_id', name='uq_employee_department'),
    )
    op.create_index('idx_dept_members_tenant', 'department_members', ['tenant_id'])
    op.create_index('idx_dept_members_employee', 'department_members', ['employee_id'])
    op.create_index('idx_dept_members_department', 'department_members', ['department_id'])

    # =========================================================
    # A-4: 遷移 employee_identities → department_members
    # =========================================================
    op.execute("""
        INSERT INTO department_members (tenant_id, employee_id, department_id, position, is_active, joined_at)
        SELECT
            ei.tenant_id,
            ei.employee_id,
            ei.department_id,
            ei.job_title,
            ei.is_active,
            COALESCE(ei.started_at, ei.created_at)
        FROM employee_identities ei
        WHERE ei.department_id IS NOT NULL
        ON CONFLICT (employee_id, department_id) DO NOTHING
    """)

    # 標記 employee_identities 為廢棄
    op.add_column('employee_identities', sa.Column(
        'deprecated_at', sa.DateTime(),
        server_default=sa.text('now()'),
        nullable=True,
        comment='廢棄標記 - 資料已遷移至 department_members'
    ))

    # =========================================================
    # A-5: employees 新增 keycloak_user_id
    # =========================================================
    op.add_column('employees', sa.Column(
        'keycloak_user_id', sa.String(36),
        nullable=True,
        unique=True,
        comment='Keycloak User UUID (唯一 SSO 識別碼，永久不變)'
    ))
    op.create_index('idx_employees_keycloak', 'employees', ['keycloak_user_id'])

    # =========================================================
    # A-6: tenants 新增 keycloak_realm
    # =========================================================
    op.add_column('tenants', sa.Column(
        'keycloak_realm', sa.String(100),
        nullable=True,
        unique=True,
        comment='Keycloak Realm 名稱 (等同 tenant_code)'
    ))
    op.create_index('idx_tenants_realm', 'tenants', ['keycloak_realm'])

    # 為現有租戶設定 keycloak_realm = tenant_code
    op.execute("UPDATE tenants SET keycloak_realm = code WHERE keycloak_realm IS NULL")

    # =========================================================
    # A-7: 新增 RBAC 表
    # =========================================================

    # system_functions_cache (從 System Admin 同步的系統功能定義)
    op.create_table(
        'system_functions_cache',
        sa.Column('id', sa.Integer(), nullable=False, comment='與 System Admin 的 id 一致'),
        sa.Column('service_code', sa.String(50), nullable=False, comment='服務代碼: hr/erp/mail/ai'),
        sa.Column('function_code', sa.String(100), nullable=False, comment='功能代碼: HR_EMPLOYEE_VIEW'),
        sa.Column('function_name', sa.String(200), nullable=False, comment='功能名稱'),
        sa.Column('function_category', sa.String(50), nullable=True, comment='功能分類: query/manage/approve/report'),
        sa.Column('is_active', sa.Boolean(), server_default='true', nullable=False),
        sa.Column('synced_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False,
                  comment='最後同步時間'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('function_code', name='uq_function_code'),
    )
    op.create_index('idx_func_cache_service', 'system_functions_cache', ['service_code'])
    op.create_index('idx_func_cache_category', 'system_functions_cache', ['function_category'])

    # roles (租戶層級角色)
    op.create_table(
        'roles',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=False, comment='租戶 ID'),
        sa.Column('role_code', sa.String(100), nullable=False, comment='角色代碼 (租戶內唯一)'),
        sa.Column('role_name', sa.String(200), nullable=False, comment='角色名稱'),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('is_active', sa.Boolean(), server_default='true', nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('tenant_id', 'role_code', name='uq_tenant_role_code'),
    )
    op.create_index('idx_roles_tenant', 'roles', ['tenant_id'])

    # role_rights (角色 → 系統功能 CRUD 權限)
    op.create_table(
        'role_rights',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('role_id', sa.Integer(), nullable=False),
        sa.Column('function_id', sa.Integer(), nullable=False),
        sa.Column('can_read', sa.Boolean(), server_default='false', nullable=False),
        sa.Column('can_create', sa.Boolean(), server_default='false', nullable=False),
        sa.Column('can_update', sa.Boolean(), server_default='false', nullable=False),
        sa.Column('can_delete', sa.Boolean(), server_default='false', nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.ForeignKeyConstraint(['role_id'], ['roles.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['function_id'], ['system_functions_cache.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('role_id', 'function_id', name='uq_role_function'),
    )
    op.create_index('idx_role_rights_role', 'role_rights', ['role_id'])
    op.create_index('idx_role_rights_function', 'role_rights', ['function_id'])

    # user_role_assignments (使用者角色分配，直接對人不對部門)
    op.create_table(
        'user_role_assignments',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tenant_id', sa.Integer(), nullable=False, comment='租戶 ID'),
        sa.Column('keycloak_user_id', sa.String(36), nullable=False, comment='Keycloak User UUID'),
        sa.Column('role_id', sa.Integer(), nullable=False),
        sa.Column('is_active', sa.Boolean(), server_default='true', nullable=False),
        sa.Column('assigned_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.Column('assigned_by', sa.String(36), nullable=True, comment='分配者 keycloak_user_id'),
        sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['role_id'], ['roles.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('keycloak_user_id', 'role_id', name='uq_user_role'),
    )
    op.create_index('idx_user_roles_tenant', 'user_role_assignments', ['tenant_id'])
    op.create_index('idx_user_roles_keycloak', 'user_role_assignments', ['keycloak_user_id'])

    # =========================================================
    # A-8: Seed data - HR 系統功能 + 初始角色
    # =========================================================
    op.execute("""
        INSERT INTO system_functions_cache (id, service_code, function_code, function_name, function_category) VALUES
        (1, 'hr', 'HR_EMPLOYEE_VIEW', '員工查詢', 'query'),
        (2, 'hr', 'HR_EMPLOYEE_MANAGE', '員工管理', 'manage'),
        (3, 'hr', 'HR_DEPT_MANAGE', '部門管理', 'manage'),
        (4, 'hr', 'HR_ROLE_MANAGE', '角色管理', 'manage'),
        (5, 'hr', 'HR_AUDIT_VIEW', '審計日誌查詢', 'query')
    """)

    op.execute("""
        INSERT INTO roles (tenant_id, role_code, role_name, description) VALUES
        (1, 'HR_ADMIN', '人資管理員', '可管理員工資料、組織架構、角色分配'),
        (1, 'HR_VIEWER', '人資查詢者', '只能查詢員工資料'),
        (1, 'SYSTEM_ADMIN', '系統管理員', '擁有所有 HR 系統功能權限')
    """)

    # HR_ADMIN 擁有所有 HR 功能的完整權限
    op.execute("""
        INSERT INTO role_rights (role_id, function_id, can_read, can_create, can_update, can_delete)
        SELECT r.id, f.id,
            true,
            CASE WHEN f.function_category = 'manage' THEN true ELSE false END,
            CASE WHEN f.function_category IN ('manage', 'approve') THEN true ELSE false END,
            CASE WHEN f.function_category = 'manage' THEN true ELSE false END
        FROM roles r, system_functions_cache f
        WHERE r.role_code = 'HR_ADMIN' AND r.tenant_id = 1
    """)

    # HR_VIEWER 只有查詢權限
    op.execute("""
        INSERT INTO role_rights (role_id, function_id, can_read, can_create, can_update, can_delete)
        SELECT r.id, f.id, true, false, false, false
        FROM roles r, system_functions_cache f
        WHERE r.role_code = 'HR_VIEWER' AND r.tenant_id = 1
          AND f.function_category = 'query'
    """)

    # SYSTEM_ADMIN 擁有所有功能的完整 CRUD
    op.execute("""
        INSERT INTO role_rights (role_id, function_id, can_read, can_create, can_update, can_delete)
        SELECT r.id, f.id, true, true, true, true
        FROM roles r, system_functions_cache f
        WHERE r.role_code = 'SYSTEM_ADMIN' AND r.tenant_id = 1
    """)


def downgrade() -> None:
    # 移除 RBAC 表
    op.drop_table('user_role_assignments')
    op.drop_table('role_rights')
    op.drop_table('roles')
    op.drop_table('system_functions_cache')

    # 移除 keycloak 欄位
    op.drop_index('idx_tenants_realm', table_name='tenants')
    op.drop_column('tenants', 'keycloak_realm')

    op.drop_index('idx_employees_keycloak', table_name='employees')
    op.drop_column('employees', 'keycloak_user_id')

    # 移除廢棄標記
    op.drop_column('employee_identities', 'deprecated_at')

    # 移除 department_members
    op.drop_table('department_members')

    # 還原 departments (重新加回 business_unit_id) - 注意: 資料無法完全還原
    op.add_column('departments', sa.Column('business_unit_id', sa.Integer(), nullable=True))
    op.create_foreign_key(
        'fk_departments_business_unit',
        'departments', 'business_units',
        ['business_unit_id'], ['id'],
        ondelete='CASCADE'
    )
    op.drop_constraint('uq_tenant_parent_dept_code', 'departments', type_='unique')
    op.create_unique_constraint(
        'uq_tenant_bu_dept_code',
        'departments',
        ['tenant_id', 'business_unit_id', 'code']
    )
    op.drop_index('idx_dept_tenant_id', table_name='departments')
    op.drop_index('idx_departments_parent', table_name='departments')
    op.drop_index('idx_departments_depth', table_name='departments')
    op.drop_constraint('fk_departments_parent', 'departments', type_='foreignkey')
    op.drop_column('departments', 'parent_id')
    op.drop_column('departments', 'email_domain')
    op.drop_column('departments', 'depth')
    op.create_index('idx_dept_tenant', 'departments', ['tenant_id'])
