#v.1.3.456
import mysql.connector
from collections import defaultdict
from datetime import datetime
from db import get_connection, get_read_write_connections

# Connessioni separate per lettura e scrittura
def clean_DB(db_read, db_write, truncate=True):
    conn_read, conn_write = get_read_write_connections(db_read, db_write)
    cursor_read = conn_read.cursor(dictionary=True)
    cursor_write = conn_write.cursor()

    # Svuota tabella di destinazione
    #cursor_write.execute("TRUNCATE TABLE wh_data_clean")
    #print("?? Tabella wh_data_clean svuotata")

    # Recupera lista (giorno, WHD_cht_id) distinti
    cursor_read.execute("""
        SELECT DISTINCT DATE(w.WHD_timestamp) AS day, w.WHD_cht_id
        FROM wh_data w
        WHERE NOT EXISTS (
            SELECT 1
            FROM wh_data_clean c
            WHERE DATE(w.WHD_timestamp) = c.WHC_day
            AND w.WHD_cht_id = c.WHC_ap
        )
        AND DATE(w.WHD_timestamp) < CURDATE() - INTERVAL 1 DAY
        ORDER BY day, w.WHD_cht_id
    """)
    day_ap_list = [item for item in cursor_read.fetchall() if item['day'] < datetime.now().date()]

    # Mappa per AP info
    cursor_read.execute("SELECT WHA_code, WHA_id, WHA_user, WHA_dom FROM wh_aps")
    aps_info = {str(row['WHA_code']): row for row in cursor_read.fetchall()}

    # Vendor MAC map
    cursor_read.execute("SELECT id, LOWER(Mac) as mac FROM mac_vendors_export WHERE LENGTH(Mac) = 8")
    mac_vendor_map = {row['mac']: row['id'] for row in cursor_read.fetchall()}

    print(f"Trovati {len(day_ap_list)} accoppiamenti giorno/AP da processare")

    inserted_total = 0

    for item in day_ap_list:
        day = item['day']
        cht_id = str(item['WHD_cht_id'])

        if cht_id not in aps_info:
            print(f"Nessun dato in wh_aps per cht_id {cht_id}")
            continue

        ap = aps_info[cht_id]
        ap_id = ap['WHA_id']
        ap_user = ap['WHA_user']
        ap_dom = ap['WHA_dom']

        # Estrai dati grezzi per quel giorno e AP
        cursor_read.execute("""
            SELECT *
            FROM wh_data
            WHERE DATE(WHD_timestamp) = %s AND WHD_cht_id = %s
        """, (day, cht_id))
        rows = cursor_read.fetchall()

        print(f"Giorno {day} / AP {cht_id} ? {len(rows)} righe")
        inserted_day_total = 0
        # Raggruppamento per fingerprint+ora
        grouped = defaultdict(list)
        for row in rows:
            ts = row['WHD_timestamp']
            key = (row['WHD_fingerprint'], ts.hour)
            grouped[key].append((row['WHD_signal'], ts, row))

        batch = []

        for (fingerprint, hour), samples in grouped.items():
            if len(samples) <= 2:
                continue

            samples_sorted = sorted(samples, key=lambda x: x[1])
            signals = [s[0] for s in samples_sorted[1:-1]] if len(samples) > 3 else [s[0] for s in samples_sorted]
            avg_signal = int(abs(sum(signals) / len(signals)))

            min_ts = samples_sorted[0][1]
            max_ts = samples_sorted[-1][1]
            ref_row = samples_sorted[0][2]  # Usato per device e random

            day_str = min_ts.strftime("%Y-%m-%d")
            week = min_ts.isoweekday()

            mac_clean = ref_row['WHD_mac'].lower()
            device_id = 0
            for vendor_mac, vendor_id in mac_vendor_map.items():
                if vendor_mac and mac_clean[:len(vendor_mac)] == vendor_mac:
                    device_id = vendor_id
                    break

            finger_input = ref_row['WHD_mac'] if fingerprint == '0x0' else fingerprint
            cursor_write.execute("SELECT checkfinger(%s)", [finger_input])
            finger_result = cursor_write.fetchone()[0]

            query = """
                INSERT INTO wh_data_clean (
                    WHC_week, WHC_day, WHC_hour,
                    WHC_signal, WHC_timestamp, WHC_timestamp_max,
                    WHC_fingerprint, WHC_ap, WHC_group, WHC_dom, WHC_device, WHC_random
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            params = (
                week, day_str, hour, avg_signal, min_ts, max_ts,
                finger_result, ap_id, ap_user, ap_dom, device_id, ref_row['WHD_is_random_mac']
            )

            try:
                cursor_write.execute(query, params)
                conn_write.commit()
                inserted_total += 1
                inserted_day_total += 1
            except Exception as e:
                print(f"Errore inserendo: {params}")
                print(f"Query: {query}")
                print(f"Eccezione: {e}")
                exit(1)
        print(f"Inseriti da {day} / AP {cht_id}: {inserted_day_total} righe")

    print(f"Totale inseriti: {inserted_total} righe")
    cursor_read.close()
    cursor_write.close()
    conn_read.close()
    conn_write.close()

# Esempio di esecuzione
if __name__ == "__main__":
    #clean_DB('axess', 'axess', False)
    clean_DB('webhooktest', 'webhooktest', False)