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

from tests.testutils.mysql.mysql_client import MySQLClient


class MySQLQueryHelper:
    """Create and execute MySQL queries for tests."""

    @staticmethod
    def _get_client():
        """Initialize a MySQL client using environment variables."""
        host = getenv('DB_CLIENT_HOST')
        db_user = getenv('DB_CLIENT_USER')
        db_password = getenv('DB_CLIENT_PASS')
        database = getenv('DB_CLIENT_DB')
        return MySQLClient(host, database, db_user, db_password)

    @staticmethod
    def get_phys_reserves(user_id=None, period_id=None):
        """Get physical reserves data from DB by user_id and given period."""

        mysql_client = MySQLQueryHelper._get_client()

        query = 'SELECT * FROM manual_adjustment ' \
                'WHERE parent_id = {} ' \
                'AND adjust_for_period_id = {} ' \
                'AND category_id in (68,69) ' \
                'ORDER BY id DESC ' \
                'LIMIT 1'.format(user_id, period_id)

        return mysql_client.execute_query(query)[0]

    @staticmethod
    def net_revenue_sum(vendor_id=None,
                        period_start=None,
                        period_finish=None):
        """Get net revenue sum by vendor_id and given periods."""

        mysql_client = MySQLQueryHelper._get_client()

        query = 'SELECT sum(amount) from vendor_accounting ' \
                'WHERE vendor_id = {} ' \
                'AND period_id between {} ' \
                'AND {} ' \
                'AND entry_type = \'dig_net_receipt\''.format(
                    vendor_id, period_start, period_finish)

        return mysql_client.execute_query(query)[0]['sum(amount)']

    @staticmethod
    def gross_revenue_sum(vendor_id=None,
                          period_start=None,
                          period_finish=None):
        """Get net revenue sum by vendor_id and given periods."""

        mysql_client = MySQLQueryHelper._get_client()

        query = 'SELECT sum(amount) from vendor_accounting ' \
                'WHERE vendor_id = {} ' \
                'AND period_id between {} ' \
                'AND {} ' \
                'AND entry_type = \'dig_gross\''.format(
                    vendor_id, period_start, period_finish)

        return mysql_client.execute_query(query)[0]['sum(amount)']

    @staticmethod
    def payment_sum(vendor_id=None,
                    period_start=None,
                    period_finish=None):
        """Get payment sum by vendor_id and given periods."""

        mysql_client = MySQLQueryHelper._get_client()

        query = 'SELECT vendor_id, paidfor_period_id, check_no, SUM(amt) ' \
                'FROM (' \
                'SELECT vendor_id, paidfor_period_id, ' \
                'check_no, sum(check_amt) amt ' \
                'FROM checkspaid cp ' \
                'INNER JOIN releases r on r.upc = cp.upc ' \
                'INNER JOIN artist_info a on r.artist_id = a.artist_id ' \
                'WHERE a.vendor_id =  {vendor_id} ' \
                'AND paidfor_period_id ' \
                'BETWEEN  {period_start} ' \
                'AND {period_finish} ' \
                'GROUP BY paidfor_period_id, check_no ' \
                'UNION SELECT vendor_id, paidfor_period_id, check_no, ' \
                'sum(check_amt) amt ' \
                'FROM vendor_checkspaid ' \
                'WHERE vendor_id =  {vendor_id} ' \
                'AND paidfor_type <> \'spotify_equity\' ' \
                'AND paidfor_period_id ' \
                'BETWEEN  {period_start} ' \
                'AND {period_finish} ' \
                'GROUP BY paidfor_period_id, check_no ' \
                'ORDER BY paidfor_period_id DESC) as t;'.format(
                    vendor_id=vendor_id,
                    period_start=period_start,
                    period_finish=period_finish)

        return mysql_client.execute_query(query)[0]['SUM(amt)']