#!/usr/bin/env python3
"""
Check yaw angles in database to see why profile faces aren't being detected
"""

import sqlite3
import os

db_path = "data/punimtag.db"

if not os.path.exists(db_path):
    print(f"❌ Database not found: {db_path}")
    exit(1)

conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

# Get all faces with yaw data
cursor.execute("""
    SELECT id, pose_mode, yaw_angle, pitch_angle, roll_angle
    FROM faces
    WHERE yaw_angle IS NOT NULL
    ORDER BY ABS(yaw_angle) DESC
""")

faces = cursor.fetchall()

print(f"Found {len(faces)} faces with yaw data\n")
print("=" * 80)
print("YAW ANGLE ANALYSIS")
print("=" * 80)
print(f"\n{'Face ID':<10} {'Pose Mode':<25} {'Yaw':<10} {'Should be Profile?'}")
print("-" * 80)

PROFILE_THRESHOLD = 30.0  # From pose_detection.py

profile_count = 0
for face in faces:
    yaw = face['yaw_angle']
    pose_mode = face['pose_mode']
    is_profile = abs(yaw) >= PROFILE_THRESHOLD
    should_be_profile = "YES" if is_profile else "NO"
    
    if is_profile:
        profile_count += 1
    
    print(f"{face['id']:<10} {pose_mode:<25} {yaw:>8.2f}° {should_be_profile}")

print("\n" + "=" * 80)
print(f"Total faces with yaw data: {len(faces)}")
print(f"Faces with |yaw| >= {PROFILE_THRESHOLD}° (should be profile): {profile_count}")
print(f"Faces currently classified as profile: {cursor.execute('SELECT COUNT(*) FROM faces WHERE pose_mode LIKE \"profile%\"').fetchone()[0]}")
print("=" * 80)

# Check yaw distribution
print("\n" + "=" * 80)
print("YAW ANGLE DISTRIBUTION")
print("=" * 80)
cursor.execute("""
    SELECT 
        CASE 
            WHEN ABS(yaw_angle) < 30 THEN 'frontal (< 30°)'
            WHEN ABS(yaw_angle) >= 30 AND ABS(yaw_angle) < 60 THEN 'profile (30-60°)'
            WHEN ABS(yaw_angle) >= 60 THEN 'extreme profile (>= 60°)'
            ELSE 'unknown'
        END as category,
        COUNT(*) as count
    FROM faces
    WHERE yaw_angle IS NOT NULL
    GROUP BY category
    ORDER BY count DESC
""")

distribution = cursor.fetchall()
for row in distribution:
    print(f"  {row['category']}: {row['count']} faces")

conn.close()

