/*
 * Copyright 2006 Takahiro Nakamura.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied. See the License for the specific language
 * governing permissions and limitations under the License.
 */
package woolpack.sql;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Pattern;

import junit.framework.TestCase;
import woolpack.convert.ConvertUtils;
import woolpack.sql.fn.PreparedStatementInfo;
import woolpack.sql.meta.ColumnInfo;
import woolpack.sql.meta.ReferenceInfo;
import woolpack.sql.meta.SqlMetaUtils;
import woolpack.sql.meta.TableInfo;
import woolpack.utils.Utils;

public class SqlMetaUtilsTest extends TestCase {
	
	private static TableInfo getTableInfo() {
		return new TableInfo(
				null, null, "my_table",
				Arrays.asList("my_id", "my_seq", "my_string", "my_int"),
				Arrays.asList("my_id", "my_seq"),
				Utils
				.map("my_id", new ColumnInfo(0, "INTEGER", 0, 0, false))
				.map("my_seq", new ColumnInfo(0, "INTEGER", 0, 0, false))
				.map("my_string", new ColumnInfo(0, "VARCHAR", 0, 0, false))
				.map("my_int", new ColumnInfo(0, "INTEGER", 0, 0, false)),
				null,
				null);
	}

	public void testInsertFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.INSERT_FACTORY.exec(getTableInfo());
		assertEquals(
				"INSERT INTO my_table(my_id, my_seq, my_string, my_int) VALUES (?, ?, ?, ?)",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "my_seq", "my_string", "my_int"),
				psInfo.getList());
	}

	public void testSelectFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.SELECT_FACTORY.exec(getTableInfo());
		assertEquals(
				"SELECT my_id, my_seq, my_string, my_int FROM my_table" +
				" WHERE my_id = ? AND my_seq = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "my_seq"),
				psInfo.getList());
	}

	public void testUpdateFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.UPDATE_FACTORY.exec(getTableInfo());
		assertEquals(
				"UPDATE my_table SET my_string = ?, my_int = ?  WHERE my_id = ? AND my_seq = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_string", "my_int", "my_id", "my_seq"),
				psInfo.getList());
	}

	public void testDeleteFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.DELETE_FACTORY.exec(getTableInfo());
		assertEquals(
				"DELETE FROM my_table WHERE my_id = ? AND my_seq = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "my_seq"),
				psInfo.getList());
	}

	public void testAllCheckingDeleteFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.ALL_CHECKING_DELETE_FACTORY.exec(getTableInfo());
		assertEquals(
				"DELETE FROM my_table WHERE my_id = ? AND my_seq = ? AND my_string = ? AND my_int = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "my_seq", "my_string", "my_int"),
				psInfo.getList());
	}

	public void testInsertFactoryFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.INSERT_FACTORY_FACTORY.exec(getTableInfo())
		.exec(Arrays.asList("my_id", "my_seq", "my_int"));
		assertEquals(
				"INSERT INTO my_table(my_id, my_seq, my_int) VALUES (?, ?, ?)",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "my_seq", "my_int"),
				psInfo.getList());
	}

	public void testSelectFactoryFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.SELECT_FACTORY_FACTORY.exec(getTableInfo())
		.exec(Arrays.asList("my_id", "my_int"));
		assertEquals(
				"SELECT my_id, my_seq, my_string, my_int FROM my_table" +
				" WHERE my_id = ? AND my_int = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "my_int"),
				psInfo.getList());
	}

	public void testJoinSelectFactoryFactory() {
		final List<TableInfo> tableInfoList = Arrays.asList(
				new TableInfo(
						null, null, "my_table",
						Arrays.asList("my_id", "my_seq", "my_string", "my_int"),
						Arrays.asList("my_id", "my_seq"),
						Utils
						.map("my_id", new ColumnInfo(0, "INTEGER", 0, 0, false))
						.map("my_seq", new ColumnInfo(0, "INTEGER", 0, 0, false))
						.map("my_string", new ColumnInfo(0, "VARCHAR", 0, 0, false))
						.map("my_int", new ColumnInfo(0, "INTEGER", 0, 0, false)),
						Arrays.asList(new ReferenceInfo(
								null, null, "your_table", null, Arrays.asList("your_id"),
								null, null, "my_table", null, Arrays.asList("my_int"))),
						new ArrayList<ReferenceInfo>()
				),
				new TableInfo(
						null, null, "your_table",
						Arrays.asList("your_id", "your_string"),
						Arrays.asList("your_id"),
						Utils
						.map("your_id", new ColumnInfo(0, "INTEGER", 0, 0, false))
						.map("your_string", new ColumnInfo(0, "VARCHAR", 0, 0, false)),
						new ArrayList<ReferenceInfo>(),
						Arrays.asList(new ReferenceInfo(
								null, null, "your_table", null, Arrays.asList("your_id"),
								null, null, "my_table", null, Arrays.asList("my_int")))
				));
		
		final PreparedStatementInfo psInfo = SqlMetaUtils.JOIN_SELECT_FACTORY_FACTORY
		.exec(tableInfoList)
		.exec(Arrays.asList("my_id", "your_id"));
		assertEquals(
				"SELECT my_table.my_id, my_table.my_seq, my_table.my_string, my_table.my_int," +
				" your_table.your_id, your_table.your_string" +
				" FROM my_table, your_table" +
				"  WHERE my_table.my_id = ? AND your_table.your_id = my_table.my_int AND your_table.your_id = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "your_id"),
				psInfo.getList());
	}

	public void testUpdateFactoryFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.UPDATE_FACTORY_FACTORY.exec(getTableInfo())
		.exec(Arrays.asList("my_id", "my_int"));
		assertEquals(
				"UPDATE my_table SET my_int = ?  WHERE my_id = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_int", "my_id"),
				psInfo.getList());
	}

	public void testAllCheckingUpdateFactoryFactory() {
		final PreparedStatementInfo psInfo = SqlMetaUtils.getAllCheckingUpdateFactoryFactory(
				ConvertUtils.convertRegExp(Pattern.compile("(.+)"), "before_$1"))
				.exec(getTableInfo())
		.exec(Arrays.asList("my_id", "my_int"));
		assertEquals(
				"UPDATE my_table SET my_id = ?, my_int = ?  WHERE my_id = ? AND my_seq = ? AND my_string = ? AND my_int = ?",
				psInfo.getQuery());
		assertEquals(
				Arrays.asList("my_id", "my_int", "before_my_id", "before_my_seq", "before_my_string", "before_my_int"),
				psInfo.getList());
	}

	public void testGenerateCreateTableQuery() {
		assertEquals(
				"CREATE TABLE my_table (my_id INTEGER, my_seq INTEGER, my_string VARCHAR, my_int INTEGER)",
				SqlMetaUtils.generateCreateTableQuery(getTableInfo()));
	}

	public void testGenerateAlterPrimaryKeyQuery() {
		assertEquals(
				"ALTER TABLE my_table ADD PRIMARY KEY (my_id, my_seq)",
				SqlMetaUtils.generateAlterPrimaryKeyQuery(getTableInfo()));
	}

	public void testGenerateAlterForeignKeyQuery() {
		final ReferenceInfo info = new ReferenceInfo();
		info.setFkTableName("my_table");
		info.setFkNameList(Arrays.asList("my_string", "my_int"));
		info.setPkTableName("your_table");
		info.setPkNameList(Arrays.asList("my_string1", "my_int1"));
		assertEquals(
				"ALTER TABLE my_table ADD FOREIGN KEY (my_string, my_int) " +
				"REFERENCES your_table (my_string1, my_int1)",
				SqlMetaUtils.generateAlterForeignKeyQuery(info));
	}
}
