"""
建立所有資料庫觸發器
根據 HR Portal 系統規格表 v3.0
"""
import psycopg2

conn = psycopg2.connect(
    host="10.1.0.20",
    port=5433,
    database="hr_portal",
    user="admin",
    password="DC1qaz2wsx"
)
conn.autocommit = False
cur = conn.cursor()

try:
    print("=" * 60)
    print("建立所有資料庫觸發器")
    print("=" * 60)

    # ========================================
    # 1. trigger_tenant_departments_seq_no
    # ========================================
    print("\n[1/6] 建立 trigger_tenant_departments_seq_no...")

    # 函數：自動生成部門 seq_no
    cur.execute("""
        CREATE OR REPLACE FUNCTION fn_auto_seq_no_tenant_departments()
        RETURNS TRIGGER AS $$
        DECLARE
            next_seq INTEGER;
        BEGIN
            -- 如果 seq_no 未提供，自動生成
            IF NEW.seq_no IS NULL THEN
                SELECT COALESCE(MAX(seq_no), 0) + 1
                INTO next_seq
                FROM tenant_departments
                WHERE tenant_id = NEW.tenant_id;

                NEW.seq_no := next_seq;
            END IF;

            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    """)

    # 觸發器
    cur.execute("""
        DROP TRIGGER IF EXISTS trigger_tenant_departments_seq_no ON tenant_departments;
    """)

    cur.execute("""
        CREATE TRIGGER trigger_tenant_departments_seq_no
        BEFORE INSERT ON tenant_departments
        FOR EACH ROW
        EXECUTE FUNCTION fn_auto_seq_no_tenant_departments();
    """)

    print("   [OK] trigger_tenant_departments_seq_no created")

    # ========================================
    # 2. trigger_tenant_employees_seq_no (SKIPPED - table not exists)
    # ========================================
    print("\n[2/6] SKIP trigger_tenant_employees_seq_no (table not exists)")

    # ========================================
    # 3. trigger_tenant_user_roles_seq_no
    # ========================================
    print("\n[3/6] 建立 trigger_tenant_user_roles_seq_no...")

    cur.execute("""
        CREATE OR REPLACE FUNCTION fn_auto_seq_no_tenant_user_roles()
        RETURNS TRIGGER AS $$
        DECLARE
            next_seq INTEGER;
        BEGIN
            IF NEW.seq_no IS NULL THEN
                SELECT COALESCE(MAX(seq_no), 0) + 1
                INTO next_seq
                FROM tenant_user_roles
                WHERE tenant_id = NEW.tenant_id;

                NEW.seq_no := next_seq;
            END IF;

            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    """)

    cur.execute("""
        DROP TRIGGER IF EXISTS trigger_tenant_user_roles_seq_no ON tenant_user_roles;
    """)

    cur.execute("""
        CREATE TRIGGER trigger_tenant_user_roles_seq_no
        BEFORE INSERT ON tenant_user_roles
        FOR EACH ROW
        EXECUTE FUNCTION fn_auto_seq_no_tenant_user_roles();
    """)

    print("   [OK] trigger_tenant_user_roles_seq_no created")

    # ========================================
    # 4. trigger_tenant_emp_resumes_seq_no
    # ========================================
    print("\n[4/6] 建立 trigger_tenant_emp_resumes_seq_no...")

    cur.execute("""
        CREATE OR REPLACE FUNCTION fn_auto_seq_no_tenant_emp_resumes()
        RETURNS TRIGGER AS $$
        DECLARE
            next_seq INTEGER;
        BEGIN
            IF NEW.seq_no IS NULL THEN
                SELECT COALESCE(MAX(seq_no), 0) + 1
                INTO next_seq
                FROM tenant_emp_resumes
                WHERE tenant_id = NEW.tenant_id;

                NEW.seq_no := next_seq;
            END IF;

            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    """)

    cur.execute("""
        DROP TRIGGER IF EXISTS trigger_tenant_emp_resumes_seq_no ON tenant_emp_resumes;
    """)

    cur.execute("""
        CREATE TRIGGER trigger_tenant_emp_resumes_seq_no
        BEFORE INSERT ON tenant_emp_resumes
        FOR EACH ROW
        EXECUTE FUNCTION fn_auto_seq_no_tenant_emp_resumes();
    """)

    print("   [OK] trigger_tenant_emp_resumes_seq_no created")

    # ========================================
    # 5&6. trigger_tenant_emp_settings_auto_fields (合併 seq_no + emp_code)
    # ========================================
    print("\n[5&6] 建立 trigger_tenant_emp_settings_auto_fields...")

    # 合併函數：同時處理 seq_no 和 emp_code
    cur.execute("""
        CREATE OR REPLACE FUNCTION fn_auto_fields_tenant_emp_settings()
        RETURNS TRIGGER AS $$
        DECLARE
            next_seq INTEGER;
            tenant_prefix VARCHAR(10);
        BEGIN
            -- 步驟 1: 自動生成 seq_no
            IF NEW.seq_no IS NULL THEN
                SELECT COALESCE(MAX(seq_no), 0) + 1
                INTO next_seq
                FROM tenant_emp_settings
                WHERE tenant_id = NEW.tenant_id;

                NEW.seq_no := next_seq;
            END IF;

            -- 步驟 2: 自動生成 tenant_emp_code
            IF NEW.tenant_emp_code IS NULL OR NEW.tenant_emp_code = '' THEN
                -- 從 tenants 表取得 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;
    """)

    # 刪除舊觸發器
    cur.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;
    """)

    # 建立新觸發器
    cur.execute("""
        CREATE TRIGGER trigger_tenant_emp_settings_auto_fields
        BEFORE INSERT ON tenant_emp_settings
        FOR EACH ROW
        EXECUTE FUNCTION fn_auto_fields_tenant_emp_settings();
    """)

    print("   [OK] trigger_tenant_emp_settings_auto_fields created")

    # 提交所有變更
    conn.commit()

    print("\n" + "=" * 60)
    print("所有觸發器建立完成！")
    print("=" * 60)

except Exception as e:
    conn.rollback()
    print(f"\nERROR: {e}")
    import traceback
    traceback.print_exc()
finally:
    cur.close()
    conn.close()