import pandas as pd
import snowflake.connector
import string
import xml.etree.ElementTree as ET
import yaml

class Snowflake:
    def __init__(self,yml_file):
        with open(yml_file, 'r') as f:
            for k,v in yaml.load(f).items():
                setattr(self,k,v)

        mfa = input('Input MFA: ')
        credentials = snowflake.connector.connect(
            user=self.user,
            password=self.password,
            passcode=mfa,
            account=self.account,
            warehouse=self.warehouse
        )
        self.cursor = credentials.cursor()

    def insert_into(self, table_name, df_data):
        columns = str(df_data.columns.tolist()).replace("'","")[1:-1]
        sql_head = "INSERT INTO {} ({}) VALUES ".format(table_name, columns)
        sql_body = ""
        for row in df_data.iterrows():
            value_string = "("
            for key, value in row[1].iteritems():
                if str(value) == "nan":
                    value_string += "NULL, "
                elif str(type(value)) == "<class 'str'>":
                    value_string += ("'" + value.replace("'","\\'") + "',")
                else:
                    value_string += str(value) + ','
            sql_body += value_string[:-1] + "), "

        sql = (sql_head + sql_body)[:-2]
        self.cursor.execute(sql)

class File:
    def __init__(self):
        file_name = input("Input Filename: ")
        with open(file_name, 'r') as f:
            self.element_tree = ET.fromstring(f.read())
        self.file_date = input('Input File Date (YYYY-MM-DD): ')

        ticket_dict = self.generate_ticket_dict()
        self.ticket_data = self.generate_df_tickets(ticket_dict)
        self.code_data = self.generate_df_codes(ticket_dict)
        self.note_data = self.generate_df_notes(ticket_dict)

    def generate_ticket_dict(self):
        tickets = {}
        for ticket in self.element_tree:
            data = {}
            for elem in ticket:
                if elem.tag == 'ticketId':
                    ticket_id = elem.text
                elif elem.tag == 'defectCodes':
                    list_codes = [code.text for code in list(elem)]
                    data['defectCodes'] = list_codes
                elif elem.tag == 'notes':
                    list_notes = [list(note) for note in list(elem)]
                    list_notes = [[note.text for note in notes] for notes in list_notes]
                    data['notes'] = list_notes
                else:
                    data[elem.tag] = elem.text
            tickets[ticket_id] = data
        return tickets

    def generate_df_tickets(self, ticket_dict):

        df_tickets = pd.DataFrame.from_dict(ticket_dict, orient='index')
        df_tickets['ticketID'] = df_tickets.index
        df_tickets['file_date'] = str(self.file_date)
        df_tickets['orchard_upc'] = df_tickets.contentUPC.combine_first(df_tickets.contentVendorId)
        columns = [
            'file_date',
            'ticketID',
            'contentTicketType',
            'contentAdamId',
            'contentVendorId',
            'contentUPC',
            'contentType',
            'contentLanguage',
            'contentGenre',
            'name',
            'discNumber',
            'trackNumber',
            'contentProvider',
            'openedBy',
            'contentTicketState',
            'created',
            'lastModified',
            'orchard_upc' ]
        df_tickets = df_tickets[columns]
        return df_tickets

    def generate_df_codes(self, ticket_dict):
        flatten_codes = []
        for ticket_id, ticket_dict in ticket_dict.items():
            codes = ticket_dict['defectCodes']
            for code in codes:
                code = "".join([char for char in code if char in string.printable])
                flatten_codes.append([ticket_id, code])
        df_codes = pd.DataFrame(flatten_codes, columns=['ticketId', 'defect_code'])
        return df_codes

    def generate_df_notes(self, ticket_dict):
        flatten_notes = []
        for ticket_id, ticket_dict in ticket_dict.items():
            notes = ticket_dict['notes']
            for note in notes:
                text = "".join([char for char in note[0] if char in string.printable])
                date = note[1]
                flatten_notes.append([ticket_id,text,date])
        df_notes = pd.DataFrame(flatten_notes, columns=['ticketId', 'note_text', 'note_datetime'])
        return df_notes

def main():
    xml = File()
    sf = Snowflake('sf_credentials.yml')

    sf.insert_into('intelligence.dev.itunes_ticket_history', xml.ticket_data)

    id_mapping = "SELECT ticket_history_id, ticketId FROM intelligence.dev.itunes_ticket_history WHERE file_date = '{}'".format(xml.file_date)
    id_mapping = pd.DataFrame(sf.cursor.execute(id_mapping).fetchall(), columns=['ticket_history_id', 'ticketId'])

    code_data = xml.code_data
    code_data['ticketId'] = code_data['ticketId'].astype(int)
    code_data = code_data.merge(id_mapping, on='ticketId', how='left')
    code_data = code_data[['ticket_history_id', 'defect_code']]

    note_data = xml.note_data
    note_data['ticketId'] = note_data['ticketId'].astype(int)
    note_data = note_data.merge(id_mapping, on='ticketId', how='left')
    note_data = note_data[['ticket_history_id', 'note_text', 'note_datetime']]

    sf.insert_into('intelligence.dev.itunes_ticket_history_code', code_data)
    sf.insert_into('intelligence.dev.itunes_ticket_history_note', note_data)

if __name__ == '__main__':
    main()