"""sme_labelcopy_loader module.

Connects to psql database, get labelcopy data and save it into csv file.
After that uploads that file on AWS s3.
"""

import csv
import os

import boto3
from boto3.s3.transfer import TransferConfig
import psycopg2

from sme_labelcopy_loader import config
from sme_labelcopy_loader.ssh_tunnel_forwarder import ssh_tunnel
from sme_labelcopy_loader.utils import get_logger
from sme_labelcopy_loader.utils import get_secret


log = get_logger()


@ssh_tunnel
def unload_labelcopy_data(date, host, port):
    """Unload labelcopy data from psql database on s3.

    Args:
        date (str): Launch date (YYYY-MM-DD).
        host (str): The psql database host.
        port (int): The psql database port.
    """
    log.info(f'{date} Connecting to {config.psql_database}.')

    conn = psycopg2.connect(
        dbname=config.psql_database,
        user=config.psql_username,
        password=get_secret('PSQL_PASSWORD'),
        host=host,
        port=port,
    )

    with conn.cursor() as cur:

        for table_name, filter_clause in config.tables.items():

            log.info(f'Start unloading data from {table_name}.')
            sql = config.sql.format(
                table_name=table_name, filter_clause=filter_clause)
            cur.execute(sql)

            filename = config.unload_filename.format(
                date=date, table_name=table_name)
            s3_upload_path = config.s3_upload_path.format(
                date=date, filename=filename)

            fieldnames = [desc[0] for desc in cur.description]

            with open(filename, 'w', newline='') as csvfile:
                writer = csv.writer(csvfile, delimiter=',')
                writer.writerow(fieldnames)
                for record in cur:
                    writer.writerow(record)

            log.info(f'Unloading to local file {filename} finished.')

            s3_resource = boto3.resource('s3')
            s3_resource.meta.client.upload_file(
                filename, config.data_bucket, s3_upload_path,
                Config=TransferConfig())

            os.remove(filename)

            log.info(
                f'Uploaded on {config.data_bucket}/{s3_upload_path}')