import pandas as pd
import io
import zipfile
import re
from datetime import datetime
from pyscript import document
from pyodide.ffi import create_proxy
from pyodide.http import pyfetch
from js import Uint8Array, File, URL, document as js_document
import bonus

URL_ARS_MAIL_SENDER = "./arsmailsender.exe" 
df_globale = None
NOMI_TARGET = ["AXA", "ALLIANZ", "PRIMA", "UNIPOLSAI"]

def clean_currency(value):
    s = str(value).strip().replace('€', '').replace(' ', '')
    if s in ["-", "", "None", "nan", "<NA>"]: return 0.0
    if '.' in s and ',' in s: s = s.replace('.', '')
    s = s.replace(',', '.')
    try: return float(s)
    except: return 0.0

def generate_excel_logic(df_data, writer, usa_subtotali):
    workbook = writer.book
    worksheet = workbook.add_worksheet('Sospesi')
    worksheet.set_landscape()
    worksheet.set_paper(9) 
    worksheet.set_margins(left=0.3, right=0.3, top=0.5, bottom=1.0) 
    worksheet.set_footer('&C&10Pagina &P di &N', {'margin': 0.2})
    worksheet.hide_gridlines(2)
    worksheet.fit_to_pages(1, 0)

    header_fmt = workbook.add_format({'bold': True, 'bg_color': '#D7E4BC', 'border': 1, 'font_size': 10})
    text_fmt = workbook.add_format({'border': 1, 'font_size': 10})
    euro_bold_fmt = workbook.add_format({'num_format': '#,##0.00 "€"', 'border': 1, 'font_size': 10, 'bold': True})
    bold_red_fmt = workbook.add_format({'bold': True, 'font_color': '#990000', 'num_format': '#,##0.00 "€"', 'border': 1, 'font_size': 10})
    group_fmt = workbook.add_format({'bold': True, 'bg_color': '#f0f0f0', 'border': 1, 'font_size': 11})

    cols = df_data.columns.tolist()
    fixed_widths = {'Compagnia': 13, 'Tipo': 18, 'Num. Contratto': 15, 'Rischio': 15, 'Effetto': 11, 'Reg. Sospeso': 11, 'Importo': 15}

    curr_row = 0
    page_breaks = []
    if usa_subtotali:
        groups = sorted(df_data['Compagnia'].unique().tolist())
        for idx, comp in enumerate(groups):
            df_c = df_data[df_data['Compagnia'] == comp]
            if idx > 0: page_breaks.append(curr_row)
            worksheet.write(curr_row, 0, f"ARS » {comp.upper()}", group_fmt)
            curr_row += 1
            for i, n in enumerate(cols): worksheet.write(curr_row, i, n, header_fmt)
            curr_row += 1
            for _, r_data in df_c.iterrows():
                for i, v in enumerate(r_data):
                    f = euro_bold_fmt if cols[i] == 'Importo' else text_fmt
                    worksheet.write(curr_row, i, v, f)
                curr_row += 1
            worksheet.write(curr_row, cols.index('Importo'), df_c['Importo'].sum(), bold_red_fmt)
            curr_row += 1
        worksheet.set_h_pagebreaks(page_breaks)
    else:
        for i, n in enumerate(cols): worksheet.write(0, i, n, header_fmt)
        for r_idx, r_data in enumerate(df_data.values):
            for i, v in enumerate(r_data):
                f = euro_bold_fmt if cols[i] == 'Importo' else text_fmt
                worksheet.write(r_idx+1, i, v, f)

    for i, col_name in enumerate(cols):
        w = fixed_widths.get(col_name, 12)
        if col_name == 'Punto Vendita': w = 16
        if col_name == 'Descrizione': w = 38
        worksheet.set_column(i, i, w)

async def analyze_file(event):
    global df_globale
    try:
        file_input = document.querySelector("#file-upload")
        file = file_input.files.item(0)
        array_buffer = await file.arrayBuffer()
        raw_df = pd.read_csv(io.BytesIO(array_buffer.to_py()), sep=';', encoding='latin-1', keep_default_na=False)
        raw_df.columns = raw_df.columns.str.strip()

        def get_pv_safe(row):
            v = str(row.get('Punto Vendita 2', '')).strip()
            if v in ["", "-", "nan", "None", "<NA>"]: v = str(row.get('Punto Vendita 1', '')).strip()
            return v if v not in ["", "-", "nan", "None", "<NA>"] else "PV NON DEFINITO"
            
        raw_df['Punto Vendita'] = raw_df.apply(get_pv_safe, axis=1)
        for c in ['Premio', 'Movimento']: raw_df[c] = raw_df[c].apply(clean_currency) if c in raw_df.columns else 0.0
        raw_df['Importo'] = raw_df['Premio'] + raw_df['Movimento']
        raw_df['Num. Contratto'] = raw_df['Num. Contratto'].astype(str).apply(lambda x: "[ - movimento - ]" if x.strip() in ["", "-", "nan", "None"] else x.strip())
        raw_df['DT_SORT'] = pd.to_datetime(raw_df['Reg. Sospeso'], errors='coerce', dayfirst=True)
        raw_df = raw_df.sort_values(by='DT_SORT', ascending=True)

        mappa = {"AX": "AXA", "AZ": "ALLIANZ", "PR": "PRIMA", "UN": "UNIPOLSAI"}
        def get_comp(r):
            v = str(r.get('Compagnia', '')).strip().upper()
            if v in ['', 'NAN', '-', 'NONE']:
                sigla = re.sub(r'[^A-Z]', '', str(r.get('Descrizione', '')).upper())[:2]
                return mappa.get(sigla, "# COMPAGNIA #")
            return mappa.get(v.split()[0][:2], v.split()[0])
        
        raw_df['Compagnia'] = raw_df.apply(get_comp, axis=1)
        for c in ['Effetto', 'Reg. Sospeso']:
            raw_df[c] = pd.to_datetime(raw_df[c], errors='coerce', dayfirst=True).dt.strftime('%d/%m/%Y').fillna("-")

        df_globale = raw_df[['Punto Vendita', 'Compagnia', 'Descrizione', 'Tipo', 'Num. Contratto', 'Rischio', 'Effetto', 'Reg. Sospeso', 'Importo']].copy()

        # CSV Master
        df_csv = df_globale.copy()
        # Formattazione per Excel italiano (punto e virgola e virgola per i decimali)
        df_csv['Importo'] = df_csv['Importo'].apply(lambda x: f"{x:.2f}".replace('.', ','))
        
        buf_csv = io.BytesIO()
        df_csv.to_csv(buf_csv, sep=';', index=False, encoding='latin-1')
        
        # Creazione del file per il download
        js_csv = Uint8Array.new(len(buf_csv.getvalue()))
        js_csv.assign(buf_csv.getvalue())
        
        # Specifichiamo il tipo MIME 'text/csv' per chiarezza
        blob_csv = File.new([js_csv], "MASTER.csv", { "type": "text/csv" })
        
        link_element = document.querySelector("#master-link-csv")
        link_element.href = URL.createObjectURL(blob_csv)
        # Forza il nome del file anche via codice
        link_element.setAttribute("download", f"MASTER_ELABORATO_{datetime.now().strftime('%H%M')}.csv")
        
        document.querySelector("#master-download-area").style.display = "block"

        # UI Export
        lc = document.querySelector("#checkbox-list"); lc.innerHTML = ""
        for comp in sorted(df_globale['Compagnia'].unique().tolist()):
            is_target = comp.upper() in NOMI_TARGET
            btn = js_document.createElement("button")
            btn.className = f"btn {'btn-success' if is_target else 'btn-danger'} comp-btn py-2 text-truncate"
            btn.innerText = str(comp); btn.dataset.active = "true" if is_target else "false"; btn.dataset.value = str(comp)
            btn.onclick = create_proxy(lambda e: toggle_btn(e))
            div = js_document.createElement("div"); div.className = "col"; div.appendChild(btn); lc.appendChild(div)
            
        for area in ["#selection-area", "#options-area", "#final-area"]: document.querySelector(area).style.display = "block"
            
    except Exception as e: js_document.alert(f"Errore: {e}")

def toggle_btn(e):
    b = e.target; act = b.dataset.active == "true"
    b.classList.replace("btn-success", "btn-danger") if act else b.classList.replace("btn-danger", "btn-success")
    b.dataset.active = "false" if act else "true"

async def process_zip(event):
    ts = datetime.now().strftime("%Y%m%d_%H%M")
    btns = document.querySelectorAll(".comp-btn")
    sel = [b.dataset.value for b in btns if b.dataset.active == "true"]
    df_f = df_globale[df_globale['Compagnia'].isin(sel)].copy()
    z_buf = io.BytesIO()
    with zipfile.ZipFile(z_buf, 'w') as zf:
        if document.querySelector("#opt-mail").checked:
            try:
                resp = await pyfetch(URL_ARS_MAIL_SENDER)
                if resp.status == 200: zf.writestr("arsmailsender.exe", await resp.bytes())
            except: pass
        if document.querySelector("#opt-master").checked:
            m_buf = io.BytesIO()
            with pd.ExcelWriter(m_buf, engine='xlsxwriter') as wr: generate_excel_logic(df_f, wr, document.querySelector("#opt-subtotali").checked)
            zf.writestr(f"00_MASTER_GENERALE_{ts}.xlsx", m_buf.getvalue())
        for pv, df_pv in df_f.groupby('Punto Vendita'):
            p_buf = io.BytesIO()
            with pd.ExcelWriter(p_buf, engine='xlsxwriter') as wr: generate_excel_logic(df_pv, wr, document.querySelector("#opt-subtotali").checked)
            zf.writestr(f"LISTE_PV_{ts}/{str(pv).replace('/','-')}.xlsx", p_buf.getvalue())
    js_a = Uint8Array.new(len(z_buf.getvalue())); js_a.assign(z_buf.getvalue())
    l = js_document.createElement("a"); l.href = URL.createObjectURL(File.new([js_a], "PV.zip")); l.download = f"SOSPESI_PV_{ts}.zip"; l.click()

# Eventi
document.querySelector("#analyze-btn").onclick = create_proxy(analyze_file)
document.querySelector("#process-btn").onclick = create_proxy(process_zip)
document.querySelector("#bonus-btn").onclick = create_proxy(lambda e: bonus.generate_stats_dashboard(df_globale))