<?php

namespace Tests\Feature\Security\SQL;

use App\Models\User;
use App\Models\Post;
use App\Models\Organization;
use App\Models\Transaction;
use App\Models\Account;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Support\Facades\DB;
use Tests\TestCase;

/**
 * SQL Injection Prevention Tests
 *
 * Tests that the application properly sanitizes user input and uses
 * parameterized queries to prevent SQL injection attacks.
 *
 * @group security
 * @group sql-injection
 * @group critical
 */
class SQLInjectionPreventionTest extends TestCase
{
    use RefreshDatabase;

    /**
     * Test search functionality prevents SQL injection
     *
     * @test
     */
    public function search_prevents_sql_injection()
    {
        // Arrange: Create test data
        $user = User::factory()->create();
        Post::factory()->count(3)->create();

        // SQL injection payloads
        $maliciousQueries = [
            "' OR '1'='1",
            "'; DROP TABLE users--",
            "' UNION SELECT * FROM users--",
            "1' OR '1' = '1')) /*",
            "admin'--",
            "' OR 1=1--",
            "' OR 'x'='x",
            "1; DROP TABLE transactions--",
        ];

        $this->actingAs($user);

        // Act & Assert: Try each malicious query
        foreach ($maliciousQueries as $query) {
            // Test search endpoint (adjust route as needed)
            $response = $this->get(route('search', ['q' => $query]));

            // Should return 200 (search results, even if empty)
            // Should NOT execute SQL injection
            $response->assertStatus(200);

            // Verify tables still exist
            $this->assertTrue(
                DB::getSchemaBuilder()->hasTable('users'),
                "SQL injection attempt deleted users table: {$query}"
            );
            $this->assertTrue(
                DB::getSchemaBuilder()->hasTable('transactions'),
                "SQL injection attempt deleted transactions table: {$query}"
            );
        }
    }

    /**
     * Test profile name input prevents SQL injection
     *
     * @test
     */
    public function profile_name_input_prevents_sql_injection()
    {
        // Arrange: Create user
        $user = User::factory()->create();

        // SQL injection payloads
        $maliciousNames = [
            "admin'; DROP TABLE users--",
            "test' OR '1'='1",
            "'; DELETE FROM transactions WHERE '1'='1",
        ];

        $this->actingAs($user);

        // Act & Assert: Try to update profile with malicious names
        foreach ($maliciousNames as $name) {
            // Note: This should be rejected by validation (alphanumeric rule)
            // But even if validation is bypassed, SQL should be safe

            $response = $this->put(route('user-profile-information.update'), [
                'name' => $name,
                'email' => $user->email,
            ]);

            // Should either be rejected by validation or safely stored
            // Tables should still exist
            $this->assertTrue(
                DB::getSchemaBuilder()->hasTable('users'),
                "SQL injection via name field succeeded: {$name}"
            );
        }
    }

    /**
     * Test transaction queries use parameterized statements
     *
     * @test
     */
    public function transaction_queries_use_parameterized_statements()
    {
        // Arrange: Create accounts and transaction
        $user = User::factory()->create();
        $fromAccount = Account::factory()->create([
            'accountable_type' => User::class,
            'accountable_id' => $user->id,
        ]);
        $toAccount = Account::factory()->create([
            'accountable_type' => User::class,
            'accountable_id' => $user->id,
        ]);

        Transaction::factory()->create([
            'from_account_id' => $fromAccount->id,
            'to_account_id' => $toAccount->id,
            'amount' => 100,
        ]);

        // SQL injection attempt in description
        $maliciousDescriptions = [
            "Payment'; DROP TABLE transactions--",
            "' OR '1'='1",
            "Test' UNION SELECT * FROM users--",
        ];

        $this->actingAs($user);

        // Act: Create transactions with malicious descriptions
        foreach ($maliciousDescriptions as $description) {
            try {
                $transaction = Transaction::create([
                    'from_account_id' => $fromAccount->id,
                    'to_account_id' => $toAccount->id,
                    'amount' => 50,
                    'description' => $description,
                    'transaction_type_id' => 1,
                ]);

                // Description should be stored as-is (string), not executed
                $this->assertEquals($description, $transaction->description);

            } catch (\Exception $e) {
                // If validation rejects it, that's also acceptable
                $this->assertTrue(true);
            }

            // Assert: Table still exists
            $this->assertTrue(
                DB::getSchemaBuilder()->hasTable('transactions'),
                "SQL injection via transaction description succeeded"
            );
        }
    }

    /**
     * Test WHERE clause uses parameter binding
     *
     * @test
     */
    public function where_clauses_use_parameter_binding()
    {
        // Arrange: Create users
        $user1 = User::factory()->create(['name' => 'testuser1']);
        $user2 = User::factory()->create(['name' => 'testuser2']);

        // SQL injection attempt in filter
        $maliciousFilter = "testuser1' OR '1'='1";

        // Act: Query with malicious filter
        // This simulates a search or filter operation
        $result = User::where('name', $maliciousFilter)->get();

        // Assert: Should return empty (no user with that exact name)
        // NOT all users (which would happen if SQL injection succeeded)
        $this->assertCount(0, $result, "SQL injection in WHERE clause succeeded");

        // Verify both users still exist
        $this->assertCount(2, User::all());
    }

    /**
     * Test ORDER BY clause prevents injection
     *
     * @test
     */
    public function order_by_clause_prevents_injection()
    {
        // Arrange: Create posts
        $user = User::factory()->create();
        Post::factory()->count(5)->create();

        $this->actingAs($user);

        // Malicious ORDER BY attempts
        $maliciousOrders = [
            "created_at; DROP TABLE posts--",
            "id' OR '1'='1--",
            "(SELECT * FROM users)",
        ];

        // Act & Assert: Try malicious order parameters
        foreach ($maliciousOrders as $order) {
            try {
                // Attempt to order by malicious input
                // In real application, this would be through a query parameter
                $response = $this->get(route('posts.index', ['sort' => $order]));

                // Should either safely handle or reject
                // Tables should still exist
                $this->assertTrue(
                    DB::getSchemaBuilder()->hasTable('posts'),
                    "SQL injection via ORDER BY succeeded: {$order}"
                );

            } catch (\Exception $e) {
                // Exception is acceptable if validation rejects it
                $this->assertTrue(true);
            }
        }
    }

    /**
     * Test raw queries are properly escaped (if any exist)
     *
     * @test
     */
    public function raw_queries_use_parameter_binding()
    {
        // This test verifies that any raw SQL queries in the codebase
        // use parameter binding instead of string concatenation

        // Arrange: Create test data
        $user = User::factory()->create();

        // Example of UNSAFE query (should NOT exist in codebase):
        // DB::select("SELECT * FROM users WHERE name = '" . $input . "'");

        // Example of SAFE query (should be used):
        // DB::select("SELECT * FROM users WHERE name = ?", [$input]);

        $maliciousInput = "admin' OR '1'='1--";

        // Act: Try query with malicious input
        $result = DB::select("SELECT * FROM users WHERE name = ?", [$maliciousInput]);

        // Assert: Should return empty (no user with that name)
        $this->assertEmpty($result, "Raw query parameter binding failed");

        // All users should still exist
        $this->assertGreaterThan(0, User::count());
    }

    /**
     * Test LIKE queries prevent injection
     *
     * @test
     */
    public function like_queries_prevent_injection()
    {
        // Arrange: Create users
        User::factory()->create(['name' => 'testuser']);
        User::factory()->create(['name' => 'admin']);

        // Malicious LIKE pattern
        $maliciousPattern = "%' OR '1'='1";

        // Act: Search with malicious pattern
        $result = User::where('name', 'LIKE', $maliciousPattern)->get();

        // Assert: Should return empty or safe results
        // Should NOT return all users
        $this->assertLessThan(2, $result->count(), "LIKE injection succeeded");
    }

    /**
     * Test JSON input prevents injection
     *
     * @test
     */
    public function json_input_prevents_injection()
    {
        // Arrange: Create user
        $user = User::factory()->create();

        $this->actingAs($user);

        // Malicious JSON payload
        $maliciousData = [
            'name' => "test'; DROP TABLE users--",
            'email' => "test@example.com",
            'extra' => "' OR '1'='1",
        ];

        // Act: Submit malicious JSON data
        $response = $this->putJson(route('user-profile-information.update'), $maliciousData);

        // Assert: Data should be safely handled
        $this->assertTrue(
            DB::getSchemaBuilder()->hasTable('users'),
            "SQL injection via JSON input succeeded"
        );
    }

    /**
     * Test bulk operations prevent injection
     *
     * @test
     */
    public function bulk_operations_prevent_injection()
    {
        // Arrange: Create users
        $user1 = User::factory()->create();
        $user2 = User::factory()->create();

        // Malicious IDs array
        $maliciousIds = [
            $user1->id,
            "1' OR '1'='1--",
            $user2->id,
        ];

        // Act: Try bulk query with malicious IDs
        try {
            $result = User::whereIn('id', $maliciousIds)->get();

            // Assert: Should only return valid IDs, not all users
            $this->assertLessThanOrEqual(2, $result->count(), "Bulk operation injection succeeded");

        } catch (\Exception $e) {
            // Exception is acceptable
            $this->assertTrue(true);
        }

        // All users should still exist
        $this->assertCount(2, User::all());
    }
}