database_cleanup.sql

-- Database Cleanup Script
-- Run this script only if you want to completely reset your database structure.
-- This will delete all tables and data created by the previous/conflicting queries.

-- 1. Drop Triggers
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;

-- 2. Drop Functions
DROP FUNCTION IF EXISTS public.handle_new_user();
DROP FUNCTION IF EXISTS public.update_leaderboard(uuid, uuid, integer);

-- 3. Drop Policies (Cascaded with Tables, but good for reference if objects remain)
-- (No need to explicitly drop policies as they go with tables)

-- 4. Drop Tables (In correct dependency order)
-- Child tables first (tables that reference others)
DROP TABLE IF EXISTS public.leaderboard;
DROP TABLE IF EXISTS public.submissions;
DROP TABLE IF EXISTS public.test_cases;
DROP TABLE IF EXISTS public.problems;
-- Parent tables next
DROP TABLE IF EXISTS public.contests;
DROP TABLE IF EXISTS public.profiles;
DROP TABLE IF EXISTS public.roles;

-- 5. Drop Extensions (Optional, usually creating them is harmless)
-- DROP EXTENSION IF EXISTS "pgcrypto";