"""
Fix one-off: corregge la colonna Channel nel sidecar export Mediametrie (Germania).
Cerca "Channel" nell'header row (riga 2 per Mediametrie, riga 6 per Germania classica)
e sovrascrive le celle con il nome decodificato dalla tabella reti.

Uso: python\python.exe fix_export_channel.py <export_DETTAGLI.xlsx> [<TABELLA DECODIFICA RETI.CSV>]
Produce: <export_DETTAGLI_fixed.xlsx> nella stessa cartella.
Da cancellare dopo l'uso.
"""
import csv
import os
import sys
import openpyxl

_DEFAULT_CSV = os.path.join(
    os.path.dirname(os.path.abspath(__file__)),
    "..", "..", "NEW_MonitorOsservatorio", "TABELLA DECODIFICA RETI.CSV"
)


def _load_decoder(csv_path: str) -> dict:
    table = {}
    with open(csv_path, newline='', encoding='latin-1') as f:
        for row in csv.DictReader(f, delimiter=';'):
            orig = row.get('ORIGINALE', '').strip()
            dec = row.get('DECODIFICATO', '').strip()
            if orig:
                table[orig.upper()] = dec
    return table


def _find_channel_col(ws) -> tuple[int | None, int]:
    """Cerca 'Channel' nelle righe header candidate. Ritorna (col_1indexed, data_start_row)."""
    for header_row, data_start in ((2, 3), (6, 7)):
        for cell in ws[header_row]:
            if str(cell.value or '').strip() == 'Channel':
                return cell.column, data_start
    return None, 3


def fix(xlsx_path: str, csv_path: str):
    decoder = _load_decoder(csv_path)
    wb = openpyxl.load_workbook(xlsx_path)
    ws = wb.active

    channel_col, data_start = _find_channel_col(ws)
    if channel_col is None:
        print("ERRORE: colonna 'Channel' non trovata nelle righe header. File non riconosciuto.")
        sys.exit(1)

    fixed = 0
    for (cell,) in ws.iter_rows(min_row=data_start, min_col=channel_col, max_col=channel_col):
        if cell.value is None:
            continue
        raw = str(cell.value).strip()
        decoded = decoder.get(raw.upper(), raw)
        if decoded != raw:
            cell.value = decoded
            fixed += 1

    out = xlsx_path.rsplit('.xlsx', 1)[0] + '_fixed.xlsx'
    wb.save(out)
    print(f"Fixati {fixed} canali su {ws.max_row - data_start + 1} righe → {out}")


if __name__ == '__main__':
    if len(sys.argv) < 2:
        print(__doc__)
        sys.exit(1)
    xlsx = sys.argv[1]
    csv_p = sys.argv[2] if len(sys.argv) > 2 else _DEFAULT_CSV
    if not os.path.exists(xlsx):
        print(f"ERRORE: file non trovato: {xlsx}")
        sys.exit(1)
    if not os.path.exists(csv_p):
        print(f"ERRORE: CSV decoder non trovato: {csv_p}")
        sys.exit(1)
    fix(xlsx, csv_p)