<reponame>bibsian/lter
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
from sqlalchemy.dialects.postgresql import *
from sqlalchemy.orm import sessionmaker, load_only
import pandas as pd
import sys, os
if sys.platform == "darwin":
    rootpath = (
        "/Users/bibsian/Desktop/git/database-development/")
    end = "/"

elif sys.platform == "win32":
    rootpath = (
        "C:\\Users\MillerLab\\Desktop\\database-development\\")
    end = "\\"

# Helper Functions to coerce datatypes as necessary
# And replace likely values that indicate null (i.e. -99999)
def find_types(tbl, name):
    ''' Method to get data types from Tbls'''
    dictname = (name + "types")
    dictname = {}
    for i,item in enumerate(tbl.__table__.c):
        name = (str(item).split('.')[1])
        dictname[name] = str(
            tbl.__table__.c[name].type)
    return dictname


taxa_table_types = find_types(taxa_table, 'taxa')
count_table_types = find_types(count_table, 'count')
density_table_types = find_types(density_table, 'density')
biomass_table_types = find_types(biomass_table, 'biomass')
individual_table_types = find_types(individual_table, 'individual')
percent_cover_table_types = find_types(percent_cover_table, 'cover')


class MergedataToUpload(object):
    def __init__(self, sess):
        self.session = sess
        self.table_types = {
            'taxa': taxa_table_types,
            'count': count_table_types,
            'density': density_table_types,
            'biomass': biomass_table_types,
            'individual': individual_table_types,
            'percent_cover': percent_cover_table_types
        }

    @property
    def site_in_proj_key_df(self):
        ''' 
        Method to save the primary key from the 
        site_in_project_table:

        Query the site_in_project_table to retrieve
        the autogenerated primary keys from our previous upload
        of the site_in_project_table ('tbl_site_in_proj')
        '''
        # Step 1) Query taxa_table to get the auto generated
        # primary keys returned. Turn query data into
        # dataframe.
        session = self.session()
        site_in_proj_key_query = select([
            site_in_project_table.__table__.c.site_in_project_key,     
            site_in_project_table.__table__.c.study_site_table_fkey,
            site_in_project_table.__table__.c.project_table_fkey
        ])
        
        site_in_proj_key_statement = session.execute(
            site_in_proj_key_query)
        session.close()
        site_in_proj_key_df = pd.DataFrame(
            site_in_proj_key_statement.fetchall())
        site_in_proj_key_df.columns = site_in_proj_key_statement.keys()
        return site_in_proj_key_df
        

    def merge_for_taxa_table_upload(self, formated_taxa_table):

        # Step 2) Merge the formatted taxa_table with the quieried
        # site_in_project_key dataframe (to add foreign keys to
        # the taxa_table)
        replace_numeric_null_with_string(formated_taxa_table)
        tbl_taxa_with_site_in_proj_key= pd.merge(
            formated_taxa_table, self.site_in_proj_key_df,
            left_on=['study_site', 'metadata_key'],
            right_on=['study_site_table_fkey', 'project_table_fkey'],
            how='inner')

        # Step 3) Making a copy of the merged table 
        tbl_taxa_merged = tbl_taxa_with_site_in_proj_key.copy()

        # Step 4) Dropping the unneccessary columns from the copy
        # of the merged taxa_table (that has foreign keys now)
        tbl_taxa_merged.drop([
            'metadata_key', 'study_site', 'study_site_table_fkey',
            'project_table_fkey'], inplace=True, axis=1)

        # Step 5) Renaming the foreign keys to match the column
        # label in the database
        tbl_taxa_merged.rename(
            columns= {
                'site_in_project_key': 'site_in_project_taxa_key'}, inplace=True)

        # Step 6) Filling Null values (blank or NaN) with 'NA' strings
        # then converting each column into it's appropriate data type
        # for the database
        tbl_taxa_merged.fillna('NA', inplace=True)
        convert_types(tbl_taxa_merged, taxa_table_types)

        # Step 7) Upload table to datbase
        tbl_taxa_merged.to_sql(
            'taxa_table', conn, if_exists='append', index=False)

    def merge_for_datatype_table_upload(
            self, raw_dataframe,
            formated_dataframe,
            formated_dataframe_name,
            raw_data_taxa_columns, uploaded_taxa_columns):

        replace_numeric_null_with_string(raw_dataframe)
        replace_numeric_null_with_string(formated_dataframe)

        # Step 2) Query taxa_table to get the auto generated
        # primary keys returned. Turn query data into
        # dataframe.
        session = self.session()
        taxa_key_query = select([taxa_table])
        taxa_key_statement = session.execute(taxa_key_query)
        session.close()
        taxa_key_df = pd.DataFrame(taxa_key_statement.fetchall())
        taxa_key_df.columns = taxa_key_statement.keys()
        taxa_key_df.replace({None: 'NA'}, inplace=True)

        # Step 3) Subsetting the query tabled for record that only pertain
        # to the count data (because we will be subsetting from this
        # queried taxa table later)
        dtype_subset_taxa_key_df = taxa_key_df[
            taxa_key_df['site_in_project_taxa_key'].isin(
                self.site_in_proj_key_df['site_in_project_key'])]
        
        # Step 4) Merge the taxa_table query results with
        # the site_in_project table query that was performed
        # to upload the taxa_table (see above). This gives
        # you a table with site names and taxonomic information
        # allowing for a merge with the original dtype data
        tbl_dtype_merged_taxakey_siteinprojectkey = pd.merge(
            dtype_subset_taxa_key_df, self.site_in_proj_key_df,
            left_on='site_in_project_taxa_key',
            right_on='site_in_project_key', how='inner')

        # Step 5) Merge the original dtype data with the
        # merged taxa_table query to have all foreign keys (taxa and site_project)
        # matched up with the original observations.
        dtype_merged_with_taxa_and_siteinproj_key = pd.merge(
            raw_dataframe, tbl_dtype_merged_taxakey_siteinprojectkey,
            left_on = list(raw_data_taxa_columns),
            right_on = list(uploaded_taxa_columns),
            how='left')

        # Step 6) Take the merged original data with all foreign keys,
        # and merged that with the formatted dtype_table based on index
        # values (order or records should not changed from the original data
        # to the formatted data)
        tbl_dtype_merged_with_all_keys = pd.merge(
            formated_dataframe,
            dtype_merged_with_taxa_and_siteinproj_key,
            left_index=True, right_index=True, how='inner',
            suffixes=('', '_y'))

        # Step 7) List the columns that will be needed to push the
        # dtype table to the database (including foreign keys)
        tbl_dtype_columns_to_upload = [
            'taxa_table_key', 'site_in_project_taxa_key', 'year',
            'month', 'day', 'spatial_replication_level_1',
            'spatial_replication_level_2', 'spatial_replication_level_3',
            'spatial_replication_level_4', 'structure',
            'covariates', 'trt_label'
        ]
        tbl_dtype_columns_to_upload.append(
            '{}_observation'.format(str(formated_dataframe_name)))

        # Step 8) Subsetting the fully merged dtype table data
        tbl_dtype_to_upload = tbl_dtype_merged_with_all_keys[
            tbl_dtype_columns_to_upload]

        # Step 9) Renaming columns to reflect that in database table
        # And converting data types
        tbl_dtype_to_upload.rename(columns={
            'taxa_table_key':
            'taxa_{}_fkey'.format(str(formated_dataframe_name))}, inplace=True)

        datatype_key = 'site_in_project_{}_fkey'.format(str(formated_dataframe_name))
        tbl_dtype_to_upload.rename(columns={
            'site_in_project_taxa_key': datatype_key}, inplace=True)
        tbl_dtype_to_upload.fillna('NA', inplace=True)
        convert_types(
            tbl_dtype_to_upload, self.table_types[str(formated_dataframe_name)])

        datatype_table = '{}_table'.format(str(formated_dataframe_name))
        # Step 10) Uploading to the database
        tbl_dtype_to_upload.to_sql(
            datatype_table,
            conn, if_exists='append', index=False)
