from collections import OrderedDict

from django.db.models import Count, Q
from django.db.models.functions import Extract
from scrobbles.models import Scrobble


TARGET_MEDIA_TYPES = ["Book", "Trail", "BirdingLocation", "BoardGame"]

CATEGORIES = OrderedDict(
    [
        ("early_bird", {"label": "Early Bird", "hours": {5, 6, 7, 8, 9, 10}}),
        ("day_jay", {"label": "Day Jay", "hours": {11, 12, 13, 14, 15, 16, 17, 18}}),
        ("night_owl", {"label": "Night Owl", "hours": {19, 20, 21, 22, 23, 0, 1, 2, 3, 4}}),
    ]
)


def _categorize_hour(hour):
    for slug, cat in CATEGORIES.items():
        if hour in cat["hours"]:
            return slug
    return None


def compute_time_of_day_categories(user, period="last_30"):
    from trends.utils import get_date_range

    start, end = get_date_range(period)
    filters = Q(user=user, media_type__in=TARGET_MEDIA_TYPES, timestamp__isnull=False)
    if start:
        filters &= Q(timestamp__gte=start)
    if end:
        filters &= Q(timestamp__lte=end)

    qs = (
        Scrobble.objects.filter(filters)
        .annotate(hour=Extract("timestamp", "hour"))
        .values("media_type", "hour")
        .annotate(count=Count("id"))
        .order_by("media_type", "hour")
    )

    raw = {}
    for row in qs:
        mt = row["media_type"]
        raw.setdefault(mt, {})[row["hour"]] = row["count"]

    by_media_type = {}
    grand_totals = {"early_bird": 0, "day_jay": 0, "night_owl": 0}
    grand_total = 0

    for mt in TARGET_MEDIA_TYPES:
        mt_data = raw.get(mt, {})
        cat_counts = {"early_bird": 0, "day_jay": 0, "night_owl": 0}
        mt_total = 0
        for hour, count in mt_data.items():
            slug = _categorize_hour(hour)
            if slug:
                cat_counts[slug] += count
                mt_total += count
        mt_categories = {}
        for slug in CATEGORIES:
            c = cat_counts[slug]
            mt_categories[slug] = {
                "count": c,
                "pct": round((c / mt_total * 100), 1) if mt_total else 0,
                "label": CATEGORIES[slug]["label"],
            }
            grand_totals[slug] += c
        by_media_type[mt] = {
            "total": mt_total,
            "categories": dict(
                sorted(mt_categories.items(), key=lambda x: x[1]["count"], reverse=True)
            ),
        }
        grand_total += mt_total

    categories = {}
    for slug in CATEGORIES:
        c = grand_totals[slug]
        categories[slug] = {
            "count": c,
            "pct": round((c / grand_total * 100), 1) if grand_total else 0,
            "label": CATEGORIES[slug]["label"],
        }
    categories = dict(
        sorted(categories.items(), key=lambda x: x[1]["count"], reverse=True)
    )

    return {
        "categories": categories,
        "total": grand_total,
        "by_media_type": by_media_type,
    }
