# Function Fix Summary: Set-Based Function Matching Loop-Based Behavior

## Overview
Fixed the set-based function to match the behavior of the loop-based function. The corrected function is saved as `process_store_metrics_set_based_fixed.sql`.

## Key Changes Made

### 1. Fixed Nested DECLARE Block Issue
**Problem**: The original function had a nested `DECLARE` block that tried to reference CTEs (`parsed_stores`, `parsed_weeks`) defined in a `WITH` clause. This is invalid SQL syntax.

**Solution**: Moved all variable declarations to the top-level `DECLARE` block and computed counts using a CTE that aggregates the results before selecting into variables.

```sql
-- Before (INVALID):
DECLARE
    v_input_count int;
BEGIN
    WITH parsed_stores AS (...), parsed_weeks AS (...)
    ...
    DECLARE  -- ❌ Can't reference CTEs here
        v_parsed_stores_count int;
    BEGIN
        SELECT count(*) INTO v_parsed_stores_count FROM parsed_stores; -- ❌ Error
    END;

-- After (VALID):
DECLARE
    v_input_count int;
    v_parsed_stores_count int;
    v_parsed_weeks_count int;
BEGIN
    WITH parsed_stores AS (...), parsed_weeks AS (...),
    counts AS (
      SELECT 
        (SELECT count(*) FROM parsed_stores) as stores_count,
        (SELECT count(*) FROM parsed_weeks) as weeks_count,
        (SELECT count(*) FROM paired) as paired_count
    )
    SELECT paired_count, stores_count, weeks_count
    INTO v_input_count, v_parsed_stores_count, v_parsed_weeks_count
    FROM counts;
```

### 2. Changed Category Handling to Match Loop-Based Function
**Problem**: The original set-based function only got categories for the specific store/week combination, while the loop-based function gets ALL customer categories (across all stores).

**Solution**: Created `all_customer_categories` CTE that unions all categories from task and sjreport for the entire customer, then uses `CROSS JOIN` to include all customer categories for each store.

```sql
-- Key change: Get ALL customer categories, not just store-specific
all_customer_categories AS (
  SELECT DISTINCT ON (norm_category)
    norm_category,
    cat AS category_name
  FROM (
    SELECT ... FROM task WHERE customer_id = p_customer_id
    UNION ALL
    SELECT ... FROM sjreport WHERE customer_id = p_customer_id
  ) u
  ORDER BY norm_category, created_at DESC
),

-- Then CROSS JOIN with latest_weeks to include all categories for each store
category_metrics AS (
  SELECT ...
  FROM latest_weeks lw
  CROSS JOIN all_customer_categories acc  -- ✅ All customer categories
  ...
)
```

### 3. Improved Input Parsing
**Problem**: The original function parsed inputs twice (once for validation, once for processing).

**Solution**: Created reusable CTEs (`input_pairs`, `input_weeks`, `paired_input`) that are used consistently throughout the function.

### 4. Maintained Set-Based Performance Benefits
- Single UPDATE statement instead of multiple updates in a loop
- Set-based operations are generally more efficient than row-by-row processing
- Proper use of CTEs for readability and optimization

## Behavior Matching

The fixed function now matches the loop-based function in:
- ✅ Getting ALL customer categories (not just store-specific)
- ✅ Proper input validation and error messages
- ✅ Latest week determination (task → sjreport → provided week)
- ✅ SOH deduplication logic
- ✅ Category image selection (task first, then sjreport, then default)
- ✅ All metric calculations (soh_no_sales, oos, negative_stock, etc.)
- ✅ Availability calculations
- ✅ JSON structure in return value

## Performance Considerations

The set-based approach should be more efficient than the loop-based approach because:
1. **Single transaction**: All stores processed in one transaction
2. **Bulk operations**: Single UPDATE instead of multiple updates
3. **Query optimization**: PostgreSQL can optimize the entire query plan
4. **Less overhead**: No loop iteration overhead

However, the set-based approach processes all stores at once, so if one store times out, the entire operation fails. The loop-based approach can continue processing other stores if one times out.

## Usage

To use the fixed function:

```sql
SELECT process_store_metrics_set_based(
    'store1,store2,store3',  -- p_store_codes_text
    '2025-01-01,2025-01-01,2025-01-01',  -- p_week_start_dates_text
    123,  -- p_customer_id
    '60s'  -- p_per_store_timeout (optional, default '60s')
);
```

## Testing Recommendations

1. Test with single store
2. Test with multiple stores
3. Test with mismatched array lengths (should return error)
4. Test with empty inputs (should return error)
5. Test timeout scenarios
6. Verify category data matches loop-based function output
7. Verify store_user_link updates match loop-based function