<?php
/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009 - 2026  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett chris@pollett.org
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009 - 2026
 * @filesource
 */
namespace seekquarry\yioop\models;

use seekquarry\yioop\configs as C;
use seekquarry\yioop\library\UrlParser;
use seekquarry\yioop\library\mail\DkimKey;
use seekquarry\yioop\models\datasources\DatasourceManager;
use seekquarry\yioop\models\datasources\Sqlite3Manager;

/**
 * This is class is used to handle
 * getting and saving the Profile.php of the current search engine instance
 *
 * @author Chris Pollett
 */
class ProfileModel extends Model
{
    /**
     * Canonical defaults for profile fields whose values must be
     * non-empty when the admin lands on a settings form for the
     * first time, or whose value would otherwise be clobbered by
     * an empty nsdefine in Profile.php. Lives in code rather than
     * the C\* config constants because Profile.php is included
     * BEFORE Config.php's nsconddefine calls run -- which means
     * once an empty value lands in the profile file, the
     * conditional define in Config.php never gets a chance to
     * supply a real default.
     *
     * @var array
     */
    public static $profile_defaults = [
        'AUTH_METHOD' => C\LOCAL_AUTHENTICATION,
        'PASSWORD_MIN_LEN' => C\PASSWORD_MIN_LEN,
        'PASSWORD_REQUIRE_LOWERCASE' => C\PASSWORD_REQUIRE_LOWERCASE,
        'PASSWORD_REQUIRE_UPPERCASE' => C\PASSWORD_REQUIRE_UPPERCASE,
        'PASSWORD_REQUIRE_DIGIT' => C\PASSWORD_REQUIRE_DIGIT,
        'PASSWORD_REQUIRE_SYMBOL' => C\PASSWORD_REQUIRE_SYMBOL,
        'LDAP_CONTROLLERS' => C\LDAP_CONTROLLERS,
        'LDAP_ACCOUNT_SUFFIX' => C\LDAP_ACCOUNT_SUFFIX,
        'LDAP_BASE_DN' => C\LDAP_BASE_DN,
        'MAIL_SMTP_PORT' => 25,
        'MAIL_SMTPS_PORT' => 465,
        'MAIL_IMAP_PORT' => 143,
        'MAIL_IMAPS_PORT' => 993,
        'ALPN_PROTOCOLS' => 'h2,http/1.1',
        'GIT_DEFAULT_BRANCH' => C\GIT_DEFAULT_BRANCH,
        'GIT_MAX_BLOB_SIZE' => C\GIT_MAX_BLOB_SIZE,
        'GIT_MAX_PUSH_SIZE' => C\GIT_MAX_PUSH_SIZE,
    ];
    /**
     * These are fields whose values might be set in a Yioop instance
     * Profile.php file
     * @var array
     */
    public $profile_fields = ['AD_LOCATION', 'API_ACCESS', 'AUTH_KEY',
        'AUTH_METHOD', 'PASSWORD_MIN_LEN', 'PASSWORD_REQUIRE_LOWERCASE',
        'PASSWORD_REQUIRE_UPPERCASE', 'PASSWORD_REQUIRE_DIGIT',
        'PASSWORD_REQUIRE_SYMBOL',
        'LDAP_CONTROLLERS', 'LDAP_ACCOUNT_SUFFIX', 'LDAP_BASE_DN',
        'AUTOLOGOUT', 'AUXILIARY_CSS', 'AUXILIARY_CSS_NAME',
        'BACKGROUND_COLOR', 'BACKGROUND_IMAGE',
        'CACHE_LINK', 'CLD_URL_BONUS', 'CONFIGURE_BOT',
        'COOKIE_LIFETIME', 'CSRF_TOKEN', 'DEBUG_LEVEL',
        'DEFAULT_CONTINUOUS_SCROLL', 'DB_HOST', 'DBMS', 'DB_NAME',
        'DB_PASSWORD', 'DB_USER', 'DEFAULT_LOCALE', 'DIFFERENTIAL_PRIVACY',
        'FAVICON', 'FOREGROUND_COLOR', 'GIT_DEFAULT_BRANCH',
        'GIT_MAX_BLOB_SIZE', 'GIT_MAX_PUSH_SIZE',
        'GLOBAL_ADSCRIPT', 'GROUP_ITEM',
        'GROUP_ANALYTICS_MODE', 'HOST_KEYWORD_BONUS', 'HOST_URL_BONUS',
        'IN_LINK', 'IP_LINK', 'LANDING_PAGE', 'LOGO_SMALL', 'LOGO_MEDIUM',
        'LOGO_LARGE', 'MAIL_DOMAINS', 'MAIL_HOST_NAME',
        'MAIL_IMAPS_PORT', 'MAIL_EXTERNAL',
        'ACME_ON', 'SECURE_DOMAINS', 'WWW_USER', 'ALPN_PROTOCOLS',
        'MAIL_IMAP_PORT', 'MAIL_LOG_ENABLED', 'MAIL_MODE',
        'MAIL_DELIVERY_SECURITY', 'MAIL_DMARC_ENFORCE',
        'MAIL_USE_STARTTLS',
        'MAIL_PASSWORD',  'MAIL_SECURITY', 'MAIL_BOT_HANDLED',
        'MAIL_SENDER', 'MAIL_SERVER', 'MAIL_SERVERPORT', 'MAIL_SMTPS_PORT',
        'MAIL_SMTP_PORT', 'MAIL_SUBMISSION_PORT',
        'MAIL_TEST_MODE',
        'MAIL_TEST_MODE_FALLBACK_PORT', 'MAIL_USERNAME',
        'MIN_RESULTS_TO_GROUP',  'MONETIZATION_TYPE', 'MORE_RESULT',
        'MEDIA_MODE', 'MODERATION_FLAG_THRESHOLD', 'USE_MODERATION',
        'NAME_SERVER', 'NUM_SLASHES_BONUS', 'PATH_KEYWORD_BONUS',
        'PRIVATE_DB_NAME', 'PRIVATE_DB_HOST', 'PRIVATE_DBMS',
        'PRIVATE_DB_PASSWORD', 'PRIVATE_DB_USER', 'PROXY_SERVERS',
        'RECOVERY_MODE', 'REGISTRATION_TYPE', 'RESULT_SCORE',
        'ROBOT_INSTANCE','RSS_ACCESS', 'SEARCH_ANALYTICS_MODE',
        'SEARCHBAR_PATH', 'SEND_MAIL_MEDIA_UPDATER', 'SERP_FAVICONS',
        'SESSION_NAME', 'SIDE_ADSCRIPT', 'SIDEBAR_COLOR', 'SIGNIN_LINK',
        'SIMILAR_LINK', 'SITE_MOTTO', 'SITE_NAME', 'SUBSEARCH_LINK',
        'TITLE_BONUS', 'TIMEZONE',
        'TOPBAR_COLOR', 'TOP_ADSCRIPT','TOR_PROXY', 'USE_FILECACHE',
        'USE_PROXY', 'USER_AGENT_SHORT', 'USER_RANK_BONUS',
        'WEB_URI', 'WEB_ACCESS', 'WIKI_BONUS',  'WORD_CLOUD', 'WORD_SUGGEST'
        ];
    /**
     * Profile fields which are stored in wiki or in a flat file
     * @var array
     */
    public $file_fields = ["AUXILIARY_CSS", "ROBOT_DESCRIPTION"];
    /**
     * Associative array (table_name => SQL statement to create that table
     * for the pubblic database)
     * List is alphabetical and contains all Yioop tables. List is only
     * initialized after an @see initializeSql call.
     * @var array
     */
    public $create_statements;
    /**
     * Associative array (table_name => SQL statement to create that table
     * for the private database)
     * List is alphabetical and contains all Yioop tables. List is only
     * initialized after an @see initializeSql call.
     * @var array
     */
    public $private_create_statements;
    /**
     * {@inheritDoc}
     *
     * @param string $db_name the name of the database for the search engine
     * @param bool $connect whether to connect to the database by default
     *     after making the datasource class
     */
    public function __construct($db_name = C\DB_NAME, $connect = true)
    {
        parent::__construct($db_name, $connect);
        $this->create_statements = [];
        $this->private_create_statements = [];
    }
    /**
     * Used to construct $this->create_statements, the list of all SQL
     * CREATE statements needed to build a Yioop database
     *
     * @param object $dbm a datasource_manager object used to get strings
     *     for autoincrement and serial types for a given db
     * @param array $dbinfo connect info for the database, also used in
     *     getting autoincrement and serial types
     */
    public function initializeSql($dbm, $dbinfo)
    {
        $auto_increment = $dbm->autoIncrement($dbinfo);
        $serial = $dbm->serialType($dbinfo);
        $integer = $dbm->integerType($dbinfo);
        $page_type = $dbm->pageType($dbinfo);
        $scraper_text = (stristr($dbinfo['DBMS'], "mysql") !== false) ?
            " TEXT " :  " VARCHAR(" . (10 * C\MAX_URL_LEN) . ") ";
        $user_session_text = (stristr($dbinfo['DBMS'], "mysql") !== false) ?
            " TEXT " :  " VARCHAR(". C\MAX_USER_SESSION_SIZE . ") ";
        $appearance_text = (stristr($dbinfo['DBMS'], "mysql") !== false) ?
            " TEXT " :  " VARCHAR(" . (10 * C\MAX_URL_LEN) . ") ";
        /**
         * SQL statements used to create the Yioop database. Some of the
         * these statements could use UNIQUE on some the columns that are
         * later used in CREATE INDEX statements. However, because of
         * restrictions on the number of bytes (not chars) in MYSQL for keys
         * this has not been done.
         */
        $this->create_statements = [
            "ACTIVE_PROCESS" =>
                "CREATE TABLE ACTIVE_PROCESS (NAME VARCHAR(".C\NAME_LEN.
                "), ID $integer, TYPE VARCHAR(" . C\NAME_LEN . "))",
            "ADVERTISEMENT" => "CREATE TABLE ADVERTISEMENT (ID
                $serial PRIMARY KEY $auto_increment, USER_ID $integer,
                NAME VARCHAR(". C\ADVERTISEMENT_NAME_LEN ."),
                DESCRIPTION VARCHAR(". C\ADVERTISEMENT_TEXT_LEN ."),
                DESTINATION VARCHAR(". C\ADVERTISEMENT_DESTINATION_LEN ."),
                KEYWORDS VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN ."),
                STATUS $integer, BUDGET $integer, CLICKS $integer,
                IMPRESSIONS $integer, START_DATE VARCHAR(".
                C\ADVERTISEMENT_DATE_LEN ."), END_DATE VARCHAR(".
                C\ADVERTISEMENT_DATE_LEN ."))",
            "ACCEPTED_AD_BIDS" => "CREATE TABLE ACCEPTED_AD_BIDS
                (AD_ID $integer, KEYWORD VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN
                ."), BID_AMOUNT $integer, BID_DATE VARCHAR(".
                C\ADVERTISEMENT_DATE_LEN ."))",
            "AAD_KEYWORD_DATE_INDEX" => "CREATE INDEX
                AAD_KEYWORD_DATE_INDEX ON ACCEPTED_AD_BIDS(KEYWORD, BID_DATE)",
            "ACTIVITY" => "CREATE TABLE ACTIVITY (ACTIVITY_ID $serial
                PRIMARY KEY $auto_increment, TRANSLATION_ID $integer,
                METHOD_NAME VARCHAR(" . C\LONG_NAME_LEN . "))",
            "ACTIVITY_TRANSLATION_ID_INDEX" => "CREATE INDEX
                ACTIVITY_TRANSLATION_ID_INDEX ON ACTIVITY (TRANSLATION_ID)",
            "CHAT_BOT" => "CREATE TABLE CHAT_BOT(
                USER_ID $integer PRIMARY KEY, BOT_TOKEN CHAR(". C\TIMESTAMP_LEN.
                ") UNIQUE, CALLBACK_URL VARCHAR(" . C\MAX_URL_LEN . "))",
            "CHAT_BOT_PATTERN" => "CREATE TABLE CHAT_BOT_PATTERN(
                PATTERN_ID $serial PRIMARY KEY $auto_increment,
                USER_ID  $integer,
                REQUEST VARCHAR(" . C\MAX_DESCRIPTION_LEN ."),
                TRIGGER_STATE VARCHAR(" . C\NAME_LEN . "),
                REMOTE_MESSAGE VARCHAR(" . C\MAX_DESCRIPTION_LEN . "),
                RESULT_STATE VARCHAR(" . C\NAME_LEN . "),
                RESPONSE VARCHAR(" . C\MAX_DESCRIPTION_LEN ."))",
            "CRAWL_MIXES" => "CREATE TABLE CRAWL_MIXES (TIMESTAMP NUMERIC(".
                C\TIMESTAMP_LEN.")
                PRIMARY KEY, NAME VARCHAR(" . C\NAME_LEN .
                "), OWNER_ID $integer,
                PARENT NUMERIC(" . C\TIMESTAMP_LEN . "))",
            "CM_OWNER_ID_INDEX" => "CREATE INDEX CM_OWNER_ID_INDEX ON
                CRAWL_MIXES (OWNER_ID)",
            "CM_PARENT_INDEX" => "CREATE INDEX CM_PARENT_INDEX ON
                CRAWL_MIXES (PARENT)",
            "CREDIT_LEDGER" => "CREATE TABLE CREDIT_LEDGER
                (USER_ID $integer, AMOUNT $integer, TYPE VARCHAR(" .
                C\NAME_LEN . "), BALANCE $integer, TIMESTAMP NUMERIC(" .
                C\TIMESTAMP_LEN . "))",
            "CL_USER_INDEX" => "CREATE INDEX CL_USER_INDEX ON
                CREDIT_LEDGER (USER_ID)",
            "CURRENT_WEB_INDEX" => "CREATE TABLE CURRENT_WEB_INDEX
                (CRAWL_TIME NUMERIC(" . C\TIMESTAMP_LEN . ") PRIMARY KEY)",
            "GROUP_ITEM" => "CREATE TABLE GROUP_ITEM (ID $serial PRIMARY KEY
                $auto_increment, PARENT_ID $integer, GROUP_ID $integer,
                USER_ID $integer, URL VARCHAR(" . C\TITLE_LEN
                .") DEFAULT '', TITLE VARCHAR(" . C\TITLE_LEN
                ."), DESCRIPTION VARCHAR(".
                C\MAX_GROUP_POST_LEN . "), PUBDATE NUMERIC(" .
                C\TIMESTAMP_LEN . "),
                EDIT_DATE NUMERIC(" . C\TIMESTAMP_LEN . "),
                UPS $integer DEFAULT 0, DOWNS $integer DEFAULT 0,
                FLAG $integer DEFAULT 0,
                PARENT_ITEM_ID $integer DEFAULT 0,
                TYPE $integer DEFAULT " . C\STANDARD_GROUP_ITEM . ")",
            "GI_GROUP_ID_INDEX" => "CREATE INDEX GI_GROUP_ID_INDEX ON
                GROUP_ITEM (GROUP_ID)",
            "GI_PUBDATE_INDEX" => "CREATE INDEX
                GI_PUBDATE_INDEX ON GROUP_ITEM (PUBDATE)",
            "GI_GROUP_ID_PUBDATE_INDEX" => "CREATE INDEX
                GI_GROUP_ID_PUBDATE_INDEX ON GROUP_ITEM (GROUP_ID, PUBDATE)",
            "GI_USER_ID_INDEX" => "CREATE INDEX GI_USER_ID_INDEX ON
                GROUP_ITEM (USER_ID)",
            "GI_PARENT_ID_INDEX" => "CREATE INDEX GI_PARENT_ID_INDEX ON
                GROUP_ITEM (PARENT_ID)",
            "GI_PARENT_ID_PUBDATE_INDEX" => "CREATE INDEX
                GI_PARENT_ID_PUBDATE_INDEX ON GROUP_ITEM (PARENT_ID, PUBDATE)",
            "GROUP_CALL" => "CREATE TABLE GROUP_CALL(
                CALL_HASH_ID $integer PRIMARY KEY, INITIATOR_ID $integer,
                CALL_TIME NUMERIC(" . C\MICROSECOND_TIMESTAMP_LEN . "))",
            "GROUP_CALL_EVENTS" => "CREATE TABLE GROUP_CALL_EVENTS(
                CALL_HASH_ID $integer, SENDER_ID $integer, EVENT_TIME
                NUMERIC(" . C\MICROSECOND_TIMESTAMP_LEN . "),
                EVENT VARCHAR(". C\MAX_GROUP_POST_LEN . "),
                PRIMARY KEY(CALL_HASH_ID, EVENT_TIME))",
            "GROUP_ITEM_VOTE" => "CREATE TABLE GROUP_ITEM_VOTE(
                USER_ID $integer, ITEM_ID $integer)",
            "GROUP_ITEM_FLAG" => "CREATE TABLE GROUP_ITEM_FLAG(
                USER_ID $integer, ITEM_ID $integer)",
            "GROUP_ITEM_RECOMMENDATION" => "CREATE TABLE
                GROUP_ITEM_RECOMMENDATION (USER_ID $integer NOT NULL,
                ITEM_ID $integer NOT NULL, ITEM_TYPE $integer NOT NULL,
                SCORE FLOAT, TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "))",
            "GI_RECOMMENDATION_INDEX" => "CREATE INDEX GI_RECOMMENDATION_INDEX
                 ON GROUP_ITEM_RECOMMENDATION (USER_ID, ITEM_TYPE)",
            "GROUP_PAGE" => "CREATE TABLE GROUP_PAGE (
                ID $serial PRIMARY KEY $auto_increment, GROUP_ID $integer,
                DISCUSS_THREAD $integer, TITLE VARCHAR(" . C\TITLE_LEN . "),
                PAGE $page_type, LOCALE_TAG VARCHAR(" . C\NAME_LEN . "),
                LAST_MODIFIED NUMERIC(" . C\TIMESTAMP_LEN . "))",
            "GP_ID_INDEX" => "CREATE INDEX GP_ID_INDEX ON GROUP_PAGE
                 (GROUP_ID, TITLE, LOCALE_TAG)",
            "GP_GROUP_ID_INDEX" => "CREATE INDEX GP_GROUP_ID_INDEX ON GROUP_PAGE
                 (GROUP_ID)",
            "GROUP_PAGE_HISTORY" => "CREATE TABLE GROUP_PAGE_HISTORY(
                PAGE_ID $integer, GROUP_ID $integer, EDITOR_ID $integer,
                TITLE VARCHAR(" . C\TITLE_LEN . "), PAGE  $page_type,
                EDIT_COMMENT VARCHAR(" . C\SHORT_TITLE_LEN .
                "), LOCALE_TAG VARCHAR(" . C\NAME_LEN . "),
                PUBDATE NUMERIC(" . C\TIMESTAMP_LEN . "),
                PRIMARY KEY(PAGE_ID, PUBDATE))",
            "GROUP_PAGE_LINK" => "CREATE TABLE GROUP_PAGE_LINK(
                LINK_TYPE_ID $integer, FROM_ID $integer,
                TO_ID $integer,
                PRIMARY KEY(LINK_TYPE_ID, FROM_ID, TO_ID))",
            "GROUP_PAGE_PRE_LINK" => "CREATE TABLE GROUP_PAGE_PRE_LINK(
                LINK_TYPE_ID $integer, FROM_ID $integer,
                TO_GROUP_ID $integer, TO_PAGE_NAME VARCHAR(" . C\TITLE_LEN .
                "), PRIMARY KEY(LINK_TYPE_ID, FROM_ID, TO_GROUP_ID,
                TO_PAGE_NAME))",
            "GPP_ID_INDEX" => "CREATE INDEX GP_PRE_INDEX ON GROUP_PAGE_PRE_LINK
                 (TO_GROUP_ID, TO_PAGE_NAME)",
            "GROUP_RESOURCE_RECOMMENDATION" => "CREATE TABLE
                GROUP_RESOURCE_RECOMMENDATION (USER_ID $integer NOT NULL,
                GROUP_ID $integer NOT NULL, PAGE_ID $integer NOT NULL,
                RESOURCE_PATH VARCHAR(". C\SHORT_TITLE_LEN.
                "), SCORE FLOAT, TIMESTAMP NUMERIC(" .
                C\TIMESTAMP_LEN . "), RESOURCE_ID $integer)",
            "GR_RECOMMENDATION_INDEX" => "CREATE INDEX GR_RECOMMENDATION_INDEX
                ON GROUP_RESOURCE_RECOMMENDATION (USER_ID)",
            "SOCIAL_GROUPS" => "CREATE TABLE SOCIAL_GROUPS (
                GROUP_ID $serial PRIMARY KEY $auto_increment,
                GROUP_NAME VARCHAR(" . C\SHORT_TITLE_LEN
                ."), CREATED_TIME VARCHAR(" . C\MICROSECOND_TIMESTAMP_LEN . "),
                OWNER_ID $integer, REGISTER_TYPE $integer,
                MEMBER_ACCESS $integer, VOTE_ACCESS $integer DEFAULT ".
                C\NON_VOTING_GROUP . ", POST_LIFETIME $integer DEFAULT ".
                C\FOREVER . ", OPTIONS $integer DEFAULT 0,
                RENDER_ENGINE $integer DEFAULT " . C\MEDIAWIKI_ENGINE . ")",
            /* NOTE: We are not using singular name GROUP for SOCIAL_GROUPS as
               GROUP is a reserved SQL keyword and the table used to be
               called GROUPS until Mysql reserved that word too
             */
            "GRP_OWNER_ID_INDEX" => "CREATE INDEX GRP_OWNER_ID_INDEX ON
                SOCIAL_GROUPS (OWNER_ID)",
            "GRP_MEMBER_ACCESS_INDEX" => "CREATE INDEX GRP_MEMBER_ACCESS_INDEX
                ON SOCIAL_GROUPS(MEMBER_ACCESS)",
            "ITEM_IMPRESSION_STAT" => "CREATE TABLE ITEM_IMPRESSION_STAT(
                ITEM_ID $integer, ITEM_TYPE $integer, UPDATE_PERIOD $integer,
                NUM_VIEWS $integer DEFAULT -1,
                FUZZY_NUM_VIEWS $integer DEFAULT -1)",
            "ITEM_IMPRESSION_SUMMARY" => "CREATE TABLE ITEM_IMPRESSION_SUMMARY(
                USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
                UPDATE_PERIOD $integer,
                UPDATE_TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),
                NUM_VIEWS $integer, FUZZY_NUM_VIEWS $integer DEFAULT -1,
                TMP_NUM_VIEWS $integer DEFAULT -1,
                PRIMARY KEY(USER_ID, ITEM_ID, ITEM_TYPE,
                UPDATE_PERIOD, UPDATE_TIMESTAMP))",
            "LOCALE" => "CREATE TABLE LOCALE(LOCALE_ID $serial PRIMARY KEY
                $auto_increment, LOCALE_TAG VARCHAR(" . C\NAME_LEN . "),
                LOCALE_NAME VARCHAR(" . C\LONG_NAME_LEN .
                "), WRITING_MODE CHAR(" .
                C\WRITING_MODE_LEN."), ACTIVE $integer DEFAULT 1)",
            "LCL_LOCALE_TAG_INDEX" => "CREATE INDEX LCL_LOCALE_TAG_INDEX ON
                LOCALE(LOCALE_TAG)",
            "MACHINE" => "CREATE TABLE MACHINE (NAME VARCHAR(" . C\NAME_LEN
                .") PRIMARY KEY,
                URL VARCHAR(" . C\MAX_URL_LEN .
                "), CHANNEL $integer,
                NUM_FETCHERS $integer, PARENT VARCHAR(" . C\NAME_LEN . ") )",
            "MAIL_ACCOUNT" => "CREATE TABLE MAIL_ACCOUNT (
                ID $serial PRIMARY KEY $auto_increment,
                USER_ID $integer,
                DISPLAY_NAME VARCHAR(" . C\MAIL_DISPLAY_NAME_LEN . "),
                PROVIDER VARCHAR(" . C\MAIL_PROVIDER_LEN . "),
                HOST VARCHAR(" . C\MAIL_HOST_LEN . "),
                PORT $integer,
                USERNAME VARCHAR(" . C\MAIL_USERNAME_LEN . "),
                PASSWORD_NONCE VARCHAR(" .
                    C\MAIL_PASSWORD_NONCE_LEN . "),
                PASSWORD_CIPHERTEXT VARCHAR(" .
                    C\MAIL_PASSWORD_CIPHERTEXT_LEN . "),
                TLS_MODE VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
                DEFAULT_FOLDER VARCHAR(" .
                    C\MAIL_DISPLAY_NAME_LEN . "),
                ALLOW_SELF_SIGNED $integer DEFAULT 0,
                SMTP_HOST VARCHAR(" . C\MAIL_HOST_LEN . "),
                SMTP_PORT $integer,
                SMTP_USERNAME VARCHAR(" . C\MAIL_USERNAME_LEN . "),
                SMTP_PASSWORD_NONCE VARCHAR(" .
                    C\MAIL_PASSWORD_NONCE_LEN . "),
                SMTP_PASSWORD_CIPHERTEXT VARCHAR(" .
                    C\MAIL_PASSWORD_CIPHERTEXT_LEN . "),
                SMTP_TLS_MODE VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
                SORT_ORDER $integer DEFAULT 0,
                CREATED_AT $integer,
                UPDATED_AT $integer)",
            "MAIL_ACCOUNT_USER_IDX" => "CREATE INDEX
                MAIL_ACCOUNT_USER_IDX ON MAIL_ACCOUNT (USER_ID)",
            "MAIL_SCHEDULED" => "CREATE TABLE MAIL_SCHEDULED (
                ID $serial PRIMARY KEY $auto_increment,
                USER_ID $integer,
                ACCOUNT_ID $integer,
                SCHEDULED_AT $integer,
                CREATED_AT $integer,
                STATUS VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
                LAST_ERROR VARCHAR(" . C\MAIL_LAST_ERROR_LEN . "),
                ATTEMPT_COUNT $integer DEFAULT 0,
                SUBJECT VARCHAR(" . C\MAIL_SUBJECT_LEN . "),
                TO_LIST TEXT,
                CC_LIST TEXT,
                BCC_LIST TEXT,
                BODY_TEXT TEXT,
                BODY_HTML TEXT,
                IN_REPLY_TO VARCHAR(" . C\MAIL_SUBJECT_LEN . "),
                REFERENCES_HEADER TEXT)",
            "MAIL_SCHEDULED_USER_IDX" => "CREATE INDEX
                MAIL_SCHEDULED_USER_IDX ON MAIL_SCHEDULED
                (USER_ID, SCHEDULED_AT)",
            "MAIL_SCHEDULED_DUE_IDX" => "CREATE INDEX
                MAIL_SCHEDULED_DUE_IDX ON MAIL_SCHEDULED
                (SCHEDULED_AT, STATUS)",
            "MAIL_CLONE_JOB" => "CREATE TABLE MAIL_CLONE_JOB (
                ID $serial PRIMARY KEY $auto_increment,
                USER_ID $integer,
                SRC_ACCOUNT $integer,
                DESTINATION_USER VARCHAR(" . C\NAME_LEN . "),
                MODE VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
                STATUS VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
                DRY_RUN $integer DEFAULT 0,
                FOLDERS_DONE TEXT,
                CURRENT_FOLDER VARCHAR(" .
                    C\MAIL_FOLDER_NAME_LEN . "),
                CURRENT_UID $integer DEFAULT 0,
                CURRENT_UID_PARTIAL_OFFSET $integer DEFAULT 0,
                CURRENT_UID_PARTIAL_SIZE $integer DEFAULT 0,
                IMPORTED $integer DEFAULT 0,
                SKIPPED $integer DEFAULT 0,
                FAILED $integer DEFAULT 0,
                FOLDER_CAP $integer DEFAULT 0,
                STARTED_AT $integer,
                UPDATED_AT $integer)",
            "MAIL_CLONE_JOB_USER_IDX" => "CREATE INDEX
                MAIL_CLONE_JOB_USER_IDX ON MAIL_CLONE_JOB
                (USER_ID, STATUS)",
            "MAIL_CLONE_SEEN" => "CREATE TABLE MAIL_CLONE_SEEN (
                JOB_ID $integer,
                SRC_UIDVALIDITY $integer,
                SRC_UID $integer,
                PRIMARY KEY (JOB_ID, SRC_UIDVALIDITY, SRC_UID))",
            "MAIL_CLONE_ERROR" => "CREATE TABLE MAIL_CLONE_ERROR (
                ID $serial PRIMARY KEY $auto_increment,
                JOB_ID $integer,
                FOLDER VARCHAR(" . C\MAIL_FOLDER_NAME_LEN . "),
                SRC_UIDVALIDITY $integer DEFAULT 0,
                SRC_UID $integer DEFAULT 0,
                ERROR_KIND VARCHAR(" .
                    C\MAIL_CLONE_ERROR_KIND_LEN . "),
                MESSAGE VARCHAR(" .
                    C\MAIL_LAST_ERROR_LEN . "),
                OCCURRED_AT $integer,
                RESOLVED_AT $integer DEFAULT 0,
                RESOLVED_VIA VARCHAR(" .
                    C\MAIL_CLONE_ERROR_KIND_LEN . "))",
            "MAIL_CLONE_ERROR_JOB_IDX" => "CREATE INDEX
                MAIL_CLONE_ERROR_JOB_IDX ON MAIL_CLONE_ERROR
                (JOB_ID, RESOLVED_AT)",
            "MAIL_CLONE_ERROR_UID_IDX" => "CREATE INDEX
                MAIL_CLONE_ERROR_UID_IDX ON MAIL_CLONE_ERROR
                (JOB_ID, SRC_UIDVALIDITY, SRC_UID)",
            "MAIL_ALIAS" => "CREATE TABLE MAIL_ALIAS (
                ID $serial PRIMARY KEY $auto_increment,
                USER_ID $integer,
                ALIAS VARCHAR(" . C\NAME_LEN . "),
                DOMAIN VARCHAR(" . C\MAIL_HOST_LEN . "),
                CREATED_AT $integer)",
            "MAIL_ALIAS_NAME_IDX" => "CREATE UNIQUE INDEX
                MAIL_ALIAS_NAME_IDX ON MAIL_ALIAS (ALIAS, DOMAIN)",
            "MAIL_ALIAS_USER_IDX" => "CREATE INDEX
                MAIL_ALIAS_USER_IDX ON MAIL_ALIAS (USER_ID)",
            "DOMAIN_ROUTE" => "CREATE TABLE DOMAIN_ROUTE (
                DOMAIN VARCHAR(" . C\MAIL_HOST_LEN . ") PRIMARY KEY,
                GROUP_ID $integer)",
            "DOMAIN_APPEARANCE" => "CREATE TABLE DOMAIN_APPEARANCE (
                DOMAIN VARCHAR(" . C\MAIL_HOST_LEN . ") PRIMARY KEY,
                APPEARANCE $appearance_text)",
            "MAIL_SENDER_ALLOW" => "CREATE TABLE MAIL_SENDER_ALLOW (
                ID $serial PRIMARY KEY $auto_increment,
                USER_ID $integer,
                SENDER VARCHAR(" . C\MAIL_USERNAME_LEN . "),
                CREATED_AT $integer)",
            "MAIL_SENDER_ALLOW_IDX" => "CREATE UNIQUE INDEX
                MAIL_SENDER_ALLOW_IDX ON MAIL_SENDER_ALLOW
                (USER_ID, SENDER)",
            "MAIL_SUPPRESSION" => "CREATE TABLE MAIL_SUPPRESSION (
                ID $serial PRIMARY KEY $auto_increment,
                EMAIL VARCHAR(" . C\MAIL_USERNAME_LEN . "),
                CREATED_AT $integer)",
            "MAIL_SUPPRESSION_IDX" => "CREATE UNIQUE INDEX
                MAIL_SUPPRESSION_IDX ON MAIL_SUPPRESSION (EMAIL)",
            "MEDIA_SOURCE" => "CREATE TABLE MEDIA_SOURCE (
                TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . ") PRIMARY KEY,
                NAME VARCHAR(" . C\LONG_NAME_LEN . "),
                TYPE VARCHAR(" . C\NAME_LEN . "),
                CATEGORY VARCHAR(" . C\NAME_LEN . ") DEFAULT 'NEWS',
                SOURCE_URL VARCHAR(" . C\MAX_URL_LEN . "), AUX_INFO VARCHAR(".
                C\MAX_URL_LEN . "), LANGUAGE VARCHAR(" . C\NAME_LEN . "))",
            "MS_TYPE_INDEX" => "CREATE INDEX MS_TYPE_INDEX ON
                MEDIA_SOURCE(TYPE)",
            "MIX_COMPONENTS" => "CREATE TABLE MIX_COMPONENTS (
                TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),
                FRAGMENT_ID $integer,
                CRAWL_TIMESTAMP NUMERIC(".C\TIMESTAMP_LEN."), WEIGHT FLOAT,
                DIRECTION $integer, KEYWORDS VARCHAR(" . C\TITLE_LEN . "),
                PRIMARY KEY(TIMESTAMP, FRAGMENT_ID, CRAWL_TIMESTAMP) )",
            "MIX_FRAGMENTS" => "CREATE TABLE MIX_FRAGMENTS (
                TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),FRAGMENT_ID $integer,
                RESULT_BOUND $integer, PRIMARY KEY(TIMESTAMP, FRAGMENT_ID))",
            "PAGE_RELATIONSHIP" => "CREATE TABLE PAGE_RELATIONSHIP (
                ID $serial PRIMARY KEY $auto_increment, NAME VARCHAR(" .
                C\NAME_LEN . ") UNIQUE)",
            "QUERY_ITEM" => "CREATE TABLE QUERY_ITEM (ID $serial PRIMARY KEY
                $auto_increment, QUERY_HASH CHAR(". C\TIMESTAMP_LEN .
                ") UNIQUE, QUERY VARCHAR(" . C\MAX_QUERY_LEN."),
                AUX_HASH VARCHAR(". C\TIMESTAMP_LEN . ") DEFAULT '',
                CREATION NUMERIC(" . C\TIMESTAMP_LEN . "))",
            "QI_QUERY_HASH_INDEX" => "CREATE INDEX QI_QUERY_HASH_INDEX ON
                QUERY_ITEM (QUERY_HASH)",
            "QI_AUX_HASH_INDEX" => "CREATE INDEX QI_AUX_HASH_INDEX ON
                QUERY_ITEM (AUX_HASH)",
            "ROLE" => "CREATE TABLE ROLE (
                ROLE_ID $serial PRIMARY KEY $auto_increment, NAME VARCHAR(".
                C\NAME_LEN."))",
            "ROLE_ACTIVITY" => "CREATE TABLE ROLE_ACTIVITY (ROLE_ID $integer,
                ACTIVITY_ID $integer,
                ALLOWED_ARGUMENTS VARCHAR(" . C\MAX_URL_LEN . ") DEFAULT 'all',
                PRIMARY KEY(ROLE_ID, ACTIVITY_ID))",
            "ROLE_LIMITS" => "CREATE TABLE ROLE_LIMITS (ROLE_ID $integer
                PRIMARY KEY, MAX_GROUPS_OWNED $integer DEFAULT -1,
                MAX_GROUP_MEMBERS $integer DEFAULT -1,
                MAX_GROUP_WIKI_PAGES $integer DEFAULT -1,
                MAX_PAGE_RESOURCE_MEMORY $integer DEFAULT -1,
                MAX_GROUP_THREADS $integer DEFAULT -1,
                MAX_THREAD_RESOURCES $integer DEFAULT -1,
                MAX_THREAD_POSTS $integer DEFAULT -1,
                ROLE_COST $integer DEFAULT 0,
                CHARGE_FREQUENCY VARCHAR(" . C\NAME_LEN . ")
                DEFAULT 'never')",
            "RECOMMENDATION_ITEM_EMBEDDING" => "CREATE TABLE
                RECOMMENDATION_ITEM_EMBEDDING (ID $integer NOT NULL,
                ITEM_TYPE $integer NOT NULL, VECTOR VARCHAR(" .
                    32 * C\EMBEDDING_VECTOR_SIZE . "), PARENT_ID $integer,
                PRIMARY KEY(ID, ITEM_TYPE))",
            "RECOMMENDATION_TERM_EMBEDDING" => "CREATE TABLE
                RECOMMENDATION_TERM_EMBEDDING (ID VARCHAR(16) NOT NULL,
                ITEM_TYPE $integer NOT NULL, VECTOR VARCHAR(" .
                    32 * C\EMBEDDING_VECTOR_SIZE . "),
                PRIMARY KEY(ID, ITEM_TYPE))",
            "SCRAPER" =>
                "CREATE TABLE SCRAPER (ID $serial PRIMARY KEY
                $auto_increment, NAME VARCHAR(" . C\TITLE_LEN . "),
                PRIORITY $integer DEFAULT 0,
                SIGNATURE VARCHAR(" . C\MAX_URL_LEN . "),
                TEXT_PATH VARCHAR(" . C\MAX_URL_LEN . ") DEFAULT '',
                DELETE_PATHS $scraper_text, EXTRACT_FIELDS $scraper_text)",
            "SUBSEARCH" => "CREATE TABLE SUBSEARCH (
                LOCALE_STRING VARCHAR(" . C\LONG_NAME_LEN . ") PRIMARY KEY,
                FOLDER_NAME VARCHAR(" . C\NAME_LEN."),
                INDEX_IDENTIFIER CHAR(" . (strlen('m:') + C\TIMESTAMP_LEN) . "),
                PER_PAGE $integer,
                DEFAULT_QUERY VARCHAR(" . C\TITLE_LEN . ") DEFAULT ''
                )",
            "TRENDING_TERM" => "CREATE TABLE TRENDING_TERM (
                TERM VARCHAR(" . C\TITLE_LEN . "),
                OCCURRENCES FLOAT DEFAULT 0,
                CATEGORY VARCHAR(" . C\TITLE_LEN . ") DEFAULT 'news',
                UPDATE_PERIOD NUMERIC,
                TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),
                LANGUAGE VARCHAR(" . C\NAME_LEN . ")
                )",
            "TRANSLATION" => "CREATE TABLE TRANSLATION (
                TRANSLATION_ID $serial PRIMARY KEY
                $auto_increment, IDENTIFIER_STRING VARCHAR(" . C\TITLE_LEN
                ."))",
            "TRANS_IDENTIFIER_STRING_INDEX" => "CREATE INDEX
                TRANS_IDENTIFIER_STRING_INDEX ON
                TRANSLATION(IDENTIFIER_STRING)",
            "TRANSLATION_LOCALE" => "CREATE TABLE TRANSLATION_LOCALE
                (TRANSLATION_ID $integer, LOCALE_ID $integer,
                TRANSLATION VARCHAR(" . C\MAX_GROUP_POST_LEN."),
                PRIMARY KEY(TRANSLATION_ID, LOCALE_ID))",
            "USERS" => "CREATE TABLE USERS(USER_ID $serial PRIMARY KEY
                $auto_increment, FIRST_NAME VARCHAR(" . C\NAME_LEN."),
                LAST_NAME VARCHAR(" . C\NAME_LEN . "), USER_NAME VARCHAR(" .
                C\NAME_LEN .") UNIQUE, EMAIL VARCHAR(" . C\LONG_NAME_LEN . "),
                PASSWORD VARCHAR(" . C\LONG_NAME_LEN . "), STATUS $integer,
                HASH VARCHAR(" . C\LONG_NAME_LEN . "),
                CREATION_TIME VARCHAR(" . C\MICROSECOND_TIMESTAMP_LEN .
                "), UPS $integer DEFAULT 0, DOWNS $integer DEFAULT 0,
                AD_CREDITS_SERVICE_ACCOUNT VARCHAR(" .
                C\AD_CREDITS_SERVICE_ACCOUNT_LEN . "),
                RECOVERY_QUESTION VARCHAR(" . C\RECOVERY_QUESTION_LEN .
                ") DEFAULT '', RECOVERY_ANSWER VARCHAR(" . C\LONG_NAME_LEN .
                ") DEFAULT '')",
            "USRS_USER_NAME_INDEX" => "CREATE INDEX USRS_USER_NAME_INDEX ON
                USERS(USER_NAME)",
            "USER_GROUP" => "CREATE TABLE USER_GROUP (USER_ID $integer,
                GROUP_ID $integer, STATUS $integer,
                RECEIVE_MAIL $integer DEFAULT 1,
                JOIN_DATE NUMERIC(" . C\TIMESTAMP_LEN . "),
                PRIMARY KEY (GROUP_ID, USER_ID) )",
            "THREAD_FOLLOW" => "CREATE TABLE THREAD_FOLLOW (
                USER_ID $integer, THREAD_ID $integer,
                PRIMARY KEY (THREAD_ID, USER_ID) )",
            "USER_ROLE" => "CREATE TABLE USER_ROLE (USER_ID $integer,
                ROLE_ID $integer, EXPIRES $integer DEFAULT " . C\FOREVER .
                ", WILL_RENEW $integer DEFAULT 1, " .
                "PRIMARY KEY (ROLE_ID, USER_ID))",
            "USER_SESSION" => "CREATE TABLE USER_SESSION (
                USER_ID $integer PRIMARY KEY, SESSION $user_session_text)",
            "AUTH_SESSION" => "CREATE TABLE AUTH_SESSION (
                SESSION_ID VARCHAR(" . C\SESSION_ID_LEN . ") PRIMARY KEY,
                USER_ID $integer, EXPIRES $integer)",
            "SIGNIN_CODE" => "CREATE TABLE SIGNIN_CODE (
                USER_ID $integer PRIMARY KEY,
                CODE_HASH VARCHAR(" . C\LONG_NAME_LEN . "),
                EXPIRES $integer, TRIES $integer)",
            "VISITOR" => "CREATE TABLE VISITOR(ADDRESS VARCHAR(".
                C\MAX_IP_ADDRESS_AS_STRING_LEN . "),
                PAGE_NAME VARCHAR(" . C\NAME_LEN . "),
                END_TIME $integer, DELAY $integer, FORGET_AGE $integer,
                ACCESS_COUNT $integer,
                PRIMARY KEY(ADDRESS, PAGE_NAME))",
            "VERSION" => "CREATE TABLE VERSION(ID $integer PRIMARY KEY)",
            ];
    }
    /**
     * Used to construct $this->private_create_statements, the list of all SQL
     * CREATE statements needed to build Yioop private database
     *
     * @param object $dbm a datasource_manager object used to get strings
     *     for autoincrement and serial types for a given db
     * @param array $dbinfo connect info for the database, also used in
     *     getting autoincrement and serial types
     */
    public function initializeSqlPrivate($dbm, $dbinfo)
    {
        $auto_increment = $dbm->autoIncrement($dbinfo);
        $serial = $dbm->serialType($dbinfo);
        $integer = $dbm->integerType($dbinfo);
        /**
         * SQL statements used to create the Yioop database. Some of the
         * these statements could use UNIQUE on some the columns that are
         * later used in CREATE INDEX statements. However, because of
         * restrictions on the number of bytes (not chars) in MYSQL for keys
         * this has not been done. AES 256 = 8*32bits long. So need
         * only 32 bytes to store a key. We give up to 64 (LONG_NAME_LEN)
         * as base 64 encoding keys to keep postgres happy when using varchhar
         */
        $this->private_create_statements = ["TYPE_KEYS" =>
            "CREATE TABLE TYPE_KEYS (KEY_ID $serial PRIMARY KEY " .
            "$auto_increment, TYPE_ID $integer, KEY_NAME VARCHAR(" .
            C\LONG_NAME_LEN . "))",
            "MAIL_SECRET" =>
            "CREATE TABLE MAIL_SECRET (KEY_ID $integer PRIMARY KEY, " .
            "KEY_VALUE VARCHAR(" . C\MAIL_SECRET_KEY_LEN . "))",
            "GIT_APP_CODE" =>
            "CREATE TABLE GIT_APP_CODE (USER_ID $integer PRIMARY KEY, " .
            "APP_CODE VARCHAR(" . C\LONG_NAME_LEN . "), EXPIRES $integer)"];
    }
    /**
     * Creates a folder to be used to maintain local information about this
     * instance of the Yioop/SeekQuarry engine
     *
     * Creates the directory provides as well as subdirectories for crawls,
     * locales, logging, and sqlite DBs.
     *
     * @param string $directory path and name of directory to create
     * @return bool true if every required subdirectory exists or was
     *      created; false on the first createIfNecessaryDirectory
     *      failure
     */
    public function makeWorkDirectory($directory)
    {
        $to_make_dirs = [$directory, "$directory/app",
            "$directory/archives", "$directory/cache",
            "$directory/classifiers", "$directory/data",
            "$directory/data/domain_filters",
            "$directory/app/locale", "$directory/log",
            "$directory/mail", "$directory/prepare",
            "$directory/schedules", "$directory/temp"];
        $dir_status = [];
        foreach ($to_make_dirs as $dir) {
            $dir_status[$dir] = $this->createIfNecessaryDirectory($dir);
            if ($dir_status[$dir] < 0) {
                return false;
            }
        }
        if ($dir_status["$directory/app/locale"] == 1) {
            $this->db->copyRecursive(C\BASE_DIR . "/locale",
                "$directory/app/locale");
        }
        if ($dir_status["$directory/data"] == 1) {
            $this->db->copyRecursive(C\BASE_DIR . "/data", "$directory/data");
        }
        return true;
    }
    /**
     * Outputs a Profile.php  file in the given directory containing profile
     * data based on new and old data sources
     *
     * This function creates a Profile.php file if it doesn't exist. A given
     * field is output in the profile
     * according to the precedence that a new value is preferred to an old
     * value is preferred to the value that comes from a currently defined
     * constant. It might be the case that a new value for a given field
     * doesn't exist, etc.
     *
     * @param string $directory the work directory to output the Profile.php
     *     file
     * @param array $new_profile_data fields and values containing at least
     *     some profile information (only $this->profile_fields
     * fields of $new_profile_data will be considered).
     * @param array $old_profile_data fields and values that come from
     *     presumably a previously existing profile
     * @param bool $reset whether the new profile data is coming from a reset
     *     to factory settings or not
     */
    public function updateProfile($directory, $new_profile_data,
        $old_profile_data, $reset = false)
    {
        $n = [];
        $n[] = <<<EOT
<?php
/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009-2012  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett chris@pollett.org
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2009-2012
 * @filesource
 */
namespace seekquarry\yioop\configs;

/**
 * Computer generated file giving the key defines of directory locations
 * as well as database settings used to run the SeekQuarry/Yioop search engine
 */
EOT;
        $base_url = C\baseUrl();
        //make sure certain fields are not null
        $not_null_fields = [
            'AD_LOCATION' => 'none',
            'AUTOLOGOUT' => C\ONE_HOUR,
            'BACKGROUND_COLOR' => "#FFF",
            'COOKIE_LIFETIME' => C\ONE_YEAR,
            'DIFFERENTIAL_PRIVACY' => false,
            'CSRF_TOKEN' => "YIOOP_TOKEN",
            'FAVICON' => "favicon.ico",
            'FOREGROUND_COLOR' => "#FFF",
            'LOGO_SMALL' => "resources/yioop-small.png",
            'LOGO_MEDIUM' => "resources/yioop-medium.png",
            'LOGO_LARGE' => "resources/yioop-large.png",
            'MEDIA_MODE' => "name_server",
            'RECOVERY_MODE' => C\EMAIL_RECOVERY,
            'SESSION_NAME' => "yioopbiscuit",
            'SIDEBAR_COLOR' => "#F8F8F8",
            'TIMEZONE' => 'America/Los_Angeles',
            'TOPBAR_COLOR' => "#F5F5FF",
        ];
        /*
            Fields where 0 (or "0") is a meaningful, explicit setting and
            must not be silently overwritten by the not-null fallback
            (which fires on any falsy value). RECOVERY_MODE is one such:
            NO_RECOVERY is the integer 0 and is a deliberate choice by
            an admin that has to be honored across saves.
         */
        $zero_is_valid_fields = ['RECOVERY_MODE'];
        $not_null_keys = array_keys($not_null_fields);
        $file_fields = $this->file_fields;
        //now integrate the different profiles
        foreach ($this->profile_fields as $field) {
            if (isset($new_profile_data[$field])) {
                if (!$reset && in_array($field,
                    ['LOGO_SMALL', 'LOGO_MEDIUM', 'LOGO_LARGE',
                    'FAVICON', 'SEARCHBAR_PATH',
                    'BACKGROUND_IMAGE'])) {
                    if (isset($new_profile_data[$field]['name']) &&
                        isset($new_profile_data[$field]['tmp_name'])) {
                        if (empty($new_profile_data[$field]['data'])) {
                            move_uploaded_file(
                            $new_profile_data[$field]['tmp_name'], C\APP_DIR .
                            "/resources/". $new_profile_data[$field]['name']);
                        } else {
                            file_put_contents(C\APP_DIR . "/resources/".
                                $new_profile_data[$field]['name'],
                                $new_profile_data[$field]['data']);
                        }
                        if (C\REDIRECTS_ON) {
                            $profile[$field] = "wd/resources/" .
                                $new_profile_data[$field]['name'];
                        } else {
                            $profile[$field] =
                                "?c=resource&amp;a=get&amp;" .
                                "f=resources&amp;n=" .
                                $new_profile_data[$field]['name'];
                        }
                    } elseif (isset($old_profile_data[$field])) {
                        $profile[$field] = $old_profile_data[$field];
                    } elseif (nsdefined($field)) {
                        $profile[$field] =  constant(C\NS_CONFIGS . $field);
                    } else {
                        $profile[$field] = "";
                    }
                } else {
                    $profile[$field] = $new_profile_data[$field];
                }
            } elseif (isset($old_profile_data[$field])) {
                $profile[$field] = $old_profile_data[$field];
            } elseif (C\nsdefined($field)) {
                $profile[$field] = constant(C\NS_CONFIGS . $field);
            } else {
                $profile[$field] = "";
            }
            if (!$profile[$field] && isset($not_null_fields[$field])) {
                if (!self::shouldKeepZero($field, $profile[$field],
                    $new_profile_data, $old_profile_data,
                    $zero_is_valid_fields)) {
                    $profile[$field] = $not_null_fields[$field];
                }
            }
            //don't let the database systems be unspecified
            if (empty($profile["DBMS"])) {
                $profile["DBMS"] = 'Sqlite3';
                $profile['DB_NAME'] = "public_default";
            }
            if (empty($profile["PRIVATE_DBMS"])) {
                $profile["PRIVATE_DBMS"] = 'Sqlite3';
                $profile['PRIVATE_DB_NAME'] = "private_default";
            }
            if ($field == "WEB_URI") {
                $profile[$field] = UrlParser::getPath(C\baseUrl());
            }
            if (in_array($field, $file_fields)) {
                continue;
            }
            $raw_value = $profile[$field];
            if ($field != "DEBUG_LEVEL") {
                $profile[$field] = "\"{$profile[$field]}\"";
            }
            $n[] = "nsdefine('$field', {$profile[$field]});";
            C\p($field, $raw_value, true);
        }
        $out = implode("\n", $n);
        if (file_put_contents($directory . C\PROFILE_FILE_NAME, $out)
            !== false) {
            set_error_handler(null);
            @chmod($directory . C\PROFILE_FILE_NAME, 0777);
            $css_name = trim($profile['AUXILIARY_CSS_NAME'] ?? "", '"');
            if (isset($new_profile_data['AUXILIARY_CSS']) &&
                !empty($css_name)) {
                $this->saveThemeStylesheet($css_name,
                    $new_profile_data['AUXILIARY_CSS']);
            }
            restore_error_handler();
            return true;
        }
        return false;
    }
    /**
     * Writes one theme's stylesheet rules to their own file in the
     * folder holding the site's themes, making the folder if it is not
     * there yet. This is the one place a theme's rules are written, and
     * every domain reads them from there.
     *
     * @param string $name the theme's name, which also names its file
     * @param string $css the stylesheet rules to write
     * @return bool whether the rules were written
     */
    public function saveThemeStylesheet($name, $css)
    {
        $theme_name = $this->themeFileName($name);
        if ($theme_name === "") {
            return false;
        }
        $folder = $this->themeFolder();
        set_error_handler(null);
        foreach ([C\APP_DIR . "/css", $folder] as $make) {
            if (!file_exists($make)) {
                @mkdir($make);
                @chmod($make, 0777);
            }
        }
        $css_file = "$folder/$theme_name.css";
        $wrote = file_put_contents($css_file, $css) !== false;
        @chmod($css_file, 0777);
        restore_error_handler();
        return $wrote;
    }
    /**
     * Returns the folder holding the site's CSS themes. Every theme lives
     * here whichever domain uses it: a theme is a thing the site owns and
     * any of its domains may be dressed in, so the list is one list and a
     * domain picks from it rather than keeping a list of its own. It sits
     * beside rather than among the stylesheets the site ships, so that
     * what an admin has added is separable from what came with Yioop.
     *
     * @return string path of the folder holding the site's themes
     */
    public function themeFolder()
    {
        return C\APP_DIR . "/css/" . C\DEFAULT_THEME_FOLDER;
    }
    /**
     * Reduces a theme or domain name to the plain name its file or
     * folder may carry, with any surrounding quotes, directory
     * separators, and parent-folder steps removed, so a name read from a
     * profile or a request can never reach outside the css folder.
     *
     * @param string $name theme or domain name to reduce
     * @return string the name with anything that could change folder
     *      taken out
     */
    public function themeFileName($name)
    {
        $clean_name = str_replace(["/", "\\", "..", "\0"], "",
            trim((string)$name, '"'));
        return trim($clean_name);
    }
    /**
     * Decides whether to preserve a resolved-to-zero profile value
     * across the not-null fallback in updateProfile. Returns true
     * (keep the zero) when the field is listed in
     * $zero_is_valid_fields AND the zero came from an explicit
     * setting in either $new_profile_data or $old_profile_data
     * (not just because the field was missing entirely). Pulled
     * out as a static so the policy can be unit-tested without
     * standing up a work directory.
     *
     * @param string $field profile field being resolved
     * @param mixed $resolved the value already resolved for the
     *      field (only called when this is falsy)
     * @param array $new_profile_data new profile data passed to
     *      updateProfile
     * @param array $old_profile_data old profile data passed to
     *      updateProfile
     * @param array $zero_is_valid_fields list of fields where 0
     *      is a meaningful, deliberate setting
     * @return bool true if the zero should be kept (don't apply
     *      the not-null fallback); false if the fallback should
     *      apply
     */
    public static function shouldKeepZero($field, $resolved,
        $new_profile_data, $old_profile_data, $zero_is_valid_fields)
    {
        return in_array($field, $zero_is_valid_fields) &&
            (isset($new_profile_data[$field]) ||
            isset($old_profile_data[$field])) &&
            (string)$resolved === "0";
    }
    /**
     * Check if $dbinfo provided the connection details for a Yioop/SeekQuarry
     * database. If it does provide a valid db connection but no data then try
     * to recreate the database from the default copy stored in /data dir.
     *
     * @param array $dbinfo has fields for DBMS, DB_USER, DB_PASSWORD, DB_HOST
     *     and DB_NAME
     * @param array $skip_list an array of table or index names not to bother
     *     creating or copying
     * @return bool returns true if can connect to/create a valid database;
     *     returns false otherwise
     */
    public function migrateDatabaseIfNecessary($dbinfo, $skip_list = [])
    {
        $test_dbm = $this->testDatabaseManager($dbinfo);
        if ($test_dbm === false || $test_dbm === true) {
            return $test_dbm;
        }
        $this->initializeSql($test_dbm, $dbinfo);
        $copy_tables = array_diff(array_keys($this->create_statements),
            $skip_list);
        if (!($create_ok = $this->createDatabaseTables($test_dbm, $dbinfo))) {
            return false;
        }
        $default_dbm = new Sqlite3Manager();
        $default_dbm->connect("", "", "", C\BASE_DIR."/data/public_default.db");
        if (!$default_dbm) {
            return false;
        }
        foreach ($copy_tables as $table_or_index) {
            if ($table_or_index != "CURRENT_WEB_INDEX" &&
                stristr($table_or_index, "_INDEX")) {
                continue;
            }
            if (!DatasourceManager::copyTable($table_or_index, $default_dbm,
                $table_or_index, $test_dbm)) {
                return false;
            }
        }
        if (stristr($dbinfo["DB_HOST"], "pgsql") !== false) {
            /* For postgres count initial values of SERIAL sequences
               will be screwed up unless do
             */
            $auto_tables = ["ACTIVITY" =>"ACTIVITY_ID",
                "GROUP_ITEM" =>"ID",
                "GROUP_PAGE" => "ID",
                "SOCIAL_GROUPS" => "GROUP_ID", "LOCALE"=> "LOCALE_ID",
                "ROLE" => "ROLE_ID", "TRANSLATION" => "TRANSLATION_ID",
                "USERS" => "USER_ID"];
            foreach ($auto_tables as $table => $auto_column) {
                $sql = "SELECT MAX($auto_column) AS NUM FROM $table";
                $result = $test_dbm->execute($sql);
                $row = $test_dbm->fetchArray($result);
                $next = $row['NUM'] ?? 1;
                $sequence = strtolower("{$table}_{$auto_column}_seq");
                $sql = "SELECT setval('$sequence', $next)";
                $test_dbm->execute($sql);
            }
        }
        return true;
    }
    /**
     * On a blank database this method create all the tables necessary for
     * Yioop less those on a skip list
     *
     * @param object $dbm a DatabaseManager open to some DBMS and with a
     *     blank database selected
     * @param array $dbinfo name of database, host, user, and password
     * @param array $skip_list an array of table or index names not to bother
     *     creating
     * @return bool whether all of the creates were successful or not
     */
    public function createDatabaseTables($dbm, $dbinfo, $skip_list = [])
    {
        $this->initializeSql($dbm, $dbinfo);
        $create_statements = $this->create_statements;
        foreach ($create_statements as $table_or_index => $statement) {
            if (in_array($table_or_index, $skip_list) || empty($statement)) {
                continue;
            }
            if (!$result = $dbm->execute($statement)) {
                echo $statement." ERROR!";
                return false;
            }
        }
        /* Generate the DKIM signing key for a fresh install so
           outbound mail can be signed; existing installs get it
           from the version 101 upgrade migration instead. */
        DkimKey::ensureKeyPair();
        return true;
    }
    /**
     * On a blank database this method create all the tables necessary for
     * private Yioop less those on a skip list
     *
     * @param object $dbm a DatabaseManager open to some DBMS and with a
     *     blank database selected
     * @param array $dbinfo name of database, host, user, and password
     * @param array $skip_list an array of table or index names not to bother
     *     creating
     * @return bool whether all of the creates were successful or not
     */
    public function createDatabaseTablesPrivate($dbm, $dbinfo, $skip_list = [])
    {
        $this->initializeSqlPrivate($dbm, $dbinfo);
        $private_create_statements = $this->private_create_statements;
        foreach ($private_create_statements as $table_or_index => $statement) {
            if (in_array($table_or_index, $skip_list)) {
                continue;
            }
            if (!$result = $dbm->execute($statement)) {
                echo $statement . " ERROR!";
                return false;
            }
        }
        return true;
    }
    /**
     * Checks if $dbinfo provides info to connect to an working instance of
     * app db.
     *
     * @param array $dbinfo has field for DBMS, DB_USER, DB_PASSWORD, DB_HOST
     *     and DB_NAME
     * @return mixed returns true if can connect to DBMS with username and
     *     password, can select the given database name and that database
     *     seems to be of Yioop/SeekQuarry type. If the connection works
     *     but database isn't there it attempts to create it. If the
     *     database is there but no data, then it returns a resource for
     *     the database. Otherwise, it returns false.
     */
    public function testDatabaseManager($dbinfo)
    {
        if (!isset($dbinfo['DBMS'])) {
            return false;
        }
        $dbms_manager = C\NS_DATASOURCES . ucfirst($dbinfo['DBMS'])."Manager";
        $test_dbm = new $dbms_manager();
        $fields = ['DB_HOST', 'DB_USER', 'DB_PASSWORD', 'DB_NAME'];
        foreach ($fields as $field) {
            if (!isset($dbinfo[$field])) {
                $dbinfo[$field] = constant($field);
            }
        }
        $host = $dbinfo['DB_HOST'];
            // for postgres database needs to already exists
        $host = str_ireplace("database=".$dbinfo['DB_NAME'], "",
            $host); // informix, ibm (use connection string DSN)
        $host = str_replace(";;", ";", $host);
        $host = trim($host);
        $conn = $test_dbm->connect($host, $dbinfo['DB_USER'],
            $dbinfo['DB_PASSWORD'], "");
        if ($conn === false) {
            return false;
        }
        //check if can select db or if not create it
        $q = "";
        if (isset($test_dbm->special_quote)) {
            $q = $test_dbm->special_quote;
        }
        set_error_handler(null);
        $is_mysql = (stristr($dbinfo['DBMS'], 'Mysql') !== false);
        $charset_clause = ($is_mysql) ? " CHARACTER SET utf8mb4 ": "";
        @$test_dbm->execute("CREATE DATABASE $q" . $dbinfo['DB_NAME'] . "$q" .
            $charset_clause);
        $test_dbm->disconnect();
        if (!$test_dbm->connect(
            $dbinfo['DB_HOST'], $dbinfo['DB_USER'],
            $dbinfo['DB_PASSWORD'], $dbinfo['DB_NAME'])) {
            return false;
        }
        /*  check if need to create db contents.
            We check if any locale exists as proxy for contents being okay.
            Temporarily disable more aggressive yioop error handler while do
            this
         */
        $sql = "SELECT LOCALE_ID FROM LOCALE";
        $result = $test_dbm->execute($sql);
        restore_error_handler();
        if ($result !== false && $test_dbm->fetchArray($result) !== false) {
            return true;
        }
        return $test_dbm;
    }
    /**
     * Modifies the config.php file so the WORK_DIRECTORY define points at
     * $directory
     *
     * @param string $directory folder that WORK_DIRECTORY should be defined to
     * @return bool true on a successful rewrite of Config.php; false
     *      when the configurable section markers can't be found or
     *      the write fails
     */
    public function setWorkDirectoryConfigFile($directory)
    {
        $dir_value = "'$directory'";
        if ($directory == C\DEFAULT_WORK_DIRECTORY) {
            $dir_value = "DEFAULT_WORK_DIRECTORY";
        }
        $config = file_get_contents(C\BASE_DIR . "/configs/Config.php");
        $start_machine_section = strpos($config,
            '/*+++ The next block of code');
        if ($start_machine_section === false) {
            return false;
        }
        $end_machine_section = strpos($config, '/*++++++*/');
        if ($end_machine_section === false) {
            return false;
        }
        $out = substr($config,  0, $start_machine_section);
        $out .= "/*+++ The next block of code is machine edited, change at\n".
            "your own risk, please use configure web page instead +++*/\n";
        $out .= "nsdefine('WORK_DIRECTORY', $dir_value);\n";
        $out .= substr($config, $end_machine_section);
        if (file_put_contents(C\BASE_DIR."/configs/Config.php", $out)) {
            return true;
        }
        return false;
    }
    /**
     * Reads a profile from a Profile.php file in the provided directory
     *
     * @param string $work_directory directory to look for profile in
     * @return array associate array of the profile fields and their values
     */
    public function getProfile($work_directory)
    {
        $profile = [];
        $profile_string = @file_get_contents($work_directory .
            C\PROFILE_FILE_NAME);
        $file_fields = $this->file_fields;
        foreach ($this->profile_fields as $field) {
            if (!in_array($field, $file_fields)) {
                $profile[$field] = $this->matchDefine($field,
                    $profile_string);
                if ($profile[$field] === "" &&
                        isset(self::$profile_defaults[$field])) {
                    $profile[$field] =
                        self::$profile_defaults[$field];
                }
                if ($field == "AD_LOCATION" && $profile[$field] == "") {
                    $profile[$field] = "none";
                }
            }
        }
        if (!empty($profile["AUXILIARY_CSS_NAME"])) {
            $profile['AUXILIARY_CSS'] =
                $this->getThemeStylesheet($profile["AUXILIARY_CSS_NAME"]);
        }
        return $profile;
    }
    /**
     * Reads one theme's stylesheet rules from its file in the folder
     * holding the site's themes. A theme whose file has gone missing
     * reads as no rules at all rather than as an error, so a profile or
     * a domain still loads when it names a theme that is no longer
     * there.
     *
     * @param string $name the theme's name, which also names its file
     * @return string the theme's stylesheet rules, empty when the theme
     *      has none or its file is gone
     */
    public function getThemeStylesheet($name)
    {
        $theme_name = $this->themeFileName($name);
        if ($theme_name === "") {
            return "";
        }
        $css_file = $this->themeFolder() . "/$theme_name.css";
        return (file_exists($css_file)) ? file_get_contents($css_file) : "";
    }
    /**
     * Get a list of auxliary stylesheets which can be applied in
     * addition to the default stylesheet across the site and which
     * can be used to control the styling of individual wiki pages.
     * The site keeps one list of themes, which every domain and every
     * wiki page picks from.
     *
     * @return array list of the available css themes
     */
    public function getThemeNames()
    {
        $folder_prefix = $this->themeFolder() . "/";
        $folder_prefix_len = strlen($folder_prefix);
        $pre_themes = glob("$folder_prefix*.css");
        $theme_names = [];
        foreach ($pre_themes as $pre_theme) {
            $theme_names[] = substr($pre_theme, $folder_prefix_len, -4);
        }
        return $theme_names;
    }
    /**
     * Deletes the CSS theme with the provided name from the folder
     * holding the site's themes. The list is shared, so a theme deleted
     * here is gone for every domain that was using it.
     *
     * @param string $theme_name name of the theme to delete
     */
    public function deleteTheme($theme_name)
    {
        $name = $this->themeFileName($theme_name);
        if ($name === "") {
            return;
        }
        $theme_path = $this->themeFolder() . "/$name.css";
        if (file_exists($theme_path)) {
            unlink($theme_path);
        }
    }
    /**
     * Finds the first occurrence of define('$defined', something) in $string
     * and returns something
     *
     * @param string $defined the constant being defined
     * @param string $string the haystack string to search in
     * @return string matched value of define if exists; empty string otherwise
     */
    public function matchDefine($defined, $string)
    {
        preg_match("/nsdefine\((?:\"$defined\"|\'$defined\')\,([^\)]*)\)/",
            $string, $match);
        $match = (isset($match[1])) ? trim($match[1]) : "";
        $len = strlen($match);
        if ($len >=2 && ($match[0] == '"' || $match[0] == "'")) {
            $match = substr($match, 1, strlen($match) - 2);
        }
        return $match;
    }
}
