appStore/prep_data.py

import pandas as pd
path_to_data = "./docStore/"
from appStore.prep_utils import create_chunks
from appStore.search import hybrid_search
from datetime import datetime

def convert_to_date(val):
    try:
        # If val is a string, first check if it represents a numeric value.
        if isinstance(val, str):
            val_str = val.strip()
            try:
                # Try converting the string to a float (i.e. it’s an epoch in string form)
                num = float(val_str)
                return datetime.utcfromtimestamp(num / 1000).strftime("%Y-%m-%d")
            except ValueError:
                # Not a numeric string; assume it's already a date string in "YYYY-MM-DD" format.
                # Optionally, you can validate it:
                datetime.strptime(val_str, "%Y-%m-%d")
                return val_str
        elif isinstance(val, (int, float)):
            return datetime.utcfromtimestamp(val / 1000).strftime("%Y-%m-%d")
        else:
            return "Unknown"
    except Exception:
        return "Unknown"


def process_iati():
    """
    this will read the iati files and create the chunks
    """
    orgas_df = pd.read_csv(f"{path_to_data}iati_files/project_orgas.csv")
    region_df = pd.read_csv(f"{path_to_data}iati_files/project_region.csv")
    sector_df = pd.read_csv(f"{path_to_data}iati_files/project_sector.csv")
    status_df = pd.read_csv(f"{path_to_data}iati_files/project_status.csv")
    texts_df = pd.read_csv(f"{path_to_data}iati_files/project_texts.csv")

    projects_df = pd.merge(orgas_df, region_df, on='iati_id', how='inner')
    projects_df = pd.merge(projects_df, sector_df, on='iati_id', how='inner')
    projects_df = pd.merge(projects_df, status_df, on='iati_id', how='inner')
    projects_df = pd.merge(projects_df, texts_df, on='iati_id', how='inner')
    projects_df = projects_df[projects_df.client.str.contains('bmz')].reset_index(drop=True)

    projects_df.drop(columns= ['orga_abbreviation', 'client',
       'orga_full_name', 'country', 
       'country_flag', 'crs_5_code', 'crs_3_code','country_code_list',
       'sgd_pred_code','crs_5_name', 'crs_3_name', 'sgd_pred_str'], inplace=True)
    #print(projects_df.columns)
    projects_df['text_size'] = projects_df.apply(lambda x: len((x['title_main'] + x['description_main']).split()), axis=1)
    projects_df['chunks'] = projects_df.apply(lambda x:create_chunks(x['title_main'] + x['description_main']),axis=1)
    projects_df = projects_df.explode(column=['chunks'], ignore_index=True)
    projects_df['source'] = 'IATI'
    projects_df.rename(columns = {'iati_id':'id','iati_orga_id':'org'}, inplace=True)

    return projects_df


def convert_crs_value(x):
    if pd.isnull(x):
        return x
    # Convert to string and remove trailing '.0' if present.
    x_str = str(x).strip()
    if x_str.endswith(".0"):
        x_str = x_str[:-2]
    try:
        return int(x_str)
    except ValueError:
        return x

def process_giz_worldwide():
    """
    This function reads the new giz_worldwide file and prepares the data for embedding.
    Adjustments made:
      - Reads the file 'giz_worldwide_api_download_23_02_2025.json'
      - Renames 'name.en' to 'project_name'
      - Uses the 'merged_text' column for embedding the whole text (no chunking)
      - Creates an empty 'url' column (since the new dataset has an empty URL)
      - Renames 'duration.project.start' to 'start_year' and 'duration.project.end' to 'end_year'
      - Converts 'crs_value' to an integer (dropping any .0) if present.
    """
    # Read the new JSON file
    giz_df = pd.read_json(f'{path_to_data}giz_worldwide/giz_worldwide_api_download_23_02_2025.json')
    
    # Reset index for safety
    giz_df = giz_df.reset_index(drop=True)
    
    # Rename columns per new dataset requirements
    giz_df = giz_df.rename(columns={
        'name.en': 'project_name',
        'duration.project.start': 'start_year',
        'duration.project.end': 'end_year'
    })
    
    giz_df['end_year'] = giz_df['end_year'].apply(convert_to_date)
    
    # Create an empty 'url' column as the new dataset has an empty URL
    giz_df['url'] = ''
    
    # Convert CRS value to numeric then to integer if possible.
    # After reading the JSON file into giz_df, convert the crs_key column:
    if 'crs_key' in giz_df.columns:
        def clean_crs_key(x):
            x_str = str(x).strip()
            if not x_str:
                return x_str
            try:
                # Convert to float then to int, then to string.
                return str(int(float(x_str)))
            except Exception:
                # Fallback: remove trailing ".0" if present.
                return x_str.replace('.0', '')
        giz_df['crs_key'] = giz_df['crs_key'].apply(clean_crs_key)



    # Compute text_size based on merged_text and assign full text to the 'chunks' column
    giz_df['text_size'] = giz_df['merged_text'].apply(lambda text: len(text.split()) if isinstance(text, str) else 0)
    
    # Use the full merged_text for embedding (no chunking)
    giz_df['chunks'] = giz_df['merged_text']
    
    giz_df['source'] = 'GIZ_WORLDWIDE'
    return giz_df



# def process_giz_worldwide():
#     """
#     this will read the giz_worldwide files and create the chunks
#     """
#     giz_df = pd.read_json(f'{path_to_data}giz_worldwide/data_giz_website.json')
#     giz_df = giz_df.rename(columns={'content':'project_description'})
#     # Sample random rows for quick embeddings (seed set for reproducibility)
#     giz_df = giz_df.sample(n=5, random_state=42)    
#     giz_df['text_size'] = giz_df.apply(lambda x: len((x['project_name'] + x['project_description']).split()), axis=1)
#     giz_df['chunks'] = giz_df.apply(lambda x:create_chunks(x['project_name'] + x['project_description']),axis=1)
#     print("initial df length:",len(giz_df))
#     giz_df = giz_df.explode(column=['chunks'], ignore_index=True)
#     print("new df length:",len(giz_df))
#     print(giz_df.columns)
#     #giz_df.drop(columns = ['filename', 'url', 'name', 'mail', 
#     #                    'language', 'start_year', 'end_year','poli_trager'], inplace=True)
#     giz_df['source'] = 'GIZ_WORLDWIDE'
#     return giz_df

def remove_duplicates(results_list):
    """
    Return a new list of results with duplicates removed, 
    based on 'url' in metadata.
    """
    unique_results = []
    seen_urls = set()

    for r in results_list:
        # Safely get the URL from metadata
        url = r.payload['metadata'].get('id', None)
        if url not in seen_urls:
            seen_urls.add(url)
            unique_results.append(r)

    return unique_results

def extract_year(date_str):
    try:
        return str(datetime.strptime(date_str, "%Y-%m-%d").year)
    except Exception:
        return "Unknown"


def get_max_end_year(_client, collection_name):
    """
    Return the maximum 'end_year' in the entire collection 
    so we can set the slider's max_value dynamically.
    """
    # For safety, get a large pool of items
    all_res = hybrid_search(_client, "", collection_name, limit=2000)
    big_list = all_res[0] + all_res[1]

    years = []
    for r in big_list:
        metadata = r.payload.get('metadata', {})
        year_str = metadata.get('end_year', None)
        if year_str:
            try:
                years.append(float(year_str))
            except ValueError:
                pass

    if not years:
        # fallback if no valid end years found
        return 2030
    return int(max(years))

# Helper: safe formatting of project IDs
def safe_format_project_id(pid):
    """
    Safely format a project ID:
      - If the ID is a float ending with ".0", remove it.
      - If the value is "nan" (case insensitive) or empty, return an empty string.
      - Otherwise, format it in the typical GIZ format if it has enough digits.
    """
    s = str(pid)
    # Remove trailing ".0" if present
    if s.endswith(".0"):
        s = s[:-2]
    # If the value is 'nan' or empty after stripping, return empty string
    if s.lower() == "nan" or s.strip() == "":
        return ""
    # Format if the string has enough digits
    if len(s) > 5:
        return s[:4] + "." + s[4:-1] + "." + s[-1]
    return s