import os
import pandas as pd
from db.db_utils import get_connection

db_conn = get_connection()
cursor = db_conn.cursor()

# Fetch distinct run_keys
cursor.execute("SELECT DISTINCT run_key FROM results")
run_keys = cursor.fetchall()

for run_key in run_keys:
    run_key_value = run_key[0]

    # Query to get the data grouped by run_key and ordered by run_row
    query = f"""
    SELECT * 
    FROM results 
    WHERE run_key = %s 
    ORDER BY run_row;
    """
    
    cursor.execute(query, (run_key_value,))
    rows = cursor.fetchall()

    # Get column names
    colnames = [desc[0] for desc in cursor.description]

    # Create a DataFrame
    df = pd.DataFrame(rows, columns=colnames)

    # Export DataFrame to CSV
    csv_filename = f"{run_key_value}_parsed.csv"
    df.to_csv(f"./results/{csv_filename}", index=False)

# Close the cursor and connection
cursor.close()
db_conn.close()