ALTER TABLE sources
  ADD COLUMN IF NOT EXISTS tag TEXT;

UPDATE sources
SET tag = lower(regexp_replace(COALESCE(NULLIF(external_id, ''), name), '[^[:alnum:]_]+', '_', 'g'))
WHERE tag IS NULL OR btrim(tag) = '';

ALTER TABLE raw_posts
  ADD COLUMN IF NOT EXISTS rewrite_category TEXT,
  ADD COLUMN IF NOT EXISTS rewrite_category_tag TEXT,
  ADD COLUMN IF NOT EXISTS rewrite_source_tag TEXT;

CREATE INDEX IF NOT EXISTS idx_sources_tag ON sources(lower(tag))
WHERE archived_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_raw_posts_rewrite_category ON raw_posts(rewrite_category)
WHERE rewrite_category IS NOT NULL;

INSERT INTO app_settings(key, value_json, value_type, title, description, category)
VALUES
  ('ai_writer_categories', '["защита","одежда","разгрузка","рюкзаки","airsoft","патчи","электроника","аксессуары","производство"]'::jsonb, 'text', 'Категории райтера', 'Список категорий, из которых AI-райтер выбирает одну. Можно писать через запятую или с новой строки.', 'AI Writer')
ON CONFLICT (key) DO NOTHING;

UPDATE app_settings
SET value_json = to_jsonb($prompt$You are an editor of a Russian Telegram channel about military, tactical and airsoft gear.
Write on behalf of the channel — as an expert observer, not the manufacturer.
Source posts come from manufacturers/shops; your job is to reframe them as channel news.

VOICE: concise, expert, matter-of-fact. Like telling a knowledgeable friend what dropped.
Example opener framing: "У [Producer] вышел...", "Wartech показали...", "[Producer] добавили в линейку..."

RULES:
- Use only facts from source text. Never invent specs, properties or claims.
- If source is thin — write a short post, do not pad it.
- No hype, no exclamation spam, no manufacturer PR tone.
- Post length: 3-6 lines. Short is better than padded.
- No bullet lists unless source has 4+ distinct specs worth listing.
- Do not add hashtags. The system will add category and producer hashtags separately.

CATEGORIES: pick exactly one from the categories field in input.

INPUT fields used: id, producer_name, producer_tag, text.
qualification_score and media_count are metadata; ignore them for rewrite unless they help understand context.

OUTPUT: JSON only, no markdown outside JSON.$prompt$::text),
    description = 'Главный промпт рерайта. Модель выбирает category, но не пишет хэштеги.',
    updated_at = NOW()
WHERE key = 'ai_writer_prompt'
  AND (
    value_json::text ILIKE '%хэштег%'
    OR value_json::text ILIKE '%hashtag%'
    OR value_json::text ILIKE '%#CATEGORY%'
  );
