Symfony Profiler

<?php
declare(strict_types=1);
namespace DoctrineMigrations;
use App\DBAL\Types\TransactionBucketType;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Auto-generated Migration: Please modify to your needs!
*/
final class Version20251216053601 extends AbstractMigration
{
private const COLORS = [
'006400' => 'Green',
'cdad00' => 'Gold',
'eeee00' => 'Yellow',
'ffa500' => 'Orange',
'ff0000' => 'Red',
'ffc0cb' => 'Pink',
'0000ff' => 'Blue',
'a020f0' => 'Purple',
'000000' => 'Black',
'00ffff' => 'Aqua',
'00ff00' => 'Lime',
'999999' => 'Grey',
'8b4513' => 'Brown',
'dad0fb' => 'Violet',
'aa4069' => 'Ruby',
];
private const TRANSACTION_COLORS = [
'006400' => 'Green',
'eeee00' => 'Yellow',
'ffa500' => 'Orange',
'ff0000' => 'Red',
'ffc0cb' => 'Pink',
'0000ff' => 'Blue',
'a020f0' => 'Purple',
'00ffff' => 'Aqua',
'00ff00' => 'Lime',
'999999' => 'Grey',
'dad0fb' => 'Violet',
'aa4069' => 'Ruby',
'000000' => 'Black',
'06dd90' => 'Loan',
];
private const BUCKET_BY_HEX = [
'00ffff' => TransactionBucketType::SAVED, // Aqua
'0000ff' => TransactionBucketType::AT_RISK, // Blue
'eeee00' => TransactionBucketType::LOST, // Yellow
'ffa500' => TransactionBucketType::LOST, // Orange
];
public function getDescription(): string
{
return '';
}
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('CREATE TABLE customer_status_color (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, description VARCHAR(255) DEFAULT NULL, color VARCHAR(6) DEFAULT NULL, sort_order INT DEFAULT NULL, INDEX idx_name (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('CREATE TABLE customer_transaction_color (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, color VARCHAR(6) DEFAULT NULL, bucket ENUM(\'late\', \'at_risk\', \'lost\', \'saved\') DEFAULT NULL COMMENT \'(DC2Type:TransactionBucket)\', sort_order INT DEFAULT NULL, INDEX idx_name (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('CREATE TABLE user_join_color (user_id INT NOT NULL, color_id INT NOT NULL, INDEX IDX_4873FDA8A76ED395 (user_id), INDEX IDX_4873FDA87ADA1FB5 (color_id), PRIMARY KEY(user_id, color_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('CREATE TABLE user_color (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, color VARCHAR(6) DEFAULT NULL, sort_order INT DEFAULT NULL, INDEX idx_name (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
$this->addSql('ALTER TABLE user_join_color ADD CONSTRAINT FK_4873FDA8A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE user_join_color ADD CONSTRAINT FK_4873FDA87ADA1FB5 FOREIGN KEY (color_id) REFERENCES user_color (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE customer_status_log ADD color_id INT DEFAULT NULL');
$this->addSql('ALTER TABLE customer_status_log ADD CONSTRAINT FK_AC993CE57ADA1FB5 FOREIGN KEY (color_id) REFERENCES customer_status_color (id) ON DELETE SET NULL');
$this->addSql('CREATE INDEX IDX_AC993CE57ADA1FB5 ON customer_status_log (color_id)');
$this->addSql('ALTER TABLE customer_transaction ADD color_id INT DEFAULT NULL');
$this->addSql('ALTER TABLE customer_transaction ADD CONSTRAINT FK_717C2ED97ADA1FB5 FOREIGN KEY (color_id) REFERENCES customer_transaction_color (id) ON DELETE SET NULL');
$this->addSql('CREATE INDEX IDX_717C2ED97ADA1FB5 ON customer_transaction (color_id)');
$colorIdByHex = [];
$id = 1;
$sort = 1;
foreach (self::COLORS as $hex => $name) {
$colorIdByHex[strtolower((string) $hex)] = $id;
$this->addSql('INSERT INTO user_color (id, name, color, sort_order) VALUES (:id, :name, :color, :sort_order)', [
'id' => $id++,
'name' => $name,
'color' => strtolower((string) $hex),
'sort_order' => $sort++,
]);
}
$sort = 1;
foreach (self::TRANSACTION_COLORS as $hex => $name) {
$bucket = isset(self::BUCKET_BY_HEX[$hex])
? self::BUCKET_BY_HEX[$hex]
: null;
$this->addSql('INSERT INTO customer_transaction_color (name, color, bucket, sort_order) VALUES (:name, :color, :bucket, :sort_order)', [
'name' => $name,
'color' => strtolower((string) $hex),
'bucket' => $bucket,
'sort_order' => $sort++,
]);
}
$sort = 1;
foreach (self::COLORS as $hex => $name) {
$this->addSql('INSERT INTO customer_status_color (name, description, color, sort_order) VALUES (:name, :description, :color, :sort_order)', [
'name' => $name,
'description' => null, // or 'TBD'
'color' => strtolower((string) $hex),
'sort_order' => $sort++,
]);
}
$this->addSql('
UPDATE customer_transaction ct
JOIN customer_transaction_color ctc
ON LOWER(ctc.color) COLLATE utf8mb4_unicode_ci
= LOWER(ct.color) COLLATE utf8mb4_unicode_ci
SET ct.color_id = ctc.id
WHERE ct.color IS NOT NULL AND ct.color <> ""
');
$this->addSql('
UPDATE customer_status_log csl
JOIN customer_status_color csc
ON LOWER(csc.color) COLLATE utf8mb4_unicode_ci
= LOWER(csl.color) COLLATE utf8mb4_unicode_ci
SET csl.color_id = csc.id
WHERE csl.color IS NOT NULL AND csl.color <> ""
');
$users = $this->connection->fetchAllAssociative('SELECT id, colors FROM user WHERE colors IS NOT NULL AND colors <> "a:0:{}"');
foreach ($users as $user) {
$userId = $user['id'];
$colorsArray = unserialize($user['colors']);
if (!\is_array($colorsArray)) {
continue;
}
foreach ($colorsArray as $colorHex) {
// Normalize: handle "006400" or 999999
if (\is_int($colorHex)) {
$colorHex = str_pad((string) $colorHex, 6, '0', \STR_PAD_LEFT);
} else {
$colorHex = strtolower(trim((string) $colorHex));
$colorHex = str_pad($colorHex, 6, '0', \STR_PAD_LEFT);
}
if (!isset($colorIdByHex[$colorHex])) {
// If unexpected color appears, you can either skip or insert it dynamically.
continue;
}
$this->addSql(
'INSERT IGNORE INTO user_join_color (user_id, color_id) VALUES (?, ?)',
[$userId, $colorIdByHex[$colorHex]]
);
}
}
$this->addSql('ALTER TABLE customer_status_log DROP color');
$this->addSql('ALTER TABLE customer_transaction DROP color');
$this->addSql('ALTER TABLE user DROP colors');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE customer_status_log DROP FOREIGN KEY FK_AC993CE57ADA1FB5');
$this->addSql('ALTER TABLE customer_transaction DROP FOREIGN KEY FK_717C2ED97ADA1FB5');
$this->addSql('ALTER TABLE user_join_color DROP FOREIGN KEY FK_4873FDA8A76ED395');
$this->addSql('ALTER TABLE user_join_color DROP FOREIGN KEY FK_4873FDA87ADA1FB5');
$this->addSql('DROP TABLE customer_status_color');
$this->addSql('DROP TABLE customer_transaction_color');
$this->addSql('DROP TABLE user_join_color');
$this->addSql('DROP TABLE user_color');
$this->addSql('DROP INDEX IDX_AC993CE57ADA1FB5 ON customer_status_log');
$this->addSql('ALTER TABLE customer_status_log ADD color VARCHAR(6) DEFAULT NULL COLLATE `utf8mb4_general_ci`, DROP color_id');
$this->addSql('DROP INDEX IDX_717C2ED97ADA1FB5 ON customer_transaction');
$this->addSql('ALTER TABLE customer_transaction ADD color VARCHAR(6) DEFAULT NULL COLLATE `utf8mb4_general_ci`, DROP color_id');
$this->addSql('ALTER TABLE user ADD colors LONGTEXT DEFAULT NULL COLLATE `utf8mb4_general_ci` COMMENT \'(DC2Type:array)\'');
}
}

migrations/Version20251216053601.php line 1