/*
 * This file is part of Nuts Framework.
 * Copyright (C) 2009 Nuts Develop Team.
 *
 * Nuts Framework is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License any later version.
 * 
 * Nuts Framework 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 Nuts Framework. If not, see <http://www.gnu.org/licenses/>.
 */
package nuts.exts.struts2.actions;

import java.io.BufferedReader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import nuts.core.lang.ExceptionUtils;
import nuts.core.lang.StringUtils;
import nuts.core.sql.SqlUtils;


/**
 */
@SuppressWarnings("serial")
public class SqlExecuteAction extends CommonAction {

	private DataSource dataSource;
	private String commenter = "--";
	private String delimiter = ";";
	private boolean ignoreError = false;
	private String sql;
	private List<Result> results;
	private int fetchLimit = 100;
	
	/**
	 */
	public static class Result {
		private String sql;
		private int updateCount;
		private String error;
		private List<String[]> resultSet;
		
		/**
		 * @param sql sql
		 */
		public Result(String sql) {
			this.sql = sql;
		}
		/**
		 * @return the sql
		 */
		public String getSql() {
			return sql;
		}
		/**
		 * @param sql the sql to set
		 */
		public void setSql(String sql) {
			this.sql = sql;
		}
		/**
		 * @return the updateCount
		 */
		public int getUpdateCount() {
			return updateCount;
		}
		/**
		 * @param updateCount the updateCount to set
		 */
		public void setUpdateCount(int updateCount) {
			this.updateCount = updateCount;
		}
		/**
		 * @return the error
		 */
		public String getError() {
			return error;
		}
		/**
		 * @param error the error to set
		 */
		public void setError(String error) {
			this.error = error;
		}
		/**
		 * @return the resultSet
		 */
		public List<String[]> getResultSet() {
			return resultSet;
		}
		/**
		 * @param resultSet the resultSet to set
		 */
		public void setResultSet(List<String[]> resultSet) {
			this.resultSet = resultSet;
		}
	}

	/**
	 * @return the dataSource
	 */
	public DataSource getDataSource() {
		return dataSource;
	}

	/**
	 * @param dataSource the dataSource to set
	 */
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * @return the commenter
	 */
	public String getCommenter() {
		return commenter;
	}

	/**
	 * @param commenter the commenter to set
	 */
	public void setCommenter(String commenter) {
		this.commenter = commenter;
	}

	/**
	 * @return the delimiter
	 */
	public String getDelimiter() {
		return delimiter;
	}

	/**
	 * @param delimiter the delimiter to set
	 */
	public void setDelimiter(String delimiter) {
		this.delimiter = delimiter;
	}

	/**
	 * @return the ignoreError
	 */
	public boolean isIgnoreError() {
		return ignoreError;
	}

	/**
	 * @param ignoreError the ignoreError to set
	 */
	public void setIgnoreError(boolean ignoreError) {
		this.ignoreError = ignoreError;
	}

	/**
	 * @return the sql
	 */
	public String getSql() {
		return sql;
	}

	/**
	 * @param sql the sql to set
	 */
	public void setSql(String sql) {
		this.sql = StringUtils.stripToNull(sql);
	}

	/**
	 * @return the fetchLimit
	 */
	public int getFetchLimit() {
		return fetchLimit;
	}

	/**
	 * @param fetchLimit the fetchLimit to set
	 */
	public void setFetchLimit(int fetchLimit) {
		this.fetchLimit = fetchLimit;
	}

	/**
	 * @return the results
	 */
	public List<Result> getResults() {
		return results;
	}

	/**
	 * execute
	 * @return INPUT
	 * @throws Exception if an error occurs
	 */
	public String execute() throws Exception {
		String sql = removeComment(this.sql);
		
		if (StringUtils.isNotEmpty(sql)) {
			Connection con = getDataSource().getConnection();
			results = new ArrayList<Result>();

			String[] ss = sql.split(delimiter);
			for (String s : ss) {
				s = s.trim();
				if ("exit".equalsIgnoreCase(s)) {
				}
				else if ("commit".equalsIgnoreCase(s)) {
					Result r = new Result(s);
					results.add(r);
					try {
						con.commit();
					}
					catch (Exception e) {
						r.setError(ExceptionUtils.getStackTrace(e));
						if (!ignoreError) {
							break;
						}
					}
				}
				else if (s.length() > 0) {
					boolean e = execSql(con, s);
					if (!e && !ignoreError) {
						break;
					}
				}
			}

			SqlUtils.closeQuietly(con);
		}

		return INPUT;
	}

	private String removeComment(String sql) throws Exception {
		if (StringUtils.isNotEmpty(sql)) {
			BufferedReader br = new BufferedReader(new StringReader(sql));
	
			StringBuilder sb = new StringBuilder();
			String line;
			while ((line = br.readLine()) != null) {
				String lt = line.trim();
				if (lt.length() > 0 && !lt.startsWith(commenter)) {
					sb.append(line).append("\r\n");
				}
			}
			sql = sb.toString();
		}
		return sql;
	}
	
	private boolean execSql(Connection con, String sql) throws Exception {
		Result r = new Result(sql);
		results.add(r);

		sql = StringUtils.replaceChars(sql, "\r\n\t", " ");
		Statement st = null;
		try {
			st = con.createStatement();
			st.execute(sql);
			
			ResultSet rs = st.getResultSet();
			if (rs != null) {
				List<String[]> list = new ArrayList<String[]>();
				r.setResultSet(list);

				String[] row;

				ResultSetMetaData meta = rs.getMetaData();
				int cnt = meta.getColumnCount();

				row = new String[cnt];
				for (int i = 1; i <= cnt; i++) {
					row[i - 1] = meta.getColumnLabel(i);
				}
				list.add(row);
				
				while (rs.next() && (fetchLimit < 1 || list.size() - 1 < fetchLimit)) {
					row = new String[cnt];
					for (int i = 1; i <= cnt; i++) {
						int type = meta.getColumnType(i);
						if (SqlUtils.isBinaryType(type)) {
							byte[] bs = rs.getBytes(i);
							if (bs != null) {
								row[i - 1] = "(" + bs.length + "B)";
							}
						}
						else {
							row[i - 1] = rs.getString(i);
						}
					}
					list.add(row);
				}
			}
			else {
				r.setUpdateCount(st.getUpdateCount());
			}
		}
		catch (Exception e) {
			r.setError(e.getClass().getName() 
				+ ": " + e.getMessage() + "\r\n"
				+ ExceptionUtils.getFullStackTrace(e));
			return false;
		}
		finally {
			SqlUtils.closeQuietly(st);
		}
		return true;
	}
}
