package main_package;

import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpSession;

@ManagedBean(name = "odlPaymentBean")
@ViewScoped

public class OverDueLoanRepaymentBean implements Serializable 
{
	private String cid, fromBank, submitBank;
	
    private int branchId;

    private double loanBalance, currentEmiAmountDue, currentDueEmi, totalEmi, totalAmount,
                   totalInterest, totalPrincipal, totalPaid, totalDue, penaltyAmount = 0;

    private Date txnDate = new Date();
    
    private ArrayList<OverDraftInfo> loanEMIList, paymentList;
    
    private OverDraftInfo selected;

    private final SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");



    public OverDueLoanRepaymentBean() 
    {
    	fetchDetails();
    }
    
    
    
    public void fetchDetails()
    {
    	HttpSession ss = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
        branchId = (int) ss.getAttribute("branchId");
        selected = (OverDraftInfo) ss.getAttribute("odCustomerData");
        
        cid = selected.getCustomerId();
        
        loanDetails();
        createLoanEMIList();
        loanPayment();
    }


    
    public void loanDetails() 
	{
        try (Connection conn = DataBaseConnection.JAVAConnection()) 
        {
            String qq = "SELECT * FROM overdue_loan WHERE customer_id = ? AND branch_id = ? AND status = 'active'";
          
            try (PreparedStatement ps = conn.prepareStatement(qq)) 
            {
                ps.setString(1, selected.getCustomerId());
                ps.setInt(2, branchId);
                
                ResultSet rs = ps.executeQuery();
                if (rs.next()) 
                {
                    selected.setLoanNo(rs.getString("loan_no"));
                    selected.setSanctionLimit(rs.getDouble("sanction_limit"));
                    selected.setInterestRate(rs.getInt("interest_rate"));
                    selected.setFees(rs.getDouble("fees"));
                    selected.setPayMode(rs.getString("pay_mode"));
                    selected.setTxnNo(rs.getString("txn_no"));
                    selected.setTxnDateStr(sdf.format(rs.getDate("txn_date")));
                    selected.setUtilizedAmount(rs.getDouble("utilized_amount"));
                    selected.setTerm(rs.getInt("term"));
                    selected.setFrequency(rs.getInt("frequency"));
                    selected.setDaysOutstanding(rs.getInt("days"));
                    selected.setInterestDue(rs.getDouble("interest_due"));
                }
            }
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }



    public void createLoanEMIList() 
    {
        loanEMIList = new ArrayList<>();
        totalEmi = totalInterest = totalPrincipal = totalPaid = totalDue = 0;
        loanBalance = currentEmiAmountDue = currentDueEmi = 0;

        try (Connection conn = DataBaseConnection.JAVAConnection()) 
        {
            double principal = selected.getUtilizedAmount();
            double totalInterest = selected.getInterestDue();
            int term = selected.getTerm();
            double interestPerEmi = totalInterest / term;
            double principalPerEmi = principal / term;
            double emi = principalPerEmi + interestPerEmi;

            Calendar cal = Calendar.getInstance();
            cal.setTime(sdf.parse(selected.getTxnDateStr()));
            cal.add(Calendar.MONTH, 1);

            double remainingPrincipal = principal;

            String query = "SELECT installment_no, status, paid_amount, txn_date "
                         + "FROM odloan_payment WHERE customer_id=? AND loan_no=? "
                         + " AND branch_id = ? AND status = 'paid' ";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, selected.getCustomerId());
            ps.setString(2, selected.getLoanNo());
            ps.setInt(3, branchId);
            ResultSet rs = ps.executeQuery();

            Map<Integer, OverDraftInfo> paymentMap = new HashMap<>();
            while (rs.next()) {
                OverDraftInfo info = new OverDraftInfo();
                info.setInstallmentNo(rs.getInt("installment_no"));
                info.setStatus(rs.getString("status"));
                info.setPaidAmount(rs.getDouble("paid_amount"));
                if (rs.getDate("txn_date") != null)
                    info.setTxnDateStr(sdf.format(rs.getDate("txn_date")));
                paymentMap.put(info.getInstallmentNo(), info);
            }

            for (int i = 1; i <= term; i++) {
                OverDraftInfo emiRow = new OverDraftInfo();
                emiRow.setLoanNo(selected.getLoanNo());
                emiRow.setInstallmentNo(i);
                emiRow.setEmiDateStr(sdf.format(cal.getTime()));

                double principalComponent = principalPerEmi;
                double interestComponent = interestPerEmi;
                remainingPrincipal -= principalComponent;
                if (remainingPrincipal < 0) remainingPrincipal = 0;

                emiRow.setEmiAmount(round(emi));
                emiRow.setInterestAmount(round(interestComponent));
                emiRow.setPrincipalAmount(round(principalComponent));
                emiRow.setPrincipalBalanceStr(String.format("%.2f", remainingPrincipal));

                OverDraftInfo payInfo = paymentMap.get(i);
                String status = "upcoming";
                double paidAmount = 0;
                double dueAmount = emi;

                if (payInfo != null) {
                    status = payInfo.getStatus();
                    paidAmount = payInfo.getPaidAmount();
                    dueAmount = emi - paidAmount;
                    if (dueAmount < 0) dueAmount = 0;
                } else {
                    Date today = new Date();
                    if (cal.getTime().before(today)) {
                        status = "due";
                        currentDueEmi++;
                        currentEmiAmountDue += emi;
                    }
                }

                emiRow.setPaidAmount(round(paidAmount));
                emiRow.setDueAmount(round(dueAmount));
                emiRow.setStatus(status);
                emiRow.setColorStatus(status);

                if (payInfo != null && "paid".equalsIgnoreCase(payInfo.getStatus())) {
                    emiRow.setRenPay(true);   // hide button if already paid
                } else {
                    emiRow.setRenPay(false);  // show button if not paid
                }

                totalEmi += emi;
                this.totalInterest += interestComponent;
                totalPrincipal += principalComponent;
                totalPaid += paidAmount;
                totalDue += dueAmount;

                loanEMIList.add(emiRow);
                cal.add(Calendar.MONTH, 1);
            }
            loanBalance = remainingPrincipal;
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
        
        checkForLoser();
    }
    
    
    
    public void checkForLoser()
    {
    	try(Connection conn = DataBaseConnection.JAVAConnection()) 
    	{
    		String query = "SELECT status FROM odloan_payment WHERE customer_id=? AND loan_no = ? AND branch_id = ? AND "
					 	 + "status = 'closer' ";
    		
    		try(PreparedStatement ps = conn.prepareStatement(query)) 
        	{
	           ps.setString(1, selected.getCustomerId());
	           ps.setString(2, selected.getLoanNo());
	           ps.setInt(3, branchId);
	           
	           ResultSet rs = ps.executeQuery();
	           if(rs.next())
	           {
	        	   if (rs.getString("status").equalsIgnoreCase("closer")) 
	        	   {
	        		   for (OverDraftInfo od : loanEMIList)
	        		   {
	        			   od.setStatus("paid");
	        			   od.setRenPay(true);
	        			   od.setColorStatus("paid");
	        		   }
	        	   }
	           }
    		} 
        	catch (Exception e) 
        	{
    			e.printStackTrace();
    		}
		} 
    	catch (Exception e) 
    	{
			e.printStackTrace();
		}
    }
    
    
    
    public void loanPayment()
    {
    	paymentList = new ArrayList<>();
    	
    	try(Connection conn = DataBaseConnection.JAVAConnection()) 
    	{
    		String qq = "SELECT * FROM odloan_payment where customer_id = ? AND loan_no = ? AND branch_id = ? AND "
    				  + "status != 'inactive' ";
    		
    		try(PreparedStatement ps = conn.prepareStatement(qq)) 
        	{
    			ps.setString(1, cid);
    			ps.setString(2, selected.getLoanNo());
    			ps.setInt(3, branchId);
    			
    			ResultSet rs = ps.executeQuery();
    			while(rs.next())
    			{
    				OverDraftInfo od = new OverDraftInfo();
    				
    				od.setId(rs.getString("id"));
    				od.setCustomerId(rs.getString("customer_id"));
    				od.setLoanNo(rs.getString("loan_no"));
    				od.setInstallmentNo(rs.getInt("installment_no"));
    				od.setDueDateStr(rs.getString("due_date"));
    				od.setPrincipalAmount(rs.getDouble("principal"));
    				od.setInterestAmount(rs.getDouble("interest"));
    				od.setEmiAmount(rs.getDouble("emi_amount"));
    				od.setPrincipalBalanceStr(rs.getString("balance"));
    				od.setPenaltyAmount(rs.getDouble("penalty_amount"));
    				od.setPayMode(rs.getString("pay_mode"));
    				od.setTxnNo(rs.getString("txn_no"));
    				
    				String td = sdf.format(rs.getDate("txn_date"));
    				od.setTxnDateStr(td);
    				
    				paymentList.add(od);
    			}
    		} 
        	catch (Exception e) 
        	{
    			e.printStackTrace();
    		}
		} 
    	catch (Exception e) 
    	{
			e.printStackTrace();
		}
    }
    

    
    public void pay() 
    {
    	String receipt = String.valueOf(loanPayReceiptNo());
    	
        if (selected == null) return;
        
        try (Connection conn = DataBaseConnection.JAVAConnection()) 
        {
            String sql = "INSERT INTO odloan_payment (customer_id, loan_no, receipt_no, installment_no, due_date, principal, "
            		   + "interest, emi_amount, paid_amount, balance, penalty_amount, pay_mode, txn_no, txn_date, from_bank, "
            		   + "submit_bank, status, remarks, branch_id) "
                       + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, cid);
            ps.setString(2, selected.getLoanNo());
            ps.setString(3, receipt);
            ps.setInt(4, selected.getInstallmentNo());
            ps.setString(5, selected.getEmiDateStr());
            ps.setDouble(6, selected.getPrincipalAmount());
            ps.setDouble(7, selected.getInterestAmount());
            ps.setDouble(8, selected.getEmiAmount());
            ps.setDouble(9, selected.getEmiAmount()); 
            ps.setString(10, selected.getPrincipalBalanceStr());
            ps.setDouble(11, penaltyAmount);
            ps.setString(12, selected.getPayMode());
            ps.setString(13, selected.getTxnNo());
            ps.setDate(14, new java.sql.Date(txnDate.getTime()));
            ps.setString(15, fromBank);
            ps.setString(16, submitBank);
            ps.setString(17, "paid");
            ps.setString(18, selected.getRemarks());
            ps.setInt(19, branchId);

            ps.executeUpdate();

            FacesContext.getCurrentInstance().addMessage(null,
                new javax.faces.application.FacesMessage("Payment Successful"));
            
            fetchDetails();

            txnDate = new Date();
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
            FacesContext.getCurrentInstance().addMessage(null,
                new javax.faces.application.FacesMessage(javax.faces.application.FacesMessage.SEVERITY_ERROR, "Error", "Payment failed."));
        }
    }
    
    
    
    public int loanPayReceiptNo() 
	{
		int i=1;
		
		try (Connection con = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = con.createStatement();
			String qq = "select receipt_no from odloan_payment order by id desc";
			ResultSet rs = st.executeQuery(qq);
			if (rs.next()) 
			{
				i= rs.getInt("receipt_no")+1;
			}
		} 
		catch (Exception e) 
		{
			 e.printStackTrace();
		}
		return i;
	}
    
    
    
    public void loanCloser()
    {
    	try 
    	{
			FacesContext.getCurrentInstance().getExternalContext().redirect("odLoanCloser.xhtml");
		} 
    	catch (IOException e) 
    	{
			e.printStackTrace();
		}
    }
    


    private double round(double val) 
    {
        return Math.round(val * 100.0) / 100.0;
    }



    public OverDraftInfo getSelected() { return selected; }
    public void setSelected(OverDraftInfo selected) { this.selected = selected; }
    public ArrayList<OverDraftInfo> getLoanEMIList() { return loanEMIList; }
    public double getLoanBalance() { return loanBalance; }
    public double getCurrentEmiAmountDue() { return currentEmiAmountDue; }
    public double getCurrentDueEmi() { return currentDueEmi; }
    public double getTotalEmi() { return totalEmi; }
    public double getTotalInterest() { return totalInterest; }
    public double getTotalPrincipal() { return totalPrincipal; }
    public double getTotalPaid() { return totalPaid; }
    public double getTotalDue() { return totalDue; }


	public double getTotalAmount() {
		return totalAmount;
	}


	public void setTotalAmount(double totalAmount) {
		this.totalAmount = totalAmount;
	}


	public void setLoanBalance(double loanBalance) {
		this.loanBalance = loanBalance;
	}


	public void setCurrentEmiAmountDue(double currentEmiAmountDue) {
		this.currentEmiAmountDue = currentEmiAmountDue;
	}


	public void setCurrentDueEmi(double currentDueEmi) {
		this.currentDueEmi = currentDueEmi;
	}


	public void setTotalEmi(double totalEmi) {
		this.totalEmi = totalEmi;
	}


	public void setTotalInterest(double totalInterest) {
		this.totalInterest = totalInterest;
	}


	public void setTotalPrincipal(double totalPrincipal) {
		this.totalPrincipal = totalPrincipal;
	}


	public void setTotalPaid(double totalPaid) {
		this.totalPaid = totalPaid;
	}


	public void setTotalDue(double totalDue) {
		this.totalDue = totalDue;
	}


	public void setLoanEMIList(ArrayList<OverDraftInfo> loanEMIList) {
		this.loanEMIList = loanEMIList;
	}


	public Date getTxnDate() {
		return txnDate;
	}


	public void setTxnDate(Date txnDate) {
		this.txnDate = txnDate;
	}


	public String getCid() {
		return cid;
	}


	public void setCid(String cid) {
		this.cid = cid;
	}



	public double getPenaltyAmount() {
		return penaltyAmount;
	}



	public void setPenaltyAmount(double penaltyAmount) {
		this.penaltyAmount = penaltyAmount;
	}



	public ArrayList<OverDraftInfo> getPaymentList() {
		return paymentList;
	}



	public void setPaymentList(ArrayList<OverDraftInfo> paymentList) {
		this.paymentList = paymentList;
	}



	public String getFromBank() {
		return fromBank;
	}



	public void setFromBank(String fromBank) {
		this.fromBank = fromBank;
	}



	public String getSubmitBank() {
		return submitBank;
	}



	public void setSubmitBank(String submitBank) {
		this.submitBank = submitBank;
	}

}