CREATE DATABASE IF NOT EXISTS gued0581_jll
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE gued0581_jll;

-- Variétés
CREATE TABLE IF NOT EXISTS varietes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  densite DECIMAL(8,2) NOT NULL,
  rendement DECIMAL(8,2) NOT NULL
);

-- Semis
CREATE TABLE IF NOT EXISTS semis (
  id INT AUTO_INCREMENT PRIMARY KEY,
  variete_id INT NOT NULL,
  date_semis DATE NOT NULL,
  nb_barquettes INT NOT NULL,
  taille ENUM('petite','moyenne','grande') NOT NULL,
  semences DECIMAL(10,2) NOT NULL,
  recolte DECIMAL(10,2) NOT NULL,
  date_recolte DATE NOT NULL,
  FOREIGN KEY (variete_id) REFERENCES varietes(id) ON DELETE CASCADE
);

-- HACCP
CREATE TABLE IF NOT EXISTS haccp (
  id INT AUTO_INCREMENT PRIMARY KEY,
  date_controle DATE NOT NULL,
  parametre ENUM('Température','Humidité','pH Eau','EC Eau') NOT NULL,
  valeur DECIMAL(8,2) NOT NULL,
  responsable VARCHAR(100),
  action_corrective TEXT
);
