backend/migrate_sessions.py

import sqlite3
import shutil
import os

def migrate():
    db_path = 'sentinel.db'
    backup_path = 'sentinel.db.backup'
    
    # 1. Backup
    if not os.path.exists(backup_path):
        shutil.copy2(db_path, backup_path)
        print("Backup created at sentinel.db.backup")
    else:
        print("Backup already exists.")
        
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    
    # 2. Get valid session_ids from student_performance
    c.execute("SELECT session_id FROM student_performance ORDER BY id ASC")
    valid_sessions = [row[0] for row in c.fetchall()]
    
    print(f"Found {len(valid_sessions)} valid sessions in student_performance.")
    
    # Create mapping: old_id -> new_id (1 to 33)
    mapping = {old_id: new_id for new_id, old_id in enumerate(valid_sessions, 1)}
    
    # 3. Delete unused sessions and logs
    placeholders = ','.join(['?'] * len(valid_sessions))
    c.execute(f"DELETE FROM sessions WHERE id NOT IN ({placeholders})", valid_sessions)
    c.execute(f"DELETE FROM emotion_logs WHERE session_id NOT IN ({placeholders})", valid_sessions)
    
    print("Deleted unused sessions and logs.")
    
    # 4. Update IDs to 1..33
    # Because we might overlap if we just update directly (e.g. updating id 1400 to 1, but id 1 might exist if it was valid),
    # we first shift all valid IDs to a high number space to avoid UNIQUE constraint violations.
    shift = 100000
    for old_id in valid_sessions:
        temp_id = old_id + shift
        c.execute("UPDATE sessions SET id = ? WHERE id = ?", (temp_id, old_id))
        c.execute("UPDATE student_performance SET session_id = ? WHERE session_id = ?", (temp_id, old_id))
        c.execute("UPDATE emotion_logs SET session_id = ? WHERE session_id = ?", (temp_id, old_id))
        
    # Now shift to 1..33
    for old_id, new_id in mapping.items():
        temp_id = old_id + shift
        c.execute("UPDATE sessions SET id = ? WHERE id = ?", (new_id, temp_id))
        c.execute("UPDATE student_performance SET session_id = ? WHERE session_id = ?", (new_id, temp_id))
        c.execute("UPDATE emotion_logs SET session_id = ? WHERE session_id = ?", (new_id, temp_id))
        
    # 5. Reset auto-increment
    c.execute("DELETE FROM sqlite_sequence WHERE name='sessions'")
    c.execute("INSERT INTO sqlite_sequence (name, seq) VALUES ('sessions', ?)", (len(valid_sessions),))
    
    conn.commit()
    conn.close()
    print("Migration complete. IDs renumbered 1 to", len(valid_sessions))

if __name__ == '__main__':
    migrate()