-- ==============================================================================
-- 🗄️ ÉTAPE 3 : ARCHITECTURE BACK-END (MySQL) - PROJET GLOBAL TOURS
-- Méthodologie : Front-End Odyssey (Push Consult)
-- Architecture type : CMS Headless / EAV (Entity-Attribute-Value)
-- ==============================================================================

-- 1. PARAMÈTRES GLOBAUX (Réglages du site)
-- Stocke les informations génériques (Contact, Réseaux sociaux, Scripts)
CREATE TABLE `gt_options` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `option_name` VARCHAR(191) NOT NULL,
  `option_value` LONGTEXT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_option_name` (`option_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. UTILISATEURS (Administrateurs)
-- Gère les accès au Back-Office
CREATE TABLE `gt_users` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(191) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `display_name` VARCHAR(100) NOT NULL,
  `role` ENUM('admin', 'editor') DEFAULT 'admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. MÉDIATHÈQUE (Fichiers uploadés)
-- Centralise toutes les images et documents du site
CREATE TABLE `gt_media` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `file_size` INT(11) NOT NULL, -- en octets
  `alt_text` VARCHAR(255) DEFAULT NULL, -- Pour le SEO
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. CONTENUS GLOBAUX (Le cœur du CMS)
-- Regroupe TOUS les types de publications : destinations, services, articles, pages, faq
CREATE TABLE `gt_contents` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `author_id` BIGINT(20) UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(191) NOT NULL, -- URL amicale (ex: foire-de-canton)
  `main_content` LONGTEXT DEFAULT NULL, -- Le texte WYSIWYG ou la réponse FAQ
  `excerpt` TEXT DEFAULT NULL, -- Résumé
  `status` ENUM('published', 'draft', 'trash') DEFAULT 'draft',
  `content_type` VARCHAR(50) NOT NULL, -- 'page', 'destination', 'service', 'article', 'faq'
  `cover_image_id` BIGINT(20) UNSIGNED DEFAULT NULL, -- Clé étrangère vers gt_media
  `published_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_type_slug` (`content_type`, `slug`),
  FOREIGN KEY (`author_id`) REFERENCES `gt_users`(`id`),
  FOREIGN KEY (`cover_image_id`) REFERENCES `gt_media`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. MÉTADONNÉES DES CONTENUS (Champs Personnalisés / Pattern EAV)
-- Stocke les données spécifiques (ex: prix d'une destination, icône d'un service, itinéraire JSON)
CREATE TABLE `gt_content_meta` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `content_id` BIGINT(20) UNSIGNED NOT NULL,
  `meta_key` VARCHAR(191) NOT NULL, -- ex: 'price', 'location', 'itinerary_json', 'icon'
  `meta_value` LONGTEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_content_id` (`content_id`),
  KEY `idx_meta_key` (`meta_key`),
  FOREIGN KEY (`content_id`) REFERENCES `gt_contents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. TAXONOMIES (Catégories et Mots-clés)
-- Pour classer le blog (Business, Inspiration) ou les destinations
CREATE TABLE `gt_terms` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `taxonomy` VARCHAR(50) NOT NULL, -- 'category', 'tag', 'destination_type'
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tax_slug` (`taxonomy`, `slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. RELATIONS CONTENUS <-> TAXONOMIES
CREATE TABLE `gt_term_relationships` (
  `content_id` BIGINT(20) UNSIGNED NOT NULL,
  `term_id` BIGINT(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`content_id`, `term_id`),
  FOREIGN KEY (`content_id`) REFERENCES `gt_contents`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`term_id`) REFERENCES `gt_terms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- EXEMPLES DE REQUÊTES POUR COMPRENDRE LA LOGIQUE
-- ==============================================================================

-- Exemple A : Initialiser les réglages du site
INSERT INTO `gt_options` (`option_name`, `option_value`) VALUES 
('site_name', 'Global Tours'),
('contact_email', 'contact@globaltours.com'),
('contact_phone', '+226 70 62 62 74');

-- Exemple B : Récupérer TOUTES les informations d'une Destination (Foire de Canton)
-- (Dans la réalité, ceci est géré par l'ORM en PHP, mais voici la logique SQL)
SELECT 
    c.title AS Destination,
    c.main_content AS Description,
    m.file_path AS Image_Couverture,
    MAX(CASE WHEN meta.meta_key = 'price' THEN meta.meta_value END) AS Prix,
    MAX(CASE WHEN meta.meta_key = 'location' THEN meta.meta_value END) AS Lieu,
    MAX(CASE WHEN meta.meta_key = 'itinerary_json' THEN meta.meta_value END) AS Programme_Complet
FROM gt_contents c
LEFT JOIN gt_media m ON c.cover_image_id = m.id
LEFT JOIN gt_content_meta meta ON c.id = meta.content_id
WHERE c.content_type = 'destination' AND c.slug = 'foire-de-canton'
GROUP BY c.id;

-- Exemple C : Ajouter une FAQ (très simple car pas de meta complexes)
INSERT INTO `gt_contents` (`author_id`, `title`, `slug`, `main_content`, `content_type`, `status`) 
VALUES (1, 'Proposez-vous des facilités de paiement ?', 'faq-paiement', 'Oui, nous comprenons les contraintes...', 'faq', 'published');