-- Diaspora Portal Real Estate schema
-- Target database: MySQL 8+ / MariaDB 10.6+
-- Purpose: Replace the local PHP seed arrays and JSON request store with proper relational tables.
-- Product vision: partner-first, verified, due-diligence-led diaspora real estate marketplace.

CREATE TABLE IF NOT EXISTS real_estate_trust_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module_key VARCHAR(100) NOT NULL UNIQUE,
  eyebrow VARCHAR(160) NOT NULL,
  title VARCHAR(255) NOT NULL,
  summary TEXT NOT NULL,
  commitment TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_trust_sections (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module_key VARCHAR(100) NOT NULL,
  title VARCHAR(180) NOT NULL,
  body TEXT NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_trust_sections_module_sort (module_key, sort_order),
  CONSTRAINT fk_trust_sections_message
    FOREIGN KEY (module_key)
    REFERENCES real_estate_trust_messages(module_key)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_developers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  verification_status VARCHAR(80) NOT NULL DEFAULT 'Under Review',
  partner_rating_score DECIMAL(3,2) NOT NULL DEFAULT 0.00,
  partner_rating_label VARCHAR(120) NOT NULL DEFAULT 'Under Review',
  monitoring_status VARCHAR(120) NOT NULL DEFAULT 'Under Review',
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_developer_verification (verification_status),
  INDEX idx_developer_rating (partner_rating_score),
  INDEX idx_developer_monitoring (monitoring_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_properties (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id VARCHAR(120) NOT NULL UNIQUE,
  developer_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  road VARCHAR(160) NOT NULL,
  location VARCHAR(160) NOT NULL,
  property_type VARCHAR(80) NOT NULL,
  size_band VARCHAR(80) NOT NULL,
  project_status VARCHAR(80) NOT NULL,
  verification_status VARCHAR(80) NOT NULL,
  monitoring_status VARCHAR(120) NOT NULL,
  price_label VARCHAR(180) NOT NULL,
  budget_band VARCHAR(80) NOT NULL,
  member_benefit VARCHAR(180) NOT NULL,
  image_path VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  source_url VARCHAR(500) NULL,
  is_visible TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_properties_visible_sort (is_visible, sort_order),
  INDEX idx_properties_developer (developer_id),
  INDEX idx_properties_location (location),
  INDEX idx_properties_type (property_type),
  INDEX idx_properties_size (size_band),
  INDEX idx_properties_status (project_status),
  INDEX idx_properties_verification (verification_status),
  INDEX idx_properties_budget (budget_band),
  CONSTRAINT fk_properties_developer
    FOREIGN KEY (developer_id)
    REFERENCES real_estate_developers(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_property_bedrooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  bedroom_label VARCHAR(80) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_property_bedroom (property_id, bedroom_label),
  INDEX idx_property_bedrooms_sort (property_id, sort_order),
  CONSTRAINT fk_property_bedrooms_property
    FOREIGN KEY (property_id)
    REFERENCES real_estate_properties(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_property_amenities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  amenity VARCHAR(120) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_property_amenity (property_id, amenity),
  INDEX idx_property_amenities_sort (property_id, sort_order),
  CONSTRAINT fk_property_amenities_property
    FOREIGN KEY (property_id)
    REFERENCES real_estate_properties(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_property_due_diligence (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  checklist_item VARCHAR(220) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Captured',
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_property_due_diligence_sort (property_id, sort_order),
  CONSTRAINT fk_due_diligence_property
    FOREIGN KEY (property_id)
    REFERENCES real_estate_properties(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_property_monitoring (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  monitoring_item VARCHAR(220) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Active',
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_property_monitoring_sort (property_id, sort_order),
  CONSTRAINT fk_monitoring_property
    FOREIGN KEY (property_id)
    REFERENCES real_estate_properties(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_property_member_benefits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  benefit VARCHAR(220) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  INDEX idx_property_benefits_sort (property_id, sort_order),
  CONSTRAINT fk_member_benefits_property
    FOREIGN KEY (property_id)
    REFERENCES real_estate_properties(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_property_reviews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  review_note VARCHAR(240) NOT NULL,
  review_type VARCHAR(80) NOT NULL DEFAULT 'Community Trust',
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_property_reviews_sort (property_id, sort_order),
  CONSTRAINT fk_property_reviews_property
    FOREIGN KEY (property_id)
    REFERENCES real_estate_properties(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS real_estate_interest_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_uuid VARCHAR(160) NOT NULL UNIQUE,
  property_id BIGINT UNSIGNED NOT NULL,
  product VARCHAR(120) NOT NULL DEFAULT 'My Diaspora Real Estate',
  request_status VARCHAR(80) NOT NULL DEFAULT 'New',
  member_id VARCHAR(120) NOT NULL,
  service_assignment_id VARCHAR(160) NOT NULL,
  service_key VARCHAR(120) NOT NULL DEFAULT 'my-diaspora-real-estate',
  preferred_unit VARCHAR(80) NOT NULL,
  intent VARCHAR(80) NOT NULL,
  timeline VARCHAR(80) NOT NULL,
  wants_due_diligence_pack TINYINT(1) NOT NULL DEFAULT 1,
  wants_advisor_follow_up TINYINT(1) NOT NULL DEFAULT 1,
  member_message TEXT NULL,
  admin_notes TEXT NULL,
  assigned_advisor VARCHAR(180) NULL,
  next_follow_up_at VARCHAR(80) NULL,
  submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_interest_member (member_id),
  INDEX idx_interest_service_assignment (service_assignment_id),
  INDEX idx_interest_property (property_id),
  INDEX idx_interest_status (request_status),
  INDEX idx_interest_submitted (submitted_at),
  CONSTRAINT fk_interest_property
    FOREIGN KEY (property_id)
    REFERENCES real_estate_properties(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;