import csv
import json
from datetime import datetime
import mysql.connector

def fmt(val):
    if val is None or val == "" or val == "NULL":
        return "NULL"
    val = str(val).replace("'", "''")
    return f"'{val}'"

BATCH_SIZE = 5000

# Connessione al database
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password= '5!1CYZP(DM?D',
    database='axess'
)
cursor = conn.cursor()
batch = []
total_inserted = 0

header = (
    "INSERT IGNORE INTO wh_data "
    "(WHD_cht_id, WHD_org_name_l1, WHD_org_site, WHD_org_site_area, WHD_mac, WHD_signal, "
    "WHD_timestamp, WHD_day, WHD_hour, WHD_minute, WHD_second, "
    "WHD_is_random_mac, WHD_fingerprint, WHD_AP_mac, WHD_source) VALUES "
)

with open("dati.csv", newline='', encoding="utf-8") as infile:
    reader = csv.DictReader(infile)

    for row in reader:
        try:
            probes = json.loads(row['probes'].replace('""', '"'))
        except Exception:
            continue

        # Timestamp derivato da `unixtime`
        try:
            raw_unixtime = row.get('unixtime', '').strip()
            if raw_unixtime and raw_unixtime.isdigit():
                ts = int(raw_unixtime) // 1000
                dt = datetime.fromtimestamp(ts)
            else:
                raw_date = row.get('date_registered', '').strip()
                if not raw_date:
                    raise ValueError("Mancano sia unixtime che date_registered")
                dt = datetime.strptime(raw_date.split('.')[0], "%Y-%m-%d %H:%M:%S")  # rimuove .000
                ts = int(dt.timestamp())

            timestamp = dt.strftime("%Y-%m-%d %H:%M:%S")
            date = dt.strftime("%Y-%m-%d")
            hour = dt.strftime("%H")
            minute = dt.strftime("%M")
            second = dt.strftime("%S")
        except Exception as e:
            print("❌ Errore determinazione timestamp:")
            print("↪️ Riga completa CSV:")
            for k, v in row.items():
                print(f"  {k}: {v}")
            print(f"⛔ Eccezione: {e}")
            raise SystemExit(1)

        # Valori fissi o derivati
        cht_id = row.get('cht_id', '0')
        org_name_l1 = 'arg'
        org_site = '99'
        org_site_area = '-'
        is_random_mac = int(row.get('is_random_mac', 0))
        fingerprint = row.get('fingerprint', '-')

        if str(fingerprint).lower() == "0x0":
            fingerprint = probe.get('mac')

        source = int(cht_id[-4:]) if cht_id[-4:].isdigit() else 0

        for probe in probes:
            probe_ts_raw = probe.get('timestamp')
            if probe_ts_raw and str(probe_ts_raw).isdigit():
                probe_dt = datetime.fromtimestamp(int(probe_ts_raw) // 1000)
            else:
                probe_dt = dt  # usa quello calcolato da unixtime/date_registered

            probe_timestamp = probe_dt.strftime("%Y-%m-%d %H:%M:%S")
            probe_date = probe_dt.strftime("%Y-%m-%d")
            probe_hour = probe_dt.strftime("%H")
            probe_minute = probe_dt.strftime("%M")
            probe_second = probe_dt.strftime("%S")

            values = (
                f"{fmt(cht_id)}, {fmt(org_name_l1)}, {fmt(org_site)}, {fmt(org_site_area)}, "
                f"{fmt(probe.get('mac'))}, {probe.get('signal')}, "
                f"{fmt(probe_timestamp)}, {fmt(probe_date)}, {probe_hour}, {probe_minute}, {probe_second}, "
                f"{is_random_mac}, {fmt(fingerprint)}, {fmt(probe.get('mac_phy'))}, {source}"
            )
            batch.append(f"({values})")

            if len(batch) >= BATCH_SIZE:
                sql = header + ",\n".join(batch) + ";"
                try:
                    cursor.execute(sql)
                    conn.commit()
                    total_inserted += len(batch)
                    print(f"✔️ Inserite {total_inserted} righe")
                except Exception as e:
                    print(f"❌ Errore batch: {e}")
                    conn.rollback()
                batch = []

# Ultima batch
if batch:
    sql = header + ",\n".join(batch) + ";"
    try:
        cursor.execute(sql)
        conn.commit()
        total_inserted += len(batch)
        print(f"✔️ Inserite {total_inserted} righe finali")
    except Exception as e:
        print(f"❌ Errore batch finale: {e}")
        conn.rollback()

cursor.close()
conn.close()