"""Create and execute MySQL queries for tests."""
from os import getenv

from tests.testutils.snowflake.snowflake_client import SnowflakeClient


class SnowflakeQueryHelper:
    """Create and execute Snowflake queries for tests."""

    @staticmethod
    def _get_client():
        """Initialize a Snowflake client using environment variables."""

        account = 'sme-delphi'
        host = 'sme-delphi.snowflakecomputing.com'
        role = 'DEV_ENGINEERING'
        warehouse = 'DEV_OWS_WAREHOUSE'
        database = 'FACTS'
        schema = 'QA'
        user = getenv('SNOWFLAKE_USER')
        password = getenv('SNOWFLAKE_PASSWORD')
        return SnowflakeClient(
            account, host, role, warehouse, database, schema, user, password)

    @staticmethod
    def get_subaccout_revenue(account_id=None, period_id=None):
        """Get subaccount revenue from snowflake
        by user_id and given period."""

        snowflake_client = SnowflakeQueryHelper._get_client()

        query = 'SELECT SUM(fs.fx_net_receipt) * ds.commissionoverride ' \
                'as revenue FROM fact_sales ' \
                'AS fs LEFT JOIN dim_subaccount ' \
                'AS ds ON ds.subaccountid = fs.subaccountid ' \
                'WHERE fs.subaccountid = {} ' \
                'AND fs.accountingperiodid = {} ' \
                'GROUP BY fs.accountingperiodid, ds.commissionoverride'\
            .format(account_id, period_id)

        return snowflake_client.execute_query(query)[0]['REVENUE']

    @staticmethod
    def transaction_types(account_id=None,
                          account_type=None,
                          period_id=None):
        """Get transaction_types from snowflake
        by account_id, account_type and given period."""

        snowflake_client = SnowflakeQueryHelper._get_client()

        query = 'SELECT DISTINCT dtt.transactiontypedesc ' \
                'FROM fact_sales fs ' \
                'INNER JOIN dim_transactiontype dtt ' \
                'ON dtt.transactiontypeid = fs.transactiontypeid ' \
                'WHERE {account_type} = {account_id} ' \
                'AND accountingperiodid = {period_id} ' \
                'ORDER BY dtt.transactiontypedesc'.format(
                    account_id=account_id,
                    account_type=account_type,
                    period_id=period_id)

        res = snowflake_client.execute_query(query)
        return list(map(lambda i: i['TRANSACTIONTYPEDESC'], res))