-- NRT SA Database Structure
-- Import this file into your cPanel MySQL database

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- Create the database (if not exists)
CREATE DATABASE IF NOT EXISTS `nrt_sa` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `nrt_sa`;

-- Users table
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehicles table
CREATE TABLE `vehicles` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `model` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `seating_capacity` int(11) NOT NULL,
  `luggage_space` varchar(255) NOT NULL,
  `amenities` json DEFAULT NULL,
  `daily_rate` decimal(10,2) NOT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `type` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bookings table
CREATE TABLE `bookings` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `pickup_date` date NOT NULL,
  `pickup_time` time NOT NULL,
  `dropoff_date` date NOT NULL,
  `dropoff_time` time NOT NULL,
  `special_requests` text DEFAULT NULL,
  `status` enum("pending","confirmed","completed","cancelled") NOT NULL DEFAULT "pending",
  `vehicle_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Testimonials table
CREATE TABLE `testimonials` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `testimonial` text NOT NULL,
  `rating` int(11) NOT NULL,
  `event_type` varchar(255) DEFAULT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `image_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Messages table
CREATE TABLE `messages` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `status` enum("new","read","replied") NOT NULL DEFAULT "new",
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Newsletters table
CREATE TABLE `newsletters` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `email` varchar(255) NOT NULL,
  `status` enum("active","inactive") NOT NULL DEFAULT "active",
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample data
INSERT INTO `vehicles` (`name`, `model`, `description`, `seating_capacity`, `luggage_space`, `daily_rate`, `type`, `is_available`, `created_at`, `updated_at`) VALUES
("Mercedes V-Class V300d", "V300d", "Our premium luxury van featuring the latest technology and comfort features.", 7, "Spacious", 2500.00, "v300d", 1, NOW(), NOW()),
("Mercedes V-Class V250d", "V250d", "Our accessible luxury option featuring comfort and style at an excellent value.", 7, "Generous", 2000.00, "v250d", 1, NOW(), NOW());

INSERT INTO `testimonials` (`customer_name`, `testimonial`, `rating`, `event_type`, `is_featured`, `created_at`, `updated_at`) VALUES
("John Smith", "Excellent service! The Mercedes V-Class was immaculate and the chauffeur was professional. Highly recommend for any special occasion.", 5, "Wedding", 1, NOW(), NOW()),
("Sarah Johnson", "Perfect for our corporate event. Punctual, clean, and comfortable. Will definitely use NRT SA again.", 5, "Corporate Event", 1, NOW(), NOW()),
("Michael Brown", "Outstanding luxury transportation. The attention to detail and customer service exceeded our expectations.", 5, "Airport Transfer", 1, NOW(), NOW());

-- Indexes
ALTER TABLE `users` ADD PRIMARY KEY (`id`);
ALTER TABLE `vehicles` ADD PRIMARY KEY (`id`);
ALTER TABLE `bookings` ADD PRIMARY KEY (`id`);
ALTER TABLE `testimonials` ADD PRIMARY KEY (`id`);
ALTER TABLE `messages` ADD PRIMARY KEY (`id`);
ALTER TABLE `newsletters` ADD PRIMARY KEY (`id`);

ALTER TABLE `users` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `vehicles` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `bookings` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `testimonials` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `messages` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `newsletters` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

-- Foreign keys
ALTER TABLE `bookings` ADD CONSTRAINT `bookings_vehicle_id_foreign` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL;

-- Unique constraints
ALTER TABLE `users` ADD UNIQUE KEY `users_email_unique` (`email`);
ALTER TABLE `newsletters` ADD UNIQUE KEY `newsletters_email_unique` (`email`);

COMMIT;