/**
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements. See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership. The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file 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.
 */
package org.apache.fineract.portfolio.account.service;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import org.apache.fineract.infrastructure.core.domain.JdbcSupport;
import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.organisation.monetary.data.CurrencyData;
import org.apache.fineract.portfolio.account.PortfolioAccountType;
import org.apache.fineract.portfolio.account.data.PortfolioAccountDTO;
import org.apache.fineract.portfolio.account.data.PortfolioAccountData;
import org.apache.fineract.portfolio.account.exception.AccountTransferNotFoundException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

@Service
public class PortfolioAccountReadPlatformServiceImpl implements PortfolioAccountReadPlatformService {

    private final JdbcTemplate jdbcTemplate;

    // mapper
    private final PortfolioSavingsAccountMapper savingsAccountMapper;
    private final PortfolioLoanAccountMapper loanAccountMapper;
    private final PortfolioLoanAccountRefundByTransferMapper accountRefundByTransferMapper;

    @Autowired
    public PortfolioAccountReadPlatformServiceImpl(final JdbcTemplate jdbcTemplate, DatabaseSpecificSQLGenerator sqlGenerator) {
        this.jdbcTemplate = jdbcTemplate;
        this.savingsAccountMapper = new PortfolioSavingsAccountMapper();
        this.loanAccountMapper = new PortfolioLoanAccountMapper();
        this.accountRefundByTransferMapper = new PortfolioLoanAccountRefundByTransferMapper(sqlGenerator);
    }

    @Override
    public PortfolioAccountData retrieveOne(final Long accountId, final Integer accountTypeId) {
        return retrieveOne(accountId, accountTypeId, null);
    }

    @Override
    public PortfolioAccountData retrieveOne(final Long accountId, final Integer accountTypeId, final String currencyCode) {

        Object[] sqlParams = new Object[] { accountId };
        PortfolioAccountData accountData = null;
        try {
            String sql = null;
            final PortfolioAccountType accountType = PortfolioAccountType.fromInt(accountTypeId);
            switch (accountType) {
                case INVALID:
                break;
                case LOAN:

                    sql = "select " + this.loanAccountMapper.schema() + " where la.id = ?";
                    if (currencyCode != null) {
                        sql += " and la.currency_code = ?";
                        sqlParams = new Object[] { accountId, currencyCode };
                    }

                    accountData = this.jdbcTemplate.queryForObject(sql, this.loanAccountMapper, sqlParams);
                break;
                case SAVINGS:
                    sql = "select " + this.savingsAccountMapper.schema() + " where sa.id = ?";
                    if (currencyCode != null) {
                        sql += " and sa.currency_code = ?";
                        sqlParams = new Object[] { accountId, currencyCode };
                    }

                    accountData = this.jdbcTemplate.queryForObject(sql, this.savingsAccountMapper, sqlParams);
                break;
            }
        } catch (final EmptyResultDataAccessException e) {
            throw new AccountTransferNotFoundException(accountId, e);
        }

        return accountData;
    }

    @Override
    public Collection<PortfolioAccountData> retrieveAllForLookup(final PortfolioAccountDTO portfolioAccountDTO) {

        final List<Object> sqlParams = new ArrayList<>();
        // sqlParams.add(portfolioAccountDTO.getClientId());
        Collection<PortfolioAccountData> accounts = null;
        String sql = null;
        long defaultAccountStatus = 300; // Active Status
        if (portfolioAccountDTO.getAccountStatus() != null) {
            defaultAccountStatus = portfolioAccountDTO.getFirstAccountStatus();
        }
        final PortfolioAccountType accountType = PortfolioAccountType.fromInt(portfolioAccountDTO.getAccountTypeId());
        switch (accountType) {
            case INVALID:
            break;
            case LOAN:
                sql = "select " + this.loanAccountMapper.schema() + " where ";
                if (portfolioAccountDTO.getClientId() != null) {
                    sql += " la.client_id = ? and la.loan_status_id in (?) ";
                    sqlParams.add(portfolioAccountDTO.getClientId());
                    sqlParams.add(defaultAccountStatus);
                } else {
                    sql += " la.loan_status_id in (?) ";
                    sqlParams.add(defaultAccountStatus);
                }
                if (portfolioAccountDTO.getCurrencyCode() != null) {
                    sql += " and la.currency_code = ?";
                    sqlParams.add(portfolioAccountDTO.getCurrencyCode());
                }

                accounts = this.jdbcTemplate.query(sql, this.loanAccountMapper, sqlParams.toArray()); // NOSONAR
            break;
            case SAVINGS:
                sql = "select " + this.savingsAccountMapper.schema() + " where ";
                if (portfolioAccountDTO.getClientId() != null) {
                    sql += " sa.client_id = ? and sa.status_enum in (?) ";
                    sqlParams.add(portfolioAccountDTO.getClientId());
                    sqlParams.add(defaultAccountStatus);
                } else {
                    sql += " sa.status_enum in (?) ";
                    sqlParams.add(defaultAccountStatus);
                }
                if (portfolioAccountDTO.getCurrencyCode() != null) {
                    sql += " and sa.currency_code = ?";
                    sqlParams.add(portfolioAccountDTO.getCurrencyCode());
                }

                if (portfolioAccountDTO.getDepositType() != null) {
                    sql += " and sa.deposit_type_enum = ?";
                    sqlParams.add(portfolioAccountDTO.getDepositType().shortValue());
                }

                if (portfolioAccountDTO.isExcludeOverDraftAccounts()) {
                    sql += " and sa.allow_overdraft = false";
                }

                if (portfolioAccountDTO.getClientId() == null && portfolioAccountDTO.getGroupId() != null) {
                    sql += " and sa.group_id = ? ";
                    sqlParams.add(portfolioAccountDTO.getGroupId());
                }

                accounts = this.jdbcTemplate.query(sql, this.savingsAccountMapper, sqlParams.toArray()); // NOSONAR
            break;
        }

        return accounts;
    }

    private static final class PortfolioSavingsAccountMapper implements RowMapper<PortfolioAccountData> {

        private final String schemaSql;

        PortfolioSavingsAccountMapper() {

            final StringBuilder sqlBuilder = new StringBuilder(400);
            sqlBuilder.append("sa.id as id, sa.account_no as accountNo, sa.external_id as externalId, ");
            sqlBuilder.append("c.id as clientId, c.display_name as clientName, ");
            sqlBuilder.append("g.id as groupId, g.display_name as groupName, ");
            sqlBuilder.append("sp.id as productId, sp.name as productName, ");
            sqlBuilder.append("s.id as fieldOfficerId, s.display_name as fieldOfficerName, ");
            sqlBuilder.append("sa.currency_code as currencyCode, sa.currency_digits as currencyDigits,");
            sqlBuilder.append("sa.currency_multiplesof as inMultiplesOf, ");
            sqlBuilder.append("curr.name as currencyName, curr.internationalized_name_code as currencyNameCode, ");
            sqlBuilder.append("curr.display_symbol as currencyDisplaySymbol ");
            sqlBuilder.append("from m_savings_account sa ");
            sqlBuilder.append("join m_savings_product sp ON sa.product_id = sp.id ");
            sqlBuilder.append("join m_currency curr on curr.code = sa.currency_code ");
            sqlBuilder.append("left join m_client c ON c.id = sa.client_id ");
            sqlBuilder.append("left join m_group g ON g.id = sa.group_id ");
            sqlBuilder.append("left join m_staff s ON s.id = sa.field_officer_id ");

            this.schemaSql = sqlBuilder.toString();
        }

        public String schema() {
            return this.schemaSql;
        }

        @Override
        public PortfolioAccountData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {

            final Long id = rs.getLong("id");
            final String accountNo = rs.getString("accountNo");
            final String externalId = rs.getString("externalId");

            final Long groupId = JdbcSupport.getLong(rs, "groupId");
            final String groupName = rs.getString("groupName");
            final Long clientId = JdbcSupport.getLong(rs, "clientId");
            final String clientName = rs.getString("clientName");

            final Long productId = rs.getLong("productId");
            final String productName = rs.getString("productName");

            final Long fieldOfficerId = rs.getLong("fieldOfficerId");
            final String fieldOfficerName = rs.getString("fieldOfficerName");

            final String currencyCode = rs.getString("currencyCode");
            final String currencyName = rs.getString("currencyName");
            final String currencyNameCode = rs.getString("currencyNameCode");
            final String currencyDisplaySymbol = rs.getString("currencyDisplaySymbol");
            final Integer currencyDigits = JdbcSupport.getInteger(rs, "currencyDigits");
            final Integer inMulitplesOf = JdbcSupport.getInteger(rs, "inMultiplesOf");
            final CurrencyData currency = new CurrencyData(currencyCode, currencyName, currencyDigits, inMulitplesOf, currencyDisplaySymbol,
                    currencyNameCode);

            return new PortfolioAccountData(id, accountNo, externalId, groupId, groupName, clientId, clientName, productId, productName,
                    fieldOfficerId, fieldOfficerName, currency);
        }
    }

    private static final class PortfolioLoanAccountMapper implements RowMapper<PortfolioAccountData> {

        private final String schemaSql;

        PortfolioLoanAccountMapper() {

            final StringBuilder sqlBuilder = new StringBuilder(400);
            sqlBuilder.append("la.id as id, la.account_no as accountNo, la.external_id as externalId, ");
            sqlBuilder.append("c.id as clientId, c.display_name as clientName, ");
            sqlBuilder.append("g.id as groupId, g.display_name as groupName, ");
            sqlBuilder.append("lp.id as productId, lp.name as productName, ");
            sqlBuilder.append("s.id as fieldOfficerId, s.display_name as fieldOfficerName, ");
            sqlBuilder.append("la.currency_code as currencyCode, la.currency_digits as currencyDigits,");
            sqlBuilder.append("la.currency_multiplesof as inMultiplesOf, ");
            sqlBuilder.append("la.total_overpaid_derived as totalOverpaid, ");
            sqlBuilder.append("curr.name as currencyName, curr.internationalized_name_code as currencyNameCode, ");
            sqlBuilder.append("curr.display_symbol as currencyDisplaySymbol ");
            sqlBuilder.append("from m_loan la ");
            sqlBuilder.append("join m_product_loan lp ON la.product_id = lp.id ");
            sqlBuilder.append("join m_currency curr on curr.code = la.currency_code ");
            sqlBuilder.append("left join m_client c ON c.id = la.client_id ");
            sqlBuilder.append("left join m_group g ON g.id = la.group_id ");
            sqlBuilder.append("left join m_staff s ON s.id = la.loan_officer_id ");

            this.schemaSql = sqlBuilder.toString();
        }

        public String schema() {
            return this.schemaSql;
        }

        @Override
        public PortfolioAccountData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {

            final Long id = rs.getLong("id");
            final String accountNo = rs.getString("accountNo");
            final String externalId = rs.getString("externalId");

            final Long groupId = JdbcSupport.getLong(rs, "groupId");
            final String groupName = rs.getString("groupName");
            final Long clientId = JdbcSupport.getLong(rs, "clientId");
            final String clientName = rs.getString("clientName");

            final Long productId = rs.getLong("productId");
            final String productName = rs.getString("productName");

            final Long fieldOfficerId = rs.getLong("fieldOfficerId");
            final String fieldOfficerName = rs.getString("fieldOfficerName");

            final String currencyCode = rs.getString("currencyCode");
            final String currencyName = rs.getString("currencyName");
            final String currencyNameCode = rs.getString("currencyNameCode");
            final String currencyDisplaySymbol = rs.getString("currencyDisplaySymbol");
            final Integer currencyDigits = JdbcSupport.getInteger(rs, "currencyDigits");
            final Integer inMulitplesOf = JdbcSupport.getInteger(rs, "inMultiplesOf");
            final BigDecimal amtForTransfer = JdbcSupport.getBigDecimalDefaultToNullIfZero(rs, "totalOverpaid");
            final CurrencyData currency = new CurrencyData(currencyCode, currencyName, currencyDigits, inMulitplesOf, currencyDisplaySymbol,
                    currencyNameCode);

            return new PortfolioAccountData(id, accountNo, externalId, groupId, groupName, clientId, clientName, productId, productName,
                    fieldOfficerId, fieldOfficerName, currency, amtForTransfer);
        }
    }

    private static final class PortfolioLoanAccountRefundByTransferMapper implements RowMapper<PortfolioAccountData> {

        private final String schemaSql;

        PortfolioLoanAccountRefundByTransferMapper(DatabaseSpecificSQLGenerator sqlGenerator) {

            final StringBuilder amountQueryString = new StringBuilder(400);
            amountQueryString.append("(select (SUM(COALESCE(mr.principal_completed_derived, 0)) +");
            amountQueryString.append("SUM(COALESCE(mr.interest_completed_derived, 0)) + ");
            amountQueryString.append("SUM(COALESCE(mr.fee_charges_completed_derived, 0)) + ");
            amountQueryString.append(" SUM(COALESCE(mr.penalty_charges_completed_derived, 0))) as total_in_advance_derived");
            amountQueryString.append(" from m_loan ml INNER JOIN m_loan_repayment_schedule mr on mr.loan_id = ml.id");
            amountQueryString.append(" where ml.id=? and ml.loan_status_id = 300");
            amountQueryString.append("  and  mr.duedate >= " + sqlGenerator.currentDate() + " group by ml.id having");
            amountQueryString.append(" (SUM(COALESCE(mr.principal_completed_derived, 0)) + ");
            amountQueryString.append(" SUM(COALESCE(mr.interest_completed_derived, 0)) + ");
            amountQueryString.append("SUM(COALESCE(mr.fee_charges_completed_derived, 0)) + ");
            amountQueryString.append("SUM(COALESCE(mr.penalty_charges_completed_derived, 0))) > 0) as totalOverpaid ");

            final StringBuilder sqlBuilder = new StringBuilder(400);
            sqlBuilder.append("la.id as id, la.account_no as accountNo, la.external_id as externalId, ");
            sqlBuilder.append("c.id as clientId, c.display_name as clientName, ");
            sqlBuilder.append("g.id as groupId, g.display_name as groupName, ");
            sqlBuilder.append("lp.id as productId, lp.name as productName, ");
            sqlBuilder.append("s.id as fieldOfficerId, s.display_name as fieldOfficerName, ");
            sqlBuilder.append("la.currency_code as currencyCode, la.currency_digits as currencyDigits,");
            sqlBuilder.append("la.currency_multiplesof as inMultiplesOf, ");
            sqlBuilder.append(amountQueryString.toString());
            sqlBuilder.append(", ");
            sqlBuilder.append("curr.name as currencyName, curr.internationalized_name_code as currencyNameCode, ");
            sqlBuilder.append("curr.display_symbol as currencyDisplaySymbol ");
            sqlBuilder.append("from m_loan la ");
            sqlBuilder.append("join m_product_loan lp ON la.product_id = lp.id ");
            sqlBuilder.append("join m_currency curr on curr.code = la.currency_code ");
            sqlBuilder.append("left join m_client c ON c.id = la.client_id ");
            sqlBuilder.append("left join m_group g ON g.id = la.group_id ");
            sqlBuilder.append("left join m_staff s ON s.id = la.loan_officer_id ");

            this.schemaSql = sqlBuilder.toString();
        }

        public String schema() {
            return this.schemaSql;
        }

        @Override
        public PortfolioAccountData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {

            final Long id = rs.getLong("id");
            final String accountNo = rs.getString("accountNo");
            final String externalId = rs.getString("externalId");

            final Long groupId = JdbcSupport.getLong(rs, "groupId");
            final String groupName = rs.getString("groupName");
            final Long clientId = JdbcSupport.getLong(rs, "clientId");
            final String clientName = rs.getString("clientName");

            final Long productId = rs.getLong("productId");
            final String productName = rs.getString("productName");

            final Long fieldOfficerId = rs.getLong("fieldOfficerId");
            final String fieldOfficerName = rs.getString("fieldOfficerName");

            final String currencyCode = rs.getString("currencyCode");
            final String currencyName = rs.getString("currencyName");
            final String currencyNameCode = rs.getString("currencyNameCode");
            final String currencyDisplaySymbol = rs.getString("currencyDisplaySymbol");
            final Integer currencyDigits = JdbcSupport.getInteger(rs, "currencyDigits");
            final Integer inMulitplesOf = JdbcSupport.getInteger(rs, "inMultiplesOf");
            final BigDecimal amtForTransfer = JdbcSupport.getBigDecimalDefaultToNullIfZero(rs, "totalOverpaid");
            final CurrencyData currency = new CurrencyData(currencyCode, currencyName, currencyDigits, inMulitplesOf, currencyDisplaySymbol,
                    currencyNameCode);

            return new PortfolioAccountData(id, accountNo, externalId, groupId, groupName, clientId, clientName, productId, productName,
                    fieldOfficerId, fieldOfficerName, currency, amtForTransfer);
        }
    }

    @Override
    public PortfolioAccountData retrieveOneByPaidInAdvance(Long accountId, Integer accountTypeId) {
        // TODO Auto-generated method stub
        Object[] sqlParams = new Object[] { accountId, accountId };
        PortfolioAccountData accountData = null;
        // String currencyCode = null;
        try {
            String sql = null;
            // final PortfolioAccountType accountType =
            // PortfolioAccountType.fromInt(accountTypeId);

            sql = "select " + this.accountRefundByTransferMapper.schema() + " where la.id = ?";
            /*
             * if (currencyCode != null) { sql += " and la.currency_code = ?"; sqlParams = new Object[] {accountId ,
             * accountId,currencyCode }; }
             */

            accountData = this.jdbcTemplate.queryForObject(sql, this.accountRefundByTransferMapper, sqlParams);

        } catch (final EmptyResultDataAccessException e) {
            throw new AccountTransferNotFoundException(accountId, e);
        }

        return accountData;
    }
}