class StoreTickerDailySummary(models.Model, SqlAlchemyMixin):
    """Store Savings, order size, value and quality score Summary table on a daily basis.
    There is one row for each unique combination of date"""

    class Meta:
        db_table = 'summary_store_ticker_daily'
        unique_together = ('date', 'store')

    store = models.IntegerField(db_index=True)
    date = models.DateField(db_index=True)

    avg_clicks_per_day = models.FloatField(default = 0.0)
    max_clicks_per_day = models.FloatField(default = 0.0)
    total_clicks = models.FloatField(default = 0.0)

    avg_sale = models.FloatField(default = 0.0)
    max_sale = models.FloatField(default = 0.0)

    avg_savings_per_transaction = models.FloatField(default = 0.0)
    avg_savings_per_day = models.FloatField(default = 0.0)
    avg_savings_per_offer = models.FloatField(default = 0.0)
    max_savings = models.FloatField(default = 0.0)

    avg_sale_on_day = models.FloatField(default = 0.0)
    avg_savings_per_transaction_on_day = models.FloatField(default = 0.0)

    avg_value_score = models.FloatField(default = 1.0)
    max_value_score = models.FloatField(default = 1.0)
    avg_quality_score = models.FloatField(default = 1.0)
    max_quality_score = models.FloatField(default = 1.0)


    @classmethod
    def summarize(kls, dates):

        store_clicks_query = """
            select sds.id, date,
                    avg(sds.clicks) as avg_clicks_per_day,
                    max(sds.clicks) as max_clicks_per_day,
                    sum(sds.clicks) as total_clicks,

                    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

             from summary_store_daily as sds
             where date between %s and %s
             group by sds.id"""

        offer_savings_date_range_query = """
            select s.id as store_id, date, 0, 0, 0,

                    sum(osds.total_sale)/sum(osds.transactions) as avg_sale,
                    max(osds.max_sale) as max_sale,
                    sum(osds.total_savings)/sum(osds.transactions) as avg_savings_per_transaction,
                    sum(osds.total_savings)/count(osds.date) as avg_savings_per_day,
                    sum(osds.total_savings)/count(osds.offer) as avg_savings_per_offer,
                    max(osds.max_savings) as max_savings,

                    0, 0, 0, 0, 0, 0

                from summary_offer_savings_daily as osds
                inner join offer as o on osds.offer = o.id
                inner join store as s on o.store_id = s.id
                where date between %s and %s
                group by s.id"""

        offer_savings_on_day_query = """
            select s.id as store_id, date, 0, 0, 0, 0, 0, 0, 0, 0, 0,

                    sum(osds.total_sale)/sum(osds.transactions) as avg_sale_on_day,
                    sum(osds.total_savings)/sum(osds.transactions) as avg_savings_per_transaction_on_day,

                    0, 0, 0, 0

                from summary_offer_savings_daily as osds
                inner join offer as o on osds.offer = o.id
                inner join store as s on o.store_id = s.id
                where date = %s
                group by s.id"""

        offer_score_query = """
            select s.id as store_id, date, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

                    avg(os.value_score) as avg_value_score,
                    max(os.value_score) as max_value_score,
                    avg(os.quality_score) as avg_quality_score,
                    max(os.quality_score) as max_quality_score

                from scoring_offerscore as os
                inner join offer as o on os.offer_id = o.id
                inner join store as s on o.store_id = s.id
                where date between %s and %s
                group by s.id"""


        store_clicks_insert_query = """
            INSERT INTO summary_store_ticker_daily (store, date, avg_clicks_per_day, max_clicks_per_day, total_clicks,
                                                                avg_sale, max_sale,
                                                                avg_savings_per_transaction, avg_savings_per_day, avg_savings_per_offer,
                                                                max_savings,

                                                                avg_sale_on_day, avg_savings_per_transaction_on_day,

                                                                avg_value_score, max_value_score, avg_quality_score, max_quality_score)
            %s
            ON DUPLICATE KEY UPDATE avg_clicks_per_day = VALUES(avg_clicks_per_day), max_clicks_per_day = VALUES(max_clicks_per_day),
                                                                total_clicks = VALUES(total_clicks)"""\
            % store_clicks_query

        offer_savings_date_range_insert_query = """
            INSERT INTO summary_store_ticker_daily (store, date, avg_clicks_per_day, max_clicks_per_day, total_clicks,
                                                                avg_sale, max_sale,
                                                                avg_savings_per_transaction, avg_savings_per_day, avg_savings_per_offer,
                                                                max_savings,

                                                                avg_sale_on_day, avg_savings_per_transaction_on_day,

                                                                avg_value_score, max_value_score, avg_quality_score, max_quality_score)
            %s
            ON DUPLICATE KEY UPDATE avg_sale = VALUES(avg_sale), max_sale = VALUES(max_sale),
                                                            avg_savings_per_transaction = VALUES(avg_savings_per_transaction),
                                                            avg_savings_per_day = VALUES(avg_savings_per_day),
                                                            avg_savings_per_offer = VALUES(avg_savings_per_offer),
                                                            max_savings = VALUES(max_savings)"""\
            % offer_savings_date_range_query

        offer_savings_on_day_insert_query = """
            INSERT INTO summary_store_ticker_daily (store, date, avg_clicks_per_day, max_clicks_per_day, total_clicks,
                                                                avg_sale, max_sale,
                                                                avg_savings_per_transaction, avg_savings_per_day, avg_savings_per_offer,
                                                                max_savings,

                                                                avg_sale_on_day, avg_savings_per_transaction_on_day,

                                                                avg_value_score, max_value_score, avg_quality_score, max_quality_score)
            %s
            ON DUPLICATE KEY UPDATE avg_sale_on_day = VALUES(avg_sale_on_day), avg_savings_per_transaction_on_day = VALUES(avg_savings_per_transaction_on_day)"""\
            % offer_savings_on_day_query

        offer_score_insert_query = """
            INSERT INTO summary_store_ticker_daily (store, date, avg_clicks_per_day, max_clicks_per_day, total_clicks,
                                                                avg_sale, max_sale,
                                                                avg_savings_per_transaction, avg_savings_per_day, avg_savings_per_offer,
                                                                max_savings,

                                                                avg_sale_on_day, avg_savings_per_transaction_on_day,

                                                                avg_value_score, max_value_score, avg_quality_score, max_quality_score)
            %s
            ON DUPLICATE KEY UPDATE avg_value_score = VALUES(avg_value_score), max_value_score = VALUES(max_value_score),
                                                            avg_quality_score = VALUES(avg_quality_score), max_quality_score = VALUES(max_quality_score)"""\
            % offer_score_query




        cursor = connection.cursor()
        for date in dates:
            cursor.execute(store_clicks_insert_query, [date, date-datetime.timedelta(days=30)])
            transaction.commit_unless_managed()

            cursor.execute(offer_savings_date_range_insert_query, [date, date-datetime.timedelta(days=30)])
            transaction.commit_unless_managed()

            cursor.execute(offer_savings_on_day_insert_query, [date])
            transaction.commit_unless_managed()

            cursor.execute(offer_score_insert_query, [date, date-datetime.timedelta(days=30)])
            transaction.commit_unless_managed()