-- version OVH
-- https://www.phpmyadmin.net/
--
-- Hôte : fa640288-001.eu.clouddb.ovh.net:35376
-- Généré le : dim. 02 avr. 2023 à 11:23
-- Version du serveur : 8.0.32
-- Version de PHP : 7.4.33

START TRANSACTION;



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

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

--
-- Structure de la table `client`
--

CREATE TABLE `client` (
  `id` int NOT NULL,
  `identifiant` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `idportefeuille` int NOT NULL,
  `client` varchar(100) NOT NULL,
  `naturejuridique` varchar(100) DEFAULT NULL,
  `siret` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `siren` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idape` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idtva` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `adresse` varchar(100) NOT NULL,
  `codepostal` int NOT NULL,
  `ville` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `pays` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `tauxtva` int DEFAULT NULL,
  `idconditionspaiement` int NOT NULL,
  `emailfacturation` varchar(50) DEFAULT NULL,
  `contact` varchar(50) DEFAULT NULL,
  `mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `customfieldint` int DEFAULT NULL,
  `customfieldstringfirst` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfieldstringsecond` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfielddate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Structure de la table `collaborateur`
--

CREATE TABLE `collaborateur` (
  `id` int NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `customfieldint` int DEFAULT NULL,
  `customfieldstringfirst` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfieldstringsecond` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfielddate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Structure de la table `commande`
--

CREATE TABLE `commande` (
  `id` int NOT NULL,
  `identifiant` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `datecommande` date NOT NULL,
  `montantcommande` int DEFAULT NULL,
  `url` varchar(100) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `customfieldint` int DEFAULT NULL,
  `customfieldstringfirst` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfieldstringsecond` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfielddate` date DEFAULT NULL,
  `idclient` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Structure de la table `conditionspaiement`
--

CREATE TABLE `conditionspaiement` (
  `id` int NOT NULL,
  `identifiant` varchar(50) NOT NULL,
  `label` varchar(50) NOT NULL,
  `conditions` int NOT NULL,
  `customfieldint` int DEFAULT NULL,
  `customfieldstringfirst` varchar(100) DEFAULT NULL,
  `customfieldstringsecond` varchar(100) DEFAULT NULL,
  `customfielddate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Structure de la table `invoice`
--

CREATE TABLE `invoice` (
  `id` int NOT NULL,
  `reference` varchar(100) NOT NULL,
  `statut` varchar(100) NOT NULL,
  `startbillingperiod` date NOT NULL,
  `endbillingperiod` date NOT NULL,
  `billingdate` date NOT NULL,
  `duedate` date NOT NULL,
  `identrepriseentreprise` int NOT NULL,
  `entreprise` varchar(100) NOT NULL,
  `entreprisenaturejuridique` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entreprisesiret` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entreprisesiren` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entrepriseidnda` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entrepriseidape` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entrepriseidtva` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entreprisercs` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entreprisecapital` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entrepriseadresse` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `entreprisecodepostal` int NOT NULL,
  `entrepriseville` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `entreprisepays` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entreprisecontact` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entreprisemobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entrepriseemail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `entreprisebanque` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `entrepriseiban` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `entreprisebic` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `idclientclient` int NOT NULL,
  `clientreference` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `clientportefeuille` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idconditionspaiement` int NOT NULL,
  `clientconditionspaiement` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `client` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `clientidtva` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `tauxtva` int NOT NULL,
  `clientadresse` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `clientcodepostal` int NOT NULL,
  `clientville` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `clientpays` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `clientemailfacturation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `clientcontact` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `clientmobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idcommande` int DEFAULT NULL,
  `commandereference` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idproduitfirst` int NOT NULL,
  `produitdescriptionfirst` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `produitquantityfirst` int NOT NULL,
  `produitpufirst` int NOT NULL,
  `produittotalhtfirst` int NOT NULL,
  `produitcustomfieldintfirst` int DEFAULT NULL,
  `produitcustomfieldstringfirst` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idproduitsecond` int DEFAULT NULL,
  `produitdescriptionsecond` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `produitquantitysecond` int DEFAULT NULL,
  `produitpusecond` int DEFAULT NULL,
  `produittotalhtsecond` int DEFAULT NULL,
  `produitcustomfieldintsecond` int DEFAULT NULL,
  `produitcustomfieldstringsecond` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idproduitthird` int DEFAULT NULL,
  `produitdescriptionthird` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `produitquantitythird` int DEFAULT NULL,
  `produitputhird` int DEFAULT NULL,
  `produittotalhtthird` int DEFAULT NULL,
  `produitcustomfieldintthird` int DEFAULT NULL,
  `produitcustomfieldstringthird` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idproduitfourth` int DEFAULT NULL,
  `produitdescriptionfourth` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `produitquantityfourth` int DEFAULT NULL,
  `produitpufourth` int DEFAULT NULL,
  `produittotalhtfourth` int DEFAULT NULL,
  `produitcustomfieldintfourth` int DEFAULT NULL,
  `produitcustomfieldstringfourth` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `idproduitfifth` int DEFAULT NULL,
  `produitdescriptionfifth` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `produitquantityfifth` int DEFAULT NULL,
  `produitpufifth` int DEFAULT NULL,
  `produittotalhtfifth` int DEFAULT NULL,
  `produitcustomfieldintfifth` int DEFAULT NULL,
  `produitcustomfieldstringfifth` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `totalht` int NOT NULL,
  `totaltva` int NOT NULL,
  `totalttc` int NOT NULL,
  `soldepayer` int NOT NULL,
  `customfieldint` int DEFAULT NULL,
  `customfieldstringfirst` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfieldstringsecond` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfielddate` date DEFAULT NULL,
  `commentaires` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Structure de la table `portefeuille`
--

CREATE TABLE `portefeuille` (
  `id` int NOT NULL,
  `identifiant` varchar(50) NOT NULL,
  `label` varchar(50) NOT NULL,
  `customfieldint` int DEFAULT NULL,
  `customfieldstringfirst` varchar(100) DEFAULT NULL,
  `customfieldstringsecond` varchar(100) DEFAULT NULL,
  `customfielddate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Structure de la table `produit`
--

CREATE TABLE `produit` (
  `id` int NOT NULL,
  `identifiant` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `label` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `unit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `tarif` int NOT NULL,
  `tvaapplicable` int DEFAULT NULL,
  `customfieldint` int DEFAULT NULL,
  `customfieldstringfirst` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfieldstringsecond` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customfielddate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Index pour la table `client`
--
ALTER TABLE `client`
  ADD PRIMARY KEY (`id`),
  ADD KEY `FK-client-portefeuille` (`idportefeuille`),
  ADD KEY `FK-client-conditionspaiement` (`idconditionspaiement`);

--
-- Index pour la table `collaborateur`
--
ALTER TABLE `collaborateur`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `commande`
--
ALTER TABLE `commande`
  ADD PRIMARY KEY (`id`),
  ADD KEY `FK-commande-client` (`idclient`);

--
-- Index pour la table `conditionspaiement`
--
ALTER TABLE `conditionspaiement`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `invoice`
--
ALTER TABLE `invoice`
  ADD PRIMARY KEY (`id`),
  ADD KEY `FK-invoice-entreprise` (`identrepriseentreprise`),
  ADD KEY `FK-invoice-client` (`idclientclient`),
  ADD KEY `FK-invoice-produit-second-produit` (`idproduitsecond`),
  ADD KEY `FK-invoice-produit-third-produit` (`idproduitthird`),
  ADD KEY `FK-invoice-produit-fourth-produit` (`idproduitfourth`),
  ADD KEY `FK-invoice-produit-fifth-produit` (`idproduitfifth`),
  ADD KEY `FK-invoice-produit-first-produit` (`idproduitfirst`) USING BTREE,
  ADD KEY `FK-invoice-conditionspaiement` (`idconditionspaiement`),
  ADD KEY `FK-invoice-commande` (`idcommande`);

--
-- Index pour la table `portefeuille`
--
ALTER TABLE `portefeuille`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `produit`
--
ALTER TABLE `produit`
  ADD PRIMARY KEY (`id`);

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

--
-- AUTO_INCREMENT pour la table `client`
--
ALTER TABLE `client`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `collaborateur`
--
ALTER TABLE `collaborateur`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `commande`
--
ALTER TABLE `commande`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `conditionspaiement`
--
ALTER TABLE `conditionspaiement`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `invoice`
--
ALTER TABLE `invoice`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `portefeuille`
--
ALTER TABLE `portefeuille`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `produit`
--
ALTER TABLE `produit`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

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

--
-- Contraintes pour la table `client`
--
ALTER TABLE `client`
  ADD CONSTRAINT `FK-client-conditionspaiement` FOREIGN KEY (`idconditionspaiement`) REFERENCES `conditionspaiement` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-client-portefeuille` FOREIGN KEY (`idportefeuille`) REFERENCES `portefeuille` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

--
-- Contraintes pour la table `commande`
--
ALTER TABLE `commande`
  ADD CONSTRAINT `FK-commande-client` FOREIGN KEY (`idclient`) REFERENCES `client` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

--
-- Contraintes pour la table `invoice`
--
ALTER TABLE `invoice`
  ADD CONSTRAINT `FK-invoice-client` FOREIGN KEY (`idclientclient`) REFERENCES `client` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-commande` FOREIGN KEY (`idcommande`) REFERENCES `commande` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-conditionspaiement` FOREIGN KEY (`idconditionspaiement`) REFERENCES `conditionspaiement` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-entreprise` FOREIGN KEY (`identrepriseentreprise`) REFERENCES `entreprise` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-produit-fifth-produit` FOREIGN KEY (`idproduitfifth`) REFERENCES `produit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-produit-first-produit` FOREIGN KEY (`idproduitfirst`) REFERENCES `produit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-produit-fourth-produit` FOREIGN KEY (`idproduitfourth`) REFERENCES `produit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-produit-second-produit` FOREIGN KEY (`idproduitsecond`) REFERENCES `produit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `FK-invoice-produit-third-produit` FOREIGN KEY (`idproduitthird`) REFERENCES `produit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
COMMIT;

