thetool/db/migrations/20250424100000_warehouse_modify_20.php

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

use Phinx\Migration\AbstractMigration;
use Phinx\Util\Literal; // Import Literal for raw SQL expressions

final class WarehouseModify20 extends AbstractMigration {
    public function up(): void {
        // Only run this migration in the 'thetool' environment
        if ($this->getEnvironment() == "thetool") {

            // --- Start: Original WarehouseShippingNoteTextElement logic ---
            $WarehouseShippingNoteTextElementTable = $this->table("WarehouseShippingNoteTextElement");
            if ($WarehouseShippingNoteTextElementTable->exists()) {
                $WarehouseShippingNoteTextElementTable->drop()->save();
            }

            $WarehouseShippingNoteTextElementTable = $this->table("WarehouseShippingNoteTextElement", ['id' => 'id', 'signed' => false]);
            if (!$WarehouseShippingNoteTextElementTable->exists()) {
                $WarehouseShippingNoteTextElementTable
                    ->addColumn("title", "string", ["limit" => 255])
                    ->addColumn("content", "text")
                    // Use Literal::from('CURRENT_TIMESTAMP') or similar if your DB supports it for default timestamps
                    // Using a fixed default like this might not be ideal
                    ->addColumn("create", "integer", ["default" => 1728541890])
                    ->addColumn("createBy", "integer", ["default" => 1])
                    ->create(); // Use create() instead of save() for new tables
            }

            $WarehouseCategoryTable = $this->table("WarehouseCategory", ['id' => 'id', 'signed' => false]);
            if (!$WarehouseCategoryTable->exists()) {
                $WarehouseCategoryTable
                    ->addColumn("name", "string", ["limit" => 255])
                    ->addColumn("description", "text")
                    ->addColumn("create", "integer", ["default" => 1728541890])
                    ->addColumn("create_by", "integer", ["default" => 1])
                    ->addColumn("edit", "integer", ["null" => true, 'default' => null])
                    ->addColumn("edit_by", "integer", ["null" => true, 'default' => null])
                    ->create(); // Use create() instead of save() for new tables
            }

            // --- End: Original WarehouseShippingNoteTextElement logic ---


            // --- Start: New WarehouseArticle Category Migration Logic ---

            // 1. Populate WarehouseCategory with distinct categories from WarehouseArticle
            // Using execute() for complex INSERT with SELECT, TRIM, UNIX_TIMESTAMP, and ON DUPLICATE KEY UPDATE
            // Assumes WarehouseCategory table exists with columns: name, description, create, create_by
            $populateCategorySql = <<<SQL
            INSERT INTO `WarehouseCategory` (`name`, `description`, `create`, `create_by`)
            SELECT DISTINCT
                TRIM(`category`),                 -- name
                TRIM(`category`),                 -- description
                UNIX_TIMESTAMP(),                 -- create (current Unix time)
                1                                 -- create_by
            FROM `WarehouseArticle`
            WHERE `category` IS NOT NULL AND TRIM(`category`) != ''
            ON DUPLICATE KEY UPDATE
                `name` = VALUES(`name`),          -- Keep existing name
                `description` = VALUES(`description`), -- Update description if needed
                `create` = VALUES(`create`),    -- Update timestamp if needed
                `create_by` = VALUES(`create_by`);    -- Update creator if needed
            SQL;
            $this->execute($populateCategorySql);

            // Get the WarehouseArticle table object
            $warehouseArticleTable = $this->table('WarehouseArticle');

            // 2. Add the new category_id column to WarehouseArticle
            $warehouseArticleTable
                ->addColumn('category_id', 'integer', [
                    'null' => true, // Allow NULL values
                    'signed' => false, // Typically IDs are unsigned
                    'default' => null,
                    'after' => 'description', // Position the column
                    'comment' => 'References the WarehouseCategory table ID'
                ])
                ->update(); // Apply the column addition

            // 3. Update WarehouseArticle.category_id based on the old category name
            // Using execute() for the UPDATE with JOIN and COLLATE
            // *** IMPORTANT: Adjust 'utf8mb4_unicode_ci' if your columns use a different collation ***
            $updateCategoryIdSql = <<<SQL
            UPDATE `WarehouseArticle` wa
            LEFT JOIN `WarehouseCategory` wc ON TRIM(wa.`category`) = wc.`name` COLLATE utf8mb4_unicode_ci
            SET wa.`category_id` = wc.`id`
            WHERE wa.`category` IS NOT NULL AND TRIM(wa.`category`) != '';
            SQL;
            $this->execute($updateCategoryIdSql);

            // 4. Drop the old category column and its index
            // Phinx automatically handles index removal when removing the column if it was defined via Phinx.
            // However, the original index 'category' might have been created manually.
            // Best practice: Explicitly remove the index first if unsure.
            if ($warehouseArticleTable->hasIndex('category')) {
                $warehouseArticleTable->removeIndexByName('category')->update();
            }
            // Now remove the column itself
            $warehouseArticleTable->removeColumn('category')->update();

            // 5. Add an index to the new category_id column
            // Check if index already exists before adding
            if (!$warehouseArticleTable->hasIndex('category_id')) {
                $warehouseArticleTable->addIndex('category_id', ['name' => 'idx_category_id'])->update();
            }

            // --- End: New WarehouseArticle Category Migration Logic ---
        }
    }

    public function down(): void {
        // Only run this migration in the 'thetool' environment
        if ($this->getEnvironment() == "thetool") {

            // --- Start: Reversal of WarehouseArticle Category Migration Logic ---

            $warehouseArticleTable = $this->table('WarehouseArticle');

            // 1. Remove the index from category_id
            if ($warehouseArticleTable->hasIndexByName('idx_category_id')) {
                $warehouseArticleTable->removeIndexByName('idx_category_id')->update();
            }

            // 2. Add the old 'category' column back
            // Ensure the definition matches the original state as closely as possible.
            $warehouseArticleTable
                ->addColumn('category', 'string', [
                    'limit' => 255,
                    'null' => false, // Assuming it was NOT NULL originally based on CREATE TABLE statement
                    'collation' => 'utf8mb4_unicode_ci', // Match original collation
                    'encoding' => 'utf8mb4',
                    'after' => 'description' // Place it back roughly where it was
                ])
                ->update();

            // 3. Add the index back to the 'category' column
            // Check if index already exists before adding
            if (!$warehouseArticleTable->hasIndex('category')) {
                $warehouseArticleTable->addIndex('category')->update();
            }

            // 4. Populate the old 'category' column based on 'category_id'
            // Using execute() for the UPDATE with JOIN
            // *** IMPORTANT: Adjust 'utf8mb4_unicode_ci' if your columns use a different collation ***
            $repopulateCategorySql = <<<SQL
            UPDATE `WarehouseArticle` wa
            LEFT JOIN `WarehouseCategory` wc ON wa.`category_id` = wc.`id`
            SET wa.`category` = wc.`name` COLLATE utf8mb4_unicode_ci -- Ensure collation match for assignment
            WHERE wa.`category_id` IS NOT NULL;
            SQL;
            $this->execute($repopulateCategorySql);

            // 5. Remove the 'category_id' column
            $warehouseArticleTable->removeColumn('category_id')->update();

            // --- End: Reversal of WarehouseArticle Category Migration Logic ---


            // --- Start: Original Down Logic (potentially destructive/incomplete) ---
            // Note: This part only recreates WarehouseShippingNoteTextElement and drops WarehouseCategory.
            // It does NOT fully revert the state before the *entire* 'up' method ran if WarehouseCategory existed before.
            // Consider if this is the desired behavior.

            $WarehouseShippingNoteTextElementTable = $this->table("WarehouseShippingNoteTextElement", ['id' => 'id', 'signed' => false]);
            // This check seems redundant if the goal is to ensure it exists after 'down'
            // if (!$WarehouseShippingNoteTextElementTable->exists()) {
            // Drop if exists, then recreate to ensure clean state matching original 'down' intent
            if ($WarehouseShippingNoteTextElementTable->exists()) {
                $WarehouseShippingNoteTextElementTable->drop()->save();
            }
            $WarehouseShippingNoteTextElementTable = $this->table("WarehouseShippingNoteTextElement", ['id' => 'id', 'signed' => false]); // Re-initialize after drop
            $WarehouseShippingNoteTextElementTable
                ->addColumn("title", "string", ["limit" => 255])
                ->addColumn("content", "text")
                ->addColumn("create", "integer", ["default" => 1728541890])
                ->addColumn("createBy", "integer", ["default" => 1])
                ->create(); // Use create()
            // }

            $WarehouseCategoryTable = $this->table("WarehouseCategory", ['id' => 'id', 'signed' => false]);
            if ($WarehouseCategoryTable->exists()) {
                // This drops the category table entirely, including data potentially added outside this migration
                $WarehouseCategoryTable->drop()->save();
            }
            // --- End: Original Down Logic ---
        }
    }
}