package main_package;

import java.io.FileInputStream;
import java.io.IOException;
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 javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.servlet.ServletContext;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.model.UploadedFile;

public class Testing1

{
	
	String filePath,filePath2, branchId;
    UploadedFile excelFile , excelFile2;
    
    
	ArrayList<NachExcelInfo> stuList = new ArrayList<>();
    Date date = new Date();
    DataBaseMethods dbm = new DataBaseMethods();
    
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;
                
            }
        }

        // ✅ 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 = "update personalloan set shareholderno =? where loanNo= ?";

        try (PreparedStatement ps = conn.prepareStatement(query)) {
            HashSet<String> insertedUmrns = new HashSet<>();

            for (NachExcelInfo ff : stuList) {
            	
            	ps.setString(1, ff.getBeneficiaryAccountHolderName());
            	ps.setString(2, ff.getUmrn());
            	
            	System.out.println("Share Holder No updated :"+ff.getUmrn()+"where loanNo was :"+ff.getBeneficiaryAccountHolderName());

            	 
                ps.executeUpdate();
                
            }
          
            FacesContext.getCurrentInstance().getExternalContext().redirect("uploadExcel.xhtml");

        } catch (Exception e) {
            e.printStackTrace();
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}
	




}