-- +goose Up
CREATE TABLE IF NOT EXISTS user_roles (
  id BINARY(16) NOT NULL,
  user_id BINARY(16) NOT NULL,
  role_id BINARY(16) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_by BINARY(16) NULL,
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  updated_by BINARY(16) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY ux_user_role (user_id, role_id),
  KEY idx_user_roles_user_id (user_id),
  KEY idx_user_roles_role_id (role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO user_roles (id, user_id, role_id, created_at, updated_at)
SELECT
  UNHEX(REPLACE(UUID(), '-', '')),
  users.id,
  users.role_id,
  CURRENT_TIMESTAMP(6),
  CURRENT_TIMESTAMP(6)
FROM users
WHERE users.role_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM user_roles
    WHERE user_roles.user_id = users.id
      AND user_roles.role_id = users.role_id
  );

-- +goose Down
DROP TABLE IF EXISTS user_roles;