-- DXB Yachts – Seed data for all sections
-- Run after schema (and featured_yachts_migration if needed).
-- Images must exist in backend/storage/uploads/images/ (run: bash backend/scripts/copy-feed-images.sh)
-- Usage: mysql -u root -p dxb-yachts < backend/sql/seed.sql


-- ========== Locations (search dropdown + booking) ==========
INSERT IGNORE INTO `locations` (`name`, `sort_order`) VALUES
('Dubai Marina', 0),
('Palm Jumeirah', 1),
('JBR (Jumeirah Beach Residence)', 2),
('Dubai Creek', 3),
('Port Rashid', 4),
('Al Seef', 5);

-- ========== Occasion types (search dropdown + booking) ==========
INSERT IGNORE INTO `occasion_types` (`name`, `sort_order`) VALUES
('Birthday', 0),
('Anniversary', 1),
('Corporate Event', 2),
('Wedding', 3),
('Family Trip', 4),
('Sunset Cruise', 5);

-- ========== Yacht types (from feed folder names: Motor Yacht, Sailing Yacht, etc.) ==========
INSERT INTO `yacht_types` (`slug`, `name`, `description`, `slogan`, `image_path`, `hero_image_path`, `sort_order`) VALUES
('motor-yacht', 'Motor Yacht', 'Enjoy fast cruising with style and comfort, perfect for family trips or private getaways.', 'Speed, Luxury, Adventure', '/storage/uploads/images/motor-yacht-card.png', '/storage/uploads/images/motor-yacht-card.png', 0),
('sailing-yacht', 'Sailing Yacht', 'Experience the joy of sailing with graceful design and serene ocean vibes.', 'Classic Elegance at Sea', '/storage/uploads/images/sailing-yacht-card.png', '/storage/uploads/images/sailing-yacht-card.png', 1),
('catamaran-yacht', 'Catamaran Yacht', 'Ideal for group trips and parties, offering ample space and smooth sailing.', 'Spacious & Stable', '/storage/uploads/images/catamaran-yacht-card.png', '/storage/uploads/images/catamaran-yacht-card.png', 2),
('luxury-yacht', 'Luxury Yacht', 'Pamper yourself with top-tier amenities, fine dining, and personalized service.', 'Ultimate Comfort & Style', '/storage/uploads/images/luxury-yacht-card.png', '/storage/uploads/images/luxury-yacht-card.png', 3),
('sport-yacht', 'Sport Yacht', 'Perfect for speed lovers and water sports enthusiasts, combining fun and performance.', 'Thrill on the Water', '/storage/uploads/images/sport-yacht-card.png', '/storage/uploads/images/sport-yacht-card.png', 4)
ON DUPLICATE KEY UPDATE
  `name` = VALUES(`name`),
  `description` = VALUES(`description`),
  `slogan` = VALUES(`slogan`),
  `image_path` = VALUES(`image_path`),
  `hero_image_path` = VALUES(`hero_image_path`),
  `sort_order` = VALUES(`sort_order`);

-- ========== Yachts (8 per type = 40 total; one image per yacht from feed 1.png–8.png) ==========
-- Remove existing seed yachts (names like "Motor Yacht 1" … "Sport Yacht 8") and their images
DELETE im FROM `yacht_images` im
JOIN `yachts` y ON y.id = im.yacht_id
JOIN `yacht_types` t ON t.id = y.yacht_type_id
WHERE t.slug IN ('motor-yacht', 'sailing-yacht', 'catamaran-yacht', 'luxury-yacht', 'sport-yacht')
  AND y.name REGEXP '^(Motor Yacht|Sailing Yacht|Catamaran Yacht|Luxury Yacht|Sport Yacht) [1-8]$';
DELETE y FROM `yachts` y
JOIN `yacht_types` t ON t.id = y.yacht_type_id
WHERE t.slug IN ('motor-yacht', 'sailing-yacht', 'catamaran-yacht', 'luxury-yacht', 'sport-yacht')
  AND y.name REGEXP '^(Motor Yacht|Sailing Yacht|Catamaran Yacht|Luxury Yacht|Sport Yacht) [1-8]$';

-- Insert 8 yachts per type; first of each type is featured for home page
INSERT INTO `yachts` (`yacht_type_id`, `name`, `price`, `sort_order`, `is_featured`, `featured_sort_order`)
SELECT t.id, CONCAT(t.name, ' ', n.n), 159.00 + (t.sort_order * 18) + (n.n - 1) * 3, n.n - 1, IF(n.n = 1, 1, 0), IF(n.n = 1, t.sort_order, 99)
FROM `yacht_types` t
CROSS JOIN (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) n
WHERE t.slug IN ('motor-yacht', 'sailing-yacht', 'catamaran-yacht', 'luxury-yacht', 'sport-yacht');

-- Yacht images: one image per yacht (yacht "Motor Yacht 1" → motor-yacht-1.png, etc.)
INSERT INTO `yacht_images` (`yacht_id`, `file_path`, `sort_order`)
SELECT y.id, CONCAT('/storage/uploads/images/', t.slug, '-', SUBSTRING_INDEX(y.name, ' ', -1), '.png'), 0
FROM `yachts` y
JOIN `yacht_types` t ON t.id = y.yacht_type_id
WHERE t.slug IN ('motor-yacht', 'sailing-yacht', 'catamaran-yacht', 'luxury-yacht', 'sport-yacht')
  AND y.name REGEXP ' [1-8]$';

-- ========== Services (with images from uploads) ==========
DELETE FROM `services`;
INSERT INTO `services` (`name`, `description`, `image_path`, `sort_order`) VALUES
('Private Charter', 'Exclusive use of your chosen yacht with a dedicated crew for a custom itinerary.', '/storage/uploads/images/motor-yacht-1.png', 0),
('Corporate Events', 'Impress clients and colleagues with a professional setting on the water.', '/storage/uploads/images/luxury-yacht-2.png', 1),
('Wedding & Celebrations', 'Make your special day unforgettable with a luxury yacht celebration.', '/storage/uploads/images/sailing-yacht-3.png', 2),
('Sunset Cruises', 'Relax and enjoy Dubai\'s skyline and coastline at golden hour.', '/storage/uploads/images/sport-yacht-4.png', 3);

-- ========== Social links ==========
DELETE FROM `social_links`;
INSERT INTO `social_links` (`platform`, `url`, `sort_order`) VALUES
('Facebook', 'https://www.facebook.com/', 0),
('Instagram', 'https://www.instagram.com/', 1),
('Twitter', 'https://twitter.com/', 2),
('YouTube', 'https://www.youtube.com/', 3);

-- ========== Testimonials (home page carousel) ==========
DELETE FROM `testimonials`;
INSERT INTO `testimonials` (`image_path`, `name`, `title`, `paragraph`, `sort_order`) VALUES
('/storage/uploads/images/motor-yacht-1.png', 'Jerrmy Jere', 'Lawyer', 'Our trip with DXB Yachts was absolutely amazing. The yacht was spotless, the crew was professional, and every detail was perfectly arranged. It was a truly luxurious and relaxing experience.', 0),
('/storage/uploads/images/luxury-yacht-2.png', 'James Wilson', 'Entrepreneur', 'Exceptional service from start to finish. The team went above and beyond to make our corporate event unforgettable. Highly recommend DXB Yachts for any occasion.', 1),
('/storage/uploads/images/sailing-yacht-3.png', 'Sarah Mitchell', 'Marketing Director', 'We celebrated our anniversary on one of their luxury yachts. Stunning views, impeccable hospitality, and memories we will cherish forever. Thank you, DXB Yachts!', 2),
('/storage/uploads/images/sport-yacht-4.png', 'Michael Chen', 'Finance Executive', 'Professional, punctual, and the vessels are in pristine condition. The crew knew the waters and made our family trip smooth and enjoyable. Will book again.', 3);

-- ========== Hero (home page) ==========
DELETE FROM `hero`;
INSERT INTO `hero` (`type`, `image_path`, `video_url`, `video_path`) VALUES
('image', '/storage/uploads/images/hero-seed.png', NULL, NULL);

-- ========== Static pages (extend default slugs with content) ==========
-- ========== Booking availability (yacht type × location: all available) ==========
INSERT IGNORE INTO `booking_availability` (`yacht_type_id`, `location_id`, `is_available`)
SELECT yt.id, loc.id, 1
FROM `yacht_types` yt
CROSS JOIN `locations` loc;

-- ========== Yacht availability (per-yacht date ranges; seed yachts only) ==========
-- Remove existing availability for seed yachts so re-run is idempotent
DELETE a FROM `yacht_availability` a
JOIN `yachts` y ON y.id = a.yacht_id
JOIN `yacht_types` t ON t.id = y.yacht_type_id
WHERE t.slug IN ('motor-yacht', 'sailing-yacht', 'catamaran-yacht', 'luxury-yacht', 'sport-yacht')
  AND y.name REGEXP '^(Motor Yacht|Sailing Yacht|Catamaran Yacht|Luxury Yacht|Sport Yacht) [1-8]$';
-- One period per seed yacht: full year (non-overlapping)
INSERT INTO `yacht_availability` (`yacht_id`, `date_from`, `date_to`)
SELECT y.id, '2025-01-01', '2025-12-31'
FROM `yachts` y
JOIN `yacht_types` t ON t.id = y.yacht_type_id
WHERE t.slug IN ('motor-yacht', 'sailing-yacht', 'catamaran-yacht', 'luxury-yacht', 'sport-yacht')
  AND y.name REGEXP '^(Motor Yacht|Sailing Yacht|Catamaran Yacht|Luxury Yacht|Sport Yacht) [1-8]$';

-- ========== Static pages (extend default slugs with content) ==========
UPDATE `static_pages` SET `title` = 'Terms and Conditions', `body` = '<p>By using our yacht charter services you agree to our terms. Contact us for full details.</p>' WHERE `slug` = 'terms';
UPDATE `static_pages` SET `title` = 'Privacy Policy', `body` = '<p>We respect your privacy and protect your personal data in accordance with applicable laws.</p>' WHERE `slug` = 'privacy-policy';
UPDATE `static_pages` SET `title` = 'About Us', `body` = '<p>DXB Yachts offers premium yacht charters in Dubai. Experience luxury on the water with our modern fleet and professional crew.</p>' WHERE `slug` = 'about-us';
