<?php
declare(strict_types=1);

require_once __DIR__ . '/../includes/bootstrap.php';

echo "Updating admin_today_tasks view to include custom_task_id...\n\n";

try {
    $pdo = Database::pdo();
    
    // Drop the existing view
    echo "Step 1: Dropping existing admin_today_tasks view...\n";
    $pdo->exec("DROP VIEW IF EXISTS admin_today_tasks");
    echo "✓ View dropped\n\n";
    
    // Create the updated view with custom_task_id
    echo "Step 2: Creating updated admin_today_tasks view...\n";
    $createViewSQL = "CREATE VIEW admin_today_tasks AS
    SELECT 
        t.task_id,
        t.custom_task_id,
        t.request_id,
        t.assigned_staff_id,
        t.scheduled_date,
        t.scheduled_time,
        t.status,
        COALESCE(staff_user.full_name, 'Unassigned') AS staff_name,
        customer_user.full_name AS customer_name,
        sr.property_address,
        s.service_name
    FROM tasks t
    LEFT JOIN service_requests sr ON t.request_id = sr.request_id
    LEFT JOIN users customer_user ON sr.customer_id = customer_user.user_id
    LEFT JOIN users staff_user ON t.assigned_staff_id = staff_user.user_id
    LEFT JOIN services s ON sr.service_id = s.service_id
    WHERE t.scheduled_date = CURDATE()
      AND t.status <> 'cancelled'
    ORDER BY t.scheduled_time ASC";
    
    $pdo->exec($createViewSQL);
    echo "✓ View created successfully\n\n";
    
    echo "===========================================\n";
    echo "✓ admin_today_tasks view updated!\n";
    echo "===========================================\n\n";
    echo "The view now includes custom_task_id column.\n";
    
} catch (Throwable $e) {
    echo "✗ Error: " . $e->getMessage() . "\n";
    echo "File: " . $e->getFile() . "\n";
    echo "Line: " . $e->getLine() . "\n";
    exit(1);
}
