package hiro.yoshioka.sql;

import hiro.yoshioka.ast.sql.oracle.WolfSQLParserConstants;
import hiro.yoshioka.sdh.ResultSetDataHolder;
import hiro.yoshioka.sdh2.ResultSetDataHolder2;
import hiro.yoshioka.sql.engine.GettingResourceRequest;
import hiro.yoshioka.sql.engine.SQLOperationType;
import hiro.yoshioka.sql.resource.DBColumn;
import hiro.yoshioka.sql.resource.DBRoot;
import hiro.yoshioka.sql.resource.DBSchema;
import hiro.yoshioka.sql.resource.DBTable;
import hiro.yoshioka.sql.resource.IDBSchema;
import hiro.yoshioka.sql.resource.IDBTable;
import hiro.yoshioka.util.SQLDataType;
import hiro.yoshioka.util.SQLUtil;
import hiro.yoshioka.util.StringUtil;

import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLiteSQL extends AbsTransactionSQL {
	Pattern SIZE_PATTERN = Pattern.compile(".*[(](\\d+)([.](\\d+))?[)].*");
	Pattern DBNAME_PATTERN = Pattern.compile(".*[/](.*)(\\.db)?");

	public static String getSuggestURL() {
		return "jdbc:sqlite:<AbsoluteFilePath>/<dbName>";
	}

	protected SQLiteSQL(Driver ds) {
		super(ds);
	}

	@Override
	public boolean commit() {
		fLogger.trace("now autoCommit=true");
		return true;
	}

	@Override
	public boolean rollback() {
		fLogger.trace("now autoCommit=true");
		return true;
	}

	@Override
	protected DBRoot getMetaData(GettingResourceRequest request) {
		DBRoot root = null;
		try {
			root = super.getMetaData(request);
			Matcher m = DBNAME_PATTERN.matcher(_info.getURLString());
			if (m.find()) {
				root.setName(m.group(1));
			} else {
				root.setName("SQLite");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// try {
			// // for [SQLITE_BUSY] The database file is locked
			// _extra_con.rollback();
			// } catch (SQLException e) {
			// }
		}
		return root;
	}

	protected String getSupportToken() {
		StringBuffer buf = new StringBuffer();
		Pattern p = Pattern.compile("\"(\\w+)\"");
		String[] str = WolfSQLParserConstants.tokenImage;
		for (int i = 0; i < str.length; i++) {
			Matcher m = p.matcher(str[i]);
			if (m.matches()) {
				buf.append(m.group(1)).append(",");
			}
		}
		if (buf.length() > 0) {
			buf.setLength(buf.length() - 1);
		}
		return buf.toString();
	}

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

	protected void createDBProcedureDef(GettingResourceRequest request)
			throws SQLException {
	}

	protected void createDBTableDef(GettingResourceRequest request)
			throws SQLException {
		ResultSetDataHolder rdh = null;
		DatabaseMetaData meta = _extra_con.getMetaData();

		rdh = RS2RDH(meta.getTables(null, null, null, null), true, null, null);
		DBRoot root = getRoot();
		for (int i = 0; i < rdh.getRowCount(); i++) {
			String tableSchem = rdh.getStringData(i, "TABLE_SCHEM");
			String tableName = rdh.getStringData(i, "TABLE_NAME");
			String type = rdh.getStringData(i, "TABLE_TYPE").toUpperCase();
			if ("INDEX".equalsIgnoreCase(type)) {
				continue;
			}

			IDBSchema mschema = (IDBSchema) root.getResource(tableSchem);
			if (mschema == null) {
				mschema = new DBSchema(root);
				mschema.setName(tableSchem);
				root.putResource(mschema.getName(), mschema);
			}
			DBTable dbTable = new DBTable(mschema);
			dbTable.setName(tableName);
			dbTable.setTableType(type);
			if (dbTable.isTable()) {
				setTableColumns(mschema.getName(), dbTable);
			}
			mschema.putTable(dbTable);

			setResourceProperties(dbTable, i, rdh);

		}
	}

	@Override
	public void setTableColumns(String schema, IDBTable table)
			throws SQLException {
		if ("sqlite_sequence".equals(table.getName())) {
			DBColumn col = new DBColumn(table);
			col.setName("name");
			col.setNotNull(true);
			col.setDataTypeString(SQLDataType.VARCHAR.name());
			col.setDataType(SQLDataType.VARCHAR);
			col.setSize(256);
			col.setDecimalDigits(0);
			table.putResource(col.getName(), col);
			col = new DBColumn(table);
			col.setName("seq");
			col.setNotNull(true);
			col.setDataTypeString(SQLDataType.INTEGER.name());
			col.setDataType(SQLDataType.INTEGER);
			col.setSize(38);
			col.setDecimalDigits(38);
			table.putResource(col.getName(), col);

			return;
		}
		List<String> pkey = null;
		if (table.isSynonym()) {
			if (!StringUtil.isEmpty(table.getComment())) {
				String[] spl = table.getComment().split("[.]");
				if (spl.length == 2) {
					pkey = getTablePrimaryKeys(null, spl[0], spl[1]);
				}
			}
		} else {
			pkey = getTablePrimaryKeys(null, schema, table.getName());
		}

		ResultSet rs = null;
		Statement st = null;
		try {
			DatabaseMetaData meta = _extra_con.getMetaData();
			rs = meta.getColumns(null, null, table.getName(), "%");
			while (rs.next()) {
				DBColumn col = new DBColumn(table);
				col.setName(rs.getString(4));
				col.setNullable((short) rs.getInt(11));
				if (pkey != null) {
					col.setPKey(pkey);
				}
				col.setDataTypeString(rs.getString(6));
				SQLDataType ty = SQLDataType.parse(col.getDataTypeString());
				col.setDataType(SQLDataType.parse(Types.VARCHAR));
				// if (ty != null) {
				// col.setDataType(ty.getType());
				// }

				col.setSize(0);
				col.setDecimalDigits(0);
				try {
					String typeName = col.getDataTypeString();
					Matcher m = SIZE_PATTERN.matcher(typeName);
					if (m.find()) {
						if (!StringUtil.isEmpty(m.group(1))) {
							col.setSize(Integer.parseInt(m.group(1)));
						}
						if (!StringUtil.isEmpty(m.group(3))) {
							col.setDecimalDigits(Integer.parseInt(m.group(3)));
						}
						col.setDataTypeString(typeName.replaceAll("(.*)[(].*",
								"$1"));
					}
				} catch (Exception e) {
				}

				col.setMaxColumnNameLength(SQLUtil
						.getMaxColumnNameBytes(_extra_con));

				table.putResource(col.getName(), col);
			}
		} catch (Exception e) {
			fLogger.warn("Error [" + schema + "] table[" + table + "]", e);
			return;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (st != null) {
				st.close();
			}
		}
	}

	@Override
	protected boolean setTableText(GettingResourceRequest request)
			throws SQLException {
		boolean retCode = true;
		ResultSetDataHolder rdh = null;

		if (request.targetType.isOnlySelectedResouce()) {
			request.beginTask(String.format("SCHEMA[%s] GET_DDL ",
					request.selectionResource.getName()), 3);
			if (request.selectionResource instanceof IDBTable) {
				IDBTable tbl = (IDBTable) request.selectionResource;

				System.out.println("table??? =" + tbl.getTableType().name());

				rdh = executePrepareQuery(
						_extra_con,
						"select sql from sqlite_master  where type='table' and upper(tbl_name) = ? ",
						new String[] { tbl.getUName() });
				if (rdh.getRowCount() > 0) {
					System.err.println(" ret==== trueeeeee" + rdh);
					tbl.setText(rdh.getStringRecordRow(0)[1].getString());
				} else {
					System.err.println(" ret==== false");
					retCode = false;
				}
			}
			return retCode;
		}
		fLogger.fatal("not support yet");
		return retCode;
	}

	@Override
	protected void getTrigger(GettingResourceRequest request)
			throws SQLException {
		fLogger.fatal("Not support yet...");
	}

	@Override
	public ResultSetDataHolder2 getSessionInfo() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	//
	// public boolean doInsertFromRs(ResultSet all_rs, String
	// mappingToSchemaName,
	// MirroringRequest.MirroringTableInfo tableInfo) throws SQLException {
	// ResultSetMetaData rs_meta = all_rs.getMetaData();
	//
	// if (rs_meta == null || rs_meta.getColumnCount() == 0) {
	// return false;
	// }
	// PreparedStatement st = null;
	// StringBuilder buf = new StringBuilder();
	// StringBuilder value_buf = new StringBuilder();
	// buf.append("INSERT INTO ");
	//
	// if (!StringUtil.isEmpty(mappingToSchemaName)) {
	// buf.append(mappingToSchemaName).append(".");
	// }
	// buf.append(tableInfo.name).append(" ");
	// buf.append("(");
	// for (int i = 1; i <= rs_meta.getColumnCount(); i++) {
	// if (i > 1) {
	// buf.append(",");
	// value_buf.append(",");
	// }
	// buf.append(rs_meta.getColumnName(i));
	// value_buf.append("?");
	// }
	// buf.append(")").append(StringUtil.LINE_SEPARATOR);
	// buf.append("VALUES (");
	// buf.append(value_buf);
	// buf.append(")");
	//
	// PreparedStatement statement = null;
	// try {
	// statement = getPrepareStatement(_con, buf.toString());
	//
	// while (all_rs.next()) {
	// try {
	// for (int i = 1; i <= rs_meta.getColumnCount(); i++) {
	// statement.setObject(i, all_rs.getObject(i));
	// }
	// statement.addBatch();
	// } finally {
	// if (st != null) {
	// st.close();
	// }
	// }
	// }
	// long time = System.currentTimeMillis();
	// notifyExecute(_con, SQLExecutionStatus.BEFORE_EXECUTE);
	// int[] ret = statement.executeBatch();
	// time = System.currentTimeMillis() - time;
	// notifyExecute(_con, SQLExecutionStatus.AFTER_EXECUTE,
	// String.valueOf(time));
	//
	// ResultSetDataHolder2 sdh = new ResultSetDataHolder2(new String[] {
	// "Returns", "Statement" }, null, getDatabaseType());
	// for (int i = 0; i < ret.length; i++) {
	// sdh.addRow(new String[] { String.valueOf(ret[i]),
	// buf.toString() });
	// }
	//
	// sdh.setWrapTime(time);
	// System.out.println("bat=" + sdh);
	// } catch (Exception e) {
	// e.printStackTrace();
	// // TODO: handle exception
	// } finally {
	// setTransactionTime(true);
	//
	// if (statement != null) {
	// statement.close();
	// }
	// }
	// try {
	// Thread.sleep(3000);
	// } catch (InterruptedException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }
	//
	// return true;
	// }

	public ResultSetDataHolder2 getLockInfo() throws SQLException {
		return null;
	}

	@Override
	protected void setComments(GettingResourceRequest request)
			throws SQLException {
		// TODO Auto-generated method stub
	}

	@Override
	protected void getSequence(GettingResourceRequest request)
			throws SQLException {
		fLogger.fatal("Not support yet...");

	}

	@Override
	public boolean existsTable(String schemaName, String tableName)
			throws SQLException {
		ResultSetDataHolder rdh = executePrepareQuery(
				"select count(*) AS CNT from sqlite_master  where type='table' and name=?",
				new String[] { tableName });
		return rdh.getIntDataDefaultZero(0, "CNT") > 0;
	}

	@Override
	protected boolean dropTable(String schemaName, IDBTable table,
			boolean cascade) throws SQLException {
		StringBuilder st = new StringBuilder();
		// drop table [if exists] [databasename.]tableName
		if (StringUtil.isEmpty(schemaName)) {
			st.append(String.format("DROP TABLE IF EXISTS %s", table.getName()));
		} else {
			st.append(String.format("DROP TABLE IF EXISTS %s.%s", schemaName,
					table.getName()));
		}

		executePrepare(st.toString(), EMPTY);
		return true;
	}
}