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.text.SimpleDateFormat;
import java.util.ArrayList;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpSession;

@ManagedBean(name = "odApplications")
@ViewScoped

public class OdApplicationListBean implements Serializable
{	
	private String status = "pending";
	
	private int branchId;
	
	private boolean renApprove, renReject, renLoanForm;
		
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"), sdfShow = new SimpleDateFormat("dd-MM-yyyy");
			
	ArrayList<OverDraftInfo> applicationList;
	
	OverDraftInfo selected;	
	
	
	public OdApplicationListBean() 
	{		
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
	    branchId = (int) ss.getAttribute("branchId");
	    
	    search();
	}
	
	
	
	public void search()
	{
		if (status.equalsIgnoreCase("Pending")) 
		{
			renApprove = true;
			renReject = true;
			renLoanForm = false;
		}
		else if (status.equalsIgnoreCase("Approved")) 
		{
			renApprove = false;
			renReject = false;
			renLoanForm = true;
		}
		else 
		{
			renApprove = false;
			renReject = false;
			renLoanForm = false;
		}
		
		overDraftApplicationListByBranchId();
	}
	
	
	
	public void overDraftApplicationListByBranchId()
    {
		applicationList = new ArrayList<>();
		
		try(Connection conn = DataBaseConnection.JAVAConnection()) 
		{	
			String qq = "SELECT * FROM od_application WHERE branch_id = ? AND application_status = ? AND status = 'active' "; 
			
			try(PreparedStatement st = conn.prepareStatement(qq)) 
			{	
				st.setInt(1, branchId);
				st.setString(2, status);
				
				ResultSet rr=st.executeQuery();
				while(rr.next())
				{
					OverDraftInfo ss = new OverDraftInfo();
					
					ss.setName(rr.getString("name"));
					ss.setPhoto(rr.getString("photo"));
					ss.setMobile(rr.getString("mobile"));				
					ss.setEmail(rr.getString("email"));
					ss.setGender(rr.getString("gender"));
					ss.setAddress(rr.getString("address"));
					ss.setPinCode(rr.getString("pin_code"));
					ss.setAadhaarNumber(rr.getString("aadhar_no"));
					ss.setAadharVerified(rr.getBoolean("aadhar_verified"));
					ss.setPanNo(rr.getString("pan_no"));
					ss.setPanVerified(rr.getBoolean("pan_verified"));
					ss.setBankName(rr.getString("bank_name"));
					ss.setAccountNo(rr.getString("account_no"));
					ss.setBankIfsc(rr.getString("bank_ifsc"));
					ss.setCustomerId(rr.getString("customer_id"));
					ss.setMonthlyIncome(rr.getDouble("monthly_income"));
					ss.setEmploymentType(rr.getString("emp_type"));
					ss.setOrgName(rr.getString("org_name"));
					ss.setId(rr.getString("id"));
					
					SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
					String ad = sdf.format(rr.getDate("application_date"));
					ss.setApplicationDateStr(ad);
					
					if (rr.getDate("dob") != null) 
					{
						String dob = sdf.format(rr.getDate("dob"));
						ss.setDobStr(dob);
					}
					
					ss.setRenLoan(checkLoanProvided(ss.getCustomerId()));
					
					applicationList.add(ss);		
				}					
			} 
			catch (Exception e)
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}		
    }
	
	
	
	public boolean checkLoanProvided(String cid)
	{
		try(Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			String qq = "SELECT customer_id FROM overdue_loan WHERE customer_id = ? AND status = 'active' ";
			
			try(PreparedStatement ps = conn.prepareStatement(qq)) 
			{
				ps.setString(1, cid);
				
				ResultSet rs = ps.executeQuery();
				if (rs.next()) 
				{
					if (cid.equalsIgnoreCase(rs.getString("customer_id"))) 
					{
						return true;
					}
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return false;
	}
	
	
	
	public void approveApplication()
	{
		try(Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			String qq = "UPDATE od_application SET application_status = ? WHERE branch_id = ? AND customer_id = ? ";
			
			try(PreparedStatement ps = conn.prepareStatement(qq)) 
			{
				ps.setString(1, "approved");
				ps.setInt(2, branchId);
				ps.setString(3, selected.getCustomerId());
				
				int i = ps.executeUpdate();
				if (i > 0) 
				{
					FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Application Approved"));
					overDraftApplicationListByBranchId();
				}
				else 
				{
					FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Application not Approved"));
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	
	
	public void rejectApplication()
	{
		try(Connection conn = DataBaseConnection.JAVAConnection()) 
		{
			String qq = "UPDATE od_application SET application_status = ? WHERE branch_id = ? AND customer_id = ? ";
			
			try(PreparedStatement ps = conn.prepareStatement(qq)) 
			{
				ps.setString(1, "rejected");
				ps.setInt(2, branchId);
				ps.setString(3, selected.getCustomerId());
				
				int i = ps.executeUpdate();
				if (i > 0) 
				{
					FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Application Rejected"));
					overDraftApplicationListByBranchId();
				}
				else 
				{
					FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Application Not Rejected"));
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	
	
	public void loanForm()
	{
		try 
		{
			HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(true);
			ss.setAttribute("odCustomerData", selected);
			FacesContext.getCurrentInstance().getExternalContext().redirect("overDraftLoanForm.xhtml");
		} 
		catch (IOException e) 
		{
			e.printStackTrace();
		}
	}
	
	
	
	public void printAgreement()
	{
		try 
		{
			HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(true);
			ss.setAttribute("odCustomerData", selected);
			FacesContext.getCurrentInstance().getExternalContext().redirect("overdueLoanAgreement.xhtml");
		} 
		catch (IOException e) 
		{
			e.printStackTrace();
		}
	}
	
	
	
	public void payEMIForm()
	{
		try 
		{
			HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(true);
			ss.setAttribute("odCustomerData", selected);
			FacesContext.getCurrentInstance().getExternalContext().redirect("overDueLoanRepayment.xhtml");
		} 
		catch (IOException e) 
		{
			e.printStackTrace();
		}
	}



	public ArrayList<OverDraftInfo> getApplicationList() {
		return applicationList;
	}



	public void setApplicationList(ArrayList<OverDraftInfo> applicationList) {
		this.applicationList = applicationList;
	}



	public OverDraftInfo getSelected() {
		return selected;
	}



	public void setSelected(OverDraftInfo selected) {
		this.selected = selected;
	}



	public String getStatus() {
		return status;
	}



	public void setStatus(String status) {
		this.status = status;
	}



	public boolean isRenApprove() {
		return renApprove;
	}



	public void setRenApprove(boolean renApprove) {
		this.renApprove = renApprove;
	}



	public boolean isRenReject() {
		return renReject;
	}



	public void setRenReject(boolean renReject) {
		this.renReject = renReject;
	}



	public boolean isRenLoanForm() {
		return renLoanForm;
	}



	public void setRenLoanForm(boolean renLoanForm) {
		this.renLoanForm = renLoanForm;
	}
}