-- FiberOps GMAO - Schéma SQL minimal v1

-- Utilisateurs et sécurité
CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(50) UNIQUE NOT NULL,
  label VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(100) UNIQUE NOT NULL,
  label VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role_key VARCHAR(50) NOT NULL DEFAULT 'agent',
  technician_type VARCHAR(50) NULL,
  totp_secret VARCHAR(64) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Paramètres clé/valeur
CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Référentiel catalogue coûts
CREATE TABLE IF NOT EXISTS cost_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cost_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  designation VARCHAR(200) NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'u',
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL DEFAULT 'XOF',
  FOREIGN KEY (category_id) REFERENCES cost_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Devis
CREATE TABLE IF NOT EXISTS quotes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_no VARCHAR(40) UNIQUE NOT NULL,
  client_name VARCHAR(150) NOT NULL,
  related_type ENUM('incident','preventive','') NOT NULL DEFAULT '',
  related_ref VARCHAR(64) NULL,
  quote_date DATE NOT NULL,
  status ENUM('En attente','Validé','Rejeté') NOT NULL DEFAULT 'En attente',
  total_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_tva DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_ttc DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL DEFAULT 'XOF'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quote_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_id INT NOT NULL,
  designation VARCHAR(200) NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'u',
  quantity DECIMAL(12,3) NOT NULL DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_line DECIMAL(12,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SLA et types de maintenance
CREATE TABLE IF NOT EXISTS maintenance_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sla_policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  maintenance_type_id INT NOT NULL,
  priority ENUM('Basse','Moyenne','Haute','Urgent') NOT NULL DEFAULT 'Moyenne',
  response_minutes INT NOT NULL DEFAULT 60,
  resolution_minutes INT NOT NULL DEFAULT 240,
  FOREIGN KEY (maintenance_type_id) REFERENCES maintenance_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seeds de base
INSERT IGNORE INTO roles (id, key_name, label) VALUES
  (1,'admin','Administrateur'),
  (2,'agent','Agent'),
  (3,'technicien','Technicien');

-- Admin par défaut
INSERT INTO users (name, email, password_hash, role_key, active)
VALUES ('Admin', 'admin@fiberops.local', '$2y$10$w2xB6m0nKcJrJ9C2k3XlRebmW0b6D4k9hA9YbZGmEo4YbE3XvAcpm', 'admin', 1)
ON DUPLICATE KEY UPDATE email=email;
-- Le hash correspond à "Admin@12345"