package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

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 main_package.DataBaseMethods;
import main_package.DataBaseMethodsHemant;
import main_package.DataBaseConnection;
import main_package.DataBaseMethodTaheer;
import main_package.UserInfo;
import report_package.DataBaseReportMethods;

import org.primefaces.context.RequestContext;

import com.mysql.jdbc.Statement;

@ManagedBean(name="ptpEmi")
@ViewScoped
public class ptpEmiReportBean implements Serializable
{
	Date startDate=new Date(),endDate=new Date();
	ArrayList<UserInfo> list,multipleRows=new ArrayList<>();
	double totalAmount;
	String category, userId, frequency, dsaCode;
	boolean show,disDsa;
	ArrayList<SelectItem> dsaList;
	int branchId;
	String branchUserId;
	List<SelectItem> branchList = new ArrayList<SelectItem>();
	boolean disAll;
	String userType;
	
	//ptp variables 
	 String ptpMemberNo, ptpLoanNo, ptpRemarks,ptpEmiNo,ptpCreatedBy;
	 Date ptpDate;
	 double ptpEmiAmt, ptpAmount;
	 
	// Bulk PTP variables
	 Date bulkPtpDate;
	 String bulkPtpRemarks;
	
	DataBaseMethods obj = new DataBaseMethods();
	DataBaseReportMethods obj1 = new DataBaseReportMethods();
	DataBaseMethodsHemant dbmh = new DataBaseMethodsHemant();
	DataBaseMethodTaheer dbmt = new DataBaseMethodTaheer();
	DataBaseMethodsRaviK dbrk = new DataBaseMethodsRaviK();
	
	
	public ptpEmiReportBean() 
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId = (int) ss.getAttribute("branchId");
		
		String companyName = dbmt.companyNameByBranchId(branchId);
		Connection conn = DataBaseConnection.JAVAConnection();
		dsaList = obj.allDsaList(branchId,"",conn);
		userId = obj.userId();
		
		System.out.println("user id for entry"+userId);
		String empDataStatus = obj.checkSMessageApiDetailFromTable("employee_wise_data", "",conn);
		
		if(obj.userType().equals("employee") && empDataStatus.equalsIgnoreCase("active"))
		{
			dsaCode=obj.dsaCodeByEmpUserId(userId, conn);
			
			if(companyName.equalsIgnoreCase("SUDERSHANA FINANCE"))
			{
				disDsa=false;
			}
			else 
			{
				disDsa=true;
			}
		}
		
		branchUserId = (String) ss.getAttribute("branchUserId");
		branchList = new DataBaseMethods().branchList(branchUserId,conn);
		
		String cc=(String) ss.getAttribute("companyName");

		userType= (String) ss.getAttribute("type");

		
	    disAll= dbmh.disAllByCompanyNameUserType(userType,cc);

		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	public void checkList() {
		if (branchId != 0) {
			show = true;

			Connection conn = DataBaseConnection.JAVAConnection();
			multipleRows = new ArrayList<UserInfo>();
			ArrayList<UserInfo> templist = obj1.allDueEMI(branchId, category, frequency, dsaCode, conn);

			list = new ArrayList<>();

			for (UserInfo ll : templist) {
				if (!ll.getEntryDateStr().equalsIgnoreCase(new SimpleDateFormat("dd/MM/yyyy").format(new Date()))) {
					boolean ptpCreated = dbrk.checkPtpCreated(ll.getShareHolderNo(), ll.getLoanNo(), ll.getPtpemiNo(),
							             branchId, conn);

					ll.setPtpCreated(ptpCreated);

					list.add(ll);
				}
			}

			Collections.sort(list);

			int i = 1;
			totalAmount = 0;
			for (UserInfo ll : list) {
				ll.setSno(i++);
				totalAmount += ll.getAmount();
			}

			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Select Branch"));
		}
	}
	
	public void ptpVariableSetup(UserInfo var)
	{
	    if(var == null)
	    {
	        return;
	    }
	    
	    ptpMemberNo = var.getShareHolderNo();
	    ptpLoanNo = var.getLoanNo();
	    ptpEmiAmt = var.getAmount();
	    ptpAmount = var.getAmount();
	    ptpEmiNo  = var.getPtpemiNo();
	    ptpCreatedBy = userId;
	    
	    System.out.println("========== PTP VARIABLE SETUP ==========");
	    System.out.println("Member No   = " + ptpMemberNo);
	    System.out.println("Loan No     = " + ptpLoanNo);
	    System.out.println("EMI Amount  = " + ptpEmiAmt);
	    System.out.println("PTP Amount  = " + ptpAmount);
	    System.out.println("PTP Emi No.  = " + ptpEmiNo);
	    System.out.println("PTP CREATED BY "+ ptpCreatedBy);
	    System.out.println("========================================");
	}


	public void savePtp()
	{
	    if (ptpMemberNo == null || ptpMemberNo.trim().equals("")
	            || ptpLoanNo == null || ptpLoanNo.trim().equals(""))
	    {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "Customer ID / Loan No is missing."));
	        return;
	    }

	    if (ptpAmount <= 0)
	    {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "Please enter PTP Amount."));
	        return;
	    }

	    if (ptpDate == null)
	    {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "Please select PTP Date."));
	        return;
	    }

	    Connection conn = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();
	        System.out.println("========== SAVE PTP ==========");
	        System.out.println("Member No  = " + ptpMemberNo);
	        System.out.println("Loan No    = " + ptpLoanNo);
	        System.out.println("EMI No     = " + ptpEmiNo);
	        System.out.println("EMI Amount = " + ptpEmiAmt);
	        System.out.println("PTP Amount = " + ptpAmount);
	        System.out.println("PTP Date   = " + ptpDate);
	        System.out.println("Created By = " + ptpCreatedBy);
	        System.out.println("Branch ID  = " + branchId);
	        System.out.println("==============================");
			int result = dbrk.addPtp(ptpMemberNo, ptpLoanNo, ptpEmiAmt, ptpAmount, ptpDate, ptpEmiNo, ptpRemarks, ptpCreatedBy, true, branchId, conn);
	        if (result > 0)
	        {
	            for (UserInfo u : list)
	            {
	                String rowMemberNo = u.getShareHolderNo();
	                String rowLoanNo = u.getLoanNo();
	                String rowEmiNo = u.getPtpemiNo();
	                if (rowMemberNo == null)
	                {
	                    rowMemberNo = "";
	                }
	                if (rowLoanNo == null)
	                {
	                    rowLoanNo = "";
	                }
	                if (rowEmiNo == null)
	                {
	                    rowEmiNo = "";
	                }

	                String savedMemberNo =ptpMemberNo == null ? "" : ptpMemberNo;
	                String savedLoanNo = ptpLoanNo == null ? "" : ptpLoanNo;
	                String savedEmiNo =ptpEmiNo == null ? "" : ptpEmiNo;

					if (rowMemberNo.equals(savedMemberNo) && rowLoanNo.equals(savedLoanNo)
						&& rowEmiNo.equals(savedEmiNo))
	                {
	                    u.setPtpCreated(true);
	                    break;
	                }
	            }

				FacesContext.getCurrentInstance().addMessage(null,
						new FacesMessage(FacesMessage.SEVERITY_INFO, "Success", "PTP saved successfully.")
	            );

	            ptpAmount = 0;
	            ptpDate = new Date();
	            ptpRemarks = "";
	           
	        }
	        else
	        {
				FacesContext.getCurrentInstance().addMessage(null,
						new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "PTP not saved."));
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();

			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "Error while saving PTP."));
	    }
	    finally
	    {
	        try
	        {
	            if (conn != null)
	            {
	                conn.close();
	            }
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
	}
	

	
	public void openMultiplePtp() {

		if (multipleRows == null || multipleRows.isEmpty()) {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_WARN, "Warning", "Please select at least one row."));
			return;
		}

		bulkPtpDate = new Date();
		bulkPtpRemarks = "";

		for (UserInfo u : multipleRows) {
			if (u == null) {
				continue;
			}
			System.out.println("Member = " + u.getShareHolderNo() + " | Loan = " + u.getLoanNo() + " | EMI = "
					+ u.getPtpemiNo() + " | EMI Amount = " + u.getAmount() + " | PTP Created = " + u.isPtpCreated());
		}

	}
	
	public void saveMultiplePtp() {
		if (multipleRows == null || multipleRows.isEmpty()) {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_WARN, "Warning", "Please select at least one row."));
			return;
		}

		if (bulkPtpDate == null) {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "Please select PTP Date."));
			return;
		}

		Connection conn = null;
		int successCount = 0;
		int skipCount = 0;

		ArrayList<UserInfo> saveList = new ArrayList<UserInfo>();

		try {

			conn = DataBaseConnection.JAVAConnection();
			for (UserInfo u : multipleRows) {

				if (u == null) {
					continue;
				}

				String memberNo = u.getShareHolderNo();
				String loanNo = u.getLoanNo();
				String emiNo = u.getPtpemiNo();

				if (memberNo == null || memberNo.trim().equals("") || loanNo == null || loanNo.trim().equals("")
						|| emiNo == null || emiNo.trim().equals("")) {

					skipCount++;

					continue;
				}

				boolean alreadyCreated = dbrk.checkPtpCreated(memberNo, loanNo, emiNo, branchId, conn);

				if (alreadyCreated) {

					System.out.println("SKIPPED - PTP ALREADY EXISTS" + " | Member=" + memberNo + " | Loan=" + loanNo
							+ " | EMI=" + emiNo);

					u.setPtpCreated(true);
					skipCount++;
					continue;
				}
				saveList.add(u);

			}

			if (saveList.isEmpty()) {

				FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN,
						 "No new PTP record found. All selected records are already created or invalid.","Bulk PTP"));

				return;
			}
			successCount = dbrk.addBulkPtp(saveList, bulkPtpDate, bulkPtpRemarks, userId, branchId, conn);

			if (successCount > 0) {

				for (int i = 0; i < successCount && i < saveList.size(); i++) {

					UserInfo u = saveList.get(i);

					u.setPtpCreated(true);

				}
			}

			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Bulk PTP",
					successCount + " PTP saved successfully. " + skipCount + " skipped."));

			bulkPtpRemarks = "";
			bulkPtpDate = new Date();
			multipleRows.clear();
		} catch (Exception e) {

			e.printStackTrace();
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error while saving Bulk PTP.","Error"));

		} finally {
			try {
				if (conn != null) {
					conn.close();
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	
	public void printReport()
	{
		if(list.size()>0)
		{
			HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
			ss.setAttribute("printDueList", list);
			ss.setAttribute("printDueTotal", totalAmount);
			RequestContext.getCurrentInstance().execute("window.open('printDueEmiReport.xhtml')");
		}
	}
	
	public void printDateWise()
	{
		if(list.size()>0)
		{
			ArrayList<UserInfo> tempList=new ArrayList<>();
			double dueAmount = 0;
			int count=1;
			for(int i=0;i<list.size();i++)
			{
				if((list.get(i).getEntryDate().after(startDate) || list.get(i).getEntryDate().equals(startDate) ) && 
						(list.get(i).getEntryDate().before(endDate) || list.get(i).getEntryDate().equals(endDate)))
				{
					dueAmount+=list.get(i).getAmount();
					list.get(i).setSno(count++);
					tempList.add(list.get(i));
				}
			}
			HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
			ss.setAttribute("printDueList", tempList);
			ss.setAttribute("printDueTotal", dueAmount);
			RequestContext.getCurrentInstance().execute("window.open('printDueEmiReport.xhtml')");
		}
	}
	
	// PTP WORK MODULE INSERT UPDATE DELETE AND VIEW FUNCTION

		public int addPtp(String shareholderNo, String loanNo, double emiAmt, double ptpAmount, Date ptpDate,String emiNo,
				          String remarks, String createdBy, boolean ptpCreate, int branchId, Connection conn)
		{
			int i=0;
			String qq = "INSERT INTO ptp_table "
	                  + "(shareholder_no, loan_no, emi_amt, ptp_amount, ptp_date, emi_no, status, remarks, created_by, ptp_created, branch_id) "
	                  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			
			try(PreparedStatement ps = conn.prepareStatement(qq)) {
				 ps.setString(1, shareholderNo);
		         ps.setString(2, loanNo);
		         ps.setDouble(3, emiAmt);
		         ps.setDouble(4, ptpAmount);
		         ps.setDate(5, new java.sql.Date(ptpDate.getTime()));
		         ps.setString(6, emiNo);
		         ps.setString(7, "pending");
		         ps.setString(8, remarks == null ? "" : remarks);
		         ps.setString(9, createdBy);
		         ps.setBoolean(10, ptpCreate);
		         ps.setInt(11, branchId);
				
				 i=ps.executeUpdate();
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return i;
			
		}
		
		
		
		public boolean checkPtpCreated(String shareholderNo, String loanNo,
		        String emiNo, int branchId, Connection conn)
		{
		    boolean exists = false;

		    String sql = "SELECT id FROM ptp_table "
		            + "WHERE shareholder_no=? "
		            + "AND loan_no=? "
		            + "AND emi_no=? "
		            + "AND branch_id=? "
		            + "AND ptp_created=1 "
		            + "LIMIT 1";

		    try (PreparedStatement ps = conn.prepareStatement(sql))
		    {
		        ps.setString(1, shareholderNo);
		        ps.setString(2, loanNo);
		        ps.setString(3, emiNo);
		        ps.setInt(4, branchId);

		        ResultSet rs = ps.executeQuery();

		        if (rs.next())
		        {
		            exists = true;
		        }
		    }
		    catch (Exception e)
		    {
		        e.printStackTrace();
		    }

		    return exists;
		}
		
		
		public int addBulkPtp(List<UserInfo> rows, Date ptpDate, String remarks, String createdBy, int branchId,
				Connection conn) {
			System.out.println("bulk ptp method is calling ");
			int successCount = 0;

			String sql = "INSERT INTO ptp_table (shareholder_no, loan_no, emi_amt, ptp_amount, ptp_date, emi_no, "
					   + "status, remarks, created_by, ptp_created, branch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			try (PreparedStatement ps = conn.prepareStatement(sql)) {

				for (UserInfo u : rows) {

					if (u == null) {
						continue;
					}

					String memberNo = u.getShareHolderNo();
					String loanNo = u.getLoanNo();
					String emiNo = u.getPtpemiNo();

					if (memberNo == null || memberNo.trim().equals("") || loanNo == null || loanNo.trim().equals("")
							|| emiNo == null || emiNo.trim().equals("")) {
						continue;
					}

					double emiAmount = u.getAmount();

					double ptpAmount = emiAmount;

					ps.setString(1, memberNo);
					ps.setString(2, loanNo);
					ps.setDouble(3, emiAmount);
					ps.setDouble(4, ptpAmount);
					ps.setDate(5, new java.sql.Date(ptpDate.getTime()));
					ps.setString(6, emiNo);
					ps.setString(7, "pending");
					ps.setString(8, remarks == null ? "" : remarks);
					ps.setString(9, createdBy);
					ps.setBoolean(10, true);
					ps.setInt(11, branchId);

					ps.addBatch();
				}

				int[] result = ps.executeBatch();

				for (int r : result) {

					if (r > 0 || r == Statement.SUCCESS_NO_INFO) {
						successCount++;
					}
				}

			} catch (Exception e) {
				e.printStackTrace();
			}

			return successCount;
		}
	
	
	
	public Date getStartDate() {
		return startDate;
	}
	public void setStartDate(Date startDate) {
		this.startDate = startDate;
	}
	public Date getEndDate() {
		return endDate;
	}
	public void setEndDate(Date endDate) {
		this.endDate = endDate;
	}
	public ArrayList<UserInfo> getList() {
		return list;
	}
	public void setList(ArrayList<UserInfo> list) {
		this.list = list;
	}
	public double getTotalAmount() {
		return totalAmount;
	}
	public void setTotalAmount(double totalAmount) {
		this.totalAmount = totalAmount;
	}

	public String getCategory() {
		return category;
	}

	public void setCategory(String category) {
		this.category = category;
	}

	public ArrayList<UserInfo> getMultipleRows() {
		return multipleRows;
	}

	public void setMultipleRows(ArrayList<UserInfo> multipleRows) {
		this.multipleRows = multipleRows;
	}

	public String getUserId() {
		return userId;
	}

	public void setUserId(String userId) {
		this.userId = userId;
	}
	
	public boolean isShow() {
		return show;
	}

	public void setShow(boolean show) {
		this.show = show;
	}

	public String getFrequency() {
		return frequency;
	}

	public void setFrequency(String frequency) {
		this.frequency = frequency;
	}

	public String getDsaCode() {
		return dsaCode;
	}

	public void setDsaCode(String dsaCode) {
		this.dsaCode = dsaCode;
	}

	public boolean isDisDsa() {
		return disDsa;
	}

	public void setDisDsa(boolean disDsa) {
		this.disDsa = disDsa;
	}

	public ArrayList<SelectItem> getDsaList() {
		return dsaList;
	}

	public void setDsaList(ArrayList<SelectItem> dsaList) {
		this.dsaList = dsaList;
	}

	public int getBranchId() {
		return branchId;
	}

	public void setBranchId(int branchId) {
		this.branchId = branchId;
	}

	public List<SelectItem> getBranchList() {
		return branchList;
	}

	public void setBranchList(List<SelectItem> branchList) {
		this.branchList = branchList;
	}

	public boolean isDisAll() {
		return disAll;
	}

	public void setDisAll(boolean disAll) {
		this.disAll = disAll;
	}

	public String getUserType() {
		return userType;
	}

	public void setUserType(String userType) {
		this.userType = userType;
	}



	public String getPtpMemberNo() {
		return ptpMemberNo;
	}



	public void setPtpMemberNo(String ptpMemberNo) {
		this.ptpMemberNo = ptpMemberNo;
	}



	public String getPtpLoanNo() {
		return ptpLoanNo;
	}



	public void setPtpLoanNo(String ptpLoanNo) {
		this.ptpLoanNo = ptpLoanNo;
	}



	public String getPtpRemarks() {
		return ptpRemarks;
	}



	public void setPtpRemarks(String ptpRemarks) {
		this.ptpRemarks = ptpRemarks;
	}



	public Date getPtpDate() {
		return ptpDate;
	}



	public void setPtpDate(Date ptpDate) {
		this.ptpDate = ptpDate;
	}



	public double getPtpEmiAmt() {
		return ptpEmiAmt;
	}



	public void setPtpEmiAmt(double ptpEmiAmt) {
		this.ptpEmiAmt = ptpEmiAmt;
	}



	public double getPtpAmount() {
		return ptpAmount;
	}



	public void setPtpAmount(double ptpAmount) {
		this.ptpAmount = ptpAmount;
	}



	public String getPtpEmiNo() {
		return ptpEmiNo;
	}



	public void setPtpEmiNo(String ptpEmiNo) {
		this.ptpEmiNo = ptpEmiNo;
	}



	public Date getBulkPtpDate() {
		return bulkPtpDate;
	}



	public void setBulkPtpDate(Date bulkPtpDate) {
		this.bulkPtpDate = bulkPtpDate;
	}



	public String getBulkPtpRemarks() {
		return bulkPtpRemarks;
	}



	public void setBulkPtpRemarks(String bulkPtpRemarks) {
		this.bulkPtpRemarks = bulkPtpRemarks;
	}



	public String getPtpCreatedBy() {
		return ptpCreatedBy;
	}



	public void setPtpCreatedBy(String ptpCreatedBy) {
		this.ptpCreatedBy = ptpCreatedBy;
	}
}
