/*
* Copyright (c) 2009 Stanford University, School of Medicine All rights reserved. 
* 
* STANFORD ACADEMIC SOFTWARE LICENSE AGREEMENT 
* FOR AngioLocalize SOURCE CODE: 
* 
* 1.  This is a legal agreement between you, RECIPIENT, and STANFORD UNIVERSITY.  
*       By accepting, receiving, and using AngioLocalize.including any accompanying information, materials or 
*       manuals (Program), you are agreeing to be bound by the terms of this Agreement.  
*       If you do not agree to the terms of this Agreement, promptly return the Program to the STANFORD.

* 2.  STANFORD grants to RECIPIENT a royalty-free, nonexclusive, and nontransferable license to use the Program 
*       and accompanying source code furnished hereunder, upon the terms and conditions set out below. 
*       documentation and/or other materials provided with the distribution. 
* 3.  RECIPIENT acknowledges that the Program is a research tool still in the development stage and that 
*       it is being supplied as is, without any accompanying services, support or improvements from STANFORD.
* 4.  RECIPIENT agrees to use the Program solely for internal non-commercial purposes. 
* 5.  RECIPIENT may create derivatives and further transfer the Program under the following terms and conditions: 
*        A) Recipient agrees to not commercially exploit the Program and agrees all uses will be for non-commercial purposes; and,
*        B) Recipient may not mark any derivatives with the title of Program and may not to use iPad mark for any derivatives; and,
*        C) Recipient agrees to mark and acknowledge all derivatives as follows: "This is a derivative work based on AngioLocalize:
*           Frameless AVM localization with 2D Digital Subtraction Angiography (DSA) and 3D Rotational Angiography (DRA) Images developed
*           by Dimitre Hristov, Lina Liu.
*        D) Recipient agrees to make all improvements, enhancements and derivatives available under 
*           similar terms and conditions to the research community. 
* 6.   RECIPIENT warrants that RECIPIENT will not remove or export any part of the software or Program from the United 
*       States except in full compliance with all United States and other applicable laws and regulations.
* 7.  RECIPIENT will use the Program in compliance with all applicable laws, policies and regulations including, but not 
*       limited to, any approvals, informed consent and patient confidentiality principles.
* 8.   RECIPIENT will indemnify, hold harmless, and defend Stanford against any claim of any kind arising out of or
*       related to the exercise of any rights granted under this Agreement or the breach of this Agreement by RECIPIENT.
* 9.  Title and copyright to the Program and any associated documentation shall at all times remain with STANFORD, 
*       and RECIPIENT agrees to preserve same.
*/ 
 DROP Table if exists Patients cascade;
 DROP Table if exists Images cascade; 
 DROP Table if exists CSOList cascade;
 DROP TABLE if exists Markers cascade;
 DROP TABLE if exists RegMatrices cascade;
 DROP TABLE if exists Users cascade;
 DROP TABLE if exists Approvals cascade;
  
  CREATE TABLE Patients(
    PatientID SERIAL    PRIMARY KEY,
    lastName  VARCHAR(45) NOT NULL,
    firstName VARCHAR(45) NOT NULL,
    dateofBirth INTEGER       NOT NULL,
    sex         INTEGER       NOT NULL,
    MRO         VARCHAR(50)   unique  NOT NULL
  );
  
  

 
 CREATE TABLE Images(
  ImageID  SERIAL     PRIMARY KEY,
  imagetype SMALLINT NOT NULL,
  imageDir  VARCHAR(255)  NULL,
  imageName VARCHAR(500) NOT NULL,
  patientID  INTEGER  NOT NULL references Patients on Delete Cascade,
  modality   VARCHAR(10) NOT NULL, 
  seriesDescr  VARCHAR(100) NOT NULL
 );
 
 
 
 
 CREATE TABLE CSOList (
   CSOId  SERIAL    PRIMARY KEY,
   srcImage INTEGER NOT NULL,
   filename VARCHAR(255),
   patientID INTEGER NOT NULL REFERENCES Patients on DELETE CASCADE
 );
 

 
 CREATE TABLE Markers (
    markerID  SERIAL    PRIMARY KEY,
    srcImage  INTEGER NOT NULL,
    filename  VARCHAR(255),
    patientID INTEGER NOT NULL REFERENCES Patients ON DELETE CASCADE 
 );
 
 
 CREATE TABLE RegMatrices (
   matrixID         SERIAL    PRIMARY KEY,
   status           INTEGER NOT NULL, -- 0: direct; 1: inferred 
   srcImage         INTEGER NOT NULL,
   targetImage      INTEGER NOT NULL,
   type             INTEGER NOT NULL, -- 0: 3d-3d; 1: 3d-2d
   affineM          TEXT NULL,
   perspectM        TEXT NULL,
   postAffineM      TEXT NULL,
   FOREIGN KEY(srcImage) REFERENCES Images(ImageID) ON DELETE CASCADE,
   FOREIGN KEY(targetImage) REFERENCES Images(ImageID) ON DELETE CASCADE
 );

 
CREATE TABLE Users (
  userID           SERIAL   PRIMARY KEY,
  userRights       VARCHAR(100) NOT NULL, --adminRight, approvalRight and readRight
  firstName        VARCHAR(100) NOT NULL,
  lastName         VARCHAR(100) NOT NULL,
  userName         VARCHAR(45) NOT NULL,
  password         VARCHAR(100) NOT NULL,
  sessionID        VARCHAR(200)
);


CREATE TABLE Approvals (
  userID           INTEGER NOT NULL,
  objID            INTEGER NOT NULL,
  objType          INTEGER NOT NULL, --0: object is a registration; 1: object is a CSO
  approvalDate     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  approvalStatus   INTEGER NOT NULL, --0: not approved; 1: approved0: not approved; 1: approved
  patientID        INTEGER NOT NULL,
  
  PRIMARY KEY (userID, objID, objType),
  FOREIGN KEY(userID) REFERENCES Users(userID) ON DELETE CASCADE,
  FOREIGN KEY(patientID) REFERENCES Patients(PatientID) ON DELETE CASCADE
);

INSERT INTO Users (userid, userRights, firstName, lastName, userName, password) Values(DEFAULT, 'adminRight','admin', 'admin', 'root', 'administrator');