import os
import pprint

import snowflake.connector



def get_snowflake_connection():
    """Sets up snowflake connection."""
    con = snowflake.connector.connect(
            user=os.environ.get('SNOWFLAKE_USER'),
            password=os.environ.get('SNOWFLAKE_PASSWORD'),
            account=os.environ.get('SNOWFLAKE_ACCOUNT'),
            warehouse=os.environ.get('SNOWFLAKE_WAREHOUSE'),
            database=os.environ.get('SNOWFLAKE_DB'),
            role=os.environ.get('SNOWFLAKE_ROLE'),
            schema=os.environ.get('SNOWFLAKE_SCHEMA'))
    return con


if __name__ == '__main__':
    snowflake_db = get_snowflake_connection()

    cur = snowflake_db.cursor()
    print('made cursor object')
    sql_show_views = "show views"
    cur.execute(sql_show_views)
    print('executed sql')
    views = cur.fetchall()
    view_list = []
    for view in views:
        if not (view[4] == 'INFORMATION_SCHEMA' or view[3] in ('SNOWFLAKE', 'ORCHARD_APP_REPORTING') or view[3] == 'DEV_ENGINEERING'):
                                                    # DB.Schema.View
            view_list.append('{}.{}.{}'.format(view[3], view[4], view[1]))
    print('number of views {}'.format(len(view_list)))
    views_with_v1 = []
    ddl_errors = []
    for view in view_list:
        sql_get_ddl = "SELECT GET_DDL('VIEW', '{}')".format(view)
        try:
            cur.execute(sql_get_ddl)
            ddl = cur.fetchall()
            if 'orchard_app_reporting.' in ddl[0][0].lower():
                print('appending view:', view)
                views_with_v1.append(view)
        except:
            ddl_errors.append(view)
            continue
    pprint.pprint(views_with_v1)
    pprint.pprint(ddl_errors)