CREATE TABLE if not exists archive_audit(
	object_name varchar(100) ,
	operation varchar(20) ,
	status varchar(20) ,
	row_count int,
	exec_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS proc_archive_table;

delimiter $$

CREATE PROCEDURE `proc_archive_table`(v_tabname varchar(64), v_col varchar(64), v_colvalue varchar(64),v_oper varchar(4))
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN
  DECLARE stmt TEXT DEFAULT '';
  declare iter int;
  declare maxiter int;  
  declare varcnt11 int;
  declare varcnt21 int;   
  declare varcnt31 int; 
  
SET @cur_archive_date = concat(year(now()),month(now()),day(now()),hour(now()),minute(now()));

-- select CONCAT("create table if not exists ",v_tabname,"_arch_",@cur_archive_date," ENGINE=InnoDB select * from ",v_tabname," where date(",v_col,") ",v_oper," '",v_colvalue,"'");
SET @sql=CONCAT("create table if not exists ",v_tabname,"_arch_",@cur_archive_date," ENGINE=InnoDB select * from ",v_tabname," where ",v_col," ",v_oper," '",v_colvalue,"'");
PREPARE stmt FROM @sql;
EXECUTE stmt;
select ROW_COUNT() into varcnt11;	
DEALLOCATE PREPARE stmt;
insert into archive_audit SELECT concat(v_tabname,":",v_col),'archiving',case when varcnt11 > 0 then 'success' when varcnt11 = -1 then concat('error code: ',varcnt11) else null end, varcnt11,now();
set FOREIGN_KEY_CHECKS=0;
-- select CONCAT("delete from ",v_tabname," where date(",v_col,") ",v_oper," '",v_colvalue,"'");
SET @sql=CONCAT("delete from ",v_tabname," where ",v_col," ",v_oper," '",v_colvalue,"'");
PREPARE stmt FROM @sql;
EXECUTE stmt;
select ROW_COUNT() into varcnt21;
DEALLOCATE PREPARE stmt;
insert into archive_audit SELECT concat(v_tabname,":",v_col),'delete',null,varcnt21,now();
set FOREIGN_KEY_CHECKS=1;
-- select CONCAT("OPTIMIZE TABLE ",v_tabname);
SET @sql=CONCAT("OPTIMIZE TABLE ",v_tabname);
PREPARE stmt FROM @sql;
EXECUTE stmt;
select ROW_COUNT() into varcnt31;
DEALLOCATE PREPARE stmt;
insert into archive_audit SELECT v_tabname,'rebuild',null,varcnt31,now();  
      
end $$
  
delimiter ;