import os.path
import snowflake.connector
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('Enter MFA Passcode: ')
        credentials = snowflake.connector.connect(
            user=self.user,
            password=self.password,
            passcode=mfa,
            account=self.account,
            warehouse=self.warehouse
        )
        self.cursor = credentials.cursor()

class Table:
    def __init__(self,connection,table_name):
        self.connection = connection
        self.table_name = table_name

    def return_column_metadata(self):
        db, schema, table = self.table_name.split(".")
        query_template = "\
            SELECT column_name, data_type\
            FROM {}.information_schema.columns\
            WHERE table_schema = UPPER('{}')\
            AND table_name = UPPER('{}');"
        sql = ' '.join(query_template.format(db, schema, table).split())
        columns = self.connection.cursor.execute(sql).fetchall()

        column_metadata = [column for column in columns if column[0][0] != '_']
        column_metadata = sorted(column_metadata, key=lambda tup: tup[0])
        return column_metadata

class View:
    def __init__(self,table):
        self.table = table
        self.type_mapping = {
            'BOOLEAN': 'yesno',
            'DATE': 'date',
            'FLOAT': 'number',
            'NUMBER': 'number',
            'TEXT': 'string',
            'TIMESTAMP_LTZ': 'date_time',
            'TIMESTAMP_NTZ': 'date_time',
            'VARIANT': ''
        }

    def return_view_file(self):
        table = self.table
        table_name = table.table_name

        view_name = '_'.join(table_name.split("."))
        view_row = 'view: {} {{\n'.format(view_name)
        table_row = '  sql_table_name: {} ;;\n\n'.format(table_name)

        dimensions = ""
        for column, data_type in table.return_column_metadata():
            data_type = self.type_mapping[data_type]
            column, data_type = column.lower(), data_type.lower()

            dimension_row = "  dimension: {} {{\n".format(column)
            type_row = "    type: {}\n".format(data_type)
            sql_row = "    sql: ${{TABLE}}.{} ;;\n  }}\n\n".format(column)

            dimension_block = dimension_row + type_row + sql_row
            dimensions += dimension_block

        view_file = view_row + table_row + dimensions + "}\n"
        return view_file

def main():
    load_tables = input("Enter Text File of Snowflake Tables: ")
    sf = Snowflake('sf_credentials.yml')
    with open(load_tables) as f:
        lines = f.readlines()
        for line in lines:
            table_name = line.replace('\n','')
            table = Table(sf, table_name)
            view = View(table)

            file = view.return_view_file()
            location = os.path.join('views/', table_name + '.view.lkml')
            with open(location, "w") as g:
                g.write(file)

if __name__ == '__main__':
    main()