import pandas as pd
import json
import uuid

df = pd.read_csv(
    '/home/cgeier/Téléchargements/IPA/Extraction/synthese_observations_2026-10-13T10_10_43.127Z.csv',
    sep=';', 
    converters={'champs_additionnels':eval}
)

rename_columns = {
    'date_debut' : 'date_visit',
    'nombre_min': 'effectif',
    'jdd_uuid': 'id_dataset',

}

df.loc[df.comment_releve.str.contains('IPA'),'nom_point_ecoute'] = 'NDM - ' + (
    df[df.comment_releve.str.contains('IPA')].comment_releve
    .str.split('\n')
    .str[0]
    .str.strip()
)
df.loc[df.comment_releve.str.contains('IPA'),'heure_debut'] = (
    df[df.comment_releve.str.contains('IPA')].comment_releve
    .str.split('\n')
    .str[1]
    .str.split('-')
    .str[0]
    .str.strip()
)
df.loc[df.comment_releve.str.contains('IPA'),'meteo_ciel'] = (
    df[df.comment_releve.str.contains('IPA')].comment_releve
    .str.split('\n')
    .str[2]
    .str.strip()
)


df['code_atlas'] = df.champs_additionnels.map(lambda x: x['code_atlas'])
df['uuid_observation'] = [str(uuid.uuid4()) for x in df.iterrows()]
df['uuid_site'] = None
df['uuid_visit'] = None
df['date_site'] = None

for s in df.nom_point_ecoute.unique():
    t1 = df.nom_point_ecoute == s
    df.loc[t1,'uuid_site'] = str(uuid.uuid4())
    if df[t1].date_debut.unique().any():
        df.loc[t1,'date_site'] = df[t1].date_debut.sort_values().unique()[0]

    for v in df[t1].date_debut.unique():
        t2 = df.date_debut == v
        df.loc[t1&t2,'uuid_visit'] = str(uuid.uuid4())

df.rename(columns=rename_columns, inplace=True)

res = df[
    ['cd_nom','nom_valide', 'nom_vernaculaire',*rename_columns.values(),
    'code_atlas','comportement','technique_observation','uuid_observation','uuid_site','uuid_visit','date_site'
    ,'nom_point_ecoute','heure_debut','meteo_ciel'
    ]
].copy()

res.to_excel('/home/cgeier/Téléchargements/IPA/Extraction/ipa_ndm.xlsx')