/****************************************************************************
 * Database create script
 *
 * Database:         jcr
 * DBMS:             mysql
 *****************************************************************************/

/*===========================================================================
 * Table:            systemids
 * Description:      Used to generate system IDs for DBMSs without support for
 *                   sequences. Must contain exactly 1 row
 *===========================================================================*/
CREATE TABLE systemids (
    nextsystemid         INT NOT NULL,
    CONSTRAINT pk_systemids PRIMARY KEY (nextsystemid)
)
TYPE=InnoDB
;


/*===========================================================================
 * Table:            systemuser
 * Description:      Records details of a user authorised to use JCR
 *===========================================================================*/
CREATE TABLE systemuser (
    systemid             INT NOT NULL,
    username             VARCHAR(20) NOT NULL,
    name                 VARCHAR(50) NOT NULL,
    issuperuser          CHAR(1) NOT NULL,
    password             VARCHAR(32) NOT NULL,
    email                VARCHAR(100),
    enabled              CHAR(1) NOT NULL,
    CONSTRAINT pk_systemuser PRIMARY KEY (systemid)
)
TYPE=InnoDB
;


/*===========================================================================
 * Table:            fileset
 * Description:      Represents a set of files that can be used by one or more
 *                   review projects.
 *                   Each set can be either uploaded as a tarball, or extracted
 *                   from source control
 *===========================================================================*/
CREATE TABLE fileset (
    systemid             INT NOT NULL,
    owner                INT NOT NULL,
    createdate           DATETIME NOT NULL,
    directory            VARCHAR(500),
    baseline             VARCHAR(500),
    CONSTRAINT pk_fileset PRIMARY KEY (systemid),
    CONSTRAINT fk_fileset_systemuser FOREIGN KEY (owner) REFERENCES systemuser(systemid)
)
TYPE=InnoDB
;


/*===========================================================================
 * Table:            project
 * Description:      Represents a code review project
 *===========================================================================*/
CREATE TABLE project (
    systemid             INT NOT NULL,
    name                 VARCHAR(100) NOT NULL,
    owner                INT NOT NULL,
    createdate           DATETIME NOT NULL,
    directory            VARCHAR(200) NOT NULL,
    currentstatus        VARCHAR(10) NOT NULL,
    originalfileset      INT,
    modifiedfileset      INT,
    notes                LONGTEXT,
    CONSTRAINT pk_project PRIMARY KEY (systemid),
    CONSTRAINT fk_project_systemuser FOREIGN KEY (owner) REFERENCES systemuser(systemid),
    CONSTRAINT fk_project_originalfileset FOREIGN KEY (originalfileset) REFERENCES fileset(systemid),
    CONSTRAINT fk_project_modifiedfileset FOREIGN KEY (modifiedfileset) REFERENCES fileset(systemid)
)
TYPE=InnoDB
;


/*===========================================================================
 * Table:            projectstatus
 * Description:      Records the history of status changes to each project
 *===========================================================================*/
CREATE TABLE projectstatus (
    systemid             INT NOT NULL,
    project              INT NOT NULL,
    changedby            INT NOT NULL,
    changedate           DATETIME NOT NULL,
    newstatus            VARCHAR(2) NOT NULL,
    CONSTRAINT pk_projectstatus PRIMARY KEY (systemid),
    CONSTRAINT fk_projectstatus_project FOREIGN KEY (project) REFERENCES project(systemid),
    CONSTRAINT fk_projectstatus_changedBy FOREIGN KEY (changedby) REFERENCES systemuser(systemid)
)
TYPE=InnoDB
;


/*===========================================================================
 * Table:            projectthread
 * Description:      Groups together project-level comments with a common title,
 *                   like an email thread
 *===========================================================================*/
CREATE TABLE projectthread (
    systemid             INT NOT NULL,
    project              INT NOT NULL,
    owner                INT NOT NULL,
    threadtitle          VARCHAR(100) NOT NULL,
    CONSTRAINT pk_projectthread PRIMARY KEY (systemid),
    CONSTRAINT fk_projectthread_project FOREIGN KEY (project) REFERENCES project(systemid),
    CONSTRAINT fk_projectthread_systemuser FOREIGN KEY (owner) REFERENCES systemuser(systemid)
)
TYPE=InnoDB
;

CREATE INDEX in_projectthread_project ON projectthread (
    project
);

CREATE INDEX in_projectthread_owner ON projectthread (
    owner
);


/*===========================================================================
 * Table:            projectcomment
 * Description:      Review comments made against a project thread
 *===========================================================================*/
CREATE TABLE projectcomment (
    systemid             INT NOT NULL,
    projectthread        INT NOT NULL,
    owner                INT NOT NULL,
    createdate           DATETIME NOT NULL,
    comment              LONGTEXT NOT NULL,
    reviewdate           DATETIME,
    reviewcomment        LONGTEXT,
    reviewer             INT,
    reviewdecision       VARCHAR(50),
    reviewactionowner    INT,
    actiondate           DATETIME,
    actioncomment        LONGTEXT,
    actioner             INT,
    actionscomplete      CHAR(1),
    CONSTRAINT pk_projectcomment PRIMARY KEY (systemid),
    CONSTRAINT fk_projectcomment_projectthread FOREIGN KEY (projectthread) REFERENCES projectthread(systemid),
    CONSTRAINT fk_projectcomment_systemuser_owner FOREIGN KEY (owner) REFERENCES systemuser(systemid),
    CONSTRAINT fk_projectcom_systemuser_reviewer FOREIGN KEY (reviewer) REFERENCES systemuser(systemid),
    CONSTRAINT fk_projectcom_systemuser_revaction FOREIGN KEY (reviewactionowner) REFERENCES systemuser(systemid),
    CONSTRAINT fk_projectcom_systemuser_actioner FOREIGN KEY (actioner) REFERENCES systemuser(systemid)
)
TYPE=InnoDB
;

CREATE INDEX in_projectcomment_projectthread ON projectcomment (
    projectthread
);

CREATE INDEX in_projectcomment_owner ON projectcomment (
    owner
);

CREATE INDEX in_projectcomment_reviewer ON projectcomment (
    reviewer
);

CREATE INDEX in_projectcomment_revaction ON projectcomment (
    reviewactionowner
);

CREATE INDEX in_projectcomment_actioner ON projectcomment (
    actioner
);


/*===========================================================================
 * Table:            projectfile
 * Description:      Represents a file within a project that has been selected
 *                   for review
 *===========================================================================*/
CREATE TABLE projectfile (
    systemid             INT NOT NULL,
    project              INT NOT NULL,
    path                 VARCHAR(500) NOT NULL,
    originalpath         VARCHAR(500),
    changetype           CHAR(1) NOT NULL,
    notes                LONGTEXT,
    reviewpriority       SMALLINT,
    CONSTRAINT pk_projectfile PRIMARY KEY (systemid),
    CONSTRAINT fk_projectfile_project FOREIGN KEY (project) REFERENCES project(systemid)
)
TYPE=InnoDB
;

CREATE UNIQUE INDEX in_projectfile_project_path ON projectfile (
    project,
    path(500)
);


/*===========================================================================
 * Table:            filecomment
 * Description:      Review comments made against lines in a file
 *===========================================================================*/
CREATE TABLE filecomment (
    systemid             INT NOT NULL,
    projectfile          INT NOT NULL,
    linenumber           INT NOT NULL,
    owner                INT NOT NULL,
    createdate           DATETIME NOT NULL,
    comment              LONGTEXT NOT NULL,
    reviewdate           DATETIME,
    reviewcomment        LONGTEXT,
    reviewer             INT,
    reviewdecision       VARCHAR(50),
    reviewactionowner    INT,
    actiondate           DATETIME,
    actioncomment        LONGTEXT,
    actioner             INT,
    actionscomplete      CHAR(1),
    CONSTRAINT pk_filecomment PRIMARY KEY (systemid),
    CONSTRAINT fk_filecomment_projectfile FOREIGN KEY (projectfile) REFERENCES projectfile(systemid),
    CONSTRAINT fk_filecomment_systemuser_owner FOREIGN KEY (owner) REFERENCES systemuser(systemid),
    CONSTRAINT fk_filecom_systemuser_reviewer FOREIGN KEY (reviewer) REFERENCES systemuser(systemid),
    CONSTRAINT fk_filecom_systemuser_revaction FOREIGN KEY (reviewactionowner) REFERENCES systemuser(systemid),
    CONSTRAINT fk_filecom_systemuser_actioner FOREIGN KEY (actioner) REFERENCES systemuser(systemid)
)
TYPE=InnoDB
;

CREATE INDEX in_filecomment_owner ON filecomment (
    owner
);

CREATE INDEX in_filecomment_reviewer ON filecomment (
    reviewer
);

CREATE INDEX in_filecomment_revaction ON filecomment (
    reviewactionowner
);

CREATE INDEX in_filecomment_actioner ON filecomment (
    actioner
);