#!/usr/bin/env python3
"""
FAK Comparison System — Cippà Trasporti
Confronta tariffe FAK da PDF / CSV / XLSX e genera report aggiornato.

Come usarlo:
  1. Metti i file dei carrier nella cartella ~/.hermes/fak-comparison/archive/YYYYMMDD-YYYYMMDD/
  2. Esegui: python3 ~/.hermes/fak-comparison/compare_fak.py
  3. Il report viene salvato in ~/.hermes/fak-comparison/latest_report.txt
  4. La dashboard HTML viene aggiornata automaticamente
"""

import json
import os
import csv
import glob
import re
from datetime import datetime

BASE_DIR = os.path.expanduser("~/.hermes/fak-comparison")
DATA_FILE = os.path.join(BASE_DIR, "comparison_data.json")
REPORT_FILE = os.path.join(BASE_DIR, "latest_report.txt")
DASHBOARD_FILE = os.path.join(BASE_DIR, "fak_dashboard.html")
ARCHIVE_DIR = os.path.join(BASE_DIR, "archive")

def load_data():
    if os.path.exists(DATA_FILE):
        with open(DATA_FILE) as f:
            return json.load(f)
    return {"carriers": {}, "rates": {}, "surcharges": {}, "metadata": {"last_updated": "N/A", "version": "1.0"}}

def save_data(data):
    with open(DATA_FILE, "w") as f:
        json.dump(data, f, indent=2, default=str)

def parse_evergreen_csv(filepath, data):
    """Parser per il formato IT Guideline Evergreen"""
    rates = {}
    with open(filepath, newline='', encoding='utf-8-sig', errors='replace') as f:
        reader = csv.reader(f, delimiter=';')
        for i, row in enumerate(reader):
            if i == 0:
                # Parse header: trova i POD (GENOVA, LA SPEZIA, etc.)
                pods = []
                for cell in row[2:]:
                    cell = cell.strip().upper()
                    if cell and cell not in pods and cell not in ['20\'','40\'','40H']:
                        pods.append(cell)
                continue
            if i == 1 or not row or len(row) < 4:
                continue
            row = [r.strip() for r in row]
            if not row[0] or row[0].startswith('1.') or 'Rate' in row[0] or 'Validity' in row[0]:
                continue
            if len(row[0]) < 2:
                continue
            
            pol_code = row[0]
            pol_name = row[1].strip() if len(row) > 1 else pol_code
            
            # Col 2-25: POD x container (20',40',40H) x 8 PODs = 24 colonne
            col = 2
            for pod_idx in range(8):
                if col + 2 >= len(row):
                    break
                pod = pods[pod_idx] if pod_idx < len(pods) else f"POD{pod_idx}"
                try:
                    rate_20 = float(row[col]) if row[col] else None
                    rate_40 = float(row[col+1]) if row[col+1] else None
                    rate_40h = float(row[col+2]) if row[col+2] else None
                except (ValueError, IndexError):
                    rate_20 = rate_40 = rate_40h = None
                
                if rate_20:
                    key = f"{pol_code}:{pod}"
                    rates[key] = {
                        "pol": pol_code, "pol_name": pol_name,
                        "pod": pod,
                        "20": rate_20, "40": rate_40, "40h": rate_40h if rate_40h else rate_40
                    }
                col += 3
    return rates

def parse_cosco_pdf(filepath, data):
    """Parser per PDF Cosco — estrazione base"""
    rates = {}
    # Prova a estrarre con pdftotext
    txt_path = filepath + ".txt"
    os.system(f'pdftotext -layout "{filepath}" "{txt_path}" 2>/dev/null')
    
    if os.path.exists(txt_path):
        with open(txt_path) as f:
            text = f.read()
        
        # Cerca pattern tariffari
        lines = text.split('\n')
        for line in lines:
            # Pattern: port code / numbers
            parts = line.strip().split()
            if len(parts) >= 4:
                # Cerca numeri che sembrano tariffe
                nums = [p for p in parts if re.match(r'^\d{3,5}$', p.replace(',','').replace('.',''))]
                if len(nums) >= 2:
                    code = parts[0]
                    if len(code) == 5 or len(code) == 6:  # port code like CNSHG
                        try:
                            r20 = float(nums[0])
                            r40 = float(nums[1]) if len(nums) > 1 else r20 * 1.6
                            key = f"{code}:GENOVA"
                            rates[key] = {"pol": code, "pod": "GENOVA", "20": r20, "40": r40, "40h": r40}
                        except:
                            pass
    
    return rates

def generate_report(data):
    """Genera report testuale"""
    now = datetime.now().strftime("%d/%m/%Y %H:%M")
    lines = [
        "=" * 72,
        f"  FAK CONFRONTO TARIFFE — Cippà Trasporti",
        f"  Generato: {now}",
        "=" * 72,
        "",
    ]
    
    carriers = list(data.get("rates", {}).keys())
    if not carriers:
        lines.append("⚠️  Nessuna tariffa caricata. Invia i file dei carrier!")
        return "\n".join(lines)
    
    lines.append(f"Carrier attivi: {', '.join(c.upper() for c in carriers)}")
    lines.append("")
    
    # Confronto per rotte comuni
    all_routes = set()
    carrier_rates = {}
    for carrier in carriers:
        routes = data["rates"].get(carrier, {})
        carrier_rates[carrier] = routes
        if isinstance(routes, dict):
            all_routes.update(routes.keys())
    
    if len(carriers) >= 2 and all_routes:
        lines.append("─" * 72)
        lines.append("  CONFRONTO CARRIER — Rotte Comuni")
        lines.append("─" * 72)
        header = f"{'POL → POD':<25} {'Tipo':<6}"
        for c in carriers:
            header += f" {c.upper():>12}"
        if len(carriers) >= 2:
            header += f" {'MIGLIOR':>12}"
        lines.append(header)
        lines.append("─" * 72)
        
        # Confronta le prime 15 rotte
        count = 0
        for route in sorted(all_routes):
            if count >= 15:
                break
            pol_pod = route
            
            for ctype in ["20", "40"]:
                vals = {}
                best_carrier = None
                best_price = float('inf')
                
                for carrier in carriers:
                    routes = carrier_rates.get(carrier, {})
                    if isinstance(routes, dict) and route in routes:
                        val = routes[route].get(ctype)
                        if val:
                            vals[carrier] = val
                            if val < best_price:
                                best_price = val
                                best_carrier = carrier
                
                if len(vals) >= 2:
                    container_label = ctype + "'"
                    line = f"{pol_pod:<25} {container_label:<6}"
                    for carrier in carriers:
                        v = vals.get(carrier, "—")
                        line += f" {str(v):>12}"
                    line += f" {'✅ ' + best_carrier.upper() if best_carrier else '—':>12}"
                    lines.append(line)
                    count += 1
        
        lines.append("─" * 72)
    
    # Tariffe disponibili per carrier
    lines.append("")
    lines.append("─" * 72)
    lines.append("  TARIFFE DISPONIBILI PER CARRIER")
    lines.append("─" * 72)
    for carrier in carriers:
        routes = data["rates"].get(carrier, {})
        if isinstance(routes, dict):
            count = len(routes)
            lines.append(f"  {carrier.upper()}: {count} rotte caricate")
        else:
            lines.append(f"  {carrier.upper()}: tariffe forfettarie caricate")
    
    # Surcharges
    lines.append("")
    lines.append("─" * 72)
    lines.append("  SOPRATTASSE APPLICABILI")
    lines.append("─" * 72)
    for carrier, surcharges in data.get("surcharges", {}).items():
        lines.append(f"  {carrier.upper()}:")
        for name, info in surcharges.items():
            if isinstance(info, dict):
                amount = info.get("amount", "")
                unit = info.get("unit", "")
                note = info.get("note", "")
                curr = info.get("currency", "")
                parts = [f"    • {name.upper()}"]
                if amount:
                    parts.append(f"{amount}{' '+curr if curr else ''}{'/'+unit if unit else ''}")
                if note:
                    parts.append(f"({note})")
                lines.append(" ".join(parts))
            elif isinstance(info, list):
                lines.append(f"    • {name.upper()}: {', '.join(str(i) for i in info)}")
            else:
                lines.append(f"    • {name.upper()}: {info}")
    
    # Istruzioni
    lines.append("")
    lines.append("=" * 72)
    lines.append("  ISTRUZIONI")
    lines.append("=" * 72)
    lines.append("  Per aggiornare le tariffe, inviami i nuovi file via Telegram.")
    lines.append("  Formati accettati: PDF, CSV, XLSX")
    lines.append("=" * 72)
    
    return "\n".join(lines)

def generate_dashboard_html(data):
    """Genera dashboard HTML"""
    now = datetime.now().strftime("%d/%m/%Y %H:%M")
    
    # Rotte migliori per confronto
    carriers = list(data.get("rates", {}).keys())
    
    carrier_cards = ""
    for carrier in carriers:
        routes = data["rates"].get(carrier, {})
        surcharges = data.get("surcharges", {}).get(carrier, {})
        sc_list = ""
        for name, info in surcharges.items():
            if isinstance(info, dict):
                amount = info.get('amount', '')
                note = info.get('note', '')
                sc_list += f"<li><strong>{name.upper()}</strong>: {amount}"
                if note:
                    sc_list += f" ({note})"
                sc_list += "</li>"
            elif isinstance(info, list):
                sc_list += f"<li><strong>{name.upper()}</strong>: {', '.join(str(i) for i in info)}</li>"
            else:
                sc_list += f"<li><strong>{name.upper()}</strong>: {info}</li>"
        
        if isinstance(routes, dict):
            route_count = len(routes)
            sample = list(routes.items())[:5] if routes else []
            route_table = "<table><tr><th>POL→POD</th><th>20'</th><th>40'</th><th>40H</th></tr>"
            for key, r in sample:
                route_table += f"<tr><td>{r.get('pol','?')} → {r.get('pod','?')}</td><td>${r.get('20','—')}</td><td>${r.get('40','—')}</td><td>${r.get('40h','—')}</td></tr>"
            route_table += "</table>"
        else:
            route_count = "Forfettarie"
            route_table = f"<p>West Med: 20' ${data['rates'][carrier].get('west_med',{}).get('20','')} / 40' ${data['rates'][carrier].get('west_med',{}).get('40','')}</p>"
        
        carrier_cards += f"""
        <div class="carrier-card">
            <h2>{carrier.upper()}</h2>
            <div class="meta">Rotte: {route_count}</div>
            <h4>Tariffe</h4>
            {route_table}
            <h4>Soprattasse</h4>
            <ul>{sc_list}</ul>
        </div>
        """
    
    # Confronto Shanghai→Genova
    comparison_table = ""
    if len(carriers) >= 2:
        comparison_table = """
        <h3>📊 Confronto Shanghai → Genova</h3>
        <table>
            <tr><th>Carrier</th><th>20'</th><th>40'</th><th>40HC</th></tr>
        """
        for carrier in carriers:
            routes = data["rates"].get(carrier, {})
            if isinstance(routes, dict):
                rate = routes.get("CNSHG:GENOVA") or routes.get("SHANGHAI:GENOVA", {})
                r20 = rate.get("20", "—")
                r40 = rate.get("40", "—")
                r40h = rate.get("40h", "—")
                comparison_table += f"<tr><td><strong>{carrier.upper()}</strong></td><td>${r20}</td><td>${r40}</td><td>${r40h}</td></tr>"
        comparison_table += "</table>"
    
    html = f"""<!DOCTYPE html>
<html lang="it">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>FAK Confronto Tariffe — Cippà Trasporti</title>
<style>
    * {{ margin:0; padding:0; box-sizing:border-box; }}
    body {{ font-family:'Segoe UI',Arial,sans-serif; background:#0f1923; color:#e0e0e0; padding:20px; }}
    h1 {{ color:#00d4aa; margin-bottom:5px; }}
    .subtitle {{ color:#8899aa; margin-bottom:20px; font-size:14px; }}
    .grid {{ display:grid; grid-template-columns:repeat(auto-fit,minmax(350px,1fr)); gap:20px; }}
    .carrier-card {{ background:#1a2a3a; border-radius:12px; padding:20px; border:1px solid #2a3a4a; }}
    .carrier-card h2 {{ color:#00d4aa; margin-bottom:10px; }}
    .meta {{ color:#8899aa; font-size:13px; margin-bottom:15px; }}
    h3 {{ color:#ffd700; margin:25px 0 15px; }}
    h4 {{ color:#66ccff; margin:15px 0 8px; }}
    table {{ width:100%; border-collapse:collapse; margin:8px 0; font-size:13px; }}
    th, td {{ padding:8px 10px; text-align:left; border-bottom:1px solid #2a3a4a; }}
    th {{ background:#1a2a3a; color:#66ccff; font-weight:600; }}
    td {{ color:#ccc; }}
    ul {{ padding-left:18px; font-size:13px; }}
    li {{ margin:4px 0; color:#aaa; }}
    .footer {{ margin-top:30px; text-align:center; color:#556677; font-size:12px; border-top:1px solid #2a3a4a; padding-top:15px; }}
    .badge {{ display:inline-block; background:#00d4aa22; color:#00d4aa; padding:2px 8px; border-radius:4px; font-size:11px; }}
</style>
</head>
<body>
    <h1>🚢 FAK Confronto Tariffe</h1>
    <div class="subtitle">Cippà Trasporti — Far East → Italia | Ultimo aggiornamento: {now}</div>
    
    <div style="margin-bottom:20px;">
        <span class="badge">Carrier: {', '.join(c.upper() for c in carriers)}</span>
        <span class="badge">Formati: PDF • CSV • XLSX</span>
    </div>
    
    {comparison_table}
    
    <h3>📦 Dettaglio per Carrier</h3>
    <div class="grid">
        {carrier_cards}
    </div>
    
    <div class="footer">
        Sistema Confronto FAK — Circuito Privato Cippà Trasporti<br>
        Per aggiornare: invia nuovi file via Telegram a Emma 🦊
    </div>
</body>
</html>"""
    
    with open(DASHBOARD_FILE, "w") as f:
        f.write(html)
    return DASHBOARD_FILE

def main():
    data = load_data()
    
    # Cerca nuovi file nell'archivio
    for folder in sorted(os.listdir(ARCHIVE_DIR), reverse=True):
        folder_path = os.path.join(ARCHIVE_DIR, folder)
        if not os.path.isdir(folder_path):
            continue
        
        # Evergreen CSV
        for f in glob.glob(os.path.join(folder_path, "*evergreen*")) + glob.glob(os.path.join(folder_path, "*IT*Guideline*")) + glob.glob(os.path.join(folder_path, "*EG*")):
            if f.endswith('.csv'):
                print(f"[INFO] Parsing Evergreen: {os.path.basename(f)}")
                rates = parse_evergreen_csv(f, data)
                if rates:
                    data["rates"]["evergreen"] = rates
                    data["carriers"]["evergreen"]["valid_from"] = folder[:8] if len(folder) >= 8 else "N/A"
                    data["carriers"]["evergreen"]["valid_to"] = folder[9:17] if len(folder) >= 17 else "N/A"
                    print(f"[OK] Evergreen: {len(rates)} rotte caricate")
        
        # Cosco PDF
        for f in glob.glob(os.path.join(folder_path, "*cosco*")) + glob.glob(os.path.join(folder_path, "*COSCO*")):
            if f.endswith('.pdf'):
                print(f"[INFO] Parsing Cosco: {os.path.basename(f)}")
                rates = parse_cosco_pdf(f, data)
                if rates:
                    data["rates"]["cosco"] = rates
                    print(f"[OK] Cosco: {len(rates)} rotte caricate")
        
        # Generici
        for f in glob.glob(os.path.join(folder_path, "*msc*")) + glob.glob(os.path.join(folder_path, "*MSC*")):
            data["rates"]["msc"] = {
                "FORFETTARIO": {
                    "west_med_20": 3750, "west_med_40": 5500,
                    "adriatic_20": 3750, "adriatic_40": 5500
                }
            }
            print(f"[OK] MSC: tariffe forfettarie caricate")
    
    data["metadata"]["last_updated"] = datetime.now().isoformat()
    save_data(data)
    
    # Genera report
    report = generate_report(data)
    with open(REPORT_FILE, "w") as f:
        f.write(report)
    print(f"[OK] Report salvato: {REPORT_FILE}")
    
    # Genera dashboard
    dash_path = generate_dashboard_html(data)
    print(f"[OK] Dashboard: {dash_path}")
    
    print(report)

if __name__ == "__main__":
    main()