package com.walker.dbmeta.support;

import com.walker.connector.Address;
import com.walker.connector.support.DatabaseConnector;
import com.walker.connector.util.ConnectorUtils;
import com.walker.db.DatabaseException;
import com.walker.db.TableInfo;
import com.walker.db.page.GenericPager;
import com.walker.dbmeta.AbstractDatabaseMetaEngine;
import com.walker.dbmeta.FieldInfo;
import com.walker.dbmeta.util.DatabaseMetaEngineUtils;
import com.walker.dbmeta.util.DatabaseUtils;
import com.walker.infrastructure.utils.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * PostgreSQL数据库对元数据的操作引擎实现，注意与Mysql的区别
 * @author shikeying
 * @date 2016年4月22日
 *
 */
public class PostgresMetaEngine extends AbstractDatabaseMetaEngine {

	public static final String SQL_GET_TBL_COUNT = "SELECT COUNT(*) cnt FROM information_schema.TABLES"
			+ " WHERE table_catalog = ? and table_schema = 'public' and TABLE_TYPE = 'BASE TABLE'";

	public final String QUERY_TABLE_FIELDS = "SELECT COLUMN_NAME from information_schema.COLUMNS "
			+ "WHERE table_catalog = ? and table_schema = 'public' AND TABLE_NAME = ?";

	public final String SQL_TABLES_ROW = "SELECT c.relname tname, '' summary, c.reltuples trows"
			+ " from pg_class c, pg_namespace n "
			+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
			+ " and c.relname in (:ids)";

	public final String SQL_ONE_TABLE_ROW = "SELECT c.reltuples trows"
			+ " from pg_class c, pg_namespace n "
			+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
			+ " and c.relname = ?";
	public final String SQL_TABLE_LIKE = "SELECT c.relname table_name"
			+ " from pg_class c, pg_namespace n "
			+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
			+ " and c.relname like ?";

	public final String SQL_TABLE_LIKE_PAGE = "SELECT c.relname tname, '' summary, c.reltuples trows"
			+ " from pg_class c, pg_namespace n "
			+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
			+ " and c.relname like ?";

	private final TableInfoMapper tableInfoMapper = new TableInfoMapper();

	@Override
	protected DatabaseConnector createDbConnector(Address address) {
		return ConnectorUtils.createPostgresConnector(address);
	}

	@SuppressWarnings("unchecked")
	protected List<Map<String, Object>> loadDatas(DatabaseConnector connector
			, String tableName, String sql) throws Exception{
		List<Map<String, Object>> list = (List<Map<String, Object>>)connector.invoke(sql, (Object[])null);
		if(list == null || list.size() == 0){
			return null;
		}
		return list;
	}

	@Override
	protected List<String> loadFields(DatabaseConnector connector, String tableName) {
		List<Map<String, Object>> list = connector.queryForList(QUERY_TABLE_FIELDS, new Object[]{DatabaseUtils.getMySQLSchemaName(connector.getServiceName()), tableName});
		if(list != null && list.size() > 0){
			List<String> result = new ArrayList<String>(list.size());
			for(Map<String, Object> map : list){
				result.add(map.get("COLUMN_NAME").toString().toLowerCase());
			}
			return result;
		}
		return null;
	}

	@Override
	protected int loadSchemaTableSize(DatabaseConnector connector) {
		return connector.queryForInt(SQL_GET_TBL_COUNT, new Object[]{connector.getServiceName()});
	}

	@Override
	protected Map<String, TableInfo> loadTablesRow(Address address, DatabaseConnector connector, List<String> tableNameList) {
		try {
			MapSqlParameterSource nameSet = new MapSqlParameterSource();
//			nameSet.addValue("schema", DatabaseUtils.getMySQLSchemaName(connector.getServiceName()));
			nameSet.addValue("ids", tableNameList);

			List<TableInfo> list = (List<TableInfo>)connector.queryForRowMapper(SQL_TABLES_ROW
					, tableInfoMapper, nameSet);
			if(!StringUtils.isEmptyList(list)){
				Map<String, TableInfo> result = new HashMap<String, TableInfo>();
				for(TableInfo ti : list){
					result.put(ti.getName(), ti);
				}
				return result;
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	protected long loadTableRow(DatabaseConnector connector, String tableName) {
		return connector.queryForLong(SQL_ONE_TABLE_ROW, new Object[]{tableName});
	}

	/**
	 * 查找给定库中的表名集合
	 * @param address
	 * @param tableNameLike 要模糊查找的表名，如: base_custdept_
	 * @return
	 */
	@Override
	public List<String> getTableNamesByLike(Address address, String tableNameLike){
		DatabaseConnector conn = getConnector(address);
		List<Map<String, Object>> list = conn.queryForList(SQL_TABLE_LIKE
				, new Object[]{DatabaseMetaEngineUtils.getLikeConditionArg(tableNameLike)});
		List<String> result = new ArrayList<>();
		if(list != null){
			for(Map<String, Object> m : list){
				result.add(m.get("table_name").toString());
			}
		}
		return result;
	}

	private class TableInfoMapper implements RowMapper<TableInfo>{
		@Override
		public TableInfo mapRow(ResultSet rs, int arg1) throws SQLException {
			TableInfo ti = new TableInfo();
			ti.setName(rs.getString("tname"));
			ti.setSummary(rs.getString("summary"));
			ti.setRows(rs.getInt("trows"));
			return ti;
		}

	}

	@Override
	public GenericPager<TableInfo> queryPageTableNamesByLike(Address address, String tableNameLike) {
//		throw new UnsupportedOperationException("未支持的方法");
		DatabaseConnector conn = getConnector(address);
		// 查询总记录
		// 查询数据集合
		String pageSql = SQL_TABLE_LIKE_PAGE + " limit ? offset ?";
		Object[] args = new Object[1];
//		args[0] = address.getServiceName();
		args[0] = tableNameLike;

		return conn.sqlSimpleQueryPager(SQL_TABLE_LIKE_PAGE, args, tableInfoMapper, pageSql);
	}

	@Override
	protected List<FieldInfo> loadFieldsObject(DatabaseConnector connector, String tableName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected void doCreateTableAction(Address address, List<FieldInfo> fieldList, String dataVersionField, String tableName)
			throws DatabaseException {
		// TODO Auto-generated method stub

	}

}