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.security.Timestamp;
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;



@ManagedBean(name = "dNach")
@ViewScoped
public class DeNachBean implements Serializable {

    private static final long serialVersionUID = 1L;

    String filePath, branchId;
    UploadedFile excelFile;
  
	ArrayList<NachExcelInfo> stuList = new ArrayList<>();
    Date date = new Date();
    DataBaseMethods dbm = new DataBaseMethods();

    public DeNachBean() {
        
    	branchId = "sam";
    	
    }
 
    public String checkLoanType(String type){
    	String lType="";
    	
    	if(type.contains("gl")||type.contains("GL")) {
    		
    	    lType = "grouploan";
    	}else {
    		
    		  lType = "personalloan";
    	}
    	
    	return lType;
  
    }
    
    /////////////////
    
public void bbpsExcel() throws IOException, ParseException {
    	
    	System.out.println("bbpsExcel2 hitted !!!");
    	
        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();
            }

            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(); 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);
                   
                    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) {
  

    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) {
               
                //  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());
               
                if(ff.getStatus().equalsIgnoreCase("Completed")) {
                
                ps.executeUpdate();
           
               }
                
            }

            FacesContext.getCurrentInstance().getExternalContext().redirect("uploadNach.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 "";
	}

    
    
    
    /////////////////
 
    // 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 void setExcelFile2(UploadedFile excelFile) {
  		this.excelFile = excelFile;
  	}

}