-- Diaspora Portal Real Estate seed data
-- Run after schema.sql.
-- This seed mirrors the current customer portal listings and trust framework.

INSERT INTO real_estate_trust_messages (module_key, eyebrow, title, summary, commitment)
VALUES (
  'my_diaspora_real_estate',
  'Invest back home safely',
  'Verified real estate access for diaspora members.',
  'My Diaspora Real Estate combines professional due diligence, community feedback, exclusive member benefits, and ongoing partner accountability so members can invest with greater confidence.',
  'Trust should never be optional. We reduce uncertainty through verification, transparency, community reviews, and continuous partner monitoring.'
)
ON DUPLICATE KEY UPDATE
  eyebrow = VALUES(eyebrow),
  title = VALUES(title),
  summary = VALUES(summary),
  commitment = VALUES(commitment);

INSERT INTO real_estate_trust_sections (module_key, title, body, sort_order)
VALUES
  ('my_diaspora_real_estate', 'Verified opportunities', 'Members access carefully reviewed developers, projects, and property opportunities before expressing interest.', 1),
  ('my_diaspora_real_estate', 'Due diligence', 'Each project view highlights document review, approvals, ownership checks, and site verification where practical.', 2),
  ('my_diaspora_real_estate', 'Partner accountability', 'Partner performance, member satisfaction, project delivery, and communication standards remain visible in the portal.', 3),
  ('my_diaspora_real_estate', 'Member-only value', 'Listings can show preferred pricing, flexible payment discussions, project incentives, and housewarming eligibility.', 4)
ON DUPLICATE KEY UPDATE
  body = VALUES(body),
  sort_order = VALUES(sort_order);

INSERT INTO real_estate_developers (id, name, verification_status, partner_rating_score, partner_rating_label, monitoring_status, notes)
VALUES
  (1, 'Approved Real Estate Partner', 'Verified', 4.60, 'Very Good Performance', 'Active Monitoring', 'Seed partner used for Kileleshwa portal properties.')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  verification_status = VALUES(verification_status),
  partner_rating_score = VALUES(partner_rating_score),
  partner_rating_label = VALUES(partner_rating_label),
  monitoring_status = VALUES(monitoring_status),
  notes = VALUES(notes);

INSERT INTO real_estate_properties (
  public_id,
  developer_id,
  name,
  road,
  location,
  property_type,
  project_status,
  verification_status,
  monitoring_status,
  price_label,
  budget_band,
  member_benefit,
  image_path,
  description,
  source_url,
  is_visible,
  sort_order
)
VALUES
  ('danaff-towers', 1, 'Danaff Towers', 'KANGUNDO RD', 'Kileleshwa', 'Apartment', 'Complete', 'Verified', 'On Track', 'USD 5M | 7.5M | 9.5M | 12.8M', '5M-15M', 'Exclusive member pricing', '/images/real-estate/kileleshwa/danaff.png', 'Spacious studios, 1, 2 & 3 bedrooms. It has a reception, gym, chilling & relaxing areas, 24/7 security, rooftop terrace, borehole, lifts, backup generator, etc.', 'https://gna.co.ke/project/danaff-towers-kileleshwa-studios-1-and-2-bedrooms/', 1, 1),
  ('siaya-park', 1, 'Siaya Park Apartments', 'OLOITOKTOK RD', 'Kileleshwa', 'Apartment', 'Complete', 'Verified', 'On Track', 'USD 7.5M / USD 13.5M', '5M-15M', 'Flexible payment option', '/images/real-estate/kileleshwa/siaya-park.png', 'Stunning 1 & 2 bedrooms. It has a pool, kids play area, gym, borehole, generator, intercom, access control, manned security etc.', 'https://gna.co.ke/project/siaya-park-apartments-kileleshwa-oloitoktok-road/', 1, 2),
  ('spring-garden', 1, 'Spring Garden Apartments', 'KIKAMBALA RD', 'Kileleshwa', 'Apartment', 'Complete', 'Verified', 'On Track', 'From USD 7M', '5M-15M', 'Exclusive member pricing', '/images/real-estate/kileleshwa/spring-garden.png', 'Stunning 1, 2, 3, 4 and 5 bedrooms. It has a pool, gym, intercom, borehole, generator, manned security, CCTV & access control etc.', 'https://gna.co.ke/project/spring-garden-apartments-kileleshwa/', 1, 3),
  ('bahari-homes', 1, 'Bahari Homes', 'KIRICHWA RD', 'Kileleshwa', 'Apartment', 'Featured', 'Verified', 'Featured Partner', 'From USD 14.5M', '10M-20M', 'Housewarming eligible', '/images/real-estate/kileleshwa/bahari-homes.jpg', 'Spacious 2 & 3 beds plus DSQ. It has a heated pool, gym, kids cycling areas, intercom, borehole, generator, access control, manned security & 24/7 security, large garden etc.', 'https://gna.co.ke/project/bahari-homes-apartments-kileleshwa-2-3-beds-plus-dsq/', 1, 4),
  ('alina-ridge', 1, 'Alina Ridge Apartments', 'OLOITOKTOK RD', 'Kileleshwa', 'Apartment', 'Complete', 'Verified', 'On Track', 'USD 6M', 'Under 10M', 'Flexible payment option', '/images/real-estate/kileleshwa/alina.png', 'Spacious studio. It has a kids care center, modern gym, cafeteria, restaurant, rooftop football pitch & recreation center, garden, borehole, landscaped zen garden, etc.', 'https://gna.co.ke/project/alina-ridge-apartments-kileleshwa-1-2-3-beds/', 1, 5),
  ('kaisa-gardens', 1, 'Kaisa Gardens', 'LAIKIPIA RD', 'Kileleshwa', 'Apartment', 'Complete', 'Verified', 'On Track', 'USD 13.5M', '10M-20M', 'Housewarming eligible', '/images/real-estate/kileleshwa/kaisa-gardens.png', '3 bedrooms, 2 en-suite. It has a kids play area, equipped gym, borehole, chilling spots, landscaped garden, access control, manned security etc.', 'https://gna.co.ke/project/kaisa-garden-apartments-in-kileleshwa-along-laikipia-road/', 1, 6)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  road = VALUES(road),
  location = VALUES(location),
  property_type = VALUES(property_type),
  project_status = VALUES(project_status),
  verification_status = VALUES(verification_status),
  monitoring_status = VALUES(monitoring_status),
  price_label = VALUES(price_label),
  budget_band = VALUES(budget_band),
  member_benefit = VALUES(member_benefit),
  image_path = VALUES(image_path),
  description = VALUES(description),
  source_url = VALUES(source_url),
  is_visible = VALUES(is_visible),
  sort_order = VALUES(sort_order);

INSERT INTO real_estate_property_bedrooms (property_id, bedroom_label, sort_order)
SELECT p.id, b.bedroom_label, b.sort_order
FROM real_estate_properties p
JOIN (
  SELECT 'danaff-towers' public_id, 'Studio' bedroom_label, 1 sort_order UNION ALL
  SELECT 'danaff-towers', '1 Bed', 2 UNION ALL
  SELECT 'danaff-towers', '2 Bed', 3 UNION ALL
  SELECT 'danaff-towers', '3 Bed', 4 UNION ALL
  SELECT 'siaya-park', '1 Bed', 1 UNION ALL
  SELECT 'siaya-park', '2 Bed', 2 UNION ALL
  SELECT 'spring-garden', '1 Bed', 1 UNION ALL
  SELECT 'spring-garden', '2 Bed', 2 UNION ALL
  SELECT 'spring-garden', '3 Bed', 3 UNION ALL
  SELECT 'spring-garden', '4 Bed', 4 UNION ALL
  SELECT 'spring-garden', '5 Bed', 5 UNION ALL
  SELECT 'bahari-homes', '2 Bed', 1 UNION ALL
  SELECT 'bahari-homes', '3 Bed', 2 UNION ALL
  SELECT 'alina-ridge', 'Studio', 1 UNION ALL
  SELECT 'alina-ridge', '1 Bed', 2 UNION ALL
  SELECT 'alina-ridge', '2 Bed', 3 UNION ALL
  SELECT 'alina-ridge', '3 Bed', 4 UNION ALL
  SELECT 'kaisa-gardens', '3 Bed', 1
) b ON p.public_id = b.public_id
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

INSERT INTO real_estate_property_amenities (property_id, amenity, sort_order)
SELECT p.id, a.amenity, a.sort_order
FROM real_estate_properties p
JOIN (
  SELECT 'danaff-towers' public_id, 'Reception' amenity, 1 sort_order UNION ALL
  SELECT 'danaff-towers', 'Gym', 2 UNION ALL
  SELECT 'danaff-towers', 'Rooftop terrace', 3 UNION ALL
  SELECT 'danaff-towers', 'Borehole', 4 UNION ALL
  SELECT 'danaff-towers', 'Lifts', 5 UNION ALL
  SELECT 'danaff-towers', 'Backup generator', 6 UNION ALL
  SELECT 'danaff-towers', '24/7 security', 7 UNION ALL
  SELECT 'siaya-park', 'Pool', 1 UNION ALL
  SELECT 'siaya-park', 'Kids play area', 2 UNION ALL
  SELECT 'siaya-park', 'Gym', 3 UNION ALL
  SELECT 'siaya-park', 'Borehole', 4 UNION ALL
  SELECT 'siaya-park', 'Generator', 5 UNION ALL
  SELECT 'siaya-park', 'Intercom', 6 UNION ALL
  SELECT 'siaya-park', 'Access control', 7 UNION ALL
  SELECT 'spring-garden', 'Pool', 1 UNION ALL
  SELECT 'spring-garden', 'Gym', 2 UNION ALL
  SELECT 'spring-garden', 'Intercom', 3 UNION ALL
  SELECT 'spring-garden', 'Borehole', 4 UNION ALL
  SELECT 'spring-garden', 'Generator', 5 UNION ALL
  SELECT 'spring-garden', 'CCTV', 6 UNION ALL
  SELECT 'spring-garden', 'Access control', 7 UNION ALL
  SELECT 'bahari-homes', 'Heated pool', 1 UNION ALL
  SELECT 'bahari-homes', 'Gym', 2 UNION ALL
  SELECT 'bahari-homes', 'Kids cycling area', 3 UNION ALL
  SELECT 'bahari-homes', 'Borehole', 4 UNION ALL
  SELECT 'bahari-homes', 'Generator', 5 UNION ALL
  SELECT 'bahari-homes', 'Large garden', 6 UNION ALL
  SELECT 'bahari-homes', '24/7 security', 7 UNION ALL
  SELECT 'alina-ridge', 'Kids care center', 1 UNION ALL
  SELECT 'alina-ridge', 'Modern gym', 2 UNION ALL
  SELECT 'alina-ridge', 'Cafeteria', 3 UNION ALL
  SELECT 'alina-ridge', 'Restaurant', 4 UNION ALL
  SELECT 'alina-ridge', 'Rooftop pitch', 5 UNION ALL
  SELECT 'alina-ridge', 'Garden', 6 UNION ALL
  SELECT 'alina-ridge', 'Borehole', 7 UNION ALL
  SELECT 'kaisa-gardens', 'Kids play area', 1 UNION ALL
  SELECT 'kaisa-gardens', 'Equipped gym', 2 UNION ALL
  SELECT 'kaisa-gardens', 'Borehole', 3 UNION ALL
  SELECT 'kaisa-gardens', 'Chilling spots', 4 UNION ALL
  SELECT 'kaisa-gardens', 'Landscaped garden', 5 UNION ALL
  SELECT 'kaisa-gardens', 'Access control', 6 UNION ALL
  SELECT 'kaisa-gardens', 'Manned security', 7
) a ON p.public_id = a.public_id
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

INSERT INTO real_estate_property_due_diligence (property_id, checklist_item, status, sort_order)
SELECT p.id, d.checklist_item, 'Captured', d.sort_order
FROM real_estate_properties p
JOIN (
  SELECT 'Company registration reviewed' checklist_item, 1 sort_order UNION ALL
  SELECT 'Compliance documents reviewed', 2 UNION ALL
  SELECT 'Ownership and title review', 3 UNION ALL
  SELECT 'Project approvals reviewed', 4 UNION ALL
  SELECT 'Site verification where practical', 5
) d
ON DUPLICATE KEY UPDATE status = VALUES(status), sort_order = VALUES(sort_order);

INSERT INTO real_estate_property_monitoring (property_id, monitoring_item, status, sort_order)
SELECT p.id, m.monitoring_item, 'Active', m.sort_order
FROM real_estate_properties p
JOIN (
  SELECT 'Project delivery progress monitored' monitoring_item, 1 sort_order UNION ALL
  SELECT 'Member satisfaction monitored', 2 UNION ALL
  SELECT 'Service quality monitored', 3 UNION ALL
  SELECT 'Communication standards monitored', 4
) m
ON DUPLICATE KEY UPDATE status = VALUES(status), sort_order = VALUES(sort_order);

INSERT INTO real_estate_property_member_benefits (property_id, benefit, sort_order)
SELECT p.id, mb.benefit, mb.sort_order
FROM real_estate_properties p
JOIN (
  SELECT 'danaff-towers' public_id, 'Preferential pricing review' benefit, 1 sort_order UNION ALL
  SELECT 'danaff-towers', 'Flexible payment discussion', 2 UNION ALL
  SELECT 'danaff-towers', 'Housewarming eligibility review', 3 UNION ALL
  SELECT 'siaya-park', 'Member offer review', 1 UNION ALL
  SELECT 'siaya-park', 'Flexible payment discussion', 2 UNION ALL
  SELECT 'siaya-park', 'Housewarming eligibility review', 3 UNION ALL
  SELECT 'spring-garden', 'Preferential pricing review', 1 UNION ALL
  SELECT 'spring-garden', 'Special incentive review', 2 UNION ALL
  SELECT 'spring-garden', 'Housewarming eligibility review', 3 UNION ALL
  SELECT 'bahari-homes', 'Exclusive discount review', 1 UNION ALL
  SELECT 'bahari-homes', 'Flexible payment discussion', 2 UNION ALL
  SELECT 'bahari-homes', 'Housewarming gift eligibility review', 3 UNION ALL
  SELECT 'alina-ridge', 'Member price guidance', 1 UNION ALL
  SELECT 'alina-ridge', 'Flexible payment review', 2 UNION ALL
  SELECT 'alina-ridge', 'Housewarming eligibility review', 3 UNION ALL
  SELECT 'kaisa-gardens', 'Negotiated member offer', 1 UNION ALL
  SELECT 'kaisa-gardens', 'Reduced acquisition cost review', 2 UNION ALL
  SELECT 'kaisa-gardens', 'Housewarming gift eligibility review', 3
) mb ON p.public_id = mb.public_id;

INSERT INTO real_estate_property_reviews (property_id, review_note, review_type, sort_order)
SELECT p.id, r.review_note, 'Community Trust', r.sort_order
FROM real_estate_properties p
JOIN (
  SELECT 'danaff-towers' public_id, 'Verified buyer reviews enabled' review_note, 1 sort_order UNION ALL
  SELECT 'danaff-towers', 'Project experience feedback monitored', 2 UNION ALL
  SELECT 'siaya-park', 'Verified buyer reviews enabled', 1 UNION ALL
  SELECT 'siaya-park', 'Customer service feedback monitored', 2 UNION ALL
  SELECT 'spring-garden', 'Project delivery feedback enabled', 1 UNION ALL
  SELECT 'spring-garden', 'Handover experience feedback monitored', 2 UNION ALL
  SELECT 'bahari-homes', 'Verified buyer reviews enabled', 1 UNION ALL
  SELECT 'bahari-homes', 'Member satisfaction feedback monitored', 2 UNION ALL
  SELECT 'alina-ridge', 'Project experience reviews enabled', 1 UNION ALL
  SELECT 'alina-ridge', 'Communication standards monitored', 2 UNION ALL
  SELECT 'kaisa-gardens', 'Verified buyer reviews enabled', 1 UNION ALL
  SELECT 'kaisa-gardens', 'Property handover feedback monitored', 2
) r ON p.public_id = r.public_id;

INSERT INTO real_estate_interest_requests (
  request_uuid,
  property_id,
  product,
  request_status,
  member_id,
  service_assignment_id,
  service_key,
  preferred_unit,
  intent,
  timeline,
  wants_due_diligence_pack,
  wants_advisor_follow_up,
  member_message,
  admin_notes,
  assigned_advisor,
  next_follow_up_at
)
SELECT
  'real-estate-interest-seed-danaff',
  p.id,
  'My Diaspora Real Estate',
  'In Review',
  'member-lameck-nyakweba',
  'service-real-estate-lameck',
  'my-diaspora-real-estate',
  '2 Bed',
  'Buy',
  '0-3 months',
  1,
  1,
  'Please prioritize title documentation and expected handover checks.',
  'Member intent received. Due diligence brief being compiled.',
  'Advisor Jane Mwangi',
  '2026-06-15T09:30:00Z'
FROM real_estate_properties p
WHERE p.public_id = 'danaff-towers'
ON DUPLICATE KEY UPDATE
  request_status = VALUES(request_status),
  member_id = VALUES(member_id),
  service_assignment_id = VALUES(service_assignment_id),
  preferred_unit = VALUES(preferred_unit),
  intent = VALUES(intent),
  timeline = VALUES(timeline),
  wants_due_diligence_pack = VALUES(wants_due_diligence_pack),
  wants_advisor_follow_up = VALUES(wants_advisor_follow_up),
  member_message = VALUES(member_message),
  admin_notes = VALUES(admin_notes),
  assigned_advisor = VALUES(assigned_advisor),
  next_follow_up_at = VALUES(next_follow_up_at);