
-- ----- SYSTEM --------------------------------------------------------------

-- system configuration
CREATE TABLE IF NOT EXISTS SystemConfiguration (
    TopLinkString           TEXT DEFAULT NULL,
    TopLinkBeginLetter      TEXT DEFAULT NULL,
    TopLinkEndLetter        TEXT DEFAULT NULL,
    AdminEmail              TEXT DEFAULT NULL,
    PasswordMailSubject     TEXT DEFAULT NULL,
    PasswordMailBody        TEXT DEFAULT NULL,
    PasswordResetMailSubject TEXT DEFAULT NULL,
    PasswordResetMailBody   TEXT DEFAULT NULL,
    MailChangeMailSubject   TEXT DEFAULT NULL,
    MailChangeMailBody      TEXT DEFAULT NULL,
    PortalName              TEXT DEFAULT NULL,
    DefaultActiveUI         TEXT DEFAULT NULL,
    DefaultCharacterSet     TEXT DEFAULT NULL,
    DefaultRecordsPerPage   INT DEFAULT 5,
    NumResourcesForSearchFacets INT DEFAULT 5000,
    MaxFacetsPerField       INT DEFAULT 75,
    DefaultSortField        INT DEFAULT NULL,
    CollapseMetadataFieldGroups INT DEFAULT 0,
    RequireEmailWithFeedback INT DEFAULT 0,
    SearchEngineUpdatePriority  INT DEFAULT 3,
    RecommenderEngineUpdatePriority  INT DEFAULT 3,
    NumClassesPerBrowsePage INT DEFAULT 80,
    NumColumnsPerBrowsePage INT DEFAULT 2,
    BrowsingFieldId         INT DEFAULT 27,
    NumAnnounceOnHomePage   INT DEFAULT 2,
    NumResourcesOnHomePage  INT DEFAULT 5,
    LegalNotice             TEXT DEFAULT NULL,
    SearchTermsRequired     INT DEFAULT 1,
    ShowNumResourcesEnabled INT DEFAULT 0,
    AnnouncementsEnabled    INT DEFAULT 1,
    ForumsEnabled           INT DEFAULT 1,
    ForumsAllowHTML         INT DEFAULT 1,
    ForumsUseWYSIWYG        INT DEFAULT 1,
    AllowMultipleUIsEnabled INT DEFAULT 0,
    ResourceLaunchesNewWindowEnabled    INT DEFAULT 1,
    UserAgentsEnabled       INT DEFAULT 0,
    ResourceRatingsEnabled  INT DEFAULT 1,
    ResourceCommentsEnabled INT DEFAULT 1,
    IncrementalKeywordSearchEnabled INT DEFAULT 0,
    ReleaseFlagApproved     INT DEFAULT 0,
    SearchDBEnabled         INT DEFAULT 1,
    RecommenderDBEnabled    INT DEFAULT 1,
    OAISQEnabled            INT DEFAULT 1,
    OaiIdDomain             TEXT DEFAULT NULL,
    OaiIdPrefix             TEXT DEFAULT NULL,
    OaiEarliestDate         TEXT DEFAULT NULL,
    OaiDateGranularity      ENUM ("DATE", "DATETIME"),
    SiteKeywords            TEXT DEFAULT NULL,
    AboutText               TEXT DEFAULT NULL,
    CurrentTheme            TEXT DEFAULT NULL,
    ContactName             TEXT DEFAULT NULL,
    ContactEmail            TEXT DEFAULT NULL,
    Organization            TEXT DEFAULT NULL,
    OrganizationType        TEXT DEFAULT NULL,
    SiteType                TEXT DEFAULT NULL,
    OkayToListSite          INT DEFAULT 0,
    AddAWStatsScript        INT DEFAULT 0,
    DisplayLimitsByDefault  INT DEFAULT 1,
    LastRegisteredOn        DATETIME DEFAULT NULL,
    TitleField              INT DEFAULT -2147483647,
    DescriptionField        INT DEFAULT -2147483647,
    UrlField                INT DEFAULT -2147483647,
    FileField               INT DEFAULT -2147483647,
    ScreenshotField         INT DEFAULT -2147483647,
    PreferredLinkValue      ENUM ("URL", "FILE") DEFAULT "FILE",
    TitlesLinkTo            ENUM ("URL", "RECORD") DEFAULT "URL",
    SecureLogin             INT DEFAULT 1,
    DefaultUserPrivs        TEXT DEFAULT NULL,
    EmailDeliverySettings   TEXT DEFAULT NULL,
    EmailLineEnding         ENUM ("CRLF", "CR", "LF") NOT NULL DEFAULT "CRLF",
    SavedSearchMailTemplate INT DEFAULT -1,
    PreferHttpHost          INT DEFAULT 0,
    RootUrlOverride         TEXT DEFAULT NULL,
    ShowGroupNamesEnabled   INT DEFAULT 0
);

-- user interface style configuration
CREATE TABLE IF NOT EXISTS UIStyleConfiguration (
    StyleName                   TEXT DEFAULT NULL,
    LogoFileName                TEXT DEFAULT NULL,
    LogoAltText                 TEXT DEFAULT NULL,
    LogoWidth                   INT DEFAULT 64,
    LogoHeight                  INT DEFAULT 64,
    PageCenterBackground        TEXT DEFAULT NULL,
    PageSideBackground          TEXT DEFAULT NULL,
    SPTTableHeaderBackground    TEXT DEFAULT NULL,
    MDTTableHeaderBackground    TEXT DEFAULT NULL,
    TableBodyBackground         TEXT DEFAULT NULL,
    ForumDarkHeaderBackground   TEXT DEFAULT NULL,
    ForumLightHeaderBackground  TEXT DEFAULT NULL,
    ForumBodyBackground         TEXT DEFAULT NULL,
    NavBoxBackground            TEXT DEFAULT NULL,
    HomeBoxBackground           TEXT DEFAULT NULL
);

-- saved searches
CREATE TABLE IF NOT EXISTS SavedSearches (
    SearchId            INT NOT NULL AUTO_INCREMENT,
    SearchName          TEXT DEFAULT NULL,
    SearchData          TEXT DEFAULT NULL,
    UserId              INT NOT NULL,
    DateLastRun         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Frequency           INT DEFAULT 0,
    LastMatchingIds     MEDIUMTEXT DEFAULT NULL,
    INDEX               Index_U (UserId),
    INDEX               Index_S (SearchId)
);

-- RSS Import Feeds
CREATE TABLE IF NOT EXISTS RSSFeeds (
    RSSId                   INT NOT NULL AUTO_INCREMENT,
    Title                   TEXT DEFAULT NULL,
    URL                     TEXT DEFAULT NULL,
    DisplayLocations        INT NOT NULL,
    NumberItemsToDisplay    INT DEFAULT 0,
    RefreshRate             INT NOT NULL,
    Enabled                 INT DEFAULT 0,
    INDEX                   Index_R (RSSId)
);

-- add additional fields to user records
ALTER TABLE APUsers ADD COLUMN ActiveUI TEXT DEFAULT NULL;
ALTER TABLE APUsers ADD COLUMN BrowsingFieldId INT DEFAULT NULL;
ALTER TABLE APUsers ADD COLUMN RecordsPerPage INT DEFAULT 5;
ALTER TABLE APUsers ADD COLUMN SearchSelections TEXT DEFAULT NULL;

-- OAI-SQ search sites
CREATE TABLE IF NOT EXISTS GlobalSearchSites (
    SiteId                      INT NOT NULL AUTO_INCREMENT,
    SiteName                    TEXT DEFAULT NULL,
    OaiUrl                      TEXT DEFAULT NULL,
    SiteUrl                     TEXT DEFAULT NULL,
    LastSearchDate              DATETIME DEFAULT NULL,
    ConsecutiveFailures         INT DEFAULT 0,
    SearchAttempts              INT DEFAULT 0,
    SuccessfulSearches          INT DEFAULT 0,
    INDEX                       Index_S (SiteId)
);

-- user-defined privileges
CREATE TABLE IF NOT EXISTS CustomPrivileges (
    Id          INT NOT NULL,
    Name        TEXT DEFAULT NULL,
    INDEX       Index_I (Id)
);

-- secure login data
CREATE TABLE LoginKeys (
  KeyPair TEXT DEFAULT NULL,
  CreationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE UsedLoginTokens (
  Token TEXT DEFAULT NULL,
  KeyCTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UserName TEXT
);

-- ----- RESOURCES AND RELATED DATA ------------------------------------------

-- metadata schemas
CREATE TABLE IF NOT EXISTS MetadataSchemas (
    SchemaId                INT NOT NULL,
    Name                    TEXT DEFAULT NULL,
    AbbreviatedName         TEXT DEFAULT NULL,
    ResourceName            TEXT DEFAULT NULL,
    AuthoringPrivileges     BLOB DEFAULT NULL,
    EditingPrivileges       BLOB DEFAULT NULL,
    ViewingPrivileges       BLOB DEFAULT NULL,
    ViewPage                TEXT DEFAULT NULL,
    INDEX                   Index_S (SchemaId)
);

-- metadata fields
CREATE TABLE IF NOT EXISTS MetadataFields (
    FieldId                         INT NOT NULL,
    FieldName                       TEXT NOT NULL,
    FieldType                       ENUM("Text", "Number", "Date", "TimeStamp",
                                            "Paragraph", "Flag", "Tree",
                                            "ControlledName", "Option", "User",
                                            "Still Image", "File", "Url",
                                            "Point", "Reference"),
    SchemaId                        INT NOT NULL DEFAULT 0,
    Label                           TEXT DEFAULT NULL,
    Description                     TEXT DEFAULT NULL,
    Instructions                    TEXT DEFAULT NULL,
    Owner                           TEXT DEFAULT NULL,
    EnableOnOwnerReturn             INT DEFAULT 0,
    RequiredBySPT                   INT DEFAULT 0,
    Enabled                         INT DEFAULT 1,
    Optional                        INT DEFAULT 1,
    CopyOnResourceDuplication       INT DEFAULT 1,
    Editable                        INT DEFAULT 1,
    AllowMultiple                   INT DEFAULT 0,
    IncludeInKeywordSearch          INT DEFAULT 0,
    IncludeInAdvancedSearch         INT DEFAULT 0,
    IncludeInFacetedSearch          INT DEFAULT 0,
    SearchGroupLogic                INT DEFAULT 2,
    IncludeInSortOptions            INT DEFAULT 1,
    IncludeInRecommender            INT DEFAULT 0,
    DisplayAsListForAdvancedSearch  INT DEFAULT 0,
    MaxDepthForAdvancedSearch       INT DEFAULT 0,
    TextFieldSize                   INT DEFAULT NULL,
    MaxLength                       INT DEFAULT NULL,
    ParagraphRows                   INT DEFAULT NULL,
    ParagraphCols                   INT DEFAULT NULL,
    DefaultValue                    TEXT DEFAULT NULL,
    MinValue                        INT DEFAULT NULL,
    `MaxValue`                      INT DEFAULT NULL,
    FlagOnLabel                     TEXT DEFAULT NULL,
    FlagOffLabel                    TEXT DEFAULT NULL,
    DateFormat                      TEXT DEFAULT NULL,
    DateFormatIsPerRecord           INT DEFAULT 0,
    SearchWeight                    INT DEFAULT 1,
    RecommenderWeight               INT DEFAULT 1,
    MaxHeight                       INT DEFAULT NULL,
    MaxWidth                        INT DEFAULT NULL,
    MaxPreviewHeight                INT DEFAULT NULL,
    MaxPreviewWidth                 INT DEFAULT NULL,
    MaxThumbnailHeight              INT DEFAULT NULL,
    MaxThumbnailWidth               INT DEFAULT NULL,
    DefaultAltText                  TEXT DEFAULT NULL,
    ImagePreviewPrivilege           INT DEFAULT 0,
    UsesQualifiers                  INT DEFAULT 0,
    HasItemLevelQualifiers          INT DEFAULT 0,
    DefaultQualifier                INT DEFAULT NULL,
    DateLastModified                TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    LastModifiedById                INT DEFAULT NULL,
    UseForOaiSets                   INT DEFAULT 0,
    NumAjaxResults                  INT DEFAULT 50,
    OptionListThreshold             INT DEFAULT 25,
    AjaxThreshold                   INT DEFAULT 50,
    ViewingPrivilege                INT DEFAULT 0,
    AuthoringPrivilege              INT DEFAULT 12,
    EditingPrivilege                INT DEFAULT 3,
    ViewingUserIsValue              INT DEFAULT 0,
    AuthoringUserIsValue            INT DEFAULT 0,
    EditingUserIsValue              INT DEFAULT 0,
    ViewingUserValue                INT DEFAULT NULL,
    AuthoringUserValue              INT DEFAULT NULL,
    EditingUserValue                INT DEFAULT NULL,
    PointPrecision                  INT DEFAULT 8,
    PointDecimalDigits              INT DEFAULT 5,
    UpdateMethod                    ENUM("NoAutoUpdate", "OnRecordCreate",
                                            "Button", "OnRecordEdit",
                                            "OnRecordChange")
                                            DEFAULT "NoAutoUpdate",
    AllowHTML                       INT DEFAULT 0,
    UseWysiwygEditor                INT DEFAULT 0,
    TreeBrowsingPrivilege           INT DEFAULT 0,
    ShowQualifiers                  INT DEFAULT 0,
    ReferenceableSchemaIds          TEXT DEFAULT NULL,
    UserPrivilegeRestrictions       TEXT DEFAULT NULL,
    AuthoringPrivileges             BLOB DEFAULT NULL,
    EditingPrivileges               BLOB DEFAULT NULL,
    ViewingPrivileges               BLOB DEFAULT NULL,
    PreviewingPrivileges            BLOB DEFAULT NULL,
    INDEX                           Index_I (FieldId),
    INDEX                           Index_T (FieldType)
);

-- resource metadata field orders
CREATE TABLE IF NOT EXISTS MetadataFieldOrders (
    SchemaId                  INT NOT NULL,
    OrderId                   INT NOT NULL,
    OrderName                 TEXT
);

-- field qualifiers
CREATE TABLE IF NOT EXISTS Qualifiers (
    QualifierId            INT NOT NULL,
    QualifierName          TEXT DEFAULT NULL,
    QualifierNamespace     TEXT DEFAULT NULL,
    QualifierUrl           TEXT DEFAULT NULL,
    INDEX                  Index_Q (QualifierId)
);

-- intersection between MetadataFields and Qualifiers
CREATE TABLE IF NOT EXISTS FieldQualifierInts (
    MetadataFieldId        INT NOT NULL,
    QualifierId            INT NOT NULL,
    INDEX                  Index_Q (QualifierId),
    UNIQUE                 UIndex_FQ (MetadataFieldId, QualifierId)
);

-- resource records
CREATE TABLE IF NOT EXISTS Resources (
    ResourceId              INT NOT NULL,
    SchemaId                INT NOT NULL DEFAULT 0,
    Title                   TEXT DEFAULT NULL,
    AlternateTitle          TEXT DEFAULT NULL,
    Description             TEXT DEFAULT NULL,
    Url                     TEXT DEFAULT NULL,
    ReleaseFlag             INT DEFAULT NULL,
    Source                  TEXT DEFAULT NULL,
    Relation                TEXT DEFAULT NULL,
    Coverage                TEXT DEFAULT NULL,
    Rights                  TEXT DEFAULT NULL,
    EmailAddress            TEXT DEFAULT NULL,
    DateIssuedBegin         DATE DEFAULT NULL,
    DateIssuedEnd           DATE DEFAULT NULL,
    DateIssuedPrecision     INT DEFAULT 0,
    DateOfRecordCreation    DATETIME DEFAULT NULL,
    DateOfRecordRelease     DATETIME NULL,
    DateRecordChecked       DATETIME NULL,
    DateLastModified        DATETIME DEFAULT NULL,
    VerificationAttempts    INT DEFAULT 0,
    CumulativeRating        INT DEFAULT 0,
    Screenshot              INT DEFAULT NULL,
    INDEX                   Index_R (ResourceId),
    INDEX                   Index_S (SchemaId)
);

-- references/links between source resources to destination resources
CREATE TABLE IF NOT EXISTS ReferenceInts (
    FieldId       INT DEFAULT NULL,
    SrcResourceId INT DEFAULT NULL,
    DstResourceId INT DEFAULT NULL,
    UNIQUE        UIndex_FSD (FieldId, SrcResourceId, DstResourceId)
);

-- intersection table between resources and users
CREATE TABLE IF NOT EXISTS ResourceUserInts (
   ResourceId INT NOT NULL,
   FieldId INT NOT NULL,
   UserId INT NOT NULL,
   INDEX Index_U (UserId),
   UNIQUE UIndex_RU (ResourceId, FieldId, UserId)
);

-- text values associated with resources
CREATE TABLE IF NOT EXISTS TextValues (
    ResourceId              INT NOT NULL,
    FieldId                 INT NOT NULL,
    TextValue               TEXT DEFAULT NULL,
    INDEX                   Index_RF (ResourceId, FieldId)
);

-- numeric values associated with resources
CREATE TABLE IF NOT EXISTS NumberValues (
    ResourceId              INT NOT NULL,
    FieldId                 INT NOT NULL,
    NumberValue             INT DEFAULT NULL,
    INDEX                   Index_RF (ResourceId, FieldId)
);

-- date/time values associated with resources
CREATE TABLE IF NOT EXISTS DateValues (
    ResourceId              INT NOT NULL,
    FieldId                 INT NOT NULL,
    DateBegin               DATETIME DEFAULT NULL,
    DateEnd                 DATETIME DEFAULT NULL,
    DatePrecision           INT DEFAULT NULL,
    INDEX                   Index_RF (ResourceId, FieldId)
);

-- user ratings of resources
CREATE TABLE IF NOT EXISTS ResourceRatings (
    ResourceId              INT NOT NULL,
    UserId                  INT NOT NULL,
    DateRated               DATETIME DEFAULT NULL,
    Rating                  INT DEFAULT NULL,
    Comments                TEXT DEFAULT NULL,
    CommentApproved         INT DEFAULT 0,
    INDEX                   Index_R (ResourceId),
    INDEX                   Index_U (UserId)
);

-- controlled names (publishers, authors, etc)
CREATE TABLE IF NOT EXISTS ControlledNames (
    ControlledNameId        INT NOT NULL AUTO_INCREMENT,
    ControlledName          TEXT DEFAULT NULL,
    FieldId                 INT NOT NULL,
    QualifierId             INT DEFAULT NULL,
    LastAssigned            TIMESTAMP NULL,
    INDEX                   Index_I (ControlledNameId),
    INDEX                   Index_N (ControlledName(16)),
    INDEX                   Index_F (FieldId),
    INDEX                   Index_A (LastAssigned)
);

-- possible variants on controlled names
CREATE TABLE IF NOT EXISTS VariantNames (
    ControlledNameId        INT NOT NULL,
    VariantName             TEXT DEFAULT NULL,
    INDEX                   Index_I (ControlledNameId),
    INDEX                   Index_V (VariantName(16))
);
-- add at least one variant name to avoid MySQL-related query problem
INSERT INTO VariantNames (ControlledNameId, VariantName) VALUES (-1, "DUMMY");

-- classifications (subjects, categories, etc)
CREATE TABLE IF NOT EXISTS Classifications (
    ClassificationId        INT NOT NULL AUTO_INCREMENT,
    FieldId                 INT NOT NULL,
    ClassificationName      TEXT DEFAULT NULL,
    Depth                   INT DEFAULT 0,
    ParentId                INT NOT NULL,
    SegmentName             TEXT DEFAULT NULL,
    ResourceCount           INT DEFAULT 0,
    FullResourceCount       INT DEFAULT 0,
    LinkString              TEXT DEFAULT NULL,
    QualifierId             INT DEFAULT NULL,
    LastAssigned            TIMESTAMP NULL,
    INDEX                   Index_I (ClassificationId),
    INDEX                   Index_P (ParentId),
    INDEX                   Index_F (FieldId),
    INDEX                   Index_FP (FieldId, ParentId),
    INDEX                   Index_A (LastAssigned),
    FULLTEXT                Index_SC (SegmentName, ClassificationName)
);

-- counts of resources per classification
CREATE TABLE IF NOT EXISTS ClassResourceCounts (
    ClassificationId        INT NOT NULL,
    ConditionalId           INT NOT NULL,
    INDEX                   Index_I (ClassificationId)
);

-- hash values for class resource count conditions
CREATE TABLE IF NOT EXISTS ClassResourceConditionals (
    ConditionalId           INT NOT NULL,
    ConditionalHash         TEXT DEFAULT NULL,
    INDEX                   Index_I (ConditionalId),
    INDEX                   Index_H (ConditionalHash(32))
);

-- intersection table between Resources and ControlledNames
CREATE TABLE IF NOT EXISTS ResourceNameInts (
    ResourceId              INT NOT NULL,
    ControlledNameId        INT NOT NULL,
    INDEX                   Index_C (ControlledNameId),
    UNIQUE                  UIndex_RC (ResourceId, ControlledNameId)
);

-- intersection table between Resources and Classifications
CREATE TABLE IF NOT EXISTS ResourceClassInts (
    ResourceId              INT NOT NULL,
    ClassificationId        INT NOT NULL,
    INDEX                   Index_C (ClassificationId),
    UNIQUE                  UIUndex_RC (ResourceId, ClassificationId)
);

-- intersection table between Resources and Images
CREATE TABLE IF NOT EXISTS ResourceImageInts (
    ResourceId              INT NOT NULL,
    FieldId                 INT NOT NULL,
    ImageId                 INT NOT NULL,
    INDEX                   Index_I (ImageId),
    UNIQUE                  UIndex_RFI (ResourceId, FieldId, ImageId)
);

-- image information
CREATE TABLE IF NOT EXISTS Images (
    ImageId                 INT NOT NULL AUTO_INCREMENT,
    Format                  INT DEFAULT NULL,
    AltText                 TEXT DEFAULT NULL,
    LinkTarget              TEXT DEFAULT NULL,
    Width                   INT DEFAULT NULL,
    Height                  INT DEFAULT NULL,
    PreviewWidth            INT DEFAULT NULL,
    PreviewHeight           INT DEFAULT NULL,
    ThumbnailWidth          INT DEFAULT NULL,
    ThumbnailHeight         INT DEFAULT NULL,
    INDEX                   Index_I (ImageId)
);

-- attached files
CREATE TABLE IF NOT EXISTS Files (
    FileId                  INT NOT NULL AUTO_INCREMENT,
    ResourceId              INT NOT NULL,
    FieldId                 INT NOT NULL,
    FileName                TEXT DEFAULT NULL,
    FileComment             TEXT DEFAULT NULL,
    FileLength              INT DEFAULT 0,
    FileType                TEXT DEFAULT NULL,
    SecretString            TEXT DEFAULT NULL,
    INDEX                   Index_R (ResourceId),
    INDEX                   Index_F (FileId),
    INDEX                   Index_RF (ResourceId, FieldId)
);

-- resource modification timestamps
CREATE TABLE IF NOT EXISTS ResourceFieldTimestamps (
    ResourceId      INT NOT NULL,
    FieldId         INT NOT NULL,
    Timestamp       DATETIME DEFAULT NULL,
    ModifiedBy      INT NOT NULL,
    INDEX           Index_RF (ResourceId, FieldId),
    INDEX           Index_T (Timestamp)
);

-- cache of which resources are viewable
CREATE TABLE IF NOT EXISTS UserPermsCache (
        ResourceId INT DEFAULT NULL,
        UserClass TEXT DEFAULT NULL,
        CanView BOOL DEFAULT FALSE,
        PRIMARY KEY (ResourceId, UserClass(32) ),
        INDEX Index_R (ResourceId),
        INDEX Index_U (UserClass(32))
);

-- ----- FORUMS --------------------------------------------------------------

-- forums (top level of forum content hierarchy)
CREATE TABLE IF NOT EXISTS Forums (
    ForumId                 INT NOT NULL AUTO_INCREMENT,
    ForumName               TEXT DEFAULT NULL,
    ForumDescription        TEXT DEFAULT NULL,
    TopicCount              INT DEFAULT 0,
    MessageCount            INT DEFAULT 0,
    ModeratorId             INT DEFAULT NULL,
    INDEX                   Index_F (ForumId)
);

-- topics within forums (second level of forum content hierarchy)
-- also:  resource annotation topics
CREATE TABLE IF NOT EXISTS Topics (
    TopicId                 INT NOT NULL AUTO_INCREMENT,
    ForumId                 INT NOT NULL,
    CreatorId               INT DEFAULT NULL,
    DateCreated             DATETIME DEFAULT NULL,
    TopicName               TEXT DEFAULT NULL,
    ViewCount               INT DEFAULT 0,
    MessageCount            INT DEFAULT 0,
    INDEX                   Index_TF (TopicId, ForumId)
);

-- messages within forum topics (lowest level of forum content hierarchy)
CREATE TABLE IF NOT EXISTS Messages (
    MessageId               INT NOT NULL AUTO_INCREMENT,
    ParentId                INT NOT NULL,
    ParentType              INT NOT NULL,
    DatePosted              DATETIME DEFAULT NULL,
    DateEdited              DATETIME DEFAULT NULL,
    PosterId                INT DEFAULT NULL,
    EditorId                INT DEFAULT NULL,
    Subject                 TEXT DEFAULT NULL,
    Body                    TEXT DEFAULT NULL,
    INDEX                   Index_MP (MessageId, ParentId)
);

-- resource searches performed by users or set up for use with user agents
CREATE TABLE IF NOT EXISTS Searches (
    SearchId                INT NOT NULL AUTO_INCREMENT,
    UserId                  INT NOT NULL,
    DateLastRun             DATETIME DEFAULT NULL,
    Keywords                TEXT DEFAULT NULL,
    RunInterval             INT DEFAULT NULL,
    INDEX                   Index_S (SearchId)
);


-- ----- RECOMMENDER SYSTEM --------------------------------------------------

-- correlation values for recommender system
CREATE TABLE IF NOT EXISTS RecContentCorrelations (
    ItemIdA                 INT NOT NULL,
    ItemIdB                 INT NOT NULL,
    Correlation             SMALLINT NOT NULL,
    INDEX                   Index_AB (ItemIdA, ItemIdB)
);


-- ----- OAI EXPORT ----------------------------------------------------------

-- OAI formats table
CREATE TABLE IF NOT EXISTS OAIFormats (
    FormatId        INT NOT NULL AUTO_INCREMENT,
    FormatName      TEXT DEFAULT NULL,
    TagName         TEXT DEFAULT NULL,
    SchemaLocation  TEXT DEFAULT NULL,
    INDEX           Index_F (FormatId)
);

-- OAI format namespaces table
CREATE TABLE IF NOT EXISTS OAIFormatNamespaces (
    FormatId        INT NOT NULL,
    NamespaceName   TEXT DEFAULT NULL,
    NamespaceURI    TEXT DEFAULT NULL,
    INDEX           Index_F (FormatId)
);

-- OAI field mappings table
CREATE TABLE IF NOT EXISTS OAIFieldMappings (
    FormatName          TEXT NOT NULL,
    SPTFieldId          INT NOT NULL,
    OAIFieldName        TEXT DEFAULT NULL,
    INDEX               Index_F (SPTFieldId)
);

-- OAI qualifier mappings table
CREATE TABLE IF NOT EXISTS OAIQualifierMappings (
    FormatName          TEXT NOT NULL,
    SPTQualifierId      INT NOT NULL,
    OAIQualifierName    TEXT DEFAULT NULL,
    INDEX               Index_Q (SPTQualifierId)
);

-- Count of resources assigned to each defined ControlledName
CREATE TABLE VisibleResourceCounts (
    SchemaId INT NOT NULL,
    UserClass TEXT NOT NULL,
    ValueId INT NOT NULL,
    ResourceCount INT NOT NULL,
    INDEX Index_SU(SchemaId,UserClass(16)) );


CREATE TABLE IF NOT EXISTS CachedValues (
    Name TEXT DEFAULT NULL,
    Value TEXT DEFAULT NULL,
    Updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ----- FOLDERS ------------------------------------------------------------

-- folders for storing groups of items
CREATE TABLE IF NOT EXISTS Folders (
    FolderId                INT NOT NULL AUTO_INCREMENT,
    PreviousFolderId        INT DEFAULT NULL,
    NextFolderId            INT DEFAULT NULL,
    OwnerId                 INT DEFAULT NULL,
    FolderName              TEXT DEFAULT NULL,
    NormalizedName          TEXT DEFAULT NULL,
    FolderNote              TEXT DEFAULT NULL,
    IsShared                INT DEFAULT 0,
    ContentType             INT DEFAULT -1,
    INDEX                   Index_O (OwnerId),
    INDEX                   Index_F (FolderId)
);

-- intersection table indicating which items are in which folders
CREATE TABLE IF NOT EXISTS FolderItemInts (
    FolderId                INT NOT NULL,
    ItemId                  INT NOT NULL,
    ItemTypeId              INT DEFAULT -1,
    PreviousItemId          INT DEFAULT -1,
    PreviousItemTypeId      INT DEFAULT -1,
    NextItemId              INT DEFAULT -1,
    NextItemTypeId          INT DEFAULT -1,
    ItemNote                TEXT DEFAULT NULL,
    INDEX                   Index_F (FolderId),
    INDEX                   Index_I (ItemId)
);

-- mapping of item type names to numerical item type IDs
CREATE TABLE IF NOT EXISTS FolderContentTypes (
    TypeId                  INT NOT NULL AUTO_INCREMENT,
    TypeName                TEXT DEFAULT NULL,
    NormalizedTypeName      TEXT DEFAULT NULL,
    INDEX                   Index_T (TypeId)
);
