"""Units resource.

This is a connector to SnowFlake DB lets us get units for a feed.
It depends on feed name and licensor. /queries folder contains parametrized
queries for every feed. Here all the identifiers and parameters to bind
become present at the query, and get_units() returns list of dicts
{filedate: units} for graphs.
"""
import os

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

from feed_status import config


sql_loader = SQLLoader(__file__)

COLUMN_NAMES = [
    'filedate',
    'units'
]

LICENSOR_REGARDLESS = [
    'itunes_tickets'
]


def get_rows(feed_id, licensor=None):
    """Return rows form DB.

    Args:
        feed_id (str): feed name string.
        licensor (str) or (None): licensor name string.
    Return:
        list(tuple): List of rows with query result.
    """
    query_name = feed_id + '_units'
    params = dict(licensor=licensor)

    with SnowflakeSQLExecutor(
            config.get_sf_conn_config()
            ) as executor:
        not_parameterized_sql = sql_loader.load_query(item=query_name)
        rows = executor.fetchall(
            sql_template=not_parameterized_sql, params=params)
    return rows


def get_units(feed_id, licensor):
    """Return units for a corresponding date for a feed.

    Args:
        feed_id (str): feed name string.
        licensor (str) or (None): licensor name string.
    Return:
        List[Dict]:  A list of date-units pairs -> [{feed_date: units}, ...]
    """
    rows = get_rows(feed_id, licensor)

    return [{c: v for c, v in zip(COLUMN_NAMES, row)} for row in rows]


def get_units_for_all_feeds():
    """Return units for all the feeds.

    Return:
        List[Dict]:  A list of feed_id: date-units pairs ->
        [{feed_id: {feed_date: units}, ...}, ...]
    """
    units = []
    for file in os.listdir('feed_status/connectors/queries'):
        feed_id = file.replace('_units.sql', '')
        licensors = ['sme', 'theorchard'] \
            if feed_id not in LICENSOR_REGARDLESS else [None]
        for licensor in licensors:
            rows = get_rows(feed_id, licensor)

            units.append({
                'feed_id': feed_id,
                'licensor': licensor,
                'units': [{c: v for c, v in
                           zip(COLUMN_NAMES, row)} for row in rows]})
    return units