-- Test Users for MSSQL Development
-- Note: Groups must exist first (created by V9__Groups.sql)

-- Insert test users (using MERGE for UPSERT)
MERGE INTO sys_user AS target
USING (VALUES
    ('USR001', 'john.doe@company.com', 'John', 'Doe', 1),
    ('USR002', 'sarah.smith@company.com', 'Sarah', 'Smith', 1),
    ('USR003', 'mike.johnson@company.com', 'Mike', 'Johnson', 1),
    ('USR004', 'anna.mueller@company.com', 'Anna', 'Mueller', 1),
    ('USR005', 'david.chen@company.com', 'David', 'Chen', 1)
) AS source (workday_id, email, firstname, lastname, is_active)
ON target.email = source.email
WHEN MATCHED THEN
    UPDATE SET
        workday_id = source.workday_id,
        firstname = source.firstname,
        lastname = source.lastname,
        is_active = source.is_active
WHEN NOT MATCHED THEN
    INSERT (workday_id, email, firstname, lastname, is_active)
    VALUES (source.workday_id, source.email, source.firstname, source.lastname, source.is_active);

-- Assign users to groups
-- John Doe -> super
INSERT INTO sys_user_group_mapping (user_id, group_id)
SELECT u.id, g.id
FROM sys_user u, sys_group g
WHERE u.email = 'john.doe@company.com'
  AND g.group_name = 'super'
  AND NOT EXISTS (
    SELECT 1 FROM sys_user_group_mapping
    WHERE user_id = u.id AND group_id = g.id
  );

-- Sarah Smith -> basic
INSERT INTO sys_user_group_mapping (user_id, group_id)
SELECT u.id, g.id
FROM sys_user u, sys_group g
WHERE u.email = 'sarah.smith@company.com'
  AND g.group_name = 'basic'
  AND NOT EXISTS (
    SELECT 1 FROM sys_user_group_mapping
    WHERE user_id = u.id AND group_id = g.id
  );

-- Mike Johnson -> calculation
INSERT INTO sys_user_group_mapping (user_id, group_id)
SELECT u.id, g.id
FROM sys_user u, sys_group g
WHERE u.email = 'mike.johnson@company.com'
  AND g.group_name = 'calculation'
  AND NOT EXISTS (
    SELECT 1 FROM sys_user_group_mapping
    WHERE user_id = u.id AND group_id = g.id
  );

-- Anna Mueller -> freight
INSERT INTO sys_user_group_mapping (user_id, group_id)
SELECT u.id, g.id
FROM sys_user u, sys_group g
WHERE u.email = 'anna.mueller@company.com'
  AND g.group_name = 'freight'
  AND NOT EXISTS (
    SELECT 1 FROM sys_user_group_mapping
    WHERE user_id = u.id AND group_id = g.id
  );

-- David Chen -> packaging
INSERT INTO sys_user_group_mapping (user_id, group_id)
SELECT u.id, g.id
FROM sys_user u, sys_group g
WHERE u.email = 'david.chen@company.com'
  AND g.group_name = 'packaging'
  AND NOT EXISTS (
    SELECT 1 FROM sys_user_group_mapping
    WHERE user_id = u.id AND group_id = g.id
  );

-- Create user nodes
-- John Doe's supplier node
INSERT INTO sys_user_node (user_id, country_id, name, address, geo_lat, geo_lng, is_deprecated)
SELECT u.id, c.id, 'My Supplier 1', 'My Road 1, 1234 MyCity', 24.489, 118.1478, 0
FROM sys_user u, country c
WHERE u.email = 'john.doe@company.com'
  AND c.iso_code = 'CN'
  AND NOT EXISTS (
    SELECT 1 FROM sys_user_node
    WHERE user_id = u.id AND name = 'My Supplier 1'
  );

-- Sarah Smith's supplier node
INSERT INTO sys_user_node (user_id, country_id, name, address, geo_lat, geo_lng, is_deprecated)
SELECT u.id, c.id, 'My Supplier 2', 'My Road 2, 1234 MyCity', 24.489, 118.1478, 0
FROM sys_user u, country c
WHERE u.email = 'sarah.smith@company.com'
  AND c.iso_code = 'CN'
  AND NOT EXISTS (
    SELECT 1 FROM sys_user_node
    WHERE user_id = u.id AND name = 'My Supplier 2'
  );

-- Verify the data
SELECT 'Users created:' AS info;
SELECT u.id, u.workday_id, u.email, u.firstname, u.lastname,
       g.group_name
FROM sys_user u
LEFT JOIN sys_user_group_mapping ugm ON u.id = ugm.user_id
LEFT JOIN sys_group g ON ugm.group_id = g.id
WHERE u.email IN (
    'john.doe@company.com',
    'sarah.smith@company.com',
    'mike.johnson@company.com',
    'anna.mueller@company.com',
    'david.chen@company.com'
)
ORDER BY u.email;
