package natch_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import java.util.Collections;

import main_package.DataBaseConnection;
import main_package.NachMandateData;

@ManagedBean(name = "enachTransactionSummaryKJ")
@ViewScoped
public class EnachTransactionSummaryKJ implements Serializable {

    private static final long serialVersionUID = 1L;
    private static final String TIME_ZONE = "Asia/Kolkata";
    private static final String MONTHLY_FREQUENCY = "MNTH";

    private List<NachMandateData> unifiedPaymentList = new ArrayList<>();
    private Date fromDate;
    private Date toDate;

    public EnachTransactionSummaryKJ() {
        Calendar cal = getIndiaCalendar();
        this.toDate = truncateTime(cal).getTime();
        cal.add(Calendar.DAY_OF_MONTH, -30);
        this.fromDate = truncateTime(cal).getTime();
    }

    

    public List<NachMandateData> fetchDataFromNachMandateRequest() {
        unifiedPaymentList.clear();
        String query = "SELECT customer_name, mobile, mandate_amount, collection_amount, " +
                       "start_date, end_date, frequency, umrn, user_id, loan_no, " +
                       "rd_connected, rd_number " +
                       "FROM nach_mandate_request WHERE status = 'approved'";

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        sdf.setTimeZone(TimeZone.getTimeZone(TIME_ZONE));
        sdf.setLenient(false);

        try (Connection conn = DataBaseConnection.JAVAConnection();
             PreparedStatement ps = conn.prepareStatement(query);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                String customerName = rs.getString("customer_name");
                String mobile = rs.getString("mobile");
                double mandateAmount = rs.getDouble("mandate_amount");
                String frequency = rs.getString("frequency");
                String umrn = rs.getString("umrn");
                String loanNo = rs.getString("loan_no");
                int rdConnected = rs.getInt("rd_connected");
                String rdNumber = rs.getString("rd_number");

                Date startDate = parseDatabaseDate(rs.getString("start_date"), sdf);
                Date endDate = parseDatabaseDate(rs.getString("end_date"), sdf);

                List<Date> generatedDates = calculatePaymentDates(startDate, endDate, frequency);

                for (Date paymentDate : generatedDates) {
                    if (!isDateBetween(paymentDate, fromDate, toDate)) {
                        continue;
                    }

                    // Check payment status from nach_payments
                    PaymentStatusResponse response = isPaymentReceivedWithUmrn(conn, umrn, paymentDate);

                    NachMandateData data = new NachMandateData();
                    data.setLoanNo(loanNo);
                    data.setCustomerName(customerName);
                    data.setMobile(mobile);
                    data.setStatus(response.getStatus());
                    data.setAmount(mandateAmount);
                    data.setNachDate(paymentDate);
                    data.setMerchantTrxnRefId(response.getMerchantTrxnRefId());
                    data.setUmrn(umrn);
                    data.setDescription(response.getDescription());
                    data.setRdConnected(rdConnected == 1);
                    data.setRdNumber(rdNumber);

                    unifiedPaymentList.add(data);
                }
            }

            // 1. Sort by nachDate descending (Newest date first)
            // Change to (d1, d2) -> d1.getNachDate().compareTo(d2.getNachDate()) for oldest date first
            Collections.sort(unifiedPaymentList, (d1, d2) -> {
                if (d1.getNachDate() == null || d2.getNachDate() == null) return 0;
                return d2.getNachDate().compareTo(d1.getNachDate()); 
            });

            // 2. Re-assign Serial Numbers (S.No) sequentially after sorting
            int sno = 1;
            for (NachMandateData item : unifiedPaymentList) {
                item.setSno(sno++);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return unifiedPaymentList;
    }

    public PaymentStatusResponse isPaymentReceivedWithUmrn(Connection conn, String umrn, Date date) {
        PaymentStatusResponse response = new PaymentStatusResponse();

        if (conn == null || umrn == null || date == null) {
            response.setStatus("ERROR");
            response.setMessage("Parameters must not be null.");
            return response;
        }

        try {
            ZoneId zoneId = ZoneId.of(TIME_ZONE);
            LocalDate calculatedDate = date.toInstant().atZone(zoneId).toLocalDate();
            LocalDate today = LocalDate.now(zoneId);

            // 1. Future date check
            if (calculatedDate.isAfter(today)) {
                response.setStatus("Upcoming");
                response.setMessage("Payment date is in the future.");
                return response;
            }

            // 2. Fetch latest status record matching UMRN from database
            String query = "SELECT merchant_trxn_ref_id, status, code, description " +
                    "FROM nach_payments " +
                    "WHERE umrn = ? " +
                    "AND received_at >= ? " +
                    "AND received_at < DATE_ADD(?, INTERVAL 3 DAY) " +
                    "ORDER BY id DESC LIMIT 1";

            try (PreparedStatement ps= conn.prepareStatement(query)) {
                ps.setString(1, umrn.trim());
                ps.setDate(2, new java.sql.Date(date.getTime()));
                ps.setDate(3, new java.sql.Date(date.getTime()));

                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) {
                        String rawDbStatus = rs.getString("status");
                        String refId = rs.getString("merchant_trxn_ref_id");
                        String code = rs.getString("code");
                        String desc = rs.getString("description");

                        if (rawDbStatus != null && !rawDbStatus.trim().isEmpty()) {
                            String cleanStatus = rawDbStatus.trim().toUpperCase();
                            switch (cleanStatus) {
                                case "SUCCESS":
                                case "SUCCESSFUL":
                                case "PAID":
                                    response.setStatus("Received");
                                    break;
                                case "FAILED":
                                case "FAILURE":
                                case "REJECTED":
                                case "CANCEL":
                                case "CANCELLED":
                                    response.setStatus("Failed");
                                    break;
                                case "PENDING":
                                    response.setStatus("Pending");
                                    break;
                                default:
                                    response.setStatus(rawDbStatus);
                                    break;
                            }
                        } else {
                            response.setStatus("Received");
                        }

                        response.setMerchantTrxnRefId(refId);
                        response.setPaymentStatus(rawDbStatus);
                        response.setCode(code);
                        response.setDescription(desc);
                    } else {
                        // 3. Fallback if no clearing record exists in database
                        LocalDate maxAllowedDate = calculatedDate.plusDays(2);
                        if (today.isAfter(maxAllowedDate)) {
                            response.setStatus("Failed");
                            response.setMessage("No clearing record received within 2 days of schedule date.");
                        } else {
                            response.setStatus("Pending");
                            response.setMessage("Awaiting NACH response file.");
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            response.setStatus("ERROR");
            response.setMessage("Processing error: " + e.getMessage());
        }

        return response;
    }

    private Date parseDatabaseDate(String dateString, SimpleDateFormat sdf) {
        if (dateString == null || dateString.trim().isEmpty()) return null;

        try {
            Date parsedDate = sdf.parse(dateString.substring(0, 10));
            Calendar calendar = getIndiaCalendar();
            calendar.setTime(parsedDate);
            return truncateTime(calendar).getTime();
        } catch (Exception e) {
            return null;
        }
    }

    private List<Date> calculatePaymentDates(Date startDate, Date endDate, String frequency) {
        List<Date> paymentDates = new ArrayList<>();
        if (startDate == null || !MONTHLY_FREQUENCY.equalsIgnoreCase(frequency)) return paymentDates;

        Calendar calendar = getIndiaCalendar();
        calendar.setTime(startDate);
        truncateTime(calendar);

        while (endDate == null || !calendar.getTime().after(endDate)) {
            paymentDates.add(calendar.getTime());
            calendar.add(Calendar.MONTH, 1);
        }
        return paymentDates;
    }

    private boolean isDateBetween(Date paymentDate, Date fromDate, Date toDate) {
        if (paymentDate == null) return false;

        Calendar payment = getIndiaCalendar();
        payment.setTime(paymentDate);
        truncateTime(payment);

        if (fromDate != null) {
            Calendar from = getIndiaCalendar();
            from.setTime(fromDate);
            if (payment.before(truncateTime(from))) return false;
        }

        if (toDate != null) {
            Calendar to = getIndiaCalendar();
            to.setTime(toDate);
            if (payment.after(truncateTime(to))) return false;
        }

        return true;
    }

    private Calendar getIndiaCalendar() {
        return Calendar.getInstance(TimeZone.getTimeZone(TIME_ZONE));
    }

    private Calendar truncateTime(Calendar calendar) {
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        return calendar;
    }

    // Response Data Transfer Object
    public static class PaymentStatusResponse implements Serializable {
        private static final long serialVersionUID = 1L;
        private String status;
        private String merchantTrxnRefId;
        private String paymentStatus;
        private String code;
        private String description;
        private String message;
        private String receivedAt;

        public String getStatus() { return status; }
        public void setStatus(String status) { this.status = status; }

        public String getMerchantTrxnRefId() { return merchantTrxnRefId; }
        public void setMerchantTrxnRefId(String merchantTrxnRefId) { this.merchantTrxnRefId = merchantTrxnRefId; }

        public String getPaymentStatus() { return paymentStatus; }
        public void setPaymentStatus(String paymentStatus) { this.paymentStatus = paymentStatus; }

        public String getCode() { return code; }
        public void setCode(String code) { this.code = code; }

        public String getDescription() { return description; }
        public void setDescription(String description) { this.description = description; }

        public String getMessage() { return message; }
        public void setMessage(String message) { this.message = message; }
		public String getReceivedAt() {
			return receivedAt;
		}
		public void setReceivedAt(String receivedAt) {
			this.receivedAt = receivedAt;
		}
    }

    // Standard Getters and Setters for JSF Binding
    public List<NachMandateData> getUnifiedPaymentList() { return unifiedPaymentList; }
    public void setUnifiedPaymentList(List<NachMandateData> unifiedPaymentList) { this.unifiedPaymentList = unifiedPaymentList; }
    public Date getFromDate() { return fromDate; }
    public void setFromDate(Date fromDate) { this.fromDate = fromDate; }
    public Date getToDate() { return toDate; }
    public void setToDate(Date toDate) { this.toDate = toDate; }
}