package main_package;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
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.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;


import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.ServletContext;


import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.*;
import org.primefaces.model.UploadedFile;

import com.itextpdf.text.log.SysoCounter;

@ManagedBean(name = "Bbps")
@ViewScoped
public class BbpsBean implements Serializable {

    private static final long serialVersionUID = 1L;

    String filePath,filePath2, branchId;
    UploadedFile excelFile , excelFile2;
   

	public void setExcelFile2(UploadedFile excelFile2) {
		this.excelFile2 = excelFile2;
	}

	ArrayList<NachExcelInfo> stuList = new ArrayList<>();
    Date date = new Date();
    DataBaseMethods dbm = new DataBaseMethods();

    public BbpsBean() {
        
    	branchId = "sam";
    	
    }
   
    public String bbpsExcel() throws IOException, ParseException {
    	
        if (excelFile != null && excelFile.getSize() > 0) {
            Connection conn = DataBaseConnection.JAVAConnection();
            filePath = excelFile.getFileName();

            dbm.makeProfile(excelFile, filePath);

            String uploadPath = dbm.fileUploadPathDetail();
            String realPath;
            if (uploadPath.trim().equalsIgnoreCase("")) {
                ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
                realPath = ctx.getRealPath("/");
            } else {
                realPath = uploadPath;
            }

            filePath = realPath + filePath;
        }

        if (filePath != null && !filePath.equals("")) {
            List<List<Cell>> sheetData = new ArrayList<>();
            FileInputStream fis = null;

            try {
                fis = new FileInputStream(filePath);
                if (filePath.endsWith("xlsx")) {
                    XSSFWorkbook workbook = new XSSFWorkbook(fis);
                    XSSFSheet sheet = workbook.getSheetAt(0);
                    for (Row row : sheet) {
                        List<Cell> rowData = new ArrayList<>();
                        for (Cell cell : row) {
                            rowData.add(cell);   
                        }
                        sheetData.add(rowData);
                    }
                } else {
                    HSSFWorkbook workbook = new HSSFWorkbook(fis);
                    HSSFSheet sheet = workbook.getSheetAt(0);
                    for (Row row : sheet) {
                        List<Cell> rowData = new ArrayList<>();
                        for (Cell cell : row) {
                            rowData.add(cell);
                        }
                        sheetData.add(rowData);
                    }
                }
            } finally {
                if (fis != null) fis.close();
            }

            showExelData(sheetData);
            
            
            
        } else {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error In File Uploading. Please Check!!!"));
        }
        
        return "uploadExcel.xhtml";
    }

    private void showExelData(List<List<Cell>> sheetData) throws ParseException {
    	
        Connection conn = DataBaseConnection.JAVAConnection();
       
        for (int i = 1; i < sheetData.size(); i++) {
            List<Cell> list = sheetData.get(i);
            NachExcelInfo info = new NachExcelInfo();
            String temp = "";

            for (int j = 0;  j < list.size(); j++) {
                Cell cell = list.get(j);
                if (cell == null) continue;
                DataFormatter fmt = new DataFormatter();
                
                temp = fmt.formatCellValue(cell);

                switch (j) {
                
                case 0: info.setNpciBillerId(temp); 
                	
                break;
                case 1: info.setBillerName(temp); break;
                case 2: info.setBillerCategory(temp); break;
                case 3: info.setNpciAgentId(temp); break;
                case 4: info.setPaymentMode(temp); break;
                case 5: info.setPaymentChannel(temp); break;
                case 6: info.setTransactionType(temp); break;
                case 7: info.setConnectionId(temp); 
               
                break;
                case 8:  info.setCustomerMobileNo(temp); break;
                case 9:  info.setCustomerName(temp); break;
                case 10: info.setBillNo(temp); break;
                case 11: info.setBillPeriod(temp); break;
                case 12: info.setRefId(temp); break;
                case 13: info.setMessageId(temp); break;
                case 14: info.setTransactionRefId(temp); break;
                case 15: info.setTransactionDate(temp); break;
                case 16: info.setTransactionAmount(temp); break;
                case 17: info.setBillAmount(temp); break;
                case 18: info.setBsfAmount(temp); break;
                case 19: info.setBsfTaxAmount(temp); break;
                case 20: info.setNpciBillerFee(temp); break;
                case 21: info.setNpcibillerFeeTax(temp); break;
                case 22: info.setNpciConvenienceFee(temp); break;
                case 23: info.setNpciConvenienceFeeTax(temp); break;
                case 24: info.setNpciTransactionStatus(temp); break;
                case 25: info.setNpciTransactionDesc(temp); break;
                case 26: info.setNpciErrorCode(temp); break;
                case 27: info.setNpciErrorDescription(temp); break;
                case 28: info.setReconciliationStatus(temp); break;
                case 29: info.setReconcillationDate(temp); break;
                case 30: info.setPayoutStatus(temp); break;
                case 31: info.setPayoutDate(temp); break;
                case 32: info.setInterchangeReferenceNumber(temp); break;
                case 33: info.setCcAccountNumber(temp); break;
            

                }
                stuList.add(info);
            }
           
        }
        
        insertData(stuList);
    }

    public void insertData(ArrayList<NachExcelInfo> stuList) {
    	
    	
    	Date todayDate = new Date();
    	
    	
    	try(Connection conn = DataBaseConnection.JAVAConnection()){
    	
    		String query = "insert into loan_payment_from_app ( employee, loan_type, loan_no, pay_date, amount, pay_mode, status, entry_date, transaction_id,  branch_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    		
    		try(PreparedStatement ps = conn.prepareStatement(query)){
    			
    			String LoanNo="";
    			for(NachExcelInfo ff : stuList) {
    				
    				if(!LoanNo.equals(ff.getConnectionId())&&("PAY").equals(ff.getTransactionType())) {
    					
    				LoanNo=ff.getConnectionId();
    				  
    				ps.setString(1, "admin");
    				System.out.println();
    				String branch_id="";
    				String type = checkLoanType(ff.getConnectionId());
    				if(("personalloan").equalsIgnoreCase(type))
    				{
    					branch_id=dbm.selectBranchIdPersonalLoan(ff.getConnectionId());
    				}
    				else {
    					branch_id=dbm.selectBranchIdGroupLoan(ff.getConnectionId());
    					
    				}
    				ps.setString(2, type);
    				
    				SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    	            Date date = formatter.parse(ff.getPayoutDate());
    				ps.setString(3, ff.getConnectionId() );
    				ps.setDate(4,  new java.sql.Date(date.getTime()));
    				ps.setString(5, ff.getBillAmount());
    				ps.setString(6, "online");
    				ps.setString(7, "pending");
    				ps.setDate(8,  new java.sql.Date(date.getTime()));
    				
    				ps.setString(9, ff.getTransactionRefId());
    				
    				
    				
    				ps.setString(10, branch_id);
    				
    				ps.executeUpdate();
    			
    				}
    			}
    			
    			FacesContext.getCurrentInstance().getExternalContext().redirect("uploadExcel.xhtml");
    			
        	}catch(Exception e) {
        	
        		e.printStackTrace(); fileCreation(e.getMessage());
        		
        	}
    		
    	}catch(Exception e) {
    		
    		e.printStackTrace(); fileCreation(e.getMessage());
    		
    	}
    	
    	
    }
    
    

  //Error Log File Creation
  	public void fileCreation(String msg) 
  	{
  		if(msg==null || msg.trim().equalsIgnoreCase(""))
  		{
  			msg="";
  		}
  		else
  		{
  			Date dt=new Date();
  			String time=new SimpleDateFormat("dd/MM/yyyy hh:mm:ss a").format(dt);
  			msg=msg+time;
  		}
  		
  		
  		
  		
  		/*ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
  		String realPath = ctx.getRealPath("/");
  			String dirPath=realPath;
  		*/  //Offline
  		
  		String folderName=fileUploadPathDetail();
  		String dirPath=folderName;         //online
  	    BufferedWriter writer = null;
       try
       {
           boolean dirFlag = false;
           File stockDir = new File(dirPath);
           try 
           {
               dirFlag = stockDir.mkdirs();
               if (dirFlag)
               {
               	File myfile=new File(dirPath+"/ErrorLogDetail.txt");
               	try
               	{
                     myfile.createNewFile();
               	}
               	catch (IOException ss)
               	{
                    ss.printStackTrace();
               	}
               }
            
           } 
           catch (SecurityException Se) 
           {
                Se.printStackTrace();fileCreation(Se.getMessage());
                String valie=String.valueOf(Se.getMessage());
           }
           String worktext="Mr. Chetan Garg";
           File myfile=new File(dirPath+"/ErrorLogDetail.txt");
           writer = new BufferedWriter(new FileWriter(myfile, true));
           writer.newLine();
           writer.write(msg);
        } 
        catch (Exception es) 
        {
            es.printStackTrace();
        } 
        finally 
        {
            try 
            {
                writer.close();
            } 
            catch (Exception s) 
            {
                s.printStackTrace();
            }
        }
    }
  	
  	
  	public String fileUploadPathDetail()
  	{
  		try(Connection con=DataBaseConnection.JAVAConnection();) 
  		{
  			Statement st=con.createStatement();
  			String qq="select * from firm_detail";
  			ResultSet rs=st.executeQuery(qq);
  			if(rs.next())
  			{
  				return rs.getString("file_upload_path");
  			}
  		}
  		catch (Exception e)
  		{
  			 e.printStackTrace();fileCreation(e.getMessage());
  		} 
  		return "";
  	}

      
      
    
    
    
    
    
    
    
    public String checkLoanType(String type){
    	String lType="";
    	
    	if(type.contains("gl")||type.contains("GL")) {
    		
    	    lType = "grouploan";
    	}else {
    		
    		  lType = "personalloan";
    	}
    	
    	return lType;
  
    }
 
    
    
    /////////////////
    
public void bbpsExcel2() throws IOException, ParseException {
    	
    	System.out.println("bbpsExcel2 hitted !!!");
    	
        if (excelFile2 != null && excelFile2.getSize() > 0) {
            Connection conn = DataBaseConnection.JAVAConnection();
            filePath2 = excelFile2.getFileName();
              
            dbm.makeProfile(excelFile2, filePath2);

            String uploadPath = dbm.fileUploadPathDetail();
            String realPath;
            
            if (uploadPath.trim().equalsIgnoreCase("")) {
                ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
                realPath = ctx.getRealPath("/");
            } else {
                realPath = uploadPath;
            }

            filePath2 = realPath + filePath2;
        } 

        if (filePath2 != null && !filePath2.equals("")) {
            List<List<Cell>> sheetData = new ArrayList<>();
            FileInputStream fis = null;

            try {
                fis = new FileInputStream(filePath2);
                if (filePath2.endsWith("xlsx")) {
                    XSSFWorkbook workbook = new XSSFWorkbook(fis);
                    XSSFSheet sheet = workbook.getSheetAt(0);
                    for (Row row : sheet) {
                        List<Cell> rowData = new ArrayList<>();
                        for (Cell cell : row) {
                            rowData.add(cell);
                        }
                        sheetData.add(rowData);
                    }
                } else {
                    HSSFWorkbook workbook = new HSSFWorkbook(fis);
                    HSSFSheet sheet = workbook.getSheetAt(0);
                    for (Row row : sheet) {
                        List<Cell> rowData = new ArrayList<>();
                        for (Cell cell : row) {
                            rowData.add(cell);
                        }
                        sheetData.add(rowData);
                    }
                }
            } finally {
                if (fis != null) fis.close();
            }

            showExelData2(sheetData);
            
            
            
        } else {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error In File Uploading. Please Check!!!"));
            
        }
    }

    
private void showExelData2(List<List<Cell>> sheetData) throws ParseException {

    System.out.println("showExelData2 hitted !!!");

    stuList = new ArrayList<>();
    Connection conn = DataBaseConnection.JAVAConnection();

    for (int i = 1; i < sheetData.size() - 1; i++) {
        List<Cell> list = sheetData.get(i);
        NachExcelInfo info = new NachExcelInfo();
        String temp = "";

        for (int j = 0; j < list.size(); j++) {
            Cell cell = list.get(j);
            if (cell == null) continue;

            DataFormatter fmt = new DataFormatter();
            temp = fmt.formatCellValue(cell);

            switch (j) {
                case 0: info.setUmrn(temp); break;
                case 1: info.setBeneficiaryAccountHolderName(temp); break;
                case 2: info.setBeneficiaryBankAccountNumber(temp); break;
                case 3: info.setDestinationBankIfscMicrIin(temp); break;
                case 4: info.setAmount(temp); break;
                case 5: info.setTransactionReference(temp); break;
                case 6: info.setReservedAchItemSeqNo(temp); break;
                case 7: 
                    info.setStatus(temp);
                    System.out.println(temp); 
                    break;
                case 8: info.setRemark(temp); break;
                case 9: info.setValueDate(temp); break;
            }
        }

        // ✅ Add the object only once per row
        stuList.add(info);
    }

    insertData2(stuList);
}

   
   
public void insertData2(ArrayList<NachExcelInfo> stuList) {
    System.out.println("insertData2 hit!");

    Date todayDate = new Date();

    try (Connection conn = DataBaseConnection.JAVAConnection()) {
        String query = "insert into loan_payment_from_app (employee, loan_type, loan_no, pay_date, amount, pay_mode, status, entry_date, transaction_id, branch_id, umrn_no) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement ps = conn.prepareStatement(query)) {
            HashSet<String> insertedUmrns = new HashSet<>();

            for (NachExcelInfo ff : stuList) {
            	 System.out.println("///////////////////////");
                System.out.println("umrn number in insert table: " + ff.getUmrn());
                System.out.println("employee: admin");
               
                System.out.println("loan_no (Transaction Reference): " + ff.getTransactionReference());
                System.out.println("pay_date: " + new java.sql.Date(date.getTime()));
                System.out.println("amount: " + ff.getAmount());
                System.out.println("pay_mode: online");
                System.out.println("status: pending");
                System.out.println("entry_date: " + new java.sql.Date(date.getTime()));
                System.out.println("transaction_id: " + ff.getTransactionRefId());
               
                System.out.println("///////////////////////");

                // ✅ Skip if UMRN already processed
                if (insertedUmrns.contains(ff.getUmrn())) {
                    continue;
                }
                
                insertedUmrns.add(ff.getUmrn());

                ps.setString(1, "admin");

                String branch_id = "";
                String type = checkLoanType(ff.getTransactionReference());

                if ("personalloan".equals(type)) {
                    branch_id = dbm.selectBranchIdPersonalLoan(ff.getTransactionReference());
                } else {
                    branch_id = dbm.selectBranchIdGroupLoan(ff.getTransactionReference());
                }

                ps.setString(2, type);

                SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
                Date date = formatter.parse(ff.getValueDate());

                ps.setString(3, ff.getTransactionReference());
                ps.setDate(4, new java.sql.Date(date.getTime()));
                ps.setString(5, ff.getAmount());
                ps.setString(6, "online");
                ps.setString(7, "pending");
                ps.setDate(8, new java.sql.Date(date.getTime()));
                ps.setString(9, ff.getTransactionRefId());
                ps.setString(10, branch_id);
                ps.setString(11, ff.getUmrn());

                ps.executeUpdate();
                
            }

            FacesContext.getCurrentInstance().getExternalContext().redirect("uploadExcel.xhtml");

        } catch (Exception e) {
            e.printStackTrace();
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

    
    
    
    /////////////////
 
    // Getters and Setters
    public String getFilePath() {
        return filePath;
    }

    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    public UploadedFile getExcelFile() {
        return excelFile;
    }

    public void setExcelFile(UploadedFile excelFile) {
        this.excelFile = excelFile;
    }
    
    public UploadedFile getExcelFile2() {
		return excelFile2;
	}


}