supabase-setup.sql

-- Solo para una instalación nueva. Para una base existente, usar la migración.
-- Las inscripciones están cerradas; el navegador no tiene acceso a estas tablas.
BEGIN;
-- Script simplificado para crear tabla de voluntarios en Supabase
-- Ejecutar este script completo en el SQL Editor de Supabase

-- 1. Crear la tabla
CREATE TABLE public.volunteers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT,
    city TEXT,
    experience TEXT,
    interests TEXT[],
    availability TEXT,
    message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Crear índices para mejor rendimiento
CREATE INDEX idx_volunteers_email ON volunteers(email);
CREATE INDEX idx_volunteers_city ON volunteers(city);

-- 3. Habilitar Row Level Security
ALTER TABLE volunteers ENABLE ROW LEVEL SECURITY;



-- ============================================
-- TABLA: python_route_registrations
-- ============================================

-- 1. Crear tabla para registros de Python Route
CREATE TABLE public.python_route_registrations (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    phone TEXT,
    age INT,
    province TEXT NOT NULL,
    exact_location TEXT,
    group_type TEXT NOT NULL,
    workshop_interest TEXT NOT NULL,
    programming_experience TEXT NOT NULL,
    newsletter_consent BOOLEAN DEFAULT false,
    data_protection_accepted BOOLEAN DEFAULT false,
    additional_comments TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Crear índices para mejor rendimiento
CREATE INDEX idx_python_route_email ON python_route_registrations(email);
CREATE INDEX idx_python_route_province ON python_route_registrations(province);
CREATE INDEX idx_python_route_created_at ON python_route_registrations(created_at);

-- 3. Habilitar Row Level Security
ALTER TABLE python_route_registrations ENABLE ROW LEVEL SECURITY;



-- Los formularios están desactivados: los roles del navegador no necesitan acceso.
-- No se borra ni se transforma ningún registro existente.
DO $security$
DECLARE
    target text;
    column_names text;
    sequence_name text;
BEGIN
    FOREACH target IN ARRAY ARRAY['volunteers', 'python_route_registrations'] LOOP
        IF to_regclass(format('public.%I', target)) IS NULL THEN
            RAISE NOTICE 'Table public.% absent; no changes required for it', target;
            CONTINUE;
        END IF;
        EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', target);
        EXECUTE format('REVOKE ALL PRIVILEGES ON TABLE public.%I FROM PUBLIC, anon, authenticated', target);
        -- Los permisos por columna sobreviven a REVOKE a nivel de tabla.
        SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum)
          INTO column_names FROM pg_attribute
          WHERE attrelid = format('public.%I', target)::regclass
            AND attnum > 0 AND NOT attisdropped;
        EXECUTE format('REVOKE ALL PRIVILEGES (%s) ON TABLE public.%I FROM PUBLIC, anon, authenticated', column_names, target);
        sequence_name := pg_get_serial_sequence(format('public.%I', target), 'id');
        IF sequence_name IS NOT NULL THEN
            EXECUTE format('REVOKE ALL PRIVILEGES ON SEQUENCE %s FROM PUBLIC, anon, authenticated', sequence_name);
        END IF;
        EXECUTE format('DROP POLICY IF EXISTS "Enable insert for all users" ON public.%I', target);
        EXECUTE format('DROP POLICY IF EXISTS "Enable read for authenticated users only" ON public.%I', target);
        EXECUTE format('DROP POLICY IF EXISTS closed_registration_forms ON public.%I', target);
        -- Restrictiva: otras políticas permisivas no vuelven a abrir estas tablas.
        EXECUTE format('CREATE POLICY closed_registration_forms ON public.%I AS RESTRICTIVE FOR ALL TO anon, authenticated USING (false) WITH CHECK (false)', target);
    END LOOP;
END
$security$;

-- NOT VALID preserva datos históricos; sí se aplica a nuevas inserciones y actualizaciones.
DO $constraint$
BEGIN
    IF to_regclass('public.volunteers') IS NOT NULL
       AND NOT EXISTS (SELECT 1 FROM pg_constraint
        WHERE conrelid = to_regclass('public.volunteers')
          AND conname = 'volunteers_safe_input') THEN
        ALTER TABLE public.volunteers
            ADD CONSTRAINT volunteers_safe_input CHECK (
    name IS NOT NULL AND char_length(btrim(name)) BETWEEN 1 AND 150
    AND email IS NOT NULL AND char_length(email) <= 254
    AND email ~ '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$'
    AND city IS NOT NULL AND char_length(btrim(city)) BETWEEN 1 AND 100
    AND (phone IS NULL OR char_length(phone) <= 40)
    AND (experience IS NULL OR char_length(experience) <= 100)
    AND (interests IS NULL OR char_length(interests::text) <= 1000)
    AND (availability IS NULL OR char_length(availability) <= 100)
    AND (message IS NULL OR char_length(message) <= 2000)
            ) NOT VALID;
    END IF;
END
$constraint$;
DO $constraint$
BEGIN
    IF to_regclass('public.python_route_registrations') IS NOT NULL
       AND NOT EXISTS (SELECT 1 FROM pg_constraint
        WHERE conrelid = to_regclass('public.python_route_registrations')
          AND conname = 'python_route_registrations_safe_input') THEN
        ALTER TABLE public.python_route_registrations
            ADD CONSTRAINT python_route_registrations_safe_input CHECK (
    name IS NOT NULL AND char_length(btrim(name)) BETWEEN 1 AND 150
    AND email IS NOT NULL AND char_length(email) <= 254
    AND email ~ '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$'
    AND age IS NOT NULL AND age BETWEEN 5 AND 120
    AND province IS NOT NULL AND char_length(btrim(province)) BETWEEN 1 AND 100
    AND group_type IS NOT NULL AND char_length(btrim(group_type)) BETWEEN 1 AND 150
    AND workshop_interest IS NOT NULL AND char_length(btrim(workshop_interest)) BETWEEN 1 AND 150
    AND programming_experience IS NOT NULL AND char_length(btrim(programming_experience)) BETWEEN 1 AND 150
    AND data_protection_accepted IS TRUE
    AND newsletter_consent IS NOT NULL
    AND (phone IS NULL OR char_length(phone) <= 40)
    AND (exact_location IS NULL OR char_length(exact_location) <= 300)
    AND (additional_comments IS NULL OR char_length(additional_comments) <= 2000)
            ) NOT VALID;
    END IF;
END
$constraint$;

COMMIT;