#!/usr/bin/env python3
"""Backfill changeover values for udcparts"""
import mysql.connector

conn = mysql.connector.connect(
    host='127.0.0.1', port=3306, user='root', 
    password='rootpassword', database='shopdb'
)
cursor = conn.cursor()

# Get all machines
cursor.execute("SELECT DISTINCT machinenumber FROM udcsessions ORDER BY machinenumber")
machines = [row[0] for row in cursor.fetchall()]

total_updated = 0
for machine in machines:
    # Get all parts for this machine ordered by start time
    cursor.execute("""
        SELECT p.partrunid, p.programstart, p.programend
        FROM udcparts p
        JOIN udcsessions s ON p.sessionid = s.sessionid
        WHERE s.machinenumber = %s AND p.programstart IS NOT NULL
        ORDER BY p.programstart ASC
    """, (machine,))
    parts = cursor.fetchall()
    
    prev_end = None
    updates = []
    for partrunid, programstart, programend in parts:
        if prev_end and programstart:
            changeover = int((programstart - prev_end).total_seconds())
            # Only set if reasonable (> 0 and < 24 hours)
            if 0 < changeover < 86400:
                updates.append((changeover, partrunid))
        prev_end = programend if programend else prev_end
    
    # Batch update
    if updates:
        cursor.executemany("UPDATE udcparts SET changeover = %s WHERE partrunid = %s", updates)
        conn.commit()
        total_updated += len(updates)
    
    print(f"Machine {machine}: {len(updates)} changeovers set")

print(f"\nTotal updated: {total_updated}")
cursor.close()
conn.close()