"""Helper functions related to Snowflake DB actions."""

from connectors import snowflake as snowdb
from utils.db_utils import run_query
from sqlalchemy.sql import text

from sql.select_rpoduct_participants_info import \
    SELECT_RPODUCT_PARTICIPANTS_INFO
from sql.select_distinct_upc import \
    SELECT_DISTINCT_UPC


@snowdb.db_session_wrap
def get_products_list(session, table):
    """Get the value of a list of fields from snowflake.

    Args:
        session (SQLAlchemy):       Session from db wrapper
        table (str):                table name to query

    Returns:
        list

    """

    sql = SELECT_DISTINCT_UPC.format(
        source_table=table
    )

    query = text(sql).columns()

    query_results = run_query(session, sql=query)

    results = [row[0] for row in query_results]

    return results


@snowdb.db_session_wrap
def get_products_participants_info(session, products):
    """Get the value of a list of fields from snowflake.

    Args:
        session (SQLAlchemy):       Session from db wrapper
        products (list):            list of upcs

    Returns:
        list of dicts

    """

    sql = SELECT_RPODUCT_PARTICIPANTS_INFO

    params = dict(
        upcs=products
    )
    query = text(sql).columns()
    query_results = run_query(session, sql=query, params=params)

    results = [dict(row) for row in query_results.mappings()]

    return results