-- Populate employee_id for biometric device integration
-- Date: 2025-11-18
-- Description: Assign numeric employee IDs to users for biometric device matching

-- Update Kevin Chand to employee_id = '1'
UPDATE user_org_memberships uom
SET employee_id = '1'
FROM user_emails ue
WHERE uom.user_id = ue.user_id
  AND ue.email = 'kevin@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;

-- Update David Thompson to employee_id = '2'
UPDATE user_org_memberships uom
SET employee_id = '2'
FROM user_emails ue
WHERE uom.user_id = ue.user_id
  AND ue.email = 'david.thompson@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;

-- Update Lisa Anderson to employee_id = '3'
UPDATE user_org_memberships uom
SET employee_id = '3'
FROM user_emails ue
WHERE uom.user_id = ue.user_id
  AND ue.email = 'lisa.anderson@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;

-- Update Michael Chen (HR) to employee_id = '4'
UPDATE user_org_memberships uom
SET employee_id = '4'
FROM user_emails ue
WHERE uom.user_id = ue.user_id
  AND ue.email = 'michael.chen@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;

-- Update Bhaskaran (HR/Admin) to employee_id = '5'
UPDATE user_org_memberships uom
SET employee_id = '5'
FROM user_emails ue
WHERE uom.user_id = ue.user_id
  AND ue.email = 'bhaskaran@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;

-- Verify the updates
SELECT 
    u.full_name,
    ue.email,
    uom.employee_id,
    uom.role
FROM user_org_memberships uom
JOIN users u ON u.user_id = uom.user_id
JOIN user_emails ue ON ue.user_id = u.user_id
WHERE uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
  AND uom.employee_id IS NOT NULL
ORDER BY uom.employee_id::int;
