"""Geocoding flow settings."""

import os

ENV = os.getenv('Environment')
TEMP_S3_PATH = (
    's3://{env}-staging-dim-imports/lat_long/lat_log.txt'.format(
        env=ENV))
TEMP_TABLE_NAME = 'dim_zip_mapping'
REQUEST_LIMIT = 100000
TASK_TIMEOUT = 17000
AWS_ACCESS_KEY_ID = os.getenv('AWS_ACCESS_KEY_ID')
AWS_SECRET_ACCESS_KEY = os.getenv('AWS_SECRET_ACCESS_KEY')

SELECT_SQL = """
    SELECT country_code, zipcode
    FROM %(db)i.%(schema)i.dim_zip
    WHERE (latitude IS NULL OR longitude IS NULL)
"""

CREATE_TEMP_TABLE_SQL = """
    CREATE TEMP TABLE %(db)i.%(schema)i.%(table_name)i (
        zip_code VARCHAR(1000),
        country_code VARCHAR(2),
        latitude NUMBER(10,6),
        longitude NUMBER(10,6));
"""

LOAD_TEMP_TABLE_SQL = """
    COPY INTO %(db)i.%(schema)i.%(table_name)i
    FROM %(s3_path)s
    CREDENTIALS=(
      AWS_KEY_ID=%(aws_key_id)s
      AWS_SECRET_KEY=%(aws_secret_key)s)
    FILE_FORMAT=(
      TYPE='CSV'
      FIELD_DELIMITER='\t'
      RECORD_DELIMITER='\n'
      EMPTY_FIELD_AS_NULL=TRUE);
"""

UPDATE_SQL = """
    UPDATE %(db)i.%(schema)i.dim_zip
    SET latitude = dzm.latitude,
    longitude = dzm.longitude, requested = 'Y'
    FROM %(db)i.%(schema)i.%(table_name)i dzm
    WHERE dim_zip.zipcode = dzm.zip_code
    AND dim_zip.country_code = dzm.country_code;
"""