CREATE TABLE IF NOT EXISTS post_publications (
  id BIGSERIAL PRIMARY KEY,
  raw_post_id BIGINT NOT NULL REFERENCES raw_posts(id) ON DELETE CASCADE,
  platform TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  target_id TEXT,
  external_id TEXT,
  url TEXT,
  error TEXT,
  attachments_json JSONB NOT NULL DEFAULT '[]'::jsonb,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(raw_post_id, platform)
);

CREATE INDEX IF NOT EXISTS idx_post_publications_platform_status
ON post_publications(platform, status, target_id, published_at DESC);

CREATE INDEX IF NOT EXISTS idx_post_publications_raw_post
ON post_publications(raw_post_id);

INSERT INTO post_publications(raw_post_id, platform, status, target_id, external_id, url, error, attachments_json, published_at, updated_at)
SELECT
  id,
  'tg',
  publication_status,
  tg_publication_chat_id::text,
  CASE WHEN tg_publication_message_ids IS NULL THEN NULL ELSE array_to_string(tg_publication_message_ids, ',') END,
  tg_publication_url,
  publication_error,
  COALESCE(to_jsonb(tg_publication_message_ids), '[]'::jsonb),
  published_at,
  NOW()
FROM raw_posts
WHERE COALESCE(publication_status, 'pending') <> 'pending'
ON CONFLICT (raw_post_id, platform) DO UPDATE
SET status=EXCLUDED.status,
    target_id=EXCLUDED.target_id,
    external_id=EXCLUDED.external_id,
    url=EXCLUDED.url,
    error=EXCLUDED.error,
    attachments_json=EXCLUDED.attachments_json,
    published_at=EXCLUDED.published_at,
    updated_at=NOW();

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

INSERT INTO app_settings(key, value_json, value_type, title, description, category)
VALUES
  ('vk_poster_enabled', 'false'::jsonb, 'bool', 'VK poster enabled', 'Double safety flag for publishing accepted posts to VK.', 'VK Poster'),
  ('vk_poster_access_token', '""'::jsonb, 'secret', 'VK poster access token', 'Group/user token with wall.post and photos permissions. If empty, env VK_GROUP_ACCESS_TOKEN/VK_ACCESS_TOKEN is used.', 'VK Poster'),
  ('vk_poster_owner_id', '0'::jsonb, 'int', 'VK target owner_id', 'Target VK group owner_id, usually negative: -123456789.', 'VK Poster'),
  ('vk_poster_from_group', 'true'::jsonb, 'bool', 'Post from group', 'Use from_group=1 when publishing to a community wall.', 'VK Poster'),
  ('vk_poster_schedule_json', '[{"id":"vk-morning","time":"10:05","count":1,"enabled":true},{"id":"vk-evening","time":"18:05","count":1,"enabled":true}]'::jsonb, 'json', 'VK publication schedule', 'EKB time schedule rows: id, time HH:MM, count, enabled.', 'VK Poster'),
  ('vk_poster_interval_sec', '60'::jsonb, 'int', 'VK poster interval, sec', 'Pause between poster checks.', 'VK Poster'),
  ('vk_poster_timeout_sec', '90'::jsonb, 'int', 'VK API timeout, sec', 'Total timeout for VK API calls and media uploads.', 'VK Poster'),
  ('vk_poster_max_attempts', '3'::jsonb, 'int', 'VK post retry attempts', 'Retries for VK API/network errors.', 'VK Poster'),
  ('vk_poster_retry_backoff_max_sec', '30'::jsonb, 'int', 'VK retry backoff max, sec', 'Max retry sleep for VK API/network errors.', 'VK Poster'),
  ('vk_poster_send_delay_sec', '1'::jsonb, 'float', 'VK send delay, sec', 'Delay between VK posts.', 'VK Poster'),
  ('vk_poster_recent_window', '20'::jsonb, 'int', 'Ranking recent window', 'How many VK-published posts are checked to reduce repeated categories and sources.', 'VK Poster'),
  ('vk_poster_category_repeat_penalty', '3'::jsonb, 'float', 'Category repeat penalty', 'Ranking penalty for recently published same category.', 'VK Poster'),
  ('vk_poster_source_repeat_penalty', '4'::jsonb, 'float', 'Source repeat penalty', 'Ranking penalty for recently published same source.', 'VK Poster'),
  ('vk_poster_dry_run', 'false'::jsonb, 'bool', 'VK dry run', 'If true, prepares attachments but does not call wall.post. Keep false for production.', 'VK Poster')
ON CONFLICT (key) DO NOTHING;
