"""refactor emp settings to relational

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

調整 tenant_emp_setting 表結構：
1. 表名改為複數：tenant_emp_setting → tenant_emp_settings
2. 移除 ARRAY 欄位（改用關聯表）
3. 新增 Keycloak 欄位
4. 調整配額欄位命名
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


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


def upgrade() -> None:
    # =========================================================
    # Phase 1: 重新命名表（單數 → 複數）
    # =========================================================
    op.rename_table('tenant_emp_setting', 'tenant_emp_settings')

    # =========================================================
    # Phase 2: 新增 Keycloak 欄位
    # =========================================================
    op.add_column('tenant_emp_settings', sa.Column(
        'tenant_keycloak_user_id',
        sa.String(36),
        nullable=True,  # 先設為 nullable，稍後更新資料後改為 NOT NULL
        comment='Keycloak User UUID'
    ))

    op.add_column('tenant_emp_settings', sa.Column(
        'tenant_keycloak_username',
        sa.String(100),
        nullable=True,
        comment='Keycloak 使用者帳號'
    ))

    # =========================================================
    # Phase 3: 新增郵件配額欄位
    # =========================================================
    op.add_column('tenant_emp_settings', sa.Column(
        'email_quota_mb',
        sa.Integer(),
        nullable=False,
        server_default='5120',
        comment='郵件配額 (MB)'
    ))

    # =========================================================
    # Phase 4: 重新命名配額欄位
    # =========================================================
    op.alter_column('tenant_emp_settings', 'tenant_user_quota',
                    new_column_name='storage_quota_gb')

    # =========================================================
    # Phase 5: 移除 ARRAY 欄位（改用關聯表）
    # =========================================================
    op.drop_column('tenant_emp_settings', 'tenant_dep_ids')
    op.drop_column('tenant_emp_settings', 'tenant_user_roles_ids')
    op.drop_column('tenant_emp_settings', 'tenant_user_personal_services')

    # =========================================================
    # Phase 6: 更新觸發器名稱（配合表名變更）
    # =========================================================
    op.execute("""
        DROP TRIGGER IF EXISTS trigger_tenant_emp_setting_seq_no ON tenant_emp_settings;
        DROP TRIGGER IF EXISTS trigger_generate_emp_code ON tenant_emp_settings;

        CREATE TRIGGER trigger_tenant_emp_settings_seq_no
        BEFORE INSERT ON tenant_emp_settings
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_emp_setting_seq();

        CREATE TRIGGER trigger_generate_emp_code
        BEFORE INSERT ON tenant_emp_settings
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_emp_code();
    """)

    # =========================================================
    # Phase 7: 更新索引名稱（配合表名變更）
    # =========================================================
    op.execute("""
        ALTER INDEX IF EXISTS idx_tenant_emp_setting_tenant
        RENAME TO idx_tenant_emp_settings_tenant;

        ALTER INDEX IF EXISTS idx_tenant_emp_setting_resume
        RENAME TO idx_tenant_emp_settings_resume;
    """)

    # =========================================================
    # Phase 8: 建立 tenant_emp_personal_service_settings 表（如果不存在）
    # =========================================================
    # 檢查表是否存在
    connection = op.get_bind()
    result = connection.execute(sa.text("""
        SELECT EXISTS (
            SELECT FROM information_schema.tables
            WHERE table_name = 'tenant_emp_personal_service_settings'
        );
    """))
    table_exists = result.fetchone()[0]

    if not table_exists:
        op.create_table(
            'tenant_emp_personal_service_settings',
            sa.Column('id', sa.Integer(), primary_key=True),
            sa.Column('tenant_id', sa.Integer(), sa.ForeignKey('tenants.id', ondelete='CASCADE'),
                      nullable=False, comment='租戶 ID'),
            sa.Column('tenant_keycloak_user_id', sa.String(36), nullable=False,
                      comment='Keycloak User UUID'),
            sa.Column('service_id', sa.Integer(), sa.ForeignKey('personal_services.id', ondelete='CASCADE'),
                      nullable=False, comment='個人化服務 ID'),
            sa.Column('quota_gb', sa.Integer(), nullable=True, comment='儲存配額 (GB)，適用於 Drive'),
            sa.Column('quota_mb', sa.Integer(), nullable=True, comment='郵件配額 (MB)，適用於 Email'),
            sa.Column('enabled_at', sa.DateTime(), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP'),
                      comment='啟用時間'),
            sa.Column('enabled_by', sa.String(36), nullable=True, comment='啟用者 keycloak_user_id'),
            sa.Column('disabled_at', sa.DateTime(), nullable=True, comment='停用時間（軟刪除）'),
            sa.Column('disabled_by', sa.String(36), nullable=True, comment='停用者 keycloak_user_id'),
            sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true', comment='是否啟用'),
            sa.Column('edit_by', sa.String(36), nullable=True, comment='最後編輯者 keycloak_user_id'),
            sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
            sa.Column('updated_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP')),
            sa.UniqueConstraint('tenant_id', 'tenant_keycloak_user_id', 'service_id', name='uq_emp_service'),
            sa.Index('idx_emp_service_tenant', 'tenant_id'),
            sa.Index('idx_emp_service_user', 'tenant_keycloak_user_id'),
            sa.Index('idx_emp_service_service', 'service_id'),
        )

    # =========================================================
    # Phase 9: 更新現有觸發器支援 tenant_keycloak_user_id
    # =========================================================
    # DepartmentMember 已經使用 employee_id，不需要調整
    # UserRoleAssignment 已經使用 keycloak_user_id，不需要調整


def downgrade() -> None:
    # 恢復 ARRAY 欄位
    op.add_column('tenant_emp_settings', sa.Column('tenant_dep_ids', postgresql.ARRAY(sa.Integer()), comment='部門設定（陣列）'))
    op.add_column('tenant_emp_settings', sa.Column('tenant_user_roles_ids', postgresql.ARRAY(sa.Integer()), comment='使用者角色（陣列）'))
    op.add_column('tenant_emp_settings', sa.Column('tenant_user_personal_services', postgresql.ARRAY(sa.Integer()), comment='個人化服務設定（陣列）'))

    # 恢復配額欄位名稱
    op.alter_column('tenant_emp_settings', 'storage_quota_gb', new_column_name='tenant_user_quota')

    # 移除郵件配額欄位
    op.drop_column('tenant_emp_settings', 'email_quota_mb')

    # 移除 Keycloak 欄位
    op.drop_column('tenant_emp_settings', 'tenant_keycloak_username')
    op.drop_column('tenant_emp_settings', 'tenant_keycloak_user_id')

    # 恢復索引名稱
    op.execute("""
        ALTER INDEX IF EXISTS idx_tenant_emp_settings_tenant
        RENAME TO idx_tenant_emp_setting_tenant;

        ALTER INDEX IF EXISTS idx_tenant_emp_settings_resume
        RENAME TO idx_tenant_emp_setting_resume;
    """)

    # 恢復觸發器名稱
    op.execute("""
        DROP TRIGGER IF EXISTS trigger_tenant_emp_settings_seq_no ON tenant_emp_settings;
        DROP TRIGGER IF EXISTS trigger_generate_emp_code ON tenant_emp_settings;

        CREATE TRIGGER trigger_tenant_emp_setting_seq_no
        BEFORE INSERT ON tenant_emp_settings
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_emp_setting_seq();

        CREATE TRIGGER trigger_generate_emp_code
        BEFORE INSERT ON tenant_emp_settings
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_emp_code();
    """)

    # 恢復表名
    op.rename_table('tenant_emp_settings', 'tenant_emp_setting')

    # 移除 tenant_emp_personal_service_settings 表（如果是這個 migration 建立的）
    # 為了安全，downgrade 不刪除此表
