import db, numpy as np
import json

def scale(value, unit):
    return value * 20 if unit.startswith('Scala') else value

def get_sector_from_answer(answer_id):
    with db.conn() as connection:
        cursor = connection.cursor()
        cursor.execute("SELECT dati FROM survey_users WHERE id = %s", (answer_id,))
        row = cursor.fetchone()
        if not row:
            return 'unknown'
        try:
            data = json.loads(row[0])
            raw = data.get('srv_settore_appartenenza', 'unknown')
            if raw == "Altro":
                # opzionale: puoi usare anche srv_settore_altro se vuoi popolare record "custom"
                return 'unknown'
            return raw
        except Exception as e:
            print(f"⚠️ Errore nel parsing JSON per answer_id={answer_id}: {e}")
            return 'unknown'
            
def compute(answer_id, sector: str = None):
    # 1) Dati utente
    rows = db.fetch_case_values(answer_id)
    if not rows:
        return []

    if sector is None:
        sector = get_sector_from_answer(answer_id)

    user = {r[0]: scale(float(r[4]), r[2]) for r in rows}

    # 2) Benchmark per settore e multi
    with db.conn() as connection:
        cursor = connection.cursor()
        cursor.execute("""
            SELECT factor_id, AVG(best_value)
                     FROM case_study_kpi
            WHERE sector = %s OR sector = 'multi'
            GROUP BY factor_id
        """, (sector,))
        bench = {fid: scale(float(avg), rows[0][2]) for fid, avg in cursor}

    # 3) Unione user + benchmark
    table = []
    for r in rows:
        fid, label, unit, _, raw = r
        table.append({
            "factor_id": fid,
            "label": label,
            "unit": unit,
            "company": float(raw),
            "benchmark": bench.get(fid)
        })

    return table