"""
Script to clear all database data EXCEPT users
This will delete all jobs, contracts, messages, notifications, etc., but keep user accounts intact
WARNING: This is irreversible!
"""
from app import create_app
from app.extensions import db
from app.models import (
    Job, Application, Contract, Payment, Invoice, Rating, Category, 
    AuditLog, WHSAssessment, Dispute
)
from app.models.notification import NotificationPreference, NotificationTemplate, Notification
from app.models.message import Message
from app.models.file_upload import FileUpload
from app.models.safety import Review, JobProgress
from app.models.time_tracking import TimeEntry, Shift, ShiftBid, ShiftSwapRequest
from app.models.gamification import Leaderboard, Achievement, PointActivity
from app.models.booking import Booking, RecurringBooking
from app.models.availability import Availability, WorkerSchedulePreferences

app = create_app()

def count_records():
    """Count all records that will be deleted"""
    counts = {
        'File Uploads': FileUpload.query.count(),
        'Messages': Message.query.count(),
        'Notifications': Notification.query.count(),
        'Notification Templates': NotificationTemplate.query.count(),
        'Payments': Payment.query.count(),
        'Invoices': Invoice.query.count(),
        'Reviews': Review.query.count(),
        'Job Progress': JobProgress.query.count(),
        'Contracts': Contract.query.count(),
        'Applications': Application.query.count(),
        'Jobs': Job.query.count(),
        'Ratings': Rating.query.count(),
        'WHS Assessments': WHSAssessment.query.count(),
        'Disputes': Dispute.query.count(),
        'Audit Logs': AuditLog.query.count(),
        'Time Entries': TimeEntry.query.count(),
        'Shifts': Shift.query.count(),
        'Shift Bids': ShiftBid.query.count(),
        'Shift Swap Requests': ShiftSwapRequest.query.count(),
        'Leaderboards': Leaderboard.query.count(),
        'Achievements': Achievement.query.count(),
        'Point Activities': PointActivity.query.count(),
        'Bookings': Booking.query.count(),
        'Recurring Bookings': RecurringBooking.query.count(),
        'Availabilities': Availability.query.count(),
        'Worker Schedule Preferences': WorkerSchedulePreferences.query.count(),
    }
    return counts

with app.app_context():
    print("="*70)
    print("DATABASE CLEANUP - KEEP USERS ONLY")
    print("="*70)
    print("\n⚠️  WARNING: This will delete ALL data except user accounts!")
    print("This includes:")
    print("  - All jobs, applications, and contracts")
    print("  - All messages and file uploads")
    print("  - All notifications and preferences")
    print("  - All payments, invoices, and ratings")
    print("  - All time entries, shifts, and bookings")
    print("  - All gamification data (leaderboards, achievements, points)")
    print("  - All disputes, audit logs, and WHS assessments")
    print("\n📊 Current record counts:")
    
    initial_counts = count_records()
    total_records = 0
    for name, count in initial_counts.items():
        if count > 0:
            print(f"  - {name}: {count}")
            total_records += count
    
    if total_records == 0:
        print("\n✅ Database is already clean (no records to delete)")
        exit()
    
    print(f"\n🔢 Total records to delete: {total_records}")
    print("\n" + "="*70)
    confirm = input("Type 'DELETE ALL' to confirm (this cannot be undone): ")
    
    if confirm != 'DELETE ALL':
        print("❌ Operation cancelled")
        exit()
    
    try:
        print("\n🗑️  Deleting records in correct order...\n")
        
        # 1. Delete file uploads (they reference messages and users)
        deleted = FileUpload.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} file upload(s)")
        
        # 2. Delete notifications (they reference users)
        deleted = Notification.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} notification(s)")
        
        # 3. Delete notification templates (standalone)
        deleted = NotificationTemplate.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} notification template(s)")
        
        # Note: Keep NotificationPreference - users may want to keep their preferences
        # Uncomment below if you want to delete them too
        # deleted = NotificationPreference.query.delete()
        # if deleted:
        #     print(f"  ✓ Deleted {deleted} notification preference(s)")
        
        # 4. Delete messages (they reference users)
        deleted = Message.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} message(s)")
        
        # 5. Delete payments and invoices (they reference contracts)
        deleted = Invoice.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} invoice(s)")
        
        deleted = Payment.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} payment(s)")
        
        # 6. Delete ratings first (they reference contracts)
        deleted = Rating.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} rating(s)")
        
        # 7. Delete time tracking data (they reference contracts)
        deleted = TimeEntry.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} time entry/entries")
        
        deleted = ShiftSwapRequest.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} shift swap request(s)")
        
        deleted = ShiftBid.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} shift bid(s)")
        
        deleted = Shift.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} shift(s)")
        
        # 8. Delete reviews (they reference contracts)
        deleted = Review.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} review(s)")
        
        # 9. Delete job progress (they reference jobs and contracts)
        deleted = JobProgress.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} job progress record(s)")
        
        # 10. Delete contracts (they reference jobs and users)
        deleted = Contract.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} contract(s)")
        
        # 11. Delete applications (they reference jobs and users)
        deleted = Application.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} application(s)")
        
        # 12. Delete jobs (they reference users and categories)
        deleted = Job.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} job(s)")
        
        # 13. Delete WHS assessments (they reference users)
        deleted = WHSAssessment.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} WHS assessment(s)")
        
        # 14. Delete disputes (they reference users)
        deleted = Dispute.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} dispute(s)")
        
        # 15. Delete audit logs (they reference users)
        deleted = AuditLog.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} audit log(s)")
        
        # 16. Delete gamification data
        deleted = PointActivity.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} point activity/activities")
        
        deleted = Achievement.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} achievement(s)")
        
        deleted = Leaderboard.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} leaderboard entry/entries")
        
        # 17. Delete booking data
        deleted = RecurringBooking.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} recurring booking(s)")
        
        deleted = Booking.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} booking(s)")
        
        # 18. Delete availability data
        deleted = Availability.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} availability record(s)")
        
        deleted = WorkerSchedulePreferences.query.delete()
        if deleted:
            print(f"  ✓ Deleted {deleted} worker schedule preference(s)")
        
        # Note: Not deleting Categories as they are system data, not user-generated
        # Uncomment below if you want to delete them too
        # deleted = Category.query.delete()
        # if deleted:
        #     print(f"  ✓ Deleted {deleted} category/categories")
        
        # Commit all deletions
        db.session.commit()
        
        print("\n" + "="*70)
        print("✅ DATABASE CLEANED SUCCESSFULLY!")
        print("="*70)
        print("\n📊 Final verification:")
        
        final_counts = count_records()
        remaining = sum(final_counts.values())
        
        if remaining == 0:
            print("  ✅ All records deleted successfully!")
        else:
            print(f"  ⚠️  {remaining} records remaining:")
            for name, count in final_counts.items():
                if count > 0:
                    print(f"     - {name}: {count}")
        
        from app.models import User
        user_count = User.query.count()
        print(f"\n👥 Users preserved: {user_count}")
        print("\n✅ All user accounts remain intact with their credentials")
        
    except Exception as e:
        db.session.rollback()
        print(f"\n❌ Error during deletion: {str(e)}")
        import traceback
        traceback.print_exc()
        print("\n⚠️  Database rolled back - no changes made")