<?php
include 'db.php';

echo "Checking foreign key constraints and fixing budget creation issues...\n\n";

// Check current foreign key constraints
try {
    $stmt = $pdo->query("
        SELECT 
            CONSTRAINT_NAME, 
            TABLE_NAME, 
            COLUMN_NAME, 
            REFERENCED_TABLE_NAME, 
            REFERENCED_COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE 
        WHERE REFERENCED_TABLE_SCHEMA = 'freestyle_db' 
        AND TABLE_NAME = 'budgets'
    ");
    
    echo "Current foreign key constraints on budgets table:\n";
    while ($row = $stmt->fetch()) {
        echo "  - {$row['CONSTRAINT_NAME']}: {$row['COLUMN_NAME']} -> {$row['REFERENCED_TABLE_NAME']}.{$row['REFERENCED_COLUMN_NAME']}\n";
    }
    echo "\n";
} catch (Exception $e) {
    echo "Error checking constraints: " . $e->getMessage() . "\n\n";
}

// Check what groups exist
try {
    $stmt = $pdo->query("SELECT id, name FROM user_groups ORDER BY id");
    echo "Existing user groups:\n";
    while ($row = $stmt->fetch()) {
        echo "  - Group #{$row['id']}: {$row['name']}\n";
    }
    echo "\n";
} catch (Exception $e) {
    echo "Error checking groups: " . $e->getMessage() . "\n\n";
}

// Check what budgets exist and their group references
try {
    $stmt = $pdo->query("SELECT id, group_id, user_id, balance FROM budgets ORDER BY id");
    echo "Existing budgets:\n";
    while ($row = $stmt->fetch()) {
        $type = $row['group_id'] ? "Group #{$row['group_id']}" : "User #{$row['user_id']}";
        echo "  - Budget #{$row['id']}: {$row['balance']} tokens ($type)\n";
    }
    echo "\n";
} catch (Exception $e) {
    echo "Error checking budgets: " . $e->getMessage() . "\n\n";
}

// Try to fix the issue by making foreign key constraints more flexible
echo "Attempting to fix foreign key constraints...\n";

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

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

try {
    // Add constraint for user_id if users table exists
    $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 foreign key constraint for user_id\n";
} catch (Exception $e) {
    echo "  - Could not add user constraint: " . $e->getMessage() . "\n";
}

echo "\nTesting budget creation...\n";

// Test creating a budget for the admin group
try {
    $stmt = $pdo->prepare("
        INSERT INTO budgets (group_id, balance, total_spent, is_active, created_by) 
        VALUES (?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
        balance = GREATEST(balance, VALUES(balance))
    ");
    $stmt->execute([1, 500.00, 0.00, 1, 1]);
    echo "✓ Successfully created/updated admin group budget\n";
} catch (Exception $e) {
    echo "✗ Failed to create admin group budget: " . $e->getMessage() . "\n";
}

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

echo "\nFinal verification:\n";
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";
}

echo "\nForeign key constraint fix completed!\n";
?>
