use test;
use sql;
use core:io;

DATABASE SimpleDB {
	TABLE test(
		id INTEGER PRIMARY KEY,
		name TEXT
	);

	// Auto name of the index.
	INDEX ON test(name);
}

test Simple {
	SQLite baseDb; // (cwdUrl / ".." / "test.db");
	SimpleDB c(baseDb);

	Int count = 0;
	for (row in WITH c: SELECT * FROM test)
		count++;

	check count == 0;

	Str test = "variable";
	WITH c: INSERT INTO test VALUES (3, "test's");
	WITH c: INSERT INTO test(id, name) VALUES (4, test);
	var insertedId = WITH c: INSERT INTO test(name) VALUES ("auto-id");

	check insertedId == 5;

	WITH c: UPDATE test SET name = name || " " || test WHERE id == 2 + 1;

	Nat numRows = WITH c: COUNT FROM test WHERE id > 3;
	check numRows == 2.nat;

	Bool b = false;
	var removed = WITH c: DELETE FROM test WHERE id == insertedId AND NOT b;

	check removed == 1.nat;

	StrBuf data;
	for (row in WITH c: SELECT * FROM test) {
		data << row.id << ", " << row.name << "\n";
	}

	check data.toS == "3, test's variable\n4, variable\n";
}

test BSEscape {
	SQLite db;
	SimpleDB c(db);

	WITH c {
		INSERT INTO test VALUES (3, ${"test" # 3});

		StrBuf data;
		for (row in SELECT name AS n FROM test)
			data << row.n << "|";

		check data.toS == "test3|";
	}
}

test Repeat {
	// Statements are cached, make sure that we can re-run them more than once.
	SQLite baseDB;
	SimpleDB c(baseDB);

	for (Int i = 0; i < 3; i++) {
		Str v = "Value: ${i}";
		WITH c: INSERT INTO test VALUES (i, v);
	}

	Int rows = 0;
	for (row in WITH c: SELECT * FROM test) {
		rows++;
	}

	check rows == 3;
}

test Like {
	SQLite db;
	SimpleDB c(db);

	WITH c {
		INSERT INTO test(name) VALUES ("test");
		INSERT INTO test(name) VALUES ("best");
		INSERT INTO test(name) VALUES ("beaver");

		StrBuf all;
		for (row in SELECT * FROM test WHERE name LIKE "_est") {
			all << row.name << "|";
		}

		for (row in SELECT * FROM test WHERE name LIKE "be%") {
			all << row.name << "!";
		}

		check all.toS == "test|best|best!beaver!";
	}
}

test ComplexCondition {
	SQLite db;
	SimpleDB c(db);

	WITH c {
		INSERT INTO test VALUES (1, "test");
		INSERT INTO test VALUES (2, "best");
		INSERT INTO test VALUES (3, "beaver");

		StrBuf all;
		for (row in SELECT * FROM test WHERE name LIKE "_est" AND id >= 2) {
			all << row.name << "|";
		}

		check all.toS == "best|";
	}
}


DATABASE TwoTables {
	TABLE test(
		id INTEGER PRIMARY KEY,
		name TEXT,
		city TEXT DEFAULT "none"
	);

	INDEX ON test(name, city);

	TABLE extra(
		id INTEGER PRIMARY KEY,
		test INTEGER,
		data TEXT
	);

	INDEX ON extra(test);
}

test AutoMigrate {
	SQLite baseDb;
	{
		SimpleDB c(baseDb);

		WITH c: INSERT INTO test VALUES (3, "test1");
	}

	{
		TwoTables c(baseDb);
		Int rows = 0;

		WITH c {
			INSERT INTO test VALUES (4, "test2", "city");
			INSERT INTO extra VALUES (1, 4, "more data");

			for (x in SELECT city FROM test WHERE id == 4) {
				rows++;
				check x.city == "city";
			}
		}

		// Outside the block to ensure it is actually executed.
		check rows == 1;
	}
}

test Join {
	SQLite db;
	TwoTables c(db);

	WITH c {
		INSERT INTO test VALUES (3, "a", "a");
		INSERT INTO test VALUES (4, "b", "b");

		INSERT INTO extra(test, data) VALUES (3, "more");
		INSERT INTO extra(test, data) VALUES (3, "even more");
		INSERT INTO extra(test, data) VALUES (4, "some more");

		StrBuf all;
		for (x in SELECT test.id AS id, data FROM test JOIN extra ON extra.test == test.id WHERE test.id == 3) {
			all << x.id << "|" << x.data << "|";
		}

		check all.toS == "3|more|3|even more|";

		all.clear();

		if (x = SELECT ONE test.id AS id FROM test JOIN extra ON extra.test == test.id WHERE test.id == 3) {
			all << x.id;
		}
		if (x = SELECT ONE test.id AS id FROM test WHERE test.id == 8) {
			all << x.id;
		}

		check all.toS == "3";

		all.clear();

		for (x in SELECT * FROM test JOIN extra ON extra.test == test.id WHERE test.id == 4) {
			all << x.test.id << "|" << x.extra.data << "|";
		}

		check all.toS == "4|some more|";
	}
}

test OrderBy {
	SQLite db;
	TwoTables c(db);

	WITH c {
		INSERT INTO test(id, name) VALUES (3, "c");
		INSERT INTO test(id, name) VALUES (1, "g");
		INSERT INTO test(id, name) VALUES (2, "g");

		StrBuf all;
		for (x in SELECT id, name FROM test ORDER BY name DESC, id ASC)
			all << x.id << x.name << "|";

		check all.toS == "1g|2g|3c|";

		all = StrBuf();
		for (x in SELECT id, name FROM test ORDER BY name ASC, id ASC)
			all << x.id << x.name << "|";

		check all.toS == "3c|1g|2g|";
	}
}

test TableAlias {
	SQLite db;
	TwoTables c(db);
	WITH c {
		INSERT INTO extra VALUES(1, 2, "a");
		INSERT INTO extra VALUES(2, 3, "b");
		INSERT INTO extra VALUES(3, 4, "c");

		StrBuf all;
		for (x in SELECT p.id AS a, q.id AS b, p.data AS c, q.data AS d FROM extra AS p JOIN extra AS q ON p.test == q.id) {
			all << x.a << x.b << x.c << x.d << "|";
		}

		check all.toS == "12ab|23bc|";
	}
}

DATABASE MultiPK {
	TABLE test(
		id1 INTEGER,
		id2 INTEGER,
		PRIMARY KEY(id1, id2)
	);
}

test MultiPKTest {
	SQLite db;
	MultiPK c(db);

	WITH c {
		INSERT INTO test VALUES (1, 2);
		INSERT INTO test VALUES (1, 3);

		try {
			INSERT INTO test VALUES (1, 2);
		} catch (SQLError e) {
			// This should happen!
		}

		StrBuf all;
		for (x in SELECT * FROM test)
			all << x.id1 << x.id2 << "|";

		check all.toS == "12|13|";
	}

	// Try re-creating the typed connection to test that we parse the declaration from SQLite properly.
	MultiPK d(db);
}


DATABASE NullDB {
	TABLE test(
		id INTEGER PRIMARY KEY,
		value1 TEXT ALLOW NULL,
		value2 TEXT,
		value3 TEXT DEFAULT "empty"
	);
}

test NullTest {
	SQLite db;
	NullDB c(db);

	WITH c {
		Str? val;

		INSERT INTO test(value2, value3) VALUES ("a", "i");
		INSERT INTO test(value1, value2) VALUES ("b", "c");

		INSERT INTO test(value1, value2) VALUES (val || "z", "d");
		val = "z";
		INSERT INTO test(value1, value2) VALUES (val || "z", "e");

		StrBuf data;
		for (row in SELECT * FROM test) {
			data << row.value1.toS << "|" << row.value2 << "|" << row.value3 << "!";
		}

		check data.toS == "null|a|i!b|c|empty!null|d|empty!zz|e|empty!";
	}
}