/**
 * $Id$
 *
 * Create database tables for Citation Management System
 */

/**
 * Create the database.
 *
 * XXX Delete this later when testing is complete.
 * XXX We'll use the ebms database.
 */
CREATE DATABASE IF NOT EXISTS citems2;

USE citems2;

/**
 * pdq_ebms_control_val
 *
 * XXX - REPLACE WITH EBMS APPROACH? - XXX
 * XXX - DELETE ALTOGETHER? - XXX
 * An integrated, general purpose control table for associated values
 * with identifiers.  This is used instead of creating separate, identically
 * structured control tables that contain separate domains of values.
 *
 * This enables us to create a new domain of control values when we need
 * them without having to create a new table.  We just add another 
 * domain_id.
 *
 * Values must be unique within a domain (enforced by the control_val_index)
 * but need not be unique across domains.
 *
 *
 *  id              Unique ID of the value.
 *  domain_id       What kind of value it is, foreign key into control_domain.
 *  board_id        Some values are board specific.  A NULL in this field
 *                   indicates that the value is not associated with any
 *                   one specific board.
 *  value           The character string associated with this value.
 *                   Changing it here changes it for all uses of the id.
 *  description     Text used in help screens and documentation to explain
 *                   the meaning of the value.
 *  inactive_date   NULL = Okay to use this value.
 *                  Non-NULL = We stopped using it at this time.  Don't 
 *                   use it again.
 *
 */
CREATE TABLE pdq_ebms_control_val (
    id              INT AUTO_INCREMENT,
    domain_id       INT NOT NULL,
    board_id        INT NULL,
    board_hier_id   CHAR(8) NULL,
    value           VARCHAR(255) NOT NULL,
    description     VARCHAR(512) NOT NULL,
    inactive_date   DATETIME NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (domain_id) REFERENCES pdq_ebms_control_domain(id),
    -- FOREIGN KEY (board_id) REFERENCES pdq_ebms_editorial_board(id),
)
    ENGINE = InnoDB;

    CREATE UNIQUE INDEX control_val_index 
           ON pdq_ebms_control_val (domain_id, value);
    CREATE VIEW pdq_ebms_active_vals_view AS 
           SELECT id, domain_id, value
             FROM pdq_ebms_control_val
            WHERE inactive_date IS NULL;

/**
 * pdq_ebms_control_domain
 *
 * Defines a domain within which a set of control_vals are meaningful.
 * For example, there might be control values for each of the following
 * domains:
 *      Citation actions
 *      Levels of evidence
 *      Board member responses
 *      etc.
 *
 * If we need a new simple control table, it can be created by simply
 * creating another row in the control_domain table and then inserting
 * the desired values into the control_val table.
 *
 *  id              Unique ID of the domain.
 *  name            Short name used in software or user interfaces.
 *  description     Text used in help screens and documentation to explain
 *                   the meaning of the domain.
 *  system          'Y' = No user edit of values in this domain.  Disaster
 *                        is guaranteed to occur if changes are made.
 *                  'N' = System does not forbid edit.  Note: disaster might
 *                        still occur!  Use caution!  Know what you are doing!
 *  inactive_date   NULL = Okay to use this domain.
 *                  Non-NULL = We stopped using it at this time.  Don't use
 *                   it again.  Can't use any vals either.
 *                  Question:
 *                   XXX - IS THIS USEFUL AT THIS LEVEL?
 *                   XXX - WHY WOULD WE INACTIVATE AN ENTIRE DOMAIN?
 *                   XXX - DOES IT JUST COMPLICATE SOFTWARE TO CHECK IT?
 *
 * Notes:
 *  Current thinking is that there should be no user interface to add or
 *  update domains.  They are central to the system.  New domains would
 *  normally only be added when new software is written.
 *
 * Question:
 *  XXX - DO WE NEED SUBDOMAINS?
 */
CREATE TABLE pdq_ebms_control_domain (
    id               INT AUTO_INCREMENT,
    name             VARCHAR(32) NOT NULL,
    description      VARCHAR(512) NOT NULL,
    inactive_date    DATETIME NULL,
    PRIMARY KEY (id)
)
    ENGINE = InnoDB;


/**
 * pdq_ebms_cite
 *
 * Citations stored in XML, unmodified from PubMed.
 * If other sources used, they may be modified to match the PubMed DTD.
 *
 * All of the bibliographic (as opposed to control) data should be
 * copied from the source data from NLM or wherever.  We should not
 * modify it in our system.
 *
 *  id              Unique CiteMS identifier.
 *  source          Name of source, 'Pubmed' predominates.
 *  source_id       ID used by source for this article, e.g., PMID.
 *  source_jrnl_id  If source = 'Pubmed': then NLM unique journal id.
 *                   Else: to be determined.
 *  source_status   Source organization's status assignment to the record.
 *                   Enables us to identify, e.g., records that are 
 *                   "In-Process" and needing future update.
 *  jrnl_title      Title of journal as found in article record.
 *  brf_jrnl_title  Journal title abbreviation found in article record.
 *                   Normally this is the NLM title abbreviation.
 *  import_date     Datetime of first import.
 *  prepub_date     XXX - How is this used in the existing system?
 *                   XXX - It's almost, but not always, present.
 *                   XXX - It's usually identical or within milliseconds of
 *                         import_date.  These should be synchonized to
 *                         be identical in the new system.
 *                   XXX - Why is it null in just a few hundred cases?
 *  update_date     Datetime of last refresh from NLM or other update.
 *  title           Title of article, always in English (I think.)
 *  abstract        Abstract, currently always English.
 *  citation        String representation of the bibliographic citation.
 *  imported_text   Full record as imported, utf-8 character set.
 *                   Record is in XML for all Pubmed citations.
 *  import_format   Currently only supported format is:
 *                   'MX' = Medine XML DTD format
 *                   Others might be added in future.
 *  old_cms_id      Unique ID in old Citation Management System, only
 *                   used for converted records.
 *  pdf             PDF of the full text of the article, if available.
 *                   These may be different types of PDF - converted, scanned,
 *                   produced with different software and conforming to
 *                   different PDF definition versions.  It is always only
 *                   a blob in our system.
 *
 * Notes:
 *  The original citation management system had dates in the bib table.
 *  These are now retrievable instead by searching for the history of
 *  actions that were applied to the citation.  See history table.
 */
CREATE TABLE pdq_ebms_cite (
    id              INT AUTO_INCREMENT,
    source          VARCHAR(32) NOT NULL,
    source_id       VARCHAR(32) NOT NULL,
    source_jrnl_id  VARCHAR(32) NOT NULL,
    source_status   VARCHAR(32) NULL,
    jrnl_title      VARCHAR(255) NOT NULL,
    brf_jrnl_title  VARCHAR(127),
    import_date     DATETIME NOT NULL,
    prepub_date     DATETIME NULL,
    update_date     DATETIME NULL,
    title           VARCHAR(400) NOT NULL,
    abstract        TEXT NULL,
    citation        VARCHAR(255) NOT NULL,
    imported_text   TEXT NULL,
    import_format   ENUM('MP', 'MX'),
    old_cms_id      INT NULL,
    pdf             LONGBLOB NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (source_id) REFERENCES ebm_control_val(id),
)
    ENGINE = InnoDB;

    CREATE INDEX title_index ON cite (title);
    CREATE INDEX source_id_index ON cite (source, source_id);
    CREATE INDEX source_status_index ON cite (source, source_status);
    CREATE INDEX source_jrnl_id_index ON cite (source, source_jrnl_id);
    CREATE INDEX jrnl_title_index ON cite (jrnl_title);
    CREATE INDEX brf_jrnl_title_index ON cite (brf_jrnl_title);
    CREATE INDEX cite_index ON cite (citation);
    CREATE INDEX import_date_index ON cite (import_date_id);
    CREATE INDEX old_cms_id_index ON cite (old_cms_id);

    -- Pubmed only citations
    CREATE VIEW pdq_ebms_pubmed_cites_view AS
         SELECT *
           FROM pdq_ebms_cite
          WHERE source = 'Pubmed';

    CREATE VIEW


/**
 * pdq_ebms_author
 *
 * List of authors.  See author_cite.
 *
 *  id              Unique ID for this character string.
 *  last_name       Surname, called LastName in NLM XML.
 *  forename        First name, just initials when first name not given.
 *  initials        May be same as forename.
 *
 * Notes:
 *  These are as they appear in the XML records.  Whether a name string
 *  is unique depends on the authority control exercised by NLM.  We assume
 *  it is not unique and searches for an author may retrieve cites by 
 *  different people.
 */
CREATE TABLE pdq_ebms_author (
    id              INT AUTO_INCREMENT,
    last_name       VARCHAR(100) NOT NULL,
    forename        VARCHAR(100),
    initials        CHAR(5),
    PRIMARY KEY (id)
)
    ENGINE = InnoDB;

    CREATE UNIQUE INDEX author_index ON author (last_name,fore_name,initials);

/**
 * pdq_ebms_author_cite
 *
 * Join the authors with the citations.
 *
 *  author_id       Unique ID in author table.
 *  cite_id         Unique ID in cite table.
 *  cite_order      Order of this author in citation, e.g., first author,
 *                   second author, etc.  Origin 1.
 *
 * Notes:
 *  Use the primary key to find all citations by an author name.
 *  As a primary key, it enforces uniqueness in the InnoDB engine.
 *
 *  It's important to cite authors in the correct order of their
 *  appearance in an article.  This table won't support that.
 *  To correctly cite the article it is necessary to use the XML.
 */
CREATE TABLE pdq_ebms_author_cite (
    author_id       INT NOT NULL,
    cite_id         INT NOT NULL,
    cite_order      INT NOT NULL,
    PRIMARY KEY (author_id, cite_id),
    FOREIGN KEY (author_id) REFERENCES pdq_ebms_author(id),
    FOREIGN KEY (cite_id) REFERENCES pdq_ebms_cite(id)
)
    ENGINE = InnoDB;

    -- Finds authors of an article in the order cited in the article
    CREATE INDEX cite_author ON author_cite (cite_id, cite_order, author_id);


/**
 * pdq_ebms_review_cycle
 *
 * One row for each cycle, expected to be one month.  The review cycle
 * extends from the start_date to the day before the next start_date in
 * for another record.
 *
 *  id              Unique ID of the cycle.
 *  start_date      First day of the period, e.g., '2011-12-01T00:00:00'.
 *  name            Display name of the cycle, e.g., 'December 2011'.
 *  date_created    Mainly for debugging or management sleuthing.
 */
CREATE TABLE pdq_ebms_review_cycle (
    id              INT AUTO_INCREMENT,
    start_date      DATETIME NOT NULL UNIQUE,
    name            VARCHAR(24) NOT NULL UNIQUE,
    date_created    DATETIME NOT NULL,
    PRIMARY KEY (id)
)
    ENGINE = InnoDB;

/**
 * pdq_ebms_not_list
 *
 * If a journal has been found to be generally useless, an authorized
 * staff member can add it to a "NOT list".  Normally, NOT listed journals
 * will not appear in search results and will be automatically rejected.
 *
 * Maintenance of NOT lists inside the CiteMS is much simpler than 
 * attempting to modify every PubMed search to exclude every journal that
 * is not desirable.
 *
 * NOT lists will probably only ever be used on Pubmed imports, but we've
 * allowed for other sources.
 *
 *  source          Name of a source related to source_jrnl_id.
 *  source_jrnl_id  The unique id for this journal assigned by the source.
 *                   Using source + source_jrnl_id is more robust than 
 *                   using the title because titles can change.
 *  board_id        ID of the board for which the journal is NOT listed.
 *                  We might use the value NULL to mean "all boards".
 *                  A journal_id may appear multiple times, once for each
 *                  of several boards.
 *  start_date      Date time when the journal was NOT listed.
 *  user_id         ID of the user adding this NOT list entry.
 */
CREATE TABLE pdq_ebms_not_list (
    source          VARCHAR(32) NOT NULL,
    source_jrnl_id  VARCHAR(32) NOT NULL,
    board_id        INT NULL,
    start_date      DATETIME NOT NULL,
    user_id         INT NOT NULL,
    FOREIGN KEY (board_id) REFERENCES pdq_ebms_editorial_board(id)
    -- Have to see what EBMS does with persons
    -- FOREIGN KEY (user_id) REFERENCES person(id)
)
    ENGINE = InnoDB;

    CREATE INDEX not_journal_index 
        ON pdq_ebms_not_list(source, source_jrnl_id, board_id);


/**
 * pdq_ebms_history
 *
 * XXX - HAVE TO DO SOMETHING WITH REFERENCES - XXX
 * XXX - HAVE TO COORDINATE WITH BOB/EBMS - XXX
 * An audit trail of all actions that have occurred.
 *
 * When an action occurs with respect to an object in the system, we
 * record the occurrence here.  The history table will grow very large,
 * but most entries are short and this will enable us to determine
 * everything that happened in the past.  It supports management information
 * reporting, citation action history, debugging, and other uses.
 *
 *  id              Unique ID of the action.  Serializes all actions.
 *  object_type_id  Control table value telling us what kind of object
 *                   was acted upon.  Examples are: Citation, Journal,
 *                   Person, control table row, etc.
 *  object_id       Unique ID of the object of this type.
 *                   We can't rely on the database to enforce referential
 *                   integrity because the objects are in different tables.
 *                   But software should enforce it.
 *  action_id       What was done, import, insert, update, review, accept, etc.
 *  board_id        Some actions are board specific.  A NULL in this field
 *                   indicates that the action is not associated with any
 *                   one specific board.
 *  board_hier_id   Experimental, see editorial_board table.
 *  user_id         Who did it.
 *                   We probably need a generic "Person" id for the system
 *                   to handle history of automatic actions.
 *                   XXX - THERE ARE OTHER WAYS TO DO THIS.
 *  completion_date Date time of action completion.  If action failed, e.g.,
 *                   with transaction rollback, there should be no entry
 *                   in the history table.
 *  tag             Optional user entered keyword style free text tag 
 *                   for searching.
 *                   A "tag" action will allow a user to attach a note to
 *                   any object that can appear in the history table.  The
 *                   tag holds a keyword or phrase.  The data holds the
 *                   free text.  It's a way for users to make notes to
 *                   themselves or others about a citation or other object.
 *                   If the object_type_id is another action, for example
 *                   a review response, a user can effectively attach a note
 *                   to that response.  The object tagged might also be
 *                   the ID for another tag object - producing a conversation.
 *  data            Information recorded with the action.  Action specific.
 *
 * Notes:
 *  This will become a significant sized table, probably accumulating on 
 *  the order of 250,000 rows per year.  If each row, with indexes,
 *  averages say 400 bytes, then yearly size increase equals:
 *
 *     250,000 * 400 = 100 MB / year.
 *
 *  Sounds pretty feasible on modern hardware, and we can prune it if
 *  we need to.
 */
CREATE TABLE pdq_ebms_history (
    id              INT AUTO_INCREMENT,
    object_type_id  INT NOT NULL REFERENCES control_val(id),
    object_id       INT NOT NULL,
    action_id       INT NOT NULL REFERENCES control_val(id),
    board_id        INT NULL REFERENCES editorial_board(id),
    board_hier_id   CHAR(8) NULL REFERENCES editorial_board(hier_id),
    review_cycle_id INT NULL REFERENCES review_cycle(id),
    user_id         INT NOT NULL REFERENCES person(id),
    completion_date DATETIME NOT NULL,
    tag             varchar(40) NULL,
    data            varchar(2048) NULL,
    PRIMARY KEY (id)
)
    ENGINE = InnoDB;

    -- XXX - DO WE NEED A SEPARATE object_type, object_id INDEX?
    CREATE INDEX object_action_index ON history (object_type_id,
                                                 object_id, action_id);
    CREATE INDEX board_id_index ON history (board_id);
    CREATE INDEX board_hier_id_index ON history (board_id);
    CREATE INDEX user_index ON history (user_id);
    CREATE INDEX datetime_index ON history (completion_date);
    CREATE INDEX tag_index ON history (tag);


/**
 * pdq_ebms_import_batch
 *
 * One row for each batch of imported citations.
 *
 * Contains information describing a specific import action.  A full
 * picture of what happens in an import uses this table in combination
 * with other tables that track information about each citation that
 * is linked to a row in this table.
 *
 *  id              Unique ID of the import batch.
 *  import_date     Datetime of the import.
 *  review_cycle    Unique ID of a review cycle for the import batch.
 *  user_id         Unique ID of user running the import.
 *  summary_id      Unique ID of the summary for which this was an import.
 *                  Might be NULL in special cases?
 */
CREATE TABLE pdq_ebms_import_batch (
    id              INT AUTO_INCREMENT,
    import_date     DATETIME NOT NULL,
    review_cycle    INT NOT NULL REFERENCES review_cycle(id),
    user_id         INT NOT NULL REFERENCES usr(id),
    PRIMARY KEY (id)
)
    ENGINE = InnoDB;

/**
 * pdq_ebms_import_disposition
 * 
 * Control table for import_action.disposition.
 * 
 *  id              Unique ID of the citation.
 *  name            Human readable display name.
 */
CREATE TABLE pdq_ebms_import_disposition (
    id              CHAR,
    name            VARCHAR(32) NOT NULL UNIQUE,
    PRIMARY KEY(id)
)
    ENGINE = InnoDB;

    INSERT INTO import_disposition (id, name) values ('I', 'Imported');
    INSERT INTO import_disposition (id, name) values ('R', 'Rejected');
    INSERT INTO import_disposition (id, name) 
                values ('T', 'Summary topic added');
    INSERT INTO import_disposition (id, name) 
                values ('C', 'Topic and review cycle added');

/**
 * pdq_ebms_import_action
 * 
 * One row for each occasion in which a citation is presented to the
 * system in an import batch - whether or not the citation is actually
 * imported or is processed or rejected as a duplicate.
 * 
 *  cite_id         Unique ID of the citation.
 *  import_batch_id Unique ID of the batch.
 *  disposition     What was done with the imported cite, a code or ID
 *                  to be interpreted as one of:
 *                      Imported as new
 *                      Rejected as a duplicate
 *                      Duplicate but new summary topic added, no new review
 *                          cycle.
 *                      Duplicate but new summary topic added, new review
 *                          cycle added.
 */
CREATE TABLE pdq_ebms_import_action (
    cite_id         INT NOT NULL REFERENCES cite(id),
    import_batch_id INT NOT NULL REFERENCES import_batch(id),
    -- disposition     INT NOT NULL REFERENCES ...,
    PRIMARY KEY (cite_id, import_batch_id)
)
    ENGINE = InnoDB;

    -- CREATE INDEX XXX

/**
 * pdq_ebms_status_ctl
 *
 * A control table listing all of the valid status values.
 *
 *  id              Unique ID of this status value, used in status tables.
 *  name            Human readable name of the status, 
 *                   e.g., "Full text requested".
 *  description     Text used in help screens and documentation to explain
 *                   the meaning of the value.
 *  inactive_date   NULL = Okay to use this value.
 *                  Non-NULL = We stopped using it at this time.  Don't 
 *                   use it again.
 */
CREATE TABLE pdq_ebms_status_ctl (
    id              INT AUTO_INCREMENT,
    name            VARCHAR(32) NOT NULL,
    description     VARCHAR(512) NULL,
    inactive_date   DATETIME NULL,
    PRIMARY KEY (id),
)
    ENGINE = InnoDB;

/**
 * pdq_ebms_status
 *
 * Status assigned to a citation + Summary topic combination.
 * Status values are chronological.  Many may be assigned, but the
 * current status of a cite+topic is the one with the latest assigned date.
 *
 *  id              Unique ID of this record.
 *  status_id       Unique ID of the assigned status, from pdw_ebms_status_ctl.
 *  cite_id         Unique ID of the citation.
 *  topic_id        Unique ID of the Summary topic.  A citation used in
 *                   two or more Summaries has two or more status values,
 *                   each specific to the citation in the context of one
 *                   Summary topic.
 *  status_date     Datetime that cite+topic entered this status.
 *  user_id         User assigning this status to this cite+topic.
 *  comment         Optional free text note associated with this 
 *                   status assignment.
 */
CREATE TABLE pdq_ebms_status (
    id              INT AUTO_INCREMENT,
    status_id       INT NOT NULL,
    cite_id         INT NOT NULL,
    topic_id        INT NOT NULL,
    status_date     DATETIME NOT NULL,
    user_id         INT NOT NULL,
    comment         TEXT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (status_id) REFERENCES pdq_ebms_status_ctl(id),
    FOREIGN KEY (cite_id) REFERENCES pdq_ebms_cite(id),
    -- XXX This is in the tables Bob produced, must check
    -- FOREIGN KEY (topic_id) REFERENCES XXX(XXX),
    -- XXX This one is a drupal table
    -- FOREIGN KEY (user_id) REFERENCES XXX(XXX)
)
    ENGINE = InnoDB;
    CREATE INDEX cite_status_index 
           ON pdq_ebms_status(cite_id, topic_id, status_id)
    CREATE INDEX status_index 
           ON pdq_ebms_status(status_id, topic_id, cite_id)


/**
 * pdq_ebms_tag_ctl
 *
 * A control table listing all of the valid tag values.
 *
 * "Tags" are mainly used to associate comments and notes with citations
 * and to enable users to categorize citations in ways that would be 
 * useful for searching.
 *
 *  id              Unique ID of this tag value, used in tag tables.
 *  name            Human readable name of the tag, 
 *                   e.g., "Important Citation"
 *  description     Text used in help screens and documentation to explain
 *                   the meaning of the value.
 *  inactive_date   NULL = Okay to use this value.
 *                  Non-NULL = We stopped using it at this time.  Don't 
 *                   use it again.
 */
CREATE TABLE pdq_ebms_tag_ctl (
    id              INT AUTO_INCREMENT,
    name            VARCHAR(32) NOT NULL,
    description     VARCHAR(512) NULL,
    inactive_date   DATETIME NULL,
    PRIMARY KEY (id),
)
    ENGINE = InnoDB;

/**
 * pdq_ebms_tag
 *
 * Tag assigned to a citation.  See pdq_ebms_tag_ctl.
 *
 * Tag recordss are active unless and until they are de-activated - which 
 * is like deleting them for some uses, but avoids dropping them out 
 * out of the database.
 *
 * A single citation can accumulate any number of tag records.  Unlike
 * status records, they are all active at once unless and until they
 * are explicitly de-activated.
 *
 *  id              Unique ID of this record.
 *  tag_id          Unique ID of the assigned tag, from pdw_ebms_tag_ctl.
 *  cite_id         Unique ID of the citation.
 *  tag_date        Datetime that cite+topic entered this tag.
 *  user_id         User assigning this status to this cite+topic.
 *  active_status   'Y' = active, 'N' = inactive.
 *  comment         Optional free text note associated with this 
 *                   tag assignment.
 */
CREATE TABLE pdq_ebms_tag (
    id              INT AUTO_INCREMENT,
    tag_id          INT NOT NULL,
    cite_id         INT NOT NULL,
    tag_date        DATETIME NOT NULL,
    active_status   ENUM('Y', 'N') NOT NULL,
    user_id         INT NOT NULL,
    comment         TEXT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (tag_id) REFERENCES pdq_ebms_tag_ctl(id),
    FOREIGN KEY (cite_id) REFERENCES pdq_ebms_cite(id),
    -- XXX This one is a drupal table
    -- FOREIGN KEY (user_id) REFERENCES XXX(XXX)
)
    ENGINE = InnoDB;
    CREATE INDEX cite_tag_index ON pdq_ebms_tag(cite_id, tag_id)
    CREATE INDEX tag_index ON pdq_ebms_tag(tag_id, cite_id)
    CREATE VIEW pdq_ebms_active_tags_view AS 
           SELECT *
             FROM pdq_ebms_tag
            WHERE active_status = 'Y';


/*
XXX - LEFT OFF HERE - XXX
*/

GRANT ALL ON citems.* TO citems;

