xtablo-source/supabase/tests/database/03_rls_policies_notes.test.sql

begin;
select plan(36); -- Total number of tests (reduced - removed 2 DELETE policy tests that don't exist)

-- ============================================================================
-- RLS Enabled Tests
-- ============================================================================

SELECT is(
    (SELECT relrowsecurity FROM pg_class WHERE relname = 'notes' AND relnamespace = 'public'::regnamespace),
    true,
    'RLS should be enabled on notes table'
);

SELECT is(
    (SELECT relrowsecurity FROM pg_class WHERE relname = 'shared_notes' AND relnamespace = 'public'::regnamespace),
    true,
    'RLS should be enabled on shared_notes table'
);

SELECT is(
    (SELECT relrowsecurity FROM pg_class WHERE relname = 'note_access' AND relnamespace = 'public'::regnamespace),
    true,
    'RLS should be enabled on note_access table'
);

-- ============================================================================
-- Notes Table RLS Policies
-- ============================================================================

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can view their own notes and public notes') > 0,
    'Policy for viewing own and public notes should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can insert their own notes') > 0,
    'Policy for inserting own notes should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can update their own notes') > 0,
    'Policy for updating own notes should exist'
);

-- Note: There is only a soft delete policy (FOR UPDATE), no hard DELETE policy
SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can delete their own notes (soft)') > 0,
    'Policy for soft deleting own notes should exist'
);

-- Test policy commands
SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can view their own notes and public notes' LIMIT 1),
    'SELECT',
    'View notes policy should be for SELECT'
);

SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can insert their own notes' LIMIT 1),
    'INSERT',
    'Insert notes policy should be for INSERT'
);

SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can update their own notes' LIMIT 1),
    'UPDATE',
    'Update notes policy should be for UPDATE'
);

-- Note: Soft delete policy is FOR UPDATE, not DELETE
SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can delete their own notes (soft)' LIMIT 1),
    'UPDATE',
    'Soft delete notes policy should be for UPDATE'
);

-- Test policy roles include both authenticated and anon for viewing
SELECT ok(
    (SELECT COALESCE('authenticated' = ANY(roles), false) FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can view their own notes and public notes' LIMIT 1),
    'View notes policy should include authenticated role'
);

SELECT ok(
    (SELECT COALESCE('anon' = ANY(roles), false) FROM pg_policies WHERE tablename = 'notes' AND policyname = 'Users can view their own notes and public notes' LIMIT 1),
    'View notes policy should include anon role for public notes'
);

-- ============================================================================
-- Shared Notes Table RLS Policies
-- ============================================================================

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Users can view their own shared notes') > 0,
    'Policy for viewing own shared notes should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Anyone can view public notes') > 0,
    'Policy for viewing public notes should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Users can insert their own shared notes') > 0,
    'Policy for inserting shared notes should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Users can update their own shared notes') > 0,
    'Policy for updating shared notes should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Users can delete their own shared notes') > 0,
    'Policy for deleting shared notes should exist'
);

-- Test policy commands
SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Users can view their own shared notes' LIMIT 1),
    'SELECT',
    'View own shared notes policy should be for SELECT'
);

SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Anyone can view public notes' LIMIT 1),
    'SELECT',
    'View public notes policy should be for SELECT'
);

-- Test that public notes policy applies to both authenticated and anon
SELECT ok(
    (SELECT COALESCE('authenticated' = ANY(roles), false) FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Anyone can view public notes' LIMIT 1),
    'Public notes policy should include authenticated role'
);

SELECT ok(
    (SELECT COALESCE('anon' = ANY(roles), false) FROM pg_policies WHERE tablename = 'shared_notes' AND policyname = 'Anyone can view public notes' LIMIT 1),
    'Public notes policy should include anon role'
);

-- ============================================================================
-- Note Access Table RLS Policies
-- ============================================================================

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'note_access' AND policyname = 'Users can view their own note access') > 0,
    'Policy for viewing own note access should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'note_access' AND policyname = 'Users can view notes shared with their tablos') > 0,
    'Policy for viewing shared notes should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'note_access' AND policyname = 'Users can insert their own note access') > 0,
    'Policy for inserting note access should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'note_access' AND policyname = 'Users can update their own note access') > 0,
    'Policy for updating note access should exist'
);

SELECT ok(
    (SELECT COUNT(*) FROM pg_policies WHERE tablename = 'note_access' AND policyname = 'Users can delete their own note access') > 0,
    'Policy for deleting note access should exist'
);

-- Test policy commands
SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'note_access' AND policyname = 'Users can view their own note access' LIMIT 1),
    'SELECT',
    'View own note access policy should be for SELECT'
);

SELECT is(
    (SELECT cmd FROM pg_policies WHERE tablename = 'note_access' AND policyname = 'Users can insert their own note access' LIMIT 1),
    'INSERT',
    'Insert note access policy should be for INSERT'
);

-- ============================================================================
-- Notes Behavior Tests with Mock Data
-- ============================================================================

-- Create test users and notes
DO $$
DECLARE
    user1_id uuid := gen_random_uuid();
    user2_id uuid := gen_random_uuid();
    note1_id text := 'test_note_' || gen_random_uuid()::text;
    note2_id text := 'test_note_' || gen_random_uuid()::text;
    public_note_id text := 'public_note_' || gen_random_uuid()::text;
BEGIN
    -- Insert test users
    INSERT INTO auth.users (id, instance_id, aud, role, email, encrypted_password, email_confirmed_at, created_at, updated_at)
    VALUES 
        (user1_id, '00000000-0000-0000-0000-000000000000', 'authenticated', 'authenticated', 'noteuser1_' || user1_id::text || '@test.com', 'encrypted', now(), now(), now()),
        (user2_id, '00000000-0000-0000-0000-000000000000', 'authenticated', 'authenticated', 'noteuser2_' || user2_id::text || '@test.com', 'encrypted', now(), now(), now())
    ON CONFLICT DO NOTHING;
    
    -- Insert test profiles
    INSERT INTO public.profiles (id, email, first_name, last_name, short_user_id)
    VALUES 
        (user1_id, 'noteuser1_' || user1_id::text || '@test.com', 'Note User', 'One', substring(user1_id::text from 1 for 8)),
        (user2_id, 'noteuser2_' || user2_id::text || '@test.com', 'Note User', 'Two', substring(user2_id::text from 1 for 8))
    ON CONFLICT DO NOTHING;
    
    -- Insert test notes
    INSERT INTO public.notes (id, title, content, user_id)
    VALUES 
        (note1_id, 'User 1 Private Note', 'This is a private note', user1_id),
        (note2_id, 'User 2 Private Note', 'This is another private note', user2_id),
        (public_note_id, 'Public Note', 'This is a public note', user1_id);
    
    -- Make one note public
    INSERT INTO public.shared_notes (note_id, user_id, is_public)
    VALUES (public_note_id, user1_id, true);
    
    -- Store test IDs
    PERFORM set_config('test.note_user1_id', user1_id::text, true);
    PERFORM set_config('test.note_user2_id', user2_id::text, true);
    PERFORM set_config('test.note1_id', note1_id, true);
    PERFORM set_config('test.public_note_id', public_note_id, true);
END $$;

-- Test: Verify notes were created
SELECT is(
    (SELECT count(*)::integer FROM public.notes WHERE id = current_setting('test.note1_id')),
    1,
    'User 1 private note should be created'
);

SELECT is(
    (SELECT count(*)::integer FROM public.notes WHERE id = current_setting('test.public_note_id')),
    1,
    'Public note should be created'
);

-- Test: Verify shared_notes entry for public note
SELECT is(
    (SELECT is_public FROM public.shared_notes WHERE note_id = current_setting('test.public_note_id') LIMIT 1),
    true,
    'Public note should be marked as public in shared_notes'
);

-- ============================================================================
-- Foreign Key Constraints Tests
-- ============================================================================

SELECT has_fk('public', 'shared_notes', 'shared_notes should have foreign key constraints');
SELECT has_fk('public', 'note_access', 'note_access should have foreign key constraints');

-- Test that shared_notes.note_id references notes.id
SELECT fk_ok(
    'public', 'shared_notes', 'note_id',
    'public', 'notes', 'id',
    'shared_notes.note_id should reference notes.id'
);

-- Test that note_access.note_id references notes.id
SELECT fk_ok(
    'public', 'note_access', 'note_id',
    'public', 'notes', 'id',
    'note_access.note_id should reference notes.id'
);

select * from finish();
rollback;