#!/usr/bin/env python3
"""
Migrate face detection results to face_recognition_results table
"""

import psycopg2
import json
from datetime import datetime


def migrate_results():
    """Migrate face detection results"""
    print("🔄 Migrating face detection results...")

    try:
        # Connect to database
        conn = psycopg2.connect(
            host="localhost",
            port=5432,
            database="momentry",
            user="accusys",
            password="accusys",
        )
        cursor = conn.cursor()

        # Get unique video UUIDs from face_detections
        cursor.execute("""
            SELECT DISTINCT video_uuid 
            FROM face_detections 
            WHERE video_uuid IS NOT NULL
        """)

        video_uuids = [row[0] for row in cursor.fetchall()]
        print(f"Found {len(video_uuids)} videos with face detections")

        migrated_count = 0

        for video_uuid in video_uuids:
            print(f"\nProcessing video: {video_uuid}")

            # Get face detection statistics
            cursor.execute(
                """
                SELECT 
                    COUNT(*) as total_faces,
                    COUNT(DISTINCT frame_number) as frames_with_faces,
                    AVG(confidence) as avg_confidence,
                    MIN(frame_number) as min_frame,
                    MAX(frame_number) as max_frame
                FROM face_detections 
                WHERE video_uuid = %s
            """,
                (video_uuid,),
            )

            stats = cursor.fetchone()
            total_faces, frames_with_faces, avg_confidence, min_frame, max_frame = stats

            # Get gender distribution from attributes JSON
            cursor.execute(
                """
                SELECT 
                    COUNT(*) FILTER (WHERE attributes->>'gender' = 'male') as male_count,
                    COUNT(*) FILTER (WHERE attributes->>'gender' = 'female') as female_count,
                    COUNT(*) FILTER (WHERE attributes->>'gender' IS NULL OR attributes->>'gender' NOT IN ('male', 'female')) as unknown_count
                FROM face_detections 
                WHERE video_uuid = %s
            """,
                (video_uuid,),
            )

            male_count, female_count, unknown_count = cursor.fetchone()

            # Get age statistics from attributes JSON
            cursor.execute(
                """
                SELECT 
                    MIN((attributes->>'age')::float) as min_age,
                    MAX((attributes->>'age')::float) as max_age,
                    AVG((attributes->>'age')::float) as avg_age
                FROM face_detections 
                WHERE video_uuid = %s AND attributes->>'age' IS NOT NULL
            """,
                (video_uuid,),
            )

            age_stats = cursor.fetchone()
            min_age, max_age, avg_age = age_stats

            # Create result data JSON
            result_data = {
                "video_uuid": video_uuid,
                "total_faces": total_faces,
                "frames_with_faces": frames_with_faces,
                "gender_distribution": {
                    "male": male_count,
                    "female": female_count,
                    "unknown": unknown_count,
                },
                "age_statistics": {
                    "min": float(min_age) if min_age else None,
                    "max": float(max_age) if max_age else None,
                    "average": float(avg_age) if avg_age else None,
                },
                "confidence": {
                    "average": float(avg_confidence) if avg_confidence else None
                },
                "frame_range": {"min": min_frame, "max": max_frame},
                "analysis_timestamp": datetime.utcnow().isoformat(),
            }

            # Check if result already exists
            cursor.execute(
                """
                SELECT COUNT(*) FROM face_recognition_results 
                WHERE video_uuid = %s
            """,
                (video_uuid,),
            )

            if cursor.fetchone()[0] == 0:
                # Insert new result
                cursor.execute(
                    """
                    INSERT INTO face_recognition_results (
                        video_uuid,
                        frame_count,
                        fps,
                        total_faces,
                        recognized_faces,
                        clusters_count,
                        result_data,
                        processing_time_secs,
                        created_at
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """,
                    (
                        video_uuid,
                        frames_with_faces,  # frame_count
                        30.0,  # fps (assumed)
                        total_faces,
                        0,  # recognized_faces (none yet)
                        0,  # clusters_count
                        json.dumps(result_data),
                        0.0,  # processing_time_secs
                        datetime.utcnow(),
                    ),
                )

                migrated_count += 1
                print(f"  ✅ Migrated {total_faces} faces")
            else:
                print("  ⚠️ Already exists, skipping")

        # Commit changes
        conn.commit()

        print(f"\n✅ Migration complete: {migrated_count} videos migrated")

        # Show summary
        cursor.execute("SELECT COUNT(*) FROM face_recognition_results")
        total_results = cursor.fetchone()[0]
        print(f"Total results in face_recognition_results: {total_results}")

        cursor.close()
        conn.close()

    except Exception as e:
        print(f"❌ Error: {e}")
        import traceback

        traceback.print_exc()


def test_api_after_migration():
    """Test API after migration"""
    print("\n🧪 Testing API after migration...")

    import requests

    BASE_URL = "http://localhost:3002"
    API_KEY = "muser_243c6725b09f43e29f319a648645b992_1774874668_f224a6d2"
    VIDEO_UUID = "384b0ff44aaaa1f1"

    headers = {"X-API-Key": API_KEY}

    try:
        response = requests.get(
            f"{BASE_URL}/api/v1/face/results/{VIDEO_UUID}", headers=headers, timeout=10
        )

        print(f"Status: {response.status_code}")

        if response.status_code == 200:
            data = response.json()
            print("✅ Success!")
            print(f"Video UUID: {data.get('video_uuid')}")
            print(f"Total faces: {data.get('total_faces')}")
            print(f"Processing time: {data.get('processing_time_secs')}s")

            # Pretty print result data
            result_data = data.get("result_data", {})
            if isinstance(result_data, str):
                result_data = json.loads(result_data)

            print("\n📊 Detailed results:")
            print(f"  Frames with faces: {result_data.get('frames_with_faces')}")

            gender_dist = result_data.get("gender_distribution", {})
            print(
                f"  Gender: {gender_dist.get('male')} male, {gender_dist.get('female')} female"
            )

            age_stats = result_data.get("age_statistics", {})
            print(f"  Age: {age_stats.get('min')}-{age_stats.get('max')} years")

            return True
        else:
            print(f"❌ Failed: {response.text}")
            return False

    except Exception as e:
        print(f"❌ Error: {e}")
        return False


def main():
    print("=" * 60)
    print("🔄 Face Results Migration Tool")
    print("=" * 60)

    # Migrate results
    migrate_results()

    # Test API
    test_api_after_migration()

    print("\n" + "=" * 60)
    print("✅ Migration and test completed!")
    print("=" * 60)


if __name__ == "__main__":
    main()