from django.db.models import Case, When, IntegerField, Exists, OuterRef, Value, BooleanField
from django.utils import timezone

_has_item_type_column = None


def _check_sponsored_ad_columns():
    """Check once if the item_type column exists (migration applied)."""
    global _has_item_type_column
    if _has_item_type_column is not None:
        return _has_item_type_column
    try:
        from django.db import connection
        with connection.cursor() as cursor:
            columns = [
                col.name for col in
                connection.introspection.get_table_description(cursor, 'sponsored_ads')
            ]
        _has_item_type_column = 'item_type' in columns
    except Exception:
        _has_item_type_column = False
    return _has_item_type_column


def annotate_priority(queryset, item_type):
    """
    Annotate a queryset with priority ordering:
    3 = sponsored, 2 = verified vendor, 1 = pro/upgraded vendor, 0 = default.

    Args:
        queryset: A Django queryset for Product, Service, Event, Menu, or VideoListing
        item_type: 'product', 'service', 'event', 'menu', or 'video'
    """
    # VideoListing.vendor -> User, others -> VendorProfile
    # So vendor verification fields differ
    is_video = item_type == 'video'
    verified_field = 'vendor__vendor_profile__is_verified_vendor' if is_video else 'vendor__is_verified_vendor'
    upgraded_field = 'vendor__vendor_profile__is_upgraded' if is_video else 'vendor__is_upgraded'

    if not _check_sponsored_ad_columns():
        # Migration not applied yet — skip sponsored annotation
        return queryset.annotate(
            is_sponsored=Value(False, output_field=BooleanField()),
            priority=Case(
                When(**{verified_field: True, 'then': Value(2)}),
                When(**{upgraded_field: True, 'then': Value(1)}),
                default=Value(0),
                output_field=IntegerField(),
            ),
        )

    from promotions.models import SponsoredAd

    now = timezone.now()
    fk_field = {item_type: OuterRef('pk')}

    return queryset.annotate(
        is_sponsored=Exists(
            SponsoredAd.objects.filter(
                item_type=item_type,
                is_active=True,
                starts_at__lte=now,
                ends_at__gt=now,
                **fk_field,
            )
        ),
        priority=Case(
            When(is_sponsored=True, then=Value(3)),
            When(**{verified_field: True, 'then': Value(2)}),
            When(**{upgraded_field: True, 'then': Value(1)}),
            default=Value(0),
            output_field=IntegerField(),
        ),
    )