-- --------------------------------------------------------
-- Hôte:                         192.168.1.203
-- Version du serveur:           10.5.29-MariaDB-0+deb11u1 - Debian 11
-- SE du serveur:                debian-linux-gnu
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------



-- Listage de la structure de la base pour gestcar
DROP DATABASE IF EXISTS `gestcar`;
CREATE DATABASE IF NOT EXISTS `gestcar` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `gestcar`;

-- Listage de la structure de table gestcar. caracteristiques
CREATE TABLE IF NOT EXISTS `caracteristiques` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_id` int(11) DEFAULT NULL,
  `conso` int(11) DEFAULT NULL,
  `conso_reel` int(11) DEFAULT NULL,
  `couple` int(11) DEFAULT NULL,
  `puissance_ch` int(11) DEFAULT NULL,
  `puissance_fiscale` int(11) DEFAULT NULL,
  `moteur` varchar(100) DEFAULT NULL,
  `coffre` int(11) DEFAULT NULL,
  `pneux` varchar(50) DEFAULT NULL,
  `poids` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_caracteristiques_vehicles` (`vehicle_id`) USING BTREE,
  CONSTRAINT `FK_caracteristiques_vehicles` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Listage des données de la table gestcar.caracteristiques : ~2 rows (environ)
DELETE FROM `caracteristiques`;
INSERT INTO `caracteristiques` (`id`, `vehicle_id`, `conso`, `conso_reel`, `couple`, `puissance_ch`, `puissance_fiscale`, `moteur`, `coffre`, `pneux`, `poids`) VALUES
	(1, 1, 6, 7, 170, 125, 6, '1.0 SCTi', 318, '195/60 R15', NULL),
	(2, 3, 7, 10, 320, 140, 8, '2.0 TDI140 CR', 885, '225/50 R17', NULL),
	(3, 2, 8, 12, 220, 200, 11, '1.6 TCe 200', 572, '245/40 R19', NULL);

-- Listage de la structure de table gestcar. maintenance
CREATE TABLE IF NOT EXISTS `maintenance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `km` int(11) DEFAULT NULL,
  `cout` decimal(10,2) NOT NULL,
  `num_facture` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `facture_pdf` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `maintenance_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_vehicle` (`vehicle_id`),
  KEY `idx_date` (`date`),
  KEY `maintenance_type_id` (`maintenance_type_id`),
  CONSTRAINT `maintenance_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `maintenance_ibfk_2` FOREIGN KEY (`maintenance_type_id`) REFERENCES `maintenance_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Listage des données de la table gestcar.maintenance : ~31 rows (environ)
DELETE FROM `maintenance`;
INSERT INTO `maintenance` (`id`, `vehicle_id`, `date`, `km`, `cout`, `num_facture`, `description`, `facture_pdf`, `created_at`, `updated_at`, `maintenance_type_id`) VALUES
	(1, 1, '2023-12-18', 75000, 675.56, NULL, 'Pneu AV + Rempl phare Arriere + CT', NULL, '2026-01-15 11:46:30', '2026-01-15 11:46:30', 3),
	(2, 1, '2025-02-13', 102282, 296.72, NULL, 'Vidange + filtre huile\r\n+ air +habitacle', NULL, '2026-01-15 11:47:42', '2026-01-15 11:47:42', 2),
	(3, 2, '2023-06-24', 84740, 58.09, NULL, 'Vidange filtre (air huile)	\r\n', NULL, '2026-01-15 13:00:24', '2026-01-15 13:00:24', 2),
	(4, 3, '2018-05-07', 110000, 392.60, NULL, 'Attelage', NULL, '2026-01-15 13:22:57', '2026-01-15 17:42:15', 11),
	(6, 2, '2023-06-25', 84740, 574.76, NULL, 'Carte grise', NULL, '2026-01-15 17:01:54', '2026-01-15 17:01:54', 11),
	(7, 2, '2023-12-30', 90000, 341.20, NULL, '4 x Pneux 4 saison', NULL, '2026-01-15 17:24:57', '2026-01-15 17:24:57', 3),
	(8, 2, '2024-02-12', 100200, 74.40, NULL, 'montage pneu', NULL, '2026-01-15 17:25:30', '2026-01-15 17:25:30', 3),
	(9, 2, '2024-07-03', 103927, 328.80, NULL, 'Vidange filtre (huile - habitacle)\r\nplaquette AV', NULL, '2026-01-15 17:26:19', '2026-01-15 17:26:19', 2),
	(10, 2, '2025-07-02', 118594, 358.59, NULL, 'Vidange filtre (huile - habitacle)\r\nplaquette AR', NULL, '2026-01-15 17:26:59', '2026-01-15 17:26:59', 2),
	(11, 2, '2025-09-02', 121000, 152.20, NULL, '2 x Pneux 4 saison AV', NULL, '2026-01-15 17:27:42', '2026-01-15 17:27:42', 3),
	(12, 2, '2025-09-05', 121200, 37.20, NULL, 'Montage pneu', NULL, '2026-01-15 17:28:51', '2026-01-15 17:28:51', 3),
	(13, 1, '2026-01-14', 108866, 185.33, NULL, 'joint vase expansion\r\nreglage frein arriere', NULL, '2026-01-15 17:29:45', '2026-01-15 17:29:45', 11),
	(14, 3, '2018-12-12', 109000, 62.00, NULL, 'CT', NULL, '2026-01-15 17:41:32', '2026-01-15 17:41:32', 11),
	(15, 3, '2019-01-19', 110000, 60.34, NULL, 'pb porte laterale', NULL, '2026-01-15 17:43:02', '2026-01-15 17:43:02', 11),
	(16, 3, '2019-06-22', 110300, 299.80, NULL, 'AV', NULL, '2026-01-15 17:43:48', '2026-01-15 17:43:48', 3),
	(17, 3, '2019-07-08', 111000, 112.50, NULL, 'changement', NULL, '2026-01-15 17:44:47', '2026-01-15 17:44:47', 5),
	(18, 3, '2019-09-13', 111100, 284.53, NULL, 'leve vitre', NULL, '2026-01-15 17:45:32', '2026-01-15 17:45:32', 11),
	(19, 3, '2019-11-20', 111200, 582.72, NULL, 'Distribution', NULL, '2026-01-15 17:46:16', '2026-01-15 17:46:16', 11),
	(20, 3, '2020-02-05', 112000, 427.52, NULL, 'Pneux + Plaquettes', NULL, '2026-01-15 17:47:08', '2026-01-15 17:47:08', 3),
	(21, 3, '2020-06-03', 112500, 251.20, NULL, 'Disques AV', NULL, '2026-01-15 17:47:51', '2026-01-15 17:47:51', 4),
	(22, 3, '2021-08-13', 113000, 489.60, NULL, 'Neman', NULL, '2026-01-15 17:48:27', '2026-01-15 17:48:27', 11),
	(23, 3, '2021-09-21', 113500, 501.07, NULL, 'Calculateur', NULL, '2026-01-15 17:49:12', '2026-01-15 17:49:12', 11),
	(24, 3, '2021-09-21', 113000, 260.97, NULL, 'V', NULL, '2026-01-15 17:50:01', '2026-01-15 17:50:01', 2),
	(25, 3, '2022-04-13', 115000, 98.17, NULL, 'recharge', NULL, '2026-01-15 17:50:47', '2026-01-15 17:50:47', 6),
	(26, 3, '2022-06-10', 116000, 461.66, NULL, 'Condenseur', NULL, '2026-01-15 17:51:29', '2026-01-15 17:51:29', 11),
	(27, 3, '2022-08-29', 117000, 258.41, NULL, 'v', NULL, '2026-01-15 17:52:03', '2026-01-15 17:52:03', 2),
	(28, 3, '2022-11-24', 118000, 643.40, NULL, 'AV +AR', NULL, '2026-01-15 17:52:52', '2026-01-15 17:52:52', 3),
	(29, 3, '2023-01-09', 150000, 706.67, NULL, 'FAP', NULL, '2026-01-15 17:53:31', '2026-01-15 17:53:31', 11),
	(30, 3, '2023-01-19', 155000, 1387.08, NULL, 'Catalyseur', NULL, '2026-01-15 17:54:18', '2026-01-15 19:18:26', 11),
	(31, 3, '2023-02-21', 160000, 266.19, NULL, 'SilentBloc', NULL, '2026-01-15 17:54:54', '2026-01-15 17:54:54', 11),
	(32, 1, '2026-01-29', 109000, 1504.56, NULL, 'Kit Distribution\r\ncourroie Accessoire\r\nPompe a eau\r\nNettoyage crepine', NULL, '2026-01-15 18:37:56', '2026-01-15 18:37:56', 9);

-- Listage de la structure de table gestcar. maintenance_types
CREATE TABLE IF NOT EXISTS `maintenance_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `icon` varchar(10) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Listage des données de la table gestcar.maintenance_types : ~11 rows (environ)
DELETE FROM `maintenance_types`;
INSERT INTO `maintenance_types` (`id`, `name`, `icon`, `description`) VALUES
	(1, 'Révision', '🔧', 'Révision complète du véhicule'),
	(2, 'Vidange', '🛢️', 'Changement d\'huile moteur'),
	(3, 'Pneus', '🛞', 'Changement ou réparation des pneus'),
	(4, 'Freins', '🔴', 'Entretien du système de freinage'),
	(5, 'Batterie', '🔋', 'Remplacement batterie'),
	(6, 'Climatisation', '❄️', 'Entretien du climatiseur'),
	(7, 'Filtre air', '💨', 'Changement filtre à air'),
	(8, 'Plaquettes', '⚙️', 'Changement plaquettes de frein'),
	(9, 'Courroie', '🔗', 'Changement courroie de distribution'),
	(10, 'Carburant', '⛽', 'Traitement carburant'),
	(11, 'Autre', '📌', 'Autre entretien');

-- Listage de la structure de table gestcar. sales
CREATE TABLE IF NOT EXISTS `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `km` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `vehicle_id` (`vehicle_id`),
  KEY `user_id` (`user_id`,`vehicle_id`),
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Listage des données de la table gestcar.sales : ~1 rows (environ)
DELETE FROM `sales`;
INSERT INTO `sales` (`id`, `vehicle_id`, `user_id`, `date`, `price`, `km`, `notes`, `created_at`) VALUES
	(1, 3, 1, '2023-06-05', 10000.00, 187000, NULL, '2026-01-15 13:23:20');

-- Listage de la structure de table gestcar. vehicles
CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `marque` varchar(50) NOT NULL,
  `modele` varchar(100) NOT NULL,
  `immatriculation` varchar(10) NOT NULL,
  `prix_achat` decimal(10,2) NOT NULL,
  `km_achat` int(11) NOT NULL,
  `km_actuel` int(11) NOT NULL,
  `date_achat` date NOT NULL,
  `date_vente` date DEFAULT NULL,
  `prix_vente` decimal(10,2) DEFAULT NULL,
  `km_vente` int(11) DEFAULT NULL,
  `is_sold` tinyint(1) DEFAULT 0,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_immat` (`immatriculation`),
  KEY `idx_sold` (`is_sold`),
  CONSTRAINT `vehicles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Listage des données de la table gestcar.vehicles : ~3 rows (environ)
DELETE FROM `vehicles`;
INSERT INTO `vehicles` (`id`, `user_id`, `marque`, `modele`, `immatriculation`, `prix_achat`, `km_achat`, `km_actuel`, `date_achat`, `date_vente`, `prix_vente`, `km_vente`, `is_sold`, `notes`, `created_at`, `updated_at`) VALUES
	(1, 1, 'FORD', 'B-Max', 'DE-519-XM', 9700.00, 58050, 108866, '2019-10-10', NULL, NULL, NULL, 0, NULL, '2026-01-15 10:04:04', '2026-01-19 06:39:07'),
	(2, 1, 'RENAULT', 'Talisman', 'EK-814-FY', 21500.00, 84740, 128594, '2023-06-20', NULL, NULL, NULL, 0, NULL, '2026-01-15 10:04:37', '2026-01-19 06:38:40'),
	(3, 1, 'VOLKSWAGEN', 'Sharan', 'BM-433-NM', 20000.00, 104000, 187000, '2017-12-01', NULL, NULL, NULL, 1, NULL, '2026-01-15 10:35:02', '2026-01-19 06:38:54');

