ap2i/src/Commands/PostUpgrade/V0_2/PostUpgradeCommand.php

<?php

namespace App\Commands\PostUpgrade\V0_2;

use PDO;
use Psr\Log\LoggerInterface;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

#[AsCommand(
    name: 'ot:upgrade:0.2',
    description: 'Execute the post-upgrade scripts for Ap2i v0.2'
)]
class PostUpgradeCommand extends Command
{
    public const TARGETED_VERSION = "0.2";

    public function __construct(private LoggerInterface $logger) {
        parent::__construct();
    }

    protected function configure(): void
    {}

    /**
     * @throws \Exception
     */
    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $this->logger->info('Run post-upgrade scripts for version ' . self::TARGETED_VERSION);

//        $this->populateSubdomains();
        $this->genEventsUuid();

        $output->writeln("Post-upgrade operations successfully executed");
        return Command::SUCCESS;
    }

    private function getOpentalentConnexion() {
        $dbUrl = $_ENV['DATABASE_URL'];
        $matches = [];
        preg_match(
            "/^mysql:\/\/(\w+):([^\s@]+)@([\w\-]+):(\d+)\/(\w+)/",
            $dbUrl,
            $matches
        );
        [$dbUser, $dbPwd, $dbHost, $dbPort, $dbName] = array_slice($matches, 1);

        $opentalentCnn = new PDO(
            "mysql:host=" . $dbHost . ";dbname=" . $dbName,
            $dbUser,
            $dbPwd,
            array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
        $opentalentCnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $opentalentCnn;
    }

    /**
     * Populate the new Subdomain table
     * @throws \Exception
     */
    private function populateSubdomains(): void
    {
        $opentalentCnn = $this->getOpentalentConnexion();

        $opentalentCnn->beginTransaction();

        $openassosCnn = new PDO(
            "mysql:host=prod-front;dbname=openassos",
            'dbcloner',
            'wWZ4hYcrmHLW2mUK',
            array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

        try {
            $stmt = $opentalentCnn->query("select count(*) from opentalent.Subdomain;");
            if ($stmt->fetchColumn(0)[0] > 0) {
                throw new \RuntimeException('Subdomain table is not empty');
            }

            $this->logger->info('Populate with reserved subdomains');
            $reservedSubdomains = [
                'app', 'my', 'api', 'ap2i', 'assistance', 'local', 'ressources', 'logs', 'stats', 'support', 'preprod',
                'test', 'admin', 'statistiques', 'drive', 'cloud', 'git', 'frames', 'v6', 'v59', 'www', 'myadmin'
            ];
            foreach ($reservedSubdomains as $reserved) {
                $sql = "insert into opentalent.Subdomain (organization_id, subdomain, active)
                    values (13, '" . $reserved . "', 0);";
                $opentalentCnn->query($sql);
            }

            $this->logger->info('Populate Subdomain table from openassos.sys_domain');

            $sql = "SELECT d.pid, REGEXP_REPLACE(d.domainName, '^(.+)\\\\.opentalent\\\\.fr$', '\\\\1')
                    FROM openassos.sys_domain d
                    where d.domainName like '%.opentalent.fr';";
            $statement = $openassosCnn->query($sql);

            foreach ($statement->fetchAll() as $row) {
                [$cmsId, $subdomain] = $row;
                if (!empty($subdomain) and is_numeric($cmsId)) {
                    $sql = "INSERT INTO opentalent.Subdomain (organization_id, subdomain)
                        SELECT o.id, '" . $subdomain . "'
                        from opentalent.Organization o 
                        where o.cmsId = " . $cmsId . ";";
                    $opentalentCnn->query($sql);
                }
            }

            $sql = "delete
                    from opentalent.Subdomain
                    where subdomain REGEXP '^(.*)\\\\.(.*)$'
                    and REGEXP_REPLACE(subdomain, '\\\\.', '-') in (select subdomain from opentalent.Subdomain);";
            $opentalentCnn->query($sql);

            $sql = "update opentalent.Subdomain
                    set subdomain = REGEXP_REPLACE(subdomain, '\\\\.', '-')
                    where subdomain REGEXP '^(.*)\\\\.(.*)$';";
            $opentalentCnn->query($sql);

            $this->logger->info('Complete with subdomains from Parameters table');
            $sql = "insert into opentalent.Subdomain (organization_id, subdomain)
                    select distinct o.id, p.subDomain
                    from opentalent.Parameters p
                    inner join opentalent.Organization o on o.parameters_id = p.id
                    left join opentalent.Subdomain s on s.organization_id = o.id
                    where p.subDomain is not null and not p.subDomain in (select subdomain from opentalent.Subdomain);";
            $opentalentCnn->query($sql);

            $this->logger->info('Set the current subdomains');
            $sql = "update opentalent.Subdomain s set s.active = false;";
            $opentalentCnn->query($sql);

            $sql = "update opentalent.Subdomain s
                    inner join opentalent.Organization o on o.id = s.organization_id
                    inner join opentalent.Parameters p on p.id = o.parameters_id and s.subdomain = p.subDomain
                    set s.active = true;";
            $opentalentCnn->query($sql);

            $this->logger->info('Set the custom domains');
            $sql = "update opentalent.Parameters
                    set customDomain = otherWebsite
                    where otherWebsite not like '%.opentalent.fr'";
            $opentalentCnn->query($sql);

            $opentalentCnn->commit();
            $this->logger->info('Subdomain table was successfully populated');
        } catch (\Exception $e) {
            $opentalentCnn->rollBack();
            $this->logger->critical('Error while running the post-upgrade script, abort and rollback');
            throw $e;
        }
    }

    private function genEventsUuid() {
        $opentalentCnn = $this->getOpentalentConnexion();

        $opentalentCnn->beginTransaction();

        try {
            $this->logger->info('Generate events uuids');

            // Generating Uuid1 with native mysql function
            $sql = "update opentalent.Booking set uuid = UUID();";
            $opentalentCnn->query($sql);

            $sql = "update opentalent.AwinProduct set uuid = UUID();";
            $opentalentCnn->query($sql);

            $opentalentCnn->commit();
            $this->logger->info('Events uuid were successfully generated');
        } catch (\Exception $e) {
            $opentalentCnn->rollBack();
            $this->logger->critical('Error while running the post-upgrade script, abort and rollback');
            throw $e;
        }
    }
}