CREATE TABLE IF NOT EXISTS content_categories (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  tag TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_content_categories_active_order
ON content_categories(is_active, sort_order, name);

INSERT INTO content_categories(name, tag, sort_order)
SELECT value, regexp_replace(lower(value), '[^[:alnum:]_а-яё]+', '_', 'g'), ordinality::int
FROM app_settings s
CROSS JOIN LATERAL jsonb_array_elements_text(
  CASE WHEN jsonb_typeof(s.value_json) = 'array' THEN s.value_json ELSE '[]'::jsonb END
) WITH ORDINALITY AS items(value, ordinality)
WHERE s.key = 'ai_writer_categories'
ON CONFLICT (name) DO UPDATE
SET tag = EXCLUDED.tag,
    sort_order = LEAST(content_categories.sort_order, EXCLUDED.sort_order),
    updated_at = NOW();
