init.sql

-- Waiting List Database Initialization Script
-- Run this SQL in your Supabase SQL Editor to set up the database

-- ============================================
-- 1. Create waitlist_entries table
-- ============================================
CREATE TABLE IF NOT EXISTS public.waitlist_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    queue_position INTEGER NOT NULL,
    joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT waitlist_entries_user_unique UNIQUE (user_id)
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_waitlist_entries_position ON public.waitlist_entries(queue_position);
CREATE INDEX IF NOT EXISTS idx_waitlist_entries_joined_at ON public.waitlist_entries(joined_at);

-- Add comments
COMMENT ON TABLE public.waitlist_entries IS 'Waitlist entries with Supabase Realtime support';
COMMENT ON COLUMN public.waitlist_entries.queue_position IS 'Position in the waitlist queue';
COMMENT ON COLUMN public.waitlist_entries.joined_at IS 'When the user joined the waitlist';

-- ============================================
-- 2. Enable Row Level Security (RLS)
-- ============================================
ALTER TABLE public.waitlist_entries ENABLE ROW LEVEL SECURITY;

-- Authenticated users can view all entries (for realtime stats)
CREATE POLICY "Authenticated users can view all entries"
    ON public.waitlist_entries
    FOR SELECT
    TO authenticated
    USING (true);

-- Users can insert their own entry
CREATE POLICY "Users can insert own entry"
    ON public.waitlist_entries
    FOR INSERT
    TO authenticated
    WITH CHECK (auth.uid() = user_id);

-- Anonymous users can view entries (for public stats page)
CREATE POLICY "Anyone can view entries for stats"
    ON public.waitlist_entries
    FOR SELECT
    TO anon
    USING (true);

-- ============================================
-- 3. Enable Supabase Realtime
-- ============================================
ALTER PUBLICATION supabase_realtime ADD TABLE public.waitlist_entries;

-- ============================================
-- 4. Create helper functions
-- ============================================

-- Function: Join waitlist (atomic operation)
CREATE OR REPLACE FUNCTION public.join_waitlist()
RETURNS TABLE(queue_pos INTEGER, total_count BIGINT, already_joined BOOLEAN)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    new_position INTEGER;
    existing_pos INTEGER;
    total BIGINT;
BEGIN
    -- Check if user already joined
    SELECT we.queue_position INTO existing_pos
    FROM public.waitlist_entries we
    WHERE we.user_id = auth.uid();

    IF existing_pos IS NOT NULL THEN
        SELECT COUNT(*) INTO total FROM public.waitlist_entries;
        RETURN QUERY SELECT existing_pos, total, true;
        RETURN;
    END IF;

    -- Get next position with lock to prevent race conditions
    LOCK TABLE public.waitlist_entries IN SHARE ROW EXCLUSIVE MODE;

    SELECT COALESCE(MAX(we.queue_position), 0) + 1 INTO new_position
    FROM public.waitlist_entries we;

    -- Insert new entry
    INSERT INTO public.waitlist_entries (user_id, queue_position)
    VALUES (auth.uid(), new_position);

    -- Get total count
    SELECT COUNT(*) INTO total FROM public.waitlist_entries;

    RETURN QUERY SELECT new_position, total, false;
END;
$$;

-- Function: Get current user's waitlist status
CREATE OR REPLACE FUNCTION public.get_my_waitlist_status()
RETURNS TABLE(is_joined BOOLEAN, queue_pos INTEGER, total_count BIGINT)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    user_pos INTEGER;
    total BIGINT;
BEGIN
    SELECT we.queue_position INTO user_pos
    FROM public.waitlist_entries we
    WHERE we.user_id = auth.uid();

    SELECT COUNT(*) INTO total FROM public.waitlist_entries;

    IF user_pos IS NOT NULL THEN
        RETURN QUERY SELECT true, user_pos, total;
    ELSE
        RETURN QUERY SELECT false, NULL::INTEGER, total;
    END IF;
END;
$$;

-- Function: Mask email for privacy
CREATE OR REPLACE FUNCTION public.mask_email(email TEXT)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    at_pos INTEGER;
    local_part TEXT;
    domain_part TEXT;
BEGIN
    at_pos := POSITION('@' IN email);
    IF at_pos = 0 THEN
        RETURN '***';
    END IF;

    local_part := SUBSTRING(email FROM 1 FOR at_pos - 1);
    domain_part := SUBSTRING(email FROM at_pos);

    IF LENGTH(local_part) <= 2 THEN
        RETURN SUBSTRING(local_part FROM 1 FOR 1) || '***' || domain_part;
    ELSE
        RETURN SUBSTRING(local_part FROM 1 FOR 2) || '***' || domain_part;
    END IF;
END;
$$;

-- Function: Get waitlist stats (for authenticated users)
CREATE OR REPLACE FUNCTION public.get_waitlist_stats()
RETURNS TABLE(
    total_entries BIGINT,
    today_entries BIGINT,
    this_week_entries BIGINT,
    this_month_entries BIGINT
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)::BIGINT,
        COUNT(*) FILTER (WHERE joined_at >= CURRENT_DATE)::BIGINT,
        COUNT(*) FILTER (WHERE joined_at >= CURRENT_DATE - INTERVAL '7 days')::BIGINT,
        COUNT(*) FILTER (WHERE joined_at >= CURRENT_DATE - INTERVAL '30 days')::BIGINT
    FROM public.waitlist_entries;
END;
$$;

-- Function: Get waitlist stats (for public/anonymous access)
CREATE OR REPLACE FUNCTION public.get_waitlist_stats_public()
RETURNS TABLE(
    total_entries BIGINT,
    today_entries BIGINT,
    this_week_entries BIGINT,
    this_month_entries BIGINT
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)::BIGINT,
        COUNT(*) FILTER (WHERE joined_at >= CURRENT_DATE)::BIGINT,
        COUNT(*) FILTER (WHERE joined_at >= CURRENT_DATE - INTERVAL '7 days')::BIGINT,
        COUNT(*) FILTER (WHERE joined_at >= CURRENT_DATE - INTERVAL '30 days')::BIGINT
    FROM public.waitlist_entries;
END;
$$;

-- Function: Get waitlist entries for admin (with full email)
CREATE OR REPLACE FUNCTION public.get_waitlist_entries_for_admin(
    page_size INTEGER DEFAULT 20,
    page_offset INTEGER DEFAULT 0
)
RETURNS TABLE(
    entry_id UUID,
    user_email TEXT,
    queue_pos INTEGER,
    joined_at_time TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN QUERY
    SELECT
        we.id,
        u.email,
        we.queue_position,
        we.joined_at
    FROM public.waitlist_entries we
    JOIN auth.users u ON we.user_id = u.id
    ORDER BY we.queue_position ASC
    LIMIT page_size
    OFFSET page_offset;
END;
$$;

-- Function: Get waitlist entries for public (with masked email)
CREATE OR REPLACE FUNCTION public.get_waitlist_entries_public(
    page_size INTEGER DEFAULT 20,
    page_offset INTEGER DEFAULT 0
)
RETURNS TABLE(
    queue_pos INTEGER,
    masked_email TEXT,
    joined_at_time TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN QUERY
    SELECT
        we.queue_position,
        public.mask_email(u.email),
        we.joined_at
    FROM public.waitlist_entries we
    JOIN auth.users u ON we.user_id = u.id
    ORDER BY we.queue_position ASC
    LIMIT page_size
    OFFSET page_offset;
END;
$$;

-- ============================================
-- Done! Your database is ready.
-- ============================================