CREATE Schema AdminSchema;
USE AdminSchema;

CREATE TABLE `SysDatabase`(
`Name` CHAR(255) NOT NULL, 
`Type` CHAR(255) NOT NULL, 
`Value` CHAR(255) NOT NULL, 
`Date` DATETIME NOT NULL, 
`Instance` INT NOT NULL, 
`Resource` INT NOT NULL, 
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

\d $$
CREATE TRIGGER `SysDatabase_AINS` 
     AFTER INSERT ON `SysDatabase` FOR EACH ROW
     BEGIN
     DECLARE Name, Type, Value CHAR(255);
     DECLARE Date DATETIME;
     DECLARE Instance, Resource INT;
     
     #Recovery
     IF (SELECT COUNT(SysDatabase.ID) FROM SysDatabase) = 1 THEN 
     SELECT SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
     SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysDatabase;
     
     ELSE
     
     SELECT 
     SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
     SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource 
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysDatabase 
     WHERE NOT EXISTS 
     (SELECT SysDatabaseTemp.ID 
      FROM SysDatabaseTemp WHERE SysDatabaseTemp.ID = SysDatabase.ID); 
     
     END IF;
     
     INSERT 
     INTO SysRecovery 
     (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
     VALUE
     ('SysDatabase', Name, Type, Value, Date, @NowDate, Instance, Resource); 
     
     #SysStructure
     INSERT
     INTO SysStructure
     (Name, Type, Date, Instance, Resource) 
     VALUES
     (Name, 'Schema', @NowDate, Instance, Resource);
     
     #SysOperation 
     SET @Action = 'Create'; 
     
     INSERT
     INTO SysOperation
     (Name, Type, Value, Date, Instance, Resource) 
     VALUES
     (Name, Type, 'Schema', @NowDate, Instance, Resource);
     
     END$$


CREATE TRIGGER `SysDatabase_BINS` 
     BEFORE INSERT ON `SysDatabase` FOR EACH ROW
     BEGIN
     #Log
     SET @NowDate = NOW(); 
     
     IF (SELECT COUNT(ID) FROM SysDatabase) > 0 THEN
     
     CREATE TEMPORARY TABLE SysDatabaseTemp ENGINE = MEMORY
     SELECT * FROM SysDatabase;
     SET @SysDatabaseTemp = TRUE;
     
     END IF;
     
     SET @Table = 'Schema';
     
     END$$

CREATE TRIGGER `SysDatabase_BUPD` 
     BEFORE UPDATE ON `SysDatabase` FOR EACH ROW
     BEGIN
     #Log
     IF @Structure IS FALSE THEN
     SET @NowDate = NOW(); 
     END IF;
     #Recovery
     CREATE TEMPORARY TABLE SysDatabaseTemp ENGINE = MEMORY
     SELECT * FROM SysDatabase;
     
     SET @SysDatabaseTemp = TRUE;
     
     SET @Table = 'Schema';
     
     END$$

CREATE TRIGGER `SysDatabase_AUPD` 
     AFTER UPDATE ON `SysDatabase` FOR EACH ROW
     BEGIN
     DECLARE Name, Type, Value CHAR(255);
     DECLARE Date DATETIME;
     DECLARE Instance, Resource, ID INT;
     
     #Recovery
     SELECT 
     SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
     SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource 
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysDatabase 
     WHERE NOT EXISTS 
     (SELECT SysDatabaseTemp.ID
      FROM SysDatabaseTemp WHERE SysDatabaseTemp.Date = SysDatabase.Date); 
     
     INSERT 
     INTO SysRecovery 
     (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
     VALUE
     ('SysDatabase', Name, Type, Value, Date, @NowDate, Instance, Resource); 
     #SysOperation
     
     SET @Action = 'Rename';
     
     IF @Structure IS FALSE THEN
     
     UPDATE SysStructure SET Date = Date
     WHERE SysStructure.Date = (SELECT 
     SysDatabaseTemp.Date
     FROM SysDatabaseTemp 
     WHERE NOT EXISTS 
     (SELECT SysDatabase.ID
      FROM SysDatabase WHERE SysDatabase.Date = SysDatabaseTemp.Date));
     
     END IF;
     
     INSERT
     INTO SysOperation
     (Name, Type, Value, Date, Instance, Resource) 
     VALUES
     (Name, Type, 'Schema', @NowDate, Instance, Resource);
     
     END$$

\d ;

CREATE TABLE `SysStructure` (
`Name` char(255) NOT NULL,
`Type` char(255) NOT NULL,
`Value` char(255) DEFAULT NULL,
`Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Instance` int(11) NOT NULL,
`Resource` int(11) NOT NULL,
`ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`))ENGINE=InnoDB;

\d $$

CREATE TRIGGER `SysStructure_BINS` 
     BEFORE INSERT ON `SysStructure` FOR EACH ROW
     BEGIN
     
     #Recovery
     IF (SELECT COUNT(ID) FROM SysStructure ) > 0 THEN 
     
     CREATE TEMPORARY TABLE SysStructureTemp ENGINE = MEMORY
     SELECT * FROM SysStructure;
     
     SET @SysStructureTemp = TRUE;
     
     END IF;
     
     END$$

CREATE TRIGGER `SysStructure_AINS` 
     AFTER INSERT ON `SysStructure` FOR EACH ROW
     BEGIN 
     DECLARE Name, Type, Value CHAR(255);
     DECLARE Date DATETIME;
     DECLARE Instance, Resource INT;
     #Log
     
     # Recovery
     IF (SELECT COUNT(SysStructure.ID) FROM SysStructure) = 1 THEN 
     SELECT SysStructure.Name, SysStructure.Type, SysStructure.Value, 
     SysStructure.Date, SysStructure.Instance, SysStructure.Resource
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysStructure;
     
     ELSE 
     
     SELECT SysStructure.Name, SysStructure.Type, SysStructure.Value, 
     SysStructure.Date, SysStructure.Instance, SysStructure.Resource
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysStructure 
     WHERE NOT EXISTS 
     (SELECT SysStructureTemp.ID 
      FROM SysStructureTemp WHERE SysStructureTemp.ID = SysStructure.ID); 
     
     END IF;
     
     INSERT 
     INTO SysRecovery 
     (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
     VALUES
     ('SysTask', Name, Type, Value, Date, @NowDate, Instance, Resource); 
     
     
     IF @SysStructureTemp = TRUE THEN 
     
     DROP TEMPORARY TABLE SysStructureTemp;
     
     SET @SysStructureTemp = FALSE; 
     
     END IF;
     
     END$$

CREATE TRIGGER `SysStructure_BUPD` 
     BEFORE UPDATE ON `SysStructure` FOR EACH ROW
     BEGIN
     #Log
     #Recovery
     CREATE TEMPORARY TABLE SysStructureTemp ENGINE = MEMORY
     SELECT * FROM SysStructure;
     
     SET @SysStructureTemp = TRUE;
     
     END$$

CREATE TRIGGER `SysStructure_AUPD` 
     AFTER UPDATE ON `SysStructure` FOR EACH ROW
     BEGIN
     DECLARE Name, Type, Value CHAR(255);
     DECLARE Date DATETIME;
     DECLARE Instance, Resource, ID INT;
     
     IF @Action IS NULL THEN
     SET @NowDate = NOW(); 
     END IF;
     #Recovery
     SELECT 
     SysStructure.Name, SysStructure.Type, SysStructure.Value, 
     SysStructure.Date, SysStructure.Instance, SysStructure.Resource 
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysStructure 
     WHERE NOT EXISTS 
     (SELECT SysStructureTemp.ID
      FROM SysStructureTemp WHERE SysStructureTemp.Date = SysStructure.Date); 
     
     INSERT 
     INTO SysRecovery 
     (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
     VALUE
     ('SysStructure', Name, Type, Value, Date, @NowDate, Instance, Resource); 
     #SysOperation
     
     CASE 1
     WHEN Type = 'Schema' THEN
     
     CASE 1 
     WHEN @Action IS NULL THEN
     
     SET @Structure = TRUE; 
     
     UPDATE SysDatabase 
     SET Date = Date 
     WHERE SysDatabase.Date = (SELECT 
     SysStructureTemp.Date 
     FROM SysStructureTemp 
     WHERE NOT EXISTS 
     (SELECT SysStructure.ID
      FROM SysStructure WHERE SysStructure.Date = SysStructureTemp.Date)); 
     
     ELSE
     
     BEGIN END;
     
     END CASE;
     
     END CASE; 
     
     IF @SysStructureTemp = TRUE THEN 
     
     DROP TEMPORARY TABLE SysStructureTemp;
     
     SET @SysStructureTemp = FALSE; 
     
     END IF;
     
     END$$

\d ;

CREATE TABLE SysRecovery(
`TableName` CHAR(255), 
`Name` CHAR(255) NOT NULL, 
`Type` CHAR(255) NOT NULL, 
`Value` CHAR(255) DEFAULT NULL, 
`Task` CHAR(255) DEFAULT NULL, 
`Date` DATETIME NOT NULL, 
`NowDate` DATETIME NOT NULL,
`Instance` INT NOT NULL, 
`Resource` INT NOT NULL, 
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE SysOperation(
`Name` CHAR(255) NOT NULL, 
`Type` CHAR(255) NOT NULL, 
`Value` CHAR(255) NOT NULL, 
`Date` DATETIME NOT NULL, 
`Instance` INT NOT NULL, 
`Resource` INT NOT NULL, 
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

\d $$

CREATE TRIGGER `SysOperation_BINS` 
     BEFORE INSERT ON `SysOperation` FOR EACH ROW
     BEGIN
     #Log

     #Recovery
     IF (SELECT COUNT(ID) FROM SysOperation) > 0 THEN 
     CREATE TEMPORARY TABLE SysOperationTemp ENGINE = MEMORY
     SELECT * FROM SysOperation;
     SET @SysOperationTemp = TRUE;
 
     END IF;
 
     END$$

CREATE TRIGGER `SysOperation_AINS` 
     AFTER INSERT ON `SysOperation` FOR EACH ROW

BEGIN
     DECLARE Name, Type, Value, Task CHAR(255);
     DECLARE Date DATETIME;
     DECLARE Instance, Resource INT;  
     
     IF (SELECT COUNT(SysOperation.ID) FROM SysOperation) = 1 THEN 
     SELECT SysOperation.Name, SysOperation.Type, SysOperation.Value, 
     SysOperation.Date, SysOperation.Instance, SysOperation.Resource
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysOperation;
     
     ELSE 
     
     IF (SELECT COUNT(SysOperationTemp.ID) FROM SysOperationTemp) > 0 THEN 
     SELECT SysOperation.Name, SysOperation.Type, SysOperation.Value, 
     SysOperation.Date, SysOperation.Instance, SysOperation.Resource
     INTO Name, Type, Value, Date, Instance, Resource
     FROM SysOperation 
     WHERE NOT EXISTS 
     (SELECT SysOperationTemp.ID 
      FROM SysOperationTemp WHERE SysOperationTemp.ID = SysOperation.ID); 
     
     END IF;
     END IF;
     
     INSERT 
     INTO SysRecovery 
     (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
     VALUES
     ('SysOperation', Name, Type, Value, Date, @NowDate, Instance, Resource); 
     
      
     IF @SysDatabaseTemp IS TRUE THEN 
     DROP TEMPORARY TABLE SysDatabaseTemp; 
     SET @SysDatabaseTemp = FALSE;
     END IF; 
     
     IF @SysOperationTemp IS TRUE THEN 
     DROP TEMPORARY TABLE SysOperationTemp;
     SET @SysOperationTemp = FALSE;
     END IF;

     SET @Action = null, @Table = null;
     
     END$$

\d;

INSERT INTO SysDatabase(Name,Type,Value,Date,Instance,Resource) VALUE ('Schema Name','Schema Type','Schema Value',NOW(),1,1);
DO SLEEP(1);
UPDATE SysStructure SET Value='DEFAULT CHARSET = UCS2', Date=NOW() WHERE Date=@NowDate;