from django.db import models
from django.db.models import (
    Avg,
    Case,
    Count,
    ExpressionWrapper,
    F,
    FloatField,
    IntegerField,
    OuterRef,
    Q,
    Subquery,
    Sum,
    When,
)
from django.db.models.functions import Coalesce

from verify_trusted.reviews.models import Platform

# https://stackoverflow.com/questions/56567841/django-count-and-sum-annotations-interfere-with-each-other


class CompanyQuerySet(models.QuerySet):
    def with_reviews_count(self):
        # Reviews count
        cal_reviews_count_user_set = self.model.objects.annotate(
            cal_reviews_count_user_set=Coalesce(
                Sum(
                    'review_sources__reviews_count',
                    filter=Q(
                        review_sources__platform__status=Platform.Status.ACTIVE,
                        review_sources__reviews_count__isnull=False,
                    ),
                ),
                0,  # Set default to Zero instead of None
            )
        ).filter(pk=OuterRef('pk'))

        cal_reviews_count_isnull = Count(
            'review_sources__reviews__rating',
            filter=Q(
                review_sources__platform__status=Platform.Status.ACTIVE,
                review_sources__reviews_count__isnull=True,
            ),
        )

        cal_reviews_count = F('cal_reviews_count_user_set') + F(
            'cal_reviews_count_isnull'
        )

        return self.annotate(
            cal_reviews_count_user_set=Subquery(
                cal_reviews_count_user_set.values('cal_reviews_count_user_set'),
                output_field=IntegerField(),
            ),
            cal_reviews_count_isnull=cal_reviews_count_isnull,
            cal_reviews_count=cal_reviews_count,
        )

    def with_avg_rating(self):
        # Avg rating
        cal_average_rating_user_set = Avg(
            'review_sources__average_rating',
            filter=Q(
                review_sources__platform__status=Platform.Status.ACTIVE,
                review_sources__average_rating__isnull=False,
            ),
            distinct=True,
        )
        count_average_rating_user_set = Count(
            'review_sources',
            filter=Q(
                review_sources__platform__status=Platform.Status.ACTIVE,
                review_sources__average_rating__isnull=False,
                review_sources__reviews_count__gt=0
            ),
            distinct=True,
        )
        cal_average_rating_isnull = Avg(
            'review_sources__reviews__rating',
            filter=Q(
                review_sources__platform__status=Platform.Status.ACTIVE,
                review_sources__average_rating__isnull=True,
            ),
            distinct=True,
        )
        count_average_rating_isnull = Count(
            'review_sources',
            filter=Q(
                review_sources__platform__status=Platform.Status.ACTIVE,
                review_sources__average_rating__isnull=True,
            ),
            distinct=True,
        )
        cal_average_rating = Case(
            When(
                cal_average_rating_isnull__isnull=True,
                then=F('cal_average_rating_user_set'),
            ),
            When(
                cal_average_rating_user_set__isnull=False,
                then=ExpressionWrapper(
                    (
                        (
                            F('cal_average_rating_user_set')
                            * F('count_average_rating_user_set')
                        )
                        + (
                            F('cal_average_rating_isnull')
                            * F('count_average_rating_isnull')
                        )
                    )
                    / (
                        F('count_average_rating_user_set')
                        + F('count_average_rating_isnull')
                    ),
                    output_field=FloatField(),
                ),
            ),
            default=F('cal_average_rating_isnull'),
            output_field=FloatField(),
        )

        return self.annotate(
            cal_average_rating_user_set=cal_average_rating_user_set,
            count_average_rating_user_set=count_average_rating_user_set,
            cal_average_rating_isnull=cal_average_rating_isnull,
            count_average_rating_isnull=count_average_rating_isnull,
            cal_average_rating=cal_average_rating,
        )


class CompanyManager(models.Manager):
    def manager_only_method(self):
        return