# Bulk Read Functionality Refactoring Summary

## Overview

The bulk read functionality has been completely refactored to support multiple service providers and services instead of being limited to Zoho only. This refactoring enables the system to handle data from various providers like Zoho, Salesforce, HubSpot, Keka, BambooHR, Intuit/QuickBooks, etc.

## Key Changes

### 1. Database Schema Changes

#### New Provider-Agnostic Tables

Created new module-specific tables that support multiple providers:

- `contacts_bulk` - Contact data from any provider/service
- `leads_bulk` - Lead data from any provider/service  
- `accounts_bulk` - Account data from any provider/service
- `deals_bulk` - Deal/Opportunity data from any provider/service
- `tasks_bulk` - Task data from any provider/service
- `vendors_bulk` - Vendor data from any provider/service
- `invoices_bulk` - Invoice data from any provider/service
- `sales_orders_bulk` - Sales order data from any provider/service
- `purchase_orders_bulk` - Purchase order data from any provider/service
- `employees_bulk` - Employee/HR data from any provider/service
- `products_bulk` - Product/inventory data from any provider/service
- `customers_bulk` - Customer data from any provider/service

#### New Job Tracking Table

- `bulk_read_jobs` - Provider-agnostic job tracking table replacing `zoho_bulk_read_jobs`

#### Key Schema Features

- **Provider Field**: Identifies the service provider (zoho, salesforce, hubspot, etc.)
- **Service Field**: Identifies the specific service within a provider (crm, books, hr, etc.)
- **Optional Fields**: Most data fields are now optional/nullable to accommodate varying data structures
- **Raw Data Field**: JSON field to store original response data
- **Enhanced Indexing**: Composite indexes on (user_uuid, provider, service) for efficient querying

### 2. API Endpoint Changes

#### Updated Routes in `bulkReadRoutes.js`

**Before:**
```javascript
POST /api/v1/bulk-read/initiate
Body: { module, fields, page, limit }

GET /api/v1/bulk-read/data/:module
```

**After:**
```javascript
POST /api/v1/bulk-read/initiate
Body: { provider, service, module, fields, page, limit }

GET /api/v1/bulk-read/data/:provider/:service/:module
GET /api/v1/bulk-read/providers
GET /api/v1/bulk-read/modules/:provider/:service
```

#### New Query Parameters
- Added `provider`, `service`, `module` filters for job listing
- Enhanced filtering capabilities for multi-provider support

### 3. Service Layer Refactoring

#### BulkReadService Updates

**Provider Configuration System:**
- Configurable provider endpoints and authentication methods
- Support for multiple services per provider
- Provider-specific request formatting

**Supported Providers & Services:**
```javascript
{
  zoho: { services: ['crm', 'books', 'inventory'] },
  salesforce: { services: ['crm'] },
  hubspot: { services: ['crm'] },
  keka: { services: ['hr'] },
  bamboohr: { services: ['hr'] },
  intuit: { services: ['accounting'] },
  quickbooks: { services: ['accounting'] }
}
```

**Key Methods:**
- `initiateBulkRead(userId, provider, service, module, fields, options)`
- `getBulkReadData(userId, provider, service, module, options)`
- `getAvailableProviders()` - Returns all supported providers/services
- `getAvailableModules(provider, service)` - Returns modules for specific provider/service

#### Provider-Specific Adapters

- **Request Formatting**: Each provider has custom request structure
- **Authentication Headers**: Provider-specific auth token handling
- **Response Parsing**: Custom job ID extraction per provider

### 4. Repository Layer Changes

#### New `bulkReadRepository.js`

Replaces provider-specific repositories with a unified repository that:
- Handles multiple table operations
- Supports provider/service filtering
- Provides bulk data insertion capabilities
- Includes error handling for non-existent tables

#### Key Methods
- `createBulkReadJob()` - Creates jobs in new schema
- `getUserData(userId, provider, service, module)` - Gets filtered data
- `insertBulkData(module, data)` - Inserts processed webhook data

### 5. Data Models

#### New Sequelize Models

Created `contactsBulk.js` and `bulkReadJobs.js` models following the new schema structure.

#### Features
- Provider/service aware models
- JSON field support for raw data
- Comprehensive indexing strategy
- Foreign key relationships maintained

## Migration Path

### Database Migrations (Files 014-026)

1. **014-022**: Core module tables (contacts, leads, accounts, deals, tasks, vendors, invoices, sales_orders, purchase_orders)
2. **023**: New bulk_read_jobs table
3. **024-026**: Additional provider tables (employees, products, customers)

### Backward Compatibility

The refactoring maintains API compatibility for existing Zoho integrations while extending support for new providers.

## Provider-Specific Implementation Details

### Zoho Integration
- **CRM Service**: contacts, leads, accounts, deals, tasks, vendors
- **Books Service**: invoices, sales_orders, purchase_orders, customers, vendors  
- **Inventory Service**: products, customers, vendors, sales_orders, purchase_orders

### Salesforce Integration
- **CRM Service**: contacts, leads, accounts, deals, tasks
- Uses SOQL query format for bulk operations
- Bearer token authentication

### HubSpot Integration  
- **CRM Service**: contacts, leads, accounts, deals
- REST API with properties-based queries
- Bearer token authentication

### HR Providers (Keka, BambooHR)
- **HR Service**: employees
- Employee data with comprehensive fields (hire_date, department, salary, etc.)

### Accounting Providers (Intuit, QuickBooks)
- **Accounting Service**: customers, vendors, invoices, products
- Financial data with proper decimal handling

## Benefits of Refactoring

1. **Scalability**: Easy to add new providers and services
2. **Maintainability**: Single codebase handles all providers
3. **Flexibility**: Optional fields accommodate varying data structures  
4. **Performance**: Optimized indexing for multi-provider queries
5. **Extensibility**: JSON raw_data field preserves original data
6. **Consistency**: Unified API across all providers

## Usage Examples

### Initiating a Bulk Read Job

```javascript
// Zoho CRM Contacts
POST /api/v1/bulk-read/initiate
{
  "provider": "zoho",
  "service": "crm", 
  "module": "contacts",
  "fields": ["first_name", "last_name", "email", "phone"]
}

// Salesforce CRM Leads  
POST /api/v1/bulk-read/initiate
{
  "provider": "salesforce",
  "service": "crm",
  "module": "leads", 
  "fields": ["FirstName", "LastName", "Company", "Email"]
}

// Keka HR Employees
POST /api/v1/bulk-read/initiate  
{
  "provider": "keka",
  "service": "hr",
  "module": "employees",
  "fields": ["employee_id", "first_name", "last_name", "department"]
}
```

### Retrieving Data

```javascript
// Get Zoho CRM contacts
GET /api/v1/bulk-read/data/zoho/crm/contacts

// Get Salesforce leads  
GET /api/v1/bulk-read/data/salesforce/crm/leads

// Get employee data from Keka
GET /api/v1/bulk-read/data/keka/hr/employees
```

### Discovery Endpoints

```javascript
// Get all available providers
GET /api/v1/bulk-read/providers

// Get modules for Zoho CRM
GET /api/v1/bulk-read/modules/zoho/crm

// Get modules for Salesforce CRM  
GET /api/v1/bulk-read/modules/salesforce/crm
```

## Next Steps

1. **Run Database Migrations**: Execute migrations 014-026 to create new schema
2. **Update Webhook Handlers**: Modify webhook processors to use new repository methods
3. **Test Provider Integrations**: Validate each provider's bulk read functionality  
4. **Update Documentation**: Update API documentation with new endpoints
5. **Monitor Performance**: Ensure new indexing strategy performs well with real data

## Files Modified/Created

### New Migrations
- `src/db/migrations/014_create_contacts_bulk.sql`
- `src/db/migrations/015_create_leads_bulk.sql`
- `src/db/migrations/016_create_accounts_bulk.sql`
- `src/db/migrations/017_create_deals_bulk.sql`
- `src/db/migrations/018_create_tasks_bulk.sql`
- `src/db/migrations/019_create_vendors_bulk.sql`
- `src/db/migrations/020_create_invoices_bulk.sql`
- `src/db/migrations/021_create_sales_orders_bulk.sql`
- `src/db/migrations/022_create_purchase_orders_bulk.sql`
- `src/db/migrations/023_create_bulk_read_jobs.sql`
- `src/db/migrations/024_create_employees_bulk.sql`
- `src/db/migrations/025_create_products_bulk.sql`
- `src/db/migrations/026_create_customers_bulk.sql`

### Updated Files
- `src/api/routes/bulkReadRoutes.js` - Updated for multi-provider support
- `src/services/bulkReadService.js` - Complete refactoring for provider-agnostic operations

### New Files  
- `src/data/repositories/bulkReadRepository.js` - Unified repository for all providers
- `src/data/models/contactsBulk.js` - Example model for new schema
- `src/data/models/bulkReadJobs.js` - New job tracking model

This refactoring provides a solid foundation for supporting multiple service providers while maintaining clean, maintainable code architecture.