import mysql.connector
import json
import re

# ========== 🔐 Database Configuration ==========
config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'admin',
    'database': 'hospital_db'
}

# ========== 🧠 Text Parser ==========
def parse_rx_text(rx_text):
    items = rx_text.split(',')
    parsed = []

    for item in items:
        parts = item.strip().split('*')
        if len(parts) == 2:
            drug_part = parts[0].strip()
            dosage_part = parts[1].strip()
        else:
            drug_part = item.strip()
            dosage_part = ""
        
        # Optional: extract name and qty like "tab pcm 2tds"
        parsed.append({
            "drug_name": drug_part,
            "dosage": dosage_part
        })

    return parsed

# ========== 🚀 Start Migration ==========
try:
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor(dictionary=True)

    # ✅ 1. Add a backup column (only if not already exists)
    cursor.execute("ALTER TABLE tx ADD COLUMN IF NOT EXISTS rx_backup TEXT")

    # ✅ 2. Fetch all tx rows
    cursor.execute("SELECT id, rx FROM tx")
    rows = cursor.fetchall()

    updated = 0

    for row in rows:
        original_rx = row['rx']
        tx_id = row['id']

        if not original_rx:
            continue

        try:
            parsed_rx = parse_rx_text(original_rx)
            json_rx = json.dumps(parsed_rx)

            # ✅ Update: Backup + Replace rx with JSON
            cursor.execute(
                "UPDATE tx SET rx_backup = %s, rx = %s WHERE id = %s",
                (original_rx, json_rx, tx_id)
            )
            updated += 1
        except Exception as e:
            print(f"[!] Error parsing ID {tx_id}: {e}")
            continue

    conn.commit()
    print(f"\n✅ Converted and updated {updated} prescriptions successfully.")

except mysql.connector.Error as err:
    print(f"❌ DB Error: {err}")

finally:
    if conn.is_connected():
        cursor.close()
        conn.close()