-- Server version: 10.1.44-MariaDB-0+deb9u1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- TABLES
--

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` (
  `id` int(11) NOT NULL,
  `wpid` int(11) NOT NULL,
  `firstname` varchar(100) DEFAULT NULL,
  `lastname` varchar(100) DEFAULT NULL,
  `fullname` varchar(100) AS (CONCAT(firstname,' ',lastname)) PERSISTENT,
  `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;

--
-- Indexes for table `ST_users`
--

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

--
-- Indexes for table `wpDusermeta`
--

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;

--
-- Indexes for table `wpDusers`
--

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;

-- TRIGGER

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);

SELECT NEW.ID, NEW.user_email,
wpDusermeta.meta_value into id_i, email_i, role_i
FROM wpDusers
INNER JOIN wpDusermeta ON wpDusers.ID = wpDusermeta.user_id
WHERE wpDusermeta.meta_key = 'wpDcapabilities'
order by wpDusers.ID desc limit 0 , 1;

INSERT INTO ST_users (wpid, email, wp_role) VALUES (id_i, email_i, role_i);

END
$$
DELIMITER ;

-- INSERT NEW USERS

INSERT INTO wpDusers (ID, user_email)
VALUES(1, email@email.mail)
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.mail)
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.mail)
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 1;

-- Return ST data

SELECT * FROM ST_users