-- ------------------------------------------------------------------------------------
--  Method 1.  Create a clone of the table
--             Create a daily event to insert into the table using a primary key
-- ------------------------------------------------------------------------------------
 

create table daily_backup_table as
select * from orignal_table;


DROP EVENT IF EXISTS `My Daily Backup`;

DELIMITER $$
   CREATE EVENT IF NOT EXISTS `My Daily Backup`
    ON SCHEDULE EVERY 1 DAY STARTS '2024-01-25 13:33:00'
   COMMENT 'Daily Backup of My Table Data'
   DO 
      BEGIN
         insert into daily_backup_table select * from original_table recid not in (select recid from daily_backup_table);
      END;
 $$ DELIMITER ;

--
-- See what events are scheduled
--

SELECT event_name, event_definition, interval_value, interval_field, starts, status, last_executed, event_comment FROM INFORMATION_SCHEMA.EVENTS;



-- ------------------------------------------------------------------------------------
--  Method 2:  Create a procedure to Create a backup table with a DATE suffix.
--             Create a daily event to create the table.
-- ------------------------------------------------------------------------------------

DROP PROCEDURE if exists `daily_table_backup`;
DELIMITER $$
CREATE PROCEDURE daily_table_backup()
BEGIN
    DECLARE given_date VARCHAR(25);
    SET given_date = DATE_FORMAT(NOW(), '%Y%m%d');
    SET @backup_table_name = CONCAT('backup_table_', given_date);
    SET @backup_query = CONCAT('CREATE TABLE ', @backup_table_name, ' AS SELECT * FROM zap_paycom');
    PREPARE stmt FROM @backup_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
$$ DELIMITER ;

--call daily_table_backup();


DROP EVENT IF EXISTS `My Daily Backup`;

DELIMITER $$
   CREATE EVENT IF NOT EXISTS `My Daily Backup`
    ON SCHEDULE EVERY 1 DAY STARTS '2024-01-25 13:33:00'
   COMMENT 'Daily Backup of My Table Data'
   DO 
      BEGIN
         call daily_table_backup();
      END;
 $$ DELIMITER ;

--
-- See what events are scheduled
--

SELECT event_name, event_definition, interval_value, interval_field, starts, status, last_executed, event_comment FROM INFORMATION_SCHEMA.EVENTS;