/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/

CREATE DOMAIN DMN_BLOBTXT AS
BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8;

CREATE DOMAIN DMN_BOOL AS
SMALLINT
DEFAULT 0
NOT NULL
CHECK (VALUE IN (0,1));

CREATE DOMAIN DMN_DATE AS
DATE
DEFAULT CURRENT_DATE
NOT NULL;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE TBL_CLINIC (-- табля с клиниками 
    ID_CLINIC    INTEGER NOT NULL,
    NAME_CLINIC  VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);

CREATE TABLE TBL_DOCTOR (-- табля с докторами
    ID_DOCTOR   INTEGER NOT NULL,
    FIO_DOCTOR  VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);

CREATE TABLE TBL_PATIENTS_GENERAL (-- пациенты с оформленной амб.картой
    ID_PATIENTS_GENERAL   INTEGER NOT NULL,
    FIO_PATIENTS_GENERAL  VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);

CREATE TABLE TBL_PATIENTS_TEMP (-- пациенты без оформленной амб.карты
    ID_PATIENTS_TEMP   INTEGER NOT NULL,
    FIO_PATIENTS_TEMP  VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);

CREATE TABLE TBL_PLANNER (-- табля расписания
    ID_PLANNER           INTEGER NOT NULL,
    FK_DOCTOR            INTEGER NOT NULL,
    FK_CLINIC            INTEGER NOT NULL,
    FK_ROOM              INTEGER NOT NULL,
    FK_PATIENTS_GENERAL  INTEGER NOT NULL,
    FK_PATIENTS_TEMP     INTEGER NOT NULL,
    DATE_PLANNER         DMN_DATE,
    NOTES_PLANNER        DMN_BLOBTXT
);

CREATE TABLE TBL_ROOM (--табля кабинетов
    ID_ROOM    INTEGER NOT NULL,
    NUMB_ROOM  INTEGER,
    NAME_ROOM  VARCHAR(20) CHARACTER SET UTF8 DEFAULT 'Unnamed Room' NOT NULL
);

CREATE TABLE TBL_TIME_SLICE (--табля интервалов
    ID_TIME_SLICE   INTEGER NOT NULL,
    FK_PLANNER      INTEGER NOT NULL,
    TIME_SLICE_300  DMN_BOOL,
    TIME_SLICE_310  DMN_BOOL,
    TIME_SLICE_320  DMN_BOOL,
    TIME_SLICE_340  DMN_BOOL,
    TIME_SLICE_350  DMN_BOOL,
    TIME_SLICE_360  DMN_BOOL,
    TIME_SLICE_370  DMN_BOOL,
    TIME_SLICE_380  DMN_BOOL    
);



/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE TBL_CLINIC ADD CONSTRAINT PK_TBL_CLINIC PRIMARY KEY (ID_CLINIC);
ALTER TABLE TBL_DOCTOR ADD CONSTRAINT PK_TBL_DOCTOR PRIMARY KEY (ID_DOCTOR);
ALTER TABLE TBL_PATIENTS_GENERAL ADD CONSTRAINT PK_TBL_PATIENTS_GENERAL PRIMARY KEY (ID_PATIENTS_GENERAL);
ALTER TABLE TBL_PATIENTS_TEMP ADD CONSTRAINT PK_TBL_PATIENTS_TEMP PRIMARY KEY (ID_PATIENTS_TEMP);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT PK_TBL_PLANNER PRIMARY KEY (ID_PLANNER);
ALTER TABLE TBL_ROOM ADD CONSTRAINT PK_TBL_ROOM PRIMARY KEY (ID_ROOM);
ALTER TABLE TBL_TIME_SLICE ADD CONSTRAINT PK_TBL_TIME_SLICE PRIMARY KEY (ID_TIME_SLICE);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_1 FOREIGN KEY (FK_DOCTOR) REFERENCES TBL_DOCTOR (ID_DOCTOR);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_2 FOREIGN KEY (FK_CLINIC) REFERENCES TBL_CLINIC (ID_CLINIC);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_3 FOREIGN KEY (FK_ROOM) REFERENCES TBL_ROOM (ID_ROOM);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_4 FOREIGN KEY (FK_PATIENTS_GENERAL) REFERENCES TBL_PATIENTS_GENERAL (ID_PATIENTS_GENERAL);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_5 FOREIGN KEY (FK_PATIENTS_TEMP) REFERENCES TBL_PATIENTS_TEMP (ID_PATIENTS_TEMP);
ALTER TABLE TBL_TIME_SLICE ADD CONSTRAINT FK_TBL_TIME_SLICE_1 FOREIGN KEY (FK_PLANNER) REFERENCES TBL_PLANNER (ID_PLANNER);
COMMIT WORK;