#!/bin/bash
# Quick Investigation Script for Orphaned Payments
# Part of SPIKE-PAYMENT-FAILURE-PREVENTION-SYSTEM

set -e

echo "╔════════════════════════════════════════════════════════╗"
echo "║  ORPHANED PAYMENT INVESTIGATION                        ║"
echo "╚════════════════════════════════════════════════════════╝"
echo ""

# Colors
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
RED='\033[0;31m'
NC='\033[0m' # No Color

echo "This script will help investigate orphaned payment issues."
echo ""

# Check 1: Run orphaned payment detection
echo -e "${YELLOW}▶ 1. Checking for orphaned payments (last 30 days)...${NC}"
php artisan payments:detect-orphaned --hours=720
echo ""

# Check 2: Look for webhook failures in recent logs
echo -e "${YELLOW}▶ 2. Searching for recent webhook failures...${NC}"
if [ -f "storage/logs/laravel-$(date +%Y-%m-%d).log" ]; then
    echo "Today's webhook errors:"
    grep -i "webhook.*error\|webhook.*fail\|signature.*fail" storage/logs/laravel-$(date +%Y-%m-%d).log | tail -10 || echo "No webhook errors found"
else
    echo -e "${RED}No logs found for today${NC}"
fi
echo ""

# Check 3: Count payment flow events
echo -e "${YELLOW}▶ 3. Checking if payment_flow_logs table exists...${NC}"
php artisan tinker --execute="
try {
    \$count = \DB::table('payment_flow_logs')->count();
    echo \"payment_flow_logs table exists with \$count events\n\";
} catch (\Exception \$e) {
    echo \"payment_flow_logs table does NOT exist - needs to be created\n\";
}
"
echo ""

# Check 4: Check webhook_events table
echo -e "${YELLOW}▶ 4. Checking webhook_events table...${NC}"
php artisan tinker --execute="
try {
    \$count = \DB::table('webhook_events')->count();
    \$recent = \DB::table('webhook_events')->where('created_at', '>=', now()->subDays(7))->count();
    echo \"webhook_events table: \$count total, \$recent in last 7 days\n\";
} catch (\Exception \$e) {
    echo \"webhook_events table does NOT exist or has issues\n\";
}
"
echo ""

# Check 5: Recent Revolut payments with no order
echo -e "${YELLOW}▶ 5. Finding recent payments with revolut_order_id but no order...${NC}"
source scripts/_db-helper.sh
ORPHANED=$(db_query "
SELECT COUNT(DISTINCT revolut_order_id) 
FROM seat_reservations 
WHERE revolut_order_id IS NOT NULL 
  AND order_id IS NULL 
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
")
echo "Found $ORPHANED distinct orphaned Revolut payments in last 30 days"
echo ""

# Summary
echo "╔════════════════════════════════════════════════════════╗"
echo "║  INVESTIGATION SUMMARY                                 ║"
echo "╚════════════════════════════════════════════════════════╝"
echo ""
echo "Next steps:"
echo "  1. Review orphaned payments found above"
echo "  2. Read full spike: docs/spikes/SPIKE-PAYMENT-FAILURE-PREVENTION-SYSTEM.md"
echo "  3. Run orphaned payment detection with --auto-fix if safe"
echo "  4. Begin Phase 1 investigation (webhook audit)"
echo ""
echo -e "${GREEN}Investigation script complete!${NC}"