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

<?php

declare(strict_types=1);

namespace App\Commands\PostUpgrade\V0_2;

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();
        $this->updateFilesStatuses();

        $output->writeln('Post-upgrade operations successfully executed');

        return Command::SUCCESS;
    }

    private function getOpentalentConnexion(): \PDO
    {
        $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,
            [\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',
            [\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->error((string) $e);
            $this->logger->critical('Error while populating the subdomains, abort and rollback');
        }
    }

    private function genEventsUuid(): void
    {
        $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->error((string) $e);
            $this->logger->critical('Error while generating events uuids, abort and rollback');
        }
    }

    private function updateFilesStatuses(): void
    {
        $opentalentCnn = $this->getOpentalentConnexion();

        $opentalentCnn->beginTransaction();

        try {
            $this->logger->info('Update file statuses');

            $sql = "update opentalent.File set status='READY', host='api';";
            $opentalentCnn->query($sql);

            $opentalentCnn->commit();
            $this->logger->info('Files statuses were successfully updated');
        } catch (\Exception $e) {
            $opentalentCnn->rollBack();
            $this->logger->error((string) $e);
            $this->logger->critical('Error while updating file statuses, abort and rollback');
        }
    }
}