-- FlyBuddy Database Schema
-- Run this SQL script in your cPanel MySQL database to create the required tables

-- Create trips table
CREATE TABLE IF NOT EXISTS `trips` (
  `id` VARCHAR(255) NOT NULL PRIMARY KEY,
  `name` VARCHAR(500) NOT NULL,
  `category` ENUM('domestic', 'international') NOT NULL,
  `duration` VARCHAR(100) NOT NULL,
  `image` TEXT NOT NULL,
  `bestTime` VARCHAR(200) NOT NULL,
  `whyChoose` TEXT NOT NULL,
  `price` VARCHAR(100) DEFAULT '',
  `originalPrice` VARCHAR(100) DEFAULT '',
  `rating` DECIMAL(3, 2) DEFAULT 4.5,
  `reviews` INT DEFAULT 0,
  `highlights` JSON,
  `itinerary` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_category` (`category`),
  INDEX `idx_rating` (`rating`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create inquiries table
CREATE TABLE IF NOT EXISTS `inquiries` (
  `id` VARCHAR(255) NOT NULL PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `numberOfPeople` VARCHAR(50) NOT NULL,
  `destination` VARCHAR(500) NOT NULL,
  `travelDates` VARCHAR(100) DEFAULT '',
  `budget` VARCHAR(100) DEFAULT '',
  `accommodationSharing` VARCHAR(50) DEFAULT '',
  `propertyType` VARCHAR(100) DEFAULT '',
  `tripId` VARCHAR(255) DEFAULT '',
  `tripName` VARCHAR(500) DEFAULT '',
  `status` ENUM('new', 'contacted', 'quoted', 'booked', 'cancelled') DEFAULT 'new',
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`),
  INDEX `idx_tripId` (`tripId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;