package hiro.yoshioka.sql.twitter;

import hiro.yoshioka.sdh.DatabaseType;
import hiro.yoshioka.sdh2.ResultSetDataHolder2;
import hiro.yoshioka.sql.AbsNoSQL;
import hiro.yoshioka.sql.SQLExecutionStatus;
import hiro.yoshioka.sql.engine.GettingResourceRequest;
import hiro.yoshioka.sql.engine.MirroringRequest;
import hiro.yoshioka.sql.engine.Request;
import hiro.yoshioka.sql.engine.SQLOperationType;
import hiro.yoshioka.sql.engine.TransactionRequest;
import hiro.yoshioka.sql.params.ConnectionProperties;
import hiro.yoshioka.sql.resource.DBRoot;
import hiro.yoshioka.sql.resource.IDBColumn;
import hiro.yoshioka.sql.resource.IDBResource;
import hiro.yoshioka.sql.resource.IDBTable;
import hiro.yoshioka.sql.resource.TableType;
import hiro.yoshioka.sql.resource.twitter.TwitterColumnType;
import hiro.yoshioka.sql.resource.twitter.TwitterDBColumn;
import hiro.yoshioka.sql.resource.twitter.TwitterDBSchema;
import hiro.yoshioka.sql.resource.twitter.TwitterDBTable;
import hiro.yoshioka.sql.resource.twitter.TwitterSchemaType;
import hiro.yoshioka.sql.resource.twitter.TwitterTableType;
import hiro.yoshioka.sql.resource.twitter.TwitterUser;
import hiro.yoshioka.util.SQLDataType;
import hiro.yoshioka.util.StringUtil;

import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import twitter4j.AccountSettings;
import twitter4j.IDs;
import twitter4j.Twitter;
import twitter4j.TwitterException;
import twitter4j.TwitterFactory;
import twitter4j.conf.ConfigurationBuilder;

public class TwitterSQL extends AbsNoSQL {
	Twitter twitter;

	public TwitterSQL() {
	}

	public DatabaseType getDatabaseType() {
		return DatabaseType.TWITTER;
	}

	@Override
	public String getDefaultSchemaName() {
		fLogger.fatal("Twitter database has nothing default schema...");
		return StringUtil.EMPTY_STRING;
	}

	protected DBRoot getMetaData(GettingResourceRequest request) {
		DBRoot root = getRoot();
		try {
			capturing = true;
			if (request.canceld()) {
				return null;
			}

			root = new DBRoot("Twitter");
			setRoot(root);
			root.setPropertyValue("This DB Resources updated at ", String
					.format("%tF %tT", new java.util.Date(),
							new java.util.Date()));
			root.setPropertyValue("PrivacyPolicy", twitter.getPrivacyPolicy());
			root.setPropertyValue("ScreenName", twitter.getScreenName());
			root.setPropertyValue("TermsOfService", twitter.getTermsOfService());
			root.setPropertyValue("Id", twitter.getId());
			root.setPropertyValue(TwitterColumnType.whomUserId.name(),
					twitter.getId());
			createSchemas(root);
			refreshProperties(root);
			// resetFollowers(_root);
		} catch (Throwable e) {
			e.printStackTrace();
			fLogger.error(e);
			return null;
		} finally {
			capturing = false;
		}
		return root;
	}

	public List<TwitterUser> getFollowers(Long whomId, Long limitCount)
			throws TwitterException, SQLException {
		ResultSet rs = getRs(TwitterSchemaType.Followers,
				TwitterTableType.FollowersIDs, null, null);
		List<TwitterUser> retList = new ArrayList<TwitterUser>();
		while (rs.next()) {
			Properties p = new Properties();

			for (TwitterColumnType column : TwitterColumnType.USER_LIST) {
				p.setProperty(column.name(),
						StringUtil.nvl(rs.getString(column.name())));
			}
			TwitterUser userRes = new TwitterUser(null);
			userRes.setProperties(p);
			userRes.setName(p.getProperty(TwitterColumnType.name.name()));
			userRes.setComment(p.getProperty(TwitterColumnType.screenName
					.name()));
			retList.add(userRes);
		}
		return retList;
	}

	private void resetFollowers(DBRoot root) throws TwitterException,
			SQLException {
		TwitterDBSchema schema = (TwitterDBSchema) root
				.getSchema(TwitterSchemaType.Followers.name());
		if (schema != null) {
			for (TwitterUser user : getFollowers(null, 6L)) {
				user.setParent(schema);
				schema.addUser(TwitterSchemaType.Followers, user);
			}
		}
	}

	public boolean refreshProperties(DBRoot root) throws TwitterException {
		TwitterSchemaType schemaType = TwitterSchemaType.Account;
		TwitterTableType[] tableTypes = new TwitterTableType[] {
				TwitterTableType.AccountSettings,
				TwitterTableType.AccountTotals };
		boolean ret = true;
		for (TwitterTableType tableType : tableTypes) {
			IDBTable table = root.getTable(schemaType.name(), tableType.name());
			if (table != null) {
				AccountSettings settings = twitter.getAccountSettings();
				Properties p = new Properties();
				List<TwitterColumnType> selectColumnList = TwitterColumnType
						.getColumnTypeListOf(schemaType, tableType);
				Object targetObject = null;
				if (tableType == TwitterTableType.AccountSettings) {
					targetObject = twitter.getAccountSettings();
				} else {
					targetObject = twitter.getAccountTotals();
				}
				for (int i = 0; i < selectColumnList.size(); i++) {
					TwitterColumnType selectColumn = selectColumnList.get(i);
					try {
						String key = selectColumn + "/"
								+ targetObject.getClass();
						Method method = methodCacheMap.get(key);
						if (method == null) {
							method = targetObject.getClass().getDeclaredMethod(
									selectColumn.getValueMethod());
							method.setAccessible(true);
							methodCacheMap.put(key, method);
						}
						switch (selectColumn.getColumnType()) {
						case Types.DATE:
							p.setProperty(selectColumn.name(), String.format(
									rsUtil.getFormatBuildDate(),
									method.invoke(targetObject)));
							break;
						default:
							p.setProperty(selectColumn.name(),
									String.valueOf(method.invoke(targetObject)));
							break;
						}
					} catch (Exception e) {
						ret = false;
						e.printStackTrace();
					}
				}
				table.setProperties(p);
			}
		}
		return ret;
	}

	@Override
	public Set<String> getTables(String name) {
		try {
			TwitterSchemaType schemaType = TwitterSchemaType.valueOf(name);
			Set<String> retSet = new LinkedHashSet<String>();
			for (TwitterTableType tableType : TwitterTableType
					.getTableTypeListOf(schemaType)) {
				retSet.add(tableType.getTableName());
			}
			return retSet;
		} catch (Exception e) {
			return Collections.EMPTY_SET;
		}
	}

	public java.util.Set<String> getSchemas() {
		Set<String> retSet = new LinkedHashSet<String>();
		for (TwitterSchemaType schemaType : TwitterSchemaType.values()) {
			retSet.add(schemaType.getSchemaName());
		}
		return retSet;
	};

	private void createSchemas(DBRoot root) {
		for (TwitterSchemaType schemaType : TwitterSchemaType.values()) {
			TwitterDBSchema schema = new TwitterDBSchema(root, schemaType);
			root.putResource(schema.getName(), schema);
			for (TwitterTableType tableType : TwitterTableType
					.getTableTypeListOf(schemaType)) {
				TwitterDBTable table = new TwitterDBTable(schema, tableType);
				table.setTableType(TableType.TABLE);
				schema.putTable(table);

				for (TwitterColumnType columnType : TwitterColumnType
						.getColumnTypeListOf(schemaType, tableType)) {
					IDBColumn column = createColumn(table, columnType);
				}
			}
		}
	}

	private IDBColumn createColumn(IDBTable table, TwitterColumnType columnType) {
		TwitterDBColumn column = new TwitterDBColumn(table, columnType);
		column.setName(columnType.name());
		switch (columnType.getColumnType()) {
		case Types.VARCHAR:
			if (column.getName().equals(TwitterColumnType.id.name())) {
				column.setSize(128);
			} else if (column.getName().endsWith("Color")) {
				column.setSize(32);
			} else {
				column.setSize(1024);
			}
			break;
		case Types.LONGVARCHAR:
		case Types.LONGNVARCHAR:
			column.setSize(4000);
		case Types.INTEGER:
		case Types.NUMERIC:
			column.setSize(30);
			break;
		case Types.DOUBLE:
			column.setSize(30);
			column.setDecimalDigits(3);
			break;
		default:
			break;
		}
		TwitterTableType tableType = TwitterTableType.valueOf(table.getName());
		column.setConditionParameter(columnType.getSearchableType(tableType));
		column.setOnClickable(columnType.isOnClickable());
		column.setPKey(columnType.isPrimaryKey());
		column.setDataType(SQLDataType.parse(columnType.getColumnType()));
		column.setDataTypeString(columnType.getDataTypeString());
		table.putResource(column.getName(), column);
		return column;
	}

	public ResultSetDataHolder2 getRdh(TwitterSchemaType schemaType,
			TwitterTableType tableType, Map<String, Object> params)
			throws TwitterException, SQLException {
		return getRdh(schemaType, tableType, null, params);
	}

	public ResultSetDataHolder2 getRdh(TwitterSchemaType schemaType,
			TwitterTableType tableType,
			List<TwitterColumnType> selectColumnList, Map<String, Object> params)
			throws TwitterException, SQLException {

		return rsUtil.RS2RDH(DatabaseType.TWITTER,
				getRs(schemaType, tableType, selectColumnList, params), true);
	}

	public ResultSet getRs(TwitterSchemaType schemaType,
			TwitterTableType tableType,
			List<TwitterColumnType> selectColumnList, Map<String, Object> params)
			throws TwitterException, SQLException {
		return new TwitterResultSet(twitter, schemaType, tableType,
				selectColumnList, params);
	}

	private Map<String, Method> methodCacheMap = new HashMap<String, Method>();

	private String[] selectColumnsOf(Object valueObject,
			List<TwitterColumnType> selectColumnList) {
		String[] columns2 = new String[selectColumnList.size()];
		Method method = null;
		for (int i = 0; i < selectColumnList.size(); i++) {
			TwitterColumnType selectColumn = selectColumnList.get(i);
			try {
				String key = selectColumn + "/" + valueObject.getClass();
				method = methodCacheMap.get(key);
				if (method == null) {
					method = valueObject.getClass().getDeclaredMethod(
							selectColumn.getValueMethod());
					method.setAccessible(true);
					methodCacheMap.put(key, method);
				}
				switch (selectColumn.getColumnType()) {
				case Types.DATE:
					columns2[i] = String.format(rsUtil.getFormatBuildDate(),
							method.invoke(valueObject));
					break;
				default:
					columns2[i] = String.valueOf(method.invoke(valueObject));
					break;
				}
			} catch (Exception e) {
				System.err.println("================================");
				System.out.println("selectColumn=" + selectColumn);
				System.out.println("method=" + method);
				e.printStackTrace();
			}
		}
		return columns2;
	}

	public void init(ConnectionProperties connectionProperties)
			throws SQLException {
		try {
			ConfigurationBuilder cb = new ConfigurationBuilder();
			cb.setDebugEnabled(true)
					.setOAuthConsumerKey(
							Messages.getString("TwitterSQL.ConsumerKey")) //$NON-NLS-1$
					.setOAuthConsumerSecret(
							Messages.getString("TwitterSQL.ConsumerSecret")) //$NON-NLS-1$
					.setOAuthAccessToken(
							connectionProperties.getAccessToken().getToken())
					.setOAuthAccessTokenSecret(
							connectionProperties.getAccessToken()
									.getTokenSecret());
			TwitterFactory tf = new TwitterFactory(cb.build());
			twitter = tf.getInstance();
			connectionProperties.setConnected(true);
		} catch (Exception e) {
			if (e.getMessage() == null) {
				Throwable ee = e.getCause();
				if (ee == null) {
					throw new SQLException(e);
				} else {
					throw new SQLException(ee.toString(), e);
				}
			} else {
				throw new SQLException(e.getMessage(), e);
			}
		}
	}

	@Override
	public boolean canDoOperation(SQLOperationType operation) {
		switch (operation) {
		case GET_DDL:
			return false;
		case ROLLBACK:
			return false;
		}
		return super.canDoOperation(operation);
	}

	@Override
	public boolean doOperation(SQLOperationType operation, Request request)
			throws SQLException {
		TransactionRequest treq = null;
		if (request instanceof TransactionRequest) {
			treq = (TransactionRequest) request;

		}
		// DominoTransactionRequest dreq = null;
		// if (request instanceof DominoTransactionRequest) {
		// dreq = (DominoTransactionRequest) request;
		// System.out.println("domino accept DominoTransactionRequest ["
		// + operation + "] dreq[" + dreq + "] ");
		// }
		// ExecutorService ex = null;
		boolean retCode = true;
		System.out.println("twitter accept request [" + operation + "] req["
				+ request + "] ");
		if (SQLOperationType.CONNECT == operation) {
			ConnectionProperties prop = request.getConnectionProperties();
			return connect(prop);
		} else if (SQLOperationType.CLOSE == operation) {
			ConnectionProperties prop = request.getConnectionProperties();
			return close();
		}
		long time = System.currentTimeMillis();

		try {
			switch (operation) {
			case RESOURCE_MIRRORING:
				MirroringRequest mirroring_request = (MirroringRequest) request;
				retCode = createMirroredTableTo(mirroring_request);
				break;
			case COUNT:
				treq.setResultCount(count(treq.getIDBTable()));
				break;
			case RESOURCE_CAPTION:
				getMetaData((GettingResourceRequest) request);
				break;
			case EXPLAIN_PLAN:
				break;
			case SELECT_SESSION:
				break;
			case SELECT_LOCK:
				break;
			case PREPARED_EXECUTE_QUERY:
				notifyExecute(SQLExecutionStatus.BEFORE_EXECUTE);
				TwitterDBTable table = (TwitterDBTable) treq.getIDBTable();
				TwitterSchemaType schemaType = ((TwitterDBSchema) table
						.getParent()).getSchemaType();
				switch (schemaType) {
				case Account:
				case LocalTrends:
				case Timeline:
				case DirectMessage:
				case Followers:
				case User:
					treq.setRDH(getRdh(schemaType, table.getTwitterTableType(),
							cnvList(treq.getSelectColumnList()),
							treq.getFirstParams()));
					// treq.setRDH(getFollowers(-1, null));
					break;
				default:
					ResultSetDataHolder2 rdh = new ResultSetDataHolder2(
							new String[] { "message" }, null,
							DatabaseType.UNKNOWN);
					rdh.addRow(new String[] { String.format(
							"Schema[%s] is not support yet.", schemaType.name()) });
					treq.setRDH(rdh);
					break;
				// treq.setRDH(getFollowers(-1, null));
				}
				break;
			case PREPARED_EXECUTE:
				break;
			default:
				return super.doOperation(operation, request);
			}
		} catch (Exception e) {
			e.printStackTrace();
			// notifyExecute(SQLExecutionStatus.EXCEPTION, e.getMessage());
			// throw cnvSQLException(e);
		}
		time = System.currentTimeMillis() - time;
		if (treq == null) {
			notifyExecute(SQLExecutionStatus.AFTER_EXECUTE, "0",
					String.valueOf(time));
		} else {
			notifyExecute(SQLExecutionStatus.AFTER_EXECUTE,
					String.valueOf(treq.getReturnedRowCount()),
					String.valueOf(time));
		}
		return retCode;

	}

	private List<TwitterColumnType> cnvList(List<String> selectColumnList) {
		if (selectColumnList == null || selectColumnList.size() == 0) {
			return null;
		}
		List<TwitterColumnType> retList = new ArrayList<TwitterColumnType>();
		for (String columnName : selectColumnList) {
			TwitterColumnType type = TwitterColumnType.valueOf(columnName);
			if (type != null) {
				retList.add(type);
			}
		}
		return retList;
	}

	public long count(IDBTable table) throws SQLException {
		if (table == null) {
			return 0L;
		}
		long sum = 0;
		TwitterDBSchema schema = (TwitterDBSchema) table.getParent();
		TwitterSchemaType schemaType = schema.getSchemaType();
		TwitterTableType tableType = ((TwitterDBTable) table)
				.getTwitterTableType();
		try {
			switch (schemaType) {
			case Account:
				return 1;
			case Timeline:
				switch (tableType) {
				case HomeTimeline:
					return 20;
				case Mentions:
					return twitter.getMentions().size();
				case PublicTimeline:
					return 20;
				case RetweetedByMe:
					return twitter.getRetweetedByMe().size();
				case RetweetedByUser:
				case RetweetedToUser:
					return 0;
				case RetweetedToMe:
					return twitter.getRetweetedToMe().size();
				case RetweetsOfMe:
					return twitter.getRetweetsOfMe().size();
				case UserTimeline:
					return twitter.getUserTimeline().size();
				}
				break;

			case Followers:
				long whomUserId = twitter.getId();
				long lc = -1;
				IDs ids = null;
				out: do {
					ids = twitter.getFollowersIDs(whomUserId, lc);
					sum += ids.getIDs().length;
				} while ((lc = ids.getNextCursor()) != 0);

				return sum;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return 0;
	}

	public ResultSetDataHolder2 counts(IDBTable[] tables) throws SQLException {
		ResultSetDataHolder2 rdh = new ResultSetDataHolder2(
				new String[] { "CNT" }, null, DatabaseType.TWITTER); //$NON-NLS-1$
		for (IDBTable table : tables) {
			rdh.addRow(new String[] { String.valueOf(count(table)) });
		}

		return rdh;
	}

	@Override
	public ResultSet getAllData(IDBTable table) throws SQLException {
		IDBResource p = table.getParent();
		try {
			TwitterSchemaType schemaType = null;
			TwitterTableType tableType = null;
			if (p instanceof TwitterDBSchema) {
				schemaType = ((TwitterDBSchema) table.getParent())
						.getSchemaType();
				tableType = ((TwitterDBTable) table).getTwitterTableType();
			} else {
				schemaType = TwitterSchemaType.valueOf(p.getName());
				tableType = TwitterTableType.valueOf(table.getName());
			}
			return getRs(schemaType, tableType, null, null);
		} catch (TwitterException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ResultSetDataHolder2 getAllData2(String schema, String table,
			Request request) throws SQLException {
		TwitterDBSchema d_schema = new TwitterDBSchema(null,
				TwitterSchemaType.valueOf(schema));
		TwitterDBTable d_table = new TwitterDBTable(d_schema,
				TwitterTableType.valueOf(table));
		ResultSet rs = getAllData(d_table);
		if (rs != null) {
			ResultSetDataHolder2 rdh2 = rsUtil.RS2RDH(DatabaseType.TWITTER, rs,
					true);
			if (rdh2 != null) {
				rdh2.setTableNameE(table);
			}
			return rdh2;
		}
		return null;
	}

	@Override
	public boolean connect(ConnectionProperties properties) throws SQLException {
		_info = properties;

		init(properties);
		return true;
	}

	@Override
	public boolean close() throws SQLException {
		_info.setConnected(false);
		_info = null;

		return true;
	}

	@Override
	public void setTableColumns(String schema, IDBTable table)
			throws SQLException {
		TwitterSchemaType schemaType = TwitterSchemaType.valueOf(schema);
		TwitterTableType tableType = TwitterTableType.valueOf(table.getName());
		for (TwitterColumnType columnType : TwitterColumnType
				.getColumnTypeListOf(schemaType, tableType)) {
			IDBColumn column = createColumn(table, columnType);
		}
	}

	@Override
	public boolean supportResultSet() {
		return true;
	}

}
