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("phonofile_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()