import os, mysql.connector, json
from decimal import Decimal
from dotenv import load_dotenv

# Carica le variabili d'ambiente dal file .env
load_dotenv()

cfg = {
    "host": os.environ.get("MYSQL_HOST"),
    "port": int(os.environ.get("MYSQL_PORT", 3306)),
    "user": os.environ.get("MYSQL_USER"),
    "password": os.environ.get("MYSQL_PASSWORD"),
    "database": os.environ.get("MYSQL_DATABASE")
}

def conn(): return mysql.connector.connect(**cfg)

def fetch_case_values(answer_id):
    with conn() as connection:
        cursor = connection.cursor()
        q = """SELECT f.id, f.label, f.unit, f.input_type, v.value_raw
               FROM business_case_values v
               JOIN business_case_factors f ON f.id=v.factor_id
               WHERE v.id_survey_item=%s"""
        cursor.execute(q, (answer_id,))
        rows = cursor.fetchall()
    return rows                              # list[tuple]

def insert_result(answer_id, kpi_json, narrative, pptx_url, chart_url, html_table, html_narrative):
    with conn() as connection:
        cursor = connection.cursor()
        cursor.execute("""REPLACE INTO business_case_results
                     (answer_id,kpi_json,narrative,pptx_url, chart_url, html_table, html_narrative)
                     VALUES (%s,%s,%s,%s, %s, %s, %s)""",
                  (answer_id, json.dumps(kpi_json), narrative, pptx_url, chart_url, html_table, html_narrative))