/*
 * ============LICENSE_START==========================================
 * ONAP Portal SDK
 * ===================================================================
 * Copyright © 2017 AT&T Intellectual Property. All rights reserved.
 * ===================================================================
 *
 * Unless otherwise specified, all software contained herein is licensed
 * under the Apache License, Version 2.0 (the "License");
 * you may not use this software except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *             http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Unless otherwise specified, all documentation contained herein is licensed
 * under the Creative Commons License, Attribution 4.0 Intl. (the "License");
 * you may not use this documentation except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *             https://creativecommons.org/licenses/by/4.0/
 *
 * Unless required by applicable law or agreed to in writing, documentation
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * ============LICENSE_END============================================
 *
 * 
 */
package org.onap.portalsdk.analytics.scheduler;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.onap.portalsdk.analytics.error.ReportSQLException;
import org.onap.portalsdk.analytics.scheduler.SchedulerUtil.Executor;
import org.onap.portalsdk.analytics.system.Globals;
import org.onap.portalsdk.analytics.util.EmailUtils;
import org.onap.portalsdk.analytics.util.MailAttachment;
public class SendEmail {
	SchedulerUtil schedulerUtil;
	EmailUtils emailUtils;
	public SendEmail() {
	}
	public void sendEmail(String p_mail_server, String p_sender, String p_subject, String p_mail_text, String p_url,
			int p_file_type, int p_schedule_id, int p_time_interval, boolean p_send_attachment, int connectionTimeout)
			throws Exception {
		String allEmailAddr = "";
		final List<String> emailArr = new ArrayList<>();
		String schedular_email;
		schedular_email = (String) schedulerUtil.getSingleResult(
				"select email from fn_user au, cr_report_schedule crs where CRS.SCHED_USER_ID = AU.USER_ID and CRS.SCHEDULE_ID = "
						+ p_schedule_id,
				"email");
		String sql = Globals.getSchedulerUserEmails().replace("[p_schedule_id]", p_schedule_id + "");
		schedulerUtil.getAndExecute(sql, new Executor() {
			@Override
			public void execute(ResultSet rs) throws SQLException {
				emailArr.add(rs.getString("email"));				
			}
		});
		if (!p_send_attachment) {
			http_to_blob(p_url, p_file_type, p_schedule_id, connectionTimeout);
		}
		int i = 0;
		for (String email : emailArr) {
			if (email.contains("@")) {
				if (i == 0)
					allEmailAddr = email;
				else
					allEmailAddr += ',' + email;
				i++;
			}
		}
		List<MailAttachment> mailAttachments = null;
		p_send_attachment=true;
		if (p_file_type > 1 && p_send_attachment) {
			mailAttachments = add_attachment(p_url, p_file_type, p_schedule_id, connectionTimeout);
		}
		emailUtils.notifyWithAttachments(p_mail_text, emailArr.toArray(new String[emailArr.size()]), p_sender,
				p_subject, new String[] { schedular_email }, null, mailAttachments, true);
	}
	class HistRec {
		String file_blob;
		BigDecimal rep_id;
		BigDecimal hist_id;
		String file_name;
		int sched_user_id;
		String recurrence;
		int file_size = 0;
		String raptor_url;
		int schedule_id;
		int file_type_id;
		int user_id;
		String deleted_yn;
	}
	private HistRec http_to_blob(String p_url, int v_file_type, int p_schedule_id, int connectionTimeout)
			throws SQLException, ReportSQLException {
		final HistRec v_hist_rec  = initializeVHistoryRecord(p_url, v_file_type, p_schedule_id);
		HttpURLConnection con = null;
		try {			
			URL url = new URL(p_url);
			con = (HttpURLConnection) url.openConnection();
			con.setConnectTimeout(connectionTimeout * 1000);
			con.setRequestMethod("GET");
			schedulerUtil.insertOrUpdate("INSERT INTO cr_filehist_log (SCHEDULE_ID, url, notes, run_time) VALUES ("
					+ p_schedule_id + ",'" + p_url + "','http_to_blob: Initiated HTTP request', "
					+ Globals.getCurrentDateString() + " )");
			int responseCode = con.getResponseCode();
			String outputFolder = Globals.getProjectFolder() + java.io.File.separator + Globals.getOutputFolder();
			String fileName = v_hist_rec.file_name;
			createFile(con, outputFolder, fileName);
			File readFile = new File(outputFolder + java.io.File.separator + fileName);
			v_hist_rec.file_size = (int) readFile.length();
			schedulerUtil
					.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
							+ p_schedule_id + ",'" + p_url + "', 'http_to_blob: http response recieved. Code "
							+ responseCode + "', " + Globals.getCurrentDateString() + " )");
			List<Object> params = new ArrayList<>();
			List<Integer> types = new ArrayList<>();
			prepareHisRecUpdate(v_hist_rec, params, types);
			schedulerUtil.insertOrUpdateWithPrepared(
					"INSERT INTO cr_report_file_history(HIST_ID, SCHED_USER_ID, SCHEDULE_ID, USER_ID, REP_ID, RUN_DATE, RECURRENCE, FILE_TYPE_ID, FILE_NAME, FILE_SIZE, RAPTOR_URL, ERROR_YN, ERROR_CODE, DELETED_YN, DELETED_BY)"
							+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
					params, types);
			try (FileInputStream fileStream = new FileInputStream(readFile)) {
				schedulerUtil.updateBinaryStream("update cr_report_file_history set file_blob = ? where hist_id = ?",
						v_hist_rec.hist_id, fileStream, v_hist_rec.file_size);
			} catch (Exception e) {
				throw e;
			}
			String userAddRecSql = Globals.getSchedulerUserEmails().replace("[p_schedule_id]", p_schedule_id + "");
			schedulerUtil.getAndExecute(userAddRecSql, new Executor() {
				@Override
				public void execute(ResultSet rs) throws SQLException {
					try {
						schedulerUtil.insertOrUpdate("INSERT INTO CR_HIST_USER_MAP (HIST_ID, USER_ID) values ( "
								+ v_hist_rec.hist_id + "," + rs.getInt("user_id") + ")");
					} catch (ReportSQLException e) {
						throw new SQLException(e.getMessage());
					}
				}
			});
			schedulerUtil.insertOrUpdate(
					"insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  (" + p_schedule_id
							+ ",'" + p_url + "','Success: http_to_blob', " + Globals.getCurrentDateString() + " )");
		} catch (Exception e) {
			schedulerUtil
					.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
							+ p_schedule_id + ",'" + p_url + "', 'Failure: http_to_blob : Exception" + e.getMessage()
							+ "', " + Globals.getCurrentDateString() + " )");
			e.printStackTrace();
		} finally {
			if (con != null)
				con.disconnect();
		}
		return v_hist_rec;
	}
	protected void prepareHisRecUpdate(final HistRec v_hist_rec, List<Object> params, List<Integer> types) {
		params.add(v_hist_rec.hist_id);
		types.add(Types.BIGINT);
		params.add(v_hist_rec.sched_user_id);
		types.add(Types.INTEGER);
		params.add(v_hist_rec.schedule_id);
		types.add(Types.INTEGER);
		params.add(v_hist_rec.user_id);
		types.add(Types.INTEGER);
		params.add(v_hist_rec.rep_id);
		types.add(Types.BIGINT);
		params.add(new java.sql.Date(Calendar.getInstance().getTime().getTime()));
		types.add(Types.DATE);
		params.add(v_hist_rec.recurrence);
		types.add(Types.VARCHAR);
		params.add(v_hist_rec.file_type_id);
		types.add(Types.INTEGER);
		params.add(v_hist_rec.file_name);
		types.add(Types.VARCHAR);
		params.add(v_hist_rec.file_size);
		types.add(Types.INTEGER);
		params.add(v_hist_rec.raptor_url);
		types.add(Types.VARCHAR);
		params.add("N");
		types.add(Types.VARCHAR);
		params.add("NULL");
		types.add(Types.INTEGER);
		params.add(v_hist_rec.deleted_yn);
		types.add(Types.VARCHAR);
		params.add(v_hist_rec.sched_user_id);
		types.add(Types.INTEGER);
	}
	protected HistRec initializeVHistoryRecord(String p_url, int v_file_type, int p_schedule_id)
			throws SQLException, ReportSQLException {
		final HistRec v_hist_rec = new HistRec();
		v_hist_rec.rep_id = (BigDecimal) schedulerUtil
				.getSingleResult("SELECT rep_id FROM cr_report_schedule WHERE schedule_id =" + p_schedule_id, "rep_id");
		Object sequenceId = schedulerUtil.getSingleResult(
				Globals.getSequenceNextVal().replace("[sequenceName]", "seq_cr_report_file_history"), "ID");
		if (sequenceId instanceof Long)
			v_hist_rec.hist_id = new BigDecimal((Long) sequenceId);
		else if (sequenceId instanceof BigDecimal)
			v_hist_rec.hist_id = (BigDecimal) sequenceId;
		v_hist_rec.file_name = (String) schedulerUtil.getSingleResult("select translate(title||to_char( "
				+ Globals.getCurrentDateString() + ",'MM-dd-yyyyHH24:mm:ss'), "
				+ "'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'||'():;.-`~^\\|'||chr(34)||chr(39)||chr(9)||' ', "
				+ "'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')|| " + v_hist_rec.hist_id
				+ " as title FROM cr_report WHERE rep_id = " + v_hist_rec.rep_id, "title");
		class File {
			String file_name;
			String file_ext;
		}
		final File file = new File();
		schedulerUtil.getAndExecute(
				"select template_file from cr_report_template_map where report_id = " + v_hist_rec.rep_id,
				new Executor() {
					@Override
					public void execute(ResultSet rs) throws SQLException {
						file.file_name = rs.getString("template_file");
						file.file_ext = file.file_name.substring(file.file_name.indexOf('.'));
					}
				});
		if (v_file_type == 2) {
			v_hist_rec.file_name = v_hist_rec.file_name + ".pdf";
		} else if (v_file_type == 4) {
			v_hist_rec.file_name = v_hist_rec.file_name + ".xls";
		} else if (v_file_type == 5) {
			if (file.file_name != null && file.file_ext.length() > 0) {
				v_hist_rec.file_name = v_hist_rec.file_name + file.file_ext;
			} else {
				v_hist_rec.file_name = v_hist_rec.file_name + ".xlsx";
			}
		} else if (v_file_type == 3) {
			v_hist_rec.file_name = v_hist_rec.file_name + ".csv";
		}
		schedulerUtil.getAndExecute(
				"select sched_user_id, rep_id, recurrence from cr_report_schedule where schedule_id=" + p_schedule_id,
				new Executor() {
					@Override
					public void execute(ResultSet rs) throws SQLException {
						v_hist_rec.sched_user_id = rs.getInt("sched_user_id");
						v_hist_rec.rep_id = rs.getBigDecimal("rep_id");
						v_hist_rec.recurrence = rs.getString("recurrence");
					}
				});
		v_hist_rec.file_size = 0;
		v_hist_rec.raptor_url = p_url;
		v_hist_rec.schedule_id = p_schedule_id;
		v_hist_rec.file_type_id = v_file_type;
		v_hist_rec.user_id = v_hist_rec.sched_user_id;
		v_hist_rec.deleted_yn = "N";
		return v_hist_rec;
	}
	private List<MailAttachment> add_attachment(String p_url, int v_file_type, int p_schedule_id, int connectionTimeout)
			throws SQLException, ReportSQLException,Exception {
		List<MailAttachment> mailAttachmentList = new ArrayList<MailAttachment>();
		final HistRec vHistRec  = initializeVHistoryRecord(p_url, v_file_type, p_schedule_id);			
		HttpURLConnection con = null;
		try {
			URL url = new URL(p_url);
			con = (HttpURLConnection) url.openConnection();
			con.setConnectTimeout(connectionTimeout * 1000);
			con.setRequestMethod("GET");
			schedulerUtil
					.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
							+ p_schedule_id + ",'" + p_url + "', 'Success: http request began.', "
							+ Globals.getCurrentDateString() + " )");
			int responseCode = con.getResponseCode();
			String outputFolder = Globals.getProjectFolder() + java.io.File.separator + Globals.getOutputFolder();
			String fileName = vHistRec.file_name;
			createFile(con, outputFolder, fileName);
			MailAttachment mailAttachment = new MailAttachment();
			mailAttachment.setAttachmentType(MailAttachment.FILE_ATTACHMENT);
			mailAttachment.setFilePathName(outputFolder);
			mailAttachment.setFileName(fileName);
			mailAttachmentList.add(mailAttachment);
			schedulerUtil
					.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
							+ p_schedule_id + ",'" + p_url + "', 'Success: http response recieved. Code " + responseCode
							+ "', " + Globals.getCurrentDateString() + " )");
			schedulerUtil.insertOrUpdate(
					"insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  (" + p_schedule_id
							+ ",'" + p_url + "','Success: added attachment', " + Globals.getCurrentDateString() + " )");
		} catch (Exception e) {
			schedulerUtil
					.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
							+ p_schedule_id + ",'" + p_url + "', 'Failure: adding attachment : Exception"
							+ e.getMessage() + "', " + Globals.getCurrentDateString() + " )");
			e.printStackTrace();
		} finally {
			if (con != null)
				con.disconnect();
		}
		return mailAttachmentList;
	}
	public void createFile(HttpURLConnection con, String outputFolder, String fileName) throws IOException {
		InputStream in = con.getInputStream();
		try {
			try (FileOutputStream out = new FileOutputStream(outputFolder + java.io.File.separator + fileName)) {
				int inputLine;
				while ((inputLine = in.read()) != -1) {
					out.write(inputLine);
				}
				out.flush();
			} catch (Exception e) {
				throw e;
			}
		} finally {
			in.close();
		}
	}
	public SchedulerUtil getSchedulerUtil() {
		return schedulerUtil;
	}
	public void setSchedulerUtil(SchedulerUtil schedulerUtil) {
		this.schedulerUtil = schedulerUtil;
	}
}