--https://zabbix.org/wiki/Docs/howto/MySQL_Table_Partitioning_(variant)
--https://zabbix.org/wiki/Docs/howto/mysql_partition
--https://zabbix.org/wiki/Docs/howto/mysql_partitioning

USE `zabbix`;

CREATE TABLE IF NOT EXISTS `manage_partitions` (
  `tablename` VARCHAR(64) NOT NULL COMMENT 'Table name',
  `period` VARCHAR(64) NOT NULL COMMENT 'Period - daily or monthly',
  `keep_history` INT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'For how many days or months to keep the partitions',
  `last_updated` DATETIME DEFAULT NULL COMMENT 'When a partition was added last time',
  `comments` VARCHAR(128) DEFAULT '1' COMMENT 'Comments',
  PRIMARY KEY (`tablename`)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `manage_partitions_history` (
  `schema_name` varchar(64) NOT NULL COMMENT 'Zabbix schema name',
  `table_name` varchar(64) NOT NULL COMMENT 'Zabbix table name',
  `table_partition_name` varchar(64) NOT NULL COMMENT 'Zabbix table partition name',
  `partition_action` varchar(64) NOT NULL COMMENT 'Zabbix table partition action',
  `partition_action_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When a partition was added or dropped'
) ENGINE=InnoDB;

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'month', 24, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'month', 24, now(), '');

DROP PROCEDURE IF EXISTS `create_next_partitions`;
DROP PROCEDURE IF EXISTS `create_partition_by_day`;
DROP PROCEDURE IF EXISTS `create_partition_by_month`;
DROP PROCEDURE IF EXISTS `drop_partitions`;
DROP PROCEDURE IF EXISTS `drop_old_partition`;
DROP EVENT IF EXISTS `e_zbx_part_mgmt`;

DELIMITER $$
CREATE PROCEDURE `create_next_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN
    DECLARE TABLENAME_TMP VARCHAR(64);
    DECLARE PERIOD_TMP VARCHAR(12);
    DECLARE DONE INT DEFAULT 0;
    DECLARE get_prt_tables CURSOR FOR
        SELECT `tablename`, `period`
        FROM manage_partitions;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN get_prt_tables;
        loop_create_part: LOOP
            IF DONE THEN
                LEAVE loop_create_part;
            END IF;
            FETCH get_prt_tables INTO TABLENAME_TMP, PERIOD_TMP;
            CASE 
                WHEN PERIOD_TMP = 'day' THEN
                    CALL `create_partition_by_day`(IN_SCHEMANAME, TABLENAME_TMP);
                WHEN PERIOD_TMP = 'month' THEN
                    CALL `create_partition_by_month`(IN_SCHEMANAME, TABLENAME_TMP);
                ELSE
                BEGIN
                    ITERATE loop_create_part;
                END;
            END CASE;
                UPDATE manage_partitions set last_updated = NOW() WHERE tablename = TABLENAME_TMP;
        END LOOP loop_create_part;
    CLOSE get_prt_tables;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE BEGINTIME TIMESTAMP;
    DECLARE ENDTIME INT UNSIGNED;
    DECLARE PART_ACTION VARCHAR(12);
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE ROWS_CNT INT UNSIGNED;
    SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;
    SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY);
    SET PART_ACTION = 'ADD';
    SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d' );
    SELECT COUNT(*) INTO ROWS_CNT
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME 
        AND table_name = IN_TABLENAME 
        AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', PARTITIONNAME,'","', PART_ACTION,'");');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        ELSE
            SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE BEGINTIME TIMESTAMP;
    DECLARE ENDTIME INT UNSIGNED;
    DECLARE PART_ACTION VARCHAR(12);
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE ROWS_CNT INT UNSIGNED;
    SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
    SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH);
    SET PART_ACTION = 'ADD';
    SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m' );
    SELECT COUNT(*) INTO ROWS_CNT
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME 
        AND table_name = IN_TABLENAME 
        AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', PARTITIONNAME,'","', PART_ACTION,'");');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        ELSE
            SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `drop_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN
    DECLARE TABLENAME_TMP VARCHAR(64);
    DECLARE PARTITIONNAME_TMP VARCHAR(64);
    DECLARE VALUES_LESS_TMP INT;
    DECLARE PERIOD_TMP VARCHAR(12);
    DECLARE KEEP_HISTORY_TMP INT;
    DECLARE KEEP_HISTORY_BEFORE INT;
    DECLARE DONE INT DEFAULT 0;
    DECLARE get_partitions CURSOR FOR
        SELECT p.`table_name`, p.`partition_name`, LTRIM(RTRIM(p.`partition_description`)), mp.`period`, mp.`keep_history`
        FROM information_schema.partitions p
        JOIN manage_partitions mp ON mp.tablename = p.table_name
        WHERE p.table_schema = IN_SCHEMANAME
        ORDER BY p.table_name, p.subpartition_ordinal_position;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN get_partitions;
        loop_check_prt: LOOP
            IF DONE THEN
                LEAVE loop_check_prt;
            END IF;
            FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
            CASE 
                WHEN PERIOD_TMP = 'day' THEN
                    SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP DAY));
                WHEN PERIOD_TMP = 'month' THEN
                    SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP MONTH - INTERVAL DAY(NOW())-1 DAY));
                ELSE
                    BEGIN
                        ITERATE loop_check_prt;
                    END;
            END CASE;
            IF KEEP_HISTORY_BEFORE >= VALUES_LESS_TMP THEN
                CALL drop_old_partition(IN_SCHEMANAME, TABLENAME_TMP, PARTITIONNAME_TMP);
            END IF;
        END LOOP loop_check_prt;
    CLOSE get_partitions;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))
BEGIN
    DECLARE PART_ACTION VARCHAR(12);
    DECLARE PART_ACTION_DATE INT;
    DECLARE ROWS_CNT INT UNSIGNED;
        SELECT COUNT(*) INTO ROWS_CNT
        FROM information_schema.partitions
        WHERE table_schema = IN_SCHEMANAME 
            AND table_name = IN_TABLENAME 
            AND partition_name = IN_PARTITIONNAME;
    SET PART_ACTION = 'DROP';
    IF ROWS_CNT = 1 THEN
        SET @SQL = CONCAT('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', IN_PARTITIONNAME,'","', PART_ACTION,'");');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',' DROP PARTITION ', IN_PARTITIONNAME, ';' );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
        ELSE
            SELECT CONCAT("partition `", IN_PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME, "` does not exist") AS result;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE EVENT `e_zbx_part_mgmt` 
	ON SCHEDULE EVERY 1 DAY STARTS '2018-01-01 00:00:00' 
	ON COMPLETION PRESERVE 
	ENABLE 
	COMMENT 'Creating and dropping partitions' 
	DO BEGIN
		CALL zabbix.drop_partitions('zabbix');
		CALL zabbix.create_next_partitions('zabbix');
	END$$
DELIMITER ;