-- =============================================================================
-- web_pages_module.sql — Page builder module for the Web CMS (dim_web_* family).
-- Adds (a) a `theme_json` design-token column on dim_web_sites (see
-- WEB-THEME-DESIGN-CONTEXT.md §1) and (b) dim_web_pages, storing each page's
-- `layout_json` block tree (see WEB-THEME-DESIGN-CONTEXT.md §2).
--
-- Follows the exact tenant/business/site/audit column pattern used by the
-- other dim_web_* tables in projects_foundation_refactor.sql / web_forms_module.sql.
-- Safe to re-run: CREATE TABLE IF NOT EXISTS + an information_schema-guarded
-- ALTER (the same @db/@sql/PREPARE pattern used throughout
-- projects_foundation_refactor.sql), avoiding MySQL-8-only
-- `ADD COLUMN IF NOT EXISTS` syntax that MariaDB (the production server —
-- see apply-migrations.sh / fix_sp_process_ai_bank_statement.sql's own
-- portability note) does not accept in the same form on every version.
-- =============================================================================

SET @db := DATABASE();

-- ── dim_web_sites.theme_json ─────────────────────────────────────────────────
-- One design-token object per site (WEB-THEME-DESIGN-CONTEXT.md §1). Editable
-- from the admin "Site Settings" screen via UPDATE_WEB_SITE_THEME; no code
-- change required to ship a new look.
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_web_sites' AND COLUMN_NAME='theme_json'),
  'ALTER TABLE dim_web_sites ADD COLUMN theme_json JSON NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── dim_web_pages ─────────────────────────────────────────────────────────────
-- One row per page. layout_json is the `{ version, blocks: [...] }` block tree
-- (WEB-THEME-DESIGN-CONTEXT.md §2) rendered by both the builder's live preview
-- and the public site renderer. Stored/served verbatim — the backend does not
-- parse or validate individual block types.
CREATE TABLE IF NOT EXISTS dim_web_pages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    slug VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    route VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    layout_json JSON NULL,
    meta_title VARCHAR(255) NULL,
    meta_description VARCHAR(500) NULL,
    og_image VARCHAR(500) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_web_pages_slug (site_id, slug),
    UNIQUE KEY uq_dim_web_pages_route (site_id, route),
    INDEX idx_dim_web_pages_tenant (tenant_id),
    INDEX idx_dim_web_pages_business (tenant_id, business_id, is_deleted),
    INDEX idx_dim_web_pages_site_status (site_id, status, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;