import duckdb
import sys
sys.path.insert(0, 'C:/PYTHON/DataHub_v2')
import os
os.chdir('C:/PYTHON/DataHub_v2')

con = duckdb.connect()
src = 'C:/PYTHON/legacy_snapshot/estr_emesso.csv'
csv_expr = f"read_csv('{src}', skip=3, delim=';', quote='\"', escape='\"', strict_mode=false, all_varchar=true, encoding='cp1252', header=true, ignore_errors=true)"

result = con.execute(f"""
    WITH base AS (
        SELECT
            TRIM("EMESSO_CCRETE")          AS rete,
            TRIM("Emesso - Data")          AS data,
            TRIM("Inizio Emissione")       AS ora_inizio,
            TRIM("EPS_PROGRAM_CODE")       AS prodotto,
            TRIM("Emesso - Audience")      AS audience,
            TRIM("Emesso - Prima Visione") AS prima_visione,
            TRIM(UPPER("tipologia"))       AS tipologia,
            TRIM("Emesso - Dur. netta")    AS durata_netta,
            TRIM("Edizione")               AS edizione
        FROM {csv_expr}
        WHERE "EPS_PROGRAM_CODE" IS NOT NULL
          AND "Emesso - Data"    IS NOT NULL
          AND "Inizio Emissione" IS NOT NULL
    ),
    dupes AS (
        SELECT rete, data, ora_inizio, COUNT(*) AS n
        FROM base
        GROUP BY rete, data, ora_inizio
        HAVING COUNT(*) > 1
        ORDER BY n DESC
        LIMIT 20
    )
    SELECT b.*
    FROM base b
    JOIN dupes d ON b.rete=d.rete AND b.data=d.data AND b.ora_inizio=d.ora_inizio
    ORDER BY b.rete, b.data, b.ora_inizio
    LIMIT 80
""").fetchall()

print(f"{'rete':<6} {'data':<10} {'ora':>6} {'prodotto':>10} {'ediz':>5} {'audience':>10} {'pv':<4} {'tipologia':<30} {'durata':>7}")
print("-" * 100)
for r in result:
    print(f"{str(r[0]):<6} {str(r[1]):<10} {str(r[2]):>6} {str(r[3]):>10} {str(r[8]):>5} {str(r[4]):>10} {str(r[5]):<4} {str(r[6]):<30} {str(r[7]):>7}")

# Conta totale duplicati
total = con.execute(f"""
    WITH base AS (
        SELECT
            TRIM("EMESSO_CCRETE") AS rete,
            TRIM("Emesso - Data") AS data,
            TRIM("Inizio Emissione") AS ora_inizio
        FROM {csv_expr}
        WHERE "EPS_PROGRAM_CODE" IS NOT NULL
          AND "Emesso - Data" IS NOT NULL
          AND "Inizio Emissione" IS NOT NULL
    )
    SELECT COUNT(*) - COUNT(DISTINCT (rete, data, ora_inizio)) AS righe_extra
    FROM base
""").fetchone()[0]
print(f"\nTotale righe 'extra' nel CSV (oltre la prima per ogni chiave): {total:,}")
con.close()