#!/usr/bin/env python3

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.worksheet.datavalidation import DataValidation
from datetime import datetime
import os

# =============================================
# PARTE 1: AGGIORNA SCORECARD CON COMMENTO LIBERO
# =============================================
print("🦊 PARTE 1: Aggiorno scorecard con commento libero...")

filepath = '/home/oem/.hermes/cache/documents/doc_c664274b11f3_Talent Managers.xlsx'

# Leggi il file con header alla riga 1
df = pd.read_excel(filepath, sheet_name='Managers', header=1)

# Pulisci righe vuote
df = df.dropna(how='all')

# Rinomina colonne
if len(df.columns) == 9:
    df.columns = ['Index', 'Societa', 'Reparto', 'Ruolo', 'Collaboratore', 'Attivita', 'Sesso', 'Data_Nascita', 'Data_Inizio']
else:
    df.columns = ['Societa', 'Reparto', 'Ruolo', 'Collaboratore', 'Attivita', 'Sesso', 'Data_Nascita', 'Data_Inizio']

# Calcola campi derivati
today = pd.Timestamp.now()
df['Data_Nascita'] = pd.to_datetime(df['Data_Nascita'])
df['Data_Inizio'] = pd.to_datetime(df['Data_Inizio'])
df['Eta'] = ((today - df['Data_Nascita']).dt.days / 365.25).round(1)
df['Anzianita'] = ((today - df['Data_Inizio']).dt.days / 365.25).round(1)

df['Fascia_Eta'] = df['Eta'].apply(lambda x: 
    '< 25' if x < 25 else '25-35' if x < 35 else '35-45' if x < 45 else '45-55' if x < 55 else '55+')

df['Fascia_Anzianita'] = df['Anzianita'].apply(lambda x:
    '< 1 anno' if x < 1 else '1-3 anni' if x < 3 else '3-5 anni' if x < 5 else '5-10 anni' if x < 10 else '10-20 anni' if x < 20 else '20+')

# Criteri
criteri = [
    {'nome': 'Reperibilità Mercato', 'peso': 3, 'key': 'REPERIBILITÀ'},
    {'nome': 'Tempo Onboarding', 'peso': 2, 'key': 'ONBOARDING'},
    {'nome': 'Know-How Esclusivo', 'peso': 2, 'key': 'KNOW-HOW'},
    {'nome': 'Impatto su Business', 'peso': 4, 'key': 'IMPATTO'},
    {'nome': 'Rete di Relazioni', 'peso': 3, 'key': 'RELAZIONI'},
    {'nome': 'Competenze Tecniche', 'peso': 3, 'key': 'COMPETENZE TEC'},
    {'nome': 'Skills Comunicative', 'peso': 2, 'key': 'COMUNICAZIONE'},
    {'nome': 'Skills Leadership', 'peso': 3, 'key': 'LEADERSHIP'}
]

peso_max = sum(c['peso'] * 4 for c in criteri)

# Costruisci Excel
wb = Workbook()
ws = wb.active
ws.title = "Scorecard Cippà"

# HEADER MAIN
header_main = [
    'Società', 'Reparto', 'Ruolo', 'Collaboratore', 'Attività', 
    'Sesso', 'Età', 'Anzianità', 'Fascia Età', 'STATO (J)'
]
for c in criteri:
    header_main.append(f"{c['key']} (P{c['peso']})")
header_main.extend(['PUNTEGGIO (0-100)', 'LIVELLO RISCHIO', 'COMMENTO LIBERO'])

# Stili
header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF", size=11)
giallo = PatternFill(start_color="FFF8E1", end_color="FFF8E1", fill_type="solid")
arancione = PatternFill(start_color="FCE4D6", end_color="FCE4D6", fill_type="solid")
azzurro = PatternFill(start_color="E3F2FD", end_color="E3F2FD", fill_type="solid")
verde_header = PatternFill(start_color="2E7D32", end_color="2E7D32", fill_type="solid")
bordo = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)

# Scrivi header (2 righe per avere spazio)
for col, header in enumerate(header_main, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    cell.border = bordo
    # Colore speciale per COMMENTO LIBERO
    if header == 'COMMENTO LIBERO':
        cell.fill = verde_header
    else:
        cell.fill = header_fill

# Nota IMPORTANTE per STATO (riga sotto header)
ws.cell(row=1, column=10, value="STATO (J)\nValutato / Non conosco").font = header_font
ws.cell(row=1, column=10).fill = arancione
ws.cell(row=1, column=10).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

# Nota per COMMENTO LIBERO
ws.cell(row=1, column=21, value="COMMENTO LIBERO\nScrivi qui note, osservazioni, dubbi").font = header_font
ws.cell(row=1, column=21).fill = verde_header
ws.cell(row=1, column=21).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

# REGOLA per riga 2: NASCONDO per ora le istruzioni
# Righe dati
for idx, row in df.iterrows():
    excel_row = idx + 2
    
    # Dati anagrafici (A-I)
    data_values = [
        row['Societa'], row['Reparto'], row['Ruolo'], row['Collaboratore'], 
        row['Attivita'], row['Sesso'], row['Eta'], row['Anzianita'], row['Fascia_Eta']
    ]
    
    for col, value in enumerate(data_values, 1):
        cell = ws.cell(row=excel_row, column=col, value=value)
        cell.border = bordo
        if excel_row % 2 == 0:
            cell.fill = PatternFill(start_color="F2F2F2", end_color="F2F2F2", fill_type="solid")
    
    # STATO (col 10) - arancione
    stato_cell = ws.cell(row=excel_row, column=10, value="")
    stato_cell.fill = arancione
    stato_cell.border = bordo
    
    # Criteri (col 11-18) - gialle
    for c_idx in range(8):
        cell = ws.cell(row=excel_row, column=11 + c_idx, value="")
        cell.fill = giallo
        cell.border = bordo
    
    # PUNTEGGIO (S = col 19)
    weights = [c['peso'] for c in criteri]
    formula = f'=IF(J{excel_row}="Non conosco","N/D",IF(COUNTA(K{excel_row}:R{excel_row})<8,"",ROUND(((K{excel_row}*{weights[0]}+L{excel_row}*{weights[1]}+M{excel_row}*{weights[2]}+N{excel_row}*{weights[3]}+O{excel_row}*{weights[4]}+P{excel_row}*{weights[5]}+Q{excel_row}*{weights[6]}+R{excel_row}*{weights[7]})/{peso_max})*100,0)))'
    cell = ws.cell(row=excel_row, column=19, value=f"={formula}")
    cell.border = bordo
    
    # LIVELLO RISCHIO (T = col 20)
    livello_formula = f'=IF(S{excel_row}="N/D","N/D",IF(S{excel_row}="","",IF(S{excel_row}>=75,"🔴 CRITICO",IF(S{excel_row}>=50,"🟠 ALTO",IF(S{excel_row}>=25,"🟡 MEDIO","🟢 BASSO")))))'
    cell = ws.cell(row=excel_row, column=20, value=f"={livello_formula}")
    cell.border = bordo
    
    # COMMENTO LIBERO (U = col 21) - azzurro e largo
    cell = ws.cell(row=excel_row, column=21, value="")
    cell.fill = azzurro
    cell.border = bordo
    cell.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)

# Data Validation
stato_dv = DataValidation(type="list", formula1='"Valutato,Non conosco"')
stato_dv.add(f'J2:J{len(df)+1}')
ws.add_data_validation(stato_dv)

criteri_dv = DataValidation(type="list", formula1='"1,2,3,4"')
criteri_dv.add(f'K2:R{len(df)+1}')
ws.add_data_validation(criteri_dv)

# STATISTICHE
sr = len(df) + 4
ws.cell(row=sr, column=1, value="📊 STATISTICHE").font = Font(bold=True, size=13)

stats = [
    ["Totale collaboratori:", len(df)],
    ["Valutati:", f'=COUNTIF(J2:J{len(df)+1},"Valutato")'],
    ["Non conosciuti:", f'=COUNTIF(J2:J{len(df)+1},"Non conosco")'],
    ["Punteggio medio:", f'=AVERAGEIF(J2:J{len(df)+1},"Valutato",S2:S{len(df)+1})'],
    ["Rischi critici (≥75):", f'=COUNTIFS(J2:J{len(df)+1},"Valutato",S2:S{len(df)+1},">=75")'],
    ["Rischi alti (50-74):", f'=COUNTIFS(J2:J{len(df)+1},"Valutato",S2:S{len(df)+1},">=50",S2:S{len(df)+1},"<75")'],
    ["Rischi medi (25-49):", f'=COUNTIFS(J2:J{len(df)+1},"Valutato",S2:S{len(df)+1},">=25",S2:S{len(df)+1},"<50")'],
    ["Rischi bassi (0-24):", f'=COUNTIFS(J2:J{len(df)+1},"Valutato",S2:S{len(df)+1},"<25")'],
]
for i, (label, val) in enumerate(stats):
    ws.cell(row=sr+1+i, column=1, value=label).font = Font(bold=True)
    ws.cell(row=sr+1+i, column=2, value=val)

# LEGENDA
lr = sr + len(stats) + 3
ws.cell(row=lr, column=1, value="📋 LEGENDA RISCHIO").font = Font(bold=True, size=13)
legenda = [
    ["🔴 CRITICO (75-100):", "Presidio totale — retention urgente"],
    ["🟠 ALTO (50-74):", "Monitoraggio attivo — azioni preventive ogni 1-2 mesi"],
    ["🟡 MEDIO (25-49):", "Controllo standard — check trimestrale"],
    ["🟢 BASSO (0-24):", "Gestione ordinaria — review annuale"],
]
for i, (r, a) in enumerate(legenda):
    ws.cell(row=lr+1+i, column=1, value=r).font = Font(bold=True)
    ws.cell(row=lr+1+i, column=2, value=a)

# COMMENTI GENERALI
cg = lr + len(legenda) + 3
ws.cell(row=cg, column=1, value="💬 COMMENTI & OSSERVAZIONI GENERALI").font = Font(bold=True, size=13)
for i in range(5):
    cell = ws.cell(row=cg + 1 + i, column=1, value="")
    cell.fill = PatternFill(start_color="E8F5E9", end_color="E8F5E9", fill_type="solid")
    cell.border = bordo
    ws.merge_cells(start_row=cg+1+i, start_column=1, end_row=cg+1+i, end_column=4)

# Formattazione colonne
col_widths = [10, 12, 8, 22, 22, 6, 7, 8, 10, 16, 14, 12, 12, 12, 14, 16, 14, 14, 14, 16, 30]
for i, w in enumerate(col_widths, 1):
    col_letter = ws.cell(row=1, column=i).column_letter
    ws.column_dimensions[col_letter].width = w

# Freeze panes
ws.freeze_panes = 'A2'

# Salva
output_path = '/home/oem/Scorecard_TalentRisk_Reale_Cippa.xlsx'
wb.save(output_path)

print(f"✅ Scorecard aggiornata con commento libero: {output_path}")

# =============================================
# PARTE 2: GUIDA PER DUMMIES DOCX
# =============================================
print("\n🦊 PARTE 2: Genero guida per dummies DOCX...")

from docx import Document
from docx.shared import Inches, Pt, Cm, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.oxml.ns import qn

doc = Document()

# Imposta margini stretti
for section in doc.sections:
    section.orientation = 1  # landscape
    section.page_height = Cm(21)
    section.page_width = Cm(29.7)
    section.top_margin = Cm(1.5)
    section.bottom_margin = Cm(1.5)
    section.left_margin = Cm(1.5)
    section.right_margin = Cm(1.5)

# TITOLO
title = doc.add_heading('Guida Rapida — Scorecard Talent Risk Cippà Trasporti', level=0)
title.alignment = WD_ALIGN_PARAGRAPH.CENTER

doc.add_paragraph('')
subtitle = doc.add_paragraph()
subtitle.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = subtitle.add_run('📋 Come compilare la valutazione dei collaboratori in 5 minuti')
run.font.size = Pt(14)
run.font.italic = True

doc.add_paragraph('')

# INTRODUZIONE
intro = doc.add_paragraph()
intro.add_run('Ciao! ').bold = True
intro.add_run('Hai davanti la scorecard dei collaboratori del tuo team. Serve a capire chi rischiamo di perdere e chi è critico per l\'azienda.')

doc.add_paragraph('')
step_intro = doc.add_paragraph()
step_intro.add_run('⏱ Tempo stimato: ').bold = True
step_intro.add_run('30-60 secondi a persona. Non pensarci troppo, vai di pancia.')

doc.add_paragraph('')

# === PASSO 1: COSA FARE ===
h1 = doc.add_heading('📌 PASSO 1: Compila la colonna STATO (J)', level=1)
p = doc.add_paragraph()
p.add_run('Per OGNI collaboratore, scegli dalla tendina (dropdown):')
doc.add_paragraph('')
states = [
    ('✅ "Valutato"', ' — Se conosci abbastanza la persona per valutarla'),
    ('❌ "Non conosco"', ' — Se non la conosci abbastanza. Meglio N/D che un voto a caso!'),
]
for label, desc in states:
    p2 = doc.add_paragraph()
    p2.add_run('  • ').font.size = Pt(11)
    r = p2.add_run(label)
    r.bold = True
    r.font.size = Pt(11)
    r2 = p2.add_run(desc)
    r2.font.size = Pt(11)

doc.add_paragraph('')
p3 = doc.add_paragraph()
p3.add_run('⚠️ IMPORTANTE: ').bold = True
p3.add_run('Se scegli "Non conosco", la riga mostrerà automaticamente N/D. Nessun voto forzato!')

doc.add_paragraph('')

# === PASSO 2: CRITERI ===
h2 = doc.add_heading('📌 PASSO 2: Valuta gli 8 criteri (colonne K-R)', level=1)
p = doc.add_paragraph()
p.add_run('Per ogni criterio, dai un voto da 1 a 4 usando il menu a tendina:')
doc.add_paragraph('')

# Tabella criteri
table = doc.add_table(rows=9, cols=4)
table.style = 'Light Grid Accent 1'
table.alignment = WD_TABLE_ALIGNMENT.CENTER

headers = ['#', 'Criterio', 'Peso', 'Cosa significa']
for i, h in enumerate(headers):
    cell = table.rows[0].cells[i]
    cell.text = h
    for para in cell.paragraphs:
        for run in para.runs:
            run.bold = True
            run.font.size = Pt(10)

crit_cells = [
    ['1', 'Reperibilità Mercato', '3', 'Quanto è difficile trovare una persona come lei/lui sul mercato?'],
    ['2', 'Tempo Onboarding', '2', 'Quanto tempo serve per formare un/una sostituto/a?'],
    ['3', 'Know-How Esclusivo', '2', 'Ha competenze uniche che nessun altro ha in azienda?'],
    ['4', 'Impatto su Business', '4', 'Che danno economico subiamo se se ne va? (SEMPRE ALTO)'],
    ['5', 'Rete di Relazioni', '3', 'Porta via clienti o rapporti importanti con sé?'],
    ['6', 'Competenze Tecniche', '3', 'Ha skill specialistiche difficili da replicare?'],
    ['7', 'Skills Comunicative', '2', 'Quanto è bravo/a a comunicare con clienti/colleghi?'],
    ['8', 'Skills Leadership', '3', 'Guida decisioni e persone? È un punto di riferimento?'],
]

for i, row_data in enumerate(crit_cells):
    for j, val in enumerate(row_data):
        cell = table.rows[i+1].cells[j]
        cell.text = val
        for para in cell.paragraphs:
            for run in para.runs:
                run.font.size = Pt(9)

doc.add_paragraph('')

# Tabella scala voti
doc.add_paragraph('Votazione (1-4):')
voti_table = doc.add_table(rows=5, cols=2)
voti_table.style = 'Light Grid Accent 1'

voti_headers = ['Voto', 'Cosa significa']
for i, h in enumerate(voti_headers):
    cell = voti_table.rows[0].cells[i]
    cell.text = h
    for para in cell.paragraphs:
        for run in para.runs:
            run.bold = True
            run.font.size = Pt(10)

voti_data = [
    ['1 - Basso', 'Non critico, facilmente sostituibile'],
    ['2 - Medio-Basso', 'Ha valore ma gestibile'],
    ['3 - Medio-Alto', 'Importante, faremmo fatica senza'],
    ['4 - Alto', 'Critico, insostituibile nel breve termine'],
]
for i, (voto, desc) in enumerate(voti_data):
    voti_table.rows[i+1].cells[0].text = voto
    voti_table.rows[i+1].cells[1].text = desc
    for para in voti_table.rows[i+1].cells[0].paragraphs:
        for run in para.runs:
            run.font.size = Pt(9)

doc.add_paragraph('')

# === PASSO 3: COMMENTO LIBERO ===
h3 = doc.add_heading('📌 PASSO 3: Commento libero (colonna U)', level=1)
p = doc.add_paragraph()
p.add_run('Nell\'ultima colonna (U) puoi scrivere ')
r = p.add_run('qualsiasi commento, dubbio, o osservazione')
r.bold = True
p.add_run('. Esempi:')
doc.add_paragraph('')
comments = [
    '💬 "Vorrei tenerlo ma so che sta cercando altro"',
    '💬 "È in burnout, serve intervenire"',
    '💬 "Ha appena chiesto un aumento, monitorare"',
    '💬 "È il nostro miglior venditore, non possiamo perderlo"',
    '💬 "Non conosco bene questa persona"',
    '💬 "Potenziale di crescita, va sviluppato"',
]
for c in comments:
    p = doc.add_paragraph(c)
    p.paragraph_format.space_before = Pt(2)
    p.paragraph_format.space_after = Pt(2)

doc.add_paragraph('')

# === PASSO 4: COSA SUCCEDE DOPO ===
h4 = doc.add_heading('📋 Cosa succede dopo?', level=1)

stages = [
    '📊 Il foglio di calcolo calcola automaticamente il punteggio (0-100)',
    '🎯 Il sistema assegna il livello di rischio: 🟢 BASSO 🟡 MEDIO 🟠 ALTO 🔴 CRITICO',
    '📈 Riceverai un report con la situazione del tuo team',
    '🔄 Rivedremo la valutazione tra 3-6 mesi per vedere i cambiamenti',
]
for s in stages:
    p = doc.add_paragraph(s, style='List Bullet')

doc.add_paragraph('')

# === LEGENDA SOLLIEVI ===
h5 = doc.add_heading('🎯 Matrice di Azione Rapida', level=1)

action_table = doc.add_table(rows=5, cols=3)
action_table.style = 'Light Grid Accent 1'

action_headers = ['Rischio', 'Range', 'Cosa fare']
for i, h in enumerate(action_headers):
    cell = action_table.rows[0].cells[i]
    cell.text = h
    for para in cell.paragraphs:
        for run in para.runs:
            run.bold = True
            run.font.size = Pt(10)

azioni = [
    ['🔴 CRITICO (75-100)', 'Urgente', '📞 Retention urgente — colloquio, aumento, tutto quello che serve'],
    ['🟠 ALTO (50-74)', 'Attenzione', '👁 Monitoraggio attivo — check ogni 1-2 mesi, azioni preventive'],
    ['🟡 MEDIO (25-49)', 'Standard', '📋 Controllo trimestrale — attenzione a segnali di cambiamento'],
    ['🟢 BASSO (0-24)', 'OK', '✅ Gestione ordinaria — review annuale, business as usual'],
]
for i, (r, azione, desc) in enumerate(azioni):
    action_table.rows[i+1].cells[0].text = r
    action_table.rows[i+1].cells[1].text = azione
    action_table.rows[i+1].cells[2].text = desc
    for para in action_table.rows[i+1].cells[0].paragraphs:
        for run in para.runs:
            run.font.size = Pt(9)
    for para in action_table.rows[i+1].cells[2].paragraphs:
        for run in para.runs:
            run.font.size = Pt(9)

doc.add_paragraph('')

# === ESEMPIO PRATICO ===
h6 = doc.add_heading('📝 Esempio pratico', level=1)

p = doc.add_paragraph()
p.add_run('Immagina Maria, impiegata in Customer Service da 13 anni:').italic = True

doc.add_paragraph('')

example_table = doc.add_table(rows=2, cols=9)
example_table.style = 'Light Grid Accent 1'
example_headers = ['Reperib.', 'Onboard.', 'Know-How', 'Impatto', 'Relazioni', 'Comp. Tec', 'Comunic.', 'Leader.', 'Risultato']
for i, h in enumerate(example_headers):
    cell = example_table.rows[0].cells[i]
    cell.text = h
    for para in cell.paragraphs:
        for run in para.runs:
            run.bold = True
            run.font.size = Pt(9)

example_vals = ['3', '2', '4', '3', '3', '2', '3', '2', '🟠 ALTO']
for i, v in enumerate(example_vals):
    example_table.rows[1].cells[i].text = v
    for para in example_table.rows[1].cells[i].paragraphs:
        for run in para.runs:
            run.font.size = Pt(9)
            run.bold = True

doc.add_paragraph('')
p = doc.add_paragraph()
p.add_run('Maria ha un Know-How unico (13 anni di esperienza) e buone relazioni. ')
p.add_run('➡ Il sistema calcola: punteggio 58 = ALTO. Attenzione!').bold = True

doc.add_paragraph('')

# === TIPS FINALI ===
h7 = doc.add_heading('💡 Consigli finali', level=1)

tips = [
    'Non pensarci troppo: 30-60 secondi a persona',
    'Il 4 è per persone VERAMENTE insostituibili',
    'Se non conosci la persona → Non conosco (N/D) — nessun problema!',
    'Il commento libero è IMPORTANTE: scrivi dubbi, note, sensazioni',
    'Non esiste la valutazione perfetta — meglio una valutazione imperfetta che nessuna',
]
for tip in tips:
    p = doc.add_paragraph(tip, style='List Bullet')

doc.add_paragraph('')
p_final = doc.add_paragraph()
p_final.alignment = WD_ALIGN_PARAGRAPH.CENTER
r = p_final.add_run('🦊 Grazie per il tuo contributo! Ogni valutazione aiuta a rendere Cippà più forte.')
r.bold = True
r.font.size = Pt(13)

# Salva DOCX
docx_path = '/home/oem/Guida_Dummies_Scorecard_Cippa.docx'
doc.save(docx_path)
print(f"✅ Guida DOCX generata: {docx_path}")

print("\n🎉 COMPLETATO! Scorecard + Guida pronte!")
print(f"📄 Scorecard: {output_path}")
print(f"📄 Guida: {docx_path}")