"""Distribution Fee ETL specific queries."""

from argparse import ArgumentTypeError

from flows import queries as flows_queries
from flows.config import ITERATION_BATCH_SIZE
from flows.distribution_fee.config import TABLE_DS_CLIENT_AMOUNT
from flows.util import batch_param_calls


CREATE_TEMP_TABLE_FEE_DMS = """
CREATE TABLE IF NOT EXISTS
    `{table_name}`
    (
        `country_id` SMALLINT,
        `split` FLOAT,
        `upc` BIGINT,
        UNIQUE INDEX uniq_upc_country_id(`upc`, `country_id`)
    )
"""


CREATE_TEMP_TABLE_FEE_REGULAR = """
CREATE TABLE IF NOT EXISTS
    `{table_name}`
    (
        `split` FLOAT,
        `upc` BIGINT,
        UNIQUE INDEX uniq_upc(`upc`)
    )
"""


CREATE_TEMP_TABLE_FEE_TERRITORY = """
CREATE TABLE IF NOT EXISTS
    `{table_name}`
    (
        `country_id` SMALLINT,
        `split` FLOAT,
        `upc` BIGINT,
        UNIQUE INDEX uniq_upc_country_id(`upc`, `country_id`)
    )
"""


CREATE_TEMP_TABLE_VENDOR_CONTRACT = """
CREATE TABLE IF NOT EXISTS
    `{table_name}`
    (
        `vendor_contract_id` INTEGER,
        `vendor_id` INTEGER,
        `upc` BIGINT
    )
"""


DROP_TEMP_TABLE = """
    DROP TABLE IF EXISTS `{table_name}`
"""


TEMP_TABLE_NAME_FEE_DMS = 'df_{cid}_fee_dms'
TEMP_TABLE_NAME_FEE_REGULAR = 'df_{cid}_fee_regular'
TEMP_TABLE_NAME_FEE_TERRITORY = 'df_{cid}_fee_territory'
TEMP_TABLE_NAME_VENDOR_CONTRACT = 'df_{cid}_vendor_contract'


INSERT_VENDOR_CONTRACT = """
INSERT INTO
    `{table_name}`
    (`vendor_contract_id`, `vendor_id`, `upc`)
VALUES
    (%s, %s, %s)
"""


GET_ACTIVE_VENDOR_CONTRACT = 'call sp_get_active_vendor_contract(%s)'


@batch_param_calls('upcs', ITERATION_BATCH_SIZE)
def get_delete_client_amount_sql(upcs):
    """Generate SQL to clear client_amount for specified UPCs.

    Args:
        upcs (list(str)): list of upcs to process.

    Returns:
        str: resulting SQL DELETE statement.
    """
    return 'DELETE FROM client_amount WHERE {upcs_condition} 1'.format(
        upcs_condition=flows_queries.sql_upcs_condition_in(upcs))


@batch_param_calls('upcs', ITERATION_BATCH_SIZE)
def get_insert_client_amount_sql(
        table, column, fee_dms_table, fee_ter_table, fee_reg_table, upcs):
    """Generate client_amount calculation and insert SQL statement.

    Args:
        table (str): source revenue table to calculate from.
        column (str): source revenue table's column to calculate from.
        fee_dms_table (str): temp table with dms fee data.
        fee_ter_table (str): temp table with territory fee data.
        fee_reg_table (str): temp table with regular fee data.
        upcs (list(str)): list of upcs to process.

    Returns:
        str: resulting SQL INSERT statement.
    """
    return """
        INSERT INTO {target_table} (
            upc, date, amount, transaction_type_id, country_id)

        SELECT
            tr.upc,
            tr.date,
            tr.{src_column} * COALESCE(dms.split, ter.split, reg.split),
            tr.transaction_type_id,
            tr.country_id
        FROM {src_table} AS tr
        LEFT JOIN {fee_dms_table} AS dms
            ON tr.upc = dms.upc AND tr.country_id = dms.country_id
        LEFT JOIN {fee_ter_table} AS ter
            ON tr.upc = ter.upc AND tr.country_id = ter.country_id
        LEFT JOIN {fee_reg_table} AS reg
            ON tr.upc = reg.upc
        WHERE
            {upcs_condition} TRUE
    """.format(
            src_table=table,
            src_column=column,
            target_table=TABLE_DS_CLIENT_AMOUNT,
            upcs_condition=flows_queries.sql_upcs_condition_in(
                upcs, column_name='tr.upc'),
            fee_dms_table=fee_dms_table,
            fee_ter_table=fee_ter_table,
            fee_reg_table=fee_reg_table)


@batch_param_calls('upcs', ITERATION_BATCH_SIZE)
def get_delete_distribution_table_data_sql(upcs):
    """Generate SQL to clear distribution_fee table for specified UPCs.

    Args:
        upcs (list(str)): list of upcs to process.

    Returns:
        str: resulting SQL DELETE statement.
    """
    return 'DELETE FROM distribution_fee WHERE {upcs_condition} 1'.format(
        upcs_condition=flows_queries.sql_upcs_condition_in(upcs))


def get_insert_distribution_table_data_sql(source_table, fee_type):
    """Generate SQL to insert data in distribution_fee table for specified UPCs.

    Args:
        source_table (str): source temp table name.
        fee_type (str): type of distribution fee to determine priority.

    Returns:
        str: resulting SQL insert statement.
    """
    country_id = 'country_id'

    if fee_type == 'regular':
        country_id = 'NULL'

    return """
        INSERT INTO distribution_fee
            (upc, country_id, split, fee_type_id)
        SELECT
            upc,
            {country_id},
            split,
            (SELECT
                id
            FROM
                distribution_fee_type
            WHERE
                fee_type = '{fee_type}')
        FROM
            {source_table};
    """.format(
        country_id=country_id,
        fee_type=fee_type,
        source_table=source_table)


@batch_param_calls('contract_ids', ITERATION_BATCH_SIZE)
def get_select_contract_fee_sql(contract_ids, fee_type):
    """Generate SQL query to get fee rates and other data for given contracts.

    This function is a generator due to the batching decorator.

    Args:
        contract_ids (list): contract IDs to look up.
        fee_type (str): regular or territory contract types.

    Raises:
        ArgumentTypeError: contract IDs are not decimal numbers.

    Yields:
        str: sql query to lookup contracts.
    """
    if not flows_queries._only_decimal(contract_ids):
        raise ArgumentTypeError('Contract IDs are not in valid format.')

    if fee_type == 'regular':
        query = SELECT_FEE_REGULAR
    elif fee_type == 'territory':
        query = SELECT_FEE_TERRITORY
    fee_where = ','.join(map(str, contract_ids))
    fee_query = query.format(vendor_contract_ids=fee_where)
    return fee_query


SELECT_TEMP_CONTRACTS = """
    SELECT
        vendor_contract_id, vendor_id, upc
    FROM
        {table_name}
"""


SELECT_FEE_REGULAR = """
    SELECT
        vendor_id, digital_split
    FROM
        vendor_contract
    WHERE
        id in ({vendor_contract_ids})
"""


INSERT_FEE_REGULAR = """
    INSERT INTO
        {table_name} (split, upc)
    VALUES
        (%s, %s)
"""


SELECT_FEE_TERRITORY = """
    SELECT
        vendor_id, country_id, territory_split
    FROM
        vendor_territory_contract
    WHERE
        vendor_contract_id IN ({vendor_contract_ids})
"""


INSERT_FEE_TERRITORY = """
    INSERT INTO
        {table_name} (country_id, split, upc)
    VALUES
        (%s, %s, %s)
"""