CREATE TABLE `wpDusers` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `wpDusermeta` (
  `umeta_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ST_users` (
  `wpid` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `wp_role` varchar(100) NOT NULL,
  `role` varchar(100) AS (MID(`wp_role`, LOCATE('"', `wp_role`)+1, LOCATE('"', MID(`wp_role`, LOCATE('"', `wp_role`)+1 , 50))-1)) PERSISTENT,
  `role_id` int(11) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ST_users`
ADD PRIMARY KEY (`wpid`),
ADD KEY `wpid` (`wpid`);
ALTER TABLE `ST_users`
MODIFY `wpid` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `ST_users`
  MODIFY `wpid` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `wpDusers`
ADD PRIMARY KEY (`ID`),
ADD KEY `user_email` (`user_email`);
ALTER TABLE `wpDusers`
MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `wpDusers`
  MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `wpDusermeta`
ADD PRIMARY KEY (`umeta_id`),
ADD KEY `user_id` (`user_id`),
ADD KEY `meta_key` (`meta_key`(191));
ALTER TABLE `wpDusermeta`
MODIFY `umeta_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `wpDusermeta`
  MODIFY `umeta_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

-- TRIGGER

DROP TRIGGER ST_copy_users_v2;

DELIMITER $$
CREATE TRIGGER `ST_copy_users_v2` AFTER INSERT ON `wpDusers` FOR EACH ROW BEGIN

DECLARE id_i int(11);
DECLARE email_i varchar(100);
DECLARE role_i varchar(100);

INSERT INTO ST_users (wpid, email, role)
SELECT NEW.id, NEW.user_email
     , wpDusermeta.meta_value
  FROM wpDusermeta
 WHERE wpDusermeta.user_id  = NEW.id
   AND wpDusermeta.meta_key = 'wpDcapabilities'
;

END
$$
DELIMITER ;

-- INSERT USERS

INSERT INTO wpdusers (ID, user_email) VALUES (1, 'email@email.com');
INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
VALUES(1, 1, 'wpDcapabilities', '_{_administrator_}_');

INSERT INTO wpDusers (ID, user_email)
VALUES(2, 'email2@email.com');
INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
VALUES(2, 2, 'wpDcapabilities', '_{_student_}_');

INSERT INTO wpDusers (ID, user_email)
VALUES(3, 'email2@email.com');
INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
VALUES(3, 3, 'wpDcapabilities', '_{_pupil_}_');

-- Return Wordpress data

SELECT wpDusers.ID, wpDusers.user_email, wpDusermeta.meta_value
FROM wpDusers
INNER JOIN wpDusermeta ON wpDusers.ID = wpDusermeta.user_id
WHERE wpDusermeta.meta_key = 'wpDcapabilities'
order by wpDusers.ID desc limit 10;

-- Return ST data

SELECT * FROM ST_users