
-- new table for event logging
CREATE TABLE EventLog (
    EventType   INT NOT NULL,
    EventDate   TIMESTAMP NOT NULL,
    UserId      INT,
    DataOne     TEXT,
    DataTwo     TEXT,
    INDEX       (EventType, EventDate),
    INDEX       (EventType)
);

-- new table for user-defined privileges
CREATE TABLE CustomPrivileges (
    Id          INT NOT NULL,
    Name        TEXT,
    INDEX       (Id)
);

-- new columns for per-field editing and viewing privileges
ALTER TABLE MetadataFields ADD COLUMN ViewingPrivilege INT DEFAULT 0;
ALTER TABLE MetadataFields ADD COLUMN AuthoringPrivilege INT DEFAULT 12;
ALTER TABLE MetadataFields ADD COLUMN EditingPrivilege INT DEFAULT 3;
UPDATE MetadataFields SET ViewingPrivilege = 12 WHERE FieldName = "Release Flag" OR FieldName = "Added By Id" OR FieldName = "Last Modified By Id";
UPDATE MetadataFields SET AuthoringPrivilege = 7 WHERE FieldName = "Release Flag";
UPDATE MetadataFields SET EditingPrivilege = 7 WHERE FieldName = "Release Flag";
UPDATE MetadataFields SET IncludeInAdvancedSearch = 1 WHERE FieldName = "Release Flag" OR FieldName = "Added By Id" OR FieldName = "Last Modified By Id";

-- new fields for user tracking
ALTER TABLE APUsers ADD COLUMN LastLocation TEXT;
ALTER TABLE APUsers ADD COLUMN LastActiveDate DATETIME;
ALTER TABLE APUsers ADD COLUMN LastIPAddress TEXT;

