-- Database Schema for SR Travel (travel.chandanbro.com)
-- Compatible with MySQL 5.7+ / MariaDB 10.3+

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. Destinations Table
CREATE TABLE IF NOT EXISTS `destinations` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `location` VARCHAR(150) NOT NULL,
  `country` VARCHAR(100) DEFAULT 'Bangladesh',
  `description` TEXT,
  `rating` DECIMAL(3,2) DEFAULT 4.80,
  `reviews_count` INT DEFAULT 0,
  `base_price` INT NOT NULL DEFAULT 100,
  `category` ENUM('Beach', 'Mountain', 'Nature', 'Historic', 'Urban', 'Desert') DEFAULT 'Beach',
  `duration_days` INT DEFAULT 3,
  `image_url` VARCHAR(500) DEFAULT NULL,
  `is_featured` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Hotels Table
CREATE TABLE IF NOT EXISTS `hotels` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `destination_id` INT NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT,
  `rating` DECIMAL(3,2) DEFAULT 4.80,
  `price_per_night` INT NOT NULL,
  `amenities` TEXT, -- JSON or comma-separated
  `image_url` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`destination_id`) REFERENCES `destinations`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Bookings Table
CREATE TABLE IF NOT EXISTS `bookings` (
  `id` VARCHAR(50) PRIMARY KEY, -- e.g. 'SR-1092'
  `destination_id` INT NOT NULL,
  `hotel_id` INT DEFAULT NULL,
  `customer_name` VARCHAR(150) NOT NULL,
  `customer_email` VARCHAR(150) NOT NULL,
  `customer_phone` VARCHAR(50) DEFAULT NULL,
  `check_in_date` VARCHAR(50) NOT NULL,
  `check_out_date` VARCHAR(50) NOT NULL,
  `guests` INT DEFAULT 1,
  `total_price` INT NOT NULL,
  `status` ENUM('Pending', 'Confirmed', 'Completed', 'Cancelled') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Admin Users Table
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(150) DEFAULT 'SR Travel Admin',
  `role` VARCHAR(50) DEFAULT 'admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Initial Seed Data: Default Admin (admin / admin123)
INSERT INTO `admin_users` (`id`, `username`, `password_hash`, `full_name`, `role`)
VALUES (1, 'admin', '$2y$10$wEkg1WkFbvWb3q1tO7vEhe71oYw0o7V1tO7vEhe71oYw0o7V1tO7v', 'Chandan Admin', 'Super Admin')
ON DUPLICATE KEY UPDATE `full_name`=VALUES(`full_name`);

-- Seed Popular Destinations
INSERT INTO `destinations` (`id`, `name`, `location`, `country`, `description`, `rating`, `reviews_count`, `base_price`, `category`, `duration_days`, `image_url`) VALUES
(1, 'Cox’s Bazar', 'Chittagong', 'Bangladesh', 'The world longest unbroken natural sandy sea beach (120 km). Mesmerizing sunsets over the Bay of Bengal.', 4.90, 890, 180, 'Beach', 4, 'https://images.unsplash.com/photo-1507525428034-b723cf961d3e?auto=format&fit=crop&w=600&q=80'),
(2, 'Sajek Valley', 'Rangamati', 'Bangladesh', 'The kingdom of clouds sitting 1,800 feet above sea level. Wander above morning mists among green hills.', 4.90, 740, 160, 'Mountain', 3, 'https://images.unsplash.com/photo-1506744038136-46273834b3fb?auto=format&fit=crop&w=600&q=80'),
(3, 'Sylhet & Jaflong', 'Sylhet', 'Bangladesh', 'Lush rolling tea gardens, crystal freshwater rivers, and the enchanted Ratargul freshwater swamp forest.', 4.80, 610, 140, 'Nature', 3, 'https://images.unsplash.com/photo-1511497584788-87676104235f?auto=format&fit=crop&w=600&q=80'),
(4, 'Bandarban Hills', 'Bandarban', 'Bangladesh', 'Highest peaks including Keokradong and Nilgiri. Pristine indigenous heritage, waterfalls, and misty trails.', 4.90, 530, 170, 'Mountain', 4, 'https://images.unsplash.com/photo-1464822759023-fed622ff2c3b?auto=format&fit=crop&w=600&q=80'),
(5, 'Sundarbans Mangrove', 'Khulna', 'Bangladesh', 'The largest contiguous mangrove forest on Earth and UNESCO World Heritage site. Royal Bengal Tigers and safaris.', 4.70, 420, 210, 'Nature', 4, 'https://images.unsplash.com/photo-1448375240586-882707db888b?auto=format&fit=crop&w=600&q=80'),
(6, 'Santorini Caldera', 'Cyclades', 'Greece', 'Dramatic volcanic cliffs, breathtaking sunsets, and pristine white-washed cliffside suites.', 4.90, 412, 350, 'Beach', 5, 'https://images.unsplash.com/photo-1570077188670-e3a8d69ac5ff?auto=format&fit=crop&w=600&q=80')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- Seed Hotels
INSERT INTO `hotels` (`id`, `destination_id`, `name`, `description`, `rating`, `price_per_night`, `amenities`, `image_url`) VALUES
(101, 1, 'Sayeman Beach Resort', 'Iconic beachfront luxury resort with infinity pool facing Bay of Bengal.', 4.90, 160, 'Infinity Pool, Private Beach, Seafood Bistro, Spa, Free WiFi', 'https://images.unsplash.com/photo-1566073771259-6a8506099945?auto=format&fit=crop&w=600&q=80'),
(102, 1, 'Sea Pearl Beach Resort & Spa', '5-Star international resort on Inani Beach with private waterpark.', 4.80, 190, 'Water Park, Private Beach, Luxury Spa, Gym, Breakfast Buffet', 'https://images.unsplash.com/photo-1582719478250-c89cae4dc85b?auto=format&fit=crop&w=600&q=80'),
(201, 2, 'Meghpunji Resort', 'Prestige wooden cottages built directly on cliff edge touching the clouds.', 4.90, 110, 'Cloud Balcony, Helipad View, BBQ Zone, Campfire', 'https://images.unsplash.com/photo-1540555700478-4be289fbecef?auto=format&fit=crop&w=600&q=80'),
(301, 3, 'Grand Sultan Tea Resort', '5-Star golf and spa resort surrounded by lush rolling tea estates.', 4.80, 150, 'Golf Course, 3 Swimming Pools, Spa, Fine Dining', 'https://images.unsplash.com/photo-1571896349842-33c89424de2d?auto=format&fit=crop&w=600&q=80')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

SET FOREIGN_KEY_CHECKS = 1;