/*
 * The Kuali Financial System, a comprehensive financial management system for higher education.
 *
 * Copyright 2005-2016 The Kuali Foundation
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.kuali.kfs.vnd.batch.dataaccess;

import org.apache.commons.lang.StringUtils;
import org.kuali.kfs.vnd.businessobject.DebarredVendorMatch;
import org.kuali.kfs.vnd.businessobject.VendorAddress;
import org.kuali.kfs.vnd.document.service.VendorService;
import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import java.util.ArrayList;
import java.util.List;

public class DebarredVendorDaoJdbc extends PlatformAwareDaoBaseJdbc implements DebarredVendorDao {
    private VendorService vendorService;
    private DebarredVendorMatchDao debarredVendorMatchDao;

    @Override
    public List<DebarredVendorMatch> match() {
        String active = "dtl.DOBJ_MAINT_CD_ACTV_IND = 'Y'";
        String joinDtl = " INNER JOIN pur_vndr_dtl_t dtl";
        String joinExcl = " INNER JOIN PUR_VNDR_EXCL_MT excl";
        String where = " WHERE " + active;
        String eplsFields = "excl.VNDR_EXCL_ID, excl.VNDR_EXCL_LOAD_DT, excl.VNDR_EXCL_NM, excl.VNDR_EXCL_LN1_ADDR, excl.VNDR_EXCL_LN2_ADDR, excl.VNDR_EXCL_CTY_NM" +
            ", excl.VNDR_EXCL_ST_CD, excl.VNDR_EXCL_PRVN_NM, excl.VNDR_EXCL_ZIP_CD, excl.VNDR_EXCL_OTHR_NM, excl.VNDR_EXCL_DESC_TXT";

        String selectName = "SELECT dtl.VNDR_HDR_GNRTD_ID, dtl.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID";
        String fromName = " FROM pur_vndr_dtl_t dtl";
        String name = filter("dtl.VNDR_NM", "., ");
        String eplsName = filter("excl.VNDR_EXCL_NM", "., ");
        String onName = " ON " + compare(name, eplsName, false); // use = to compare
        String sqlName = selectName + fromName + joinExcl + onName + where;

        String selectAlias = "SELECT als.VNDR_HDR_GNRTD_ID, als.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID";
        String fromAlias = " FROM pur_vndr_alias_t als";
        String onAlsDtl = " ON als.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND als.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID";
        String alias = filter("als.VNDR_ALIAS_NM", "., ");
        String eplsAlias = filter("excl.VNDR_EXCL_NM", "., ");
        String onAlias = " ON " + compare(alias, eplsAlias, false); // use = to compare
        String sqlAlias = selectAlias + fromAlias + joinDtl + onAlsDtl + joinExcl + onAlias + where;

        String selectAddr = "SELECT addr.VNDR_HDR_GNRTD_ID, addr.VNDR_DTL_ASND_ID, " + eplsFields + " , addr.VNDR_ADDR_GNRTD_ID";
        String fromAddr = " FROM pur_vndr_addr_t addr";
        String onAddrDtl = " ON addr.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND addr.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID";
        //
        String addr1 = filter("addr.VNDR_LN1_ADDR", ".,# ");
        String eplsAddr1 = filter("excl.VNDR_EXCL_LN1_ADDR", ".,# ");
        String cmpAddr1 = compare(addr1, eplsAddr1, true); // use LIKE to compare
        //
        String city = filter("addr.VNDR_CTY_NM", "., ");
        String eplsCity = filter("excl.VNDR_EXCL_CTY_NM", "., ");
        String cmpCity = compare(city, eplsCity, false); // use = to compare
        //
        String state = "upper(addr.VNDR_ST_CD)";
        String eplsState = "upper(excl.VNDR_EXCL_ST_CD)";
        String cmpState = compare(state, eplsState, false); // use = to compare
        //
        String zip = filter("addr.VNDR_ZIP_CD", "-");
        String eplsZip = filter("excl.VNDR_EXCL_ZIP_CD", "-");
        String cmpZip = compare(zip, eplsZip, false); // use = to compare
        String fullZip = "length(addr.VNDR_ZIP_CD) > 5";
        //
        String onAddr = " ON (" + cmpAddr1 + " OR " + cmpZip + " AND " + fullZip + ") AND " + cmpCity + " AND " + cmpState;
        String sqlAddr = selectAddr + fromAddr + joinDtl + onAddrDtl + joinExcl + onAddr + where;

        String max = ", MAX(VNDR_ADDR_GNRTD_ID)";
        String selectFields = "VNDR_HDR_GNRTD_ID, VNDR_DTL_ASND_ID, VNDR_EXCL_ID, VNDR_EXCL_LOAD_DT, VNDR_EXCL_NM, VNDR_EXCL_LN1_ADDR, VNDR_EXCL_LN2_ADDR, VNDR_EXCL_CTY_NM" +
            ", VNDR_EXCL_ST_CD, VNDR_EXCL_PRVN_NM, VNDR_EXCL_ZIP_CD, VNDR_EXCL_OTHR_NM, VNDR_EXCL_DESC_TXT";
        String select = "SELECT " + selectFields + max;
        String subqr = sqlName + " UNION " + sqlAlias + " UNION " + sqlAddr;
        String from = " FROM (" + subqr + ")";
        String group = " GROUP BY " + selectFields;
        String sql = select + from + group;


        List<DebarredVendorMatch> matches = new ArrayList<DebarredVendorMatch>();
        try {
            SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql);
            DebarredVendorMatch match;

            while (rs.next()) {
                match = new DebarredVendorMatch();
                match.setVendorHeaderGeneratedIdentifier(new Integer(rs.getInt(1)));
                match.setVendorDetailAssignedIdentifier(new Integer(rs.getInt(2)));
                match.setLoadDate(rs.getDate(4));
                match.setName(rs.getString(5));
                match.setAddress1(rs.getString(6));
                match.setAddress2(rs.getString(7));
                match.setCity(rs.getString(8));
                match.setState(rs.getString(9));
                match.setProvince(rs.getString(10));
                match.setZip(rs.getString(11));
                match.setAliases(rs.getString(12));
                match.setDescription(rs.getString(13));
                match.setAddressGeneratedId(rs.getLong(14));
                // didn't find a matched address, search for best one
                if (match.getAddressGeneratedId() == 0) {
                    match.setAddressGeneratedId(getMatchAddressId(match));
                }

                DebarredVendorMatch oldMatch = debarredVendorMatchDao.getPreviousVendorExcludeConfirmation(match);
                if (oldMatch == null) {
                    // store the match only if an exact old match doesn't exist
                    match.setConfirmStatusCode("U"); // status - Unprocessed
                    matches.add(match);
                }
            }
        } catch (Exception e) {
            // if exception occurs, return empty results
            throw new RuntimeException(e);
        }

        return matches;
    }

    /**
     * Gets the addressGeneratedId of the vendor address that matches best with the address of the
     * EPLS debarred vendor in the specified vendor exclude match.
     * If no address matches, returns the default address for IU campus.
     */
    protected long getMatchAddressId(DebarredVendorMatch match) {
        long bestid = 0;
        long defaultId = 0;
        int maxPriority = 0;
        List<VendorAddress> addresses = vendorService.getVendorDetail(match.getVendorHeaderGeneratedIdentifier(),
            match.getVendorDetailAssignedIdentifier()).getVendorAddresses();
        if (addresses == null) {
            return bestid;
        }

        for (VendorAddress address : addresses) {
            if (address.isVendorDefaultAddressIndicator()) {
                defaultId = address.getVendorAddressGeneratedIdentifier();
            }
            //each condition satisfied will increase the priority score for this address
            int priority = 0;
            String vendorAddr1 = StringUtils.replaceChars(address.getVendorLine1Address(), ".,# ", "");
            String eplsAddr1 = StringUtils.replaceChars(match.getAddress1(), ".,# ", "");
            if (StringUtils.equalsIgnoreCase(vendorAddr1, eplsAddr1)) {
                priority++;
            }
            String vendorCity = StringUtils.replaceChars(address.getVendorCityName(), "., ", "");
            String eplsCity = StringUtils.replaceChars(match.getCity(), "., ", "");
            if (StringUtils.equalsIgnoreCase(vendorCity, eplsCity)) {
                priority++;
            }
            if (StringUtils.equalsIgnoreCase(address.getVendorStateCode(), match.getState())) {
                priority++;
            }
            String vendorZip = StringUtils.substring(address.getVendorZipCode(), 0, 5);
            String eplsZip = StringUtils.substring(match.getZip(), 0, 5);
            if (StringUtils.equals(vendorZip, eplsZip)) {
                priority++;
            }
            if (priority >= maxPriority) {
                bestid = address.getVendorAddressGeneratedIdentifier();
                maxPriority = priority;
            }
        }
        if (bestid == 0) {
            bestid = defaultId;
        }
        return bestid;
    }

    protected String filter(String field, String charset) {
        // add upper function
        String upper = "upper(" + field + ")";
        if (charset == null)
            return upper;

        // add replace functions layer by layer to filter out the chars in the charset one by one
        String replace = upper;
        char[] chararr = charset.toCharArray();
        for (char ch : chararr) {
            // replace with empty string
            replace = "replace(" + replace + ", '" + ch + "', '')";
        }
        return replace;
    }

    protected String compare(String fieldl, String fieldr, boolean useLike) {
        String cmpstr = "";

        // whether neither field is null
        String notnulll = fieldl + " IS NOT NULL";
        String notnullr = fieldr + " IS NOT NULL";

        if (useLike) {
            // whether one of the two fields is substring of the other
            String like1 = notnullr + " AND " + fieldl + " LIKE '%'||" + fieldr + "||'%'";
            String like2 = notnulll + " AND " + fieldr + " LIKE '%'||" + fieldl + "||'%'";
            cmpstr += "(" + like1 + " OR " + like2 + ")"; // put () around the 'OR' to ensure integrity
        } else {
            // whether the two fields equal
            cmpstr = notnulll + " AND " + fieldl + " = " + fieldr;
        }

        return cmpstr;
    }

    /**
     * Gets the vendorService attribute.
     *
     * @return Returns the vendorService.
     */
    public VendorService getVendorService() {
        return vendorService;
    }

    /**
     * Sets the vendorService attribute value.
     *
     * @param vendorService The vendorService to set.
     */
    public void setVendorService(VendorService vendorService) {
        this.vendorService = vendorService;
    }

    /**
     * Gets the debarredVendorMatchDao attribute.
     *
     * @return Returns the debarredVendorMatchDao.
     */
    public DebarredVendorMatchDao getDebarredVendorMatchDao() {
        return debarredVendorMatchDao;
    }

    /**
     * Sets the debarredVendorMatchDao attribute value.
     *
     * @param debarredVendorMatchDao The debarredVendorMatchDao to set.
     */
    public void setDebarredVendorMatchDao(DebarredVendorMatchDao debarredVendorMatchDao) {
        this.debarredVendorMatchDao = debarredVendorMatchDao;
    }

}
