CREATE TABLE IF NOT EXISTS `real_estate_sources` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `url` TEXT NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `real_estate_posts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `source_id` INT UNSIGNED NOT NULL,
  `post_url` TEXT NOT NULL,
  `post_hash` CHAR(64) NOT NULL,
  `title` TEXT DEFAULT NULL,
  `price_usd` INT DEFAULT NULL,
  `square_meters` DECIMAL(8,2) DEFAULT NULL,
  `location_text` VARCHAR(255) DEFAULT NULL,
  `floor_text` VARCHAR(100) DEFAULT NULL,
  `apartment_code` VARCHAR(50) DEFAULT NULL,
  `raw_text` MEDIUMTEXT DEFAULT NULL,
  `sent_to_telegram` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_real_estate_posts_hash` (`post_hash`),
  KEY `idx_real_estate_posts_source_id` (`source_id`),
  KEY `idx_real_estate_posts_price_usd` (`price_usd`),
  KEY `idx_real_estate_posts_sent` (`sent_to_telegram`),
  CONSTRAINT `fk_real_estate_posts_source_id`
    FOREIGN KEY (`source_id`) REFERENCES `real_estate_sources` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `real_estate_sources` (`name`, `url`, `active`)
SELECT 'Elman Realty', 'https://www.facebook.com/61560997086458', 1
WHERE NOT EXISTS (SELECT 1 FROM `real_estate_sources` WHERE `name` = 'Elman Realty');

INSERT INTO `real_estate_sources` (`name`, `url`, `active`)
SELECT 'Kentron Real Estate', 'https://www.facebook.com/search/top?q=Kentron%20Real%20Estate', 1
WHERE NOT EXISTS (SELECT 1 FROM `real_estate_sources` WHERE `name` = 'Kentron Real Estate');

INSERT INTO `real_estate_sources` (`name`, `url`, `active`)
SELECT 'Yerevan Real Estate', 'https://www.facebook.com/search/top?q=Yerevan%20Real%20Estate', 1
WHERE NOT EXISTS (SELECT 1 FROM `real_estate_sources` WHERE `name` = 'Yerevan Real Estate');

INSERT INTO `real_estate_sources` (`name`, `url`, `active`)
SELECT 'Bars Real Estate', 'https://www.facebook.com/search/top?q=Bars%20Real%20Estate', 1
WHERE NOT EXISTS (SELECT 1 FROM `real_estate_sources` WHERE `name` = 'Bars Real Estate');

INSERT INTO `real_estate_sources` (`name`, `url`, `active`)
SELECT 'Best House Realty', 'https://www.facebook.com/search/top?q=Best%20House%20Realty', 1
WHERE NOT EXISTS (SELECT 1 FROM `real_estate_sources` WHERE `name` = 'Best House Realty');

INSERT INTO `real_estate_sources` (`name`, `url`, `active`)
SELECT 'Red Invest Group', 'https://www.facebook.com/search/top?q=Red%20Invest%20Group', 1
WHERE NOT EXISTS (SELECT 1 FROM `real_estate_sources` WHERE `name` = 'Red Invest Group');