-- =====================================================
-- TD Extraction de données : une clinique vétérinaire
-- Script de création et de peuplement des tables
-- À exécuter avant de traiter les questions du TD
-- =====================================================

DROP TABLE IF EXISTS Consultation;
DROP TABLE IF EXISTS Animal;
DROP TABLE IF EXISTS Veterinaire;
DROP TABLE IF EXISTS Proprietaire;

CREATE TABLE Proprietaire (
  id_proprietaire INT PRIMARY KEY AUTO_INCREMENT,
  nom             VARCHAR(50),
  prenom          VARCHAR(50),
  ville           VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE Veterinaire (
  id_veterinaire INT PRIMARY KEY AUTO_INCREMENT,
  nom            VARCHAR(50),
  specialite     VARCHAR(50)
) ENGINE = InnoDB;

CREATE TABLE Animal (
  id_animal       INT PRIMARY KEY AUTO_INCREMENT,
  nom             VARCHAR(50),
  espece          VARCHAR(30),
  date_naissance  DATE,
  id_proprietaire INT,
  FOREIGN KEY (id_proprietaire) REFERENCES Proprietaire(id_proprietaire)
) ENGINE = InnoDB;

CREATE TABLE Consultation (
  id_consultation   INT PRIMARY KEY AUTO_INCREMENT,
  date_consultation DATE,
  motif             VARCHAR(100),
  cout              DECIMAL(6,2),
  id_animal         INT,
  id_veterinaire    INT,
  FOREIGN KEY (id_animal) REFERENCES Animal(id_animal),
  FOREIGN KEY (id_veterinaire) REFERENCES Veterinaire(id_veterinaire)
) ENGINE = InnoDB;

-- =====================================================
-- Données
-- =====================================================

INSERT INTO Proprietaire (nom, prenom, ville) VALUES
  ('Dupont', 'Marie', 'Lyon'),
  ('Bernard', 'Julien', 'Villeurbanne'),
  ('Morel', 'Sophie', 'Lyon');

INSERT INTO Veterinaire (nom, specialite) VALUES
  ('Lefevre', 'Generaliste'),
  ('Dubois', 'Chirurgie'),
  ('Renard', 'Dermatologie');

INSERT INTO Animal (nom, espece, date_naissance, id_proprietaire) VALUES
  ('Rex', 'Chien', '2020-03-01', 1),
  ('Mimi', 'Chat', '2019-07-12', 1),
  ('Fido', 'Chien', '2021-01-15', 2),
  ('Nemo', 'Poisson', '2022-05-01', 3);

INSERT INTO Consultation (date_consultation, motif, cout, id_animal, id_veterinaire) VALUES
  ('2026-01-10', 'Vaccin', 45.00, 1, 1),
  ('2026-02-05', 'Consultation generale', 35.00, 1, 1),
  ('2026-01-20', 'Vaccin', 45.00, 2, 1),
  ('2026-03-01', 'Chirurgie', 250.00, 3, 2);

SELECT v.id_Veterinaire, v.nom, COUNT(c.id_consultation) AS nb
FROM Veterinaire v 
LEFT JOIN Consultation c ON v.id_veterinaire = c.id_veterinaire
GROUP BY v.id_veterinaire, v.nom;

SELECT a.id_animal,a.nom, SUM(c.cout) AS cout
FROM Animal a 
LEFT JOIN Consultation c ON a.id_animal = c.id_animal
GROUP BY a.id_animal, a.nom;

SELECT v.nom, COUNT(c.id_consultation) AS nb
FROM Veterinaire v
LEFT JOIN Consultation c ON v.id_veterinaire= c.id_veterinaire
GROUP BY v.id_veterinaire, v.nom
HAVING COUNT(c.id_consultation) >1;

SELECT p.nom AS animal, p.nom
FROM Animal a 
JOIN Proprietaire p ON a.id_proprietaire= p.id_proprietaire;

SELECT c.id_consultation, a.nom AS animal, v.nom AS Veterinaire
FROM Consultation c 
JOIN Animal a ON c.id_animal= a.id_animal
JOIN Veterinaire v ON c.id_veterinaire= v.id_veterinaire;

SELECT p.nom, p.prenom, COUNT(a.id_animal) AS nombres
FROM Proprietaire p
JOIN Animal a ON p.id_proprietaire = a.id_proprietaire
GROUP BY p.id_proprietaire, p.nom, p.prenom
HAVING COUNT (a.id_animal) >1;