-- Base de données MySQL pour Forage Grenville
-- Préfixe: forage_
-- À exécuter dans phpMyAdmin de OVH

-- Sélectionner la base de données
USE gagnonn157;

-- Table des véhicules
CREATE TABLE IF NOT EXISTS `forage_vehicules` (
  `id` VARCHAR(10) PRIMARY KEY,
  `type` VARCHAR(20) NOT NULL,
  `marque` VARCHAR(50) NOT NULL,
  `modele` VARCHAR(50) NOT NULL,
  `annee` INT NOT NULL,
  `immatriculation` VARCHAR(20),
  `kilometrage` INT DEFAULT 0,
  `statut` VARCHAR(20) DEFAULT 'Disponible',
  `conducteur` VARCHAR(100),
  `foreuse_id` VARCHAR(10),
  `projet_actuel` VARCHAR(20),
  `localisation` VARCHAR(100),
  `date_acquisition` DATE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des foreuses
CREATE TABLE IF NOT EXISTS `forage_foreuses` (
  `id` VARCHAR(10) PRIMARY KEY,
  `type` VARCHAR(50) NOT NULL,
  `marque` VARCHAR(50) NOT NULL,
  `modele` VARCHAR(50) NOT NULL,
  `annee` INT NOT NULL,
  `profondeur_max` INT NOT NULL,
  `heures_totales` INT DEFAULT 0,
  `statut` VARCHAR(20) DEFAULT 'Disponible',
  `vehicule_id` VARCHAR(10),
  `projet_id` VARCHAR(20),
  `prochain_entretien` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`vehicule_id`) REFERENCES `forage_vehicules`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des conducteurs
CREATE TABLE IF NOT EXISTS `forage_conducteurs` (
  `id` VARCHAR(10) PRIMARY KEY,
  `nom` VARCHAR(100) NOT NULL,
  `prenom` VARCHAR(100) NOT NULL,
  `telephone` VARCHAR(20),
  `email` VARCHAR(100),
  `permis` VARCHAR(50),
  `date_embauche` DATE,
  `statut` VARCHAR(20) DEFAULT 'Actif',
  `vehicule_assigne` VARCHAR(10),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`vehicule_assigne`) REFERENCES `forage_vehicules`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des projets
CREATE TABLE IF NOT EXISTS `forage_projets` (
  `numero` VARCHAR(20) PRIMARY KEY,
  `nom` VARCHAR(200) NOT NULL,
  `client` VARCHAR(200) NOT NULL,
  `localisation` VARCHAR(200),
  `latitude` DECIMAL(10, 8),
  `longitude` DECIMAL(11, 8),
  `type_travaux` VARCHAR(100),
  `profondeur_prevue` INT,
  `date_debut` DATE,
  `date_fin_prevue` DATE,
  `date_fin_reelle` DATE,
  `statut` VARCHAR(20) DEFAULT 'En attente',
  `foreuse_assignee` VARCHAR(10),
  `budget` DECIMAL(12, 2),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`foreuse_assignee`) REFERENCES `forage_foreuses`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des entretiens
CREATE TABLE IF NOT EXISTS `forage_entretien` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `vehicule_id` VARCHAR(10),
  `type` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `date_prevue` DATE,
  `date_effectuee` DATE,
  `kilometrage` INT,
  `cout` DECIMAL(10, 2),
  `statut` VARCHAR(20) DEFAULT 'Planifié',
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`vehicule_id`) REFERENCES `forage_vehicules`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des pièces
CREATE TABLE IF NOT EXISTS `forage_pieces` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `numero_piece` VARCHAR(50) NOT NULL,
  `nom` VARCHAR(200) NOT NULL,
  `categorie` VARCHAR(50),
  `vehicule_compatible` VARCHAR(200),
  `quantite_stock` INT DEFAULT 0,
  `seuil_alerte` INT DEFAULT 5,
  `cout_unitaire` DECIMAL(10, 2),
  `fournisseur` VARCHAR(100),
  `emplacement` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des fournisseurs
CREATE TABLE IF NOT EXISTS `forage_fournisseurs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nom` VARCHAR(200) NOT NULL,
  `type` VARCHAR(50),
  `adresse` VARCHAR(300),
  `ville` VARCHAR(100),
  `province` VARCHAR(50),
  `code_postal` VARCHAR(10),
  `telephone` VARCHAR(20),
  `email` VARCHAR(100),
  `distance_km` INT,
  `specialite` VARCHAR(100),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des heures de conduite
CREATE TABLE IF NOT EXISTS `forage_heures_conduite` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `conducteur_id` VARCHAR(10) NOT NULL,
  `vehicule_id` VARCHAR(10) NOT NULL,
  `date` DATE NOT NULL,
  `heure_debut` TIME NOT NULL,
  `heure_fin` TIME,
  `duree_minutes` INT,
  `kilometrage_debut` INT,
  `kilometrage_fin` INT,
  `projet_id` VARCHAR(20),
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`conducteur_id`) REFERENCES `forage_conducteurs`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vehicule_id`) REFERENCES `forage_vehicules`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`projet_id`) REFERENCES `forage_projets`(`numero`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table de configuration
CREATE TABLE IF NOT EXISTS `forage_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `cle` VARCHAR(100) UNIQUE NOT NULL,
  `valeur` TEXT,
  `type` VARCHAR(20) DEFAULT 'string',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insérer les paramètres par défaut
INSERT INTO `forage_settings` (`cle`, `valeur`, `type`) VALUES
('nom_entreprise', 'Forage Grenville', 'string'),
('adresse', '', 'string'),
('latitude', '45.6368', 'number'),
('longitude', '-74.6011', 'number'),
('telephone', '', 'string'),
('rayon_alerte_km', '100', 'number'),
('seuil_entretien_km', '500', 'number'),
('notifications_actives', 'true', 'boolean')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;
