#1.5.453
import mysql.connector
from collections import defaultdict
from datetime import datetime, timedelta
from db import get_connection, get_read_write_connections

def build_motion_data(db_name, truncate=True):
    conn = get_connection(db_name)
    cursor = conn.cursor(dictionary=True)

    #if truncate:
    #    cursor.execute("TRUNCATE TABLE wh_data_motion_raw")
    #    print(f"🧹 Tabella {db_name}.wh_data_motion_raw svuotata")

    cursor.execute("""WITH valid_groups AS (
            SELECT WHA_group
            FROM wh_data_aps
            GROUP BY WHA_group
            HAVING COUNT(DISTINCT WHA_id) >= 2
        ),
        valid_aps AS (
            SELECT DISTINCT WHA_id
            FROM wh_data_aps
            WHERE WHA_group IN (SELECT WHA_group FROM valid_groups)
        )

        SELECT DISTINCT c.WHC_ap, c.WHC_day
        FROM wh_data_clean c
        WHERE c.WHC_ap IN (SELECT WHA_id FROM valid_aps)
        AND NOT EXISTS (
            SELECT 1
            FROM wh_data_motion_raw m
            WHERE m.WHM_fingerprint = c.WHC_fingerprint
                AND m.WHM_day = c.WHC_day       
        )
        ORDER BY c.WHC_ap, c.WHC_day;""")
    rows = cursor.fetchall()

    movements = []  # lista di tuple da inserire (finger_id, day, from_ap, to_ap, duration)
    current_group = defaultdict(list)

    for row in rows:
        key = (row['WHC_fingerprint'], row['WHC_day'])
        current_group[key].append(row)

    for (finger, day), records in current_group.items():
        current_ap = None
        start_time = None
        end_time = None

        for i, record in enumerate(records):
            ap = record['WHC_ap']
            ts_start = record['WHC_timestamp']
            ts_end = record['WHC_timestamp_max']

            if current_ap is None:
                current_ap = ap
                start_time = ts_start
                end_time = ts_end
                continue

            if ap == current_ap:
                end_time = max(end_time, ts_end)
            else:
                # Permanenza registrata
                duration = int((end_time - start_time).total_seconds())
                movements.append((finger, day, current_ap, current_ap, duration))
                # Cambio AP registrato
                movements.append((finger, day, current_ap, ap, 0))
                # Inizia nuova permanenza
                current_ap = ap
                start_time = ts_start
                end_time = ts_end

        # Chiusura ultima permanenza
        if current_ap is not None and start_time and end_time:
            duration = int((end_time - start_time).total_seconds())
            movements.append((finger, day, current_ap, current_ap, duration))

    print(f"📦 Trovati {len(movements)} movimenti totali")

    cursor_insert = conn.cursor()
    insert_sql = """
        INSERT INTO wh_data_motion_raw (
            WHM_fingerprint, WHM_day,
            WHM_from_ap, WHM_to_ap,
            WHM_duration
        ) VALUES (%s, %s, %s, %s, %s)
    """

    cursor_insert.executemany(insert_sql, movements)
    conn.commit()
    print(f"✅ Inseriti {cursor_insert.rowcount} movimenti in wh_data_motion_raw")

    cursor_insert.close()

    build_motion_sequences(conn)  # chiamata alla nuova funzione

    cursor.close()
    conn.close()

def build_motion_sequences(conn):
    cursor = conn.cursor(dictionary=True)
    cursor.execute("TRUNCATE TABLE wh_data_motion_sequence")
    print("🧹 Tabella wh_data_motion_sequence svuotata")

    cursor.execute("SELECT * FROM wh_data_motion_raw ORDER BY WHM_fingerprint, WHM_day, WHM_id")
    rows = cursor.fetchall()

    sequences = defaultdict(list)
    durations = defaultdict(int)

    for row in rows:
        key = (row['WHM_fingerprint'], row['WHM_day'])
        if len(sequences[key]) == 0:
            sequences[key].append(str(row['WHM_from_ap']))
        if row['WHM_from_ap'] != row['WHM_to_ap']:
            sequences[key].append(str(row['WHM_to_ap']))
        durations[key] += row['WHM_duration']

    insert_seq = conn.cursor()
    insert_sql = """
        INSERT INTO wh_data_motion_sequence (
            WHS_fingerprint, WHS_day, WHS_path,
            WHS_hops, WHS_total_duration
        ) VALUES (%s, %s, %s, %s, %s)
    """

    data = []
    for (finger, day), path in sequences.items():
        hops = len(path) - 1
        total_duration = durations[(finger, day)]
        path_str = ">".join(path)
        data.append((finger, day, path_str, hops, total_duration))

    insert_seq.executemany(insert_sql, data)
    conn.commit()
    print(f"✅ Inserite {insert_seq.rowcount} sequenze in wh_data_motion_sequence")
    insert_seq.close()

# Esegui per un database
if __name__ == "__main__":
    build_motion_data('webhooktest')