package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.faces.FacesException;
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="branchTransferBean")
@ViewScoped

public class MemberBranchTransferBean implements Serializable{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	String branchName, member, currentBranch ,newBranch , reason  ,newBranchName ;
	int branchId;
	public static List<String> list;
	Date transferDate = new Date() ;
	ArrayList<LoanInfo> transferListCust;
	 ArrayList<String> branchList;
	 ArrayList<String>transferList ; 

	 DataBaseMethods db = new DataBaseMethods();
	 
	 public MemberBranchTransferBean() {
		 
		 HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(true);
	     branchId = (int) ss.getAttribute("branchId");
		 
		 Connection conn =DataBaseConnection.JAVAConnection();
	    	branchName =  db.branchNameByBranchId(branchId, conn);
		 System.out.println("branch Name"+branchName);
		 
		 customerTransferDetails();
	 }


	 public void customerTransferDetails() {
		 
		 transferListCust = new ArrayList<>();
		 
		 try(Connection conn = DataBaseConnection.JAVAConnection()){
		
			 String query = "select * from branch_transfer_customers" ;
			  
			 try(PreparedStatement ps = conn.prepareStatement(query)){
				 
				 ResultSet rs = ps.executeQuery();
				 
				 while(rs.next()) {
					 
					 System.out.println("loop Initiated");
					
					 LoanInfo li = new LoanInfo();
					 
					 li.setShareHolderNo(rs.getString("shareholder_no"));
					 li.setCustomerName (rs.getString("customer_name"));
					 String branchOld =  db.branchNameByBranchId(rs.getInt("old_branch_name"), conn);
					 li.setOldBranchName(branchOld);
					 String branchNew =  db.branchNameByBranchId(rs.getInt("new_branch_name"), conn);
					 li.setNewBranchName(branchNew);
					 li.setTransferDate (rs.getString("transfer_date"));
					 li.setRemarks(rs.getString("remarks"));
					 transferListCust.add(li);
					 Collections.reverse(transferListCust);
				 }
				 
			 }catch(Exception e) {
				 e.printStackTrace();
				 
			 }
			 
			 
		 }catch(Exception e) {
			 e.printStackTrace();
			 
		 }
		 
		 
	 }
	
	
	
	public void submitTransfer() {
	    System.out.println("Start");
	    
	    int index = 1;
	    String shareholderno = member.split("/", 2)[0]; // Extract number before first "/"
	    String customerName = member.split("/")[1]; // Extract number before first "/"

	    HashMap<Integer, String> listP = new HashMap<>();
	    HashMap<Integer, String> listG = new HashMap<>();
	    HashMap<Integer, String> listS = new HashMap<>();

	    for (int loop = 1; loop <= 3; loop++) {
	        System.out.println("Loop start: " + loop);

	        try (Connection conn = DataBaseConnection.JAVAConnection()) {
	            String query = "";


	            if (loop == 1) {

	                query = "SELECT * FROM personalloan WHERE shareholderno=?";
	            } else if (loop == 2) {
	                query = "SELECT * FROM grouploan WHERE teamLeader=?";
	            } else {
	                query = "SELECT * FROM secure_loan WHERE shareholderno=?";
	            }

	            System.out.println("Query select: " + query + " | Loop: " + loop);

	            try (PreparedStatement ps = conn.prepareStatement(query)) {
	                ps.setString(1, shareholderno);
	                ResultSet rs = ps.executeQuery();

	                while (rs.next()) {
	                    System.out.println("While start " + loop);

	                    if (loop == 1) {
	                        listP.put(index, rs.getString("loanNo"));
	                    } else if (loop == 2) {
	                        listG.put(index, rs.getString("sub_loan_No"));
	                    } else {
	                        listS.put(index, rs.getString("loanNo"));
	                    }
	                    
	                    index++;
	                }

	                index = 1; // Reset for next type of loan

	            } catch (Exception e) {
	                e.printStackTrace();
	            }

	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	    }

	    // ✅ Print or process HashMap values
	    System.out.println("Personal Loans: " + listP);
	    System.out.println("Group Loans: " + listG);
	    System.out.println("Secure Loans: " + listS);

	    // Example of iterating listP:
	    for (Map.Entry<Integer, String> entry : listP.entrySet()) {
	    	
	        System.out.println("Key: " + entry.getKey() + ", LoanNo: " + entry.getValue());
	    }
	    
       if(listP.size()>0) {
    	   loanPaymentBranchUpdate(shareholderno, listP,customerName);
       }else if(listG.size()>0) {
    	   loanPaymentBranchUpdate(shareholderno, listG,customerName);
    	   
       }else if(listS.size()>0) {
    	   loanPaymentBranchUpdate(shareholderno, listS,customerName);
       }
	    
	}

public void loanPaymentBranchUpdate(String shareholderno, HashMap<Integer, String> loanList , String customerName) {
	    
	    System.out.println("=== Starting Branch Update Process ===");
	    System.out.println("Shareholder No: " + shareholderno);
	    System.out.println("Loan List Size: " + loanList.size());

	    for (int loop = 1; loop <= 10; loop++) {

	        try (Connection conn = DataBaseConnection.JAVAConnection()) {

	            String query = "";
	            String tableName = ""; 

	            if (loop == 1) {
	                query = "UPDATE shareholder_info SET branch_id = ? WHERE shareholderno = ?";
	                tableName = "shareholder_info";
	            } else if (loop == 2) {
	                query = "UPDATE personalloan SET branch_id = ? WHERE shareholderno = ?";
	                tableName = "personalloan";
	            } else if (loop == 3) {
	                query = "UPDATE grouploan SET branch_id = ? WHERE teamLeader = ?";
	                tableName = "grouploan";
	            } else if (loop == 4) {
	                query = "UPDATE secure_loan SET branch_id = ? WHERE shareholderno = ?";
	                tableName = "secure_loan";
	            } else if (loop == 5) {
	                query = "UPDATE debit_loan SET branch_id = ? WHERE shareholderno = ?";
	                tableName = "debit_loan";
	            }   else if (loop == 6) {
	                query = "UPDATE customer_info SET branch_id = ? WHERE shareHolderNo = ?";
	                tableName = "customer_info";
	            }  else if (loop == 7) {
	                query = "UPDATE payment_receipt SET branch_id = ? WHERE user_no = ?";
	                tableName = "payment_receipt";
	            }  else if (loop == 8) {
	                query = "UPDATE returned_amount_info SET branch_id = ? WHERE user_no = ?";
	                tableName = "returned_amount_info";
	            } else if (loop == 9) {
	                query = "UPDATE loanpayment SET branch_id = ? WHERE loanNo = ?";
	                tableName = "loanpayment";
	            } else if (loop == 10) {
	                query = "UPDATE income_table SET branch_id = ? WHERE loan_no = ?";
	                tableName = "income_table";
	            } 
	            
	            System.out.println("\n[INFO] Processing table: " + tableName);
	            System.out.println("[INFO] SQL Query: " + query);

	            try (PreparedStatement ps = conn.prepareStatement(query)) {

	                if (loop >= 1 && loop <= 8) {
	                    ps.setString(1, newBranch);
	                    ps.setString(2, shareholderno);
	                    int rows = ps.executeUpdate();
	                    System.out.println("[SUCCESS] Updated " + rows + " rows in " + tableName + " for shareholder: " + shareholderno);
	                } 
	                else if (loop > 8) {
	                    for (int i = 1; i <= loanList.size(); i++) {
	                        String loanNo = loanList.get(i);
	                        ps.setString(1, newBranch);
	                        ps.setString(2, loanNo);
	                        int rows = ps.executeUpdate();
	                        System.out.println("[SUCCESS] Updated " + rows + " rows in "+tableName+" for loanNo: " + loanNo);
	                    }
	                }

	            } catch (Exception e) {
	                System.err.println("[ERROR] While updating table: " + tableName);
	                e.printStackTrace();
	            }

	        } catch (Exception e) {
	            System.err.println("[ERROR] Database connection failed in loop " + loop);
	            e.printStackTrace();
	        }
	    }
    
	     System.out.println("\n=== Branch Update Process Completed ==="); 
	
	   transferCustomerDetails(shareholderno,customerName);
	   
	     
	}
	public void transferCustomerDetails(String shareholderno , String customerName) {
		
		
		Connection con = DataBaseConnection.JAVAConnection();
		int newBranchId = Integer.parseInt(newBranch);
		 newBranchName = db.branchNameByBranchId(newBranchId, con);
		
		try(Connection conn = DataBaseConnection.JAVAConnection()){
		
			String query =  "INSERT INTO branch_transfer_Customers "
			        + "(shareholder_no, customer_name, old_branch_name, new_branch_name, transfer_date, remarks) "
			        + "VALUES (?, ?, ?, ?, ?, ?)";
			
			try(PreparedStatement ps = conn.prepareStatement(query)){
				

			    // 1️⃣ Shareholder Number
			    ps.setString(1, shareholderno);

			    // 2️⃣ Customer Name (if available, else null or fetch from DB)
			    ps.setString(2, customerName); // or null if not available

			    // 3️⃣ Old Branch ID
			    ps.setInt(3, branchId);

			    // 4️⃣ New Branch ID
			    ps.setString(4, newBranch);

			    // 5️⃣ Transferred By (the user who triggered this)
			    ps.setDate(5,new java.sql.Date(transferDate.getTime())); // e.g., logged-in username

			    
			    // 6️⃣ Remarks (optional note)
			    ps.setString(6, reason);

			

			    // Execute
			    int rows = ps.executeUpdate();

			    System.out.println("[LOG INSERTED] Rows affected: " + rows 
			        + " | Shareholder: " + shareholderno 
			        + " | From Branch: " + currentBranch 
			        + " | To Branch: " + newBranch);
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		
		
		FacesContext.getCurrentInstance().addMessage(null,	new FacesMessage("Customer Name "+customerName+" Succesfully Transfered In Branch "+ newBranchName));
		customerTransferDetails();
	}

	
	
	
	public List<String> completeText(String name)
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(true);
	     branchId = (int) ss.getAttribute("branchId");
		list=new DataBaseAutoCompAndDuplicate().shareHolderAllNo(branchId,name);

		return list;
     	
	}
	
	

	public String getCurrentBranch() {
		return currentBranch;
	}

	public void setCurrentBranch(String currentBranch) {
		this.currentBranch = currentBranch;
	}

	public String getNewBranch() {
		return newBranch;
	}

	public void setNewBranch(String newBranch) {
		this.newBranch = newBranch;
	}

	public String getReason() {
		return reason;
	}

	public void setReason(String reason) {
		this.reason = reason;
	}

	public Date getTransferDate() {
		return transferDate;
	}

	public void setTransferDate(Date transferDate) {
		this.transferDate = transferDate;
	}

	public ArrayList<String> getBranchList() {
		return branchList;
	}

	public void setBranchList(ArrayList<String> branchList) {
		this.branchList = branchList;
	}

	public ArrayList<String> getTransferList() {
		return transferList;
	}

	public void setTransferList(ArrayList<String> transferList) {
		this.transferList = transferList;
	}

	public String getMember() {
		return member;
	}

	public void setMember(String member) {
		this.member = member;
	}

	public String getBranchName() {
		return branchName;
	}

	public void setBranchName(String branchName) {
		this.branchName = branchName;
	}


	public ArrayList<LoanInfo> getTransferListCust() {
		return transferListCust;
	}


	public void setTransferListCust(ArrayList<LoanInfo> transferListCust) {
		this.transferListCust = transferListCust;
	}


	

	

	

}