#!/usr/bin/env python3
"""
Cleanup script to remove test cases from the database
Removes cases with file_case_id starting with 'TEST_CASE_'
"""

import sqlite3
import os
import sys

# Get database path
db_path = 'database/vera_medical.db'

if not os.path.exists(db_path):
    print(f"Error: Database not found at {db_path}")
    sys.exit(1)

conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

try:
    # Find all test cases
    test_cases = cursor.execute("""
        SELECT id, file_case_id FROM cases 
        WHERE file_case_id LIKE 'TEST_CASE_%'
    """).fetchall()
    
    if not test_cases:
        print("No test cases found to delete.")
        conn.close()
        sys.exit(0)
    
    print(f"Found {len(test_cases)} test cases to delete:")
    for case in test_cases:
        print(f"  - Case ID: {case['id']}, File Case ID: {case['file_case_id']}")
    
    # Get confirmation (skip if --yes flag is provided)
    if '--yes' not in sys.argv:
        response = input("\nDo you want to delete these test cases? (yes/no): ")
        if response.lower() != 'yes':
            print("Deletion cancelled.")
            conn.close()
            sys.exit(0)
    else:
        print("\nProceeding with deletion (--yes flag provided)...")
    
    # Get all case IDs
    case_ids = [case['id'] for case in test_cases]
    placeholders = ','.join(['?'] * len(case_ids))
    
    print("\nDeleting related data...")
    
    # Delete related appointments
    appointments_deleted = cursor.execute(f"""
        DELETE FROM appointments WHERE case_id IN ({placeholders})
    """, case_ids).rowcount
    print(f"  - Deleted {appointments_deleted} appointments")
    
    # Delete related invoices
    invoices_deleted = cursor.execute(f"""
        DELETE FROM invoices WHERE case_id IN ({placeholders})
    """, case_ids).rowcount
    print(f"  - Deleted {invoices_deleted} invoices")
    
    # Delete related reports
    reports_deleted = cursor.execute(f"""
        DELETE FROM reports WHERE case_id IN ({placeholders})
    """, case_ids).rowcount
    print(f"  - Deleted {reports_deleted} reports")
    
    # Delete related tasks
    tasks_deleted = cursor.execute(f"""
        DELETE FROM bring_forward_tasks WHERE case_id IN ({placeholders})
    """, case_ids).rowcount
    print(f"  - Deleted {tasks_deleted} tasks")
    
    # Delete related appointment letters
    # First get appointment IDs that were deleted
    appointment_ids = cursor.execute(f"""
        SELECT id FROM appointments WHERE case_id IN ({placeholders})
    """, case_ids).fetchall()
    
    if appointment_ids:
        apt_ids = [apt['id'] for apt in appointment_ids]
        apt_placeholders = ','.join(['?'] * len(apt_ids))
        letters_deleted = cursor.execute(f"""
            DELETE FROM appointment_letters WHERE appointment_id IN ({apt_placeholders})
        """, apt_ids).rowcount
        print(f"  - Deleted {letters_deleted} appointment letters")
    
    # Delete related additional services
    if appointment_ids:
        services_deleted = cursor.execute(f"""
            DELETE FROM appointment_additional_services WHERE appointment_id IN ({apt_placeholders})
        """, apt_ids).rowcount
        print(f"  - Deleted {services_deleted} additional services")
    
    # Delete related meds documentation
    if appointment_ids:
        meds_deleted = cursor.execute(f"""
            DELETE FROM appointment_meds WHERE appointment_id IN ({apt_placeholders})
        """, apt_ids).rowcount
        print(f"  - Deleted {meds_deleted} meds documentation")
    
    # Delete the cases themselves
    print("\nDeleting test cases...")
    cases_deleted = cursor.execute(f"""
        DELETE FROM cases WHERE id IN ({placeholders})
    """, case_ids).rowcount
    
    conn.commit()
    print(f"\n✓ Successfully deleted {cases_deleted} test cases and all related data!")
    
except Exception as e:
    conn.rollback()
    print(f"\n✗ Error: {str(e)}")
    sys.exit(1)
finally:
    conn.close()