Duplicating answers from a multi-section form populating multiple tables

Question

I am implementing a system in which a client fills a form. Each form has multiple sections and each section populates multiple tables, thus user fills each section separately.

I was asked to make a mechanism that duplicates the answers with a new completion id. Each table referencing to a form completion has a foreign key reference into this table:

Completion:
-----
id PK Auto Increment
completed bool (true if all segments completed)
step_completed (an integer representation of each completed step)
date_created Record Creation timestamp
date_completed A timestamp in which user has completed all Steps

Each step is defined with a constant upon php code. My duplication is done via:

namespace App\DB;

/**
 * Duplicate database records from one fillId into another.
 */
class DbDuplicator
{
    private \PDO $pdo;

    // We want these tables to be skipped.
    // `completion` table is duplicated at the begining thus we do not want to be iterated.
    const TABLES_TO_OMMIT=['phinxlog','completion'];

    public function __construct(\PDO $pdo){
        $this->pdo = $pdo;
    }

    /**
     * List Current Database Tables
     *
     * @return \PDOStatement
     */
    private function getTables():\PDOStatement
    {
       return $this->pdo->query("SHOW TABLES");
    }

    /**
     * Retrieve Column Names and in which table is referenced to.
     *
     * @param string $table Table name
     * @return \PDOStatement Statement in which is used to fetch the records. The statement is return in order to avoid high memory consumtion.
     *
     */
    private function getColumnNames(string $table): \PDOStatement
    {
        // MAX removes the ones having null if duplicate records exists upon `columns`.`COLUMN_NAME`
        $sql = <<<SQL
            SELECT 
                `columns`.`COLUMN_NAME`,
                MAX(`fk`.`REFERENCED_TABLE_NAME`) AS `REFERENCED_TABLE_NAME`
            FROM 
                information_schema.`COLUMNS` as `columns` LEFT JOIN information_schema.`KEY_COLUMN_USAGE` as `fk` on  
                        `columns`.`COLUMN_NAME` = `fk`.`COLUMN_NAME` 
                    and `columns`.`TABLE_SCHEMA` = `fk`.`TABLE_SCHEMA`
                    and `columns`.`TABLE_NAME` = `fk`.`TABLE_NAME`
            WHERE 
                `columns`.TABLE_SCHEMA = DATABASE() 
            and `columns`.`TABLE_NAME` = :table
            AND `columns`.EXTRA NOT LIKE '%auto_increment%'
        GROUP BY 
            `columns`.`COLUMN_NAME`;
SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':table', $table, \PDO::PARAM_STR);
        $stmt->execute();

        return $stmt;
    }


    /**
     * Create Duplication SQL.
     *
     * @param string $table The table name
     * @param int $oldcompletionId Previous Completion id
     * @param int $newCompletionId New Completion Id
     * @return string
     */
    private function createDuplicationSql(string $table, int $oldcompletionId, int $newCompletionId): string
    {
        $columnsStmt = $this->getColumnNames($table);

        $sql = "INSERT INTO `$table`(%s) SELECT %s FROM `$table` %s";
        $insertColumns="";
        $selectColumns="";
        $fkColumn="";

        while(($columnInfo=$columnsStmt->fetch(\PDO::FETCH_ASSOC))!==false){
            $isFK = !empty($columnInfo['REFERENCED_TABLE_NAME']);

            $selectColumns.="`".$columnInfo["COLUMN_NAME"]."`,";
            $insertColumnName ="`".$columnInfo["COLUMN_NAME"]."`";

            if($isFK){
                $fkColumn=$columnInfo["COLUMN_NAME"];
                $insertColumnName ="'".$newCompletionId."'";
            }

            $insertColumns.=$insertColumnName.",";

        }


        $selectColumns = substr($selectColumns, 0, -1);
        $insertColumns = substr($insertColumns, 0, -1);

        $where="";
        if($fkColumn!=""){
            $where=" where `$fkColumn`=".$oldcompletionId;
        }

        $sql = sprintf($sql, $selectColumns, $insertColumns,$where);

        return $sql;
    }

    /**
     * Create Ne completion id derived from previous One.
     *
     * @param int $completionId Previous Completion Id.
     * @return int New Completion Id
     */
    private function duplicateCompletion(int $completionId,):int
    {
        $sql = <<<SQL
        INSERT INTO completion(date_created,date_completed,completed,step_completed)
        SELECT date_created,date_completed,completed,step_completed from completion
        where id=:fillId;
SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':fillId', $completionId, \PDO::PARAM_INT);
        $stmt->execute();

        return (int)$this->pdo->lastInsertId();
    }

    /**
     * Duplicate the completion and it nessesary tables.
     *
     * @param int $completionId The completion Id to duplicate
     * @return int With the new completion id
     *
     * @throws \Exception
     */
    public function duplicate(int $completionId):int
    {
        if($completionId < 0){
            throw new \InvalidArgumentException("Completion Id has not been defined");
        }

        $this->pdo->beginTransaction();

        try{
            $newCompletionId = $this->duplicateCompletion($completionId);
            $tableStmt = $this->getTables();
            while(($table = $tableStmt->fetchColumn())!=false){
                if(in_array($table, self::TABLES_TO_OMMIT)){
                    continue;
                }
                $sql = $this->createDuplicationSql($table, $completionId, $newCompletionId);
                $this->pdo->query($sql);
            }
            $this->pdo->commit();
        }catch (\Exception $e){
            $this->pdo->rollBack();
            throw $e;
        }

        return $newCompletionId;
    }
}

In which I initially duplicate the records using INSERT ... SELECT statement upon the completion table. Afterwards I iterate all database's tables and I dynamically create the nessesary INSERT ... SELECT statement in order to duplicate them.

I approached it like this because I do not want to add extra queries if schema is modified. Though, I am not sure if this class will cause a maintenance hell towards to my system.

Is this approach a good one? The class will be called upon a simple php script via web (and not cli):

// In case browser performs an options request. (Possibly in a future Ajax)
// The if statement and code inside has been made using chatgpt.
if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
    // Handle preflight request
    header('HTTP/1.1 204 No Content');
    header('Access-Control-Allow-Origin: *'); // Adjust this as needed for security
    header('Access-Control-Allow-Methods: POST, OPTIONS');
    header('Access-Control-Allow-Headers: Content-Type, Authorization'); // Adjust headers as needed
    exit;
}

// Allow only POST
if($_SERVER['REQUEST_METHOD'] != "POST") {
    header('HTTP/1.1 405 Method Not Allowed');
    header('Allow: POST');
    header('Content-Type: text/plain');
    echo "ONLY POST http method allowed";
    exit(0);
}


if(empty($_POST['fill_id']){
   echo "NO fill id provided"; 
   exit(1);
}

require __DIR__ . "/../vendor/autoload.php";
use App\DB\DbDuplicator;

// Connection settings ommited for simplicity
$pdo = new \PDO();
$duplicator = new \App\DB\DbDuplicator($pdo);

$newFillId = $duplicator->duplicate((int)$_POST['fill_id']??-1);


// Return responses here do stuff with $newFillId 

Is there a better approach in my case? The only alternative is I think it to manually add INSERT ... SELECT ... queries that will cause me to spend more time for duplication once schema is changed.

The database used is MariaDb or mysql.