-- Script para limpiar completamente la base de datos dev_pixelato
-- y configurarla con el usuario postgres

\c dev_pixelato

-- Eliminar todas las tablas existentes
DO $$ 
DECLARE 
    r RECORD;
BEGIN
    -- Eliminar todas las tablas en cascada
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') 
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
    
    -- Eliminar todas las secuencias
    FOR r IN (SELECT sequencename FROM pg_sequences WHERE schemaname = 'public')
    LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS public.' || quote_ident(r.sequencename) || ' CASCADE';
    END LOOP;
    
    -- Eliminar todas las vistas
    FOR r IN (SELECT viewname FROM pg_views WHERE schemaname = 'public')
    LOOP
        EXECUTE 'DROP VIEW IF EXISTS public.' || quote_ident(r.viewname) || ' CASCADE';
    END LOOP;
END $$;

-- Cambiar el owner del esquema public a postgres
ALTER SCHEMA public OWNER TO postgres;

-- Otorgar todos los permisos a postgres
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON DATABASE dev_pixelato TO postgres;

-- Revocar permisos del usuario anterior si es necesario
REVOKE ALL ON SCHEMA public FROM pixelato_user CASCADE;
REVOKE ALL ON DATABASE dev_pixelato FROM pixelato_user;

-- Verificación
\echo ''
\echo '==================================='
\echo 'Base de datos limpiada exitosamente'
\echo '==================================='
\echo ''
\echo 'Verificando estado:'
SELECT current_database() as base_de_datos, 
       current_user as usuario_actual,
       (SELECT count(*) FROM pg_tables WHERE schemaname = 'public') as tablas_publicas;

\echo ''
\echo 'Owner de la base de datos:'
SELECT datname, datowner::regrole FROM pg_database WHERE datname = 'dev_pixelato';