"""add tenant scoped sequences

Revision ID: 0009
Revises: 0008
Create Date: 2026-02-20

為每個租戶建立獨立的序號生成器
- 每個租戶的資料從 1 開始編號
- 保證租戶內序號連續性
"""
from alembic import op
import sqlalchemy as sa


revision = '0009'
down_revision = '0008'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # =========================================================
    # 方案：為主要實體表新增租戶內序號欄位
    # =========================================================

    # 1. org_employees: 新增 seq_no (租戶內序號)
    op.add_column('org_employees', sa.Column('seq_no', sa.Integer(), nullable=True, comment='租戶內序號'))

    # 為現有資料填充 seq_no
    op.execute("""
        WITH numbered AS (
            SELECT id,
                   ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY id) as seq
            FROM org_employees
        )
        UPDATE org_employees
        SET seq_no = numbered.seq
        FROM numbered
        WHERE org_employees.id = numbered.id
    """)

    # 設為 NOT NULL
    op.alter_column('org_employees', 'seq_no', nullable=False)

    # 建立唯一索引：tenant_id + seq_no
    op.create_index(
        'idx_org_employees_tenant_seq',
        'org_employees',
        ['tenant_id', 'seq_no'],
        unique=True
    )

    # 2. org_departments: 新增 seq_no
    op.add_column('org_departments', sa.Column('seq_no', sa.Integer(), nullable=True, comment='租戶內序號'))

    op.execute("""
        WITH numbered AS (
            SELECT id,
                   ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY id) as seq
            FROM org_departments
        )
        UPDATE org_departments
        SET seq_no = numbered.seq
        FROM numbered
        WHERE org_departments.id = numbered.id
    """)

    op.alter_column('org_departments', 'seq_no', nullable=False)
    op.create_index(
        'idx_org_departments_tenant_seq',
        'org_departments',
        ['tenant_id', 'seq_no'],
        unique=True
    )

    # 3. org_user_roles: 新增 seq_no
    op.add_column('org_user_roles', sa.Column('seq_no', sa.Integer(), nullable=True, comment='租戶內序號'))

    op.execute("""
        WITH numbered AS (
            SELECT id,
                   ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY id) as seq
            FROM org_user_roles
        )
        UPDATE org_user_roles
        SET seq_no = numbered.seq
        FROM numbered
        WHERE org_user_roles.id = numbered.id
    """)

    op.alter_column('org_user_roles', 'seq_no', nullable=False)
    op.create_index(
        'idx_org_user_roles_tenant_seq',
        'org_user_roles',
        ['tenant_id', 'seq_no'],
        unique=True
    )

    # 4. 建立自動生成序號的觸發器函數
    op.execute("""
        CREATE OR REPLACE FUNCTION generate_tenant_seq_no()
        RETURNS TRIGGER AS $$
        BEGIN
            -- 如果 seq_no 未提供，自動生成
            IF NEW.seq_no IS NULL THEN
                SELECT COALESCE(MAX(seq_no), 0) + 1
                INTO NEW.seq_no
                FROM org_employees
                WHERE tenant_id = NEW.tenant_id;
            END IF;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    """)

    # 5. 為各表建立觸發器
    for table_name in ['org_employees', 'org_departments', 'org_user_roles']:
        trigger_name = f'trigger_{table_name}_seq_no'
        op.execute(f"""
            CREATE TRIGGER {trigger_name}
            BEFORE INSERT ON {table_name}
            FOR EACH ROW
            EXECUTE FUNCTION generate_tenant_seq_no();
        """)


def downgrade() -> None:
    # 移除觸發器
    for table_name in ['org_employees', 'org_departments', 'org_user_roles']:
        trigger_name = f'trigger_{table_name}_seq_no'
        op.execute(f"DROP TRIGGER IF EXISTS {trigger_name} ON {table_name}")

    # 移除函數
    op.execute("DROP FUNCTION IF EXISTS generate_tenant_seq_no()")

    # 移除索引和欄位
    op.drop_index('idx_org_user_roles_tenant_seq', table_name='org_user_roles')
    op.drop_column('org_user_roles', 'seq_no')

    op.drop_index('idx_org_departments_tenant_seq', table_name='org_departments')
    op.drop_column('org_departments', 'seq_no')

    op.drop_index('idx_org_employees_tenant_seq', table_name='org_employees')
    op.drop_column('org_employees', 'seq_no')
