-- ============================================
-- Travel Database - Complete Schema & Demo Data
-- ============================================

CREATE DATABASE IF NOT EXISTS `Travel`;
USE `Travel`;

-- ============================================
-- Settings Table
-- ============================================
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Users Table (Admin & Regular Users)
-- ============================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(200) NOT NULL UNIQUE,
  `phone` VARCHAR(20) DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `address` TEXT DEFAULT NULL,
  `profile_image` VARCHAR(255) DEFAULT 'default.png',
  `role` ENUM('admin','user') DEFAULT 'user',
  `status` ENUM('active','inactive','banned') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Places Table
-- ============================================
CREATE TABLE IF NOT EXISTS `places` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `description` TEXT,
  `image` VARCHAR(255) DEFAULT NULL,
  `is_featured` TINYINT(1) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Tour Packages Table
-- ============================================
CREATE TABLE IF NOT EXISTS `packages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `place_id` INT NOT NULL,
  `title` VARCHAR(300) NOT NULL,
  `slug` VARCHAR(300) NOT NULL UNIQUE,
  `description` TEXT,
  `price_per_person` DECIMAL(10,2) NOT NULL,
  `offer_price` DECIMAL(10,2) DEFAULT NULL,
  `duration_days` INT NOT NULL DEFAULT 1,
  `duration_nights` INT NOT NULL DEFAULT 0,
  `min_people` INT DEFAULT 1,
  `max_people` INT DEFAULT 20,
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `inclusions` TEXT,
  `exclusions` TEXT,
  `is_featured` TINYINT(1) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`place_id`) REFERENCES `places`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Package Images Table
-- ============================================
CREATE TABLE IF NOT EXISTS `package_images` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `package_id` INT NOT NULL,
  `image` VARCHAR(255) NOT NULL,
  `is_primary` TINYINT(1) DEFAULT 0,
  `sort_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Itinerary Table (Day-wise)
-- ============================================
CREATE TABLE IF NOT EXISTS `itinerary` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `package_id` INT NOT NULL,
  `day_number` INT NOT NULL,
  `title` VARCHAR(300) NOT NULL,
  `description` TEXT,
  `meals` VARCHAR(200) DEFAULT NULL,
  `accommodation` VARCHAR(200) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Bookings Table
-- ============================================
CREATE TABLE IF NOT EXISTS `bookings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `booking_id` VARCHAR(50) NOT NULL UNIQUE,
  `user_id` INT NOT NULL,
  `package_id` INT NOT NULL,
  `num_people` INT NOT NULL DEFAULT 1,
  `travel_date` DATE NOT NULL,
  `total_amount` DECIMAL(10,2) NOT NULL,
  `discount_amount` DECIMAL(10,2) DEFAULT 0,
  `final_amount` DECIMAL(10,2) NOT NULL,
  `special_requests` TEXT,
  `status` ENUM('pending','confirmed','cancelled','completed') DEFAULT 'pending',
  `payment_status` ENUM('pending','paid','refunded','failed') DEFAULT 'pending',
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Transactions Table
-- ============================================
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` VARCHAR(100) NOT NULL UNIQUE,
  `booking_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `payment_method` VARCHAR(50) NOT NULL,
  `payment_gateway_id` VARCHAR(200) DEFAULT NULL,
  `status` ENUM('success','failed','pending','refunded') DEFAULT 'pending',
  `payment_data` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Enquiries Table
-- ============================================
CREATE TABLE IF NOT EXISTS `enquiries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(200) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `package_id` INT DEFAULT NULL,
  `message` TEXT,
  `status` ENUM('new','read','replied','closed') DEFAULT 'new',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Hero Sliders Table
-- ============================================
CREATE TABLE IF NOT EXISTS `sliders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(300) DEFAULT NULL,
  `subtitle` VARCHAR(500) DEFAULT NULL,
  `image` VARCHAR(255) NOT NULL,
  `link` VARCHAR(500) DEFAULT NULL,
  `sort_order` INT DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Email Templates Table
-- ============================================
CREATE TABLE IF NOT EXISTS `email_templates` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `subject` VARCHAR(300) NOT NULL,
  `body` TEXT NOT NULL,
  `variables` TEXT,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- Activity Log Table
-- ============================================
CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL,
  `action` VARCHAR(200) NOT NULL,
  `description` TEXT,
  `ip_address` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- INSERT DEFAULT SETTINGS
-- ============================================
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'Traveler - Tour & Travel'),
('site_tagline', 'Explore the World with Us'),
('site_logo', 'logo.png'),
('site_favicon', 'favicon.png'),
('site_email', 'info@traveler.com'),
('site_phone', '+91 98765 43210'),
('site_address', '123 Travel Street, Mumbai, India'),
('currency', 'INR'),
('currency_symbol', '₹'),
('timezone', 'Asia/Kolkata'),
('razorpay_enabled', '1'),
('razorpay_key_id', 'rzp_test_xxxxxxxxxxxx'),
('razorpay_key_secret', 'xxxxxxxxxxxxxxxxxxxxxxxx'),
('phonepe_enabled', '0'),
('phonepe_merchant_id', ''),
('phonepe_salt_key', ''),
('cash_enabled', '1'),
('smtp_host', 'smtp.gmail.com'),
('smtp_port', '587'),
('smtp_username', ''),
('smtp_password', ''),
('smtp_encryption', 'tls'),
('smtp_from_email', 'noreply@traveler.com'),
('smtp_from_name', 'Traveler'),
('whatsapp_number', '+919876543210'),
('invoice_prefix', 'TRV'),
('invoice_gst', '18'),
('google_analytics', ''),
('meta_description', 'Discover amazing tour packages and travel destinations with Traveler.'),
('meta_keywords', 'travel, tour, packages, holiday, vacation, india');

-- ============================================
-- INSERT DEFAULT ADMIN USER (password: admin123)
-- ============================================
INSERT INTO `users` (`name`, `email`, `phone`, `password`, `role`, `status`) VALUES
('Admin', 'admin@traveler.com', '+919876543210', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');

-- ============================================
-- INSERT DEMO USERS (password: password)
-- ============================================
INSERT INTO `users` (`name`, `email`, `phone`, `password`, `address`, `role`, `status`) VALUES
('Rahul Sharma', 'rahul@example.com', '+919876543211', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '45 MG Road, Bangalore, Karnataka', 'user', 'active'),
('Priya Patel', 'priya@example.com', '+919876543212', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '78 Park Street, Kolkata, West Bengal', 'user', 'active'),
('Amit Kumar', 'amit@example.com', '+919876543213', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '12 Connaught Place, New Delhi', 'user', 'active'),
('Sneha Reddy', 'sneha@example.com', '+919876543214', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '56 Jubilee Hills, Hyderabad, Telangana', 'user', 'active');

-- ============================================
-- INSERT DEMO PLACES
-- ============================================
INSERT INTO `places` (`name`, `slug`, `description`, `image`, `is_featured`, `status`) VALUES
('Goa', 'goa', 'Goa is a state on the southwestern coast of India within the Konkan region, known for its beautiful beaches, vibrant nightlife, and Portuguese heritage architecture.', 'goa.jpg', 1, 'active'),
('Manali', 'manali', 'Manali is a high-altitude Himalayan resort town in India''s northern Himachal Pradesh state. It has a reputation as a backpacking center and honeymoon destination.', 'manali.jpg', 1, 'active'),
('Kerala', 'kerala', 'Kerala, a state on India''s tropical Malabar Coast, has nearly 600km of Arabian Sea shoreline. Known as God''s Own Country for its backwaters, beaches, and wildlife.', 'kerala.jpg', 1, 'active'),
('Rajasthan', 'rajasthan', 'Rajasthan is a state in northern India known for its royal heritage, magnificent forts, vibrant culture, and the vast Thar Desert.', 'rajasthan.jpg', 1, 'active'),
('Andaman Islands', 'andaman-islands', 'The Andaman Islands are an Indian archipelago in the Bay of Bengal, known for pristine beaches, crystal-clear waters, and rich marine biodiversity.', 'andaman.jpg', 1, 'active'),
('Ladakh', 'ladakh', 'Ladakh is a region administered by India as a union territory, known for its remote mountain beauty, Buddhist monasteries, and dramatic landscapes.', 'ladakh.jpg', 1, 'active'),
('Shimla', 'shimla', 'Shimla is the capital of the northern Indian state of Himachal Pradesh, known for its colonial architecture, scenic beauty, and pleasant climate.', 'shimla.jpg', 0, 'active'),
('Varanasi', 'varanasi', 'Varanasi is a city in the northern Indian state of Uttar Pradesh, one of the oldest living cities in the world, famous for its ghats along the Ganges.', 'varanasi.jpg', 0, 'active');

-- ============================================
-- INSERT DEMO PACKAGES
-- ============================================
INSERT INTO `packages` (`place_id`, `title`, `slug`, `description`, `price_per_person`, `offer_price`, `duration_days`, `duration_nights`, `min_people`, `max_people`, `start_date`, `end_date`, `inclusions`, `exclusions`, `is_featured`, `status`) VALUES
(1, 'Goa Beach Paradise - 4 Days', 'goa-beach-paradise-4-days', 'Experience the best of Goa with our carefully curated 4-day beach paradise package. Enjoy pristine beaches, water sports, vibrant nightlife, and delicious Goan cuisine. This package covers North and South Goa with comfortable stays and guided tours.', 8999.00, 6999.00, 4, 3, 2, 15, '2026-03-01', '2026-12-31', 'Hotel Accommodation (3 Nights)\nDaily Breakfast & Dinner\nAirport/Station Transfers\nNorth Goa Sightseeing\nSouth Goa Sightseeing\nWater Sports (Parasailing, Jet Ski)\nCruise Dinner\nAll Taxes', 'Airfare/Train Fare\nLunch\nPersonal Expenses\nTravel Insurance\nAnything not mentioned in inclusions', 1, 'active'),

(2, 'Magical Manali Adventure - 5 Days', 'magical-manali-adventure-5-days', 'Discover the magic of Manali with this 5-day adventure package. From snow-capped mountains to lush green valleys, experience the best of Himachal Pradesh. Includes visits to Solang Valley, Rohtang Pass, and Old Manali.', 12999.00, 9999.00, 5, 4, 2, 20, '2026-03-01', '2026-12-31', 'Hotel Accommodation (4 Nights)\nDaily Breakfast & Dinner\nVolvo Bus from Delhi\nSolang Valley Visit\nRohtang Pass Excursion\nHadimba Temple Visit\nMall Road Tour\nAll Taxes', 'Lunch\nPersonal Expenses\nAdventure Activities (Extra)\nTravel Insurance', 1, 'active'),

(3, 'Kerala Backwater Bliss - 6 Days', 'kerala-backwater-bliss-6-days', 'Immerse yourself in the serene beauty of Kerala with our 6-day backwater bliss package. Cruise through tranquil backwaters, relax on pristine beaches, explore tea plantations in Munnar, and experience authentic Ayurvedic treatments.', 15999.00, 12999.00, 6, 5, 2, 12, '2026-03-01', '2026-12-31', 'Hotel/Houseboat Accommodation (5 Nights)\nDaily Breakfast & Dinner\nAirport Transfers\nMunnar Sightseeing\nAlleppey Houseboat (1 Night)\nKochi City Tour\nSpice Plantation Visit\nAyurvedic Massage\nAll Taxes', 'Airfare\nLunch\nPersonal Expenses\nCamera Fees at Monuments\nTravel Insurance', 1, 'active'),

(4, 'Royal Rajasthan Tour - 7 Days', 'royal-rajasthan-tour-7-days', 'Experience the royal heritage of Rajasthan with our 7-day comprehensive tour. Visit magnificent forts, opulent palaces, and vibrant markets across Jaipur, Jodhpur, and Udaipur. Includes a desert safari in Jaisalmer.', 18999.00, 14999.00, 7, 6, 2, 20, '2026-03-01', '2026-12-31', 'Hotel Accommodation (6 Nights)\nDaily Breakfast & Dinner\nAC Vehicle for Transfers\nJaipur City Tour\nJodhpur Sightseeing\nUdaipur Lake Tour\nDesert Safari\nCultural Evening\nAll Monument Entries\nAll Taxes', 'Airfare/Train Fare\nLunch\nPersonal Expenses\nCamera Fees\nTravel Insurance\nTips & Gratuities', 1, 'active'),

(5, 'Andaman Island Escape - 5 Days', 'andaman-island-escape-5-days', 'Escape to the pristine Andaman Islands with our 5-day island hopping package. Explore crystal-clear waters, white sandy beaches, and vibrant coral reefs. Includes visits to Havelock Island, Neil Island, and Ross Island.', 22999.00, 18999.00, 5, 4, 2, 10, '2026-04-01', '2026-12-31', 'Hotel Accommodation (4 Nights)\nDaily Breakfast & Dinner\nFerry Transfers\nHavelock Island Tour\nRadhanagar Beach Visit\nSnorkeling & Scuba Diving\nRoss Island Tour\nCellular Jail Visit\nAll Taxes', 'Airfare\nLunch\nPersonal Expenses\nWater Sports (Extra)\nTravel Insurance', 1, 'active'),

(6, 'Ladakh Expedition - 7 Days', 'ladakh-expedition-7-days', 'Embark on the ultimate Ladakh expedition with our 7-day adventure package. Traverse high-altitude passes, visit ancient monasteries, and witness breathtaking landscapes. Includes Pangong Lake, Nubra Valley, and Magnetic Hill.', 25999.00, 21999.00, 7, 6, 4, 15, '2026-05-01', '2026-09-30', 'Hotel/Camp Accommodation (6 Nights)\nDaily All Meals\nInner Line Permits\nLeh Sightseeing\nPangong Lake Excursion\nNubra Valley Tour\nMagnetic Hill Visit\nMonastery Tours\nOxygen Cylinder Support\nAll Taxes', 'Airfare\nPersonal Expenses\nMedical Expenses\nTravel Insurance\nBike/ATV Rentals', 1, 'active'),

(7, 'Shimla Getaway - 3 Days', 'shimla-getaway-3-days', 'A perfect weekend getaway to the Queen of Hills. Enjoy the colonial charm of Shimla with visits to Mall Road, Ridge, and Kufri. Ideal for couples and families looking for a quick mountain escape.', 6999.00, 4999.00, 3, 2, 2, 20, '2026-03-01', '2026-12-31', 'Hotel Accommodation (2 Nights)\nDaily Breakfast\nVolvo Bus from Delhi\nShimla Local Sightseeing\nKufri Excursion\nMall Road Tour\nAll Taxes', 'Lunch & Dinner\nPersonal Expenses\nAdventure Activities\nTravel Insurance', 0, 'active'),

(8, 'Varanasi Spiritual Journey - 3 Days', 'varanasi-spiritual-journey-3-days', 'Experience the spiritual essence of India in the ancient city of Varanasi. Witness the mesmerizing Ganga Aarti, explore ancient temples, and take a boat ride on the holy Ganges. A soul-stirring journey through India''s spiritual capital.', 5999.00, 4499.00, 3, 2, 1, 20, '2026-03-01', '2026-12-31', 'Hotel Accommodation (2 Nights)\nDaily Breakfast\nAirport/Station Transfers\nGanga Aarti Experience\nBoat Ride on Ganges\nTemple Tour\nSarnath Excursion\nAll Taxes', 'Airfare/Train Fare\nLunch & Dinner\nPersonal Expenses\nDonations at Temples\nTravel Insurance', 0, 'active');

-- ============================================
-- INSERT DEMO PACKAGE IMAGES
-- ============================================
INSERT INTO `package_images` (`package_id`, `image`, `is_primary`, `sort_order`) VALUES
(1, 'goa-1.jpg', 1, 1),
(1, 'goa-2.jpg', 0, 2),
(1, 'goa-3.jpg', 0, 3),
(2, 'manali-1.jpg', 1, 1),
(2, 'manali-2.jpg', 0, 2),
(2, 'manali-3.jpg', 0, 3),
(3, 'kerala-1.jpg', 1, 1),
(3, 'kerala-2.jpg', 0, 2),
(3, 'kerala-3.jpg', 0, 3),
(4, 'rajasthan-1.jpg', 1, 1),
(4, 'rajasthan-2.jpg', 0, 2),
(4, 'rajasthan-3.jpg', 0, 3),
(5, 'andaman-1.jpg', 1, 1),
(5, 'andaman-2.jpg', 0, 2),
(5, 'andaman-3.jpg', 0, 3),
(6, 'ladakh-1.jpg', 1, 1),
(6, 'ladakh-2.jpg', 0, 2),
(6, 'ladakh-3.jpg', 0, 3),
(7, 'shimla-1.jpg', 1, 1),
(7, 'shimla-2.jpg', 0, 2),
(8, 'varanasi-1.jpg', 1, 1),
(8, 'varanasi-2.jpg', 0, 2);

-- ============================================
-- INSERT DEMO ITINERARY
-- ============================================
-- Goa Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(1, 1, 'Arrival in Goa & North Goa Beaches', 'Arrive at Goa Airport/Station. Transfer to hotel. After freshening up, head to the famous Calangute Beach and Baga Beach. Enjoy water sports like parasailing and jet skiing. Evening free for exploring the vibrant nightlife of Tito''s Lane.', 'Dinner', 'Hotel in North Goa'),
(1, 2, 'North Goa Sightseeing', 'After breakfast, visit Fort Aguada, Sinquerim Beach, and Anjuna Beach. Explore the famous Anjuna Flea Market (if Wednesday). Visit Chapora Fort for stunning sunset views. Evening enjoy a Mandovi River Cruise with dinner and live entertainment.', 'Breakfast, Dinner', 'Hotel in North Goa'),
(1, 3, 'South Goa Exploration', 'After breakfast, drive to South Goa. Visit the beautiful Colva Beach, Palolem Beach, and Butterfly Beach. Explore the Basilica of Bom Jesus (UNESCO World Heritage Site) and Se Cathedral. Visit a spice plantation with lunch. Evening at leisure.', 'Breakfast, Dinner', 'Hotel in North Goa'),
(1, 4, 'Departure', 'After breakfast, check out from the hotel. Last-minute shopping at Mapusa Market or Panjim Market for Goan souvenirs, cashews, and feni. Transfer to Airport/Station for departure.', 'Breakfast', 'N/A');

-- Manali Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(2, 1, 'Delhi to Manali Journey', 'Board the Volvo bus from Delhi in the evening. Overnight journey through the scenic route via Chandigarh and Kullu Valley. Enjoy the changing landscapes as you ascend into the mountains.', 'Dinner (packed)', 'Volvo Bus'),
(2, 2, 'Arrival & Local Sightseeing', 'Arrive in Manali by morning. Check into hotel and freshen up. After lunch, visit Hadimba Temple, Manu Temple, and Vashisht Hot Springs. Evening stroll on Mall Road for shopping and local cuisine.', 'Breakfast, Dinner', 'Hotel in Manali'),
(2, 3, 'Solang Valley Adventure', 'After breakfast, head to Solang Valley - the adventure capital of Manali. Enjoy activities like zorbing, paragliding, and rope way. In winter, enjoy skiing and snowboarding. Return to hotel by evening.', 'Breakfast, Dinner', 'Hotel in Manali'),
(2, 4, 'Rohtang Pass Excursion', 'Early morning departure for Rohtang Pass (subject to weather/permits). Enjoy snow activities and breathtaking views of the Pir Panjal range. Visit Rahala Falls and Nehru Kund on the way. Return to Manali by evening.', 'Breakfast, Dinner', 'Hotel in Manali'),
(2, 5, 'Departure', 'After breakfast, check out from hotel. Visit Old Manali and the local market for last-minute shopping. Board the Volvo bus back to Delhi in the afternoon/evening.', 'Breakfast', 'Volvo Bus');

-- Kerala Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(3, 1, 'Arrival in Kochi', 'Arrive at Cochin International Airport. Transfer to hotel. Evening visit to Fort Kochi to see the famous Chinese Fishing Nets, St. Francis Church, and Santa Cruz Cathedral. Enjoy a traditional Kathakali dance performance.', 'Dinner', 'Hotel in Kochi'),
(3, 2, 'Kochi to Munnar', 'After breakfast, drive to Munnar (4 hours). En route, visit Cheeyappara and Valara Waterfalls. Arrive in Munnar and check into hotel. Evening visit to the tea gardens and enjoy the misty mountain views.', 'Breakfast, Dinner', 'Hotel in Munnar'),
(3, 3, 'Munnar Sightseeing', 'Full day Munnar sightseeing. Visit Eravikulam National Park (home of Nilgiri Tahr), Mattupetty Dam, Echo Point, and Tea Museum. Visit a spice plantation and learn about cardamom, pepper, and vanilla cultivation.', 'Breakfast, Dinner', 'Hotel in Munnar'),
(3, 4, 'Munnar to Alleppey Houseboat', 'After breakfast, drive to Alleppey (5 hours). Board a traditional Kerala houseboat (Kettuvallam) for an unforgettable backwater cruise. Glide through palm-fringed canals, paddy fields, and local villages. Enjoy fresh Kerala cuisine on board.', 'Breakfast, Lunch, Dinner', 'Houseboat'),
(3, 5, 'Alleppey to Kovalam Beach', 'Disembark from houseboat after breakfast. Drive to Kovalam Beach (4 hours). Check into beachside resort. Relax on the famous Lighthouse Beach. Evening enjoy an Ayurvedic massage at the resort spa.', 'Breakfast, Dinner', 'Resort in Kovalam'),
(3, 6, 'Departure', 'After breakfast, free time for beach activities or shopping. Transfer to Trivandrum Airport for departure. Carry home beautiful memories of God''s Own Country.', 'Breakfast', 'N/A');

-- Rajasthan Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(4, 1, 'Arrival in Jaipur', 'Arrive in Jaipur - The Pink City. Transfer to hotel. Evening visit to Birla Mandir and enjoy the illuminated city views. Welcome dinner with traditional Rajasthani cuisine and folk dance performance.', 'Dinner', 'Hotel in Jaipur'),
(4, 2, 'Jaipur Sightseeing', 'Full day Jaipur tour. Visit Amber Fort (elephant ride), Jal Mahal, Hawa Mahal, City Palace, and Jantar Mantar. Evening shopping at Johari Bazaar for traditional jewelry, textiles, and handicrafts.', 'Breakfast, Dinner', 'Hotel in Jaipur'),
(4, 3, 'Jaipur to Jodhpur', 'After breakfast, drive to Jodhpur - The Blue City (5 hours). En route, visit Ajmer Sharif Dargah and Pushkar Lake. Arrive in Jodhpur and check into hotel. Evening visit to the Clock Tower market.', 'Breakfast, Dinner', 'Hotel in Jodhpur'),
(4, 4, 'Jodhpur Sightseeing', 'Visit the magnificent Mehrangarh Fort, Jaswant Thada, and Umaid Bhawan Palace Museum. Explore the blue-painted houses of the old city. Enjoy traditional Rajasthani thali for lunch. Evening at leisure.', 'Breakfast, Dinner', 'Hotel in Jodhpur'),
(4, 5, 'Jodhpur to Udaipur', 'After breakfast, drive to Udaipur - The City of Lakes (5 hours). En route, visit Ranakpur Jain Temple with its 1444 marble pillars. Arrive in Udaipur and check into lakeside hotel. Evening boat ride on Lake Pichola.', 'Breakfast, Dinner', 'Hotel in Udaipur'),
(4, 6, 'Udaipur Sightseeing', 'Visit City Palace, Jagdish Temple, Saheliyon Ki Bari, and Fateh Sagar Lake. Afternoon visit to Sajjangarh (Monsoon Palace) for panoramic views. Evening enjoy a cultural show at Bagore Ki Haveli.', 'Breakfast, Dinner', 'Hotel in Udaipur'),
(4, 7, 'Departure', 'After breakfast, last-minute shopping for miniature paintings, silver jewelry, and handicrafts. Transfer to Udaipur Airport/Station for departure.', 'Breakfast', 'N/A');

-- Andaman Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(5, 1, 'Arrival in Port Blair', 'Arrive at Veer Savarkar Airport, Port Blair. Transfer to hotel. Afternoon visit to Cellular Jail - the infamous colonial prison. Evening attend the Light and Sound Show at Cellular Jail depicting India''s freedom struggle.', 'Dinner', 'Hotel in Port Blair'),
(5, 2, 'Port Blair to Havelock Island', 'After breakfast, take a ferry to Havelock Island (2 hours). Check into beach resort. Visit the world-famous Radhanagar Beach (Asia''s Best Beach). Enjoy swimming and sunbathing. Evening bonfire on the beach.', 'Breakfast, Dinner', 'Resort in Havelock'),
(5, 3, 'Havelock Island Activities', 'Early morning visit to Elephant Beach for snorkeling and scuba diving. Explore vibrant coral reefs and marine life. Afternoon at leisure - kayaking, glass-bottom boat ride, or simply relax on the beach.', 'Breakfast, Dinner', 'Resort in Havelock'),
(5, 4, 'Havelock to Neil Island & Back', 'Ferry to Neil Island. Visit Bharatpur Beach, Laxmanpur Beach, and the Natural Bridge (Howrah Bridge). Enjoy the laid-back island vibes. Return to Port Blair by evening ferry. Visit Ross Island if time permits.', 'Breakfast, Dinner', 'Hotel in Port Blair'),
(5, 5, 'Departure', 'After breakfast, visit Samudrika Naval Marine Museum and Anthropological Museum. Last-minute shopping for shell crafts and pearl jewelry at Aberdeen Bazaar. Transfer to airport for departure.', 'Breakfast', 'N/A');

-- Ladakh Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(6, 1, 'Arrival in Leh & Acclimatization', 'Arrive at Kushok Bakula Rimpochee Airport, Leh. Transfer to hotel. IMPORTANT: Rest and acclimatize to the high altitude (3,500m). Light walk around Leh Market in the evening. Stay hydrated and avoid strenuous activity.', 'All Meals', 'Hotel in Leh'),
(6, 2, 'Leh Local Sightseeing', 'After acclimatization, visit Leh Palace, Shanti Stupa, and Leh Old Town. Afternoon visit to Hall of Fame (Army Museum) and Magnetic Hill. Evening visit to Sangam Point (confluence of Indus and Zanskar rivers).', 'All Meals', 'Hotel in Leh'),
(6, 3, 'Leh to Nubra Valley', 'Drive to Nubra Valley via Khardung La Pass (one of the world''s highest motorable passes at 18,380 ft). Visit Diskit Monastery and the giant Maitreya Buddha statue. Evening camel ride on sand dunes at Hunder.', 'All Meals', 'Camp in Nubra Valley'),
(6, 4, 'Nubra Valley to Pangong Lake', 'Drive from Nubra Valley to Pangong Lake via Shyok Route. Arrive at the stunning Pangong Tso - the famous blue lake from "3 Idiots". Watch the lake change colors through the day. Overnight camping by the lake.', 'All Meals', 'Camp at Pangong Lake'),
(6, 5, 'Pangong Lake to Leh', 'Early morning photography at Pangong Lake. Drive back to Leh via Chang La Pass (17,590 ft). En route visit Thiksey Monastery and Hemis Monastery. Evening at leisure in Leh Market.', 'All Meals', 'Hotel in Leh'),
(6, 6, 'Leh Monastery Tour', 'Visit Alchi Monastery (1000-year-old), Likir Monastery, and Basgo Palace. Afternoon visit to Zanskar River for river rafting (optional). Evening farewell dinner with Ladakhi cultural performance.', 'All Meals', 'Hotel in Leh'),
(6, 7, 'Departure', 'After breakfast, last-minute shopping for Pashmina shawls, Tibetan artifacts, and prayer flags. Transfer to Leh Airport for departure.', 'Breakfast', 'N/A');

-- Shimla Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(7, 1, 'Delhi to Shimla', 'Board the Volvo bus from Delhi early morning. Arrive in Shimla by afternoon. Check into hotel. Evening stroll on the famous Mall Road and Ridge. Visit Christ Church - the second oldest church in North India.', 'Dinner', 'Hotel in Shimla'),
(7, 2, 'Shimla & Kufri Sightseeing', 'After breakfast, visit Jakhoo Temple, Vice Regal Lodge, and Scandal Point. Drive to Kufri for horse riding and enjoy panoramic Himalayan views. Visit the Himalayan Nature Park. Evening free for shopping on Mall Road.', 'Breakfast, Dinner', 'Hotel in Shimla'),
(7, 3, 'Departure', 'After breakfast, visit the famous Shimla toy train station. Last-minute shopping for Himachali caps, shawls, and apple products. Board the Volvo bus back to Delhi.', 'Breakfast', 'N/A');

-- Varanasi Package Itinerary
INSERT INTO `itinerary` (`package_id`, `day_number`, `title`, `description`, `meals`, `accommodation`) VALUES
(8, 1, 'Arrival in Varanasi', 'Arrive in Varanasi. Transfer to hotel near the ghats. Evening witness the magnificent Ganga Aarti ceremony at Dashashwamedh Ghat - a mesmerizing spiritual experience with fire rituals, chanting, and bells.', 'Dinner', 'Hotel in Varanasi'),
(8, 2, 'Varanasi Sightseeing', 'Early morning boat ride on the Ganges to witness the sunrise and morning rituals at the ghats. Visit Kashi Vishwanath Temple, Tulsi Manas Temple, and Durga Temple. Afternoon excursion to Sarnath - where Buddha gave his first sermon.', 'Breakfast, Dinner', 'Hotel in Varanasi'),
(8, 3, 'Departure', 'Early morning optional visit to the ghats. After breakfast, visit the famous Banarasi silk weaving centers. Shopping for Banarasi sarees, silk, and local sweets. Transfer to airport/station for departure.', 'Breakfast', 'N/A');

-- ============================================
-- INSERT DEMO BOOKINGS
-- ============================================
INSERT INTO `bookings` (`booking_id`, `user_id`, `package_id`, `num_people`, `travel_date`, `total_amount`, `discount_amount`, `final_amount`, `special_requests`, `status`, `payment_status`, `payment_method`) VALUES
('TRV-2026-001', 2, 1, 2, '2026-03-15', 17998.00, 4000.00, 13998.00, 'Vegetarian meals preferred', 'confirmed', 'paid', 'razorpay'),
('TRV-2026-002', 3, 3, 2, '2026-04-10', 31998.00, 6000.00, 25998.00, 'Need extra bed for child', 'confirmed', 'paid', 'razorpay'),
('TRV-2026-003', 4, 4, 3, '2026-04-20', 56997.00, 12000.00, 44997.00, NULL, 'pending', 'pending', 'cash'),
('TRV-2026-004', 5, 2, 2, '2026-05-01', 25998.00, 6000.00, 19998.00, 'Honeymoon couple - special arrangement', 'confirmed', 'paid', 'phonepe'),
('TRV-2026-005', 2, 6, 4, '2026-06-15', 103996.00, 16000.00, 87996.00, 'Need oxygen support', 'pending', 'pending', 'razorpay');

-- ============================================
-- INSERT DEMO TRANSACTIONS
-- ============================================
INSERT INTO `transactions` (`transaction_id`, `booking_id`, `user_id`, `amount`, `payment_method`, `payment_gateway_id`, `status`) VALUES
('TXN-2026-001', 1, 2, 13998.00, 'razorpay', 'pay_LxYz1234AbCd', 'success'),
('TXN-2026-002', 2, 3, 25998.00, 'razorpay', 'pay_MnOp5678EfGh', 'success'),
('TXN-2026-003', 4, 5, 19998.00, 'phonepe', 'PP_QrSt9012IjKl', 'success');

-- ============================================
-- INSERT DEMO ENQUIRIES
-- ============================================
INSERT INTO `enquiries` (`name`, `email`, `phone`, `package_id`, `message`, `status`) VALUES
('Vikram Singh', 'vikram@example.com', '+919876543220', 1, 'I am interested in the Goa package. Can you customize it for a group of 10 people?', 'new'),
('Meera Joshi', 'meera@example.com', '+919876543221', 6, 'Is the Ladakh trip suitable for senior citizens? My parents are 65 years old.', 'read'),
('Arjun Nair', 'arjun@example.com', '+919876543222', 3, 'Can we extend the Kerala package by 2 more days? We want to visit Wayanad too.', 'replied');

-- ============================================
-- INSERT DEMO SLIDERS
-- ============================================
INSERT INTO `sliders` (`title`, `subtitle`, `image`, `link`, `sort_order`, `status`) VALUES
('Explore the World', 'Discover amazing destinations and create unforgettable memories', 'slider-1.jpg', '#packages', 1, 'active'),
('Beach Paradise Awaits', 'Sun, sand, and serenity - your perfect beach vacation starts here', 'slider-2.jpg', 'package.php?slug=goa-beach-paradise-4-days', 2, 'active'),
('Mountain Adventures', 'Conquer peaks and breathe in the fresh mountain air', 'slider-3.jpg', 'package.php?slug=ladakh-expedition-7-days', 3, 'active');

-- ============================================
-- INSERT EMAIL TEMPLATES
-- ============================================
INSERT INTO `email_templates` (`name`, `subject`, `body`, `variables`) VALUES
('booking_confirmation', 'Booking Confirmed - {{booking_id}}', '<h2>Booking Confirmed!</h2><p>Dear {{user_name}},</p><p>Your booking <strong>{{booking_id}}</strong> for <strong>{{package_name}}</strong> has been confirmed.</p><p><strong>Travel Date:</strong> {{travel_date}}<br><strong>Number of People:</strong> {{num_people}}<br><strong>Total Amount:</strong> {{currency}}{{amount}}</p><p>Thank you for choosing Traveler!</p>', '{{user_name}}, {{booking_id}}, {{package_name}}, {{travel_date}}, {{num_people}}, {{currency}}, {{amount}}'),
('payment_receipt', 'Payment Receipt - {{transaction_id}}', '<h2>Payment Receipt</h2><p>Dear {{user_name}},</p><p>We have received your payment of <strong>{{currency}}{{amount}}</strong> for booking <strong>{{booking_id}}</strong>.</p><p><strong>Transaction ID:</strong> {{transaction_id}}<br><strong>Payment Method:</strong> {{payment_method}}<br><strong>Date:</strong> {{date}}</p><p>Thank you!</p>', '{{user_name}}, {{transaction_id}}, {{booking_id}}, {{currency}}, {{amount}}, {{payment_method}}, {{date}}'),
('welcome_email', 'Welcome to Traveler!', '<h2>Welcome to Traveler!</h2><p>Dear {{user_name}},</p><p>Thank you for registering with Traveler. We are excited to help you plan your next adventure!</p><p>Explore our amazing tour packages and start your journey today.</p><p>Happy Traveling!</p>', '{{user_name}}');

-- ============================================
-- INSERT DEMO ACTIVITY LOG
-- ============================================
INSERT INTO `activity_log` (`user_id`, `action`, `description`, `ip_address`) VALUES
(1, 'admin_login', 'Admin logged in', '127.0.0.1'),
(1, 'package_created', 'Created package: Goa Beach Paradise - 4 Days', '127.0.0.1'),
(1, 'package_created', 'Created package: Magical Manali Adventure - 5 Days', '127.0.0.1'),
(2, 'user_login', 'User Rahul Sharma logged in', '192.168.1.100'),
(2, 'booking_created', 'Booking TRV-2026-001 created', '192.168.1.100'),
(3, 'user_login', 'User Priya Patel logged in', '192.168.1.101'),
(3, 'booking_created', 'Booking TRV-2026-002 created', '192.168.1.101');
