# Consumption Data Recalculation

## Overview

This document explains how to recalculate consumption data for the ELMIS system using the ConsumptionSeeder.

## Background

The system has been updated to improve how consumption data is managed:

- **Historical Data (2022-2024)**: Consumption data is recalculated from distribution records using the seeder
- **2025 Onwards**: Consumption should be calculated directly by the application from distribution records
- **No Gaps Allowed**: The validation requires consumption records for ALL months from first distribution onwards

## What the Seeder Does

The `ConsumptionSeeder` performs the following operations:

1. **Clears existing data**: Removes all consumption records from 2022-2024
2. **Fetches distributions**: Retrieves `province_outs` records from 2022-01-01 to 2024-12-31
3. **Groups by entity**: Groups distributions by:
   - IP ID
   - Province ID
   - Facility ID
   - Item ID
   - Item Category ID
   - Project ID
   - Provider ID
   - User ID
4. **Fills all months**: For each entity, creates consumption records for **every month** from first distribution to end of 2024
5. **Calculates consumption**: Sums `out_quantity` for each month (0 if no distributions)
6. **Creates records**: Inserts consumption records with **no gaps** to satisfy validation

## Data Scope

### 2022-2024 (Historical)
- Handled by this seeder
- Monthly consumption = SUM of distributions for that month
- Creates records for **all months** from first distribution to December 2024
- Fills gaps with 0 consumption to prevent validation errors

### 2025+ (Current/Future)
- Handled by the application directly
- Users enter consumption data through the UI
- Application can calculate consumption from distributions in real-time
- Not processed by this seeder

## Formula

```
Monthly Consumption = SUM(province_outs.out_quantity) 
WHERE same (ip_id, province_id, facility_id, item_id, project_id, provided_by)
AND YEAR(date) = year
AND MONTH(date) = month
```

## Running the Seeder

### Option 1: Run only ConsumptionSeeder

```bash
cd backend
php artisan db:seed --class=ConsumptionSeeder
```

### Option 2: Run all seeders

```bash
cd backend
php artisan db:seed
```

**Note**: Option 2 will run all seeders defined in `DatabaseSeeder.php`, including RoleAndPermissionSeeder and ConsumptionSeeder.

## Expected Output

When running the seeder, you should see:

```
Clearing existing consumption data from 2022-2024...
Fetching distribution records from 2022-2024...
Found XXXX distribution records.
Processing YYYY unique entities.
Creating records from first distribution to: 2024-12 (end of 2024)
Will create ZZZZ consumption records (filling all months with no gaps).
[Progress bar showing completion]
✓ Successfully inserted ZZZZ consumption records (with no gaps).

Summary by year (2022-2024):
  2022: 150 records, Total consumption: 12500
  2023: 180 records, Total consumption: 15600
  2024: 200 records, Total consumption: 18900

✓ Consumption data recalculation complete (2022-2024)!
Formula: Monthly Consumption = SUM(province_outs.out_quantity) for same month

📌 Note: For 2025 onwards, consumption should be calculated directly by the application.
```

## Important Notes

### Data Safety
- ⚠️ **Warning**: The seeder deletes all consumption records from 2022-2024 before recalculating
- Only run this seeder when you're ready to recalculate historical data (2022-2024)
- Consider backing up your database before running the seeder
- **Scope**: This seeder only handles 2022-2024 data; 2025+ should be managed through the application

### Performance
- The seeder uses a progress bar to show real-time progress
- Processing time depends on the number of distribution records
- Large datasets may take several minutes to process

### Error Handling
- If any records fail to insert, the seeder will report the count
- Individual errors are logged to help with debugging
- The seeder continues processing even if some records fail

## How It Solves the Validation Issue

The validation error "you should fill this first 2023 April" occurs because:

1. **Validation Logic**: The system checks for consumption records from the first distribution onwards
2. **No Gaps Allowed**: If any month is missing, the validation fails
3. **Seeder Solution**: Creates records for **every single month** from first distribution to end of 2024
4. **Result**: Validation passes for historical data because all 2022-2024 months have records

Example timeline for historical data:
- First distribution: April 2022
- Seeder scope: April 2022 → December 2024
- Seeder creates: 33 consumption records (one per month from April 2022 to Dec 2024)
- Result: All historical months (2022-2024) have consumption records with no gaps

For 2025 data:
- Users enter consumption through the UI for each month
- Application can calculate consumption from distributions in real-time
- No seeder needed for 2025+

## Code Changes Made

### 1. ConsumptionSeeder.php
Located at: `database/seeders/ConsumptionSeeder.php`
- **Key Changes**:
  - Fetches distributions from 2022-01-01 to 2024-12-31
  - Creates records from first distribution to **end of 2024** (December 2024)
  - Fills in ALL months with no gaps (creates records even when consumption = 0)
  - Deletes consumption data for 2022-2024 only before recalculation
  - Shows year-by-year summary after completion

## Verification

After running the seeder, verify the results:

```sql
-- Check total consumption records by year
SELECT year, COUNT(*) as total_records, SUM(consume) as total_consumption
FROM consumptions
WHERE year >= 2022
GROUP BY year
ORDER BY year;

-- Check for gaps (should return 0 rows if no gaps)
-- This checks if there are any missing months for each entity
SELECT c1.ip_id, c1.province_id, c1.facility_id, c1.item_id, 
       c1.year, c1.month, 'Missing next month' as issue
FROM consumptions c1
LEFT JOIN consumptions c2 
  ON c1.ip_id = c2.ip_id 
  AND c1.province_id = c2.province_id
  AND c1.facility_id = c2.facility_id
  AND c1.item_id = c2.item_id
  AND c1.project_id = c2.project_id
  AND c1.provided_by = c2.provided_by
  AND c1.user_id = c2.user_id
  AND ((c1.month = 12 AND c2.month = 1 AND c2.year = c1.year + 1) 
       OR (c1.month < 12 AND c2.month = c1.month + 1 AND c2.year = c1.year))
WHERE c1.year >= 2022 AND c2.id IS NULL
  AND NOT (c1.year = YEAR(NOW()) AND c1.month = MONTH(NOW()) - 1);

-- Compare with distributions
SELECT YEAR(date) as year, COUNT(*) as total_distributions, SUM(out_quantity) as total_distributed
FROM province_outs
WHERE deleteStatus = 0 
AND date >= '2022-01-01'
GROUP BY YEAR(date)
ORDER BY year;
```

## Troubleshooting

### Seeder fails to run
- Ensure you're in the `backend` directory
- Check database connection in `.env` file
- Verify you have proper permissions to modify the database

### Missing records
- Check if distribution records exist for the expected period
- Verify `deleteStatus = 0` on province_outs records
- Review error messages for specific issues

### Incorrect calculations
- Verify the grouping logic matches your business requirements
- Check if all necessary fields are included in the grouping

## Support

For issues or questions, please contact the development team or review the code in:
- `database/seeders/ConsumptionSeeder.php`
- `app/Http/Controllers/Province/ProvinceConsumptionController.php`