"""Theatrical ETL related MySQL queries."""

from flows.queries import sql_upcs_condition_in
from flows.theatrical import config
from flows.util import batch_upc_calls

COPY_TO_PROD = """
    INSERT INTO {revenue_table} (
    upc, display_upc, amount, date, transaction_type_id, country_id, units,
    orchard_amount)
    SELECT upc, display_upc, amount, date, transaction_type_id, country_id,
    units, orchard_amount
    FROM {temp_table};
"""

DROP_TABLE = 'DROP TABLE {table_name};'

CREATE_TEMP_TABLE = (
    'CREATE TABLE {temp_table} LIKE {revenue_table};')


def insert_raw_data(column_names):
    """Compose query for inserting raw theatrical data.

    Args:
        column_names (list(str)): List of column names

    Returns:
        str: SQL query.
    """
    return """
      INSERT INTO {revenue_raw_table} ({column_names})
      VALUES ({values});
    """.format(
        revenue_raw_table=config.THEATRICAL_REVENUE_RAW,
        column_names=', '.join(column_names),
        values=', '.join('%s' for _ in column_names))


@batch_upc_calls
def clear_existing_data(table_name, upcs):
    """Compose query for deleting existing data.

    Args:
        table_name (str): table name.
        upcs (list(str)): list of UPCs for erasing.

    Returns:
        str: SQL query.
    """
    return """
        DELETE FROM {table_name}
        WHERE {upc_condition} date BETWEEN %(date_start)s AND %(date_end)s;
    """.format(
        table_name=table_name, upc_condition=sql_upcs_condition_in(upcs))


@batch_upc_calls
def insert_to_temp_from_raw(table_name, upcs):
    """Compose query for inserting data for raw data table.

    Args:
        table_name (str): table name.
        upcs (list(str)): list of UPCs for erasing.

    Returns:
        str: SQL query.
    """
    return """
        INSERT INTO {temp_table} (
            upc, display_upc, amount, date, transaction_type_id, country_id,
            units, orchard_amount)
        SELECT
            upc, RIGHT(CONCAT('0000000000', CAST(upc AS CHAR(12))), 12),
            SUM(gross), date, 41, 1, 1, SUM(gross) * 0.4
        FROM {revenue_raw_table}
        WHERE {upc_condition} date BETWEEN %(date_start)s AND %(date_end)s
        GROUP BY upc, date;
    """.format(
        temp_table=table_name, upc_condition=sql_upcs_condition_in(upcs),
        revenue_raw_table=config.THEATRICAL_REVENUE_RAW)