CREATE TABLE TABLE_RECEPTION (
    NUM_RECEPTION    INTEGER NOT NULL,
    NUM_SERVICE      INTEGER,
    NUM_MEDIC        INTEGER,
    DATA_RECEPTION   DATE,
    SURNAME_PATIENT  VARCHAR(30),
    FNAME_PATIENT    VARCHAR(30),
    PAYMENT          FLOAT,
    PAID             VARCHAR(5)
);

CREATE TABLE TABLE_MEDIC (
    NUM_MEDIC     INTEGER NOT NULL,
    SURNAME       VARCHAR(30),
    FNAME         VARCHAR(30),
    PATRONIMIC    VARCHAR(30),
    NUM_KAB       INTEGER,
    JOB_PHONE     VARCHAR(12),
    NAME_SERVICE  VARCHAR(30)
);

CREATE TABLE TABLE_PATIENT (
    "KEY"            INTEGER NOT NULL,
    SURNAME          VARCHAR(30),
    FNAME            VARCHAR(30),
    PATRANIMIC       VARCHAR(30),
    NAME_SERVICE     VARCHAR(30),
    GENDER           VARCHAR(5),
    DOB              DATE,
    CITY             VARCHAR(10),
    STREET           VARCHAR(10),
    NUM_MED_POLICY   INTEGER,
    SER_NUM_PASPORT  INTEGER,
    ISSUED_BY        VARCHAR(20),
    YAMMER           VARCHAR(50)
);

CREATE TABLE TABLE_SERVICE (
    NUM_SERVICE   INTEGER NOT NULL,
    NAME_SERVICE  VARCHAR(20),
    PRICE         FLOAT
);

CREATE TABLE TABLE_USERS (
    KEY_USER    INTEGER NOT NULL,
    LOGIN       VARCHAR(10),
    "PASSWORD"  VARCHAR(30),
    STATUS      VARCHAR(5)
);

ALTER TABLE TABLE_MEDIC ADD CONSTRAINT PK_TABLE_MEDIC PRIMARY KEY (NUM_MEDIC)
USING INDEX PK_NUM_MEDIC;

ALTER TABLE TABLE_SERVICE ADD CONSTRAINT PK_TABLE_SERVICE PRIMARY KEY (NUM_SERVICE)
USING INDEX PK_NUM_SERVICE;

ALTER TABLE TABLE_PATIENT ADD CONSTRAINT PK_TABLE_PATIENT PRIMARY KEY (COD_PATIENT)
USING INDEX PK_COD_PATIENT;

ALTER TABLE TABLE_RECEPTION ADD CONSTRAINT PK_TABLE_RECEPTION PRIMARY KEY (NUM_RECEPTION)
USING INDEX PK_NUM_REC;

ALTER TABLE TABLE_RECEPTION ADD CONSTRAINT FK_TABLE_RECEPTION_1 FOREIGN KEY (NUM_SERVICE) REFERENCES TABLE_SERVICE (NUM_SERVICE) ON DELETE CASCADE ON UPDATE CASCADE
  USING INDEX FK_NUM_SERVICE;
ALTER TABLE TABLE_RECEPTION ADD CONSTRAINT FK_TABLE_RECEPTION_2 FOREIGN KEY (NUM_MEDIC) REFERENCES TABLE_MEDIC (NUM_MEDIC) ON DELETE CASCADE ON UPDATE CASCADE
  USING INDEX FK_NUM_MEDIC;
ALTER TABLE TABLE_RECEPTION ADD CONSTRAINT FK_TABLE_RECEPTION_3 FOREIGN KEY (COD_PATIENT) REFERENCES TABLE_PATIENT (COD_PATIENT) ON DELETE CASCADE ON UPDATE CASCADE;