#!/usr/bin/env python3
"""
Analyze all faces to see why most don't have angle data
"""

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 total faces
cursor.execute("SELECT COUNT(*) FROM faces")
total_faces = cursor.fetchone()[0]

# Get faces with angle data
cursor.execute("SELECT COUNT(*) FROM faces WHERE yaw_angle IS NOT NULL OR pitch_angle IS NOT NULL OR roll_angle IS NOT NULL")
faces_with_angles = cursor.fetchone()[0]

# Get faces without any angle data
faces_without_angles = total_faces - faces_with_angles

print("=" * 80)
print("FACE ANGLE DATA ANALYSIS")
print("=" * 80)
print(f"\nTotal faces: {total_faces}")
print(f"Faces WITH angle data: {faces_with_angles}")
print(f"Faces WITHOUT angle data: {faces_without_angles}")
print(f"Percentage with angle data: {(faces_with_angles/total_faces*100):.1f}%")

# Check pose_mode distribution
print("\n" + "=" * 80)
print("POSE_MODE DISTRIBUTION")
print("=" * 80)
cursor.execute("""
    SELECT pose_mode, COUNT(*) as count
    FROM faces
    GROUP BY pose_mode
    ORDER BY count DESC
""")

pose_modes = cursor.fetchall()
for row in pose_modes:
    percentage = (row['count'] / total_faces) * 100
    print(f"  {row['pose_mode']:<30} : {row['count']:>4} ({percentage:>5.1f}%)")

# Check faces with pose_mode=frontal but might have high yaw
print("\n" + "=" * 80)
print("FACES WITH POSE_MODE='frontal' BUT NO ANGLE DATA")
print("=" * 80)
print("(These faces might actually be profile faces but weren't analyzed)")

cursor.execute("""
    SELECT COUNT(*) 
    FROM faces 
    WHERE pose_mode = 'frontal' 
    AND yaw_angle IS NULL 
    AND pitch_angle IS NULL 
    AND roll_angle IS NULL
""")
frontal_no_data = cursor.fetchone()[0]
print(f"  Faces with pose_mode='frontal' and no angle data: {frontal_no_data}")

# Check if pose detection is being run for all faces
print("\n" + "=" * 80)
print("ANALYSIS")
print("=" * 80)
print(f"Only {faces_with_angles} out of {total_faces} faces have angle data stored.")
print("This suggests that pose detection is NOT being run for all faces.")
print("\nPossible reasons:")
print("  1. Pose detection may have been disabled or failed for most faces")
print("  2. Only faces processed recently have pose data")
print("  3. Pose detection might only run when RetinaFace is available")

conn.close()

