/* modelo_fisico_integrado_sprint02: */

CREATE DATABASE omnimed CHARACTER SET utf8 COLLATE utf8_bin;

USE omnimed;

CREATE TABLE RESPONSAVEIS (
    RES_ID INT AUTO_INCREMENT PRIMARY KEY,
    RES_TEL_CELULAR1 VARCHAR(20),
    FK_PACIENTES_PAC_ID INT
);

CREATE TABLE USUARIO_COMUM (
    USC_ID INT AUTO_INCREMENT PRIMARY KEY,
    USC_NOME VARCHAR(100) NOT NULL,
    USC_SEXO INT NOT NULL,
    USC_CIDADE VARCHAR(100) NOT NULL,
    USC_BAIRRO VARCHAR(100) NOT NULL,
    USC_DATA_NASCIMENTO DATE NOT NULL,
    USC_ESTADO INT NOT NULL,
    USC_SENHA VARCHAR(70) NOT NULL,
    USC_ORGAO_EMISSOR VARCHAR(10) NOT NULL,
    USC_CPF VARCHAR(20) NOT NULL,
    USC_RG VARCHAR(15) NOT NULL,
    USC_LOGRADOURO VARCHAR(200) NOT NULL,
    USC_NUMERO VARCHAR(10) NOT NULL,
    USC_HASH_REC VARCHAR(70),
    USC_UPDATE_AT DATETIME
);

CREATE TABLE FINALIDADE_REMEDIOS (
    FIN_ID INT AUTO_INCREMENT PRIMARY KEY,
    FIN_DESCRICAO VARCHAR(100) NOT NULL
);

CREATE TABLE PAGAMENTOS_MEDICOS (
    PGM_ID INT AUTO_INCREMENT PRIMARY KEY,
    PGM_DATA_PAGAMENTO DATE NOT NULL,
    PGM_SALARIO DOUBLE(8,2) NOT NULL,
    PGM_VALOR_COMISSAO DOUBLE(8,2) NOT NULL,
    PGM_VALOR_TOTAL DOUBLE(8,2) NOT NULL,
    PGM_DATA_PAGAMENTO_REALIZADO DATE,
    PGM_CONSULTAS_MES INT NOT NULL,
    FK_MEDICOS_MED_ID INT
);

CREATE TABLE FUNCIONARIOS (
    FUN_ID INT AUTO_INCREMENT PRIMARY KEY,
    FUN_PRONTUARIO VARCHAR(8) NOT NULL,
    FUN_CATEGORIA VARCHAR(20) NOT NULL,
    FK_USUARIO_COMUM_USC_ID INT
);

CREATE TABLE CONTABILIDADE (
    CON_ID INT AUTO_INCREMENT PRIMARY KEY,
    FK_FUNCIONARIOS_FUN_ID INT
);

CREATE TABLE PACIENTES (
    PAC_ID INT AUTO_INCREMENT PRIMARY KEY,
    PAC_EMAIL VARCHAR(200) NOT NULL,
    PAC_STATUS BOOLEAN NOT NULL,
    FK_PLANOS_PLN_ID INT,
    FK_USUARIO_COMUM_USC_ID INT
);

CREATE TABLE EXAMES (
    EXM_ID INT AUTO_INCREMENT PRIMARY KEY,
    EXM_NOME VARCHAR(100) NOT NULL,
    EXM_ANEXO_GUIA VARCHAR(255) NOT NULL,
    EXM_OBS_SECRETARIO VARCHAR(200),
    EXM_AGENDAMENTO VARCHAR(50) NOT NULL,
    EXM_AUTORIZADO BOOLEAN,
    EXM_ANEXO_RESULTADO VARCHAR(255),
    EXM_OBS_PACIENTE VARCHAR(200),
    FK_PACIENTES_PAC_ID INT,
    FK_MEDICOS_MED_ID INT
);

CREATE TABLE DEPENDENTES (
    DEP_ID INT AUTO_INCREMENT PRIMARY KEY,
    DEP_NV_PARENTESCO INT NOT NULL,
    FK_RESPONSAVEIS_RES_ID INT,
    FK_PACIENTES_PAC_ID INT
);

CREATE TABLE TRIAGENS (
    TRG_ID INT AUTO_INCREMENT PRIMARY KEY,
    TRG_SINTOMAS VARCHAR(255) NOT NULL,
    TRG_TABELA_DOR INT NOT NULL,
    TRG_PRESSAO_SISTOLICA INT,
    TRG_TEMPERATURA DOUBLE,
    TRG_PRESSAO_DIASTOLICA INT,
    TRG_QTD_FILHOS INT NOT NULL,
    TRG_QUAIS_TRAT_MED VARCHAR(200),
    TRG_TEM_ALERGIA_MEDICA BOOLEAN NOT NULL,
    TRG_QUAL_MEDICACAO VARCHAR(200),
    TRG_QUAIS_ALERGIAS VARCHAR(200),
    TRG_UTILIZA_MEDICACAO BOOLEAN NOT NULL,
    TRG_TEM_FILHOS BOOLEAN NOT NULL,
    TRG_QUAIS_DOENCAS_CRONICAS VARCHAR(200),
    TRG_TRATAMENTO_MEDICO BOOLEAN NOT NULL,
    TRG_DOENCAS_CRONICAS BOOLEAN NOT NULL,
    FK_AGENDAMENTOS_AGD_ID INT
);

CREATE TABLE CLINICAS_MEDICAS (
    CLI_ID INT AUTO_INCREMENT PRIMARY KEY,
    CLI_RAZAO_SOCIAL VARCHAR(200) NOT NULL,
    CLI_CONTATO1 VARCHAR(20) NOT NULL,
    CLI_CONTATO2 VARCHAR(20),
    CLI_DATA_FUNDACAO DATE NOT NULL,
    CLI_CNPJ VARCHAR(30) NOT NULL,
    FK_ADMINISTRATIVO_ADM_ID INT
);

CREATE TABLE MEDICOS (
    MED_ID INT AUTO_INCREMENT PRIMARY KEY,
    FK_FUNCIONARIOS_FUN_ID INT
);

CREATE TABLE AGENDAMENTOS (
    AGD_ID INT AUTO_INCREMENT PRIMARY KEY,
    AGD_STATUS INT NOT NULL,
    AGD_ENCAMINHAMENTOS BOOLEAN,
    AGD_DATA DATE NOT NULL,
    AGD_HORARIO TIME,
    AGD_MEET_LINK VARCHAR(255),
    AGD_ENCAMINHAMENTO_PRESENCIAL BOOLEAN,
    AGD_RETORNO BOOLEAN,
    AGD_MOTIVO_ENCAMINHAMENTO VARCHAR(255),
    AGD_TRIAGEM_REALIZADA BOOLEAN,
    FK_MEDICOS_MED_ID INT,
    FK_ESPECIALIDADES_MEDICAS_ESM_ID INT,
    FK_PACIENTES_PAC_ID INT
);

CREATE TABLE ESPECIALIDADES_MEDICAS (
    ESM_ID INT AUTO_INCREMENT PRIMARY KEY,
    ESM_DESCRICAO VARCHAR(200) NOT NULL
);

CREATE TABLE BENEFICIOS (
    BNF_ID INT AUTO_INCREMENT PRIMARY KEY,
    BNF_DESCRICAO VARCHAR(100) NOT NULL
);

CREATE TABLE PAGAMENTOS (
    PGD_ID INT AUTO_INCREMENT PRIMARY KEY,
    PGD_VENCIMENTO DATE NOT NULL,
    PGD_DATA_PAGAMENTO DATE,
    PGD_VALOR_TOTAL DOUBLE(8,2) NOT NULL,
    PGD_QUANTIDADE_DEPENDENTES INT NOT NULL,
    PGD_VALOR_PLANO DOUBLE(8,2),
    FK_PACIENTES_PAC_ID INT
);

CREATE TABLE CONSULTAS_ONLINE (
    CNS_ID INT AUTO_INCREMENT PRIMARY KEY,
    CNS_DATA DATE NOT NULL,
    CNS_ANOTACOES VARCHAR(255),
    CNS_SINTOMAS_IDENTIFICADOS VARCHAR(255) NOT NULL,
    CNS_DIAGNOSTICO VARCHAR(255) NOT NULL,
    FK_AGENDAMENTOS_AGD_ID INT
);

CREATE TABLE ENFERMEIROS (
    ENF_ID INT AUTO_INCREMENT PRIMARY KEY,
    FK_FUNCIONARIOS_FUN_ID INT
);

CREATE TABLE REMEDIOS (
    RMD_ID INT AUTO_INCREMENT PRIMARY KEY,
    RMD_NOME VARCHAR(100) NOT NULL,
    RMD_VIA_DOSAGEM INT NOT NULL,
    RMD_INDICACAO TEXT,
    RMD_CONTRAINDICACAO TEXT,
    RMD_DOSAGEM INT(4) NOT NULL,
    RMD_QUANTIDADE INTEGER,
    RMD_DISPONIVEL BOOLEAN,
    FK_FORMAS_FARMACEUTICAS_FRM_ID INT
);

CREATE TABLE CONTATOS_SETORIAIS (
    CSE_ID INT AUTO_INCREMENT PRIMARY KEY,
    CSE_TELEFONE_CELULAR VARCHAR(20) NOT NULL,
    CSE_TELEFONE_FIXO VARCHAR(20),
    CSE_SETOR VARCHAR(200) NOT NULL,
    CSE_EMAIL VARCHAR(200) NOT NULL
);

CREATE TABLE ADMINISTRATIVO (
    ADM_ID INT AUTO_INCREMENT PRIMARY KEY,
    ADM_FUNCAO INT NOT NULL,
    ADM_DATA_ADMISSAO DATE NOT NULL,
    ADM_DATA_DEMISSAO DATE,
    ADM_CLINICA INT NOT NULL,
    ADM_SALARIO FLOAT NOT NULL,
    ADM_CRM VARCHAR(20),
    ADM_TELEFONE_CELULAR VARCHAR(20) NOT NULL,
    FK_FUNCIONARIOS_FUN_ID INT
);

CREATE TABLE VALORES_DEPENDENTES (
    VPD_ID INT AUTO_INCREMENT PRIMARY KEY,
    VPD_IDADE_MAXIMA INT NOT NULL,
    VPD_IDADE_MINIMA INT NOT NULL,
    VPD_VALOR DOUBLE(6,2) NOT NULL,
    FK_PLANOS_PLN_ID INT
);

CREATE TABLE PLANOS (
    PLN_ID INT AUTO_INCREMENT PRIMARY KEY,
    PLN_NOME VARCHAR(100) NOT NULL,
    PLN_INSTITUICAO VARCHAR(100) NOT NULL,
    PLN_PRECO DOUBLE(8,2) NOT NULL
);

CREATE TABLE PRESCRICOES (
    PRC_ID INT AUTO_INCREMENT PRIMARY KEY,
    PRC_DURACAO_TRATAMENTO VARCHAR(60) NOT NULL,
    PRC_OBSERVACOES VARCHAR(500),
    PRC_ASSINATURA_MEDICO BLOB NOT NULL,
    PRC_DATA_EMISSAO DATE NOT NULL,
    FK_MEDICOS_MED_ID INT,
    FK_CONSULTAS_ONLINE_CNS_ID INT
);

CREATE TABLE FORMAS_FARMACEUTICAS (
    FRM_ID INT AUTO_INCREMENT PRIMARY KEY,
    FRM_DESCRICAO VARCHAR(100) NOT NULL
);

CREATE TABLE PLANOS_BENEFICIOS (
    FK_BENEFICIOS_BNF_ID INT,
    FK_PLANOS_PLN_ID INT,
    PBN_ID INT AUTO_INCREMENT PRIMARY KEY,
    PBN_VALOR_BENEFICIO INT NOT NULL
);

CREATE TABLE USO_REMEDIO (
    URM_ID INT PRIMARY KEY,
    FK_FINALIDADE_REMEDIOS_FIN_ID INT,
    FK_REMEDIOS_RMD_ID INT
);

CREATE TABLE REMEDIOS_PRESCRICOES (
    FK_PRESCRICOES_PRC_ID INT,
    FK_REMEDIOS_RMD_ID INT,
    RMP_ID INT AUTO_INCREMENT PRIMARY KEY,
    RMP_ESQUEMA_POSOLOGICO VARCHAR(200) NOT NULL
);

CREATE TABLE ESPECIALISTAS (
    FK_ESPECIALIDADES_MEDICAS_ESM_ID INT,
    FK_MEDICOS_MED_ID INT,
    ESP_ID INT AUTO_INCREMENT PRIMARY KEY
);
 
ALTER TABLE RESPONSAVEIS ADD CONSTRAINT FK_RESPONSAVEIS_2
    FOREIGN KEY (FK_PACIENTES_PAC_ID)
    REFERENCES PACIENTES (PAC_ID)
    ON DELETE CASCADE;
 
ALTER TABLE PAGAMENTOS_MEDICOS ADD CONSTRAINT FK_PAGAMENTOS_MEDICOS_2
    FOREIGN KEY (FK_MEDICOS_MED_ID)
    REFERENCES MEDICOS (MED_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE FUNCIONARIOS ADD CONSTRAINT FK_FUNCIONARIOS_2
    FOREIGN KEY (FK_USUARIO_COMUM_USC_ID)
    REFERENCES USUARIO_COMUM (USC_ID)
    ON DELETE CASCADE;
 
ALTER TABLE CONTABILIDADE ADD CONSTRAINT FK_CONTABILIDADE_2
    FOREIGN KEY (FK_FUNCIONARIOS_FUN_ID)
    REFERENCES FUNCIONARIOS (FUN_ID)
    ON DELETE CASCADE;
 
ALTER TABLE PACIENTES ADD CONSTRAINT FK_PACIENTES_2
    FOREIGN KEY (FK_PLANOS_PLN_ID)
    REFERENCES PLANOS (PLN_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE PACIENTES ADD CONSTRAINT FK_PACIENTES_3
    FOREIGN KEY (FK_USUARIO_COMUM_USC_ID)
    REFERENCES USUARIO_COMUM (USC_ID)
    ON DELETE CASCADE;
 
ALTER TABLE EXAMES ADD CONSTRAINT FK_EXAMES_2
    FOREIGN KEY (FK_PACIENTES_PAC_ID)
    REFERENCES PACIENTES (PAC_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE EXAMES ADD CONSTRAINT FK_EXAMES_3
    FOREIGN KEY (FK_MEDICOS_MED_ID)
    REFERENCES MEDICOS (MED_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE DEPENDENTES ADD CONSTRAINT FK_DEPENDENTES_2
    FOREIGN KEY (FK_RESPONSAVEIS_RES_ID)
    REFERENCES RESPONSAVEIS (RES_ID)
    ON DELETE CASCADE;
 
ALTER TABLE DEPENDENTES ADD CONSTRAINT FK_DEPENDENTES_3
    FOREIGN KEY (FK_PACIENTES_PAC_ID)
    REFERENCES PACIENTES (PAC_ID)
    ON DELETE CASCADE;
 
ALTER TABLE TRIAGENS ADD CONSTRAINT FK_TRIAGENS_2
    FOREIGN KEY (FK_AGENDAMENTOS_AGD_ID)
    REFERENCES AGENDAMENTOS (AGD_ID)
    ON DELETE CASCADE;
 
ALTER TABLE CLINICAS_MEDICAS ADD CONSTRAINT FK_CLINICAS_MEDICAS_2
    FOREIGN KEY (FK_ADMINISTRATIVO_ADM_ID)
    REFERENCES ADMINISTRATIVO (ADM_ID)
    ON DELETE CASCADE;
 
ALTER TABLE MEDICOS ADD CONSTRAINT FK_MEDICOS_2
    FOREIGN KEY (FK_FUNCIONARIOS_FUN_ID)
    REFERENCES FUNCIONARIOS (FUN_ID)
    ON DELETE CASCADE;
 
ALTER TABLE AGENDAMENTOS ADD CONSTRAINT FK_AGENDAMENTOS_2
    FOREIGN KEY (FK_MEDICOS_MED_ID)
    REFERENCES MEDICOS (MED_ID)
    ON DELETE SET NULL;
 
ALTER TABLE AGENDAMENTOS ADD CONSTRAINT FK_AGENDAMENTOS_3
    FOREIGN KEY (FK_ESPECIALIDADES_MEDICAS_ESM_ID)
    REFERENCES ESPECIALIDADES_MEDICAS (ESM_ID)
    ON DELETE CASCADE;
 
ALTER TABLE AGENDAMENTOS ADD CONSTRAINT FK_AGENDAMENTOS_4
    FOREIGN KEY (FK_PACIENTES_PAC_ID)
    REFERENCES PACIENTES (PAC_ID)
    ON DELETE CASCADE;
 
ALTER TABLE PAGAMENTOS ADD CONSTRAINT FK_PAGAMENTOS_2
    FOREIGN KEY (FK_PACIENTES_PAC_ID)
    REFERENCES PACIENTES (PAC_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE CONSULTAS_ONLINE ADD CONSTRAINT FK_CONSULTAS_ONLINE_2
    FOREIGN KEY (FK_AGENDAMENTOS_AGD_ID)
    REFERENCES AGENDAMENTOS (AGD_ID)
    ON DELETE CASCADE;
 
ALTER TABLE ENFERMEIROS ADD CONSTRAINT FK_ENFERMEIROS_2
    FOREIGN KEY (FK_FUNCIONARIOS_FUN_ID)
    REFERENCES FUNCIONARIOS (FUN_ID)
    ON DELETE CASCADE;
 
ALTER TABLE REMEDIOS ADD CONSTRAINT FK_REMEDIOS_2
    FOREIGN KEY (FK_FORMAS_FARMACEUTICAS_FRM_ID)
    REFERENCES FORMAS_FARMACEUTICAS (FRM_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE ADMINISTRATIVO ADD CONSTRAINT FK_ADMINISTRATIVO_2
    FOREIGN KEY (FK_FUNCIONARIOS_FUN_ID)
    REFERENCES FUNCIONARIOS (FUN_ID)
    ON DELETE CASCADE;
 
ALTER TABLE VALORES_DEPENDENTES ADD CONSTRAINT FK_VALORES_DEPENDENTES_2
    FOREIGN KEY (FK_PLANOS_PLN_ID)
    REFERENCES PLANOS (PLN_ID)
    ON DELETE CASCADE;
 
ALTER TABLE PRESCRICOES ADD CONSTRAINT FK_PRESCRICOES_2
    FOREIGN KEY (FK_MEDICOS_MED_ID)
    REFERENCES MEDICOS (MED_ID)
    ON DELETE CASCADE;
 
ALTER TABLE PRESCRICOES ADD CONSTRAINT FK_PRESCRICOES_3
    FOREIGN KEY (FK_CONSULTAS_ONLINE_CNS_ID)
    REFERENCES CONSULTAS_ONLINE (CNS_ID)
    ON DELETE CASCADE;
 
ALTER TABLE PLANOS_BENEFICIOS ADD CONSTRAINT FK_PLANOS_BENEFICIOS_2
    FOREIGN KEY (FK_BENEFICIOS_BNF_ID)
    REFERENCES BENEFICIOS (BNF_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE PLANOS_BENEFICIOS ADD CONSTRAINT FK_PLANOS_BENEFICIOS_3
    FOREIGN KEY (FK_PLANOS_PLN_ID)
    REFERENCES PLANOS (PLN_ID)
    ON DELETE CASCADE;
 
ALTER TABLE USO_REMEDIO ADD CONSTRAINT FK_USO_REMEDIO_1
    FOREIGN KEY (FK_FINALIDADE_REMEDIOS_FIN_ID)
    REFERENCES FINALIDADE_REMEDIOS (FIN_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE USO_REMEDIO ADD CONSTRAINT FK_USO_REMEDIO_2
    FOREIGN KEY (FK_REMEDIOS_RMD_ID)
    REFERENCES REMEDIOS (RMD_ID)
    ON DELETE CASCADE;
 
ALTER TABLE REMEDIOS_PRESCRICOES ADD CONSTRAINT FK_REMEDIOS_PRESCRICOES_2
    FOREIGN KEY (FK_PRESCRICOES_PRC_ID)
    REFERENCES PRESCRICOES (PRC_ID)
    ON DELETE CASCADE;
 
ALTER TABLE REMEDIOS_PRESCRICOES ADD CONSTRAINT FK_REMEDIOS_PRESCRICOES_3
    FOREIGN KEY (FK_REMEDIOS_RMD_ID)
    REFERENCES REMEDIOS (RMD_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE ESPECIALISTAS ADD CONSTRAINT FK_ESPECIALISTAS_2
    FOREIGN KEY (FK_ESPECIALIDADES_MEDICAS_ESM_ID)
    REFERENCES ESPECIALIDADES_MEDICAS (ESM_ID)
    ON DELETE RESTRICT;
 
ALTER TABLE ESPECIALISTAS ADD CONSTRAINT FK_ESPECIALISTAS_3
    FOREIGN KEY (FK_MEDICOS_MED_ID)
    REFERENCES MEDICOS (MED_ID)
    ON DELETE CASCADE;