import mysql.connector
import hashlib

def get_md5(val):
    return hashlib.md5(val.encode('utf-8')).hexdigest()

conn = mysql.connector.connect(
    host="85.215.144.168",
    port=3306,
    database="pastelerias",
    user="asesoft",
    password="Pantera1"
)
cursor = conn.cursor(dictionary=True)

# Fetch all recipe IDs and check their MD5
cursor.execute("SELECT id, codigo, descripcion FROM ob_recetas;")
recipes = cursor.fetchall()

md5_to_recipe = {get_md5(r['id']): r for r in recipes}

print("=== CHECKING MATCHES ===")
# Let's inspect the first orphaned ingredient's proceso_id: '453e2812757a5508700003cf8f485451'
orphan_pid = '453e2812757a5508700003cf8f485451'
if orphan_pid in md5_to_recipe:
    print(f"Orphaned ID {orphan_pid} matches MD5 of recipe: {md5_to_recipe[orphan_pid]}")
else:
    print(f"Orphaned ID {orphan_pid} does NOT match any MD5 of recipe.")

# Let's check if the processes table has this ID:
cursor.execute(f"SELECT * FROM ob_recetas_procesos WHERE id = '{orphan_pid}';")
proc = cursor.fetchone()
print(f"Process with ID {orphan_pid} in DB:", proc)

# Let's count how many recipe MD5s exist in the processes table
cursor.execute("SELECT id FROM ob_recetas_procesos;")
db_proc_ids = {p['id'] for p in cursor.fetchall()}
print(f"Total processes in DB: {len(db_proc_ids)}")

matches_count = sum(1 for pid in md5_to_recipe if pid in db_proc_ids)
print(f"Number of recipe MD5s that exist in ob_recetas_procesos table: {matches_count} (out of {len(recipes)} recipes)")

# Let's look at the actual processes table rows for a recipe that has orphaned ingredients
cursor.execute("""
    SELECT DISTINCT i.proceso_id 
    FROM ob_procesos_ingredientes i
    LEFT JOIN ob_recetas_procesos p ON i.proceso_id = p.id
    WHERE p.id IS NULL
    LIMIT 3;
""")
orphans = [row['proceso_id'] for row in cursor.fetchall()]
for o_pid in orphans:
    if o_pid in md5_to_recipe:
        recipe = md5_to_recipe[o_pid]
        print(f"\nOrphaned proceso_id {o_pid} belongs to recipe {recipe['codigo']} ({recipe['descripcion']})")
        # Check what processes exist for this recipe in the DB:
        cursor.execute(f"SELECT * FROM ob_recetas_procesos WHERE receta_id = '{recipe['id']}';")
        db_procs = cursor.fetchall()
        print("Processes for this recipe in DB:")
        for db_p in db_procs:
            print("  ", db_p)

cursor.close()
conn.close()