forge/backend/scripts/reconcile_assets.py

"""Reconcile database assets with storage files

This script cleans up mismatches between database records and actual files:
1. Deletes database records that point to non-existent files
2. Deletes orphaned files that don't have corresponding database records
"""
import os
import sys
from pathlib import Path

# Add parent directory to path for imports
sys.path.insert(0, str(Path(__file__).parent.parent))

from app.database import SessionLocal
from app.models.asset import Asset
from app.config import settings


def reconcile():
    """Main reconciliation function"""
    db = SessionLocal()

    print("=" * 80)
    print("FORGE AI: Asset Reconciliation Script")
    print("=" * 80)
    print()

    # STEP 1: Find database records with missing files
    print("Step 1: Checking database records for missing files...")
    print("-" * 80)

    assets = db.query(Asset).all()
    missing_files = []

    for asset in assets:
        if not os.path.exists(asset.file_path):
            missing_files.append(asset)
            print(f"✗ Missing file: {asset.file_path}")
            print(f"  Asset ID: {asset.id}")
            print(f"  Filename: {asset.original_filename}")
            print()

    if missing_files:
        print(f"\nFound {len(missing_files)} database records with missing files")
        confirm = input(f"\nDelete {len(missing_files)} database records? (yes/no): ")

        if confirm.lower() == 'yes':
            for asset in missing_files:
                db.delete(asset)
            db.commit()
            print(f"✓ Deleted {len(missing_files)} orphaned database records")
        else:
            print("Skipped deleting database records")
    else:
        print("✓ No database records with missing files")

    print()
    print("=" * 80)

    # STEP 2: Find orphaned files in storage
    print("\nStep 2: Checking storage for orphaned files...")
    print("-" * 80)

    storage_dirs = ['images', 'videos', 'audio', 'audios', 'documents']
    orphaned_files = []

    for dir_name in storage_dirs:
        dir_path = os.path.join(settings.storage_path, dir_name)

        if not os.path.exists(dir_path):
            print(f"⚠ Directory not found: {dir_path}")
            continue

        print(f"\nScanning: {dir_path}")

        for filename in os.listdir(dir_path):
            file_path = os.path.join(dir_path, filename)

            # Skip directories
            if os.path.isdir(file_path):
                continue

            # Check if file exists in database
            asset = db.query(Asset).filter(Asset.file_path == file_path).first()

            if not asset:
                orphaned_files.append(file_path)
                file_size = os.path.getsize(file_path)
                print(f"✗ Orphaned file: {file_path} ({file_size / 1024:.1f} KB)")

    if orphaned_files:
        print(f"\nFound {len(orphaned_files)} orphaned files")
        confirm = input(f"\nDelete {len(orphaned_files)} orphaned files? (yes/no): ")

        if confirm.lower() == 'yes':
            for file_path in orphaned_files:
                try:
                    os.remove(file_path)
                    print(f"✓ Deleted: {file_path}")
                except Exception as e:
                    print(f"✗ Failed to delete {file_path}: {e}")
            print(f"\n✓ Deleted {len(orphaned_files)} orphaned files")
        else:
            print("Skipped deleting orphaned files")
    else:
        print("\n✓ No orphaned files found")

    print()
    print("=" * 80)
    print("Reconciliation complete!")
    print("=" * 80)

    db.close()


if __name__ == "__main__":
    try:
        reconcile()
    except KeyboardInterrupt:
        print("\n\nReconciliation cancelled by user")
        sys.exit(1)
    except Exception as e:
        print(f"\n\nError during reconciliation: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)