import psycopg2
import os

# Database connection parameters
DB_PARAMS = {
    'dbname': 'bookbeach',
    'user': 'postgres',
    'password': 'F@f@k0s!!',
    'host': 'localhost'
}

def drop_all_tables(conn):
    """Drop all tables in the database"""
    try:
        cursor = conn.cursor()
        
        # Disable foreign key checks temporarily
        cursor.execute("SET session_replication_role = replica;")
        
        # Get all table names
        cursor.execute("""
            SELECT tablename 
            FROM pg_tables 
            WHERE schemaname = 'public'
        """)
        
        tables = cursor.fetchall()
        
        # Drop each table
        for table in tables:
            table_name = table[0]
            print(f"Dropping table: {table_name}")
            cursor.execute(f"DROP TABLE IF EXISTS {table_name} CASCADE;")
        
        # Re-enable foreign key checks
        cursor.execute("SET session_replication_role = DEFAULT;")
        
        conn.commit()
        cursor.close()
        
        print("All tables dropped successfully!")
        return True
    except Exception as e:
        print(f"Error dropping tables: {e}")
        conn.rollback()
        return False

def create_tables(conn, script_dir):
    """Create tables from the SQL script"""
    try:
        create_script = os.path.join(script_dir, 'database', 'scripts', '01_create_tables.sql')
        
        with open(create_script, 'r') as f:
            sql_content = f.read()
        
        cursor = conn.cursor()
        cursor.execute(sql_content)
        conn.commit()
        cursor.close()
        
        print("Tables created successfully!")
        return True
    except Exception as e:
        print(f"Error creating tables: {e}")
        conn.rollback()
        return False

def main():
    """Reset the database - drop all tables and recreate structure"""
    print("Resetting BookBeach database...")
    
    # Get the directory of this script
    script_dir = os.path.dirname(os.path.abspath(__file__))
    
    # Connect to the database
    try:
        conn = psycopg2.connect(
            host="localhost",
            database="bookbeach",
            user="postgres",
            password="F@f@k0s!!"
        )
        print("Connected to database successfully!")
    except Exception as e:
        print(f"Error connecting to database: {e}")
        return
    
    # Drop all tables
    print("Dropping all existing tables...")
    if not drop_all_tables(conn):
        print("Failed to drop tables!")
        conn.close()
        return
    
    # Create tables from scratch
    print("Creating tables from scratch...")
    if not create_tables(conn, script_dir):
        print("Failed to create tables!")
        conn.close()
        return
    
    # Close the connection
    conn.close()
    
    print("Database reset completed successfully!")
    print("All tables have been dropped and recreated with empty structure.")

if __name__ == "__main__":
    main()