DELIMITER $$

CREATE PROCEDURE archive_last_days_dynamic(
  IN p_source_table VARCHAR(64),
  IN p_archive_table VARCHAR(64),
  IN p_date_column VARCHAR(64),
  IN p_days_back INT,
  IN p_batch_size INT, -- If batch size is NULL or <= 0, run once without LIMIT
  IN p_optimize_after INT  -- 0 = do not optimize, 1 = OPTIMIZE TABLE
)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rows_affected INT DEFAULT 0;
  DECLARE total_rows INT DEFAULT 0;

  DECLARE insert_sql VARCHAR(4000);
  DECLARE delete_sql VARCHAR(4000);
  DECLARE optimize_sql VARCHAR(4000);
  
  -- catch NOTEs and WARNINGS so they are not shown in summary (optional)
  -- DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
  -- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
  -- DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
  -- DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' BEGIN END; 
  -- DECLARE CONTINUE HANDLER FOR SQLSTATE '01S00' BEGIN END; 

  -- Validation
  IF p_source_table IS NULL OR p_source_table = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source table name is required';
  END IF;

  IF p_archive_table IS NULL OR p_archive_table = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Archive table name is required';
  END IF;

  IF p_date_column IS NULL OR p_date_column = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Date column name is required';
  END IF;

  IF p_days_back IS NULL OR p_days_back <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Days back must be greater than 0';
  END IF;

  -- If batch size is NULL or <= 0, run once without LIMIT
  IF p_batch_size IS NULL OR p_batch_size <= 0 THEN

    SET insert_sql = CONCAT(
      'INSERT INTO `', p_archive_table, '` ',
      'SELECT * FROM `', p_source_table, '` ',
      'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY'
    );

    SET delete_sql = CONCAT(
      'DELETE FROM `', p_source_table, '` ',
      'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY'
    );

    START TRANSACTION;

    SET @insert_sql = insert_sql;
    PREPARE stmt_insert FROM @insert_sql;
    EXECUTE stmt_insert;
    SET rows_affected = ROW_COUNT();
    SET total_rows = rows_affected;
    DEALLOCATE PREPARE stmt_insert;

    SET @delete_sql = delete_sql;
    PREPARE stmt_delete FROM @delete_sql;
    EXECUTE stmt_delete;
    DEALLOCATE PREPARE stmt_delete;

    COMMIT;

  ELSE
    REPEAT
      SET insert_sql = CONCAT(
        'INSERT INTO `', p_archive_table, '` ',
        'SELECT * FROM `', p_source_table, '` ',
        'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY ',
        'LIMIT ', p_batch_size
      );

      SET delete_sql = CONCAT(
        'DELETE FROM `', p_source_table, '` ',
        'WHERE `', p_date_column, '` <= NOW() - INTERVAL ', p_days_back, ' DAY ',
        'LIMIT ', p_batch_size
      );

      START TRANSACTION;

      SET @insert_sql = insert_sql;
      PREPARE stmt_insert FROM @insert_sql;
      EXECUTE stmt_insert;
      SET rows_affected = ROW_COUNT();
      SET total_rows = total_rows + rows_affected;
      DEALLOCATE PREPARE stmt_insert;

      IF rows_affected > 0 THEN
        SET @delete_sql = delete_sql;
        PREPARE stmt_delete FROM @delete_sql;
        EXECUTE stmt_delete;
        DEALLOCATE PREPARE stmt_delete;
      END IF;

      COMMIT;

      IF rows_affected < p_batch_size THEN
        SET done = TRUE;
      END IF;

    UNTIL done END REPEAT;

  END IF;

  -- optional optimization of source table
  IF p_optimize_after = 1 THEN
    SET optimize_sql = CONCAT('OPTIMIZE TABLE `', p_source_table, '`');
    SET @optimize_sql = optimize_sql;
    -- instead of showing result of OPTIMIZE we use DO
    SET @sql = CONCAT('DO (', @optimize_sql, ')');
    PREPARE stmt_optimize FROM @sql;
    EXECUTE stmt_optimize;
    DEALLOCATE PREPARE stmt_optimize;
  END IF;

  SELECT 'ok' AS status, total_rows AS total_rows_moved;

END $$

DELIMITER ;




//USE
CALL archive_last_days_dynamic('orders', 'orders_archive', 'created_at', 30, 500, 1);
