# Trigger Troubleshooting Guide

## Problem: Trigger Not Firing When Task Status Changes to 'Fixed'

### Step 1: Verify Trigger Exists

Run this query to check if the trigger is created:

```sql
SELECT 
    tgname AS trigger_name,
    tgrelid::regclass AS table_name,
    CASE tgenabled 
        WHEN 'O' THEN 'enabled'
        WHEN 'D' THEN 'disabled'
        ELSE 'unknown'
    END AS status,
    pg_get_triggerdef(oid) AS trigger_definition
FROM pg_trigger
WHERE tgname = 'trg_task_fixed_update_visibility';
```

**If no rows returned**: The trigger doesn't exist. Run `create_trigger.sql` to create it.

**If status is 'disabled'**: Enable it with:
```sql
ALTER TABLE task ENABLE TRIGGER trg_task_fixed_update_visibility;
```

### Step 2: Verify Function Exists

```sql
SELECT proname, pg_get_functiondef(oid) 
FROM pg_proc 
WHERE proname = 'fn_task_fixed_update_visibility';
```

**If no rows returned**: The function doesn't exist. Run `task_status_fixed_trigger.sql` to create it.

### Step 3: Check Status Values

The trigger checks for status = 'fixed' (lowercase). Check what actual values exist:

```sql
SELECT DISTINCT status, count(*) 
FROM task 
WHERE status IS NOT NULL
GROUP BY status
ORDER BY count(*) DESC;
```

**If statuses are 'Fixed' (capital F)**: The trigger should still work because it uses `lower()` function, but verify the WHEN clause matches.

### Step 4: Enable Notice Messages

RAISE NOTICE messages won't show unless notice level is enabled:

```sql
SET client_min_messages TO NOTICE;
```

Or in psql:
```sql
\set VERBOSITY verbose
```

### Step 5: Test Trigger Manually

1. Find a test task:
```sql
SELECT id, status, store_code, customer_id, week_start_date
FROM task
WHERE status IS NOT NULL 
  AND lower(status) != 'fixed'
LIMIT 1;
```

2. Update it:
```sql
UPDATE task
SET status = 'Fixed'
WHERE id = <task_id>;
```

3. Check logs for RAISE NOTICE messages. You should see:
   - "TRIGGER FIRED: task id=..."
   - Either "Guard prevented execution" or "Processing task id=..."

### Step 6: Check Dependencies

Verify all required functions exist:

```sql
SELECT proname 
FROM pg_proc 
WHERE proname IN (
    'update_store_metrics',
    'process_store_metrics_set_based',
    'update_product_store_data'
);
```

**If any are missing**: Create them using the respective SQL files.

### Step 7: Check Table Permissions

Ensure the trigger function has necessary permissions:

```sql
-- Check if function owner can access tables
SELECT 
    p.proname,
    p.proowner::regrole AS owner,
    has_table_privilege(p.proowner, 'task', 'UPDATE') AS can_update_task,
    has_table_privilege(p.proowner, 'store_user_link', 'UPDATE') AS can_update_store_user_link,
    has_table_privilege(p.proowner, 'store_visibility', 'INSERT') AS can_insert_store_visibility
FROM pg_proc p
WHERE p.proname = 'fn_task_fixed_update_visibility';
```

### Step 8: Check for Errors in Logs

Check PostgreSQL logs for any errors when updating a task:

```sql
-- In PostgreSQL logs, look for:
-- ERROR, WARNING, or NOTICE messages related to task_status_fixed_trigger
```

### Common Issues and Solutions

#### Issue 1: Trigger Not Created
**Symptom**: No trigger found in pg_trigger
**Solution**: Run `create_trigger.sql`

#### Issue 2: Trigger Disabled
**Symptom**: Trigger exists but status is 'disabled'
**Solution**: 
```sql
ALTER TABLE task ENABLE TRIGGER trg_task_fixed_update_visibility;
```

#### Issue 3: Function Missing
**Symptom**: Trigger exists but function doesn't
**Solution**: Run `task_status_fixed_trigger.sql` to create the function

#### Issue 4: Status Case Mismatch
**Symptom**: Status is 'Fixed' but trigger checks for 'fixed'
**Solution**: The trigger uses `lower()` so this should work, but verify the WHEN clause:
```sql
-- Check trigger definition
SELECT pg_get_triggerdef(oid) 
FROM pg_trigger 
WHERE tgname = 'trg_task_fixed_update_visibility';
```

#### Issue 5: Guard Preventing Execution
**Symptom**: Trigger fires but guard prevents execution
**Solution**: Check RAISE NOTICE messages to see why guard is preventing execution. Common reasons:
- Status didn't actually change
- Status is not 'fixed'
- Not an UPDATE operation

#### Issue 6: Missing Dependencies
**Symptom**: Trigger fires but fails with "function does not exist"
**Solution**: Create missing functions:
- `update_store_metrics` → `update_store_metrics.sql`
- `process_store_metrics_set_based` → `process_store_metrics_set_based_fixed.sql`
- `update_product_store_data` → (should already exist)

#### Issue 7: Permission Issues
**Symptom**: Trigger fires but fails with permission error
**Solution**: Grant necessary permissions:
```sql
GRANT UPDATE ON task TO <function_owner>;
GRANT UPDATE ON store_user_link TO <function_owner>;
GRANT INSERT, UPDATE ON store_visibility TO <function_owner>;
```

### Quick Diagnostic Script

Run `diagnose_trigger.sql` to check all of the above automatically.

### Manual Trigger Creation

If the trigger still doesn't work, create it manually:

```sql
-- Drop existing trigger
DROP TRIGGER IF EXISTS trg_task_fixed_update_visibility ON task;

-- Create trigger function first (if not exists)
\i database/functions/task_status_fixed_trigger.sql

-- Create the trigger
CREATE TRIGGER trg_task_fixed_update_visibility
  AFTER UPDATE ON task
  FOR EACH ROW
  WHEN (
    lower(COALESCE(NEW.status, '')) = 'fixed'
    AND lower(COALESCE(OLD.status, '')) IS DISTINCT FROM lower(COALESCE(NEW.status, ''))
  )
  EXECUTE FUNCTION fn_task_fixed_update_visibility();

-- Verify
SELECT tgname, tgrelid::regclass, tgenabled 
FROM pg_trigger 
WHERE tgname = 'trg_task_fixed_update_visibility';
```

### Testing Checklist

- [ ] Trigger exists in pg_trigger
- [ ] Trigger is enabled (status = 'O')
- [ ] Function exists in pg_proc
- [ ] Notice messages are enabled
- [ ] Test UPDATE shows RAISE NOTICE messages
- [ ] All dependency functions exist
- [ ] Permissions are correct
- [ ] Status values match expected format