<?php

use PHPUnit\Framework\TestCase;
use Fury\Modules\ThinBuilder\ThinBuilder;

class ThinBuilderTest extends TestCase {
	private ?ThinBuilder $tb = null;

	protected function setUp(): void {
		$this -> tb = new ThinBuilder([
			'dblib' => 'sqlite',
			'dbname' => ':memory:',
		], null, false);

		$this -> create_test_table();
	}

	protected function tearDown(): void {
		$this -> tb = null;
	}

	private function create_test_table(): void {
		$sql = "CREATE TABLE test_table (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			alias VARCHAR(255),
			value VARCHAR(255),
			status VARCHAR(50)
		)";
		$this -> tb -> query($sql);
	}

	public function test_insert_and_select(): void {
		$id = $this -> tb -> insert('test_table', [
			'alias' => 'foo',
			'value' => 'bar',
			'status' => 'active',
		]);

		$this -> assertGreaterThan(0, $id);

		$rows = $this -> tb -> select('test_table', ['id', 'alias', 'value', 'status'], [
			['alias', '=', 'foo']
		]);

		$this -> assertCount(1, $rows);
		$this -> assertSame('foo', $rows[0]['alias']);
		$this -> assertSame('bar', $rows[0]['value']);
	}

	public function test_select_with_flat_where_syntax(): void {
		$this -> tb -> insert('test_table', ['alias' => 'a1', 'value' => 'v1', 'status' => 'ok']);
		$this -> tb -> insert('test_table', ['alias' => 'a2', 'value' => 'v2', 'status' => 'fail']);

		$rows = $this -> tb -> select('test_table', ['id', 'alias', 'value', 'status'], ['alias', '=', 'a1']);
		$this -> assertCount(1, $rows);
		$this -> assertSame('a1', $rows[0]['alias']);
	}

	public function test_select_multiple_conditions(): void {
		$this -> tb -> insert('test_table', ['alias' => 'x', 'value' => 'y', 'status' => 'active']);
		$this -> tb -> insert('test_table', ['alias' => 'x', 'value' => 'z', 'status' => 'removed']);

		$rows = $this -> tb -> select('test_table', ['id', 'alias', 'value', 'status'], [
			['alias', '=', 'x'],
			'AND',
			['status', '=', 'active']
		]);

		$this -> assertCount(1, $rows);
		$this -> assertSame('y', $rows[0]['value']);
	}

	public function test_update(): void {
		$id = $this -> tb -> insert('test_table', ['alias' => 'old', 'value' => 'val']);

		$updated = $this -> tb -> update('test_table', ['alias' => 'new'], [
			['id', '=', $id]
		]);

		$this -> assertSame(1, $updated);

		$rows = $this -> tb -> select('test_table', ['alias'], [['id', '=', $id]]);
		$this -> assertSame('new', $rows[0]['alias']);
	}

	public function test_delete(): void {
		$id = $this -> tb -> insert('test_table', ['alias' => 'delme', 'value' => 'v']);

		$deleted = $this -> tb -> delete('test_table', [['id', '=', $id]]);
		$this -> assertSame(1, $deleted);

		$rows = $this -> tb -> select('test_table', ['id', 'alias', 'value', 'status'], [['id', '=', $id]]);
		$this -> assertCount(0, $rows);
	}

	public function test_sql_injection_blocked_in_identifier(): void {
		$this -> expectException(\Exception::class);
		$this -> tb -> select('test`; DROP TABLE users; --', ['id']);
	}

	public function test_sql_injection_in_value_is_escaped_by_placeholder(): void {
		$this -> tb -> insert('test_table', [
			'alias' => "foo'; DROP TABLE test_table; --",
			'value' => 'safe',
		]);

		$rows = $this -> tb -> select('test_table', ['id', 'alias', 'value', 'status'], [
			['alias', '=', "foo'; DROP TABLE test_table; --"]
		]);

		$this -> assertCount(1, $rows);
		$this -> assertSame("foo'; DROP TABLE test_table; --", $rows[0]['alias']);

		$stmt = $this -> tb -> query("SELECT COUNT(*) as c FROM test_table");
		$check = $stmt -> fetch(\PDO::FETCH_ASSOC);
		$this -> assertGreaterThanOrEqual(1, $check['c']);
	}

	public function test_transaction_commit(): void {
		$this -> tb -> beginTransaction();
		$this -> tb -> insert('test_table', ['alias' => 'tx', 'value' => 'v']);
		$this -> tb -> commit();

		$rows = $this -> tb -> select('test_table', ['id', 'alias', 'value', 'status'], [['alias', '=', 'tx']]);
		$this -> assertCount(1, $rows);
	}

	public function test_transaction_rollback(): void {
		$this -> tb -> beginTransaction();
		$this -> tb -> insert('test_table', ['alias' => 'tx2', 'value' => 'v']);
		$this -> tb -> rollBack();

		$rows = $this -> tb -> select('test_table', ['id', 'alias', 'value', 'status'], [['alias', '=', 'tx2']]);
		$this -> assertCount(0, $rows);
	}

	public function test_sql_generation_mode(): void {
		$sql = $this -> tb -> sql_for() -> select('test_table', ['id', 'alias'], [['status', '=', 'active']], ['id'], 'ASC', [10]);
		$this -> assertStringContainsString('SELECT', $sql);
		$this -> assertStringContainsString('FROM `test_table`', $sql);
	}

	public function test_where_operator_whitelist_rejects_invalid(): void {
		$this -> expectException(\Exception::class);
		$this -> expectExceptionMessage('Invalid WHERE operator');
		$this -> tb -> select('test_table', ['id'], [['alias', 'OR 1=1 --', 'foo']]);
	}

	public function test_where_in_requires_array(): void {
		$this -> expectException(\Exception::class);
		$this -> expectExceptionMessage('IN operator requires array value');
		$this -> tb -> select('test_table', ['id'], [['alias', 'IN', 'not_an_array']]);
	}

	public function test_short_where_syntax_with_in(): void {
		$this -> tb -> insert('test_table', ['alias' => 'in1', 'value' => 'v1']);
		$this -> tb -> insert('test_table', ['alias' => 'in2', 'value' => 'v2']);
		$this -> tb -> insert('test_table', ['alias' => 'in3', 'value' => 'v3']);

		$rows = $this -> tb -> select('test_table', ['id', 'alias'], ['alias', ['in1', 'in2']]);
		$this -> assertCount(2, $rows);
		$aliases = array_column($rows, 'alias');
		$this -> assertContains('in1', $aliases);
		$this -> assertContains('in2', $aliases);
	}

	public function test_identifier_rejects_numeric_start(): void {
		$this -> expectException(\Exception::class);
		$this -> expectExceptionMessage('Invalid SQL identifier');
		$this -> tb -> select('test_table', ['id'], [['123abc', '=', 'foo']]);
	}
}