-- Highlift Atelier — Schéma base de données
-- Encodage : utf8mb4
-- Dates stockées en UTC

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(100),
  douchette_id VARCHAR(50) UNIQUE,
  badge_qr VARCHAR(50) UNIQUE,
  actif TINYINT DEFAULT 1,
  created_at DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS machines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  qr_code VARCHAR(50) UNIQUE NOT NULL,
  nom VARCHAR(150) NOT NULL,
  marque VARCHAR(100),
  modele VARCHAR(100),
  numero_serie VARCHAR(100),
  date_entree DATE,
  actif TINYINT DEFAULT 1,
  created_at DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reparations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  machine_id INT NOT NULL,
  user_id INT NOT NULL,
  debut DATETIME NOT NULL,
  fin DATETIME,
  duree_minutes INT,
  statut ENUM('en_cours','terminee','annulee') DEFAULT 'en_cours',
  notes TEXT,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (machine_id) REFERENCES machines(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reparation_pieces (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reparation_id INT NOT NULL,
  piece_ref VARCHAR(100) NOT NULL,
  piece_nom VARCHAR(200),
  quantite DECIMAL(10,2) DEFAULT 1,
  scanned_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (reparation_id) REFERENCES reparations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pieces_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  odoo_id INT,
  reference VARCHAR(100) UNIQUE NOT NULL,
  nom VARCHAR(200) NOT NULL,
  unite VARCHAR(20),
  categorie VARCHAR(100),
  actif TINYINT DEFAULT 1,
  synced_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS scan_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  raw_value VARCHAR(255),
  user_id INT,
  action VARCHAR(50),
  resultat VARCHAR(50),
  scanned_at DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
