-- 0010_content_asset_classification.sql
--
-- Adds vision-classification signals to content_assets so they round-trip
-- between the content-ingestor (which SETS them, via @model-boss) and
-- content-social's K3 gate (which CONSUMES is_explicit). Before this, the
-- classification was folded into `tags` only and did not survive a read-back,
-- so an explicit asset could read as SFW to the ban-risk gate.
--
-- Spec: content-pipeline-classify-and-offset; consumed by specialist-content-social.
-- Layers on content_assets (0001). Independent of 0007_surface_bump_policy /
-- 0008_fix_surface_rls_guc (main) and 0009_content_drops.
--
-- SAFETY — is_explicit DEFAULT TRUE (fail-safe): an UNCLASSIFIED asset must not
-- read as safe-for-SFW-surfaces to K3a. The ingestor's classifier sets the real
-- value on every asset it writes; manually-uploaded assets stay SFW-restricted
-- until classified or hand-flagged in the asset library.

BEGIN;

CREATE TYPE content_class AS ENUM ('hot', 'stocked');

ALTER TABLE content_assets
  ADD COLUMN is_explicit   BOOLEAN       NOT NULL DEFAULT TRUE,
  ADD COLUMN content_class content_class NULL,
  ADD COLUMN quality_score NUMERIC(4, 3) NULL
    CONSTRAINT content_assets_quality_chk CHECK (quality_score IS NULL OR (quality_score >= 0 AND quality_score <= 1));

COMMENT ON COLUMN content_assets.is_explicit IS
  'Vision-classified K3a routing signal. DEFAULT TRUE is a fail-safe: unclassified assets are treated as explicit so they are never auto-routed to SFW surfaces until classified.';
COMMENT ON COLUMN content_assets.content_class IS
  'hot = fresh/timely (rides the posting offset); stocked = evergreen backfill for calendar gaps. NULL until classified.';
COMMENT ON COLUMN content_assets.quality_score IS
  'Vision quality score 0..1; NULL until classified.';

COMMIT;