<?php
include 'db.php';

echo "Checking users and fixing user foreign key constraint...\n\n";

// Check what users exist
try {
    $stmt = $pdo->query("SELECT id, username, role FROM users ORDER BY id LIMIT 10");
    echo "Existing users:\n";
    while ($row = $stmt->fetch()) {
        echo "  - User #{$row['id']}: {$row['username']} ({$row['role']})\n";
    }
    echo "\n";
} catch (Exception $e) {
    echo "Error checking users: " . $e->getMessage() . "\n\n";
}

// Fix the user foreign key constraint to be more flexible
echo "Making user foreign key constraint more flexible...\n";

try {
    // Drop the existing user foreign key constraint
    $pdo->exec("ALTER TABLE budgets DROP FOREIGN KEY budgets_user_fk");
    echo "✓ Dropped strict user foreign key constraint\n";
} catch (Exception $e) {
    echo "  - Could not drop user constraint: " . $e->getMessage() . "\n";
}

try {
    // Add a more flexible constraint
    $pdo->exec("
        ALTER TABLE budgets 
        ADD CONSTRAINT budgets_user_fk 
        FOREIGN KEY (user_id) REFERENCES users(id) 
        ON DELETE SET NULL ON UPDATE CASCADE
    ");
    echo "✓ Added flexible user foreign key constraint\n";
} catch (Exception $e) {
    echo "  - Could not add flexible user constraint: " . $e->getMessage() . "\n";
}

// Test budget creation with actual existing users
echo "\nTesting budget creation with existing users...\n";

try {
    // Get first existing user
    $user_stmt = $pdo->query("SELECT id FROM users ORDER BY id LIMIT 1");
    $user_id = $user_stmt->fetchColumn();
    
    if ($user_id) {
        $stmt = $pdo->prepare("
            INSERT INTO budgets (user_id, balance, total_spent, is_active, created_by) 
            VALUES (?, ?, ?, ?, ?)
        ");
        $stmt->execute([$user_id, 150.00, 0.00, 1, $user_id]);
        echo "✓ Successfully created user budget for user #$user_id\n";
    } else {
        echo "✗ No users found in database\n";
    }
} catch (Exception $e) {
    echo "✗ Failed to create user budget: " . $e->getMessage() . "\n";
}

// Also test creating a budget without foreign key dependencies
echo "\nTesting budget creation without foreign key dependencies...\n";

try {
    // Create a budget with just group_id (which should work now)
    $stmt = $pdo->prepare("
        INSERT INTO budgets (group_id, balance, total_spent, is_active) 
        VALUES (?, ?, ?, ?)
    ");
    $stmt->execute([3, 300.00, 0.00, 1]); // Group 3
    echo "✓ Successfully created group budget for group #3\n";
} catch (Exception $e) {
    echo "✗ Failed to create group budget: " . $e->getMessage() . "\n";
}

echo "\nAll constraint fixes completed!\n";

// Show final budget count
try {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM budgets");
    $count = $stmt->fetch()['count'];
    echo "✓ Total budgets in database: $count\n";
} catch (Exception $e) {
    echo "✗ Error counting budgets: " . $e->getMessage() . "\n";
}
?>
