-- ============================================================
--  AUDIT APP — Schéma MySQL
-- ============================================================

CREATE DATABASE IF NOT EXISTS audit_app
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE audit_app;

-- ── Départements / Rayons ─────────────────────────────────
CREATE TABLE IF NOT EXISTS departements (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nom         VARCHAR(100) NOT NULL,
  ordre       TINYINT UNSIGNED DEFAULT 0,
  actif       TINYINT(1) DEFAULT 1,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO departements (nom, ordre) VALUES
  ('Boucherie',                1),
  ('Boulangerie Viennoiserie', 2),
  ('Charcuterie',              3),
  ('Direction',                4),
  ('Drive',                    5),
  ('F&L',                      6),
  ('Fromagerie - Cremerie',    7),
  ('Marée',                    8),
  ('Pâtisserie Viennoiserie',  9),
  ('PGC alimentaires',        10),
  ('Produits Industriels LS', 11),
  ('Rôtisserie',              12),
  ('SMQ',                     13);

-- ── Points d'audit ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS points_audit (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  departement_id  INT UNSIGNED NOT NULL,
  libelle         TEXT NOT NULL,
  critere         VARCHAR(50) DEFAULT NULL,   -- ex: "Hygiène", "Sécurité"…
  obligatoire     TINYINT(1) DEFAULT 0,
  actif           TINYINT(1) DEFAULT 1,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (departement_id) REFERENCES departements(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Sessions d'audit ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS sessions_audit (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nom         VARCHAR(150) NOT NULL,
  date_debut  DATE NOT NULL,
  date_fin    DATE,
  statut      ENUM('en_cours','termine','archive') DEFAULT 'en_cours',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Réponses aux points d'audit ──────────────────────────
CREATE TABLE IF NOT EXISTS reponses (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id       INT UNSIGNED NOT NULL,
  point_id         INT UNSIGNED NOT NULL,
  statut           ENUM('conforme','non_conforme','na') DEFAULT NULL,
  commentaire      TEXT,
  photo_path       VARCHAR(255) DEFAULT NULL,
  auditeur         VARCHAR(100) DEFAULT NULL,
  repondu_le       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (session_id) REFERENCES sessions_audit(id) ON DELETE CASCADE,
  FOREIGN KEY (point_id)   REFERENCES points_audit(id)   ON DELETE CASCADE,
  UNIQUE KEY uniq_session_point (session_id, point_id)
) ENGINE=InnoDB;

-- ── Plan d'actions ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS plan_actions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reponse_id      INT UNSIGNED NOT NULL,
  description     TEXT NOT NULL,
  responsable     VARCHAR(100),
  echeance        DATE,
  statut          ENUM('ouvert','en_cours','cloture') DEFAULT 'ouvert',
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (reponse_id) REFERENCES reponses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Notes libres ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id  INT UNSIGNED NOT NULL,
  contenu     TEXT NOT NULL,
  auteur      VARCHAR(100),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (session_id) REFERENCES sessions_audit(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Vue pratique : nb de points par département ──────────
CREATE OR REPLACE VIEW v_stats_departements AS
SELECT
  d.id,
  d.nom,
  d.ordre,
  COUNT(pa.id)                                          AS total_points,
  SUM(CASE WHEN r.statut IS NULL THEN 1 ELSE 0 END)    AS a_auditer,
  SUM(CASE WHEN r.statut = 'conforme'     THEN 1 ELSE 0 END) AS conformes,
  SUM(CASE WHEN r.statut = 'non_conforme' THEN 1 ELSE 0 END) AS non_conformes,
  SUM(CASE WHEN r.statut = 'na'           THEN 1 ELSE 0 END) AS na
FROM departements d
LEFT JOIN points_audit pa ON pa.departement_id = d.id AND pa.actif = 1
LEFT JOIN reponses r       ON r.point_id = pa.id
WHERE d.actif = 1
GROUP BY d.id, d.nom, d.ordre
ORDER BY d.ordre;
