"""Config for SWF Snowflake views."""

import os

SWF_FLOW_NAME = 'snowflake_views'
SWF_FLOW_VERSION = '1.0'

SF_PARAMS = {
    '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')}

SF_MICROSERVICE_PARAMS = dict(SF_PARAMS)
SF_MICROSERVICE_PARAMS.update(
    {'warehouse': os.environ.get('SNOWFLAKE_MICROSERVICE_WAREHOUSE')})

SF_CACHE_WARMUP_ATTEMPT_LIMIT = int(os.environ.get(
    'SF_CACHE_WARMUP_ATTEMPT_LIMIT', 5))

INGESTION_STATUS_TABLE = os.environ.get('INGESTION_STATUS_TABLE')
AWS_REGION = os.environ.get('AWS_REGION')

VIEW_CONFIG = {
    'summary_geographics_by_isrc': {
        'table_name': 'SUMMARY_GEOGRAPHICS_BY_ISRC',
        'create_select': (
            'SELECT '
            '  download_activity_date,'
            '  labelid,'
            '  subaccountid,'
            '  artistid,'
            '  isrc,'
            '  fd.territory_code as territory_code,'
            '  coalesce(drm.orchard_region_name, \'Unknown\')'
            '    as orchard_region_name,'
            '  coalesce(drm.orchard_region_code, drm.region_code)'
            '    as orchard_region_code,'
            '  sum(streams) as streams,'
            '  hll_export(hll_accumulate(user_id)) as hll_users_variant,'
            '  TO_VARCHAR(hll_users_variant) as hll_users_varchar,'
            '  CAST(current_timestamp as TIMESTAMP_NTZ) as processed_datetime '
            'FROM %(db)i.%(schema)i.FACT_DEMOGRAPHICS fd '
            'LEFT OUTER JOIN'
            '  %(db)i.%(schema)i.DEMOGRAPHICS_REGION_MAPPING drm'
            '  ON fd.territory_code = drm.territory_code'
            '  AND fd.region_code = drm.region_code '
            'WHERE download_activity_date BETWEEN'
            '  dateadd(day, -370, CURRENT_DATE()) AND CURRENT_DATE() '
            'GROUP BY 1,2,3,4,5,6,7,8 '
            'ORDER BY'
            '  labelid,'
            '  download_activity_date,'
            '  subaccountid,'
            '  artistid,'
            '  isrc,'
            '  territory_code,'
            '  orchard_region_name,'
            '  orchard_region_code'),
        'grant_params': {
            'privs': 'SELECT',
            'role': '%(db)i_DB_%(schema)i_SCHEMA_READ'},
        'cluster_dims':
            'labelid, download_activity_date,'
            'subaccountid, artistid, isrc',
        'state_sql': (
            'SELECT CAST(MAX(processed_timestamp) as TIMESTAMP_NTZ) FROM '
            '%(db)i.%(schema)i.FACT_DEMOGRAPHICS')},
    'summary_geographics_by_artist': {
        'table_name': 'SUMMARY_GEOGRAPHICS_BY_ARTIST',
        'create_select': (
            'SELECT '
            '  download_activity_date,'
            '  labelid,'
            '  subaccountid,'
            '  artistid,'
            '  territory_code,'
            '  orchard_region_name,'
            '  orchard_region_code,'
            '  sum(streams) as streams,'
            '  hll_export(hll_combine(hll_import(hll_users_variant)))'
            '    as hll_users_variant,'
            '  TO_VARCHAR(hll_export(hll_combine('
            '   hll_import(hll_users_variant)))) as hll_users_varchar,'
            '  CAST(current_timestamp as TIMESTAMP_NTZ) as processed_datetime '
            'FROM %(db)i.%(schema)i.SUMMARY_GEOGRAPHICS_BY_ISRC '
            'GROUP BY 1,2,3,4,5,6,7 '
            'ORDER BY'
            '  labelid,'
            '  download_activity_date,'
            '  subaccountid,'
            '  artistid,'
            '  territory_code,'
            '  orchard_region_name,'
            '  orchard_region_code'),
        'grant_params': {
            'privs': 'SELECT',
            'role': '%(db)i_DB_%(schema)i_SCHEMA_READ'},
        'cluster_dims':
            'labelid, download_activity_date,'
            'subaccountid, artistid',
        'state_sql': (
            'SELECT CAST(MAX(processed_datetime) as TIMESTAMP_NTZ) FROM '
            '%(db)i.%(schema)i.SUMMARY_GEOGRAPHICS_BY_ISRC')},
    'summary_geographics_by_label': {
        'table_name': 'SUMMARY_GEOGRAPHICS_BY_LABEL',
        'create_select': (
            'SELECT '
            '  download_activity_date,'
            '  labelid,'
            '  subaccountid,'
            '  territory_code,'
            '  orchard_region_name,'
            '  orchard_region_code,'
            '  sum(streams) as streams,'
            '  hll_export(hll_combine(hll_import(hll_users_variant)))'
            '    as hll_users_variant,'
            '  TO_VARCHAR(hll_export(hll_combine('
            '    hll_import(hll_users_variant)))) as hll_users_varchar '
            'FROM %(db)i.%(schema)i.SUMMARY_GEOGRAPHICS_BY_ARTIST '
            'GROUP BY 1,2,3,4,5,6 '
            'ORDER BY'
            '  labelid,'
            '  download_activity_date,'
            '  subaccountid,'
            '  territory_code,'
            '  orchard_region_name,'
            '  orchard_region_code'),
        'grant_params': {
            'privs': 'SELECT',
            'role': '%(db)i_DB_%(schema)i_SCHEMA_READ'},
        'cluster_dims': 'labelid, download_activity_date, subaccountid',
        'state_sql': (
            'SELECT CAST(MAX(processed_datetime) as TIMESTAMP_NTZ) FROM '
            '%(db)i.%(schema)i.SUMMARY_GEOGRAPHICS_BY_ARTIST')}}