HH/diagnose_charts.py

#!/usr/bin/env python
"""
Diagnostic script to check chart data generation
"""
import os
import django

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings.dev')
django.setup()

from django.utils import timezone
from datetime import timedelta
from django.db.models import Count, Avg, Q, F, Case, When, IntegerField
from django.db.models.functions import TruncDate
from apps.surveys.models import SurveyInstance

print("="*70)
print("CHART DATA DIAGNOSTIC")
print("="*70)

# Get base queryset
stats_queryset = SurveyInstance.objects.select_related('survey_template')

print(f"\nTotal surveys in database: {stats_queryset.count()}")
print(f"Total completed: {stats_queryset.filter(status='completed').count()}")

# 1. Score Distribution
print("\n" + "="*70)
print("SCORE DISTRIBUTION")
print("="*70)

completed_surveys = stats_queryset.filter(status='completed', total_score__isnull=False)
print(f"Completed surveys with scores: {completed_surveys.count()}")

score_ranges = [
    ('1-2', 1, 2),
    ('2-3', 2, 3),
    ('3-4', 3, 4),
    ('4-5', 4, 5),
]

for label, min_score, max_score in score_ranges:
    if max_score == 5:
        count = completed_surveys.filter(
            total_score__gte=min_score,
            total_score__lte=max_score
        ).count()
    else:
        count = completed_surveys.filter(
            total_score__gte=min_score,
            total_score__lt=max_score
        ).count()
    print(f"{label}: {count} surveys")

# 2. Engagement Funnel
print("\n" + "="*70)
print("ENGAGEMENT FUNNEL")
print("="*70)

sent_count = stats_queryset.filter(status__in=['sent', 'pending']).count()
viewed_count = stats_queryset.filter(status='viewed').count()
opened_count = stats_queryset.filter(open_count__gt=0).count()
in_progress_count = stats_queryset.filter(status='in_progress').count()
completed_count = stats_queryset.filter(status='completed').count()

print(f"Sent/Pending: {sent_count}")
print(f"Viewed: {viewed_count}")
print(f"Opened: {opened_count}")
print(f"In Progress: {in_progress_count}")
print(f"Completed: {completed_count}")

# 3. Completion Time Distribution
print("\n" + "="*70)
print("COMPLETION TIME DISTRIBUTION")
print("="*70)

completed_with_time = stats_queryset.filter(
    status='completed',
    time_spent_seconds__isnull=False
)
print(f"Completed surveys with time data: {completed_with_time.count()}")

completion_time_ranges = [
    ('< 1 min', 0, 60),
    ('1-5 min', 60, 300),
    ('5-10 min', 300, 600),
    ('10-20 min', 600, 1200),
    ('20+ min', 1200, float('inf')),
]

for label, min_seconds, max_seconds in completion_time_ranges:
    if max_seconds == float('inf'):
        count = completed_with_time.filter(time_spent_seconds__gte=min_seconds).count()
    else:
        count = completed_with_time.filter(
            time_spent_seconds__gte=min_seconds,
            time_spent_seconds__lt=max_seconds
        ).count()
    print(f"{label}: {count} surveys")

# 4. Device Type Distribution
print("\n" + "="*70)
print("DEVICE TYPE DISTRIBUTION")
print("="*70)

from apps.surveys.models import SurveyTracking

tracking_events = SurveyTracking.objects.filter(
    survey_instance__in=stats_queryset
).values('device_type').annotate(
    count=Count('id')
).order_by('-count')

total_tracking = tracking_events.count()
print(f"Total tracking events: {total_tracking}")

for entry in tracking_events:
    print(f"{entry['device_type']}: {entry['count']} events")

# 5. Survey Trend
print("\n" + "="*70)
print("30-DAY TREND")
print("="*70)

thirty_days_ago = timezone.now() - timedelta(days=30)

# Try sent_at first
trend_queryset = stats_queryset.filter(
    sent_at__gte=thirty_days_ago
)

if not trend_queryset.exists():
    print("No surveys with sent_at in last 30 days, trying created_at...")
    trend_queryset = stats_queryset.filter(
        created_at__gte=thirty_days_ago
    ).annotate(date=TruncDate('created_at'))
else:
    trend_queryset = trend_queryset.annotate(date=TruncDate('sent_at'))

trend_data = trend_queryset.values('date').annotate(
    sent=Count('id'),
    completed=Count('id', filter=Q(status='completed'))
).order_by('date')

print(f"Days with survey activity: {trend_data.count()}")
for entry in trend_data[:5]:
    if entry['date']:
        print(f"{entry['date'].strftime('%Y-%m-%d')}: sent={entry['sent']}, completed={entry['completed']}")

# 6. Survey Types
print("\n" + "="*70)
print("SURVEY TYPE DISTRIBUTION")
print("="*70)

survey_type_data = stats_queryset.values(
    'survey_template__survey_type'
).annotate(
    count=Count('id')
).order_by('-count')

for entry in survey_type_data:
    print(f"{entry['survey_template__survey_type']}: {entry['count']} surveys")

print("\n" + "="*70)
print("DIAGNOSTIC COMPLETE")
print("="*70)