import re
import hashlib

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

input_file = r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\documentacion raul asencio\recetas\PASTELERIAS-recetas-import.sql'

with open(input_file, 'r', encoding='utf-8') as f:
    content = f.read()

# Pattern for ob_recetas insert:
# INSERT INTO `ob_recetas` ( ... ) VALUES ( 'recipe_id', ... );
# We want to capture the recipe ID and output the INSERT statement followed by an INSERT statement for ob_recetas_procesos.
# Let's find all ob_recetas inserts first.
recetas_pattern = re.compile(
    r"(INSERT\s+INTO\s+`ob_recetas`\s*\([^)]+\)\s*VALUES\s*\(\s*'([a-f0-9]{32})'[^;]*\);)",
    re.DOTALL | re.IGNORECASE
)

# We will replace them by appending the process insert
def replace_receta(match):
    full_statement = match.group(1)
    rid = match.group(2)
    pid = get_md5(rid)
    process_stmt = f"\nINSERT INTO `ob_recetas_procesos` (`id`, `receta_id`, `orden`, `nombre`, `proceso_final`) VALUES ('{pid}', '{rid}', 1, 'Proceso Principal', 1);"
    return full_statement + process_stmt

content_modified = recetas_pattern.sub(replace_receta, content)

# Now, we need to adapt ob_procesos_ingredientes (or ob_recetas_ingredientes if any remain)
# Let's handle both possible table names just in case: `ob_procesos_ingredientes` and `ob_recetas_ingredientes`
# Pattern: INSERT INTO `ob_..._ingredientes` ( ... `receta_id` ... ) VALUES ( 'id', 'recipe_id', ... );
# We need to change the column `receta_id` to `proceso_id` and the value to the md5 of that recipe_id.
ing_pattern = re.compile(
    r"(INSERT\s+INTO\s+`(ob_procesos_ingredientes|ob_recetas_ingredientes)`\s*\(([^)]*)\)\s*VALUES\s*\(\s*'([a-f0-9-]{36}|[a-f0-9]{32})'\s*,\s*'([a-f0-9]{32})'([^;]*)\);)",
    re.DOTALL | re.IGNORECASE
)

def replace_ingredient(match):
    full_stmt = match.group(1)
    table_name = match.group(2)
    cols = match.group(3)
    ing_id = match.group(4)
    rid = match.group(5)
    rest_vals = match.group(6)
    
    # Replace table name to ob_procesos_ingredientes
    new_table = "ob_procesos_ingredientes"
    
    # Replace receta_id with proceso_id in columns
    new_cols = cols.replace("`receta_id`", "`proceso_id`").replace("receta_id", "proceso_id")
    
    # Generate process id from recipe id
    pid = get_md5(rid)
    
    new_stmt = f"INSERT INTO `{new_table}` ({new_cols}) VALUES ('{ing_id}', '{pid}'{rest_vals});"
    return new_stmt

content_modified = ing_pattern.sub(replace_ingredient, content_modified)

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

print("SQL script adapted successfully!")