-- phpMyAdmin SQL Dump
-- version 5.2.3
-- https://www.phpmyadmin.net/
--
-- Hôte : atafranc2026.mysql.db
-- Généré le : mar. 01 sep. 2026 à 17:14
-- Version du serveur : 8.4.11-11
-- Version de PHP : 8.4.22

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

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

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

--
-- Structure de la table `adresses`
--

CREATE TABLE `adresses` (
  `id` int UNSIGNED NOT NULL,
  `client_id` int UNSIGNED NOT NULL,
  `libelle` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `adresse` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rue` varchar(160) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'N° et nom de rue (dans la ville tarifée)',
  `ville` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Ville issue de la liste tarifee CITIES (fait foi pour le tarif)',
  `code_postal` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Code postal (optionnel, garde-fou doux)',
  `par_defaut` tinyint(1) NOT NULL DEFAULT '0',
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `avoirs`
--

CREATE TABLE `avoirs` (
  `id` int UNSIGNED NOT NULL,
  `facture_id` int UNSIGNED NOT NULL,
  `reservation_id` int UNSIGNED NOT NULL,
  `client_id` int UNSIGNED DEFAULT NULL,
  `numero` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `montant_ht` decimal(8,2) NOT NULL,
  `montant_tva` decimal(8,2) NOT NULL,
  `montant_ttc` decimal(8,2) NOT NULL,
  `taux_tva` decimal(4,2) NOT NULL DEFAULT '10.00',
  `motif` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emis_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `blocages_disponibilite`
--

CREATE TABLE `blocages_disponibilite` (
  `id` int UNSIGNED NOT NULL,
  `date_debut` date NOT NULL,
  `date_fin` date NOT NULL,
  `heure_debut` time NOT NULL DEFAULT '00:00:00',
  `heure_fin` time NOT NULL DEFAULT '23:59:00',
  `journee_entiere` tinyint(1) NOT NULL DEFAULT '1',
  `motif` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `actif` tinyint(1) NOT NULL DEFAULT '1',
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `clients`
--

CREATE TABLE `clients` (
  `id` int UNSIGNED NOT NULL,
  `email` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mot_de_passe` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `prenom` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nom` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `telephone` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `adresse_facturation` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `societe` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `siret` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `logipax_id_client` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stripe_customer_id` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_verifie` tinyint(1) NOT NULL DEFAULT '0',
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifie_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `configuration`
--

CREATE TABLE `configuration` (
  `cle` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `valeur` text COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `factures`
--

CREATE TABLE `factures` (
  `id` int UNSIGNED NOT NULL,
  `reservation_id` int UNSIGNED NOT NULL,
  `client_id` int UNSIGNED DEFAULT NULL,
  `numero` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `montant_ht` decimal(8,2) NOT NULL,
  `montant_tva` decimal(8,2) NOT NULL,
  `montant_ttc` decimal(8,2) NOT NULL,
  `taux_tva` decimal(4,2) NOT NULL DEFAULT '10.00',
  `chemin_pdf` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emise_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `fidelite_paliers`
--

CREATE TABLE `fidelite_paliers` (
  `id` int UNSIGNED NOT NULL,
  `libelle` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `seuil_courses` int UNSIGNED NOT NULL DEFAULT '0',
  `taux_cashback` decimal(5,2) NOT NULL DEFAULT '0.00',
  `actif` tinyint(1) NOT NULL DEFAULT '1',
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifie_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `gares_bloquees`
--

CREATE TABLE `gares_bloquees` (
  `id` int UNSIGNED NOT NULL,
  `gare_nom` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code_postal` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `heure_debut` time NOT NULL DEFAULT '07:00:00',
  `heure_fin` time NOT NULL DEFAULT '20:00:00',
  `actif` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `journal_calculs`
--

CREATE TABLE `journal_calculs` (
  `id` int UNSIGNED NOT NULL,
  `empreinte` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_id` int UNSIGNED DEFAULT NULL,
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `portefeuille_mouvements`
--

CREATE TABLE `portefeuille_mouvements` (
  `id` int UNSIGNED NOT NULL,
  `client_id` int UNSIGNED NOT NULL,
  `montant` decimal(8,2) NOT NULL,
  `type` enum('credit','debit','annulation') COLLATE utf8mb4_unicode_ci NOT NULL, -- CORRIGE 01/09/2026 : ajout 'annulation' (Portefeuille::annulerCredit)
  `motif` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reservation_id` int UNSIGNED DEFAULT NULL,
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `annule_le` datetime DEFAULT NULL,
  `annule_par_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `reinitialisations_mdp`
--

CREATE TABLE `reinitialisations_mdp` (
  `id` int UNSIGNED NOT NULL,
  `client_id` int UNSIGNED NOT NULL,
  `jeton` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expire_le` datetime NOT NULL,
  `utilise` tinyint(1) NOT NULL DEFAULT '0',
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `reservations`
--

CREATE TABLE `reservations` (
  `id` int UNSIGNED NOT NULL,
  `reference` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_id` int UNSIGNED DEFAULT NULL,
  `depart_libelle` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `depart_adresse` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `arrivee_libelle` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `arrivee_adresse` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `retour_depose_adresse` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_heure_aller` datetime NOT NULL,
  `aller_retour` tinyint(1) NOT NULL DEFAULT '0',
  `date_heure_retour` datetime DEFAULT NULL,
  `vehicule` enum('berline','minivan') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'berline',
  `passagers` tinyint UNSIGNED NOT NULL DEFAULT '1',
  `bagages` tinyint UNSIGNED NOT NULL DEFAULT '0',
  `numero_vol_train` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `retour_numero_vol_train` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `commentaire` text COLLATE utf8mb4_unicode_ci,
  `distance_km` decimal(7,2) DEFAULT NULL,
  `prix_ttc` decimal(8,2) NOT NULL,
  `credit_utilise` decimal(8,2) NOT NULL DEFAULT '0.00',
  `passager_prenom` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `passager_nom` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `passager_email` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `passager_tel` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `statut` enum('en_attente','payee','confirmee','annulee') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'en_attente',
  `stripe_payment_id` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `logipax_id_course` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `envoye_logipax` tinyint(1) NOT NULL DEFAULT '0',
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifie_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `sessions`
--

CREATE TABLE `sessions` (
  `id` int UNSIGNED NOT NULL,
  `client_id` int UNSIGNED NOT NULL,
  `jeton` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expire_le` datetime NOT NULL,
  `cree_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Structure de la table `tarification`
--

CREATE TABLE `tarification` (
  `id` int UNSIGNED NOT NULL,
  `minimum_berline` decimal(6,2) NOT NULL DEFAULT '65.00',
  `minimum_minivan` decimal(6,2) NOT NULL DEFAULT '75.00',
  `seuil_km` int NOT NULL DEFAULT '100',
  `berline_km_court` decimal(5,2) NOT NULL DEFAULT '1.90',
  `berline_km_long` decimal(5,2) NOT NULL DEFAULT '1.50',
  `minivan_km_court` decimal(5,2) NOT NULL DEFAULT '2.20',
  `minivan_km_long` decimal(5,2) NOT NULL DEFAULT '1.80',
  `prix_km_tranche1` decimal(5,2) NOT NULL DEFAULT '1.75',
  `prix_km_tranche2` decimal(5,2) NOT NULL DEFAULT '1.45',
  `prix_km_tranche3` decimal(5,2) NOT NULL DEFAULT '1.30',
  `seuil_tranche1` int NOT NULL DEFAULT '100',
  `seuil_tranche2` int NOT NULL DEFAULT '200',
  `majoration_minivan` decimal(5,2) NOT NULL DEFAULT '25.00',
  `prix_plancher_actif` tinyint(1) NOT NULL DEFAULT '1',
  `coefficient_route` decimal(4,2) NOT NULL DEFAULT '1.25',
  `actif` tinyint(1) NOT NULL DEFAULT '1',
  `modifie_le` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Index pour la table `adresses`
--
ALTER TABLE `adresses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_client` (`client_id`),
  ADD KEY `idx_ville` (`ville`);

--
-- Index pour la table `avoirs`
--
ALTER TABLE `avoirs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_numero_avoir` (`numero`),
  ADD KEY `idx_facture` (`facture_id`),
  ADD KEY `idx_reservation_avoir` (`reservation_id`),
  ADD KEY `idx_client_avoir` (`client_id`);

--
-- Index pour la table `blocages_disponibilite`
--
ALTER TABLE `blocages_disponibilite`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_periode` (`date_debut`,`date_fin`);

--
-- Index pour la table `clients`
--
ALTER TABLE `clients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_email` (`email`);

--
-- Index pour la table `configuration`
--
ALTER TABLE `configuration`
  ADD PRIMARY KEY (`cle`);

--
-- Index pour la table `factures`
--
ALTER TABLE `factures`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_numero` (`numero`),
  ADD KEY `idx_reservation` (`reservation_id`),
  ADD KEY `idx_client` (`client_id`);

--
-- Index pour la table `fidelite_paliers`
--
ALTER TABLE `fidelite_paliers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_seuil` (`seuil_courses`);

--
-- Index pour la table `gares_bloquees`
--
ALTER TABLE `gares_bloquees`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_gare` (`gare_nom`);

--
-- Index pour la table `journal_calculs`
--
ALTER TABLE `journal_calculs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_empreinte_date` (`empreinte`,`cree_le`);

--
-- Index pour la table `portefeuille_mouvements`
--
ALTER TABLE `portefeuille_mouvements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_client_portefeuille` (`client_id`),
  ADD KEY `idx_resa_portefeuille` (`reservation_id`),
  ADD KEY `idx_annule` (`annule_le`);

--
-- Index pour la table `reinitialisations_mdp`
--
ALTER TABLE `reinitialisations_mdp`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_jeton` (`jeton`),
  ADD KEY `idx_client` (`client_id`);

--
-- Index pour la table `reservations`
--
ALTER TABLE `reservations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_reference` (`reference`),
  ADD KEY `idx_client` (`client_id`),
  ADD KEY `idx_statut` (`statut`);

--
-- Index pour la table `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_jeton` (`jeton`),
  ADD KEY `idx_client` (`client_id`);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Contraintes pour la table `adresses`
--
ALTER TABLE `adresses`
  ADD CONSTRAINT `fk_adresse_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;

--
-- Contraintes pour la table `avoirs`
--
ALTER TABLE `avoirs`
  ADD CONSTRAINT `fk_avoir_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_avoir_facture` FOREIGN KEY (`facture_id`) REFERENCES `factures` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_avoir_resa` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE;

--
-- Contraintes pour la table `factures`
--
ALTER TABLE `factures`
  ADD CONSTRAINT `fk_facture_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_facture_resa` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE;

--
-- Contraintes pour la table `portefeuille_mouvements`
--
ALTER TABLE `portefeuille_mouvements`
  ADD CONSTRAINT `fk_portefeuille_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_portefeuille_resa` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE SET NULL;

--
-- Contraintes pour la table `reservations`
--
ALTER TABLE `reservations`
  ADD CONSTRAINT `fk_resa_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL;

--
-- Contraintes pour la table `sessions`
--
ALTER TABLE `sessions`
  ADD CONSTRAINT `fk_session_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
