myeasycms-v2/apps/web/supabase/tests/database/member-functions.test.sql

begin;

create extension "basejump-supabase_test_helpers" version '0.0.6';

select no_plan();

-- =====================================================
-- Member Management Function Tests
-- Tests PG functions for correctness, auth, atomicity
-- =====================================================

-- Setup: create test users and account
select tests.create_supabase_user('owner', 'owner@test.com');
select tests.create_supabase_user('member_user', 'member@test.com');
select tests.create_supabase_user('outsider', 'outsider@test.com');

select makerkit.set_identifier('owner', 'owner@test.com');
select makerkit.set_identifier('member_user', 'member@test.com');
select makerkit.set_identifier('outsider', 'outsider@test.com');

-- Create a team account owned by 'owner'
set local role service_role;
select public.create_team_account('Test Verein', tests.get_supabase_uid('owner'));

-- Get account ID
select makerkit.authenticate_as('owner');
\set test_account_id '(select id from public.accounts where slug = ''test-verein'' limit 1)'

-- Grant members.write permission to owner
set local role postgres;
insert into public.role_permissions (role, permission)
values ('owner', 'members.write')
on conflict do nothing;

-- -------------------------------------------------------
-- Test: get_next_member_number
-- -------------------------------------------------------
select makerkit.authenticate_as('owner');

select is(
  public.get_next_member_number(:test_account_id),
  '0001',
  'First member number should be 0001'
);

-- Insert a member to test incrementing
set local role service_role;
insert into public.members (account_id, first_name, last_name, member_number, status, entry_date, created_by, updated_by)
values (:test_account_id, 'Max', 'Mustermann', '0001', 'active', current_date,
        tests.get_supabase_uid('owner'), tests.get_supabase_uid('owner'));

select makerkit.authenticate_as('owner');

select is(
  public.get_next_member_number(:test_account_id),
  '0002',
  'Second member number should be 0002'
);

-- -------------------------------------------------------
-- Test: get_member_quick_stats
-- -------------------------------------------------------
select isnt_empty(
  $$ select * from public.get_member_quick_stats((select id from public.accounts where slug = 'test-verein' limit 1)) $$,
  'Quick stats returns data for account with members'
);

-- -------------------------------------------------------
-- Test: check_duplicate_member
-- -------------------------------------------------------
select isnt_empty(
  $$ select * from public.check_duplicate_member(
    (select id from public.accounts where slug = 'test-verein' limit 1),
    'Max', 'Mustermann', null
  ) $$,
  'Duplicate check finds existing member by name'
);

select is_empty(
  $$ select * from public.check_duplicate_member(
    (select id from public.accounts where slug = 'test-verein' limit 1),
    'Nonexistent', 'Person', null
  ) $$,
  'Duplicate check returns empty for non-matching name'
);

-- -------------------------------------------------------
-- Test: approve_application
-- -------------------------------------------------------

-- Create a test application
set local role service_role;
insert into public.membership_applications (
  account_id, first_name, last_name, email, status
) values (
  :test_account_id, 'Anna', 'Bewerberin', 'anna@test.com', 'submitted'
);

select makerkit.authenticate_as('owner');

-- Approve it
select lives_ok(
  $$ select public.approve_application(
    (select id from public.membership_applications where email = 'anna@test.com'),
    tests.get_supabase_uid('owner')
  ) $$,
  'Owner can approve application'
);

-- Verify member was created
select isnt_empty(
  $$ select * from public.members where first_name = 'Anna' and last_name = 'Bewerberin' $$,
  'Approved application creates a member'
);

-- Verify application status changed
select is(
  (select status from public.membership_applications where email = 'anna@test.com'),
  'approved'::public.application_status,
  'Application status is approved'
);

-- -------------------------------------------------------
-- Test: reject_application
-- -------------------------------------------------------
set local role service_role;
insert into public.membership_applications (
  account_id, first_name, last_name, email, status
) values (
  :test_account_id, 'Bob', 'Abgelehnt', 'bob@test.com', 'submitted'
);

select makerkit.authenticate_as('owner');

select lives_ok(
  $$ select public.reject_application(
    (select id from public.membership_applications where email = 'bob@test.com'),
    tests.get_supabase_uid('owner'),
    'Nicht qualifiziert'
  ) $$,
  'Owner can reject application'
);

select is(
  (select status from public.membership_applications where email = 'bob@test.com'),
  'rejected'::public.application_status,
  'Application status is rejected'
);

-- -------------------------------------------------------
-- Test: approve_application — already approved should fail
-- -------------------------------------------------------
-- Verify the re-approval throws with status message
prepare approve_again as select public.approve_application(
  (select id from public.membership_applications where email = 'anna@test.com'),
  tests.get_supabase_uid('owner')
);
select throws_ok(
  'approve_again',
  'P0001',
  'Application is not in a reviewable state (current: approved)',
  'Cannot approve already-approved application'
);

-- -------------------------------------------------------
-- Test: get_member_timeline
-- -------------------------------------------------------
-- The member creation via approve_application should have generated an audit entry
select isnt_empty(
  $$ select * from public.get_member_timeline(
    (select id from public.members where first_name = 'Anna' limit 1),
    1, 50, null
  ) $$,
  'Member timeline has entries after creation'
);

-- -------------------------------------------------------
-- Test: log_member_audit_event
-- -------------------------------------------------------
select makerkit.authenticate_as('owner');

select lives_ok(
  $$ select public.log_member_audit_event(
    (select id from public.members where first_name = 'Max' limit 1),
    (select id from public.accounts where slug = 'test-verein' limit 1),
    'note_added',
    '{"note": "Test note"}'::jsonb,
    '{}'::jsonb
  ) $$,
  'Owner can log audit event for member'
);

-- -------------------------------------------------------
-- Test: outsider cannot access functions
-- -------------------------------------------------------
select makerkit.authenticate_as('outsider');

-- Outsider should get an error when calling get_next_member_number
prepare outsider_member_number as select public.get_next_member_number(
  (select id from public.accounts where slug = 'test-verein' limit 1)
);
select throws_ok(
  'outsider_member_number',
  'P0001',
  null,
  'Outsider cannot call get_next_member_number'
);

select * from finish();

rollback;