#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
诊断脚本：检查手术医嘱是否正确保存和显示
"""

import psycopg2
import json
from datetime import datetime

# 数据库配置
DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "database": "his",
    "user": "postgres",
    "password": "postgres",
}


def check_surgery_advice():
    """检查最近保存的手术医嘱"""
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cursor = conn.cursor()

        print("=" * 80)
        print("手术医嘱诊断报告")
        print("=" * 80)
        print(f"查询时间: {datetime.now()}")
        print()

        # 1. 查询最近的手术医嘱（category_enum = 4）
        print("1. 查询最近保存的手术医嘱:")
        print("-" * 80)
        cursor.execute("""
            SELECT 
                id,
                category_enum,
                activity_id,
                content_json,
                patient_id,
                encounter_id,
                create_time
            FROM wor_service_request 
            WHERE category_enum = 4 
            AND delete_flag = '0'
            ORDER BY create_time DESC 
            LIMIT 5
        """)

        rows = cursor.fetchall()
        if not rows:
            print("⚠️ 没有找到手术医嘱记录（category_enum = 4）")
        else:
            for row in rows:
                print(f"\n手术医嘱 ID: {row[0]}")
                print(f"  category_enum: {row[1]}")
                print(f"  activity_id: {row[2]}")
                print(f"  patient_id: {row[4]}")
                print(f"  encounter_id: {row[5]}")
                print(f"  create_time: {row[6]}")

                # 解析 content_json
                try:
                    content = json.loads(row[3]) if row[3] else {}
                    surgery_name = content.get("surgeryName", "NOT FOUND")
                    surgery_code = content.get("surgeryCode", "NOT FOUND")
                    print(f"  content_json.surgeryName: {surgery_name}")
                    print(f"  content_json.surgeryCode: {surgery_code}")

                    if surgery_name == "NOT FOUND" or not surgery_name:
                        print("  ❌ 警告: content_json 中没有 surgeryName！")
                    else:
                        print("  ✅ surgeryName 已正确保存")
                except Exception as e:
                    print(f"  ❌ 解析 content_json 失败: {e}")
                    print(f"  原始内容: {row[3]}")

        print("\n" + "=" * 80)

        # 2. 测试 SQL 查询是否能正确获取 advice_name
        print("\n2. 测试 SQL 查询是否能正确获取手术名称:")
        print("-" * 80)

        cursor.execute("""
            SELECT 
                wsr.id,
                COALESCE(wad.NAME, wsr.content_json::jsonb->>'surgeryName', wsr.content_json::jsonb->>'adviceName') AS advice_name,
                wsr.content_json::jsonb->>'surgeryName' AS surgery_name_from_json,
                wad.NAME AS activity_name,
                wsr.category_enum
            FROM wor_service_request wsr
            LEFT JOIN wor_activity_definition wad ON wad.ID = wsr.activity_id AND wad.delete_flag = '0'
            WHERE wsr.category_enum = 4 
            AND wsr.delete_flag = '0'
            ORDER BY wsr.create_time DESC 
            LIMIT 5
        """)

        rows = cursor.fetchall()
        if not rows:
            print("⚠️ 没有找到手术医嘱记录")
        else:
            for row in rows:
                print(f"\n手术医嘱 ID: {row[0]}")
                print(f"  COALESCE advice_name: {row[1]}")
                print(f"  surgeryName from JSON: {row[2]}")
                print(f"  activity_definition name: {row[3]}")
                print(f"  category_enum: {row[4]}")

                if not row[1]:
                    print("  ❌ 警告: advice_name 为空！")
                else:
                    print("  ✅ advice_name 可以正确获取")

        print("\n" + "=" * 80)

        # 3. 检查是否存在 activity_id 对应的 wor_activity_definition
        print("\n3. 检查手术医嘱的 activity_id 关联:")
        print("-" * 80)

        cursor.execute("""
            SELECT 
                wsr.id,
                wsr.activity_id,
                wad.id AS def_id,
                wad.name AS def_name,
                CASE WHEN wad.id IS NULL THEN 'MISSING' ELSE 'OK' END AS status
            FROM wor_service_request wsr
            LEFT JOIN wor_activity_definition wad ON wad.ID = wsr.activity_id
            WHERE wsr.category_enum = 4 
            AND wsr.delete_flag = '0'
            ORDER BY wsr.create_time DESC 
            LIMIT 5
        """)

        rows = cursor.fetchall()
        for row in rows:
            status_icon = "✅" if row[4] == "OK" else "❌"
            print(
                f"{status_icon} service_request.id={row[0]}, activity_id={row[1]}, definition={row[3] or 'NULL'}"
            )

        print("\n" + "=" * 80)
        print("诊断完成")
        print("=" * 80)

        cursor.close()
        conn.close()

    except Exception as e:
        print(f"❌ 诊断失败: {e}")
        import traceback

        traceback.print_exc()


if __name__ == "__main__":
    check_surgery_advice()