`migrations/settlement_publish_check_negation_fix_20260609.sql`

95 lines
CREATE OR REPLACE FUNCTION public.settlement_publish_check()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
  pt text;
  -- Part A keyword groups
  has_leaves boolean;
  has_directional boolean;
  has_open_space boolean;
  has_single_color boolean;
  -- Part B
  prohibited text[];
  prohibited_hit text;
  -- Acceptable
  has_acceptable boolean;
  -- Operator override
  override_flag text;
BEGIN
  -- Only fire on the false → true publish flip. Lets unpublish + edit pass.
  IF OLD.is_published IS NOT DISTINCT FROM NEW.is_published THEN
    RETURN NEW;
  END IF;
  IF NEW.is_published IS NOT TRUE THEN
    RETURN NEW;
  END IF;

  -- Audited override: SET LOCAL settlement.allow_override = 'true' before
  -- the UPDATE. The session-local setting writes its presence to the notes
  -- field so the override is traceable.
  override_flag := current_setting('settlement.allow_override', true);
  IF override_flag = 'true' THEN
    NEW.notes := COALESCE(NEW.notes || E'\n', '') ||
      'settlement.allow_override=true at ' || now()::text;
    RETURN NEW;
  END IF;

  pt := lower(COALESCE(NEW.prompt, ''));

  -- FIX 2026-06-09: strip NEGATED anti-prompt clauses so a prohibition is not
  -- counted as positive presence. Generators carry boilerplate like
  -- "NO banana leaves, NO palm fronds, NO tropical jungle" which previously
  -- tripped both has_leaves and the Part-B banana match (false positive that
  -- blocked ~1000 clean drunk-animals designs). Longest phrases first so e.g.
  -- "palm fronds" is consumed before the bare "palm". A genuine positive use
  -- ("directional banana leaves") is NOT preceded by a negator, so still matches.
  pt := regexp_replace(pt,
    '(no|without|never|avoid|exclude) (banana leaves|banana leaf|palm fronds|palm frond|tropical jungle|monstera|palm|fronds|frond|banana|tropical|jungle|grapes|grape|birds|bird|parrots|parrot|toucans|toucan|butterflies|butterfly|moths|moth)',
    ' ', 'g');

  -- Part A — needs all three
  has_leaves      := pt ~ '\m(palm|frond|banana leaf|monstera|leaves|leaf|foliage|jungle)\M';
  has_directional := pt ~ '\m(directional|varied angle|tossed|scatter|rotated|multi-angle|overlapping|turning leaves|fanning)\M';
  has_open_space  := pt ~ '\m(open space|breathing room|breathing space|scatter|tossed|sparse)\M';
  has_single_color := pt ~ '\m(monochrome|single ink|one color|one-color|black ink|single-color|silhouette|tonal)\M';

  -- Part B — any one
  prohibited := ARRAY['banana', 'banana pod', 'grape', 'bird', 'birds', 'parrot', 'toucan', 'butterfly', 'butterflies', 'moth'];
  prohibited_hit := NULL;
  FOR i IN 1..array_length(prohibited, 1) LOOP
    IF pt LIKE '%' || prohibited[i] || '%' THEN
      prohibited_hit := prohibited[i];
      EXIT;
    END IF;
  END LOOP;

  -- Acceptable cues — at least one
  has_acceptable := pt ~ '\m(tree trunk|tree trunks|branch|branches|twig|bark|monkey|sloth|lemur|orangutan|gibbon|tarsier|fruit|cherry|apple|pear|pomegranate|fig|orange)\M';

  -- Decision: BLOCK only when (Part A all 3) AND (Part B any) AND not acceptable.
  -- FIX 2026-06-09: added "AND NOT has_acceptable" — the carve-out was computed
  -- but never applied to the block (contradicting this very comment), so
  -- orangutan/fruit/tree-trunk designs (settlement carve-out elements) blocked anyway.
  IF has_leaves AND has_directional AND has_open_space AND (NOT has_single_color)
     AND prohibited_hit IS NOT NULL AND NOT has_acceptable THEN
    RAISE EXCEPTION
      'Settlement violation on publish of design id=%: prompt satisfies Part A (directional %s + open space + multi-color) AND Part B (%). To override, set local: SET LOCAL settlement.allow_override = ''true''; — and document why in notes.',
      NEW.id,
      (CASE WHEN pt LIKE '%palm%' THEN 'palm' WHEN pt LIKE '%banana%' THEN 'banana' WHEN pt LIKE '%frond%' THEN 'frond' ELSE 'leaves' END),
      prohibited_hit;
  END IF;

  -- NEEDS_REVIEW (Part A satisfied without Part B but no acceptable cue) is
  -- borderline. We log it but don't block — the JS gate already catches this
  -- pre-gen. Trigger writes a soft notes flag for audit instead.
  IF has_leaves AND has_directional AND has_open_space AND (NOT has_single_color)
     AND prohibited_hit IS NULL AND NOT has_acceptable THEN
    NEW.notes := COALESCE(NEW.notes || E'\n', '') ||
      'settlement: Part A satisfied without acceptable cue — review pattern for tree-trunk/branch presence (' || now()::text || ')';
  END IF;

  RETURN NEW;
END;
$function$