import duckdb
from datetime import date

pq = 'I:/SOFTWARE/PYTHON_SRV/parquet_layer1/diritti.parquet'
today = date.today().isoformat()
con = duckdb.connect()
con.execute(f"CREATE TABLE _dir AS SELECT * FROM '{pq}' WHERE UPPER(tipo_diritto)='FREE'")

con.execute(f"""
    CREATE TABLE _dvbt_only AS
    SELECT DISTINCT d.prod
    FROM _dir d
    WHERE UPPER(d.piattaforma) = 'DVB-T'
      AND (d.decr <= '{today}' AND d.scad >= '{today}' OR d.decr > '{today}')
      AND NOT EXISTS (
          SELECT 1 FROM _dir a
          WHERE UPPER(a.piattaforma) = 'ANALOGICO'
            AND a.prod = d.prod AND a.decr = d.decr AND a.scad = d.scad
      )
""")

# Simula il risultato di diritti_enrich: quali righe finiscono in ESCLUSIVO_TEM=TRUE?
rows = con.execute(f"""
    SELECT
        COUNT(*)                                           AS tot_righe,
        COUNT(DISTINCT d.prod)                            AS tot_prodotti,
        SUM(CASE WHEN UPPER(d.piattaforma)='ANALOGICO' THEN 1 ELSE 0 END) AS righe_analogico,
        SUM(CASE WHEN UPPER(d.piattaforma)='DVB-T'    THEN 1 ELSE 0 END) AS righe_dvbt
    FROM _dir d
    WHERE d.prod IN (SELECT prod FROM _dvbt_only)
      AND (
          UPPER(d.piattaforma) = 'ANALOGICO'
          OR (UPPER(d.piattaforma) = 'DVB-T'
              AND NOT EXISTS (
                  SELECT 1 FROM _dir a
                  WHERE UPPER(a.piattaforma)='ANALOGICO'
                    AND a.prod=d.prod AND a.decr=d.decr AND a.scad=d.scad
              ))
      )
""").fetchone()

print(f"Righe ESCLUSIVO_TEM=TRUE : {rows[0]}")
print(f"Prodotti distinti        : {rows[1]}")
print(f"  di cui righe Analogico : {rows[2]}")
print(f"  di cui righe DVB-T     : {rows[3]}")