# Function de construction de KPI

def custom_date_parser(date_string):
    return pd.to_datetime(date_string, utc = True, format = 'ISO8601')

def display_input_databases(directory_path, file_name, datetime_col = None):
    """
    This function returns the file from s3 storage 
    """
    file_path = "projet-bdc2324-team1" + "/0_Input/Company_" + directory_path + "/" + file_name + ".csv"
    print("File path : ", file_path)
    with fs.open(file_path, mode="rb") as file_in:
        df = pd.read_csv(file_in, sep=",", parse_dates = datetime_col, date_parser=custom_date_parser)        
    return df       

def campaigns_kpi_function(campaigns_information = None, max_date = "2023-12-01"):
    
    # Nombre de campagnes de mails
    nb_campaigns = campaigns_information[['customer_id', 'campaign_name']].groupby('customer_id').count().reset_index()
    nb_campaigns.rename(columns = {'campaign_name' : 'nb_campaigns'}, inplace = True)
    
    # Temps d'ouverture moyen (en minutes)
    campaigns_information['time_to_open'] = ((pd.to_datetime(campaigns_information['opened_at'], utc = True, format = 'ISO8601') - pd.to_datetime(campaigns_information['delivered_at'], utc = True, format = 'ISO8601')) / np.timedelta64(1, 'h'))
    campaigns_information['time_to_open'] = campaigns_information['time_to_open'].fillna((pd.to_datetime(campaigns_information['delivered_at'], utc = True, format = 'ISO8601') - pd.to_datetime(max_date, utc = True, format = 'ISO8601')) / np.timedelta64(1, 'h'))
    
    time_to_open = campaigns_information[['customer_id', 'time_to_open']].groupby('customer_id').mean().reset_index()

    # Nombre de mail ouvert
    opened_campaign = campaigns_information[['customer_id', 'campaign_name', 'opened_at']]
    opened_campaign.dropna(subset=['opened_at'], inplace=True)
    opened_campaign = opened_campaign[['customer_id', 'campaign_name']].groupby('customer_id').count().reset_index()
    opened_campaign.rename(columns = {'campaign_name' : 'nb_campaigns_opened' }, inplace = True)

    # Fusion des indicateurs
    campaigns_reduced = pd.merge(nb_campaigns, opened_campaign, on = 'customer_id', how = 'left')
    campaigns_reduced = pd.merge(campaigns_reduced, time_to_open, on = 'customer_id', how = 'left')

    # Taux de mails ouvert
    campaigns_reduced['taux_ouverture_mail'] = campaigns_reduced['nb_campaigns_opened'] / campaigns_reduced['nb_campaigns']
    
    # Fill NaN values
    campaigns_reduced[['nb_campaigns', 'nb_campaigns_opened', 'taux_ouverture_mail']] = campaigns_reduced[['nb_campaigns', 'nb_campaigns_opened', 'taux_ouverture_mail']].fillna(0)
    # Remplir les NaT : time_to_open (??)

    return campaigns_reduced

def tickets_kpi_function(tickets_information = None):

    tickets_information_copy = tickets_information.copy()
    
    # Dummy : Canal de vente en ligne
    liste_mots = ['en ligne', 'internet', 'web', 'net', 'vad', 'online'] # vad = vente à distance
    tickets_information_copy['vente_internet'] = tickets_information_copy['supplier_name'].fillna('').str.contains('|'.join(liste_mots), case=False).astype(int)

    # Proportion de vente en ligne
    prop_vente_internet = tickets_information_copy[tickets_information_copy['vente_internet'] == 1].groupby(['customer_id'])['purchase_id'].nunique().reset_index()
    prop_vente_internet.rename(columns = {'purchase_id' : 'nb_purchases_internet'}, inplace = True)

    # Mixte KPI comportement achat
    tickets_kpi = (tickets_information_copy[['customer_id', 'purchase_id' ,'ticket_id','supplier_name', 'purchase_date', 'amount', 'vente_internet']]
                   .groupby(['customer_id']) 
                   .agg(nb_tickets=('ticket_id', 'nunique'), 
                        nb_purchases=('purchase_id', 'nunique'),
                        total_amount=('amount', 'sum'),
                        nb_suppliers=('supplier_name', 'nunique'),
                        achat_internet=('vente_internet', 'max'),
                        purchase_date_min=('purchase_date', 'min'),
                        purchase_date_max=('purchase_date', 'max'))
                   .reset_index())
        
    tickets_kpi['time_between_purchase'] = tickets_kpi['purchase_date_max'] - tickets_kpi['purchase_date_min']
    tickets_kpi['time_between_purchase'] = tickets_kpi['time_between_purchase'] / np.timedelta64(1, 'D') # En nombre de jours

    # Convertir date et en chiffre
    max_date = tickets_kpi['purchase_date_max'].max()
    tickets_kpi['purchase_date_max'] = (max_date - tickets_kpi['purchase_date_max']) / np.timedelta64(1, 'D')
    tickets_kpi['purchase_date_min'] = (max_date - tickets_kpi['purchase_date_min']) / np.timedelta64(1, 'D')
    
    # Proportion de ticket internet
    tickets_kpi = tickets_kpi.merge(prop_vente_internet, on = ['customer_id'], how = 'left')
    tickets_kpi['nb_purchases_internet'] = tickets_kpi['nb_purchases_internet'].fillna(0)
    tickets_kpi['prop_purchases_internet'] = tickets_kpi['nb_purchases_internet'] / tickets_kpi['nb_purchases']

    # Nombre d'achat à choisir
    tickets_information_copy['month_year_purchase'] = 'purchases_' + tickets_information_copy['purchase_date'].dt.month.astype(str) + '_' + tickets_information_copy['purchase_date'].dt.year.astype(str)
    purchases_by_month = tickets_information_copy.pivot_table(index='customer_id', columns='month_year_purchase', values='purchase_id', aggfunc='nunique', fill_value=0)
    tickets_kpi = pd.merge(tickets_kpi, purchases_by_month, on = 'customer_id', how = 'left')
    
    return tickets_kpi

def customerplus_kpi_function(customerplus_clean = None):
    # KPI sur les données socio-demographique   
    
    # Le genre
    customerplus_clean["gender_label"] = customerplus_clean["gender"].map({
        0: 'female',
        1: 'male',
        2: 'other'
    })
    gender_dummies = pd.get_dummies(customerplus_clean["gender_label"], prefix='gender').astype(int)
    customerplus_clean = pd.concat([customerplus_clean, gender_dummies], axis=1)
    customerplus_clean.drop(columns = "gender", inplace = True)


    # Age
    customerplus_clean['categorie_age_0_10'] = ((customerplus_clean['age'] >= 0) & (customerplus_clean['age'] < 10)).astype(int)
    customerplus_clean['categorie_age_10_20'] = ((customerplus_clean['age'] >= 10) & (customerplus_clean['age'] < 20)).astype(int)
    customerplus_clean['categorie_age_20_30'] = ((customerplus_clean['age'] >= 20) & (customerplus_clean['age'] < 30)).astype(int)
    customerplus_clean['categorie_age_30_40'] = ((customerplus_clean['age'] >= 30) & (customerplus_clean['age'] < 40)).astype(int)
    customerplus_clean['categorie_age_40_50'] = ((customerplus_clean['age'] >= 40) & (customerplus_clean['age'] < 50)).astype(int)
    customerplus_clean['categorie_age_50_60'] = ((customerplus_clean['age'] >= 50) & (customerplus_clean['age'] < 60)).astype(int)
    customerplus_clean['categorie_age_60_70'] = ((customerplus_clean['age'] >= 60) & (customerplus_clean['age'] < 70)).astype(int)
    customerplus_clean['categorie_age_70_80'] = ((customerplus_clean['age'] >= 70) & (customerplus_clean['age'] < 80)).astype(int)
    customerplus_clean['categorie_age_plus_80'] = (customerplus_clean['age'] >= 80).astype(int)
    customerplus_clean['categorie_age_inconnue'] = customerplus_clean['age'].apply(lambda x: 1 if pd.isna(x) else 0)
    # customerplus_clean.drop(columns = "age", inplace = True)

    # Consentement au mailing
    customerplus_clean['opt_in'] = customerplus_clean['opt_in'].astype(int)
    
    # Indicatrice si individue vit en France
    customerplus_clean["country_fr"] = customerplus_clean["country"].apply(lambda x : int(x=="fr") if pd.notna(x) else np.nan)
    # customerplus_clean.drop(columns = "country", inplace = True)
    
    customerplus_clean['is_profession_known'] = customerplus_clean['profession'].notna().astype(int)
    # customerplus_clean.drop(columns = "profession", inplace = True)

    customerplus_clean['is_zipcode_known'] = customerplus_clean['zipcode'].notna().astype(int)
    # customerplus_clean.drop(columns = "zipcode", inplace = True)
    
    
    return customerplus_clean

def targets_KPI(df_target = None):
    
    df_target['target_name'] = df_target['target_name'].fillna('').str.lower()

    # Target name cotegory musees / 
    df_target['target_jeune'] = df_target['target_name'].str.contains('|'.join(['jeune', 'pass_culture', 'etudiant', '12-25 ans', 'student', 'jeunesse']), case=False).astype(int)
    df_target['target_optin'] = df_target['target_name'].str.contains('|'.join(['optin' ,'opt-in']), case=False).astype(int)
    df_target['target_optout'] = df_target['target_name'].str.contains('|'.join(['optout', 'unsubscribed']), case=False).astype(int)
    df_target['target_scolaire'] = df_target['target_name'].str.contains('|'.join(['scolaire' , 'enseignant', 'chercheur', 'schulen', 'école']), case=False).astype(int)
    df_target['target_entreprise'] = df_target['target_name'].str.contains('|'.join(['b2b', 'btob', 'cse']), case=False).astype(int)
    df_target['target_famille'] = df_target['target_name'].str.contains('|'.join(['famille', 'enfants', 'family']), case=False).astype(int)
    df_target['target_newsletter'] = df_target['target_name'].str.contains('|'.join(['nl', 'newsletter']), case=False).astype(int)
    
    # Target name category for sport compagnies
    df_target['target_abonne'] = ((
                            df_target['target_name']
                            .str.contains('|'.join(['abo', 'adh']), case=False)
                            & ~df_target['target_name'].str.contains('|'.join(['hors abo', 'anciens abo']), case=False)
                            ).astype(int))
    
    df_target_categorie = df_target.groupby('customer_id')[['target_jeune', 'target_optin', 'target_optout', 'target_scolaire', 'target_entreprise', 'target_famille', 'target_newsletter', 'target_abonne']].max()
    
    target_agg = df_target.groupby('customer_id').agg(
        nb_targets=('target_name', 'nunique')  # Utilisation de tuples pour spécifier les noms de colonnes
        # all_targets=('target_name', concatenate_names),
        # all_target_types=('target_type_name', concatenate_names)
        ).reset_index()
    
    target_agg = pd.merge(target_agg, df_target_categorie, how='left', on='customer_id')
    
    return target_agg
    