/*
 * ============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.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;
import org.onap.portalsdk.analytics.error.RaptorException;
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.core.logging.logic.EELFLoggerDelegate;

public class SendNotifications {

    private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SendNotifications.class);
    SchedulerUtil schedulerUtil;
    public SendEmail sendEmail;

    public SendNotifications() throws Exception {
        schedulerUtil = new SchedulerUtil();
        sendEmail = new SendEmail();
        sendEmail.setSchedulerUtil(schedulerUtil);
        init();
    }

    public void deInit() throws SQLException {
        schedulerUtil.closeConnection();
    }

    public void init() throws SQLException, ReportSQLException {
        schedulerUtil.init();
    }

    public void send_notification(final String p_mail_server, final String p_sender, final String p_system_name,
            final String p_system_url, final int connectionTimeout) throws RaptorException, Exception {

        logger.info(p_mail_server + " " + p_sender + " " + p_system_name + " " + p_system_url);
        final int p_time_interval = Globals.getSchedulerInterval();
        int v_num_recs = 0;
        BigDecimal v_id = null;
        String v_r_action = "report.download.pdf";

        final String CNotificationsql = Globals.getAvailableSchedules()
                .replace("[currentDate]", Globals.getCurrentDateString());

        try (final ResultSet rs = schedulerUtil.getConnection().createStatement().executeQuery(CNotificationsql);) {
            while (rs.next()) {
                final int v_schedule_id = rs.getInt("schedule_id");
                final int offset = get_report_sched_offset(rs.getInt("rep_id"), v_schedule_id);
				  if (offset >= p_time_interval) { continue; }
                final Date v_touch_date = (Date) schedulerUtil.getSingleResult(
                        "select touch_date from cr_report_email_sent_log where schedule_id = " + v_schedule_id
                                + " and log_id = (select max(log_id) from cr_report_email_sent_log where schedule_id = "
                                + v_schedule_id + ")",
                        "touch_date");
                if (v_touch_date != null) {
                    if (Math.abs(System.currentTimeMillis() - v_touch_date.getTime()) / 1000 < (p_time_interval - 1)) {
                        return;
                    }
                }

                if ("Y".equals(rs.getString("conditional_yn"))) {
                    v_num_recs = (Integer) schedulerUtil
                            .getSingleResult(
                                    "select count(*) count from (" + rs.getString("condition_large_sql") + " )",
                                    "count");
                }

                if (v_num_recs > 0 || "N".equals(rs.getString("conditional_yn"))) {
                    final String v_gen_key = ("Z" + UUID.randomUUID()).toString()
                            .substring(0, 24); // 25 character string
                    final Object sequenceId =
                            schedulerUtil.getSingleResult(
                                    Globals.getSequenceNextVal().replace("[sequenceName]", "seq_email_sent_log_id"),
                                    "id");

                    if (sequenceId instanceof Long) {
                        v_id = new BigDecimal((Long) sequenceId);
                    } else if (sequenceId instanceof BigDecimal) {
                        v_id = (BigDecimal) sequenceId;
                    }

                    schedulerUtil.insertOrUpdate(
                            "insert into cr_report_email_sent_log (log_id, gen_key, schedule_id, rep_id, user_id, touch_date) values ("
                                    + v_id + ",'" + v_gen_key + "'," + rs.getInt("schedule_id") + ","
                                    + rs.getInt("rep_id")
                                    + ","
                                    + rs.getInt("user_id") + ", " + Globals.getCurrentDateString() + " )");

                    final int notify_type = rs.getInt("notify_type");
                    if (notify_type == 4) {
                        v_r_action = "report.download";
                    } else if (notify_type == 2) {
                        v_r_action = "report.download.pdf";
                    } else if (notify_type == 3) {
                        v_r_action = "report.csv.download";
                    } else if (notify_type == 5) {
                        v_r_action = "report.download.excel2007";
                    } else if (notify_type == 6) {
                        v_r_action = "download.all";
                    }

                    final String v_formfields;
                    if (rs.getObject("processed_formfields") != null) {
                        v_formfields = modify_formfields(v_schedule_id, rs.getString("processed_formFields"));
                    } else {
                        v_formfields = strip_formfields(v_schedule_id, rs.getString("initial_formfields"));
                    }
					  final String v_url = p_system_url + "&r_action=" + v_r_action + "&log_id=" +
					  v_id + "&user_id=" + rs .getString("user_id") + "&pdfAttachmentKey=" +
					  v_gen_key + "&download_limit=" + rs.getInt("max_row") + v_formfields;
                    final boolean v_attach_email_yn = shouldSendAttachmentInEmail(v_schedule_id);

                    final String v_email_msg =
                            "<html><body><p><b><u><i>" + p_system_name + " System Notification</i></u></b></p>"
                                    + "<p>Report <b>" + rs.getString("title")
                                    + "</b> is available for viewing.</p><p>You can view the report if it is attached. </br>"
                                    + "If it is not attached, or you have problem to open it, you can log into Business Direct and run the report.</p>"
                                    + "</body></html>";

                    if (rs.getInt("notify_type") != 6) {
                        sendEmail.sendEmail(p_mail_server, p_sender,
                                p_system_name + " System Notification: Report " + rs.getString("title") + " generated",
                                v_email_msg, v_url, rs.getInt("notify_type"), v_schedule_id, p_time_interval,
                                v_attach_email_yn,
                                connectionTimeout);

                    } else {
                        // may not necessary
                        schedulerUtil.insertOrUpdate(
                                "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
                                        + v_schedule_id + ",'" + v_url + "'," + "'Success: http request began.', "
                                        + Globals
                                                .getCurrentDateString()
                                        + " )");
                        schedulerUtil.insertOrUpdate(
                                "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
                                        + v_schedule_id + ",'" + v_url + "',"
                                        + "'Success: http response recieved. Code  resp.status_code '' desc '' resp.reason_phrase', "
                                        + Globals.getCurrentDateString() + " )");
                    }

                    schedulerUtil.insertOrUpdate(
                            "update cr_report_schedule set run_date = " + Globals.getCurrentDateString()
                                    + " where schedule_id="
                                    + v_schedule_id);

                    schedulerUtil.insertOrUpdate(
                            "update cr_report_email_sent_log set sent_date= " + Globals.getCurrentDateString()
                                    + " , access_flag='N' where log_id=" + v_id);
                    schedulerUtil.insertOrUpdate(
                            "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values ("
                                    + v_schedule_id
                                    + ",'" + v_url + "','Success: Email Sent', " + Globals.getCurrentDateString()
                                    + " )");
                }
            }
        } catch (final SQLException e) {
            logger.warn("Failed to open connection", e);
        }
    }

    private boolean shouldSendAttachmentInEmail(int v_schedule_id) throws SQLException, ReportSQLException {

        String l_boolean = (String) schedulerUtil.getSingleResult(
                "SELECT ATTACHMENT_YN from cr_report_schedule where schedule_id  = " + v_schedule_id, "ATTACHMENT_YN");
        if ("Y".equals(l_boolean))
            return true;
        return false;
    }

    private String strip_formfields(int v_schedule_id, String p_formfields) throws SQLException, ReportSQLException {

        String v_formfields_insert = "";
        String v_formfields_generate = "";
        String v_name = "";
        String v_value = "";

        String[] column_values = schedulerUtil.cr_dissecturl(p_formfields, "&");

        for (String column_value : column_values) {
            if (column_value == null || column_value.isEmpty())
                continue;

            v_name = column_value.substring(0, column_value.indexOf('='));
            v_formfields_insert += column_value + "&";
            v_value = column_value.substring(column_value.indexOf('=') + 1);
            if (column_value.indexOf("_auto") > 0) {
                v_formfields_generate =
                        v_formfields_generate + v_name.substring(0, v_name.indexOf("_auto")) + "=" + v_value + "&";
            } else {
                v_formfields_generate = v_formfields_generate + column_value + "&";
            }
        }

        schedulerUtil.insertOrUpdate("update CR_REPORT_SCHEDULE set processed_formfields ='" + v_formfields_insert
                + "' where schedule_id = " + v_schedule_id);

        return v_formfields_generate.substring(0, v_formfields_generate.length());

    }

    private String modify_formfields(int v_schedule_id, String p_formfields) throws SQLException, ReportSQLException {

        class Result {

            String v_formfields_insert = "";
            String v_formfields_generate = "";
            String v_name = "";
            String v_value = "";
            Date v_date;
            String v_hour = "";
            String v_hour_value = "";
        }

        final Result result = new Result();

        final String v_recurrence = (String) schedulerUtil.getSingleResult(
                "select recurrence from cr_report_schedule where schedule_id = " + v_schedule_id, "recurrence");

        String[] column_values = schedulerUtil.cr_dissecturl(p_formfields, "&");

        for (String column_value : column_values) {
            if (column_value == null)
                column_value = "";

            if ("MONTHLY".equals(v_recurrence)) {
                if (column_value.indexOf("_auto") > 0) {
                    result.v_name = column_value.substring(0, column_value.indexOf('='));
                    result.v_date =
                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
                    result.v_value = schedulerUtil.add_months(result.v_date, 1).toString();

                    if (result.v_name.length() > 0) {
                        result.v_formfields_insert =
                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
                        result.v_formfields_generate = result.v_formfields_generate
                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
                                + "&";
                    }
                } else {
                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
                }

            } else if ("DAILY".equals(v_recurrence)) {
                if (column_value.indexOf("_auto") > 0) {
                    result.v_name = column_value.substring(0, column_value.indexOf('='));
                    result.v_date =
                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
                    result.v_value = schedulerUtil.add_months(result.v_date, 1).toString();

                    if (result.v_name.length() > 0) {
                        result.v_formfields_insert =
                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
                        result.v_formfields_generate = result.v_formfields_generate
                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
                                + "&";
                    }
                } else {
                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
                }

            } else if ("DAILY_MO_FR".equals(v_recurrence)) {
                if (column_value.indexOf("_auto") > 0) {
                    result.v_name = column_value.substring(0, column_value.indexOf('='));
                    result.v_date =
                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
                    SimpleDateFormat sdf = new SimpleDateFormat("EEE");
                    sdf.format(result.v_date);
                    if ("FRI".equals(result.v_date.toString())) {
                        result.v_date = schedulerUtil.add_days(result.v_date, 3);
                    } else if ("SAT".equals(result.v_date.toString())) {
                        result.v_date = schedulerUtil.add_days(result.v_date, 2);
                    } else {
                        result.v_date = schedulerUtil.add_days(result.v_date, 1);
                    }
                    result.v_value = result.v_date.toString();
                    if (result.v_name.length() > 0) {
                        result.v_formfields_insert =
                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
                        result.v_formfields_generate = result.v_formfields_generate
                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
                                + "&";
                    }

                } else {
                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
                }

            } else if ("HOURLY".equals(v_recurrence)) {

                result.v_name =
                        column_value.indexOf('=') > 0 ? column_value.substring(0, column_value.indexOf('=')) : "";
                if (column_value.indexOf("_auto") > 0) {

                    String[] column_values2 = schedulerUtil.cr_dissecturl(p_formfields, "&");

                    for (String column_value2 : column_values2) {

                        String key = column_value2.substring(0, column_value2.indexOf("="));
                        if (key.equals(result.v_name.substring(0, result.v_name.indexOf("_auto")))
                                || key.equals(result.v_name.substring(0, result.v_name.indexOf("_Hr")))) {
                            result.v_hour = column_value2;
                        }
                    }

                    // schedulerUtil.getAndExecute("select c.column_value from table(CR_DISSECTURL(p_formfields)) c
                    // where substr(c.column_value, 1, instr(c.column_value, '=')-1) = substr(" + result.v_name +
                    // ",1,instr(" + result.v_name + ",'_auto')-1)||'_Hr'", new Executor() {
                    //
                    // @Override
                    // public void execute(ResultSet rs) throws SQLException {
                    //
                    // result.v_hour = rs.getString("column_value");
                    // }
                    //
                    // });

                    if (result.v_hour.length() > 0) {
                        result.v_hour_value = result.v_hour.substring(result.v_hour.indexOf('=') + 1);
                    }

                    result.v_date = schedulerUtil.to_date(
                            column_value.substring(column_value.indexOf('=') + 1) + " " + result.v_hour_value,
                            "mm/dd/yyyy HH24:MI:SS");

                    result.v_value =
                            schedulerUtil.to_date_str(schedulerUtil.add_hours(result.v_date, 1), "mm/dd/yyyy HH24");

                    if (result.v_name.length() > 0) {
                        if (result.v_hour.length() > 0) {

                            result.v_formfields_insert =
                                    result.v_formfields_insert + result.v_name + "=" + result.v_value.substring(0, 10)
                                            + "&" + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "_Hr="
                                            + result.v_value.substring(11, 13);
                            result.v_formfields_generate = result.v_formfields_generate
                                    + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "="
                                    + result.v_value.substring(0, 10) + "&"
                                    + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "_Hr="
                                    + result.v_value.substring(11, 13) + "&";

                        } else {
                            result.v_formfields_insert =
                                    result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
                            result.v_formfields_generate = result.v_formfields_generate
                                    + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
                                    + "&";
                        }
                    }
                }
                if (column_value.indexOf("_Hr") <= 0) {
                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
                }
            } else if ("WEEKLY".equals(v_recurrence)) {

                if (column_value.indexOf("_auto") > 0) {
                    result.v_name = column_value.substring(0, column_value.indexOf('='));
                    result.v_date =
                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
                    result.v_value = schedulerUtil.add_days(result.v_date, 7).toString();

                    if (result.v_name.length() > 0) {
                        result.v_formfields_insert =
                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
                        result.v_formfields_generate = result.v_formfields_generate
                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
                                + "&";
                    }

                } else {
                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
                }

            } else {
                if (column_value.indexOf("_auto") > 0) {
                    result.v_name = column_value.substring(0, column_value.indexOf('='));
                    result.v_date =
                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
                    result.v_value = schedulerUtil.add_days(result.v_date, 7).toString();
                    if (result.v_name.length() > 0) {
                        result.v_formfields_insert =
                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
                        result.v_formfields_generate = result.v_formfields_generate
                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
                                + "&";
                    }

                } else {
                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
                }
            }
        }

        schedulerUtil.insertOrUpdate("update CR_REPORT_SCHEDULE set processed_formfields ='"
                + result.v_formfields_insert + "' where schedule_id =" + v_schedule_id);
        return "&" + result.v_formfields_generate.substring(0, result.v_formfields_generate.length());
    }

    private int get_report_sched_offset(int p_rep_id, int p_schedule_id) throws SQLException, ReportSQLException {

        class CrReportSchedule {

            Date run_date;
            String recurrence;
        }
        Date v_last_date = null;
        Date v_sysdate = new Date();

        final CrReportSchedule v_report_schedule_rec = new CrReportSchedule();

        schedulerUtil.getAndExecute(
                "SELECT * FROM cr_report_schedule WHERE rep_id = " + p_rep_id + " and schedule_id = " + p_schedule_id,
                new Executor() {

                    @Override
                    public void execute(ResultSet rs) throws SQLException {

                        v_report_schedule_rec.recurrence = rs.getString("recurrence");
                        java.sql.Timestamp runDate = rs.getTimestamp("run_date");
                        v_report_schedule_rec.run_date = new Date(runDate.getTime());
                    }

                });

        if (v_report_schedule_rec.run_date == null || v_report_schedule_rec.run_date.compareTo(v_sysdate) > 0) {
            return Integer.MAX_VALUE;
        }

        Date v_next_date = v_report_schedule_rec.run_date;

        while (v_next_date.compareTo(v_sysdate) < 0) {

            if ("HOURLY".equals(v_report_schedule_rec.recurrence)) {
                v_next_date = SchedulerUtil.add_hours(v_next_date, 1);
            } else if ("DAILY".equals(v_report_schedule_rec.recurrence)) {
                v_next_date = SchedulerUtil.add_days(v_next_date, 1);
            } else if ("DAILY_MO_FR".equals(v_report_schedule_rec.recurrence)) {
                SimpleDateFormat sdf = new SimpleDateFormat("EEE");
                sdf.format(v_next_date);
                if ("FRI".equals(v_next_date.toString())) {
                    v_next_date = SchedulerUtil.add_days(v_next_date, 3);
                } else if ("SAT".equals(v_next_date.toString())) {
                    v_next_date = SchedulerUtil.add_days(v_next_date, 2);
                } else {
                    v_next_date = SchedulerUtil.add_days(v_next_date, 1);
                }
            } else if ("WEEKLY".equals(v_report_schedule_rec.recurrence)) {
                v_next_date = SchedulerUtil.add_days(v_next_date, 7);
            } else if ("MONTHLY".equals(v_report_schedule_rec.recurrence)) {
                v_next_date = SchedulerUtil.add_months(v_next_date, 1);
            } else {
                break;
            }
            v_last_date = v_next_date;
        }

        if (v_last_date != null
                && SchedulerUtil.trunc_hour(v_last_date).compareTo(SchedulerUtil.trunc_hour(v_sysdate)) == 0) {
            return (int) (Math.abs(v_sysdate.getTime() - v_last_date.getTime()) / 1000);
        } else {
            // More than an hour
            return 3601;
        }
    }

    public SchedulerUtil getSchedulerUtil() {
        return schedulerUtil;
    }

    public void setSchedulerUtil(SchedulerUtil schedulerUtil) {
        this.schedulerUtil = schedulerUtil;
    }

}