-- Tours and Packages Database Tables
-- Run this SQL to create tours and packages functionality

-- Tours table - Main tour definitions
CREATE TABLE IF NOT EXISTS `tours` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `destination` varchar(255) NOT NULL,
  `duration_days` int NOT NULL DEFAULT 1,
  `duration_nights` int NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `highlights` text DEFAULT NULL,
  `itinerary` text DEFAULT NULL,
  `inclusions` text DEFAULT NULL,
  `exclusions` text DEFAULT NULL,
  `terms` text DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `category` enum('domestic','international','adventure','cultural','beach','safari','pilgrimage','honeymoon','other') DEFAULT 'other',
  `difficulty` enum('easy','moderate','challenging') DEFAULT 'easy',
  `best_season` varchar(255) DEFAULT NULL,
  `min_pax` int DEFAULT 1,
  `max_pax` int DEFAULT 50,
  `status` enum('active','inactive','draft') DEFAULT 'draft',
  `is_featured` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tours_agency_id_index` (`agency_id`),
  KEY `tours_destination_index` (`destination`),
  KEY `tours_category_index` (`category`),
  KEY `tours_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tour Packages - Different package variants for each tour
CREATE TABLE IF NOT EXISTS `tour_packages` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tour_id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `package_code` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `duration_days` int NOT NULL DEFAULT 1,
  `duration_nights` int NOT NULL DEFAULT 0,
  `base_price` decimal(12,2) NOT NULL DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `price_per` enum('person','group','room','unit') DEFAULT 'person',
  `min_pax` int DEFAULT 1,
  `max_pax` int DEFAULT 50,
  `hotel_category` varchar(100) DEFAULT NULL,
  `meal_plan` enum('room_only','bed_breakfast','half_board','full_board','all_inclusive') DEFAULT 'room_only',
  `flights_included` tinyint(1) DEFAULT 0,
  `transfers_included` tinyint(1) DEFAULT 1,
  `sightseeing_included` tinyint(1) DEFAULT 1,
  `visa_included` tinyint(1) DEFAULT 0,
  `travel_insurance_included` tinyint(1) DEFAULT 0,
  `valid_from` date DEFAULT NULL,
  `valid_until` date DEFAULT NULL,
  `status` enum('active','inactive','sold_out') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tour_packages_tour_id_index` (`tour_id`),
  KEY `tour_packages_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tour Bookings - Bookings for tours
CREATE TABLE IF NOT EXISTS `tour_bookings` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `tour_id` bigint UNSIGNED NOT NULL,
  `package_id` bigint UNSIGNED NOT NULL,
  `client_id` bigint UNSIGNED DEFAULT NULL,
  `booking_reference` varchar(20) NOT NULL,
  `booking_date` date NOT NULL,
  `travel_date` date NOT NULL,
  `status` enum('pending','confirmed','cancelled','completed','refunded') DEFAULT 'pending',
  `adults` int NOT NULL DEFAULT 1,
  `children` int DEFAULT 0,
  `infants` int DEFAULT 0,
  `total_pax` int NOT NULL DEFAULT 1,
  `base_price` decimal(12,2) NOT NULL DEFAULT 0,
  `markup` decimal(12,2) DEFAULT 0,
  `tax_amount` decimal(12,2) DEFAULT 0,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0,
  `paid_amount` decimal(12,2) DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `client_notes` text DEFAULT NULL,
  `internal_notes` text DEFAULT NULL,
  `hotel_name` varchar(255) DEFAULT NULL,
  `room_type` varchar(100) DEFAULT NULL,
  `flight_details` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tour_bookings_booking_reference_unique` (`booking_reference`),
  KEY `tour_bookings_agency_id_index` (`agency_id`),
  KEY `tour_bookings_tour_id_index` (`tour_id`),
  KEY `tour_bookings_client_id_index` (`client_id`),
  KEY `tour_bookings_status_index` (`status`),
  KEY `tour_bookings_travel_date_index` (`travel_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;