thetool/db/migrations/20250715110000_warehouse_offer_versioning.php

<?php

/** @noinspection ALL */
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;
use Phinx\Db\Adapter\MysqlAdapter;

final class WarehouseOfferVersioning extends AbstractMigration
{
    public function up(): void
    {
        if ($this->getEnvironment() == "thetool") {
            // Use Phinx schema builder to create the WarehouseOfferClosingText table
            $this->table('WarehouseOfferClosingText', [
                'id' => false,
                'primary_key' => ['id'],
                'engine' => 'InnoDB',
                'encoding' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'comment' => 'Stores standard closing text snippets for offers',
            ])
                ->addColumn('id', 'integer', ['identity' => true, 'signed' => true])
                ->addColumn('name', 'string', ['limit' => 255])
                ->addColumn('text', 'text')
                ->addColumn('createBy', 'integer')
                ->addColumn('create', 'integer')
                ->create();

            // Use Phinx schema builder to create the WarehouseOfferJournal table
            $this->table('WarehouseOfferJournal', [
                'id' => false,
                'primary_key' => ['id'],
                'engine' => 'InnoDB',
                'encoding' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'comment' => 'Journal for tracking actions on warehouse offers',
            ])
                ->addColumn('id', 'integer', ['identity' => true, 'signed' => false])
                ->addColumn('offerId', 'integer', ['null' => true])
                ->addColumn('fileIds', 'text', ['null' => true])
                ->addColumn('message', 'string', ['limit' => 255, 'null' => true])
                ->addColumn('create', 'integer', ['null' => true])
                ->addColumn('createBy', 'integer', ['null' => true])
                ->addIndex(['offerId'], ['name' => 'offerId'])
                ->addIndex(['createBy'], ['name' => 'createBy'])
                ->create();

            // Use Phinx schema builder to add columns to the WarehouseOffer table
            $warehouseOffer = $this->table('WarehouseOffer');
            $warehouseOffer
                ->addColumn('contactPerson', 'string', ['limit' => 255, 'null' => true, "after" => 'customerName'])
                ->addColumn('contactPersonEmail', 'string', ['limit' => 255, 'null' => true, 'after' => 'contactPerson'])
                ->addColumn('lastSentDate', 'integer', ['null' => true, 'after' => 'status'])
                ->addColumn('version', 'integer', ['default' => 1, 'after' => 'id'])
                ->addColumn('history_id', 'integer', ['null' => true, 'after' => 'version'])
                ->save();

            // Use Phinx schema builder to add the 'data' column to WarehouseHistory
            $warehouseHistory = $this->table('WarehouseHistory');
            $warehouseHistory
                ->addColumn('data', 'text', [
                    'limit' => MysqlAdapter::TEXT_LONG,
                    'null' => true,
                    'after' => 'note',
                    'encoding' => 'utf8mb4',
                    'collation' => 'utf8mb4_unicode_ci'
                ])
                ->save();

            // Data migration steps remain as raw SQL execution due to their complexity.
            $this->execute("DELETE FROM `WarehouseHistory` WHERE `table` = 'WarehouseOffer';");

            // Create a baseline version history for all existing offers.
            $this->execute("
                INSERT INTO `WarehouseHistory` (`table`, `row_id`, `key`, `old_value`, `new_value`, `note`, `data`, `user_id`, `create`)
                SELECT
                    'WarehouseOffer' AS `table`,
                    wo.id AS `row_id`,
                    'version' AS `key`,
                    0 AS `old_value`,
                    1 AS `new_value`,
                    'Baseline Version 1 erstellt durch Migration.' AS `note`,
                    JSON_OBJECT(
                        'id', wo.id,
                        'version', 1,
                        'history_id', NULL,
                        'offerNumber', wo.offerNumber,
                        'reference', wo.reference,
                        'customerNumber', wo.customerNumber,
                        'customerName', wo.customerName,
                        'contactPerson', wo.contactPerson,
                        'contactPersonEmail', wo.contactPersonEmail,
                        'customerStreet', wo.customerStreet,
                        'customerCity', wo.customerCity,
                        'customerZip', wo.customerZip,
                        'customerVAT', wo.customerVAT,
                        'editor', wo.editor,
                        'purpose', wo.purpose,
                        'positions', wo.positions,
                        'alternativePositions', wo.alternativePositions,
                        'totalDiscount', wo.totalDiscount,
                        'paymentTerms', wo.paymentTerms,
                        'deliveryTerms', wo.deliveryTerms,
                        'closingText', wo.closingText,
                        'notes', wo.notes,
                        'status', wo.status,
                        'lastSentDate', wo.lastSentDate,
                        'totalAmount', wo.totalAmount,
                        'create', wo.create,
                        'createBy', wo.createBy
                    ) AS `data`,
                    wo.createBy AS `user_id`,
                    UNIX_TIMESTAMP() AS `create`
                FROM `WarehouseOffer` wo;
            ");

            // Update the history_id in the WarehouseOffer table to link to the newly created history entry.
            $this->execute("
                UPDATE `WarehouseOffer` wo
                JOIN `WarehouseHistory` wh ON wo.id = wh.row_id
                SET wo.history_id = wh.id
                WHERE wh.`table` = 'WarehouseOffer' AND wh.new_value = 1 AND wh.note = 'Baseline Version 1 erstellt durch Migration.';
            ");
        }
    }

    public function down(): void
    {
        if ($this->getEnvironment() == "thetool") {
            // Clean up the history created by this migration
            $this->execute("DELETE FROM `WarehouseHistory` WHERE `note` = 'Baseline Version 1 erstellt durch Migration.' AND `table` = 'WarehouseOffer';");

            // Revert changes to the WarehouseOffer table
            $warehouseOfferTable = $this->table('WarehouseOffer');
            if ($warehouseOfferTable->hasColumn('history_id')) {
                $warehouseOfferTable->removeColumn('history_id')->save();
            }
            if ($warehouseOfferTable->hasColumn('version')) {
                $warehouseOfferTable->removeColumn('version')->save();
            }
            if ($warehouseOfferTable->hasColumn('lastSentDate')) {
                $warehouseOfferTable->removeColumn('lastSentDate')->save();
            }
            if ($warehouseOfferTable->hasColumn('contactPersonEmail')) {
                $warehouseOfferTable->removeColumn('contactPersonEmail')->save();
            }

            // Revert changes to the WarehouseHistory table
            $warehouseHistoryTable = $this->table('WarehouseHistory');
            if ($warehouseHistoryTable->hasColumn('data')) {
                $warehouseHistoryTable->removeColumn('data')->save();
            }

            // Drop the newly created tables
            if ($this->hasTable('WarehouseOfferJournal')) {
                $this->table('WarehouseOfferJournal')->drop()->save();
            }
            if ($this->hasTable('WarehouseOfferClosingText')) {
                $this->table('WarehouseOfferClosingText')->drop()->save();
            }
        }
    }
}