setup.php

<?php
// Database setup script for To-Do List App

echo "=== To-Do List App Database Setup ===\n\n";

// Database configuration
$dbname = 'todo_db';
$password = ''; // Try empty password first

// Function to execute MySQL commands
function executeMySQLCommand($command, $password = '') {
    $tempFile = tempnam(sys_get_temp_dir(), 'sql_');
    file_put_contents($tempFile, $command . ";\n");
    
    $passwordParam = $password ? "-p$password" : '';
    $mysqlPath = 'C:\\laragon\\bin\\mysql\\mysql-8.4.3-winx64\\bin\\mysql';
    
    // Execute the SQL from the temporary file
    $command = "\"$mysqlPath\" -u root $passwordParam < " . escapeshellarg($tempFile) . " 2>&1";
    exec($command, $output, $return_var);
    
    // Clean up the temporary file
    unlink($tempFile);
    
    return [
        'success' => $return_var === 0,
        'output' => implode("\n", $output)
    ];
}

// Try to connect to MySQL
$result = executeMySQLCommand('SELECT 1', $password);

// If connection failed with empty password, try common passwords
if (!$result['success']) {
    $common_passwords = ['root', 'password', 'admin'];
    
    foreach ($common_passwords as $pwd) {
        $result = executeMySQLCommand('SELECT 1', $pwd);
        if ($result['success']) {
            $password = $pwd;
            break;
        }
    }
    
    if (!$result['success']) {
        echo "✗ Failed to connect to MySQL. Please check your MySQL installation and credentials.\n";
        echo "Error: " . $result['output'] . "\n\n";
        echo "Make sure MySQL server is running and accessible.\n";
        echo "Common issues to check:\n";
        echo "1. MySQL service is not running\n";
        echo "2. Incorrect MySQL root password\n";
        echo "3. MySQL user 'root' doesn't have proper permissions\n\n";
        exit(1);
    }
}

echo "✓ Connected to MySQL server\n";

// Create database if it doesn't exist
$result = executeMySQLCommand("CREATE DATABASE IF NOT EXISTS $dbname", $password);
if ($result['success']) {
    echo "✓ Database '$dbname' created/verified\n";
} else {
    echo "✗ Failed to create database: " . $result['output'] . "\n";
    exit(1);
}

// Create a simple test table with the database specified directly
$testSql = "CREATE TABLE $dbname.test_table (id INT)";
echo "Creating test table...\n";
$result = executeMySQLCommand($testSql, $password);
if ($result['success']) {
    echo "✓ Test table created successfully\n";
    // Drop the test table
    executeMySQLCommand("DROP TABLE IF EXISTS $dbname.test_table", $password);
} else {
    echo "✗ Failed to create test table: " . $result['output'] . "\n";
    exit(1);
}

echo "Creating tasks table...\n";
// Create tasks table if it doesn't exist
$createTableSql = "
    CREATE TABLE IF NOT EXISTS $dbname.tasks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        completed TINYINT(1) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";

$result = executeMySQLCommand($createTableSql, $password);
if ($result['success']) {
    echo "✓ 'tasks' table created/verified\n";
} else {
    // If table already exists, that's fine
    if (strpos($result['output'], 'already exists') !== false) {
        echo "✓ 'tasks' table already exists\n";
    } else {
        echo "✗ Failed to create table: " . $result['output'] . "\n";
        exit(1);
    }
}

// Clear any existing data to avoid duplicates
$clearSql = "TRUNCATE TABLE $dbname.tasks";
$result = executeMySQLCommand($clearSql, $password);
if ($result['success']) {
    echo "✓ Cleared existing tasks\n";
} else {
    echo "⚠️ Warning: Could not clear existing tasks: " . $result['output'] . "\n";
}

// Insert sample data
$sampleTasks = [
    ['title' => 'Welcome to To-Do List', 'description' => 'This is your first task!', 'completed' => 0],
    ['title' => 'Mark as complete', 'description' => 'Click the checkbox to mark a task as complete', 'completed' => 0],
    ['title' => 'Delete tasks', 'description' => 'Click the trash icon to delete a task', 'completed' => 0]
];

foreach ($sampleTasks as $task) {
    $title = addslashes($task['title']);
    $description = addslashes($task['description']);
    $completed = (int)$task['completed'];
    
    // Use the database name in the table reference
    $sql = "INSERT INTO $dbname.tasks (title, description, completed) 
            VALUES ('$title', '$description', $completed)";
    
    // Execute the query directly without IGNORE to see any errors
    $result = executeMySQLCommand($sql, $password);
    if ($result['success']) {
        echo "✓ Added task: $title\n";
    } else {
        echo "⚠️ Warning: Failed to insert sample data: " . $result['output'] . "\n";
        echo "SQL: $sql\n";
    }
}

echo "✓ Sample data inserted\n";

// Test API endpoints
echo "\n=== Testing API Endpoints ===\n";

// Build the base URL
$testUrl = 'http://localhost/to-do-list/backend/api/get_tasks.php';
echo "Testing URL: $testUrl\n";

// Use cURL for more reliable HTTP requests
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $testUrl);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HEADER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, [
    'Accept: application/json',
    'Content-Type: application/json'
]);

$response = curl_exec($ch);
$error = curl_error($ch);
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
$headerSize = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
$body = substr($response, $headerSize);

curl_close($ch);

echo "HTTP Status: $httpCode\n";

if ($error) {
    echo "cURL Error: $error\n";
    echo "✗ GET /get_tasks.php - Failed to connect\n";
} else {
    $data = json_decode($body, true);
    if (json_last_error() === JSON_ERROR_NONE) {
        if (isset($data['tasks'])) {
            echo "✓ GET /get_tasks.php - Working (" . count($data['tasks']) . " tasks found)\n";
        } else {
            echo "✗ GET /get_tasks.php - Invalid response format\n";
            echo "Response: " . htmlspecialchars($body) . "\n";
        }
    } else {
        echo "✗ GET /get_tasks.php - Invalid JSON response\n";
        echo "Response: " . htmlspecialchars($body) . "\n";
    }
}

echo "\n=== Setup Complete ===\n";
echo "Your To-Do List app is ready to use!\n";
echo "Frontend: Run 'flutter run' in the frontend directory\n";
echo "Backend: API endpoints are available at http://localhost/to-do%20list/backend/api/\n";

exit(0);
?>