/*
 * 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.meta;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import woolpack.fn.Fn;
import woolpack.sql.fn.PreparedStatementInfo;

/**
 * DDL(Data Definition Language)系と
 * DML(Data Manipulation Language)系の
 * SQLを生成するためのユーティリティです。
 * @author nakamura
 *
 */
public class SqlMetaUtils {
	private static final String COMMA = ", ";
	private static final String DOT = ".";
	private static final String AND = " AND ";
	private static final String WHERE = " WHERE ";
	private static final String SET = " SET ";
	private static final String EQUAL = " = ";
	private static final String EMPTY = "";
	private static final String SPACE = " ";
	private static final String HATENA = "?";
	
	/**
	 * 登録用のクエリ情報を生成する{@link Fn}です。
	 */
	public static final Fn<TableInfo, PreparedStatementInfo, RuntimeException> INSERT_FACTORY = new Fn<TableInfo, PreparedStatementInfo, RuntimeException>() {
		public PreparedStatementInfo exec(final TableInfo c) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
			sb.append("INSERT INTO ");
			sb.append(c.getTableName());
			sb.append('(');
			sb.mark(0);
			sb.append(") VALUES (");
			sb.mark(1);
			sb.append(')');
			final List<String> list = new ArrayList<String>(c.getColNameList().size());
			for (final String column : c.getColNameList()) {
				sb.insert(0, EMPTY, COMMA);
				sb.insert(0, column);
				sb.insert(1, EMPTY, COMMA);
				sb.insert(1, HATENA);
				list.add(column);
			}
			return new PreparedStatementInfo(sb.toString(), list);
		}
	};
	
	/**
	 * 主キーを検索条件として検索用のクエリ情報を生成する{@link Fn}です。
	 */
	public static final Fn<TableInfo, PreparedStatementInfo, RuntimeException> SELECT_FACTORY = new Fn<TableInfo, PreparedStatementInfo, RuntimeException>() {
		public PreparedStatementInfo exec(final TableInfo c) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
	    	sb.append("SELECT ");
	    	sb.mark(0);
	    	sb.append(" FROM ");
			sb.append(c.getTableName());
	    	sb.mark(1);
			final List<String> list = new ArrayList<String>(c.getColNameList().size());
			for (final String column : c.getColNameList()) {
				if (c.getPkNameList().contains(column)) {
					sb.insert(1, WHERE, AND);
					sb.insert(1, column);
					sb.insert(1, EQUAL);
					sb.insert(1, HATENA);
					list.add(column);
				}
				sb.insert(0, EMPTY, COMMA);
				sb.insert(0, column);
			}
			return new PreparedStatementInfo(sb.toString(), list);
		}
	};
	
	/**
	 * 主キーを検索条件として主キー以外の値を更新する更新用のクエリ情報を生成する{@link Fn}です。
	 */
	public static final Fn<TableInfo, PreparedStatementInfo, RuntimeException> UPDATE_FACTORY = new Fn<TableInfo, PreparedStatementInfo, RuntimeException>() {
		public PreparedStatementInfo exec(final TableInfo c) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
			sb.append("UPDATE ");
			sb.append(c.getTableName());
			sb.mark(0);
			sb.append(SPACE);
			sb.mark(1);
			final List<String> list = new ArrayList<String>(c.getColNameList().size());
			int setPosition = 0;
			for (final String column : c.getColNameList()) {
				if (c.getPkNameList().contains(column)) {
					sb.insert(1, WHERE, AND);
					sb.insert(1, column);
					sb.insert(1, EQUAL);
					sb.insert(1, HATENA);
					list.add(column);
				} else {
					sb.insert(0, SET, COMMA);
					sb.insert(0, column);
					sb.insert(0, EQUAL);
					sb.insert(0, HATENA);
					list.add(setPosition, column);
					setPosition++;
				}
			}
			return new PreparedStatementInfo(sb.toString(), list);
		}
	};
	
	/**
	 * 主キーを検索条件とする削除用のクエリ情報を生成する{@link Fn}です。
	 */
	public static final Fn<TableInfo, PreparedStatementInfo, RuntimeException> DELETE_FACTORY = new Fn<TableInfo, PreparedStatementInfo, RuntimeException>() {
		public PreparedStatementInfo exec(final TableInfo c) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 1);
	    	sb.append("DELETE FROM ");
			sb.append(c.getTableName());
			sb.mark(0);
			final List<String> list = new ArrayList<String>(c.getPkNameList().size());
			for (final String column : c.getPkNameList()) {
				sb.insert(0, WHERE, AND);
				sb.insert(0, column);
				sb.insert(0, EQUAL);
				sb.insert(0, HATENA);
				list.add(column);
			}
			return new PreparedStatementInfo(sb.toString(), list);
		}
	};
	
	/**
	 * 全てのカラムを検索条件とする削除用のクエリ情報を生成する{@link Fn}です。
	 */
	public static final Fn<TableInfo, PreparedStatementInfo, RuntimeException> ALL_CHECKING_DELETE_FACTORY = new Fn<TableInfo, PreparedStatementInfo, RuntimeException>() {
		public PreparedStatementInfo exec(final TableInfo c) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 1);
	    	sb.append("DELETE FROM ");
			sb.append(c.getTableName());
			sb.mark(0);
			final List<String> list = new ArrayList<String>(c.getPkNameList().size());
			for (final String column : c.getColNameList()) {
				sb.insert(0, WHERE, AND);
				sb.insert(0, column);
				sb.insert(0, EQUAL);
				sb.insert(0, HATENA);
				list.add(column);
			}
			return new PreparedStatementInfo(sb.toString(), list);
		}
	};
	
	/**
	 * 指定されたカラム名だけを指定する登録用のクエリ情報を生成する{@link Fn}を返す{@link Fn}です。
	 */
	public static final Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException> INSERT_FACTORY_FACTORY
	= new Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException>(){
		public Fn<Collection<String>, PreparedStatementInfo, RuntimeException> exec(final TableInfo c0) {
			return new Fn<Collection<String>, PreparedStatementInfo, RuntimeException>() {
				public PreparedStatementInfo exec(final Collection<String> c1) {
					return INSERT_FACTORY.exec(retain(c0, c1));
				}
			};
		}
	};

	/**
	 * 主キーの指定されたカラム名だけを検索条件として
	 * 指定されたカラム名だけを指定する更新用のクエリ情報を生成する{@link Fn}を返す{@link Fn}です。
	 */
	public static final Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException> UPDATE_FACTORY_FACTORY
	= new Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException>(){
		public Fn<Collection<String>, PreparedStatementInfo, RuntimeException> exec(final TableInfo c0) {
			return new Fn<Collection<String>, PreparedStatementInfo, RuntimeException>() {
				public PreparedStatementInfo exec(final Collection<String> c1) {
					return UPDATE_FACTORY.exec(retain(c0, c1));
				}
			};
		}
	};

	/**
	 * 指定されたカラム名だけを検索条件に指定する検索用のクエリ情報を生成する{@link Fn}を返す{@link Fn}です。
	 */
	public static final Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException> SELECT_FACTORY_FACTORY
	= new Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException>(){
		public Fn<Collection<String>, PreparedStatementInfo, RuntimeException> exec(final TableInfo c0) {
			return new Fn<Collection<String>, PreparedStatementInfo, RuntimeException>() {
				public PreparedStatementInfo exec(final Collection<String> c1) {
					final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
			    	sb.append("SELECT ");
			    	sb.mark(0);
			    	sb.append(" FROM ");
					sb.append(c0.getTableName());
			    	sb.mark(1);
					final List<String> list = new ArrayList<String>(c1.size());
					for (final String column : c0.getColNameList()) {
						if (c1.contains(column)) {
							sb.insert(1, WHERE, AND);
							sb.insert(1, column);
							sb.insert(1, EQUAL);
							sb.insert(1, HATENA);
							list.add(column);
						}
						sb.insert(0, EMPTY, COMMA);
						sb.insert(0, column);
					}
					return new PreparedStatementInfo(sb.toString(), list);
				}
			};
		}
	};

	/**
	 * 指定されたカラム名だけを検索条件に指定する検索用のクエリ情報を生成する{@link Fn}を返す{@link Fn}です。
	 * この機能で生成されるSQLはDBのアクセスパスを考慮しないので、
	 * レコード量が多いシステムに対して期待する性能がでない可能性があります。
	 */
	public static final Fn<List<TableInfo>, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException> JOIN_SELECT_FACTORY_FACTORY
	= new Fn<List<TableInfo>, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException>(){
		public Fn<Collection<String>, PreparedStatementInfo, RuntimeException> exec(final List<TableInfo> c0) {
			return new Fn<Collection<String>, PreparedStatementInfo, RuntimeException>() {
				public PreparedStatementInfo exec(final Collection<String> c1) {
					final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 3);
			    	sb.append("SELECT ");
			    	sb.mark(0);
			    	sb.append(" FROM ");
			    	sb.mark(1);
			    	sb.append(SPACE);
			    	sb.mark(2);

					final List<String> list = new ArrayList<String>(c1.size());
					for (final TableInfo tableInfo : c0) {
						sb.insert(1, EMPTY, COMMA);
						sb.insert(1, tableInfo.getTableName());

						for (final String column : tableInfo.getColNameList()) {
							if (c1.contains(column)) {
								sb.insert(2, WHERE, AND);
								sb.insert(2, tableInfo.getTableName());
								sb.insert(2, DOT);
								sb.insert(2, column);
								sb.insert(2, EQUAL);
								sb.insert(2, HATENA);
								list.add(column);
							}
							sb.insert(0, EMPTY, COMMA);
							sb.insert(0, tableInfo.getTableName());
							sb.insert(0, DOT);
							sb.insert(0, column);
						}
						refLoop:for (final ReferenceInfo referenceInfo : tableInfo.getImportedKeysList()) {
							for(final TableInfo tmp : c0) {
								if (referenceInfo.getPkTableName().equals(tmp.getTableName())) {
									final int size = referenceInfo.getPkNameList().size();
									for (int i = 0; i < size; i++) {
										sb.insert(2, WHERE, AND);
										sb.insert(2, referenceInfo.getPkTableName());
										sb.insert(2, DOT);
										sb.insert(2, referenceInfo.getPkNameList().get(i));
										sb.insert(2, EQUAL);
										sb.insert(2, referenceInfo.getFkTableName());
										sb.insert(2, DOT);
										sb.insert(2, referenceInfo.getFkNameList().get(i));
									}
									continue refLoop;
								}
							}
						}
					}
					return new PreparedStatementInfo(sb.toString(), list);
				}
			};
		}
	};
	
	private SqlMetaUtils() {
	}
	
	private static TableInfo retain(final TableInfo base, final Collection<String> retainList) {
		final TableInfo result = new TableInfo();
		result.setTableName(base.getTableName());
		result.setColNameList(new ArrayList<String>(base.getColNameList()));
		result.getColNameList().retainAll(retainList);
		result.setPkNameList(new ArrayList<String>(base.getPkNameList()));
		result.getPkNameList().retainAll(retainList);
		return result;
	}


	/**
	 * 全てのカラムを検索条件として
	 * 指定されたカラム名だけを指定する
	 * 更新用のクエリ情報を生成する{@link Fn}を返す{@link Fn}を返します。
	 */
	public static final Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException> getAllCheckingUpdateFactoryFactory(
			final Fn<String, String, RuntimeException> fn) {
		return new Fn<TableInfo, Fn<Collection<String>, PreparedStatementInfo, RuntimeException>, RuntimeException>(){
			public Fn<Collection<String>, PreparedStatementInfo, RuntimeException> exec(final TableInfo c0) {
				return new Fn<Collection<String>, PreparedStatementInfo, RuntimeException>() {
					public PreparedStatementInfo exec(final Collection<String> c1) {
						final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
						sb.append("UPDATE ");
						sb.append(c0.getTableName());
						sb.mark(0);
						sb.append(SPACE);
						sb.mark(1);
						final List<String> list = new ArrayList<String>(c0.getColNameList().size());
						int setPosition = 0;
						for (final String column : c0.getColNameList()) {
							sb.insert(1, WHERE, AND);
							sb.insert(1, column);
							sb.insert(1, EQUAL);
							sb.insert(1, HATENA);
							list.add(fn.exec(column));
							
							if (c1.contains(column)) {
								sb.insert(0, SET, COMMA);
								sb.insert(0, column);
								sb.insert(0, EQUAL);
								sb.insert(0, HATENA);
								list.add(setPosition, column);
								setPosition++;
							}
						}
						return new PreparedStatementInfo(sb.toString(), list);
					}
				};
			}
		};
	}

	/**
	 * テーブル・主キー・参照制約を登録するクエリを生成して返します。
	 * @param inList
	 * @return テーブル・主キー・参照制約を登録するクエリの一覧。
	 */
	public static List<String> generateCreateQuery(final Collection<TableInfo> inList) {
		final List<String> outList = new ArrayList<String>();
		for (final TableInfo tableInfo : inList) {
			outList.add(generateCreateTableQuery(tableInfo));
			if (tableInfo.getPkNameList().size() > 0) {
				outList.add(generateAlterPrimaryKeyQuery(tableInfo));
			}
			for (final ReferenceInfo refInfo : tableInfo.getImportedKeysList()) {
				outList.add(generateAlterForeignKeyQuery(refInfo));
			}
		}
		return outList;
	}
	
	/**
	 * テーブルを登録するクエリを生成して返します。
	 * @param info
	 * @return テーブルを登録するクエリ。
	 */
	public static String generateCreateTableQuery(final TableInfo info) {
		final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
		sb.append("CREATE TABLE ");
		sb.append(info.getTableName());
		sb.append(" (");
		sb.mark(0);
		sb.append(")");
		for (final String colName : info.getColNameList()) {
			final ColumnInfo colInfo = info.getColumnInfoMap().get(colName);
			sb.insert(0, EMPTY, COMMA);
			sb.insert(0, colName);
			sb.insert(0, SPACE);
			sb.insert(0, colInfo.getTypeName());
		}
		return sb.toString();
	}

	/**
	 * テーブルに主キーを登録するクエリを生成して返します。
	 * @param info
	 * @return テーブルに主キーを登録するクエリ。
	 */
	public static String generateAlterPrimaryKeyQuery(final TableInfo info) {
		final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
		sb.append("ALTER TABLE ");
		sb.append(info.getTableName());
		sb.append(" ADD PRIMARY KEY (");
		sb.mark(0);
		sb.append(")");
		for (final String colName : info.getPkNameList()) {
			sb.insert(0, EMPTY, COMMA);
			sb.insert(0, colName);
		}
		return sb.toString();
	}

	/**
	 * テーブルに参照制約を登録するクエリを生成して返します。
	 * @param info
	 * @return テーブルに参照制約を登録するクエリ。
	 */
	public static String generateAlterForeignKeyQuery(final ReferenceInfo info) {
		final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
		sb.append("ALTER TABLE ");
		sb.append(info.getFkTableName());
		sb.append(" ADD FOREIGN KEY (");
		sb.mark(0);
		sb.append(") REFERENCES ");
		sb.append(info.getPkTableName());
		sb.append(" (");
		sb.mark(1);
		sb.append(")");
		
		for (final String colName : info.getFkNameList()) {
			sb.insert(0, EMPTY, COMMA);
			sb.insert(0, colName);
		}
		for (final String colName : info.getPkNameList()) {
			sb.insert(1, EMPTY, COMMA);
			sb.insert(1, colName);
		}
		return sb.toString();
	}
}
