#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Radish - Ciqual 2025 : part des cases "vitamines et mineraux" sans valeur (tiret), par constituant et par groupe.

Methode : voir https://nutri-track.fr/blog/ciqual-2025-vitamines-mineraux-valeurs-manquantes.html
Method  : see https://nutri-track.fr/blog/ciqual-2025-vitamines-mineraux-valeurs-manquantes-en.html

Usage   : python3 ciqual-2025-lacunes.py "Table Ciqual 2025_FR_2025_11_03.xlsx" dossier_de_sortie
          (Python 3, bibliotheque standard seule. Ecrit les 2 CSV fr/en + res.json.)
Entree  : fichier Excel officiel Anses, DOI 10.57745/RDMHWY (version du 19/11/2025),
          MD5 0d9758ce23f3f13dd63a005bc1bb4f2c. Le script s'arrete sur toute forme de cellule inconnue.

Licence : script et resultats sous Licence Ouverte Etalab 2.0. Les donnees sources sont celles de l'Anses
          (Table de composition nutritionnelle des aliments Ciqual 2025, Licence Ouverte Etalab 2.0).
          Les calculs sont de Radish ; l'Anses ne les a ni valides ni approuves.
Cite    : "Radish, d'apres Anses, Table de composition nutritionnelle des aliments Ciqual 2025,
          https://doi.org/10.57745/RDMHWY (version du 19/11/2025), calculs du 16/11/2026."
License : script and output under the Etalab Open Licence 2.0; source data (c) Anses, same licence;
          calculations by Radish, not validated or endorsed by Anses.
"""
import sys,re,csv,json,collections,os
import zipfile
import xml.etree.ElementTree as ET
NS={'m':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
def col(ref):
    s=re.match(r'[A-Z]+',ref).group();n=0
    for c in s:n=n*26+ord(c)-64
    return n-1
def lire(path):
    z=zipfile.ZipFile(path)
    ss=[]
    for si in ET.fromstring(z.read('xl/sharedStrings.xml')).findall('m:si',NS):
        ss.append(''.join(t.text or '' for t in si.iter('{%s}t'%NS['m'])))
    wb=ET.fromstring(z.read('xl/workbook.xml'))
    names=[s.get('name') for s in wb.find('m:sheets',NS)]
    out={}
    for i,n in enumerate(names,1):
        rows=[]
        for r in ET.fromstring(z.read(f'xl/worksheets/sheet{i}.xml')).iter('{%s}row'%NS['m']):
            d={}
            for c in r.findall('m:c',NS):
                v=c.find('m:v',NS);t=c.get('t')
                if t=='inlineStr':
                    val=''.join(x.text or '' for x in c.iter('{%s}t'%NS['m']))
                elif v is None: val=None
                elif t=='s': val=ss[int(v.text)]
                else: val=v.text
                d[col(c.get('r'))]=val
            rows.append(d)
        out[n]=rows
    return out

# --- calcul ---
src,out=sys.argv[1],sys.argv[2]
os.makedirs(out,exist_ok=True)
o=lire(src)
r=o['composition nutritionnelle']; h=r[0]; rows=r[1:]
norm=lambda s:re.sub(r'[^0-9A-Za-zµ]','',s or '')
inf={norm(x[2]):(x[0],x[1]) for x in o['codes INFOODS'][1:]}
EN={50:'Calcium',51:'Chloride',52:'Copper',53:'Iron',54:'Iodine',55:'Magnesium',56:'Manganese',57:'Phosphorus',58:'Potassium',59:'Selenium',60:'Sodium',61:'Zinc',
62:'Vitamin A activity (retinol equivalents)',63:'Retinol',64:'Beta-carotene',65:'Vitamin D',66:'Vitamin D2 (ergocalciferol)',67:'Vitamin D3 (cholecalciferol)',68:'Alpha-tocopherol (vitamin E)',69:'Vitamin E',70:'Vitamin K1',71:'Vitamin K2',72:'Vitamin C',73:'Vitamin B1 (thiamin)',74:'Vitamin B2 (riboflavin)',75:'Vitamin B3 (niacin)',76:'Vitamin B5 (pantothenic acid)',77:'Vitamin B6',78:'Vitamin B9, total folates in DFE',79:'Vitamin B9, total folates',80:'Intrinsic folates',81:'Folic acid (fortification)',82:'Vitamin B12'}
GEN={'viandes, oeufs, poissons':'meat, eggs, fish','fruits, légumes, légumineuses et oléagineux':'fruit, vegetables, legumes and oilseeds','entrées et plats composés':'starters and composite dishes','produits sucrés':'sugary products','produits laitiers':'dairy products','eaux et autres boissons':'water and other beverages','aides culinaires et ingrédients divers':'culinary aids and miscellaneous ingredients','produits céréaliers':'cereal products','matières grasses':'fats and oils','aliments infantiles':'infant foods','glaces et sorbets':'ice creams and sorbets'}
def cls(v):
    if v is None: return 'absent'   # cellule vide
    if v=='-': return 'absent'
    if v=='traces': return 'traces'
    if re.match(r'<',v): return 'inf'
    if re.fullmatch(r'\d+([.,]\d+)?([eE]-?\d+)?',v): return 'val'
    raise SystemExit('inconnu:'+repr(v))
g=lambda x:re.sub(r'\s+',' ',x or '').strip()
groups=sorted({g(x.get(3)) for x in rows if x.get(3)})
res=[];cols=list(range(50,83))
for k in cols:
    code,orig=inf[norm(h[k])]
    for gr in ['(tous)']+groups:
        sel=[x for x in rows if gr=='(tous)' or g(x.get(3))==gr]
        c=collections.Counter(cls(x.get(k)) for x in sel)
        n=len(sel)
        res.append(dict(k=k,code=code,fr=g(h[k]),en=EN[k],groupe=gr,n=n,val=c['val'],traces=c['traces'],inf=c['inf'],absent=c['absent']))
json.dump(dict(res=res,groups=groups,n=len(rows),sans_groupe=sum(1 for x in rows if not x.get('3') and not x.get(3))),open(out+'/res.json','w'),ensure_ascii=False)
pct=lambda a,n:round(100*a/n,1) if n else ''
for lang in ('fr','en'):
    with open(f'{out}/ciqual-2025-lacunes-vitamines-mineraux-{lang}.csv','w',newline='',encoding='utf-8-sig') as f:
        w=csv.writer(f,delimiter=';')
        if lang=='fr': w.writerow(['code_INFOODS','constituant','groupe_aliments','nb_aliments','nb_valeur_chiffree','nb_traces','nb_inferieur_a','nb_tiret','pct_tiret','pct_traces','pct_inferieur_a','pct_valeur_chiffree'])
        else: w.writerow(['INFOODS_code','constituent','food_group','n_foods','n_numeric_value','n_traces','n_less_than','n_dash','pct_dash','pct_traces','pct_less_than','pct_numeric_value'])
        for x in res:
            gr=x['groupe']
            gname=('(tous les aliments)' if lang=='fr' else '(all foods)') if gr=='(tous)' else (gr if lang=='fr' else GEN[gr])
            w.writerow([x['code'],x['fr'] if lang=='fr' else x['en'],gname,x['n'],x['val'],x['traces'],x['inf'],x['absent'],pct(x['absent'],x['n']),pct(x['traces'],x['n']),pct(x['inf'],x['n']),pct(x['val'],x['n'])])
print(len(rows),len(groups),len(res))
for x in res:
    if x['groupe']=='(tous)': print(x['k'],x['code'],x['en'],x['n'],x['val'],x['traces'],x['inf'],x['absent'],pct(x['absent'],x['n']))
