"""add tenants table for multi-tenant support

Revision ID: 0001_5
Revises: fba4e3f40f05
Create Date: 2026-02-15 19:00:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '0001_5'
down_revision: Union[str, None] = 'fba4e3f40f05'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # 創建 tenants 表
    op.create_table(
        'tenants',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('code', sa.String(length=50), nullable=False, comment='租戶代碼 (唯一)'),
        sa.Column('name', sa.String(length=200), nullable=False, comment='租戶名稱'),
        sa.Column('status', sa.String(length=20), nullable=False, server_default='active', comment='狀態'),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('now()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('now()')),
        sa.PrimaryKeyConstraint('id')
    )

    # 創建索引
    op.create_index('idx_tenants_code', 'tenants', ['code'], unique=True)
    op.create_index('idx_tenants_status', 'tenants', ['status'])

    # 添加預設租戶 (Porsche World)
    # 注意: keycloak_realm 欄位在 0005 migration 才加入，這裡先不設定
    op.execute("""
        INSERT INTO tenants (code, name, status)
        VALUES ('porscheworld', 'Porsche World', 'active')
    """)

    # 為現有表添加 tenant_id 欄位
    op.add_column('employees', sa.Column('tenant_id', sa.Integer(), nullable=True))
    op.add_column('business_units', sa.Column('tenant_id', sa.Integer(), nullable=True))
    op.add_column('departments', sa.Column('tenant_id', sa.Integer(), nullable=True))
    op.add_column('employee_identities', sa.Column('tenant_id', sa.Integer(), nullable=True))
    op.add_column('network_drives', sa.Column('tenant_id', sa.Integer(), nullable=True))
    op.add_column('audit_logs', sa.Column('tenant_id', sa.Integer(), nullable=True))

    # 將所有現有記錄設定為預設租戶
    op.execute("UPDATE employees SET tenant_id = 1 WHERE tenant_id IS NULL")
    op.execute("UPDATE business_units SET tenant_id = 1 WHERE tenant_id IS NULL")
    op.execute("UPDATE departments SET tenant_id = 1 WHERE tenant_id IS NULL")
    op.execute("UPDATE employee_identities SET tenant_id = 1 WHERE tenant_id IS NULL")
    op.execute("UPDATE network_drives SET tenant_id = 1 WHERE tenant_id IS NULL")
    op.execute("UPDATE audit_logs SET tenant_id = 1 WHERE tenant_id IS NULL")

    # 將 tenant_id 設為 NOT NULL
    op.alter_column('employees', 'tenant_id', nullable=False)
    op.alter_column('business_units', 'tenant_id', nullable=False)
    op.alter_column('departments', 'tenant_id', nullable=False)
    op.alter_column('employee_identities', 'tenant_id', nullable=False)
    op.alter_column('network_drives', 'tenant_id', nullable=False)
    op.alter_column('audit_logs', 'tenant_id', nullable=False)

    # 添加外鍵約束
    op.create_foreign_key('fk_employees_tenant', 'employees', 'tenants', ['tenant_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('fk_business_units_tenant', 'business_units', 'tenants', ['tenant_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('fk_departments_tenant', 'departments', 'tenants', ['tenant_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('fk_employee_identities_tenant', 'employee_identities', 'tenants', ['tenant_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('fk_network_drives_tenant', 'network_drives', 'tenants', ['tenant_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('fk_audit_logs_tenant', 'audit_logs', 'tenants', ['tenant_id'], ['id'], ondelete='CASCADE')

    # 添加索引
    op.create_index('idx_employees_tenant', 'employees', ['tenant_id'])
    op.create_index('idx_business_units_tenant', 'business_units', ['tenant_id'])
    op.create_index('idx_departments_tenant', 'departments', ['tenant_id'])
    op.create_index('idx_employee_identities_tenant', 'employee_identities', ['tenant_id'])
    op.create_index('idx_network_drives_tenant', 'network_drives', ['tenant_id'])
    op.create_index('idx_audit_logs_tenant', 'audit_logs', ['tenant_id'])


def downgrade() -> None:
    # 移除索引
    op.drop_index('idx_audit_logs_tenant', table_name='audit_logs')
    op.drop_index('idx_network_drives_tenant', table_name='network_drives')
    op.drop_index('idx_employee_identities_tenant', table_name='employee_identities')
    op.drop_index('idx_departments_tenant', table_name='departments')
    op.drop_index('idx_business_units_tenant', table_name='business_units')
    op.drop_index('idx_employees_tenant', table_name='employees')

    # 移除外鍵
    op.drop_constraint('fk_audit_logs_tenant', 'audit_logs', type_='foreignkey')
    op.drop_constraint('fk_network_drives_tenant', 'network_drives', type_='foreignkey')
    op.drop_constraint('fk_employee_identities_tenant', 'employee_identities', type_='foreignkey')
    op.drop_constraint('fk_departments_tenant', 'departments', type_='foreignkey')
    op.drop_constraint('fk_business_units_tenant', 'business_units', type_='foreignkey')
    op.drop_constraint('fk_employees_tenant', 'employees', type_='foreignkey')

    # 移除 tenant_id 欄位
    op.drop_column('audit_logs', 'tenant_id')
    op.drop_column('network_drives', 'tenant_id')
    op.drop_column('employee_identities', 'tenant_id')
    op.drop_column('departments', 'tenant_id')
    op.drop_column('business_units', 'tenant_id')
    op.drop_column('employees', 'tenant_id')

    # 移除 tenants 表
    op.drop_index('idx_tenants_status', table_name='tenants')
    op.drop_index('idx_tenants_code', table_name='tenants')
    op.drop_table('tenants')
