<?php
try {
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=projects_membershipdb;charset=utf8mb4', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

    // Create post_categories table
    $pdo->exec("CREATE TABLE IF NOT EXISTS post_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        slug VARCHAR(255) NOT NULL UNIQUE,
        description TEXT DEFAULT NULL,
        sort_order INT DEFAULT 0,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Created post_categories table\n";

    // Add category_id to announcements
    $stmt = $pdo->query("SHOW COLUMNS FROM announcements LIKE 'category_id'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE announcements ADD COLUMN category_id INT DEFAULT NULL AFTER visibility");
        $pdo->exec("ALTER TABLE announcements ADD FOREIGN KEY (category_id) REFERENCES post_categories(id) ON DELETE SET NULL");
        echo "Added category_id to announcements\n";
    }

    // Add post_type to announcements
    $stmt = $pdo->query("SHOW COLUMNS FROM announcements LIKE 'post_type'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE announcements ADD COLUMN post_type VARCHAR(50) DEFAULT 'announcement' AFTER title");
        echo "Added post_type to announcements\n";
    }

    // Add some default categories
    $defaults = [
        ['General', 'general', 'General announcements and updates'],
        ['Membership', 'membership', 'Membership-related news'],
        ['Events', 'events', 'Event announcements'],
        ['Education', 'education', 'CME and educational content'],
        ['Policy', 'policy', 'Policy and advocacy updates'],
    ];
    foreach ($defaults as $cat) {
        $stmt = $pdo->prepare("SELECT id FROM post_categories WHERE slug = ?");
        $stmt->execute([$cat[1]]);
        if (!$stmt->fetch()) {
            $pdo->prepare("INSERT INTO post_categories (name, slug, description) VALUES (?, ?, ?)")->execute($cat);
            echo "Added category: {$cat[0]}\n";
        }
    }

    echo "Migration 016 completed successfully\n";
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
    exit(1);
}
