-- create a table
CREATE TABLE patients (
    numSecu VARCHAR(15) PRIMARY KEY,
    nom VARCHAR(50),
    prenom VARCHAR(50),
    dateNaissance DATE,
    ville VARCHAR(50)
) Engine = InnoDB;

















-- create a table
CREATE TABLE medecin (
    rpps VARCHAR(15) PRIMARY KEY,
    nom VARCHAR(50),
    specialite VARCHAR(50)
) Engine = InnoDB;


-- create a table
CREATE TABLE medicament (
    codeCIP VARCHAR(15) PRIMARY KEY,
    nom VARCHAR(50),
    dosage VARCHAR(30)
) Engine = InnoDB;


-- insert some values

-- Trois patients
INSERT INTO patients VALUES ('123456789012345', 'Dupuis', 'Maël', '2002-03-14', 'Compiègne');
INSERT INTO patients VALUES ('234567890123456', 'Albato', 'Luc', '1999-11-20', 'Paris');
INSERT INTO patients VALUES ('323456789012345', 'Albert', 'Léa', '2026-03-25', 'Bruxelles');

-- Patient demandé dans la question 5
INSERT INTO patients VALUES ('1548def', 'Martin', 'Julie', '2001-07-12', 'Lyon');
INSERT INTO patients VALUES ('1550def', 'Loé','Diane','2001-08-14','Reims')    ;
INSERT INTO patients VALUES ('1549def','Martine','Julieke', '2001-08-13','Paris');


-- Deux médecins
INSERT INTO medecin VALUES ('101', 'Dr B', 'Médecine générale');
INSERT INTO medecin VALUES ('102', 'Dr C', 'Endocrinologie');
INSERT INTO medecin VALUES ('103','Dr Dre', 'gynécologie');


-- Trois médicaments
INSERT INTO medicament VALUES ('340093000001', 'Paracétamol', '500 mg');
INSERT INTO medicament VALUES ('340093000002', 'Kétoprofène', '400 mg');
INSERT INTO medicament VALUES ('340093000003', 'Amoxicilline', '500 mg');
INSERT INTO medicament VALUES ('340093000004','aspégic','1000mg');


-- Afficher les patients habitant à Compiègne
SELECT nom, prenom
FROM patients
WHERE ville = 'Compiègne';


-- Création de la table Prescription
CREATE TABLE Prescription (
    idPrescription INTEGER PRIMARY KEY,
    datePrescription DATE,
    dureeJours INTEGER,
    patient_id VARCHAR(15),
    medecin_id VARCHAR(15),
    FOREIGN KEY (patient_id) REFERENCES patients(numSecu),
    FOREIGN KEY (medecin_id) REFERENCES medecin(rpps)
) Engine = InnoDB;


-- Création de la table LignePrescription
CREATE TABLE LignePrescription (
    prescription_id INTEGER,
    medicament_id VARCHAR(15),
    posologie VARCHAR(100),
    PRIMARY KEY (prescription_id, medicament_id),
    FOREIGN KEY (prescription_id) REFERENCES Prescription(idPrescription),
    FOREIGN KEY (medicament_id) REFERENCES medicament(codeCIP)
) Engine = InnoDB;


-- Une prescription reliant un patient à un médecin
INSERT INTO Prescription VALUES
(1, '2026-09-17', 7, '1548def', '101');


-- Deux lignes de prescription avec deux médicaments différents
INSERT INTO LignePrescription VALUES
(1, '340093000001', '2 fois par jour');

INSERT INTO LignePrescription VALUES
(1, '340093000002', '1 fois par jour');

-- Nom du patient et nom du médecin de la prescription n°1
SELECT patients.nom, medecin.nom
FROM Prescription
JOIN patients ON Prescription.patient_id = patients.numSecu
JOIN medecin ON Prescription.medecin_id = medecin.rpps
WHERE Prescription.idPrescription = 1;

-- Médicaments prescrits au patient '1548def'
SELECT medicament.nom
FROM Prescription
JOIN LignePrescription
    ON Prescription.idPrescription = LignePrescription.prescription_id
JOIN medicament
    ON LignePrescription.medicament_id = medicament.codeCIP
WHERE Prescription.patient_id = '1548def';