import psycopg2
import json

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


def diagnose():
    conn = None
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cursor = conn.cursor()

        print("=" * 80)
        print("Diagnosis: Surgery Data Flow")
        print("=" * 80)

        # 1. Find latest cli_surgery records
        print("\n1. Latest cli_surgery records:")
        print("-" * 80)
        cursor.execute("""
            SELECT id, surgery_name, surgery_code, surgery_no, create_time
            FROM cli_surgery
            WHERE delete_flag = '0'
            ORDER BY create_time DESC
            LIMIT 3
        """)

        rows = cursor.fetchall()
        for row in rows:
            print(f"ID: {row[0]}")
            print(f"  surgery_name: {'[HAS VALUE]' if row[1] else '[NULL/EMPTY]'}")
            print(f"  surgery_code: {'[HAS VALUE]' if row[2] else '[NULL/EMPTY]'}")

        # 2. Check corresponding wor_service_request
        print("\n2. Check wor_service_request:")
        print("-" * 80)

        for cli_id, cli_name, cli_code, cli_no, cli_time in rows:
            cursor.execute(
                """
                SELECT 
                    wsr.id,
                    wsr.content_json::jsonb->>'surgeryName' as surgery_name_json,
                    wsr.content_json::jsonb->>'surgeryCode' as surgery_code_json,
                    wsr.create_time
                FROM wor_service_request wsr
                WHERE wsr.activity_id = %s
                AND wsr.category_enum = 4
                AND wsr.delete_flag = '0'
            """,
                (cli_id,),
            )

            wsr_row = cursor.fetchone()
            if wsr_row:
                print(f"cli_surgery ID: {cli_id}")
                print(
                    f"  cli_surgery surgery_name: {'[HAS VALUE]' if cli_name else '[NULL]'}"
                )
                print(
                    f"  cli_surgery surgery_code: {'[HAS VALUE]' if cli_code else '[NULL]'}"
                )
                print(f"  wor_service_request ID: {wsr_row[0]}")
                print(
                    f"  wor_service_request surgeryName: {'[HAS VALUE]' if wsr_row[1] else '[NULL/EMPTY]'}"
                )
                print(
                    f"  wor_service_request surgeryCode: {'[HAS VALUE]' if wsr_row[2] else '[NULL/EMPTY]'}"
                )

                if not wsr_row[1] and cli_name:
                    print(
                        f"  *** PROBLEM: cli_surgery has name but wor_service_request does NOT! ***"
                    )
            else:
                print(
                    f"cli_surgery ID: {cli_id} - No matching wor_service_request found!"
                )

        # 3. Statistics
        print("\n3. Statistics:")
        print("-" * 80)
        cursor.execute("""
            SELECT COUNT(*)
            FROM wor_service_request
            WHERE category_enum = 4
            AND delete_flag = '0'
            AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '')
        """)

        count = cursor.fetchone()[0]
        print(f"  Surgery advice without surgeryName: {count}")

        cursor.close()

    except Exception as e:
        print(f"Error: {e}")
    finally:
        if conn:
            conn.close()


if __name__ == "__main__":
    diagnose()