-- Categories for adverts and category_id on ads
CREATE TABLE IF NOT EXISTS `ad_categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(140) NOT NULL,
  `parent_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_ad_categories_slug` (`slug`),
  INDEX `idx_ad_categories_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `ads`
  ADD COLUMN `category_id` INT DEFAULT NULL AFTER `title`;

ALTER TABLE `ads`
  ADD INDEX `idx_ads_category_id` (`category_id`);

ALTER TABLE `ads`
  ADD CONSTRAINT `fk_ads_category`
  FOREIGN KEY (`category_id`) REFERENCES `ad_categories`(`id`)
  ON DELETE SET NULL ON UPDATE CASCADE;
