<?PHP

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

/**
* Perform all of the site upgrades for 3.0.0.
* @return Returns NULL on success and an eror message if an error occurs.
*/
function SiteUpgrade300_PerformUpgrade()
{
    SiteUpgrade300_RenameDBColumns();
    SiteUpgrade300_MigrateImageAssociations();

    try
    {
        # make sure all the schemas have a MetadataFieldOrder
        Msg(1, "Upgrading field ordering for Resource schema...");
        SiteUpgrade300_UpgradeFieldOrder();

        # remove any existing user data that is probably garbage. this must be
        # called before creating the metadata fields or it will have no effect
        Msg(1, "Removing extraneous user data...");
        SiteUpgrade300_RemoveGarbageUserData();

        # create the fields for the default user columns
        Msg(1, "Creating fields for default user columns...");
        SiteUpgrade300_CreateFieldsForDefaultUserColumns();

        # fix the schema privileges
        Msg(1, "Fixing schema privileges...");
        SiteUpgrade300_FixSchemaPrivileges();

        # create the fields for the custom user columns
        Msg(1, "Creating fields for custom user columns...");
        SiteUpgrade300_CreateFieldsForCustomUserColumns();

        # migrate over existing user data
        Msg(1, "Migrating over existing user data...");
        SiteUpgrade300_MigrateUserData();
    }

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

/**
* Split off / rename database columns in the Resources table for schemas
* other than the default, to prevent collisions.
*/
function SiteUpgrade300_RenameDBColumns()
{
    # define list of field types that have columns we need to change
    $PertinentFieldTypes = MetadataSchema::MDFTYPE_TEXT
                |MetadataSchema::MDFTYPE_PARAGRAPH
                |MetadataSchema::MDFTYPE_URL
                |MetadataSchema::MDFTYPE_NUMBER
                |MetadataSchema::MDFTYPE_USER
                |MetadataSchema::MDFTYPE_POINT
                |MetadataSchema::MDFTYPE_FILE
                |MetadataSchema::MDFTYPE_FLAG
                |MetadataSchema::MDFTYPE_DATE
                |MetadataSchema::MDFTYPE_TIMESTAMP;

    # build list of columns to not delete
    $DoNotDelete = array();
    $Schema = new MetadataSchema(MetadataSchema::SCHEMAID_DEFAULT);
    $Fields = $Schema->GetFields($PertinentFieldTypes);
    foreach ($Fields as $FieldId => $Field)
    {
        $DoNotDelete[] = $Field->DBFieldName();
    }

    # for each schema
    $DB = new Database();
    $Schemas = MetadataSchema::GetAllSchemas();
    foreach ($Schemas as $SchemaId => $Schema)
    {
        # skip default schema
        if ($SchemaId == MetadataSchema::SCHEMAID_DEFAULT) {  continue;  }

        # get fields that have database columns
        $Fields = $Schema->GetFields($PertinentFieldTypes);

        # for each field
        foreach ($Fields as $FieldId => $Field)
        {
            # get old and new base column names
            $NewName = $Field->DBFieldName();
            $OldName = preg_replace('/'.$Schema->Id().'$/', "", $NewName);

            # determine column names and types
            switch ($Field->Type())
            {
                case MetadataSchema::MDFTYPE_TEXT:
                case MetadataSchema::MDFTYPE_PARAGRAPH:
                case MetadataSchema::MDFTYPE_URL:
                    $ColInfo[$NewName] = array(
                            " " => "TEXT".($Field->Optional() ? "" : " NOT NULL"),
                            );
                    break;

                case MetadataSchema::MDFTYPE_NUMBER:
                case MetadataSchema::MDFTYPE_USER:
                    $ColInfo[$NewName] = array(
                            " " => "INT".($Field->Optional() ? "" : " NOT NULL"),
                            );
                    break;

                case MetadataSchema::MDFTYPE_POINT:
                    $ColInfo[$NewName] = array(
                            "X" => "DECIMAL(".$Field->PointPrecision()
                                    .",".$Field->PointDecimalDigits().")",
                            "Y" => "DECIMAL(".$Field->PointPrecision()
                                    .",".$Field->PointDecimalDigits().")",
                            );
                    break;

                case MetadataSchema::MDFTYPE_FILE:
                    $ColInfo[$NewName] = array(
                            " " => "TEXT",
                            );
                    break;

                case MetadataSchema::MDFTYPE_FLAG:
                    $ColInfo[$NewName] = array(
                            " " => "INT DEFAULT ".($Field->DefaultValue() ? "1" : "0"),
                            );
                    break;

                case MetadataSchema::MDFTYPE_DATE:
                    $ColInfo[$NewName] = array(
                            "Begin" => "DATE".($Field->Optional() ? "" : " NOT NULL"),
                            "End" => "DATE".($Field->Optional() ? "" : " NOT NULL"),
                            "Precision" => "INT".($Field->Optional() ? "" : " NOT NULL"),
                            );
                    break;

                case MetadataSchema::MDFTYPE_TIMESTAMP:
                    $ColInfo[$NewName] = array(
                            " " => "DATETIME".($Field->Optional() ? "" : " NOT NULL"),
                            );
                    break;
            }

            # for each column
            foreach ($ColInfo[$NewName] as $ColSuffix => $ColType)
            {
                # if column not already processed
                $ColSuffix = trim($ColSuffix);
                if (!$DB->FieldExists("Resources", $NewName.$ColSuffix))
                {
                    # create new column
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `"
                            .$NewName.$ColSuffix."` ".$ColType);

                    # copy over values from old column
                    $DB->Query("UPDATE Resources SET `".$NewName.$ColSuffix
                            ."` = `".$OldName.$ColSuffix
                            ."` WHERE SchemaId = ".intval($Schema->Id()));
                }
            }
        }
    }

    # for each schema
    foreach ($Schemas as $SchemaId => $Schema)
    {
        # skip default schema
        if ($SchemaId == MetadataSchema::SCHEMAID_DEFAULT) {  continue;  }

        # get fields that have database columns
        $Fields = $Schema->GetFields($PertinentFieldTypes);

        # for each field
        foreach ($Fields as $FieldId => $Field)
        {
            # get old and new base column names
            $NewName = $Field->DBFieldName();
            $OldName = preg_replace('/'.$Schema->Id().'$/', "", $NewName);

            # for each column
            foreach ($ColInfo[$NewName] as $ColSuffix => $ColType)
            {
                # if old column still exists
                $ColSuffix = trim($ColSuffix);
                if ($DB->FieldExists("Resources", $OldName.$ColSuffix))
                {
                    # if column is not on Do Not Delete list
                    if (!in_array($OldName, $DoNotDelete))
                    {
                        # delete old column
                        $DB->Query("ALTER TABLE Resources DROP COLUMN `"
                                .$OldName.$ColSuffix."`");
                    }
                }
            }
        }
    }
}

/**
* Migrate the association between images and resources from the old format
* (stored in Resources) to the new format (stored in ResourceImageInts).
*/
function SiteUpgrade300_MigrateImageAssociations()
{
    # for each schema
    $Schemas = MetadataSchema::GetAllSchemas();
    foreach ($Schemas as $SchemaId => $Schema)
    {
        # for each Image metadata field
        $DB = new Database();
        $Fields = $Schema->GetFields(MetadataSchema::MDFTYPE_IMAGE);
        foreach ($Fields as $Field)
        {
            # if field still has data in Resources table
            $ColumnName = $Field->DBFieldName();
            if ($DB->FieldExists("Resources", $ColumnName))
            {
                # get entries from resource table
                $DB->Query("SELECT ResourceId, ".$ColumnName." FROM Resources"
                        ." WHERE ".$ColumnName." > 0");
                $Images = $DB->FetchColumn($ColumnName, "ResourceId");

                # for each entry in resources table
                foreach ($Images as $ResourceId => $ImageId)
                {
                    # add entry to resource/image intersection table
                    $DB->Query("INSERT INTO ResourceImageInts"
                            ." (ResourceId, FieldId, ImageId) VALUES"
                            ." (".intval($ResourceId).", "
                            .intval($Field->Id()).", "
                            .intval($ImageId).")");
                }

                # remove image data for field from Resources table
                $DB->Query("ALTER TABLE Resources DROP COLUMN ".$ColumnName);
            }
        }
    }
}

/**
* Remove garbage user data if it exists. This *must* be called before the user
* metadata fields are created or it will have no effect.
* @throws Exception if an error occurs.
*/
function SiteUpgrade300_RemoveGarbageUserData()
{
    $DB = new Database();
    # see if there are preexisting users
    $Result = $DB->Query("
        SELECT * FROM Resources
        WHERE `SchemaId` = '".intval(MetadataSchema::SCHEMAID_USER)."'
        LIMIT 1");

    # there was an error when executing the query
    if ($Result === FALSE)
    {
        throw new Exception("The query to check for preexisting users has failed.");
    }

    # found preexisting user data
    if ($DB->NumRowsSelected() > 0)
    {
        $Schema = new MetadataSchema(MetadataSchema::SCHEMAID_USER);
        $Fields = $Schema->GetFields();

        # do nothing if any fields already exist
        if (count($Fields) > 0)
        {
            return;
        }

        # no fields exist so delete the user data because it's probably garbage
        # and will interfere with the upgrade
        else
        {
            $Result = $DB->Query("
                DELETE FROM Resources
                WHERE SchemaId = '".intval(MetadataSchema::SCHEMAID_USER)."'");

            # there was an error when executing the query
            if ($Result === FALSE)
            {
                throw new Exception("Could not remove pre-existing user resources.");
            }
        }
    }
}

/**
* Create the metadata fields for default user columns in the APUsers table.
* @throws Exception if an error occurs.
*/
function SiteUpgrade300_CreateFieldsForDefaultUserColumns()
{
    $Schema = new MetadataSchema(MetadataSchema::SCHEMAID_USER);
    $SchemaXml = "install/MetadataSchema--User.xml";

    # make sure the XML file exists
    if (!file_exists($SchemaXml))
    {
        throw new Exception("The XML file for user metadata schema was not found.");
    }

    # add the default columns from the XML file
    $Result = $Schema->AddFieldsFromXmlFile($SchemaXml);

    # failed to add the fields
    if ($Result !== TRUE)
    {
        $SchemaErrors = $Schema->ErrorMessages();
        $Msg = "";
        foreach ($SchemaErrors as $Errors)
        {
            foreach ($Errors as $Error)
            {
                $Msg .= $Error."<br/>";
            }
        }
        throw new Exception($Msg);
    }
}

/**
* Fix the schema privileges for the user schema.
*/
function SiteUpgrade300_FixSchemaPrivileges()
{
    $Schema = new MetadataSchema(MetadataSchema::SCHEMAID_USER);

    # get the newly-created user ID field
    $UserIdField = $Schema->GetFieldByName("UserId");

    # setup the default privileges
    $ViewingPrivileges = new PrivilegeSet();
    $AuthoringPrivileges = new PrivilegeSet();
    $EditingPrivileges = new PrivilegeSet();
    $ViewingPrivileges->AddCondition($UserIdField);
    $ViewingPrivileges->AddPrivilege(PRIV_USERADMIN);
    $ViewingPrivileges->AddPrivilege(PRIV_SYSADMIN);
    $EditingPrivileges->AddCondition($UserIdField);
    $EditingPrivileges->AddPrivilege(PRIV_USERADMIN);
    $EditingPrivileges->AddPrivilege(PRIV_SYSADMIN);

    # set the privileges
    $Schema->ViewingPrivileges($ViewingPrivileges);
    $Schema->AuthoringPrivileges($AuthoringPrivileges);
    $Schema->EditingPrivileges($EditingPrivileges);
}

/**
* Create the metadata fields for custom user columns in the APUsers table.
* @throws Exception if an error occurs.
*/
function SiteUpgrade300_CreateFieldsForCustomUserColumns()
{
    $Schema = new MetadataSchema(MetadataSchema::SCHEMAID_USER);

    # add the migration info for custom user fields
    foreach (SiteUpgrade300_GetUserTableStructure() as $Column)
    {
        # skip default columns because they're added as part of an XML file
        if (SiteUpgrade300_IsDefaultUserColumn($Column))
        {
            continue;
        }

        # skip the field if it already exists
        if ($Schema->FieldExists($Column["Field"]))
        {
            continue;
        }

        # create the new field
        $Field = MetadataField::Create(
            MetadataSchema::SCHEMAID_USER,
            SiteUpgrade300_GetCustomColumnType($Column),
            $Column["Field"],
            $Column["Null"] == "YES",
            $Column["Default"]);

        # set some additional values
        $Field->Label(SiteUpgrade300_GetCustomColumnLabel($Column));
        $Field->Description("This is a custom user field migrated from a"
            ." version of CWIS prior to 3.0.0.");

        # setup the default privileges for the custom field. make them very
        # restrictive since it's unknown how the columns were used
        $ViewingPrivileges = new PrivilegeSet();
        $AuthoringPrivileges = new PrivilegeSet();
        $EditingPrivileges = new PrivilegeSet();
        $ViewingPrivileges->AddPrivilege(PRIV_USERADMIN);
        $ViewingPrivileges->AddPrivilege(PRIV_SYSADMIN);
        $AuthoringPrivileges->AddPrivilege(PRIV_USERADMIN);
        $AuthoringPrivileges->AddPrivilege(PRIV_SYSADMIN);
        $EditingPrivileges->AddPrivilege(PRIV_USERADMIN);
        $EditingPrivileges->AddPrivilege(PRIV_SYSADMIN);

        # set the privileges
        $Field->Editable(FALSE);
        $Field->ViewingPrivileges($ViewingPrivileges);
        $Field->AuthoringPrivileges($AuthoringPrivileges);
        $Field->EditingPrivileges($EditingPrivileges);

        # make the field permanent
        $Field->IsTempItem(FALSE);
    }
}

/**
* Make an attempt at transforming the column name to a metadata field label.
* @param array $Column Array of column structure data.
* @return Returns the label attempt.
*/
function SiteUpgrade300_GetCustomColumnLabel(array $Column)
{
    # attempt to transform camel case to words
    $Label = preg_replace('/([A-Z]+)/', ' $1', $Column["Field"]);

    # replace underscores with spaces
    $Label = str_replace("_", " ", $Label);

    # capitalize words that aren't capitalized
    $Label = ucwords($Label);

    # remove any whitespace that was inadvertently added
    $Label = trim($Label);

    return $Label;
}

/**
* Make an attempt at guessing which metadata field type to use for a custom
* column in the APUsers table.
* @param array $Column Array of column structure data.
* @return Returns the metadata field type attempt.
* @throws Exception if an error occurs.
*/
function SiteUpgrade300_GetCustomColumnType(array $Column)
{
    $DB = new Database();
    # integer fields could be number or flag fields
    if (strpos($Column["Type"], "int") !== FALSE)
    {
        # see if it's only 1s and 0s and assume it's a flag field if so
        $Result = $DB->Query("
            SELECT UserId FROM APUsers
            WHERE `".$Column["Field"]."` != 1
            AND `".$Column["Field"]."` != 0
            LIMIT 1");

        # there was an error when executing the query
        if ($Result === FALSE)
        {
            throw new Exception("The query to check if the \"".$Column["Field"]
                ."\" column of the APUsers table is used as a flag field failed.");
        }

        # found a row that wasn't a 1 or 0, so make it a number field
        if ($DB->NumRowsSelected())
        {
            return MetadataSchema::MDFTYPE_NUMBER;
        }

        # only 1s and 0s so assume it's a flag field
        return MetadataSchema::MDFTYPE_FLAG;
    }

    # datetime fields should be mapped to timestamp fields
    if ($Column["Type"] == "datetime")
    {
        return MetadataSchema::MDFTYPE_TIMESTAMP;
    }

    # everything else should be treated as text
    return MetadataSchema::MDFTYPE_TEXT;
}

/**
* Get the structure of the APUsers table.
* @return Returns an array of the structure of the APUsers table.
* @throws Exception if an error occurs.
*/
function SiteUpgrade300_GetUserTableStructure()
{
    $DB = new Database();
    $Result = $DB->Query("DESCRIBE APUsers");

    # there was an error when executing the query
    if ($Result === FALSE)
    {
        throw new Exception("Could not get the APUsers table structure.");
    }

    # couldn't find any fields
    if ($DB->NumRowsSelected() < 1)
    {
        return array();
    }

    # fetch the rows
    $Rows = $DB->FetchRows();

    # an error occurred because there should be at least one row
    if (!count($Rows))
    {
        throw new Exception("Table structure data does not match query result.");
    }

    # return the data
    return $Rows;
}

/**
* Determine if a column is a default user column.
* @param array $Column Array of column structure data.
* @return Returns true if the column is a default one and FALSE otherwise.
*/
function SiteUpgrade300_IsDefaultUserColumn(array $Column)
{
    static $DefaultColumns;

    if (!isset($DefaultColumns))
    {
        $DefaultColumns = SiteUpgrade300_DefaultUserColumnNames();
    }

    return in_array($Column["Field"], $DefaultColumns);
}

/**
* Get an array of default user column names.
* @return Returns an array of default user column names.
*/
function SiteUpgrade300_DefaultUserColumnNames()
{
    static $DefaultColumns = array(
        "UserId", "UserName", "UserPassword", "CreationDate", "LastLoginDate",
        "LoggedIn", "RegistrationConfirmed", "EMail", "EMailNew", "WebSite",
        "RealName", "AddressLineOne", "AddressLineTwo", "City", "State",
        "Country", "ZipCode", "LastLocation", "LastActiveDate", "LastIPAddress",
        "ActiveUI", "BrowsingFieldId", "RecordsPerPage", "SearchSelections");

    return $DefaultColumns;
}

/**
* Migrate user data from the APUsers table to the Resources table.
* @throws Exception if an error occurs.
*/
function SiteUpgrade300_MigrateUserData()
{
    $DB = new Database();
    # see if there are preexisting users
    $Result = $DB->Query("
        SELECT * FROM Resources
        WHERE `SchemaId` = '".intval(MetadataSchema::SCHEMAID_USER)."'
        LIMIT 1");

    # there was an error when executing the query
    if ($Result === FALSE)
    {
        throw new Exception("The query to check for preexisting users has failed.");
    }

    # found preexisting users so skip user data migration
    if ($DB->NumRowsSelected() > 0)
    {
        return;
    }

    # set the resource ID column to auto increment temporarily
    $Result = $DB->Query("
        ALTER TABLE Resources
        MODIFY COLUMN ResourceId INT AUTO_INCREMENT");

    # there was an error when executing the query
    if ($Result === FALSE)
    {
        throw new Exception("Could not temporarily set the ResourceId column of"
            ." the Resources table to auto increment.");
    }

    $Schema = new MetadataSchema(MetadataSchema::SCHEMAID_USER);
    $Fields = $Schema->GetFields();
    $Map = array();

    # map field names (APUsers columns) to resource table column names
    foreach ($Schema->GetFields() as $Field)
    {
        # migrate any fields that exist in the APUsers table
        if ($DB->FieldExists("APUsers", $Field->Name()))
        {
            $Map[$Field->Name()] = $Field->DBFieldName();
        }

        # and make sure that db columns exist for user fields
        if ($Field->Type() == MetadataSchema::MDFTYPE_USER)
        {
            $DB->Query("ALTER TABLE Resources ADD COLUMN "
                    .$Field->DBFieldName()." INT");
        }
    }

    # construct the strings for the query
    $APUsers = implode(",", array_keys($Map));
    $Resources = implode(",", array_values($Map));

    # migrate all of the data over
    $Result = $DB->Query("
        INSERT INTO Resources
        (ResourceId, SchemaId, ".$Resources.")
        SELECT NULL, ".intval(MetadataSchema::SCHEMAID_USER).", ".$APUsers."
        FROM APUsers");

    # there was an error when executing the query
    if ($Result === FALSE)
    {
        throw new Exception("The user data migration query failed.");
    }

    # remove the auto increment
    $Result = $DB->Query("
        ALTER TABLE Resources
        MODIFY COLUMN ResourceId INT NOT NULL");

    # there was an error when executing the query
    if ($Result === FALSE)
    {
        throw new Exception("Could not remove the auto increment setting on the"
            ." ResourceId column of the Resources table.");
    }
}


/**
* Make sure each schema has a field order defined.
*/
function SiteUpgrade300_UpgradeFieldOrder()
{
    $DB = new Database();

    foreach (MetadataSchema::GetAllSchemas() as $Schema)
    {
        foreach (array(MetadataSchema::ORDER_DISPLAY_NAME,
                       MetadataSchema::ORDER_EDIT_NAME) as $Name)
        {
            # try to fetch an existing order
            $Order = MetadataFieldOrder::GetOrderForSchema($Schema, $Name);

            # if the order doesn't exist
            if (is_null($Order))
            {
                # see if we had a folder for the order stored for the sysconfig
                $OldId = ($Name == MetadataSchema::ORDER_EDIT_NAME) ?
                    $GLOBALS["SysConfig"]->FieldEditFolder() :
                    $GLOBALS["SysConfig"]->FieldDisplayFolder() ;

                # if the older version of MetadataFieldOrder was in use
                if ($OldId && $Schema->Id() == MetadataSchema::SCHEMAID_DEFAULT)
                {
                    # add an entry for the existing folder
                    $DB->Query(
                        "INSERT INTO MetadataFieldOrders "
                        ."SET SchemaId = '".addslashes(MetadataSchema::SCHEMAID_DEFAULT)."', "
                        ."OrderId = '".addslashes($OldId)."',"
                        ."OrderName = '".addslashes($Name)."'");
                }
                else
                {
                    $UpgradeOrder = array();

                    # temporarily suppress errors
                    $Setting = Database::DisplayQueryErrors();
                    Database::DisplayQueryErrors(FALSE);

                    # see if the old columns exist
                    $Handle = $DB->Query(
                        "SELECT EditingOrderPosition "
                        ."FROM MetadataFields LIMIT 1");

                    # restore the earlier error setting
                    Database::DisplayQueryErrors($Setting);

                    if ($Handle !== FALSE)
                    {
                        # determine which column to use for ordering
                        $Column = ($Name == MetadataSchema::ORDER_DISPLAY_NAME)
                            ? "DisplayOrderPosition" : "EditingOrderPosition";

                        # query for the fields in their proper order
                        $DB->Query(
                            "SELECT FieldId "
                            ."FROM MetadataFields "
                            ."WHERE FieldId > 0 "
                            ."ORDER BY ".$Column." ASC");

                        # use the order from the DB
                        $UpgradeOrder = $DB->FetchColumn("FieldId");
                    }

                    # otherwise create a new order
                    MetadataFieldOrder::Create(
                        $Schema, $Name, $UpgradeOrder);
                }
            }
        }
    }
}
