custom_ui/frontend/PAGINATION_USAGE.md

# Server-Side Pagination Implementation Guide

## Overview

This implementation provides server-side pagination with persistent state management for large datasets (5000+ records). It combines PrimeVue's lazy loading capabilities with Pinia stores for state persistence.

## Architecture

### Stores

1. **`usePaginationStore`** - Manages pagination state (page, pageSize, totalRecords, sorting)
2. **`useFiltersStore`** - Manages filter state (existing, enhanced for pagination)
3. **`useLoadingStore`** - Manages loading states (existing, works with pagination)

### Components

1. **`DataTable`** - Enhanced with lazy loading support
2. **`Api`** - Updated with pagination and filtering parameters

## Key Features

✅ **Server-side pagination** - Only loads current page data
✅ **Persistent state** - Page and filter state survive navigation
✅ **Real-time filtering** - Filters reset to page 1 and re-query server
✅ **Sorting support** - Server-side sorting with state persistence
✅ **Loading states** - Integrated with existing loading system
✅ **Performance** - Handles 5000+ records efficiently

## Usage

### Basic Paginated DataTable

```vue
<template>
  <DataTable
    :data="tableData"
    :columns="columns"
    tableName="clients"
    :lazy="true"
    :totalRecords="totalRecords"
    :loading="isLoading"
    :onLazyLoad="handleLazyLoad"
    @lazy-load="handleLazyLoad"
  />
</template>

<script setup>
import { ref, onMounted } from "vue";
import { usePaginationStore } from "@/stores/pagination";
import { useFiltersStore } from "@/stores/filters";
import Api from "@/api";

const paginationStore = usePaginationStore();
const filtersStore = useFiltersStore();

const tableData = ref([]);
const totalRecords = ref(0);
const isLoading = ref(false);

const handleLazyLoad = async (event) => {
  try {
    isLoading.value = true;

    const paginationParams = {
      page: event.page || 0,
      pageSize: event.rows || 10,
      sortField: event.sortField,
      sortOrder: event.sortOrder,
    };

    const filters = {};
    if (event.filters) {
      Object.keys(event.filters).forEach((key) => {
        if (key !== "global" && event.filters[key]?.value) {
          filters[key] = event.filters[key];
        }
      });
    }

    const result = await Api.getPaginatedData(paginationParams, filters);

    tableData.value = result.data;
    totalRecords.value = result.totalRecords;
    paginationStore.setTotalRecords("tableName", result.totalRecords);
  } catch (error) {
    console.error("Error loading data:", error);
    tableData.value = [];
    totalRecords.value = 0;
  } finally {
    isLoading.value = false;
  }
};

onMounted(async () => {
  // Initialize stores
  paginationStore.initializeTablePagination("tableName", { rows: 10 });
  filtersStore.initializeTableFilters("tableName", columns);

  // Load initial data
  const pagination = paginationStore.getTablePagination("tableName");
  const filters = filtersStore.getTableFilters("tableName");

  await handleLazyLoad({
    page: pagination.page,
    rows: pagination.rows,
    first: pagination.first,
    sortField: pagination.sortField,
    sortOrder: pagination.sortOrder,
    filters: filters,
  });
});
</script>
```

## API Implementation

### Required API Method Structure

```javascript
// In your API class
static async getPaginatedData(paginationParams = {}, filters = {}) {
  const {
    page = 0,
    pageSize = 10,
    sortField = null,
    sortOrder = null
  } = paginationParams;

  // Build database query with pagination
  const offset = page * pageSize;
  const limit = pageSize;

  // Apply filters to query
  const whereClause = buildWhereClause(filters);

  // Apply sorting
  const orderBy = sortField ? `${sortField} ${sortOrder === -1 ? 'DESC' : 'ASC'}` : '';

  // Execute queries
  const [data, totalCount] = await Promise.all([
    db.query(`SELECT * FROM table ${whereClause} ${orderBy} LIMIT ${limit} OFFSET ${offset}`),
    db.query(`SELECT COUNT(*) FROM table ${whereClause}`)
  ]);

  return {
    data: data,
    totalRecords: totalCount[0].count
  };
}
```

### Frappe Framework Implementation

```javascript
static async getPaginatedClientDetails(paginationParams = {}, filters = {}) {
  const { page = 0, pageSize = 10, sortField = null, sortOrder = null } = paginationParams;

  // Build Frappe filters
  let frappeFilters = {};
  Object.keys(filters).forEach(key => {
    if (filters[key] && filters[key].value) {
      switch (key) {
        case 'fullName':
          frappeFilters.address_line1 = ['like', `%${filters[key].value}%`];
          break;
        // Add other filter mappings
      }
    }
  });

  // Get total count and paginated data
  const [totalCount, records] = await Promise.all([
    this.getDocCount("DocType", frappeFilters),
    this.getDocsList("DocType", ["*"], frappeFilters, page, pageSize)
  ]);

  // Process and return data
  const processedData = records.map(record => ({
    id: record.name,
    // ... other fields
  }));

  return {
    data: processedData,
    totalRecords: totalCount
  };
}
```

## DataTable Props

### New Props for Pagination

```javascript
const props = defineProps({
  // Existing props...

  // Server-side pagination
  lazy: {
    type: Boolean,
    default: false, // Set to true for server-side pagination
  },
  totalRecords: {
    type: Number,
    default: 0, // Total records from server
  },
  onLazyLoad: {
    type: Function,
    default: null, // Lazy load handler function
  },
});
```

### Events

- **`@lazy-load`** - Emitted when pagination/filtering/sorting changes
- **`@page-change`** - Emitted when page changes
- **`@sort-change`** - Emitted when sorting changes
- **`@filter-change`** - Emitted when filters change

## Pagination Store Methods

### Basic Usage

```javascript
const paginationStore = usePaginationStore();

// Initialize pagination for a table
paginationStore.initializeTablePagination("clients", {
  rows: 10,
  totalRecords: 0,
});

// Update pagination after API response
paginationStore.setTotalRecords("clients", 1250);

// Navigate pages
paginationStore.setPage("clients", 2);
paginationStore.nextPage("clients");
paginationStore.previousPage("clients");

// Get pagination parameters for API calls
const params = paginationStore.getPaginationParams("clients");
// Returns: { page: 2, pageSize: 10, offset: 20, limit: 10, sortField: null, sortOrder: null }

// Get page information for display
const info = paginationStore.getPageInfo("clients");
// Returns: { start: 21, end: 30, total: 1250 }
```

### Advanced Methods

```javascript
// Handle PrimeVue lazy load events
const params = paginationStore.handleLazyLoad("clients", primeVueEvent);

// Set sorting
paginationStore.setSorting("clients", "name", 1); // 1 for ASC, -1 for DESC

// Change rows per page
paginationStore.setRowsPerPage("clients", 25);

// Reset to first page (useful when filters change)
paginationStore.resetToFirstPage("clients");

// Get computed properties
const totalPages = paginationStore.getTotalPages("clients");
const hasNext = paginationStore.hasNextPage("clients");
const hasPrevious = paginationStore.hasPreviousPage("clients");
```

## Filter Integration

Filters work seamlessly with pagination:

```javascript
// When a filter changes, pagination automatically resets to page 1
const handleFilterChange = (fieldName, value) => {
  // Update filter
  filtersStore.updateTableFilter("clients", fieldName, value);

  // Pagination automatically resets to page 1 in DataTable component
  // New API call is triggered with updated filters
};
```

## State Persistence

Both pagination and filter states persist across:

- Component re-mounts
- Page navigation
- Browser refresh (if using localStorage)

### Persistence Configuration

```javascript
// In your store, you can add persistence
import { defineStore } from "pinia";

export const usePaginationStore = defineStore("pagination", {
  // ... store definition

  persist: {
    enabled: true,
    strategies: [
      {
        key: "pagination-state",
        storage: localStorage, // or sessionStorage
        paths: ["tablePagination"],
      },
    ],
  },
});
```

## Performance Considerations

### Database Optimization

1. **Indexes** - Ensure filtered and sorted columns are indexed
2. **Query Optimization** - Use efficient WHERE clauses
3. **Connection Pooling** - Handle concurrent requests efficiently

### Frontend Optimization

1. **Debounced Filtering** - Avoid excessive API calls
2. **Loading States** - Provide user feedback during requests
3. **Error Handling** - Gracefully handle API failures
4. **Memory Management** - Clear data when not needed

### Recommended Page Sizes

- **Small screens**: 5-10 records
- **Desktop**: 10-25 records
- **Large datasets**: 25-50 records
- **Avoid**: 100+ records per page

## Error Handling

```javascript
const handleLazyLoad = async (event) => {
  try {
    isLoading.value = true;
    const result = await Api.getPaginatedData(params, filters);

    // Success handling
    tableData.value = result.data;
    totalRecords.value = result.totalRecords;
  } catch (error) {
    console.error("Pagination error:", error);

    // Reset to safe state
    tableData.value = [];
    totalRecords.value = 0;

    // Show user-friendly error
    showErrorToast("Failed to load data. Please try again.");

    // Optionally retry with fallback parameters
    if (event.page > 0) {
      paginationStore.setPage(tableName, 0);
      // Retry with page 0
    }
  } finally {
    isLoading.value = false;
  }
};
```

## Migration from Client-Side

### Before (Client-side)

```javascript
// Old approach - loads all data
onMounted(async () => {
  const data = await Api.getAllClients(); // 5000+ records
  tableData.value = data;
});
```

### After (Server-side)

```javascript
// New approach - loads only current page
onMounted(async () => {
  paginationStore.initializeTablePagination("clients");

  await handleLazyLoad({
    page: 0,
    rows: 10,
    // ... other params
  });
});
```

## Testing

### Unit Tests

```javascript
import { usePaginationStore } from "@/stores/pagination";

describe("Pagination Store", () => {
  it("should initialize pagination correctly", () => {
    const store = usePaginationStore();
    store.initializeTablePagination("test", { rows: 20 });

    const pagination = store.getTablePagination("test");
    expect(pagination.rows).toBe(20);
    expect(pagination.page).toBe(0);
  });

  it("should handle page navigation", () => {
    const store = usePaginationStore();
    store.setTotalRecords("test", 100);
    store.setPage("test", 2);

    expect(store.getTablePagination("test").page).toBe(2);
    expect(store.hasNextPage("test")).toBe(true);
  });
});
```

### Integration Tests

```javascript
// Test lazy loading with mock API
const mockLazyLoad = vi.fn().mockResolvedValue({
  data: [{ id: 1, name: "Test" }],
  totalRecords: 50,
});

// Test component with mocked API
const wrapper = mount(DataTableComponent, {
  props: {
    lazy: true,
    onLazyLoad: mockLazyLoad,
  },
});

// Verify API calls
expect(mockLazyLoad).toHaveBeenCalledWith({
  page: 0,
  rows: 10,
  // ... expected parameters
});
```

## Troubleshooting

### Common Issues

1. **Infinite Loading**
   - Check API endpoint returns correct totalRecords
   - Verify pagination parameters are calculated correctly

2. **Filters Not Working**
   - Ensure filter parameters are passed to API correctly
   - Check database query includes WHERE clauses

3. **Page State Not Persisting**
   - Verify store persistence is configured
   - Check localStorage/sessionStorage permissions

4. **Performance Issues**
   - Add database indexes for filtered/sorted columns
   - Optimize API query efficiency
   - Consider reducing page size

### Debug Information

```javascript
// Add debug logging to lazy load handler
const handleLazyLoad = async (event) => {
  console.log("Lazy Load Event:", {
    page: event.page,
    rows: event.rows,
    sortField: event.sortField,
    sortOrder: event.sortOrder,
    filters: event.filters,
    timestamp: new Date().toISOString(),
  });

  // ... rest of implementation
};
```

This implementation provides a robust, performant solution for handling large datasets with persistent pagination and filtering state.