ot_typo3/ot_admin/Classes/Controller/SiteController.php

<?php

namespace Opentalent\OtAdmin\Controller;

use Doctrine\DBAL\Driver\Exception;
use Opentalent\OtAdmin\Domain\Entity\SiteInfos;
use Opentalent\OtAdmin\Domain\Entity\SiteStatus;
use Opentalent\OtCore\Exception\InvalidWebsiteConfigurationException;
use Opentalent\OtCore\Exception\NoSuchOrganizationException;
use Opentalent\OtCore\Exception\NoSuchRecordException;
use Opentalent\OtCore\Exception\NoSuchWebsiteException;
use Opentalent\OtCore\Cache\OtCacheManager;
use Opentalent\OtCore\Controller\ActionController;
use Opentalent\OtCore\Domain\Model\Organization;
use Opentalent\OtCore\Domain\Repository\OrganizationRepository;
use Opentalent\OtCore\Exception\ApiRequestException;
use Opentalent\OtCore\Service\OpentalentEnvService;
use Opentalent\OtCore\Utility\FileUtility;
use PDO;
use RuntimeException;
use Symfony\Component\Yaml\Yaml;
use TYPO3\CMS\Core\Cache\CacheManager;
use TYPO3\CMS\Core\Cache\Exception\NoSuchCacheException;
use TYPO3\CMS\Core\Crypto\PasswordHashing\InvalidPasswordHashException;
use TYPO3\CMS\Core\Crypto\PasswordHashing\PasswordHashFactory;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;


/**
 * The SiteController implements some admin-only operations
 * on Typo3 websites, like creation or update.
 */
class SiteController extends ActionController
{
    // Templates names
    private const TEMPLATE_HOME = "OpenTalent.OtTemplating->home";
    private const TEMPLATE_1COL = "OpenTalent.OtTemplating->1Col";
    private const TEMPLATE_3COL = "OpenTalent.OtTemplating->home";
    private const TEMPLATE_EVENTS = "OpenTalent.OtTemplating->events";
    private const TEMPLATE_STRUCTURESEVENTS = "OpenTalent.OtTemplating->structuresEvents";
    private const TEMPLATE_STRUCTURES = "OpenTalent.OtTemplating->structures";
    private const TEMPLATE_CONTACT = "OpenTalent.OtTemplating->contact";
    private const TEMPLATE_NEWS = "OpenTalent.OtTemplating->news";
    private const TEMPLATE_MEMBERS = "OpenTalent.OtTemplating->members";
    private const TEMPLATE_MEMBERSCA = "OpenTalent.OtTemplating->membersCa";
    private const TEMPLATE_LEGAL = "OpenTalent.OtTemplating->legal";

    // Pages dokType values
    private const DOK_PAGE = 1;
    private const DOK_SHORTCUT = 4;
    private const DOK_FOLDER = 116;

    // Contents CTypes
    private const CTYPE_TEXT = 'text';
    private const CTYPE_IMAGE = 'image';
    private const CTYPE_TEXTPIC = 'textpic';
    private const CTYPE_TEXTMEDIA = 'textmedia';
    private const CTYPE_HTML = 'html';
    private const CTYPE_HEADER = 'header';
    private const CTYPE_UPLOADS = 'uploads';
    private const CTYPE_LIST = 'list';
    private const CTYPE_SITEMAP = 'menu_sitemap';

    // Default values
    private const DEFAULT_THEME = 'Classic';
    private const DEFAULT_COLOR = 'light-blue';

    // BE rights
    private const BEGROUP_EDITOR_STANDARD = 10;
    private const BEGROUP_EDITOR_PREMIUM = 20;
    private const BEGROUP_ADMIN_STANDARD = 30;
    private const BEGROUP_ADMIN_PREMIUM = 40;

    private const BEGROUP_NAME = [
        self::BEGROUP_EDITOR_STANDARD => "Editor_Standard",
        self::BEGROUP_EDITOR_PREMIUM => "Editor_Premium",
        self::BEGROUP_ADMIN_STANDARD => "Admin_Standard",
        self::BEGROUP_ADMIN_PREMIUM => "Admin_Premium"
    ];

    private const IS_PRODUCT_PREMIUM = [
        "school-standard" => false,
        "artist-standard" => false,
        "school-premium" => true,
        "artist-premium" => true,
        "manager" => true,
    ];

    // access permissions
    private const PERM_SHOW = 1;
    private const PERM_EDIT_CONTENT = 16;
    private const PERM_EDIT_PAGE = 2;
    private const PERM_DELETE = 4;
    private const PERM_NEW = 8;

    // Domain name validation
    private const RX_DOMAIN = "/([a-z0-9A-Z]\.)*[a-z0-9-]+\.([a-z0-9]{2,24})+(\.co\.([a-z0-9]{2,24})|\.([a-z0-9]{2,24}))*\/?/";

    // Redirections creation status
    private const REDIRECTION_UNKNOWN_STATUS = 0;
    public const REDIRECTION_UPDATED = 1;
    public const REDIRECTION_CREATED = 2;

    /**
     * @var ConnectionPool
     */
    private ConnectionPool $connectionPool;

    public function injectConnectionPool(ConnectionPool $connectionPool): void
    {
        $this->connectionPool = $connectionPool;
    }

    /**
     * @var CacheManager
     */
    private CacheManager $cacheManager;

    public function injectCacheManager(CacheManager $cacheManager): void
    {
        $this->cacheManager = $cacheManager;
    }

    /**
     * @var OrganizationRepository
     */
    protected OrganizationRepository $organizationRepository;

    public function injectOrganizationRepository(OrganizationRepository $organizationRepository): void
    {
        $this->organizationRepository = $organizationRepository;
    }

    /**
     * Index of the pages created during the process
     * >> [slug => uid]
     * @var array
     */
    private array $createdPagesIndex;

    /**
     * List of the directories created in the process (for rollback purposes)
     * @var array
     */
    private array $createdDirs;

    /**
     * List of the files created in the process (for rollback purposes)
     * @var array
     */
    private array $createdFiles;

    public function __construct()
    {
        $this->createdPagesIndex = [];
        $this->createdDirs = [];
        $this->createdFiles = [];
    }

    /**
     * Return the SiteInfos object for the organization's website
     *
     * @param int $organizationId
     * @return SiteInfos
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     * @throws InvalidWebsiteConfigurationException
     * @throws Exception
     */
    public function getSiteInfosAction(int $organizationId): SiteInfos
    {
        $website = $this->otWebsiteRepository->getWebsiteByOrganizationId($organizationId);
        $rootUid = $this->otWebsiteRepository->getWebsiteRootUid($website['uid']);

        $organizationExtraData = $this->fetchOrganizationExtraData($organizationId);

        $rootPage = $this->otPageRepository->getPage($rootUid);

        $site = new SiteInfos(
            $rootUid,
            $website['organization_name'],
            $this->otWebsiteRepository->resolveWebsiteDomain($website),
            $website['template'],
            $website['template_preferences'],
            $website['matomo_id'],
            self::IS_PRODUCT_PREMIUM[$organizationExtraData['admin']['product']] ?? false,
            (bool)$rootPage['deleted'],
            ($rootPage['hidden'] || $rootPage['fe_group'] < 0),
            null,
            null,
            $rootPage['perms_userid'],
            $rootPage['perms_groupid']
        );

        // Owners
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_groups');
        $queryBuilder->getRestrictions()->removeAll();
        $beUsers = $queryBuilder
            ->select('uid', 'username')
            ->from('be_users')
            ->where('FIND_IN_SET(' . $rootUid . ', db_mountpoints) > 0')
            ->execute()
            ->fetchAllAssociative();

        foreach ($beUsers as $beUser) {
            $site->addMountedForBeUser($beUser);
        }

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_groups');
        $queryBuilder->getRestrictions()->removeAll();
        $beGroups = $queryBuilder
            ->select('uid', 'title')
            ->from('be_groups')
            ->where('FIND_IN_SET(' . $rootUid . ', db_mountpoints) > 0')
            ->execute()
            ->fetchAllAssociative();

        foreach ($beGroups as $beGroup) {
            $site->addMountedForBeGroups($beGroup);
        }

        return $site;
    }

    /**
     * Creates a new website for the given organization, and
     * returns the root page uid of the newly created site
     *
     * @param int $organizationId
     * @return int Uid of the root page of the newly created website
     * @throws RuntimeException|\Throwable
     */
    public function createSiteAction(int $organizationId): int
    {
        $organization = $this->fetchOrganization($organizationId);

        // This extra-data can not be retrieved from the API for now, but
        // this shall be set up as soon as possible, to avoid requesting
        // the prod-back DB directly.
        $organizationExtraData = $this->fetchOrganizationExtraData($organizationId);

        $isNetwork = $organizationExtraData['category'] === 'NETWORK';

        // Is there already a website with this organization's id?
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $queryBuilder->getRestrictions()->removeAll();
        $statement = $queryBuilder
            ->select('uid')
            ->from('ot_websites')
            ->where($queryBuilder->expr()->eq('organization_id', $queryBuilder->createNamedParameter($organization->getId())))
            ->execute();
        if ($statement->rowCount() > 0) {
            throw new RuntimeException("A website with this organization's id already exists: " . $organization->getName() . "\n(if you can't see it, it might have been soft-deleted)");
        }

        // ** Create the new website

        // start transactions
        $this->connectionPool->getConnectionByName('Default')->beginTransaction();

        // keep tracks of the created folders and files to be able to remove them during a rollback
        try {
            // Create the website:
            $websiteUid = $this->insertOtWebsite($organization);

            // Create the site pages:
            // > Root page
            $rootUid = $this->insertRootPage(
                $websiteUid,
                $organization->getName()
            );

            // > 'Accueil' shortcut
            $this->insertPage(
                $websiteUid,
                $rootUid,
                'Accueil',
                '/accueil',
                '',
                [
                    'dokType' => self::DOK_SHORTCUT,
                    'shortcut' => $rootUid
                ]
            );

            // > 'Présentation' page
            $this->insertPage(
                $websiteUid,
                $rootUid,
                'Présentation',
                '/presentation'
            );

            // > 'Présentation > Qui sommes-nous ?' page (hidden by default)
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/presentation'],
                'Qui sommes nous?',
                '/qui-sommes-nous',
                '',
                ['hidden' => 1]
            );

            // > 'Présentation > Les adhérents' page
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/presentation'],
                'Les adhérents',
                '/les-adherents',
                self::TEMPLATE_MEMBERS
            );

            // > 'Présentation > Les membres du CA' page
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/presentation'],
                'Les membres du CA',
                '/les-membres-du-ca',
                self::TEMPLATE_MEMBERSCA
            );

            if ($isNetwork) {
                // > 'Présentation > Les sociétés adhérentes' page
                $this->insertPage(
                    $websiteUid,
                    $this->createdPagesIndex['/presentation'],
                    'Les sociétés adhérentes',
                    '/societes-adherentes',
                    self::TEMPLATE_STRUCTURES
                );
            }

            // > 'Présentation > Historique' page (hidden by default)
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/presentation'],
                'Historique',
                '/historique',
                '',
                ['hidden' => 1]
            );

            // ~ Contact shortcut will be created after the contact page

            // > 'Actualités' page (hidden by default)
            $this->insertPage(
                $websiteUid,
                $rootUid,
                'Actualités',
                '/actualites',
                self::TEMPLATE_NEWS,
                ['hidden' => 1]
            );

            // > 'Saison en cours' page
            $this->insertPage(
                $websiteUid,
                $rootUid,
                'Saison en cours',
                '/saison-en-cours'
            );

            // > 'Saison en cours > Les évènements' page
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/saison-en-cours'],
                'Les évènements',
                '/les-evenements',
                self::TEMPLATE_EVENTS
            );

            if ($isNetwork) {
                // > 'Présentation > Les sociétés adhérentes' page
                $this->insertPage(
                    $websiteUid,
                    $this->createdPagesIndex['/presentation'],
                    'Évènements des structures',
                    '/evenements-des-structures',
                    self::TEMPLATE_STRUCTURESEVENTS
                );
            }

            // > 'Vie interne' page (restricted, hidden by default)
            $this->insertPage(
                $websiteUid,
                $rootUid,
                'Vie interne',
                '/vie-interne',
                '',
                [
                    'hidden' => 1,
                    'fe_group' => -2
                ]
            );

            // > 'Footer' page (not in the menu)
            $this->insertPage(
                $websiteUid,
                $rootUid,
                'Footer',
                '/footer',
                '',
                [
                    'dokType' => self::DOK_FOLDER,
                    'nav_hide' => 1
                ]
            );

            // > 'Footer > Contact' page
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/footer'],
                'Contact',
                '/contact',
                self::TEMPLATE_CONTACT
            );

            // > 'Footer > Plan du site' page
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/footer'],
                'Plan du site',
                '/plan-du-site'
            );

            // > 'Footer > Mentions légales' page
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/footer'],
                'Mentions légales',
                '/mentions-legales',
                self::TEMPLATE_LEGAL
            );

            // > 'Présentation > Contact' shortcut
            $this->insertPage(
                $websiteUid,
                $this->createdPagesIndex['/presentation'],
                'Contact',
                '/ecrivez-nous',
                '',
                [
                    'dokType' => self::DOK_SHORTCUT,
                    'shortcut' => $this->createdPagesIndex['/contact']
                ]
            );

            // > 'Page introuvable' page (not in the menu, read-only)
            $this->insertPage(
                $websiteUid,
                $rootUid,
                'Page introuvable',
                '/page-introuvable',
                '',
                [
                    'nav_hide' => 1,
                    'no_search' => 1
                ]
            );

            // Add content to these pages

            // >> root page content
            $this->insertContent(
                $rootUid,
                self::CTYPE_TEXTPIC,
                '<h1>Bienvenue sur le site de ' . $organization->getName() . '.</h1>',
                0
            );

            // >> page 'qui sommes nous?'
            $this->insertContent(
                $this->createdPagesIndex['/qui-sommes-nous'],
                self::CTYPE_TEXT,
                'Qui sommes nous ...',
                0
            );

            // >> page 'historique'
            $this->insertContent(
                $this->createdPagesIndex['/historique'],
                self::CTYPE_TEXT,
                "Un peu d'histoire ...",
                0
            );

            // >> page 'plan du site'
            $this->insertContent(
                $this->createdPagesIndex['/plan-du-site'],
                self::CTYPE_SITEMAP
            );

            // update sys_template
            $constants = $this->getTemplateConstants($organizationId, $organizationExtraData);

            $include = "EXT:fluid_styled_content/Configuration/TypoScript/";
            $include .= ",EXT:fluid_styled_content/Configuration/TypoScript/Styling/";
            $include .= ",EXT:form/Configuration/TypoScript/";
            $include .= ",EXT:news/Configuration/TypoScript";
            $include .= ",EXT:frontend_editing/Configuration/TypoScript";
            $include .= ",EXT:frontend_editing/Configuration/TypoScript/FluidStyledContent9";
            $include .= ",EXT:ot_templating/Configuration/TypoScript";
            $include .= ",EXT:hcaptcha/Configuration/TypoScript";

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_template');
            $queryBuilder->insert('sys_template')
                ->values([
                    'pid' => $rootUid,
                    'title' => $organization->getName(),
                    'sitetitle' => $organization->getName(),
                    'root' => 1,
                    'clear' => 3,
                    'config' => "config.frontend_editing = 1",
                    'include_static_file' => $include,
                    'constants' => $constants
                ])
                ->execute();

            // ## Create the site config.yaml file
            $identifier = $this->writeConfigFile(
                $rootUid,
                true
            );

            // Update the ot_website identifier
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('ot_websites');
            $queryBuilder->update('ot_websites')
                ->set('config_identifier', $identifier)
                ->where($queryBuilder->expr()->eq('uid', $websiteUid))
                ->execute();

            // Create the user_upload and form_definitions directories and update the sys_filemounts table
            $uploadRelPath = "user_upload/" . $organizationId;
            $fileadminDir = $_ENV['TYPO3_PATH_APP'] . "/public/fileadmin";
            $uploadDir = rtrim($fileadminDir, '/') . "/" . ltrim($uploadRelPath, '/');
            if (file_exists($uploadDir)) {
                throw new RuntimeException("A directory or file " . $uploadDir . " already exists. Abort.");
            }

            $formsRelPath = '/form_definitions/' . $organizationId;
            $formsDir = $fileadminDir . $formsRelPath;
            if (file_exists($formsDir)) {
                throw new RuntimeException("A directory or file " . $formsDir . " already exists. Abort.");
            }

            $this->mkDir($uploadDir);
            $this->mkDir($formsDir);

            // Insert the filemounts points (sys_filemounts)
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_filemounts');
            $queryBuilder->insert('sys_filemounts')
                ->values([
                    'title' => 'Documents',
                    'path' => rtrim($uploadRelPath, '/') . '/',
                    'base' => 1
                ])
                ->execute();

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_filemounts');
            $queryBuilder->insert('sys_filemounts')
                ->values([
                    'title' => 'Forms_' . $organizationId,
                    'path' => rtrim($formsRelPath, '/') . '/',
                    'base' => 1
                ])
                ->execute();

            // Create the BE Editors group
            // -- NB: this user will then be auto-updated by the ot_connect extension --
            $beGroupUid = $this->createOrUpdateBeGroup(
                $organizationId,
                $rootUid,
                $organizationExtraData['admin']
            );

            // Create the BE User
            // -- NB: this user will then be auto-updated by the ot_connect extension --
            $beUserUid = $this->createOrUpdateBeUser(
                $organizationId,
                $beGroupUid,
                $organizationExtraData['admin']
            );

            // Update the user TsConfig
            $tsconfig = "options.uploadFieldsInTopOfEB = 1\n" .
                        "options.defaultUploadFolder=1:" . rtrim($uploadRelPath, '/') . "/\n";
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_users');
            $queryBuilder
                ->update('be_users')
                ->where($queryBuilder->expr()->eq('uid', $beUserUid))
                ->set('TSconfig', $tsconfig)
                ->execute();

            // Setup user and group rights
            $this->setBeUserPerms($organizationId, false, $beGroupUid, $beUserUid);

            // Try to commit the result
            $commitSuccess = $this->connectionPool->getConnectionByName('Default')->commit();
            if (!$commitSuccess) {
                throw new RuntimeException('Something went wrong while committing the result');
            }

            // Clear the whole cache, to avoid errors with the configuration files cache
            //      >> this could probably be improved by targeting the caches to clean
            //         more precisely, but that'll do for the moment
            OtCacheManager::clearCache(false);

        } catch(\Throwable $e) {
            // rollback
            $this->connectionPool->getConnectionByName('Default')->rollback();

            // remove created files and dirs
            foreach (array_reverse($this->createdFiles) as $filename) {
                unlink($filename);
            }
            $this->createdFiles = [];

            foreach (array_reverse($this->createdDirs) as $dirname) {
                rmdir($dirname);
            }
            $this->createdDirs = [];

            throw $e;
        }

        // Extra steps that do not need any rollback:
        $this->enableFeEditing($beUserUid);

        return $rootUid;
    }

    /**
     * Performs an update of the organization's website based on data fetched from the opentalent DB:
     *
     * - Update the pages table (structure id, structure domain)
     * - (hard update only) Update the config.yaml file
     * - Update the `sys_template`.`constants` and the `pages`.`TSConfig` fields
     * - (hard update only) Reset the users permissions
     * - Clear the Typo3 cache for the website
     *
     * @param int $organizationId
     * @return int
     * @throws NoSuchOrganizationException
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     * @throws \Doctrine\DBAL\ConnectionException
     * @throws \Doctrine\DBAL\DBALException
     * @throws InvalidWebsiteConfigurationException
     * @throws \Throwable
     */
    public function updateSiteAction(int $organizationId): int
    {
        $website = $this->otWebsiteRepository->getWebsiteByOrganizationId($organizationId);
        $rootUid = $this->otWebsiteRepository->getWebsiteRootUid($website['uid']);

        $organization = $this->fetchOrganization($organizationId);

        // This extra-data can not be retrieved from the API for now, but
        // this shall be set up as soon as possible, to avoid requesting
        // the prod-back DB directly.
        $organizationExtraData = $this->fetchOrganizationExtraData($organizationId);

        // start transactions
        $this->connectionPool->getConnectionByName('Default')->beginTransaction();

        // keep tracks of the created folders and files to be able to remove them during a rollback
        try {
            // ## Update the ot_website table
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('ot_websites');
            $queryBuilder->update('ot_websites')
                ->set('subdomain', $organization->getSubDomain())
                ->set('organization_name', $organization->getName())
                ->where($queryBuilder->expr()->eq('uid', $website['uid']))
                ->execute();

            // ## Update the subpages of the rootpage
            $sitePages = $this->otPageRepository->getAllSubpagesForPage($rootUid);
            foreach ($sitePages as $page) {
                $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
                $queryBuilder->update('pages')
                    ->set('ot_website_uid', $website['uid'])
                    ->where($queryBuilder->expr()->eq('uid', $page['uid']))
                    ->execute();
            }

            // ## Update the `sys_template`.`constants` and the `pages`.`TSConfig` fields
            $constants = $this->getTemplateConstants($organizationId, $organizationExtraData);

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_template');
            $queryBuilder
                ->update('sys_template')
                ->set('constants', $constants)
                ->where($queryBuilder->expr()->eq('pid', $rootUid))
                ->execute();

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
            $queryBuilder
                ->update('pages')
                ->set('TSconfig', 'TCAdefaults.pages.ot_website_uid=' . $website['uid'])
                ->where($queryBuilder->expr()->eq('uid', $rootUid))
                ->execute();

            // ## Update the config.yaml file
            $identifier = $this->otWebsiteRepository->findConfigIdentifierFor($rootUid);
            $this->writeConfigFile($rootUid, true, $identifier);

            // ## Update the ot_website identifier
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('ot_websites');
            $queryBuilder->update('ot_websites')
                ->set('config_identifier', $identifier)
                ->where($queryBuilder->expr()->eq('uid', $website['uid']))
                ->execute();

            // Try to commit the result
            $commitSuccess = $this->connectionPool->getConnectionByName('Default')->commit();
            if (!$commitSuccess) {
                throw new RuntimeException('Something went wrong while committing the result');
            }

            // ## Clear the Typo3 cache for the website
            OtCacheManager::clearSiteCache($rootUid, true);

        } catch(\Throwable $e) {
            // rollback
            $this->connectionPool->getConnectionByName('Default')->rollback();
            throw $e;
        }

        return $rootUid;
    }

    /**
     * Delete the website with all its pages, contents and related records
     *
     * If the hard parameter is false, the records' `deleted` field will be set to true and
     * the files and directories will be renamed. This kind of 'soft' deletion can be undone.
     *
     * Otherwise, if hard is set to true, the records and files will be permanently removed,
     * with no possibility of undoing anything. In this case, you'll have to confirm your intention
     * by creating a file in the Typo3 root directory, named 'DEL####' (#### is the organization id)
     *
     * If $redirectToParent is set to true, the website's url will be redirected to its parent's website
     *
     * The $force parameter, if true, will both bypass the 'DEL###' file confirmation and recursively delete the
     * user_upload and form_definitions of the website. <!> USE WITH CAUTION
     *
     * @param int $organizationId
     * @param bool $hard
     * @param bool $redirectToParent  If set to true, the website's url will be redirected to its parent's website
     * @param bool $force
     * @return int
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     * @throws \Doctrine\DBAL\DBALException
     * @throws \Throwable
     */
    public function deleteSiteAction(int $organizationId, bool $hard=false, bool $redirectToParent=true, bool $force = false): int
    {
        $website = $this->otWebsiteRepository->getWebsiteByOrganizationId($organizationId);
        $websiteUid = $website['uid'];
        $rootUid = $this->otWebsiteRepository->getWebsiteRootUid($website['uid']);

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $isDeleted = $queryBuilder
            ->select('deleted')
            ->from('ot_websites')
            ->where($queryBuilder->expr()->eq('uid', $website['uid']))
            ->execute()
            ->fetchOne() === 1;

        $confirm_file = $_ENV['TYPO3_PATH_APP'] . "/DEL" . $organizationId;
        if ($hard && !$force && !file_exists($confirm_file)) {
            throw new RuntimeException(
                "You are going to completely delete the website with root uid " . $rootUid .
                ", and all of its pages, files, contents...etc. If you are sure, create a file named '" .
                $confirm_file . "', and launch this command again."
            );
        }

        // Prepare the redirection
        if ($redirectToParent) {
            $originDomain = $this->otWebsiteRepository->resolveWebsiteDomain($website);

            $organization = $this->fetchOrganization($organizationId);

            $targetOrganizationId = $organization->getParentId();
            $targetOrganizationWebsite = $this->otWebsiteRepository->getWebsiteByOrganizationId($targetOrganizationId);
            $targetDomain = $this->otWebsiteRepository->resolveWebsiteDomain($targetOrganizationWebsite);
        }

        // start transactions
        $this->connectionPool->getConnectionByName('Default')->beginTransaction();

        // keep track of renamed file for an eventual rollback
        $renamed = [];

        try {

            $pages = $this->otPageRepository->getAllSubpagesForPage($rootUid);
            foreach($pages as $page) {
                $this->delete('tt_content', 'pid', $page['uid'], $hard);
                $this->delete('pages', 'uid', $page['uid'], $hard);
            }
            $this->delete('tt_content', 'pid', $rootUid, $hard);
            $this->delete('pages', 'uid', $rootUid, $hard);

            $this->delete('sys_template', 'pid', $rootUid, $hard);
            $this->delete('ot_websites', 'uid', $websiteUid, $hard);

            try {
                $adminBeUserUid = $this->findAdminBeUserUid($rootUid);
                if ($adminBeUserUid !== null) {
                    $this->delete('be_users', 'uid', $adminBeUserUid, $hard);
                }
            } catch (NoSuchRecordException $e) {}

            try {
                $editorsGroupUid = $this->findEditorsBeGroupUid($rootUid);
                if ($editorsGroupUid !== null) {
                    $this->delete('be_groups', 'uid', $editorsGroupUid, $hard);
                }
            } catch (NoSuchRecordException $e) {}

            // Delete the filemounts
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_filemounts');
            $queryBuilder
                ->select('uid')
                ->from('sys_filemounts')
                ->where("path LIKE '%user_upload/" . $organizationId . "/%'")
                ->orWhere("path LIKE '%form_definitions/" . $organizationId . "/%'");
            $statement = $queryBuilder->execute();
            $rows = $statement->fetchAllAssociative();
            foreach ($rows as $row) {
                $this->delete('sys_filemounts', 'uid', $row['uid'], $hard);
            }

            // Look up for the config.yaml file of the website
            $configMainDir = $_ENV['TYPO3_PATH_APP'] . '/config/sites';
            $configYamlFile = "";
            foreach (glob($configMainDir . '/*',  GLOB_ONLYDIR) as $subdir) {
                if (!$isDeleted) {
                    $yamlFile = $subdir . '/config.yaml';
                } else {
                    $yamlFile = $subdir . '/config.yaml.deleted';
                }

                if (is_file($yamlFile)) {
                    $conf = Yaml::parseFile($yamlFile);

                    if ($conf['rootPageId'] === $rootUid) {
                        $configYamlFile = $yamlFile;
                        break;
                    }
                }
            }
            if (!$isDeleted) {
                $uploadDir = $_ENV['TYPO3_PATH_APP'] . '/public/fileadmin/user_upload/' . $organizationId . '/';
                $formsDir = $_ENV['TYPO3_PATH_APP'] . '/public/fileadmin/form_definitions/' . $organizationId . '/';
            } else {
                $uploadDir = $_ENV['TYPO3_PATH_APP'] . '/public/fileadmin/user_upload/deleted_' . $organizationId . '/';
                $formsDir = $_ENV['TYPO3_PATH_APP'] . '/public/fileadmin/form_definitions/deleted_' . $organizationId . '/';
            }

            // If hard deletion, verify that dirs are empty
            if (!$force) {
                foreach ([$uploadDir, $formsDir] as $dir) {
                    if ($hard && is_dir($dir)) {
                        foreach (scandir($dir) as $subdir) {
                            if ($subdir === '.' || $subdir === '..') {
                                continue;
                            }
                            $subdir = $dir . $subdir;
                            if (!is_dir($subdir)) {
                                throw new RuntimeException(
                                    'The directory ' . $dir . ' contains non-directory files' .
                                    ', this humble script prefers not to take care of them automatically. Abort.');
                            }
                            if (is_readable($subdir)) {
                                foreach (scandir($subdir) as $filename) {
                                    if ($filename !== '.' && $filename !== '..') {
                                        throw new RuntimeException(
                                            'The directory ' . $subdir . ' is not empty, ' .
                                            'this humble script prefers not to take care of them automatically. Abort.');
                                    }
                                }
                            }
                        }
                    }
                }
            }

            // If soft deletion, check that no deleted file or directory exist
            if (!$hard) {
                $toRename = [];

                if (is_file($configYamlFile)) {
                    $toRename[$configYamlFile] = $configYamlFile . '.deleted';
                }
                if (is_dir($uploadDir)) {
                    $toRename[$uploadDir] = dirname($uploadDir) . '/deleted_' . basename($uploadDir);
                }

                foreach ($toRename as $initialPath => $newPath) {
                    if (is_file($newPath)) {
                        throw new RuntimeException(
                            'A file or directory named ' . $newPath . ' already exists, what happened?. Cancel.');
                    }
                }
            }

            // Delete or rename files and dirs
            if ($hard) {
                if (is_file($configYamlFile)) {
                    unlink($configYamlFile);
                }
                if (is_dir(dirname($configYamlFile))) {
                    FileUtility::rmdir(dirname($configYamlFile), $force);
                }
                if (is_dir($uploadDir)) {
                    FileUtility::rmdir($uploadDir, $force);
                }
                if (is_dir($formsDir)) {
                    FileUtility::rmdir($formsDir, $force);
                }
            } else {
                $renamed = [];
                foreach ($toRename as $initialPath => $newPath) {
                    rename($initialPath, $newPath);
                    $renamed[$initialPath] = $newPath;
                }
            }

            // Add the redirection
            if ($redirectToParent) {
                try {
                    $this->addRedirection($originDomain, $targetDomain);
                } catch (RuntimeException $e) {} // silent the error until this https://assistance.opentalent.fr/browse/V8-3344
            }

            // Try to commit the result
            $commitSuccess = $this->connectionPool->getConnectionByName('Default')->commit();
            if (!$commitSuccess) {
                throw new RuntimeException('Something went wrong while commiting the result');
            }
            return $rootUid;

        } catch(\Throwable $e) {
            // rollback
            $this->connectionPool->getConnectionByName('Default')->rollback();
            if (!$hard) {
                foreach ($renamed as $initialPath => $newPath) {
                    rename($newPath, $initialPath);
                }
            }
            throw $e;
        } finally {
            if (file_exists($confirm_file)) {
                unlink($confirm_file);
            }
        }
    }

    /**
     * Delete a record from the typo3 db.
     * If $hard is true, the record is permanently deleted.
     * Else, it's just marked as deleted.
     *
     * @param string $table
     * @param string $whereKey
     * @param $whereValue
     * @param bool $hard
     */
    private function delete(string $table, string $whereKey, $whereValue, bool $hard=false): void
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable($table);
        if (!$hard) {
            $queryBuilder
                ->update($table)
                ->set('deleted', 1)
                ->where($queryBuilder->expr()->eq($whereKey, $whereValue))
                ->execute();
        } else {
            $queryBuilder
                ->delete($table)
                ->where($queryBuilder->expr()->eq($whereKey, $whereValue))
                ->execute();
        }
    }

    /**
     * Undo a soft-deletion performed using deleteSiteAction()
     *
     * @param int $organizationId
     * @return int
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     * @throws \Doctrine\DBAL\ConnectionException
     * @throws \Doctrine\DBAL\DBALException
     * @throws \Throwable
     */
    public function undeleteSiteAction(int $organizationId): int
    {
        $website = $this->otWebsiteRepository->getWebsiteByOrganizationId($organizationId, false);
        $websiteUid = $website['uid'];
        $rootUid = $this->otWebsiteRepository->getWebsiteRootUid($website['uid'], false);

        // start transactions
        $this->connectionPool->getConnectionByName('Default')->beginTransaction();

        // keep track of renamed file for an eventual rollback
        $renamed = [];

        try {
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
            $queryBuilder
                ->update('ot_websites')
                ->set('deleted', 0)
                ->where($queryBuilder->expr()->eq('uid', $websiteUid))
                ->execute();

            $pages = $this->otPageRepository->getAllSubpagesForPage($rootUid);
            foreach($pages as $page) {
                $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
                $queryBuilder
                    ->update('tt_content')
                    ->set('deleted', 0)
                    ->where($queryBuilder->expr()->eq('pid', $page['uid']))
                    ->execute();

                $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
                $queryBuilder
                    ->update('pages')
                    ->set('deleted', 0)
                    ->where($queryBuilder->expr()->eq('uid', $page['uid']))
                    ->execute();
            }
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
            $queryBuilder
                ->update('tt_content')
                ->set('deleted', 0)
                ->where($queryBuilder->expr()->eq('pid', $rootUid))
                ->execute();

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
            $queryBuilder
                ->update('pages')
                ->set('deleted', 0)
                ->where($queryBuilder->expr()->eq('uid', $rootUid))
                ->execute();

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_template');
            $queryBuilder
                ->update('sys_template')
                ->set('deleted', 0)
                ->where($queryBuilder->expr()->eq('pid', $rootUid))
                ->execute();

            // remove filemounts
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_filemounts');
            $queryBuilder
                ->update('sys_filemounts')
                ->set('deleted', 0)
                ->where($queryBuilder->expr()->eq('path', "'/user_upload/" . $organizationId . "/'"))
                ->execute();

            try {
                $editorsGroupUid = $this->findEditorsBeGroupUid($rootUid, false);
                if ($editorsGroupUid !== null) {
                    $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_groups');
                    $queryBuilder
                        ->update('be_groups')
                        ->set('deleted', 0)
                        ->where($queryBuilder->expr()->eq('uid', $editorsGroupUid))
                        ->execute();
                }
            } catch (NoSuchRecordException $e) {}

            try {
                $adminBeUserUid = $this->findAdminBeUserUid($rootUid, false);
                if ($adminBeUserUid !== null) {
                    $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_users');
                    $queryBuilder
                        ->update('be_users')
                        ->set('deleted', 0)
                        ->where($queryBuilder->expr()->eq('uid', $adminBeUserUid))
                        ->execute();
                }
            } catch (NoSuchRecordException $e) {}

            // Look up for the config.yaml file of the website
            $configMainDir = $_ENV['TYPO3_PATH_APP'] . '/config/sites';
            $configYamlFile = "";
            foreach (glob($configMainDir . '/*',  GLOB_ONLYDIR) as $subdir) {
                $yamlFile = $subdir . '/config.yaml.deleted';

                if (is_file($yamlFile)) {
                    $conf = Yaml::parseFile($yamlFile);

                    if ($conf['rootPageId'] === $rootUid) {
                        $configYamlFile = $yamlFile;
                        break;
                    }
                }
            }
            $uploadDir = $_ENV['TYPO3_PATH_APP'] . '/public/fileadmin/user_upload/deleted_' . $organizationId . '/';
            $formsDir = $_ENV['TYPO3_PATH_APP'] . '/public/fileadmin/form_definitions/deleted_' . $organizationId . '/';

            $toRename = [];
            if (is_file($configYamlFile)) {
                $toRename[$configYamlFile] = dirname($configYamlFile) . '/config.yaml';
            }
            if (is_dir($uploadDir)) {
                $toRename[$uploadDir] = dirname($uploadDir) . '/' . $organizationId;
            }
            if (is_dir($formsDir)) {
                $toRename[$formsDir] = dirname($formsDir) . '/' . $organizationId;
            }

            foreach ($toRename as $initialPath => $newPath) {
                if (is_file($newPath)) {
                    throw new RuntimeException(
                        'A file or directory named ' . $newPath . ' already exists, what happened?. Cancel.');
                }
            }

            $renamed = [];
            foreach ($toRename as $initialPath => $newPath) {
                rename($initialPath, $newPath);
                $renamed[$initialPath] = $newPath;
            }

            // remove eventual redirection from this site to another
            $originUrl = $this->otWebsiteRepository->resolveWebsiteDomain($website);
            $this->removeRedirectionsFrom($originUrl);

            // Try to commit the result
            $commitSuccess = $this->connectionPool->getConnectionByName('Default')->commit();
            if (!$commitSuccess) {
                throw new RuntimeException('Something went wrong while commiting the result');
            }
            return $rootUid;

        } catch(\Throwable $e) {
            // rollback
            $this->connectionPool->getConnectionByName('Default')->rollback();
            foreach ($renamed as $initialPath => $newPath) {
                rename($newPath, $initialPath);
            }
            throw $e;
        }
    }

    /**
     * Clear the cache of the organization's website
     *
     * @param int $organizationId the organization's id whom site cache should be cleared
     * @param bool $clearAll if true, all caches will be cleared, and not only the frontend one
     * @return int
     * @throws NoSuchWebsiteException
     */
    public function clearSiteCacheAction(int $organizationId, bool $clearAll=false): int
    {
        $rootUid = $this->otWebsiteRepository->findRootUidForOrganization($organizationId);

        OtCacheManager::clearSiteCache($rootUid, $clearAll);

        return $rootUid;
    }

    /**
     * Perform a full scan of the website and returns a list of warnings
     *
     * @param int $organizationId
     * @return array
     * @throws NoSuchOrganizationException
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     * @throws Exception
     */
    private function scanSite(int $organizationId): array
    {
        $website = $this->otWebsiteRepository->getWebsiteByOrganizationId($organizationId);
        $rootUid = $this->otWebsiteRepository->getWebsiteRootUid($website['uid']);

        $warnings = [];

        // fetch and index pages and root page
        $pages = $this->otPageRepository->getPageWithSubpages($rootUid);
        $rootPage = null;
        $pageIndex = [];
        foreach ($pages as $page) {
            $pageIndex[$page['uid']] = $page;
            if ($page['is_siteroot'] === 1) { $rootPage = $page; }
        }

        // fetch organization and extradata
        $organization = $this->fetchOrganization($organizationId);
        $extraData = $this->fetchOrganizationExtraData($organizationId);

        // load site's settings (uncomment if needed)
        // $config = $this->otWebsiteRepository->findConfigFor($rootUid);

        // Check site's title
        if (trim($website['organization_name']) !== trim($organization->getName())) {
            $warnings[] = "Website's organization name is different from what is registered in the Opentalent DB";
        }
        if (trim($rootPage['title']) !== trim($organization->getName())) {
            $warnings[] = "Root page's title does not match the organization name";
        }

        // Who is the expected owner among the be_users? there should be only one.
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $queryBuilder->getRestrictions()->removeAll();
        $beUsers = $queryBuilder
            ->select('uid', 'username')
            ->from('be_users')
            ->where('FIND_IN_SET(' . $rootUid . ', db_mountpoints) > 0')
            ->execute()
            ->fetchAllAssociative();

        $owner = null;
        if (count($beUsers) > 1) {
            $warnings[] = 'Website is mounted on more than one be_user: ' .
                implode(', ', array_map(static function($u) { return $u['username']; } ,$beUsers));
        } elseif (count($beUsers) === 0) {
            $warnings[] = 'Website is not mounted on any be_user';
        } else {
            $owner = $beUsers[0];
        }

        // are template constants up to date?
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $actual_constants = $queryBuilder
            ->select('constants')
            ->from('sys_template')
            ->where($queryBuilder->expr()->eq('pid', $rootUid))
            ->execute()
            ->fetchOne();
        $expected_constants = $this->getTemplateConstants($organizationId, $extraData);
        $norm = static function ($s) {
            return strtolower(preg_replace('/\s/', '', $s));
        };
        if ($norm($expected_constants) !== $norm($actual_constants)) {
            $warnings[] = 'Template constants need an update';
        }

        $expected_templates = [
            "OpenTalent.OtTemplating->home" => 0,
            "OpenTalent.OtTemplating->legal" => 0,
            "OpenTalent.OtTemplating->contact" => 0
        ];

        foreach ($pages as $page) {
            if ($page['deleted']) {
                continue;
            }

            // Is it the correct owner?
            if ($owner !== null && $page['perms_userid'] !== $owner['uid']) {
                $warnings[] = 'Page ' . $page['uid'] . ' has wrong owner';
            }

            if (!$page['is_siteroot']) {
                // is the parent page state (deleted, hidden, restricted) the same as this page?
                $parent = $pageIndex[$page['pid']];

                if ($parent['deleted']) {
                    $warnings[] = 'The non-deleted page ' . $page['uid'] . ' has a deleted parent page';
                }
                if ($parent['hidden'] && !$page['hidden']) {
                    $warnings[] = 'The non-hidden page ' . $page['uid'] . ' has a hidden parent page';
                }
                if ($parent['fe_group'] < 0 && !($page['fe_group'] >= 0)) {
                    $warnings[] = 'The non-restricted page ' . $page['uid'] . ' has a restricted parent page';
                }
            }

            // an expected template was found, remove it from the list of expected
            if (
                !$page['hidden'] &&
                in_array($page['tx_fed_page_controller_action'], $expected_templates, true)
                )
            {
                unset($expected_templates[$page['tx_fed_page_controller_action']]);
            }
        }

        foreach ($expected_templates as $template => $_) {
            $warnings[] = 'No page with template ' . $template;
        }

        return $warnings;
    }

    /**
     * Get the current status of the organization's website
     * If $fullScan is true, a deeper scan will be performed and warnings may be logged
     *
     * The status is among:
     *
     *   - STATUS_NO_SUCH_WEBSITE
     *   - STATUS_EXISTING
     *   - STATUS_EXISTING_DELETED
     *   - STATUS_EXISTING_HIDDEN
     *   - STATUS_EXISTING_WITH_WARNINGS
     *
     * @param int $organizationId the organization's id whom site cache should be cleared
     * @param bool $fullScan If true, a 'warnings' entry will be added to the result, and a full scan of
     *                       the website pages will be performed.
     * @return SiteStatus
     * @throws Exception
     * @throws InvalidWebsiteConfigurationException
     * @throws NoSuchOrganizationException
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     */
    public function getSiteStatusAction(int $organizationId, bool $fullScan = false): SiteStatus
    {
        try {
            $siteInfos = $this->getSiteInfosAction($organizationId);
        } catch (NoSuchWebsiteException $e) {
            return new SiteStatus($organizationId, SiteStatus::STATUS_NO_SUCH_WEBSITE);
        }

        if ($siteInfos->isDeleted()) {
            return new SiteStatus($organizationId, SiteStatus::STATUS_EXISTING_DELETED, $siteInfos);
        }
        if ($siteInfos->isHiddenOrRestricted()) {
            return new SiteStatus($organizationId, SiteStatus::STATUS_EXISTING_HIDDEN, $siteInfos);
        }

        $warnings = null;
        if ($fullScan) {
            // ** Look for potential issues
            $warnings = $this->scanSite($organizationId);
        }

        return new SiteStatus(
            $organizationId,
            $warnings ? SiteStatus::STATUS_EXISTING_WITH_WARNINGS : SiteStatus::STATUS_EXISTING,
            $siteInfos,
            $warnings
        );
    }

    /**
     * Set a new domain for the website.
     * If $redirect is true, also add a redirection from the former domain to the new one
     *
     * @param int $organizationId
     * @param string $newDomain
     * @param bool $redirect
     * @return int
     * @throws InvalidWebsiteConfigurationException
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     */
    public function setSiteCustomDomainAction(int $organizationId, string $newDomain, bool $redirect = true): int
    {
        if (!preg_match(self::RX_DOMAIN,$newDomain) &&
            !preg_match("/[a-z0-9A-Z-]+\//", $newDomain)) {
            throw new \InvalidArgumentException("The given domain does not seems to be a valid domain: " . $newDomain);
        }

        $website = $this->otWebsiteRepository->getWebsiteByOrganizationId($organizationId);
        $websiteUid = $website['uid'];
        $rootUid = $this->otWebsiteRepository->getWebsiteRootUid($websiteUid);

        // ## Update the ot_website table
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('ot_websites');
        $queryBuilder->update('ot_websites')
            ->set('custom_domain', $newDomain)
            ->where($queryBuilder->expr()->eq('uid', $websiteUid))
            ->execute();

        $originDomain = $this->otWebsiteRepository->resolveWebsiteDomain($website);

        if (preg_replace('/https?:\/\//', '', $originDomain) === preg_replace('/https?:\/\//', '', $newDomain) ) {
            throw new RuntimeException('The new domain should be different of the current one');
        }

        $this->writeConfigFile($organizationId, $rootUid, $newDomain);

        if ($redirect) {
            // Add the redirection
            try {
                $this->addRedirection($originDomain, $newDomain);
            } catch (\RuntimeException $e) {} // silent the error until this https://assistance.opentalent.fr/browse/V8-3344
        }

        OtCacheManager::clearSiteCache($rootUid, true);
        return $rootUid;
    }

    /**
     * Return all the redirections from the given domain name,
     * even if they have been marked as deleted.
     *
     * @param $domain string Domain name, without the http(s):// part
     * @return array Rows from the sys_redirect table
     * @throws Exception
     */
    protected function getRedirectionsFrom(string $domain): array
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_redirect');
        $queryBuilder->getRestrictions()->removeAll();
        return $queryBuilder
            ->select('*')
            ->from('sys_redirect')
            ->where($queryBuilder->expr()->eq('source_host', $queryBuilder->expr()->literal($domain)))
            ->execute()
            ->fetchAllAssociative();
    }

    /**
     * Return all the redirections to the given domain name,
     * even if they have been marked as deleted.
     *
     * @param $domain string Domain name, without the http(s):// part
     * @return array Rows from the sys_redirect table
     */
    protected function getRedirectionsTo(string $domain): array
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_redirect');
        $queryBuilder->getRestrictions()->removeAll();
        /** @noinspection HttpUrlsUsage */
        return $queryBuilder
            ->select('*')
            ->from('sys_redirect')
            ->where(
                $queryBuilder->expr()->in(
                    'target',
                    [
                        'http://' . $queryBuilder->expr()->literal($domain),
                        'https://' . $queryBuilder->expr()->literal($domain)
                    ]
                )
            )
            ->execute()
            ->fetchAllAssociative();
    }

    /**
     * Add a new redirection from $fromDomain to $toDomain.
     * If this redirection already exists but has been deleted and/or disabled, it will be restored and enabled
     * If a redirection already exists but is not deleted and targets another domain, a RuntimeException will be thrown.
     *
     * @param $fromDomain
     * @param $toDomain
     * @return int  Status of the operation
     * @throws Exception
     */
    public function addRedirection($fromDomain, $toDomain): int
    {
        $fromDomain = preg_replace('/https?:\/\//', '', $fromDomain);
        $toDomain = preg_replace('/https?:\/\//', '', $toDomain);

        if (!preg_match(self::RX_DOMAIN, $fromDomain)) {
            throw new \InvalidArgumentException("The does not seems to be a valid domain: " . $fromDomain);
        }
        if (!preg_match(self::RX_DOMAIN, $toDomain)) {
            throw new \InvalidArgumentException("The does not seems to be a valid domain: " . $toDomain);
        }

        $existing = $this->getRedirectionsFrom($fromDomain);
        $toUpdate = null;

        foreach ($existing as $redirection) {
            if (!$redirection['deleted'] && !$redirection['disabled']) {
                // a redirection from this domain already exists, and it is not deleted nor disabled

                // check if the target is not the same domain
                if (!preg_match('/https?:\/\/' . $toDomain . '/', $redirection['target'])) {
                    throw new RuntimeException(
                        'A redirection is already active for ' . $fromDomain . ' targeting ' . $redirection['target']
                    );
                }

                // else, target is already the same domain, it will be updated
                $toUpdate = $redirection;
                break;

            }

            // a redirection from this domain already exists, but it is deleted or disabled
            if (preg_match('/https?:\/\/' . $toDomain . '/', $redirection['target'])) {
                // the target is the same domain, we'll reactivate this record
                $toUpdate = $redirection;
                break;
            }
        }

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_redirect');

        $data = [
            'deleted' => 0,
            'disabled' => 0,
            'source_host' => $fromDomain,
            'source_path' => '/.*/',
            'is_regexp' => 1,
            'force_https' => 0,
            'respect_query_parameters' => 0,
            'keep_query_parameters' => 1,
            'target' => 'https://' . $toDomain,
            'target_statuscode' => 301
        ];

        if ($toUpdate !== null) {
            $q = $queryBuilder
                ->update('sys_redirect')
                ->where($queryBuilder->expr()->eq('uid', $toUpdate['uid']));
            foreach ($data as $k => $v) {
                $q->set($k, $v);
            }
            $q->execute();
            return self::REDIRECTION_UPDATED;

        }

        $queryBuilder
            ->insert('sys_redirect')
            ->values($data)
            ->execute();
        return self::REDIRECTION_CREATED;
    }

    /**
     * Remove any existing redirection from $fromDomain to any url by marking it as deleted.
     * If $hard is true, delete it completely.
     *
     * @param $fromDomain
     * @param bool $hard
     * @return int Number of affected rows
     * @throws Exception
     */
    public function removeRedirectionsFrom($fromDomain, bool $hard=false): int
    {
        $fromDomain = preg_replace('/https?:\/\//', '', $fromDomain);
        if (!preg_match(self::RX_DOMAIN, $fromDomain)) {
            throw new \InvalidArgumentException("The does not seems to be a valid domain: " . $fromDomain);
        }

        $existing = $this->getRedirectionsFrom($fromDomain);
        $deleted = 0;
        foreach ($existing as $redirection) {
            $this->delete('sys_redirect', 'uid', $redirection['uid'], $hard);
            ++$deleted;
        }
        return $deleted;
    }

    /**
     * Set the rights of admin and editors of the website
     * on all the existing pages, including deleted ones
     *
     * @param int $organizationId
     * @param bool $createIfMissing  Create the admin be user and/or the editors group if they are not found in the DB
     * @param int|null $editorsGroupUid Force the editors be-group uid
     * @param int|null $adminUid Force the admin be-user uid
     * @return int   The uid of the website root page
     * @throws NoSuchWebsiteException
     * @throws NoSuchRecordException
     */
    protected function setBeUserPerms(
        int $organizationId,
        bool $createIfMissing = false,
        int $editorsGroupUid = null,
        int $adminUid = null
    ): int
    {
        if ($createIfMissing && ($editorsGroupUid || $adminUid)) {
            throw new \InvalidArgumentException("You can not set $createIfMissing to true " .
                                                        "and force the admin or group uid at the same time.");
        }

        $rootUid = $this->otWebsiteRepository->findRootUidForOrganization($organizationId);

        $organizationExtraData = $this->fetchOrganizationExtraData($organizationId);
        $isPremium = self::IS_PRODUCT_PREMIUM[$organizationExtraData['admin']['product']];

        if ($editorsGroupUid === null) {
            try {
                $editorsGroupUid = $this->findEditorsBeGroupUid($rootUid);
            } catch (NoSuchRecordException $e) {
                if (!$createIfMissing) {
                    throw $e;
                }
            }
        }

        if ($adminUid === null) {
            try {
                $adminUid = $this->findAdminBeUserUid($rootUid);
            } catch (NoSuchRecordException $e) {
                if (!$createIfMissing) {
                    throw $e;
                }
            }
        }

        // Creates or update the admin be_group
        $editorsGroupUid = $this->createOrUpdateBeGroup(
            $organizationId,
            $rootUid,
            $organizationExtraData['admin'],
            $editorsGroupUid
        );

        // Creates or update the admin be_user
        $adminUid = $this->createOrUpdateBeUser(
            $organizationId,
            $editorsGroupUid,
            $organizationExtraData['admin'],
            $adminUid
        );

        // Reset the appartenance to groups
        $adminGroupUid = $this->getBaseBeGroupUid($isPremium ? self::BEGROUP_ADMIN_PREMIUM : self::BEGROUP_ADMIN_STANDARD);
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_users');
        $queryBuilder
            ->update('be_users')
            ->where($queryBuilder->expr()->eq('uid', $adminUid))
            ->set('usergroup', $adminGroupUid . ',' . $editorsGroupUid)
            ->execute();

        $mainEditorGroupUid = $this->getBaseBeGroupUid($isPremium ? self::BEGROUP_EDITOR_PREMIUM : self::BEGROUP_EDITOR_STANDARD);
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_groups');
        $queryBuilder
            ->update('be_groups')
            ->where($queryBuilder->expr()->eq('uid', $editorsGroupUid))
            ->set('subgroup', $mainEditorGroupUid)
            ->execute();

        // setup default owner for the website
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $tsConfig = $queryBuilder->select('TSconfig')
                                ->from('pages')
                                ->where($queryBuilder->expr()->eq('uid', $rootUid))
                                ->execute()
                                ->fetchOne();

        $tsConfig = trim(preg_replace('/TCEMAIN {[^{]*}/', '', $tsConfig));

        $tsConfig .= "\nTCEMAIN {\n" .
                     "  permissions.userid = " . $adminUid ."\n" .
                     "  permissions.groupid = " . $editorsGroupUid . "\n" .
                     "}";

        $queryBuilder
            ->update('pages')
            ->where($queryBuilder->expr()->eq('uid', $rootUid))
            ->set('TSconfig', $tsConfig)
            ->execute();

        // fetch pages and root page
        $pages = $this->otPageRepository->getPageWithSubpages($rootUid);

        // To understand how the rights levels are computed:
        // @see https://ressources.opentalent.fr/display/EX/Droits+des+BE+Users
        foreach ($pages as $page) {

            if ($page['is_siteroot']) {

                $adminPerms = self::PERM_SHOW + self::PERM_EDIT_CONTENT + self::PERM_EDIT_PAGE;
                if ($isPremium)  {
                    $adminPerms += self::PERM_NEW;
                }
                $editorsPerms = self::PERM_SHOW + self::PERM_EDIT_CONTENT;

            } else if (
                $page['slug'] === '/footer' ||
                $page['tx_fed_page_controller_action'] === 'OpenTalent.OtTemplating->legal' ||
                $page['tx_fed_page_controller_action'] === 'OpenTalent.OtTemplating->contact' ||
                $page['slug'] === '/plan-du-site'
            ) {
                $adminPerms = self::PERM_SHOW;
                if ($isPremium)  {
                    $adminPerms += self::PERM_NEW;
                }
                $editorsPerms = self::PERM_SHOW;

            } else if (
                $page['tx_fed_page_controller_action'] === 'OpenTalent.OtTemplating->members' ||
                $page['tx_fed_page_controller_action'] === 'OpenTalent.OtTemplating->membersCa' ||
                $page['tx_fed_page_controller_action'] === 'OpenTalent.OtTemplating->structures' ||
                $page['tx_fed_page_controller_action'] === 'OpenTalent.OtTemplating->events' ||
                $page['tx_fed_page_controller_action'] === 'OpenTalent.OtTemplating->structuresEvents'
            ) {
                $adminPerms = self::PERM_SHOW;
                if ($isPremium)  {
                    $adminPerms += self::PERM_NEW + self::PERM_EDIT_PAGE;
                }
                $editorsPerms = self::PERM_SHOW;

            } else {
                $adminPerms = self::PERM_SHOW + self::PERM_EDIT_CONTENT + self::PERM_EDIT_PAGE;
                if ($isPremium)  {
                    $adminPerms += self::PERM_DELETE + self::PERM_NEW;
                }

                $editorsPerms = self::PERM_SHOW + self::PERM_EDIT_CONTENT + self::PERM_EDIT_PAGE;
                if ($isPremium)  {
                    $editorsPerms += self::PERM_NEW;
                }
            }

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
            $queryBuilder
                ->update('pages')
                ->where($queryBuilder->expr()->eq('uid', $page['uid']))
                ->set('perms_userid', $adminUid)
                ->set('perms_groupid', $editorsGroupUid)
                ->set('perms_user', $adminPerms)
                ->set('perms_group', $editorsPerms)
                ->set('perms_everybody', 0)
                ->execute();
        }

        return $rootUid;
    }

    /**
     * Action for resetting the rights of admin and editors of the website
     * on all the existing pages, including deleted ones
     *
     * @param int $organizationId
     * @param bool $createIfMissing
     * @return int
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     * @throws \Throwable
     */
    public function resetBeUserPermsAction(int $organizationId, bool $createIfMissing = false): int
    {
        $this->connectionPool->getConnectionByName('Default')->beginTransaction();
        try {
            $rootUid = $this->setBeUserPerms($organizationId, $createIfMissing);

            $commitSuccess = $this->connectionPool->getConnectionByName('Default')->commit();
            if (!$commitSuccess) {
                throw new RuntimeException('Something went wrong while commiting the result');
            }
            return $rootUid;
        }
        catch (\Throwable $e) {
            // rollback
            $this->connectionPool->getConnectionByName('Default')->rollback();
            throw $e;
        }
    }

    /**
     * Delete then regenerate all the typo3 sites yaml config files
     *
     * This is a more efficient alternative to the update --all, designed to be executed on a development environment
     * just after the databases cloning.
     *
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     * @throws \Doctrine\DBAL\ConnectionException
     * @throws \Doctrine\DBAL\DBALException
     * @throws InvalidWebsiteConfigurationException
     * @throws \Throwable
     */
    public function regenConfigFilesAction(): void
    {
        $configRootDir = $_ENV['TYPO3_PATH_APP'] . "/config/sites/";
        $backupConfigDir = $_ENV['TYPO3_PATH_APP'] . "/config/bkp_sites";

        if (file_exists($backupConfigDir)) {
            throw new RuntimeException(
                'A directory or a file with this name already exist: ' . $backupConfigDir . '. Delete it before continuing.'
            );
        }

        $this->connectionPool->getConnectionByName('Default')->beginTransaction();
        try {
            // archive the existing files, in case a rollback is needed
            rename($configRootDir, $backupConfigDir);
            $this->mkDir($configRootDir);

            $websites = $this->otWebsiteRepository->getAll();

            foreach ($websites as $website) {
                if (!((int)$website['organization_id'] > 0)) {
                    continue;
                }

                $identifier = str_replace('/', '', $website['subdomain']) . '_' . $website['organization_id'];
                $configDir = $configRootDir . $identifier;
                $configFilename = $configDir . "/config.yaml";

                $siteConfig = $this->otWebsiteRepository->generateWebsiteConfiguration($website, $identifier);

                $config = $siteConfig->getConfiguration();

                $yamlConfig = Yaml::dump($config, 99, 2);

                try {
                    $this->mkDir($configDir);
                } catch (\Throwable $e) {
                    throw new RuntimeException('Unable to create the directory ' . $configDir, 0, $e);
                }
                $this->writeFile($configFilename, $yamlConfig);

                // ## Update the ot_website identifier
                $queryBuilder = $this->connectionPool->getQueryBuilderForTable('ot_websites');
                $queryBuilder->update('ot_websites')
                    ->set('config_identifier', $identifier)
                    ->where($queryBuilder->expr()->eq('uid', $website['uid']))
                    ->execute();
            }

            $commitSuccess = $this->connectionPool->getConnectionByName('Default')->commit();
            if (!$commitSuccess) {
                throw new RuntimeException('Something went wrong while committing the result');
            }
            $this->rrmdir($backupConfigDir);
        }
        catch (\Throwable $e) {
            // rollback
            if (!file_exists($configRootDir)) {
                $this->rrmdir($configRootDir);
            }
            if (!file_exists($backupConfigDir)) {
                rename($backupConfigDir, $configRootDir);
            }
            $this->connectionPool->getConnectionByName('Default')->rollback();
            throw $e;
        }
    }

    /**
     * Retrieve the Organization object from the repository and then,
     * from the Opentalent API
     *
     * @param $organizationId
     * @return Organization
     * @throws NoSuchOrganizationException
     */
    private function fetchOrganization($organizationId): object
    {
        try {
            return $this->organizationRepository->findById($organizationId);
        } catch (ApiRequestException $e) {
            throw new NoSuchOrganizationException('Unable to fetch the organization with id: ' . $organizationId);
        }
    }

    /**
     * Insert a new row in the 'pages' table of the Typo3 DB
     * and return its uid
     *
     * @param Organization $organization
     * @return int
     */
    private function insertOtWebsite(Organization $organization): int
    {
        $values = [
            'organization_id' => $organization->getId(),
            'subdomain' => $organization->getSubDomain(),
            'locale' => 'fr_FR',
            'organization_name' => $organization->getName()
        ];

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('ot_websites');
        $queryBuilder->insert('ot_websites')
            ->values($values)
            ->execute();

        return (int)$queryBuilder->getConnection()->lastInsertId();
    }

    /**
     * Determine which folder-type Typo3 page should contain the new website
     * CREATES IT if needed, and return its uid
     *
     * @return int
     */
    private function getParentFolderUid(): int
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $siteCount = $queryBuilder
            ->count('uid')
            ->from('pages')
            ->where('is_siteroot=1')
            ->execute()
            ->fetchOne();

        $thousand = (int)(($siteCount + 1) / 1000);

        $folderName = "Web Sites " . (1000 * $thousand) . " - " . ((1000 * $thousand) + 999);

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $uid = $queryBuilder
            ->select('uid')
            ->from('pages')
            ->where($queryBuilder->expr()->eq('title', $queryBuilder->createNamedParameter($folderName)))
            ->andWhere('dokType=254')
            ->execute()
            ->fetchOne();

        if ($uid === null) {
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
            $queryBuilder->insert('pages')
                ->values([
                    'pid' => 0,
                    'title' => $folderName,
                    'dokType' => 254,
                    'sorting' => 11264,
                    'perms_userid' => 1,
                    'perms_groupid' => 31,
                    'perms_group' => 27,

                ])
                ->execute();
            $uid = $queryBuilder->getConnection()->lastInsertId();
        }
        return $uid;
    }

    /**
     * Insert a new row in the 'pages' table of the Typo3 DB
     * and return its uid
     *
     * @param int $website_uid
     * @param int $pid
     * @param string $title
     * @param string $slug
     * @param string $template
     * @param array $moreValues
     * @return int
     */
    private function insertPage(int $website_uid,
                                int $pid,
                                string $title,
                                string $slug,
                                string $template = '',
                                array $moreValues = []
                                ): int
    {
        $defaultValues = [
            'pid' => $pid,
            'perms_groupid' => 3,
            'perms_user' => 27,
            'cruser_id' => 1,
            'dokType' => self::DOK_PAGE,
            'title' => $title,
            'slug' => $slug,
            'backend_layout' => 'flux__grid',
            'backend_layout_next_level' => 'flux__grid',
            'ot_website_uid' => $website_uid,
        ];

        if ($template) {
            $defaultValues['tx_fed_page_controller_action'] = $template;
            $defaultValues['tx_fed_page_controller_action_sub'] = self::TEMPLATE_1COL;
        }

        $values = array_merge($defaultValues, $moreValues);

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $queryBuilder->insert('pages')
            ->values($values)
            ->execute();

        $uid = (int)$queryBuilder->getConnection()->lastInsertId();

        $this->createdPagesIndex[$slug] = $uid;
        return $uid;
    }

    /**
     * Insert the root page of a new organization's website
     * and return its uid
     *
     * @param int $website_uid
     * @param string $title
     * @return int
     */
    private function insertRootPage(int $website_uid, string $title): int
    {
        return $this->insertPage(
            $website_uid,
            $this->getParentFolderUid(),
            $title,
            '/',
            self::TEMPLATE_HOME,
            [
                'is_siteroot' => 1,
                'TSconfig' => 'TCAdefaults.pages.ot_website_uid=' . $website_uid
            ]
        );
    }

    /**
     * Insert a new row in the 'tt_content' table of the Typo3 DB
     *
     * @param int $pid
     * @param string $cType
     * @param string $bodyText
     * @param int $colPos
     * @param array $moreValues
     */
    private function insertContent(int $pid,
                                   string $cType=self::CTYPE_TEXT,
                                   string $bodyText = '',
                                   int $colPos=0,
                                   array $moreValues = []): void
    {
        $defaultValues = [
            'pid' => $pid,
            'cruser_id' => 1,
            'CType' => $cType,
            'colPos' => $colPos,
            'bodyText' => $bodyText
        ];

        $values = array_merge($defaultValues, $moreValues);

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
        $queryBuilder->insert('tt_content')
            ->values($values)
            ->execute();
    }

    private function fetchOrganizationExtraData(int $organizationId) {
        $cnn = new PDO(
            "mysql:host=" . OpentalentEnvService::get('DB_HOST') . ";dbname=opentalent",
            OpentalentEnvService::get('DB_USER'),
            OpentalentEnvService::get('DB_PASSWORD'),
            array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8')
        );
        $cnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $cnn->query(
            "SELECT o.id, o.name, o.facebook, o.twitter, o.instagram, o.youtube,
                                 o.category, o.logo_id, p.logoDonorsMove
                          FROM opentalent.Organization o INNER JOIN opentalent.Parameters p 
                          ON o.parameters_id = p.id
                          WHERE o.id=" . $organizationId . ";"
        );
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data = $stmt->fetch();

        $stmt = $cnn->query(
            "SELECT c.email 
                          FROM opentalent.ContactPoint c
                            INNER JOIN opentalent.organization_contactpoint o 
                            ON o.contactPoint_id = c.id
                          WHERE c.contactType = 'PRINCIPAL' 
                            AND o.organization_id = " . $organizationId . ";"
        );
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data['email'] = $stmt->fetch()['email'];

        $stmt = $cnn->query(
            "SELECT n.name, n.logo, n.url 
                          FROM opentalent.Network n
                          INNER JOIN 
                              (opentalent.NetworkOrganization l
                              INNER JOIN opentalent.Organization o
                              ON l.organization_id = o.id)
                          ON l.network_id = n.id
                          WHERE l.endDate is NULL
                            AND o.id=" . $organizationId . ";"
        );
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data['network'] = $stmt->fetch();

        $stmt = $cnn->query(
            "SELECT p.username, p.id as person_id, s.product, p.password
                      FROM opentalent.Person p 
                        INNER JOIN opentalent.Access a ON p.id = a.person_id
                        INNER JOIN opentalent.Settings s on a.organization_id = s.organization_id
                      where a.organization_id=" . $organizationId . " AND  a.adminAccess=1;"
        );
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data['admin'] = $stmt->fetch();

        return $data;
    }

    /**
     * Return the content of `sys_template`.`constants` of
     * the website of the given organization
     *
     * @param int $organizationId
     * @param array $organizationExtraData
     * @return string
     */
    private function getTemplateConstants(int $organizationId, array $organizationExtraData): string
    {
        return "plugin.tx_ottemplating {\n" .
            "    settings {\n" .
            "        organization {\n" .
            "            id = " . $organizationId . "\n" .
            "            name = " . $organizationExtraData['name'] . "\n" .
            "            is_network = " . ($organizationExtraData['category'] === 'NETWORK' ? '1' : '0') . "\n" .
            "            email = " . $organizationExtraData['email'] . "\n" .
            "            logoid = " . $organizationExtraData['logo_id'] . "\n" .
            "            twitter = " . $organizationExtraData['twitter'] . "\n" .
            "            facebook = " . $organizationExtraData['facebook'] . "\n" .
            "            instagram = " . $organizationExtraData['instagram'] . "\n" .
            "            youtube = " . $organizationExtraData['youtube'] . "\n" .
            "        }\n" .
            "    network {\n" .
            "            logo = " . $organizationExtraData['network']['logo'] . "\n" .
            "            name = " . $organizationExtraData['network']['name'] . "\n" .
            "            url = " . $organizationExtraData['network']['url'] . "\n" .
            "        }\n" .
            "    }\n" .
            "}";
    }

    /**
     * Create the given directory, give its property to the www-data group and
     * record it as a newly created dir (for an eventual rollback)
     *
     * @param string $dirPath
     */
    private function mkDir(string $dirPath): void
    {
        if (!mkdir($dirPath) && !is_dir($dirPath)) {
            throw new RuntimeException(sprintf('Directory "%s" was not created', $dirPath));
        }
        $this->createdDirs[] = $dirPath;
        chgrp($dirPath, 'www-data');
    }

    /**
     * Write the given file with content, give its property to the www-data group and
     * record it as a newly created file (for an eventual rollback)
     *
     * @param string $path
     * @param string $content
     */
    private function writeFile(string $path, string $content): void
    {
        $f = fopen($path, 'wb');
        try
        {
            fwrite($f, $content);
            $this->createdFiles[] = $path;
            try {
                chgrp($path, 'www-data');
            } catch (\Throwable $e) {
            }
        } finally {
            fclose($f);
        }
    }

    /**
     * Create or update the .../sites/.../config.yaml file of the given site
     * Return the identifier of the created website
     *
     * @param int $rootUid
     * @param bool $forceRecreate
     * @param null $identifier
     * @return string Identifier of the newly created configuration file
     * @throws InvalidWebsiteConfigurationException
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     */
    private function writeConfigFile(int $rootUid, bool $forceRecreate = false, $identifier = null): string
    {
        $website = $this->otWebsiteRepository->getWebsiteByPageUid($rootUid);
        $domain = $this->otWebsiteRepository->resolveWebsiteDomain($website);

        try {
            $existing = $this->otWebsiteRepository->findConfigFileAndContentFor($rootUid, $identifier);
        } catch (RuntimeException $e) {
            // identifier might be obsolete
            $existing = $this->otWebsiteRepository->findConfigFileAndContentFor($rootUid);
        }

        [$configFilename, $config] = $existing;

        if (!$configFilename) {
            $identifier = $website['subdomain'] . '_' . $website['organization_id'];
            $configDir = $_ENV['TYPO3_PATH_APP'] . "/config/sites/" . $identifier;
            $configFilename = $configDir . "/config.yaml";
            $isNew = true;

            if (file_exists($configFilename)) {
                throw new RuntimeException("A file named " . $configFilename . " already exists. Abort.");
            }
        } else {
            $configDir = dirname($configFilename);
            if ($identifier === null) {
                $identifier = basename($configDir);
            }
            $config['base'] = 'https://' . $domain;
            $isNew = false;
        }

        if ($isNew || $forceRecreate) {
            $siteConfig = $this->otWebsiteRepository->generateWebsiteConfiguration($website, $identifier);
            $config = $siteConfig->getConfiguration();
        }

        $yamlConfig = Yaml::dump($config, 99, 2);

        if (!file_exists($configDir)) {
            $this->mkDir($configDir);
        }
        $this->writeFile($configFilename, $yamlConfig);

        // Set the owner and mods, in case www-data is not the one who run this command
        // @see https://www.php.net/manual/fr/function.stat.php
        try {
            $stats = stat($_ENV['TYPO3_PATH_APP'] . '/public/index.php');
            chown($configFilename, $stats['4']);
            chgrp($configFilename, $stats['5']);
            chmod($configFilename, $stats['2']);
        } catch (\Throwable $e) {
        }

        // Flush cache:
        try {
            $cacheSystem = $this->cacheManager->getCache('cache_core');
            $cacheSystem->remove('site-configuration');
            $cacheSystem->remove('pseudo-sites');
        } catch (NoSuchCacheException $e) {
        }

        return $identifier;
    }

    /**
     * Recursively remove the target directory (! no rollback available)
     */
    private function rrmdir(string $dir): bool
    {
        if (!is_dir($dir) || is_link($dir)) {
            return unlink($dir);
        }
        foreach (scandir($dir) as $file) {
            if ($file === '.' || $file === '..') {
                continue;
            }
            if (!$this->rrmdir($dir . DIRECTORY_SEPARATOR . $file)) {
                chmod($dir . DIRECTORY_SEPARATOR . $file, 0777);
                if (!$this->rrmdir($dir . DIRECTORY_SEPARATOR . $file)) {
                    return false;
                }
            }
        }
        return rmdir($dir);
    }

    /**
     * Create the BE user for the website, then return its uid
     * The user shall be already created in the Opentalent DB
     *
     * @param int $organizationId
     * @param int $siteGroupUid
     * @param array $userData
     * @param int|null $updateUid If passed, this method will update this be user instead of creating a new one
     * @return int The uid of the created be_user
     * @throws InvalidPasswordHashException
     */
    private function createOrUpdateBeUser(int $organizationId,
                                          int $siteGroupUid,
                                          array $userData,
                                          int $updateUid = null): int
    {

        if (!isset($userData['username'])) {
            throw new RuntimeException('Can not find any user with admin access in the Opentalent DB. Abort.');
        }

        $hashInstance = GeneralUtility::makeInstance(PasswordHashFactory::class)->getDefaultHashInstance('BE');
        $hashedPassword = $hashInstance->getHashedPassword($userData['password']);

        $adminGroup = self::IS_PRODUCT_PREMIUM[$userData['product']] ? self::BEGROUP_ADMIN_PREMIUM : self::BEGROUP_ADMIN_STANDARD;
        $adminGroupUid = $this->getBaseBeGroupUid($adminGroup);

        $values = [
            'username' => $userData['username'],
            'password' => $hashedPassword,
            'description' => '[Auto-generated] BE Admin for organization ' . $organizationId,
            'deleted' => 0,
            'lang' => 'fr',
            'usergroup' => $siteGroupUid . ',' . $adminGroupUid,
            'userMods' => null,   // inherited from the base AdminGroup
            'db_mountpoints' => null, // inherited from the editors group
            'file_mountpoints' => null, // inherited from the editors group
            'options' => 3,  // allow to inherit both db and file mountpoints from groups
            'tx_opentalent_opentalentId' => $userData['id'],
            'tx_opentalent_organizationId' => $organizationId,
            'tx_opentalent_generationDate' => date('Y/m/d H:i:s')
        ];

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_users');
        if ($updateUid !== null) {
            $q = $queryBuilder->update('be_users')->where($queryBuilder->expr()->eq('uid', $updateUid));
            foreach ($values as $k => $v) {
                $q->set($k, $v);
            }
            $q->execute();

            return $updateUid;
        }

        $queryBuilder->insert('be_users')
            ->values($values)
            ->execute();

        return (int)$queryBuilder->getConnection()->lastInsertId();
    }

    /**
     * Create the BE editors group for the website, then return its uid
     *
     * @param int $organizationId
     * @param int $rootUid
     * @param array $userData
     * @param int|null $updateUid If passed, this method will update this be_group instead of creating a new one
     * @return int The uid of the created be_group
     */
    private function createOrUpdateBeGroup(int $organizationId,
                                  int $rootUid,
                                  array $userData,
                                  int $updateUid = null): int
    {
        $groupName = 'editors_' . $organizationId;

        // get the existing filemounts
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_filemounts');
        $queryBuilder
            ->select('uid')
            ->from('sys_filemounts')
            ->where("path LIKE '%user_upload/" . $organizationId . "/'")
            ->orWhere("path LIKE '%form_definitions/" . $organizationId . "/'");
        $statement = $queryBuilder->execute();
        $rows = $statement->fetchAllAssociative() ?: [];
        $files = [];
        foreach ($rows as $row) {
            $files[] = $row[0];
        }

        $mainGroup = self::IS_PRODUCT_PREMIUM[$userData['product']] ? self::BEGROUP_EDITOR_PREMIUM : self::BEGROUP_EDITOR_STANDARD;
        $mainGroupUid = $this->getBaseBeGroupUid($mainGroup);

        $values = [
            'title' => $groupName,
            'deleted' => 0,
            'subgroup' => $mainGroupUid,
            'db_mountpoints' => $rootUid,
            'file_mountPoints' => implode(',', $files),
            'file_permissions' => 'readFolder,writeFolder,addFolder,renameFolder,moveFolder,deleteFolder,readFile,writeFile,addFile,renameFile,replaceFile,moveFile,copyFile,deleteFile',
            'groupMods' => '',   // inherited from the base EditorsGroup
            'pagetypes_select' => '',   // inherited from the base EditorsGroup
            'tables_select' => '',   // inherited from the base EditorsGroup
            'tables_modify' => '',   // inherited from the base EditorsGroup
            'non_exclude_fields' => '',   // inherited from the base EditorsGroup
        ];

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_groups');
        if ($updateUid !== null) {
            $q = $queryBuilder->update('be_groups')->where($queryBuilder->expr()->eq('uid', $updateUid));
            foreach ($values as $k => $v) {
                $q->set($k, $v);
            }
            $q->execute();

            return $updateUid;
        }

        $queryBuilder->insert('be_groups')
            ->values($values)
            ->execute();

        return $queryBuilder->getConnection()->lastInsertId();
    }

    /**
     * Try to find and return the uid of the editors be_group
     * for this website
     *
     * @param int $rootUid
     * @param bool $withRestrictions If false, the default restrictions won't apply, meaning this could return a deleted record
     * @return int
     * @throws Exception
     * @throws NoSuchRecordException
     */
    protected function findEditorsBeGroupUid(int $rootUid, bool $withRestrictions=true): int {
        $editorsGroups = [
            $this->getBaseBeGroupUid(self::BEGROUP_EDITOR_STANDARD),
            $this->getBaseBeGroupUid(self::BEGROUP_EDITOR_PREMIUM)
        ];

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_groups');
        if (!$withRestrictions) {
            $queryBuilder->getRestrictions()->removeAll();
        }
        $editorsGroupUid = $queryBuilder
            ->select('uid')
            ->from('be_groups')
            ->where('FIND_IN_SET(' . $rootUid . ', db_mountpoints) > 0')
            ->andWhere('(FIND_IN_SET(' . $editorsGroups[0] . ', subgroup) > 0 OR FIND_IN_SET(' . $editorsGroups[1] . ', subgroup) > 0)')
            ->execute()
            ->fetchOne();

        if ($editorsGroupUid === null) {
            throw new NoSuchRecordException("No editors be_group found " .
                "among the groups that have this website mounted (root uid: " . $rootUid . ")");
        }

        return $editorsGroupUid;
    }

    /**
     * Try to find and return the uid of the admin be_user
     * for this website
     *
     * @param int $rootUid
     * @param bool $withRestrictions If false, the default restrictions won't apply, meaning this could return a deleted record
     * @return int
     * @throws NoSuchRecordException
     * @throws NoSuchWebsiteException
     */
    protected function findAdminBeUserUid(int $rootUid, bool $withRestrictions=true): int {
        $adminGroups = [
            $this->getBaseBeGroupUid(self::BEGROUP_ADMIN_STANDARD),
            $this->getBaseBeGroupUid(self::BEGROUP_ADMIN_PREMIUM)
        ];

        $adminUid = null;

        try {
            $editorsGroupUid = $this->findEditorsBeGroupUid($rootUid);

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_users');
            if (!$withRestrictions) {
                $queryBuilder->getRestrictions()->removeAll();
            }
            return $queryBuilder
                ->select('uid')
                ->from('be_users')
                ->where('FIND_IN_SET(' . $editorsGroupUid . ', usergroup) > 0')
                ->andWhere('(FIND_IN_SET(' . $adminGroups[0] . ', usergroup) > 0 OR FIND_IN_SET(' . $adminGroups[1] . ', usergroup) > 0)')
                ->execute()
                ->fetchOne();

        } catch (NoSuchRecordException $e) {
            // the editors group does not exist
        }

        // [For retro-compatibility] Try to find if there is a be_user still in the v8.7 data format
        if ($adminUid === null) {
            $website = $this->otWebsiteRepository->getWebsiteByPageUid($rootUid);

            $extraData = $this->fetchOrganizationExtraData($website['organization_id']);
            $expectedUsername = $extraData['admin']['username'];

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_users');
            if (!$withRestrictions) {
                $queryBuilder->getRestrictions()->removeAll();
            }
            $row = $queryBuilder
                ->select('uid', 'db_mountpoints')
                ->from('be_users')
                ->where($queryBuilder->expr()->eq('username', $queryBuilder->expr()->literal($expectedUsername)))
                ->execute()
                ->fetchAssociative();

            if ($row['uid']) {
                if ((string)$rootUid !== (string)$row['db_mountpoints']) {
                    throw new RuntimeException(
                        "The be_user named '" . $expectedUsername .
                        "' has unexpected mounted website(s) (expected: " . $rootUid .
                        ", found: " . $row['db_mountpoints'] . "). Abort."
                    );
                }
                $adminUid = $row['uid'];
            }
        }

        if ($adminUid === null) {
            throw new NoSuchRecordException("No admin be_user found " .
                "among the users that have this website mounted (root uid: " . $rootUid . ")");
        }

        return $adminUid;
    }

    /**
     * Return the uid of one of the base groups (BEGROUP_EDITOR_STANDARD,  BEGROUP_EDITOR_PREMIUM, ...)
     *
     * @param int $groupType
     * @return int
     */
    protected function getBaseBeGroupUid(int $groupType): int
    {
        $expectedName = self::BEGROUP_NAME[$groupType];

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('be_groups');
        $uid = $queryBuilder
            ->select('uid')
            ->from('be_groups')
            ->where($queryBuilder->expr()->eq('title', $queryBuilder->expr()->literal($expectedName)))
            ->execute()
            ->fetchOne();

        if (!$uid) {
            throw new RuntimeException("Expects a BE group named '" . $expectedName . "', but none was found.");
        }
        return $uid;
    }

    /**
     * Enable frontend editing for user
     *
     * @param int $adminUid
     * @noinspection PhpInternalEntityUsedInspection
     */
    private function enableFeEditing(int $adminUid): void
    {
        $BE_USER = GeneralUtility::makeInstance(BackendUserAuthentication::class);
        $user = $BE_USER->getRawUserByUid($adminUid);
        $BE_USER->user = $user;
        $BE_USER->backendSetUC();
        $BE_USER->uc['frontend_editing'] = 1;
        $BE_USER->uc['frontend_editing_overlay'] = 1;
        $BE_USER->writeUC($BE_USER->uc);
    }
}