"""
MySQL Utilities

Utility methods related to MySQL
"""


def get_export_all_data_sql(database, entity):
    """ Get sql statement for getting all data from a table or a view
    Args:
        database (str): MySQL database name
        entity (str): name of database table or view
    Return:
        str: SQL statement
    """
    if not database:
        raise Exception('Missing database name')
    else:
        return 'use {database} + ; select * from {entity}'.format(
            database=database, entity=entity)


def _get_line_terminator(file_path):
    """Check line terminator in passed file. Assume that whole file contains
        same line terminators
    Args:
        file_path: Path to checking file.

    Returns:
        str: One of the possible line terminators '\\r\\n', '\\n', '\\r'
    """
    with open(file_path, 'rb') as s:
        first_line = s.readline()
    if b'\r\n' in first_line:
        return '\\r\\n'
    elif b'\n' in first_line:
        return '\\n'
    else:
        return '\\r'


def create_sql_for_load_csv(
        file_to_load, replace, table_name, columns_names,
        fields_terminator=',', line_terminator=None, ignore_lines=0):
    """Generate SQL for loading temp CSV file to the specified table.
    Args:
        file_to_load (str): Path to CSV file.
        replace (bool): If True, input rows replace existing row (by primary
        key or unique index).
        table_name (str): Name of db table.
        columns_names (list of str): List of columns names.
        fields_terminator (str): Fields terminater character.
        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.
        ignore_lines (int): Number of lines to ignore at the start of the file.
    Return:
        sql (str): SQL statement.
    """

    column_names_arg = ', '.join(columns_names)

    if replace:
        replace_arg = 'REPLACE'
    else:
        replace_arg = ''

    # defining TERMINATED BY param for MySQL command
    possible_line_terminators = ['\\r\\n', '\\r', '\\n']
    if line_terminator:
        if line_terminator in possible_line_terminators:
            line_terminator_arg = line_terminator
        else:
            raise ValueError('line_terminator value can only one of {}'.format(
                possible_line_terminators))
    else:
        line_terminator_arg = _get_line_terminator(file_to_load)

    sql = (
        "LOAD DATA LOCAL INFILE '{file_name}' {replace_param} "
        'INTO TABLE {table_name} '
        "FIELDS TERMINATED BY '{fields_terminator}' "
        "LINES TERMINATED BY '{line_terminator}' "
        'IGNORE {ignore_lines} LINES ({column_names})'.
        format(
            file_name=file_to_load, replace_param=replace_arg,
            table_name=table_name, fields_terminator=fields_terminator,
            line_terminator=line_terminator_arg, ignore_lines=ignore_lines,
            column_names=column_names_arg))
    return sql


def create_sql_for_insert_or_replace_from_csv(table_name, columns_names):
    """Generate SQL for loading temp CSV file to the specified table.

    Args:
        table_name (str): Name of db table.
        columns_names (list of str): List of columns names.
    Return:
        sql (str): SQL statement.
    """

    column_names_arg = ', '.join(columns_names)

    update_arg_list = []
    for column_name in columns_names:
        update_arg_list.append(
            '{column_name}=NEW.{column_name}, '.format(
                column_name=column_name))
    update_arg = ''.join(update_arg_list).rstrip(', ')

    values_arg = '%s,' * len(columns_names)
    values_arg = values_arg.rstrip(',')

    # 'VALUES function' is deprecated and will be removed in a future release.
    # Recommended to use an alias: VALUES (...) AS alias ... col = alias.col;
    sql = (
        'INSERT INTO {table_name} ({column_names}) '
        'VALUES ({values_arg}) AS NEW '
        'ON DUPLICATE KEY UPDATE {update_arg}'.format(
            table_name=table_name,
            column_names=column_names_arg,
            values_arg=values_arg,
            update_arg=update_arg))
    return sql