#!/opt/homebrew/bin/python3.11
"""
Backfill Frame Data
Calculates start_frame and end_frame based on time and FPS.
"""

import psycopg2

DB_URL = "postgresql://accusys@localhost:5432/momentry"
FPS = 24.0


def backfill(table, time_col_start, time_col_end):
    print(f"🔄 Backfilling {table}...")
    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor()

    # Get all rows
    cur.execute(f"SELECT id, {time_col_start}, {time_col_end} FROM {table}")
    rows = cur.fetchall()

    updates = []
    for id, start, end in rows:
        if start is not None:
            s_frame = int(round(start * FPS))
            e_frame = int(round(end * FPS)) if end is not None else s_frame
            updates.append((s_frame, e_frame, id))

    # Batch update
    for s_frame, e_frame, id in updates:
        cur.execute(
            f"""
            UPDATE {table} 
            SET start_frame = %s, end_frame = %s, fps = %s 
            WHERE id = %s
        """,
            (s_frame, e_frame, FPS, id),
        )

    conn.commit()
    print(f"✅ Updated {len(updates)} rows in {table}.")
    cur.close()
    conn.close()


if __name__ == "__main__":
    backfill("parent_chunks", "start_time", "end_time")
    backfill("child_chunks", "start_time", "end_time")