CREATE TABLE consultas (
    id_consulta INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(30),
    sobrenome VARCHAR(50),
    data DATE,
    horario TIME,
    cpf VARCHAR(11),
    valor_consulta DECIMAL(10,2)
);

CREATE TABLE Paciente (
    nome VARCHAR(30),
    sobrenome VARCHAR(50),
    data_nascimento DATE,
    cpf VARCHAR(11) PRIMARY KEY,
    id_consulta INT
);

CREATE TABLE Medico (
    crm INT PRIMARY KEY,
    nome VARCHAR(30),
    sobrenome VARCHAR(50),
    telefone VARCHAR(11),
    especialidade VARCHAR(15),
    id_consulta INT
);

CREATE TABLE Paciente_Telefone (
    cpf VARCHAR(11),
    telefone VARCHAR(11),
    PRIMARY KEY (cpf, telefone)
);

ALTER TABLE Paciente 
    ADD FOREIGN KEY (id_consulta) REFERENCES consultas(id_consulta);

ALTER TABLE Medico 
    ADD FOREIGN KEY (id_consulta) REFERENCES consultas(id_consulta);

ALTER TABLE Paciente_Telefone 
    ADD FOREIGN KEY (cpf) REFERENCES Paciente(cpf);

INSERT INTO consultas (nome, sobrenome, data, horario, cpf, valor_consulta)
VALUES 
('Ana', 'Silva', '2024-05-10', '14:30:00', '12345678901', 150.00),
('Carlos', 'Souza', '2024-05-11', '09:00:00', '98765432100', 200.00),
('Mariana', 'Oliveira', '2024-05-12', '16:15:00', '11122233344', 180.00),
('Antonella','Aniello','2025-03-12','10:30:00','00436722805', 200.00),
('Lorenzo','Aniello','2025-12-21','09:00:00','98745623167',100.00);


INSERT INTO Paciente (nome, sobrenome, data_nascimento, cpf, id_consulta)
VALUES
('Ana', 'Silva', '1990-03-15', '12345678901', 1),
('Carlos', 'Souza', '1985-07-22', '98765432100', 2),
('Mariana', 'Oliveira', '1992-11-05', '11122233344', 3),
('Antonella','Aniello','2025-03-12','00436722805',2),
('Lorenzo','Aniello','2025-12-21','98745623167',3);


INSERT INTO Medico (crm, nome, sobrenome, telefone, especialidade, id_consulta)
VALUES
(12345, 'Roberto', 'Lima', '11987654321', 'Cardiologia', 1),
(67890, 'Fernanda', 'Costa', '11999887766', 'Dermatologia', 2),
(54321, 'Juliana', 'Mendes', '11988776655', 'Pediatria', 3);

INSERT INTO Paciente_Telefone (cpf, telefone)
VALUES
('12345678901', '11911112222'),
('12345678901', '11933334444'),
('98765432100', '11955556666'),
('11122233344', '11977778888');

SELECT * FROM consultas;
SELECT * FROM Paciente;

SELECT * FROM Medico;
SELECT * FROM Paciente_Telefone;