package natch_package;

import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.model.UploadedFile;

import main_package.DataBaseConnection;

@ManagedBean(name = "uploadKjMandatesBean")
@ViewScoped
public class UploadKjMandatesBean implements Serializable {

    private transient UploadedFile excelFile;

    

    // ======================= UPLOAD METHOD =======================
    public void uploadKjMandatesFromExcel() {

        if (excelFile == null) {
            FacesContext.getCurrentInstance().addMessage(null,
                new FacesMessage(FacesMessage.SEVERITY_ERROR, "Please select Excel file", null));
            return;
        }

        try (InputStream input = excelFile.getInputstream();) {

            Workbook workbook = excelFile.getFileName().endsWith(".xlsx")
                    ? new XSSFWorkbook(input)
                    : new HSSFWorkbook(input);

            Sheet sheet = workbook.getSheetAt(0);
            DataFormatter formatter = new DataFormatter();
            SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");

            for (Row row : sheet) {

                if (row.getRowNum() == 0) continue; // skip header

                KjMandate dto = new KjMandate();

                dto.setCustomerName(formatter.formatCellValue(row.getCell(0)));
                dto.setMobile(formatter.formatCellValue(row.getCell(1)));
                dto.setUmrn(formatter.formatCellValue(row.getCell(2)));
                dto.setEmail(formatter.formatCellValue(row.getCell(3)));
                dto.setAccountNumber(getAccountNumber(row.getCell(4)));
                dto.setIfsc(formatter.formatCellValue(row.getCell(5)));
                dto.setBankCode(formatter.formatCellValue(row.getCell(6)));
                dto.setAuthType(formatter.formatCellValue(row.getCell(7)));

                dto.setMandateAmount(new BigDecimal(formatter.formatCellValue(row.getCell(8)).replace(",", "")));
                dto.setCollectionAmount(new BigDecimal(formatter.formatCellValue(row.getCell(9)).replace(",", "")));

                dto.setStartDate(parseExcelDate(row.getCell(10)));
                dto.setEndDate(parseExcelDate(row.getCell(11)));


                dto.setFrequency(formatter.formatCellValue(row.getCell(12)));
                dto.setMerchantTxnRefId(formatter.formatCellValue(row.getCell(13)));
                dto.setStatus(formatter.formatCellValue(row.getCell(14)));
                dto.setUserId(formatter.formatCellValue(row.getCell(15)));
                dto.setLoanNo(formatter.formatCellValue(row.getCell(16)));

                // Required fields for DB
                dto.setRedirectUrl("https://delightscredit.com/SOFT/thankYou.html");
                dto.setCallbackUrl("https://delightscredit.com/callback");

                // Save to DB via service
                 saveFromExcel(dto);
                 System.out.println(dto);

            }

            workbook.close();

            FacesContext.getCurrentInstance().addMessage(null,
                new FacesMessage(FacesMessage.SEVERITY_INFO, "Mandates Uploaded Successfully!", null));

        } catch (Exception e) {
            e.printStackTrace();
            FacesContext.getCurrentInstance().addMessage(null,
                new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading Excel data", null));
        }
    }
    
    public void saveFromExcel(UploadKjMandatesBean.KjMandate dto) throws SQLException {

        String sql = "INSERT INTO nach_mandate_request (" +
                "customer_name, mobile, email, account_number, ifsc, bank_code, auth_type, " +
                "mandate_amount, collection_amount, start_date, end_date, frequency, " +
                "redirect_url, callback_url, merchant_trxn_ref_id, umrn, status, user_id, loan_no" +
                ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try (Connection con = DataBaseConnection.JAVAConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {

            ps.setString(1, dto.getCustomerName());
            ps.setString(2, dto.getMobile());
            ps.setString(3, dto.getEmail());
            ps.setString(4, dto.getAccountNumber());
            ps.setString(5, dto.getIfsc());
            ps.setString(6, dto.getBankCode());
            ps.setString(7, dto.getAuthType());

            ps.setBigDecimal(8, dto.getMandateAmount());
            ps.setBigDecimal(9, dto.getCollectionAmount());

            ps.setDate(10, new java.sql.Date(dto.getStartDate().getTime()));
            ps.setDate(11, new java.sql.Date(dto.getEndDate().getTime()));

            ps.setString(12, dto.getFrequency());

            ps.setString(13, dto.getRedirectUrl());
            ps.setString(14, dto.getCallbackUrl());

            ps.setString(15, dto.getMerchantTxnRefId());
            ps.setString(16, dto.getUmrn());
            ps.setString(17, dto.getStatus());

            ps.setString(18, dto.getUserId());
            ps.setString(19, dto.getLoanNo());

            ps.executeUpdate();
        }
    }
    
    private java.sql.Date parseExcelDate(Cell cell) throws Exception {

        if (cell == null) return null;

        // Case 1: Real Excel Date type
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            return new java.sql.Date(cell.getDateCellValue().getTime());
        }

        String dateStr = new DataFormatter().formatCellValue(cell);

        if (dateStr == null || dateStr.trim().isEmpty() || dateStr.contains("1900")) {
            return null;
        }

        Date parsed = null;

        try {
            parsed = new SimpleDateFormat("d/M/yyyy").parse(dateStr);
        } catch (Exception e) {
            // Try MM-DD-YYYY
            parsed = new SimpleDateFormat("MM-dd-yyyy").parse(dateStr);
        }

        return new java.sql.Date(parsed.getTime());  // Always stored as YYYY-MM-DD
    }


    private String getAccountNumber(Cell cell) {

        if (cell == null) return null;

        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // Prevent scientific notation
            return new java.math.BigDecimal(cell.getNumericCellValue())
                    .toPlainString();
        } else {
            return new DataFormatter().formatCellValue(cell);
        }
    }





    // ======================= DTO CLASS =======================
    public static class KjMandate implements Serializable {

    	@Override
    	public String toString() {
    	    return "KjMandate {" +
    	            "customerName='" + customerName + '\'' +
    	            ", mobile='" + mobile + '\'' +
    	            ", umrn='" + umrn + '\'' +
    	            ", email='" + email + '\'' +
    	            ", accountNumber='" + accountNumber + '\'' +
    	            ", ifsc='" + ifsc + '\'' +
    	            ", bankCode='" + bankCode + '\'' +
    	            ", authType='" + authType + '\'' +
    	            ", mandateAmount=" + mandateAmount +
    	            ", collectionAmount=" + collectionAmount +
    	            ", startDate=" + startDate +
    	            ", endDate=" + endDate +
    	            ", frequency='" + frequency + '\'' +
    	            ", merchantTxnRefId='" + merchantTxnRefId + '\'' +
    	            ", status='" + status + '\'' +
    	            ", userId='" + userId + '\'' +
    	            ", loanNo='" + loanNo + '\'' +
    	            ", redirectUrl='" + redirectUrl + '\'' +
    	            ", callbackUrl='" + callbackUrl + '\'' +
    	            '}';
    	}

        private String customerName;
        private String mobile;
        private String umrn;
        private String email;
        private String accountNumber;
        private String ifsc;
        private String bankCode;
        private String authType;
        private BigDecimal mandateAmount;
        private BigDecimal collectionAmount;
        private Date startDate;
        private Date endDate;
        private String frequency;
        private String merchantTxnRefId;
        private String status;
        private String userId;
        private String loanNo;
        private String redirectUrl;
        private String callbackUrl;

        // ===== GETTERS & SETTERS =====

        public String getCustomerName() { return customerName; }
        public void setCustomerName(String customerName) { this.customerName = customerName; }

        public String getMobile() { return mobile; }
        public void setMobile(String mobile) { this.mobile = mobile; }

        public String getUmrn() { return umrn; }
        public void setUmrn(String umrn) { this.umrn = umrn; }

        public String getEmail() { return email; }
        public void setEmail(String email) { this.email = email; }

        public String getAccountNumber() { return accountNumber; }
        public void setAccountNumber(String accountNumber) { this.accountNumber = accountNumber; }

        public String getIfsc() { return ifsc; }
        public void setIfsc(String ifsc) { this.ifsc = ifsc; }

        public String getBankCode() { return bankCode; }
        public void setBankCode(String bankCode) { this.bankCode = bankCode; }

        public String getAuthType() { return authType; }
        public void setAuthType(String authType) { this.authType = authType; }

        public BigDecimal getMandateAmount() { return mandateAmount; }
        public void setMandateAmount(BigDecimal mandateAmount) { this.mandateAmount = mandateAmount; }

        public BigDecimal getCollectionAmount() { return collectionAmount; }
        public void setCollectionAmount(BigDecimal collectionAmount) { this.collectionAmount = collectionAmount; }

        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 String getFrequency() { return frequency; }
        public void setFrequency(String frequency) { this.frequency = frequency; }

        public String getMerchantTxnRefId() { return merchantTxnRefId; }
        public void setMerchantTxnRefId(String merchantTxnRefId) { this.merchantTxnRefId = merchantTxnRefId; }

        public String getStatus() { return status; }
        public void setStatus(String status) { this.status = status; }

        public String getUserId() { return userId; }
        public void setUserId(String userId) { this.userId = userId; }

        public String getLoanNo() { return loanNo; }
        public void setLoanNo(String loanNo) { this.loanNo = loanNo; }

        public String getRedirectUrl() { return redirectUrl; }
        public void setRedirectUrl(String redirectUrl) { this.redirectUrl = redirectUrl; }

        public String getCallbackUrl() { return callbackUrl; }
        public void setCallbackUrl(String callbackUrl) { this.callbackUrl = callbackUrl; }
    }

    // ================= FILE GET/SET =================
    public UploadedFile getExcelFile() { return excelFile; }
    public void setExcelFile(UploadedFile excelFile) { this.excelFile = excelFile; }
}
