-- Fix missing slug column issue
-- Execute this SQL to add the missing 'slug' column to publications table

-- Add slug column if it doesn't exist
ALTER TABLE publications 
ADD COLUMN IF NOT EXISTS slug VARCHAR(255) AFTER title;

-- Add index for slug
ALTER TABLE publications 
ADD INDEX IF NOT EXISTS idx_publications_slug (slug);

-- Add published_at column if it doesn't exist  
ALTER TABLE publications 
ADD COLUMN IF NOT EXISTS published_at TIMESTAMP NULL DEFAULT NULL AFTER created_at;

-- 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 = '';

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