zilnik/install.sql at main · chevinou/zilnik · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
-- =====================================================================
-- install.sql — Schéma complet de Zilnik
-- Compatible MySQL 5.7+ / MariaDB 10.4+
-- Exécuter UNE SEULE FOIS lors de l'installation initiale.
-- =====================================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
SET NAMES utf8mb4;

-- ── Catégories ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `categories` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `nom`        varchar(100) NOT NULL,
  `couleur`    varchar(7)   NOT NULL DEFAULT '#005a9c',
  `icone`      varchar(10)  NOT NULL DEFAULT '📦',
  `created_at` timestamp    NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Produits ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `produits` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `reference`    varchar(50)  NOT NULL,
  `nom`          varchar(200) NOT NULL,
  `categorie_id` int(11)      DEFAULT NULL,
  `description`  text         DEFAULT NULL,
  `quantite`     int(11)      NOT NULL DEFAULT 0,
  `seuil_alerte` int(11)      NOT NULL DEFAULT 5,
  `unite`        varchar(20)  NOT NULL DEFAULT 'unité(s)',
  `fournisseur`  varchar(150) DEFAULT NULL,
  `emplacement`  varchar(150) DEFAULT NULL,
  `actif`        tinyint(1)   NOT NULL DEFAULT 1,
  `created_at`   timestamp    NOT NULL DEFAULT current_timestamp(),
  `updated_at`   timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_reference` (`reference`),
  KEY `fk_categorie` (`categorie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Mouvements de stock ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mouvements` (
  `id`              int(11)                            NOT NULL AUTO_INCREMENT,
  `produit_id`      int(11)                            NOT NULL,
  `type`            enum('entree','sortie','ajustement') NOT NULL,
  `quantite`        int(11)                            NOT NULL,
  `quantite_avant`  int(11)                            NOT NULL,
  `quantite_apres`  int(11)                            NOT NULL,
  `commentaire`     text                               DEFAULT NULL,
  `user_nom`        varchar(150)                       DEFAULT NULL,
  `user_mail`       varchar(150)                       DEFAULT NULL,
  `user_service`    varchar(150)                       DEFAULT NULL,
  `created_at`      timestamp                          NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_mouvement_produit` (`produit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Utilisateurs autorisés (toutes méthodes d'auth) ─────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `mail`       varchar(150) NOT NULL,
  `nom`        varchar(150) DEFAULT NULL,
  `prenom`     varchar(150) DEFAULT NULL,
  `service`    varchar(150) DEFAULT NULL,
  `autorise`   tinyint(1)   NOT NULL DEFAULT 0 COMMENT '0 = refusé (défaut), 1 = autorisé',
  `created_at` timestamp    NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mail` (`mail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Comptes locaux (AUTH_METHOD = 'local' uniquement) ────────────────
-- Non utilisée avec les méthodes SSO ou LDAP.
CREATE TABLE IF NOT EXISTS `local_users` (
  `id`            int(11)      NOT NULL AUTO_INCREMENT,
  `mail`          varchar(150) NOT NULL,
  `nom`           varchar(150) DEFAULT NULL,
  `prenom`        varchar(150) DEFAULT NULL,
  `service`       varchar(150) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL COMMENT 'bcrypt via password_hash()',
  `actif`         tinyint(1)   NOT NULL DEFAULT 1,
  `created_at`    timestamp    NOT NULL DEFAULT current_timestamp(),
  `updated_at`    timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_local_mail` (`mail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contraintes ──────────────────────────────────────────────────────
ALTER TABLE `mouvements`
  ADD CONSTRAINT `fk_mouvement_produit`
  FOREIGN KEY (`produit_id`) REFERENCES `produits` (`id`) ON DELETE CASCADE;

ALTER TABLE `produits`
  ADD CONSTRAINT `fk_produit_categorie`
  FOREIGN KEY (`categorie_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL;

-- ── Données de démonstration (supprimez ce bloc en production) ───────
INSERT INTO `categories` (`nom`, `couleur`, `icone`) VALUES
  ('Stockage',      '#3b82f6', '💾'),
  ('Câbles',        '#10b981', '🔌'),
  ('Périphériques', '#f59e0b', '🖱️'),
  ('Réseau',        '#8b5cf6', '🌐'),
  ('Bureautique',   '#ef4444', '🖨️'),
  ('Autre',         '#64748b', '📦');

-- Autorisez votre premier compte administrateur après l'installation :
-- UPDATE users SET autorise = 1 WHERE mail = 'votre.adresse@domaine.fr';

COMMIT;