/*
 * ============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.system;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import javax.sql.DataSource;
import org.onap.portalsdk.analytics.error.RaptorException;
import org.onap.portalsdk.analytics.error.ReportSQLException;
import org.onap.portalsdk.analytics.model.runtime.ReportRuntime;
import org.onap.portalsdk.analytics.util.DataSet;
import org.onap.portalsdk.analytics.xmlobj.DataColumnType;
import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
import org.onap.portalsdk.core.web.support.AppUtils;
import org.springframework.beans.factory.annotation.Autowired;

public class DbUtils {

    private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(DbUtils.class);

    private DataSource dataSource;

    public DbUtils() {
    }

    public static Connection getConnection() throws ReportSQLException {
        try {
            return AppUtils.getDatasource().getConnection();
        } catch (final SQLException ex) {
            logger.warn("Failed to get Connection", ex);
        }
        return null;
    } // getConnection

    public static void clearConnection(final Connection con) throws ReportSQLException {
        try {
            if ((con != null) && !con.isClosed()) {
                Globals.getDbUtils().clearConnection(con);
            }
        } catch (final Exception ex) {
            logger.warn("Failed to clear Connection", ex);
            throw new ReportSQLException(ex.getMessage(), ex.getCause());
        }
    } // clearConnection

    public static Connection startTransaction() throws ReportSQLException {
        Connection con = null;
        try {
            con = getConnection();
            if(con != null) {
                con.setAutoCommit(false);
            }
        } catch (final Exception ex2) {
            logger.warn("Failed to start Transaction", ex2);
            throw new ReportSQLException(ex2.getMessage(), ex2.getCause());
        }
        return con;
    } // startTransaction

    public static void commitTransaction(final Connection con) throws ReportSQLException {
        try {
            con.commit();
        } catch (final Exception ex2) {
            logger.warn("Failed to commit Transaction", ex2);
            throw new ReportSQLException(ex2.getMessage(), ex2.getCause());
        }
    } // commitTransaction

    public static void rollbackTransaction(final Connection con) throws ReportSQLException {
        try {
            con.rollback();
            clearConnection(con);
        } catch (final Exception ex2) {
            logger.warn("Failed to rollback Transaction", ex2);
            throw new ReportSQLException(ex2.getMessage(), ex2.getCause());
        }
    } // rollbackTransaction

    public static String executeCall(Connection con, final String sql, final boolean expectResult)
        throws ReportSQLException {
        String result = null;

        try {
            if (con == null || con.isClosed()) {
                con = getConnection();
            }
            logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL Call] " + sql));
            if (con != null) {
                try (final CallableStatement stmt = con.prepareCall(sql)) {
                    if (expectResult) {
                        stmt.registerOutParameter(1, Types.CHAR);
                    }
                    stmt.executeUpdate();
                    if (expectResult) {
                        result = stmt.getString(1);
                    }
                    con.commit();
                }
            }
        } catch (final SQLException e) {
            logger.warn("Failed to execute Call", e);
            throw new ReportSQLException(e.getMessage(), sql);
        } finally {
            clearConnection(con);
        }

        return result;
    } // executeCall

    public static String executeCall(final String sql, final boolean expectResult)
        throws RaptorException {
        Connection con = null;
        con = getConnection();
        return executeCall(con, sql, expectResult);
    } // executeCall

    public static int executeUpdate(final Connection con, final String sql) throws ReportSQLException {
        int rcode = -1;
        if (con != null) {
            try (Statement stmt = con.createStatement()) {
                logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL Update] " + sql));
                rcode = stmt.executeUpdate(sql);
            } catch (final SQLException e) {
                logger.warn("Failed to execute Update", e);
                throw new ReportSQLException(e.getMessage(), sql);
            }
        }
        return rcode;
    } // executeUpdate

    public static int executeUpdate(final String sql) throws ReportSQLException {
        try (final Connection con = getConnection()) {
            final int rcode = executeUpdate(con, sql);
            if (con != null && "oracle".equals(Globals.getDBType())) {
                con.commit();
            }
            return rcode;
        } catch (final SQLException e) {
            logger.warn("Failed to execute Update", e);
            throw new ReportSQLException(e.getMessage(), sql);
        }
    } // executeUpdate

    public static DataSet executeQuery(final Connection con, final String sql) throws ReportSQLException {
        return executeQuery(con, sql, Integer.MAX_VALUE);
    } // executeQuery

    public static DataSet executeQuery(Connection con, final String sql, final int maxRowLimit)
        throws ReportSQLException {
        DataSet ds = null;
        try {
            if (con.isClosed()) {
                con = getConnection();
            }
            if (con != null) {
                try (final Statement stmt = con.createStatement();
                        final ResultSet rs = stmt.executeQuery(sql)) {
                    logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql));
                    ds = new DataSet(rs, maxRowLimit);
                }
            }
        } catch (final SQLException e) {
            logger.warn("Failed to execute Query", e);
            throw new ReportSQLException(e.getMessage(), sql);
        }
        return ds;
    } // executeQuery

    public static DataSet executeQuery(final String sql, final String reportName, final String reportID)
        throws ReportSQLException {
        DataSet ds = null;
        try (final Connection con = getConnection();) {
            if (con != null) {
                try (final PreparedStatement preparedStatement = con.prepareStatement(sql);) {
                    if (!reportID.isEmpty()) {
                        preparedStatement.setString(1, reportID);
                        preparedStatement.setString(2, reportName);
                    } else {
                        preparedStatement.setString(1, reportName);
                    }

                    try (final ResultSet rs = preparedStatement.executeQuery();) {
                        logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql));
                        ds = new DataSet(rs, Integer.MAX_VALUE);
                    }
                }
            }
        } catch (final Exception e) {
            logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql), e);
            throw new ReportSQLException(e.getMessage(), e.getCause());
        }
        return ds;
    } // executeQuery

    public static DataSet executeQuery(final String sql) throws ReportSQLException {
        return executeQuery(sql, Integer.MAX_VALUE);
    } // executeQuery

    public static DataSet executeQuery(final String sql, final int maxRowLimit) throws ReportSQLException {
        try (final Connection con = getConnection()) {
            return executeQuery(con, sql, maxRowLimit);
        } catch (final ReportSQLException ex) {
            logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql));
            throw new ReportSQLException(ex.getMessage(), ex);
        } catch (final Exception ex1) {
            logger.warn("Failed to execute Query", ex1);
            throw new ReportSQLException(ex1.getMessage(), ex1.getCause());
        }
    } // executeQuery

    //For ZK Support

    public static int executeQuery(final ReportRuntime rr, final int dateOption) {

        int rowCount = 0;
        try (final Connection con = ConnectionUtils.getConnection(rr.getDBInfo());) {
            final String wholeSql = rr.getWholeSQL();
            final DataColumnType dc = rr.getColumnWhichNeedEnhancedPagination();
            String dateColId = dc.getColId();
            final String dataFormat = dc.getColFormat();
            if (dataFormat != null && dataFormat.length() > 0) {
                dateColId = "to_date(" + dateColId + ", '" + dataFormat + "')";
            }
            String sql = "";
            if (dateOption == 1) {
                sql = "select count(distinct to_char(" + dateColId + ", 'YYYY/MM')) from (" + wholeSql + ")";
            } else if (dateOption == 3) {
                sql = "select count(distinct to_char(" + dateColId + ", 'YYYY/MM/DD')) from (" + wholeSql + ")";
            } else if (dateOption == 2) {
                sql = "select count(distinct to_char(" + dateColId + ", 'YYYY')) from (" + wholeSql + ")";
            }
            final DataSet ds = executeQuery(con, sql);
            if(ds != null) {
                rowCount = ds.getInt(0, 0);
            }
        } catch (final Exception ex1) {
            logger.warn("Failed to execute Query", ex1);
        }
        return rowCount;
    }

    public static String nvls(final String s) {
        return (s == null) ? "" : s;
    }

    public static String nvl(final String s, final String sDefault) {
        return nvls(s).isEmpty() ? sDefault : s;
    }

    public String nvl(final String s) {
        return (s == null) ? "" : s;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    @Autowired
    public void setDataSource(final DataSource dataSource) {
        this.dataSource = dataSource;
    }
} // DbUtils