"""
建立 system_functions_cache 表
"""
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("Creating system_functions_cache table...")

    cur.execute("""
        CREATE TABLE IF NOT EXISTS system_functions_cache (
            id INTEGER PRIMARY KEY,
            service_code VARCHAR(50) NOT NULL,
            function_code VARCHAR(100) NOT NULL UNIQUE,
            function_name VARCHAR(200) NOT NULL,
            function_category VARCHAR(50),
            is_active BOOLEAN NOT NULL DEFAULT TRUE,
            synced_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT uq_function_code UNIQUE (function_code)
        );
    """)

    # 建立索引
    cur.execute("""
        CREATE INDEX IF NOT EXISTS idx_func_cache_service
        ON system_functions_cache(service_code);
    """)

    cur.execute("""
        CREATE INDEX IF NOT EXISTS idx_func_cache_category
        ON system_functions_cache(function_category);
    """)

    conn.commit()
    print("SUCCESS: system_functions_cache table created")

    # 驗證
    cur.execute("""
        SELECT table_name
        FROM information_schema.tables
        WHERE table_name = 'system_functions_cache';
    """)
    result = cur.fetchone()
    if result:
        print(f"Verified: {result[0]} table exists")
    else:
        print("ERROR: Table not found after creation")

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