clear_data.py

import asyncio
from sqlalchemy import text
from app.core.database import engine

async def clear_all_data():
    """
    Deletes all data from all tables in the public schema
    while keeping the schema and tables intact.
    Restarts all sequences (IDs).
    """
    async with engine.begin() as conn:
        print("Fetching all table names...")
        # Get all table names in public schema
        result = await conn.execute(text(
            "SELECT table_name FROM information_schema.tables "
            "WHERE table_schema = 'public' AND table_type = 'BASE TABLE' "
            "AND table_name != 'alembic_version'"
        ))
        tables = [row[0] for row in result.fetchall()]
        
        if not tables:
            print("No tables found to clear.")
            return

        print(f"Found {len(tables)} tables: {', '.join(tables)}")
        
        # Truncate all tables in one go with CASCADE to handle foreign keys
        # RESTART IDENTITY resets serial/identity columns to 1
        truncate_query = f"TRUNCATE TABLE {', '.join(tables)} RESTART IDENTITY CASCADE;"
        
        print("Executing TRUNCATE...")
        await conn.execute(text(truncate_query))
        print("Successfully cleared all data and reset IDs.")

if __name__ == "__main__":
    confirm = input("Are you SURE you want to delete ALL data from the database? (y/n): ")
    if confirm.lower() == 'y':
        asyncio.run(clear_all_data())
    else:
        print("Operation cancelled.")