"""
MySQL tasks

Garcon tasks related to MySQL
"""

import csv
import subprocess
import tempfile

from boto.s3 import bucket
from boto.s3 import connection
from boto.s3 import key
from garcon import task
import pymysql

from garcon.contrib import mysql_utils  # noqa
from garcon.contrib.aws.utils import s3 as s3utils  # noqa


@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.
        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.
    """
    s3_connection = connection.S3Connection()
    bucket_name, file_to_ingest_path = s3utils.extract_bucket_path(s3_key_path)
    bucket_obj = bucket.Bucket(s3_connection, bucket_name)
    key_obj = key.Key(bucket_obj, file_to_ingest_path)
    with tempfile.NamedTemporaryFile(
            mode='w+t', newline='') as temp_file_to_ingest:
        key_obj.get_contents_to_filename(temp_file_to_ingest.name)
        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 = 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.
    """

    s3_connection = connection.S3Connection()
    bucket_name, file_to_ingest_path = s3utils.extract_bucket_path(s3_key_path)
    bucket_obj = bucket.Bucket(s3_connection, bucket_name)
    key_obj = key.Key(bucket_obj, file_to_ingest_path)

    with tempfile.NamedTemporaryFile(
            mode='w+t', newline='') as temp_file_to_ingest:
        key_obj.get_contents_to_filename(temp_file_to_ingest.name)
        temp_file_to_ingest.seek(0)

        csv_reader_obj = csv.reader(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 = 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()