"""
MySQL tasks

Garcon tasks related to MySQL
"""

import csv
import subprocess
import tempfile

import boto3

from garcon import task
import pymysql

from garcon_contrib.mysql import garcon_mysql_utils
from garcon_contrib.aws.utils import garcon_s3 as s3_utils


@task.decorate(timeout=7200)
def pipe_mysql_from_stdin_to_stdout(
        activity, pipe, host, port, user, password):
    """Extract data from mysql to a file.
    Args:
        activity (ActivityWorker): The swf activity worker.
        pipe (Popen): Store pipe signal
        host (str): the mysql server  host.
        port (str, int): the mysql server port.
        user (str, int): the mysql server user.
        password (str): the mysql server password.
    Return:
        dict: A dictionary with a Popen object
    """

    if pipe is None:
        raise Exception('Pipe is empty')

    temp_stderr = tempfile.TemporaryFile(mode='w+t')
    p2 = subprocess.Popen(
        ['mysql', '-q', '-N', '--host={}'.format(host),
         '--port={}'.format(port), '-u', user,
         '--password={}'.format(password), '--protocol=TCP'],
        stdin=pipe.stdout, stderr=temp_stderr, stdout=subprocess.PIPE,
        close_fds=True)
    activity.logger.info('Setup of pipe_mysql_to_stdout pipe task is done.')
    return dict(pipe=p2, mysql_stderr=temp_stderr)


@task.decorate(timeout=1000)
def ingest_csv_from_s3(
        activity, s3_key_path, mysql_config, table_name, columns_names,
        replace, ignore_lines=0, line_terminator=None, fields_terminator=','):
    """Ingest CSV file from S3 with LOAD DATA INFILE command to MySQL tables.
    Args:
        activity (ActivityWorker): The activity worker.
        s3_key_path (str): CSV file (S3 key for validated file).
        mysql_config (dict): MySQL connection credentials.
        table_name (str): Name of db table.
        columns_names (list of str): List of columns names.
        replace (bool): If True, input rows replace existing row (by primary
        key or unique index).
        ignore_lines (int): Number of lines to ignore at the start of the file.
        line_terminator (str): Line terminator character. If None the character
            will be detected automatically. The value can be one of the next
            '\\r\\n', '\\r', '\\n', otherwise ValueError exception will be
            raised.
        fields_terminator (str): Fields terminater character.
    """

    b_name, b_path = s3_utils.extract_bucket_path(s3_key_path)
    s3 = boto3.client('s3')

    with tempfile.NamedTemporaryFile(mode='w+b') \
            as temp_file_to_ingest:
        s3.download_fileobj(b_name, b_path, temp_file_to_ingest)
        temp_file_to_ingest.seek(0)

        db_connection = pymysql.connect(
            host=mysql_config['host'], user=mysql_config['user'],
            password=mysql_config['password'], db=mysql_config['db_name'],
            charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor,
            local_infile=True)
        try:
            with db_connection.cursor() as cursor:
                sql = garcon_mysql_utils.create_sql_for_load_csv(
                    temp_file_to_ingest.name, replace, table_name,
                    columns_names, ignore_lines=ignore_lines,
                    line_terminator=line_terminator,
                    fields_terminator=fields_terminator)
                activity.logger.info(sql)
                cursor.execute(sql)
        except pymysql.Error as e:
            activity.logger.error(
                '{exception} in ingest_csv_from_s3 '
                'func'.format(exception=str(e)))
            raise
        else:
            db_connection.commit()
        finally:
            db_connection.close()


@task.decorate(timeout=1000)
def bulk_insert_from_csv_file_on_s3(
        activity, s3_key_path, mysql_config, table_name, columns_names,
        ignore_lines=0):
    """Ingest CSV file from S3 with bulk insert/replace command to MySQL table.
    Args:
        activity (ActivityWorker): The activity worker.
        s3_key_path (str): CSV files (S3 keys for validated files).
        mysql_config (dict): MySQL connection credentials.
        table_name (str): Name of db table.
        columns_names (list of str): List of columns names.
        ignore_lines (int): Number of lines to ignore at the start of the file.
    """

    b_name, b_path = s3_utils.extract_bucket_path(s3_key_path)
    s3 = boto3.client('s3')

    with tempfile.NamedTemporaryFile(mode='w+b') \
            as temp_file_to_ingest:
        s3.download_fileobj(b_name, b_path, temp_file_to_ingest)
        temp_file_to_ingest.seek(0)

        csv_reader_obj = csv.reader(
            line.decode('utf8') for line in iter(temp_file_to_ingest))

        db_connection = pymysql.connect(
            host=mysql_config['host'], user=mysql_config['user'],
            password=mysql_config['password'], db=mysql_config['db_name'],
            charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

        sql = garcon_mysql_utils.create_sql_for_insert_or_replace_from_csv(
            table_name, columns_names)

        try:
            lines_to_process = []
            with db_connection.cursor() as cursor:

                # process first 1000 * n lines
                for line_number, line in enumerate(csv_reader_obj):
                    if line_number >= ignore_lines:
                        lines_to_process.append(line)
                        if len(lines_to_process) >= 1000:
                            cursor.executemany(sql, lines_to_process)
                            lines_to_process = []

                # process rest of the lines
                cursor.executemany(sql, lines_to_process)
        except pymysql.Error as e:
            activity.logger.error(
                '{exception} in bulk_insert_from_csv_file_on_s3 '
                'func'.format(exception=str(e)))
            raise
        else:
            db_connection.commit()
        finally:
            db_connection.close()