ALTER TABLE raw_posts
  ADD COLUMN IF NOT EXISTS rewrite_category_id INTEGER,
  ADD COLUMN IF NOT EXISTS final_category_id INTEGER;

WITH duplicate_categories AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY sort_order ORDER BY is_active DESC, id) AS duplicate_rank
  FROM content_categories
),
renumber AS (
  SELECT id,
         (SELECT COALESCE(MAX(sort_order), 0) FROM content_categories)
         + ROW_NUMBER() OVER (ORDER BY id) AS new_sort_order
  FROM duplicate_categories
  WHERE duplicate_rank > 1
)
UPDATE content_categories c
SET sort_order = r.new_sort_order,
    updated_at = NOW()
FROM renumber r
WHERE c.id = r.id;

CREATE UNIQUE INDEX IF NOT EXISTS idx_content_categories_public_id
ON content_categories(sort_order);

UPDATE raw_posts rp
SET rewrite_category_id = c.sort_order
FROM content_categories c
WHERE rp.rewrite_category_id IS NULL
  AND rp.rewrite_category IS NOT NULL
  AND (
    lower(c.name) = lower(rp.rewrite_category)
    OR lower(c.tag) = lower(COALESCE(rp.rewrite_category_tag, ''))
  );

UPDATE raw_posts rp
SET final_category_id = c.sort_order
FROM content_categories c
WHERE rp.final_category_id IS NULL
  AND rp.final_category IS NOT NULL
  AND (
    lower(c.name) = lower(rp.final_category)
    OR lower(c.tag) = lower(COALESCE(rp.final_category_tag, ''))
  );

UPDATE app_settings
SET value_json = to_jsonb(
  replace(
    value_json #>> '{}',
    E'- выбери ровно одну category из списка categories во входных данных;\n- если сомневаешься, выбирай ближайшую по назначению товара.',
    E'- выбери ровно один category_id из списка categories во входных данных;\n- categories приходит списком объектов: id, name, tag;\n- если сомневаешься, выбирай ближайший id по назначению товара.'
  )
),
updated_at = NOW()
WHERE key='ai_writer_prompt';

UPDATE app_settings
SET value_json = to_jsonb($contract$
OUTPUT SCHEMA (return array matching input order):
{"rewrites":[{"id":123,"category_id":2,"text":"готовый текст без хэштегов","notes":"короткая заметка для редактора или null"}]}

Rules:
- Input is a JSON object with key "posts" containing accepted posts.
- Input has key "categories" with objects: id, name, tag.
- Each post includes producer_name and producer_tag. producer_name can be a manufacturer, shop, or publishing source.
- Pick exactly one category from the categories list in input and return its numeric id as category_id.
- Do not add links or hashtags to rewritten text.
- Return JSON only. No markdown. No text outside JSON.
- Return one rewrite for every input post id.
- The "text" field must be a ready-to-publish Russian post with normal paragraph line breaks encoded as JSON string newlines.
- The "notes" field must be a short editor note in Russian or null.
$contract$::text),
updated_at = NOW()
WHERE key='ai_writer_contract';
