ALTER TABLE raw_posts
  ADD COLUMN IF NOT EXISTS publication_status TEXT NOT NULL DEFAULT 'pending',
  ADD COLUMN IF NOT EXISTS publication_error TEXT,
  ADD COLUMN IF NOT EXISTS published_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS tg_publication_chat_id BIGINT,
  ADD COLUMN IF NOT EXISTS tg_publication_thread_id BIGINT,
  ADD COLUMN IF NOT EXISTS tg_publication_message_ids BIGINT[],
  ADD COLUMN IF NOT EXISTS tg_publication_url TEXT;

CREATE INDEX IF NOT EXISTS idx_raw_posts_publication_queue
ON raw_posts(publication_status, editorial_status, rewrite_status, published_at, reviewed_at, id);

CREATE INDEX IF NOT EXISTS idx_raw_posts_publication_rank
ON raw_posts(final_category_tag, final_source_tag, published_at DESC);

CREATE TABLE IF NOT EXISTS publication_runs (
  id BIGSERIAL PRIMARY KEY,
  poster TEXT NOT NULL,
  schedule_id TEXT NOT NULL,
  scheduled_for DATE NOT NULL,
  scheduled_time TEXT NOT NULL,
  planned_count INTEGER NOT NULL DEFAULT 0,
  published_count INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'started',
  error TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(poster, schedule_id, scheduled_for)
);

CREATE INDEX IF NOT EXISTS idx_publication_runs_poster_date
ON publication_runs(poster, scheduled_for DESC, scheduled_time);

INSERT INTO worker_controls(name, enabled, settings_json)
VALUES ('tg-poster', FALSE, '{}'::jsonb)
ON CONFLICT (name) DO NOTHING;

INSERT INTO app_settings(key, value_json, value_type, title, description, category)
VALUES
  ('tg_poster_enabled', 'false'::jsonb, 'bool', 'TG poster enabled', 'Double safety flag for publishing accepted posts to Telegram.', 'TG Poster'),
  ('tg_poster_bot_token', '""'::jsonb, 'secret', 'TG poster bot token', 'Bot token for final Telegram publication. For test can be the same as upload bot token.', 'TG Poster'),
  ('tg_poster_chat_id', '"-1003712724327"'::jsonb, 'str', 'TG publication chat id', 'Target Telegram channel/group id. Supports chat_id:thread_id for topics.', 'TG Poster'),
  ('tg_poster_schedule_json', '[{"id":"morning","time":"10:00","count":1,"enabled":true},{"id":"evening","time":"18:00","count":1,"enabled":true}]'::jsonb, 'json', 'TG publication schedule', 'EKB time schedule rows: id, time HH:MM, count, enabled.', 'TG Poster'),
  ('tg_poster_interval_sec', '60'::jsonb, 'int', 'TG poster interval, sec', 'Pause between poster checks.', 'TG Poster'),
  ('tg_poster_media_group_max_items', '10'::jsonb, 'int', 'TG media group max items', 'Telegram media group supports 2..10 media items.', 'TG Poster'),
  ('tg_poster_caption_limit', '1024'::jsonb, 'int', 'TG caption limit', 'Telegram caption limit for media messages.', 'TG Poster'),
  ('tg_poster_message_limit', '4096'::jsonb, 'int', 'TG message limit', 'Telegram text message limit.', 'TG Poster'),
  ('tg_poster_max_attempts', '3'::jsonb, 'int', 'TG post retry attempts', 'Retries for Telegram API errors.', 'TG Poster'),
  ('tg_poster_retry_backoff_max_sec', '30'::jsonb, 'int', 'TG retry backoff max, sec', 'Max retry sleep for Telegram API errors.', 'TG Poster'),
  ('tg_poster_send_delay_sec', '1'::jsonb, 'float', 'TG send delay, sec', 'Delay between Telegram sends.', 'TG Poster'),
  ('tg_poster_text_overflow_caption', '"⬇️ Описание под медиа"'::jsonb, 'str', 'TG overflow caption', 'Caption used when full text is longer than Telegram media caption limit.', 'TG Poster'),
  ('tg_poster_recent_window', '20'::jsonb, 'int', 'Ranking recent window', 'How many published posts are checked to reduce repeated categories and sources.', 'TG Poster'),
  ('tg_poster_category_repeat_penalty', '3'::jsonb, 'float', 'Category repeat penalty', 'Ranking penalty for recently published same category.', 'TG Poster'),
  ('tg_poster_source_repeat_penalty', '4'::jsonb, 'float', 'Source repeat penalty', 'Ranking penalty for recently published same source.', 'TG Poster')
ON CONFLICT (key) DO NOTHING;

UPDATE app_settings
SET description='Обрезает исходный raw-текст перед отправкой в LLM. Итоговый rewrite должен быть компактным: Telegram caption 1024 символа минус будущая строка хэштегов.'
WHERE key='ai_writer_max_text_chars';

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.
- Keep text compact enough for Telegram media caption: target 850 characters, hard maximum 900 characters.
- Remember the system appends category and source hashtags after text, so leave space for them.
- Return JSON only. No markdown. No text outside JSON.
- Never return an empty object. Never omit the "rewrites" key.
- 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';

UPDATE app_settings
SET value_json = to_jsonb(
  regexp_replace(
    value_json #>> '{}',
    '- 3-6 коротких строк или 2-5 компактных абзацев;',
    '- 3-6 коротких строк или 2-4 компактных абзаца; итоговый text должен целиться в 850 символов и не превышать 900 символов, потому что система добавит хэштеги и Telegram caption ограничен 1024 символами;',
    'g'
  )
)
WHERE key='ai_writer_prompt'
  AND (value_json #>> '{}') LIKE '%3-6 коротких строк или 2-5 компактных абзацев%';