INSERT INTO app_settings(key, value_json, value_type, title, description, category)
VALUES
  ('site_poster_provider', '""'::jsonb, 'str', 'Site provider', 'Publish adapter: ghost now; wordpress can be added as a code adapter later.', 'Site Poster'),
  ('site_poster_base_url', '""'::jsonb, 'str', 'Site API base URL', 'Public/admin base URL for the selected site provider.', 'Site Poster'),
  ('site_poster_secret_file', '""'::jsonb, 'str', 'Site secret file', 'Server path to the selected provider API secret. Keep empty in git.', 'Site Poster'),
  ('site_poster_fallback_title', '"Новость"'::jsonb, 'str', 'Fallback article title', 'Title used when post text has no first line.', 'Site Poster')
ON CONFLICT (key) DO NOTHING;

UPDATE app_settings
SET value_json='"ghost"'::jsonb,
    updated_at=NOW()
WHERE key='site_poster_provider'
  AND COALESCE(value_json #>> '{}', '') = ''
  AND EXISTS (
    SELECT 1 FROM app_settings legacy
    WHERE legacy.key='site_poster_ghost_url'
      AND COALESCE(legacy.value_json #>> '{}', '') <> ''
  );

UPDATE app_settings target
SET value_json=to_jsonb(legacy.value_json #>> '{}'),
    updated_at=NOW()
FROM app_settings legacy
WHERE target.key='site_poster_base_url'
  AND legacy.key='site_poster_ghost_url'
  AND COALESCE(target.value_json #>> '{}', '') = ''
  AND COALESCE(legacy.value_json #>> '{}', '') <> '';

UPDATE app_settings target
SET value_json=to_jsonb(legacy.value_json #>> '{}'),
    updated_at=NOW()
FROM app_settings legacy
WHERE target.key='site_poster_secret_file'
  AND legacy.key='site_poster_key_file'
  AND COALESCE(target.value_json #>> '{}', '') = ''
  AND COALESCE(legacy.value_json #>> '{}', '') <> '';