-- 0015_kb_article_editor.sql  (Neon Postgres; extends kb_articles from kb-module)
-- Wave 12: kb-article-editor schema delta
-- Adds SEO columns, soft-delete, and generated search_text to kb_articles.

ALTER TABLE kb_articles ADD COLUMN IF NOT EXISTS meta_title       TEXT;
ALTER TABLE kb_articles ADD COLUMN IF NOT EXISTS meta_description TEXT;
-- view_count is owned by kb-module (in its kb_articles CREATE TABLE) — NOT re-added here.
ALTER TABLE kb_articles ADD COLUMN IF NOT EXISTS deleted_at       TIMESTAMPTZ;
ALTER TABLE kb_articles ADD COLUMN IF NOT EXISTS search_text      TEXT GENERATED ALWAYS AS (
  title || ' ' || COALESCE(meta_title, '') || ' ' || COALESCE(meta_description, '')
) STORED;

-- live (non-deleted) tree lookups
CREATE INDEX IF NOT EXISTS idx_kb_articles_live
  ON kb_articles (space_id, parent_id, position)
  WHERE deleted_at IS NULL;

-- search support (used by search-completeness, exact-prefix + trigram-ready)
CREATE INDEX IF NOT EXISTS idx_kb_articles_search_text
  ON kb_articles (tenant_id)
  INCLUDE (search_text)
  WHERE deleted_at IS NULL;
