import re, csv, sys, pptx, fitz                   # fitz=PyMuPDF
import mysql.connector, re, os
from docx import Document
import pandas as pd
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 load_factor_map():
    
    with mysql.connector.connect(**cfg) as cnx:
        cur = cnx.cursor()
        cur.execute("""
            SELECT id, label, COALESCE(regex_pattern, ''), COALESCE(synonyms, '')
            FROM business_case_factors
            WHERE is_active = 1
        """)
        rows = cur.fetchall()

    factor_map = {}
    for fid, label, rex, syn in rows:
        patterns = [rex] if rex else []
        # usa i sinonimi o la label stessa
        patterns += [re.escape(s.strip()) for s in syn.split(',') if s.strip()]
        if not patterns:
            patterns.append(re.escape(label))

        # compila una regex unica “(produttività|productivity|output)”
        pattern = r'(' + r'|'.join(patterns) + r')'
        factor_map[pattern] = fid
    return factor_map



def guess_factor(text):
    for pat, fid in FACTOR_MAP.items():
        if re.search(pat, text, re.I):
            return fid
    return None

def grab_numbers(text):
    # Estrae numeri con o senza %/€
    m = re.search(r'(-?\d+(?:\.\d+)?)(?:\s?[%€])?', text)
    return float(m.group(1)) if m else None


def parse_pptx(path, sector_hint):
    prs = pptx.Presentation(path)
    rows = []
    for slide in prs.slides:
        for shape in slide.shapes:
            if not shape.has_text_frame: continue
            txt = shape.text_frame.text
            fid = guess_factor(txt)
            val = grab_numbers(txt)
            if fid and val is not None:
                rows.append((sector_hint, fid, val))
    return rows

def parse_pdf(path, sector_hint):
    doc = fitz.open(path)
    rows = []
    for page in doc:
        for line in page.get_text().splitlines():
            fid = guess_factor(line)
            val = grab_numbers(line)
            if fid and val is not None:
                rows.append((sector_hint, fid, val))
    return rows

def parse_docx(path, sector_hint):
    """
    Estrae coppie (sector, factor_id, best_value) da un file Word.
    Funziona sia su paragrafi plain-text sia su tabelle.
    """
    doc = Document(path)
    rows = []

    # 1️⃣ Paragrafi
    for par in doc.paragraphs:
        txt = par.text.strip()
        if not txt:
            continue
        fid  = guess_factor(txt)
        val  = grab_numbers(txt)
        if fid and val is not None:
            rows.append((sector_hint, fid, val))
    

    # 2️⃣ Tabelle (molti case-study usano righe tabellari)
    for table in doc.tables:
        for row in table.rows:
            txt_row = " ".join(cell.text.strip() for cell in row.cells)
            fid  = guess_factor(txt_row)
            val  = grab_numbers(txt_row)
            if fid and val is not None:
                rows.append((sector_hint, fid, val))

    return rows

FACTOR_MAP = load_factor_map()          # <-- rimpiazza il vecchio dict

def load_into_db(csvfile):
    df = pd.read_csv(csvfile)
    # drop rows where best_value NaN
    df = df[df[['sector', 'factor_id', 'best_value']].notna().all(axis=1)]

    
    with mysql.connector.connect(**cfg) as c:
        cur = c.cursor()
        for row in df.itertuples(index=False):
            # ensure Python None for SQL NULL if needed
            val = None if pd.isna(row.best_value) else row.best_value
            cur.execute("""
              INSERT INTO case_study_kpi (sector, factor_id, best_value)
              VALUES (%s,%s,%s)
              ON DUPLICATE KEY UPDATE best_value = VALUES(best_value)
            """, (row.sector, row.factor_id, val))
    print("📥 Import terminato")


def main():
    in_file, out_csv, sector = sys.argv[1:4]
    rows = []

    if in_file.endswith(".pptx"):
        rows = list(parse_pptx(in_file, sector))
    elif in_file.endswith(".pdf"):
        rows = list(parse_pdf(in_file, sector))
    elif in_file.endswith(".docx"):
        rows = list(parse_docx(in_file, sector))

    with open(out_csv, "w", newline='') as f:
        w = csv.writer(f)
        w.writerow(["sector", "factor_id", "best_value"])
        w.writerows(rows)

    load_into_db(out_csv)

if __name__ == "__main__":
    main()