"""add_employee_relations_audit_fields

Revision ID: 0526fc6e6496
Revises: 0012
Create Date: 2026-02-21 00:33:22.528147

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = '0526fc6e6496'
down_revision: Union[str, None] = '0012'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('personal_services', sa.Column('description', sa.String(length=500), nullable=True, comment='服務說明'))
    op.alter_column('personal_services', 'service_code',
               existing_type=sa.VARCHAR(length=50),
               type_=sa.String(length=20),
               comment='服務代碼: SSO/Email/Calendar/Drive/Office',
               existing_comment='服務代碼',
               existing_nullable=False)
    op.alter_column('personal_services', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('personal_services', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('personal_services', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.create_index(op.f('ix_personal_services_id'), 'personal_services', ['id'], unique=False)
    op.create_unique_constraint('uq_personal_service_code', 'personal_services', ['service_code'])
    op.drop_column('system_functions_cache', 'created_at')
    op.drop_column('system_functions_cache', 'updated_at')
    op.drop_column('system_functions_cache', 'edit_by')
    op.alter_column('tenant_audit_logs', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment='租戶 ID',
               existing_nullable=False)
    op.alter_column('tenant_audit_logs', 'resource_type',
               existing_type=sa.VARCHAR(length=50),
               comment='資源類型 (employee/department/role)',
               existing_comment='資源類型 (employee/identity/department)',
               existing_nullable=False)
    op.alter_column('tenant_audit_logs', 'performed_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='操作時間',
               existing_nullable=False)
    op.alter_column('tenant_audit_logs', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_audit_logs', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_audit_logs', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.drop_index(op.f('idx_audit_logs_tenant'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_audit_logs_action'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_audit_logs_id'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_audit_logs_performed_at'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_audit_logs_performed_by'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_audit_logs_resource_id'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_audit_logs_resource_type'), table_name='tenant_audit_logs')
    op.create_index('idx_audit_tenant_action', 'tenant_audit_logs', ['tenant_id', 'action'], unique=False)
    op.create_index('idx_audit_tenant_resource', 'tenant_audit_logs', ['tenant_id', 'resource_type', 'resource_id'], unique=False)
    op.create_index('idx_audit_tenant_time', 'tenant_audit_logs', ['tenant_id', 'performed_at'], unique=False)
    op.create_index(op.f('ix_tenant_audit_logs_action'), 'tenant_audit_logs', ['action'], unique=False)
    op.create_index(op.f('ix_tenant_audit_logs_id'), 'tenant_audit_logs', ['id'], unique=False)
    op.create_index(op.f('ix_tenant_audit_logs_performed_at'), 'tenant_audit_logs', ['performed_at'], unique=False)
    op.create_index(op.f('ix_tenant_audit_logs_performed_by'), 'tenant_audit_logs', ['performed_by'], unique=False)
    op.create_index(op.f('ix_tenant_audit_logs_resource_id'), 'tenant_audit_logs', ['resource_id'], unique=False)
    op.create_index(op.f('ix_tenant_audit_logs_resource_type'), 'tenant_audit_logs', ['resource_type'], unique=False)
    op.create_index(op.f('ix_tenant_audit_logs_tenant_id'), 'tenant_audit_logs', ['tenant_id'], unique=False)
    op.alter_column('tenant_batch_logs', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_batch_logs', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_batch_logs', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.drop_index(op.f('ix_batch_logs_batch_name'), table_name='tenant_batch_logs')
    op.drop_index(op.f('ix_batch_logs_id'), table_name='tenant_batch_logs')
    op.drop_index(op.f('ix_batch_logs_started_at'), table_name='tenant_batch_logs')
    op.create_index(op.f('ix_tenant_batch_logs_batch_name'), 'tenant_batch_logs', ['batch_name'], unique=False)
    op.create_index(op.f('ix_tenant_batch_logs_id'), 'tenant_batch_logs', ['id'], unique=False)
    op.create_index(op.f('ix_tenant_batch_logs_started_at'), 'tenant_batch_logs', ['started_at'], unique=False)
    op.alter_column('tenant_departments', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment='租戶 ID',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號 (觸發器自動生成)',
               existing_comment='租戶內序號',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'parent_id',
               existing_type=sa.INTEGER(),
               comment='上層部門 ID (NULL=第一層，即原事業部)',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'code',
               existing_type=sa.VARCHAR(length=20),
               comment='部門代碼 (同層內唯一)',
               existing_comment='部門代碼 (在同一事業部內唯一)',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'email_domain',
               existing_type=sa.VARCHAR(length=100),
               comment='郵件網域 (只有 depth=0 可設定，例如 ease.taipei)',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'email_address',
               existing_type=sa.VARCHAR(length=255),
               comment='部門信箱 (例如: wind@ease.taipei)',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'email_quota_mb',
               existing_type=sa.INTEGER(),
               comment='部門信箱配額 (MB)',
               existing_nullable=False,
               existing_server_default=sa.text('5120'))
    op.alter_column('tenant_departments', 'depth',
               existing_type=sa.INTEGER(),
               comment='層次深度 (0=第一層，1=第二層，以此類推)',
               existing_nullable=False,
               existing_server_default=sa.text('1'))
    op.alter_column('tenant_departments', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment='是否啟用',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.drop_index(op.f('idx_departments_tenant'), table_name='tenant_departments')
    op.drop_index(op.f('idx_org_departments_tenant_seq'), table_name='tenant_departments')
    op.drop_index(op.f('ix_departments_id'), table_name='tenant_departments')
    op.create_index(op.f('ix_tenant_departments_id'), 'tenant_departments', ['id'], unique=False)
    op.create_index(op.f('ix_tenant_departments_tenant_id'), 'tenant_departments', ['tenant_id'], unique=False)
    op.create_unique_constraint('uq_tenant_dept_seq', 'tenant_departments', ['tenant_id', 'seq_no'])
    op.add_column('tenant_dept_members', sa.Column('assigned_by', sa.String(length=36), nullable=True, comment='分配者 keycloak_user_id'))
    op.add_column('tenant_dept_members', sa.Column('removed_by', sa.String(length=36), nullable=True, comment='移除者 keycloak_user_id'))
    op.alter_column('tenant_dept_members', 'joined_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='加入時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_dept_members', 'ended_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='離開時間（軟刪除）',
               existing_nullable=True)
    op.alter_column('tenant_dept_members', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_dept_members', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               type_=sa.String(length=36),
               comment='最後編輯者 keycloak_user_id',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_dept_members', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_dept_members', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='更新時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.create_index(op.f('ix_tenant_dept_members_id'), 'tenant_dept_members', ['id'], unique=False)
    op.alter_column('tenant_email_accounts', 'account_type',
               existing_type=sa.VARCHAR(length=20),
               comment='帳號類型: personal(個人), department(部門)',
               existing_nullable=False,
               existing_server_default=sa.text("'personal'::character varying"))
    op.alter_column('tenant_email_accounts', 'employee_id',
               existing_type=sa.INTEGER(),
               nullable=True,
               comment='員工 ID (僅 personal 類型需要)',
               existing_comment='員工 ID')
    op.alter_column('tenant_email_accounts', 'department_id',
               existing_type=sa.INTEGER(),
               comment='部門 ID (僅 department 類型需要)',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'email_address',
               existing_type=sa.VARCHAR(length=255),
               comment='郵件地址 (例如: porsche.chen@lab.taipei)',
               existing_comment='郵件地址',
               existing_nullable=False)
    op.alter_column('tenant_email_accounts', 'quota_mb',
               existing_type=sa.INTEGER(),
               comment='配額 (MB),依職級: Junior=2048, Mid=3072, Senior=5120, Manager=10240',
               existing_comment='配額 (MB)',
               existing_nullable=False,
               existing_server_default=sa.text('2048'))
    op.alter_column('tenant_email_accounts', 'forward_to',
               existing_type=sa.VARCHAR(length=255),
               comment='轉寄地址 (可選,例如外部郵箱)',
               existing_comment='轉寄地址',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'auto_reply',
               existing_type=sa.TEXT(),
               comment='自動回覆內容 (可選,例如休假通知)',
               existing_comment='自動回覆內容',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.create_index(op.f('ix_tenant_email_accounts_department_id'), 'tenant_email_accounts', ['department_id'], unique=False)
    op.create_index(op.f('ix_tenant_email_accounts_email_address'), 'tenant_email_accounts', ['email_address'], unique=True)
    op.create_index(op.f('ix_tenant_email_accounts_employee_id'), 'tenant_email_accounts', ['employee_id'], unique=False)
    op.create_index(op.f('ix_tenant_email_accounts_id'), 'tenant_email_accounts', ['id'], unique=False)
    op.create_index(op.f('ix_tenant_email_accounts_tenant_id'), 'tenant_email_accounts', ['tenant_id'], unique=False)
    op.drop_column('tenant_email_accounts', 'business_unit_id')
    op.add_column('tenant_emp_personal_service_settings', sa.Column('keycloak_user_id', sa.String(length=36), nullable=False, comment='Keycloak User UUID'))
    op.alter_column('tenant_emp_personal_service_settings', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_personal_service_settings', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.drop_index(op.f('idx_emp_service_user'), table_name='tenant_emp_personal_service_settings')
    op.create_index('idx_emp_service_user', 'tenant_emp_personal_service_settings', ['keycloak_user_id'], unique=False)
    op.drop_constraint(op.f('uq_emp_service'), 'tenant_emp_personal_service_settings', type_='unique')
    op.create_unique_constraint('uq_emp_service', 'tenant_emp_personal_service_settings', ['tenant_id', 'keycloak_user_id', 'service_id'])
    op.create_index(op.f('ix_tenant_emp_personal_service_settings_id'), 'tenant_emp_personal_service_settings', ['id'], unique=False)
    op.drop_column('tenant_emp_personal_service_settings', 'tenant_keycloak_user_id')
    op.add_column('tenant_emp_resumes', sa.Column('legal_name', sa.String(length=100), nullable=False, comment='法定姓名'))
    op.add_column('tenant_emp_resumes', sa.Column('english_name', sa.String(length=100), nullable=True, comment='英文名稱'))
    op.add_column('tenant_emp_resumes', sa.Column('id_number', sa.String(length=20), nullable=False, comment='身分證字號/護照號碼'))
    op.add_column('tenant_emp_resumes', sa.Column('birth_date', sa.Date(), nullable=True, comment='出生日期'))
    op.add_column('tenant_emp_resumes', sa.Column('gender', sa.String(length=10), nullable=True, comment='性別: M/F/Other'))
    op.add_column('tenant_emp_resumes', sa.Column('marital_status', sa.String(length=20), nullable=True, comment='婚姻狀況: single/married/divorced/widowed'))
    op.add_column('tenant_emp_resumes', sa.Column('nationality', sa.String(length=50), nullable=True, comment='國籍'))
    op.add_column('tenant_emp_resumes', sa.Column('phone', sa.String(length=20), nullable=True, comment='聯絡電話'))
    op.add_column('tenant_emp_resumes', sa.Column('mobile', sa.String(length=20), nullable=True, comment='手機'))
    op.add_column('tenant_emp_resumes', sa.Column('personal_email', sa.String(length=255), nullable=True, comment='個人郵箱'))
    op.add_column('tenant_emp_resumes', sa.Column('address', sa.Text(), nullable=True, comment='通訊地址'))
    op.add_column('tenant_emp_resumes', sa.Column('emergency_phone', sa.String(length=20), nullable=True, comment='緊急聯絡電話'))
    op.add_column('tenant_emp_resumes', sa.Column('education_level', sa.String(length=50), nullable=True, comment='學歷: high_school/bachelor/master/phd'))
    op.add_column('tenant_emp_resumes', sa.Column('school_name', sa.String(length=200), nullable=True, comment='畢業學校'))
    op.add_column('tenant_emp_resumes', sa.Column('major', sa.String(length=100), nullable=True, comment='主修科系'))
    op.add_column('tenant_emp_resumes', sa.Column('graduation_year', sa.Integer(), nullable=True, comment='畢業年份'))
    op.add_column('tenant_emp_resumes', sa.Column('notes', sa.Text(), nullable=True, comment='備註'))
    op.alter_column('tenant_emp_resumes', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment='租戶 ID',
               existing_nullable=False)
    op.alter_column('tenant_emp_resumes', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號 (觸發器自動生成)',
               existing_comment='租戶內序號',
               existing_nullable=False)
    op.alter_column('tenant_emp_resumes', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_emp_resumes', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_emp_resumes', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_resumes', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.create_index('idx_emp_resume_tenant', 'tenant_emp_resumes', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_tenant_emp_resumes_id'), 'tenant_emp_resumes', ['id'], unique=False)
    op.create_unique_constraint('uq_tenant_id_number', 'tenant_emp_resumes', ['tenant_id', 'id_number'])
    op.drop_column('tenant_emp_resumes', 'name_eng')
    op.drop_column('tenant_emp_resumes', 'name_tw')
    op.drop_column('tenant_emp_resumes', 'personal_id')
    op.drop_column('tenant_emp_resumes', 'academic_qualification')
    op.drop_column('tenant_emp_resumes', 'personal_add')
    op.drop_column('tenant_emp_resumes', 'emergency_tel')
    op.drop_column('tenant_emp_resumes', 'personal_tel')
    op.add_column('tenant_emp_settings', sa.Column('resign_date', sa.Date(), nullable=True, comment='離職日期'))
    op.add_column('tenant_emp_settings', sa.Column('job_title', sa.String(length=100), nullable=True, comment='職稱'))
    op.add_column('tenant_emp_settings', sa.Column('employment_type', sa.String(length=50), nullable=False, comment='任用類型: full_time/part_time/contractor/intern'))
    op.add_column('tenant_emp_settings', sa.Column('salary_amount', sa.Integer(), nullable=True, comment='月薪（加密）'))
    op.add_column('tenant_emp_settings', sa.Column('salary_currency', sa.String(length=10), nullable=True, comment='薪資幣別'))
    op.add_column('tenant_emp_settings', sa.Column('primary_dept_id', sa.Integer(), nullable=True, comment='主要部門 ID'))
    op.add_column('tenant_emp_settings', sa.Column('employment_status', sa.String(length=20), nullable=False, comment='任用狀態: active/on_leave/resigned/terminated'))
    op.alter_column('tenant_emp_settings', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號 (觸發器自動生成)',
               existing_comment='租戶內序號',
               existing_nullable=False)
    op.alter_column('tenant_emp_settings', 'tenant_resume_id',
               existing_type=sa.INTEGER(),
               comment='人員基本檔 ID（一個人只有一筆任用設定）',
               existing_comment='人員基本資料 ID',
               existing_nullable=False)
    op.alter_column('tenant_emp_settings', 'tenant_emp_code',
               existing_type=sa.VARCHAR(length=20),
               comment='員工編號（自動生成，格式: prefix + seq_no，例如 PWD0001）',
               existing_comment='員工工號（自動生成：prefix + seq_no）',
               existing_nullable=False)
    op.alter_column('tenant_emp_settings', 'tenant_keycloak_user_id',
               existing_type=sa.VARCHAR(length=36),
               comment='Keycloak User UUID (唯一 SSO 識別碼，永久不變)',
               existing_comment='Keycloak User UUID',
               existing_nullable=True)
    op.alter_column('tenant_emp_settings', 'tenant_keycloak_username',
               existing_type=sa.VARCHAR(length=100),
               comment='Keycloak 登入帳號',
               existing_comment='Keycloak 使用者帳號',
               existing_nullable=True)
    op.alter_column('tenant_emp_settings', 'storage_quota_gb',
               existing_type=sa.INTEGER(),
               comment='儲存配額 (GB) - Drive 使用',
               existing_comment='配額設定（GB）',
               existing_nullable=False,
               existing_server_default=sa.text('20'))
    op.alter_column('tenant_emp_settings', 'email_quota_mb',
               existing_type=sa.INTEGER(),
               comment='郵件配額 (MB) - Email 使用',
               existing_comment='郵件配額 (MB)',
               existing_nullable=False,
               existing_server_default=sa.text('5120'))
    op.alter_column('tenant_emp_settings', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_emp_settings', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               type_=sa.String(length=36),
               comment='最後編輯者 keycloak_user_id',
               existing_nullable=True)
    op.alter_column('tenant_emp_settings', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_settings', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.drop_index(op.f('idx_tenant_emp_settings_resume'), table_name='tenant_emp_settings')
    op.drop_index(op.f('idx_tenant_emp_settings_tenant'), table_name='tenant_emp_settings')
    op.drop_constraint(op.f('uq_tenant_resume'), 'tenant_emp_settings', type_='unique')
    op.create_index('idx_emp_setting_tenant', 'tenant_emp_settings', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_tenant_emp_settings_tenant_emp_code'), 'tenant_emp_settings', ['tenant_emp_code'], unique=False)
    op.create_index(op.f('ix_tenant_emp_settings_tenant_keycloak_user_id'), 'tenant_emp_settings', ['tenant_keycloak_user_id'], unique=True)
    op.create_unique_constraint('uq_tenant_resume_setting', 'tenant_emp_settings', ['tenant_id', 'tenant_resume_id'])
    op.create_unique_constraint(None, 'tenant_emp_settings', ['tenant_keycloak_username'])
    op.drop_constraint(op.f('tenant_emp_setting_tenant_resume_id_fkey'), 'tenant_emp_settings', type_='foreignkey')
    op.create_foreign_key(None, 'tenant_emp_settings', 'tenant_emp_resumes', ['tenant_resume_id'], ['id'], ondelete='RESTRICT')
    op.create_foreign_key(None, 'tenant_emp_settings', 'tenant_departments', ['primary_dept_id'], ['id'], ondelete='SET NULL')
    op.alter_column('tenant_employees', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment='租戶 ID',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號 (自動從1開始)',
               existing_comment='租戶內序號',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'employee_id',
               existing_type=sa.VARCHAR(length=20),
               comment='員工編號 (EMP001, 租戶內唯一，永久不變)',
               existing_comment='員工編號 (EMP001)',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'keycloak_user_id',
               existing_type=sa.VARCHAR(length=36),
               comment='Keycloak User UUID (唯一 SSO 識別碼，永久不變，一個員工只有一個)',
               existing_comment='Keycloak User UUID (唯一 SSO 識別碼，永久不變)',
               existing_nullable=True)
    op.alter_column('tenant_employees', 'username_base',
               existing_type=sa.VARCHAR(length=50),
               comment='基礎帳號名稱 (全系統唯一)',
               existing_comment='基礎帳號名稱 (全公司唯一)',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_employees', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='更新時間',
               existing_nullable=False)
    op.drop_constraint(op.f('employees_keycloak_user_id_key'), 'tenant_employees', type_='unique')
    op.drop_index(op.f('idx_employees_keycloak'), table_name='tenant_employees')
    op.drop_index(op.f('idx_employees_tenant'), table_name='tenant_employees')
    op.drop_index(op.f('idx_org_employees_tenant_seq'), table_name='tenant_employees')
    op.drop_index(op.f('ix_employees_employee_id'), table_name='tenant_employees')
    op.drop_index(op.f('ix_employees_id'), table_name='tenant_employees')
    op.drop_index(op.f('ix_employees_username_base'), table_name='tenant_employees')
    op.create_index(op.f('ix_tenant_employees_employee_id'), 'tenant_employees', ['employee_id'], unique=False)
    op.create_index(op.f('ix_tenant_employees_id'), 'tenant_employees', ['id'], unique=False)
    op.create_index(op.f('ix_tenant_employees_keycloak_user_id'), 'tenant_employees', ['keycloak_user_id'], unique=True)
    op.create_index(op.f('ix_tenant_employees_tenant_id'), 'tenant_employees', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_tenant_employees_username_base'), 'tenant_employees', ['username_base'], unique=True)
    op.create_unique_constraint('uq_tenant_employee_id', 'tenant_employees', ['tenant_id', 'employee_id'])
    op.create_unique_constraint('uq_tenant_seq_no', 'tenant_employees', ['tenant_id', 'seq_no'])
    op.alter_column('tenant_network_drives', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment='是否啟用',
               existing_nullable=False)
    op.alter_column('tenant_network_drives', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_network_drives', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False)
    op.alter_column('tenant_network_drives', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='更新時間',
               existing_nullable=False)
    op.drop_index(op.f('idx_network_drives_tenant'), table_name='tenant_network_drives')
    op.drop_index(op.f('ix_network_drives_employee_id'), table_name='tenant_network_drives')
    op.drop_index(op.f('ix_network_drives_id'), table_name='tenant_network_drives')
    op.create_index(op.f('ix_tenant_network_drives_employee_id'), 'tenant_network_drives', ['employee_id'], unique=True)
    op.create_index(op.f('ix_tenant_network_drives_id'), 'tenant_network_drives', ['id'], unique=False)
    op.drop_constraint(op.f('fk_network_drives_tenant'), 'tenant_network_drives', type_='foreignkey')
    op.drop_column('tenant_network_drives', 'tenant_id')
    op.alter_column('tenant_permissions', 'system_name',
               existing_type=sa.VARCHAR(length=100),
               comment='系統名稱 (gitea, portainer, traefik, keycloak)',
               existing_comment='系統名稱',
               existing_nullable=False)
    op.alter_column('tenant_permissions', 'access_level',
               existing_type=sa.VARCHAR(length=50),
               comment='存取層級 (admin/user/readonly)',
               existing_comment='存取層級',
               existing_nullable=False,
               existing_server_default=sa.text("'user'::character varying"))
    op.alter_column('tenant_permissions', 'granted_by',
               existing_type=sa.INTEGER(),
               comment='授予人 (員工 ID)',
               existing_comment='授予人',
               existing_nullable=True)
    op.alter_column('tenant_permissions', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_permissions', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_permissions', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.create_index(op.f('ix_tenant_permissions_employee_id'), 'tenant_permissions', ['employee_id'], unique=False)
    op.create_index(op.f('ix_tenant_permissions_id'), 'tenant_permissions', ['id'], unique=False)
    op.create_index(op.f('ix_tenant_permissions_system_name'), 'tenant_permissions', ['system_name'], unique=False)
    op.create_index(op.f('ix_tenant_permissions_tenant_id'), 'tenant_permissions', ['tenant_id'], unique=False)
    op.alter_column('tenant_role_rights', 'role_id',
               existing_type=sa.INTEGER(),
               comment='角色 ID',
               existing_nullable=False)
    op.alter_column('tenant_role_rights', 'function_id',
               existing_type=sa.INTEGER(),
               comment='系統功能 ID',
               existing_nullable=False)
    op.alter_column('tenant_role_rights', 'can_read',
               existing_type=sa.BOOLEAN(),
               comment='查詢權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'can_create',
               existing_type=sa.BOOLEAN(),
               comment='新增權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'can_update',
               existing_type=sa.BOOLEAN(),
               comment='修改權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'can_delete',
               existing_type=sa.BOOLEAN(),
               comment='刪除權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_role_rights', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_role_rights', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.create_index(op.f('ix_tenant_role_rights_id'), 'tenant_role_rights', ['id'], unique=False)
    op.add_column('tenant_user_role_assignments', sa.Column('revoked_at', sa.DateTime(), nullable=True, comment='撤銷時間（軟刪除）'))
    op.add_column('tenant_user_role_assignments', sa.Column('revoked_by', sa.String(length=36), nullable=True, comment='撤銷者 keycloak_user_id'))
    op.alter_column('tenant_user_role_assignments', 'keycloak_user_id',
               existing_type=sa.VARCHAR(length=36),
               comment='Keycloak User UUID (永久識別碼)',
               existing_comment='Keycloak User UUID',
               existing_nullable=False)
    op.alter_column('tenant_user_role_assignments', 'role_id',
               existing_type=sa.INTEGER(),
               comment='角色 ID',
               existing_nullable=False)
    op.alter_column('tenant_user_role_assignments', 'assigned_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='分配時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_user_role_assignments', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_user_role_assignments', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               type_=sa.String(length=36),
               comment='最後編輯者 keycloak_user_id',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_user_role_assignments', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_user_role_assignments', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.create_index(op.f('ix_tenant_user_role_assignments_id'), 'tenant_user_role_assignments', ['id'], unique=False)
    op.alter_column('tenant_user_roles', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號 (觸發器自動生成)',
               existing_comment='租戶內序號',
               existing_nullable=False)
    op.alter_column('tenant_user_roles', 'role_code',
               existing_type=sa.VARCHAR(length=100),
               comment='角色代碼 (租戶內唯一，例如 HR_ADMIN)',
               existing_comment='角色代碼 (租戶內唯一)',
               existing_nullable=False)
    op.alter_column('tenant_user_roles', 'description',
               existing_type=sa.TEXT(),
               comment='角色說明',
               existing_nullable=True)
    op.alter_column('tenant_user_roles', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_user_roles', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenant_user_roles', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_user_roles', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='更新時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.drop_index(op.f('idx_org_user_roles_tenant_seq'), table_name='tenant_user_roles')
    op.create_index(op.f('ix_tenant_user_roles_id'), 'tenant_user_roles', ['id'], unique=False)
    op.create_unique_constraint('uq_tenant_role_seq', 'tenant_user_roles', ['tenant_id', 'seq_no'])
    op.add_column('tenants', sa.Column('keycloak_realm', sa.String(length=100), nullable=True, comment='Keycloak Realm 名稱 (等同 code，每個組織一個獨立 Realm)'))
    op.add_column('tenants', sa.Column('url', sa.String(length=200), nullable=True, comment='公司網站'))
    op.add_column('tenants', sa.Column('plan_id', sa.String(length=50), nullable=True, server_default='standard', comment='方案 ID (starter/standard/enterprise)'))
    op.add_column('tenants', sa.Column('max_users', sa.Integer(), nullable=True, server_default='100', comment='最大用戶數'))
    op.add_column('tenants', sa.Column('storage_quota_gb', sa.Integer(), nullable=True, server_default='1000', comment='總儲存配額 (GB)'))
    op.alter_column('tenants', 'code',
               existing_type=sa.VARCHAR(length=50),
               comment='租戶代碼 (英文，例如 porscheworld)',
               existing_comment='租戶代碼 (唯一)',
               existing_nullable=False)
    op.alter_column('tenants', 'name',
               existing_type=sa.VARCHAR(length=200),
               comment='公司名稱',
               existing_comment='租戶名稱',
               existing_nullable=False)
    op.alter_column('tenants', 'tax_id',
               existing_type=sa.VARCHAR(length=20),
               comment='統一編號',
               existing_comment='公司統編',
               existing_nullable=True)
    op.alter_column('tenants', 'prefix',
               existing_type=sa.VARCHAR(length=10),
               comment='員工編號前綴 (例如 PWD → PWD0001)',
               existing_comment='公司簡稱（員工編號前綴）',
               existing_nullable=False,
               existing_server_default=sa.text("'ORG'::character varying"))
    op.alter_column('tenants', 'domain_set',
               existing_type=sa.INTEGER(),
               type_=sa.Text(),
               nullable=True,
               comment='網域集合 (JSON Array，例如 ["ease.taipei", "lab.taipei"])',
               existing_comment='網域條件：1=組織網域，2=部門網域',
               existing_server_default=sa.text('2'))
    op.alter_column('tenants', 'tel',
               existing_type=sa.VARCHAR(length=20),
               type_=sa.String(length=50),
               comment='公司電話',
               existing_comment='公司代表號',
               existing_nullable=True)
    op.alter_column('tenants', 'add',
               existing_type=sa.TEXT(),
               type_=sa.String(length=500),
               comment='公司地址',
               existing_comment='公司登記地址',
               existing_nullable=True)
    op.alter_column('tenants', 'is_sysmana',
               existing_type=sa.BOOLEAN(),
               comment='是否為系統管理公司 (管理其他租戶)',
               existing_comment='是否為系統管理公司',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenants', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='最後編輯者',
               existing_comment='資料編輯者',
               existing_nullable=True)
    op.alter_column('tenants', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenants', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment='更新時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.drop_index(op.f('idx_tenants_code'), table_name='tenants')
    op.drop_index(op.f('idx_tenants_status'), table_name='tenants')
    op.create_index(op.f('ix_tenants_code'), 'tenants', ['code'], unique=True)
    op.create_index(op.f('ix_tenants_id'), 'tenants', ['id'], unique=False)
    op.create_index(op.f('ix_tenants_keycloak_realm'), 'tenants', ['keycloak_realm'], unique=True)
    op.drop_column('tenants', 'contact')
    op.drop_column('tenants', 'contact_email')
    op.drop_column('tenants', 'contact_mobil')
    op.drop_column('tenants', 'domain')
    op.drop_column('tenants', 'quota')
    op.drop_column('tenants', 'fax')
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('tenants', sa.Column('fax', sa.VARCHAR(length=20), autoincrement=False, nullable=True, comment='公司傳真電話'))
    op.add_column('tenants', sa.Column('quota', sa.INTEGER(), server_default=sa.text('100'), autoincrement=False, nullable=False, comment='組織配額（GB）'))
    op.add_column('tenants', sa.Column('domain', sa.VARCHAR(length=100), autoincrement=False, nullable=True, comment='組織網域（domain_set=1時使用）'))
    op.add_column('tenants', sa.Column('contact_mobil', sa.VARCHAR(length=20), autoincrement=False, nullable=True, comment='公司聯絡人行動電話'))
    op.add_column('tenants', sa.Column('contact_email', sa.VARCHAR(length=255), autoincrement=False, nullable=True, comment='公司聯絡人電子郵件'))
    op.add_column('tenants', sa.Column('contact', sa.VARCHAR(length=100), autoincrement=False, nullable=True, comment='公司聯絡人'))
    op.drop_index(op.f('ix_tenants_keycloak_realm'), table_name='tenants')
    op.drop_index(op.f('ix_tenants_id'), table_name='tenants')
    op.drop_index(op.f('ix_tenants_code'), table_name='tenants')
    op.create_index(op.f('idx_tenants_status'), 'tenants', ['status'], unique=False)
    op.create_index(op.f('idx_tenants_code'), 'tenants', ['code'], unique=True)
    op.alter_column('tenants', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='更新時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenants', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenants', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenants', 'is_sysmana',
               existing_type=sa.BOOLEAN(),
               comment='是否為系統管理公司',
               existing_comment='是否為系統管理公司 (管理其他租戶)',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenants', 'add',
               existing_type=sa.String(length=500),
               type_=sa.TEXT(),
               comment='公司登記地址',
               existing_comment='公司地址',
               existing_nullable=True)
    op.alter_column('tenants', 'tel',
               existing_type=sa.String(length=50),
               type_=sa.VARCHAR(length=20),
               comment='公司代表號',
               existing_comment='公司電話',
               existing_nullable=True)
    op.alter_column('tenants', 'domain_set',
               existing_type=sa.Text(),
               type_=sa.INTEGER(),
               nullable=False,
               comment='網域條件：1=組織網域，2=部門網域',
               existing_comment='網域集合 (JSON Array，例如 ["ease.taipei", "lab.taipei"])',
               existing_server_default=sa.text('2'))
    op.alter_column('tenants', 'prefix',
               existing_type=sa.VARCHAR(length=10),
               comment='公司簡稱（員工編號前綴）',
               existing_comment='員工編號前綴 (例如 PWD → PWD0001)',
               existing_nullable=False,
               existing_server_default=sa.text("'ORG'::character varying"))
    op.alter_column('tenants', 'tax_id',
               existing_type=sa.VARCHAR(length=20),
               comment='公司統編',
               existing_comment='統一編號',
               existing_nullable=True)
    op.alter_column('tenants', 'name',
               existing_type=sa.VARCHAR(length=200),
               comment='租戶名稱',
               existing_comment='公司名稱',
               existing_nullable=False)
    op.alter_column('tenants', 'code',
               existing_type=sa.VARCHAR(length=50),
               comment='租戶代碼 (唯一)',
               existing_comment='租戶代碼 (英文，例如 porscheworld)',
               existing_nullable=False)
    op.drop_column('tenants', 'storage_quota_gb')
    op.drop_column('tenants', 'max_users')
    op.drop_column('tenants', 'plan_id')
    op.drop_column('tenants', 'url')
    op.drop_column('tenants', 'keycloak_realm')
    op.drop_constraint('uq_tenant_role_seq', 'tenant_user_roles', type_='unique')
    op.drop_index(op.f('ix_tenant_user_roles_id'), table_name='tenant_user_roles')
    op.create_index(op.f('idx_org_user_roles_tenant_seq'), 'tenant_user_roles', ['tenant_id', 'seq_no'], unique=True)
    op.alter_column('tenant_user_roles', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='更新時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_user_roles', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_user_roles', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_user_roles', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_user_roles', 'description',
               existing_type=sa.TEXT(),
               comment=None,
               existing_comment='角色說明',
               existing_nullable=True)
    op.alter_column('tenant_user_roles', 'role_code',
               existing_type=sa.VARCHAR(length=100),
               comment='角色代碼 (租戶內唯一)',
               existing_comment='角色代碼 (租戶內唯一，例如 HR_ADMIN)',
               existing_nullable=False)
    op.alter_column('tenant_user_roles', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號',
               existing_comment='租戶內序號 (觸發器自動生成)',
               existing_nullable=False)
    op.drop_index(op.f('ix_tenant_user_role_assignments_id'), table_name='tenant_user_role_assignments')
    op.alter_column('tenant_user_role_assignments', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_user_role_assignments', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_user_role_assignments', 'edit_by',
               existing_type=sa.String(length=36),
               type_=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者 keycloak_user_id',
               existing_nullable=True)
    op.alter_column('tenant_user_role_assignments', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_user_role_assignments', 'assigned_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='分配時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_user_role_assignments', 'role_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='角色 ID',
               existing_nullable=False)
    op.alter_column('tenant_user_role_assignments', 'keycloak_user_id',
               existing_type=sa.VARCHAR(length=36),
               comment='Keycloak User UUID',
               existing_comment='Keycloak User UUID (永久識別碼)',
               existing_nullable=False)
    op.drop_column('tenant_user_role_assignments', 'revoked_by')
    op.drop_column('tenant_user_role_assignments', 'revoked_at')
    op.drop_index(op.f('ix_tenant_role_rights_id'), table_name='tenant_role_rights')
    op.alter_column('tenant_role_rights', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_role_rights', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_role_rights', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_role_rights', 'can_delete',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='刪除權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'can_update',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='修改權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'can_create',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='新增權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'can_read',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='查詢權限',
               existing_nullable=False,
               existing_server_default=sa.text('false'))
    op.alter_column('tenant_role_rights', 'function_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='系統功能 ID',
               existing_nullable=False)
    op.alter_column('tenant_role_rights', 'role_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='角色 ID',
               existing_nullable=False)
    op.drop_index(op.f('ix_tenant_permissions_tenant_id'), table_name='tenant_permissions')
    op.drop_index(op.f('ix_tenant_permissions_system_name'), table_name='tenant_permissions')
    op.drop_index(op.f('ix_tenant_permissions_id'), table_name='tenant_permissions')
    op.drop_index(op.f('ix_tenant_permissions_employee_id'), table_name='tenant_permissions')
    op.alter_column('tenant_permissions', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_permissions', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_permissions', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_permissions', 'granted_by',
               existing_type=sa.INTEGER(),
               comment='授予人',
               existing_comment='授予人 (員工 ID)',
               existing_nullable=True)
    op.alter_column('tenant_permissions', 'access_level',
               existing_type=sa.VARCHAR(length=50),
               comment='存取層級',
               existing_comment='存取層級 (admin/user/readonly)',
               existing_nullable=False,
               existing_server_default=sa.text("'user'::character varying"))
    op.alter_column('tenant_permissions', 'system_name',
               existing_type=sa.VARCHAR(length=100),
               comment='系統名稱',
               existing_comment='系統名稱 (gitea, portainer, traefik, keycloak)',
               existing_nullable=False)
    op.add_column('tenant_network_drives', sa.Column('tenant_id', sa.INTEGER(), autoincrement=False, nullable=False))
    op.create_foreign_key(op.f('fk_network_drives_tenant'), 'tenant_network_drives', 'tenants', ['tenant_id'], ['id'], ondelete='CASCADE')
    op.drop_index(op.f('ix_tenant_network_drives_id'), table_name='tenant_network_drives')
    op.drop_index(op.f('ix_tenant_network_drives_employee_id'), table_name='tenant_network_drives')
    op.create_index(op.f('ix_network_drives_id'), 'tenant_network_drives', ['id'], unique=False)
    op.create_index(op.f('ix_network_drives_employee_id'), 'tenant_network_drives', ['employee_id'], unique=True)
    op.create_index(op.f('idx_network_drives_tenant'), 'tenant_network_drives', ['tenant_id'], unique=False)
    op.alter_column('tenant_network_drives', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='更新時間',
               existing_nullable=False)
    op.alter_column('tenant_network_drives', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False)
    op.alter_column('tenant_network_drives', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_network_drives', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='是否啟用',
               existing_nullable=False)
    op.drop_constraint('uq_tenant_seq_no', 'tenant_employees', type_='unique')
    op.drop_constraint('uq_tenant_employee_id', 'tenant_employees', type_='unique')
    op.drop_index(op.f('ix_tenant_employees_username_base'), table_name='tenant_employees')
    op.drop_index(op.f('ix_tenant_employees_tenant_id'), table_name='tenant_employees')
    op.drop_index(op.f('ix_tenant_employees_keycloak_user_id'), table_name='tenant_employees')
    op.drop_index(op.f('ix_tenant_employees_id'), table_name='tenant_employees')
    op.drop_index(op.f('ix_tenant_employees_employee_id'), table_name='tenant_employees')
    op.create_index(op.f('ix_employees_username_base'), 'tenant_employees', ['username_base'], unique=True)
    op.create_index(op.f('ix_employees_id'), 'tenant_employees', ['id'], unique=False)
    op.create_index(op.f('ix_employees_employee_id'), 'tenant_employees', ['employee_id'], unique=True)
    op.create_index(op.f('idx_org_employees_tenant_seq'), 'tenant_employees', ['tenant_id', 'seq_no'], unique=True)
    op.create_index(op.f('idx_employees_tenant'), 'tenant_employees', ['tenant_id'], unique=False)
    op.create_index(op.f('idx_employees_keycloak'), 'tenant_employees', ['keycloak_user_id'], unique=False)
    op.create_unique_constraint(op.f('employees_keycloak_user_id_key'), 'tenant_employees', ['keycloak_user_id'], postgresql_nulls_not_distinct=False)
    op.alter_column('tenant_employees', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='更新時間',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_employees', 'username_base',
               existing_type=sa.VARCHAR(length=50),
               comment='基礎帳號名稱 (全公司唯一)',
               existing_comment='基礎帳號名稱 (全系統唯一)',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'keycloak_user_id',
               existing_type=sa.VARCHAR(length=36),
               comment='Keycloak User UUID (唯一 SSO 識別碼，永久不變)',
               existing_comment='Keycloak User UUID (唯一 SSO 識別碼，永久不變，一個員工只有一個)',
               existing_nullable=True)
    op.alter_column('tenant_employees', 'employee_id',
               existing_type=sa.VARCHAR(length=20),
               comment='員工編號 (EMP001)',
               existing_comment='員工編號 (EMP001, 租戶內唯一，永久不變)',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號',
               existing_comment='租戶內序號 (自動從1開始)',
               existing_nullable=False)
    op.alter_column('tenant_employees', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='租戶 ID',
               existing_nullable=False)
    op.drop_constraint(None, 'tenant_emp_settings', type_='foreignkey')
    op.drop_constraint(None, 'tenant_emp_settings', type_='foreignkey')
    op.create_foreign_key(op.f('tenant_emp_setting_tenant_resume_id_fkey'), 'tenant_emp_settings', 'tenant_emp_resumes', ['tenant_resume_id'], ['id'], ondelete='CASCADE')
    op.drop_constraint(None, 'tenant_emp_settings', type_='unique')
    op.drop_constraint('uq_tenant_resume_setting', 'tenant_emp_settings', type_='unique')
    op.drop_index(op.f('ix_tenant_emp_settings_tenant_keycloak_user_id'), table_name='tenant_emp_settings')
    op.drop_index(op.f('ix_tenant_emp_settings_tenant_emp_code'), table_name='tenant_emp_settings')
    op.drop_index('idx_emp_setting_tenant', table_name='tenant_emp_settings')
    op.create_unique_constraint(op.f('uq_tenant_resume'), 'tenant_emp_settings', ['tenant_id', 'tenant_resume_id'], postgresql_nulls_not_distinct=False)
    op.create_index(op.f('idx_tenant_emp_settings_tenant'), 'tenant_emp_settings', ['tenant_id'], unique=False)
    op.create_index(op.f('idx_tenant_emp_settings_resume'), 'tenant_emp_settings', ['tenant_resume_id'], unique=False)
    op.alter_column('tenant_emp_settings', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_settings', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_settings', 'edit_by',
               existing_type=sa.String(length=36),
               type_=sa.VARCHAR(length=100),
               comment=None,
               existing_comment='最後編輯者 keycloak_user_id',
               existing_nullable=True)
    op.alter_column('tenant_emp_settings', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_emp_settings', 'email_quota_mb',
               existing_type=sa.INTEGER(),
               comment='郵件配額 (MB)',
               existing_comment='郵件配額 (MB) - Email 使用',
               existing_nullable=False,
               existing_server_default=sa.text('5120'))
    op.alter_column('tenant_emp_settings', 'storage_quota_gb',
               existing_type=sa.INTEGER(),
               comment='配額設定（GB）',
               existing_comment='儲存配額 (GB) - Drive 使用',
               existing_nullable=False,
               existing_server_default=sa.text('20'))
    op.alter_column('tenant_emp_settings', 'tenant_keycloak_username',
               existing_type=sa.VARCHAR(length=100),
               comment='Keycloak 使用者帳號',
               existing_comment='Keycloak 登入帳號',
               existing_nullable=True)
    op.alter_column('tenant_emp_settings', 'tenant_keycloak_user_id',
               existing_type=sa.VARCHAR(length=36),
               comment='Keycloak User UUID',
               existing_comment='Keycloak User UUID (唯一 SSO 識別碼，永久不變)',
               existing_nullable=True)
    op.alter_column('tenant_emp_settings', 'tenant_emp_code',
               existing_type=sa.VARCHAR(length=20),
               comment='員工工號（自動生成：prefix + seq_no）',
               existing_comment='員工編號（自動生成，格式: prefix + seq_no，例如 PWD0001）',
               existing_nullable=False)
    op.alter_column('tenant_emp_settings', 'tenant_resume_id',
               existing_type=sa.INTEGER(),
               comment='人員基本資料 ID',
               existing_comment='人員基本檔 ID（一個人只有一筆任用設定）',
               existing_nullable=False)
    op.alter_column('tenant_emp_settings', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號',
               existing_comment='租戶內序號 (觸發器自動生成)',
               existing_nullable=False)
    op.drop_column('tenant_emp_settings', 'employment_status')
    op.drop_column('tenant_emp_settings', 'primary_dept_id')
    op.drop_column('tenant_emp_settings', 'salary_currency')
    op.drop_column('tenant_emp_settings', 'salary_amount')
    op.drop_column('tenant_emp_settings', 'employment_type')
    op.drop_column('tenant_emp_settings', 'job_title')
    op.drop_column('tenant_emp_settings', 'resign_date')
    op.add_column('tenant_emp_resumes', sa.Column('personal_tel', sa.VARCHAR(length=20), autoincrement=False, nullable=True, comment='通訊電話'))
    op.add_column('tenant_emp_resumes', sa.Column('emergency_tel', sa.VARCHAR(length=20), autoincrement=False, nullable=True, comment='緊急聯絡人電話'))
    op.add_column('tenant_emp_resumes', sa.Column('personal_add', sa.TEXT(), autoincrement=False, nullable=True, comment='通訊地址'))
    op.add_column('tenant_emp_resumes', sa.Column('academic_qualification', sa.VARCHAR(length=200), autoincrement=False, nullable=True, comment='最高學歷'))
    op.add_column('tenant_emp_resumes', sa.Column('personal_id', sa.VARCHAR(length=20), autoincrement=False, nullable=True, comment='身份證號/護照號碼'))
    op.add_column('tenant_emp_resumes', sa.Column('name_tw', sa.VARCHAR(length=100), autoincrement=False, nullable=False, comment='中文姓名'))
    op.add_column('tenant_emp_resumes', sa.Column('name_eng', sa.VARCHAR(length=100), autoincrement=False, nullable=True, comment='英文姓名'))
    op.drop_constraint('uq_tenant_id_number', 'tenant_emp_resumes', type_='unique')
    op.drop_index(op.f('ix_tenant_emp_resumes_id'), table_name='tenant_emp_resumes')
    op.drop_index('idx_emp_resume_tenant', table_name='tenant_emp_resumes')
    op.alter_column('tenant_emp_resumes', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_resumes', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_resumes', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment=None,
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_emp_resumes', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_emp_resumes', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號',
               existing_comment='租戶內序號 (觸發器自動生成)',
               existing_nullable=False)
    op.alter_column('tenant_emp_resumes', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='租戶 ID',
               existing_nullable=False)
    op.drop_column('tenant_emp_resumes', 'notes')
    op.drop_column('tenant_emp_resumes', 'graduation_year')
    op.drop_column('tenant_emp_resumes', 'major')
    op.drop_column('tenant_emp_resumes', 'school_name')
    op.drop_column('tenant_emp_resumes', 'education_level')
    op.drop_column('tenant_emp_resumes', 'emergency_phone')
    op.drop_column('tenant_emp_resumes', 'address')
    op.drop_column('tenant_emp_resumes', 'personal_email')
    op.drop_column('tenant_emp_resumes', 'mobile')
    op.drop_column('tenant_emp_resumes', 'phone')
    op.drop_column('tenant_emp_resumes', 'nationality')
    op.drop_column('tenant_emp_resumes', 'marital_status')
    op.drop_column('tenant_emp_resumes', 'gender')
    op.drop_column('tenant_emp_resumes', 'birth_date')
    op.drop_column('tenant_emp_resumes', 'id_number')
    op.drop_column('tenant_emp_resumes', 'english_name')
    op.drop_column('tenant_emp_resumes', 'legal_name')
    op.add_column('tenant_emp_personal_service_settings', sa.Column('tenant_keycloak_user_id', sa.VARCHAR(length=36), autoincrement=False, nullable=False, comment='Keycloak User UUID'))
    op.drop_index(op.f('ix_tenant_emp_personal_service_settings_id'), table_name='tenant_emp_personal_service_settings')
    op.drop_constraint('uq_emp_service', 'tenant_emp_personal_service_settings', type_='unique')
    op.create_unique_constraint(op.f('uq_emp_service'), 'tenant_emp_personal_service_settings', ['tenant_id', 'tenant_keycloak_user_id', 'service_id'], postgresql_nulls_not_distinct=False)
    op.drop_index('idx_emp_service_user', table_name='tenant_emp_personal_service_settings')
    op.create_index(op.f('idx_emp_service_user'), 'tenant_emp_personal_service_settings', ['tenant_keycloak_user_id'], unique=False)
    op.alter_column('tenant_emp_personal_service_settings', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_emp_personal_service_settings', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.drop_column('tenant_emp_personal_service_settings', 'keycloak_user_id')
    op.add_column('tenant_email_accounts', sa.Column('business_unit_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.drop_index(op.f('ix_tenant_email_accounts_tenant_id'), table_name='tenant_email_accounts')
    op.drop_index(op.f('ix_tenant_email_accounts_id'), table_name='tenant_email_accounts')
    op.drop_index(op.f('ix_tenant_email_accounts_employee_id'), table_name='tenant_email_accounts')
    op.drop_index(op.f('ix_tenant_email_accounts_email_address'), table_name='tenant_email_accounts')
    op.drop_index(op.f('ix_tenant_email_accounts_department_id'), table_name='tenant_email_accounts')
    op.alter_column('tenant_email_accounts', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_email_accounts', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'auto_reply',
               existing_type=sa.TEXT(),
               comment='自動回覆內容',
               existing_comment='自動回覆內容 (可選,例如休假通知)',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'forward_to',
               existing_type=sa.VARCHAR(length=255),
               comment='轉寄地址',
               existing_comment='轉寄地址 (可選,例如外部郵箱)',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'quota_mb',
               existing_type=sa.INTEGER(),
               comment='配額 (MB)',
               existing_comment='配額 (MB),依職級: Junior=2048, Mid=3072, Senior=5120, Manager=10240',
               existing_nullable=False,
               existing_server_default=sa.text('2048'))
    op.alter_column('tenant_email_accounts', 'email_address',
               existing_type=sa.VARCHAR(length=255),
               comment='郵件地址',
               existing_comment='郵件地址 (例如: porsche.chen@lab.taipei)',
               existing_nullable=False)
    op.alter_column('tenant_email_accounts', 'department_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='部門 ID (僅 department 類型需要)',
               existing_nullable=True)
    op.alter_column('tenant_email_accounts', 'employee_id',
               existing_type=sa.INTEGER(),
               nullable=False,
               comment='員工 ID',
               existing_comment='員工 ID (僅 personal 類型需要)')
    op.alter_column('tenant_email_accounts', 'account_type',
               existing_type=sa.VARCHAR(length=20),
               comment=None,
               existing_comment='帳號類型: personal(個人), department(部門)',
               existing_nullable=False,
               existing_server_default=sa.text("'personal'::character varying"))
    op.drop_index(op.f('ix_tenant_dept_members_id'), table_name='tenant_dept_members')
    op.alter_column('tenant_dept_members', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='更新時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_dept_members', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.alter_column('tenant_dept_members', 'edit_by',
               existing_type=sa.String(length=36),
               type_=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者 keycloak_user_id',
               existing_nullable=True)
    op.alter_column('tenant_dept_members', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='是否啟用',
               existing_nullable=False,
               existing_server_default=sa.text('true'))
    op.alter_column('tenant_dept_members', 'ended_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='離開時間（軟刪除）',
               existing_nullable=True)
    op.alter_column('tenant_dept_members', 'joined_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='加入時間',
               existing_nullable=False,
               existing_server_default=sa.text('now()'))
    op.drop_column('tenant_dept_members', 'removed_by')
    op.drop_column('tenant_dept_members', 'assigned_by')
    op.drop_constraint('uq_tenant_dept_seq', 'tenant_departments', type_='unique')
    op.drop_index(op.f('ix_tenant_departments_tenant_id'), table_name='tenant_departments')
    op.drop_index(op.f('ix_tenant_departments_id'), table_name='tenant_departments')
    op.create_index(op.f('ix_departments_id'), 'tenant_departments', ['id'], unique=False)
    op.create_index(op.f('idx_org_departments_tenant_seq'), 'tenant_departments', ['tenant_id', 'seq_no'], unique=True)
    op.create_index(op.f('idx_departments_tenant'), 'tenant_departments', ['tenant_id'], unique=False)
    op.alter_column('tenant_departments', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_departments', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'is_active',
               existing_type=sa.BOOLEAN(),
               comment=None,
               existing_comment='是否啟用',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'depth',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='層次深度 (0=第一層，1=第二層，以此類推)',
               existing_nullable=False,
               existing_server_default=sa.text('1'))
    op.alter_column('tenant_departments', 'email_quota_mb',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='部門信箱配額 (MB)',
               existing_nullable=False,
               existing_server_default=sa.text('5120'))
    op.alter_column('tenant_departments', 'email_address',
               existing_type=sa.VARCHAR(length=255),
               comment=None,
               existing_comment='部門信箱 (例如: wind@ease.taipei)',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'email_domain',
               existing_type=sa.VARCHAR(length=100),
               comment=None,
               existing_comment='郵件網域 (只有 depth=0 可設定，例如 ease.taipei)',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'code',
               existing_type=sa.VARCHAR(length=20),
               comment='部門代碼 (在同一事業部內唯一)',
               existing_comment='部門代碼 (同層內唯一)',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'parent_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='上層部門 ID (NULL=第一層，即原事業部)',
               existing_nullable=True)
    op.alter_column('tenant_departments', 'seq_no',
               existing_type=sa.INTEGER(),
               comment='租戶內序號',
               existing_comment='租戶內序號 (觸發器自動生成)',
               existing_nullable=False)
    op.alter_column('tenant_departments', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='租戶 ID',
               existing_nullable=False)
    op.drop_index(op.f('ix_tenant_batch_logs_started_at'), table_name='tenant_batch_logs')
    op.drop_index(op.f('ix_tenant_batch_logs_id'), table_name='tenant_batch_logs')
    op.drop_index(op.f('ix_tenant_batch_logs_batch_name'), table_name='tenant_batch_logs')
    op.create_index(op.f('ix_batch_logs_started_at'), 'tenant_batch_logs', ['started_at'], unique=False)
    op.create_index(op.f('ix_batch_logs_id'), 'tenant_batch_logs', ['id'], unique=False)
    op.create_index(op.f('ix_batch_logs_batch_name'), 'tenant_batch_logs', ['batch_name'], unique=False)
    op.alter_column('tenant_batch_logs', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_batch_logs', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_batch_logs', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.drop_index(op.f('ix_tenant_audit_logs_tenant_id'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_tenant_audit_logs_resource_type'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_tenant_audit_logs_resource_id'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_tenant_audit_logs_performed_by'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_tenant_audit_logs_performed_at'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_tenant_audit_logs_id'), table_name='tenant_audit_logs')
    op.drop_index(op.f('ix_tenant_audit_logs_action'), table_name='tenant_audit_logs')
    op.drop_index('idx_audit_tenant_time', table_name='tenant_audit_logs')
    op.drop_index('idx_audit_tenant_resource', table_name='tenant_audit_logs')
    op.drop_index('idx_audit_tenant_action', table_name='tenant_audit_logs')
    op.create_index(op.f('ix_audit_logs_resource_type'), 'tenant_audit_logs', ['resource_type'], unique=False)
    op.create_index(op.f('ix_audit_logs_resource_id'), 'tenant_audit_logs', ['resource_id'], unique=False)
    op.create_index(op.f('ix_audit_logs_performed_by'), 'tenant_audit_logs', ['performed_by'], unique=False)
    op.create_index(op.f('ix_audit_logs_performed_at'), 'tenant_audit_logs', ['performed_at'], unique=False)
    op.create_index(op.f('ix_audit_logs_id'), 'tenant_audit_logs', ['id'], unique=False)
    op.create_index(op.f('ix_audit_logs_action'), 'tenant_audit_logs', ['action'], unique=False)
    op.create_index(op.f('idx_audit_logs_tenant'), 'tenant_audit_logs', ['tenant_id'], unique=False)
    op.alter_column('tenant_audit_logs', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_audit_logs', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('tenant_audit_logs', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('tenant_audit_logs', 'performed_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='操作時間',
               existing_nullable=False)
    op.alter_column('tenant_audit_logs', 'resource_type',
               existing_type=sa.VARCHAR(length=50),
               comment='資源類型 (employee/identity/department)',
               existing_comment='資源類型 (employee/department/role)',
               existing_nullable=False)
    op.alter_column('tenant_audit_logs', 'tenant_id',
               existing_type=sa.INTEGER(),
               comment=None,
               existing_comment='租戶 ID',
               existing_nullable=False)
    op.add_column('system_functions_cache', sa.Column('edit_by', sa.VARCHAR(length=100), autoincrement=False, nullable=True, comment='資料編輯者'))
    op.add_column('system_functions_cache', sa.Column('updated_at', postgresql.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP'), autoincrement=False, nullable=True))
    op.add_column('system_functions_cache', sa.Column('created_at', postgresql.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP'), autoincrement=False, nullable=False))
    op.drop_constraint('uq_personal_service_code', 'personal_services', type_='unique')
    op.drop_index(op.f('ix_personal_services_id'), table_name='personal_services')
    op.alter_column('personal_services', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               comment=None,
               existing_comment='更新時間',
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('personal_services', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               comment=None,
               existing_comment='建立時間',
               existing_nullable=False,
               existing_server_default=sa.text('CURRENT_TIMESTAMP'))
    op.alter_column('personal_services', 'edit_by',
               existing_type=sa.VARCHAR(length=100),
               comment='資料編輯者',
               existing_comment='最後編輯者',
               existing_nullable=True)
    op.alter_column('personal_services', 'service_code',
               existing_type=sa.String(length=20),
               type_=sa.VARCHAR(length=50),
               comment='服務代碼',
               existing_comment='服務代碼: SSO/Email/Calendar/Drive/Office',
               existing_nullable=False)
    op.drop_column('personal_services', 'description')
    # ### end Alembic commands ###
