-- ============================================================================
-- BASE DE PRATIQUE MYSQL - REVISION BILINGUE FR / EN
-- Practice database for MySQL revision - bilingual French / English
-- ============================================================================
-- Reexecution : ce script supprime et recree toute la base.
-- Re-running: this script drops and recreates the whole database.
-- Compatible : MySQL 8.x / MariaDB recentes (avec InnoDB).
-- Compatible with: MySQL 8.x / recent MariaDB versions (using InnoDB).
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP DATABASE IF EXISTS revision_mysql;
SET FOREIGN_KEY_CHECKS = 1;

CREATE DATABASE revision_mysql
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE revision_mysql;

-- ----------------------------------------------------------------------------
-- 1) TABLES PRINCIPALES / MAIN TABLES
-- ----------------------------------------------------------------------------

CREATE TABLE race (
    numr        INT NOT NULL,
    nomr        VARCHAR(40) NOT NULL,
    poids_min   DECIMAL(5,2) NULL,
    poids_max   DECIMAL(5,2) NULL,
    CONSTRAINT pk_race PRIMARY KEY (numr),
    CONSTRAINT uq_race_nom UNIQUE (nomr),
    CONSTRAINT chk_race_poids CHECK (
        poids_min IS NULL OR poids_max IS NULL OR poids_min <= poids_max
    )
) ENGINE = InnoDB;

CREATE TABLE proprietaire (
    nump        INT NOT NULL AUTO_INCREMENT,
    nomp        VARCHAR(30) NOT NULL,
    prenomp     VARCHAR(30) NOT NULL,
    villep      VARCHAR(40) NOT NULL,
    telp        VARCHAR(20) NULL,
    email       VARCHAR(100) NULL,
    CONSTRAINT pk_proprietaire PRIMARY KEY (nump),
    CONSTRAINT uq_proprietaire_email UNIQUE (email)
) ENGINE = InnoDB;

CREATE TABLE animal (
    numa        INT NOT NULL AUTO_INCREMENT,
    nump        INT NOT NULL,
    numr        INT NOT NULL,
    noma        VARCHAR(40) NOT NULL,
    datenaissa  DATE NULL,
    tatouage    VARCHAR(20) NULL,
    CONSTRAINT pk_animal PRIMARY KEY (numa),
    CONSTRAINT uq_animal_tatouage UNIQUE (tatouage),
    CONSTRAINT fk_animal_proprietaire
        FOREIGN KEY (nump) REFERENCES proprietaire(nump)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_animal_race
        FOREIGN KEY (numr) REFERENCES race(numr)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE = InnoDB;

CREATE TABLE veterinaire (
    numv        INT NOT NULL AUTO_INCREMENT,
    nomv        VARCHAR(30) NOT NULL,
    prenomv     VARCHAR(30) NOT NULL,
    villev      VARCHAR(40) NOT NULL,
    CONSTRAINT pk_veterinaire PRIMARY KEY (numv)
) ENGINE = InnoDB;

CREATE TABLE consultation (
    numc        INT NOT NULL AUTO_INCREMENT,
    numv        INT NOT NULL,
    numa        INT NOT NULL,
    datec       DATE NOT NULL,
    heurec      TIME NOT NULL,
    prixc       DECIMAL(8,2) NOT NULL DEFAULT 0,
    CONSTRAINT pk_consultation PRIMARY KEY (numc),
    CONSTRAINT chk_consultation_prix CHECK (prixc >= 0),
    CONSTRAINT fk_consultation_veterinaire
        FOREIGN KEY (numv) REFERENCES veterinaire(numv)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_consultation_animal
        FOREIGN KEY (numa) REFERENCES animal(numa)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE medicament (
    numm        INT NOT NULL AUTO_INCREMENT,
    nomm        VARCHAR(60) NOT NULL,
    prixm       DECIMAL(8,2) NOT NULL,
    CONSTRAINT pk_medicament PRIMARY KEY (numm),
    CONSTRAINT uq_medicament_nom UNIQUE (nomm),
    CONSTRAINT chk_medicament_prix CHECK (prixm > 0)
) ENGINE = InnoDB;

CREATE TABLE soin (
    nums        INT NOT NULL AUTO_INCREMENT,
    numc        INT NOT NULL,
    natures     VARCHAR(80) NOT NULL,
    CONSTRAINT pk_soin PRIMARY KEY (nums),
    CONSTRAINT fk_soin_consultation
        FOREIGN KEY (numc) REFERENCES consultation(numc)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;

-- Tables de bac a sable pour les exercices LDD.
-- Sandbox tables for DDL exercises.
CREATE TABLE assistant (
    numas       INT NOT NULL,
    nomas       VARCHAR(20) NULL,
    tel         VARCHAR(20) NULL
) ENGINE = InnoDB;

CREATE TABLE eleve (
    idel        INT NOT NULL AUTO_INCREMENT,
    nomel       VARCHAR(30) NOT NULL,
    noteg       DECIMAL(4,2) NULL,
    CONSTRAINT pk_eleve PRIMARY KEY (idel),
    CONSTRAINT chk_eleve_note CHECK (noteg IS NULL OR noteg BETWEEN 0 AND 20)
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- 2) DONNEES / DATA
-- ----------------------------------------------------------------------------

INSERT INTO race (numr, nomr, poids_min, poids_max) VALUES
(1,  'Labrador',          25.00, 36.00),
(2,  'Berger allemand',   22.00, 40.00),
(3,  'Siamois',            3.00,  6.00),
(4,  'Caniche',            4.00, 14.00),
(5,  'Europeen',           3.00,  7.00),
(6,  'Beagle',             9.00, 14.00),
(7,  'Boxer',             25.00, 32.00),
(8,  'Lapin belier nain',  1.20,  2.50),
(9,  'Golden retriever',  25.00, 34.00),
(10, 'Persan',             3.00,  7.00);

INSERT INTO proprietaire (nump, nomp, prenomp, villep, telp, email) VALUES
(1,  'Fevrier',  'Jean-Yves',  'Dijon',  '0328785965', NULL),
(2,  'Fevrier',  'Frederique', 'Paris',  '0145855844', 'frederique.fevrier@example.fr'),
(3,  'Mars',     'Josette',    'Nancy',  '0347896587', 'josette.mars@example.fr'),
(4,  'Javelas',  'Henri',      'Lyon',   '0478123456', 'henri.javelas@example.fr'),
(5,  'Loriette', 'Sylvain',    'Paris',  '0144556677', 'sylvain.loriette@example.fr'),
(6,  'Fevrier',  'Jean-Yves',  'Caen',   '0257845454', 'jy.fevrier.caen@example.fr'),
(7,  'Brown',    'Alice',      'Nantes', '0251001122', NULL),
(8,  'Martin',   'Chloe',      'Lyon',   '0477008899', 'chloe.martin@example.fr'),
(9,  'Wilson',   'David',      'Paris',  '0144002233', 'david.wilson@example.fr'),
(10, 'Petit',    'Emma',       'Nantes', '0251223344', 'emma.petit@example.fr');

INSERT INTO animal (numa, nump, numr, noma, datenaissa, tatouage) VALUES
(1,  1,  1, 'Elan',      '1980-01-01', 'ERT502'),
(2,  2,  2, 'Demon',     '1985-06-05', NULL),
(3,  3,  3, 'Zoe',       '2000-02-14', 'ZEN245'),
(4,  4,  4, 'Nina',      '1996-01-01', 'DFG001'),
(5,  4,  5, 'Niok',      '2000-03-12', 'OIU115'),
(6,  5,  1, 'Jeune Alf', NULL,         NULL),
(7,  5,  3, 'Pollen',    '2018-05-20', NULL),
(8,  4,  5, 'Jafna',     '1996-05-23', 'BNJ145'),
(9,  4,  5, 'Fabel',     '1995-10-24', 'BNA485'),
(10, 4,  5, 'Tanis',     '1994-06-30', 'BUI416'),
(11, 6,  6, 'Rio',       '1994-07-15', 'KIU521'),
(12, 7,  4, 'Bipsie',    '1985-09-03', 'DZE445'),
(13, 7,  4, 'Bouboule',  '1995-04-08', 'MPO444'),
(14, 6,  9, 'Nouki',     NULL,         NULL),
(15, 8,  6, 'Milou',     '2015-11-11', NULL),
(16, 8,  3, 'Maya',      '2019-02-02', 'CAT101'),
(17, 9,  7, 'Oscar',     '2017-08-18', 'BOX201'),
(18, 9,  5, 'Luna',      '2020-12-12', 'EUR221'),
(19, 10, 2, 'Rocky',     '2016-06-06', 'GER330'),
(20, 10, 1, 'Bella',     '2021-03-30', 'LAB999');

INSERT INTO veterinaire (numv, nomv, prenomv, villev) VALUES
(1, 'Carre', 'Paul',     'Paris'),
(2, 'Carre', 'Violette', 'Lyon'),
(3, 'Rhin',  'Michel',   'Nantes');

INSERT INTO consultation (numc, numv, numa, datec, heurec, prixc) VALUES
(1,  1, 1,  '2023-01-10', '09:00:00', 120.00),
(2,  1, 2,  '2023-02-15', '10:00:00',  80.00),
(3,  2, 3,  '2023-03-20', '11:30:00', 150.00),
(4,  2, 4,  '2023-04-02', '14:00:00', 220.00),
(5,  3, 5,  '2023-05-14', '15:30:00',  60.00),
(6,  1, 8,  '2023-06-18', '08:30:00', 200.00),
(7,  2, 9,  '2023-07-22', '13:15:00', 180.00),
(8,  3, 10, '2023-08-03', '16:00:00', 300.00),
(9,  1, 11, '2023-09-11', '09:45:00', 110.00),
(10, 2, 12, '2023-10-25', '10:20:00',  95.00),
(11, 3, 13, '2023-11-30', '17:00:00', 250.00),
(12, 1, 15, '2024-01-05', '09:10:00',  70.00),
(13, 2, 16, '2024-02-09', '11:40:00', 130.00),
(14, 3, 17, '2024-03-13', '14:20:00', 400.00),
(15, 1, 18, '2024-04-17', '15:10:00', 160.00),
(16, 2, 19, '2024-05-21', '16:30:00', 210.00),
(17, 1, 1,  '2024-06-02', '08:45:00', 100.00),
(18, 1, 2,  '2024-07-06', '10:15:00', 140.00),
(19, 2, 3,  '2024-08-10', '12:00:00', 170.00),
(20, 2, 4,  '2024-09-14', '13:40:00', 230.00),
(21, 3, 5,  '2024-10-18', '15:50:00',  90.00),
(22, 1, 8,  '2025-01-12', '09:30:00', 205.00),
(23, 2, 9,  '2025-02-16', '11:00:00', 185.00),
(24, 3, 10, '2025-03-22', '14:30:00', 310.00),
(25, 1, 11, '2025-04-26', '16:00:00', 115.00),
(26, 2, 12, '2025-05-30', '10:50:00', 105.00),
(27, 3, 13, '2025-06-03', '13:20:00', 260.00),
(28, 1, 15, '2026-01-08', '08:20:00',  75.00),
(29, 2, 16, '2026-02-12', '10:40:00', 135.00),
(30, 3, 17, '2026-03-18', '15:00:00', 420.00);

INSERT INTO medicament (numm, nomm, prixm) VALUES
(1, 'Vermifuge pate',     35.00),
(2, 'Vermifuge cachet',   40.00),
(3, 'Vaccin rage',        86.00),
(4, 'Vaccin Leucose',    119.00),
(5, 'Diuretique',         45.00),
(6, 'Croquettes 10 Kg',  350.00),
(7, 'Antibiotique chat',  72.50),
(8, 'Anti-inflammatoire', 98.90);

INSERT INTO soin (nums, numc, natures) VALUES
(1,  1,  'vaccination'),
(2,  2,  'CONTROLE GENERAL'),
(3,  3,  'vermifuge'),
(4,  4,  'Chirurgie legere'),
(5,  5,  'controle dentaire'),
(6,  6,  'VACCINATION RAGE'),
(7,  8,  'chirurgie orthopedique'),
(8,  11, 'analyse sanguine'),
(9,  14, 'URGENCE'),
(10, 17, 'suivi annuel'),
(11, 20, 'Controle general'),
(12, 24, 'chirurgie'),
(13, 27, 'vaccination'),
(14, 30, 'urgence orthopedique');

INSERT INTO assistant (numas, nomas, tel) VALUES
(1, 'Pemba', '0606060606'),
(2, 'Duroc', '0607606060');

INSERT INTO eleve (nomel, noteg) VALUES
('Titi', 12.50),
('Toto', 15.00),
('Tutu', NULL);

-- ----------------------------------------------------------------------------
-- 3) VUE UTILE / USEFUL VIEW
-- ----------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_animal_detail AS
SELECT
    a.numa,
    a.noma,
    r.nomr AS race,
    CONCAT(p.prenomp, ' ', p.nomp) AS proprietaire,
    p.villep,
    a.datenaissa,
    a.tatouage
FROM animal AS a
INNER JOIN race AS r ON r.numr = a.numr
INNER JOIN proprietaire AS p ON p.nump = a.nump;

-- ----------------------------------------------------------------------------
-- 4) VERIFICATIONS RAPIDES / QUICK CHECKS
-- ----------------------------------------------------------------------------
SELECT 'race' AS table_name, COUNT(*) AS nb_lignes FROM race
UNION ALL SELECT 'proprietaire', COUNT(*) FROM proprietaire
UNION ALL SELECT 'animal', COUNT(*) FROM animal
UNION ALL SELECT 'veterinaire', COUNT(*) FROM veterinaire
UNION ALL SELECT 'consultation', COUNT(*) FROM consultation
UNION ALL SELECT 'medicament', COUNT(*) FROM medicament
UNION ALL SELECT 'soin', COUNT(*) FROM soin;

-- Resultats attendus / Expected row counts:
-- race=10, proprietaire=10, animal=20, veterinaire=3,
-- consultation=30, medicament=8, soin=14.

-- Fin du script / End of script.
