CREATE DATABASE bdTeste
GO
USE bdTeste

CREATE TABLE clinica(
id                              INT		          NOT NULL,
nome                            VARCHAR(255)      NOT NULL,
logradouro                      VARCHAR(255)      NOT NULL,
numero                          VARCHAR(20)       NOT NULL,
cep                             CHAR(8)           NOT NULL,
complemento                     VARCHAR(100)      NULL,
telefone                        CHAR(11)          NOT NULL,
email                           VARCHAR(255)      NOT NULL
PRIMARY KEY (id)
)
GO
CREATE TABLE paciente(
cpf                             CHAR(11)          NOT NULL,
nome                            VARCHAR(255)      NOT NULL,
logradouro                      VARCHAR(255)      NOT NULL,
numero                          VARCHAR(30)       NOT NULL,
cep                             CHAR(8)           NOT NULL,
complemento                     VARCHAR(100)      NULL,
telefone                        CHAR(11)          NOT NULL,
tipoSanguineo                   VARCHAR(3)        NOT NULL,
email                           VARCHAR(255)      NOT NULL
PRIMARY KEY (cpf)
)
GO
CREATE TABLE especialidade(
id                              INT               NOT NULL,
especialidade                   VARCHAR(100)      NOT NULL
PRIMARY KEY (id)
)
GO
CREATE TABLE medico(
crm                             CHAR(6)           NOT NULL,
nome                            VARCHAR(255)      NOT NULL,
logradouro                      VARCHAR(255)      NOT NULL,
numero                          VARCHAR(30)		  NOT NULL,
cep                             CHAR(8)           NULL,
complemento                     VARCHAR(100)      NULL,
telefone                        CHAR(11)          NOT NULL,
email                           VARCHAR(255)      NOT NULL,
especialidadeID                 INT               NOT NULL
PRIMARY KEY (crm)
FOREIGN KEY (especialidadeID) REFERENCES especialidade(id)
)
GO
CREATE TABLE consulta(
id                              INT               NOT NULL,
pacienteCPF                     CHAR(11)          NOT NULL,
medicoCRM                       CHAR(6)           NOT NULL,
clinicaID						INT               NOT NULL,
dataConsulta					DATE			  NOT NULL,
horaConsulta					TIME			  NOT NULL,
observacao                      VARCHAR(255)      NOT NULL
PRIMARY KEY (id)
FOREIGN KEY (pacienteCPF) REFERENCES paciente(cpf),
FOREIGN KEY (medicoCRM) REFERENCES medico(crm),
FOREIGN KEY (clinicaID) REFERENCES clinica(id)
)
GO
CREATE TABLE receituario(
id                              VARCHAR(30)       NOT NULL,
consultaID                      INT               NOT NULL,
prescricao                      VARCHAR(255)      NOT NULL
PRIMARY KEY (id)
FOREIGN KEY (consultaID) REFERENCES consulta(id)
)

INSERT INTO especialidade VALUES
(10001, 'Pediatra'),
(10002, 'Ginecologista'),
(10003, 'Fisioterapeuta'),
(10004, 'Otorrinolaringolista'),
(10005, 'Neurologista'),
(10006, 'Oftalmologista')

INSERT INTO paciente VALUES
('12345678987', 'Carla Santos', 'Rua D', '750', '04561000', NULL, '11999994444', 'A+', 'csantos@bd.com'),
('18337658982', 'Carlos Rodrigues', 'Rua A', '87', '04231000', 'Apto 87I', '11999995555', 'AB+', 'crodrigues@bd.com'),
('17845736471', 'Osvaldo Dias', 'Rua P', '239', '04279000', NULL, '11999996666', 'O+', 'odias@bd.com'),
('11928743576', 'Andre Souza', 'Rua B', '097', '08631000', 'Casa 2', '11999997777', 'B-', 'asouza@bd.com')

INSERT INTO medico VALUES
('123456', 'Maria Silva', 'Rua F', '824', '06541000', NULL, '11999998888', 'msilva@bd.com', 10001),
('789123', 'Bernardo Otawa', 'Rua L', '875', '06382000', 'Apto 8', '11999999999', 'botawa@bd.com', 10002),
('456789', 'Lucas Oliveira', 'Rua E', '69', '09356000', NULL, '11999991111', 'loliveira@bd.com', 10003),
('192837', 'Bruno Fraga', 'Rua T', '837', '07398000', 'Casa 9', '11999992222', 'bfraga@bd.com', 10004),
('465782', 'Renato Caxias', 'Rua C', '123', '06381000', NULL, '11999993333', 'rcaxias@bd.com', 10005),
('192347', 'Ronaldo Gaucho', 'Rua G', '389', '02847000', NULL, '11999991234', 'rgaucho@bd.com', 10006)

INSERT INTO clinica VALUES
(1001, 'HealthWork', 'Rua H', 167, '02363000', 'Travessa da Rua M', '1199805372', 'clinicahealthwork@bd.com')

INSERT INTO consulta VALUES
(101, '12345678987', '123456', 1001, '29/08/2022', '13:30', 'O paciente estava com dor de cabeça, receitei um paracetamol'),
(102, '17845736471', '192347', 1001, '15/09/2022', '10:30', 'O paciente esta com miopia')

INSERT INTO receituario VALUES
('1', 101, 'Paracetamol'),
('2', 102, 'Recomendo fazer um óculos')

ALTER TABLE consulta
ALTER COLUMN horaConsulta VARCHAR(30) NOT NULL

ALTER TABLE consulta
ALTER COLUMN dataConsulta VARCHAR(30) NOT NULL


/*Daqui para baixo foi só para auxiliar no código DAO*/

SELECT *
FROM clinica

UPDATE clinica SET nome= 'Colevas', logradouro='Rua C', numero= '168', cep= '05431000', complemento='', telefone='11940028922', email='col@bd.com'
WHERE id= 1001

SELECT m.crm AS CRM, m.nome, m.logradouro, m.numero, m.cep, m.complemento, m.telefone, m.email, e.id AS ID_Especialidade, e.especialidade AS Especialidade
FROM medico m, especialidade e
WHERE especialidadeID = e.id


SELECT co.id, co.dataConsulta, co.horaConsulta, co.observacao, p.cpf AS Paciente, m.crm AS Medico, cl.id AS Clinica
FROM consulta co, paciente p, medico m, clinica cl
WHERE co.id = 101
AND p.cpf = co.pacienteCPF
AND m.crm = co.medicoCRM
AND cl.id = co.clinicaID

SELECT r.id, c.id AS Consulta, r.prescricao AS Prescricao
FROM receituario r, consulta c
WHERE r.consultaID = c.id