CREATE DATABASE IF NOT EXISTS lvj_inscriptions CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE lvj_inscriptions;


CREATE TABLE groupes (
id INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(120) NOT NULL,
description TEXT NULL,
actif TINYINT(1) DEFAULT 1,
date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE destinations (
id INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(120) NOT NULL,
description TEXT NULL,
pays VARCHAR(80) NULL,
actif TINYINT(1) DEFAULT 1,
image_url VARCHAR(255) NULL
);


CREATE TABLE sejours (
id INT AUTO_INCREMENT PRIMARY KEY,
id_groupe INT NOT NULL,
id_destination INT NOT NULL,
titre VARCHAR(160) NOT NULL,
description TEXT NULL,
date_debut DATE NOT NULL,
date_fin DATE NOT NULL,
prix DECIMAL(10,2) NOT NULL,
places INT NOT NULL DEFAULT 0,
statut ENUM('ouvert','complet','annule') DEFAULT 'ouvert',
options_json JSON NULL,
CONSTRAINT fk_sej_g FOREIGN KEY (id_groupe) REFERENCES groupes(id),
CONSTRAINT fk_sej_d FOREIGN KEY (id_destination) REFERENCES destinations(id)
);


CREATE TABLE voyageurs (
id INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(120) NOT NULL,
prenom VARCHAR(120) NOT NULL,
email VARCHAR(160) NOT NULL,
telephone VARCHAR(40) NULL,
adresse TEXT NULL,
date_naissance DATE NULL,
passeport_num VARCHAR(80) NULL,
assurance_fichier VARCHAR(255) NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_voy_email (email)
);


CREATE TABLE inscriptions (
id INT AUTO_INCREMENT PRIMARY KEY,
id_voyageur INT NOT NULL,
id_sejour INT NOT NULL,
date_inscription TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
statut ENUM('en_attente','valide','annule') DEFAULT 'en_attente',
contrat_signe TINYINT(1) DEFAULT 0,
contrat_fichier VARCHAR(255) NULL,
acompte DECIMAL(10,2) DEFAULT 0,
solde DECIMAL(10,2) DEFAULT 0,
paiement_statut ENUM('non_paye','acompte_paye','solde_paye') DEFAULT 'non_paye',
mode_paiement ENUM('cb','virement','cheque') DEFAULT 'cb',
piece_identite VARCHAR(255) NULL,
options_json JSON NULL,
montant_total DECIMAL(10,2) NULL,
CONSTRAINT fk_ins_v FOREIGN KEY (id_voyageur) REFERENCES voyageurs(id),
CONSTRAINT fk_ins_s FOREIGN KEY (id_sejour) REFERENCES sejours(id)
);


CREATE TABLE paiements (
id INT AUTO_INCREMENT PRIMARY KEY,
id_inscription INT NOT NULL,
montant DECIMAL(10,2) NOT NULL,
type ENUM('acompte','solde') NOT NULL,
statut ENUM('en_attente','succeeded','failed') DEFAULT 'en_attente',
date_paiement TIMESTAMP NULL,
reference_transaction VARCHAR(160) NULL,
CONSTRAINT fk_pay_i FOREIGN KEY (id_inscription) REFERENCES inscriptions(id)
);


-- Vues utiles
CREATE OR REPLACE VIEW v_sejours_places AS
SELECT s.id, s.titre, s.date_debut, s.date_fin, s.places,
(s.places - COALESCE((SELECT COUNT(*) FROM inscriptions i WHERE i.id_sejour = s.id AND i.statut <> 'annule'),0)) AS places_restantes
FROM sejours s;
