<?php
include 'db.php';

echo "Checking and fixing budget table constraints...\n\n";

// Check table structure
try {
    $stmt = $pdo->query('SHOW CREATE TABLE budgets');
    $create_sql = $stmt->fetch()['Create Table'];
    echo "Current budgets table structure:\n";
    echo $create_sql . "\n\n";
} catch (Exception $e) {
    echo "Error getting table structure: " . $e->getMessage() . "\n\n";
}

// Check indexes and constraints
try {
    $stmt = $pdo->query('SHOW INDEX FROM budgets');
    echo "Current indexes on budgets table:\n";
    while ($row = $stmt->fetch()) {
        echo "  - {$row['Key_name']}: {$row['Column_name']} (unique: {$row['Non_unique']})\n";
    }
    echo "\n";
} catch (Exception $e) {
    echo "Error checking indexes: " . $e->getMessage() . "\n\n";
}

// Fix the unique constraint issue
echo "Fixing constraint issues...\n";

try {
    // Drop problematic unique constraint on group_id if it exists
    $pdo->exec("ALTER TABLE budgets DROP INDEX group_id");
    echo "✓ Dropped unique constraint on group_id\n";
} catch (Exception $e) {
    echo "  - Could not drop group_id index: " . $e->getMessage() . "\n";
}

try {
    // Add proper indexes that allow multiple budgets per group/user if needed
    $pdo->exec("ALTER TABLE budgets ADD INDEX idx_group_id (group_id)");
    echo "✓ Added non-unique index for group_id\n";
} catch (Exception $e) {
    echo "  - Could not add group_id index: " . $e->getMessage() . "\n";
}

try {
    $pdo->exec("ALTER TABLE budgets ADD INDEX idx_user_id (user_id)");
    echo "✓ Added non-unique index for user_id\n";
} catch (Exception $e) {
    echo "  - Could not add user_id index: " . $e->getMessage() . "\n";
}

try {
    $pdo->exec("ALTER TABLE budgets ADD INDEX idx_active (is_active)");
    echo "✓ Added index for is_active\n";
} catch (Exception $e) {
    echo "  - Could not add is_active index: " . $e->getMessage() . "\n";
}

// Test budget creation now
echo "\nTesting budget creation after fixes...\n";

try {
    // Try to create a user-specific budget
    $stmt = $pdo->prepare("
        INSERT INTO budgets (user_id, balance, total_spent, is_active, created_by) 
        VALUES (?, ?, ?, ?, ?)
    ");
    $stmt->execute([2, 100.00, 0.00, 1, 1]); // User 2
    echo "✓ Successfully created user budget for user #2\n";
} catch (Exception $e) {
    echo "✗ Failed to create user budget: " . $e->getMessage() . "\n";
}

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

echo "\nBudget constraint fixes completed!\n";
?>
