import datetime
from decimal import Decimal

from django.core.exceptions import FieldDoesNotExist, FieldError
from django.db.models import (
    BooleanField, Case, CharField, Count, DateTimeField, DecimalField, Exists,
    ExpressionWrapper, F, FloatField, Func, IntegerField, Max, OuterRef, Q,
    Subquery, Sum, Value, When,
)
from django.db.models.expressions import RawSQL
from django.db.models.functions import (
    Coalesce, ExtractYear, Floor, Length, Lower, Trim,
)
from django.test import TestCase, skipUnlessDBFeature
from django.test.utils import register_lookup

from .models import (
    Author, Book, Company, DepartmentStore, Employee, Publisher, Store, Ticket,
)


class NonAggregateAnnotationTestCase(TestCase):

    @classmethod
    def setUpTestData(cls):
        cls.a1 = Author.objects.create(name='Adrian Holovaty', age=34)
        cls.a2 = Author.objects.create(name='Jacob Kaplan-Moss', age=35)
        cls.a3 = Author.objects.create(name='Brad Dayley', age=45)
        cls.a4 = Author.objects.create(name='James Bennett', age=29)
        cls.a5 = Author.objects.create(name='Jeffrey Forcier', age=37)
        cls.a6 = Author.objects.create(name='Paul Bissex', age=29)
        cls.a7 = Author.objects.create(name='Wesley J. Chun', age=25)
        cls.a8 = Author.objects.create(name='Peter Norvig', age=57)
        cls.a9 = Author.objects.create(name='Stuart Russell', age=46)
        cls.a1.friends.add(cls.a2, cls.a4)
        cls.a2.friends.add(cls.a1, cls.a7)
        cls.a4.friends.add(cls.a1)
        cls.a5.friends.add(cls.a6, cls.a7)
        cls.a6.friends.add(cls.a5, cls.a7)
        cls.a7.friends.add(cls.a2, cls.a5, cls.a6)
        cls.a8.friends.add(cls.a9)
        cls.a9.friends.add(cls.a8)

        cls.p1 = Publisher.objects.create(name='Apress', num_awards=3)
        cls.p2 = Publisher.objects.create(name='Sams', num_awards=1)
        cls.p3 = Publisher.objects.create(name='Prentice Hall', num_awards=7)
        cls.p4 = Publisher.objects.create(name='Morgan Kaufmann', num_awards=9)
        cls.p5 = Publisher.objects.create(name="Jonno's House of Books", num_awards=0)

        cls.b1 = Book.objects.create(
            isbn='159059725', name='The Definitive Guide to Django: Web Development Done Right',
            pages=447, rating=4.5, price=Decimal('30.00'), contact=cls.a1, publisher=cls.p1,
            pubdate=datetime.date(2007, 12, 6)
        )
        cls.b2 = Book.objects.create(
            isbn='067232959', name='Sams Teach Yourself Django in 24 Hours',
            pages=528, rating=3.0, price=Decimal('23.09'), contact=cls.a3, publisher=cls.p2,
            pubdate=datetime.date(2008, 3, 3)
        )
        cls.b3 = Book.objects.create(
            isbn='159059996', name='Practical Django Projects',
            pages=300, rating=4.0, price=Decimal('29.69'), contact=cls.a4, publisher=cls.p1,
            pubdate=datetime.date(2008, 6, 23)
        )
        cls.b4 = Book.objects.create(
            isbn='013235613', name='Python Web Development with Django',
            pages=350, rating=4.0, price=Decimal('29.69'), contact=cls.a5, publisher=cls.p3,
            pubdate=datetime.date(2008, 11, 3)
        )
        cls.b5 = Book.objects.create(
            isbn='013790395', name='Artificial Intelligence: A Modern Approach',
            pages=1132, rating=4.0, price=Decimal('82.80'), contact=cls.a8, publisher=cls.p3,
            pubdate=datetime.date(1995, 1, 15)
        )
        cls.b6 = Book.objects.create(
            isbn='155860191', name='Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',
            pages=946, rating=5.0, price=Decimal('75.00'), contact=cls.a8, publisher=cls.p4,
            pubdate=datetime.date(1991, 10, 15)
        )
        cls.b1.authors.add(cls.a1, cls.a2)
        cls.b2.authors.add(cls.a3)
        cls.b3.authors.add(cls.a4)
        cls.b4.authors.add(cls.a5, cls.a6, cls.a7)
        cls.b5.authors.add(cls.a8, cls.a9)
        cls.b6.authors.add(cls.a8)

        cls.s1 = Store.objects.create(
            name='Amazon.com',
            original_opening=datetime.datetime(1994, 4, 23, 9, 17, 42),
            friday_night_closing=datetime.time(23, 59, 59)
        )
        cls.s2 = Store.objects.create(
            name='Books.com',
            original_opening=datetime.datetime(2001, 3, 15, 11, 23, 37),
            friday_night_closing=datetime.time(23, 59, 59)
        )
        cls.s3 = Store.objects.create(
            name="Mamma and Pappa's Books",
            original_opening=datetime.datetime(1945, 4, 25, 16, 24, 14),
            friday_night_closing=datetime.time(21, 30)
        )
        cls.s1.books.add(cls.b1, cls.b2, cls.b3, cls.b4, cls.b5, cls.b6)
        cls.s2.books.add(cls.b1, cls.b3, cls.b5, cls.b6)
        cls.s3.books.add(cls.b3, cls.b4, cls.b6)

    def test_basic_annotation(self):
        books = Book.objects.annotate(is_book=Value(1))
        for book in books:
            self.assertEqual(book.is_book, 1)

    def test_basic_f_annotation(self):
        books = Book.objects.annotate(another_rating=F('rating'))
        for book in books:
            self.assertEqual(book.another_rating, book.rating)

    def test_joined_annotation(self):
        books = Book.objects.select_related('publisher').annotate(
            num_awards=F('publisher__num_awards'))
        for book in books:
            self.assertEqual(book.num_awards, book.publisher.num_awards)

    def test_joined_transformed_annotation(self):
        Employee.objects.bulk_create([
            Employee(
                first_name='John',
                last_name='Doe',
                age=18,
                store=self.s1,
                salary=15000,
            ),
            Employee(
                first_name='Jane',
                last_name='Jones',
                age=30,
                store=self.s2,
                salary=30000,
            ),
            Employee(
                first_name='Jo',
                last_name='Smith',
                age=55,
       
         store=self.s3,
                salary=50000,
            ),
        ])
        employees = Employee.objects.annotate(
            store_opened_year=F('store__original_opening__year'),
        )
        for employee in employees:
            self.assertEqual(
                employee.store_opened_year,
                
employee.store.original_opening.year,
            )

    def test_custom_transform_annotation(self):
        with register_lookup(DecimalField, Floor):
            books = Book.objects.annotate(floor_price=F('price__floor'))

        self.assertSequenceEqual(books.values_list('pk', 'floor_price'), [
            (self.b1.pk, 30),
            (self.b2.pk, 23),
            (self.b3.pk, 29),
            (self.b4.pk, 29),
            (self.b5.pk, 82),
            (self.b6.pk, 75),
        ])

    def test_chaining_transforms(self):
        Company.objects.create(name=' Django Software Foundation  ')
        Company.objects.create(name='Yahoo')
        with register_lookup(CharField, Trim), register_lookup(CharField, Length):
            for expr in [Length('name__trim'), F('name__trim__length')]:
                with self.subTest(expr=expr):
                    self.assertCountEqual(
                        Company.objects.annotate(length=expr).values('name', 'length'),
                        [
                            {'name': ' Django Software Foundation  ', 'length': 26},
                            {'name': 'Yahoo', 'length': 5},
                        ],
                    )

    def test_mixed_type_annotation_date_interval(self):
        active = datetime.datetime(2015, 3, 20, 14, 0, 0)
        duration = datetime.timedelta(hours=1)
        expires = datetime.datetime(2015, 3, 20, 14, 0, 0) + duration
        Ticket.objects.create(active_at=active, duration=duration)
        t = Ticket.objects.annotate(
            expires=ExpressionWrapper(F('active_at') + F('duration'), output_field=DateTimeField())
        ).first()
        self.assertEqual(t.expires, expires)

    def test_mixed_type_annotation_numbers(self):
        test = self.b1
        b = Book.objects.annotate(
           