-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Hôte : localhost
-- Généré le : mar. 26 nov. 2024 à 12:02
-- Version du serveur : 8.0.31
-- Version de PHP : 8.3.11

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

--
-- Base de données : `gbrc_db`
--

-- --------------------------------------------------------

DROP TABLE `cd_garanties_pj`;
DROP TABLE `cd_garanties`;

--
-- Structure de la table `cd_garanties`
--


CREATE TABLE `cd_garanties` (
  `id_garantie` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `demande_id` bigint UNSIGNED NOT NULL,
  `nature` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `marque` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `annee_acqui` char(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caracteristique` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `valeur` double(15,2) DEFAULT '0.00',
  `type_garantie` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `groupe` enum('mat','gag','hyp','fin','aut') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `emetteur` enum('1','2') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '1:Emprunteur, 2:Caution',
  `num_matricul` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `num_moteur` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `num_serie` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `localisation` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Structure de la table `cd_garanties_pj`
--

CREATE TABLE `cd_garanties_pj` (
  `id_pj` bigint NOT NULL,
  `garantie_id` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `original_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Index pour les tables déchargées
--

--
-- Index pour la table `cd_garanties`
--
ALTER TABLE `cd_garanties`
  ADD PRIMARY KEY (`id_garantie`),
  ADD KEY `demande_id` (`demande_id`),
  ADD KEY `type_garantie` (`type_garantie`);

--
-- Index pour la table `cd_garanties_pj`
--
ALTER TABLE `cd_garanties_pj`
  ADD PRIMARY KEY (`id_pj`),
  ADD KEY `garantie_id` (`garantie_id`);

--
-- AUTO_INCREMENT pour les tables déchargées
--

--
-- AUTO_INCREMENT pour la table `cd_garanties_pj`
--
ALTER TABLE `cd_garanties_pj`
  MODIFY `id_pj` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- Contraintes pour les tables déchargées
--

--
-- Contraintes pour la table `cd_garanties`
--
ALTER TABLE `cd_garanties`
  ADD CONSTRAINT `cd_garanties_ibfk_1` FOREIGN KEY (`demande_id`) REFERENCES `cd_demandes` (`id_demande`) ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD CONSTRAINT `cd_garanties_ibfk_2` FOREIGN KEY (`type_garantie`) REFERENCES `cd_type_garanties` (`id_type`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Contraintes pour la table `cd_garanties_pj`
--
ALTER TABLE `cd_garanties_pj`
  ADD CONSTRAINT `cd_garanties_pj_ibfk_1` FOREIGN KEY (`garantie_id`) REFERENCES `cd_garanties` (`id_garantie`) ON DELETE RESTRICT ON UPDATE CASCADE;
COMMIT;
