"""Constants module."""

# Just a hint that these are the only externally used variables.
__all__ = (
    'SQL_MS_SELECT_DATA',
    'SQL_SF_COPY_TO_TEMP',
    'SF_TABLE_TEMP_FULLNAME',
    'S3_PATH_PREFIX',
    'CSV_FILE_NAME',
    'CSV_COLUMNS',
    'CSV_EMPTY_STR',
    'COLUMN_STORE_INTERNAL_ID',
    'COLUMN_STORE_ID',
    'COLUMN_STOREFRONT_URL',
)


# Fields constants
# ----------------

# Snowflake tables
SF_SCHEMA = 'STORE_AVAIL'
SF_TABLE_DEST_NAME = 'AVAILABILITY'
SF_TABLE_TEMP_NAME = 'AVAILABILITY_TEMP'
SF_TABLE_DEST_FULLNAME = '"{}"."{}"'.format(SF_SCHEMA, SF_TABLE_DEST_NAME)
SF_TABLE_TEMP_FULLNAME = '"{}"."{}"'.format(SF_SCHEMA, SF_TABLE_TEMP_NAME)


# Extraction constants
# --------------------

SQL_MS_SELECT_DATA = """
SELECT
    product.product_id,
    product.upc,
    product_in_store.store_internal_id,
    product_in_store.status,
    product_in_store.go_live_date,
    product_in_store.delivery_date,
    product_in_store.store_id
FROM product_in_store
    INNER JOIN product ON
        product_in_store.product_id = product.product_id;
"""

# Transformation constants
# ------------------------

STORE_ID_ITUNES = 1
STORE_ID_SPOTIFY = 286
STORE_ID_DEEZER = 348
STORE_IDS = (
    STORE_ID_ITUNES,
    STORE_ID_SPOTIFY,
    STORE_ID_DEEZER,
)
STOREFRONT_URLS_TEMPLATES = {
    STORE_ID_ITUNES: 'https://itunes.apple.com/album/id{0}',
    STORE_ID_SPOTIFY: 'https://open.spotify.com/album/{0}',
    STORE_ID_DEEZER: 'https://www.deezer.com/album/{0}',
}
CSV_FIELD_OPTIONALLY_ENCLOSED_BY = '\''
CSV_EMPTY_STR = '{0}{0}'.format(CSV_FIELD_OPTIONALLY_ENCLOSED_BY)


# Loading constants
# -----------------

S3_PATH_PREFIX = 'ows-store-availability/csv'

SQL_SF_TRUNCATE_TEMP = 'TRUNCATE TABLE {};'.format(SF_TABLE_TEMP_FULLNAME)

# Note: escape format keys we do not know values for yet.
SQL_SF_COPY_TO_TEMP = """
COPY INTO {temp_table} FROM s3://{{s3_bucket}}/{s3_path_prefix}/
    CREDENTIALS = (
        aws_key_id=:aws_key_id,
        aws_secret_key=:aws_secret_key
    )
    FILE_FORMAT = (
        TYPE = CSV
        FIELD_OPTIONALLY_ENCLOSED_BY = "{csv_field_optionally_enclosed_by}"
    )
""".format(
    temp_table=SF_TABLE_TEMP_FULLNAME,
    csv_field_optionally_enclosed_by=CSV_FIELD_OPTIONALLY_ENCLOSED_BY,
    s3_path_prefix=S3_PATH_PREFIX,
)

# TODO: check merge on different data sets
SQL_MERGE_TEMP_TO_DEST = """
MERGE INTO {dest_table} USING {temp_table}
    ON
        {dest_table}.store_id = {temp_table}.store_id
        AND
        {dest_table}.product_id = {temp_table}.product_id
    WHEN MATCHED THEN UPDATE SET
        display_upc = {temp_table}.display_upc,
        storefront_url = {temp_table}.storefront_url,
        status = {temp_table}.status,
        go_live_date = {temp_table}.go_live_date,
        delivery_date = {temp_table}.delivery_date
   WHEN NOT MATCHED THEN INSERT VALUES(
        product_id, store_id, display_upc, storefront_url,
        status, go_live_date, delivery_date);
""".format(
    temp_table=SF_TABLE_TEMP_FULLNAME, dest_table=SF_TABLE_DEST_FULLNAME)

CSV_FILE_NAME = 'extracted.csv'
CSV_COLUMNS = [
    'product_id',
    'store_id',
    'upc',
    'storefront_url',
    'status',
    'go_live_date',
    'delivery_date',
]
# Separate constants for columns we reference explicitly
COLUMN_STORE_INTERNAL_ID = 'store_internal_id'
COLUMN_STORE_ID = 'store_id'
COLUMN_STOREFRONT_URL = 'storefront_url'