# Inventory Processing Cron Job

## Overview

The inventory processing system uses a two-phase approach:

1. **Transaction Logging** - When a POS sale occurs, inventory changes are logged to `inventory_txn` table with `processed_at = NULL`
2. **Background Processing** - A cron job periodically processes these transactions and updates the actual `inventory` table

## Setup Instructions

### 1. Make the script executable (optional but recommended)

```bash
chmod +x /var/www/html/TAF/api/cron/process_inventory.php
```

### 2. Add to crontab

Open crontab for editing:

```bash
crontab -e
```

Add one of the following entries based on your requirements:

#### Process every minute (high-frequency sales)
```bash
* * * * * php /var/www/html/TAF/api/cron/process_inventory.php >> /var/www/html/TAF/logs/inventory_cron.log 2>&1
```

#### Process every 5 minutes (recommended for most cases)
```bash
*/5 * * * * php /var/www/html/TAF/api/cron/process_inventory.php >> /var/www/html/TAF/logs/inventory_cron.log 2>&1
```

#### Process every 15 minutes (low-frequency sales)
```bash
*/15 * * * * php /var/www/html/TAF/api/cron/process_inventory.php >> /var/www/html/TAF/logs/inventory_cron.log 2>&1
```

### 3. Create logs directory (if it doesn't exist)

```bash
mkdir -p /var/www/html/TAF/logs
chmod 755 /var/www/html/TAF/logs
```

### 4. Test the cron job manually

```bash
php /var/www/html/TAF/api/cron/process_inventory.php
```

Expected output:
```
[2025-10-01 14:30:00] Starting inventory transaction processing...
[2025-10-01 14:30:01] Inventory transaction processing completed successfully.
```

## How It Works

### Flow Diagram

```
POS Sale
   ↓
Save to inventory_txn (processed_at = NULL)
   ↓
Cron job runs every X minutes
   ↓
Fetch unprocessed transactions (WHERE processed_at IS NULL)
   ↓
Lock rows (prevent concurrent processing)
   ↓
Aggregate changes by product + branch
   ↓
Update inventory.current_qty
   ↓
Mark transactions as processed (processed_at = NOW)
```

### Database Tables

#### `inventory_txn` (Transaction Log)
- Stores all inventory changes
- `change` = positive for additions, negative for sales
- `processed_at` = NULL until processed by cron job

#### `inventory` (Current Stock)
- Stores current inventory levels
- `current_qty` = actual quantity on hand
- Updated by cron job based on `inventory_txn`

## Monitoring

### Check cron logs

```bash
tail -f /var/www/html/TAF/logs/inventory_cron.log
```

### Check unprocessed transactions

```sql
SELECT COUNT(*) as unprocessed_count 
FROM inventory_txn 
WHERE processed_at IS NULL 
AND isDeleted = false;
```

### Check recent inventory updates

```sql
SELECT p.name, i.current_qty, i.updatedAt
FROM inventory i
JOIN product_batch_number pbn ON i.product_batch_number_id = pbn.product_batch_number_id
JOIN products p ON pbn.product_id = p.product_id
WHERE i.isDeleted = false
ORDER BY i.updatedAt DESC
LIMIT 20;
```

## Troubleshooting

### Cron job not running

1. Check if cron service is running:
   ```bash
   systemctl status cron
   ```

2. Check crontab is configured:
   ```bash
   crontab -l
   ```

3. Check PHP CLI path:
   ```bash
   which php
   ```

### Transactions not processing

1. Check for errors in log file:
   ```bash
   tail -n 50 /var/www/html/TAF/logs/inventory_cron.log
   ```

2. Run manually with verbose output:
   ```bash
   php /var/www/html/TAF/api/cron/process_inventory.php
   ```

3. Check database connectivity and permissions

### Performance Issues

If processing is slow:

1. **Add database index** on `inventory_txn.processed_at`:
   ```sql
   CREATE INDEX idx_inventory_txn_processed ON inventory_txn(processed_at) 
   WHERE processed_at IS NULL AND isDeleted = false;
   ```

2. **Increase cron frequency** to prevent large backlogs

3. **Monitor transaction volume** during peak hours

## Best Practices

1. **Start with 5-minute intervals** and adjust based on transaction volume
2. **Monitor the logs** regularly for the first few days
3. **Set up alerts** if unprocessed transaction count grows too large
4. **Back up your database** before making configuration changes
5. **Test in development** environment first

## Production Considerations

For high-volume POS systems:

- Consider running every 1-2 minutes
- Set up monitoring/alerting for failed cron jobs
- Implement queue-based processing (Redis/RabbitMQ) for even better performance
- Use database replication to offload processing from primary database