from pathlib import Path
from datetime import datetime, date
from decimal import Decimal, ROUND_HALF_UP
import calendar, json
import xlsxwriter

ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / 'ESP_00VB_Livrables'
OUT.mkdir(exist_ok=True)
START = date(2026,9,1)
END = date(2027,8,31)
HIST = date(2025,9,1)
TODAY = date(2026,9,14)
SERVICES = [('Juridique','Moumadou N’Tougu',180,80),('Psychologie','Micheline Champagne',120,80),('Sexologie','Julie Grandmaison',100,80)]
CLIENTS = [
('C001','Camille','Exemple','Oui'),('C002','Alex','Démo','Oui'),
('C003','Claude','Essai','Oui'),('C004','Lou','Modèle','Oui'),
('C005','Dominique','Fictif','Oui'),('C006','Sacha','Témoin','Oui'),
('C007','Charlie','Test','Oui'),('C008','Maxime','Simulation','Oui'),
('C009','Robin','Échantillon','Non'),('C010','Noa','Prototype','Oui'),
('C011','Morgan','Vérification','Oui'),('C012','Alix','Démonstration','Oui')]
# ID, date, client, service, planned hours, actual hours, status, invoice status, invoice number.
ROWS = []
def rdv(day, client, service, hp=1, hr=1, status='Réalisé', state='Brouillon'):
    rid = f'R{len(ROWS)+1:03}'
    num = f'DEMO-{len(ROWS)+1:03}' if state=='Émise' else ''
    ROWS.append([rid,date.fromisoformat(day),client,service,hp,hr,status,state,num])
rdv('2025-08-31','C005','Sexologie',state='Émise')
rdv('2025-09-01','C001','Sexologie',state='Émise')
rdv('2026-08-31','C002','Sexologie',state='Émise')
rdv('2026-06-04','C003','Psychologie',state='Émise')
rdv('2026-07-06','C004','Juridique',state='Émise')
rdv('2026-08-04','C006','Sexologie',hr=0,status='Annulé')
rdv('2026-04-07','C007','Sexologie',state='Émise')
rdv('2026-04-09','C007','Psychologie',state='Émise')
rdv('2026-05-12','C008','Sexologie',state='Émise')
rdv('2026-08-18','C009','Sexologie',state='Émise')
rdv('2026-07-22','C010','Juridique',state='Émise')
rdv('2026-08-01','C011','Psychologie',state='Émise')
rdv('2026-08-20','C012','Sexologie',hp=1.5,hr=1.5,state='Émise')
rdv('2026-09-01','C001','Sexologie',state='Émise')
rdv('2026-09-02','C002','Sexologie',hp=1.5,hr=1.5,state='Émise')
rdv('2026-09-03','C003','Psychologie',state='Émise')
rdv('2026-09-04','C004','Juridique',hp=2,hr=2,state='Émise')
rdv('2026-09-05','C005','Sexologie')
rdv('2026-09-06','C006','Sexologie')
rdv('2026-09-07','C007','Psychologie')
rdv('2026-09-08','C007','Sexologie')
rdv('2026-09-09','C008','Sexologie',hr=0,status='Annulé')
rdv('2026-09-10','C012','Sexologie',hp=1,hr=1.25)
rdv('2026-09-18','C001','Sexologie',hr=0,status='Planifié')
rdv('2026-09-21','C003','Psychologie',hr=0,status='Planifié')
rdv('2026-09-25','C004','Juridique',hp=2,hr=0,status='Planifié')
rdv('2027-08-31','C001','Sexologie',hr=0,status='Planifié')
rdv('2027-09-01','C001','Sexologie',hr=0,status='Planifié')

def money(x): return float(Decimal(str(x)).quantize(Decimal('.01'), rounding=ROUND_HALF_UP))
def eligible(cid): return any(r[2]==cid and r[3]=='Sexologie' and r[6]=='Réalisé' and HIST<=r[1]<START for r in ROWS)
def computed(r):
    rid,day,cid,svc,hp,hr,status,state,no = r
    cli=next(c for c in CLIENTS if c[0]==cid)
    s=next(s for s in SERVICES if s[0]==svc)
    rate=.05 if status=='Réalisé' and svc=='Sexologie' and eligible(cid) and START<=day<=END else 0
    gross=money(hr*s[2]) if status=='Réalisé' else 0
    rebate=money(gross*rate)
    alert='Écart heures' if status=='Réalisé' and abs(hp-hr)>.001 else 'OK'
    return [rid,day,cid,cli[1]+' '+cli[2],svc,s[1],hp,hr,status,s[2], 'Oui' if eligible(cid) else 'Non',rate,gross,rebate,money(gross-rebate),state,no,hr if status=='Réalisé' else 0,alert]
DATA=[computed(r) for r in ROWS]

def build():
    wb=xlsxwriter.Workbook(OUT/'03_Gestion_organisme.xlsx')
    wb.set_properties({'title':'Gestion administrative — ESP 00VB','author':'Dossier de démonstration','comments':'Données entièrement fictives. Dates et tarifs hypothétiques.'})
    wb.set_calc_mode('auto')
    base={'font_name':'Calibri','font_size':11,'valign':'vcenter'}
    fmt={
      'title':wb.add_format({**base,'font_size':22,'bold':True,'font_color':'#16324F'}),
      'sub':wb.add_format({**base,'font_color':'#526477','text_wrap':True}),
      'head':wb.add_format({**base,'bg_color':'#16324F','font_color':'white','bold':True,'text_wrap':True,'border':1,'border_color':'#D9E2EC'}),
      'in':wb.add_format({**base,'bg_color':'#FFF2CC','locked':False}),
      'calc':wb.add_format({**base,'bg_color':'#EAF3F8'}),
      'txt':wb.add_format({**base,'text_wrap':True}),
      'date':wb.add_format({**base,'num_format':'yyyy-mm-dd','bg_color':'#FFF2CC','locked':False}),
      'datecalc':wb.add_format({**base,'num_format':'yyyy-mm-dd','bg_color':'#EAF3F8'}),
      'num':wb.add_format({**base,'num_format':'0.00','bg_color':'#EAF3F8'}),
      'numin':wb.add_format({**base,'num_format':'0.00','bg_color':'#FFF2CC','locked':False}),
      'money':wb.add_format({**base,'num_format':'#,##0.00 " $"','bg_color':'#EAF3F8'}),
      'pct':wb.add_format({**base,'num_format':'0.0%','bg_color':'#EAF3F8'}),
      'pctin':wb.add_format({**base,'num_format':'0.0%','bg_color':'#FFF2CC','locked':False}),
      'bad':wb.add_format({'bg_color':'#FCE4D6','font_color':'#9C0006'}),
      'good':wb.add_format({'bg_color':'#E2F0D9','font_color':'#375623'}),
      'link':wb.add_format({**base,'font_color':'#007D8A','underline':True}),
      'big':wb.add_format({**base,'bold':True,'font_size':20,'font_color':'#007D8A','num_format':'#,##0.00'}),
    }
    names=['Accueil','Parametres','Services','Clients','RendezVous','Messages','Portrait','Facture','Fusion','Tests']
    ws={n:wb.add_worksheet(n) for n in names}
    for sh in ws.values():
        sh.hide_gridlines(2); sh.set_default_row(22); sh.set_landscape(); sh.set_paper(1); sh.fit_to_pages(1,0)
        sh.set_margins(.3,.3,.45,.45); sh.set_footer('&LDémonstration — données fictives&RPage &P / &N')
    def head(sh,cols):
        sh.write_row(0,0,cols,fmt['head']);sh.set_row(0,36);sh.freeze_panes(1,0)
    def protect(sh): sh.protect('',{'autofilter':True,'sort':True,'select_locked_cells':True,'select_unlocked_cells':True})
    def val(sh,rng,opts):
        sh.data_validation(rng,{'error_type':'stop','show_error':True,'error_title':'Valeur à corriger','error_message':'Utilisez une valeur autorisée; consultez le guide.',**opts})
    # Paramètres : adresses stables référencées dans les autres fichiers.
    sh=ws['Parametres']; sh.set_column('A:A',36);sh.set_column('B:B',25);sh.set_column('C:C',75)
    head(sh,['Paramètre','Valeur','Règle'])
    pars=[('Début de la promotion',START,'Hypothèse pédagogique à confirmer.'),('Fin de la promotion',END,'Douze mois; dernière journée incluse.'),('Début de l’historique',HIST,'Première journée d’admissibilité incluse.'),('Taux du rabais',.05,'Appliqué seulement aux consultations de sexologie admissibles.'),('Année du bilan',2026,'Douze mois civils.'),('Date de référence',TODAY,'Date fixe pour reproduire les essais; actualiser quotidiennement.'),('Mois du portrait',date(2026,9,1),'Saisir le premier jour du mois.'),('Coordonnées organisme','[À compléter]','Adresse, téléphone et courriel réels requis avant utilisation.'),('Capacité du prototype',300,'RendezVous et Messages : lignes 2 à 301; Clients : lignes 2 à 51.')]
    for i,(a,b,c) in enumerate(pars,1):
        sh.write(i,0,a,fmt['txt']);sh.write(i,2,c,fmt['txt'])
        if isinstance(b,date): sh.write_datetime(i,1,datetime.combine(b,datetime.min.time()),fmt['date'])
        else:sh.write(i,1,b,fmt['pctin'] if i==4 else fmt['in'])
    val(sh,'B2',{'validate':'date','criteria':'between','minimum':datetime(2020,1,1),'maximum':datetime(2040,12,31)})
    val(sh,'B3',{'validate':'custom','value':'=AND(ISNUMBER(B3),B3>=B2)'})
    val(sh,'B4',{'validate':'custom','value':'=AND(ISNUMBER(B4),B4<B2)'})
    val(sh,'B5',{'validate':'decimal','criteria':'between','minimum':0,'maximum':1})
    val(sh,'B6',{'validate':'integer','criteria':'between','minimum':2020,'maximum':2040})
    val(sh,'B7:B8',{'validate':'date','criteria':'between','minimum':datetime(2020,1,1),'maximum':datetime(2040,12,31)})
    # Catalogue et listes de validation.
    sh=ws['Services'];head(sh,['Service','Professionnel','Tarif démo / h','Capacité / mois (h)','Statut RDV','État facture','État message','Oui / Non'])
    for i,s in enumerate(SERVICES,1):sh.write_row(i,0,s,fmt['in'])
    for col,li in [(4,['Planifié','Réalisé','Annulé','Absent']),(5,['Brouillon','Émise']),(6,['À transmettre','Transmis','Traité']),(7,['Oui','Non'])]:
        for i,v in enumerate(li,1):sh.write(i,col,v,fmt['calc'])
    sh.set_column('A:A',18);sh.set_column('B:B',28);sh.set_column('C:H',21)
    for n,ref in [('ListeServices','Services!$A$2:$A$4'),('ListeStatuts','Services!$E$2:$E$5'),('ListeFactures','Services!$F$2:$F$3'),('ListeMessages','Services!$G$2:$G$4'),('OuiNon','Services!$H$2:$H$3'),('ListeClients','Clients!$A$2:$A$51'),('ListeRdv','RendezVous!$A$2:$A$301')]:wb.define_name(n,'='+ref)
    val(sh,'C2:D4',{'validate':'decimal','criteria':'>=','value':0})
    # Clients : un identifiant par personne, admissibilité calculée depuis l’historique.
    sh=ws['Clients'];cols=['ID_Client','Prenom','Nom','Adresse','Ville','Province','Code_postal','Telephone','Courriel','Actuel','Admissible','Nom_complet','Controle']
    head(sh,cols);sh.set_column('A:C',16);sh.set_column('D:I',23);sh.set_column('J:K',16);sh.set_column('L:M',27)
    for i in range(1,51):
        r=i+1;c=CLIENTS[i-1] if i<=len(CLIENTS) else None
        if c:sh.write_row(i,0,[c[0],c[1],c[2],f'{100+i}, rue Démonstration','Ville Exemple','QC','H0H 0H0','[Téléphone démo]',f'client{i:02}@example.invalid',c[3]],fmt['in'])
        else:sh.write_blank(i,0,None,fmt['in']); [sh.write_blank(i,j,None,fmt['in']) for j in range(1,10)]
        f=f'=IF(A{r}="","",IF(COUNTIFS(RendezVous!$C$2:$C$301,A{r},RendezVous!$E$2:$E$301,"Sexologie",RendezVous!$I$2:$I$301,"Réalisé",RendezVous!$B$2:$B$301,">="&Parametres!$B$4,RendezVous!$B$2:$B$301,"<"&Parametres!$B$2)>0,"Oui","Non"))'
        sh.write_formula(i,10,f,fmt['calc'],('Oui' if eligible(c[0]) else 'Non') if c else '')
        sh.write_formula(i,11,f'=IF(A{r}="","",B{r}&" "&C{r})',fmt['calc'],c[1]+' '+c[2] if c else '')
        sh.write_formula(i,12,f'=IF(A{r}="","",IF(COUNTIF($A$2:$A$51,A{r})>1,"DOUBLON ID",IF(OR(B{r}="",C{r}="",D{r}="",E{r}="",G{r}=""),"Adresse incomplète","OK")))',fmt['calc'],'OK' if c else '')
    val(sh,'A2:A51',{'validate':'custom','value':'=OR(A2="",COUNTIF($A$2:$A$51,A2)=1)'})
    val(sh,'J2:J51',{'validate':'list','source':'=OuiNon'})
    sh.conditional_format('A2:M51',{'type':'formula','criteria':'=AND($A2<>"",$M2<>"OK")','format':fmt['bad']})
    sh.autofilter('A1:M51');protect(sh)
    # Journal unique des rendez-vous et facturation.
    sh=ws['RendezVous'];cols=['ID_Rdv','Date','ID_Client','Client','Service','Professionnel','Heures_prevues','Heures_reelles','Statut','Tarif_horaire','Admissible','Taux_rabais','Brut_HT','Rabais','Net_HT','Etat_facture','No_facture','Heures_facturables','Alerte','Justification_ecart']
    head(sh,cols);sh.set_column('A:C',14);sh.set_column('D:F',26);sh.set_column('G:R',17);sh.set_column('S:S',28);sh.set_column('T:T',48);sh.freeze_panes(1,3)
    for i in range(1,301):
        r=i+1;d=DATA[i-1] if i<=len(DATA) else None
        sh.write_blank(i,19,None,fmt['in'])
        for col in [0,1,2,4,6,7,8,15,16]:
            if d:
                if col==1:sh.write_datetime(i,col,datetime.combine(d[col],datetime.min.time()),fmt['date'])
                else:sh.write(i,col,d[col],fmt['numin'] if col in [6,7] else fmt['in'])
            else:sh.write_blank(i,col,None,fmt['date'] if col==1 else fmt['in'])
        fs={
        3:f'=IF(C{r}="","",IFERROR(VLOOKUP(C{r},Clients!$A$2:$M$51,12,FALSE),"CLIENT INCONNU"))',
        5:f'=IF(E{r}="","",IFERROR(VLOOKUP(E{r},Services!$A$2:$D$4,2,FALSE),"SERVICE INCONNU"))',
        9:f'=IF(E{r}="","",IFERROR(VLOOKUP(E{r},Services!$A$2:$D$4,3,FALSE),0))',
        10:f'=IF(C{r}="","",IFERROR(VLOOKUP(C{r},Clients!$A$2:$M$51,11,FALSE),"Non"))',
        11:f'=IF(A{r}="","",IF(I{r}<>"Réalisé",0,IF(E{r}<>"Sexologie",0,IF(K{r}<>"Oui",0,IF(AND(B{r}>=Parametres!$B$2,B{r}<=Parametres!$B$3),Parametres!$B$5,0)))))',
        12:f'=IF(A{r}="","",IF(I{r}="Réalisé",ROUND(H{r}*J{r},2),0))',
        13:f'=IF(A{r}="","",ROUND(M{r}*L{r},2))',
        14:f'=IF(A{r}="","",ROUND(M{r}-N{r},2))',
        17:f'=IF(A{r}="","",IF(I{r}="Réalisé",H{r},0))',
        18:f'=IF(A{r}="","",IF(COUNTIF($A$2:$A$301,A{r})>1,"DOUBLON RDV",IF(COUNTIF(Clients!$A$2:$A$51,C{r})<>1,"Client à corriger",IF(COUNTIF(Services!$A$2:$A$4,E{r})<>1,"Service à corriger",IF(OR(NOT(ISNUMBER(B{r})),G{r}<=0,H{r}<0,COUNTIF(ListeStatuts,I{r})<>1,COUNTIF(ListeFactures,P{r})<>1),"Saisie à corriger",IF(AND(I{r}="Réalisé",OR(H{r}<=0,B{r}>Parametres!$B$7)),"Réalisation à vérifier",IF(AND(P{r}="Émise",OR(Q{r}="",I{r}<>"Réalisé")),"Facture à corriger",IF(AND(Q{r}<>"",COUNTIF($Q$2:$Q$301,Q{r})>1),"DOUBLON FACTURE",IF(AND(I{r}="Réalisé",ABS(G{r}-H{r})>0.001),"Écart heures","OK")))))))))'
        }
        fs[18]=fs[18].replace(f'ABS(G{r}-H{r})>0.001)',f'ABS(G{r}-H{r})>0.001,T{r}="")')
        for col,f in fs.items():sh.write_formula(i,col,f,fmt['pct'] if col==11 else fmt['money'] if col in [9,12,13,14] else fmt['num'] if col==17 else fmt['calc'],d[col] if d else '')
    val(sh,'A2:A301',{'validate':'custom','value':'=OR(A2="",COUNTIF($A$2:$A$301,A2)=1)'})
    val(sh,'B2:B301',{'validate':'date','criteria':'between','minimum':datetime(2020,1,1),'maximum':datetime(2040,12,31)})
    val(sh,'C2:C301',{'validate':'list','source':'=ListeClients'})
    val(sh,'E2:E301',{'validate':'list','source':'=ListeServices'})
    val(sh,'G2:G301',{'validate':'decimal','criteria':'between','minimum':.25,'maximum':12})
    val(sh,'H2:H301',{'validate':'decimal','criteria':'between','minimum':0,'maximum':12})
    val(sh,'I2:I301',{'validate':'list','source':'=ListeStatuts'})
    val(sh,'P2:P301',{'validate':'list','source':'=ListeFactures'})
    val(sh,'Q2:Q301',{'validate':'custom','value':'=OR(Q2="",COUNTIF($Q$2:$Q$301,Q2)=1)'})
    sh.conditional_format('A2:T301',{'type':'formula','criteria':'=AND($A2<>"",$S2<>"OK")','format':fmt['bad']})
    sh.conditional_format('L2:L301',{'type':'cell','criteria':'>','value':0,'format':fmt['good']})
    sh.autofilter('A1:T301');sh.print_area('A1:T29');sh.repeat_rows(0);protect(sh)
    # Messages : suivi administratif, sans notes cliniques.
    sh=ws['Messages'];head(sh,['ID_Message','Date_recue','Nom / ID client','Telephone','Service','Responsable','Objet administratif','Statut','Date_transmission','Date_traitement','Alerte'])
    sh.set_column('A:E',19);sh.set_column('F:G',29);sh.set_column('H:K',23)
    msgs=[['M001',date(2026,9,10),'C001','[Téléphone démo]','Sexologie','Demande de rappel','À transmettre',None,None],['M002',date(2026,9,13),'C003','[Téléphone démo]','Psychologie','Déplacer un rendez-vous','Transmis',date(2026,9,13),None],['M003',date(2026,9,11),'C004','[Téléphone démo]','Juridique','Confirmer une disponibilité','Traité',date(2026,9,11),date(2026,9,12)]]
    for i in range(1,301):
        r=i+1;m=msgs[i-1] if i<=len(msgs) else None
        for ci,mi in [(0,0),(1,1),(2,2),(3,3),(4,4),(6,5),(7,6),(8,7),(9,8)]:
            value=m[mi] if m else None
            if isinstance(value,date):sh.write_datetime(i,ci,datetime.combine(value,datetime.min.time()),fmt['date'])
            elif value is not None:sh.write(i,ci,value,fmt['in'])
            else:sh.write_blank(i,ci,None,fmt['in'])
        sh.write_formula(i,5,f'=IF(E{r}="","",IFERROR(VLOOKUP(E{r},Services!$A$2:$D$4,2,FALSE),"Service à corriger"))',fmt['calc'],next(s[1] for s in SERVICES if s[0]==m[4]) if m else '')
        ff=f'=IF(A{r}="","",IF(COUNTIF($A$2:$A$301,A{r})>1,"DOUBLON",IF(OR(B{r}="",C{r}="",D{r}="",COUNTIF(ListeServices,E{r})<>1,G{r}="",COUNTIF(ListeMessages,H{r})<>1),"Saisie incomplète",IF(OR(AND(I{r}<>"",I{r}<B{r}),AND(J{r}<>"",OR(I{r}="",J{r}<I{r})),B{r}>Parametres!$B$7),"Dates à corriger",IF(AND(H{r}="Traité",OR(I{r}="",J{r}="")),"Dates manquantes",IF(AND(H{r}="Transmis",I{r}=""),"Transmission manquante",IF(AND(H{r}<>"Traité",Parametres!$B$7-B{r}>1),"À relancer","OK")))))))'
        sh.write_formula(i,10,ff,fmt['calc'],['À relancer','OK','OK'][i-1] if m else '')
    val(sh,'E2:E301',{'validate':'list','source':'=ListeServices'});val(sh,'H2:H301',{'validate':'list','source':'=ListeMessages'})
    val(sh,'A2:A301',{'validate':'custom','value':'=OR(A2="",COUNTIF($A$2:$A$301,A2)=1)'})
    for rg in ['B2:B301','I2:J301']:val(sh,rg,{'validate':'date','criteria':'between','minimum':datetime(2020,1,1),'maximum':datetime(2040,12,31)})
    sh.conditional_format('A2:K301',{'type':'formula','criteria':'=AND($A2<>"",$K2<>"OK")','format':fmt['bad']});sh.autofilter('A1:K301');sh.print_area('A1:K4');protect(sh)
    # Portrait : bilan mensuel et occupation, source exacte des champs Word.
    sh=ws['Portrait'];sh.set_column('A:A',20);sh.set_column('B:H',18)
    sh.merge_range('A1:H1','PORTRAIT DES ACTIVITÉS',fmt['title'])
    sh.merge_range('A2:H2','Données fictives • heures réalisées distinctes des réservations • montants hors taxes',fmt['sub'])
    sh.write('A3','Année',fmt['head']);sh.write_formula('B3','=Parametres!B6',fmt['calc'],2026)
    sh.write('D3','Mois analysé',fmt['head']);sh.write_formula('E3','=Parametres!B8',fmt['datecalc'],46266)
    sh.write_row('A5',['Mois','H. juridique','H. psychologie','H. sexologie','H. réalisées','Brut HT','Rabais','Net HT'],fmt['head'])
    for i in range(12):
        r=i+6;month=i+1
        serial=(date(2026,month,1)-date(1899,12,30)).days
        sh.write_formula(r-1,0,f'=DATE(Parametres!$B$6,{month},1)',fmt['datecalc'],serial)
        md=[d for d in DATA if d[1].year==2026 and d[1].month==month]
        for j,svc in enumerate(['Juridique','Psychologie','Sexologie'],1):
            f=f'=SUMIFS(RendezVous!$R$2:$R$301,RendezVous!$B$2:$B$301,">="&$A{r},RendezVous!$B$2:$B$301,"<"&DATE(YEAR($A{r}),MONTH($A{r})+1,1),RendezVous!$E$2:$E$301,"{svc}")'
            sh.write_formula(r-1,j,f,fmt['num'],sum(d[17] for d in md if d[4]==svc))
        sh.write_formula(r-1,4,f'=SUM(B{r}:D{r})',fmt['num'],sum(d[17] for d in md))
        for j,scol,dcol in [(5,'M',12),(6,'N',13),(7,'O',14)]:
            sh.write_formula(r-1,j,f'=SUMIFS(RendezVous!${scol}$2:${scol}$301,RendezVous!$B$2:$B$301,">="&$A{r},RendezVous!$B$2:$B$301,"<"&DATE(YEAR($A{r}),MONTH($A{r})+1,1))',fmt['money'],money(sum(d[dcol] for d in md)))
    sh.write('A18','TOTAL ANNUEL',fmt['head'])
    for j in range(1,8):
        letter=xlsxwriter.utility.xl_col_to_name(j);cached=sum(d[17] for d in DATA if d[1].year==2026 and d[4]==SERVICES[j-1][0]) if j<=3 else sum(d[17 if j==4 else j+7] for d in DATA if d[1].year==2026)
        sh.write_formula(17,j,f'=SUM({letter}6:{letter}17)',fmt['num'] if j<5 else fmt['money'],money(cached))
    sh.write_row('A21',['Service','Capacité (h)','Réservées (h)','Réalisées (h)','Occupation','Disponibles (h)','Orientation'],fmt['head'])
    for i,(svc,prof,rate,cap) in enumerate(SERVICES,21):
        r=i+1;md=[d for d in DATA if d[1].year==2026 and d[1].month==9 and d[4]==svc]
        planned=sum(d[6] for d in md if d[8] not in ['Annulé','Absent']);actual=sum(d[17] for d in md)
        sh.write(i,0,svc,fmt['txt']);sh.write_formula(i,1,f'=VLOOKUP(A{r},Services!$A$2:$D$4,4,FALSE)',fmt['num'],cap)
        sh.write_formula(i,2,f'=SUMIFS(RendezVous!$G$2:$G$301,RendezVous!$E$2:$E$301,A{r},RendezVous!$B$2:$B$301,">="&Parametres!$B$8,RendezVous!$B$2:$B$301,"<"&DATE(YEAR(Parametres!$B$8),MONTH(Parametres!$B$8)+1,1),RendezVous!$I$2:$I$301,"<>Annulé",RendezVous!$I$2:$I$301,"<>Absent")',fmt['num'],planned)
        sh.write_formula(i,3,f'=SUMIFS(RendezVous!$R$2:$R$301,RendezVous!$E$2:$E$301,A{r},RendezVous!$B$2:$B$301,">="&Parametres!$B$8,RendezVous!$B$2:$B$301,"<"&DATE(YEAR(Parametres!$B$8),MONTH(Parametres!$B$8)+1,1))',fmt['num'],actual)
        sh.write_formula(i,4,f'=IFERROR(C{r}/B{r},0)',fmt['pct'],planned/cap)
        sh.write_formula(i,5,f'=MAX(0,B{r}-C{r})',fmt['num'],max(0,cap-planned))
        sh.write_formula(i,6,f'=IF(B{r}<=0,"Capacité à valider",IF(E{r}>=1,"Complet",IF(E{r}>=0.9,"Vérifier les créneaux","Promotion possible")))',fmt['txt'],'Promotion possible')
    sh.conditional_format('E22:E24',{'type':'data_bar','bar_color':'#007D8A','min_type':'num','min_value':0,'max_type':'num','max_value':1})
    sh.merge_range('A26:H26','La capacité mensuelle ne garantit pas un créneau précis : consulter les dates de RendezVous avant une promotion.',fmt['sub'])
    sh.write('A28','Contrôle RDV',fmt['head']);sh.write_formula('B28','=COUNTIFS(RendezVous!$A$2:$A$301,"<>",RendezVous!$S$2:$S$301,"<>OK")',fmt['calc'],1)
    sh.write('D28','Messages à revoir',fmt['head']);sh.write_formula('E28','=COUNTIFS(Messages!$A$2:$A$301,"<>",Messages!$K$2:$K$301,"<>OK")',fmt['calc'],1)
    sh.write('A30','H. émises (année)',fmt['head']);sh.write_formula('B30','=SUMIFS(RendezVous!$R$2:$R$301,RendezVous!$P$2:$P$301,"Émise",RendezVous!$B$2:$B$301,">="&DATE(Parametres!$B$6,1,1),RendezVous!$B$2:$B$301,"<"&DATE(Parametres!$B$6+1,1,1))',fmt['num'],sum(d[17] for d in DATA if d[1].year==2026 and d[15]=='Émise'))
    sh.write('D30','H. à facturer',fmt['head']);sh.write_formula('E30','=E18-B30',fmt['num'],sum(d[17] for d in DATA if d[1].year==2026 and d[15]!='Émise'))
    sh.merge_range('A32:H32','Les revenus du portrait représentent les prestations réalisées, y compris les brouillons restant à facturer.',fmt['sub'])
    chart=wb.add_chart({'type':'column','subtype':'stacked'})
    for j in range(1,4):chart.add_series({'name':['Portrait',4,j],'categories':['Portrait',5,0,16,0],'values':['Portrait',5,j,16,j],'fill':{'color':['#16324F','#007D8A','#E0AC46'][j-1]}})
    chart.set_title({'name':'Heures réalisées par mois et par service'});chart.set_x_axis({'num_format':'mmm'});chart.set_y_axis({'name':'Heures'});chart.set_legend({'position':'bottom'});chart.set_size({'width':800,'height':310});sh.insert_chart('A35',chart)
    sh.print_area('A1:H32');sh.fit_to_pages(1,1);protect(sh)
    # Facture : un rendez-vous sélectionné, aucun montant ressaisi.
    sh=ws['Facture'];sh.set_portrait();sh.set_column('A:A',3);sh.set_column('B:B',30);sh.set_column('C:C',38);sh.set_column('D:F',13)
    sh.merge_range('B2:F2','FACTURE — DÉMONSTRATION',fmt['title'])
    sh.merge_range('B3:F3','Société d’accueil social et communautaire de ressources adaptées et personnelles',fmt['sub'])
    sh.merge_range('B4:F4','[Adresse, téléphone et courriel de l’organisme à compléter]',fmt['sub'])
    sh.write('B6','Rendez-vous à afficher',fmt['head']);sh.write('C6','R014',fmt['in']);val(sh,'C6',{'validate':'list','source':'=ListeRdv'})
    d=DATA[13]
    mappings=[(8,'Numéro de facture',17),(9,'Date du service',2),(10,'Client',4),(11,'Service',5),(12,'Professionnel',6),(14,'Heures réalisées',8),(15,'Tarif horaire',10),(17,'Montant brut HT',13),(18,'Rabais applicable',12),(19,'Montant du rabais',14),(21,'TOTAL HORS TAXES',15),(23,'État de la facture',16),(24,'Contrôle de la saisie',19)]
    for r,label,index in mappings:
        sh.write(r-1,1,label,fmt['head'] if r==21 else fmt['txt'])
        f=f'=IFERROR(VLOOKUP($C$6,RendezVous!$A$2:$S$301,{index},FALSE),"")'
        value=d[index-1];ff=fmt['datecalc'] if r==9 else fmt['pct'] if r==18 else fmt['money'] if r in [15,17,19,21] else fmt['calc']
        if isinstance(value,date):value=(value-date(1899,12,30)).days
        sh.write_formula(r-1,2,f,ff,value)
    sh.merge_range('B26:F26','Montants de simulation hors taxes. Paramétrage fiscal requis avant une émission réelle.',fmt['sub'])
    sh.merge_range('B28:F28','Contrôler le client, les heures et le numéro unique; conserver un PDF figé de toute facture émise.',fmt['sub'])
    sh.write('B30','Autorisation technique',fmt['txt']);sh.write_formula('C30','=IF($C$6="","Choisir un rendez-vous",IFERROR(IF(AND(VLOOKUP($C$6,RendezVous!$A$2:$S$301,9,FALSE)="Réalisé",C24="OK"),"Prête à contrôler","Émission bloquée"),"Rendez-vous inconnu"))',fmt['calc'],'Prête à contrôler')
    sh.print_area('B2:F30');sh.fit_to_pages(1,1);protect(sh)
    # Source unique de publipostage : une ligne par client, règle Word sur Admissible.
    sh=ws['Fusion'];cols=['ID_Client','Prenom','Nom','Adresse','Ville','Province','Code_postal','Admissible','Envoyer','Debut_promotion','Fin_promotion']
    head(sh,cols);sh.set_column('A:C',17);sh.set_column('D:G',25);sh.set_column('H:K',20)
    maps={0:'A',1:'B',2:'C',3:'D',4:'E',5:'F',6:'G',7:'K'}
    for i in range(1,51):
        r=i+1;c=CLIENTS[i-1] if i<=12 else None
        vals=[c[0],c[1],c[2],f'{100+i}, rue Démonstration','Ville Exemple','QC','H0H 0H0','Oui' if eligible(c[0]) else 'Non'] if c else ['']*8
        for col,sc in maps.items():sh.write_formula(i,col,f'=IF(Clients!A{r}="","",Clients!{sc}{r})',fmt['calc'],vals[col])
        send='Oui' if c and (c[3]=='Oui' or eligible(c[0])) else 'Non' if c else ''
        sh.write_formula(i,8,f'=IF(A{r}="","",IF(AND(Clients!M{r}="OK",OR(Clients!J{r}="Oui",H{r}="Oui")),"Oui","Non"))',fmt['calc'],send)
        for col,pc,v in [(9,2,'2026-09-01'),(10,3,'2027-08-31')]:sh.write_formula(i,col,f'=IF(A{r}="","",TEXT(Parametres!$B${pc},"yyyy-mm-dd"))',fmt['calc'],v if c else '')
    sh.autofilter('A1:K51');protect(sh)
    # Jeu de tests explicite et recalculable, y compris dates limites hors du journal réel.
    sh=ws['Tests'];head(sh,['Test','Historique admissible','Service','Date du service','Statut','Heures','Tarif','Taux attendu','Taux calculé','Net attendu','Net calculé','Résultat'])
    sh.set_column('A:A',36);sh.set_column('B:L',18)
    cases=[('T01 début inclus','Oui','Sexologie',START,'Réalisé',1,100,.05,95),('T02 veille du début','Oui','Sexologie',date(2026,8,31),'Réalisé',1,100,0,100),('T03 fin incluse','Oui','Sexologie',END,'Réalisé',1,100,.05,95),('T04 après la fin','Oui','Sexologie',date(2027,9,1),'Réalisé',1,100,0,100),('T05 autre service','Oui','Psychologie',START,'Réalisé',1,120,0,120),('T06 nouveau client','Non','Sexologie',START,'Réalisé',1,100,0,100),('T07 annulation','Oui','Sexologie',START,'Annulé',1,100,0,0),('T08 réservation','Oui','Sexologie',START,'Planifié',1,100,0,0),('T09 arrondi','Oui','Sexologie',START,'Réalisé',1.25,100,.05,118.75)]
    for i,c in enumerate(cases,1):
        r=i+1
        for col,v in enumerate(c[:8]):
            if isinstance(v,date):sh.write_datetime(i,col,datetime.combine(v,datetime.min.time()),fmt['date'])
            else:sh.write(i,col,v,fmt['pctin'] if col==7 else fmt['in'])
        sh.write_formula(i,8,f'=IF(E{r}<>"Réalisé",0,IF(C{r}<>"Sexologie",0,IF(B{r}<>"Oui",0,IF(AND(D{r}>=Parametres!$B$2,D{r}<=Parametres!$B$3),Parametres!$B$5,0))))',fmt['pct'],c[7])
        sh.write(i,9,c[8],fmt['money']);sh.write_formula(i,10,f'=IF(E{r}="Réalisé",ROUND(F{r}*G{r},2)-ROUND(ROUND(F{r}*G{r},2)*I{r},2),0)',fmt['money'],c[8]);sh.write_formula(i,11,f'=IF(AND(ABS(H{r}-I{r})<0.000001,ABS(J{r}-K{r})<0.005),"RÉUSSI","ÉCHEC")',fmt['calc'],'RÉUSSI')
    sh.merge_range('A13:L13','Ces tests vérifient le moteur de calcul. Les essais Word, la coédition et la répétition orale figurent dans le rapport.',fmt['sub'])
    sh.conditional_format('L2:L10',{'type':'text','criteria':'containing','value':'RÉUSSI','format':fmt['good']});sh.print_area('A1:L13')
    # Accueil and navigation.
    sh=ws['Accueil'];sh.set_column('A:A',4);sh.set_column('B:B',30);sh.set_column('C:H',15)
    sh.merge_range('B2:H2','UN BUREAU MIEUX COORDONNÉ',fmt['title'])
    sh.merge_range('B4:H5','Classeur de démonstration • ESP 00VB\nSaisir dans les cellules jaunes. Les cellules bleues se calculent automatiquement.',fmt['sub'])
    for r,(n,desc) in enumerate([('Parametres','Dates, taux et période du bilan'),('Clients','Fichier maître et admissibilité historique'),('RendezVous','Agenda administratif, heures et facturation'),('Messages','Réception, transmission et suivi'),('Portrait','Indicateurs mensuels et occupation'),('Facture','Facture calculée à partir d’un rendez-vous'),('Fusion','Destinataires de la lettre anniversaire'),('Tests','Neuf cas de calcul reproductibles')],7):
        sh.write_url(r,1,f"internal:'{n}'!A1",fmt['link'],n);sh.merge_range(r,2,r,7,desc,fmt['txt'])
    sh.merge_range('B18:H19','Hypothèses : promotion du 1er septembre 2026 au 31 août 2027; historique du 1er septembre 2025 au 31 août 2026. Tous les clients et montants sont fictifs.',fmt['sub'])
    sh.merge_range('B21:H22','Les alertes repèrent les erreurs; la technicienne les résout avant validation. Les messages sont consultés dans le registre : aucune notification n’est envoyée automatiquement.',fmt['sub'])
    sh.merge_range('B24:H25','Pour travailler simultanément : déployer une copie .xlsx dans l’espace Microsoft 365 autorisé et effectuer un essai à deux comptes. Une copie locale ne prouve pas la coédition.',fmt['sub'])
    sh.merge_range('B27:H28','Capacité : 50 clients, 300 rendez-vous et 300 messages. Extension des plages et nouveau test requis avant dépassement. Protection sans mot de passe contre les erreurs de saisie.',fmt['sub'])
    sh.print_area('B2:H28');sh.fit_to_pages(1,1)
    for n,area in [('Parametres','A1:C10'),('Services','A1:H5'),('Clients','A1:M13'),('Fusion','A1:K13')]:ws[n].print_area(area)
    for n in names[1:]:
        # Link outside the data ranges to preserve mail-merge schemas.
        ws[n].write_url(0,21,"internal:'Accueil'!B2",fmt['link'],'Retour à l’accueil')
    wb.close()
    year=[d for d in DATA if d[1].year==2026]
    summary={'clients':len(CLIENTS),'eligible':[c[0] for c in CLIENTS if eligible(c[0])],'rdv':len(DATA),'year_hours':sum(d[17] for d in year),'year_gross':money(sum(d[12] for d in year)),'year_discount':money(sum(d[13] for d in year)),'year_net':money(sum(d[14] for d in year)), 'september_hours':sum(d[17] for d in year if d[1].month==9),'september_net':money(sum(d[14] for d in year if d[1].month==9))}
    (ROOT/'_fabrication_00VB'/'summary.json').write_text(json.dumps(summary,ensure_ascii=False,indent=2),encoding='utf-8')
    print(json.dumps(summary,ensure_ascii=False))

if __name__=='__main__':build()