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

    // Add visibility to announcements
    $stmt = $pdo->query("SHOW COLUMNS FROM announcements LIKE 'visibility'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE announcements ADD COLUMN visibility ENUM('public','private') DEFAULT 'public' AFTER status");
        echo "Added visibility column to announcements\n";
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM announcements LIKE 'image'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE announcements ADD COLUMN image VARCHAR(255) DEFAULT NULL AFTER content");
        echo "Added image column to announcements\n";
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM announcements LIKE 'link_url'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE announcements ADD COLUMN link_url VARCHAR(500) DEFAULT NULL AFTER image");
        echo "Added link_url column to announcements\n";
    }

    // Add visibility to events
    $stmt = $pdo->query("SHOW COLUMNS FROM events LIKE 'visibility'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE events ADD COLUMN visibility ENUM('public','private') DEFAULT 'public' AFTER status");
        echo "Added visibility column to events\n";
    }

    // Add excerpt to announcements
    $stmt = $pdo->query("SHOW COLUMNS FROM announcements LIKE 'excerpt'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE announcements ADD COLUMN excerpt TEXT DEFAULT NULL AFTER content");
        echo "Added excerpt column to announcements\n";
    }

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