<?PHP

$GLOBALS["G_ErrMsgs"] = SiteUpgrade390_PerformUpgrade();

/**
* Perform all of the site upgrades for 3.9.0.
* @return Returns NULL on success and an eror message if an error occurs.
*/
function SiteUpgrade390_PerformUpgrade()
{
    try
    {
        Msg(1, "Adding Has No Password field to User Schema...");
        SiteUpgrade390_UpdateUserSchema();

        Msg(1, "Migrating User fields to new storage format...");
        SiteUpgrade390_MultipleUserValues();

        Msg(1, "Migrating SavedSearches to new storage format...");
        SiteUpgrade390_MigrateSavedSearches();

        Msg(1, "Migrating EventLog information for searches to new storage format...");
        SiteUpgrade390_MigrateEventLog();

        Msg(1, "Migrating per-user search field selections to new format...");
        SiteUpgrade390_MigrateSearchSelections();

        Msg(1, "Making sure all metadata field names are compliant...");
        SiteUpgrade390_CheckMetadataFieldNames();

        Msg(1, "Making sure fields don't have values from other schemas...");
        SiteUpgrade390_FixDBFieldDefaults();

        Msg(1, "Enabling Backward Compatibility plugin...");
        $Plugin = $GLOBALS["G_PluginManager"]->GetPlugin(
                "BackwardCompatibility", TRUE);
        $Plugin->IsEnabled(TRUE);
    }

    catch (Exception $Exception)
    {
        return array($Exception->getMessage(),
                "Exception Trace:<br/><pre>"
                        .$Exception->getTraceAsString()."</pre>");
    }
}

/**
* Add the 'Has No Password' database column.
*/
function SiteUpgrade390_UpdateUserSchema()
{
    $Schema = new MetadataSchema(MetadataSchema::SCHEMAID_USER);

    if ($Schema->GetFieldByName("Has No Password") === NULL)
    {
        $Schema->AddFieldsFromXmlFile(
            "install/MetadataSchema--User.xml");
    }
}

/**
* Modify database schema to support multiple values in user fields.
*/
function SiteUpgrade390_MultipleUserValues()
{
    $DB = new Database();

    $DB->SetQueryErrorsToIgnore(
        array("/CREATE TABLE /i" => "/Table '[a-z0-9_]+' already exists/i"));

    # attempt to create the ResourceUser intersection table
    $DB->Query(
        "CREATE TABLE ResourceUserInts (".
        "ResourceId INT NOT NULL,".
        "FieldId INT NOT NULL,".
        "UserId INT NOT NULL,".
        "INDEX Index_U (UserId),".
        "UNIQUE UIndex_RU (ResourceId, FieldId, UserId) )");

    # see if this generated an error
    if ($DB->IgnoredError() !== FALSE)
    {
        # if so, the table already existed and we're done
        return;
    }

    # however, if table creation succeeded then we need to popualte the table

    # iterate over every schema
    foreach (MetadataSchema::GetAllSchemas() as $SchemaId => $Schema)
    {
        foreach ($Schema->GetFields(MetadataSchema::MDFTYPE_USER) as
                 $FieldId => $Field)
        {
            # get the name of the db column
            $DBFieldName = $Field->DBFieldName();

            # migrate the existing data into our intersection table
            $DB->Query(
                "INSERT INTO ResourceUserInts ".
                "SELECT ResourceId,".$FieldId.",".$DBFieldName." FROM Resources ".
                "WHERE ".$DBFieldName." IS NOT NULL");

            # nuke the existing column
            $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."`");
        }
    }
}

/**
* Move SavedSearches to new storage format.
*/
function SiteUpgrade390_MigrateSavedSearches()
{
    $DB = new Database();

    # check if the SearchData column exists
    $DB->Query("LOCK TABLES SavedSearches WRITE");
    if ($DB->FieldExists("SavedSearches", "SearchData"))
    {
        # if so, the migration is already complete and we should exit
        $DB->Query("UNLOCK TABLES");
        return;
    }

    # if not, it needs to be added
    $DB->Query(
        "ALTER TABLE SavedSearches ADD COLUMN SearchData TEXT DEFAULT NULL");
    $DB->Query("UNLOCK TABLES");

    # otherwise, we need to conver the old search data
    $Schema = new MetadataSchema();

    $DB->Query("SELECT SearchId FROM SavedSearches");
    $SearchIds = $DB->FetchColumn("SearchId");
    foreach ($SearchIds as $SearchId)
    {
        # create a new parameter set
        $SearchParams = new SearchParameterSet();

        # for each text search parameter
        $DB->Query("SELECT * FROM SavedSearchTextParameters"
                   ." WHERE SearchId = ".$SearchId);
        while ($Record = $DB->FetchRow())
        {
            # add parameter to search criteria
            if ($Record["FieldId"] == -101)
            {
                $SearchParams->AddParameter($Record["SearchText"]);
            }
            else
            {
                $SearchParams->AddParameter(
                    $Record["SearchText"], $Record["FieldId"]);
            }
        }

        # extract the per-field search value IDs from the database
        $Subgroups = array();
        $DB->Query("SELECT * FROM SavedSearchIdParameters"
                   ." WHERE SearchId = ".$SearchId);
        while ($Record = $DB->FetchRow())
        {
            $Subgroups[$Record["FieldId"]][]= $Record["SearchValueId"];
        }

        # iterate over each subgroup
        foreach ($Subgroups as $FieldId => $SearchValues)
        {
            # translate the ValueIds back to search strings
            $SearchStrings = SearchParameterSet::TranslateLegacySearchValues(
                $FieldId, $SearchValues);

            # create the corresponding SearchParameterSet
            $SubParams = new SearchParameterSet();
            $SubParams->Logic("OR");
            $SubParams->AddParameter($SearchStrings, $FieldId);

            # add it to our parent SearchParameterSet
            $SearchParams->AddSet($SubParams);
        }

        # and set the SPS for this SavedSearch
        $SavedSearch = new SavedSearch($SearchId);
        $SavedSearch->SearchParameters($SearchParams);
    }

    # drop the now unused SavedSearch(Text|Id)Parameters tables
    $DB->Query("DROP TABLE SavedSearchTextParameters");
    $DB->Query("DROP TABLE SavedSearchIdParameters");
}

function SiteUpgrade390_MigrateEventLog()
{
    $DB = new Database();
    $DB->Caching(FALSE);

    if ($DB->FieldExists("EventLog", "EventId"))
    {
        # if so, the migration is already complete and we should exit
        return;
    }

    # this may take a while, avoid timing out
    set_time_limit(3600);

    $DB->Query("ALTER TABLE EventLog ADD COLUMN EventId "
            ."INTEGER PRIMARY KEY AUTO_INCREMENT");

    $DB->Query("SELECT EventId FROM EventLog WHERE "
            ."EventType IN (".SPTEventLog::SPTEVENT_SEARCH.","
                .SPTEventLog::SPTEVENT_ADVANCEDSEARCH.") "
            ."AND DataOne IS NOT NULL "
            ."AND LENGTH(DataOne)>0 "
            ."AND DataOne NOT LIKE 'a:%'" );
    $EventIds = $DB->FetchColumn("EventId");

    foreach ($EventIds as $EventId)
    {
        $DB->Query("SELECT DataOne, EventDate FROM "
            ."EventLog WHERE EventId=".$EventId);
        $Row = $DB->FetchRow();

        try
        {
            $SearchParams = new SearchParameterSet();
            $SearchParams->SetFromLegacyUrl($Row["DataOne"]);

            $DB->Query("UPDATE EventLog "
                    ."SET DataOne='".addslashes($SearchParams->Data())."', "
                    ."EventDate='".$Row["EventDate"]."' "
                    ."WHERE EventId=".$EventId );
        }
        catch (Exception $e)
        {
            ; # skip events we can't migrate
        }
    }
}

function SiteUpgrade390_MigrateSearchSelections()
{
    $DB = new Database();

    $DB->Query("SELECT UserId, SearchSelections "
            ."FROM APUsers WHERE SearchSelections IS NOT NULL");

    $SearchData = $DB->FetchColumn("SearchSelections", "UserId");

    foreach ($SearchData as $UserId => $OldSetting)
    {
        $SearchSelections = unserialize($OldSetting);

        if (!is_array($SearchSelections))
        {
            continue;
        }

        foreach ($SearchSelections as &$Item)
        {
            if ($Item == "Keyword")
            {
                $Item = "KEYWORD";
            }
        }

        $NewSetting = serialize($SearchSelections);
        if ($NewSetting != $OldSetting)
        {
            $DB->Query("UPDATE APUsers SET "
                    ."SearchSelections='".addslashes($NewSetting)."' "
                    ."WHERE UserId=".intval($UserId));
        }
    }
}

/*
* Check to make sure no metadata field name can be interpreted as a number.
*/
function SiteUpgrade390_CheckMetadataFieldNames()
{
    $Schemas = MetadataSchema::GetAllSchemas();
    foreach ($Schemas as $SchemaId => $Schema)
    {
        foreach ($Schema->GetFields(NULL, NULL, TRUE) as $FieldId => $Field)
        {
            if (is_numeric($Field->Name()))
            {
                $Field->Name("X".$Field->Name());
            }
        }
    }
}

/**
* Make sure that database fields default to NULL so that we don't get
* spurious values assigned in fields that belong to a different
* schema.
*/
function SiteUpgrade390_FixDBFieldDefaults()
{
    $DB = new Database();

    # fields where the SQL allows a default
    $TypesWithDefaults = MetadataSchema::MDFTYPE_NUMBER |
                  MetadataSchema::MDFTYPE_FLAG |
                  MetadataSchema::MDFTYPE_DATE |
                  MetadataSchema::MDFTYPE_TIMESTAMP;

    # fields where we want to clear spurious values, but mysql doesn't
    # allow defaults
    $TypesWithoutDefaults = MetadataSchema::MDFTYPE_TEXT |
                  MetadataSchema::MDFTYPE_PARAGRAPH |
                  MetadataSchema::MDFTYPE_URL ;

    # iterate over all schemas
    $Schemas = MetadataSchema::GetAllSchemas();
    foreach ($Schemas as $SchemaId => $Schema)
    {
        # for the fields that can have a default
        foreach ($Schema->GetFields($TypesWithDefaults, NULL, TRUE)
                 as $FieldId => $Field)
        {
            # if this is a date field, handle the 'Begin' and 'End' cols and clear out
            # bogus values in them
            if ($Field->Type() == MetadataSchema::MDFTYPE_DATE)
            {
                foreach (array("Begin", "End") as $Suffix)
                {
                    if ($DB->FieldExists("Resources", $Field->DBFieldName().$Suffix))
                    {
                        $DB->Query("ALTER TABLE Resources ALTER COLUMN "
                                   ."`".$Field->DBFieldName().$Suffix."`"
                                   ." SET DEFAULT NULL");
                        $DB->Query("UPDATE Resources SET "
                                   ."`".$Field->DBFieldName().$Suffix."` = NULL "
                                   ."WHERE SchemaId != ".$SchemaId);
                    }
                }
            }
            else
            {
                # otherwise, just clear the default off the column itself
                if ($DB->FieldExists("Resources", $Field->DBFieldName()))
                {
                    if ($Field->Type() == MetadataSchema::MDFTYPE_TIMESTAMP)
                    {
                        # for timestamps, just drop the default as they cannot default
                        # to null
                        $DB->Query("ALTER TABLE Resources ALTER COLUMN "
                                   ."`".$Field->DBFieldName()."`"
                                   ." DROP DEFAULT");
                    }
                    else
                    {
                        # for everything else, default to null
                        $DB->Query("ALTER TABLE Resources ALTER COLUMN "
                                   ."`".$Field->DBFieldName()."`"
                                   ." SET DEFAULT NULL");
                    }

                    $DB->Query("UPDATE Resources SET "
                               ."`".$Field->DBFieldName()."` = NULL "
                               ."WHERE SchemaId != ".$SchemaId);
                }
            }
        }

        # next, iterate over fields that aren't allowed to have a default
        # and null out spurious values
        foreach ($Schema->GetFields($TypesWithoutDefaults, NULL, TRUE)
                 as $FieldId => $Field)
        {
            if ($DB->FieldExists("Resources", $Field->DBFieldName()))
            {
                $DB->Query("UPDATE Resources SET "
                           ."`".$Field->DBFieldName()."` = NULL "
                           ."WHERE SchemaId != ".$SchemaId);
            }
        }
    }
}
