ot_typo3/ot_admin/Classes/Controller/SiteController.php

<?php

namespace Opentalent\OtAdmin\Controller;

use http\Exception\RuntimeException;
use Opentalent\OtAdmin\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\Page\OtPageRepository;
use PDO;
use Symfony\Component\Yaml\Yaml;
use TYPO3\CMS\Core\Crypto\Random;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Object\ObjectManager;


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

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

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

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

    // BE rights
    const PRODUCT_MAPPING = [
        "school-standard" => 1, // Association writer basic
        "artist-standard" => 1, // Association writer basic
        "school-premium" => 3, // Association writer full
        "artist-premium" => 3, // Association writer full
        "manager" => 3, // Association writer full
    ];

    // Websites statuses
    const STATUS_NO_SUCH_WEBSITE = 0;
    const STATUS_EXISTING = 1;
    const STATUS_EXISTING_DELETED = 2;
    const STATUS_EXISTING_HIDDEN = 3;

    const MODE_PROD = 1;
    const MODE_DEV = 1;

    /**
     * @var \TYPO3\CMS\Core\Database\ConnectionPool
     */
    private $connectionPool;

    public function injectConnectionPool(\TYPO3\CMS\Core\Database\ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }

    /**
     * @var \TYPO3\CMS\Core\Cache\CacheManager
     */
    private $cacheManager;

    public function injectCacheManager(\TYPO3\CMS\Core\Cache\CacheManager $cacheManager)
    {
        $this->cacheManager = $cacheManager;
    }

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

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

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

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

    /**
     * Creates a new website for the given organization, and
     * returns the root page uid of the newly created site
     *
     * @param int $organizationId
     * @param int $mode Can be either MODE_PROD or MODE_DEV, MODE_PROD being the normal behaviour.
     *                    If MODE_DEV is used, sites urls will be of the form 'http://host/subdomain'
     *                    instead of 'http://subdomain/host'
     * @return int Uid of the root page of the newly created website
     * @throws \RuntimeException|\Exception
     */
    public function createSiteAction(int $organizationId, int $mode=self::MODE_PROD) {

        $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('pages')
            ->where($queryBuilder->expr()->eq('tx_opentalent_structure_id', $queryBuilder->createNamedParameter($organization->getId())))
            ->andWhere('is_siteroot=1')
            ->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 site pages:
            // > Root page
            $rootUid = $this->insertRootPage($organization);

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

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

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

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

            // > 'Présentation > Les membres du CA' page
            $this->insertPage(
                $organization,
                $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(
                    $organization,
                    $this->createdPagesIndex['/presentation'],
                    'Les sociétés adhérentes',
                    '/societes-adherentes',
                    self::TEMPLATE_STRUCTURES
                );
            }

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

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

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

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

            // > 'Saison en cours > Les évènements' page
            $this->insertPage(
                $organization,
                $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(
                    $organization,
                    $this->createdPagesIndex['/presentation'],
                    'Évènements des structures',
                    '/evenements-des-structures',
                    self::TEMPLATE_STRUCTURESEVENTS
                );
            }

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

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

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

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

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

            // > 'Présentation > Contact' shortcut
            $this->insertPage(
                $organization,
                $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(
                $organization,
                $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
            );

            // >> page 'mentions légales'
            $this->insertContent(
                $this->createdPagesIndex['/mentions-legales'],
                self::CTYPE_TEXT,
                '<p style="margin-bottom: 0"><b>Mentions Légales</b></p>',
                0
            );

            // Build and update the domain
            if ($mode == self::MODE_PROD) {
                $domain = $organization->getSubDomain() .  '.opentalent.fr';
            } elseif ($mode == self::MODE_DEV) {
                $domain = $organization->getSubDomain() . '/';
            } else {
                throw new RuntimeException('Unknown value for $mode: ' . $mode);
            }

            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_domain');
            $queryBuilder->insert('sys_domain')
                ->values([
                    'pid' => $rootUid,
                    'domainName' => $domain
                ])
                ->execute();

            // 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";

            $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
            $this->writeConfigFile($organizationId, $rootUid, $domain);

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

            $defaultUploadDir = $uploadDir . '/images';
            $formsDir = $uploadDir . '/Forms';

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

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

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

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

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

            // Give the keys of the website to this user (makes him the owner)
            $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
            foreach($this->createdPagesIndex as $slug => $uid) {
                $queryBuilder
                    ->update('pages')
                    ->where($queryBuilder->expr()->eq('uid', $uid))
                    ->set('perms_userid', $beUserUid)
                    ->execute();
            }

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


        } catch(\Exception $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;
    }

    /**
     * Update the `sys_template`.`constants` field of the given
     * organization's website with the data fetched from the opentalent DB.
     *
     * @param int $organizationId
     * @return int
     */
    public function updateSiteConstantsAction(int $organizationId) {
        $rootUid = $this->findRootUidFor($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);

        $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();
        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)
     *
     * @param int $organizationId
     * @param bool $hard
     * @return int
     * @throws \Exception
     */
    public function deleteSiteAction(int $organizationId, bool $hard=false) {
        $rootUid = $this->findRootUidFor($organizationId);

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $isDeleted = $queryBuilder
            ->select('deleted')
            ->from('pages')
            ->where($queryBuilder->expr()->eq('uid', $rootUid))
            ->execute()
            ->fetchColumn(0) == 1;

        $confirm_file = $_ENV['TYPO3_PATH_APP'] . "/DEL" . $organizationId;
        if ($hard && !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."
            );
        }

        // 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);

            if ($hard) {
                // there is no 'deleted' field in sys_domain
                $this->delete('sys_domain', 'pid', $rootUid, $hard);
            }

            // remove filemounts
            $this->delete('sys_filemounts',
                'path',
                "'/user_upload/" . $organizationId . "/images'",
                $hard);
            $this->delete('sys_filemounts',
                'path',
                "'/user_upload/" . $organizationId . "/Forms'",
                $hard);

            $this->delete('be_users', 'db_mountpoints', $rootUid, $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 . '/';
            } else {
                $uploadDir = $_ENV['TYPO3_PATH_APP'] . '/public/fileadmin/user_upload/deleted_' . $organizationId . '/';
            }

            // If hard deletion, verify that upload dirs are empty
            if ($hard && is_dir($uploadDir)) {
                foreach (scandir($uploadDir) as $subdir) {
                    if ($subdir == '.' or $subdir == '..') {
                        continue;
                    }
                    $subdir = $uploadDir . $subdir;
                    if (!is_dir($subdir)) {
                        throw new \RuntimeException(
                            'The directory ' . $uploadDir . ' 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 (!$hard) {
                    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))) {
                    rmdir(dirname($configYamlFile));
                }
                if (is_dir($uploadDir . 'images')) {
                    rmdir($uploadDir . 'images');
                }
                if (is_dir($uploadDir . 'Forms')) {
                    rmdir($uploadDir . 'Forms');
                }
                if (is_dir($uploadDir)) {
                    rmdir($uploadDir);
                }
            } else {
                $renamed = [];
                foreach ($toRename as $initialPath => $newPath) {
                    rename($initialPath, $newPath);
                    $renamed[$initialPath] = $newPath;
                }
            }

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

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

    /**
     * @param string $table
     * @param string $whereKey
     * @param $whereValue
     * @param int $hard
     */
    private function delete(string $table, string $whereKey, $whereValue, $hard=0) {
        $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();
        }
    }


    public function undeleteSiteAction(int $organizationId) {
        $rootUid = $this->findRootUidFor($organizationId);

        // 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) {
                $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 . "/images'"))
                ->execute();

            $queryBuilder
                ->update('sys_filemounts')
                ->set('deleted', 0)
                ->where($queryBuilder->expr()->eq('path', "'/user_upload/" . $organizationId . "/Forms'"))
                ->execute();

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

            // 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 . '/';

            $toRename = [];
            if (is_file($configYamlFile)) {
                $toRename[$configYamlFile] = dirname($configYamlFile) . '/config.yaml';
            }
            if (is_dir($uploadDir)) {
                $toRename[$uploadDir] = dirname($uploadDir) . '/' . $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;
            }

            // 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(\Exception $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, $clearAll=false): int
    {
        $rootUid = $this->findRootUidFor($organizationId);

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

        return $rootUid;
    }

    /**
     * Pack the status informations into an array for the getSiteStatusAction method
     *
     * @param int $organizationId
     * @param int $code
     * @param int|null $rootUid
     * @param array|null $warnings
     * @return array
     */
    private function buildStatusResult(
        int $organizationId,
        int $code,
        ?int $rootUid = null,
        ?array $warnings = null): array
    {
        $message = "";
        if($code == self::STATUS_NO_SUCH_WEBSITE) {
            $message = 'No website were found for organization ' . $organizationId;
        } elseif ($code == self::STATUS_EXISTING) {
            $message = 'A website exists for organization ' . $organizationId;
        }
        elseif ($code == self::STATUS_EXISTING_DELETED) {
            $message = 'A website exists for organization ' . $organizationId . ', but has been deleted';
        }
        elseif ($code == self::STATUS_EXISTING_HIDDEN) {
            $message = 'A website exists for organization ' . $organizationId . ', but is hidden or has a restricted access';
        }

        $title = '';
        if ($rootUid !== null) {
            $title = $this->otPageRepository->getPage($rootUid)['title'];
        }

        $status = [
            'organization_id' => $organizationId,
            'code' => $code,
            'message' => $message,
            'root_uid' => $rootUid,
            'title' => $title
        ];
        if ($warnings !== null) {
            $status['warnings'] = $warnings;
        }
        return $status;
    }

    /**
     * Perform a full scan of the website and returns a list of warnings
     *
     * @param int $organizationId
     * @param int $rootUid
     * @return array
     */
    private function scanSite(int $organizationId, int $rootUid) {

        $warnings = [];

        // 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()
            ->fetchAll();

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

        // scan pages
        $pages = $this->otPageRepository->getAllSitePages($rootUid);

        foreach ($pages as $page) {

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



        return $warnings;
    }

    /**
     * Get the current status of the organization's website
     *
     * The result is an array of the form:
     *
     *  [
     *     'organization_id' => int,
     *     'code' => int,
     *     'message' => string,
     *     'root_uid' => ?int,
     *     'site_title' => ?string,
     *     ('warnings' => array)
     *  ]
     *
     * If $statusOnly is true, the warnings entry won't be added
     *
     * The code value is among:
     *
     *   - STATUS_NO_SUCH_WEBSITE
     *   - STATUS_EXISTING
     *   - STATUS_EXISTING_DELETED
     *   - STATUS_EXISTING_HIDDEN
     *
     * @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 array
     */
    public function getSiteStatusAction(int $organizationId, bool $fullScan = false): array
    {
        try {
            $rootUid = $this->findRootUidFor($organizationId);
        } catch (NoSuchWebsiteException $e) {
            return $this->buildStatusResult($organizationId, self::STATUS_NO_SUCH_WEBSITE);
        }

        if ($rootUid['deleted']) {
            return $this->buildStatusResult($organizationId, self::STATUS_EXISTING_DELETED, $rootUid);
        }
        if ($rootUid['hidden'] || ($rootUid['fe_group'] < 0)) {
            return $this->buildStatusResult($organizationId, self::STATUS_EXISTING_HIDDEN, $rootUid);
        }

        $warnings = null;
        if ($fullScan) {
            // ** Look for potential issues
            $warnings = $this->scanSite($organizationId, $rootUid);
        }
        return $this->buildStatusResult($organizationId, self::STATUS_EXISTING, $rootUid, $warnings);
    }



    /**
     * Retrieve the Organization object from the repository and then,
     * from the Opentalent API
     * @param $organizationId
     * @return Organization
     */
    private function fetchOrganization($organizationId) {
        $organizationRepository = GeneralUtility::makeInstance(ObjectManager::class)->get(OrganizationRepository::class);
        try {
            return $organizationRepository->findById($organizationId);
        } catch (ApiRequestException $e) {
            throw new \RuntimeException('Unable to fetch the organization with id: ' . $organizationId);
        }
    }

    /**
     * Try to find the root page uid of the organization's website and return it.
     * Throw a Opentalent\OtAdmin\NoSuchWebsiteException exception if the website does not exist.
     *
     * @param $organizationId
     * @return int
     * @throws NoSuchWebsiteException
     */
    private function findRootUidFor($organizationId) {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $queryBuilder->getRestrictions()->removeAll();
        $rootUid = $queryBuilder
            ->select('uid')
            ->from('pages')
            ->where('is_siteroot=1')
            ->andWhere($queryBuilder->expr()->eq('tx_opentalent_structure_id', $organizationId))
            ->execute()
            ->fetchColumn(0);

        if ($rootUid > 0) {
            return $rootUid;
        }
        throw new NoSuchWebsiteException("The website of this organization can not be found");
    }

    /**
     * Determine which folder-type Typo3 page should contain the new website
     * CREATES IT if needed, and return its uid
     *
     * @return int
     */
    private function getParentFolderUid() {

        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
        $siteCount = $queryBuilder
            ->count('uid')
            ->from('pages')
            ->where('is_siteroot=1')
            ->execute()
            ->fetchColumn(0);

        $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()
            ->fetchColumn(0);

        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 Organization $organization
     * @param int $pid
     * @param string $title
     * @param string $slug
     * @param string $template
     * @param array $moreValues
     * @return int
     */
    private function insertPage(Organization $organization,
                                int $pid,
                                string $title,
                                string $slug,
                                string $template = '',
                                array $moreValues = []
                                ) {

        $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',
            'tx_opentalent_structure_id' => $organization->getId()
        ];

        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 Organization $organization
     * @return int
     */
    private function insertRootPage(Organization $organization) {
        return $this->insertPage(
            $organization,
            $this->getParentFolderUid(),
            $organization->getName(),
            '/',
            self::TEMPLATE_HOME,
            [
                'is_siteroot' => 1,
                'TSconfig' => 'TCAdefaults.pages.tx_opentalent_structure_id =' . $organization->getId(),
                'tx_opentalent_template' => self::DEFAULT_THEME,
                'tx_opentalent_template_preferences' => '{"themeColor":"' . self::DEFAULT_COLOR . '","displayCarousel":"1"}'
            ]
        );
    }

    /**
     * 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 = []) {
        $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=prod-back;dbname=opentalent",
            'dbcloner',
            'wWZ4hYcrmHLW2mUK',
            array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8')
        );
        $cnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $cnn->prepare(
            "SELECT o.id, o.name, o.facebook, o.twitter, 
                                 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->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data = $stmt->fetch();


        $stmt = $cnn->prepare(
            "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->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data['email'] = $stmt->fetch()['email'];

        $stmt = $cnn->prepare(
            "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->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $data['network'] = $stmt->fetch();

        $stmt = $cnn->prepare(
            "SELECT p.username, a.id, s.product
                      FROM opentalent.Person p 
                        INNER JOIN Access a ON p.id = a.person_id
                        INNER JOIN Settings s on a.organization_id = s.organization_id
                      where a.organization_id=" . $organizationId . " AND  a.adminAccess=1;"
        );
        $stmt->execute();
        $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) {
        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" .
            "        }\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) {
        mkdir($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) {
        $f = fopen($path, "w");
        try
        {
            fwrite($f, $content);
            $this->createdFiles[] = $path;
            chgrp($path, 'www-data');
        } finally {
            fclose($f);
        }
    }

    /**
     * Write the .../sites/.../config.yml file of the given site
     *
     * @param int $organizationId
     * @param int $rootUid
     * @param string $domain
     */
    private function writeConfigFile(int $organizationId, int $rootUid, string $domain) {

        $subdomain = explode('.', $domain)[0];

        $config_dir = $_ENV['TYPO3_PATH_APP'] . "/config/sites/" . $subdomain . '_' . $organizationId;
        $config_filename = $config_dir . "/config.yaml";

        if (file_exists($config_filename)) {
            throw new \RuntimeException("A file named " . $config_filename . " already exists. Abort.");
        }

        $config = ['base' => 'https://' . $domain,
            'baseVariants'=>[
                ['base' => $subdomain . '/',
                 'condition' => 'applicationContext == "Development"']
            ],
            'errorHandling'=>[
                ['errorCode'=>'404',
                 'errorHandler'=>'PHP',
                 'errorPhpClassFQCN'=>'Opentalent\OtTemplating\Page\ErrorHandler'],
                ['errorCode'=>'403',
                 'errorHandler'=>'PHP',
                 'errorPhpClassFQCN'=>'Opentalent\OtTemplating\Page\ErrorHandler'],
            ],
            'flux_content_types'=>'',
            'flux_page_templates'=>'',
            'languages'=>[[
                'title'=>'Fr',
                'enabled'=>True,
                'base'=>'/',
                'typo3Language'=>'fr',
                'locale'=>'fr_FR',
                'iso-639-1'=>'fr',
                'navigationTitle'=>'Fr',
                'hreflang'=>'fr-FR',
                'direction'=>'ltr',
                'flag'=>'fr',
                'languageId'=>'0',
            ]],
            'rootPageId'=>$rootUid,
            'routes'=>[]
        ];
        $yamlConfig = Yaml::dump($config, 99, 2);

        if (!file_exists($config_dir)) {
            $this->mkDir($config_dir);
        }
        GeneralUtility::writeFile($config_filename, $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($config_filename, $stats['4']);
            chgrp($config_filename, $stats['5']);
            chmod($config_filename, $stats['2']);
        } catch (\TYPO3\CMS\Core\Error\Exception $e) {
        }

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


    /**
     * Create the BE user for the website
     * The user shall be already created in the Opentalent DB
     *
     * @param int $organizationId
     * @param int $rootUid
     * @param string $domain
     * @param array $userData
     * @return int
     */
    private function createBeUser(int $organizationId,
                                  int $rootUid,
                                  string $domain,
                                  array $userData) {

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

        // Since we don't want to store the password in the TYPO3 DB, we store a random string instead
        $randomStr = (new Random)->generateRandomHexString(20);

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

        $values = [
            'username' => $userData['username'],
            'password' => $randomStr,
            'description' => '[ATTENTION: enregistrement auto-généré, ne pas modifier directement] BE Admin for ' . $domain . ' (id: ' . $userData['id'] . ')',
            'deleted' => 0,
            'lang' => 'fr',
            'usergroup' => isset(self::PRODUCT_MAPPING[$user_data['product']]) ? self::PRODUCT_MAPPING[$userData['product']] : 1,
            'db_mountpoints' => $rootUid,
            'userMods' => 'file_FilelistList',
            'file_mountPoints' => join(',', $files),
            'options' => 2,
            'file_permissions' => 'readFolder,writeFolder,addFolder,renameFolder,moveFolder,deleteFolder,readFile,writeFile,addFile,renameFile,replaceFile,moveFile,copyFile,deleteFile',
            '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');
        $queryBuilder->insert('be_users')
            ->values($values)
            ->execute();

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

    /**
     * Enable frontend editing for user
     *
     * @param int $adminUid
     */
    private function enableFeEditing(int $adminUid) {
        $BE_USER = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Authentication\\BackendUserAuthentication');
        $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);
    }
}