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

/**
 * cite
 *
 * Citations stored in XML, unmodified from PubMed.
 * If other sources used, they may be modified to match the PubMed DTD.
 *
 *  id              Unique CiteMS identifier.
 *  source_id       ID of source, always PubMed in initial system.
 *  pubmed_id       PubMed unique article ID.
 *  other_id        Used if and only if article is not from PubMed.
 *  journal_id      ID of journal in journal table.
 *  title           Title from article, always in English.
 *                   The title is denormalized from the XML for the citation
 *                   but it's a useful item for indexing and searching.
 *  abstract        XXX - Do we need it?  It's frequently displayed.
 *                   XXX - Should we get it from the XML each time?
 *  citation        String representation of the bibliographic citation.
 *                   XXX - Should this be maintained?
 *  xml             Full record in NLM DTD format, utf-8 character set.
 *
 *
 * 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 audit_trail.
 */
CREATE TABLE cite (
    id              INT AUTO_INCREMENT,
    source_id       INT NOT NULL REFERENCES control_val(id),
    pubmed_id       INT,
    other_id        VARCHAR(25) NULL,
    journal_id      INT REFERENCES journal(id),
    title           VARCHAR(400) NOT NULL,
    abstract        TEXT NULL,
    citation        VARCHAR(255) NOT NULL,
    xml             TEXT NOT NULL,
    PRIMARY KEY (id)
);
  CREATE INDEX title_index ON title;
  CREATE INDEX pubmed_index ON pubmed_id;
  CREATE INDEX journal_index ON journal;
  CREATE INDEX cite_index ON citation;

/**
 * author
 *
 * List of authors.  See author_cite.
 *
 *  id              Unique ID for this character string.
 *  name            Last name, first name, middle initial.
 *                   e.g. "Hall, Ingrid J"
 *  short_name      Last name plus initials in old Medline format.
 *                   e.g. "Hall IJ"
 *
 * 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.
 *
 *  Short name is used to find searches using old style names.  There
 *  are other ways to do this, but PubMed uses this approach so we do too.
 */
CREATE TABLE author (
    id              INT AUTO_INCREMENT,
    name            VARCHAR(100),
    short_name      VARChAR(50),
    PRIMARY KEY (id)
);
  CREATE INDEX name_index ON name;
  CREATE INDEX short_name_index ON short_name;

/**
 * author_cite
 *
 * Join the authors with the citations.
 *
 *  author_id       Unique ID in author table.
 *  cite_id         Unique ID in cite table.
 *
 * Notes:
 *  Use the primary key to find all citations by an author name.
 *
 *  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 author_cite (
    author_id       INT REFERENCES author(id),
    cite_id         INT REFERENCES cite(id),
    PRIMARY KEY (author_id, cite_id)
);

/**
 * journal
 *
 * Table of journals to which individual citations belong.
 * Every journal, without exception, should reference an entry in the
 * journal table.
 *
 * Journals are complicated.  They mutate, change titles, split in two,
 * merge into one and change characteristics.  However we don't worry
 * about that for any journal originating at NLM.  We let them maintain
 * the journals and we just download information from them, getting
 * updates when needed.  So long as PubMed is internally consistent
 * (i.e., the NLM Unique Journal ID assigned to a citation matches a
 * journal in their database, we will be consistent.
 *
 *  id              CiteMS unique journal ID.
 *  source_id       ID of source, always PubMed in initial system.
 *  pubmed_id       PubMed unique article ID.
 *  other_id        Used if and only if journal is not from PubMed.
 *  short_title     Used in brief citation displays.
 *  full_title      Used in long displays.
 */
CREATE TABLE journal (
    id              INT AUTO_INCREMENT,
    source_id       INT NOT NULL REFERENCES control_val(id),
    pubmed_id       INT NULL,
    other_id        VARCHAR(25) NULL,
    short_title     VARCHAR(50) NOT NULL,
    full_title      VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);
    CREATE INDEX pubmed_journal_index (pubmed_id);
    CREATE INDEX short_title_index (short_title);
    CREATE INDEX full_title_index (full_title);

/**
 * control_val
 *
 * 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.
 *  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 control_val (
    id              INT AUTO_INCREMENT,
    domain_id       INT NOT NULL REFERENCES control_domain(id),
    value           VARCHAR(255) NOT NULL,
    description     VARCHAR(512) NOT NULL,
    inactive_date   DATETIME NULL,
    PRIMARY_KEY (id)
);
    CREATE UNIQUE INDEX control_val_index (domain_id, value);
    CREATE VIEW active_vals AS 
           SELECT id, domain_id, value, value
             FROM control_val
            WHERE inactive_date IS NULL;

/**
 * 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 control_domain (
   id               INT AUTO_INCREMENT,
   name             VARCHAR(255) NOT NULL,
   description      VARCHAR(512) NOT NULL,
   inactive_date    DATETIME NULL,
   PRIMARY KEY (id)
);

/**
 * history
 *
 * 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     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          What was done, import, insert, update, review, accept, etc.
 *  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.
 *  timestamp       Date time of action completion.  If action failed, e.g.,
 *                   with transaction rollback, there should be no entry
 *                   in the history table.
 *  data            Information recorded with the action.  Action specific.
 */
CREATE TABLE history (
   id               INT AUTO_INCREMENT,
   object_type      INT NOT NULL REFERENCES control_val(id),
   object_id        INT NOT NULL,
   action           INT NOT NULL REFERENCES control_val(id),
   user_id          INT NOT NULL REFERENCES person(id),
   timestamp        DATETIME NOT NULL,
   data             varchar(512) NULL
   PRIMARY KEY (id)
);
   -- XXX - DO WE NEED A SEPARATE object_type, object_id INDEX?
   CREATE INDEX object_action_index (object_type, object_id, action);
   CREATE INDEX user_index (user_id);
   CREATE INDEX datetime_index (timestamp);

XXX - LEFT OFF HERE - XXX
Need:
    tag
        Table of tag + comment + cite id + datetime
        tags are in control table
    status
        Table of status + comment + cite_id + datetime
        statuses are in control table
