-- DXB Yachts Dashboard – MySQL schema
-- Run after creating database: CREATE DATABASE `dxb-yachts` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Leads (from Contact Us)
CREATE TABLE IF NOT EXISTS `leads` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`),
  KEY `subject` (`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Locations
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Occasion types
CREATE TABLE IF NOT EXISTS `occasion_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Yacht types
CREATE TABLE IF NOT EXISTS `yacht_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `slogan` varchar(255) DEFAULT NULL,
  `image_path` varchar(500) DEFAULT NULL,
  `hero_image_path` varchar(500) DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Yachts (per yacht type)
CREATE TABLE IF NOT EXISTS `yachts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `yacht_type_id` int unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `featured_sort_order` int NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `yacht_type_id` (`yacht_type_id`),
  KEY `is_featured` (`is_featured`),
  CONSTRAINT `yachts_yacht_type` FOREIGN KEY (`yacht_type_id`) REFERENCES `yacht_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Yacht images
CREATE TABLE IF NOT EXISTS `yacht_images` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `yacht_id` int unsigned NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `yacht_id` (`yacht_id`),
  CONSTRAINT `yacht_images_yacht` FOREIGN KEY (`yacht_id`) REFERENCES `yachts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bookings
CREATE TABLE IF NOT EXISTS `bookings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `yacht_id` int unsigned NOT NULL,
  `location_id` int unsigned NOT NULL,
  `occasion_type_id` int unsigned NOT NULL,
  `guest_name` varchar(255) NOT NULL,
  `guest_email` varchar(255) NOT NULL,
  `guest_phone` varchar(50) DEFAULT NULL,
  `booking_date` date NOT NULL,
  `date_to` date DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'pending',
  `notes` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `yacht_id` (`yacht_id`),
  KEY `location_id` (`location_id`),
  KEY `occasion_type_id` (`occasion_type_id`),
  KEY `booking_date` (`booking_date`),
  KEY `status` (`status`),
  CONSTRAINT `bookings_yacht` FOREIGN KEY (`yacht_id`) REFERENCES `yachts` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `bookings_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `bookings_occasion` FOREIGN KEY (`occasion_type_id`) REFERENCES `occasion_types` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Booking availability (yacht type × location)
CREATE TABLE IF NOT EXISTS `booking_availability` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `yacht_type_id` int unsigned NOT NULL,
  `location_id` int unsigned NOT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `yacht_type_location` (`yacht_type_id`,`location_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `avail_yacht_type` FOREIGN KEY (`yacht_type_id`) REFERENCES `yacht_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `avail_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Yacht availability (per-yacht date ranges: when a yacht is available)
CREATE TABLE IF NOT EXISTS `yacht_availability` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `yacht_id` int unsigned NOT NULL,
  `date_from` date NOT NULL,
  `date_to` date NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `yacht_id` (`yacht_id`),
  KEY `yacht_dates` (`yacht_id`, `date_from`, `date_to`),
  CONSTRAINT `yacht_availability_yacht` FOREIGN KEY (`yacht_id`) REFERENCES `yachts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Services
CREATE TABLE IF NOT EXISTS `services` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `image_path` varchar(500) DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Social links
CREATE TABLE IF NOT EXISTS `social_links` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `platform` varchar(50) NOT NULL,
  `url` varchar(500) NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `platform` (`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Static pages
CREATE TABLE IF NOT EXISTS `static_pages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` longtext,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Testimonials (home page carousel)
CREATE TABLE IF NOT EXISTS `testimonials` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `image_path` varchar(500) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `paragraph` text NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hero (home page)
CREATE TABLE IF NOT EXISTS `hero` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('image','video') NOT NULL DEFAULT 'image',
  `image_path` varchar(500) DEFAULT NULL,
  `video_url` varchar(500) DEFAULT NULL,
  `video_path` varchar(500) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Seed static page slugs
INSERT INTO `static_pages` (`slug`, `title`, `body`) VALUES
('terms', 'Terms and Conditions', ''),
('privacy-policy', 'Privacy Policy', ''),
('about-us', 'About Us', '')
ON DUPLICATE KEY UPDATE `slug` = `slug`;
