"""Sales data queries to any database."""

# AR Queries.

GET_PERIOD_DATA_BY_ID = """
    SELECT
        period_id, year, month, quarter
    FROM
        `period`
    WHERE period_id = {period_id}"""

# FT Queries.

DELETE_RAW_DATA = """
    DELETE FROM
        accounting_revenue_raw
    WHERE
        {upc_in_clause}
        accounting_period_id = {accounting_period_id}"""

CREATE_TEMP_RAW_TABLE = """
    CREATE TABLE IF NOT EXISTS
        {table_name}
    LIKE
        accounting_revenue_raw"""

DROP_TABLE = """
    DROP TABLE
        {table_name}"""

INSERT_TO_TEMP_RAW_TABLE = """
    INSERT INTO
        {table_name}
        ({column_names})
    VALUES
        ({values_placeholder})"""

INSERT_RAW_DATA = """
    INSERT INTO
        accounting_revenue_raw
        ({column_names})
    SELECT
        {column_names}
    FROM
        {table_name}"""

GET_STORE_IDS_FOR_GROUP = """
    SELECT
        store_id
    FROM
        stores
    WHERE
        `group` = '{group}'"""

DELETE_REVENUE_DATA = """
    DELETE FROM
        accounting_revenue
    WHERE
        upc = %(upc)s
        AND
        date BETWEEN %(date_start)s AND %(date_end)s"""

INSERT_REVENUE_DATA = """
    INSERT INTO
        accounting_revenue
        (upc, date, amount, transaction_type_id, country_id, store_id)
    VALUES
        (
            %(upc)s, %(date)s, %(amount)s, %(transaction_type_id)s,
            %(country_id)s, %(store_id)s)"""

SELECT_AGGREGATE_RAW_DATA = """
    SELECT
        (SUM(net_receipt) / DAY(LAST_DAY(activity_date))) as daily_amount,
        activity_date as date_start,
        LAST_DAY(activity_date) as date_end,
        country_id,
        raw.store_id,
        raw.transaction_type_id AS new_transaction_type_id,
        upc
    FROM
        accounting_revenue_raw raw
    WHERE
        {upc_in_clause}
        -- get all activities to recalculate since the accounting_revenue
        -- table does not have accounting period IDs
        activity_period_id IN (
            SELECT DISTINCT
                activity_period_id
            FROM
                accounting_revenue_raw
            WHERE
                accounting_period_id = %(accounting_period_id)s
        )
    GROUP BY
        activity_date,
        country_id,
        raw.store_id,
        new_transaction_type_id,
        upc"""

DELETE_REVENUE_DATA_MASS = """
    DELETE ar
    FROM accounting_revenue ar
    JOIN (
        SELECT
            activity_date as date_start,
            LAST_DAY(activity_date) as date_end,
            upc
        FROM
            accounting_revenue_raw raw
        WHERE
            {upc_in_clause}
            -- get all activities to recalculate since the accounting_revenue
            -- table does not have accounting period IDs
            activity_period_id IN (
                SELECT DISTINCT
                    activity_period_id
                FROM
                    accounting_revenue_raw
                WHERE
                    accounting_period_id = %(accounting_period_id)s
            )
        GROUP BY
            activity_date,
            upc) full_raw
    ON ar.upc = full_raw.upc
        AND ar.date BETWEEN full_raw.date_start AND full_raw.date_end;
    """

COUNT_INGESTED_ACCOUNTING_PERIOD_ID = """
    SELECT
        COUNT(*)
    FROM
        accounting_revenue_etl_log
    WHERE
        accounting_period_id = %(accounting_period_id)s
        AND
        etl_status = 'INGESTED'
    """

# SF Queries.

UNLOAD_COLUMNS = [
    'accounting_period_id', 'accounting_date', 'activity_period_id',
    'activity_date', 'upc', 'display_upc', 'store_id', 'country_id',
    'transaction_type_id', 'net_receipt', 'units']

SELECT_SALES_DATA_FOR_UNLOAD = """
    SELECT
        fs.accountingperiodid as accounting_period_id,
        date_from_parts(p1.year, p1.month, 1) AS accounting_date,
        fs.activityperiodid as activity_period_id,
        date_from_parts(p2.year, p2.month, 1) AS activity_date,
        fs.releaseid as upc,
        fs.releaseid as display_upc,
        fs.storeid as store_id,
        fs.countryid as country_id,
        fs.transactiontypeid as transaction_type_id,
        SUM(net_receipt) AS net_receipt,
        SUM(sales) AS units
    FROM
        fact_sales fs
    INNER JOIN
        dim_period p1
        ON
        p1.periodid = fs.accountingperiodid
    INNER JOIN
        dim_period p2
        ON
        p2.periodid = fs.activityperiodid
    WHERE
        {upcs_where_clause}
        fs.accountingperiodid BETWEEN {start_period} AND {end_period}
    GROUP BY
        fs.releaseid,
        fs.countryid,
        fs.storeid,
        fs.transactiontypeid,
        fs.accountingperiodid,
        accounting_date,
        fs.activityperiodid,
        activity_date"""

COPY_TO_S3_COMMAND = """
    COPY INTO
        '{destination}'
    FROM
        ({select})
    CREDENTIALS=(
        AWS_KEY_ID='{access_key_id}'
        AWS_SECRET_KEY='{secret_access_key}')
    FILE_FORMAT=(
        {options}
    )
    SINGLE = TRUE"""