# -*- coding: utf-8 -*-
from sqlalchemy import and_, func
from sqlalchemy.exc import IntegrityError

from gold_digger.database.db_model import ExchangeRate


class DaoExchangeRate:
    def __init__(self, db_session, logger):
        self.db_session = db_session
        self._logger = logger

    def insert_exchange_rate_to_db(self, records):
        """
        It would be much faster to call session.bulk_insert_mappings(ExchangeRate, records)
        but we have to handle IntegrityError because API requests can perform rate update;
        so in the case of explicit update some rates could be already in database and they
        can also come at random times while updating explicitly.
        """
        for record in records:
            try:
                self.db_session.add(ExchangeRate(**record))
                self.db_session.commit()
            except IntegrityError:
                self.db_session.rollback()
                self._logger.warning("Session rollback record %s", record)
        self.db_session.commit()

    def get_rates_by_date_currency(self, date_of_exchange, currency):
        return self.db_session.query(ExchangeRate).filter(
            and_(ExchangeRate.date == date_of_exchange, ExchangeRate.currency == currency)
        ).all()

    def get_rate_by_date_currency_provider(self, date_of_exchange, currency, provider_name):
        return self.db_session.query(ExchangeRate).filter(
            and_(ExchangeRate.date == date_of_exchange, ExchangeRate.currency == currency, ExchangeRate.provider.has(name=provider_name))
        ).first()

    def insert_new_rate(self, date_of_exchange, db_provider, currency, rate):
        """
        Insert new exchange rate for the specified date by specified provider.
        Date, currency and provider must be unique, therefore if record is already in database return it (without any update or insert)
        """
        db_record = ExchangeRate(date=date_of_exchange, provider_id=db_provider.id, currency=currency, rate=rate)
        try:
            self.db_session.add(db_record)
            self.db_session.commit()
        except IntegrityError:  # rate for this currency, date and provider is already in database
            self.db_session.rollback()
            db_record = self.get_rate_by_date_currency_provider(date_of_exchange, currency, db_provider.name)
        return db_record

    def get_sum_of_rates_in_period(self, start_date, end_date, currency):
        """
        SELECT provider_id, count(*), SUM(rate) FROM "USD_exchange_rates" WHERE date >= '%Y-%m-%d' AND date <= '%Y-%m-%d' GROUP BY provider_id
        """
        return self.db_session.query(ExchangeRate.provider_id, func.count(), func.sum(ExchangeRate.rate)).filter(
            and_(ExchangeRate.date >= start_date, ExchangeRate.date <= end_date, ExchangeRate.currency == currency, ExchangeRate.rate.isnot(None))
        ).group_by(ExchangeRate.provider_id).all()

@@ -2643,32 +2643,46 @@
  self.db_session
+%5C%0A            
 .query(ExchangeR
@@ -2740,16 +2740,30 @@
 e.rate))
+%5C%0A            
 .filter(
@@ -2755,32 +2755,36 @@
        .filter(%0A
+    
             and_
@@ -2812,24 +2812,45 @@
  start_date,
+%0A                    
  ExchangeRat
@@ -2868,16 +2868,37 @@
 nd_date,
+%0A                    
  Exchang
@@ -2916,32 +2916,53 @@
 ncy == currency,
+%0A                    
  ExchangeRate.ra
@@ -2985,17 +2985,35 @@
         
-)
+    )%5C%0A            
 .group_b
@@ -3039,15 +3039,78 @@
 ider_id)
+%5C%0A            .order_by(ExchangeRate.provider_id)%5C%0A            
 .all()%0A

Range req: order by provider in DAO.