import re, datetime as dt, copy
from collections import defaultdict, Counter
from openpyxl import load_workbook, Workbook
from openpyxl.styles import PatternFill, Font, Alignment
from openpyxl.utils import get_column_letter

SRC='/Users/paolo/Downloads/MODULO RIBA.xlsx'
OUT='/Users/paolo/Desktop/MODULO RIBA - DA SISTEMARE (per Tommaso).xlsx'
wb=load_workbook(SRC, data_only=True)
ws=wb['RIBA']
hdr=[c.value for c in ws[1]]
I=dict(DATA=0,FORN=1,NUM=2,TOT=3,RATA=4,SCAD=5,MET=19,STATO=20,NOTE=21)

def norm_forn(v): return re.sub(r'\s+',' ',str(v)).strip().upper() if v is not None else ''
def num(v):
    if v is None or v=='': return None
    if isinstance(v,(int,float)): return float(v)
    s=str(v).replace('€','').replace('.','').replace(',','.').strip()
    try: return float(s)
    except: return None
def norm_num(v): return re.sub(r'\s+','',str(v)).strip().upper() if v is not None else ''
def isdate(v): return isinstance(v,(dt.datetime,dt.date))
def anno(r):
    for c in (I['DATA'], I['SCAD']):
        if isdate(r[c]): return r[c].year
    return None

rows=[]
for ridx,r in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
    vals=[v for v in r if v is not None and str(v).strip()!='']
    if not vals: continue
    # righe di piede (totale in fondo, senza fornitore): non sono dati
    if norm_forn(r[I['FORN']])=='' and norm_num(r[I['NUM']])=='' and not isdate(r[I['DATA']]): continue
    rows.append((ridx,r))

# codici -> (colore, etichetta breve, cosa fare)
REGOLE={
 'FORMATO':  ('FF9E9E9E','Formato',            'Nel totale e nella rata ci va SOLO un numero (es. 2397,30). Niente somme scritte a mano, niente due fatture nella stessa riga: una fattura per riga.'),
 'RATE':     ('FFFF6B6B','Rate ≠ totale',       'La somma delle rate deve dare esattamente il totale della fattura. I casi tipici: un centesimo o una cifra sbagliata; una riga inserita due volte (c\'è scritto RIGA DOPPIA: cancellane una); una nota di credito o la ritenuta d\'acconto del commercialista che ha ridotto quello che hai pagato davvero. In quest\'ultimo caso scrivi nel totale l\'importo che risulta dalle rate, cioè quello pagato.'),
 'NEGATIVO': ('FFD7B5FF','Nota di credito',     'Va bene tenerle, ma totale e rata devono avere lo stesso segno (tutti e due negativi), e nella colonna RI.BA - RID - BONIFICO scrivi NOTA DI CREDITO. Se la nota è stata scalata da una fattura, l\'importo scalato va tolto anche dal totale di quella fattura.'),
 'SENZA_NUM':('FFFFE066','Manca il n. fattura', 'Scrivi il numero della fattura. Se è una bolletta o una tassa senza numero, scrivi il periodo (es. LUGLIO 2022) oppure spostala nel foglio TASSE.'),
 'RIUSATO':  ('FFFFB347','Numero ripetuto',     'Stesso fornitore, stesso numero, stesso anno, ma totali diversi: una delle due ha il numero sbagliato. Controlla sulle fatture cartacee.'),
 'SCADENZA': ('FF8ECAE6','Manca la scadenza',   'Scrivi la data di scadenza (o quella del pagamento, se è già stata pagata). Niente punti interrogativi.'),
}
ORDINE=['FORMATO','RATE','NEGATIVO','SENZA_NUM','RIUSATO','SCADENZA']

prob=defaultdict(list)  # ridx -> [(codice, dettaglio)]
for ridx,r in rows:
    t=num(r[I['TOT']]); ra=num(r[I['RATA']])
    if r[I['TOT']] not in (None,'') and t is None: prob[ridx].append(('FORMATO','totale non è un numero: "%s"'%r[I['TOT']]))
    if r[I['RATA']] not in (None,'') and ra is None: prob[ridx].append(('FORMATO','rata non è un numero: "%s"'%r[I['RATA']]))
    if r[I['TOT']] in (None,''): prob[ridx].append(('FORMATO','manca il totale'))
    if r[I['RATA']] in (None,''): prob[ridx].append(('FORMATO','manca la rata'))
    n=str(r[I['NUM']]) if r[I['NUM']] is not None else ''
    if '+' in n: prob[ridx].append(('FORMATO','due fatture nella stessa riga: "%s"'%n))
    if not isdate(r[I['SCAD']]): prob[ridx].append(('SCADENZA','scadenza vuota o "%s"'%(r[I['SCAD']] or '')))
    if norm_num(r[I['NUM']])=='': prob[ridx].append(('SENZA_NUM','numero fattura vuoto'))
    if (t is not None and t<0) or (ra is not None and ra<0):
        d='totale e rata di segno diverso' if (t is not None and ra is not None and (t<0)!=(ra<0)) else 'importo negativo'
        prob[ridx].append(('NEGATIVO',d))

gruppi=defaultdict(list)
for ridx,r in rows:
    f=norm_forn(r[I['FORN']]); n=norm_num(r[I['NUM']]); t=num(r[I['TOT']])
    if f and n and t is not None: gruppi[(f,n,round(t,2))].append((ridx,num(r[I['RATA']]) or 0.0))
rowmap=dict(rows)
for k,lst in gruppi.items():
    somma=round(sum(x[1] for x in lst),2)
    if abs(somma-k[2])>0.02:
        # riga doppia: stessa rata e stessa scadenza dentro la stessa fattura
        visti=Counter((round(ra,2), rowmap[ridx][I['SCAD']]) for ridx,ra in lst)
        for ridx,ra in lst:
            d='rate %.2f contro totale %.2f (%d righe)'%(somma,k[2],len(lst))
            if visti[(round(ra,2), rowmap[ridx][I['SCAD']])]>1: d='RIGA DOPPIA (stessa rata e stessa scadenza) - '+d
            prob[ridx].append(('RATE',d))
per_num=defaultdict(set)
for (f,n,t),lst in gruppi.items():
    a=anno(dict(rows)[lst[0][0]])
    per_num[(f,n,a)].add(t)
for (f,n,a),tots in per_num.items():
    if len(tots)>1:
        for t in tots:
            for ridx,_ in gruppi[(f,n,t)]: prob[ridx].append(('RIUSATO','n. %s usato %d volte nel %s con totali %s'%(n,len(tots),a,', '.join('%.2f'%x for x in sorted(tots)))))

# ---------- workbook nuovo (niente pivot) ----------
out=Workbook(); out.remove(out.active)
FMT_DATE='DD/MM/YYYY'; FMT_NUM='#,##0.00'
bold=Font(bold=True)
def copia_foglio(nome, evidenzia=None):
    src=wb[nome]; dst=out.create_sheet(nome)
    maxc=src.max_column
    for row in src.iter_rows(min_row=1, max_row=src.max_row):
        for c in row:
            d=dst.cell(c.row,c.column,c.value)
            if isdate(c.value): d.number_format=FMT_DATE
            elif isinstance(c.value,float): d.number_format=FMT_NUM
            if c.row==1: d.font=bold
    for i in range(1,maxc+1): dst.column_dimensions[get_column_letter(i)].width=14
    dst.column_dimensions['B'].width=22
    dst.freeze_panes='A2'
    return dst

dst=copia_foglio('RIBA')
colP=ws.max_column+1; colD=colP+1
dst.cell(1,colP,'PROBLEMA').font=bold; dst.cell(1,colD,'COSA NON VA').font=bold
dst.column_dimensions[get_column_letter(colP)].width=22
dst.column_dimensions[get_column_letter(colD)].width=70
conteggi=Counter()
for ridx,lst in prob.items():
    codici=[]
    for c,_ in lst:
        if c not in codici: codici.append(c)
    principale=next(c for c in ORDINE if c in codici)
    fill=PatternFill('solid', fgColor=REGOLE[principale][0])
    for col in range(1,ws.max_column+1): dst.cell(ridx,col).fill=fill
    dst.cell(ridx,colP,' + '.join(REGOLE[c][1] for c in codici)).fill=fill
    dst.cell(ridx,colD,'; '.join(d for _,d in lst)).alignment=Alignment(wrap_text=False)
    for c in codici: conteggi[c]+=1
dst.auto_filter.ref=f"A1:{get_column_letter(colD)}{ws.max_row}"

# fogli secondari copiati tali e quali
copia_foglio('TASSE'); copia_foglio('CARTA DI CREDITO')

# LEGENDA
lg=out.create_sheet('LEGENDA',0)
lg.column_dimensions['A'].width=6; lg.column_dimensions['B'].width=24; lg.column_dimensions['C'].width=10; lg.column_dimensions['D'].width=110
lg['A1']='Foglio RIBA: %d righe, %d con qualcosa da sistemare (%.0f%%). Le righe colorate hanno la spiegazione nelle due colonne in fondo a destra (PROBLEMA e COSA NON VA). Il filtro sulla riga 1 aiuta a vederle tutte insieme.'%(len(rows),len(prob),100*len(prob)/len(rows))
lg['A1'].font=bold
lg.merge_cells('A1:D1'); lg['A1'].alignment=Alignment(wrap_text=True, vertical='top'); lg.row_dimensions[1].height=40
lg['A3']='Colore'; lg['B3']='Problema'; lg['C3']='Righe'; lg['D3']='Cosa fare'
for c in 'ABCD': lg[c+'3'].font=bold
r=4
for c in ORDINE:
    lg.cell(r,1).fill=PatternFill('solid',fgColor=REGOLE[c][0])
    lg.cell(r,2,REGOLE[c][1]); lg.cell(r,3,conteggi[c]); lg.cell(r,4,REGOLE[c][2]).alignment=Alignment(wrap_text=True, vertical='top')
    lg.row_dimensions[r].height=48
    r+=1
r+=1
note=[
 'Le righe che non sono colorate vanno bene così come sono: non toccarle.',
 'Se una riga ha più di un colore possibile, il colore è quello del problema più grosso, ma nella colonna COSA NON VA ci sono tutti.',
 'Nomi dei fornitori scritti in modi diversi (CASIO / casio / CASIO con lo spazio): ci pensiamo noi, non serve che li sistemi.',
 'Stato: per noi V = pagata. Le X nella colonna STATO cosa vogliono dire? Sono %d righe.'%sum(1 for _,x in rows if str(x[I['STATO']] or '').strip().upper()=='X'),
 'F24 e tasse messe nel foglio RIBA con MANFREDINI come fornitore: meglio nel foglio TASSE, così restano separate dai fornitori di merce.',
 'Le ultime righe in fondo al foglio (il totale generale) le abbiamo lasciate fuori: non sono fatture.',
]
for n in note:
    lg.cell(r,1,'•'); lg.cell(r,2,n); lg.merge_cells(start_row=r,start_column=2,end_row=r,end_column=4)
    lg.cell(r,2).alignment=Alignment(wrap_text=True, vertical='top'); lg.row_dimensions[r].height=30; r+=1

out.save(OUT)
# verifica riapertura
chk=load_workbook(OUT); print('salvato e riaperto:', chk.sheetnames, chk['RIBA'].max_row)
print('righe dati:',len(rows),'| con problemi:',len(prob))
for c in ORDINE: print('  %-12s %d'%(REGOLE[c][1],conteggi[c]))