-- Add slug column to publications table
-- Run this migration to fix the "Column not found: 1054 Unknown column 'slug'" error

ALTER TABLE publications 
ADD COLUMN slug VARCHAR(255) AFTER title,
ADD INDEX idx_publications_slug (slug);

-- Update existing publications to generate slugs from titles
UPDATE publications 
SET slug = LOWER(REPLACE(REPLACE(REPLACE(title, '[^a-z0-9 ]', ''), ' ', '-'), '-', '')) 
WHERE slug IS NULL OR slug = '';

-- Add published_at column for better publication management
ALTER TABLE publications 
ADD COLUMN published_at TIMESTAMP NULL DEFAULT NULL AFTER created_at;

-- Update published_at for existing published publications
UPDATE publications 
SET published_at = created_at 
WHERE status = 'published' AND published_at IS NULL;