from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()

# ============= STYLES =============
titre = Font(bold=True, size=14, color="FFFFFF")
gras = Font(bold=True)
fill_titre = PatternFill("solid", fgColor="1F4E78")
fill_section = PatternFill("solid", fgColor="D9E1F2")
fill_net = PatternFill("solid", fgColor="C6EFCE")
centre = Alignment(horizontal="center", vertical="center")
bord = Border(*[Side(style="thin")]*4)

def style_cell(ws, ref, value=None, font=None, fill=None, align=None, border=True, fmt=None):
    c = ws[ref]
    if value is not None: c.value = value
    if font: c.font = font
    if fill: c.fill = fill
    if align: c.alignment = align
    if border: c.border = bord
    if fmt: c.number_format = fmt
    return c

# ============= ONGLET PARAMETRES =============
p = wb.active
p.title = "Parametres"
p.column_dimensions['A'].width = 28
p.column_dimensions['B'].width = 15
p.column_dimensions['C'].width = 40

style_cell(p, "A1", "PARAMETRES DE PAIE", titre, fill_titre)
p.merge_cells("A1:C1")

params = [
    ("Plafond_CNPS", 750000, "Plafond mensuel CNPS"),
    ("Taux_CNPS_sal", 0.042, "Cotisation salariale CNPS (4,2%)"),
    ("Taux_CNPS_pat", 0.1345, "Cotisation patronale (info)"),
    ("Taux_ancient", 0.02, "Taux prime ancienneté"),
    ("Abattement_20p", 0.20, "Abattement forfaitaire IRPP"),
    ("Plafond_abatt_mens", 41667, "Plafond mensuel abattement"),
    ("Transport_exo", 10000, "Transport exonéré"),
    ("Seuil_T1", 166667, "0%"),
    ("Seuil_T2", 250000, "11%"),
    ("Seuil_T3", 416667, "16,5%"),
    ("Seuil_T4", 833333, "23% / au-delà 30%"),
]
row = 3
for nom, val, com in params:
    style_cell(p, f"A{row}", nom, gras)
    style_cell(p, f"B{row}", val, fmt="#,##0" if val > 1 else "0.00%")
    style_cell(p, f"C{row}", com)
    # Nom défini
    wb.defined_names.add(
        __import__("openpyxl").workbook.defined_name.DefinedName(nom, attr_text=f"Parametres!$B${row}")
    )
    row += 1

# ============= ONGLET PAIE =============
ws = wb.create_sheet("Paie")
for col, w in zip("ABCDE", [6, 32, 12, 10, 15]):
    ws.column_dimensions[col].width = w

# En-tête
style_cell(ws, "A1", "BULLETIN DE PAIE", titre, fill_titre, centre)
ws.merge_cells("A1:E1")

infos = [
    ("A2","Entreprise :","B2","INDUSTRIE SARL","D2","Période :","E2","Janvier 2025"),
    ("A3","N° CNPS Emp. :","B3","XXXXX","D3","Catégorie :","E3","Ouvrier"),
    ("A4","Salarié :","B4","NOM Prénom","D4","Matricule :","E4","MAT-001"),
    ("A5","Emploi :","B5","Opérateur","D5","Date embauche :","E5","01/01/2020"),
]
for a,la,b,lb,d,ld,e,le in infos:
    style_cell(ws, a, la, gras); style_cell(ws, b, lb)
    style_cell(ws, d, ld, gras); style_cell(ws, e, le)

# --- GAINS ---
style_cell(ws, "A7", "GAINS", gras, fill_section)
for col in "BCDE": style_cell(ws, f"{col}7", "", fill=fill_section)
headers = ["Code","Libellé","Base","Taux","Montant"]
for i,h in enumerate(headers):
    style_cell(ws, f"{get_column_letter(i+1)}8", h, gras, fill_section, centre)

gains = [
    ("100","Salaire de base",1,300000,"=C9*D9"),
    ("110","Prime d'ancienneté","","=Taux_ancient","=E9*Taux_ancient"),
    ("120","Prime de rendement","","", 25000),
    ("130","Heures supplémentaires (130%)",10,1500,"=C12*D12*1.3"),
    ("140","Indemnité de transport","","","=Transport_exo"),
    ("150","Indemnité de logement","","", 30000),
]
r = 9
for code,lib,base,taux,mont in gains:
    style_cell(ws, f"A{r}", code, centre)
    style_cell(ws, f"B{r}", lib)
    style_cell(ws, f"C{r}", base, align=centre)
    style_cell(ws, f"D{r}", taux, align=centre)
    style_cell(ws, f"E{r}", mont, fmt="#,##0")
    r += 1

# Total brut
tb = r
style_cell(ws, f"B{tb}", "TOTAL BRUT", gras, fill_section)
style_cell(ws, f"E{tb}", f"=SOMME(E9:E{r-1})", gras, fill_section, fmt="#,##0")

# --- BASES & RETENUES ---
r = tb + 2
style_cell(ws, f"A{r}", "RETENUES", gras, fill_section)
for col in "BCDE": style_cell(ws, f"{col}{r}", "", fill=fill_section)
r += 1

base_cnps = r
style_cell(ws, f"B{r}", "Base CNPS")
style_cell(ws, f"E{r}", f"=MIN(E{tb}-Transport_exo;Plafond_CNPS)", fmt="#,##0")
r += 1

base_irpp = r
style_cell(ws, f"B{r}", "Base IRPP brute")
style_cell(ws, f"E{r}", f"=E{tb}-Transport_exo", fmt="#,##0")
r += 1

cnps = r
style_cell(ws, f"B{r}", "Cotisation CNPS salariale")
style_cell(ws, f"E{r}", f"=E{base_cnps}*Taux_CNPS_sal", fmt="#,##0")
r += 1

nri = r
style_cell(ws, f"B{r}", "Revenu net imposable")
style_cell(ws, f"E{r}", f"=MAX(0;E{base_irpp}-E{cnps}-MIN(E{base_irpp}*Abattement_20p;Plafond_abatt_mens))", fmt="#,##0")
r += 1

irpp = r
style_cell(ws, f"B{r}", "IRPP")
formule_irpp = (
    f"=ARRONDI(SI(E{nri}<=Seuil_T1;0;"
    f"SI(E{nri}<=Seuil_T2;(E{nri}-Seuil_T1)*0.11;"
    f"SI(E{nri}<=Seuil_T3;(Seuil_T2-Seuil_T1)*0.11+(E{nri}-Seuil_T2)*0.165;"
    f"SI(E{nri}<=Seuil_T4;(Seuil_T2-Seuil_T1)*0.11+(Seuil_T3-Seuil_T2)*0.165+(E{nri}-Seuil_T3)*0.23;"
    f"(Seuil_T2-Seuil_T1)*0.11+(Seuil_T3-Seuil_T2)*0.165+(Seuil_T4-Seuil_T3)*0.23+(E{nri}-Seuil_T4)*0.30"
    f")))) ;0)"
)
style_cell(ws, f"E{r}", formule_irpp, fmt="#,##0")
r += 1

avance = r
style_cell(ws, f"B{r}", "Avance sur salaire")
style_cell(ws, f"E{r}", 0, fmt="#,##0")
r += 1

tot_ret = r
style_cell(ws, f"B{r}", "TOTAL RETENUES", gras, fill_section)
style_cell(ws, f"E{r}", f"=E{cnps}+E{irpp}+E{avance}", gras, fill_section, fmt="#,##0")
r += 2

# --- NET A PAYER ---
net = r
style_cell(ws, f"B{r}", "NET À PAYER", Font(bold=True,size=12), fill_net)
style_cell(ws, f"E{r}", f"=E{tb}-E{tot_ret}", Font(bold=True,size=12), fill_net, fmt="#,##0")
r += 2

# --- COTISATION PATRONALE (info) ---
style_cell(ws, f"A{r}", "CHARGES PATRONALES (information)", gras, fill_section)
for col in "BCDE": style_cell(ws, f"{col}{r}", "", fill=fill_section)
r += 1
style_cell(ws, f"B{r}", "CNPS part patronale")
style_cell(ws, f"E{r}", f"=E{base_cnps}*Taux_CNPS_pat", fmt="#,##0")

wb.save("Bulletin_Paie_Cameroun.xlsx")
print("✅ Fichier 'Bulletin_Paie_Cameroun.xlsx' généré avec succès !")('Hello world!')