#!/usr/bin/env python3
"""
Analyze why only 6 faces have yaw angle data - investigate the matching process
"""

import sqlite3
import os
import json

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")
faces_with_yaw = cursor.fetchone()[0]

# Get faces without angle data
cursor.execute("SELECT COUNT(*) FROM faces WHERE yaw_angle IS NULL AND pitch_angle IS NULL AND roll_angle IS NULL")
faces_without_angles = cursor.fetchone()[0]

print("=" * 80)
print("POSE DATA COVERAGE ANALYSIS")
print("=" * 80)
print(f"\nTotal faces: {total_faces}")
print(f"Faces WITH yaw angle: {faces_with_yaw}")
print(f"Faces WITHOUT any angle data: {faces_without_angles}")
print(f"Coverage: {(faces_with_yaw/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,
           SUM(CASE WHEN yaw_angle IS NOT NULL THEN 1 ELSE 0 END) as with_yaw,
           SUM(CASE WHEN pitch_angle IS NOT NULL THEN 1 ELSE 0 END) as with_pitch,
           SUM(CASE WHEN roll_angle IS NOT NULL THEN 1 ELSE 0 END) as with_roll
    FROM faces
    GROUP BY pose_mode
    ORDER BY count DESC
""")

pose_modes = cursor.fetchall()
for row in pose_modes:
    print(f"\n{row['pose_mode']}:")
    print(f"  Total: {row['count']}")
    print(f"  With yaw: {row['with_yaw']}")
    print(f"  With pitch: {row['with_pitch']}")
    print(f"  With roll: {row['with_roll']}")

# Check photos and see if some photos have pose data while others don't
print("\n" + "=" * 80)
print("POSE DATA BY PHOTO")
print("=" * 80)
cursor.execute("""
    SELECT 
        p.id as photo_id,
        p.filename,
        COUNT(f.id) as total_faces,
        SUM(CASE WHEN f.yaw_angle IS NOT NULL THEN 1 ELSE 0 END) as faces_with_yaw,
        SUM(CASE WHEN f.pitch_angle IS NOT NULL THEN 1 ELSE 0 END) as faces_with_pitch,
        SUM(CASE WHEN f.roll_angle IS NOT NULL THEN 1 ELSE 0 END) as faces_with_roll
    FROM photos p
    LEFT JOIN faces f ON f.photo_id = p.id
    GROUP BY p.id, p.filename
    HAVING COUNT(f.id) > 0
    ORDER BY faces_with_yaw DESC, total_faces DESC
    LIMIT 20
""")

photos = cursor.fetchall()
print(f"\n{'Photo ID':<10} {'Filename':<40} {'Total':<8} {'Yaw':<6} {'Pitch':<7} {'Roll':<6}")
print("-" * 80)
for row in photos:
    print(f"{row['photo_id']:<10} {row['filename'][:38]:<40} {row['total_faces']:<8} "
          f"{row['faces_with_yaw']:<6} {row['faces_with_pitch']:<7} {row['faces_with_roll']:<6}")

# Check if there's a pattern - maybe older photos don't have pose data
print("\n" + "=" * 80)
print("ANALYSIS")
print("=" * 80)

# Check date added vs pose data
cursor.execute("""
    SELECT 
        DATE(p.date_added) as date_added,
        COUNT(f.id) as total_faces,
        SUM(CASE WHEN f.yaw_angle IS NOT NULL THEN 1 ELSE 0 END) as faces_with_yaw
    FROM photos p
    LEFT JOIN faces f ON f.photo_id = p.id
    GROUP BY DATE(p.date_added)
    ORDER BY date_added DESC
""")

dates = cursor.fetchall()
print("\nFaces by date added:")
print(f"{'Date':<15} {'Total':<8} {'With Yaw':<10} {'Coverage':<10}")
print("-" * 50)
for row in dates:
    coverage = (row['faces_with_yaw'] / row['total_faces'] * 100) if row['total_faces'] > 0 else 0
    print(f"{row['date_added'] or 'NULL':<15} {row['total_faces']:<8} {row['faces_with_yaw']:<10} {coverage:.1f}%")

# Check if pose detection might be failing for some photos
print("\n" + "=" * 80)
print("POSSIBLE REASONS FOR LOW COVERAGE")
print("=" * 80)
print("\n1. Pose detection might not be running for all photos")
print("2. Matching between DeepFace and RetinaFace might be failing (IoU threshold too strict?)")
print("3. RetinaFace might not be detecting faces in some photos")
print("4. Photos might have been processed before pose detection was fully implemented")

# Check if there are photos with multiple faces where some have pose data and some don't
cursor.execute("""
    SELECT 
        p.id as photo_id,
        p.filename,
        COUNT(f.id) as total_faces,
        SUM(CASE WHEN f.yaw_angle IS NOT NULL THEN 1 ELSE 0 END) as faces_with_yaw,
        SUM(CASE WHEN f.yaw_angle IS NULL THEN 1 ELSE 0 END) as faces_without_yaw
    FROM photos p
    JOIN faces f ON f.photo_id = p.id
    GROUP BY p.id, p.filename
    HAVING COUNT(f.id) > 1 
       AND SUM(CASE WHEN f.yaw_angle IS NOT NULL THEN 1 ELSE 0 END) > 0
       AND SUM(CASE WHEN f.yaw_angle IS NULL THEN 1 ELSE 0 END) > 0
    ORDER BY total_faces DESC
    LIMIT 10
""")

mixed_photos = cursor.fetchall()
if mixed_photos:
    print("\n" + "=" * 80)
    print("PHOTOS WITH MIXED POSE DATA (some faces have it, some don't)")
    print("=" * 80)
    print(f"\n{'Photo ID':<10} {'Filename':<40} {'Total':<8} {'With Yaw':<10} {'Without Yaw':<12}")
    print("-" * 80)
    for row in mixed_photos:
        print(f"{row['photo_id']:<10} {row['filename'][:38]:<40} {row['total_faces']:<8} "
              f"{row['faces_with_yaw']:<10} {row['faces_without_yaw']:<12}")
    print("\n⚠️  This suggests matching is failing for some faces even when pose detection runs")
else:
    print("\n✅ No photos found with mixed pose data (all or nothing per photo)")

conn.close()

