#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import psycopg2
import json
from datetime import datetime

DB_CONFIG = {
    "host": "192.168.110.252",
    "port": 15432,
    "database": "postgresql",
    "user": "postgresql",
    "password": "Jchl1528",
    "options": "-c search_path=hisdev",
}


def check_surgery_advice():
    conn = None
    try:
        print("Connecting to database...")
        conn = psycopg2.connect(**DB_CONFIG)
        cursor = conn.cursor()
        print("Connected!\n")

        print("=" * 80)
        print("Surgery Advice Diagnostic Report")
        print("=" * 80)
        print(f"Time: {datetime.now()}")
        print()

        # 1. Query recent surgery advice
        print("1. Recent surgery advice records:")
        print("-" * 80)
        cursor.execute("""
            SELECT 
                id,
                category_enum,
                activity_id,
                content_json::jsonb->>'surgeryName' as surgery_name,
                content_json::jsonb->>'surgeryCode' as surgery_code,
                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("No surgery advice found (category_enum = 4)")
        else:
            for row in rows:
                print(f"\nID: {row[0]}")
                print(f"  category_enum: {row[1]}")
                print(f"  activity_id: {row[2]}")
                print(f"  surgeryName from JSON: {row[3]}")
                print(f"  surgeryCode from JSON: {row[4]}")
                print(f"  create_time: {row[5]}")

                if not row[3]:
                    print("  WARNING: surgeryName is EMPTY!")
                else:
                    print("  OK: surgeryName exists")

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

        # 2. Test SQL COALESCE query
        print("\n2. Testing SQL COALESCE query:")
        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.activity_id
            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()
        for row in rows:
            print(f"\nID: {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"  activity_id: {row[4]}")

            if not row[1]:
                print("  WARNING: advice_name is EMPTY!")
            else:
                print("  OK: advice_name exists")

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

        cursor.close()

    except Exception as e:
        print(f"ERROR: {e}")
        import traceback

        traceback.print_exc()
    finally:
        if conn:
            conn.close()


if __name__ == "__main__":
    check_surgery_advice()