-- Remove stub AI text that the old mock mode wrote into real paper fields.
--
-- Until ClaudeAiService was changed to fail when no provider key is set, the
-- "AI process" button on the submit form returned strings like
--
--   [DEV] This is a test paper (हिन्दी अनुवाद — OpenAI key लगाने पर वास्तविक अनुवाद होगा)
--   [DEV] भारतीय ज्ञान-परम्परा … (English translation — will work with OpenAI key)
--
-- and resolveHiEn() stored them as the paper's Hindi or English title/abstract.
-- This strips the wrapper, leaving the author's own text — the same result the
-- no-AI fallback produces today.
--
-- Idempotent: rows without the marker are untouched, so re-running is safe.
-- Apply with:
--   docker exec -i shodh-sanchayan-api-postgres-1 \
--     psql -U shodh -d shodh_sanchayan < scripts/cleanup-dev-ai-placeholders.sql

BEGIN;

-- One pass per column. The suffix is optional so a truncated stub still cleans.
UPDATE papers SET title_hi = regexp_replace(
        regexp_replace(title_hi, '^\[DEV\]\s*', ''),
        '\s*\((?:हिन्दी अनुवाद|English translation)[^)]*\)\s*$', '')
 WHERE title_hi LIKE '[DEV]%';

UPDATE papers SET title_en = regexp_replace(
        regexp_replace(title_en, '^\[DEV\]\s*', ''),
        '\s*\((?:हिन्दी अनुवाद|English translation)[^)]*\)\s*$', '')
 WHERE title_en LIKE '[DEV]%';

UPDATE papers SET abstract_hi = regexp_replace(
        regexp_replace(abstract_hi, '^\[DEV\]\s*', ''),
        '\s*\((?:हिन्दी अनुवाद|English translation)[^)]*\)\s*$', '')
 WHERE abstract_hi LIKE '[DEV]%';

UPDATE papers SET abstract_en = regexp_replace(
        regexp_replace(abstract_en, '^\[DEV\]\s*', ''),
        '\s*\((?:हिन्दी अनुवाद|English translation)[^)]*\)\s*$', '')
 WHERE abstract_en LIKE '[DEV]%';

COMMIT;

-- Should report 0 in every column.
SELECT count(*) FILTER (WHERE title_hi    LIKE '%[DEV]%') AS title_hi_left,
       count(*) FILTER (WHERE title_en    LIKE '%[DEV]%') AS title_en_left,
       count(*) FILTER (WHERE abstract_hi LIKE '%[DEV]%') AS abstract_hi_left,
       count(*) FILTER (WHERE abstract_en LIKE '%[DEV]%') AS abstract_en_left
  FROM papers;