<?php
// api/update_schema_v2.php
require_once '../config/database.php';
require_once '../includes/functions.php';

// Allow admin only? Or run once.
// requireRole('admin'); 

try {
    $pdo->beginTransaction();

    // 1. Add physical_attendance to youtube_manual_logs
    try {
        $pdo->query("SELECT physical_attendance FROM youtube_manual_logs LIMIT 1");
    } catch (PDOException $e) {
        $pdo->exec("ALTER TABLE youtube_manual_logs ADD COLUMN physical_attendance INT DEFAULT 0 AFTER peak_viewers");
        echo "Added physical_attendance to youtube_manual_logs.<br>";
    }

    // 2. Add service_type to radio_manual_logs
    try {
        $pdo->query("SELECT service_type FROM radio_manual_logs LIMIT 1");
    } catch (PDOException $e) {
        $pdo->exec("ALTER TABLE radio_manual_logs ADD COLUMN service_type VARCHAR(100) DEFAULT 'General' AFTER service_date");
        echo "Added service_type to radio_manual_logs.<br>";
    }

    // 3. Ensure radio_manual_logs has an ID
    try {
        $pdo->query("SELECT id FROM radio_manual_logs LIMIT 1");
    } catch (PDOException $e) {
        $pdo->exec("ALTER TABLE radio_manual_logs ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST");
        echo "Added ID to radio_manual_logs.<br>";
    }

    $pdo->commit();
    echo "Schema update completed successfully.";

} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Error updating schema: " . $e->getMessage();
}
?>
