"""refactor to final architecture

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

完整架構重構：
1. 統一表名前綴：org_* → tenant_*（organizes → tenants）
2. 統一通用欄位：is_active, edit_by, created_at, updated_at
3. 擴充 tenants 表業務欄位
4. 新增 personal_services 表
5. 新增 tenant_emp_resumes 表（人員基本資料）
6. 新增 tenant_emp_setting 表（員工任用設定，使用複合主鍵）
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


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


def upgrade() -> None:
    # =========================================================
    # Phase 1: 統一表名前綴（org_* → tenant_*）
    # =========================================================

    # 1.1 核心表
    op.rename_table('organizes', 'tenants')
    op.rename_table('org_departments', 'tenant_departments')
    op.rename_table('org_employees', 'tenant_employees')
    op.rename_table('org_user_roles', 'tenant_user_roles')

    # 1.2 關聯表
    op.rename_table('org_dept_members', 'tenant_dept_members')
    op.rename_table('org_email_accounts', 'tenant_email_accounts')
    op.rename_table('org_network_drives', 'tenant_network_drives')
    op.rename_table('org_permissions', 'tenant_permissions')
    op.rename_table('org_role_rights', 'tenant_role_rights')
    op.rename_table('org_user_role_assignments', 'tenant_user_role_assignments')

    # 1.3 系統表
    op.rename_table('org_audit_logs', 'tenant_audit_logs')
    op.rename_table('org_batch_logs', 'tenant_batch_logs')

    # =========================================================
    # Phase 2: 統一通用欄位（所有表）
    # =========================================================

    # 需要更新的所有表（包含新命名的）
    tables_to_update = [
        'business_units',
        'employee_identities',
        'system_functions_cache',
        'tenant_audit_logs',
        'tenant_batch_logs',
        'tenant_departments',
        'tenant_dept_members',
        'tenant_email_accounts',
        'tenant_employees',
        'tenant_network_drives',
        'tenant_permissions',
        'tenant_role_rights',
        'tenant_user_role_assignments',
        'tenant_user_roles',
        'tenants',
    ]

    for table_name in tables_to_update:
        # 2.1 新增 edit_by（使用 op.add_column，安全處理已存在情況）
        try:
            op.add_column(table_name, sa.Column('edit_by', sa.String(100), comment='資料編輯者'))
        except:
            pass  # 已存在則跳過

        # 2.2 確保有 created_at
        # 先檢查是否有 created_at 或 create_at
        result = op.get_bind().execute(sa.text(f"""
            SELECT column_name FROM information_schema.columns
            WHERE table_name = '{table_name}'
              AND column_name IN ('created_at', 'create_at')
        """))
        existing_cols = [row[0] for row in result]

        if 'create_at' in existing_cols and 'created_at' not in existing_cols:
            op.alter_column(table_name, 'create_at', new_column_name='created_at')
        elif not existing_cols:
            op.add_column(table_name, sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')))

        # 2.3 確保有 updated_at
        result2 = op.get_bind().execute(sa.text(f"""
            SELECT column_name FROM information_schema.columns
            WHERE table_name = '{table_name}'
              AND column_name IN ('updated_at', 'edit_at')
        """))
        existing_cols2 = [row[0] for row in result2]

        if 'edit_at' in existing_cols2 and 'updated_at' not in existing_cols2:
            op.alter_column(table_name, 'edit_at', new_column_name='updated_at')
        elif not existing_cols2:
            op.add_column(table_name, sa.Column('updated_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP')))

    # =========================================================
    # Phase 3: 擴充 tenants 表
    # =========================================================

    # 3.1 移除重複欄位
    op.drop_column('tenants', 'keycloak_realm')  # 與 code 重複

    # 3.2 欄位已在 migration 0007 重新命名，跳過
    # tenant_code → code (已完成)
    # company_name → name (已完成)

    # 3.3 新增業務欄位
    op.add_column('tenants', sa.Column('name_eng', sa.String(200), comment='公司英文名稱'))
    op.add_column('tenants', sa.Column('tax_id', sa.String(20), comment='公司統編'))
    op.add_column('tenants', sa.Column('prefix', sa.String(10), nullable=False, server_default='ORG', comment='公司簡稱（員工編號前綴）'))
    op.add_column('tenants', sa.Column('domain_set', sa.Integer, nullable=False, server_default='2', comment='網域條件：1=組織網域，2=部門網域'))
    op.add_column('tenants', sa.Column('domain', sa.String(100), comment='組織網域（domain_set=1時使用）'))
    op.add_column('tenants', sa.Column('tel', sa.String(20), comment='公司代表號'))
    op.add_column('tenants', sa.Column('add', sa.Text, comment='公司登記地址'))
    op.add_column('tenants', sa.Column('fax', sa.String(20), comment='公司傳真電話'))
    op.add_column('tenants', sa.Column('contact', sa.String(100), comment='公司聯絡人'))
    op.add_column('tenants', sa.Column('contact_email', sa.String(255), comment='公司聯絡人電子郵件'))
    op.add_column('tenants', sa.Column('contact_mobil', sa.String(20), comment='公司聯絡人行動電話'))
    op.add_column('tenants', sa.Column('is_sysmana', sa.Boolean, nullable=False, server_default='false', comment='是否為系統管理公司'))
    op.add_column('tenants', sa.Column('quota', sa.Integer, nullable=False, server_default='100', comment='組織配額（GB）'))

    # 3.4 更新現有租戶 1 的資料
    op.execute("""
        UPDATE tenants
        SET
            name_eng = 'Porscheworld',
            tax_id = '82871784',
            prefix = 'PWD',
            domain_set = 2,
            tel = '0226262026',
            add = '新北市淡水區北新路197號7樓',
            contact = 'porsche.chen',
            contact_email = 'porsche.chen@gmail.com',
            contact_mobil = '0910326333',
            is_sysmana = true,
            quota = 100
        WHERE id = 1
    """)

    # =========================================================
    # Phase 4: 新增 personal_services 表
    # =========================================================

    op.create_table(
        'personal_services',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('service_name', sa.String(100), nullable=False, comment='服務名稱'),
        sa.Column('service_code', sa.String(50), unique=True, nullable=False, comment='服務代碼'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true', comment='是否啟用'),
        sa.Column('edit_by', sa.String(100), comment='資料編輯者'),
        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')),
    )

    # 插入預設資料
    op.execute("""
        INSERT INTO personal_services (service_name, service_code, is_active) VALUES
        ('單一簽入', 'SSO', true),
        ('電子郵件', 'Email', true),
        ('日曆', 'Calendar', true),
        ('網路硬碟', 'Drive', true),
        ('線上office工具', 'Office', true)
    """)

    # =========================================================
    # Phase 5: 新增 tenant_emp_resumes 表（人員基本資料）
    # =========================================================

    op.create_table(
        'tenant_emp_resumes',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('tenant_id', sa.Integer(), sa.ForeignKey('tenants.id', ondelete='CASCADE'), nullable=False, index=True),
        sa.Column('seq_no', sa.Integer(), nullable=False, comment='租戶內序號'),
        sa.Column('name_tw', sa.String(100), nullable=False, comment='中文姓名'),
        sa.Column('name_eng', sa.String(100), comment='英文姓名'),
        sa.Column('personal_id', sa.String(20), comment='身份證號/護照號碼'),
        sa.Column('personal_tel', sa.String(20), comment='通訊電話'),
        sa.Column('personal_add', sa.Text, comment='通訊地址'),
        sa.Column('emergency_contact', sa.String(100), comment='緊急聯絡人'),
        sa.Column('emergency_tel', sa.String(20), comment='緊急聯絡人電話'),
        sa.Column('academic_qualification', sa.String(200), comment='最高學歷'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.Column('edit_by', sa.String(100)),
        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', 'seq_no', name='uq_tenant_resume_seq'),
    )

    # 建立序號觸發器
    op.execute("""
        CREATE TRIGGER trigger_tenant_emp_resumes_seq_no
        BEFORE INSERT ON tenant_emp_resumes
        FOR EACH ROW
        EXECUTE FUNCTION generate_tenant_seq_no();
    """)

    # =========================================================
    # Phase 6: 新增 tenant_emp_setting 表（員工任用設定）
    # 使用複合主鍵：(tenant_id, seq_no)
    # =========================================================

    op.create_table(
        'tenant_emp_setting',
        sa.Column('tenant_id', sa.Integer(), sa.ForeignKey('tenants.id', ondelete='CASCADE'), nullable=False, comment='租戶 ID'),
        sa.Column('seq_no', sa.Integer(), nullable=False, comment='租戶內序號'),
        sa.Column('tenant_resume_id', sa.Integer(), sa.ForeignKey('tenant_emp_resumes.id', ondelete='CASCADE'), nullable=False, comment='人員基本資料 ID'),
        sa.Column('tenant_emp_code', sa.String(20), nullable=False, comment='員工工號（自動生成：prefix + seq_no）'),
        sa.Column('hire_at', sa.Date(), nullable=False, comment='到職日期'),
        sa.Column('tenant_dep_ids', postgresql.ARRAY(sa.Integer()), comment='部門設定（陣列）'),
        sa.Column('tenant_user_roles_ids', postgresql.ARRAY(sa.Integer()), comment='使用者角色（陣列）'),
        sa.Column('tenant_user_quota', sa.Integer(), nullable=False, server_default='20', comment='配額設定（GB）'),
        sa.Column('tenant_user_personal_services', postgresql.ARRAY(sa.Integer()), comment='個人化服務設定（陣列）'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.Column('edit_by', sa.String(100)),
        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.PrimaryKeyConstraint('tenant_id', 'seq_no', name='pk_tenant_emp_setting'),

        # 其他唯一約束
        sa.UniqueConstraint('tenant_id', 'tenant_emp_code', name='uq_tenant_emp_code'),
        sa.UniqueConstraint('tenant_id', 'tenant_resume_id', name='uq_tenant_resume'),  # 一人一筆任用檔
    )

    # 建立索引
    op.create_index('idx_tenant_emp_setting_tenant', 'tenant_emp_setting', ['tenant_id'])
    op.create_index('idx_tenant_emp_setting_resume', 'tenant_emp_setting', ['tenant_resume_id'])

    # 建立序號觸發器（針對複合主鍵表調整）
    op.execute("""
        CREATE OR REPLACE FUNCTION generate_tenant_emp_setting_seq()
        RETURNS TRIGGER AS $$
        BEGIN
            IF NEW.seq_no IS NULL THEN
                SELECT COALESCE(MAX(seq_no), 0) + 1
                INTO NEW.seq_no
                FROM tenant_emp_setting
                WHERE tenant_id = NEW.tenant_id;
            END IF;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;

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

    # 建立員工工號自動生成觸發器
    op.execute("""
        CREATE OR REPLACE FUNCTION generate_tenant_emp_code()
        RETURNS TRIGGER AS $$
        DECLARE
            tenant_prefix VARCHAR(10);
        BEGIN
            IF NEW.tenant_emp_code IS NULL OR NEW.tenant_emp_code = '' THEN
                -- 取得租戶的 prefix
                SELECT prefix INTO tenant_prefix
                FROM tenants
                WHERE id = NEW.tenant_id;

                -- 生成工號：prefix + LPAD(seq_no, 4, '0')
                -- 例如：PWD + 0001 = PWD0001
                NEW.tenant_emp_code := tenant_prefix || LPAD(NEW.seq_no::TEXT, 4, '0');
            END IF;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;

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

    # =========================================================
    # Phase 7: 更新現有觸發器函數（支援新表）
    # =========================================================

    # 更新原有的 generate_tenant_seq_no 函數，使其支援不同表
    op.execute("""
        CREATE OR REPLACE FUNCTION generate_tenant_seq_no()
        RETURNS TRIGGER AS $$
        BEGIN
            IF NEW.seq_no IS NULL THEN
                -- 動態根據 TG_TABLE_NAME 決定查詢哪個表
                EXECUTE format('
                    SELECT COALESCE(MAX(seq_no), 0) + 1
                    FROM %I
                    WHERE tenant_id = $1
                ', TG_TABLE_NAME)
                INTO NEW.seq_no
                USING NEW.tenant_id;
            END IF;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    """)


def downgrade() -> None:
    # 移除新建的觸發器和函數
    op.execute("DROP TRIGGER IF EXISTS trigger_generate_emp_code ON tenant_emp_setting")
    op.execute("DROP FUNCTION IF EXISTS generate_tenant_emp_code()")
    op.execute("DROP TRIGGER IF EXISTS trigger_tenant_emp_setting_seq_no ON tenant_emp_setting")
    op.execute("DROP FUNCTION IF EXISTS generate_tenant_emp_setting_seq()")

    op.drop_index('idx_tenant_emp_setting_resume', table_name='tenant_emp_setting')
    op.drop_index('idx_tenant_emp_setting_tenant', table_name='tenant_emp_setting')
    op.drop_table('tenant_emp_setting')

    op.execute("DROP TRIGGER IF EXISTS trigger_tenant_emp_resumes_seq_no ON tenant_emp_resumes")
    op.drop_table('tenant_emp_resumes')

    op.drop_table('personal_services')

    # 恢復表名（tenant_* → org_*）
    op.rename_table('tenant_batch_logs', 'org_batch_logs')
    op.rename_table('tenant_audit_logs', 'org_audit_logs')
    op.rename_table('tenant_user_role_assignments', 'org_user_role_assignments')
    op.rename_table('tenant_role_rights', 'org_role_rights')
    op.rename_table('tenant_permissions', 'org_permissions')
    op.rename_table('tenant_network_drives', 'org_network_drives')
    op.rename_table('tenant_email_accounts', 'org_email_accounts')
    op.rename_table('tenant_dept_members', 'org_dept_members')
    op.rename_table('tenant_user_roles', 'org_user_roles')
    op.rename_table('tenant_employees', 'org_employees')
    op.rename_table('tenant_departments', 'org_departments')
    op.rename_table('tenants', 'organizes')
