"""Projections ETL related queries."""

from flows.projections import config


PROJECTIONS_ANCHOR_DATE = """
    SELECT
        min(`union_table`.`date`) as `anchor_date`
    FROM
        (
            (
                SELECT
                    min(`date`) as `date`
                FROM
                    {digital_revenue}
                WHERE
                    upc = %(upc)s
                GROUP BY
                    upc
            )
            UNION
            (
                SELECT
                    min(`date`) as `date`
                FROM
                    {cable_revenue}
                WHERE
                    upc = %(upc)s
                GROUP BY
                    upc
            )
            UNION
            (
                SELECT
                    min(`date`) as `date`
                FROM
                    {theatrical_revenue}
                WHERE
                    upc = %(upc)s
                GROUP BY
                    upc
            )
            UNION
            (
                SELECT
                    min(`date`) as `date`
                FROM
                    {accounting_revenue}
                WHERE
                    upc = %(upc)s
                GROUP BY
                    upc
            )
        ) as union_table;""".format(
    digital_revenue=config.DIGITAL_REVENUE,
    cable_revenue=config.CABLE_REVENUE,
    theatrical_revenue=config.THEATRICAL_REVENUE,
    accounting_revenue=config.ACCOUNTING_REVENUE)

ORIGINAL_PROJECTIONS_ANCHOR_DATE = """
    SELECT
        min(`union_table`.`date`) as `anchor_date`
    FROM
        (
            (
                SELECT
                    min(`release_date`) as date
                FROM
                    {releases}
                WHERE
                    upc = %(upc)s
                GROUP BY
                    upc
            )
            UNION
            (
                SELECT
                    min(`theatrical_release_date`) as date
                FROM
                    {releases}
                WHERE
                    upc = %(upc)s
                GROUP BY
                    upc
            )
        ) AS union_table;""".format(
    releases=config.RELEASES)

DROP_TABLE = 'DROP TABLE {table_name};'

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

INSERT_TO_TEMP_TABLE = """
    INSERT INTO {table_name} (upc, date, transaction_type_id, amount)
        VALUES (%s, %s, %s, %s);
"""

COPY_TO_PROD = """
    REPLACE INTO {prod_table} (upc, date, transaction_type_id, amount)
        SELECT upc, date, transaction_type_id, amount
        FROM {temp_table};
"""