SQL:Examples:IsAdmin

-- Create system admins table
CREATE TABLE IF NOT EXISTS public.admins (
    id SERIAL PRIMARY KEY,
    user_id UUID UNIQUE NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create index on is_admin for filtering
CREATE INDEX IF NOT EXISTS idx_admins_user_id ON public.admins(user_id);

-- Enable RLS
ALTER TABLE public.admins ENABLE ROW LEVEL SECURITY;

-- RLS Policy: Only admins can see admin records
CREATE POLICY "Admin can view admin records" ON public.admins
    FOR ALL USING (
        EXISTS (
            SELECT 1 FROM public.admins
            WHERE user_id = auth.uid()
        )
    );

-- Function to check if current user is admin
CREATE OR REPLACE FUNCTION public.is_admin()
    RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.admins
        WHERE user_id = auth.uid()
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Test suite for admins table migration
BEGIN;

SELECT plan(15);

-- Test table existence
SELECT has_table('public', 'admins', 'admins table should exist');

-- Test columns
SELECT has_column('public', 'admins', 'id', 'admins should have id column');
SELECT has_column('public', 'admins', 'user_id', 'admins should have user_id column');
SELECT has_column('public', 'admins', 'created_at', 'admins should have created_at column');
SELECT has_column('public', 'admins', 'updated_at', 'admins should have updated_at column');

-- Test column types
SELECT col_type_is('public', 'admins', 'id', 'integer', 'id should be integer (serial)');
SELECT col_type_is('public', 'admins', 'user_id', 'uuid', 'user_id should be uuid');
SELECT col_type_is('public', 'admins', 'created_at', 'timestamp with time zone', 'created_at should be timestamp with time zone');
SELECT col_type_is('public', 'admins', 'updated_at', 'timestamp with time zone', 'updated_at should be timestamp with time zone');

-- Test constraints
SELECT col_not_null('public', 'admins', 'user_id', 'user_id should be NOT NULL');
SELECT col_is_unique('public', 'admins', 'user_id', 'user_id should be UNIQUE');

-- Test primary key
SELECT has_pk('public', 'admins', 'admins should have a primary key');

-- Test index
SELECT has_index('public', 'admins', 'idx_admins_user_id', 'idx_admins_user_id index should exist');

-- Test RLS
SELECT results_eq(
    'SELECT relrowsecurity FROM pg_class WHERE relname = ''admins'' AND relnamespace = ''public''::regnamespace',
    ARRAY[true],
    'RLS should be enabled on admins table'
);

-- Test function existence
SELECT has_function('public', 'is_admin', 'is_admin() function should exist');

SELECT * FROM finish();
ROLLBACK;