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

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import woolpack.utils.CheckUtils;
import woolpack.utils.PropertyUtils;

/**
 * 定数と静的メソッドの集まり。
 * @author nakamura
 *
 */
public final class CrudConstants {
	private static final String COMMA = ", ";
	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 SPACE = "";
	private static final String HATENA = "?";
	private static final String GE = " >= ";
	private static final String LE = " <= ";
	
	/**
	 * 登録用{@link Expression}を生成する{@link ExpressionFactory2}。
	 */
	public static final ExpressionFactory2 INSERT = new ExpressionFactory2() {
		public Expression newInstance(final TableInfo tableInfo, final Map<String, List<Object>> map) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
			sb.append("INSERT INTO ");
			sb.append(tableInfo.getName());
			sb.append('(');
			sb.mark(0);
			sb.append(") VALUES (");
			sb.mark(1);
			sb.append(')');
			
			final List<InputMapPointer> inputMapPointerList = new ArrayList<InputMapPointer>();

			final Set<String> columnSet = map.keySet();
			for (final String column : tableInfo.getColumnCollection()) {
				final String javaColumn = PropertyUtils.toJavaName(column);
				if (!columnSet.contains(javaColumn)) {
					continue;
				}
				inputMapPointerList.add(new InputMapPointer(javaColumn));

				sb.insert(0, CrudConstants.SPACE, CrudConstants.COMMA);
				sb.insert(0, column);
				sb.insert(1, CrudConstants.SPACE, CrudConstants.COMMA);
				sb.insert(1, CrudConstants.HATENA);
			}
			
			final Expression expression = new Expression();
			expression.setQuery(sb.toString());
			expression.setInputMapPointerList(inputMapPointerList);
			return expression;
		}
	};
	
	/**
	 * 検索用{@link Expression}を生成する{@link ExpressionFactory2}。
	 */
	public static final ExpressionFactory2 SELECT = new ExpressionFactory2() {
		public Expression newInstance(final TableInfo tableInfo, final Map<String, List<Object>> map) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
	    	sb.append("SELECT ");
	    	sb.mark(0);
	    	sb.append(" FROM ");
			sb.append(tableInfo.getName());
	    	sb.mark(1);
			
			final List<InputMapPointer> inputMapPointerList = new ArrayList<InputMapPointer>();
			buildSelectQueryBase(tableInfo, map, sb, inputMapPointerList, 0, 1);
			
			final Expression expression = new Expression();
			expression.setQuery(sb.toString());
			expression.setInputMapPointerList(inputMapPointerList);
			return expression;
		}
	};
	
	/**
	 * 更新用{@link Expression}を生成する{@link ExpressionFactory2}。
	 */
	public static final ExpressionFactory2 UPDATE = new ExpressionFactory2() {
		public Expression newInstance(final TableInfo tableInfo, final Map<String, List<Object>> map) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 2);
			sb.append("UPDATE ");
			sb.append(tableInfo.getName());
			sb.mark(0);
			sb.append(" ");
			sb.mark(1);
			
			final List<InputMapPointer> inputMapPointerList = new ArrayList<InputMapPointer>();

			final Set<String> columnSet = map.keySet();
			int setPosition = 0;
			for (final String column : tableInfo.getColumnCollection()) {
				final String javaColumn = PropertyUtils.toJavaName(column);
				if (!columnSet.contains(javaColumn)) {
					continue;
				}
				
				if (tableInfo.getPrimaryKeyCollection().contains(column)) {
					sb.insert(1, CrudConstants.WHERE, CrudConstants.AND);
					sb.insert(1, column);
					sb.insert(1, CrudConstants.EQUAL);
					sb.insert(1, CrudConstants.HATENA);
					inputMapPointerList.add(new InputMapPointer(javaColumn));
				} else {
					sb.insert(0, CrudConstants.SET, CrudConstants.COMMA);
					sb.insert(0, column);
					sb.insert(0, CrudConstants.EQUAL);
					sb.insert(0, CrudConstants.HATENA);

					inputMapPointerList.add(setPosition, new InputMapPointer(javaColumn));
					setPosition++;
				}
			}
			
			final Expression expression = new Expression();
			expression.setQuery(sb.toString());
			expression.setInputMapPointerList(inputMapPointerList);
			return expression;
		}
		
	};

	/**
	 * 削除用{@link Expression}を生成する{@link ExpressionFactory2}。
	 */
	public static final ExpressionFactory2 DELETE = new ExpressionFactory2() {
		public Expression newInstance(final TableInfo tableInfo, final Map<String, List<Object>> map) {
			final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 1);
	    	sb.append("DELETE FROM ");
			sb.append(tableInfo.getName());
			sb.mark(0);
			
			final List<InputMapPointer> inputMapPointerList = new ArrayList<InputMapPointer>();

			final Set<String> columnSet = map.keySet();
			for (final String column : tableInfo.getColumnCollection()) {
				final String javaColumn = PropertyUtils.toJavaName(column);
				if (!columnSet.contains(javaColumn)) {
					continue;
				}
				inputMapPointerList.add(new InputMapPointer(javaColumn));
				sb.insert(0, CrudConstants.WHERE, CrudConstants.AND);
				sb.insert(0, column);
				sb.insert(0, CrudConstants.EQUAL);
				sb.insert(0, CrudConstants.HATENA);
			}
			
			final Expression expression = new Expression();
			expression.setQuery(sb.toString());
			expression.setInputMapPointerList(inputMapPointerList);
			return expression;
		}
	};
	
	private static final Comparator<OrderInfo> ORDER_INFO_COMPARATOR = new Comparator<OrderInfo>() {
		public int compare(final OrderInfo o1, final OrderInfo o2) {
			return o1.absoluteOrder - o2.absoluteOrder;
		}
	};
	
	private CrudConstants() {
	}
	
	/**
	 * SELECT クエリのベース部分を作成し、sb と inputMapPointerList に反映する。
	 * @param tableInfo テーブル情報。本メソッドはこの引数の状態を変化させない。
	 * @param map 条件値の{@link Map}。本メソッドはこの引数の状態を変化させない。
	 * @param sb 編集中のクエリ文字列。本メソッドはこの引数の状態を変化させる。
	 * @param inputMapPointerList 値を設定することが可能な、解析されたクエリの入力マップのポインタの一覧。本メソッドはこの引数の状態を変化させる。
	 * @param colIndex sb 上における SELECT 句を挿入する位置。
	 * @param whereIndex sb 上における WHERE 句を挿入する位置。
	 */
	public static void buildSelectQueryBase(
			final TableInfo tableInfo,
			final Map<String, List<Object>> map,
			final MarkableStringBuilder sb,
			final List<InputMapPointer> inputMapPointerList,
			final int colIndex,
			final int whereIndex) {
		for (final String column : tableInfo.getColumnCollection()) {
			final String javaColumn = PropertyUtils.toJavaName(column);
			final List<Object> list = map.get(javaColumn);
			if (list != null) {
				if (list.size() == 2) {
					sb.insert(whereIndex, CrudConstants.WHERE, CrudConstants.AND);
					sb.insert(whereIndex, column);
					sb.insert(whereIndex, CrudConstants.GE);
					sb.insert(whereIndex, CrudConstants.HATENA);
					inputMapPointerList.add(new InputMapPointer(javaColumn, 0));
					
					sb.insert(whereIndex, CrudConstants.WHERE, CrudConstants.AND);
					sb.insert(whereIndex, column);
					sb.insert(whereIndex, CrudConstants.LE);
					sb.insert(whereIndex, CrudConstants.HATENA);
					inputMapPointerList.add(new InputMapPointer(javaColumn, 1));
				} else {
					sb.insert(whereIndex, CrudConstants.WHERE, CrudConstants.AND);
					sb.insert(whereIndex, column);
					sb.insert(whereIndex, CrudConstants.EQUAL);
					sb.insert(whereIndex, CrudConstants.HATENA);
					inputMapPointerList.add(new InputMapPointer(javaColumn));
				}
			}
			sb.insert(colIndex, CrudConstants.SPACE, CrudConstants.COMMA);
			sb.insert(colIndex, column);
		}
	}
	
	/**
	 * SELECT クエリの ORDER BY 部分を作成し、sb に反映する。
	 * orderPattern にマッチする map のキーについて、
	 * 値を数値とみなして値が正ならソート順を ASC に、値が負ならソート順を DESC にする様にクエリを作成する。
	 * また値の絶対値が小さいキーから順にソートする様にクエリを作成する。
	 * @param map 条件値の{@link Map}。
	 * @param sb 編集中のクエリ文字列。
	 * @param orderPattern ORDER BY の適用対象となる map のキーのパターン。
	 * @param orderIndex sb 上における ORDER BY 句を挿入する位置。
	 */
	public static void buildSelectQueryOrder(final Map<String, List<Object>> map,
			final MarkableStringBuilder sb,
			final Pattern orderPattern,
			final int orderIndex) {
		final List<OrderInfo> orderList = new ArrayList<OrderInfo>();
		for (final Entry<String, List<Object>> entry : map.entrySet()) {
			final Matcher m = orderPattern.matcher(entry.getKey());
			if (!m.matches()) {
				continue;
			}
			final OrderInfo orderInfo = new OrderInfo();
			orderInfo.javaName = m.group(1);
			orderInfo.sqlName = PropertyUtils.toSQLName(orderInfo.javaName);
			orderInfo.order = Integer.valueOf(entry.getValue().get(0).toString());
			orderInfo.absoluteOrder = Math.abs(orderInfo.order);
			orderList.add(orderInfo);
		}
		Collections.sort(orderList, ORDER_INFO_COMPARATOR);
		for (final OrderInfo orderInfo : orderList) {
			sb.insert(orderIndex, "ORDER BY ", COMMA);
			sb.insert(orderIndex, orderInfo.sqlName);
			sb.insert(orderIndex, (orderInfo.order > 0) ? " ASC" : " DESC");
		}
	}

	static class OrderInfo {
		String javaName;
		String sqlName;
		int absoluteOrder;
		int order;
	}
	
	/**
	 * 検索用{@link Expression}を生成する{@link ExpressionFactory2}を生成して返す。
	 * @param orderPattern ORDER BY の適用対象となる map のキーのパターン。
	 * @return 検索用{@link Expression}を生成する{@link ExpressionFactory2}。
	 */
	public static ExpressionFactory2 getSelectExpressionFactory2(final Pattern orderPattern) {
		return new ExpressionFactory2() {
			public Expression newInstance(final TableInfo tableInfo, final Map<String, List<Object>> map) {
				final MarkableStringBuilder sb = new MarkableStringBuilder(new StringBuilder(), 3);
		    	sb.append("SELECT ");
		    	sb.mark(0);
		    	sb.append(" FROM ");
				sb.append(tableInfo.getName());
		    	sb.mark(1);
		    	sb.append(" ");
		    	sb.mark(2);
				
				final List<InputMapPointer> inputMapPointerList = new ArrayList<InputMapPointer>();
				buildSelectQueryBase(tableInfo, map, sb, inputMapPointerList, 0, 1);
				buildSelectQueryOrder(map, sb, orderPattern, 2);
				
				final Expression expression = new Expression();
				expression.setQuery(sb.toString());
				expression.setInputMapPointerList(inputMapPointerList);
				return expression;
			}
		};
	}
	
	/**
	 * テーブル情報の一覧を取得して返す。
	 * @param dataSource データソース。
	 * @return テーブル情報の一覧。
	 */
	public static List<TableInfo> getTableInfoList(final DataSource dataSource) {
		final List<TableInfo> list = new ArrayList<TableInfo>();
		try {
			final Connection con = dataSource.getConnection();
			try {
				final DatabaseMetaData metaData = con.getMetaData();
				getTables(list, metaData);
				for (final TableInfo tableInfo : list) {
					getColumns(tableInfo, metaData);
					getPrimaryKeys(tableInfo, metaData);
				}
			} finally {
				con.close();
			}
		} catch (final SQLException e) {
			throw new IllegalStateException(e);
		}
		return list;
	}
	
	private static void getTables(
			final List<TableInfo> list,
			final DatabaseMetaData metaData) throws SQLException {
		final ResultSet rs = metaData.getTables(null, "%", "%", null);
		try {
			while (rs.next()) {
				if ("SYSTEM TABLE".equals(rs.getString("TABLE_TYPE"))) {
					continue;
				}
				final TableInfo tableInfo = new TableInfo();
				tableInfo.setCatalog(rs.getString("TABLE_CAT"));
				tableInfo.setSchema(rs.getString("TABLE_SCHEM"));
				tableInfo.setName(rs.getString("TABLE_NAME"));
				list.add(tableInfo);
			}
		} finally {
			rs.close();
		}
	}
	
	private static void getColumns(
			final TableInfo tableInfo,
			final DatabaseMetaData metaData) throws SQLException {
		final ResultSet rs = metaData.getColumns(
				tableInfo.getCatalog(),
				tableInfo.getSchema(),
				tableInfo.getName(), "%");
		try {
			while (rs.next()) {
				tableInfo.getColumnCollection().add(rs.getString("COLUMN_NAME"));
			}
		} finally {
			rs.close();
		}
	}
	
	private static void getPrimaryKeys(
			final TableInfo tableInfo,
			final DatabaseMetaData metaData) throws SQLException {
		final ResultSet rs = metaData.getPrimaryKeys(
				tableInfo.getCatalog(),
				tableInfo.getSchema(),
				tableInfo.getName());
		try {
			while (rs.next()) {
				tableInfo.getPrimaryKeyCollection().add(rs.getString("COLUMN_NAME"));
			}
		} finally {
			rs.close();
		}
	}
	
	/**
	 * テーブル情報の一覧をテーブル名の java 表現をキーとしてテーブル情報を値とした{@link Map}に変換する。
	 * @param list テーブル情報の一覧。
	 * @return テーブル名のjava表現をキーとしてテーブル情報を値とした{@link Map}。
	 */
	public static Map<String, TableInfo> convertJavaNameTableInfoMap(final List<TableInfo> list) {
		final Map<String, TableInfo> map = new HashMap<String, TableInfo>();
		for (final TableInfo info : list) {
			map.put(PropertyUtils.toJavaName(info.getName()), info);
		}
		return map;
	}
	
	/**
	 * デフォルトの{@link CrudFactory}を生成して返す。
	 * @param dataSource データソース。
	 * @param orderPattern ORDER BY の適用対象となる map のキーのパターン。
	 * @param maxRecode 取得する最大レコード数。
	 * @param startPositionKey {@link ResultSet}から取得する開始位置のキー。
	 * @param recodeCountKey {@link ResultSet}から取得する件数のキー。
	 * @return 生成された{@link CrudFactory}。
	 * @throws NullPointerException 引数が null の場合。
	 */
	public static CrudFactory getCrudFactory(
			final DataSource dataSource,
			final Pattern orderPattern,
			final int maxRecode,
			final String startPositionKey,
			final String recodeCountKey) {
		CheckUtils.checkNotNull(dataSource);
		final List<TableInfo> tableInfoList = getTableInfoList(dataSource);
		final Map<String, TableInfo> tableInfoMap = convertJavaNameTableInfoMap(tableInfoList);
		final Executable executable = new PreparedStatementExecutor(dataSource);
		
		return new CrudFactory() {
			private final UpdatableFactory insertFactory = new UpdatableFactoryCacheImpl(
					new HashMap<Object, Updatable>(),
					new UpdatableFactoryImpl(
							new ExpressionFactoryImpl(tableInfoMap, INSERT),
							executable
					)
			);
			private final QueryFactory selectFactory =
					new QueryFactoryImpl(
							new ExpressionFactoryImpl(tableInfoMap, getSelectExpressionFactory2(orderPattern)),
							executable,
							maxRecode,
							startPositionKey,
							recodeCountKey
					);
			private final UpdatableFactory updateFactory = new UpdatableFactoryCacheImpl(
					new HashMap<Object, Updatable>(),
					new UpdatableFactoryImpl(
							new ExpressionFactoryImpl(tableInfoMap, UPDATE),
							executable
					)
			);
			private final UpdatableFactory deleteFactory = new UpdatableFactoryCacheImpl(
					new HashMap<Object, Updatable>(),
					new UpdatableFactoryImpl(
							new ExpressionFactoryImpl(tableInfoMap, DELETE),
							executable
					)
			);
			
			public Updatable newInsertUpdatable(final String id, final Map<String, List<Object>> map) {
				return insertFactory.newInstance(id, map);
			}

			public Query newSelectQuery(final String id, final Map<String, List<Object>> map) {
				return selectFactory.newInstance(id, map);
			}

			public Updatable newUpdateUpdatable(final String id, final Map<String, List<Object>> map) {
				return updateFactory.newInstance(id, map);
			}

			public Updatable newDeleteUpdatable(final String id, final Map<String, List<Object>> map) {
				return deleteFactory.newInstance(id, map);
			}
		};
	}
}
