-- Adds the "New Development" section (off-plan / new / upcoming projects).
-- Run after sql/schema.sql: mysql --default-character-set=utf8mb4 -u root kigaliyacu < sql/migration_developments.sql

USE kigaliyacu;

ALTER TABLE leads MODIFY COLUMN type ENUM('contact','tour','valuation','list_property','brochure','general') NOT NULL DEFAULT 'general';

CREATE TABLE IF NOT EXISTS developments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  district_id INT UNSIGNED NOT NULL,
  cover_image VARCHAR(255) NOT NULL,
  starting_price DECIMAL(14,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'RWF',
  completion_date DATE,
  status ENUM('off-plan','under-construction','near-completion') NOT NULL DEFAULT 'off-plan',
  highlights TEXT,
  description TEXT,
  is_featured TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (district_id) REFERENCES districts(id)
) ENGINE=InnoDB;

INSERT INTO developments (name, slug, district_id, cover_image, starting_price, currency, completion_date, status, highlights, description, is_featured) VALUES
('The Rebero Heights Residences', 'rebero-heights-residences', 5, 'assets/images/properties/villa-ext-d.jpg', 185000000.00, 'RWF', '2027-06-30', 'off-plan', '["Panoramic city-view terraces on every unit","Shared rooftop pool and clubhouse","Staged payment plan tied to construction milestones","Secure gated estate with 24/7 concierge"]', 'A boutique collection of 18 architect-designed townhouses on Rebero''s ridge line, combining sweeping Kigali views with resort-style shared amenities. Reserve now at pre-construction pricing with a staged payment plan through to handover.', 1),
('Gacuriro Garden Villas', 'gacuriro-garden-villas', 6, 'assets/images/properties/villa-ext-e.jpg', 145000000.00, 'RWF', '2026-12-31', 'under-construction', '["Private garden plots on every villa","Estate-wide solar backup power","Children''s play park and jogging trail","30% deposit, balance on handover"]', 'Currently under construction, Gacuriro Garden Villas brings 24 family-sized villas to one of Gasabo''s fastest-growing planned communities, with structural work already complete on the first phase.', 1),
('Kanombe Airport Corridor Business Park', 'kanombe-airport-corridor-business-park', 7, 'assets/images/properties/office-ext-1.jpg', 62000000.00, 'RWF', '2027-03-31', 'off-plan', '["Titled commercial plots from 300 sqm","Positioned directly on the airport growth corridor","Flexible retail, warehouse or office zoning","Early-phase pricing before road upgrade completion"]', 'A new mixed-use business park positioned along the Kanombe growth corridor connecting central Kigali to the airport and Bugesera expressway — an early opportunity to secure commercial land ahead of infrastructure completion.', 0);