package com.jeecms.cms.dao.assist.impl;

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

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import com.jeecms.cms.dao.assist.CmsDataBackDao;
import com.jeecms.cms.entity.back.CmsField;
import com.jeecms.cms.Constants;

@Repository
public class CmsDataBackDaoImpl extends JdbcDaoSupport implements
		CmsDataBackDao {

	public String createTableDDL(String tablename) {
		String sql = " show create table " + tablename;
		String ddl = getJdbcTemplate().queryForObject(sql,
				new RowMapper<String>() {
					public String mapRow(ResultSet set, int arg1)
							throws SQLException {
						return set.getString(2);
					}
				});
		return ddl;
	}

	public List<Object[]> createTableData(String tablename) {
		int filedNum = getTableFieldNums(tablename);
		List<Object[]> results = new ArrayList<Object[]>();
		String sql = " select * from   " + tablename;
		SqlRowSet set = getJdbcTemplate().queryForRowSet(sql);
		while (set.next()) {
			Object[] oneResult = new Object[filedNum];
			for (int i = 1; i <= filedNum; i++) {
				oneResult[i - 1] = set.getObject(i);
			}
			results.add(oneResult);
		}
		return results;
	}

	public List<CmsField> listFields(String tablename) {
		String sql = " desc  " + tablename;
		List<CmsField> fields = new ArrayList<CmsField>();
		SqlRowSet set = getJdbcTemplate().queryForRowSet(sql);
		while (set.next()) {
			CmsField field = new CmsField();
			field.setName(set.getString(1));
			field.setFieldType(set.getString(2));
			field.setNullable(set.getString(3));
			field.setFieldProperty(set.getString(4));
			field.setFieldDefault(set.getString(5));
			field.setExtra(set.getString(6));
			fields.add(field);
		}
		return fields;
	}

	public List<String> listTables(String catalog) {
	//	String sql = " show tables ";
		String sql = " SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='" + catalog + "' "; 
		List<String> tables = new ArrayList<String>();
		SqlRowSet set = getJdbcTemplate().queryForRowSet(sql);
		while (set.next()) {
			tables.add(set.getString(1));
		}
		return tables;
	}
	
	public List<String> listDataBases() {
		String sql = " show  databases ";
		List<String> tables = new ArrayList<String>();
		SqlRowSet set = getJdbcTemplate().queryForRowSet(sql);
		while (set.next()) {
			tables.add(set.getString(1));
		}
		return tables;
	}
	public String getDefaultCatalog() throws SQLException{
		  return getJdbcTemplate().getDataSource().getConnection().getCatalog();
	}
	
	public void setDefaultCatalog(String catalog) throws SQLException{
		   getJdbcTemplate().getDataSource().getConnection().setCatalog(catalog);
	}

	private int getTableFieldNums(String tablename) {
		String sql = " desc  " + tablename;
		SqlRowSet set = getJdbcTemplate().queryForRowSet(sql);
		int rownum = 0;
		while (set.next()) {
			rownum++;
		}
		return rownum;
	}

	public Boolean executeSQL(String sql) {
		try {
			String[]s=sql.split(Constants.ONESQL_PREFIX);
			for(String sqls:s){
				if(StringUtils.isNotBlank(sqls)){
					getJdbcTemplate().execute(sqls.trim());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

}