"""Cable Calculation ETL related queries."""

from flows.config import ITERATION_BATCH_SIZE
from flows.queries import sql_upcs_condition_in
from flows.util import batch_param_calls


CREATE_TEMP_TABLE = """
    CREATE TABLE {table_name} (
        -- for split calc
        `resolution` VARCHAR(10),
        `content_type` VARCHAR(10),
        `theatrical_release_date` DATE,
        `home_video_release_date` DATE,
        `vod_start_of_window` DATE,
        `revenue` double DEFAULT NULL,
        -- for final data
        `upc` BIGINT(20) UNSIGNED COMMENT 'UPC with no leading zeros',
        `amount` DECIMAL(18,6) COMMENT 'Transaction amount',
        `orchard_amount` DECIMAL(18,6) COMMENT 'Orchard amount after split',
        `date` DATE COMMENT 'Day of transaction',
        `transaction_type_id` SMALLINT(5) UNSIGNED COMMENT 'Transaction ID',
        `country_id` SMALLINT(5) UNSIGNED COMMENT 'Country ID',
        `units` INT(11) COMMENT 'Number of units',
        `paid` TINYINT(3) UNSIGNED COMMENT 'Units are paid',
        `format_id` TINYINT(3) UNSIGNED COMMENT 'Format ID',
        `split_id` VARCHAR(32) COMMENT 'Split ID used for calculation'
    ) ENGINE=INNODB;"""

TEMP_TABLE_NAME = 'cable_revenue_{correlation_hex}'

DELETE_FROM_CABLE_REVENUE = """
    DELETE FROM
        cable_revenue
    WHERE
        {upc_in_clause}
        date >= %(date_start)s
        AND
        date < %(date_end)s"""

INSERT_FROM_TEMP_TABLE = """
    INSERT INTO
        cable_revenue
        (
            upc,
            display_upc,
            amount,
            date,
            transaction_type_id,
            country_id,
            units,
            paid,
            format_id,
            orchard_amount,
            split_id)
    SELECT
        upc,
        upc AS display_upc,
        revenue,
        date,
        transaction_type_id,
        country_id,
        units,
        paid,
        format_id,
        orchard_amount,
        split_id
    FROM
        {table_name}
    WHERE
        transaction_type_id IS NOT NULL
        AND
        units IS NOT NULL;"""

SELECT_BAD_ROWS_FROM_TEMP = """
    SELECT COUNT(*)
    FROM {table_name}
    WHERE upc IS NULL OR transaction_type_id IS NULL OR units IS NULL;"""


DROP_TEMP_TABLE = 'DROP TABLE {table_name}'

SELECT_FROM_RAW_TABLE = """
    SELECT
        r.operator_id,
        r.resolution,
        r.content_type,
        r.theatrical_release_date,
        r.home_video_release_date,
        IF (
            r.vod_start_of_window is null,
            null, -- preserve default calculation w/null behavior
            s.vod_start_date) as vod_start_of_window,
        r.revenue,
        CAST(r.title_category AS UNSIGNED) as title_category,
        -- Free set to 0
        IF(
            r.box_office_gross IS NULL OR r.box_office_gross = '',
            0,
            r.box_office_gross) AS dbo,
        r.date,
        r.transactions,
        (CASE r.content_type
            WHEN 'FOD' THEN 42
            WHEN 'TOD' THEN 42
            WHEN 'SVOD' THEN 42
            WHEN 'EST' THEN 43 END) AS transaction_type_id,
        IF(r.content_type = 'FOD', 0, 1) AS paid,
        (CASE r.resolution
            WHEN 'SD' THEN 3
            WHEN 'HD' THEN 2
            ELSE 1 END) AS format_id,
        1 AS country_id, -- always USA
        0 AS orchard_amount -- default, calcu will change it
    FROM
        cable_revenue_raw AS r
    LEFT JOIN
        (
            SELECT
                title_category as upc,
                min(vod_start_of_window) as vod_start_date
            FROM
                cable_revenue_raw
            GROUP BY
                title_category
        ) AS s
        ON
        s.upc = r.title_category
    WHERE
        {upc_in_clause}
        r.date >= %(date_start)s
        AND
        r.date < %(date_end)s;"""


INSERT_TO_TEMP_TABLE = """
    INSERT INTO {table_name} (
        `resolution`,
        `content_type`,
        `theatrical_release_date`,
        `home_video_release_date`,
        `vod_start_of_window`,
        `revenue`,
        `upc`,
        `amount`,
        `date`,
        `units`,
        `transaction_type_id`,
        `paid`,
        `format_id`,
        `country_id`,
        `orchard_amount`,
        `split_id`
    ) VALUES
    (
        %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s, %s, %s
    )"""

# datawarehouse query.
SELECT_SALES_DATE_FOR_UPCS = """
    SELECT releaseid as upc, sale_start_date::timestamp::date
    FROM facts.prod.dim_release
    WHERE releaseid IN ({upcs});"""

SELECT_GROSS_FROM_THEATRICAL = """
    SELECT upc, `date`, amount
    FROM theatrical_revenue
    WHERE upc IN ({upcs})"""


@batch_param_calls('upcs', ITERATION_BATCH_SIZE)
def get_delete_from_cable_revenue_sql(upcs):
    """Generate delete from cable revenue queries batched on UPCs.

    Args:
        upcs (list): upcs to include in the delete query.

    Yields:
        str: delete query.
    """
    delete_upcs = sql_upcs_condition_in(upcs, column_name='upc')
    sql = DELETE_FROM_CABLE_REVENUE.format(upc_in_clause=delete_upcs)
    return sql