#!/bin/bash

# Test Sync Filters Integration
# This script verifies the complete flow from frontend to backend

echo "================================================"
echo "Testing Sync Filters Integration"
echo "================================================"
echo ""

# 1. Verify hook exists
echo "1. Checking if time_clock_aggregation hook exists..."
docker exec -i postgres psql -U taf -d devdb -c "
SELECT name, trigger_table, trigger_operations, 
       config->>'require_date_filter' as requires_dates,
       hook_type, is_active
FROM sync_hooks 
WHERE name = 'time_clock_aggregation';
" || exit 1
echo ""

# 2. Verify Payroll module has syncFilters enabled
echo "2. Checking if Payroll module has syncFilters enabled..."
docker exec -i postgres psql -U taf -d devdb -c "
SELECT 
    name,
    config->'body'->0->'tabs'->0->'elements'->0->'options'->>'syncFilters' as sync_enabled,
    config->'body'->0->'tabs'->0->'elements'->0->'options'->>'tableName' as table_name,
    config->'body'->0->'tabs'->0->'elements'->0->'options'->>'listenColumn' as listen_column
FROM modules 
WHERE name = 'Payroll';
" || exit 1
echo ""

# 3. Check if time_clocks data exists
echo "3. Checking if time_clocks data exists..."
docker exec -i postgres psql -U taf -d devdb -c "
SELECT 
    COUNT(*) as total_clocks,
    MIN(clocked_at::date) as earliest_clock,
    MAX(clocked_at::date) as latest_clock,
    COUNT(DISTINCT user_id) as unique_users
FROM time_clocks 
WHERE NOT isdeleted;
" || exit 1
echo ""

# 4. Check current aggregates
echo "4. Checking current aggregates (before test)..."
docker exec -i postgres psql -U taf -d devdb -c "
SELECT 
    COUNT(*) as total_aggregates,
    MIN(start_date) as earliest_period,
    MAX(end_date) as latest_period
FROM time_clock_aggregates;
" || exit 1
echo ""

# 5. Simulate backend sync with filters (via API endpoint)
echo "5. To test the complete flow:"
echo ""
echo "   A. Manual Browser Test:"
echo "      1. Open application in browser"
echo "      2. Open Developer Console (F12)"
echo "      3. Run this command:"
echo ""
echo "         const mgr = await (await import('services/syncservice')).getManager();"
echo "         await mgr.syncTableWithFilters('time_clock_aggregates', {"
echo "           start_date: '2025-05-01',"
echo "           end_date: '2025-08-31'"
echo "         });"
echo ""
echo "   B. UI Test:"
echo "      1. Open Payroll module"
echo "      2. Change date range to May 1 - Aug 31, 2025"
echo "      3. Watch console logs for sync messages"
echo "      4. Verify table shows calculated hours"
echo ""

# 6. Check hook execution logs
echo "6. Checking recent hook execution logs..."
docker exec -i postgres psql -U taf -d devdb -c "
SELECT 
    (SELECT name FROM sync_hooks WHERE hook_id = sync_hook_logs.hook_id) as hook_name,
    trigger_table,
    trigger_operation,
    executed_at,
    metadata->>'total_processed' as records_processed,
    metadata->>'execution_time' as exec_time_ms,
    CASE WHEN error_message IS NULL THEN '✓ Success' ELSE '✗ Error' END as status
FROM sync_hook_logs 
WHERE hook_id = (SELECT hook_id FROM sync_hooks WHERE name = 'time_clock_aggregation')
ORDER BY executed_at DESC 
LIMIT 5;
" 2>/dev/null
echo ""

echo "================================================"
echo "Setup Complete!"
echo "================================================"
echo ""
echo "The sync filters integration is ready to test."
echo "Follow the manual test steps above to verify the complete flow."
echo ""