import psycopg2
import sys

sys.stdout.reconfigure(encoding="utf-8")

conn = psycopg2.connect(
    host="192.168.110.252",
    port=15432,
    database="postgresql",
    user="postgresql",
    password="Jchl1528",
)

cursor = conn.cursor()
cursor.execute("SET search_path TO hisdev, public")

print("=" * 80)
print("直接检查 ServiceRequest 数据")
print("=" * 80)
print()

# 直接查询表数据
cursor.execute("""
    SELECT 
        id,
        category_enum,
        content_json,
        activity_id
    FROM wor_service_request
    WHERE encounter_id = 2038823905749327873
      AND delete_flag = '0'
      AND parent_id IS NULL
""")

print("原始数据:")
for row in cursor.fetchall():
    print(f"  ID: {row[0]}")
    print(f"  category_enum: {row[1]}")
    print(f"  activity_id: {row[3]}")
    if row[2]:
        import json

        try:
            content = json.loads(row[2]) if isinstance(row[2], str) else row[2]
            print(f"  content_json.surgeryName: {content.get('surgeryName', 'N/A')}")
        except:
            print(f"  content_json: {row[2][:100]}")
    print()

print()
print("=" * 80)
print("测试 COALESCE 语法")
print("=" * 80)
print()

# 测试 COALESCE 语法
cursor.execute("""
    SELECT 
        id,
        COALESCE(category_enum, 3) as advice_type,
        COALESCE(content_json::jsonb->>'surgeryName', '默认值') as surgery_name
    FROM wor_service_request
    WHERE category_enum = 4
      AND delete_flag = '0'
    LIMIT 1
""")

row = cursor.fetchone()
if row:
    print(f"COALESCE 测试:")
    print(f"  ID: {row[0]}")
    print(f"  advice_type: {row[1]}")
    print(f"  surgery_name: {row[2]}")
else:
    print("未找到记录")

print()
print("=" * 80)
print("检查 activity_definition 关联")
print("=" * 80)
print()

# 检查 activity_definition
cursor.execute("""
    SELECT 
        sr.id,
        sr.activity_id,
        ad.name as activity_name,
        COALESCE(ad.name, sr.content_json::jsonb->>'surgeryName') as coalesce_name
    FROM wor_service_request sr
    LEFT JOIN wor_activity_definition ad ON ad.id = sr.activity_id AND ad.delete_flag = '0'
    WHERE sr.category_enum = 4
      AND sr.delete_flag = '0'
      AND sr.encounter_id = 2038823905749327873
""")

for row in cursor.fetchall():
    print(f"  ServiceRequest ID: {row[0]}")
    print(f"  activity_id: {row[1]}")
    print(f"  activity_name: {row[2]}")
    print(f"  coalesce_name: {row[3]}")
    print()

cursor.close()
conn.close()