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.db.page.ListPageContext;
import com.walker.dbmeta.AbstractDatabaseMetaEngine;
import com.walker.dbmeta.FieldInfo;
import com.walker.dbmeta.util.DatabaseMetaEngineUtils;
import com.walker.infrastructure.utils.StringUtils;
import com.walker.jdbc.util.SqlUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

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

/**
 * 描述：oracle元数据管理引擎具体实现
 * @author 时克英
 * @date 2017年11月14日 下午10:40:26
 */

public class OracleMetaEngine extends AbstractDatabaseMetaEngine {

//	private final Oracle9iDialect oracleDialect = new Oracle9iDialect();
	private final TableInfoMapper tableInfoMapper = new TableInfoMapper();
	private final FieldInfoMapper fieldInfoMapper = new FieldInfoMapper();

	/* oracle数据库只读用户后缀，因为连接的很可能是我们创建的只读用户，如：zzdxzcgl_read,
	 * 因此需要用原始的用户才能访问到实际的表信息 */
	private final static String READ_SUFFIX = "_READ";

	private final String SQL_ALL_TABLES = "Select t.table_name, t.tablespace_name, t.status, t.num_rows, t.blocks, t.avg_row_len, t.last_analyzed, c.comments"
			+ " from all_tables t, all_tab_comments c"
			+ " where t.owner=? And t.owner = c.owner And c.table_type = 'TABLE' And c.table_name = t.table_name"
			+ " order by num_rows Desc";

	private final String SQL_ALL_TABLES_LIKE = "Select t.table_name, t.tablespace_name, t.status, t.num_rows, t.blocks, t.avg_row_len, t.last_analyzed, c.comments"
			+ " from all_tables t, all_tab_comments c"
			+ " where t.owner=? And t.owner = c.owner And c.table_type = 'TABLE' And c.table_name = t.table_name And t.table_name Like ?"
			+ " order by num_rows Desc";
	private final String SQL_ALL_TABLES_WHERE_IN = "Select t.table_name, t.tablespace_name, t.status, t.num_rows, t.blocks, t.avg_row_len, t.last_analyzed, c.comments"
			+ " from all_tables t, all_tab_comments c"
			+ " where t.owner=:owner And t.owner = c.owner And c.table_type = 'TABLE' And c.table_name = t.table_name And t.table_name in (:ids)"
			+ " order by num_rows Desc";

	/*private final String SQL_FIELDS = "select c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.DATA_LENGTH, c.NULLABLE, c.COLUMN_ID, s.COMMENTS "
			+ "from user_tab_columns c, user_col_comments s where c.Table_Name=? "
			+ "and c.TABLE_NAME = s.TABLE_NAME and c.COLUMN_NAME = s.COLUMN_NAME order by c.COLUMN_ID";*/
	private final String SQL_FIELDS = "t1.*, ucc.constraint_name, uc.constraint_type from ("
			+ "select c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.DATA_LENGTH, c.DATA_PRECISION, C.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, s.COMMENTS "
			+ "from user_tab_columns c, user_col_comments s "
			+ "where c.Table_Name=? and c.TABLE_NAME = s.TABLE_NAME and c.COLUMN_NAME = s.COLUMN_NAME order by c.COLUMN_ID"
			+ ") t1 "
			+ "left join user_cons_columns ucc on ucc.table_name=t1.Table_Name and ucc.column_name=t1.COLUMN_NAME "
			+ "left join user_constraints uc on uc.constraint_name=ucc.constraint_name";

	@Override
	public GenericPager<TableInfo> queryPageTableNamesByLike(Address address, String tableNameLike) {
		DatabaseConnector conn = getConnector(address);

		String query = null;
		String pageSql = null;
		Object[] args = null;
		if(StringUtils.isEmpty(tableNameLike)){
			query = SQL_ALL_TABLES;
//			pageSql = oracleDialect.getLimitString(SQL_ALL_TABLES, true);
			pageSql = SqlUtils.getOraclePageSql(query);
			args = new Object[1];
			args[0] = this.getDatabaseUser(address.getAuthentication());
		} else {
			query = SQL_ALL_TABLES_LIKE;
//			pageSql = oracleDialect.getLimitString(SQL_ALL_TABLES_LIKE, true);
			pageSql = SqlUtils.getOraclePageSql(query);
			args = new Object[2];
			args[0] = this.getDatabaseUser(address.getAuthentication());
			args[1] = DatabaseMetaEngineUtils.getLikeConditionArg(tableNameLike.toUpperCase());
		}

		return conn.sqlSimpleQueryPager(query, args, tableInfoMapper
//				, ListPageContext.getCurrentPageIndex(), ListPageContext.getCurrentPageSize()
				, pageSql);
	}

	/**
	 * 对于只读库，我们会规范oracle用户命名为：***_READ，因此在查询用户库元数据时，
	 * 需要判断是否是原始用户，最终必须使用原始用户才能查询到。
	 * @param oracleUserName
	 * @return
	 */
	private String getDatabaseUser(String oracleUserName){
		String oracleUser = oracleUserName.toUpperCase();
		int index = oracleUser.indexOf(READ_SUFFIX);
		if(index > 0){
			oracleUser = oracleUser.substring(0, index);
		}
		return oracleUser;
	}

	private class TableInfoMapper implements RowMapper<TableInfo>{
		@Override
		public TableInfo mapRow(ResultSet rs, int arg1) throws SQLException {
			TableInfo ti = new TableInfo();
			ti.setName(rs.getString("table_name"));
			ti.setSummary(rs.getString("comments"));
			ti.setRows(rs.getInt("num_rows"));
			ti.setAvgRowLen(rs.getLong("avg_row_len"));
			ti.setBlocks(rs.getLong("blocks"));
			ti.setLastAnalyzed(rs.getString("last_analyzed"));
			ti.setStatus(rs.getString("status"));
			ti.setTableSpace(rs.getString("tablespace_name"));
			return ti;
		}
	}

	private class FieldInfoMapper implements RowMapper<FieldInfo> {
		@Override
		public FieldInfo mapRow(ResultSet rs, int arg1) throws SQLException {
			ResultSetMetaData rsMetaData = rs.getMetaData();
			FieldInfo f = new FieldInfo();
			f.setColumnId(rs.getInt("COLUMN_ID"));
			f.setComments(rs.getString("COMMENTS"));
			f.setDataType(rs.getString("DATA_TYPE"));
			f.setFieldName(rs.getString("COLUMN_NAME"));
			f.setLength(rs.getLong("DATA_LENGTH"));
			f.setNullable(rs.getString("NULLABLE"));
			f.setTableName(rs.getString("TABLE_NAME"));
			String constraintType = rs.getString("constraint_type");
			if(StringUtils.isNotEmpty(constraintType) && constraintType.equals("P")){
				f.setColumnKey(FieldInfo.NAME_PRIMARY_KEY);
			}
			if(rs.getObject("DATA_PRECISION") != null){
				f.setPrecision(rs.getInt("DATA_PRECISION"));
			}
//			if(DatabaseMetaEngineUtils.isContainColumnName("data_scale", rsMetaData)){
			if(rs.getObject("DATA_SCALE") != null){
				f.setScale(rs.getInt("DATA_SCALE"));
			}
			return f;
		}
	}

	@Override
	protected DatabaseConnector createDbConnector(Address address) {
		DatabaseConnector connector = ConnectorUtils.createOracleConnector(address);
		if(connector == null){
			throw new IllegalArgumentException("connector create failed!");
		}
		return ConnectorUtils.acquireTransactionProxyConnector(connector);
	}

	@Override
	protected int loadSchemaTableSize(DatabaseConnector connnector) {
		return 0;
	}

	@Override
	protected List<String> loadFields(DatabaseConnector connector, String tableName) {
		return null;
	}

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

		List<TableInfo> list = (List<TableInfo>)connector.queryForRowMapper(SQL_ALL_TABLES_WHERE_IN, 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;
	}

	@Override
	protected long loadTableRow(DatabaseConnector connector, String tableName) {
		return 0;
	}

	@Override
	protected List<Map<String, Object>> loadDatas(DatabaseConnector connector, String tableName, String sql)
			throws Exception {
		return null;
	}

	@Override
	protected List<FieldInfo> loadFieldsObject(DatabaseConnector connector, String tableName) {
		return connector.queryForRowMapper(SQL_FIELDS, new Object[]{tableName.toUpperCase()}, fieldInfoMapper);
	}

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

	}

	public static void main(String[] args){
		Address address = new Address();
		address.setUrl("127.0.0.1");
		address.setPort(1521);
		address.setService("ORCL2");
		address.setAuthentication("YGZW");
		address.setCertification("YGZW");

		OracleMetaEngine metaEngine = new OracleMetaEngine();

		ListPageContext.setCurrentPageIndex(1);
		ListPageContext.setCurrentPageSize(10);

		GenericPager<TableInfo> pager = metaEngine.queryPageTableNamesByLike(address, "biz");
		System.out.println(pager.getDatas());
	}
}