import pymysql

conn = pymysql.connect(
    host='85.215.144.168',
    user='asesoft',
    password='Pantera1',
    database='pastelerias',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with conn.cursor() as cur:
        # 1. Expand column length if needed
        cur.execute("ALTER TABLE ge_lotes MODIFY COLUMN codigo_antiguo VARCHAR(30) DEFAULT NULL COMMENT 'Este codigo es el codigo en el formato anterior'")
        print("Column modified to VARCHAR(30)")

        # 2. Convert empty strings to NULL
        cur.execute("UPDATE ge_lotes SET codigo_antiguo = NULL WHERE TRIM(codigo_antiguo) = ''")
        print("Empties updated to NULL")

        # 3. Find duplicates and disambiguate
        cur.execute("""
            SELECT codigo_antiguo, COUNT(*) as cnt
            FROM ge_lotes 
            WHERE codigo_antiguo IS NOT NULL AND codigo_antiguo != ''
            GROUP BY codigo_antiguo
            HAVING cnt > 1
        """)
        dup_codes = [r['codigo_antiguo'] for r in cur.fetchall()]
        print(f"Found {len(dup_codes)} duplicate codes to disambiguate")

        for code in dup_codes:
            cur.execute("""
                SELECT id, activo, id_articulo, fecha, created_at 
                FROM ge_lotes 
                WHERE codigo_antiguo = %s 
                ORDER BY id ASC
            """, (code,))
            rows = cur.fetchall()
            # Keep the first one as-is, suffix subsequent ones with -2, -3, ...
            for idx, r in enumerate(rows[1:], start=2):
                new_code = f"{code}-{idx}"
                cur.execute("UPDATE ge_lotes SET codigo_antiguo = %s WHERE id = %s", (new_code, r['id']))
                print(f"  Updated ge_lotes id {r['id']} (art {r['id_articulo']}) from '{code}' to '{new_code}'")

        conn.commit()

        # 4. Verify no duplicates remain
        cur.execute("""
            SELECT codigo_antiguo, COUNT(*) as cnt
            FROM ge_lotes 
            WHERE codigo_antiguo IS NOT NULL AND codigo_antiguo != ''
            GROUP BY codigo_antiguo
            HAVING cnt > 1
        """)
        remaining_dups = cur.fetchall()
        print("Remaining duplicates count:", len(remaining_dups))

        if len(remaining_dups) == 0:
            # 5. Check if unique index already exists
            cur.execute("SHOW INDEX FROM ge_lotes WHERE Key_name = 'uq_ge_lotes_codigo_antiguo'")
            if not cur.fetchone():
                cur.execute("ALTER TABLE ge_lotes ADD UNIQUE KEY uq_ge_lotes_codigo_antiguo (codigo_antiguo)")
                conn.commit()
                print("Successfully added UNIQUE KEY uq_ge_lotes_codigo_antiguo (codigo_antiguo)!")
            else:
                print("UNIQUE KEY uq_ge_lotes_codigo_antiguo already exists.")

finally:
    conn.close()
