-- Diagnostic script to check trigger setup and test

-- 1. Check if trigger function exists
SELECT 
    proname AS function_name,
    pg_get_functiondef(oid) AS function_definition
FROM pg_proc
WHERE proname = 'fn_task_fixed_update_visibility';

-- 2. Check if trigger exists on task table
SELECT 
    tgname AS trigger_name,
    tgrelid::regclass AS table_name,
    tgenabled AS enabled,
    tgtype::text AS trigger_type,
    pg_get_triggerdef(oid) AS trigger_definition
FROM pg_trigger
WHERE tgname = 'trg_task_fixed_update_visibility'
   OR (tgrelid = 'task'::regclass AND tgname LIKE '%fixed%');

-- 3. Check task table structure (status column)
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'task'
  AND column_name = 'status';

-- 4. Check sample task statuses to see actual values
SELECT DISTINCT status, count(*) 
FROM task 
WHERE status IS NOT NULL
GROUP BY status
ORDER BY count(*) DESC
LIMIT 10;

-- 5. Test trigger manually (update a test task)
-- Uncomment and modify task_id to test:
/*
UPDATE task
SET status = 'Fixed'
WHERE id = <your_task_id>
RETURNING id, status;

-- Check if RAISE NOTICE messages appear in logs
-- In psql, you may need to: SET client_min_messages TO NOTICE;
*/

-- 6. Check if update_store_metrics function exists
SELECT 
    proname AS function_name,
    pg_get_function_arguments(oid) AS arguments
FROM pg_proc
WHERE proname = 'update_store_metrics';

