-- SQL equivalent of M260824120000CreateContentPages::up().
-- Select the intended application database before running this file.
-- This script is forward-only and does not remove existing content.

CREATE TABLE IF NOT EXISTS `content_pages` (
  `id` CHAR(32) PRIMARY KEY,
  `draft_title` VARCHAR(200) NOT NULL,
  `draft_slug` VARCHAR(190) NOT NULL,
  `draft_html_body` MEDIUMTEXT NOT NULL,
  `draft_meta_description` VARCHAR(320) NOT NULL DEFAULT '',
  `draft_page_type` ENUM('general','terms','privacy') NOT NULL DEFAULT 'general',
  `draft_legal_version` VARCHAR(40) NULL,
  `draft_footer_label` VARCHAR(100) NOT NULL DEFAULT '',
  `draft_show_in_footer` TINYINT(1) NOT NULL DEFAULT 0,
  `draft_footer_sort_order` INT NOT NULL DEFAULT 0,
  `draft_sanitizer_version` INT NOT NULL,
  `published_title` VARCHAR(200) NULL,
  `published_slug` VARCHAR(190) NULL,
  `published_html_body` MEDIUMTEXT NULL,
  `published_meta_description` VARCHAR(320) NULL,
  `published_page_type` ENUM('general','terms','privacy') NULL,
  `published_legal_version` VARCHAR(40) NULL,
  `published_footer_label` VARCHAR(100) NULL,
  `published_show_in_footer` TINYINT(1) NULL,
  `published_footer_sort_order` INT NULL,
  `published_sanitizer_version` INT NULL,
  `published_slug_slot` VARCHAR(190) NULL,
  `published_legal_slot` VARCHAR(16) NULL,
  `published_legal_revision_id` CHAR(32) NULL,
  `status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_by` CHAR(32) NOT NULL,
  `updated_by` CHAR(32) NOT NULL,
  `published_by` CHAR(32) NULL,
  `unpublished_by` CHAR(32) NULL,
  `archived_by` CHAR(32) NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `published_at` DATETIME NULL,
  `unpublished_at` DATETIME NULL,
  `archived_at` DATETIME NULL,
  UNIQUE KEY `uq_content_pages_published_slug` (`published_slug_slot`),
  UNIQUE KEY `uq_content_pages_published_legal_slot` (`published_legal_slot`),
  KEY `idx_content_pages_draft_slug` (`draft_slug`),
  KEY `idx_content_pages_status_footer` (`status`, `published_show_in_footer`, `published_footer_sort_order`),
  KEY `idx_content_pages_legal_revision` (`published_legal_revision_id`),
  CHECK (`draft_footer_sort_order` BETWEEN 0 AND 9999),
  CHECK (`published_footer_sort_order` IS NULL OR `published_footer_sort_order` BETWEEN 0 AND 9999),
  CHECK (`published_legal_slot` IS NULL OR `published_legal_slot` IN ('terms','privacy'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `content_page_legal_revisions` (
  `id` CHAR(32) PRIMARY KEY,
  `content_page_id` CHAR(32) NOT NULL,
  `page_type` ENUM('terms') NOT NULL,
  `legal_version` VARCHAR(40) NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(190) NOT NULL,
  `html_body` MEDIUMTEXT NOT NULL,
  `meta_description` VARCHAR(320) NOT NULL DEFAULT '',
  `sanitizer_version` INT NOT NULL,
  `body_sha256` CHAR(64) NOT NULL,
  `published_by` CHAR(32) NOT NULL,
  `published_at` DATETIME NOT NULL,
  UNIQUE KEY `uq_content_page_legal_revision_version` (`page_type`, `legal_version`),
  KEY `idx_content_page_legal_revision_page` (`content_page_id`, `published_at`),
  CONSTRAINT `fk_content_page_legal_revision_page` FOREIGN KEY (`content_page_id`) REFERENCES `content_pages` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;