-- Brings a live database up to date for:
--   * Pages: How to Use (heading + steps) and Additional Content
--   * Settings > Layout: section order, page width, section spacing, and the
--     Features / How to Use / FAQ designs
--
-- The same changes `php database/migrate.php` makes, for a host with no
-- command line: paste this into phpMyAdmin's SQL tab for the site's database
-- and run it. Safe to run more than once — every step checks first and skips
-- what is already there. Works on MySQL 5.7+/8 and MariaDB.

-- Settings > Layout: one row of optional settings; blank means the default.
CREATE TABLE IF NOT EXISTS layout_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  section_order VARCHAR(255) NULL,
  container_width SMALLINT UNSIGNED NULL,
  section_spacing SMALLINT UNSIGNED NULL,
  feature_design VARCHAR(30) NULL,
  how_to_design VARCHAR(30) NULL,
  faq_design VARCHAR(30) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Each column below is added only when it is missing.

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE layout_settings ADD COLUMN feature_design VARCHAR(30) NULL AFTER section_spacing', 'DO 0')
             FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'layout_settings' AND COLUMN_NAME = 'feature_design');
PREPARE step FROM @sql; EXECUTE step; DEALLOCATE PREPARE step;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE layout_settings ADD COLUMN how_to_design VARCHAR(30) NULL AFTER feature_design', 'DO 0')
             FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'layout_settings' AND COLUMN_NAME = 'how_to_design');
PREPARE step FROM @sql; EXECUTE step; DEALLOCATE PREPARE step;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE layout_settings ADD COLUMN faq_design VARCHAR(30) NULL AFTER how_to_design', 'DO 0')
             FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'layout_settings' AND COLUMN_NAME = 'faq_design');
PREPARE step FROM @sql; EXECUTE step; DEALLOCATE PREPARE step;

-- Pages: How to Use and Additional Content, both optional.

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE pages ADD COLUMN how_to_heading LONGTEXT NULL AFTER instructions_description', 'DO 0')
             FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pages' AND COLUMN_NAME = 'how_to_heading');
PREPARE step FROM @sql; EXECUTE step; DEALLOCATE PREPARE step;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE pages ADD COLUMN how_to_steps LONGTEXT NULL AFTER how_to_heading', 'DO 0')
             FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pages' AND COLUMN_NAME = 'how_to_steps');
PREPARE step FROM @sql; EXECUTE step; DEALLOCATE PREPARE step;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE pages ADD COLUMN additional_content LONGTEXT NULL AFTER how_to_steps', 'DO 0')
             FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pages' AND COLUMN_NAME = 'additional_content');
PREPARE step FROM @sql; EXECUTE step; DEALLOCATE PREPARE step;