/******************************************************************************
 * Product: Adempiere ERP & CRM Smart Business Solution                       *
 * Copyright (C) 2010 Heng Sin Low                							  *
 * This program is free software; you can redistribute it and/or modify it    *
 * under the terms version 2 of the GNU General Public License as published   *
 * by the Free Software Foundation. This program is distributed in the hope   *
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
 * See the GNU General Public License for more details.                       *
 * You should have received a copy of the GNU General Public License along    *
 * with this program; if not, write to the Free Software Foundation, Inc.,    *
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
 *****************************************************************************/
package org.adempiere.process;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.logging.Level;

import org.adempiere.exceptions.DBException;
import org.compiere.model.MColumn;
import org.compiere.model.MProcessPara;
import org.compiere.model.MTable;
import org.compiere.model.M_Element;
import org.compiere.model.PO;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.AdempiereUserError;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.DisplayType;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.compiere.util.Trx;
import org.compiere.util.Util;
import org.compiere.util.ValueNamePair;

/**
 * Add UUID column (tableName_UU) to table and update existing records with new UUID.<br/>
 * Warning: this process is only safe to run if it have exclusive access to database.
 * @author hengsin
 *
 */
@org.adempiere.base.annotation.Process
public class UUIDGenerator extends SvrProcess {

	private String tableName;

	private boolean isFillUUID = false;

	private boolean isClearUUID = false;

	/**	Logger							*/
	private static final CLogger log = CLogger.getCLogger(UUIDGenerator.class);

	/**
	 * @see org.compiere.process.SvrProcess#prepare()
	 */
	@Override
	protected void prepare() {
		for(ProcessInfoParameter param : getParameter()) {
			if (param.getParameter() == null)
				;
			else if (param.getParameterName().equals("TableName"))
				tableName = param.getParameter().toString();
			else if (param.getParameterName().equals("IsFillUUID"))
				isFillUUID = param.getParameterAsBoolean();
			else if (param.getParameterName().equals("IsClearUUID"))
				isClearUUID = param.getParameterAsBoolean();
			else
				MProcessPara.validateUnknownParameter(getProcessInfo().getAD_Process_ID(), param);
		}
	}

	/**
	 * @see org.compiere.process.SvrProcess#doIt()
	 */
	@Override
	protected String doIt() throws Exception {
		if (tableName == null || tableName.trim().length() == 0)
			tableName = "%";
		else
			tableName = tableName.trim();
		String sql = "SELECT AD_Table_ID, TableName FROM AD_Table WHERE TableName LIKE ? AND IsView = 'N' AND IsActive='Y' ORDER BY TableName";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int count = 0;
		try  {
			stmt = DB.prepareStatement(sql, null);
			stmt.setString(1, tableName);
			rs = stmt.executeQuery();
			while(rs.next()) {
				int AD_Table_ID = rs.getInt(1);
				String cTableName = rs.getString(2);
				statusUpdate(Msg.getMsg(getCtx(), "Processing") + " " + cTableName);

				String columnName = PO.getUUIDColumnName(cTableName);
				int AD_Column_ID = DB.getSQLValue(null, "SELECT AD_Column_ID FROM AD_Column Where AD_Table_ID = ? AND ColumnName = ?", AD_Table_ID, columnName);
				if (AD_Column_ID <= 0) {
					if (log.isLoggable(Level.INFO)) log.info("Adding UUID to " + cTableName);
					count++;
					//create column
					MColumn mColumn = new MColumn(getCtx(), 0, null);
					mColumn.setAD_Table_ID(AD_Table_ID);
					int AD_Element_ID = DB.getSQLValue(null, "SELECT AD_Element_ID FROM AD_Element WHERE ColumnName=?",columnName);
					if (AD_Element_ID <= 0) {
						M_Element adElement = new M_Element(getCtx(), 0, null);
						adElement.setColumnName(columnName);
						adElement.setName(columnName);
						adElement.setPrintName(columnName);
						adElement.setEntityType("U");
						adElement.saveEx();
						AD_Element_ID = adElement.getAD_Element_ID();
					}
					mColumn.setAD_Element_ID(AD_Element_ID);
					mColumn.setColumnName(columnName);
					mColumn.setAD_Reference_ID(DisplayType.UUID);
					mColumn.setEntityType("U");
					mColumn.setFieldLength(36);
					mColumn.setName(columnName);
					mColumn.setVersion(Env.ONE);
					mColumn.saveEx();
					AD_Column_ID = mColumn.getAD_Column_ID();

					syncColumn(mColumn);

					//update db
					if (isFillUUID) {
						String msg = updateUUID(mColumn, null);
						if (! Util.isEmpty(msg)) {
							addBufferLog(0, null, null, msg, 0, 0);
						}
					}
				} else {
					MColumn column = MColumn.get(AD_Column_ID);
					if (column.isActive()) {
						if (isFillUUID) {
							MColumn mColumn = MColumn.get(getCtx(), AD_Column_ID);
							String msg = updateUUID(mColumn, null);
							if (! Util.isEmpty(msg)) {
								addBufferLog(0, null, null, msg, 0, 0);
							}
						}
					} else {
						if (isClearUUID) {
							StringBuilder sqlclear = new StringBuilder("UPDATE ")
									.append(cTableName)
									.append(" SET ")
									.append(columnName)
									.append("=NULL WHERE ")
									.append(columnName)
									.append(" IS NOT NULL");
							int cnt = DB.executeUpdateEx(sqlclear.toString(), get_TrxName());
							if (cnt > 0) {
								String msg = cnt + " UUID cleared from table " + cTableName;
								addBufferLog(0, null, null, msg, 0, 0);
							}
						}
					}
				}
			}
		} finally {
			DB.close(rs,stmt);
		}
		StringBuilder msgreturn = new StringBuilder().append(count).append(" table altered");
		return msgreturn.toString();
	}

	/**
	 * Fill column with new uuid value (if it is null)
	 * @param column
	 * @param trxName
	 * @return message
	 */
	public static String updateUUID(MColumn column, String trxName) {
		MTable table = (MTable) column.getAD_Table();
		if (table.getTableName().startsWith("T_")) {
			// don't update UUID for temporary tables
			return "";
		}
		int AD_Column_ID = 0;
		StringBuilder sql = new StringBuilder("SELECT ");
		String keyColumn = null;
		String[] compositeKeys = table.getKeyColumns();
		if (compositeKeys == null || compositeKeys.length == 1) {
			keyColumn = compositeKeys[0];
			AD_Column_ID = table.getColumn(keyColumn).getAD_Column_ID();
			compositeKeys = null;
		}
		if ((compositeKeys == null || compositeKeys.length == 0) && keyColumn == null) {
			// TODO: Update using rowid for oracle or ctid for postgresql
			log.warning("Cannot update orphan table " + table.getTableName() + " (not ID neither parents)");
			return "";
		}
		if (compositeKeys == null) {
			sql.append(keyColumn);
		} else {
			for(String s : compositeKeys) {
				sql.append(s).append(",");
			}
			sql.deleteCharAt(sql.length()-1);
		}
		sql.append(" FROM ").append(table.getTableName());
		sql.append(" WHERE ").append(column.getColumnName()).append(" IS NULL ");
		StringBuilder updateSQL = new StringBuilder("UPDATE ");
		updateSQL.append(table.getTableName());
		updateSQL.append(" SET ");
		updateSQL.append(column.getColumnName());
		updateSQL.append("=? WHERE ");
		if (AD_Column_ID > 0) {
			updateSQL.append(keyColumn).append("=?");
		} else {
			for(String s : compositeKeys) {
				updateSQL.append(s).append("=? AND "); 
			}
			int length = updateSQL.length();
			updateSQL.delete(length-5, length); // delete last AND
		}
		
		boolean localTrx = false;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Trx trx = trxName != null ? Trx.get(trxName, false) : null;
		if (trx == null) {
			trx = Trx.get(Trx.createTrxName(), true);
			trx.setDisplayName(UUIDGenerator.class.getName()+"_updateUUID");
			localTrx = true;
		}
		String msg = "";
		try {				
			if (localTrx)
				trx.start();
			stmt = DB.prepareStatement(sql.toString(), trx.getTrxName());
			stmt.setFetchSize(100);
			rs = stmt.executeQuery();
			int no = 0;
			while (rs.next()) {
				if (AD_Column_ID > 0) {
					int recordId = rs.getInt(1);
					// this line is to avoid users generating official UUIDs - comment it to do official migration script work
					if (recordId > MTable.MAX_OFFICIAL_ID) {
						UUID uuid = UUID.randomUUID();
						no += DB.executeUpdateEx(updateSQL.toString(),new Object[]{uuid.toString(), recordId}, trx.getTrxName());
					}
				} else {
					UUID uuid = UUID.randomUUID();
					List<Object> params = new ArrayList<Object>();
					params.add(uuid.toString());
					for (String s : compositeKeys) {
						params.add(rs.getObject(s));
					}
					no += DB.executeUpdateEx(updateSQL.toString(),params.toArray(),trx.getTrxName());
				}
			}
			if (no > 0) {
				msg = no + " UUID assigned for table " + table.getTableName();
			}
			if (localTrx) {
				trx.commit(true);
			}
		} catch (SQLException e) {
			if (localTrx) {
				trx.rollback();
			}
			throw new DBException(e);
		} finally {
			DB.close(rs, stmt);
			if (localTrx) {
				trx.close();
			}
		}
		return msg;
	}

	/**
	 * sync column with DB
	 * @param column
	 */
	private void syncColumn(MColumn column) {
		//	Find Column in Database
		Connection conn = null;
		try {
			conn = DB.getConnection();
			DatabaseMetaData md = conn.getMetaData();
			String catalog = DB.getDatabase().getCatalog();
			String schema = DB.getDatabase().getSchema();
			MTable table = (MTable) column.getAD_Table();
			String tableName = table.getTableName();
			if (md.storesUpperCaseIdentifiers())
			{
				tableName = tableName.toUpperCase();
			}
			else if (md.storesLowerCaseIdentifiers())
			{
				tableName = tableName.toLowerCase();
			}
			int noColumns = 0;
			StringBuilder sql = null;
			//
			ResultSet rs = null;
			try
			{
				rs = md.getColumns(catalog, schema, tableName, null);
				while (rs.next())
				{
					noColumns++;
					StringBuilder columnName = new StringBuilder().append(rs.getString ("COLUMN_NAME"));
					if (!columnName.toString().equalsIgnoreCase(column.getColumnName()))
						continue;

					//	update existing column
					boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
					sql = new StringBuilder().append(column.getSQLModify(table, column.isMandatory() != notNull));
					break;
				}
			}
			finally
			{
				DB.close(rs);
			}

			//	No Table
			if (noColumns == 0)
				sql = new StringBuilder(table.getSQLCreate ());
			//	No existing column
			else if (sql == null)
				sql = new StringBuilder(column.getSQLAdd(table));

			int no = 0;			
			if (sql.indexOf(DB.SQLSTATEMENT_SEPARATOR) == -1)
			{
				no = DB.executeUpdate(sql.toString(), false, null);
				addLog (0, null, new BigDecimal(no), sql.toString());
			}
			else
			{
				String statements[] = sql.toString().split(DB.SQLSTATEMENT_SEPARATOR);
				for (int i = 0; i < statements.length; i++)
				{
					int count = DB.executeUpdate(statements[i], false, null);
					addLog (0, null, new BigDecimal(count), statements[i]);
					no += count;
				}
			}

			if (no == -1)
			{
				StringBuilder msg = new StringBuilder("@Error@ ");
				ValueNamePair pp = CLogger.retrieveError();
				if (pp != null)
					msg = new StringBuilder(pp.getName()).append(" - ");
				msg.append(sql);
				throw new AdempiereUserError (msg.toString());
			}
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {}
			}
		}
	}

}