"""Theatrical cuts ETL related queries."""

from flows.theatrical_cuts import config


THEATRICAL_CUTS_ANCHOR_DATE = """
    SELECT min(`date`) as anchor_date
    FROM {theatrical_revenue}
    WHERE upc = %(upc)s
    GROUP BY upc;
""".format(theatrical_revenue=config.THEATRICAL_REVENUE)

DELETE_EXIST_UPC_CUTS = """
    DELETE FROM {theatrical_cuts_raw}
    WHERE upc = %(upc)s;
""".format(theatrical_cuts_raw=config.THEATRICAL_CUTS_RAW)

INSERT_UPC_CUTS = """
    INSERT {theatrical_cuts_raw} (upc, date, percentage)
    VALUES(%(upc)s, %(date)s, %(percentage)s);
""".format(theatrical_cuts_raw=config.THEATRICAL_CUTS_RAW)

UPDATE_REVENUE = """
    UPDATE theatrical_revenue R
    JOIN theatrical_cuts_raw C
        ON (R.upc = C.upc)
            AND (R.date BETWEEN C.date AND DATE_ADD(C.date, INTERVAL 6 DAY))
        SET R.orchard_amount = R.amount * C.percentage / 100
    WHERE R.upc = %(upc)s;
"""