-- Database Reset Script
-- Deletes data from process tables but keeps catalogs and the admin user (ID 1).

BEGIN;

-- 1. Disable triggers temporarily if needed (PostgreSQL doesn't have a simple "disable all checks" without superuser, 
-- but we will delete in order to avoid FK violations).

-- 2. Delete data from "Leaf" tables (tables that depend on others but no one depends on them, or low level)

-- Bitacoras & Evidences
TRUNCATE TABLE bitacora_evidencias RESTART IDENTITY CASCADE;
TRUNCATE TABLE bitacora_tareas RESTART IDENTITY CASCADE;
TRUNCATE TABLE bitacoras RESTART IDENTITY CASCADE;

-- Rescheduling
TRUNCATE TABLE clase_recalendarizaciones RESTART IDENTITY CASCADE;

-- Goals
TRUNCATE TABLE meta_alumnos RESTART IDENTITY CASCADE;

-- Auth & Notifications
TRUNCATE TABLE webauthn_credentials RESTART IDENTITY CASCADE;
TRUNCATE TABLE notificaciones RESTART IDENTITY CASCADE;

-- Payments
TRUNCATE TABLE pagos RESTART IDENTITY CASCADE;

-- Relations
DELETE FROM rel_usuario_archivo;

-- 3. Delete from Tables with dependencies

-- Classes (depends on Materia, Salon, Usuario)
TRUNCATE TABLE clases RESTART IDENTITY CASCADE;

-- Profiles (depends on Usuario)
TRUNCATE TABLE perfil_profesores RESTART IDENTITY CASCADE;
TRUNCATE TABLE perfil_alumnos RESTART IDENTITY CASCADE;

-- 4. Delete Core Process Tables
TRUNCATE TABLE materias RESTART IDENTITY CASCADE;
TRUNCATE TABLE salones RESTART IDENTITY CASCADE;

-- 5. Delete Files (Archivos)
-- Assumes all files in 'archivos' are user uploads that should be cleared.
-- If some files are system assets, we should filter them. Based on current knowledge, they are uploads.
-- We must delete them carefully if they are referenced by users.
-- Since we deleted rel_usuario_archivo, payments(comprobante), bitacora_tareas, notificaciones, we should be safe to clear archives.
TRUNCATE TABLE archivos RESTART IDENTITY CASCADE;

-- 6. Delete Users & Addresses (The Special Case)
-- We need to keep Admin (ID 1).

-- Addresses: Delete all addresses belonging to users other than ID 1.
DELETE FROM direcciones WHERE fk_id_usuario != 1;

-- Users: Delete all users except ID 1.
DELETE FROM usuarios WHERE id_usuario != 1;

-- 7. Reset Sequences
-- 'TRUNCATE ... RESTART IDENTITY' handles this for truncated tables, but for 'usuarios' and 'direcciones' (where we used DELETE), we might need to manually reset logic if we wanted them to start from a specific number, 
-- but since we kept ID 1, the sequence should probably continue or be reset to 2 if we want to fill the gaps.
-- TRUNCATE RESTART IDENTITY resets to the initial value (usually 1).
-- Let's explicitly reset sequences for tables we truncated just to be double sure, although RESTART IDENTITY already does it.
-- For Usuarios, we want next id to be 2 (if 1 exists).

ALTER SEQUENCE usuarios_id_usuario_seq RESTART WITH 2;
-- Verify if direcciones needs reset. If admin has an address, we shouldn't reset to 1 if that address ID > 1?
-- Assuming admin address might be ID 1 or doesn't exist. Safest is to set it to max(id) + 1.
SELECT setval('direcciones_id_direccion_seq', COALESCE((SELECT MAX(id_direccion) FROM direcciones), 1), true);

-- For other tables that were Truncated with RESTART IDENTITY, they represent a clean slate.
-- So they are already at 1.

COMMIT;

-- Verification query (Optional, can be run manually)
-- SELECT count(*) as usuarios_restantes FROM usuarios;
