-- settings-kb (wave-14): KB publication workflow, spaces ordering, tenant KB settings
-- Idempotent: all statements use IF NOT EXISTS / DO NOTHING guards.

-- 1. Add sort-position column to kb_spaces (description already owned by kb-module)
ALTER TABLE kb_spaces
  ADD COLUMN IF NOT EXISTS position INTEGER NOT NULL DEFAULT 0;
--> statement-breakpoint
-- 2. Tenant-level KB settings on the shared tenant_settings singleton
ALTER TABLE tenant_settings
  ADD COLUMN IF NOT EXISTS kb_require_review     BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS kb_versioning_enabled BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS kb_default_space_id   UUID REFERENCES kb_spaces(id) ON DELETE SET NULL;
--> statement-breakpoint
-- 3. Review-queue query performance: composite index on (tenant_id, status)
CREATE INDEX IF NOT EXISTS idx_kb_articles_tenant_status
  ON kb_articles(tenant_id, status);
--> statement-breakpoint
-- 4. Widen CHECK constraint on kb_articles.status to include PENDING_REVIEW
--    The existing constraint is advisory but present — drop and recreate.
ALTER TABLE kb_articles DROP CONSTRAINT IF EXISTS kb_articles_status_check;
--> statement-breakpoint
ALTER TABLE kb_articles ADD CONSTRAINT kb_articles_status_check
  CHECK (status IN ('DRAFT', 'PENDING_REVIEW', 'PUBLISHED'));
