reset_database.py

#!/usr/bin/env python3
"""
Database reset script for the LLM Chatbot.
This script clears all transaction data while keeping the basic structure intact.
"""

import sqlite3
import os

def reset_database():
    """Reset the database by clearing all transaction data"""
    
    db_path = "chatbot.db"
    
    if not os.path.exists(db_path):
        print(f"❌ Database file '{db_path}' not found.")
        return
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        print("🗑️  Clearing transaction data...")
        
        # Clear all transaction data
        cursor.execute("DELETE FROM sales")
        cursor.execute("DELETE FROM purchases")
        
        # Reset auto-increment counters
        cursor.execute("DELETE FROM sqlite_sequence WHERE name IN ('sales', 'purchases')")
        
        # Clear customers that were created during testing (keep default ones)
        cursor.execute("DELETE FROM customers")
        
        # Keep default suppliers and products, but can remove dynamic ones
        # For now, we'll keep all suppliers and products
        
        conn.commit()
        
        # Check results
        cursor.execute("SELECT COUNT(*) FROM purchases")
        purchases_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM sales")
        sales_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM customers")
        customers_count = cursor.fetchone()[0]
        
        print(f"✅ Database reset complete!")
        print(f"   - Purchases: {purchases_count}")
        print(f"   - Sales: {sales_count}")
        print(f"   - Customers: {customers_count}")
        print(f"💡 You can now add new sample data using 'python populate_sample_data.py'")
        
    except Exception as e:
        print(f"❌ Error resetting database: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    response = input("⚠️  This will delete all transaction data. Continue? (y/N): ")
    if response.lower() in ['y', 'yes']:
        reset_database()
    else:
        print("🚫 Operation cancelled.")