import psycopg2
import json
import sys

# 设置UTF-8编码
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()

# 设置schema
cursor.execute("SET search_path TO hisdev, public")
conn.commit()

# 1. 查询需要修复的记录数
print("=" * 60)
print("Bug #318 历史数据修复")
print("=" * 60)
print()

print("步骤1: 查询需要修复的手术申请单...")
cursor.execute("""
    SELECT COUNT(*) 
    FROM doc_request_form rf
    LEFT JOIN wor_service_request sr ON sr.prescription_no = rf.prescription_no 
        AND sr.delete_flag = '0'
    WHERE rf.type_code = 'PROCEDURE'
      AND rf.delete_flag = '0'
      AND sr.id IS NULL
""")
count = cursor.fetchone()[0]
print(f"✓ 发现 {count} 条需要修复的手术申请单")
print()

if count == 0:
    print("没有需要修复的数据")
else:
    # 2. 查看部分记录详情
    print("步骤2: 查看部分记录详情...")
    cursor.execute("""
        SELECT rf.id, rf.prescription_no, rf.encounter_id, rf.patient_id, rf.create_time
        FROM doc_request_form rf
        LEFT JOIN wor_service_request sr ON sr.prescription_no = rf.prescription_no 
            AND sr.delete_flag = '0'
        WHERE rf.type_code = 'PROCEDURE'
          AND rf.delete_flag = '0'
          AND sr.id IS NULL
        ORDER BY rf.create_time DESC
        LIMIT 5
    """)
    rows = cursor.fetchall()
    for row in rows:
        print(
            f"  ID: {row[0]}, 单号: {row[1]}, 就诊: {row[2]}, 患者: {row[3]}, 时间: {row[4]}"
        )
    print()

    # 3. 执行修复 - 生成ServiceRequest
    print("步骤3: 生成手术医嘱（ServiceRequest）...")
    cursor.execute("""
        INSERT INTO wor_service_request (
            bus_no, prescription_no, status_enum, generate_source_enum,
            therapy_enum, quantity, unit_code, category_enum,
            patient_id, requester_id, encounter_id, authored_time,
            org_id, content_json, delete_flag, create_time, create_by, tenant_id
        )
        SELECT 
            LPAD(FLOOR(RANDOM() * 10000)::TEXT, 4, '0'),
            rf.prescription_no,
            1, 1, 2, 1, '次', 4,
            rf.patient_id, rf.requester_id, rf.encounter_id, rf.create_time,
            rf.org_id, rf.desc_json, '0', rf.create_time, rf.requester_id, 1
        FROM doc_request_form rf
        LEFT JOIN wor_service_request sr ON sr.prescription_no = rf.prescription_no 
            AND sr.delete_flag = '0'
        WHERE rf.type_code = 'PROCEDURE'
          AND rf.delete_flag = '0'
          AND sr.id IS NULL
        RETURNING id, prescription_no
    """)
    new_requests = cursor.fetchall()
    print(f"✓ 成功生成 {len(new_requests)} 条手术医嘱")
    print()

    # 4. 生成ChargeItem
    print("步骤4: 生成收费项目（ChargeItem）...")

    # 获取刚插入的ServiceRequest
    prescription_nos = [r[1] for r in new_requests]
    if prescription_nos:
        cursor.execute(
            """
            INSERT INTO adm_charge_item (
                bus_no, status_enum, generate_source_enum, patient_id,
                context_enum, encounter_id, enterer_id, entered_date,
                service_table, service_id, product_table, requesting_org_id,
                quantity_value, quantity_unit, unit_price, total_price,
                delete_flag, create_time, create_by, tenant_id
            )
            SELECT 
                'CI' || sr.bus_no, 1, 1, sr.patient_id, 3, sr.encounter_id,
                sr.requester_id, sr.create_time, 'wor_service_request', sr.id,
                'wor_activity_definition', sr.org_id, 1, '次',
                COALESCE((sr.content_json::jsonb->>'surgeryFee')::numeric, 0),
                COALESCE((sr.content_json::jsonb->>'surgeryFee')::numeric, 0),
                '0', sr.create_time, sr.requester_id, 1
            FROM wor_service_request sr
            WHERE sr.prescription_no = ANY(%s)
              AND sr.category_enum = 4
              AND sr.delete_flag = '0'
        """,
            (prescription_nos,),
        )

        # 麻醉费用
        cursor.execute(
            """
            INSERT INTO adm_charge_item (
                bus_no, status_enum, generate_source_enum, patient_id,
                context_enum, encounter_id, enterer_id, entered_date,
                service_table, service_id, product_table, requesting_org_id,
                quantity_value, quantity_unit, unit_price, total_price,
                delete_flag, create_time, create_by, tenant_id
            )
            SELECT 
                'CI' || sr.bus_no || '_A', 1, 1, sr.patient_id, 3, sr.encounter_id,
                sr.requester_id, sr.create_time, 'wor_service_request', sr.id,
                'wor_activity_definition', sr.org_id, 1, '次',
                (sr.content_json::jsonb->>'anesthesiaFee')::numeric,
                (sr.content_json::jsonb->>'anesthesiaFee')::numeric,
                '0', sr.create_time, sr.requester_id, 1
            FROM wor_service_request sr
            WHERE sr.prescription_no = ANY(%s)
              AND sr.category_enum = 4
              AND sr.delete_flag = '0'
              AND sr.content_json::jsonb->>'anesthesiaFee' IS NOT NULL
              AND (sr.content_json::jsonb->>'anesthesiaFee')::numeric > 0
        """,
            (prescription_nos,),
        )

        print(f"✓ 成功生成收费项目")
    print()

    # 5. 验证结果
    print("步骤5: 验证修复结果...")
    cursor.execute("""
        SELECT COUNT(*) FROM wor_service_request 
        WHERE category_enum = 4 AND delete_flag = '0'
    """)
    service_count = cursor.fetchone()[0]

    cursor.execute("""
        SELECT COUNT(*) FROM adm_charge_item ci
        WHERE ci.service_table = 'wor_service_request'
          AND EXISTS (
              SELECT 1 FROM wor_service_request sr 
              WHERE sr.id = ci.service_id AND sr.category_enum = 4
          )
    """)
    charge_count = cursor.fetchone()[0]

    print(f"✓ 手术医嘱总数: {service_count}")
    print(f"✓ 手术收费项目总数: {charge_count}")
    print()

    # 提交事务
    conn.commit()
    print("=" * 60)
    print("✓ 修复完成！")
    print("=" * 60)

cursor.close()
conn.close()