-- CMS core tables (proposta)
-- Nota: este ficheiro é um draft para converter para Migrations CI4 mais tarde.

CREATE TABLE IF NOT EXISTS `mt_articles` (
  `id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(250) NOT NULL,
  `slug` VARCHAR(250) NOT NULL,
  `body` LONGTEXT NULL,
  `status` ENUM('draft','pending','live','archived') NOT NULL DEFAULT 'draft',
  `published_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `legacy_article_id` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_articles_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Artigos comuns a várias editions: relação N:N.
-- `published_at` aqui pode ser o “global default”; por edition pode ser sobrescrito no futuro.
CREATE TABLE IF NOT EXISTS `mt_article_editions` (
  `article_id` BIGINT UNSIGNED NOT NULL,
  `edition_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('draft','pending','live','archived') NOT NULL DEFAULT 'draft',
  `published_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`article_id`, `edition_id`),
  KEY `idx_article_editions_edition` (`edition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Taxonomias globais (confirmado: consistentes em todas as editions).
CREATE TABLE IF NOT EXISTS `mt_categories` (
  `id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `position` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_categories_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mt_subjects` (
  `id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `article_category_id` BIGINT UNSIGNED NULL,
  `position` INT NOT NULL DEFAULT 0,
  `status` ENUM('live','hidden') NOT NULL DEFAULT 'live',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_subjects_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mt_threads` (
  `id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `position` INT NOT NULL DEFAULT 0,
  `status` ENUM('live','hidden') NOT NULL DEFAULT 'live',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_threads_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ligações N:N entre artigos e taxonomias (draft).
CREATE TABLE IF NOT EXISTS `mt_article_categories` (
  `article_id` BIGINT UNSIGNED NOT NULL,
  `category_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`article_id`, `category_id`),
  KEY `idx_article_categories_category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mt_article_subjects` (
  `article_id` BIGINT UNSIGNED NOT NULL,
  `subject_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`article_id`, `subject_id`),
  KEY `idx_article_subjects_subject` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mt_article_threads` (
  `article_id` BIGINT UNSIGNED NOT NULL,
  `thread_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`article_id`, `thread_id`),
  KEY `idx_article_threads_thread` (`thread_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mt_images` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` TEXT NOT NULL,
  `alt` VARCHAR(255) NULL,
  `width` INT NULL,
  `height` INT NULL,
  `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 `mt_article_images` (
  `article_id` BIGINT UNSIGNED NOT NULL,
  `image_id` BIGINT UNSIGNED NOT NULL,
  `role` ENUM('featured','inline','gallery','slider') NOT NULL DEFAULT 'inline',
  `sort_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`article_id`, `image_id`, `role`),
  KEY `idx_article_images_article` (`article_id`),
  KEY `idx_article_images_image` (`image_id`),
  CONSTRAINT `fk_mt_article_images_article` FOREIGN KEY (`article_id`) REFERENCES `mt_articles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mt_article_images_image` FOREIGN KEY (`image_id`) REFERENCES `mt_images`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;