CREATE TABLE version( version VARCHAR( 20 ) );                       
CREATE TABLE BaseInfo ( 												
    id          INTEGER         PRIMARY KEY,			
    caseName    VARCHAR( 100 )   NOT NULL,							
    patientName VARCHAR( 10 )   NOT NULL,							
    sex         VARCHAR( 10 )   NOT NULL,							
    age         INTEGER         NOT NULL,							
    birth       DATE            NOT NULL,
    career      VARCHAR( 50 ),							
    hometown    VARCHAR( 100 ),
    workplace   VARCHAR( 100 ),
    marriage    VARCHAR( 10 ),
    complaint   VARCHAR( 200 )  NOT NULL,							
    photo       BLOB
);												
CREATE TABLE GeneralInfo (
    id           INTEGER          PRIMARY KEY,			
    caseCata1Id  INTEGER          NOT NULL,
    caseCata2Id  INTEGER          NOT NULL,
    caseCata3Id  INTEGER          NOT NULL,
    caseID       INTEGER          NOT NULL								
                                REFERENCES BaseInfo ( id ),			
    caseName     VARCHAR( 100 )   NOT NULL,							
    presentation VARCHAR( 1000 )  NOT NULL,
    diagnosis    VARCHAR( 1000 )   NOT NULL,
    keyLeaningIssues VARCHAR( 1000 ) NOT NULL,
    difficulty   VARCHAR( 100 )   NOT NULL,
    author       VARCHAR( 50 )    NOT NULL,
    affiliation   VARCHAR( 100 )
);
CREATE TABLE KeyWords (
    id           INTEGER         PRIMARY KEY,			
    caseID       INTEGER          NOT NULL								
                                REFERENCES BaseInfo ( id ),			
    keyword      VARCHAR( 50 )   NOT NULL
);
CREATE TABLE Interview ( 											
    id         INTEGER          PRIMARY KEY,				
    caseID     INTEGER          NOT NULL								
                                REFERENCES BaseInfo ( id ),			
    question   VARCHAR(500)     NOT NULL,								
    answer     VARCHAR(500)     NOT NULL,								
    score      INTEGER          NOT NULL,								
    parentID   INTEGER          NOT NULL,
    isRight    BOOLEAN          NOT NULL
);																	
CREATE TABLE Exam ( 													
    id          INTEGER          PRIMARY KEY,			
    caseID      INTEGER          NOT NULL								
                                 REFERENCES BaseInfo ( id ),				
    itemID      INTEGER          NOT NULL,								
    itemName    VARCHAR(200)     NOT NULL,
    result      VARCHAR(500)     NOT NULL,
    score       INTEGER          NOT NULL,
    isRight     BOOLEAN          NOT NULL,
    videoExName VARCHAR(50),
    picture     BLOB,
    video       BLOB
);																	
CREATE TABLE Lab ( 													
    id          INTEGER          PRIMARY KEY,
    caseID      INTEGER          NOT NULL								
                                 REFERENCES BaseInfo ( id ),				
    itemID      INTEGER          NOT NULL,
    itemName    VARCHAR(200)     NOT NULL,								
    result      VARCHAR(500)     NOT NULL,
    score       INTEGER          NOT NULL,
    isRight     BOOLEAN          NOT NULL,
    videoExName VARCHAR(50),
    picture     BLOB,
    video       BLOB
);																	
CREATE TABLE Hypothesis (
    id        INTEGER          PRIMARY KEY,				
    caseID    INTEGER          NOT NULL								
                               REFERENCES BaseInfo ( id ),				
    resultID  INTEGER          NOT NULL,								
    score     INTEGER          NOT NULL								
);
CREATE TABLE HypothesisConclusion (
    id            INTEGER          PRIMARY KEY,				
    hypothesisId  INTEGER          NOT NULL								
                                   REFERENCES Hypothesis( resultID ),				
    conclusionId  INTEGER          NOT NULL,								
    score         INTEGER          NOT NULL								
);
CREATE TABLE Diagnosis ( 											
    id             INTEGER          PRIMARY KEY,		
    caseID         INTEGER          NOT NULL							
                                    REFERENCES BaseInfo ( id ),		
    result         VARCHAR(2000)    NOT NULL,
    basis          VARCHAR(2000)    NOT NULL,
    score          INTEGER          NOT NULL,
    isRight        BOOLEAN          NOT NULL
);
CREATE TABLE DiagnoseResult (
    id             INTEGEr          PRIMARY KEY,
    DiagnoseID     INTEGER          NOT NULL
                                    REFERENCES Diagnosis ( id ),
    DiagnoseBaseID INTEGER          NOT NULL,
    DiagnoseType   INTEGER          NOT NULL  -- 0-major,1-minor,2-complication
);
CREATE TABLE DiagnoseBasisInterview (
    id             INTEGER          PRIMARY KEY,
    DiagnoseID     INTEGER          NOT NULL
                                    REFERENCES Diagnosis ( id ),
    interviewID    INTEGER          NOT NULL
                                    REFERENCES Interview ( id )
);
CREATE TABLE DiagnoseBasisExam (
    id             INTEGER          PRIMARY KEY,
    DiagnoseID     INTEGER          NOT NULL
                                    REFERENCES Diagnosis ( id ),
    examID         INTEGER          NOT NULL
                                    REFERENCES Exam ( id )
);
CREATE TABLE DiagnoseBasisLab (
    id             INTEGER          PRIMARY KEY,
    DiagnoseID     INTEGER          NOT NULL
                                    REFERENCES Diagnosis ( id ),
    labID          INTEGER          NOT NULL
                                    REFERENCES Lab ( id )
);
CREATE TABLE Differentiation( 											
    id             INTEGER          PRIMARY KEY,		
    caseID         INTEGER          NOT NULL							
                                    REFERENCES BaseInfo ( id ),		
    result         VARCHAR(2000)    NOT NULL,
    basis          VARCHAR(2000)    NOT NULL,
    score          INTEGER          NOT NULL,
    isRight        BOOLEAN          NOT NULL
);
CREATE TABLE DifferentiationResult (
    id                    INTEGEr          PRIMARY KEY,
    DifferentiationID     INTEGER          NOT NULL
                                           REFERENCES Differentiation ( id ),
    DifferentiationBaseID INTEGER          NOT NULL,
    DifferentiationType   INTEGER          NOT NULL  -- 0-major,1-minor,2-complication
);
CREATE TABLE DifferentiationBasisInterview (
    id                    INTEGER          PRIMARY KEY,
    DifferentiationID     INTEGER          NOT NULL
                                           REFERENCES Differentiation ( id ),
    interviewID           INTEGER          NOT NULL
                                           REFERENCES Interview ( id )
);
CREATE TABLE DifferentiationBasisExam (
    id                    INTEGER          PRIMARY KEY,
    DifferentiationID     INTEGER          NOT NULL
                                           REFERENCES Differentiation ( id ),
    examID                INTEGER          NOT NULL
                                           REFERENCES Exam ( id )
);
CREATE TABLE DifferentiationBasisLab (
    id                    INTEGER          PRIMARY KEY,
    DifferentiationID     INTEGER          NOT NULL
                                           REFERENCES Differentiation ( id ),
    labID                 INTEGER          NOT NULL
                                           REFERENCES Lab ( id )
);

CREATE TABLE Management ( 											
    id             INTEGER PRIMARY KEY,					
    caseID         INTEGER NOT NULL									
                         REFERENCES BaseInfo ( id ),					
    cataID         INTEGER        NOT NULL,
    managementType VARCHAR(100)   NOT NULL,
    management     VARCHAR(1000)  NOT NULL,
    score          INTEGER NOT NULL,    
    isRight        BOOLEAN NOT NULL
);																	
CREATE TABLE Medication ( 											
    id                INTEGER         PRIMARY KEY,			
    caseID            INTEGER         NOT NULL							
                                      REFERENCES BaseInfo ( id ),		
    medicationID      INTEGER         NOT NULL,							
    medication        VARCHAR(100)    NOT NULL,
    score             INTEGER 		  NOT NULL,									
    isRight           BOOLEAN         NOT NULL
);
CREATE TABLE MedicationUsage (
    id                INTEGER         PRIMARY_KEY,
    medicationID      INTEGER         NOT NULL
                                      REFERENCES Medication ( id ),
    usage             VARCHAR(100)    NOT NULL,
    isRight           BOOLEAN         NOT NULL
);
CREATE TABLE MedicationDose (
    id                INTEGER         PRIMARY_KEY,
    medicationID      INTEGER         NOT NULL
                                      REFERENCES Medication ( id ),
    dose              VARCHAR(100)    NOT NULL,
    isRight           BOOLEAN         NOT NULL
);

