scripts/clear_videos_data.py

import asyncio
import os
import sys
import shutil

# Add the parent directory to sys.path to allow importing app
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from app.services.supabase_client import get_supabase_service
from app.config import get_settings

async def clear_all_video_data():
    """
    Clears all video files and associated database records for team and personal videos.
    """
    supabase = get_supabase_service()
    settings = get_settings()
    
    print("🚀 Starting comprehensive video data cleanup...")
    
    # 1. Fetch all videos to know what files to delete
    print("--- 📺 Fetching all video records ---")
    videos = await supabase.select("videos")
    print(f"Found {len(videos)} video records.")
    
    # 2. Delete files for each video
    print("--- 📂 Deleting local and cloud files ---")
    for video in videos:
        video_id = str(video.get("id"))
        storage_path = video.get("storage_path")
        
        # Original video
        if storage_path and os.path.exists(storage_path):
            try:
                os.remove(storage_path)
                print(f"Deleted original: {storage_path}")
            except Exception as e:
                print(f"Error deleting original for {video_id}: {e}")
        
        # Annotated video
        annotated_path = os.path.join("output_videos", "annotated", f"{video_id}.mp4")
        if os.path.exists(annotated_path):
            try:
                os.remove(annotated_path)
                print(f"Deleted annotated: {annotated_path}")
            except Exception as e:
                print(f"Error deleting annotated for {video_id}: {e}")
                
        # Personal analysis outputs
        personal_output_dir = os.path.join("uploads", "personal_output")
        for suffix in ["_output.mp4", "_output.avi", "_report.txt", "_input.mp4", "_input.avi"]:
            personal_path = os.path.join(personal_output_dir, f"{video_id}{suffix}")
            if os.path.exists(personal_path):
                try:
                    os.remove(personal_path)
                    print(f"Deleted personal output: {personal_path}")
                except Exception:
                    pass
        
        # Reports
        reports_dir = "reports"
        report_path = os.path.join(reports_dir, f"{video_id}_report.txt")
        if os.path.exists(report_path):
            try:
                os.remove(report_path)
                print(f"Deleted report: {report_path}")
            except Exception:
                pass

        # Supabase Storage (if connected)
        if supabase.is_connected:
            try:
                # Video bucket
                uploader_id = video.get("uploader_id")
                filename = os.path.basename(storage_path) if storage_path else f"{video_id}.mp4"
                cloud_path = f"{uploader_id}/{filename}"
                await supabase.delete_file("videos", cloud_path)
                
                # Personal analysis bucket
                personal_cloud_path = f"{uploader_id}/{video_id}_output.mp4"
                await supabase.delete_file("personal-analysis-videos", personal_cloud_path)
            except Exception:
                pass

    # 3. Purge entire directories to catch orphaned files
    print("--- 🧹 Purging directories ---")
    dirs_to_purge = [
        os.path.join("uploads", "personal_output"),
        "output_videos/annotated",
        "stubs",
        "reports",
    ]
    
    for d in dirs_to_purge:
        if os.path.exists(d):
            try:
                for f in os.listdir(d):
                    fpath = os.path.join(d, f)
                    if os.path.isfile(fpath):
                        os.remove(fpath)
                    elif os.path.isdir(fpath):
                        shutil.rmtree(fpath)
                print(f"Purged directory: {d}")
            except Exception as e:
                print(f"Error purging {d}: {e}")

    # 4. Clear database tables
    print("--- 🗄️  Clearing database tables ---")
    # Tables to clear that are directly video-related
    # Order: Children first if NO CASCADE (but schema has cascade for detections and analysis_results)
    
    # Tables that might NOT have cascade or need manual clearing of video-linked rows
    video_related_tables = [
        "analytics",
        "personal_analyses",
        "activities" # Some activities might be linked to video processing
    ]
    
    for table in video_related_tables:
        try:
            print(f"Clearing video-related records in {table}...")
            # Use neq id 0 as a broad filter to delete everything in these tables
            await supabase._run_sync(lambda: supabase.client.table(table).delete().neq("id", "00000000-0000-0000-0000-000000000000").execute())
            print(f"Successfully cleared {table}")
        except Exception as e:
            print(f"Note: Error clearing {table}: {e}")

    # Finally, clear the main videos table (should cascade to detections and analysis_results)
    try:
        print("Clearing videos table (cascading)...")
        await supabase._run_sync(lambda: supabase.client.table("videos").delete().neq("id", "00000000-0000-0000-0000-000000000000").execute())
        print("Successfully cleared videos table")
    except Exception as e:
        print(f"Error clearing videos table: {e}")

    print("✅ Cleanup complete.")

if __name__ == "__main__":
    asyncio.run(clear_all_video_data())