-- ============================================================
-- Travel Hotels Module — Full SQL
-- Generated: 2026-07-27
-- ============================================================

-- 1. Countries
CREATE TABLE IF NOT EXISTS `travel_countries` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(10) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Cities
CREATE TABLE IF NOT EXISTS `travel_cities` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `country_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    CONSTRAINT `fk_travel_cities_country` FOREIGN KEY (`country_id`) REFERENCES `travel_countries`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Hotel Categories
CREATE TABLE IF NOT EXISTS `travel_hotel_categories` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `stars_number` TINYINT UNSIGNED NOT NULL DEFAULT 3,
    `color` VARCHAR(20) DEFAULT NULL,
    `icon` VARCHAR(80) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Services
CREATE TABLE IF NOT EXISTS `travel_services` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) DEFAULT NULL,
    `icon` VARCHAR(80) DEFAULT NULL,
    `color` VARCHAR(20) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Conditions
CREATE TABLE IF NOT EXISTS `travel_conditions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) DEFAULT NULL,
    `icon` VARCHAR(80) DEFAULT NULL,
    `color` VARCHAR(20) DEFAULT NULL,
    `badge_type` VARCHAR(40) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Alter travel_hotels — add new columns
ALTER TABLE `travel_hotels`
    ADD COLUMN `country_id` BIGINT UNSIGNED NULL AFTER `id`,
    ADD COLUMN `city_id` BIGINT UNSIGNED NULL AFTER `country_id`,
    ADD COLUMN `category_id` BIGINT UNSIGNED NULL AFTER `city_id`,
    ADD COLUMN `slug` VARCHAR(255) NULL AFTER `name`,
    ADD COLUMN `short_description` VARCHAR(500) NULL AFTER `slug`,
    ADD COLUMN `description` TEXT NULL AFTER `short_description`,
    ADD COLUMN `main_image` VARCHAR(500) NULL AFTER `description`,
    ADD COLUMN `gallery_images` JSON NULL AFTER `main_image`,
    ADD COLUMN `is_active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `gallery_images`,
    ADD COLUMN `has_promotion` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_active`,
    ADD COLUMN `promotion_label` VARCHAR(255) NULL AFTER `has_promotion`,
    ADD COLUMN `promotion_percent` TINYINT UNSIGNED NULL AFTER `promotion_label`,
    ADD COLUMN `email` VARCHAR(255) NULL AFTER `phone`,
    ADD COLUMN `website` VARCHAR(500) NULL AFTER `email`,
    ADD COLUMN `google_maps_link` VARCHAR(500) NULL AFTER `website`,
    ADD COLUMN `latitude` DECIMAL(10,7) NULL AFTER `google_maps_link`,
    ADD COLUMN `longitude` DECIMAL(10,7) NULL AFTER `latitude`,
    ADD COLUMN `tags` VARCHAR(500) NULL AFTER `longitude`,
    ADD CONSTRAINT `fk_travel_hotels_country` FOREIGN KEY (`country_id`) REFERENCES `travel_countries`(`id`) ON DELETE SET NULL,
    ADD CONSTRAINT `fk_travel_hotels_city` FOREIGN KEY (`city_id`) REFERENCES `travel_cities`(`id`) ON DELETE SET NULL,
    ADD CONSTRAINT `fk_travel_hotels_category` FOREIGN KEY (`category_id`) REFERENCES `travel_hotel_categories`(`id`) ON DELETE SET NULL;

-- 7. Pivot: hotel <-> service
CREATE TABLE IF NOT EXISTS `travel_hotel_service` (
    `hotel_id` BIGINT UNSIGNED NOT NULL,
    `service_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`hotel_id`, `service_id`),
    CONSTRAINT `fk_hs_hotel` FOREIGN KEY (`hotel_id`) REFERENCES `travel_hotels`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_hs_service` FOREIGN KEY (`service_id`) REFERENCES `travel_services`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Pivot: hotel <-> condition
CREATE TABLE IF NOT EXISTS `travel_hotel_condition` (
    `hotel_id` BIGINT UNSIGNED NOT NULL,
    `condition_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`hotel_id`, `condition_id`),
    CONSTRAINT `fk_hc_hotel` FOREIGN KEY (`hotel_id`) REFERENCES `travel_hotels`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_hc_condition` FOREIGN KEY (`condition_id`) REFERENCES `travel_conditions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Room Types
CREATE TABLE IF NOT EXISTS `travel_room_types` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `hotel_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    CONSTRAINT `fk_roomtype_hotel` FOREIGN KEY (`hotel_id`) REFERENCES `travel_hotels`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Countries
INSERT INTO `travel_countries` (`name`, `code`, `is_active`, `created_at`, `updated_at`) VALUES
('Algérie', 'DZ', 1, NOW(), NOW()),
('Tunisie', 'TN', 1, NOW(), NOW()),
('Maroc', 'MA', 1, NOW(), NOW()),
('Turquie', 'TR', 1, NOW(), NOW()),
('Égypte', 'EG', 1, NOW(), NOW());

-- Cities (Algerie)
INSERT INTO `travel_cities` (`country_id`, `name`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Alger', 1, NOW(), NOW()),
(1, 'Oran', 1, NOW(), NOW()),
(1, 'Constantine', 1, NOW(), NOW()),
(1, 'Annaba', 1, NOW(), NOW()),
(1, 'Tlemcen', 1, NOW(), NOW()),
(1, 'Béjaïa', 1, NOW(), NOW()),
(1, 'Tizi Ouzou', 1, NOW(), NOW()),
(1, 'Blida', 1, NOW(), NOW()),
(1, 'Sétif', 1, NOW(), NOW()),
(1, 'Batna', 1, NOW(), NOW()),
-- Cities (Tunisie)
(2, 'Tunis', 1, NOW(), NOW()),
(2, 'Sousse', 1, NOW(), NOW()),
(2, 'Sfax', 1, NOW(), NOW()),
(2, 'Hammamet', 1, NOW(), NOW()),
(2, 'Djerba', 1, NOW(), NOW()),
(2, 'Monastir', 1, NOW(), NOW()),
-- Cities (Maroc)
(3, 'Casablanca', 1, NOW(), NOW()),
(3, 'Marrakech', 1, NOW(), NOW()),
(3, 'Fès', 1, NOW(), NOW()),
(3, 'Tanger', 1, NOW(), NOW()),
(3, 'Agadir', 1, NOW(), NOW()),
(3, 'Rabat', 1, NOW(), NOW()),
-- Cities (Turquie)
(4, 'Istanbul', 1, NOW(), NOW()),
(4, 'Antalya', 1, NOW(), NOW()),
(4, 'Bodrum', 1, NOW(), NOW()),
(4, 'Alanya', 1, NOW(), NOW()),
(4, 'Cappadoce', 1, NOW(), NOW()),
-- Cities (Egypte)
(5, 'Le Caire', 1, NOW(), NOW()),
(5, 'Hurghada', 1, NOW(), NOW()),
(5, 'Sharm El Sheikh', 1, NOW(), NOW()),
(5, 'Marsa Alam', 1, NOW(), NOW());

-- Categories
INSERT INTO `travel_hotel_categories` (`name`, `stars_number`, `color`, `icon`, `is_active`, `created_at`, `updated_at`) VALUES
('Standard', 2, '#6c757d', 'building', 1, NOW(), NOW()),
('Confort', 3, '#0d6efd', 'star', 1, NOW(), NOW()),
('Supérieur', 4, '#ffc107', 'crown', 1, NOW(), NOW()),
('Luxe', 5, '#dc3545', 'gem', 1, NOW(), NOW());

-- Services
INSERT INTO `travel_services` (`name`, `slug`, `icon`, `color`, `is_active`, `created_at`, `updated_at`) VALUES
('WiFi', 'wifi', 'wifi', '#0d6efd', 1, NOW(), NOW()),
('Piscine', 'piscine', 'droplet', '#0dcaf0', 1, NOW(), NOW()),
('Spa & Wellness', 'spa-wellness', 'heart-pulse', '#dc3545', 1, NOW(), NOW()),
('Restaurant', 'restaurant', 'utensils', '#198754', 1, NOW(), NOW()),
('Bar', 'bar', 'wine', '#ffc107', 1, NOW(), NOW()),
('Parking', 'parking', 'car', '#6c757d', 1, NOW(), NOW()),
('Salle de sport', 'salle-de-sport', 'dumbbell', '#0d6efd', 1, NOW(), NOW()),
('Plage privée', 'plage-privee', 'umbrella-beach', '#fd7e14', 1, NOW(), NOW()),
('Navette aéroport', 'navette-aeroport', 'bus', '#20c997', 1, NOW(), NOW()),
('Service en chambre', 'service-en-chambre', 'concierge-bell', '#6f42c1', 1, NOW(), NOW());

-- Conditions
INSERT INTO `travel_conditions` (`name`, `slug`, `icon`, `color`, `badge_type`, `is_active`, `created_at`, `updated_at`) VALUES
('Non-remboursable', 'non-remboursable', 'x-circle', '#dc3545', 'danger', 1, NOW(), NOW()),
('Annulation gratuite 48h', 'annulation-gratuite-48h', 'check-circle', '#198754', 'success', 1, NOW(), NOW()),
('Petit-déjeuner inclus', 'petit-dejeuner-inclus', 'coffee', '#fd7e14', 'warning', 1, NOW(), NOW()),
('All inclusive', 'all-inclusive', 'cup-straw', '#0d6efd', 'primary', 1, NOW(), NOW()),
('Demi-pension', 'demi-pension', 'egg-fried', '#6f42c1', 'info', 1, NOW(), NOW()),
('Transfert inclus', 'transfer-inclus', 'bus-front', '#20c997', 'success', 1, NOW(), NOW()),
('Animaux acceptés', 'animaux-acceptes', 'paw-print', '#fd7e14', 'warning', 1, NOW(), NOW()),
('Familial', 'familial', 'users', '#0dcaf0', 'info', 1, NOW(), NOW());
