CREATE DATABASE IF NOT EXISTS blog_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE blog_app;

CREATE TABLE IF NOT EXISTS posts (
  id               CHAR(36)     NOT NULL PRIMARY KEY,
  portal_id        VARCHAR(64)  NOT NULL,   -- which portal this post belongs to
  slug             VARCHAR(191) NOT NULL,
  title            VARCHAR(255) NOT NULL,
  subtitle         VARCHAR(255) NOT NULL DEFAULT '',
  excerpt          TEXT NULL,
  category         VARCHAR(80)  NOT NULL DEFAULT '',
  author           VARCHAR(120) NOT NULL DEFAULT '',
  tags             JSON NULL,
  cover_image_url  VARCHAR(500) NOT NULL DEFAULT '',
  content          JSON NULL,   -- ordered blocks: subtitle | paragraph | image | backlink | table
  is_published     TINYINT(1)   NOT NULL DEFAULT 0,
  published_at     DATETIME NULL,
  created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_portal_slug (portal_id, slug),
  KEY idx_public (portal_id, is_published, published_at),
  KEY idx_category (portal_id, category)
) ENGINE=InnoDB;