#!/bin/bash
# Test the Sync Hooks System

echo "=========================================="
echo "Sync Hooks System - End-to-End Test"
echo "=========================================="
echo ""

# Apply the migration
echo "1. Applying sync hooks migration..."
docker exec -i postgres psql -U taf -d devdb < sql/migrations/20251008_sync_hooks_system.sql
if [ $? -eq 0 ]; then
    echo "✓ Migration applied successfully"
else
    echo "✗ Migration failed"
    exit 1
fi
echo ""

# Verify tables were created
echo "2. Verifying sync_hooks and sync_hook_logs tables..."
docker exec postgres psql -U taf -d devdb -c "\d sync_hooks" > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "✓ sync_hooks table exists"
else
    echo "✗ sync_hooks table not found"
    exit 1
fi

docker exec postgres psql -U taf -d devdb -c "\d sync_hook_logs" > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "✓ sync_hook_logs table exists"
else
    echo "✗ sync_hook_logs table not found"
    exit 1
fi
echo ""

# Check hook configuration
echo "3. Checking time_clock_aggregation hook configuration..."
HOOK_COUNT=$(docker exec postgres psql -U taf -d devdb -t -c "SELECT COUNT(*) FROM sync_hooks WHERE name = 'time_clock_aggregation' AND is_active = true;")
if [ "$HOOK_COUNT" -gt 0 ]; then
    echo "✓ Hook is configured and active"
    docker exec postgres psql -U taf -d devdb -c "SELECT name, trigger_table, hook_type, priority FROM sync_hooks WHERE name = 'time_clock_aggregation';"
else
    echo "✗ Hook not found or inactive"
    exit 1
fi
echo ""

# Test hook execution via sync API
echo "4. Testing hook execution via sync API..."
echo "   Pulling time_clock_aggregates with date filter..."
RESPONSE=$(curl -s 'http://localhost:8080/api/sync/pull?tables[time_clock_aggregates]=&start_date=2024-01-01&end_date=2024-12-31' \
    -b 'PHPSESSID=d39e7c4fef5dc152c3f222a2ce30da7a')

echo "$RESPONSE" | jq . > /tmp/hook_test_response.json

# Check if hooks were executed
HOOKS_EXECUTED=$(echo "$RESPONSE" | jq '._hooks | length' 2>/dev/null)
if [ "$HOOKS_EXECUTED" -gt 0 ]; then
    echo "✓ Hooks executed successfully"
    echo "   Hook results:"
    echo "$RESPONSE" | jq '._hooks'
else
    echo "⚠ No hooks executed (may be expected if no date filter provided)"
    echo "   Full response saved to /tmp/hook_test_response.json"
fi
echo ""

# Check hook logs
echo "5. Checking sync_hook_logs for execution history..."
LOG_COUNT=$(docker exec postgres psql -U taf -d devdb -t -c "SELECT COUNT(*) FROM sync_hook_logs;")
if [ "$LOG_COUNT" -gt 0 ]; then
    echo "✓ Found $LOG_COUNT log entries"
    echo "   Recent hook executions:"
    docker exec postgres psql -U taf -d devdb -c "SELECT created_at, trigger_table, operation, status, execution_time_ms FROM sync_hook_logs ORDER BY created_at DESC LIMIT 5;"
else
    echo "⚠ No log entries found"
fi
echo ""

# Check aggregates were generated
echo "6. Checking if aggregates were generated..."
AGG_COUNT=$(docker exec postgres psql -U taf -d devdb -t -c "SELECT COUNT(*) FROM time_clock_aggregates;")
if [ "$AGG_COUNT" -gt 0 ]; then
    echo "✓ Found $AGG_COUNT aggregate records"
    echo "   Sample aggregates:"
    docker exec postgres psql -U taf -d devdb -c "SELECT user_id, pay_period_start, pay_period_end, total_net_hours, total_overtime_hours FROM time_clock_aggregates LIMIT 3;"
else
    echo "⚠ No aggregates found (may be expected if no time clock data exists)"
fi
echo ""

echo "=========================================="
echo "Test Summary"
echo "=========================================="
echo "✓ Migration applied"
echo "✓ Tables created"
echo "✓ Hook configured"
if [ "$HOOKS_EXECUTED" -gt 0 ]; then
    echo "✓ Hooks executed via API"
else
    echo "⚠ Hooks not executed (check date filters and time clock data)"
fi
echo ""
echo "Next Steps:"
echo "1. Add time clock records if none exist"
echo "2. Test with different date ranges"
echo "3. Monitor sync_hook_logs table for execution history"
echo "4. Add new hooks following the documentation in SyncHookService.php"
echo ""