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.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.servlet.http.HttpSession;

import group_head_package.DataBaseIncomeExpense;

@ManagedBean(name = "odLoanCloserBean")
@ViewScoped
public class odLoanCloserBean implements Serializable 
{
	private String cid, receiptNo, payMode, txnNo, bankName, submittedBankName;
	
    private int branchId;

    private double loanBalance, currentEmiAmountDue, currentDueEmi, totalEmi, totalAmount, totalInterest, totalPrincipal, totalPaid, 
    			   totalDue, discountInterest, remainingInterest, closureCharges, paidAmount;

    private Date txnDate = new Date(), payDate = new Date();
    
    private boolean showNoDate, renLoanClosed = false;
    
    private ArrayList<OverDraftInfo> loanEMIList;
    
    private ArrayList<SelectItem> bankList;
    
    private OverDraftInfo selected;

    private final SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");



    public odLoanCloserBean() 
    {
    	fetchDetails();
    	receiptNo = String.valueOf(loanPayReceiptNo());
    	
    	Connection con=DataBaseConnection.JAVAConnection();
    	bankList=new DataBaseIncomeExpense().allBankList(con);
    }
    
    
    
    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();
    }


    // ================== Fetch Loan Details ==================
    public void loanDetails() {
        try (Connection conn = DataBaseConnection.JAVAConnection()) {
            String qq = "SELECT * FROM overdue_loan WHERE customer_id = ? AND status = 'active'";
            try (PreparedStatement ps = conn.prepareStatement(qq)) {
                ps.setString(1, selected.getCustomerId());
                ResultSet rs = ps.executeQuery();
                while (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();
        }
    }

    // ================== EMI Table Creation ==================
    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");
	        		   }
	        		   
	        		   renLoanClosed = true;
	        	   }
	           }
    		} 
        	catch (Exception e) 
        	{
    			e.printStackTrace();
    		}
		} 
    	catch (Exception e) 
    	{
			e.printStackTrace();
		}
    }
    


    public void pay() 
    {
        if (selected == null) return;
        try (Connection conn = DataBaseConnection.JAVAConnection()) {
            String sql = "INSERT INTO odloan_payment (customer_id, loan_no, installment_no, due_date, principal, interest, "
            		   + "total_amount, paid_amount, balance, pay_mode, txn_no, txn_date, status, remarks, branch_id) "
                       + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, cid);
            ps.setString(2, selected.getLoanNo());
            ps.setInt(3, selected.getInstallmentNo());
            ps.setString(4, selected.getEmiDateStr());
            ps.setDouble(5, selected.getPrincipalAmount());
            ps.setDouble(6, selected.getInterestAmount());
            ps.setDouble(7, selected.getEmiAmount());
            ps.setDouble(8, selected.getEmiAmount()); 
            ps.setString(9, selected.getPrincipalBalanceStr());
            ps.setString(10, selected.getPayMode());
            ps.setString(11, selected.getTxnNo());
            ps.setDate(12, new java.sql.Date(txnDate.getTime()));
            ps.setString(13, "paid");
            ps.setString(14, selected.getRemarks());
            ps.setInt(15, 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 void checkPayMode()
    {
    	if (payMode.equals("0")) 
		{
			showNoDate = false;
		}
		else if (payMode.equalsIgnoreCase("Cash") || payMode.equalsIgnoreCase("transfer")) 
		{
			showNoDate = false;
		}
		else
		{
			showNoDate = true;
		}
    }
    
    
    
    public void checkAmount()
    {
    	remainingInterest = totalInterest - totalInterestPaid();
    	
    	if (discountInterest > remainingInterest) 
    	{
			discountInterest = remainingInterest;
			FacesContext.getCurrentInstance().addMessage(null, 
					new FacesMessage("Discount cannot be greater than remaining interest"));
		}
    	
    	paidAmount = totalDue + remainingInterest - discountInterest + closureCharges;
    }
    
    
    
    public void submit()
    {
    	String receipt = String.valueOf(loanPayReceiptNo());
    	int emiNo = nextEmiPaidNo();
    	
    	Calendar cal = Calendar.getInstance();
        try 
        {
			cal.setTime(sdf.parse(selected.getTxnDateStr()));
		} 
        catch (ParseException e1) 
        {
			e1.printStackTrace();
		}
        cal.add(Calendar.MONTH, selected.getTerm());
        String dueDate = sdf.format(cal.getTime());
        
        
    	
        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, discount, charges, branch_id) "
                       + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, cid);
            ps.setString(2, selected.getLoanNo());
            ps.setString(3, receipt);
            ps.setInt(4, emiNo);
            ps.setString(5, dueDate);
            ps.setDouble(6, totalPrincipal);
            ps.setDouble(7, totalInterest);
            ps.setDouble(8, totalDue);
            ps.setDouble(9, paidAmount); 
            ps.setDouble(10, totalDue);
            ps.setDouble(11, 0);
            ps.setString(12, payMode);
            ps.setString(13, txnNo);
            ps.setDate(14, new java.sql.Date(txnDate.getTime()));
            ps.setString(15, bankName);
            ps.setString(16, submittedBankName);
            ps.setString(17, "closer");
            ps.setString(18, "");
            ps.setDouble(19, discountInterest);
            ps.setDouble(20, closureCharges);
            ps.setInt(21, branchId);

            int i = ps.executeUpdate();

            if (i > 0) 
            {
            	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 void printClientStatement()
    {
    	try 
    	{
    		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(true);
			ss.setAttribute("odClientStatement", loanEMIList);
			ss.setAttribute("odlTotalEmi", totalEmi);
			ss.setAttribute("odlTotalInterest", totalInterest);
			ss.setAttribute("odlTotalPrincipal", totalPrincipal);
			ss.setAttribute("odlTotalPaid", totalPaid);
			ss.setAttribute("odlTotalDue", totalDue);
			FacesContext.getCurrentInstance().getExternalContext().redirect("printOverDueClientStatement.xhtml");
		} 
    	catch (IOException e) 
    	{
			e.printStackTrace();
		}
    }
    
    
    
    public double totalInterestPaid() 
	{
		try (Connection con = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = con.createStatement();
			
			String qq = "select sum(interest) as total from odloan_payment where customer_id = '"+ selected.getCustomerId() +"' and "
					  + "loan_no = '"+ selected.getLoanNo() +"' and status = 'paid' ";
			
			ResultSet rs = st.executeQuery(qq);
			if (rs.next()) 
			{
				return rs.getDouble("total");
			}
		} 
		catch (Exception e) 
		{
			 e.printStackTrace();
		}
		return 0;
	}
    
    
    
    public int nextEmiPaidNo() 
	{
		try (Connection con = DataBaseConnection.JAVAConnection();) 
		{
			Statement st = con.createStatement();
			String qq = "select installment_no from odloan_payment order by id desc";
			ResultSet rs = st.executeQuery(qq);
			if (rs.next()) 
			{
				return rs.getInt("installment_no") + 1;
			}
		} 
		catch (Exception e) 
		{
			 e.printStackTrace();
		}
		return 0;
	}
    
    
    
    public int loanPayReceiptNo() 
	{
		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()) 
			{
				return rs.getInt("receipt_no") + 1;
			}
		} 
		catch (Exception e) 
		{
			 e.printStackTrace();
		}
		return 0;
	}

    
    
    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 String getReceiptNo() {
		return receiptNo;
	}



	public void setReceiptNo(String receiptNo) {
		this.receiptNo = receiptNo;
	}



	public double getDiscountInterest() {
		return discountInterest;
	}



	public void setDiscountInterest(double discountInterest) {
		this.discountInterest = discountInterest;
	}



	public double getRemainingInterest() {
		return remainingInterest;
	}



	public void setRemainingInterest(double remainingInterest) {
		this.remainingInterest = remainingInterest;
	}



	public double getClosureCharges() {
		return closureCharges;
	}



	public void setClosureCharges(double closureCharges) {
		this.closureCharges = closureCharges;
	}



	public double getPaidAmount() {
		return paidAmount;
	}



	public void setPaidAmount(double paidAmount) {
		this.paidAmount = paidAmount;
	}



	public String getPayMode() {
		return payMode;
	}



	public void setPayMode(String payMode) {
		this.payMode = payMode;
	}



	public Date getPayDate() {
		return payDate;
	}



	public void setPayDate(Date payDate) {
		this.payDate = payDate;
	}



	public ArrayList<SelectItem> getBankList() {
		return bankList;
	}



	public void setBankList(ArrayList<SelectItem> bankList) {
		this.bankList = bankList;
	}



	public boolean isShowNoDate() {
		return showNoDate;
	}



	public void setShowNoDate(boolean showNoDate) {
		this.showNoDate = showNoDate;
	}



	public String getTxnNo() {
		return txnNo;
	}



	public void setTxnNo(String txnNo) {
		this.txnNo = txnNo;
	}



	public String getBankName() {
		return bankName;
	}



	public void setBankName(String bankName) {
		this.bankName = bankName;
	}



	public String getSubmittedBankName() {
		return submittedBankName;
	}



	public void setSubmittedBankName(String submittedBankName) {
		this.submittedBankName = submittedBankName;
	}



	public boolean isRenLoanClosed() {
		return renLoanClosed;
	}



	public void setRenLoanClosed(boolean renLoanClosed) {
		this.renLoanClosed = renLoanClosed;
	}

}