#!/usr/bin/env python3
"""Classeurs Excel du traité (openpyxl, formules vivantes) : spreadsheets/
- cas1_grille_comparaison.xlsx : grille d'ajustements des 5 comparables (ch. 16-17) avec formules (prix ajusté, brut/net, pondération)
- cas3_dcf_12_logements.xlsx : état locatif, dépenses normalisées, RNE, capitalisation, DCF 10 ans, financement (ch. 28-31, 47)
- amortissement_hypothecaire.xlsx : calculateur d'hypothèque canadienne (composition semestrielle) avec tableau d'amortissement
Les valeurs sont celles des modules traite.cas1 / traite.cas3 ; les formules Excel reproduisent les identités du livre."""
import sys, pathlib
sys.path.insert(0, str(pathlib.Path(__file__).resolve().parents[1]))
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from traite import cas1, cas3, finance as fin

ROOT = pathlib.Path(__file__).resolve().parents[3]; OUT = ROOT / "spreadsheets"; OUT.mkdir(exist_ok=True)
H = Font(bold=True, color="FFFFFF"); HF = PatternFill("solid", fgColor="003E7E"); NOTE = Font(italic=True, color="606670")
MONEY = '#,##0 "$"'; PCT = "0.00%"

def entete(ws, row, labels):
    for j, l in enumerate(labels, 1):
        c = ws.cell(row=row, column=j, value=l); c.font = H; c.fill = HF; c.alignment = Alignment(wrap_text=True, vertical="center")

def avert(ws, row, txt="DONNÉES SYNTHÉTIQUES — aucun bien réel. Généré par analysis/python/cases/make_spreadsheets.py"):
    ws.cell(row=row, column=1, value=txt).font = NOTE

def grille_cas1():
    r = cas1.tout(); comps = r["selection"]["comparables"].reset_index(drop=True); adj = r["ajustements"]; s = r["sujet"]
    wb = Workbook(); ws = wb.active; ws.title = "Grille"
    avert(ws, 1); ws.cell(row=2, column=1, value="Grille d'ajustements — cas 1, maison de Gatineau (chapitres 16-17)").font = Font(bold=True, size=13)
    cols = ["Élément", "Sujet"] + list(comps["id_vente"])
    entete(ws, 4, cols)
    lignes = [("Date de vente", s["date_evaluation"], [str(d.date()) for d in comps["date_vente"]]), ("Superficie habitable (m²)", s["superficie_habitable_m2"], list(comps["superficie_habitable_m2"])),
              ("Terrain (m²)", s["superficie_terrain_m2"], list(comps["superficie_terrain_m2"])), ("Année de construction", s["annee_construction"], list(comps["annee_construction"])),
              ("Qualité (1-5)", s["qualite_1a5"], list(comps["qualite_1a5"])), ("Garage", s["garage_places"], list(comps["garage_places"])), ("Sous-sol fini", s["sous_sol_fini"], list(comps["sous_sol_fini"]))]
    row = 5
    for lab, sv, vals in lignes:
        ws.cell(row=row, column=1, value=lab); ws.cell(row=row, column=2, value=sv)
        for j, v in enumerate(vals, 3): ws.cell(row=row, column=j, value=float(v) if isinstance(v, (int, float)) and not isinstance(v, bool) else v)
        row += 1
    row += 1; ws.cell(row=row, column=1, value="Prix de vente").font = Font(bold=True); prix_row = row
    for j, v in enumerate(comps["prix_vente"], 3): ws.cell(row=row, column=j, value=float(v)).number_format = MONEY
    row += 1; ws.cell(row=row, column=1, value="Ajustements (du point de vue du sujet, $)").font = Font(italic=True)
    groupes = ["conditions_marche", "localisation", "superficie", "terrain", "age", "qualite", "garage", "sous_sol", "piscine", "type"]
    noms = ["Conditions du marché", "Localisation", "Superficie habitable", "Terrain", "Âge", "Qualité", "Garage", "Sous-sol", "Piscine", "Type"]
    first_adj = row + 1
    for g, nm in zip(groupes, noms):
        row += 1; ws.cell(row=row, column=1, value=nm)
        for j in range(len(comps)): ws.cell(row=row, column=3 + j, value=round(float(adj.loc[j, g]), 2)).number_format = MONEY
    last_adj = row
    row += 1; ws.cell(row=row, column=1, value="Prix ajusté").font = Font(bold=True); paj_row = row
    for j in range(len(comps)):
        L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"={L}{prix_row}+SUM({L}{first_adj}:{L}{last_adj})").number_format = MONEY
    row += 1; ws.cell(row=row, column=1, value="Ajustement brut (%)"); brut_row = row
    for j in range(len(comps)):
        L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"=SUMPRODUCT(ABS({L}{first_adj}:{L}{last_adj}))/{L}{prix_row}").number_format = PCT
    row += 1; ws.cell(row=row, column=1, value="Ajustement net (%)")
    for j in range(len(comps)):
        L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"=({L}{paj_row}-{L}{prix_row})/{L}{prix_row}").number_format = PCT
    row += 1; ws.cell(row=row, column=1, value="Poids ∝ 1/brut"); w_row = row
    rng = f"{get_column_letter(3)}{brut_row}:{get_column_letter(2 + len(comps))}{brut_row}"
    for j in range(len(comps)):
        L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"=(1/MAX({L}{brut_row},0.01))/SUMPRODUCT(1/IF({rng}<0.01,0.01,{rng}))").number_format = "0.000"
    row += 2; ws.cell(row=row, column=1, value="Valeur indiquée (pondérée)").font = Font(bold=True)
    ws.cell(row=row, column=2, value=f"=SUMPRODUCT({get_column_letter(3)}{paj_row}:{get_column_letter(2+len(comps))}{paj_row},{get_column_letter(3)}{w_row}:{get_column_letter(2+len(comps))}{w_row})").number_format = MONEY
    row += 1; ws.cell(row=row, column=1, value="Moyenne / médiane des prix ajustés"); ws.cell(row=row, column=2, value=f"=AVERAGE({get_column_letter(3)}{paj_row}:{get_column_letter(2+len(comps))}{paj_row})").number_format = MONEY
    ws.cell(row=row, column=3, value=f"=MEDIAN({get_column_letter(3)}{paj_row}:{get_column_letter(2+len(comps))}{paj_row})").number_format = MONEY
    for c in range(1, 8): ws.column_dimensions[get_column_letter(c)].width = 26 if c == 1 else 16
    wb.save(OUT / "cas1_grille_comparaison.xlsx")

def dcf_cas3():
    r = cas3.tout(); e = r["etat"]; b = r["base"]; h = r["hyp"]; proj = r["projection"]; f = r["financement"]
    wb = Workbook(); ws = wb.active; ws.title = "Hypothèses"; avert(ws, 1)
    ws.cell(row=2, column=1, value="Cas 3 — 12 logements à Montréal : capitalisation, DCF et financement (chapitres 28-31, 47)").font = Font(bold=True, size=13)
    hyp = [("Taux d'inoccupation et pertes", h["taux_inoccupation_pertes"], PCT), ("Croissance loyers contractuels (TAL)", h["croissance_loyers_contrat"], PCT), ("Croissance loyers de marché", h["croissance_loyers_marche"], PCT),
           ("Rotation annuelle", h["rotation_annuelle"], PCT), ("Croissance des dépenses", h["croissance_depenses"], PCT), ("Taux d'actualisation", h["taux_actualisation"], PCT), ("Taux terminal", h["taux_terminal"], PCT),
           ("Coûts de vente", h["couts_vente"], PCT), ("Taux de capitalisation retenu (médiane extraite)", r["taux"]["mediane"], PCT), ("RPV max", h["financement"]["rpv"], PCT), ("Taux hypothécaire (nominal, semestriel)", h["financement"]["taux"], PCT),
           ("Amortissement (mois)", h["financement"]["amortissement_mois"], "0"), ("RCD minimal", h["financement"]["rcd_min"], "0.00")]
    names = {}
    for i, (lab, v, fmt) in enumerate(hyp, 4):
        ws.cell(row=i, column=1, value=lab); c = ws.cell(row=i, column=2, value=float(v)); c.number_format = fmt; names[lab] = f"Hypothèses!$B${i}"
    ws.column_dimensions["A"].width = 46; ws.column_dimensions["B"].width = 14
    # état locatif
    w2 = wb.create_sheet("État locatif"); avert(w2, 1); entete(w2, 3, ["Logement", "Pièces", "Chambres", "Superficie (m²)", "Loyer contrat ($/mois)", "Loyer marché ($/mois)", "Écart ($/mois)", "Locataire depuis"])
    for i, row in e.iterrows():
        rr = 4 + i
        for j, k in enumerate(["logement", "pieces", "chambres", "superficie_m2", "loyer_mensuel_contrat", "loyer_mensuel_marche"], 1):
            v = row[k]; w2.cell(row=rr, column=j, value=float(v) if isinstance(v, (int, float)) and k not in ("logement",) else str(v))
        w2.cell(row=rr, column=7, value=f"=F{rr}-E{rr}"); w2.cell(row=rr, column=8, value=int(row["locataire_depuis"]))
    last = 3 + len(e); w2.cell(row=last + 1, column=1, value="Total mensuel").font = Font(bold=True)
    for col in "EFG": w2.cell(row=last + 1, column=ord(col) - 64, value=f"=SUM({col}4:{col}{last})").number_format = MONEY
    w2.cell(row=last + 2, column=1, value="RBP annuel (contrat)").font = Font(bold=True); w2.cell(row=last + 2, column=5, value=f"=E{last+1}*12").number_format = MONEY
    w2.cell(row=last + 2, column=6, value=f"=F{last+1}*12").number_format = MONEY
    rbp_ref = f"'État locatif'!$E${last+2}"
    # dépenses
    w3 = wb.create_sheet("Dépenses et RNE"); avert(w3, 1); entete(w3, 3, ["Poste", "Historique 2025 ($)", "Normalisé ($)", "Type"])
    det = b["detail"]
    for i, row in det.reset_index(drop=True).iterrows():
        rr = 4 + i; w3.cell(row=rr, column=1, value=row["poste"]); w3.cell(row=rr, column=2, value=None if row["historique_2025"] != row["historique_2025"] else float(row["historique_2025"])).number_format = MONEY
        w3.cell(row=rr, column=3, value=round(float(row["normalise"]), 2)).number_format = MONEY; w3.cell(row=rr, column=4, value=row["type"])
    ld = 3 + len(det)
    w3.cell(row=ld + 2, column=1, value="RBP (contrat)"); w3.cell(row=ld + 2, column=3, value=f"={rbp_ref}").number_format = MONEY
    w3.cell(row=ld + 3, column=1, value="Autres revenus"); w3.cell(row=ld + 3, column=3, value=float(b["autres_revenus"])).number_format = MONEY
    w3.cell(row=ld + 4, column=1, value="Inoccupation et pertes"); w3.cell(row=ld + 4, column=3, value=f"=-C{ld+2}*{names['Taux d\'inoccupation et pertes']}").number_format = MONEY
    w3.cell(row=ld + 5, column=1, value="RBE").font = Font(bold=True); w3.cell(row=ld + 5, column=3, value=f"=C{ld+2}+C{ld+3}+C{ld+4}").number_format = MONEY
    w3.cell(row=ld + 6, column=1, value="Dépenses totales (réserve incluse)"); w3.cell(row=ld + 6, column=3, value=f"=SUM(C4:C{ld})").number_format = MONEY
    w3.cell(row=ld + 7, column=1, value="RNE").font = Font(bold=True); w3.cell(row=ld + 7, column=3, value=f"=C{ld+5}-C{ld+6}").number_format = MONEY
    w3.cell(row=ld + 8, column=1, value="Ratio de dépenses"); w3.cell(row=ld + 8, column=3, value=f"=C{ld+6}/C{ld+5}").number_format = PCT
    w3.cell(row=ld + 10, column=1, value="Valeur par capitalisation directe (RNE année 1 / R)").font = Font(bold=True)
    w3.cell(row=ld + 10, column=3, value=f"='DCF'!C5/{names['Taux de capitalisation retenu (médiane extraite)']}").number_format = MONEY
    w3.column_dimensions["A"].width = 44; w3.column_dimensions["B"].width = 18; w3.column_dimensions["C"].width = 18
    # DCF
    w4 = wb.create_sheet("DCF"); avert(w4, 1); entete(w4, 3, ["Année", "RBP ($)", "RNE ($)", "CAPEX ($)", "Flux avant dette ($)", "Facteur d'actualisation", "VA ($)"])
    T = h["horizon_ans"]
    for i, row in proj.iterrows():
        rr = 4 + i; w4.cell(row=rr, column=1, value=int(row["annee"]))
        w4.cell(row=rr, column=2, value=round(float(row["rbp"]), 2)).number_format = MONEY; w4.cell(row=rr, column=3, value=round(float(row["rne"]), 2)).number_format = MONEY
        w4.cell(row=rr, column=4, value=round(float(row["capex"]), 2)).number_format = MONEY; w4.cell(row=rr, column=5, value=f"=C{rr}-D{rr}").number_format = MONEY
        if row["annee"] <= T:
            w4.cell(row=rr, column=6, value=f"=1/(1+{names['Taux d\'actualisation']})^A{rr}").number_format = "0.0000"; w4.cell(row=rr, column=7, value=f"=E{rr}*F{rr}").number_format = MONEY
    rT1 = 4 + T; rT = 3 + T
    w4.cell(row=rT1 + 2, column=1, value="Valeur terminale brute = RNE(T+1)/R_T"); w4.cell(row=rT1 + 2, column=7, value=f"=C{rT1}/{names['Taux terminal']}").number_format = MONEY
    w4.cell(row=rT1 + 3, column=1, value="Valeur terminale nette (après coûts de vente)"); w4.cell(row=rT1 + 3, column=7, value=f"=G{rT1+2}*(1-{names['Coûts de vente']})").number_format = MONEY
    w4.cell(row=rT1 + 4, column=1, value="VA de la valeur terminale"); w4.cell(row=rT1 + 4, column=7, value=f"=G{rT1+3}*F{rT}").number_format = MONEY
    w4.cell(row=rT1 + 5, column=1, value="VA des flux (années 1 à T)"); w4.cell(row=rT1 + 5, column=7, value=f"=SUM(G4:G{rT})").number_format = MONEY
    w4.cell(row=rT1 + 6, column=1, value="VALEUR DCF").font = Font(bold=True); w4.cell(row=rT1 + 6, column=7, value=f"=G{rT1+4}+G{rT1+5}").number_format = MONEY
    w4.cell(row=rT1 + 7, column=1, value="Part de la valeur terminale"); w4.cell(row=rT1 + 7, column=7, value=f"=G{rT1+4}/G{rT1+6}").number_format = PCT
    w4.cell(row=rT1 + 8, column=1, value=f"Contrôle : valeur calculée par traite.cas3 = {round(r['dcf']['valeur'])} $").font = NOTE
    w4.column_dimensions["A"].width = 44
    for c in "BCDEFG": w4.column_dimensions[c].width = 16
    # financement
    w5 = wb.create_sheet("Financement"); avert(w5, 1)
    w5.cell(row=3, column=1, value="Valeur retenue (DCF)"); w5.cell(row=3, column=2, value=f"='DCF'!G{rT1+6}").number_format = MONEY
    w5.cell(row=4, column=1, value="RNE année 1"); w5.cell(row=4, column=2, value="='DCF'!C4").number_format = MONEY
    w5.cell(row=5, column=1, value="Taux mensuel équivalent (composition semestrielle)"); w5.cell(row=5, column=2, value=f"=(1+{names['Taux hypothécaire (nominal, semestriel)']}/2)^(1/6)-1").number_format = "0.000000"
    w5.cell(row=6, column=1, value="Constante hypothécaire annuelle f"); w5.cell(row=6, column=2, value=f"=12*B5/(1-(1+B5)^(-{names['Amortissement (mois)']}))").number_format = "0.000000"
    w5.cell(row=7, column=1, value="Prêt max par RPV"); w5.cell(row=7, column=2, value=f"=B3*{names['RPV max']}").number_format = MONEY
    w5.cell(row=8, column=1, value="Prêt max par RCD"); w5.cell(row=8, column=2, value=f"=B4/{names['RCD minimal']}/B6").number_format = MONEY
    w5.cell(row=9, column=1, value="Prêt retenu (le plus faible)").font = Font(bold=True); w5.cell(row=9, column=2, value="=MIN(B7,B8)").number_format = MONEY
    w5.cell(row=10, column=1, value="Service de la dette annuel"); w5.cell(row=10, column=2, value="=B9*B6").number_format = MONEY
    w5.cell(row=11, column=1, value="RCD"); w5.cell(row=11, column=2, value="=B4/B10").number_format = "0.000"
    w5.cell(row=12, column=1, value="RPV"); w5.cell(row=12, column=2, value="=B9/B3").number_format = PCT
    w5.cell(row=13, column=1, value="Mise de fonds"); w5.cell(row=13, column=2, value="=B3-B9").number_format = MONEY
    w5.cell(row=14, column=1, value=f"Contrôle : prêt calculé par traite.cas3 = {round(f['pret'])} $ ({f['contrainte']})").font = NOTE
    w5.column_dimensions["A"].width = 50; w5.column_dimensions["B"].width = 18
    wb.save(OUT / "cas3_dcf_12_logements.xlsx")

def amortissement():
    wb = Workbook(); ws = wb.active; ws.title = "Calculateur"
    ws.cell(row=1, column=1, value="Calculateur d'hypothèque canadienne (composition semestrielle, Loi sur l'intérêt art. 6) — chapitre 31").font = Font(bold=True, size=12)
    ws.cell(row=3, column=1, value="Capital emprunté ($)"); ws.cell(row=3, column=2, value=300000).number_format = MONEY
    ws.cell(row=4, column=1, value="Taux nominal annuel"); ws.cell(row=4, column=2, value=0.05).number_format = PCT
    ws.cell(row=5, column=1, value="Amortissement (mois)"); ws.cell(row=5, column=2, value=300)
    ws.cell(row=6, column=1, value="Taux mensuel équivalent"); ws.cell(row=6, column=2, value="=(1+B4/2)^(1/6)-1").number_format = "0.0000000"
    ws.cell(row=7, column=1, value="Paiement mensuel ($)").font = Font(bold=True); ws.cell(row=7, column=2, value="=B3*B6/(1-(1+B6)^(-B5))").number_format = '#,##0.00 "$"'
    ws.cell(row=8, column=1, value="Contrôle (traite.finance) : 300 000 $ à 5 %% sur 25 ans = %.2f $" % fin.Hypotheque(300000, 0.05, 300).paiement).font = NOTE
    entete(ws, 10, ["Mois", "Paiement", "Intérêt", "Capital", "Solde"])
    ws.cell(row=11, column=1, value=0); ws.cell(row=11, column=5, value="=B3").number_format = MONEY
    for k in range(1, 361):
        r = 11 + k
        ws.cell(row=r, column=1, value=f'=IF(A{r-1}<$B$5,A{r-1}+1,"")')
        ws.cell(row=r, column=2, value=f'=IF(A{r}="","",$B$7)').number_format = MONEY
        ws.cell(row=r, column=3, value=f'=IF(A{r}="","",E{r-1}*$B$6)').number_format = MONEY
        ws.cell(row=r, column=4, value=f'=IF(A{r}="","",B{r}-C{r})').number_format = MONEY
        ws.cell(row=r, column=5, value=f'=IF(A{r}="","",MAX(E{r-1}-D{r},0))').number_format = MONEY
    ws.column_dimensions["A"].width = 30
    for c in "BCDE": ws.column_dimensions[c].width = 16
    wb.save(OUT / "amortissement_hypothecaire.xlsx")

if __name__ == "__main__":
    grille_cas1(); dcf_cas3(); amortissement()
    print("classeurs écrits dans spreadsheets/ :", sorted(p.name for p in OUT.glob("*.xlsx")))