-- ============================================================
-- SBM SmartLedger migration: provider-managed blog
-- ------------------------------------------------------------
-- Adds the blog_post table used by the provider console (authoring) and the
-- public marketing site (viewer). Safe to re-run.
-- ============================================================

CREATE TABLE IF NOT EXISTS blog_post (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title             VARCHAR(200)    NOT NULL,
    slug              VARCHAR(220)    NOT NULL,
    excerpt           VARCHAR(320)    NULL,
    body              MEDIUMTEXT      NOT NULL,
    cover_image_path  VARCHAR(255)    NULL,
    category          VARCHAR(80)     NULL,
    tags              VARCHAR(255)    NULL,
    status            ENUM('draft','published') NOT NULL DEFAULT 'draft',
    meta_title        VARCHAR(200)    NULL,
    meta_description  VARCHAR(320)    NULL,
    author_id         BIGINT UNSIGNED NULL,
    author_name       VARCHAR(120)    NULL,
    view_count        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    published_at      DATETIME        NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_blog_slug (slug),
    KEY ix_blog_status_pub (status, published_at),
    KEY ix_blog_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;