from __future__ import unicode_literals

import datetime
from decimal import Decimal
import re

from django.db import connection
from django.db.models import Avg, Sum, Count, Max, Min
from django.test import TestCase
from django.test.utils import Approximate
from django.test.utils import CaptureQueriesContext

from .models import Author, Publisher, Book, Store


class BaseAggregateTestCase(TestCase):
    fixtures = ["aggregation.json"]

    def test_empty_aggregate(self):
        self.assertEqual(Author.objects.all().aggregate(), {})

    def test_single_aggregate(self):
        vals = Author.objects.aggregate(Avg("age"))
        self.assertEqual(vals, {"age__avg": Approximate(37.4, places=1)})

    def test_multiple_aggregates(self):
        vals = Author.objects.aggregate(Sum("age"), Avg("age"))
        self.assertEqual(vals, {"age__sum": 337, "age__avg": Approximate(37.4, places=1)})

    def test_filter_aggregate(self):
        vals = Author.objects.filter(age__gt=29).aggregate(Sum("age"))
        self.assertEqual(len(vals), 1)
        self.assertEqual(vals["age__sum"], 254)

    def test_related_aggregate(self):
        vals = Author.objects.aggregate(Avg("friends__age"))
        self.assertEqual(len(vals), 1)
        self.assertAlmostEqual(vals["friends__age__avg"], 34.07, places=2)

        vals = Book.objects.filter(rating__lt=4.5).aggregate(Avg("authors__age"))
        self.assertEqual(len(vals), 1)
        self.assertAlmostEqual(vals["authors__age__avg"], 38.2857, places=2)

        vals = Author.objects.all().filter(name__contains="a").aggregate(Avg("book__rating"))
        self.assertEqual(len(vals), 1)
        self.assertEqual(vals["book__rating__avg"], 4.0)

        vals = Book.objects.aggregate(Sum("publisher__num_awards"))
        self.assertEqual(len(vals), 1)
        self.assertEqual(vals["publisher__num_awards__sum"], 30)

        vals = Publisher.objects.aggregate(Sum("book__price"))
        self.assertEqual(len(vals), 1)
        self.assertEqual(vals["book__price__sum"], Decimal("270.27"))

    def test_aggregate_multi_join(self):
        vals = Store.objects.aggregate(Max("books__authors__age"))
        self.assertEqual(len(vals), 1)
        self.assertEqual(vals["books__authors__age__max"], 57)

        vals = Author.objects.aggregate(Min("book__publisher__num_awards"))
        self.assertEqual(len(vals), 1)
        self.assertEqual(vals["book__publisher__num_awards__min"], 1)

    def test_aggregate_alias(self):
        vals = Store.objects.filter(name="Amazon.com").aggregate(amazon_mean=Avg("books__rating"))
        self.assertEqual(len(vals), 1)
        self.assertAlmostEqual(vals["amazon_mean"], 4.08, places=2)

    def test_annotate_basic(self):
        self.assertQuerysetEqual(
            Book.objects.annotate().order_by('pk'), [
                "The Definitive Guide to Django: Web Development Done Right",
                "Sams Teach Yourself Django in 24 Hours",
                "Practical Django Projects",
                "Python Web Development with Django",
                "Artificial Intelligence: A Modern Approach",
                "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp"
            ],
            lambda b: b.name
        )

        books = Book.objects.annotate(mean_age=Avg("authors__age"))
        b = books.get(pk=1)
        self.assertEqual(
            b.name,
            'The Definitive Guide to Django: Web Development Done Right'