import re
import hashlib

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

def parse_sql_statements(content):
    statements = []
    current = []
    in_quote = False
    quote_char = None
    escape = False
    
    for char in content:
        if escape:
            current.append(char)
            escape = False
            continue
        if char == '\\':
            current.append(char)
            escape = True
            continue
        if char in ("'", '"', "`"):
            if not in_quote:
                in_quote = True
                quote_char = char
            elif quote_char == char:
                in_quote = False
                quote_char = None
            current.append(char)
        elif char == ';' and not in_quote:
            current.append(char)
            statements.append("".join(current))
            current = []
        else:
            current.append(char)
    if current:
        statements.append("".join(current))
    return statements

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()

statements = parse_sql_statements(content)
new_statements = []

# Regex to check if it's an insert to ob_recetas and capture recipe ID
# Let's extract first value from VALUES ( ... )
receta_val_pattern = re.compile(
    r"VALUES\s*\(\s*'([a-f0-9]{32})'", re.IGNORECASE
)

# Regex for ingredients: can be ob_recetas_ingredientes or ob_procesos_ingredientes
ing_cols_pattern = re.compile(
    r"INSERT\s+INTO\s+`(ob_recetas_ingredientes|ob_procesos_ingredientes)`\s*\(([^)]*)\)", re.IGNORECASE
)
ing_val_pattern = re.compile(
    r"VALUES\s*\(\s*'([a-f0-9-]{36}|[a-f0-9]{32})'\s*,\s*'([a-f0-9]{32})'", re.IGNORECASE
)

for stmt in statements:
    # Skip any existing ob_recetas_procesos inserts to start clean
    if "INSERT INTO `ob_recetas_procesos`" in stmt or "INSERT INTO ob_recetas_procesos" in stmt:
        continue
    
    # Process recipes
    if "INSERT INTO `ob_recetas`" in stmt or "INSERT INTO ob_recetas" in stmt:
        m = receta_val_pattern.search(stmt)
        if m:
            rid = m.group(1)
            pid = get_md5(rid)
            new_statements.append(stmt)
            # Create process insertion statement
            process_stmt = f"\nINSERT INTO `ob_recetas_procesos` (`id`, `receta_id`, `orden`, `nombre`, `proceso_final`) VALUES ('{pid}', '{rid}', 1, 'Proceso Principal', 1);"
            new_statements.append(process_stmt)
        else:
            new_statements.append(stmt)
            
    # Process ingredients
    elif "INSERT INTO `ob_recetas_ingredientes`" in stmt or "INSERT INTO ob_recetas_ingredientes" in stmt or \
         "INSERT INTO `ob_procesos_ingredientes`" in stmt or "INSERT INTO ob_procesos_ingredientes" in stmt:
        
        # 1. Update table name and columns
        m_cols = ing_cols_pattern.search(stmt)
        if m_cols:
            table_name = m_cols.group(1)
            cols = m_cols.group(2)
            new_cols = cols.replace("`receta_id`", "`proceso_id`").replace("receta_id", "proceso_id")
            stmt_mod = stmt.replace(f"INSERT INTO `{table_name}` ({cols})", f"INSERT INTO `ob_procesos_ingredientes` ({new_cols})")
            stmt_mod = stmt_mod.replace(f"INSERT INTO {table_name} ({cols})", f"INSERT INTO `ob_procesos_ingredientes` ({new_cols})")
        else:
            stmt_mod = stmt
            
        # 2. Update values (recipe_id -> process_id)
        m_vals = ing_val_pattern.search(stmt_mod)
        if m_vals:
            ing_id = m_vals.group(1)
            rid = m_vals.group(2)
            pid = get_md5(rid)
            stmt_mod = stmt_mod.replace(f"'{ing_id}', '{rid}'", f"'{ing_id}', '{pid}'")
            
        new_statements.append(stmt_mod)
    else:
        new_statements.append(stmt)

# Reassemble
output = "".join(new_statements)
with open(input_file, 'w', encoding='utf-8') as f:
    f.write(output)

print("SQL script adapted successfully and cleanly using v3 parser!")