import mysql.connector
import hashlib

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

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

# 1. Get all recipes from the database
cursor.execute("SELECT id FROM ob_recetas;")
recipes = cursor.fetchall()

# Map from double-hash MD5 to single-hash MD5
double_to_single = {}
for r in recipes:
    rid = r['id']
    single = get_md5(rid)
    double = get_md5(single)
    double_to_single[double] = single

print(f"Loaded {len(double_to_single)} hash mappings.")

# 2. Fix the database ob_procesos_ingredientes
print("Fixing database ob_procesos_ingredientes...")
updated_count = 0
cursor.execute("SELECT id, proceso_id FROM ob_procesos_ingredientes;")
ings = cursor.fetchall()
for ing in ings:
    pid = ing['proceso_id']
    if pid in double_to_single:
        correct_pid = double_to_single[pid]
        cursor.execute(
            "UPDATE ob_procesos_ingredientes SET proceso_id = %s WHERE id = %s;",
            (correct_pid, ing['id'])
        )
        updated_count += 1

conn.commit()
print(f"Updated {updated_count} ingredients in the database.")

# 3. Fix the SQL file PASTELERIAS-recetas-import.sql
print("Fixing SQL import file...")
sql_file_path = r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\documentacion raul asencio\recetas\PASTELERIAS-recetas-import.sql'
with open(sql_file_path, 'r', encoding='utf-8') as f:
    sql_content = f.read()

# Replace any occurrence of the double-hashed ID with the single-hashed ID
replaced_sql_count = 0
for double, single in double_to_single.items():
    if double in sql_content:
        sql_content = sql_content.replace(double, single)
        replaced_sql_count += 1

with open(sql_file_path, 'w', encoding='utf-8') as f:
    f.write(sql_content)

print(f"Replaced {replaced_sql_count} double-hashed keys in the SQL file.")

cursor.close()
conn.close()
print("Fix complete!")