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

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','user') NOT NULL DEFAULT 'user'
);

CREATE TABLE varietes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  densite DECIMAL(8,2) NOT NULL,
  rendement DECIMAL(8,2) NOT NULL
);

CREATE TABLE 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
);

CREATE TABLE 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
);
