<?php
declare(strict_types=1);

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

echo "Starting database migration...\n\n";

try {
    $pdo = Database::pdo();
    
    // Step 1: Add custom_task_id column
    echo "Step 1: Adding custom_task_id column to tasks table...\n";
    $pdo->exec("ALTER TABLE tasks ADD COLUMN custom_task_id VARCHAR(20) NULL AFTER task_id");
    echo "✓ Column added successfully\n\n";
    
    // Step 2: Add index
    echo "Step 2: Adding index on custom_task_id...\n";
    $pdo->exec("ALTER TABLE tasks ADD INDEX idx_custom_task_id (custom_task_id)");
    echo "✓ Index added successfully\n\n";
    
    // Step 3: Update existing tasks with custom IDs
    echo "Step 3: Updating existing tasks with custom task IDs...\n";
    $existingTasks = $pdo->query("SELECT task_id, scheduled_date FROM tasks ORDER BY scheduled_date ASC, task_id ASC")->fetchAll();
    
    $monthCounters = [];
    foreach ($existingTasks as $task) {
        $taskId = (int)$task['task_id'];
        $scheduledDate = (string)$task['scheduled_date'];
        $yearMonth = date('Y-m', strtotime($scheduledDate));
        
        if (!isset($monthCounters[$yearMonth])) {
            $monthCounters[$yearMonth] = 0;
        }
        $monthCounters[$yearMonth]++;
        
        $customTaskId = $yearMonth . '-' . $monthCounters[$yearMonth];
        
        $stmt = $pdo->prepare("UPDATE tasks SET custom_task_id = :custom_id WHERE task_id = :tid");
        $stmt->execute(['custom_id' => $customTaskId, 'tid' => $taskId]);
    }
    echo "✓ Updated " . count($existingTasks) . " existing tasks\n\n";
    
    // Step 4: Update admin_all_tasks view
    echo "Step 4: Updating admin_all_tasks view...\n";
    $pdo->exec("DROP VIEW IF EXISTS admin_all_tasks");
    
    $createViewSQL = "CREATE VIEW admin_all_tasks AS
    SELECT 
        t.task_id,
        t.custom_task_id,
        t.request_id,
        t.assigned_staff_id,
        t.assigned_by_admin_id,
        t.scheduled_date,
        t.scheduled_time,
        t.status AS task_status,
        t.created_at AS task_created_at,
        t.updated_at AS task_updated_at,
        COALESCE(staff_user.full_name, 'Unassigned') AS staff_name,
        COALESCE(staff_user.email, '') AS staff_email,
        customer_user.full_name AS customer_name,
        customer_user.email AS customer_email,
        sr.customer_id,
        sr.service_id,
        sr.property_address,
        sr.pest_type,
        sr.severity_level,
        sr.preferred_date,
        sr.preferred_time,
        sr.status AS request_status,
        s.service_name,
        s.description AS service_description,
        p.payment_id,
        p.payment_status,
        p.payment_method,
        p.due_date AS payment_due_date
    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
    LEFT JOIN payments p ON sr.request_id = p.request_id";
    
    $pdo->exec($createViewSQL);
    echo "✓ View updated successfully\n\n";
    
    echo "===========================================\n";
    echo "✓ Migration completed successfully!\n";
    echo "===========================================\n\n";
    echo "You can now use the month-based task numbering system.\n";
    echo "Task IDs will be in format: YYYY-MM-N (e.g., 2026-03-1)\n\n";
    
} catch (Throwable $e) {
    echo "✗ Error: " . $e->getMessage() . "\n";
    echo "File: " . $e->getFile() . "\n";
    echo "Line: " . $e->getLine() . "\n";
    exit(1);
}
