-- ============================================================
--  ATA FRANCE — SCHÉMA DE LA BASE DE DONNÉES
-- ============================================================
--
--  Ce fichier crée les "tableaux" (tables) qui stockent durablement
--  les données du site : comptes clients, adresses enregistrées,
--  réservations et factures.
--
--  MODE D'EMPLOI :
--  Dans l'espace client OVH → Bases de données SQL → phpMyAdmin,
--  sélectionnez votre base, puis onglet "Importer", et envoyez ce fichier.
--
--  Encodage utf8mb4 : gère tous les caractères (accents, emojis…).
-- ============================================================



-- ============================================================
--  TABLE : clients
--  Un compte client particulier (créé après la 1re réservation
--  ou volontairement via "Mon compte").
-- ============================================================
CREATE TABLE IF NOT EXISTS clients (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email             VARCHAR(180) NOT NULL,
    mot_de_passe      VARCHAR(255) NULL,          -- haché (jamais en clair) ; NULL si connexion par lien magique uniquement
    prenom            VARCHAR(80)  NOT NULL,
    nom               VARCHAR(80)  NOT NULL,
    telephone         VARCHAR(30)  NOT NULL,

    -- Informations de facturation (pré-remplissent les réservations)
    adresse_facturation VARCHAR(255) NULL,
    societe           VARCHAR(120) NULL,
    siret             VARCHAR(20)  NULL,

    -- Référence du client dans Logipax (rempli lors de la 1re synchro)
    logipax_id_client VARCHAR(60)  NULL,

    -- Jeton Stripe du moyen de paiement enregistré (paiement 1-clic).
    -- On ne stocke JAMAIS le numéro de carte : seulement cette référence Stripe.
    stripe_customer_id VARCHAR(60) 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,

    PRIMARY KEY (id),
    UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
--  TABLE : adresses
--  Les adresses enregistrées par un client (Domicile, Bureau…),
--  proposées en un clic lors des futures réservations.
-- ============================================================
CREATE TABLE IF NOT EXISTS adresses (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id     INT UNSIGNED NOT NULL,
    libelle       VARCHAR(80)  NOT NULL,          -- ex : "Domicile", "Bureau"
    adresse       VARCHAR(255) NOT NULL,
    par_defaut    TINYINT(1)   NOT NULL DEFAULT 0,
    cree_le       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_client (client_id),
    CONSTRAINT fk_adresse_client
        FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
--  TABLE : reservations
--  Une réservation payée sur le site.
-- ============================================================
CREATE TABLE IF NOT EXISTS reservations (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    reference         VARCHAR(20)  NOT NULL,       -- code lisible, ex : "ATA-2026-000123"
    client_id         INT UNSIGNED NULL,           -- NULL si réservation en tant qu'invité

    -- Trajet
    depart_libelle    VARCHAR(180) NOT NULL,       -- ex : "Versailles" ou "Aéroport Roissy CDG T2E"
    depart_adresse    VARCHAR(255) NULL,           -- précisée seulement pour une ville
    arrivee_libelle   VARCHAR(180) NOT NULL,
    arrivee_adresse   VARCHAR(255) NULL,

    date_heure_aller  DATETIME     NOT NULL,
    aller_retour      TINYINT(1)   NOT NULL DEFAULT 0,
    date_heure_retour DATETIME     NULL,

    -- Détails
    vehicule          ENUM('berline','minivan') NOT NULL DEFAULT 'berline',
    passagers         TINYINT UNSIGNED NOT NULL DEFAULT 1,
    bagages           TINYINT UNSIGNED NOT NULL DEFAULT 0,
    numero_vol_train  VARCHAR(30)  NULL,
    commentaire       TEXT         NULL,

    -- Tarif (calculé côté serveur via Mapbox)
    distance_km       DECIMAL(7,2) NULL,
    prix_ttc          DECIMAL(8,2) NOT NULL,

    -- Coordonnées du passager (utiles même pour un invité)
    passager_prenom   VARCHAR(80)  NOT NULL,
    passager_nom      VARCHAR(80)  NOT NULL,
    passager_email    VARCHAR(180) NOT NULL,
    passager_tel      VARCHAR(30)  NOT NULL,

    -- Suivi du paiement et de la synchronisation
    statut            ENUM('en_attente','payee','confirmee','annulee') NOT NULL DEFAULT 'en_attente',
    stripe_payment_id VARCHAR(80)  NULL,
    logipax_id_course VARCHAR(60)  NULL,           -- rempli après envoi à Logipax
    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,

    PRIMARY KEY (id),
    UNIQUE KEY uniq_reference (reference),
    KEY idx_client (client_id),
    KEY idx_statut (statut),
    CONSTRAINT fk_resa_client
        FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
--  TABLE : factures
--  Une facture liée à une réservation, téléchargeable par le client.
-- ============================================================
CREATE TABLE IF NOT EXISTS factures (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    reservation_id INT UNSIGNED NOT NULL,
    client_id      INT UNSIGNED NULL,
    numero         VARCHAR(30)  NOT NULL,          -- ex : "F-2026-000123"

    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,   -- transport de personnes : 10 %

    chemin_pdf     VARCHAR(255) NULL,              -- emplacement du PDF généré
    emise_le       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uniq_numero (numero),
    KEY idx_reservation (reservation_id),
    KEY idx_client (client_id),
    CONSTRAINT fk_facture_resa
        FOREIGN KEY (reservation_id) REFERENCES reservations (id) ON DELETE CASCADE,
    CONSTRAINT fk_facture_client
        FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
--  TABLE : sessions
--  Les connexions actives des clients (jetons de session sécurisés).
-- ============================================================
CREATE TABLE IF NOT EXISTS sessions (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id    INT UNSIGNED NOT NULL,
    jeton        VARCHAR(128) NOT NULL,            -- jeton aléatoire, haché
    expire_le    DATETIME     NOT NULL,
    cree_le      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uniq_jeton (jeton),
    KEY idx_client (client_id),
    CONSTRAINT fk_session_client
        FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
--  TABLE : tarification
--  La grille tarifaire, modifiable par l'administrateur (vous),
--  sans toucher au code — utile car le carburant varie.
--  Une seule ligne "active" à la fois.
-- ============================================================
CREATE TABLE IF NOT EXISTS tarification (
    id                    INT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- Tarifs minimums
    minimum_berline       DECIMAL(6,2) NOT NULL DEFAULT 65.00,
    minimum_minivan       DECIMAL(6,2) NOT NULL DEFAULT 75.00,

    -- Prix au kilomètre par tranche
    prix_km_tranche1      DECIMAL(5,2) NOT NULL DEFAULT 1.75,   -- 0 à 100 km
    prix_km_tranche2      DECIMAL(5,2) NOT NULL DEFAULT 1.45,   -- 101 à 200 km
    prix_km_tranche3      DECIMAL(5,2) NOT NULL DEFAULT 1.30,   -- au-delà de 200 km
    seuil_tranche1        INT NOT NULL DEFAULT 100,
    seuil_tranche2        INT NOT NULL DEFAULT 200,

    -- Majoration minivan (en pourcentage du calcul kilométrique)
    majoration_minivan    DECIMAL(5,2) NOT NULL DEFAULT 25.00,

    -- Options
    prix_plancher_actif   TINYINT(1)   NOT NULL DEFAULT 1,      -- empêche qu'un trajet long coûte moins qu'un court
    coefficient_route     DECIMAL(4,2) NOT NULL DEFAULT 1.25,   -- utilisé seulement si Mapbox indisponible

    actif                 TINYINT(1)   NOT NULL DEFAULT 1,
    modifie_le            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- On insère la grille tarifaire de départ (vos tarifs validés).
INSERT INTO tarification (id, minimum_berline, minimum_minivan)
VALUES (1, 65.00, 75.00)
ON DUPLICATE KEY UPDATE id = id;


-- ============================================================
--  TABLE : gares_bloquees
--  Les gares bloquées sur certaines plages horaires
--  (paramétrable en administration).
-- ============================================================
CREATE TABLE IF NOT EXISTS gares_bloquees (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    gare_nom      VARCHAR(180) NOT NULL,
    code_postal   VARCHAR(10)  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,

    PRIMARY KEY (id),
    KEY idx_gare (gare_nom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
--  TABLE : journal_calculs
--  Trace des calculs de distance (pour la limitation anti-abus).
--  Permet de compter les calculs par visiteur et par minute.
-- ============================================================
CREATE TABLE IF NOT EXISTS journal_calculs (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    empreinte    VARCHAR(64)  NOT NULL,            -- identifiant anonyme du visiteur (IP hachée)
    client_id    INT UNSIGNED NULL,               -- renseigné si le visiteur est connecté
    cree_le      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_empreinte_date (empreinte, cree_le)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
