-- Add content_cluster column to published_content for internal linking
-- Run with: mysql -u root -p wordpress < 002_add_content_cluster_to_published.sql

ALTER TABLE parketry_published_content
ADD COLUMN content_cluster VARCHAR(50) AFTER main_keywords;

-- Backfill existing records from topics where possible
UPDATE parketry_published_content pc
JOIN parketry_articles a ON pc.wp_post_id = a.wp_post_id
JOIN parketry_content_topics t ON a.topic_id = t.id
SET pc.content_cluster = t.content_cluster
WHERE pc.content_cluster IS NULL;

-- Add index for cluster filtering
CREATE INDEX idx_content_cluster ON parketry_published_content(content_cluster);

-- Record this migration
INSERT INTO parketry_migrations (migration_name) VALUES ('002_add_content_cluster_to_published')
ON DUPLICATE KEY UPDATE executed_at = CURRENT_TIMESTAMP;
