--- data-users

-- Default roles
INSERT INTO user_roles (
  title,
  description,
  admin,
  deleted,
  permissions,
  created_at,
  updated_at)
  SELECT *
  FROM (VALUES
    (
      'Administrator',
      'System administrator',
      1,
      0,
      '',
      sysdatetimeoffset(),
      sysdatetimeoffset()
    ),
    (
      'User',
      'User',
      0,
      0,
      '',
      sysdatetimeoffset(),
      sysdatetimeoffset()
    )) AS t (
       title,
       description,
       admin,
       deleted,
       permissions,
       created_at,
       updated_at)
  WHERE (SELECT COUNT(*)
         FROM users) > 0;

-- Default user roles
UPDATE users
SET role_id = (
  SELECT id
  FROM user_roles
  WHERE title = 'User')
WHERE deleted = 0;

-- Admin user roles
UPDATE users
SET role_id = (
  SELECT id
  FROM user_roles
  WHERE title = 'Administrator')
WHERE exists(
    SELECT *
    FROM user_groups_members m
      JOIN user_groups g ON g.id = m.group_id
    WHERE
      g.admin = 1
      AND m.deleted = 0
      AND m.user_id = users.id);
