import snowflake.connector
import os
import pandas as pd
import csv

def connect_to_snowflake():

    credentials = snowflake.connector.connect(
        user = os.getenv('SNOWFLAKE_USER'),
        password = os.getenv('SNOWFLAKE_PASSWORD'),
        account = 'orchard',
        warehouse = os.getenv('SNOWFLAKE_WAREHOUSE')
    )

    return credentials.cursor()

def get_date():

    cursor = connect_to_snowflake()

    sql = '''

    SELECT
        DATE_FROM_PARTS(year,month,31) :: string AS date
    FROM facts.prod.dim_period
    WHERE periodid=250

    '''

    results = cursor.execute(sql)

    df = pd.DataFrame(results, columns=['date'])

    df['date'] = df['date'].str.replace('-','')

    return df['date'].values[0]

def generate_sap_upload():

    date = get_date ()

    cursor = connect_to_snowflake()

    with open('row1.sql','r') as f:
        sql = f.read()

    results = cursor.execute(sql).fetchall()

    df = pd.DataFrame(results)

    df.to_csv('mechanicals_row1.csv', index=False)

    with open('row2.sql','r') as f:
        sql = f.read()

    results = cursor.execute(sql).fetchall()

    df = pd.DataFrame(results)
    df.to_csv('mechanicals_row2.csv', index=False)

    with open('header.sql','r') as f:
        sql = f.read()
        sql = sql.format(date=date)

    results = cursor.execute(sql).fetchall()

    df= pd.DataFrame(results)
    df.to_csv('mechanicals_header.csv', index=False)

    with open('mechanicals_header.csv','r') as h:
        with open('mechanicals_row1.csv','r') as r1:
            with open('mechanicals_row2.csv','r') as r2:


                headers=list(csv.reader(h))
                row1=list(csv.reader(r1))
                row2=list(csv.reader(r2))

                with open('redessent_upload.csv','w') as s:
                    sap = csv.writer(s, delimiter=',')

                    index=1

                    for row in headers[1:]:
                        sap.writerow(headers[index])
                        sap.writerow(row1[index])
                        sap.writerow(row2[index])
                        index+=1

if __name__ == '__main__':

    generate_sap_upload()