"""Cable Ingestion ETL SQL Queries."""


CREATE_TEMP_TABLE = 'CREATE TABLE {table_name} LIKE cable_revenue_raw'

TEMP_TABLE_NAME = 'cable_revenue_raw_{correlation_hex}'

INSERT_TO_TEMP_TABLE = """
    INSERT INTO
        {table_name}
        (
            week_ending,
            date,
            country,
            operator_id,
            operator,
            tv_market_id,
            tv_market_name,
            rolled_up_provider_id,
            rolled_up_provider_name,
            provider_id,
            provider,
            title_id,
            title,
            rolled_up_title_id,
            rolled_up_title_name,
            rating,
            genre,
            box_office_gross,
            theatrical_release_date,
            home_video_release_date,
            release_window,
            vod_start_of_window,
            vod_end_of_window,
            vod_window,
            no_of_weeks_in_window,
            platform_channel,
            run_time,
            resolution,
            asset_id,
            content_type,
            window_type,
            transactions,
            revenue,
            unique_stbs,
            sybs_using,
            buy_rate,
            playtime,
            average_playtime,
            no_rewinds,
            no_fast_forwards,
            no_pauses,
            studio,
            title_category,
            title_subcategory
        )
    VALUES
        (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
        )"""

RAW_TABLE_NAME = 'cable_revenue_raw'

TABLE_DATE_RANGE = """
    SELECT
        min(date) as date_start,
        max(date) as date_end
    FROM
        {table_name}"""

DELETE_FROM_TABLE_BY_DATE = """
    DELETE FROM
        {table_name}
    WHERE
        date BETWEEN %(date_start)s AND %(date_end)s"""

INSERT_SELECT_TO_RAW_TABLE = """
    INSERT INTO
        cable_revenue_raw
        (
            week_ending,
            date,
            country,
            operator_id,
            operator,
            tv_market_id,
            tv_market_name,
            rolled_up_provider_id,
            rolled_up_provider_name,
            provider_id,
            provider,
            title_id,
            title,
            rolled_up_title_id,
            rolled_up_title_name,
            rating,
            genre,
            box_office_gross,
            theatrical_release_date,
            home_video_release_date,
            release_window,
            vod_start_of_window,
            vod_end_of_window,
            vod_window,
            no_of_weeks_in_window,
            platform_channel,
            run_time,
            resolution,
            asset_id,
            content_type,
            window_type,
            transactions,
            revenue,
            unique_stbs,
            sybs_using,
            buy_rate,
            playtime,
            average_playtime,
            no_rewinds,
            no_fast_forwards,
            no_pauses,
            studio,
            title_category,
            title_subcategory
        )
    SELECT
        week_ending,
        date,
        country,
        operator_id,
        operator,
        tv_market_id,
        tv_market_name,
        rolled_up_provider_id,
        rolled_up_provider_name,
        provider_id,
        provider,
        title_id,
        title,
        rolled_up_title_id,
        rolled_up_title_name,
        rating,
        genre,
        box_office_gross,
        theatrical_release_date,
        home_video_release_date,
        release_window,
        vod_start_of_window,
        vod_end_of_window,
        vod_window,
        no_of_weeks_in_window,
        platform_channel,
        run_time,
        resolution,
        asset_id,
        content_type,
        window_type,
        transactions,
        revenue,
        unique_stbs,
        sybs_using,
        buy_rate,
        playtime,
        average_playtime,
        no_rewinds,
        no_fast_forwards,
        no_pauses,
        studio,
        title_category,
        title_subcategory
    FROM
        {table_name}"""

DROP_TEMP_TABLE = 'DROP TABLE {table_name}'

SELECT_ETL_LOG = """
    SELECT
        correlation_id,
        workflow_run_id,
        date_start,
        date_end,
        etl_start,
        etl_end,
        etl_status
    FROM
        cable_revenue_ingestion_etl_log
    WHERE
        correlation_id = %(correlation_id)s"""