<?php
/**
 * Database Migration Runner
 * Adds missing 'slug' column to publications table
 */

require_once 'includes/config.php';
require_once 'includes/database.php';

try {
    $db = Database::getInstance();
    
    echo "Starting migration: Add slug column to publications table...\n";
    
    // Check if slug column already exists
    $checkSql = "SHOW COLUMNS FROM publications LIKE 'slug'";
    $result = $db->fetchAll($checkSql);
    
    if (empty($result)) {
        echo "Adding slug column...\n";
        
        // Add slug column
        $db->query("ALTER TABLE publications ADD COLUMN slug VARCHAR(255) AFTER title");
        
        // Add index for slug
        $db->query("ALTER TABLE publications ADD INDEX idx_publications_slug (slug)");
        
        echo "Slug column added successfully.\n";
    } else {
        echo "Slug column already exists.\n";
    }
    
    // Check if published_at column exists
    $checkPublishedAt = "SHOW COLUMNS FROM publications LIKE 'published_at'";
    $resultPublishedAt = $db->fetchAll($checkPublishedAt);
    
    if (empty($resultPublishedAt)) {
        echo "Adding published_at column...\n";
        $db->query("ALTER TABLE publications ADD COLUMN published_at TIMESTAMP NULL DEFAULT NULL AFTER created_at");
        echo "Published_at column added successfully.\n";
    } else {
        echo "Published_at column already exists.\n";
    }
    
    // Update existing publications to generate slugs
    echo "Generating slugs for existing publications...\n";
    $updateSql = "UPDATE publications SET slug = LOWER(REPLACE(REPLACE(REPLACE(title, '[^a-z0-9 ]', ''), ' ', '-'), '-', '')) WHERE slug IS NULL OR slug = ''";
    $db->query($updateSql);
    
    // Update published_at for existing published publications
    echo "Updating published_at for existing publications...\n";
    $updatePublishedAtSql = "UPDATE publications SET published_at = created_at WHERE status = 'published' AND published_at IS NULL";
    $db->query($updatePublishedAtSql);
    
    echo "Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "Migration failed: " . $e->getMessage() . "\n";
    echo "Please check your database configuration and permissions.\n";
}
?>