-- Script to add missing users to user_org_memberships table
-- Organization: Biosecurity Authority of Fiji
-- Org ID: 0199b208-8943-7ca3-ba31-781efbe25058

-- Insert all users who don't have an org membership
INSERT INTO user_org_memberships (
    membership_id,
    user_id,
    org_id,
    role,
    options,
    joined_at,
    isDeleted,
    updatedAt
)
SELECT 
    uuid_generate_v7(),
    u.user_id,
    '0199b208-8943-7ca3-ba31-781efbe25058'::uuid,
    'employee',
    NULL,
    NOW(),
    FALSE,
    NOW()
FROM users u
WHERE u.isDeleted = FALSE
  AND NOT EXISTS (
    SELECT 1 
    FROM user_org_memberships uom 
    WHERE uom.user_id = u.user_id 
      AND uom.isDeleted = FALSE
  );

-- Verify the results
SELECT 
    'After Insert' as status,
    COUNT(*) as total_memberships 
FROM user_org_memberships 
WHERE isDeleted = FALSE;

-- Show all users with their org membership
SELECT 
    u.user_id,
    u.full_name,
    uom.org_id,
    uom.role
FROM users u
INNER JOIN user_org_memberships uom ON u.user_id = uom.user_id
WHERE u.isDeleted = FALSE 
  AND uom.isDeleted = FALSE
ORDER BY u.full_name;
