-- MySQL Workbench Forward Engineering

SET sql_log_bin = OFF;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema log
-- -----------------------------------------------------
-- Schema for Master and Slaves, replicated to Master
DROP SCHEMA IF EXISTS `log` ;

-- -----------------------------------------------------
-- Schema log
--
-- Schema for Master and Slaves, replicated to Master
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `log` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `log` ;

-- -----------------------------------------------------
-- Table `log`.`audit_trail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `log`.`audit_trail` ;

CREATE TABLE IF NOT EXISTS `log`.`audit_trail` (
  `schema` VARCHAR(64) NOT NULL,
  `table` VARCHAR(64) NOT NULL,
  `column` VARCHAR(64) NOT NULL,
  `action` ENUM('insert', 'update', 'delete') NOT NULL DEFAULT 'insert',
  `row_id` LONGTEXT NOT NULL,
  `old_value` LONGBLOB NULL,
  `new_value` LONGBLOB NULL,
  `external_id` TEXT NULL DEFAULT NULL,
  `server_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `server_uuid` VARBINARY(16) NOT NULL DEFAULT 0x0,
  `connection_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `user` CHAR(128) NOT NULL DEFAULT '',
  `created` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6))
ENGINE = ARCHIVE;


-- -----------------------------------------------------
-- Table `log`.`log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `log`.`log` ;

CREATE TABLE IF NOT EXISTS `log`.`log` (
  `message` LONGBLOB NULL DEFAULT NULL,
  `severity` ENUM('error', 'exception', 'warning', 'info', 'verbose') NOT NULL DEFAULT 'error',
  `owner` TEXT NULL DEFAULT NULL,
  `external_id` TEXT NULL DEFAULT NULL,
  `server_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `server_uuid` VARBINARY(16) NOT NULL DEFAULT 0x0,
  `connection_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `user` CHAR(128) NOT NULL DEFAULT '',
  `created` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6))
ENGINE = ARCHIVE;

USE `log` ;

-- -----------------------------------------------------
-- Placeholder table for view `log`.`v_log`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `log`.`v_log` (`message` INT, `severity` INT, `owner` INT, `external_id` INT, `server_id` INT, `server_uuid` INT, `connection_id` INT, `user` INT, `created` INT);

-- -----------------------------------------------------
-- Placeholder table for view `log`.`v_audit_trail`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `log`.`v_audit_trail` (`schema` INT, `table` INT, `column` INT, `sequence` INT, `primary` INT, `action` INT, `row_id` INT, `old_value` INT, `new_value` INT, `external_id` INT, `server_id` INT, `server_uuid` INT, `connection_id` INT, `user` INT, `created` INT);

-- -----------------------------------------------------
-- Placeholder table for view `log`.`v_schema`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `log`.`v_schema` (`name` INT);

-- -----------------------------------------------------
-- Placeholder table for view `log`.`v_table`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `log`.`v_table` (`schema` INT, `name` INT, `engine` INT, `collation` INT, `auto_increment` INT, `desc_txt` INT, `created` INT, `updated` INT);

-- -----------------------------------------------------
-- Placeholder table for view `log`.`v_column`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `log`.`v_column` (`schema` INT, `table` INT, `name` INT, `sequence` INT, `primary` INT, `nullable` INT, `type` INT, `default` INT, `desc_txt` INT);

-- -----------------------------------------------------
-- procedure auditTrail
-- -----------------------------------------------------

USE `log`;
DROP procedure IF EXISTS `log`.`auditTrail`;

DELIMITER $$
USE `log`$$
CREATE DEFINER = CURRENT_USER
      PROCEDURE `log`.`auditTrail`(IN _schema		varchar(64) CHARSET utf8mb4,
                                   IN _table		varchar(64) CHARSET utf8mb4,
                                   IN _column		varchar(64) CHARSET utf8mb4,
                                   IN _action		enum('insert','update','delete') CHARSET utf8mb4,
                                   IN _rowId		longtext CHARSET utf8mb4,
                                   IN _old			longblob,
                                   IN _new			longblob,
                                   IN _externalId	text CHARSET utf8mb4)
   LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER
BEGIN
   DECLARE EXIT HANDLER FOR NOT FOUND, SQLSTATE '02000', SQLSTATE '23000' BEGIN END;

   INSERT IGNORE INTO `log`.`audit_trail` (`schema`,
										   `table`,
										   `column`,
										   `action`,
										   `row_id`,
										   `old_value`,
										   `new_value`,
										   `external_id`)
   VALUES (_schema,
		   _table,
		   _column,
		   _action,
		   _rowId,
		   _old,
		   _new,
		   _externalId);
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure writeLog
-- -----------------------------------------------------

USE `log`;
DROP procedure IF EXISTS `log`.`writeLog`;

DELIMITER $$
USE `log`$$
CREATE DEFINER = CURRENT_USER
	  PROCEDURE `log`.`writeLog`(IN _message	longblob,
								 IN _severity	enum('error','exception','warning','info','verbose') CHARSET utf8mb4,
								 IN _owner		text CHARSET utf8mb4,
								 IN _externalId	text CHARSET utf8mb4)
   LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER
BEGIN
   INSERT IGNORE
	 INTO `log`.`log` (`message`,
					   `severity`,
					   `owner`,
					   `external_id`)
   VALUES (_message,
		   _severity,
		   _owner,
		   _externalId);
END$$

DELIMITER ;

-- -----------------------------------------------------
-- View `log`.`v_log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `log`.`v_log`;
DROP VIEW IF EXISTS `log`.`v_log` ;
USE `log`;
CREATE  OR REPLACE ALGORITHM = UNDEFINED
       DEFINER = CURRENT_USER
       SQL SECURITY DEFINER
VIEW `log`.`v_log`
AS
   SELECT convert(ifnull(`message`, '') USING utf8mb4) COLLATE utf8mb4_0900_ai_ci AS `message`,
          `severity`,
		  `owner`,
		  `external_id`,
		  `server_id`,
		  convert(BIN_TO_UUID(`server_uuid`, TRUE) USING utf8mb4) COLLATE utf8mb4_0900_ai_ci AS `server_uuid`,
          `connection_id`,
          `user`,
          `created`
     FROM `log`.`log`;

-- -----------------------------------------------------
-- View `log`.`v_audit_trail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `log`.`v_audit_trail`;
DROP VIEW IF EXISTS `log`.`v_audit_trail` ;
USE `log`;
CREATE  OR REPLACE ALGORITHM = UNDEFINED
       DEFINER = CURRENT_USER
       SQL SECURITY DEFINER
VIEW `log`.`v_audit_trail`
AS
   SELECT `audit_trail`.`schema` AS `schema`,
		  `audit_trail`.`table` AS `table`,
		  `audit_trail`.`column` AS `column`,
		  `v_column`.`sequence` AS `sequence`,
		  `v_column`.`primary` AS `primary`,
		  `audit_trail`.`action` AS `action`,
		  `audit_trail`.`row_id` AS `row_id`,
		  if(`v_column`.`type` NOT LIKE '%blob%',
		     convert(`audit_trail`.`old_value` USING utf8mb4) COLLATE utf8mb4_0900_ai_ci,
			 `audit_trail`.`old_value`) AS `old_value`,
		  if(`v_column`.`type` NOT LIKE '%blob%',
		     convert(`audit_trail`.`new_value` USING utf8mb4) COLLATE utf8mb4_0900_ai_ci,
			 `audit_trail`.`new_value`) AS `new_value`,
		  `audit_trail`.`external_id` AS `external_id`,
		  `audit_trail`.`server_id` AS `server_id`,
		  convert(BIN_TO_UUID(`audit_trail`.`server_uuid`, TRUE) USING utf8mb4) COLLATE utf8mb4_0900_ai_ci AS `server_uuid`,
		  `audit_trail`.`connection_id` AS `connection_id`,
		  `audit_trail`.`user` AS `user`,
		  `audit_trail`.`created` AS `created`
	 FROM `log`.`audit_trail`
		JOIN `log`.`v_column`
		  ON `v_column`.`schema` <=> `audit_trail`.`schema`
		 AND `v_column`.`table` <=> `audit_trail`.`table`
		 AND `v_column`.`name` <=> `audit_trail`.`column`;

-- -----------------------------------------------------
-- View `log`.`v_schema`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `log`.`v_schema`;
DROP VIEW IF EXISTS `log`.`v_schema` ;
USE `log`;
CREATE  OR REPLACE ALGORITHM = UNDEFINED
       DEFINER = CURRENT_USER
       SQL SECURITY DEFINER
VIEW `log`.`v_schema`
AS
   SELECT `SCHEMA_NAME` AS `name`
	 FROM `information_schema`.`SCHEMATA`
	WHERE lower(`SCHEMA_NAME`) NOT IN ('information_schema',
									   'log',
									   'mysql',
									   'performance_schema',
									   'phpmyadmin',
									   'sys')
   ORDER BY `name` ASC;

-- -----------------------------------------------------
-- View `log`.`v_table`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `log`.`v_table`;
DROP VIEW IF EXISTS `log`.`v_table` ;
USE `log`;
CREATE  OR REPLACE ALGORITHM = UNDEFINED
       DEFINER = CURRENT_USER
       SQL SECURITY DEFINER
VIEW `log`.`v_table`
AS
   SELECT  `TABLES`.`TABLE_SCHEMA` AS `schema`,
		   `TABLES`.`TABLE_NAME` AS `name`,
		   `TABLES`.`ENGINE` AS `engine`,
		   `TABLES`.`TABLE_COLLATION` AS `collation`,
		   `TABLES`.`AUTO_INCREMENT` AS `auto_increment`,
		   `TABLES`.`TABLE_COMMENT` AS `desc_txt`,
		   `TABLES`.`CREATE_TIME` AS `created`,
		   ifnull(`TABLES`.`UPDATE_TIME`, `TABLES`.`CREATE_TIME`) AS `updated`
	 FROM `information_schema`.`TABLES`
		JOIN `information_schema`.`ENGINES`
		  ON `ENGINES`.`ENGINE` <=> `TABLES`.`ENGINE`
		 AND lower(`ENGINES`.`SUPPORT`) <> 'no'
		JOIN `log`.`v_schema`
	      ON `v_schema`.`name` <=> `TABLES`.`TABLE_SCHEMA`
	WHERE lower(`TABLES`.`TABLE_TYPE`) NOT LIKE '%view%'
   ORDER BY `schema` ASC, `name` ASC;

-- -----------------------------------------------------
-- View `log`.`v_column`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `log`.`v_column`;
DROP VIEW IF EXISTS `log`.`v_column` ;
USE `log`;
CREATE  OR REPLACE ALGORITHM = UNDEFINED
       DEFINER = CURRENT_USER
       SQL SECURITY DEFINER
VIEW `log`.`v_column`
AS
   SELECT `COLUMNS`.`TABLE_SCHEMA` AS `schema`,
		  `COLUMNS`.`TABLE_NAME` AS `table`,
		  `COLUMNS`.`COLUMN_NAME` AS `name`,
		  `COLUMNS`.`ORDINAL_POSITION` AS `sequence`,
		  trim(lower(`COLUMNS`.`COLUMN_KEY`)) <=> 'pri' AS `primary`,
		  trim(lower(`COLUMNS`.`IS_NULLABLE`)) <=> 'yes' AS `nullable`,
		  trim(lower(`COLUMNS`.`DATA_TYPE`)) AS `type`,
		  if(char_length(trim(ifnull(`COLUMNS`.`COLUMN_DEFAULT`, ''))) > 0, trim(`COLUMNS`.`COLUMN_DEFAULT`), NULL) AS `default`,
		  trim(`COLUMNS`.`COLUMN_COMMENT`) AS `desc_txt`
	 FROM `information_schema`.`COLUMNS`
		JOIN `log`.`v_table`
		  ON `v_table`.`schema` <=> `COLUMNS`.`TABLE_SCHEMA`
		 AND `v_table`.`name` <=> `COLUMNS`.`TABLE_NAME`
	WHERE char_length(ifnull(trim(`COLUMNS`.`GENERATION_EXPRESSION`), '')) <= 0
   ORDER BY `schema` ASC, `table` ASC, `sequence` ASC;
USE `log`;

DELIMITER $$

USE `log`$$
DROP TRIGGER IF EXISTS `log`.`audit_trail_BI` $$
USE `log`$$
CREATE DEFINER = CURRENT_USER
      TRIGGER `log`.`audit_trail_BI`
   BEFORE INSERT
   ON `audit_trail`
   FOR EACH ROW
BEGIN
   SET NEW.`schema` = lower(substring(trim(NEW.`schema`), 1, 64));
   SET NEW.`table` = lower(substring(trim(NEW.`table`), 1, 64));
   SET NEW.`column` = lower(substring(trim(NEW.`column`), 1, 64));
   SET NEW.`action` =
		  if(abs(NEW.`action`) BETWEEN 1 AND 3,
		     NEW.`action`,
		     1);
   SET NEW.`row_id` = ifnull(trim(NEW.`row_id`), '');
-- SET NEW.`old_value` = NEW.`old_value`;
-- SET NEW.`new_value` = NEW.`new_value`;
   SET NEW.`external_id` =
          if(char_length(ifnull(trim(NEW.`external_id`), '')) > 0,
		     trim(NEW.`external_id`),
			 NULL);
   SET NEW.`server_id` = @@GLOBAL.server_id;
   SET NEW.`server_uuid` = UUID_TO_BIN(@@GLOBAL.server_uuid, TRUE);
   SET NEW.`connection_id` = connection_id();
   SET NEW.`user` = user();
   SET NEW.`created` = current_timestamp(6);

   IF ifnull(length(NEW.`old_value`), 0) <= 0 OR abs(NEW.`action`) <=> 1	-- insert
   THEN
      SET NEW.`old_value` = NULL;
   END IF;

   IF NEW.`old_value` <=> NEW.`new_value` AND abs(NEW.`action`) <=> 2	-- update
   THEN
	  BEGIN
		 DECLARE _er_dup_entry CONDITION FOR SQLSTATE '23000';

		 SIGNAL _er_dup_entry
			SET MYSQL_ERRNO = 1062;
	  END;
   END IF;

   IF ifnull(length(NEW.`new_value`), 0) <= 0 OR abs(NEW.`action`) <=> 3	-- delete
   THEN
      SET NEW.`new_value` = NULL;
   END IF;

/* BEGIN
	  DECLARE _msg		longtext CHARSET utf8mb4 DEFAULT '<log.audit_trail_BI> ';
	  DECLARE _raise	bit(1) DEFAULT FALSE;

	  BEGIN
		 DECLARE _name	varchar(64) CHARSET utf8mb4 DEFAULT NULL;
		 DECLARE EXIT HANDLER FOR NOT FOUND
		 BEGIN
			SET _raise = TRUE;
			SET _msg = concat(_msg,
							  'Schema[name: ',
							  ifnull(NEW.`schema`, 'NULL'),
							  '] does not exists.');
		 END;

		 SELECT `name`
		   INTO _name
		   FROM `log`.`v_schema`
		  WHERE `name` <=> ifnull(NEW.`schema`, '');

		 BEGIN
			DECLARE EXIT HANDLER FOR NOT FOUND
			BEGIN
			   SET _raise = TRUE;
			   SET _msg = concat(_msg,
								 'Table[schema: ',
								 NEW.`schema`,
								 ' - name: ',
								 ifnull(NEW.`table`, 'NULL'),
								 '] does not exists.');
			END;

			SELECT `name`
			  INTO _name
			  FROM `log`.`v_table`
			 WHERE `schema` <=> NEW.`schema`
			   AND `name` <=> ifnull(NEW.`table`, '');

			BEGIN
			   DECLARE EXIT HANDLER FOR NOT FOUND
			   BEGIN
			      SET _raise = TRUE;
			      SET _msg = concat(_msg,
									'Column[schema: ',
									NEW.`schema`,
									' - table: ',
									NEW.`table`,
									' - name: ',
									ifnull(NEW.`column`, 'NULL'),
									'] does not exists.');
			   END;

			   SELECT `name`
				 INTO _name
				 FROM `log`.`v_column`
				WHERE `schema` <=> NEW.`schema`
				  AND `table` <=> NEW.`table`
				  AND `name` <=> ifnull(NEW.`column`, '');
			END;
		 END;
	  END;

	  IF _raise
	  THEN
		 CALL `log`.`writeLog`('log', _msg, 'error', NEW.`external_id`);
		 BEGIN
			DECLARE _er_signal_not_found  CONDITION FOR SQLSTATE '02000';

			IF char_length(_msg) > 128
			THEN
			   SET _msg = concat(substring(_msg, 1, 125), '...');
			END IF;

			SIGNAL _er_signal_not_found
			   SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 1643;
		 END;
	  END IF;
   END;*/
END$$


USE `log`$$
DROP TRIGGER IF EXISTS `log`.`log_BI` $$
USE `log`$$
CREATE DEFINER = CURRENT_USER
      TRIGGER `log`.`log_BI`
   BEFORE INSERT
   ON `log`
   FOR EACH ROW
BEGIN
   SET NEW.`message` =
          if(length(ifnull(trim(NEW.`message`), '')) > 0,
             trim(NEW.`message`),
             NULL);
   SET NEW.`severity` =
          if(abs(NEW.`severity`) BETWEEN 1 AND 5,
		     NEW.`severity`,
		     1);
   SET NEW.`owner` =
          if(char_length(ifnull(trim(NEW.`owner`), '')) > 0,
		     trim(NEW.`owner`),
			 NULL);
   SET NEW.`external_id` =
          if(char_length(ifnull(trim(NEW.`external_id`), '')) > 0,
		     trim(NEW.`external_id`),
			 NULL);
   SET NEW.`server_id` = @@GLOBAL.server_id;
   SET NEW.`server_uuid` = UUID_TO_BIN(@@GLOBAL.server_uuid, TRUE);
   SET NEW.`connection_id` = connection_id();
   SET NEW.`user` = user();
   SET NEW.`created` = current_timestamp(6);
END$$


DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET sql_log_bin = ON;