-- =============================================
-- WILD DUBAI — SEED DATA SCRIPT
-- Safe to re-run: uses INSERT IGNORE / ON DUPLICATE KEY
-- Roles: 1=super_admin, 2=developer, 3=agency, 4=agent, 5=customer
-- Users 1-9 assumed to already exist in the DB
-- =============================================


-- =============================================
-- STEP 1: LOCATIONS (required FK for projects & units)
-- =============================================
INSERT IGNORE INTO locations (id, name, slug, location_type, emirate, latitude, longitude, is_active) VALUES
(1,  'Dubai Marina',      'dubai-marina',      'area', 'Dubai', 25.0805,  55.1403, 1),
(2,  'Downtown Dubai',    'downtown-dubai',    'area', 'Dubai', 25.1972,  55.2744, 1),
(3,  'Palm Jumeirah',     'palm-jumeirah',     'area', 'Dubai', 25.1124,  55.1390, 1),
(4,  'Business Bay',      'business-bay',      'area', 'Dubai', 25.1851,  55.2708, 1),
(5,  'Arabian Ranches',   'arabian-ranches',   'area', 'Dubai', 25.0522,  55.2708, 1),
(6,  'Jumeirah Village Circle', 'jvc',         'area', 'Dubai', 25.0657,  55.2100, 1);

-- =============================================
-- STEP 3: USER_ROLES junction
-- =============================================
INSERT IGNORE INTO user_roles (user_id, role_id) VALUES
(1, 1), (2, 2), (3, 2), (4, 3), (5, 4), (6, 4), (7, 5), (8, 5), (9, 5);

-- =============================================
-- STEP 4: DEVELOPERS (linked to user IDs 2 & 3)
-- =============================================
INSERT IGNORE INTO developers
  (id, name, slug, description, email, phone, website,
   headquarters_address, established_year, total_projects_completed,
   total_units_delivered, owner_id, status)
VALUES
(1,
 'Emaar Premium Developments',
 'emaar-premium-developments',
 'One of Dubai''s most iconic luxury real estate developers, delivering world-class residential and commercial projects since 1997.',
 'developer@emaar.ae', '+97142222255', 'https://emaar.com',
 'Emaar Square, Downtown Dubai, UAE',
 1997, 85, 42000, 2, 'active'),

(2,
 'Nakheel Grand Properties',
 'nakheel-grand-properties',
 'Visionary developer behind Palm Jumeirah and Dubai''s most ambitious waterfront communities.',
 'developer2@nakheel.ae', '+97143333366', 'https://nakheel.com',
 'Nakheel Tower, Palm Jumeirah, Dubai, UAE',
 2000, 60, 28000, 3, 'active');

-- =============================================
-- STEP 5: AGENCIES (linked to user ID 4)
-- =============================================
INSERT IGNORE INTO agencies
  (id, name, slug, description, email, phone, website,
   address, city, orn, rera_verified_at, rera_verification_status,
   trade_license_number, owner_id, status)
VALUES
(1,
 'Luxe Realty Dubai',
 'luxe-realty-dubai',
 'Dubai''s premier boutique real estate agency specialising in ultra-luxury residential and off-plan properties.',
 'agency@luxerealty.ae', '+97144556677', 'https://luxerealty.ae',
 'Level 12, DIFC Gate Building, Dubai', 'Dubai',
 'ORN-12345', NOW(), 'verified',
 'TL-2019-88765', 4, 'active');

-- =============================================
-- STEP 6: AGENCY_AGENTS (link agents 5 & 6 to agency 1)
-- =============================================
INSERT IGNORE INTO agency_agents
  (agency_id, agent_id, designation, commission_split,
   specializes_luxury, specializes_residential, specializes_off_plan, is_active)
VALUES
(1, 5, 'Senior Luxury Consultant', 60.00, TRUE, TRUE, TRUE,  TRUE),
(1, 6, 'Off-Plan Specialist',      55.00, TRUE, TRUE, TRUE,  TRUE);

-- =============================================
-- STEP 7: PROJECTS (linked to developers 1 & 2)
-- =============================================
INSERT IGNORE INTO projects
  (id, name, slug, description, developer_id, project_type,
   total_units, total_floors, launch_date, completion_date, handover_date,
   completion_status, construction_progress,
   location_id, address, latitude, longitude,
   min_price, max_price, currency,
   cover_image_url, rera_permit_number, escrow_account_number,
   status, is_featured, amenities_json)
VALUES
(1,
 'Marina Burj Vista',
 'marina-burj-vista',
 'An architectural masterpiece rising 72 floors above Dubai Marina, offering panoramic sea and skyline views. Features ultra-luxury residences with private pools, concierge services, and direct marina access.',
 1, 'residential',
 320, 72, '2023-01-15', '2026-12-31', '2027-03-31',
 'under_construction', 68.00,
 1, 'Plot 7, Dubai Marina Walk, Dubai Marina, UAE',
 25.0805, 55.1403,
 2500000.00, 45000000.00, 'AED',
 'https://images.unsplash.com/photo-1545324418-cc1a3fa10c00?w=1200&q=80',
 'RERA-PRJ-2023-001', 'ESC-2023-MBV-001',
 'active', TRUE,
 '[3, 4, 5, 6, 7, 11, 12]'),

(2,
 'Downtown Elegance Residences',
 'downtown-elegance-residences',
 'Nestled in the heart of Downtown Dubai, these signature residences offer unobstructed views of the Burj Khalifa and Dubai Fountain. A curated collection of 1 to 5-bedroom apartments and penthouses.',
 1, 'residential',
 180, 55, '2022-06-01', '2025-12-31', '2026-06-30',
 'under_construction', 85.00,
 2, 'Mohammed Bin Rashid Boulevard, Downtown Dubai, UAE',
 25.1972, 55.2744,
 1800000.00, 38000000.00, 'AED',
 'https://images.unsplash.com/photo-1613490493576-7fde63acd811?w=1200&q=80',
 'RERA-PRJ-2022-002', 'ESC-2022-DER-002',
 'active', TRUE,
 '[3, 4, 5, 6, 7, 8, 11, 12]'),

(3,
 'Palm Azure Villas',
 'palm-azure-villas',
 'Exclusive beachfront villas on the iconic Palm Jumeirah fronds. Each villa features a private beach, infinity pool, and 6-car garage. The pinnacle of Dubai waterfront living.',
 2, 'residential',
 48, 3, '2023-09-01', '2026-06-30', '2026-09-30',
 'off_plan', 35.00,
 3, 'Frond K, Palm Jumeirah, Dubai, UAE',
 25.1124, 55.1390,
 18000000.00, 75000000.00, 'AED',
 'https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=1200&q=80',
 'RERA-PRJ-2023-003', 'ESC-2023-PAV-003',
 'active', TRUE,
 '[3, 4, 5, 6, 7, 8, 9, 10, 11]');

-- =============================================
-- STEP 8: UNITS (5 premium properties)
-- property_type_id: 1=apartment, 2=villa, 3=townhouse, 4=penthouse, 5=studio
-- agent_id: 5 (Sara Hassan) or 6 (James Mitchell)
-- agency_id: 1 (Luxe Realty Dubai)
-- =============================================
INSERT IGNORE INTO units
  (id, unit_number, title, slug, description,
   project_id, listing_type, agent_id, agency_id, developer_id,
   property_type_id, bedrooms, bathrooms, area_sqft, area_sqm,
   floor_number, total_floors, parking_spots,
   completion_status, price, price_per_sqft, currency,
   payment_plan_available, down_payment_percent,
   location_id, address, latitude, longitude,
   view_type, furnished,
   images_json, amenities_json,
   rera_permit_number, rera_verified_at,
   is_active, is_featured, is_premium,
   view_count, inquiry_count)
VALUES

-- Unit 1: Penthouse in Marina Burj Vista
(1,
 'PH-7201',
 'Marina Burj Vista — Sky Penthouse',
 'marina-burj-vista-sky-penthouse',
 'The crown jewel of Marina Burj Vista. This 4-bedroom sky penthouse spans the entire 72nd floor with 360° panoramic views of the Arabian Gulf, Dubai Marina, and the city skyline. Features a private rooftop terrace with a plunge pool, chef''s kitchen, and bespoke Italian marble finishes throughout.',
 1, 'sale', 5, 1, 1,
 4, 4, 5.0, 8400, 780.5,
 72, 72, 4,
 'under_construction', 45000000.00, 5357.14, 'AED',
 TRUE, 20.00,
 1, 'Level 72, Marina Burj Vista, Dubai Marina, UAE',
 25.0805, 55.1403,
 '["sea", "marina", "city"]', 'fully_furnished',
 '[{"url":"https://images.unsplash.com/photo-1545324418-cc1a3fa10c00?w=1200&q=80","order":1,"is_featured":true},{"url":"https://images.unsplash.com/photo-1600607687939-ce8a6c25118c?w=1200&q=80","order":2,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600566753190-17f0baa2a6c3?w=1200&q=80","order":3,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600585154526-990dced4db0d?w=1200&q=80","order":4,"is_featured":false}]',
 '[3,4,5,6,7,8,11,12]',
 'RERA-UNIT-2023-001', NOW(),
 TRUE, TRUE, TRUE,
 1247, 38),

-- Unit 2: 3BR Apartment in Downtown Elegance
(2,
 'APT-3204',
 'Downtown Elegance — Burj View Apartment',
 'downtown-elegance-burj-view-apartment',
 'A stunning 3-bedroom apartment on the 32nd floor of Downtown Elegance Residences, offering direct views of the Burj Khalifa and Dubai Fountain. Designed by award-winning interior architects with floor-to-ceiling windows, a wraparound balcony, and smart home automation.',
 2, 'sale', 5, 1, 1,
 1, 3, 3.5, 3200, 297.3,
 32, 55, 2,
 'under_construction', 12500000.00, 3906.25, 'AED',
 TRUE, 15.00,
 2, 'Level 32, Downtown Elegance Residences, Downtown Dubai, UAE',
 25.1972, 55.2744,
 '["burj_khalifa", "fountain", "city"]', 'semi_furnished',
 '[{"url":"https://images.unsplash.com/photo-1613490493576-7fde63acd811?w=1200&q=80","order":1,"is_featured":true},{"url":"https://images.unsplash.com/photo-1564013799919-ab600027ffc6?w=1200&q=80","order":2,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600566753086-00f18fb6b3ea?w=1200&q=3","order":3,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=1200&q=80","order":4,"is_featured":false}]',
 '[3,4,5,6,7,11,12]',
 'RERA-UNIT-2022-002', NOW(),
 TRUE, TRUE, TRUE,
 892, 24);

-- Unit 3: Beachfront Villa on Palm Jumeirah
INSERT IGNORE INTO units
  (id, unit_number, title, slug, description,
   project_id, listing_type, agent_id, agency_id, developer_id,
   property_type_id, bedrooms, bathrooms, area_sqft, area_sqm,
   floor_number, total_floors, parking_spots,
   completion_status, price, price_per_sqft, currency,
   payment_plan_available, down_payment_percent,
   location_id, address, latitude, longitude,
   view_type, furnished,
   images_json, amenities_json,
   rera_permit_number, rera_verified_at,
   is_active, is_featured, is_premium,
   view_count, inquiry_count)
VALUES
(3,
 'VL-K07',
 'Palm Azure — Beachfront Signature Villa',
 'palm-azure-beachfront-signature-villa',
 'An extraordinary 6-bedroom beachfront villa on the prestigious Frond K of Palm Jumeirah. This architectural marvel features a 25-metre private infinity pool, direct beach access, a home cinema, wine cellar, and a rooftop sky lounge. Designed by Zaha Hadid Architects with bespoke finishes.',
 3, 'sale', 6, 1, 2,
 2, 6, 7.0, 12400, 1152.0,
 1, 3, 6,
 'off_plan', 68000000.00, 5483.87, 'AED',
 TRUE, 25.00,
 3, 'Frond K-07, Palm Jumeirah, Dubai, UAE',
 25.1124, 55.1390,
 '["sea", "beach", "skyline"]', 'unfurnished',
 '[{"url":"https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=1200&q=80","order":1,"is_featured":true},{"url":"https://images.unsplash.com/photo-1512917774080-9991f1c4c750?w=1200&q=80","order":2,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600607687939-ce8a6c25118c?w=1200&q=80","order":3,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600566753190-17f0baa2a6c3?w=1200&q=80","order":4,"is_featured":false}]',
 '[3,4,5,6,7,8,9,10,11]',
 'RERA-UNIT-2023-003', NOW(),
 TRUE, TRUE, TRUE,
 2103, 67),

-- Unit 4: 2BR Apartment in Business Bay (standalone, no project)
(4,
 'APT-1508',
 'Business Bay — Canal View Residence',
 'business-bay-canal-view-residence',
 'A sleek 2-bedroom apartment in the heart of Business Bay with stunning Dubai Canal views. Features an open-plan living area, a gourmet kitchen with Miele appliances, and a private balcony overlooking the water. Walking distance to Dubai Mall and Burj Khalifa.',
 NULL, 'sale', 6, 1, NULL,
 1, 2, 2.5, 1850, 171.9,
 15, 40, 1,
 'ready', 3200000.00, 1729.73, 'AED',
 FALSE, NULL,
 4, 'Tower 8, Business Bay, Dubai, UAE',
 25.1851, 55.2708,
 '["canal", "city"]', 'fully_furnished',
 '[{"url":"https://images.unsplash.com/photo-1564013799919-ab600027ffc6?w=1200&q=80","order":1,"is_featured":true},{"url":"https://images.unsplash.com/photo-1600566753086-00f18fb6b3ea?w=1200&q=80","order":2,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600585154526-990dced4db0d?w=1200&q=80","order":3,"is_featured":false}]',
 '[3,4,5,6,7,12]',
 'RERA-UNIT-2024-004', NOW(),
 TRUE, FALSE, FALSE,
 445, 12),

-- Unit 5: 5BR Villa in Arabian Ranches (standalone)
(5,
 'VL-AR-22',
 'Arabian Ranches — Desert Sanctuary Villa',
 'arabian-ranches-desert-sanctuary-villa',
 'A magnificent 5-bedroom villa in the exclusive Arabian Ranches community. Set on a 9,000 sqft plot with a private garden, temperature-controlled pool, and a dedicated maid''s quarter. Surrounded by lush landscaping and a championship golf course, offering the ultimate family sanctuary.',
 NULL, 'sale', 5, 1, NULL,
 2, 5, 5.0, 7800, 724.6,
 1, 2, 3,
 'ready', 9500000.00, 1217.95, 'AED',
 FALSE, NULL,
 5, 'Cluster 4, Arabian Ranches, Dubai, UAE',
 25.0522, 55.2708,
 '["garden", "golf_course"]', 'semi_furnished',
 '[{"url":"https://images.unsplash.com/photo-1512917774080-9991f1c4c750?w=1200&q=80","order":1,"is_featured":true},{"url":"https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=1200&q=80","order":2,"is_featured":false},{"url":"https://images.unsplash.com/photo-1600566753190-17f0baa2a6c3?w=1200&q=80","order":3,"is_featured":false}]',
 '[3,4,5,6,7,8,9,10,11,12]',
 'RERA-UNIT-2024-005', NOW(),
 TRUE, TRUE, FALSE,
 678, 19);

-- =============================================
-- STEP 9: AMENITIES (extend the schema defaults)
-- Schema already inserts IDs 1-12; add extras safely
-- =============================================
INSERT IGNORE INTO amenities (id, name, category, sort_order) VALUES
(1,  'Security',          'security',     1),
(2,  'CCTV',              'security',     2),
(3,  'Swimming Pool',     'leisure',      3),
(4,  'Gym',               'leisure',      4),
(5,  'Parking',           'convenience',  5),
(6,  'Balcony',           'convenience',  6),
(7,  'Central AC',        'convenience',  7),
(8,  'Maid Room',         'convenience',  8),
(9,  'Study Room',        'convenience',  9),
(10, 'Pets Allowed',      'policy',       10),
(11, 'Concierge',         'services',     11),
(12, 'Elevator',          'convenience',  12),
(13, 'Valet Parking',     'services',     13),
(14, 'Private Beach',     'leisure',      14),
(15, 'Infinity Pool',     'leisure',      15),
(16, 'Home Cinema',       'leisure',      16),
(17, 'Smart Home',        'technology',   17),
(18, 'Wine Cellar',       'leisure',      18),
(19, 'Rooftop Terrace',   'leisure',      19),
(20, 'BBQ Area',          'leisure',      20),
(21, 'Kids Play Area',    'leisure',      21),
(22, 'Sauna & Steam',     'leisure',      22),
(23, 'Business Lounge',   'services',     23),
(24, 'EV Charging',       'convenience',  24);

-- =============================================
-- STEP 10: SUBSCRIPTIONS for agents (free plan)
-- =============================================
INSERT IGNORE INTO subscriptions
  (subscriber_type, subscriber_id, plan_id, billing_cycle, amount,
   status, current_period_starts_at, current_period_ends_at)
VALUES
('agent', 5, 1, 'monthly', 0.00, 'active', NOW(), DATE_ADD(NOW(), INTERVAL 10 YEAR)),
('agent', 6, 1, 'monthly', 0.00, 'active', NOW(), DATE_ADD(NOW(), INTERVAL 10 YEAR));

-- =============================================
-- STEP 11: SAMPLE PROPERTY INQUIRIES
-- =============================================
INSERT IGNORE INTO property_inquiries
  (id, customer_id, unit_id, message, inquiry_type,
   preferred_contact_method, status, created_at)
VALUES
(1, 7, 1, 'I am very interested in the Sky Penthouse. Can we schedule a private viewing this weekend?',
 'schedule_viewing', 'whatsapp', 'pending', NOW()),
(2, 8, 2, 'Please provide the full payment plan details and floor plan for the Burj View Apartment.',
 'more_info', 'email', 'responded', NOW()),
(3, 9, 3, 'What is the earliest handover date for the Palm Azure Villa? We are ready to proceed.',
 'availability', 'phone', 'pending', NOW()),
(4, 7, 5, 'Is the Arabian Ranches villa negotiable on price? We are cash buyers.',
 'price_quote', 'whatsapp', 'pending', NOW());

-- =============================================
-- STEP 12: SAMPLE FAVORITES
-- =============================================
INSERT IGNORE INTO favorites (customer_id, unit_id) VALUES
(7, 1), (7, 3), (8, 2), (8, 4), (9, 3), (9, 5);

