"""Data loading functions."""
import csv
import os

import boto3

from sqlalchemy import text

from availability_etl import config
from availability_etl import constants
from availability_etl.connectors import snowflake_db


def load(dicts):
    """Perform all data loading operations.

    Args:
        dicts (iterable): An iterable of data rows as dictionaries.
    """
    upload_csv_to_s3(save_csv(dicts))
    load_into_snowflake_temp_table()
    merge_snowflake_temp_and_dest_tables()


def load_into_snowflake_temp_table():
    """Load data into snowflake temporary table.

    Use previously uploaded csv file(s) on S3 bucket to load into Snowflake
    temporary table.
    """
    with snowflake_db.get_snowflake_connection() as conn:
        conn.execute(constants.SQL_SF_TRUNCATE_TEMP)
        conn.execute(
            text(constants.SQL_SF_COPY_TO_TEMP.format(
                s3_bucket=config.S3_BUCKET_NAME,
                )).bindparams(
                    aws_key_id=config.AWS_ACCESS_KEY_ID,
                    aws_secret_key=config.AWS_SECRET_ACCESS_KEY,
            ))


def merge_snowflake_temp_and_dest_tables():
    """Update data in the Snowflake destination from the temporary table."""
    with snowflake_db.get_snowflake_connection() as conn:
        conn.execute(constants.SQL_MERGE_TEMP_TO_DEST)


def save_csv(dicts):
    """Save csv file to a local filesystem from an iterable.

    Args:
        dicts (iterable): An iterable of row dictionaries.

    Returns:
        str: An absolute result file path.
    """
    with open(constants.CSV_FILE_NAME, 'w') as result_file:
        result_csv = csv.writer(result_file)
        for d in dicts:
            row = []
            for column in constants.CSV_COLUMNS:
                row.append(d[column])
            result_csv.writerow(row)
    return os.path.join(os.getcwd(), result_file.name)


def upload_csv_to_s3(filename):
    """Upload local file to S3 preserving file name.

    Args:
        filename (str): Local file path.
    """
    remote_filename = '/'.join(
        (constants.S3_PATH_PREFIX, os.path.basename(filename)))
    s3 = boto3.resource('s3')
    s3.meta.client.upload_file(
        filename, config.S3_BUCKET_NAME, remote_filename)