# Trigger and Functions Summary

## Overview
This document explains the trigger and function chain that updates store metrics when a task status changes to 'Fixed'.

## Function Chain

```
Task Status Update → Trigger → update_store_metrics() → process_store_metrics_set_based() → store_user_link UPDATE
                                                      ↓
                                              update_product_store_data()
                                                      ↓
                                              store_visibility UPSERT
```

## Files Created

### 1. `process_store_metrics_set_based_fixed.sql`
**Purpose**: Set-based function that processes multiple stores/weeks efficiently.

**Parameters**:
- `p_store_codes_text TEXT` - Comma-separated store codes
- `p_week_start_dates_text TEXT` - Comma-separated week start dates (paired with store codes)
- `p_customer_id INTEGER` - Customer ID
- `p_per_store_timeout TEXT DEFAULT '60s'` - Timeout per store operation

**Returns**: JSONB array of store metrics

**Key Features**:
- Processes all stores in a single transaction
- Gets ALL customer categories (not just store-specific)
- Updates `store_user_link` table with metrics
- Proper error handling and timeout management

### 2. `update_store_metrics.sql`
**Purpose**: Wrapper function for single store/week processing. Called by the trigger.

**Parameters**:
- `p_customer_id INTEGER` - Customer ID from task
- `p_store_code TEXT` - Store code from task
- `p_week_start_date TEXT` - Week start date from task
- `p_per_row_timeout TEXT DEFAULT '10s'` - Timeout for single row processing
- `p_total_budget INTERVAL DEFAULT '30s'` - Total budget (for consistency)

**Returns**: JSONB result from underlying function

**Key Features**:
- Wraps the set-based function for single store processing
- Formats single store/week as comma-separated strings
- Handles errors gracefully

### 3. `task_status_fixed_trigger.sql`
**Purpose**: Trigger function that fires when a task status changes to 'Fixed'.

**Trigger Logic**:
1. **Guard Check**: Only processes when status changes to 'fixed'
2. **Update Store Metrics**: Calls `update_store_metrics()` with task data
3. **Update Product Store Data**: Calls `update_product_store_data()` (existing function)
4. **Fetch Availability**: Gets updated availability from `store_user_link`
5. **Update Visibility**: Upserts availability_score and task_id into `store_visibility`

**Key Features**:
- Per-row timeout protection (10s)
- Total budget check (30s)
- Comprehensive error handling with RAISE NOTICE for debugging
- Updates `store_visibility` table with availability score

## Database Tables Updated

### `store_user_link`
Updated by `process_store_metrics_set_based()`:
- `soh_no_sales`
- `oos`
- `negative_stock`
- `no_sales_100_days`
- `seven_day_cover`
- `total_soh`
- `availability`
- `fixed_count`
- `not_fixed_count`
- `week_start_date`
- `category_data` (JSONB)
- `updated_at`

### `store_visibility`
Updated by trigger:
- `availability_score` (from `store_user_link.availability`)
- `task_id` (the task that triggered the update)
- `updated_at`

## Installation

To install these functions and trigger:

```sql
-- 1. Create the set-based function
\i database/functions/process_store_metrics_set_based_fixed.sql

-- 2. Create the wrapper function
\i database/functions/update_store_metrics.sql

-- 3. Create the trigger function
\i database/functions/task_status_fixed_trigger.sql

-- 4. Create the trigger (uncomment in task_status_fixed_trigger.sql or run manually)
DROP TRIGGER IF EXISTS trg_task_fixed_update_visibility ON task;
CREATE TRIGGER trg_task_fixed_update_visibility
  AFTER UPDATE ON task
  FOR EACH ROW
  WHEN (
    lower(NEW.status) = 'fixed'
    AND lower(COALESCE(OLD.status, '')) IS DISTINCT FROM lower(NEW.status)
  )
  EXECUTE FUNCTION fn_task_fixed_update_visibility();
```

## Testing

### Test Single Store Update
```sql
-- Update a task status to 'Fixed'
UPDATE task
SET status = 'Fixed'
WHERE id = <task_id>;

-- Check store_user_link was updated
SELECT store_code, availability, soh_no_sales, oos, category_data
FROM store_user_link
WHERE store_code = '<store_code>' AND customer_id = <customer_id>;

-- Check store_visibility was updated
SELECT * FROM store_visibility
WHERE store_code = '<store_code>' AND customer_id = <customer_id>;
```

### Test Set-Based Function Directly
```sql
SELECT process_store_metrics_set_based(
    'store1,store2,store3',
    '2025-01-01,2025-01-01,2025-01-01',
    123,
    '60s'
);
```

## Error Handling

All functions include comprehensive error handling:

1. **Input Validation**: Checks for empty inputs and mismatched array lengths
2. **Timeout Handling**: Catches `query_canceled` exceptions
3. **General Exceptions**: Catches and returns error details in JSONB format
4. **Trigger Errors**: Logs errors with RAISE NOTICE but doesn't fail the transaction

## Performance Considerations

### Set-Based Function Advantages:
- Single transaction for all stores
- Bulk UPDATE operations
- Better query optimization by PostgreSQL
- Less overhead than row-by-row processing

### Trigger Considerations:
- Fires for each task status update
- Uses short timeouts (10s per row, 30s total budget)
- Continues processing even if one step fails (with logging)

## Dependencies

- `update_product_store_data()` - Must exist (referenced in trigger)
- `store_user_link` table - Must exist with proper columns
- `store_visibility` table - Must exist with unique constraint on (customer_id, store_code, week_start_date)
- `task` table - Must exist (trigger source)
- `sjreport` table - Used for SOH calculations
- `task` table - Used for metrics calculations

## Notes

1. The trigger uses `WHEN` clause to filter, but also includes a guard check for safety
2. The set-based function processes ALL customer categories, not just store-specific ones (matching loop-based behavior)
3. The UPDATE statement filters by both `store_code` AND `customer_id` to ensure correct row updates
4. The trigger logs all operations with RAISE NOTICE for debugging
5. Timeouts are reset to '0' (unlimited) after operations complete