
-- add columns for high memory usage logging in app framework
ALTER TABLE ApplicationFrameworkSettings
        ADD COLUMN LogHighMemoryUsage INT DEFAULT 0;
ALTER TABLE ApplicationFrameworkSettings
        ADD COLUMN HighMemoryUsageThreshold INT DEFAULT 90;

-- Add columns for per-tree field display settings
ALTER TABLE MetadataFields
        ADD COLUMN DisplayAsListForAdvancedSearch INT DEFAULT 0;

-- add columns for new system configuration settings
ALTER TABLE SystemConfiguration
        ADD COLUMN PreferHttpHost INT DEFAULT 0;
ALTER TABLE SystemConfiguration
        ADD COLUMN RootUrlOverride TEXT;

-- Add columns for tracking MRU Classifications and ControlledNames
ALTER TABLE ControlledNames
        ADD COLUMN LastAssigned TIMESTAMP NULL;
ALTER TABLE ControlledNames
        ADD INDEX (LastAssigned);
ALTER TABLE Classifications
        ADD COLUMN LastAssigned TIMESTAMP NULL;
ALTER TABLE Classifications
        ADD INDEX (LastAssigned);

-- Add uniqueness constraints to intersection tables
CREATE TABLE ResourceNameInts_old AS SELECT DISTINCT * FROM ResourceNameInts;
DELETE FROM ResourceNameInts;
ALTER TABLE ResourceNameInts
        ADD CONSTRAINT UNIQUE (ResourceId, ControlledNameId);
INSERT INTO ResourceNameInts
        SELECT * FROM ResourceNameInts_old;
DROP TABLE ResourceNameInts_old;

CREATE TABLE ResourceClassInts_old AS SELECT DISTINCT * FROM ResourceClassInts;
DELETE FROM ResourceClassInts;
ALTER TABLE ResourceClassInts
        ADD CONSTRAINT UNIQUE (ResourceId, ClassificationId);
INSERT INTO ResourceClassInts
        SELECT * FROM ResourceClassInts_old;
DROP TABLE ResourceClassInts_old;

CREATE TABLE ResourceImageInts_old AS SELECT DISTINCT * FROM ResourceImageInts;
DELETE FROM ResourceImageInts;
ALTER TABLE ResourceImageInts
        ADD CONSTRAINT UNIQUE (ResourceId,FieldId,ImageId);
INSERT INTO ResourceImageInts
        SELECT * FROM ResourceImageInts_old;
DROP TABLE ResourceImageInts_old;

CREATE TABLE ReferenceInts_old AS SELECT DISTINCT * FROM ReferenceInts;
DELETE FROM ReferenceInts;
ALTER TABLE ReferenceInts
        ADD CONSTRAINT UNIQUE (FieldId, SrcResourceId, DstResourceId);
INSERT INTO ReferenceInts
        SELECT * FROM ReferenceInts_old;
DROP TABLE ReferenceInts_old;

CREATE TABLE FieldQualifierInts_old AS SELECT DISTINCT * FROM FieldQualifierInts;
DELETE FROM FieldQualifierInts;
ALTER TABLE FieldQualifierInts
        ADD CONSTRAINT UNIQUE (MetadataFieldId,QualifierId);
INSERT INTO FieldQualifierInts
        SELECT * FROM FieldQualifierInts_old;
DROP TABLE FieldQualifierInts_old;

-- remove unnecessary cache tables
DROP TABLE ControlledNameFacetCache;
DROP TABLE ClassificationFacetCache;

-- remove accessibility features
DROP TABLE UserUIPreferences;
ALTER TABLE SystemConfiguration
    DROP COLUMN AccessibilityWizardEnabled;
