"""Find products to remove from the queue."""

from snowflake_connector.etl_connector import SnowflakeSQLExecutor
from snowflake_connector.etl_connector import SQLLoader

from src import config


def find_deleted_products():
    """Build a list of products to remove from the queue, along with a reason."""
    product_data = query_snowflake()
    deleted_products = []
    for row in product_data:
        deleted_products.append((row['PRODUCT_ID'], get_reason(row)))
    return deleted_products


def get_reason(row):
    """Determine the reason for removing the product."""
    if row['RELEASE_ID'] is None or row['RELEASES_FIVETRAN_DELETED'] is True:
        return 'release_hard_delete'
    if row['RELEASES_DELETIONS'] == 'Y':
        return 'release_soft_delete'
    if row['VENDOR_ID'] is None or row['VENDOR_FIVETRAN_DELETED'] is True:
        return 'vendor_hard_delete'
    if row['VENDOR_STATUS'] in {'deletion', 'inactive'}:
        return 'vendor_soft_delete'
    if row['RELEASES_SUBACCOUNT_ID'] is not None:
        if row['SUBACCOUNT_ID'] is None or row['SUBACCOUNT_FIVETRAN_DELETED'] is True:
            return 'subaccount_hard_delete'
        if row['SUBACCOUNT_DELETED_DATE'] is not None:
            return 'subaccount_soft_delete'
    if row['PROJECT_ID'] is None or row['PROJECT_FIVETRAN_DELETED'] is True:
        return 'project_hard_delete'
    if row['PROJECT_DELETIONS'] == 'Y':
        return 'project_soft_delete'
    return 'unknown_reason'


def query_snowflake():
    """Select a list of products to remove from snowflake."""
    with SnowflakeSQLExecutor(config.SNOWFLAKE_OPTIONS) as sf_executor:
        sql, non_identifier_params = sf_executor.validator.format_identifiers(
            SQLLoader(__file__).load_query('deleted_products'),
            {'env': config.SNOWFLAKE_OPTIONS['snowflake_env']})
        return sf_executor.fetchall(sql, params=non_identifier_params, dict_cursor=True)