#!/usr/bin/env python3
"""Update status of a reported photo in the auth database."""

import sys
from pathlib import Path

# Add project root to path
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

from sqlalchemy import text
from backend.db.session import get_auth_database_url, AuthSessionLocal

def update_reported_photo_status(report_id: int, new_status: str):
    """Update the status of a reported photo."""
    if AuthSessionLocal is None:
        raise ValueError("Auth database not configured. Set DATABASE_URL_AUTH environment variable.")
    
    db = AuthSessionLocal()
    try:
        # First check if the report exists and get its current status
        check_result = db.execute(text("""
            SELECT id, status, review_notes
            FROM inappropriate_photo_reports
            WHERE id = :report_id
        """), {"report_id": report_id})
        
        row = check_result.fetchone()
        if not row:
            print(f"❌ Reported photo {report_id} not found in database.")
            return
        
        current_status = row.status
        review_notes = row.review_notes
        
        print(f"📋 Current status: '{current_status}'")
        if review_notes:
            print(f"📝 Review notes: '{review_notes}'")
        
        if current_status == new_status:
            print(f"ℹ️  Status is already '{new_status}'. No update needed.")
            return
        
        # Update the status
        result = db.execute(text("""
            UPDATE inappropriate_photo_reports
            SET status = :new_status
            WHERE id = :report_id
        """), {
            "new_status": new_status,
            "report_id": report_id
        })
        
        db.commit()
        
        if result.rowcount > 0:
            print(f"✅ Successfully updated reported photo {report_id} status from '{current_status}' to '{new_status}'")
        else:
            print(f"⚠️  No rows updated.")
            
    except Exception as e:
        db.rollback()
        print(f"❌ Error updating reported photo status: {str(e)}")
        raise
    finally:
        db.close()

def find_reported_photo_by_note(search_note: str):
    """Find reported photos by review notes."""
    if AuthSessionLocal is None:
        raise ValueError("Auth database not configured. Set DATABASE_URL_AUTH environment variable.")
    
    db = AuthSessionLocal()
    try:
        result = db.execute(text("""
            SELECT id, photo_id, status, review_notes, reported_at
            FROM inappropriate_photo_reports
            WHERE review_notes LIKE :search_pattern
            ORDER BY id DESC
        """), {"search_pattern": f"%{search_note}%"})
        
        rows = result.fetchall()
        if not rows:
            print(f"❌ No reported photos found with note containing '{search_note}'")
            return []
        
        print(f"📋 Found {len(rows)} reported photo(s) with note containing '{search_note}':\n")
        for row in rows:
            print(f"  ID: {row.id}, Photo ID: {row.photo_id}, Status: {row.status}")
            print(f"  Notes: {row.review_notes}")
            print(f"  Reported at: {row.reported_at}\n")
        
        return rows
        
    except Exception as e:
        print(f"❌ Error searching for reported photos: {str(e)}")
        raise
    finally:
        db.close()

if __name__ == "__main__":
    if len(sys.argv) < 3:
        print("Usage: python scripts/update_reported_photo_status.py <report_id> <new_status>")
        print("   OR: python scripts/update_reported_photo_status.py search <search_text>")
        print("Example: python scripts/update_reported_photo_status.py 57 dismissed")
        print("Example: python scripts/update_reported_photo_status.py search 'agree. removed'")
        sys.exit(1)
    
    if sys.argv[1] == "search":
        search_text = sys.argv[2]
        find_reported_photo_by_note(search_text)
    else:
        report_id = int(sys.argv[1])
        new_status = sys.argv[2]
        update_reported_photo_status(report_id, new_status)

