import sys
import pandas as pd
from sqlalchemy import create_engine

def load_data(messages_filepath, categories_filepath):
    """
    input:
    messages_filepath: filepath the messages are stored in
    categories_filepath: filepath the categories are stored in

    """
    # load messages dataset
    messages = pd.read_csv(messages_filepath)
    categories = pd.read_csv(categories_filepath)

    # merge
    df = messages.merge(categories, how="left", on="id")
    return df


def clean_data(df):
    """
    cleaning provided data
    input:
    df: a pandas dataframe that will be cleaned
    output:
    cleaned pandas dataframe
    """
    #split categories into separate columns
    categories = df.categories.str.split(pat=";",expand=True)
    #get list of new column names
    row = categories.iloc[0]
    category_colnames = row.apply(lambda x: x[0:-2])
    # name columns accordingly
    categories.columns = category_colnames
    #change entries of category columns to 1 and 0
    for column in categories:
        categories[column] = categories[column].apply(lambda x: x[-1:])    
        categories[column] = pd.to_numeric(categories[column])
    #join df and categories dataframe
    df=pd.concat([df,categories],axis=1)
    df.drop("categories",axis=1,inplace=True)
    #2. remove duplicates
    df.drop_duplicates(inplace=True)
    return df

def save_data(df, database_filename):
    """
    saving provided dataframe
    input:
    df: dataframe that will be saved
    database_filename: name of the database the df will be saved to
    """
    engine = create_engine('sqlite:///{}'.format(database_filename))
    df.to_sql('disaster_message_cat', engine, index=False,if_exists="replace")
    print("saved successfully")
  


def main():
    if len(sys.argv) == 4:

        messages_filepath, categories_filepath, database_filepath = sys.argv[1:]

        print('Loading data...\n    MESSAGES: {}\n    CATEGORIES: {}'
              .format(messages_filepath, categories_filepath))
        df = load_data(messages_filepath, categories_filepath)

        print('Cleaning data...')
        df = clean_data(df)
        
        print('Saving data...\n    DATABASE: {}'.format(database_filepath))
        save_data(df, database_filepath)
        
        print('Cleaned data saved to database!')
    
    else:
        print('Please provide the filepaths of the messages and categories '\
              'datasets as the first and second argument respectively, as '\
              'well as the filepath of the database to save the cleaned data '\
              'to as the third argument. \n\nExample: python process_data.py '\
              'disaster_messages.csv disaster_categories.csv '\
              'DisasterResponse.db')


if __name__ == '__main__':
    main()
