Untitled

-- Define the address table
CREATE TABLE address (
    is_default NUMBER(1) DEFAULT 0 NOT NULL,
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    zip_code VARCHAR2(8 CHAR) NOT NULL,
    id RAW(16) NOT NULL,
    city VARCHAR2(255 CHAR) NOT NULL,
    complement VARCHAR2(255 CHAR),
    country VARCHAR2(255 CHAR) CHECK (country IN (
        'AFGHANISTAN', 'ALBANIA', 'ALGERIA', 'AMERICAN_SAMOA', 'ANDORRA', 'ANGOLA', 'ANTIGUA_AND_BARBUDA', 'ARGENTINA', 'ARMENIA', 'AUSTRALIA', 'AUSTRIA', 'AZERBAIJAN', 'BAHAMAS', 'BAHRAIN', 'BANGLADESH', 'BARBADOS', 'BELARUS', 'BELGIUM', 'BELIZE', 'BENIN', 'BHUTAN', 'BOLIVIA', 'BOSNIA_AND_HERZEGOVINA', 'BOTSWANA', 'BRAZIL', 'BRUNEI', 'BULGARIA', 'BURKINA_FASO', 'BURMA_MYANMAR', 'BURUNDI', 'CAMBODIA', 'CAMEROON', 'CANADA', 'CAPE_VERDE', 'CENTRAL_AFRICAN_REPUBLIC', 'CHAD', 'CHILE', 'CHINA', 'COLOMBIA', 'COMOROS', 'CONGO', 'COSTA_RICA', 'CROATIA', 'CUBA', 'CYPRUS', 'CZECH_REPUBLIC', 'DENMARK', 'DJIBOUTI', 'DOMINICA', 'DOMINICAN_REPUBLIC', 'EAST_TIMOR', 'ECUADOR', 'EGYPT', 'EL_SALVADOR', 'EQUATORIAL_GUINEA', 'ERITREA', 'ESTONIA', 'ETHIOPIA', 'FIJI', 'FINLAND', 'FRANCE', 'GABON', 'GAMBIA', 'GEORGIA', 'GERMANY', 'GHANA', 'GREECE', 'GRENADA', 'GUAM', 'GUATEMALA', 'GUINEA', 'GUINEA_BISSAU', 'GUYANA', 'HAITI', 'HONDURAS', 'HUNGARY', 'ICELAND', 'INDIA', 'INDONESIA', 'IRAN', 'IRAQ', 'IRELAND', 'ISRAEL', 'ITALY', 'IVORY_COAST', 'JAMAICA', 'JAPAN', 'JORDAN', 'KAZAKHSTAN', 'KENYA', 'KIRIBATI', 'NORTH_KOREA', 'SOUTH_KOREA', 'KUWAIT', 'KYRGYZSTAN', 'LAOS', 'LATVIA', 'LEBANON', 'LESOTHO', 'LIBERIA', 'LIBYA', 'LIECHTENSTEIN', 'LITHUANIA', 'LUXEMBOURG', 'MADAGASCAR', 'MALAWI', 'MALAYSIA', 'MALDIVES', 'MALI', 'MALTA', 'MARSHALL_ISLANDS', 'MAURITANIA', 'MAURITIUS', 'MEXICO', 'MICRONESIA', 'MOLDOVA', 'MONACO', 'MONGOLIA', 'MONTENEGRO', 'MOROCCO', 'MOZAMBIQUE', 'NAMIBIA', 'NAURU', 'NEPAL', 'NEW_ZEALAND', 'NICARAGUA', 'NIGER', 'NIGERIA', 'NORWAY', 'OMAN', 'PAKISTAN', 'PALAU', 'PANAMA', 'PAPUA_NEW_GUINEA', 'PARAGUAY', 'PERU', 'PHILIPPINES', 'POLAND', 'PORTUGAL', 'QATAR', 'ROMANIA', 'RUSSIA', 'RWANDA', 'SAINT_LUCIA', 'SAMOA', 'SAN_MARINO', 'SAO_TOME_AND_PRINCIPE', 'SAUDI_ARABIA', 'SENEGAL', 'SERBIA', 'SEYCHELLES', 'SIERRA_LEONE', 'SINGAPORE', 'SLOVAKIA', 'SLOVENIA', 'SOLOMON_ISLANDS', 'SOMALIA', 'SOUTH_AFRICA', 'SOUTH_SUDAN', 'SPAIN', 'SRI_LANKA', 'SUDAN', 'SURINAME', 'SWAZILAND', 'SWEDEN', 'SWITZERLAND', 'SYRIA', 'TAIWAN', 'TAJIKISTAN', 'TANZANIA', 'THAILAND', 'TOGO', 'TONGA', 'TRINIDAD_AND_TOBAGO', 'TUNISIA', 'TURKEY', 'TURKMENISTAN', 'TUVALU', 'UGANDA', 'UKRAINE', 'UNITED_ARAB_EMIRATES', 'UNITED_KINGDOM', 'UNITED_STATES', 'URUGUAY', 'UZBEKISTAN', 'VANUATU', 'VATICAN_CITY', 'VENEZUELA', 'VIETNAM', 'YEMEN', 'ZAMBIA', 'ZIMBABWE')),
    neighborhood VARCHAR2(255 CHAR) NOT NULL,
    number VARCHAR2(255 CHAR),
    place VARCHAR2(255 CHAR) NOT NULL,
    state VARCHAR2(255 CHAR) CHECK (state IN ('AC', 'AL', 'AP', 'AM', 'BH', 'CE', 'DF', 'ES', 'GO', 'MA', 'MT', 'MS', 'MG', 'PA', 'PB', 'PR', 'PE', 'PI', 'RJ', 'RN', 'RS', 'RO', 'RR', 'SC', 'SP', 'SE', 'TO')),
    PRIMARY KEY (id)
);

-- Define the appointment table
CREATE TABLE appointment (
    date DATE NOT NULL,
    patient_attended NUMBER(1) NOT NULL,
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    doctor_id RAW(16) NOT NULL,
    history_id RAW(16),
    id RAW(16) NOT NULL,
    patient_id RAW(16) NOT NULL,
    PRIMARY KEY (id)
);

-- Define the doctor table
CREATE TABLE doctor (
    birthdate DATE NOT NULL,
    is_active NUMBER(1),
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    id RAW(16) NOT NULL,
    gender VARCHAR2(255 CHAR) NOT NULL CHECK (gender IN ('MALE', 'FEMALE')),
    name VARCHAR2(255 CHAR) NOT NULL,
    nationality VARCHAR2(255 CHAR) DEFAULT 'BRAZILIAN' NOT NULL CHECK (nationality IN (
        'AFGHAN', 'ALBANIAN', 'ALGERIAN', 'AMERICAN', 'ANDORRAN', 'ANGOLAN', 'ANTIGUAN', 'ARGENTINE', 'ARMENIAN', 'AUSTRALIAN', 'AUSTRIAN', 'AZERBAIJANI', 'BAHAMIAN', 'BAHRAINI', 'BANGLADESHI', 'BARBADIAN', 'BELARUSIAN', 'BELGIAN', 'BELIZEAN', 'BENINESE', 'BHUTANESE', 'BOLIVIAN', 'BOSNIAN', 'BOTSWANAN', 'BRAZILIAN', 'BRUNEIAN', 'BULGARIAN', 'BURKINABE', 'BURMESE', 'BURUNDIAN', 'CAMBODIAN', 'CAMEROONIAN', 'CANADIAN', 'CAPE_VERDEAN', 'CENTRAL_AFRICAN', 'CHADIAN', 'CHILEAN', 'CHINESE', 'COLOMBIAN', 'COMORAN', 'CONGOLESE', 'COSTA_RICAN', 'CROATIAN', 'CUBAN', 'CYPRIOT', 'CZECH', 'DANISH', 'DJIBOUTIAN', 'DOMINICAN', 'DUTCH', 'EAST_TIMORESE', 'ECUADORIAN', 'EGYPTIAN', 'EMIRIAN', 'EQUATORIAL_GUINEAN', 'ERITREAN', 'ESTONIAN', 'ETHIOPIAN', 'FIJIAN', 'FILIPINO', 'FINNISH', 'FRENCH', 'GABONESE', 'GAMBIAN', 'GEORGIAN', 'GERMAN', 'GHANAIAN', 'GREEK', 'GRENADIAN', 'GUATEMALAN', 'GUINEAN', 'GUYANESE', 'HAITIAN', 'HERZEGOVINIAN', 'HONDURAN', 'HUNGARIAN', 'ICELANDIC', 'INDIAN', 'INDONESIAN', 'IRANIAN', 'IRAQI', 'IRISH', 'ISRAELI', 'ITALIAN', 'IVORIAN', 'JAMAICAN', 'JAPANESE', 'JORDANIAN', 'KAZAKHSTANI', 'KENYAN', 'KIRIBATIAN', 'NORTH_KOREAN', 'SOUTH_KOREAN', 'KUWAITI', 'KYRGYZSTANI', 'LAOTIAN', 'LATVIAN', 'LEBANESE', 'LESOTHO', 'LIBERIAN', 'LIBYAN', 'LIECHTENSTEINER', 'LITHUANIAN', 'LUXEMBOURGER', 'MADAGASCAN', 'MALAWIAN', 'MALAYSIAN', 'MALDIVIAN', 'MALIAN', 'MALTESE', 'MARSHALLESE', 'MAURITANIAN', 'MAURITIAN', 'MEXICAN', 'MICRONESIAN', 'MOLDAVIAN', 'MONACAN', 'MONGOLIAN', 'MONTENEGRIN', 'MOROCCAN', 'MOZAMBICAN', 'NAMIBIAN', 'NAURUAN', 'NEPALESE', 'NEW_ZEALANDER', 'NICARAGUAN', 'NIGERIEN', 'NIGERIAN', 'NORWEGIAN', 'OMANI', 'PAKISTANI', 'PALAUAN', 'PANAMANIAN', 'PAPUA_NEW_GUINEAN', 'PARAGUAYAN', 'PERUVIAN', 'PHILIPPINE', 'POLISH', 'PORTUGUESE', 'QATARI', 'ROMANIAN', 'RUSSIAN', 'RWANDAN', 'SAINT_LUCIAN', 'SALVADORAN', 'SAMOAN', 'SAN_MARINESE', 'SAUDI_ARABIAN', 'SENEGALESE', 'SERBIAN', 'SEYCHELLOIS', 'SIERRA_LEONEAN', 'SINGAPOREAN', 'SLOVAK', 'SLOVENIAN', 'SOLOMON_ISLANDER', 'SOMALI', 'SOUTH_AFRICAN', 'SOUTH_SUDANESE', 'SPANISH', 'SRI_LANKAN', 'SUDANESE', 'SURINAMESE', 'SWAZI', 'SWEDISH', 'SWISS', 'SYRIAN', 'TAIWANESE', 'TAJIK', 'TANZANIAN', 'THAI', 'TOGOLESE', 'TONGAN', 'TRINIDADIAN_OR_TOBAGONIAN', 'TUNISIAN', 'TURKISH', 'TURKMEN', 'TUVALUAN', 'UGANDAN', 'UKRAINIAN', 'URUGUAYAN', 'UZBEKISTANI', 'VANUATUAN', 'VENEZUELAN', 'VIETNAMESE', 'YEMENI', 'ZAMBIAN', 'ZIMBABWEAN')),
    place_of_birth VARCHAR2(255 CHAR) CHECK (place_of_birth IN ('AC', 'AL', 'AP', 'AM', 'BH', 'CE', 'DF', 'ES', 'GO', 'MA', 'MT', 'MS', 'MG', 'PA', 'PB', 'PR', 'PE', 'PI', 'RJ', 'RN', 'RS', 'RO', 'RR', 'SC', 'SP', 'SE', 'TO')),
    PRIMARY KEY (id)
);

-- Define the document table
CREATE TABLE document (
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    doctor_id RAW(16),
    id RAW(16) NOT NULL,
    number VARCHAR2(255 CHAR) NOT NULL,
    type VARCHAR2(255 CHAR) NOT NULL CHECK (type IN ('CPF', 'RG', 'CRM')),
    uf VARCHAR2(255 CHAR) CHECK (uf IN ('AC', 'AL', 'AP', 'AM', 'BH', 'CE', 'DF', 'ES', 'GO', 'MA', 'MT', 'MS', 'MG', 'PA', 'PB', 'PR', 'PE', 'PI', 'RJ', 'RN', 'RS', 'RO', 'RR', 'SC', 'SP', 'SE', 'TO')),
    PRIMARY KEY (id)
);

-- Define the email table
CREATE TABLE email (
    is_default NUMBER(1) DEFAULT 0 NOT NULL,
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    id RAW(16) NOT NULL,
    address VARCHAR2(255 CHAR) NOT NULL,
    name VARCHAR2(255 CHAR),
    PRIMARY KEY (id)
);

-- Define the history table
CREATE TABLE history (
    date_time DATE NOT NULL,
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    appointment_id RAW(16),
    id RAW(16) NOT NULL,
    observation VARCHAR2(255 CHAR),
    PRIMARY KEY (id)
);

-- Define the patient table
CREATE TABLE patient (
    birthdate DATE NOT NULL,
    is_active NUMBER(1) NOT NULL,
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    id RAW(16) NOT NULL,
    gender VARCHAR2(255 CHAR) NOT NULL CHECK (gender IN ('MALE', 'FEMALE')),
    name VARCHAR2(255 CHAR) NOT NULL,
    nationality VARCHAR2(255 CHAR) DEFAULT 'BRAZILIAN' NOT NULL CHECK (nationality IN ('AFGHAN', 'ALBANIAN', 'ALGERIAN', 'AMERICAN', 'ANDORRAN', 'ANGOLAN', 'ANTIGUAN', 'ARGENTINE', 'ARMENIAN', 'AUSTRALIAN', 'AUSTRIAN', 'AZERBAIJANI', 'BAHAMIAN', 'BAHRAINI', 'BANGLADESHI', 'BARBADIAN', 'BELARUSIAN', 'BELGIAN', 'BELIZEAN', 'BENINESE', 'BHUTANESE', 'BOLIVIAN', 'BOSNIAN', 'BOTSWANAN', 'BRAZILIAN', 'BRUNEIAN', 'BULGARIAN', 'BURKINABE', 'BURMESE', 'BURUNDIAN', 'CAMBODIAN', 'CAMEROONIAN', 'CANADIAN', 'CAPE_VERDEAN', 'CENTRAL_AFRICAN', 'CHADIAN', 'CHILEAN', 'CHINESE', 'COLOMBIAN', 'COMORAN', 'CONGOLESE', 'COSTA_RICAN', 'CROATIAN', 'CUBAN', 'CYPRIOT', 'CZECH', 'DANISH', 'DJIBOUTIAN', 'DOMINICAN', 'DUTCH', 'EAST_TIMORESE', 'ECUADORIAN', 'EGYPTIAN', 'EMIRIAN', 'EQUATORIAL_GUINEAN', 'ERITREAN', 'ESTONIAN', 'ETHIOPIAN', 'FIJIAN', 'FILIPINO', 'FINNISH', 'FRENCH', 'GABONESE', 'GAMBIAN', 'GEORGIAN', 'GERMAN', 'GHANAIAN', 'GREEK', 'GRENADIAN', 'GUATEMALAN', 'GUINEAN', 'GUYANESE', 'HAITIAN', 'HERZEGOVINIAN', 'HONDURAN', 'HUNGARIAN', 'ICELANDIC', 'INDIAN', 'INDONESIAN', 'IRANIAN', 'IRAQI', 'IRISH', 'ISRAELI', 'ITALIAN', 'IVORIAN', 'JAMAICAN', 'JAPANESE', 'JORDANIAN', 'KAZAKHSTANI', 'KENYAN', 'KIRIBATIAN', 'NORTH_KOREAN', 'SOUTH_KOREAN', 'KUWAITI', 'KYRGYZSTANI', 'LAOTIAN', 'LATVIAN', 'LEBANESE', 'LESOTHO', 'LIBERIAN', 'LIBYAN', 'LIECHTENSTEINER', 'LITHUANIAN', 'LUXEMBOURGER', 'MADAGASCAN', 'MALAWIAN', 'MALAYSIAN', 'MALDIVIAN', 'MALIAN', 'MALTESE', 'MARSHALLESE', 'MAURITANIAN', 'MAURITIAN', 'MEXICAN', 'MICRONESIAN', 'MOLDAVIAN', 'MONACAN', 'MONGOLIAN', 'MONTENEGRIN', 'MOROCCAN', 'MOZAMBICAN', 'NAMIBIAN', 'NAURUAN', 'NEPALESE', 'NEW_ZEALANDER', 'NICARAGUAN', 'NIGERIEN', 'NIGERIAN', 'NORWEGIAN', 'OMANI', 'PAKISTANI', 'PALAUAN', 'PANAMANIAN', 'PAPUA_NEW_GUINEAN', 'PARAGUAYAN', 'PERUVIAN', 'PHILIPPINE', 'POLISH', 'PORTUGUESE', 'QATARI', 'ROMANIAN', 'RUSSIAN', 'RWANDAN', 'SAINT_LUCIAN', 'SALVADORAN', 'SAMOAN', 'SAN_MARINESE', 'SAUDI_ARABIAN', 'SENEGALESE', 'SERBIAN', 'SEYCHELLOIS', 'SIERRA_LEONEAN', 'SINGAPOREAN', 'SLOVAK', 'SLOVENIAN', 'SOLOMON_ISLANDER', 'SOMALI', 'SOUTH_AFRICAN', 'SOUTH_SUDANESE', 'SPANISH', 'SRI_LANKAN', 'SUDANESE', 'SURINAMESE', 'SWAZI', 'SWEDISH', 'SWISS', 'SYRIAN', 'TAIWANESE', 'TAJIK', 'TANZANIAN', 'THAI', 'TOGOLESE', 'TONGAN', 'TRINIDADIAN_OR_TOBAGONIAN', 'TUNISIAN', 'TURKISH', 'TURKMEN', 'TUVALUAN', 'UGANDAN', 'UKRAINIAN', 'URUGUAYAN', 'UZBEKISTANI', 'VANUATUAN', 'VENEZUELAN', 'VIETNAMESE', 'YEMENI', 'ZAMBIAN', 'ZIMBABWEAN')),
    place_of_birth VARCHAR2(255 CHAR) CHECK (place_of_birth IN ('AC', 'AL', 'AP', 'AM', 'BH', 'CE', 'DF', 'ES', 'GO', 'MA', 'MT', 'MS', 'MG', 'PA', 'PB', 'PR', 'PE', 'PI', 'RJ', 'RN', 'RS', 'RO', 'RR', 'SC', 'SP', 'SE', 'TO')),
    PRIMARY KEY (id)
);

-- Define the patient_addresses table
CREATE TABLE patient_addresses (
    addresses_id RAW(16) NOT NULL,
    patient_id RAW(16) NOT NULL
);

-- Define the patient_documents table
CREATE TABLE patient_documents (
    documents_id RAW(16) NOT NULL,
    patient_id RAW(16) NOT NULL
);

-- Define the patient_emails table
CREATE TABLE patient_emails (
    emails_id RAW(16) NOT NULL,
    patient_id RAW(16) NOT NULL
);

-- Define the patient_phones table
CREATE TABLE patient_phones (
    patient_id RAW(16) NOT NULL,
    phones_id RAW(16) NOT NULL
);

-- Define the phone table
CREATE TABLE phone (
    ddd VARCHAR2(2 CHAR) NOT NULL,
    ddi VARCHAR2(2 CHAR) NOT NULL,
    is_default NUMBER(1) NOT NULL,
    date_created TIMESTAMP,
    date_updated TIMESTAMP,
    number VARCHAR2(9 CHAR) NOT NULL,
    id RAW(16) NOT NULL,
    name VARCHAR2(255 CHAR),
    PRIMARY KEY (id)
);

-- Add foreign key constraints
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_doctor FOREIGN KEY (doctor_id) REFERENCES doctor (id);
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_patient FOREIGN KEY (patient_id) REFERENCES patient (id);
ALTER TABLE document ADD CONSTRAINT fk_document_doctor FOREIGN KEY (doctor_id) REFERENCES doctor (id);
ALTER TABLE email ADD CONSTRAINT fk_email_patient FOREIGN KEY (patient_id) REFERENCES patient (id);
ALTER TABLE history ADD CONSTRAINT fk_history_appointment FOREIGN KEY (appointment_id) REFERENCES appointment (id);
ALTER TABLE history ADD CONSTRAINT fk_history_doctor FOREIGN KEY (doctor_id) REFERENCES doctor (id);
ALTER TABLE patient_addresses ADD CONSTRAINT fk_patient_addresses_addresses FOREIGN KEY (addresses_id) REFERENCES address (id);
ALTER TABLE patient_addresses ADD CONSTRAINT fk_patient_addresses_patient FOREIGN KEY (patient_id) REFERENCES patient (id);
ALTER TABLE patient_documents ADD CONSTRAINT fk_patient_documents_documents FOREIGN KEY (documents_id) REFERENCES document (id);
ALTER TABLE patient_documents ADD CONSTRAINT fk_patient_documents_patient FOREIGN KEY (patient_id) REFERENCES patient (id);
ALTER TABLE patient_emails ADD CONSTRAINT fk_patient_emails_emails FOREIGN KEY (emails_id) REFERENCES email (id);
ALTER TABLE patient_emails ADD CONSTRAINT fk_patient_emails_patient FOREIGN KEY (patient_id) REFERENCES patient (id);
ALTER TABLE patient_phones ADD CONSTRAINT fk_patient_phones_patient FOREIGN KEY (patient_id) REFERENCES patient (id);
ALTER TABLE patient_phones ADD CONSTRAINT fk_patient_phones_phones FOREIGN KEY (phones_id) REFERENCES phone (id);