"""Config for the proper_incoming Proper Ingestion Workflow."""
import os

from feed_ingestion.flows.helpers import decode_snowflake_key

environment = os.environ.get('Environment', 'dev')
feed_name = 'proper_daily'
expected_bucket_owner = os.environ.get('EXPECTED_BUCKET_OWNER', '437795906767')

snowflake_db_config = {
    'user': os.environ.get('SNOWFLAKE_USER'),
    'key': decode_snowflake_key(os.environ.get('SNOWFLAKE_KEY')),
    'account': os.environ.get('SNOWFLAKE_ACCOUNT'),
    'role': os.environ.get('SNOWFLAKE_ROLE'),
    'warehouse': os.environ.get('SNOWFLAKE_WAREHOUSE'),
    'db': os.environ.get('SNOWFLAKE_DATABASE'),
    'schema': os.environ.get('SNOWFLAKE_SCHEMA')
}

mysql_db_config = {
    'host': os.environ.get('PROPER_MYSQL_HOST'),
    'user': os.environ.get('PROPER_MYSQL_USER'),
    'password': os.environ.get('PROPER_MYSQL_PASSWORD'),
    'db_name': os.environ.get('PROPER_MYSQL_DBNAME'),
}

update_proper_stock_essn_by_upc_sql = (
    'UPDATE art_relations.proper_stock_essn AS stock '
    'INNER JOIN art_relations.releases AS releases '
    'ON stock.ean = releases.upc '
    'INNER JOIN art_relations.product_physical '
    'ON releases.release_id = '
    'art_relations.product_physical.release_id '
    'SET stock.release_id = releases.release_id ')

update_proper_stock_essn_by_display_upc_sql = (
    'UPDATE art_relations.proper_stock_essn AS stock '
    'INNER JOIN art_relations.releases AS releases '
    'ON stock.ean = releases.display_upc '
    'INNER JOIN art_relations.product_physical '
    'ON releases.release_id = '
    'art_relations.product_physical.release_id '
    'SET stock.release_id = releases.release_id '
    'WHERE stock.release_id IS NULL ')

update_proper_stock_essn_by_manufacturer_upc_sql = (
    'UPDATE art_relations.proper_stock_essn AS stock '
    'INNER JOIN art_relations.releases AS releases '
    'ON stock.ean = releases.manufacturer_upc '
    'INNER JOIN art_relations.product_physical '
    'ON releases.release_id = '
    'art_relations.product_physical.release_id '
    'SET stock.release_id = releases.release_id '
    'WHERE stock.release_id IS NULL ')

update_proper_stock_essn_by_catalog_number_sql = (
    'UPDATE art_relations.proper_stock_essn AS stock '
    'INNER JOIN art_relations.releases AS releases '
    'ON stock.catalogue_number = releases.product_code '
    'INNER JOIN art_relations.product_physical '
    'ON releases.release_id = '
    'art_relations.product_physical.release_id '
    'SET stock.release_id = releases.release_id '
    'WHERE stock.release_id IS NULL')

ftp = {
    'host': os.environ.get('PROPER_DROP_FTP_HOST'),
    'username': os.environ.get('PROPER_FTP_USERNAME'),
    'password': os.environ.get('PROPER_FTP_PASSWORD'),
    'port': int(os.environ.get('PROPER_FTP_PORT', 22)),
    'path': os.environ.get('PROPER_FTP_PATH')
}

target_s3_path = 's3://{s3_bucket}/ProperIncoming/archives/{date_YYYY_MM_DD}/'

path_to_unload = '/ProperIncoming/snowflake/{date}'

# save clean stock file
target_s3_stock_path = (
    's3://{s3_bucket}/ProperIncoming/stock/{date_YYYY_MM_DD}/')

s3_bucket = os.environ.get('FEED_INGESTION_DATA_BUCKET', 'dev-cucumbers')

if environment == 'production':
    s3_bucket = 'cucumbers'

FEED_TYPE_GOODSIN = 'goodsin'
FEED_TYPE_SALES = 'sales'
FEED_TYPE_SHORTAGES = 'shortages'
FEED_TYPE_STOCK = 'stock'

proper_feeds = {
    FEED_TYPE_GOODSIN: {
        'ftp_file_name': 'GoodsIn_ESSN_{date_YYYY_MM_DD}.csv.done',
        's3_file_name': 'GoodsIn_ESSN_{date_YYYY_MM_DD}.csv'
    },
    FEED_TYPE_SALES: {
        'ftp_file_name': 'Sales_ESSN_{date_YYYY_MM_DD}.csv.done',
        's3_file_name': 'Sales_ESSN_{date_YYYY_MM_DD}.csv'
    },
    FEED_TYPE_SHORTAGES: {
        'ftp_file_name': 'Shortages_ESSN_{date_YYYY_MM_DD}.csv.done',
        's3_file_name': 'Shortages_ESSN_{date_YYYY_MM_DD}.csv'
    },
    FEED_TYPE_STOCK: {
        'ftp_file_name': 'Stock_ESSN_{date_YYYY_MM_DD}.csv.done',
        's3_file_name': 'Stock_ESSN_{date_YYYY_MM_DD}.csv'
    }
}