// Find bills that are NOT finalized (checked = false or null)
WHERE b.checked = false OR b.checked IS NULL

// Find bills that are finalized but NOT completed
WHERE b.checked = true
  AND (b.completed = false OR b.completed IS NULL)

// Find bills that are completed
WHERE b.completed = true
ORDER BY b.completedAt DESC

public void saveDisposalIssueReturnBill() {
    // Validate return quantities
    if (!validateReturnQuantities()) {
        return;
    }
    saveBill();           // Save bill entity
    saveBillComponents(); // Save bill items
    JsfUtil.addSuccessMessage("Saved");
}

public void finalizeDisposalIssueReturnBill() {
    // Validate and save first
    if (!validateReturnQuantities()) {
        return;
    }
    saveDisposalIssueReturnBill();

    // Mark as finalized
    getReturnBill().setEditedAt(new Date());
    getReturnBill().setEditor(sessionController.getLoggedUser());
    getReturnBill().setChecked(true);
    getReturnBill().setCheckeAt(new Date());
    getReturnBill().setCheckedBy(sessionController.getLoggedUser());

    getBillFacade().edit(getReturnBill());
    printPreview = true;  // Switch to print preview mode
    JsfUtil.addSuccessMessage("Finalized");
}

public void settleDisposalIssueReturnBill() {
    // Validate bill is finalized
    if (getReturnBill().getCheckedBy() == null) {
        JsfUtil.addErrorMessage("Please Finalize Bill First. Cannot Return");
        return;
    }

    // Validate quantities
    if (!hasQtyToReturn(returnBillItems)) {
        JsfUtil.addErrorMessage("Return Quantity is Zero. Cannot Return");
        return;
    }

    if (!validateReturnQuantities()) {
        return;
    }

    // Save as completed
    calculateBillTotal();
    saveSettlingBill();          // Sets completed = true
    saveSettlingBillComponents();

    // Update references
    getReturnBill().setReferenceBill(getOriginalBill());
    getBillFacade().edit(getReturnBill());

    getOriginalBill().setRefundedBill(getReturnBill());
    getOriginalBill().setRefunded(true);
    getOriginalBill().getRefundBills().add(getReturnBill());
    getBillFacade().edit(getOriginalBill());

    printPreview = true;
    JsfUtil.addSuccessMessage("Successfully Returned");
}

private Bill originalBill;           // The original disposal issue bill
private Bill returnBill;             // The return bill being created
private List<BillItem> returnBillItems;
private boolean printPreview;        // Toggle between edit/print mode

public void fillDisposalReturnsToFinalize() {
    Map<String, Object> params = new HashMap<>();
    String jpql = "SELECT b FROM Bill b "
        + "WHERE b.retired = :retired "
        + "AND b.billTypeAtomic = :billTypeAtomic "
        + "AND b.department = :department "
        + "AND (b.checked = :checked OR b.checked IS NULL) "
        + "AND (b.cancelled = :cancelled OR b.cancelled IS NULL) "
        + "ORDER BY b.createdAt DESC";

    params.put("retired", false);
    params.put("billTypeAtomic", BillTypeAtomic.PHARMACY_DISPOSAL_ISSUE_RETURN);
    params.put("department", sessionController.getDepartment());
    params.put("checked", false);
    params.put("cancelled", false);

    disposalReturnsToFinalize = billFacade.findByJpql(jpql, params);
}

public void fillDisposalReturnsToApprove() {
    Map<String, Object> params = new HashMap<>();
    String jpql = "SELECT b FROM Bill b "
        + "WHERE b.retired = :retired "
        + "AND b.billTypeAtomic = :billTypeAtomic "
        + "AND b.department = :department "
        + "AND b.checked = :checked "
        + "AND (b.cancelled = :cancelled OR b.cancelled IS NULL) "
        + "AND (b.completed = :completed OR b.completed IS NULL) "
        + "ORDER BY b.checkeAt DESC";

    params.put("retired", false);
    params.put("billTypeAtomic", BillTypeAtomic.PHARMACY_DISPOSAL_ISSUE_RETURN);
    params.put("department", sessionController.getDepartment());
    params.put("checked", true);
    params.put("cancelled", false);
    params.put("completed", false);

    disposalReturnsToApprove = billFacade.findByJpql(jpql, params);
}

public void fillCompletedDisposalReturns() {
    Map<String, Object> params = new HashMap<>();
    String jpql = "SELECT b FROM Bill b "
        + "WHERE b.retired = :retired "
        + "AND b.billTypeAtomic = :billTypeAtomic "
        + "AND b.department = :department "
        + "AND b.completed = :completed "
        + "AND (b.cancelled = :cancelled OR b.cancelled IS NULL) "
        + "ORDER BY b.completedAt DESC";

    params.put("retired", false);
    params.put("billTypeAtomic", BillTypeAtomic.PHARMACY_DISPOSAL_ISSUE_RETURN);
    params.put("department", sessionController.getDepartment());
    params.put("completed", true);
    params.put("cancelled", false);

    completedDisposalReturns = billFacade.findByJpql(jpql, params);
}

public String navigateToProcessDisposalReturn(Bill disposalReturnBill) {
    if (disposalReturnBill == null || disposalReturnBill.getReferenceBill() == null) {
        JsfUtil.addErrorMessage("No disposal return bill selected");
        return null;
    }

    // Reload bills to get fresh data
    Bill returnBill = billService.reloadBill(disposalReturnBill);
    Bill originalBill = billService.reloadBill(disposalReturnBill.getReferenceBill());

    // Set in IssueReturnController
    issueReturnController.setReturnBill(returnBill);
    issueReturnController.setOriginalBill(originalBill);
    issueReturnController.setReturnBillItems(returnBill.getBillItems());
    issueReturnController.setOriginalBillItems(originalBill.getBillItems());

    // Navigate to processing page
    return "/pharmacy/pharmacy_bill_return_issue?faces-redirect=true";
}

public String navigateToPrintCompletedDisposalReturn(Bill completedReturnBill) {
    if (completedReturnBill == null || completedReturnBill.getReferenceBill() == null) {
        JsfUtil.addErrorMessage("No disposal return bill selected");
        return null;
    }

    // Reload bills
    Bill returnBill = billService.reloadBill(completedReturnBill);
    Bill originalBill = billService.reloadBill(completedReturnBill.getReferenceBill());

    // Set in IssueReturnController
    issueReturnController.setReturnBill(returnBill);
    issueReturnController.setOriginalBill(originalBill);
    issueReturnController.setReturnBillItems(returnBill.getBillItems());
    issueReturnController.setOriginalBillItems(originalBill.getBillItems());

    // Enable print preview mode
    issueReturnController.setPrintPreview(true);

    // Navigate to print preview
    return "/pharmacy/pharmacy_bill_return_issue?faces-redirect=true";
}

public void closeDisposalReturn(Bill returnBillToClose) {
    // Validate bill can be closed
    if (returnBillToClose.isCompleted()) {
        JsfUtil.addErrorMessage("Cannot close a completed disposal return");
        return;
    }
    if (returnBillToClose.isBillClosed()) {
        JsfUtil.addErrorMessage("This disposal return is already closed");
        return;
    }

    // Mark as closed
    returnBillToClose.setBillClosed(true);
    billFacade.edit(returnBillToClose);

    // Refresh lists
    fillDisposalReturnsToFinalize();
    fillDisposalReturnsToApprove();

    JsfUtil.addSuccessMessage("Disposal return closed successfully...");
}

String jpql = "SELECT COUNT(b) FROM Bill b "
    + "WHERE b.retired = :retired "
    + "AND b.billTypeAtomic = :billTypeAtomic "
    + "AND b.department = :department "
    + "AND b.referenceBill = :referenceBill "
    + "AND (b.cancelled = :cancelled OR b.cancelled IS NULL) "
    + "AND (b.billClosed = :closed OR b.billClosed IS NULL) "  // Exclude closed
    + "AND (b.completed = :completed OR b.completed IS NULL) ";

params.put("closed", false);

public boolean validateReturnQuantities() {
    if (returnBillItems == null || returnBillItems.isEmpty()) {
        JsfUtil.addErrorMessage("No items found to return");
        return false;
    }

    for (BillItem returnItem : returnBillItems) {
        if (returnItem == null || returnItem.getReferanceBillItem() == null) {
            continue;
        }

        double requestedReturnQty = returnItem.getQty();
        if (requestedReturnQty <= 0) {
            continue; // Skip zero quantities
        }

        // Validate against original issued quantity and previous returns
        if (!validateItemReturnQuantity(returnItem)) {
            return false;
        }
    }

    return true;
}

public boolean hasPendingDisposalReturnForSpecificBill(Bill disposalIssueBill) {
    String jpql = "SELECT COUNT(b) FROM Bill b "
        + "WHERE b.retired = :retired "
        + "AND b.billTypeAtomic = :billTypeAtomic "
        + "AND b.department = :department "
        + "AND b.referenceBill = :referenceBill "
        + "AND b.checked = :checked "
        + "AND (b.cancelled = :cancelled OR b.cancelled IS NULL) "
        + "AND (b.completed = :completed OR b.completed IS NULL)";

    // ... parameters

    Long count = billFacade.countByJpql(jpql, params);
    return count != null && count > 0;
}