"""Configuration for SWF workflow."""

import os

sf_config = dict(
    role=os.environ.get('SNOWFLAKE_ROLE'),
    warehouse=os.environ.get('SNOWFLAKE_WAREHOUSE'),
    db=os.environ.get('SNOWFLAKE_DATABASE'),
    schema=os.environ.get('SNOWFLAKE_SCHEMA'),
    user=os.environ.get('SNOWFLAKE_USER'),
    password=os.environ.get('SNOWFLAKE_PASSWORD'),
    account=os.environ.get('SNOWFLAKE_ACCOUNT')
)

aws = dict(
    aws_access_key=os.environ.get('AWS_ACCESS_KEY_ID'),
    aws_access_secret=os.environ.get('AWS_SECRET_ACCESS_KEY')
)


sentry_dsn = os.environ.get('SENTRY_DSN')
name = 'label_aggregation'

# Max size of a file uploaded from Snowflake
s3_upload_max_size_bytes = 5 * 1024 * 1024 * 1024

# NOTE: Production uses the "prod-aggregate-load" bucket.
#   To use alternatives (as you will probably need to in dev)
#   add the S3_BUCKET environment variable.
s3_path = os.environ.get('S3_BUCKET') or 's3://{env}-aggregate-load'
snowflake_base = (s3_path +
                  '/to-import/{config}-{start_date}-{end_date}/{slug}_')
dynamodb_base = s3_path + '/dynamodb/'

label_social_query = '''
    SELECT
        CASE fa.subaccountid
            WHEN 0 THEN ('L' || ':' ||
                        fa.labelid || ':' ||
                        fa.transactiontypeabbr)
            ELSE ('S' || ':' || NVL(fa.subaccountid, 0) ||
                        ':' || fa.transactiontypeabbr)
            END AS metric,
        fa.displayDate AS display_date,
        fa.labelid AS label_id,
        fa.subaccountid AS subaccount_id,
        sum(fa.activities) AS activities,
        0 AS activities_paid
    FROM (
            SELECT
                fsss.labelid,
                fsss.dayId,
                dd.displaydate,
                NVL(fsss.subaccountid, 0) as subaccountid,
                fsss.socialActionId,
                sum(fsss.dailyCount) as activities,
                fsss.socialmediaid,
                (case fsss.socialActionId
                    when 1 then 10001
                    when 2 then 10002
                    when 3 then 10003
                    end ) as transactiontypeid,
                (case fsss.socialActionId
                    when 1 then ('FFA')
                    when 2 then ('FFR')
                    when 3 then ('FEI')
                    end ) as transactiontypeabbr,
                (case fsss.socialActionId
                    when 1 then ('Social Fan Gain')
                    when 2 then ('Social Fan Lost')
                    when 3 then ('Social Fan Engagement')
                    end ) as transactiontypedesc
            FROM
                fact_social fsss
            INNER JOIN
                dim_day dd on dd.dayId = fsss.dayId
            WHERE
                dd.displaydate

                    BETWEEN '{start_date}' AND '{end_date}'
                AND socialmediaid IN (1)
                AND fsss.socialActionId in (1, 2, 3)
            GROUP BY
                dd.displaydate,
                fsss.labelid,
                fsss.subaccountid,
                fsss.dayId,
                socialActionId,
                socialmediaid
            ) fa

        INNER JOIN
            dim_socialaction dsa
                ON dsa.socialActionId = fa.socialActionId
        INNER JOIN
            dim_socialmedia dsm
                ON dsm.socialmediaid = fa.socialmediaid

    WHERE {sql_where_transaction}

    GROUP BY
        fa.dayid,
        fa.labelid,
        dsa.socialAction,
        fa.socialActionId,
        dsm.name,
        fa.subaccountid,
        fa.displayDate,
        fa.transactiontypeid,
        fa.transactiontypeabbr,
        fa.transactiontypedesc'''

distributor_social_query = '''
    SELECT
        ('L' || ':' || fa.labelid || ':' ||
            fa.transactiontypeabbr) as metric,
        fa.displayDate AS display_date,
        fa.labelid AS label_id,
        fa.subaccountid AS subaccount_id,
        sum(fa.activities) AS activities,
        0 AS activities_paid
    FROM (
            SELECT
                fsss.labelid,
                fsss.dayId,
                dd.displaydate,
                0 as subaccountid,
                fsss.socialActionId,
                sum(fsss.dailyCount) as activities,
                fsss.socialmediaid,
                (case fsss.socialActionId
                    when 1 then 10001
                    when 2 then 10002
                    when 3 then 10003
                    end ) as transactiontypeid,
                (case fsss.socialActionId
                    when 1 then ('FFA')
                    when 2 then ('FFR')
                    when 3 then ('FEI')
                    end ) as transactiontypeabbr,
                (case fsss.socialActionId
                    when 1 then ('Social Fan Gain')
                    when 2 then ('Social Fan Lost')
                    when 3 then ('Social Fan Engagement')
                    end ) as transactiontypedesc
            FROM
                fact_social fsss
            INNER JOIN
                dim_day dd on dd.dayId = fsss.dayId
            WHERE
                dd.displaydate
                    BETWEEN '{start_date}' AND '{end_date}'
                AND socialmediaid IN (1)
                AND fsss.socialActionId in (1, 2, 3)
            GROUP BY
                dd.displaydate,
                fsss.labelid,
                fsss.subaccountid,
                fsss.dayId,
                socialActionId,
                socialmediaid
            ) fa

         INNER JOIN
            dim_socialaction dsa
                ON dsa.socialActionId = fa.socialActionId
         INNER JOIN
            dim_socialmedia dsm
                ON dsm.socialmediaid = fa.socialmediaid

    WHERE {sql_where_transaction}

    GROUP BY
        fa.dayid,
        fa.labelid,
        dsa.socialAction,
        fa.socialActionId,
        dsm.name,
        fa.subaccountid,
        fa.displayDate,
        fa.transactiontypeid,
        fa.transactiontypeabbr,
        fa.transactiontypedesc'''

label_query = '''
    SELECT
        CASE fa.subaccountid
            WHEN 0
                THEN ('L' || ':' || fa.labelid || ':' ||
                    dtt.transactiontypeabbr)
            ELSE
                ('S' || ':' || NVL(fa.subaccountid,0) ||
                    ':' || dtt.transactiontypeabbr)
            END as metric,
        dd.displaydate AS display_date,
        fa.labelid AS label_id,
        fa.subaccountid AS subaccount_id,
        fa.activities AS activities,
        fa.activities_paid AS activities_paid
    FROM (
        SELECT
            fa.labelid,
            NVL(fa.subaccountid,0) as subaccountid,
            fa.dayid,
            fa.transactiontypeid,
            SUM(fa.units) AS activities,
            SUM(fa.paidunits) as activities_paid FROM fact_analytics fa
            INNER JOIN dim_day dd on dd.dayid = fa.dayid

          WHERE
            {sql_where_transaction} AND
            dd.displaydate BETWEEN '{start_date}' AND '{end_date}'

          GROUP BY
            fa.dayid,
            fa.labelid,
            fa.subaccountid,
            fa.transactiontypeid
        ) fa
    INNER JOIN dim_transactiontype dtt ON
        dtt.transactiontypeid = fa.transactiontypeid
    INNER JOIN dim_day dd on dd.dayid = fa.dayid'''

distributor_query = '''
        SELECT
          'L' || ':' || fa.labelid || ':' ||
            dtt.transactiontypeabbr AS metric,
          dd.displaydate AS display_date,
          fa.labelid AS label_id,
          fa.subaccountid AS subaccount_id,
          fa.activities AS activities,
          fa.activities_paid AS activities_paid
        FROM (
            SELECT
                fa.labelid,
                0 AS subaccountid,
                fa.dayid,
                fa.transactiontypeid,
                SUM(fa.units) AS activities,
                SUM(fa.paidunits) AS activities_paid FROM fact_analytics fa
                INNER JOIN dim_day dd on dd.dayid = fa.dayid

              WHERE
                {sql_where_transaction} AND
                    fa.subaccountid != 0 AND
                    dd.displaydate between '{start_date}' AND
                    '{end_date}'

              GROUP BY fa.dayid, fa.labelid, fa.transactiontypeid
            ) fa
        INNER JOIN dim_transactiontype dtt ON
            dtt.transactiontypeid = fa.transactiontypeid
        INNER JOIN dim_day dd on dd.dayid = fa.dayid'''

album_query = '''
        SELECT
          'A:' || fa.catalogid || ':' ||
            dtt.transactiontypeabbr AS metric,
          dd.displaydate AS display_date,
          fa.labelid AS label_id,
          fa.subaccountid AS subaccount_id,
          fa.activities AS activities,
          fa.activities_paid AS activities_paid
        FROM (
            SELECT
                fa.catalogid,
                fa.labelid,
                fa.subaccountid,
                fa.dayid,
                fa.transactiontypeid,
                SUM(fa.units) AS activities,
                SUM(fa.paidunits) AS activities_paid
            FROM
                fact_analytics fa
            INNER JOIN
                dim_day dd
                    ON dd.dayid = fa.dayid
            WHERE
                {sql_where_transaction} AND
                dd.displaydate BETWEEN '{start_date}' AND
                '{end_date}'
            GROUP BY
                fa.catalogid, fa.subaccountid, fa.dayid, fa.labelid,
                fa.transactiontypeid
            ) fa
        INNER JOIN
            dim_transactiontype dtt
                ON dtt.transactiontypeid = fa.transactiontypeid
        INNER JOIN
            dim_day dd on dd.dayid = fa.dayid'''

context = {
    'snowflake.query': '''
        SELECT
            metric, display_date, label_id, subaccount_id, activities,
            activities_paid
        FROM
            (({social_distributor})
             UNION ({social_label})
             UNION ({label})
             UNION ({distributor})
             UNION ({album}))
        ORDER BY
            metric, display_date'''.format(
        social_distributor=distributor_social_query,
        social_label=label_social_query,
        label=label_query,
        distributor=distributor_query,
        album=album_query),

    # Snowflake configuration
    'snowflake.dest': snowflake_base,
    'dynamodb.dest': dynamodb_base,

    # Dynamodb table configuration
    'dynamodb.table_name': '{env}_aggregate_analytics',
    'dynamodb.throughput_write': 10000,
    'dynamodb.default_throughput_write': 10,
}