import pandas as pd
import numpy as numpy
import os
from env import host, user, password 

###################### Acquire Titanic Data ######################

#defines function to create a sql url using personal credentials
def get_connection(db, user=user, host=host, password=password):
    '''
    This function uses my info from my env file to
    create a connection url to access the Codeup db.
    '''
    return f'mysql+pymysql://{user}:{password}@{host}/{db}'



#returns titanic data from db as a pandas df
def get_titanic_data():
    '''
    This function reads in the titanic data from the Codeup db
    and returns a pandas DataFrame with all columns.
    '''
    #create SQL query
    sql_query = 'SELECT * FROM passengers'
    
    #read in dataframe from Codeup db
    df = pd.read_sql(sql_query, get_connection('titanic_db'))
    
    return df


#adds caching to get_titanic_data and checks for local filename (titanic.csv)
#if file exists, uses the .csv file
#if file doesn't exist, then produces SQL & pandas necessary to create a df, then write the df to a .csv file
def cached_titanic_data(cached=False):
    '''
    This function reads in titanic data from Codeup database and writes data to
    a csv file if cached == False or if cached == True reads in titanic df from
    a csv file, returns df.
    ''' 
    if cached == False or os.path.isfile('titanic_df.csv') == False:
        
        # Read fresh data from db into a DataFrame.
        df = get_titanic_data()
        
        # Write DataFrame to a csv file.
        df.to_csv('titanic_df.csv')
        
    else:
        
        # If csv file exists or cached == True, read in data from csv.
        df = pd.read_csv('titanic_df.csv', index_col=0)
        
    return df



###################### Acquire Iris Data ######################

#returns iris data from db as a pandas df
def get_iris_data():
    '''
    This function reads in the iris data from the Codeup db
    and returns a pandas DataFrame.
    '''
    #create SQL query
    sql_query = 'SELECT * FROM measurements JOIN species USING (species_id)'
    
    #read in dataframe from Codeup db
    df = pd.read_sql(sql_query, get_connection('iris_db'))
    
    return df


#adds caching to get_iris_data and checks for local filename (iris.csv)
#if file exists, uses the .csv file
#if file doesn't exist, then produces SQL & pandas necessary to create a df, then write the df to a .csv file
def cached_iris_data(cached=False):
    '''
    This function reads in iris data from Codeup database and writes data to
    a csv file if cached == False or if cached == True reads in iris df from
    a csv file, returns df.
    ''' 
    if cached == False or os.path.isfile('iris_df.csv') == False:
        
        # Read fresh data from db into a DataFrame.
        df = get_iris_data()
        
        # Write DataFrame to a csv file.
        df.to_csv('iris_df.csv')
    
    else:
        
        # If csv file exists or cached == True, read in data from csv.
        df = pd.read_csv('iris_df.csv', index_col=0)
    
    return df

