calendar_api.php

<?php
require_once __DIR__ . '/../bootstrap.php';

header('Content-Type: application/json; charset=utf-8');

global $pdo;

function json_error($message, $code = 400) {
    http_response_code($code);
    echo json_encode(['error' => $message]);
    exit;
}

function parse_payload() {
    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
    // Handles: application/json, application/json; charset=UTF-8, etc.
    if (stripos($contentType, 'application/json') !== false) {
        $raw = file_get_contents('php://input');
        $data = json_decode($raw, true);
        return is_array($data) ? $data : [];
    }
    return $_POST;
}

function normalize_hex($color) {
    $color = trim((string)$color);
    if ($color === '') return '#ff8c1a';
    if ($color[0] !== '#') $color = '#' . $color;
    if (!preg_match('/^#[0-9a-fA-F]{6}$/', $color)) {
        return '#ff8c1a';
    }
    return strtolower($color);
}

function normalize_job_number($value) {
    // Keep compatibility with older data: just trim + store as-is
    return trim((string)$value);
}


function dt_from_input($value, $allDay) {
    if ($value === null || $value === '') return null;
    try {
        if ($allDay) {
            // YYYY-MM-DD
            $dt = new DateTime($value . ' 00:00:00', new DateTimeZone('UTC'));
        } else {
            // Accept: YYYY-MM-DDTHH:MM (datetime-local), ISO strings, etc.
            $v = str_replace('T', ' ', $value);
            if (strlen($v) === 16) $v .= ':00';
            $dt = new DateTime($v, new DateTimeZone('UTC'));
        }
        return $dt;
    } catch (Throwable $e) {
        return null;
    }
}

// API-safe auth: do NOT redirect to login, return JSON so the UI can display the issue.
if (!isLoggedIn()) {
    json_error('Not authenticated. Please refresh the page and log in again.', 401);
}

// --- GET endpoints ---
$action = $_GET['action'] ?? null;
if ($_SERVER['REQUEST_METHOD'] === 'GET') {

    if ($action === 'events' || $action === 'list') {
        if (!hasPermission('calendar.view')) json_error('Access denied', 403);

        $start = $_GET['start'] ?? null;
        $end   = $_GET['end'] ?? null;
        $category = (isset($_GET['category']) && $_GET['category'] !== '') ? (int)$_GET['category'] : null;
        $vanId = (isset($_GET['van_id']) && $_GET['van_id'] !== '') ? (int)$_GET['van_id'] : null;

        $where = [];
        $params = [];

        // Range filtering (FullCalendar gives an exclusive range end)
        if ($start && $end) {
            try {
                $rs = new DateTime($start, new DateTimeZone('UTC'));
                $re = new DateTime($end, new DateTimeZone('UTC'));
                // End times are stored as **exclusive** for all-day events (end = next day 00:00).
                // Using >= leaks events that end exactly at the range start into the next day.
                $where[] = '(e.start_at < ? AND (e.end_at IS NULL OR e.end_at > ?))';
                $params[] = $re->format('Y-m-d H:i:s');
                $params[] = $rs->format('Y-m-d H:i:s');
            } catch (Throwable $e) {
                // ignore range filter if parsing fails
            }
        }

        if ($category) {
            $where[] = 'e.category_id = ?';
            $params[] = $category;
        }

        if ($vanId) {
            $where[] = 'e.van_id = ?';
            $params[] = $vanId;
        }

        $sql = "
            SELECT
                e.id, e.title, e.description, e.location,
                e.start_at, e.end_at, e.all_day,
                e.category_id, e.meta,
                e.run_number, e.van_id, e.van_plate_temp,
                e.driver_user_id, e.driver_name_other,
                e.porter_user_id, e.porter_name_other,
                e.is_run_event,
                e.meeting_with, e.meeting_status,
                c.name AS category_name,
                c.color AS category_color,
                v.tag_color AS van_color,
                v.plate_full AS van_plate_full,
                v.plate_short AS van_plate_short,
                driver.display_name AS driver_display_name,
                porter.display_name AS porter_display_name
            FROM calendar_events e
            LEFT JOIN calendar_categories c ON c.id = e.category_id
            LEFT JOIN vans v ON v.id = e.van_id
            LEFT JOIN users driver ON driver.id = e.driver_user_id
            LEFT JOIN users porter ON porter.id = e.porter_user_id
        ";

        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        $sql .= ' ORDER BY e.start_at ASC';

        try {
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $rows = $stmt->fetchAll();
        } catch (Throwable $e) {
            json_error('Calendar tables not found or query failed', 500);
        }

        $events = [];
        foreach ($rows as $r) {
            // Color logic: Runs use van color, all others use category color
            if ($r['is_run_event'] == 1 && $r['van_color']) {
                $color = $r['van_color'];
            } else {
                $color = $r['category_color'] ?: '#ff8c1a';
            }
            
            $meta = $r['meta'] ? json_decode($r['meta'], true) : [];
            if (!is_array($meta)) $meta = [];
            
            $events[] = [
                'id' => (string)$r['id'],
                'title' => $r['title'],
                'start' => date('c', strtotime($r['start_at'])),
                'end' => $r['end_at'] ? date('c', strtotime($r['end_at'])) : null,
                'allDay' => ((int)$r['all_day'] === 1),
                'backgroundColor' => $color,
                'borderColor' => $color,
                'extendedProps' => [
                    'description' => $r['description'],
                    'location' => $r['location'],
                    'category_id' => $r['category_id'],
                    'category_name' => $r['category_name'],
                    'van_plate' => $r['van_plate_full'] ?: $r['van_plate_temp'],
                    'job_number' => $r['run_number'],
                    'run_number' => $r['run_number'],
                    'driver' => $r['driver_display_name'] ?: $r['driver_name_other'],
                    'porter' => $r['porter_display_name'] ?: $r['porter_name_other'],
                    'meeting_with' => $r['meeting_with'] ?? null,
                    'meeting_status' => $r['meeting_status'] ?? null,
                    'custom_fields' => $meta
                ]
            ];
        }

        echo json_encode($events);
        exit;
    }

    if ($action === 'categories') {
        if (!hasPermission('calendar.view')) json_error('Access denied', 403);
        try {
            $stmt = $pdo->query("SELECT id, name, color, sort_order FROM calendar_categories ORDER BY sort_order ASC, name ASC");
            $categories = $stmt->fetchAll();
            
            // Load custom fields for each category
            foreach ($categories as &$cat) {
                $fieldStmt = $pdo->prepare("SELECT id, field_name, field_label, field_type, field_options, required, sort_order FROM calendar_category_fields WHERE category_id = ? ORDER BY sort_order ASC");
                $fieldStmt->execute([$cat['id']]);
                $cat['fields'] = $fieldStmt->fetchAll();
            }
            
            echo json_encode(['categories' => $categories]);
            exit;
        } catch (Throwable $e) {
            json_error('Calendar tables not found', 500);
        }
    }
    
    if ($action === 'get') {
        if (!hasPermission('calendar.view')) json_error('Access denied', 403);
        
        $id = isset($_GET['id']) ? (int)$_GET['id'] : 0;
        if ($id <= 0) json_error('Invalid event ID');
        
        try {
            $stmt = $pdo->prepare("
                SELECT
                    e.id, e.title, e.description, e.location,
                    e.start_at, e.end_at, e.all_day,
                    e.category_id, e.meta,
                    e.run_number, e.van_id, e.van_plate_temp,
                    e.driver_user_id, e.driver_name_other,
                    e.porter_user_id, e.porter_name_other,
                    e.is_run_event,
                    e.meeting_with, e.meeting_by_user_id, e.meeting_status,
                    c.name AS category_name,
                    c.color AS category_color,
                    v.tag_color AS van_color,
                    v.plate_full AS van_plate_full,
                    v.plate_short AS van_plate_short,
                    driver.display_name AS driver_display_name,
                    porter.display_name AS porter_display_name,
                    meeting_by.display_name AS meeting_by_name
                FROM calendar_events e
                LEFT JOIN calendar_categories c ON c.id = e.category_id
                LEFT JOIN vans v ON v.id = e.van_id
                LEFT JOIN users driver ON driver.id = e.driver_user_id
                LEFT JOIN users porter ON porter.id = e.porter_user_id
                LEFT JOIN users meeting_by ON meeting_by.id = e.meeting_by_user_id
                WHERE e.id = ?
            ");
            $stmt->execute([$id]);
            $event = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$event) {
                json_error('Event not found', 404);
            }
            
            // Format dates
            $event['start'] = date('Y-m-d', strtotime($event['start_at']));
            $event['end'] = $event['end_at'] ? date('Y-m-d', strtotime($event['end_at'])) : null;
            $event['notes'] = $event['description'];
            
            // Add formatted names for display
            $event['driver_name'] = $event['driver_display_name'] ?: $event['driver_name_other'];
            $event['porter_name'] = $event['porter_display_name'] ?: $event['porter_name_other'];
            
            // Load annual leave staff assignments if applicable
            try {
                $staffStmt = $pdo->prepare("
                    SELECT u.id, u.display_name 
                    FROM annual_leave_staff als
                    JOIN users u ON u.id = als.user_id
                    WHERE als.event_id = ?
                    ORDER BY u.display_name
                ");
                $staffStmt->execute([$id]);
                $staffMembers = $staffStmt->fetchAll(PDO::FETCH_ASSOC);
                $event['staff_members'] = $staffMembers;
                $event['staff_ids'] = array_column($staffMembers, 'id');
            } catch (Throwable $e) {
                $event['staff_members'] = [];
                $event['staff_ids'] = [];
            }
            
            echo json_encode(['success' => true, 'event' => $event]);
            exit;
        } catch (Throwable $e) {
            json_error('Failed to load event', 500);
        }
    }

    json_error('Unknown action', 400);
}

// --- POST endpoints ---
$payload = parse_payload();
$action = $payload['action'] ?? '';

// CSRF validation for all writes (local to the calendar module)
$csrf = $_SERVER['HTTP_X_CSRF_TOKEN'] ?? '';
$expected = $_SESSION['calendar_csrf_token'] ?? '';
if (!$expected || !is_string($csrf) || !hash_equals($expected, $csrf)) {
    json_error('CSRF check failed. Please refresh the page and try again.', 403);
}

try {
    // Unified save action for runs (handles both create and update)
    if ($action === 'save') {
        $id = isset($payload['id']) && $payload['id'] ? (int)$payload['id'] : 0;
        
        if ($id > 0) {
            // Update existing
            if (!hasPermission('calendar.edit')) json_error('Access denied', 403);
            
            $title = trim((string)($payload['title'] ?? ''));
            $allDay = (int)($payload['all_day'] ?? 1) === 1;
            $start = dt_from_input($payload['start'] ?? null, $allDay);
            $end = dt_from_input($payload['end'] ?? null, $allDay);
            
            if ($title === '') json_error('Title is required');
            if (!$start) json_error('Start is required');
            
            if ($allDay && $end) {
                $end->modify('+1 day');
            }
            
            $categoryId = (isset($payload['category_id']) && $payload['category_id'] !== '') ? (int)$payload['category_id'] : null;
            $location = trim((string)($payload['location'] ?? ''));
            $notes = trim((string)($payload['notes'] ?? ''));
            
            $runNumber = normalize_job_number($payload['run_number'] ?? '');
            $vanId = (isset($payload['van_id']) && $payload['van_id'] !== '' && $payload['van_id'] !== 'temp') ? (int)$payload['van_id'] : null;
            $vanPlateTemp = trim((string)($payload['van_plate_temp'] ?? ''));
            $driverUserId = (isset($payload['driver_user_id']) && $payload['driver_user_id'] !== '' && $payload['driver_user_id'] !== 'other') ? (int)$payload['driver_user_id'] : null;
            $driverNameOther = trim((string)($payload['driver_name_other'] ?? ''));
            $porterUserId = (isset($payload['porter_user_id']) && $payload['porter_user_id'] !== '' && $payload['porter_user_id'] !== 'other') ? (int)$payload['porter_user_id'] : null;
            $porterNameOther = trim((string)($payload['porter_name_other'] ?? ''));
            $isRunEvent = (int)($payload['is_run_event'] ?? 0) === 1 ? 1 : 0;
            
            // Meeting fields
            $meetingWith = trim((string)($payload['meeting_with'] ?? ''));
            $meetingByUserId = (isset($payload['meeting_by_user_id']) && $payload['meeting_by_user_id'] !== '') ? (int)$payload['meeting_by_user_id'] : null;
            $meetingStatus = trim((string)($payload['meeting_status'] ?? 'pending'));
            if (!in_array($meetingStatus, ['pending', 'attended', 'no_show', 'cancelled'])) {
                $meetingStatus = 'pending';
            }
            
            $stmt = $pdo->prepare("UPDATE calendar_events SET title=?, description=?, location=?, start_at=?, end_at=?, all_day=?, category_id=?, run_number=?, van_id=?, van_plate_temp=?, driver_user_id=?, driver_name_other=?, porter_user_id=?, porter_name_other=?, is_run_event=?, meeting_with=?, meeting_by_user_id=?, meeting_status=?, updated_by=? WHERE id=?");
            $stmt->execute([
                $title,
                $notes ?: null,
                $location ?: null,
                $start->format('Y-m-d H:i:s'),
                $end ? $end->format('Y-m-d H:i:s') : null,
                $allDay ? 1 : 0,
                $categoryId,
                $runNumber ?: null,
                $vanId,
                $vanPlateTemp ?: null,
                $driverUserId,
                $driverNameOther ?: null,
                $porterUserId,
                $porterNameOther ?: null,
                $isRunEvent,
                $meetingWith ?: null,
                $meetingByUserId,
                $meetingStatus,
                (int)($_SESSION['user_id'] ?? 0) ?: null,
                $id
            ]);
            
            // Handle annual leave staff assignments
            if (isset($payload['staff_ids'])) {
                $staffIds = is_array($payload['staff_ids']) ? $payload['staff_ids'] : explode(',', $payload['staff_ids']);
                $staffIds = array_filter(array_map('intval', $staffIds));
                
                // Delete existing assignments
                $pdo->prepare("DELETE FROM annual_leave_staff WHERE event_id = ?")->execute([$id]);
                
                // Insert new assignments
                if (!empty($staffIds)) {
                    $stmt = $pdo->prepare("INSERT IGNORE INTO annual_leave_staff (event_id, user_id) VALUES (?, ?)");
                    foreach ($staffIds as $userId) {
                        $stmt->execute([$id, $userId]);
                    }
                }
            }
            
            logActivity('calendar.edit', 'calendar_event', $id, 'Updated job: ' . $title);
            echo json_encode(['success' => true, 'id' => $id]);
            exit;
        } else {
            // Create new
            if (!hasPermission('calendar.create')) json_error('Access denied', 403);
            
            $title = trim((string)($payload['title'] ?? ''));
            $allDay = (int)($payload['all_day'] ?? 1) === 1;
            $start = dt_from_input($payload['start'] ?? null, $allDay);
            $end = dt_from_input($payload['end'] ?? null, $allDay);
            
            if ($title === '') json_error('Title is required');
            if (!$start) json_error('Start is required');
            
            if ($allDay && $end) {
                $end->modify('+1 day');
            }
            
            $categoryId = (isset($payload['category_id']) && $payload['category_id'] !== '') ? (int)$payload['category_id'] : null;
            $location = trim((string)($payload['location'] ?? ''));
            $notes = trim((string)($payload['notes'] ?? ''));
            
            $runNumber = normalize_job_number($payload['run_number'] ?? '');
            $vanId = (isset($payload['van_id']) && $payload['van_id'] !== '' && $payload['van_id'] !== 'temp') ? (int)$payload['van_id'] : null;
            $vanPlateTemp = trim((string)($payload['van_plate_temp'] ?? ''));
            $driverUserId = (isset($payload['driver_user_id']) && $payload['driver_user_id'] !== '' && $payload['driver_user_id'] !== 'other') ? (int)$payload['driver_user_id'] : null;
            $driverNameOther = trim((string)($payload['driver_name_other'] ?? ''));
            $porterUserId = (isset($payload['porter_user_id']) && $payload['porter_user_id'] !== '' && $payload['porter_user_id'] !== 'other') ? (int)$payload['porter_user_id'] : null;
            $porterNameOther = trim((string)($payload['porter_name_other'] ?? ''));
            $isRunEvent = (int)($payload['is_run_event'] ?? 0) === 1 ? 1 : 0;
            
            // Meeting fields
            $meetingWith = trim((string)($payload['meeting_with'] ?? ''));
            $meetingByUserId = (isset($payload['meeting_by_user_id']) && $payload['meeting_by_user_id'] !== '') ? (int)$payload['meeting_by_user_id'] : null;
            $meetingStatus = trim((string)($payload['meeting_status'] ?? 'pending'));
            if (!in_array($meetingStatus, ['pending', 'attended', 'no_show', 'cancelled'])) {
                $meetingStatus = 'pending';
            }
            
            $stmt = $pdo->prepare("INSERT INTO calendar_events (title, description, location, start_at, end_at, all_day, category_id, run_number, van_id, van_plate_temp, driver_user_id, driver_name_other, porter_user_id, porter_name_other, is_run_event, meeting_with, meeting_by_user_id, meeting_status, created_by, updated_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $stmt->execute([
                $title,
                $notes ?: null,
                $location ?: null,
                $start->format('Y-m-d H:i:s'),
                $end ? $end->format('Y-m-d H:i:s') : null,
                $allDay ? 1 : 0,
                $categoryId,
                $runNumber ?: null,
                $vanId,
                $vanPlateTemp ?: null,
                $driverUserId,
                $driverNameOther ?: null,
                $porterUserId,
                $porterNameOther ?: null,
                $isRunEvent,
                $meetingWith ?: null,
                $meetingByUserId,
                $meetingStatus,
                (int)($_SESSION['user_id'] ?? 0) ?: null,
                (int)($_SESSION['user_id'] ?? 0) ?: null
            ]);
            
            $newId = (int)$pdo->lastInsertId();
            
            // Handle annual leave staff assignments
            if (isset($payload['staff_ids']) && !empty($payload['staff_ids'])) {
                $staffIds = is_array($payload['staff_ids']) ? $payload['staff_ids'] : explode(',', $payload['staff_ids']);
                $staffIds = array_filter(array_map('intval', $staffIds));
                
                // Delete existing assignments
                $pdo->prepare("DELETE FROM annual_leave_staff WHERE event_id = ?")->execute([$newId]);
                
                // Insert new assignments
                if (!empty($staffIds)) {
                    $stmt = $pdo->prepare("INSERT IGNORE INTO annual_leave_staff (event_id, user_id) VALUES (?, ?)");
                    foreach ($staffIds as $userId) {
                        $stmt->execute([$newId, $userId]);
                    }
                }
            }
            
            logActivity('calendar.create', 'calendar_event', $newId, 'Created job: ' . $title);
            echo json_encode(['success' => true, 'id' => $newId]);
            exit;
        }
    }
    
    if ($action === 'create_event') {
        if (!hasPermission('calendar.create')) json_error('Access denied', 403);

        $title = trim((string)($payload['title'] ?? ''));
        $allDay = (int)($payload['all_day'] ?? 0) === 1;
        $start = dt_from_input($payload['start'] ?? null, $allDay);
        $endIn = $payload['end'] ?? null;
        $end = dt_from_input($endIn, $allDay);
        $endExclusive = (int)($payload['end_exclusive'] ?? 0) === 1;

        if ($title === '') json_error('Title is required');
        if (!$start) json_error('Start is required');

        // For all-day end dates from forms: store exclusive end (end + 1 day at 00:00)
        if ($allDay && $end && !$endExclusive) {
            $end->modify('+1 day');
        }

        $categoryId = (isset($payload['category_id']) && $payload['category_id'] !== '') ? (int)$payload['category_id'] : null;
        $location = trim((string)($payload['location'] ?? ''));
        $description = trim((string)($payload['notes'] ?? $payload['description'] ?? ''));
        
        // Handle custom fields
        $customFields = isset($payload['custom_fields']) && is_array($payload['custom_fields']) ? $payload['custom_fields'] : [];
        
        // Validate required custom fields if category is set
        if ($categoryId) {
            try {
                $fieldStmt = $pdo->prepare("SELECT field_name, field_label, required FROM calendar_category_fields WHERE category_id = ?");
                $fieldStmt->execute([$categoryId]);
                $fields = $fieldStmt->fetchAll();
                
                foreach ($fields as $field) {
                    if ($field['required'] && empty($customFields[$field['field_name']])) {
                        json_error($field['field_label'] . ' is required');
                    }
                }
            } catch (Throwable $e) {
                // Field validation table might not exist yet
            }
        }
        
        $metaJson = !empty($customFields) ? json_encode($customFields) : null;
        
        // Handle run-based fields
        $runNumber = normalize_job_number($payload['run_number'] ?? '');
        $vanId = (isset($payload['van_id']) && $payload['van_id'] !== '') ? (int)$payload['van_id'] : null;
        $vanPlateTemp = trim((string)($payload['van_plate_temp'] ?? ''));
        $driverUserId = (isset($payload['driver_user_id']) && $payload['driver_user_id'] !== '' && $payload['driver_user_id'] !== 'other') ? (int)$payload['driver_user_id'] : null;
        $driverNameOther = trim((string)($payload['driver_name_other'] ?? ''));
        $porterUserId = (isset($payload['porter_user_id']) && $payload['porter_user_id'] !== '' && $payload['porter_user_id'] !== 'other') ? (int)$payload['porter_user_id'] : null;
        $porterNameOther = trim((string)($payload['porter_name_other'] ?? ''));
        $isRunEvent = (int)($payload['is_run_event'] ?? 0) === 1 ? 1 : 0;

        $stmt = $pdo->prepare("INSERT INTO calendar_events (title, description, location, start_at, end_at, all_day, category_id, meta, run_number, van_id, van_plate_temp, driver_user_id, driver_name_other, porter_user_id, porter_name_other, is_run_event, created_by, updated_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt->execute([
            $title,
            $description ?: null,
            $location ?: null,
            $start->format('Y-m-d H:i:s'),
            $end ? $end->format('Y-m-d H:i:s') : null,
            $allDay ? 1 : 0,
            $categoryId,
            $metaJson,
            $runNumber ?: null,
            $vanId,
            $vanPlateTemp ?: null,
            $driverUserId,
            $driverNameOther ?: null,
            $porterUserId,
            $porterNameOther ?: null,
            $isRunEvent,
            (int)($_SESSION['user_id'] ?? 0) ?: null,
            (int)($_SESSION['user_id'] ?? 0) ?: null
        ]);

        $id = (int)$pdo->lastInsertId();
        logActivity('calendar.create', 'calendar_event', $id, 'Created event: ' . $title);

        echo json_encode(['success' => true, 'id' => $id]);
        exit;
    }

    if ($action === 'update_event') {
        if (!hasPermission('calendar.edit')) json_error('Access denied', 403);

        $id = (int)($payload['id'] ?? 0);
        if ($id <= 0) json_error('Invalid event id');

        $title = trim((string)($payload['title'] ?? ''));
        $allDay = (int)($payload['all_day'] ?? 0) === 1;
        $start = dt_from_input($payload['start'] ?? null, $allDay);
        $end = dt_from_input($payload['end'] ?? null, $allDay);
        $endExclusive = (int)($payload['end_exclusive'] ?? 0) === 1;

        if ($title === '') json_error('Title is required');
        if (!$start) json_error('Start is required');

        if ($allDay && $end && !$endExclusive) {
            $end->modify('+1 day');
        }

        $categoryId = (isset($payload['category_id']) && $payload['category_id'] !== '') ? (int)$payload['category_id'] : null;
        $location = trim((string)($payload['location'] ?? ''));
        $description = trim((string)($payload['notes'] ?? $payload['description'] ?? ''));
        
        // Handle custom fields
        $customFields = isset($payload['custom_fields']) && is_array($payload['custom_fields']) ? $payload['custom_fields'] : [];
        
        // Validate required custom fields if category is set
        if ($categoryId) {
            try {
                $fieldStmt = $pdo->prepare("SELECT field_name, field_label, required FROM calendar_category_fields WHERE category_id = ?");
                $fieldStmt->execute([$categoryId]);
                $fields = $fieldStmt->fetchAll();
                
                foreach ($fields as $field) {
                    if ($field['required'] && empty($customFields[$field['field_name']])) {
                        json_error($field['field_label'] . ' is required');
                    }
                }
            } catch (Throwable $e) {
                // Field validation table might not exist yet
            }
        }
        
        $metaJson = !empty($customFields) ? json_encode($customFields) : null;
        
        // Handle run-based fields
        $runNumber = normalize_job_number($payload['run_number'] ?? '');
        $vanId = (isset($payload['van_id']) && $payload['van_id'] !== '') ? (int)$payload['van_id'] : null;
        $vanPlateTemp = trim((string)($payload['van_plate_temp'] ?? ''));
        $driverUserId = (isset($payload['driver_user_id']) && $payload['driver_user_id'] !== '' && $payload['driver_user_id'] !== 'other') ? (int)$payload['driver_user_id'] : null;
        $driverNameOther = trim((string)($payload['driver_name_other'] ?? ''));
        $porterUserId = (isset($payload['porter_user_id']) && $payload['porter_user_id'] !== '' && $payload['porter_user_id'] !== 'other') ? (int)$payload['porter_user_id'] : null;
        $porterNameOther = trim((string)($payload['porter_name_other'] ?? ''));
        $isRunEvent = (int)($payload['is_run_event'] ?? 0) === 1 ? 1 : 0;

        $stmt = $pdo->prepare("UPDATE calendar_events SET title=?, description=?, location=?, start_at=?, end_at=?, all_day=?, category_id=?, meta=?, run_number=?, van_id=?, van_plate_temp=?, driver_user_id=?, driver_name_other=?, porter_user_id=?, porter_name_other=?, is_run_event=?, updated_by=? WHERE id=?");
        $stmt->execute([
            $title,
            $description ?: null,
            $location ?: null,
            $start->format('Y-m-d H:i:s'),
            $end ? $end->format('Y-m-d H:i:s') : null,
            $allDay ? 1 : 0,
            $categoryId,
            $metaJson,
            $runNumber ?: null,
            $vanId,
            $vanPlateTemp ?: null,
            $driverUserId,
            $driverNameOther ?: null,
            $porterUserId,
            $porterNameOther ?: null,
            $isRunEvent,
            (int)($_SESSION['user_id'] ?? 0) ?: null,
            $id
        ]);

        logActivity('calendar.edit', 'calendar_event', $id, 'Updated event: ' . $title);
        echo json_encode(['success' => true]);
        exit;
    }

    if ($action === 'delete' || $action === 'delete_event') {
        if (!hasPermission('calendar.delete')) json_error('Access denied', 403);
        $id = (int)($payload['id'] ?? 0);
        if ($id <= 0) json_error('Invalid event id');

        $title = null;
        try {
            $st = $pdo->prepare('SELECT title FROM calendar_events WHERE id=?');
            $st->execute([$id]);
            $title = $st->fetchColumn();
        } catch (Throwable $e) {}

        $stmt = $pdo->prepare('DELETE FROM calendar_events WHERE id=?');
        $stmt->execute([$id]);
        logActivity('calendar.delete', 'calendar_event', $id, 'Deleted event: ' . ($title ?: ('#'.$id)));
        echo json_encode(['success' => true]);
        exit;
    }

    if ($action === 'create_category') {
        if (!hasPermission('calendar.categories.manage')) json_error('Access denied', 403);
        $name = trim((string)($payload['name'] ?? ''));
        if ($name === '') json_error('Name is required');
        $color = normalize_hex($payload['color'] ?? '#ff8c1a');
        $sort = (int)($payload['sort_order'] ?? 0);

        $stmt = $pdo->prepare('INSERT INTO calendar_categories (name, color, sort_order, created_by) VALUES (?,?,?,?)');
        $stmt->execute([$name, $color, $sort, (int)($_SESSION['user_id'] ?? 0) ?: null]);
        $id = (int)$pdo->lastInsertId();
        logActivity('calendar.category_create', 'calendar_category', $id, 'Created category: ' . $name);
        echo json_encode(['success' => true, 'id' => $id]);
        exit;
    }

    if ($action === 'update_category') {
        if (!hasPermission('calendar.categories.manage')) json_error('Access denied', 403);
        $id = (int)($payload['id'] ?? 0);
        if ($id <= 0) json_error('Invalid category id');
        $name = trim((string)($payload['name'] ?? ''));
        if ($name === '') json_error('Name is required');
        $color = normalize_hex($payload['color'] ?? '#ff8c1a');
        $sort = (int)($payload['sort_order'] ?? 0);

        $stmt = $pdo->prepare('UPDATE calendar_categories SET name=?, color=?, sort_order=? WHERE id=?');
        $stmt->execute([$name, $color, $sort, $id]);
        logActivity('calendar.category_edit', 'calendar_category', $id, 'Updated category: ' . $name);
        echo json_encode(['success' => true]);
        exit;
    }

    if ($action === 'delete_category') {
        if (!hasPermission('calendar.categories.manage')) json_error('Access denied', 403);
        $id = (int)($payload['id'] ?? 0);
        if ($id <= 0) json_error('Invalid category id');

        $title = null;
        try {
            $st = $pdo->prepare('SELECT name FROM calendar_categories WHERE id=?');
            $st->execute([$id]);
            $title = $st->fetchColumn();
        } catch (Throwable $e) {}

        $stmt = $pdo->prepare('DELETE FROM calendar_categories WHERE id=?');
        $stmt->execute([$id]);
        logActivity('calendar.category_delete', 'calendar_category', $id, 'Deleted category: ' . ($title ?: ('#'.$id)));
        echo json_encode(['success' => true]);
        exit;
    }

    json_error('Unknown action', 400);

} catch (Throwable $e) {
    json_error('Server error: ' . $e->getMessage(), 500);
}