"""extend organization structure

Revision ID: 0003
Revises: 0002
Create Date: 2026-02-15 15:30:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    # 擴充 business_units 表
    op.add_column('business_units', sa.Column('primary_domain', sa.String(100), nullable=True))
    op.add_column('business_units', sa.Column('email_address', sa.String(255), nullable=True))
    op.add_column('business_units', sa.Column('email_quota_mb', sa.Integer(), server_default='10240', nullable=False))

    # 擴充 departments 表
    op.add_column('departments', sa.Column('email_address', sa.String(255), nullable=True))
    op.add_column('departments', sa.Column('email_quota_mb', sa.Integer(), server_default='5120', nullable=False))

    # 擴充 email_accounts 表 (支援組織/事業部/部門信箱)
    op.add_column('email_accounts', sa.Column('account_type', sa.String(20), server_default='personal', nullable=False))
    op.add_column('email_accounts', sa.Column('department_id', sa.Integer(), nullable=True))
    op.add_column('email_accounts', sa.Column('business_unit_id', sa.Integer(), nullable=True))

    # 添加外鍵約束
    op.create_foreign_key('fk_email_accounts_department', 'email_accounts', 'departments', ['department_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('fk_email_accounts_business_unit', 'email_accounts', 'business_units', ['business_unit_id'], ['id'], ondelete='CASCADE')

    # 更新現有的 business_units 資料 (三大事業部)
    op.execute("""
        UPDATE business_units SET
            primary_domain = 'ease.taipei',
            email_address = 'business@ease.taipei'
        WHERE name = '業務發展部' OR code = 'BD'
    """)

    op.execute("""
        UPDATE business_units SET
            primary_domain = 'lab.taipei',
            email_address = 'tech@lab.taipei'
        WHERE name = '技術發展部' OR code = 'TD'
    """)

    op.execute("""
        UPDATE business_units SET
            primary_domain = 'porscheworld.tw',
            email_address = 'operations@porscheworld.tw'
        WHERE name = '營運管理部' OR code = 'OM'
    """)

    # 插入初始部門資料
    # 業務發展部 (假設 id=1)
    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '玄鐵風能', 'WIND', 'wind@ease.taipei', 5120, NOW()
        FROM business_units WHERE name = '業務發展部' LIMIT 1
    """)

    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '虛擬公司', 'VIRTUAL', 'virtual@ease.taipei', 5120, NOW()
        FROM business_units WHERE name = '業務發展部' LIMIT 1
    """)

    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '國際碳權', 'CARBON', 'carbon@ease.taipei', 5120, NOW()
        FROM business_units WHERE name = '業務發展部' LIMIT 1
    """)

    # 技術發展部 (假設 id=2)
    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '智能研發', 'AI', 'ai@lab.taipei', 5120, NOW()
        FROM business_units WHERE name = '技術發展部' LIMIT 1
    """)

    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '軟體開發', 'DEV', 'dev@lab.taipei', 5120, NOW()
        FROM business_units WHERE name = '技術發展部' LIMIT 1
    """)

    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '虛擬MIS', 'MIS', 'mis@lab.taipei', 5120, NOW()
        FROM business_units WHERE name = '技術發展部' LIMIT 1
    """)

    # 營運管理部 (假設 id=3)
    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '人資', 'HR', 'hr@porscheworld.tw', 5120, NOW()
        FROM business_units WHERE name = '營運管理部' LIMIT 1
    """)

    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '財務', 'FIN', 'finance@porscheworld.tw', 5120, NOW()
        FROM business_units WHERE name = '營運管理部' LIMIT 1
    """)

    op.execute("""
        INSERT INTO departments (tenant_id, business_unit_id, name, code, email_address, email_quota_mb, created_at)
        SELECT 1, id, '總務', 'ADMIN', 'admin@porscheworld.tw', 5120, NOW()
        FROM business_units WHERE name = '營運管理部' LIMIT 1
    """)


def downgrade() -> None:
    # 移除外鍵約束
    op.drop_constraint('fk_email_accounts_business_unit', 'email_accounts', type_='foreignkey')
    op.drop_constraint('fk_email_accounts_department', 'email_accounts', type_='foreignkey')

    # 移除 email_accounts 擴充欄位
    op.drop_column('email_accounts', 'business_unit_id')
    op.drop_column('email_accounts', 'department_id')
    op.drop_column('email_accounts', 'account_type')

    # 移除 departments 擴充欄位
    op.drop_column('departments', 'email_quota_mb')
    op.drop_column('departments', 'email_address')

    # 移除 business_units 擴充欄位
    op.drop_column('business_units', 'email_quota_mb')
    op.drop_column('business_units', 'email_address')
    op.drop_column('business_units', 'primary_domain')

    # 刪除部門資料 (downgrade 時)
    op.execute("DELETE FROM departments WHERE tenant_id = 1")
