-- ===================================================================
-- My Truck — Schéma MariaDB pour installation FRESH
-- ===================================================================
-- Si tu installes My Truck sur une DB VIERGE (sans ton autre site web),
-- importe ce fichier. Sinon, utilise `migration_mytruck.sql` pour ajouter
-- My Truck à ta DB existante.
-- ===================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) NULL,
  fullname VARCHAR(100) NOT NULL DEFAULT '',
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'cariste',
  mytruck_role VARCHAR(20) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(6) NULL,
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS folders (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plate VARCHAR(50) NOT NULL,
  problem MEDIUMTEXT NOT NULL,
  status ENUM('en_attente','en_cours','cloture') NOT NULL DEFAULT 'en_attente',
  cariste_id INT NOT NULL,
  cariste_name VARCHAR(255) NOT NULL DEFAULT '',
  assigned_to_id INT NULL,
  assigned_to_name VARCHAR(255) NULL,
  created_at DATETIME(6) NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  date_key VARCHAR(10) NOT NULL,
  KEY ix_date_key (date_key),
  KEY ix_cariste (cariste_id),
  KEY ix_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS folder_photos (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  folder_id INT NOT NULL,
  position INT NOT NULL,
  data LONGTEXT NOT NULL,
  KEY ix_folder_pos (folder_id, position),
  CONSTRAINT fk_photos_folder FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS folder_history (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  folder_id INT NOT NULL,
  action VARCHAR(50) NOT NULL,
  by_id INT NULL,
  by_name VARCHAR(255) NULL,
  at DATETIME(6) NOT NULL,
  label TEXT NOT NULL,
  KEY ix_folder_at (folder_id, at),
  CONSTRAINT fk_history_folder FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS phrases (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  text VARCHAR(500) NOT NULL,
  created_at DATETIME(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS manual_notifications (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sender_id INT NOT NULL,
  sender_name VARCHAR(255) NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME(6) NOT NULL,
  recipients_count INT NOT NULL DEFAULT 0,
  KEY ix_sender (sender_id),
  KEY ix_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS manual_notification_recipients (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  notification_id INT NOT NULL,
  user_id INT NOT NULL,
  read_at DATETIME(6) NULL,
  KEY ix_notif (notification_id),
  KEY ix_user (user_id),
  CONSTRAINT fk_recip_notif FOREIGN KEY (notification_id) REFERENCES manual_notifications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_tokens (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token VARCHAR(512) NOT NULL,
  platform VARCHAR(20) NOT NULL DEFAULT 'web',
  created_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_token (token),
  KEY ix_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- Au premier démarrage du backend, si aucun bureau/cariste n'existe,
-- un compte par défaut est créé : admin.b / 123456 (à changer dès la
-- première connexion).
