-- RealtorBH Laravel CMS database bootstrap
-- MySQL 8.0+ / MariaDB 10.6+, utf8mb4
-- Import into an EMPTY database selected in phpMyAdmin.
-- No administrator password is embedded. After deployment run: php artisan admin:create

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE `migrations` (`id` int unsigned NOT NULL AUTO_INCREMENT,`migration` varchar(255) NOT NULL,`batch` int NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `users` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,`email` varchar(255) NOT NULL,`email_verified_at` timestamp NULL,`password` varchar(255) NOT NULL,`role` varchar(255) NOT NULL DEFAULT 'consumer',`is_active` tinyint(1) NOT NULL DEFAULT 1,`two_factor_secret` text NULL,`two_factor_recovery_codes` text NULL,`two_factor_confirmed_at` timestamp NULL,`last_login_at` timestamp NULL,`remember_token` varchar(100) NULL,`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),UNIQUE KEY `users_email_unique` (`email`),KEY `users_role_index` (`role`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `password_reset_tokens` (`email` varchar(255) NOT NULL,`token` varchar(255) NOT NULL,`created_at` timestamp NULL,PRIMARY KEY (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `sessions` (`id` varchar(255) NOT NULL,`user_id` bigint unsigned NULL,`ip_address` varchar(45) NULL,`user_agent` text NULL,`payload` longtext NOT NULL,`last_activity` int NOT NULL,PRIMARY KEY (`id`),KEY `sessions_user_id_index` (`user_id`),KEY `sessions_last_activity_index` (`last_activity`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cache` (`key` varchar(255) NOT NULL,`value` mediumtext NOT NULL,`expiration` int NOT NULL,PRIMARY KEY (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cache_locks` (`key` varchar(255) NOT NULL,`owner` varchar(255) NOT NULL,`expiration` int NOT NULL,PRIMARY KEY (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `jobs` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`queue` varchar(255) NOT NULL,`payload` longtext NOT NULL,`attempts` tinyint unsigned NOT NULL,`reserved_at` int unsigned NULL,`available_at` int unsigned NOT NULL,`created_at` int unsigned NOT NULL,PRIMARY KEY (`id`),KEY `jobs_queue_index` (`queue`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `job_batches` (`id` varchar(255) NOT NULL,`name` varchar(255) NOT NULL,`total_jobs` int NOT NULL,`pending_jobs` int NOT NULL,`failed_jobs` int NOT NULL,`failed_job_ids` longtext NOT NULL,`options` mediumtext NULL,`cancelled_at` int NULL,`created_at` int NOT NULL,`finished_at` int NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `failed_jobs` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`uuid` varchar(255) NOT NULL,`connection` text NOT NULL,`queue` text NOT NULL,`payload` longtext NOT NULL,`exception` longtext NOT NULL,`failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`),UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pages` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`title` varchar(255) NOT NULL,`slug` varchar(255) NOT NULL,`template` varchar(255) NOT NULL DEFAULT 'default',`excerpt` text NULL,`status` varchar(255) NOT NULL DEFAULT 'draft',`meta_title` varchar(255) NULL,`meta_description` text NULL,`published_at` timestamp NULL,`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),UNIQUE KEY `pages_slug_unique` (`slug`),KEY `pages_status_index` (`status`),KEY `pages_published_at_index` (`published_at`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `page_sections` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`page_id` bigint unsigned NOT NULL,`type` varchar(255) NOT NULL,`name` varchar(255) NOT NULL,`content` json NOT NULL,`settings` json NULL,`sort_order` int unsigned NOT NULL DEFAULT 0,`is_active` tinyint(1) NOT NULL DEFAULT 1,`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),KEY `page_sections_page_id_sort_order_index` (`page_id`,`sort_order`),CONSTRAINT `page_sections_page_id_foreign` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `settings` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`key` varchar(255) NOT NULL,`value` longtext NULL,`type` varchar(255) NOT NULL DEFAULT 'string',`group` varchar(255) NOT NULL DEFAULT 'general',`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),UNIQUE KEY `settings_key_unique` (`key`),KEY `settings_group_index` (`group`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `listings` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`listing_key` varchar(255) NOT NULL,`listing_id` varchar(255) NOT NULL,`standard_status` varchar(255) NOT NULL,`property_type` varchar(255) NULL,`property_sub_type` varchar(255) NULL,`list_price` decimal(18,3) NOT NULL DEFAULT 0,`currency` varchar(3) NOT NULL DEFAULT 'BHD',`bedrooms_total` smallint unsigned NULL,`bathrooms_total` decimal(6,2) NULL,`living_area` decimal(14,2) NULL,`lot_size_area` decimal(14,2) NULL,`street_number` varchar(255) NULL,`street_name` varchar(255) NULL,`city` varchar(255) NULL,`state_or_province` varchar(255) NULL,`postal_code` varchar(255) NULL,`country` varchar(255) NOT NULL DEFAULT 'Bahrain',`latitude` decimal(11,8) NULL,`longitude` decimal(11,8) NULL,`public_remarks` longtext NULL,`list_office_key` varchar(255) NULL,`list_agent_key` varchar(255) NULL,`modification_timestamp` timestamp NULL,`photos` json NULL,`raw_payload` json NULL,`sync_source` varchar(255) NOT NULL DEFAULT 'local',`is_featured` tinyint(1) NOT NULL DEFAULT 0,`is_visible` tinyint(1) NOT NULL DEFAULT 1,`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),UNIQUE KEY `listings_listing_key_unique` (`listing_key`),KEY `listings_listing_id_index` (`listing_id`),KEY `listings_status_index` (`standard_status`),KEY `listings_property_type_index` (`property_type`),KEY `listings_price_index` (`list_price`),KEY `listings_city_index` (`city`),KEY `listings_modified_index` (`modification_timestamp`),KEY `listings_featured_index` (`is_featured`),KEY `listings_visible_index` (`is_visible`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `sync_runs` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`direction` varchar(255) NOT NULL,`resource` varchar(255) NOT NULL,`status` varchar(255) NOT NULL,`started_at` timestamp NULL,`finished_at` timestamp NULL,`cursor` varchar(255) NULL,`processed` int unsigned NOT NULL DEFAULT 0,`created_count` int unsigned NOT NULL DEFAULT 0,`updated_count` int unsigned NOT NULL DEFAULT 0,`failed_count` int unsigned NOT NULL DEFAULT 0,`message` text NULL,`context` json NULL,`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),KEY `sync_runs_status_index` (`status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `inquiries` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,`email` varchar(255) NOT NULL,`phone` varchar(255) NULL,`subject` varchar(255) NULL,`message` text NOT NULL,`listing_id` bigint unsigned NULL,`status` varchar(255) NOT NULL DEFAULT 'new',`source` varchar(255) NOT NULL DEFAULT 'website',`consent_at` timestamp NULL,`assigned_to` bigint unsigned NULL,`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),KEY `inquiries_email_index` (`email`),KEY `inquiries_status_index` (`status`),CONSTRAINT `inquiries_listing_id_foreign` FOREIGN KEY (`listing_id`) REFERENCES `listings` (`id`) ON DELETE SET NULL,CONSTRAINT `inquiries_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `audit_logs` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`user_id` bigint unsigned NULL,`event` varchar(255) NOT NULL,`subject_type` varchar(255) NULL,`subject_id` bigint unsigned NULL,`ip_address` varchar(45) NULL,`user_agent` text NULL,`old_values` json NULL,`new_values` json NULL,`created_at` timestamp NULL,`updated_at` timestamp NULL,PRIMARY KEY (`id`),KEY `audit_logs_event_index` (`event`),KEY `audit_logs_subject_type_subject_id_index` (`subject_type`,`subject_id`),CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`,`batch`) VALUES ('2026_08_14_000001_create_core_tables',1),('2026_08_14_000002_create_cms_and_reso_tables',1);
INSERT INTO `settings` (`key`,`value`,`type`,`group`,`created_at`,`updated_at`) VALUES
('site.name','RealtorBH','string','general',NOW(),NOW()),('site.email','hello@realtor.bh','string','general',NOW(),NOW()),('site.phone','+973 0000 0000','string','general',NOW(),NOW()),('site.address','Manama, Kingdom of Bahrain','string','general',NOW(),NOW()),('seo.default_description','Find homes, communities and developments across Bahrain.','string','seo',NOW(),NOW()),('mls.disclaimer','Demo data is shown until MLS.BH syndication is enabled.','string','general',NOW(),NOW());
INSERT INTO `pages` (`id`,`title`,`slug`,`template`,`excerpt`,`status`,`published_at`,`created_at`,`updated_at`) VALUES
(1,'Home','home','landing','Bahrain property discovery, made easy.','published',NOW(),NOW(),NOW()),(2,'About RealtorBH','about','default','RealtorBH connects people with property information across Bahrain.','published',NOW(),NOW(),NOW()),(3,'Communities','communities','default','Explore the places that make Bahrain distinctive.','published',NOW(),NOW(),NOW()),(4,'Developments','developments','default','Discover developments throughout Bahrain.','published',NOW(),NOW(),NOW()),(5,'Contact','contact','default','Speak with the RealtorBH team.','published',NOW(),NOW(),NOW()),(6,'Advanced Search','advance-search','default','Narrow listings by location, type and price.','published',NOW(),NOW(),NOW()),(7,'Professional Directory','directory','default','Find real estate professionals.','published',NOW(),NOW(),NOW()),(8,'Localities','localities','default','Browse Bahrain by locality.','published',NOW(),NOW(),NOW()),(9,'Property Types','property-types','default','Explore property types.','published',NOW(),NOW(),NOW()),(10,'Mortgage Guide','mortgage-guide','default','Understand the financing journey.','published',NOW(),NOW(),NOW()),(11,'Insurance Guide','insurance-guide','default','Learn about property cover.','published',NOW(),NOW(),NOW()),(12,'Terms & Conditions','terms','legal','Replace with approved legal content.','published',NOW(),NOW(),NOW()),(13,'Privacy Policy','privacy','legal','Replace with an approved privacy policy.','published',NOW(),NOW(),NOW());
INSERT INTO `page_sections` (`page_id`,`type`,`name`,`content`,`settings`,`sort_order`,`is_active`,`created_at`,`updated_at`) VALUES
(1,'hero','Why RealtorBH',JSON_OBJECT('eyebrow','DISCOVER BAHRAIN','heading','Property decisions backed by better information.','text','Explore homes, communities and developments in one clear experience.','button_label','Browse properties','button_url','/properties'),JSON_OBJECT(),0,1,NOW(),NOW()),
(1,'cards','Explore Bahrain',JSON_OBJECT('heading','Explore by what matters to you','items',JSON_ARRAY(JSON_OBJECT('title','Communities','text','Understand neighborhoods, amenities and local character.'),JSON_OBJECT('title','Developments','text','Discover noteworthy residential developments.'),JSON_OBJECT('title','Professionals','text','Connect with professionals across Bahrain.'))),JSON_OBJECT(),1,1,NOW(),NOW()),
(1,'cta','Get started',JSON_OBJECT('heading','Your next move starts here.','text','Browse our demonstration listings today.','button_label','View properties','button_url','/properties'),JSON_OBJECT(),2,1,NOW(),NOW()),
(2,'text','Our purpose',JSON_OBJECT('heading','A clearer way to navigate Bahrain real estate','body','RealtorBH brings property listings, community knowledge and professional connections together.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(3,'cards','Popular communities',JSON_OBJECT('heading','Find your part of Bahrain','items',JSON_ARRAY(JSON_OBJECT('title','Manama','text','Capital living with business and cultural destinations.'),JSON_OBJECT('title','Seef','text','Modern living close to retail and business.'),JSON_OBJECT('title','Amwaj Islands','text','Waterfront communities and marina access.'))),JSON_OBJECT(),0,1,NOW(),NOW()),
(4,'text','Development directory',JSON_OBJECT('heading','Development information in one place','body','Manage project profiles, amenities and developer details from the CMS.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(5,'text','Contact RealtorBH',JSON_OBJECT('heading','We are here to help','body','Email hello@realtor.bh or update the contact details from the CMS.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(6,'text','Overview',JSON_OBJECT('heading','Advanced Search','body','Use the public property filters to refine demonstration listings.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(7,'text','Overview',JSON_OBJECT('heading','Professional Directory','body','This area is ready for company, agent, mortgage and insurance profiles.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(8,'text','Overview',JSON_OBJECT('heading','Localities','body','Build rich locality guides with additional CMS sections.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(9,'text','Overview',JSON_OBJECT('heading','Property Types','body','Explore apartments, villas, townhouses, commercial property and land.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(10,'text','Overview',JSON_OBJECT('heading','Mortgage Guide','body','Replace this demonstration guide with approved financing information.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(11,'text','Overview',JSON_OBJECT('heading','Insurance Guide','body','Replace this demonstration guide with approved insurance information.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(12,'text','Legal',JSON_OBJECT('heading','Terms & Conditions','body','Demonstration text only. Obtain legal approval before launch.'),JSON_OBJECT(),0,1,NOW(),NOW()),
(13,'text','Legal',JSON_OBJECT('heading','Privacy Policy','body','Demonstration text only. Replace with a policy matching actual data processing.'),JSON_OBJECT(),0,1,NOW(),NOW());
INSERT INTO `listings` (`listing_key`,`listing_id`,`standard_status`,`property_type`,`property_sub_type`,`list_price`,`currency`,`bedrooms_total`,`bathrooms_total`,`living_area`,`city`,`country`,`public_remarks`,`modification_timestamp`,`photos`,`raw_payload`,`sync_source`,`is_featured`,`is_visible`,`created_at`,`updated_at`) VALUES
('DEMO-0001','RBH-D0001','Active','Residential','Villa',220000,'BHD',4,4,310,'Saar','Bahrain','Demonstration property. Remove when MLS.BH synchronization is enabled.',NOW(),JSON_ARRAY(JSON_OBJECT('url','/images/property-placeholder.svg','order',0)),JSON_OBJECT('Demo',true),'demo',1,1,NOW(),NOW()),
('DEMO-0002','RBH-D0002','Active','Residential','Apartment',95000,'BHD',2,2,125,'Seef','Bahrain','Demonstration property. Remove when MLS.BH synchronization is enabled.',NOW(),JSON_ARRAY(JSON_OBJECT('url','/images/property-placeholder.svg','order',0)),JSON_OBJECT('Demo',true),'demo',1,1,NOW(),NOW()),
('DEMO-0003','RBH-D0003','Active','Residential','Penthouse',310000,'BHD',4,5,340,'Manama','Bahrain','Demonstration property. Remove when MLS.BH synchronization is enabled.',NOW(),JSON_ARRAY(JSON_OBJECT('url','/images/property-placeholder.svg','order',0)),JSON_OBJECT('Demo',true),'demo',1,1,NOW(),NOW()),
('DEMO-0004','RBH-D0004','Active','Residential','Townhouse',165000,'BHD',3,3,220,'Amwaj Islands','Bahrain','Demonstration property. Remove when MLS.BH synchronization is enabled.',NOW(),JSON_ARRAY(JSON_OBJECT('url','/images/property-placeholder.svg','order',0)),JSON_OBJECT('Demo',true),'demo',1,1,NOW(),NOW()),
('DEMO-0005','RBH-D0005','Active','Residential','Apartment',78000,'BHD',1,1,82,'Juffair','Bahrain','Demonstration property. Remove when MLS.BH synchronization is enabled.',NOW(),JSON_ARRAY(JSON_OBJECT('url','/images/property-placeholder.svg','order',0)),JSON_OBJECT('Demo',true),'demo',1,1,NOW(),NOW()),
('DEMO-0006','RBH-D0006','Active','Residential','Villa',275000,'BHD',5,5,390,'Riffa','Bahrain','Demonstration property. Remove when MLS.BH synchronization is enabled.',NOW(),JSON_ARRAY(JSON_OBJECT('url','/images/property-placeholder.svg','order',0)),JSON_OBJECT('Demo',true),'demo',1,1,NOW(),NOW());

SET FOREIGN_KEY_CHECKS=1;