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

-- system configuration
CREATE TABLE IF NOT EXISTS SystemConfiguration (
    TopLinkString           TEXT,
    TopLinkBeginLetter      TEXT,
    TopLinkEndLetter        TEXT,
    AdminEmail              TEXT,
    PasswordMailSubject     TEXT,
    PasswordMailBody        TEXT,
    PasswordResetMailSubject TEXT,
    PasswordResetMailBody   TEXT,
    MailChangeMailSubject   TEXT,
    MailChangeMailBody      TEXT,
    PortalName              TEXT,
    DefaultActiveUI         TEXT,
    DefaultCharacterSet     TEXT,
    DefaultRecordsPerPage   INT DEFAULT 5,
    DefaultSortField        INT,
    NumClassesPerBrowsePage INT DEFAULT 80,
    NumColumnsPerBrowsePage INT DEFAULT 2,
    BrowsingFieldId         INT DEFAULT 27,
    NumAnnounceOnHomePage   INT DEFAULT 2,
    NumResourcesOnHomePage  INT DEFAULT 5,
    LegalNotice             TEXT,
    SearchTermsRequired     INT,
    AnnouncementsEnabled    INT DEFAULT 1,
    ForumsEnabled           INT DEFAULT 1,
    AllowMultipleUIsEnabled INT DEFAULT 0,
    ResourceLaunchesNewWindowEnabled    INT DEFAULT 1,
    UserAgentsEnabled       INT DEFAULT 0,
    ResourceRatingsEnabled  INT DEFAULT 0,
    ResourceCommentsEnabled INT DEFAULT 0,
    AccessibilityWizardEnabled  INT DEFAULT 0,
    ReleaseFlagApproved     INT DEFAULT 0,
    SearchDBEnabled         INT DEFAULT 1,
    RecommenderDBEnabled    INT DEFAULT 1,
    OAISQEnabled            INT DEFAULT 1,
    OaiIdDomain             TEXT,
    OaiIdPrefix             TEXT,
    OaiEarliestDate         TEXT,
    OaiDateGranularity      ENUM ("DATE", "DATETIME"),
    SiteKeywords            TEXT,
    AboutText               TEXT,
    CurrentTheme            TEXT,
    ContactName             TEXT,
    ContactEmail            TEXT,
    Organization            TEXT,
    OrganizationType        TEXT,
    SiteType                TEXT,
    OkayToListSite          INT,
    AddAWStatsScript        INT DEFAULT 0,
    AddGoogleAnalytics      INT DEFAULT 0,
    GoogleAnalyticsCode     TEXT,
    DisplayLimitsByDefault  INT DEFAULT 0,
    LastRegisteredOn        DATETIME,
    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
);

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

-- portal news items or announcements
CREATE TABLE IF NOT EXISTS Announcements (
    AnnouncementId          INT NOT NULL AUTO_INCREMENT,
    AnnouncementHeading     TEXT,
    AnnouncementText        TEXT,
    DatePosted              DATETIME,
    PosterId                INT,
    INDEX                   (AnnouncementId)
);

-- saved searches
CREATE TABLE IF NOT EXISTS SavedSearches (
    SearchId            INT NOT NULL AUTO_INCREMENT,
    SearchName          TEXT,
    UserId              INT NOT NULL,
    DateLastRun         TIMESTAMP,
    Frequency           INT,
    INDEX               (UserId),
    INDEX               (SearchId)
);

-- saved search text parameters
CREATE TABLE IF NOT EXISTS SavedSearchTextParameters (
    SearchId            INT NOT NULL,
    FieldId             INT,
    SearchText          TEXT,
    INDEX               (SearchId)
);

-- saved search value ID parameters (used for controlled names, options, etc)
CREATE TABLE IF NOT EXISTS SavedSearchIdParameters (
    SearchId            INT NOT NULL,
    FieldId             INT,
    SearchValueId       INT,
    INDEX               (SearchId)
);

-- Rich Site Summary data export parameters
CREATE TABLE IF NOT EXISTS RSSConfiguration (
    EntriesToPublish        INT,
    NotifyBrowser           INT,
    ChannelTitle            TEXT,
    ChannelDescription      TEXT,
    ImageUrl                TEXT,
    ImageHeight             INT,
    ImageWidth              INT,
    ImageDescription        TEXT,
    Language                TEXT,
    Copyright               TEXT,
    ManagingEditor          TEXT,
    Webmaster               TEXT,
    PicsRating              TEXT
);

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

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

-- UI / accessibility user preferences
CREATE TABLE IF NOT EXISTS UserUIPreferences (
    UserId                      INT NOT NULL,
    FontSize                    INT,
    FontTypeFace                TEXT,
    FontColor                   TEXT,
    BackgroundColor             TEXT,
    ColorAvoidanceFlags         INT,
    ContentDensity              INT,
    ContentView                 INT,
    AudioDescriptionLevel       INT,
    AudioDescriptionLanguage    TEXT,
    VisualDescriptionLanguage   TEXT,
    UseGraphicAlternatives      INT,
    SignLanguage                TEXT,
    CaptionType                 INT,
    CaptionRate                 INT DEFAULT 120,
    INDEX                       (UserId)
);

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

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

-- Secure login data
CREATE TABLE LoginKeys (
  KeyPair TEXT,
  CreationTime TIMESTAMP
);

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

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

-- resource metadata field descriptions
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"),
    Description             TEXT,
    RequiredBySPT           INT DEFAULT 0,
    Enabled                 INT DEFAULT 1,
    Optional                INT DEFAULT 1,
    Viewable                INT DEFAULT 1,
    AllowMultiple           INT DEFAULT 0,
    IncludeInKeywordSearch  INT DEFAULT 0,
    IncludeInAdvancedSearch INT DEFAULT 0,
    IncludeInSortOptions    INT DEFAULT 1,
    IncludeInRecommender    INT DEFAULT 0,
    TextFieldSize           INT,
    MaxLength               INT,
    ParagraphRows           INT,
    ParagraphCols           INT,
    DefaultValue            TEXT,
    MinValue                INT,
    MaxValue                INT,
    FlagOnLabel             TEXT,
    FlagOffLabel            TEXT,
    DateFormat              TEXT,
    DateFormatIsPerRecord   INT,
    SearchWeight            INT,
    RecommenderWeight       INT,
    MaxHeight               INT,
    MaxWidth                INT,
    MaxPreviewHeight        INT,
    MaxPreviewWidth         INT,
    MaxThumbnailHeight      INT,
    MaxThumbnailWidth       INT,
    DefaultAltText          TEXT,
    ImagePreviewPrivilege   INT DEFAULT 0,
    UsesQualifiers          INT DEFAULT 0,
    HasItemLevelQualifiers  INT DEFAULT 0,
    DefaultQualifier        INT,
    DateLastModified        TIMESTAMP,
    LastModifiedById        INT,
    DisplayOrderPosition    INT,
    EditingOrderPosition    INT,
    UseForOaiSets           INT DEFAULT 0,
    ViewingPrivilege        INT DEFAULT 0,
    AuthoringPrivilege      INT DEFAULT 12,
    EditingPrivilege        INT DEFAULT 3,
    PointPrecision          INT,
    PointDecimalDigits      INT,
    UpdateMethod            ENUM("NoAutoUpdate","OnRecordCreate","Button",
                                    "OnRecordEdit", "OnRecordChange")
                                    DEFAULT "NoAutoUpdate",
    AllowHTML               INT DEFAULT 0,
    TreeBrowsingPrivilege   INT DEFAULT 0,
    ShowQualifiers          INT DEFAULT 0,
    INDEX                   (FieldId),
    INDEX                   (FieldType)
);

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

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

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

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

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

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

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

-- controlled names (publishers, authors, etc)
CREATE TABLE IF NOT EXISTS ControlledNames (
    ControlledNameId        INT NOT NULL AUTO_INCREMENT,
    ControlledName          TEXT,
    FieldId                 INT NOT NULL,
    QualifierId             INT,
    INDEX                   (ControlledNameId),
    INDEX                   (ControlledName(16)),
    INDEX                   (FieldId)
);

-- possible variants on controlled names
CREATE TABLE IF NOT EXISTS VariantNames (
    ControlledNameId        INT NOT NULL,
    VariantName             TEXT,
    INDEX                   (ControlledNameId),
    INDEX                   (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,
    Depth                   INT,
    ParentId                INT NOT NULL,
    SegmentName             TEXT,
    ResourceCount           INT,
    LinkString              TEXT,
    QualifierId             INT,
    INDEX                   (ClassificationId),
    INDEX                   (ParentId),
    INDEX                   (FieldId),
    INDEX                   (FieldId, ParentId),
    FULLTEXT                (SegmentName, ClassificationName)
);

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

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

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

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

-- image information
CREATE TABLE IF NOT EXISTS Images (
    ImageId                 INT NOT NULL,
    Format                  INT,
    AltText                 TEXT,
    Width                   INT,
    Height                  INT,
    PreviewWidth            INT,
    PreviewHeight           INT,
    ThumbnailWidth          INT,
    ThumbnailHeight         INT,
    INDEX                   (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,
    FileComment             TEXT,
    FileLength              INT,
    FileType                TEXT,
    SecretString            TEXT,
    INDEX                   (ResourceId),
    INDEX                   (FileId),
    INDEX                   (ResourceId, FieldId)
);

-- list of search word stems
CREATE TABLE IF NOT EXISTS SearchStems (
    WordId          INT NOT NULL AUTO_INCREMENT,
    WordText        TEXT,
    INDEX           (WordText(16)),
    INDEX           (WordId)
);

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

-- forums (top level of forum content hierarchy)
CREATE TABLE IF NOT EXISTS Forums (
    ForumId                 INT NOT NULL AUTO_INCREMENT,
    ForumName               TEXT,
    ForumDescription        TEXT,
    TopicCount              INT,
    MessageCount            INT,
    ModeratorId             INT,
    INDEX                   (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,
    DateCreated             DATETIME,
    TopicName               TEXT,
    ViewCount               INT,
    MessageCount            INT,
    INDEX                   (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,
    PosterId                INT,
    Subject                 TEXT,
    Body                    TEXT,
    INDEX                   (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,
    Keywords                TEXT,
    RunInterval             INT,
    INDEX                   (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                   (ItemIdA, ItemIdB)
);


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

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

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

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

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

CREATE TABLE IF NOT EXISTS ResourceCounts (
    FieldId INT,
    ClassName TEXT,
    CountType TEXT,
    Count INT
);
