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

wb = openpyxl.Workbook()

# ============================================================
# STYLES
# ============================================================
header_font = Font(name='Calibri', bold=True, size=12, color='FFFFFF')
title_font = Font(name='Calibri', bold=True, size=14, color='1F4E79')
subtitle_font = Font(name='Calibri', bold=True, size=11, color='1F4E79')
bold_font = Font(name='Calibri', bold=True, size=10)
normal_font = Font(name='Calibri', size=10)
net_font = Font(name='Calibri', bold=True, size=13, color='FFFFFF')
section_font = Font(name='Calibri', bold=True, size=11, color='FFFFFF')

dark_blue_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
light_blue_fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid')
green_fill = PatternFill(start_color='2E7D32', end_color='2E7D32', fill_type='solid')
orange_fill = PatternFill(start_color='E65100', end_color='E65100', fill_type='solid')
light_gray_fill = PatternFill(start_color='F2F2F2', end_color='F2F2F2', fill_type='solid')
yellow_fill = PatternFill(start_color='FFF9C4', end_color='FFF9C4', fill_type='solid')
white_fill = PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid')

thin_border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)
bottom_thick = Border(bottom=Side(style='medium'))
double_bottom = Border(bottom=Side(style='double'))

center_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
left_align = Alignment(horizontal='left', vertical='center', wrap_text=True)
right_align = Alignment(horizontal='right', vertical='center')

fcfa_format = '#,##0'
pct_format = '0.00%'

def apply_border_range(ws, min_row, max_row, min_col, max_col):
    for r in range(min_row, max_row + 1):
        for c in range(min_col, max_col + 1):
            ws.cell(row=r, column=c).border = thin_border

# ============================================================
# ONGLET PARAMÈTRES
# ============================================================
ws_params = wb.active
ws_params.title = "Paramètres"
ws_params.sheet_properties.tabColor = "1F4E79"

# Largeurs colonnes
ws_params.column_dimensions['A'].width = 35
ws_params.column_dimensions['B'].width = 20
ws_params.column_dimensions['C'].width = 25
ws_params.column_dimensions['D'].width = 20

# Titre
ws_params.merge_cells('A1:D1')
c = ws_params['A1']
c.value = "⚙️ PARAMÈTRES LÉGAUX & TAUX - CAMEROUN"
c.font = Font(name='Calibri', bold=True, size=16, color='FFFFFF')
c.fill = dark_blue_fill
c.alignment = center_align
for col in range(1, 5):
    ws_params.cell(row=1, column=col).fill = dark_blue_fill

# --- Section CNPS ---
ws_params.merge_cells('A3:D3')
c = ws_params['A3']
c.value = "📋 COTISATIONS SOCIALES (CNPS)"
c.font = section_font
c.fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')
for col in range(1, 5):
    ws_params.cell(row=3, column=col).fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')

params_cnps = [
    ("Plafond mensuel CNPS", 750000, "B5", "Plafond_CNPS"),
    ("Taux salarial CNPS (Vieillesse + AF)", 0.042, "B6", "Taux_CNPS_sal"),
    ("Taux patronal CNPS (Prestations familiales)", 0.07, "B7", "Taux_CNPS_pf"),
    ("Taux patronal CNPS (Pensions)", 0.042, "B8", "Taux_CNPS_pen"),
    ("Taux patronal CNPS (Accident du travail)", 0.0175, "B9", "Taux_CNPS_at"),
    ("Taux patronal CNPS (total)", 0.1295, "B10", "Taux_CNPS_pat"),
]

for i, (label, val, cell_ref, name) in enumerate(params_cnps):
    row = 5 + i
    ws_params.cell(row=row, column=1, value=label).font = normal_font
    ws_params.cell(row=row, column=1).fill = light_gray_fill if i % 2 == 0 else white_fill
    cell = ws_params.cell(row=row, column=2, value=val)
    cell.font = bold_font
    cell.alignment = right_align
    cell.border = thin_border
    cell.fill = yellow_fill
    if isinstance(val, float) and val < 1:
        cell.number_format = pct_format
    else:
        cell.number_format = fcfa_format
    ws_params.cell(row=row, column=3, value=f"Nom: {name}").font = Font(name='Calibri', italic=True, size=9, color='888888')
    ws_params[cell_ref].name = name

# --- Section IRPP ---
ws_params.merge_cells('A12:D12')
c = ws_params['A12']
c.value = "📋 BARÈME IRPP (Impôt sur le Revenu des Personnes Physiques)"
c.font = section_font
c.fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')
for col in range(1, 5):
    ws_params.cell(row=12, column=col).fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')

# En-têtes barème
headers_irpp = ["Tranche", "Plancher mensuel", "Plafond mensuel", "Taux"]
for j, h in enumerate(headers_irpp, 1):
    c = ws_params.cell(row=13, column=j, value=h)
    c.font = bold_font
    c.fill = light_blue_fill
    c.alignment = center_align
    c.border = thin_border

bareme_irpp = [
    ("Tranche 1", 0, 166667, 0),
    ("Tranche 2", 166667, 250000, 0.11),
    ("Tranche 3", 250000, 416667, 0.165),
    ("Tranche 4", 416667, 833333, 0.23),
    ("Tranche 5", 833333, 999999999, 0.30),
]

for i, (tranche, bas, haut, taux) in enumerate(bareme_irpp):
    row = 14 + i
    ws_params.cell(row=row, column=1, value=tranche).font = normal_font
    ws_params.cell(row=row, column=1).fill = light_gray_fill if i % 2 == 0 else white_fill
    c2 = ws_params.cell(row=row, column=2, value=bas)
    c2.number_format = fcfa_format
    c2.alignment = right_align
    c2.border = thin_border
    c2.fill = yellow_fill
    c3 = ws_params.cell(row=row, column=3, value=haut if haut < 99999999 else "∞")
    c3.number_format = fcfa_format
    c3.alignment = right_align
    c3.border = thin_border
    c3.fill = yellow_fill
    c4 = ws_params.cell(row=row, column=4, value=taux)
    c4.number_format = pct_format
    c4.alignment = right_align
    c4.border = thin_border
    c4.fill = yellow_fill

# Noms pour le barème
ws_params['B14'].name = 'IRPP_T1_bas'
ws_params['C14'].name = 'IRPP_T1_haut'
ws_params['D14'].name = 'IRPP_T1_taux'
ws_params['B15'].name = 'IRPP_T2_bas'
ws_params['C15'].name = 'IRPP_T2_haut'
ws_params['D15'].name = 'IRPP_T2_taux'
ws_params['B16'].name = 'IRPP_T3_bas'
ws_params['C16'].name = 'IRPP_T3_haut'
ws_params['D16'].name = 'IRPP_T3_taux'
ws_params['B17'].name = 'IRPP_T4_bas'
ws_params['C17'].name = 'IRPP_T4_haut'
ws_params['D17'].name = 'IRPP_T4_taux'
ws_params['B18'].name = 'IRPP_T5_bas'
ws_params['D18'].name = 'IRPP_T5_taux'

# --- Section Abattements ---
ws_params.merge_cells('A20:D20')
c = ws_params['A20']
c.value = "📋 ABATTEMENTS & EXONÉRATIONS"
c.font = section_font
c.fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')
for col in range(1, 5):
    ws_params.cell(row=20, column=col).fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')

abattements = [
    ("Taux d'abattement forfaitaire IRPP", 0.20, "B22", "Taux_abattement", pct_format),
    ("Plafond annuel abattement", 500000, "B23", "Plafond_abat_annuel", fcfa_format),
    ("Plafond mensuel abattement (= annuel/12)", None, "B24", "Plafond_abat_mensuel", fcfa_format),
    ("Indemnité transport exonérée", 10000, "B25", "Transport_exo", fcfa_format),
]

for i, (label, val, cell_ref, name, fmt) in enumerate(abattements):
    row = 22 + i
    ws_params.cell(row=row, column=1, value=label).font = normal_font
    ws_params.cell(row=row, column=1).fill = light_gray_fill if i % 2 == 0 else white_fill
    cell = ws_params.cell(row=row, column=2)
    cell.font = bold_font
    cell.alignment = right_align
    cell.border = thin_border
    cell.fill = yellow_fill
    cell.number_format = fmt
    if val is not None:
        cell.value = val
    ws_params.cell(row=row, column=3, value=f"Nom: {name}").font = Font(name='Calibri', italic=True, size=9, color='888888')

# Formule pour plafond mensuel abattement
ws_params['B24'].value = '=B23/12'

ws_params['B22'].name = 'Taux_abattement'
ws_params['B23'].name = 'Plafond_abat_annuel'
ws_params['B24'].name = 'Plafond_abat_mensuel'
ws_params['B25'].name = 'Transport_exo'

# --- Section Taux supplémentaires ---
ws_params.merge_cells('A27:D27')
c = ws_params['A27']
c.value = "📋 AUTRES PARAMÈTRES"
c.font = section_font
c.fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')
for col in range(1, 5):
    ws_params.cell(row=27, column=col).fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')

autres = [
    ("Taux prime d'ancienneté", 0.02, "B29", "Taux_anciennete", pct_format),
    ("Majoration heures sup. (130%)", 1.30, "B30", "Majoration_HS", pct_format),
    ("Taux heures sup. nuit (150%)", 1.50, "B31", "Majoration_HS_nuit", pct_format),
    ("Taux heures sup. dimanche/jour férié (200%)", 2.00, "B32", "Majoration_HS_dim", pct_format),
]

for i, (label, val, cell_ref, name, fmt) in enumerate(autres):
    row = 29 + i
    ws_params.cell(row=row, column=1, value=label).font = normal_font
    ws_params.cell(row=row, column=1).fill = light_gray_fill if i % 2 == 0 else white_fill
    cell = ws_params.cell(row=row, column=2, value=val)
    cell.font = bold_font
    cell.alignment = right_align
    cell.border = thin_border
    cell.fill = yellow_fill
    cell.number_format = fmt
    ws_params.cell(row=row, column=3, value=f"Nom: {name}").font = Font(name='Calibri', italic=True, size=9, color='888888')
    ws_params[cell_ref].name = name

# ============================================================
# ONGLET PAIE
# ============================================================
ws = wb.create_sheet("Paie", 0)
ws.sheet_properties.tabColor = "2E7D32"

# Largeurs colonnes
ws.column_dimensions['A'].width = 5
ws.column_dimensions['B'].width = 35
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 18
ws.column_dimensions['F'].width = 18

# === EN-TÊTE ENTREPRISE ===
ws.merge_cells('A1:F1')
c = ws['A1']
c.value = "BULLETIN DE PAIE"
c.font = Font(name='Calibri', bold=True, size=18, color='FFFFFF')
c.fill = dark_blue_fill
c.alignment = center_align
for col in range(1, 7):
    ws.cell(row=1, column=col).fill = dark_blue_fill
ws.row_dimensions[1].height = 35

ws.merge_cells('A2:F2')
c = ws['A2']
c.value = "RÉPUBLIQUE DU CAMEROUN — Travail et Sécurité Sociale"
c.font = Font(name='Calibri', italic=True, size=10, color='FFFFFF')
c.fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')
c.alignment = center_align
for col in range(1, 7):
    ws.cell(row=2, column=col).fill = PatternFill(start_color='37474F', end_color='37474F', fill_type='solid')

# === INFORMATIONS ENTREPRISE ===
ws.merge_cells('A4:F4')
c = ws['A4']
c.value = "🏢 INFORMATIONS EMPLOYEUR"
c.font = section_font
c.fill = dark_blue_fill
for col in range(1, 7):
    ws.cell(row=4, column=col).fill = dark_blue_fill

info_entreprise = [
    ("Raison sociale :", "", "N° CNPS :", ""),
    ("Adresse :", "", "N° Employeur :", ""),
    ("Activité :", "Industrie / Manufacture", "Convention collective :", "Industrie au Cameroun"),
]

for i, (l1, v1, l2, v2) in enumerate(info_entreprise):
    row = 5 + i
    ws.cell(row=row, column=1, value=l1).font = bold_font
    ws.merge_cells(start_row=row, start_column=2, end_row=row, end_column=3)
    ws.cell(row=row, column=2, value=v1).font = normal_font
    ws.cell(row=row, column=2).border = Border(bottom=Side(style='dotted'))
    ws.cell(row=row, column=4, value=l2).font = bold_font
    ws.merge_cells(start_row=row, start_column=5, end_row=row, end_column=6)
    ws.cell(row=row, column=5, value=v2).font = normal_font
    ws.cell(row=row, column=5).border = Border(bottom=Side(style='dotted'))

# === INFORMATIONS SALARIÉ ===
ws.merge_cells('A9:F9')
c = ws['A9']
c.value = "👤 INFORMATIONS SALARIÉ"
c.font = section_font
c.fill = dark_blue_fill
for col in range(1, 7):
    ws.cell(row=9, column=col).fill = dark_blue_fill

info_salarie = [
    ("Nom & Prénom :", "", "Matricule :", ""),
    ("Date de naissance :", "", "N° CNPS :", ""),
    ("Poste / Emploi :", "", "Catégorie :", ""),
    ("Date d'embauche :", "", "Ancienneté (années) :", ""),
    ("Nbre personnes à charge :", "", "Période :", "Janvier 2025"),
]

for i, (l1, v1, l2, v2) in enumerate(info_salarie):
    row = 10 + i
    ws.cell(row=row, column=1, value=l1).font = bold_font
    ws.merge_cells(start_row=row, start_column=2, end_row=row, end_column=3)
    ws.cell(row=row, column=2, value=v1).font = normal_font
    ws.cell(row=row, column=2).border = Border(bottom=Side(style='dotted'))
    ws.cell(row=row, column=4, value=l2).font = bold_font
    ws.merge_cells(start_row=row, start_column=5, end_row=row, end_column=6)
    ws.cell(row=row, column=5, value=v2).font = normal_font
    ws.cell(row=row, column=5).border = Border(bottom=Side(style='dotted'))

# === SECTION GAINS ===
row_gains_header = 17
ws.merge_cells(f'A{row_gains_header}:F{row_gains_header}')
c = ws.cell(row=row_gains_header, column=1)
c.value = "💰 GAINS / RÉMUNÉRATION"
c.font = section_font
c.fill = green_fill
for col in range(1, 7):
    ws.cell(row=row_gains_header, column=col).fill = green_fill

row_gains_sub = row_gains_header + 1
gains_headers = ["N°", "Désignation", "Base / Quantité", "Taux / Unité", "Montant (FCFA)", "Soumis CNPS"]
for j, h in enumerate(gains_headers, 1):
    c = ws.cell(row=row_gains_sub, column=j, value=h)
    c.font = bold_font
    c.fill = light_blue_fill
    c.alignment = center_align
    c.border = thin_border

# Lignes de gains
gains = [
    (1, "Salaire de base mensuel", "=C19*D19", "SAISIR", True),
    (2, "Prime d'ancienneté", "=E18*Taux_anciennete", "Auto", True),
    (3, "Prime de rendement / performance", "", "SAISIR", True),
    (4, "Indemnité de logement", "", "SAISIR", True),
    (5, "Indemnité de risque / salissure", "", "SAISIR", True),
    (6, "Heures supplémentaires (130%)", "=C23*D23*Majoration_HS", "SAISIR", True),
    (7, "Heures sup. nuit (150%)", "", "SAISIR", True),
    (8, "Heures sup. dimanche/férié (200%)", "", "SAISIR", True),
    (9, "Indemnité de transport", "", "SAISIR", False),
    (10, "Prime de panier", "", "SAISIR", False),
    (11, "Autres primes / indemnités", "", "SAISIR", True),
]

start_gain_row = row_gains_sub + 1  # row 18
for i, (num, designation, formula, note, cnps) in enumerate(gains):
    row = start_gain_row + i
    ws.cell(row=row, column=1, value=num).font = normal_font
    ws.cell(row=row, column=1).alignment = center_align
    ws.cell(row=row, column=1).border = thin_border

    ws.cell(row=row, column=2, value=designation).font = normal_font
    ws.cell(row=row, column=2).border = thin_border

    ws.cell(row=row, column=3).font = normal_font
    ws.cell(row=row, column=3).alignment = right_align
    ws.cell(row=row, column=3).border = thin_border
    ws.cell(row=row, column=3).fill = yellow_fill
    ws.cell(row=row, column=3).number_format = fcfa_format

    ws.cell(row=row, column=4).font = normal_font
    ws.cell(row=row, column=4).alignment = right_align
    ws.cell(row=row, column=4).border = thin_border
    ws.cell(row=row, column=4).fill = yellow_fill
    ws.cell(row=row, column=4).number_format = fcfa_format

    ws.cell(row=row, column=5).font = normal_font
    ws.cell(row=row, column=5).alignment = right_align
    ws.cell(row=row, column=5).border = thin_border
    ws.cell(row=row, column=5).number_format = fcfa_format
    if formula and formula.startswith("="):
        ws.cell(row=row, column=5).value = formula
    else:
        ws.cell(row=row, column=5).fill = yellow_fill

    ws.cell(row=row, column=6, value="Oui" if cnps else "Non").font = normal_font
    ws.cell(row=row, column=6).alignment = center_align
    ws.cell(row=row, column=6).border = thin_border

    if i % 2 == 0:
        for col in range(1, 7):
            if ws.cell(row=row, column=col).fill == PatternFill():
                ws.cell(row=row, column=col).fill = light_gray_fill

# Correction: pour les lignes saisies, la colonne E doit être saisie manuellement
# Ligne 1 (salaire base): E = C * D
ws.cell(row=start_gain_row, column=5).value = f"=C{start_gain_row}*D{start_gain_row}"
ws.cell(row=start_gain_row, column=5).fill = white_fill

# Ligne 2 (ancienneté): E = E_salaire_base * taux
ws.cell(row=start_gain_row+1, column=5).value = f"=E{start_gain_row}*Taux_anciennete"
ws.cell(row=start_gain_row+1, column=5).fill = white_fill

# Lignes saisies manuellement (3,4,5) : E est jaune (saisie)
for r in [start_gain_row+2, start_gain_row+3, start_gain_row+4]:
    ws.cell(row=r, column=5).value = None

# Ligne 6 (HS 130%): E = C * D * Majoration_HS
ws.cell(row=start_gain_row+5, column=5).value = f"=C{start_gain_row+5}*D{start_gain_row+5}*Majoration_HS"
ws.cell(row=start_gain_row+5, column=5).fill = white_fill

# Lignes 7, 8 saisies
# Ligne 9 transport: saisie avec indication
# Lignes 10, 11 saisies

# Total brut
total_brut_row = start_gain_row + len(gains)  # row 29
ws.merge_cells(f'A{total_brut_row}:D{total_brut_row}')
c = ws.cell(row=total_brut_row, column=1)
c.value = "TOTAL BRUT"
c.font = Font(name='Calibri', bold=True, size=11, color='1F4E79')
c.alignment = Alignment(horizontal='right', vertical='center')
for col in range(1, 7):
    ws.cell(row=total_brut_row, column=col).fill = light_blue_fill
    ws.cell(row=total_brut_row, column=col).border = Border(top=Side(style='medium'), bottom=Side(style='medium'))
ws.cell(row=total_brut_row, column=5).value = f"=SOMME(E{start_gain_row}:E{total_brut_row-1})"
ws.cell(row=total_brut_row, column=5).font = Font(name='Calibri', bold=True, size=11)
ws.cell(row=total_brut_row, column=5).alignment = right_align
ws.cell(row=total_brut_row, column=5).number_format = fcfa_format

# === SECTION RETENUES ===
row_retenues_header = total_brut_row + 2
ws.merge_cells(f'A{row_retenues_header}:F{row_retenues_header}')
c = ws.cell(row=row_retenues_header, column=1)
c.value = "📉 COTISATIONS & RETENUES"
c.font = section_font
c.fill = orange_fill
for col in range(1, 7):
    ws.cell(row=row_retenues_header, column=col).fill = orange_fill

row_ret_sub = row_retenues_header + 1
ret_headers = ["N°", "Désignation", "Base imposable", "Taux", "Montant (FCFA)", "Part"]
for j, h in enumerate(ret_headers, 1):
    c = ws.cell(row=row_ret_sub, column=j, value=h)
    c.font = bold_font
    c.fill = light_blue_fill
    c.alignment = center_align
    c.border = thin_border

# Lignes de retenues
start_ret_row = row_ret_sub + 1
retenues = [
    (1, "CNPS - Part salariale (4,2%)", f"=MIN(E{total_brut_row}-Transport_exo;Plafond_CNPS)", "Taux_CNPS_sal", "Salariale"),
    (2, "IRPP (avant abattement)", f"=MAX(0;E{total_brut_row}-Transport_exo)", "", ""),
    (3, "Abattement forfaitaire 20%", "", "", ""),
    (4, "Revenu net imposable (RNI)", "", "", ""),
    (5, "IRPP dû", "", "", ""),
    (6, "Taxe communale (10% IRPP)", "", "", ""),
    (7, "Avance sur salaire", "", "", "Saisir"),
    (8, "Opposition / Saisie", "", "", "Saisir"),
    (9, "Prêt logement", "", "", "Saisir"),
    (10, "Autres retenues", "", "", "Saisir"),
]

for i, (num, designation, base_f, taux_f, note) in enumerate(retenues):
    row = start_ret_row + i
    ws.cell(row=row, column=1, value=num).font = normal_font
    ws.cell(row=row, column=1).alignment = center_align
    ws.cell(row=row, column=1).border = thin_border

    ws.cell(row=row, column=2, value=designation).font = normal_font
    ws.cell(row=row, column=2).border = thin_border

    ws.cell(row=row, column=3).font = normal_font
    ws.cell(row=row, column=3).alignment = right_align
    ws.cell(row=row, column=3).border = thin_border
    ws.cell(row=row, column=3).number_format = fcfa_format

    ws.cell(row=row, column=4).font = normal_font
    ws.cell(row=row, column=4).alignment = right_align
    ws.cell(row=row, column=4).border = thin_border
    ws.cell(row=row, column=4).number_format = pct_format

    ws.cell(row=row, column=5).font = normal_font
    ws.cell(row=row, column=5).alignment = right_align
    ws.cell(row=row, column=5).border = thin_border
    ws.cell(row=row, column=5).number_format = fcfa_format

    ws.cell(row=row, column=6, value=note if note else "").font = Font(name='Calibri', italic=True, size=9, color='888888')
    ws.cell(row=row, column=6).alignment = center_align
    ws.cell(row=row, column=6).border = thin_border

    if i % 2 == 0:
        for col in range(1, 7):
            if ws.cell(row=row, column=col).fill == PatternFill():
                ws.cell(row=row, column=col).fill = light_gray_fill

# Remplissage des formules de retenues
r1 = start_ret_row      # CNPS
r2 = start_ret_row + 1  # IRPP avant abattement (base)
r3 = start_ret_row + 2  # Abattement
r4 = start_ret_row + 3  # RNI
r5 = start_ret_row + 4  # IRPP dû
r6 = start_ret_row + 5  # Taxe communale
r7 = start_ret_row + 6  # Avance
r8 = start_ret_row + 7  # Opposition
r9 = start_ret_row + 8  # Prêt
r10 = start_ret_row + 9 # Autres

# Ligne 1 CNPS
ws.cell(row=r1, column=3).value = f"=MIN(E{total_brut_row}-Transport_exo;Plafond_CNPS)"
ws.cell(row=r1, column=3).fill = white_fill
ws.cell(row=r1, column=4).value = Taux_CNPS_sal if False else '=Taux_CNPS_sal'  
# Fix: need to use the named range
ws.cell(row=r1, column=4).value = '=Taux_CNPS_sal'
ws.cell(row=r1, column=4).fill = white_fill
ws.cell(row=r1, column=5).value = f"=C{r1}*D{r1}"

# Ligne 2 Base IRPP brute
ws.cell(row=r2, column=3).value = f"=MAX(0;E{total_brut_row}-Transport_exo)"
ws.cell(row=r2, column=3).fill = white_fill
ws.cell(row=r2, column=4).value = None
ws.cell(row=r2, column=5).value = f"=C{r2}"

# Ligne 3 Abattement
ws.cell(row=r3, column=3).value = None
ws.cell(row=r3, column=5).value = f"=MIN(C{r2}*Taux_abattement;Plafond_abat_mensuel)"
ws.cell(row=r3, column=4).value = '=Taux_abattement'
ws.cell(row=r3, column=4).fill = white_fill

# Ligne 4 RNI = Base IRPP - CNPS sal - Abattement
ws.cell(row=r4, column=5).value = f"=MAX(0;C{r2}-E{r1}-E{r3})"
ws.cell(row=r4, column=3).value = f"=E{r4}"
ws.cell(row=r4, column=3).fill = white_fill

# Ligne 5 IRPP dû - formule en escalier
ws.cell(row=r5, column=5).value = (
    f'=ARRONDI('
    f'SI(E{r4}<=IRPP_T1_haut;0;'
    f'SI(E{r4}<=IRPP_T2_haut;(E{r4}-IRPP_T2_bas)*IRPP_T2_taux;'
    f'SI(E{r4}<=IRPP_T3_haut;(IRPP_T3_bas-IRPP_T2_bas)*IRPP_T2_taux+(E{r4}-IRPP_T3_bas)*IRPP_T3_taux;'
    f'SI(E{r4}<=IRPP_T4_haut;(IRPP_T3_bas-IRPP_T2_bas)*IRPP_T2_taux+(IRPP_T4_bas-IRPP_T3_bas)*IRPP_T3_taux+(E{r4}-IRPP_T4_bas)*IRPP_T4_taux;'
    f'(IRPP_T3_bas-IRPP_T2_bas)*IRPP_T2_taux+(IRPP_T4_bas-IRPP_T3_bas)*IRPP_T3_taux+(IRPP_T5_bas-IRPP_T4_bas)*IRPP_T4_taux+(E{r4}-IRPP_T5_bas)*IRPP_T5_taux))));0)'
)

# Ligne 6 Taxe communale = 10% IRPP
ws.cell(row=r6, column=5).value = f"=ARRONDI(E{r5}*0.10;0)"
ws.cell(row=r6, column=4).value = 0.10

# Lignes 7-10 : saisie manuelle (jaune)
for r in [r7, r8, r9, r10]:
    ws.cell(row=r, column=5).fill = yellow_fill

# Total retenues
total_ret_row = start_ret_row + len(retenues)
ws.merge_cells(f'A{total_ret_row}:D{total_ret_row}')
c = ws.cell(row=total_ret_row, column=1)
c.value = "TOTAL RETENUES"
c.font = Font(name='Calibri', bold=True, size=11, color='C62828')
c.alignment = Alignment(horizontal='right', vertical='center')
for col in range(1, 7):
    ws.cell(row=total_ret_row, column=col).fill = PatternFill(start_color='FFCDD2', end_color='FFCDD2', fill_type='solid')
    ws.cell(row=total_ret_row, column=col).border = Border(top=Side(style='medium'), bottom=Side(style='medium'))
ws.cell(row=total_ret_row, column=5).value = f"=SOMME(E{start_ret_row}:E{total_ret_row-1})"
ws.cell(row=total_ret_row, column=5).font = Font(name='Calibri', bold=True, size=11, color='C62828')
ws.cell(row=total_ret_row, column=5).alignment = right_align
ws.cell(row=total_ret_row, column=5).number_format = fcfa_format

# === NET À PAYER ===
net_row = total_ret_row + 2
ws.merge_cells(f'A{net_row}:D{net_row}')
c = ws.cell(row=net_row, column=1)
c.value = "✅ NET À PAYER (FCFA)"
c.font = net_font
c.fill = green_fill
c.alignment = Alignment(horizontal='right', vertical='center')
for col in range(1, 7):
    ws.cell(row=net_row, column=col).fill = green_fill
ws.cell(row=net_row, column=5).value = f"=E{total_brut_row}-E{total_ret_row}"
ws.cell(row=net_row, column=5).font = Font(name='Calibri', bold=True, size=14, color='FFFFFF')
ws.cell(row=net_row, column=5).fill = green_fill
ws.cell(row=net_row, column=5).alignment = right_align
ws.cell(row=net_row, column=5).number_format = '#,##0 "FCFA"'
ws.cell(row=net_row, column=5).border = Border(top=Side(style='double'), bottom=Side(style='double'))
ws.row_dimensions[net_row].height = 30

# Ligne en toutes lettres
lettres_row = net_row + 1
ws.merge_cells(f'A{lettres_row}:F{lettres_row}')
ws.cell(row=lettres_row, column=1).value = f'Arrêté le présent bulletin à la somme de : NET À PAYER = {ws.cell(row=net_row, column=5).coordinate} FCFA'
ws.cell(row=lettres_row, column=1).value = f'=CONCATENATE("Arrêté le présent bulletin à la somme de : ",TEXTE(E{net_row};"# ##0")," FCFA")'
ws.cell(row=lettres_row, column=1).font = Font(name='Calibri', italic=True, size=10)

# === COTISATIONS PATRONALES (pour info) ===
patron_row = lettres_row + 2
ws.merge_cells(f'A{patron_row}:F{patron_row}')
c = ws.cell(row=patron_row, column=1)
c.value = "📋 COTISATIONS PATRONALES (à titre informatif)"
c.font = section_font
c.fill = PatternFill(start_color='5D4037', end_color='5D4037', fill_type='solid')
for col in range(1, 7):
    ws.cell(row=patron_row, column=col).fill = PatternFill(start_color='5D4037', end_color='5D4037', fill_type='solid')

pat_sub = patron_row + 1
pat_headers = ["N°", "Désignation", "Base", "Taux", "Montant (FCFA)", "Remarque"]
for j, h in enumerate(pat_headers, 1):
    c = ws.cell(row=pat_sub, column=j, value=h)
    c.font = bold_font
    c.fill = light_blue_fill
    c.alignment = center_align
    c.border = thin_border

cotis_patron = [
    (1, "Prestations familiales (7%)", f"=C{r1}", '=Taux_CNPS_pf'),
    (2, "Pensions (4,2%)", f"=C{r1}", '=Taux_CNPS_pen'),
    (3, "Accident du travail (1,75%)", f"=C{r1}", '=Taux_CNPS_at'),
    (4, "TOTAL COTISATIONS PATRONALES", "", ""),
]

start_pat_row = pat_sub + 1
for i, (num, des, base, taux) in enumerate(cotis_patron):
    row = start_pat_row + i
    ws.cell(row=row, column=1, value=num).font = normal_font
    ws.cell(row=row, column=1).alignment = center_align
    ws.cell(row=row, column=1).border = thin_border
    ws.cell(row=row, column=2, value=des).font = normal_font
    ws.cell(row=row, column=2).border = thin_border
    ws.cell(row=row, column=3).border = thin_border
    ws.cell(row=row, column=3).number_format = fcfa_format
    ws.cell(row=row, column=4).border = thin_border
    ws.cell(row=row, column=4).number_format = pct_format
    ws.cell(row=row, column=5).border = thin_border
    ws.cell(row=row, column=5).number_format = fcfa_format
    if i < 3:
        ws.cell(row=row, column=3).value = base
        ws.cell(row=row, column=3).fill = white_fill
        ws.cell(row=row, column=4).value = taux
        ws.cell(row=row, column=4).fill = white_fill
        ws.cell(row=row, column=5).value = f"=C{row}*D{row}"
        ws.cell(row=row, column=5).fill = white_fill
    if i % 2 == 0:
        for col in range(1, 7):
            if ws.cell(row=row, column=col).fill == PatternFill():
                ws.cell(row=row, column=col).fill = light_gray_fill

# Total patronal
total_pat_row = start_pat_row + 3
ws.cell(row=total_pat_row, column=2).value = "TOTAL COTISATIONS PATRONALES"
ws.cell(row=total_pat_row, column=2).font = Font(name='Calibri', bold=True, size=10, color='5D4037')
ws.cell(row=total_pat_row, column=5).value = f"=SOMME(E{start_pat_row}:E{total_pat_row-1})"
ws.cell(row=total_pat_row, column=5).font = Font(name='Calibri', bold=True, size=10, color='5D4037')
ws.cell(row=total_pat_row, column=5).number_format = fcfa_format
for col in range(1, 7):
    ws.cell(row=total_pat_row, column=col).fill = PatternFill(start_color='D7CCC8', end_color='D7CCC8', fill_type='solid')
    ws.cell(row=total_pat_row, column=col).border = Border(top=Side(style='medium'), bottom=Side(style='medium'))

# === COÛT TOTAL EMPLOYEUR ===
cout_row = total_pat_row + 2
ws.merge_cells(f'A{cout_row}:D{cout_row}')
c = ws.cell(row=cout_row, column=1)
c.value = "COÛT TOTAL EMPLOYEUR"
c.font = Font(name='Calibri', bold=True, size=11, color='FFFFFF')
c.fill = PatternFill(start_color='5D4037', end_color='5D4037', fill_type='solid')
c.alignment = Alignment(horizontal='right', vertical='center')
for col in range(1, 7):
    ws.cell(row=cout_row, column=col).fill = PatternFill(start_color='5D4037', end_color='5D4037', fill_type='solid')
ws.cell(row=cout_row, column=5).value = f"=E{total_brut_row}+E{total_pat_row}"
ws.cell(row=cout_row, column=5).font = Font(name='Calibri', bold=True, size=12, color='FFFFFF')
ws.cell(row=cout_row, column=5).alignment = right_align
ws.cell(row=cout_row, column=5).number_format = '#,##0 "FCFA"'
ws.cell(row=cout_row, column=5).border = Border(bottom=Side(style='double'))

# === SIGNATURES ===
sig_row = cout_row + 3
ws.merge_cells(f'A{sig_row}:C{sig_row}')
ws.cell(row=sig_row, column=1).value = "Signature de l'Employeur :"
ws.cell(row=sig_row, column=1).font = bold_font
ws.merge_cells(f'D{sig_row}:F{sig_row}')
ws.cell(row=sig_row, column=4).value = "Signature du Salarié (avec mention 'Bon pour accord') :"
ws.cell(row=sig_row, column=4).font = bold_font

ws.merge_cells(f'A{sig_row+2}:C{sig_row+2}')
ws.cell(row=sig_row+2, column=1).value = "Date : _____/_____/_________"
ws.cell(row=sig_row+2, column=1).font = normal_font
ws.merge_cells(f'D{sig_row+2}:F{sig_row+2}')
ws.cell(row=sig_row+2, column=4).value = "Date : _____/_____/_________"
ws.cell(row=sig_row+2, column=4).font = normal_font

# Mention légale
legal_row = sig_row + 4
ws.merge_cells(f'A{legal_row}:F{legal_row}')
ws.cell(row=legal_row, column=1).value = "Mention légale : Conformément à l'article 37 du Décret n°2019/482, ce bulletin de paie doit être conservé par le salarié pendant une durée minimale de 4 ans."
ws.cell(row=legal_row, column=1).font = Font(name='Calibri', italic=True, size=8, color='999999')
ws.cell(row=legal_row, column=1).alignment = Alignment(wrap_text=True)

# === IMPRESSION ===
ws.print_area = f'A1:F{legal_row}'
ws.page_setup.orientation = 'portrait'
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 1
ws.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)

# Protection (optionnelle) — on laisse tout déverrouillé pour modification facile
# ws.protection.sheet = True

# ============================================================
# ONGLET EXEMPLE CHIFFRÉ (pour vérification)
# ============================================================
ws_ex = wb.create_sheet("Exemple chiffré")
ws_ex.sheet_properties.tabColor = "FF6F00"
ws_ex.column_dimensions['A'].width = 35
ws_ex.column_dimensions['B'].width = 20

ws_ex.merge_cells('A1:B1')
c = ws_ex['A1']
c.value = "📊 EXEMPLE CHIFFRÉ DE VÉRIFICATION"
c.font = Font(name='Calibri', bold=True, size=14, color='FFFFFF')
c.fill = PatternFill(start_color='FF6F00', end_color='FF6F00', fill_type='solid')
c.alignment = center_align
ws_ex['B1'].fill = PatternFill(start_color='FF6F00', end_color='FF6F00', fill_type='solid')

exemples = [
    ("", ""),
    ("Saisissez dans l'onglet 'Paie' :", ""),
    ("Cellule C18 (Base salaire)", "173.33 heures"),
    ("Cellule D18 (Taux horaire)", "1731 FCFA"),
    ("→ Salaire de base (E18)", "300 000 FCFA"),
    ("", ""),
    ("Cellule E19 (Ancienneté)", "Auto-calculé 2%"),
    ("Cellule E20 (Prime rendement)", "15 000"),
    ("Cellule E21 (Ind. logement)", "20 000"),
    ("Cellule E22 (Ind. risque)", "0"),
    ("Cellule C23 (Nb heures sup)", "10 heures"),
    ("Cellule D23 (Taux horaire sup)", "1731 FCFA"),
    ("Cellule E26 (Transport)", "10 000"),
    ("", ""),
    ("RÉSULTATS ATTENDUS :", ""),
    ("Total Brut", "≈ 364 316 FCFA"),
    ("CNPS salariale (4,2%)", "≈ 14 811 FCFA"),
    ("IRPP dû", "≈ 12 800 FCFA"),
    ("Taxe communale", "≈ 1 280 FCFA"),
    ("Net à payer", "≈ 335 425 FCFA"),
]

for i, (label, val) in enumerate(exemples):
    row = 2 + i
    ws_ex.cell(row=row, column=1, value=label).font = bold_font if ":" in label or "→" in label or "RÉSULTATS" in label else normal_font
    ws_ex.cell(row=row, column=2, value=val).font = normal_font
    if "→" in label or "RÉSULTATS" in label:
        ws_ex.cell(row=row, column=1).fill = light_blue_fill
        ws_ex.cell(row=row, column=2).fill = light_blue_fill

# Sauvegarde
filename = "Bulletin_Paie_Cameroun_Industrie.xlsx"
wb.save(filename)
print(f"✅ Fichier '{filename}' généré avec succès!")
print(f"   → Onglet 'Paie' : bulletin complet avec formules")
print(f"   → Onglet 'Paramètres' : taux et barèmes modifiables")
print(f"   → Onglet 'Exemple chiffré' : guide de saisie")('Hello world!')