package hiro.yoshioka.sql;

import hiro.yoshioka.sdh.DatabaseType;
import hiro.yoshioka.sdh2.ResultSetDataHolder2;
import hiro.yoshioka.util.FileExtentionType;
import hiro.yoshioka.util.SQLUtil;
import hiro.yoshioka.util.StringUtil;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hslf.HSLFSlideShow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;

import twitter4j.Status;

public class ResultSetUtil {
	protected Log fLogger = LogFactory.getLog(getClass());
	public static final String BINARY = "<BINARY>";
	public static final String FORMAT_ALL = "%tF %<tT.%<tL";
	public static final String FORMAT_TIME = "%tF %<tT";
	public static final String FORMAT_DAY = "%tF";

	protected String formatBuildTimeStamp;
	protected String formatBuildDate;
	protected String connectionDisplayString;
	private boolean makeBlobData;

	public ResultSetUtil() {
		this(FORMAT_DAY, FORMAT_TIME);
	}

	public ResultSetUtil(String formatBuildDate, String formatBuildTimeStamp) {
		this.formatBuildDate = formatBuildDate;
		this.formatBuildTimeStamp = formatBuildTimeStamp;
	}

	public String getStringOf(Object o) {
		if (o == null) {
			return null;
		}
		if (o instanceof java.util.Date) {
			return String.format(formatBuildDate, o);
		} else if (o instanceof java.sql.Date) {
			java.util.Date ud = SQLUtil.getDateOfUtil(o);
			return String.format(formatBuildDate, ud);
		} else if (o instanceof java.sql.Timestamp) {
			Timestamp ts = (Timestamp) o;
			return String.format(formatBuildTimeStamp,
					new java.util.Date(ts.getTime()));
		} else if (o instanceof Status) {
			return net.arnx.jsonic.JSON.encode(o);
		} else if (o instanceof Object[]) {
			return net.arnx.jsonic.JSON.encode(o);
		}
		return String.valueOf(o);
	}

	public String getFormatBuildDate() {
		return formatBuildDate;
	}

	public void setMakeBlobData(boolean makeblob) {
		this.makeBlobData = makeblob;
	}

	public boolean isMakeBlobData() {
		return this.makeBlobData;
	}

	public ResultSetDataHolder2 RS2RDH(DatabaseType databaseType, ResultSet rs,
			boolean closeResultset) throws SQLException {
		ResultSetDataHolder2 ret = RS2RDH(databaseType, rs, closeResultset,
				null, null);
		ret.setConnectionDisplayString(connectionDisplayString);
		return ret;
	}

	public void setBuildTimeStamp(String format) {
		formatBuildTimeStamp = format;
	}

	public void setBuildDate(String format) {
		formatBuildDate = format;
	}

	public ResultSetDataHolder2 RS2RDH(DatabaseType databaseType, ResultSet rs,
			boolean closeResultset, String statement, Object[] binds)
			throws SQLException {
		ResultSetMetaData meta = rs.getMetaData();
		ResultSetDataHolder2 sdh = null;
		int colsize = meta.getColumnCount();
		String[] columns = new String[colsize];
		int[] colSize = new int[colsize];
		for (int i = 0; i < colsize; i++) {
			columns[i] = meta.getColumnName(i + 1);
			colSize[i] = meta.getColumnDisplaySize(i + 1);
		}
		sdh = new ResultSetDataHolder2(columns, meta, databaseType);
		while (rs.next()) {
			String[] row = new String[colsize];
			for (int i = 0; i < colsize; i++) {
				row[i] = getStringUsingType(databaseType, meta, rs, i + 1);
			}
			sdh.addRow(row);
		}

		if (closeResultset) {
			rs.close();
		}
		sdh.setSqlStatement(statement);
		sdh.setBinds(binds);
		sdh.setConnectionDisplayString(connectionDisplayString);
		return sdh;
	}

	private String getStringUsingType(DatabaseType databaseType,
			ResultSetMetaData meta, ResultSet rs, int index)
			throws SQLException {

		try {
			switch (meta.getColumnType(index)) {
			case Types.TIMESTAMP:
				Timestamp t = rs.getTimestamp(index);
				if (t == null) {
					return null;
				}
				java.util.Date dd = new java.util.Date(t.getTime());
				return String.format(formatBuildTimeStamp, dd);
			case Types.DATE:
				if ("YEAR".equals(meta.getColumnTypeName(index))) {
					// MYSQL TYPE OF YEAR
					int year = rs.getInt(index);
					if (year == 0) {
						return null;
					}
					return String.valueOf(year);
				}
				Timestamp tt = rs.getTimestamp(index);
				if (tt == null) {
					return null;
				}
				return String.format(formatBuildDate,
						new java.util.Date(tt.getTime()));

			case Types.CLOB:
				Clob clob = rs.getClob(index);
				if (clob == null) {
					return null;
				}
				Reader reader = clob.getCharacterStream();
				char[] cbuffer = new char[1024];
				int length = -1;
				StringBuilder buf = new StringBuilder();
				try {
					while ((length = reader.read(cbuffer)) != -1) {
						buf.append(new String(cbuffer, 0, length));
					}
				} catch (IOException e1) {
					e1.printStackTrace();
				}
				return buf.toString();
			case Types.BLOB:
				switch (databaseType) {
				case SQLITE:
					if (makeBlobData) {
						byte[] bytes0 = rs.getBytes(index);
						if (bytes0 == null || bytes0.length == 0) {
							return null;
						}
						String prefix = StringUtil.nvl(meta
								.getColumnName(index));
						return makeBinary(prefix, bytes0);

					} else {
						InputStream is = null;
						try {
							is = rs.getBinaryStream(index);
							if (is == null) {
								return null;
							}
							if (!makeBlobData) {
								if (is.available() > 0) {
									return BINARY;
								} else {
									return null;
								}
							}
						} catch (Exception e) {
							e.printStackTrace();
						} finally {
							if (is != null) {
								try {
									is.close();
								} catch (IOException e) {
								}
							}
						}
						// for getBinaryException!
						byte[] bytes0 = rs.getBytes(index);
						if (bytes0 == null || bytes0.length == 0) {
							return null;
						}
						return BINARY;
					}
				default:
					Blob blob = rs.getBlob(index);
					if (blob == null) {
						return null;
					}
					if (makeBlobData) {
						String prefix = StringUtil.nvl(meta
								.getColumnName(index));
						return makeBinary(prefix,
								new BufferedInputStream(blob.getBinaryStream()));

					} else {
						return BINARY;
					}
				}
			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
				InputStream is = null;
				try {
					is = rs.getBinaryStream(index);
					if (is == null) {
						return null;
					}
					if (!makeBlobData) {
						if (is.available() > 0) {
							return BINARY;
						} else {
							return null;
						}
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
				if (makeBlobData) {

					String prefix = StringUtil.nvl(meta.getColumnName(index));
					String str = makeBinary(prefix, new BufferedInputStream(is));

					return str;
				}
			case Types.DECIMAL:
			case Types.NUMERIC:
				BigDecimal decimal = rs.getBigDecimal(index);
				if (decimal == null) {
					return null;
				}
				return decimal.toPlainString();
			default:
				return rs.getString(index);
			}
		} catch (SQLException e) {
			fLogger.info(StringUtil.EMPTY_STRING, e);
			return null;
		}
	}

	public String makeBinary(String prefix, BufferedInputStream in) {
		File folder = getBlobFolder(prefix);
		if (folder == null) {
			return "fail make folder[" + prefix + "]";
		}
		return makeBinary(prefix, folder, in);
	}

	public String makeBinary(String prefix, byte[] bytes) {
		File folder = getBlobFolder(prefix);
		if (folder == null) {
			return "fail make folder[" + prefix + "]";
		}
		return makeBinary(prefix, folder, bytes);
	}

	private File getBlobFolder(String prefix) {
		File folder = new File("blob", prefix);
		if (!folder.exists()) {
			folder.mkdirs();
			if (!folder.exists()) {
				return null;
			}
		}
		return folder;
	}

	public String makeBinary(String prefix, byte[] bytes, String fileName) {
		File folder = getBlobFolder(prefix);
		if (folder == null) {
			return "fail make folder[" + prefix + "]";
		}
		File file = null;
		BufferedOutputStream out = null;
		try {
			while (prefix.length() < 3) {
				prefix += "_";
			}
			file = new File(folder, fileName);

			out = new BufferedOutputStream(new FileOutputStream(file));
			out.write(bytes, 0, bytes.length);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.flush();
					out.close();
				} catch (IOException e) {
				}
			}
		}
		return file.getAbsolutePath();
	}

	private File replaceFileNameToOffice(File file) {
		try {
			File nf = new File(file.getAbsolutePath() + ".xls");
			new HSSFWorkbook(new FileInputStream(file));
			file.renameTo(nf);
			return nf;
		} catch (Exception e) {
		}
		try {
			File nf = new File(file.getAbsolutePath() + ".doc");
			new HWPFDocument(new FileInputStream(file));
			file.renameTo(nf);
			return nf;
		} catch (Exception e) {
		}
		try {
			File nf = new File(file.getAbsolutePath() + ".ppt");
			new HSLFSlideShow(new FileInputStream(file));
			file.renameTo(nf);
			return nf;
		} catch (Exception e) {
		}
		return file;
	}

	private File createTmpFileFromBinary(byte[] bytes, String prefix,
			File folder, int bytes_length) throws IOException {
		int len = bytes_length;
		if (len > 256) {
			len = 256;
		}
		FileExtentionType type = FileExtentionType.parse(new String(bytes, 0,
				len));
		String ext = null;
		if (type == null) {
			ext = StringUtil.EMPTY_STRING;
		} else {
			ext = "." + type.getName();
		}
		return File.createTempFile(prefix, ext, folder);
	}

	private String makeBinary(String prefix, File folder, BufferedInputStream in) {
		BufferedOutputStream out = null;
		boolean testPoi = false;
		File file = null;
		try {
			byte[] buff = new byte[256];
			int len = 0;
			while (prefix.length() < 3) {
				prefix += "_";
			}
			for (int ii = 0; (len = in.read(buff, 0, 256)) > 0; ii++) {
				if (ii == 0) {
					file = createTmpFileFromBinary(buff, prefix, folder, len);
					if (file == null) {
						file = File.createTempFile(prefix,
								StringUtil.EMPTY_STRING, folder);
						testPoi = false;
					}
					if (file.getName().lastIndexOf('.') < 0) {
						testPoi = true;
					}
					out = new BufferedOutputStream(new FileOutputStream(file));
				}
				out.write(buff, 0, len);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.flush();
					out.close();
				} catch (IOException e) {
				}
			}
		}
		try {
			if (testPoi) {
				file = replaceFileNameToOffice(file);
			}
			return file.getAbsolutePath();
		} catch (Exception e) {
			fLogger.warn(StringUtil.EMPTY_STRING, e);
			return "<ERROR>";
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
				}
			}
		}
	}

	private String makeBinary(String prefix, File folder, byte[] bytes) {
		boolean testPoi = false;
		File file = null;
		BufferedOutputStream out = null;
		try {
			while (prefix.length() < 3) {
				prefix += "_";
			}
			file = createTmpFileFromBinary(bytes, prefix, folder, bytes.length);
			if (file == null) {
				file = File.createTempFile(prefix, StringUtil.EMPTY_STRING,
						folder);
				testPoi = false;
			}
			if (file.getName().lastIndexOf('.') < 0) {
				testPoi = true;
			}
			out = new BufferedOutputStream(new FileOutputStream(file));
			out.write(bytes, 0, bytes.length);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.flush();
					out.close();
				} catch (IOException e) {
				}
			}
		}
		try {
			if (testPoi) {
				file = replaceFileNameToOffice(file);
			}
			return file.getAbsolutePath();
		} catch (Exception e) {
			fLogger.warn(StringUtil.EMPTY_STRING, e);
			return "<ERROR>";
		}
	}

	public void setConnectionDisplayString(String displayName) {
		this.connectionDisplayString = displayName;
	}

}
