#!/bin/bash
#==========================================================================
# CUSTOMER TRACE FINDER
#==========================================================================
# Purpose: Find ANY trace of a customer across all tables and logs
# Usage: ./scripts/find-customer-trace.sh <email_or_identifier>
#==========================================================================

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(cd "$SCRIPT_DIR/.." && pwd)"
cd "$PROJECT_ROOT"

# Load database helper
source "$SCRIPT_DIR/_db-helper.sh"

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

if [ $# -eq 0 ]; then
    echo "Usage: $0 <email_or_identifier>"
    echo "Example: $0 customer@example.com"
    exit 1
fi

SEARCH_TERM="$1"

echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}  Customer Trace Finder${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""
echo -e "Searching for: ${CYAN}${SEARCH_TERM}${NC}"
echo ""

FOUND_SOMETHING=false

#==========================================================================
# SEARCH 1: Orders Table
#==========================================================================
echo -e "${BLUE}[1/10] Searching orders...${NC}"

ORDERS=$(db_query "
SELECT id, customer_name, customer_email, total_amount, currency, status, created_at
FROM orders
WHERE customer_email LIKE '%${SEARCH_TERM}%'
OR customer_phone LIKE '%${SEARCH_TERM}%'
OR customer_name LIKE '%${SEARCH_TERM}%'
OR id = '${SEARCH_TERM}'
" 2>/dev/null || echo "")

if [ -n "$ORDERS" ] && [ "$ORDERS" != "id" ]; then
    echo -e "${GREEN}✅ Found in orders table:${NC}"
    db_table "
    SELECT id, customer_name, customer_email, total_amount, currency, status, created_at
    FROM orders
    WHERE customer_email LIKE '%${SEARCH_TERM}%'
    OR customer_phone LIKE '%${SEARCH_TERM}%'
    OR customer_name LIKE '%${SEARCH_TERM}%'
    OR id = '${SEARCH_TERM}'
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in orders${NC}"
fi
echo ""

#==========================================================================
# SEARCH 2: Customers Table
#==========================================================================
echo -e "${BLUE}[2/10] Searching customers...${NC}"

CUSTOMERS=$(db_query "
SELECT id, email, name, phone, created_at
FROM customers
WHERE email LIKE '%${SEARCH_TERM}%'
OR phone LIKE '%${SEARCH_TERM}%'
OR name LIKE '%${SEARCH_TERM}%'
" 2>/dev/null || echo "")

if [ -n "$CUSTOMERS" ] && [ "$CUSTOMERS" != "id" ]; then
    echo -e "${GREEN}✅ Found in customers table:${NC}"
    db_table "
    SELECT id, email, name, phone, created_at
    FROM customers
    WHERE email LIKE '%${SEARCH_TERM}%'
    OR phone LIKE '%${SEARCH_TERM}%'
    OR name LIKE '%${SEARCH_TERM}%'
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in customers${NC}"
fi
echo ""

#==========================================================================
# SEARCH 3: Seat Reservations
#==========================================================================
echo -e "${BLUE}[3/10] Searching seat reservations...${NC}"

RESERVATIONS=$(db_query "
SELECT id, event_id, session_id, hold_token, customer_email, status, created_at, expires_at
FROM seat_reservations
WHERE customer_email LIKE '%${SEARCH_TERM}%'
OR customer_phone LIKE '%${SEARCH_TERM}%'
OR hold_token = '${SEARCH_TERM}'
OR session_id = '${SEARCH_TERM}'
" 2>/dev/null || echo "")

if [ -n "$RESERVATIONS" ] && [ "$RESERVATIONS" != "id" ]; then
    echo -e "${GREEN}✅ Found in seat_reservations:${NC}"
    db_table "
    SELECT id, event_id, session_id, hold_token, customer_email, status, created_at, expires_at
    FROM seat_reservations
    WHERE customer_email LIKE '%${SEARCH_TERM}%'
    OR customer_phone LIKE '%${SEARCH_TERM}%'
    OR hold_token = '${SEARCH_TERM}'
    OR session_id = '${SEARCH_TERM}'
    ORDER BY created_at DESC
    LIMIT 10
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in seat_reservations${NC}"
fi
echo ""

#==========================================================================
# SEARCH 4: Payment Intents (Stripe/Revolut)
#==========================================================================
echo -e "${BLUE}[4/10] Searching payment_intents...${NC}"

PAYMENTS=$(db_query "
SELECT id, payment_intent_id, amount, currency, status, customer_email, gateway, created_at
FROM payment_intents
WHERE customer_email LIKE '%${SEARCH_TERM}%'
OR payment_intent_id = '${SEARCH_TERM}'
OR metadata LIKE '%${SEARCH_TERM}%'
" 2>/dev/null || echo "")

if [ -n "$PAYMENTS" ] && [ "$PAYMENTS" != "id" ]; then
    echo -e "${GREEN}✅ Found in payment_intents:${NC}"
    db_table "
    SELECT id, payment_intent_id, amount, currency, status, customer_email, gateway, created_at
    FROM payment_intents
    WHERE customer_email LIKE '%${SEARCH_TERM}%'
    OR payment_intent_id = '${SEARCH_TERM}'
    OR metadata LIKE '%${SEARCH_TERM}%'
    ORDER BY created_at DESC
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in payment_intents${NC}"
fi
echo ""

#==========================================================================
# SEARCH 5: Revolut Orders (if table exists)
#==========================================================================
echo -e "${BLUE}[5/10] Searching revolut_orders...${NC}"

REVOLUT=$(db_query "
SELECT id, public_id, amount, currency, email, state, created_at
FROM revolut_orders
WHERE email LIKE '%${SEARCH_TERM}%'
OR public_id = '${SEARCH_TERM}'
" 2>/dev/null || echo "")

if [ -n "$REVOLUT" ] && [ "$REVOLUT" != "id" ]; then
    echo -e "${GREEN}✅ Found in revolut_orders:${NC}"
    db_table "
    SELECT id, public_id, amount, currency, email, state, created_at
    FROM revolut_orders
    WHERE email LIKE '%${SEARCH_TERM}%'
    OR public_id = '${SEARCH_TERM}'
    ORDER BY created_at DESC
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in revolut_orders${NC}"
fi
echo ""

#==========================================================================
# SEARCH 6: Account Setup Invitations
#==========================================================================
echo -e "${BLUE}[6/10] Searching account_setup_invitations...${NC}"

INVITATIONS=$(db_query "
SELECT id, email, status, token, sent_at, created_at
FROM account_setup_invitations
WHERE email LIKE '%${SEARCH_TERM}%'
OR token = '${SEARCH_TERM}'
" 2>/dev/null || echo "")

if [ -n "$INVITATIONS" ] && [ "$INVITATIONS" != "id" ]; then
    echo -e "${GREEN}✅ Found in account_setup_invitations:${NC}"
    db_table "
    SELECT id, email, status, token, sent_at, created_at
    FROM account_setup_invitations
    WHERE email LIKE '%${SEARCH_TERM}%'
    OR token = '${SEARCH_TERM}'
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in invitations${NC}"
fi
echo ""

#==========================================================================
# SEARCH 7: Users Table
#==========================================================================
echo -e "${BLUE}[7/10] Searching users...${NC}"

USERS=$(db_query "
SELECT id, email, name, created_at
FROM users
WHERE email LIKE '%${SEARCH_TERM}%'
OR name LIKE '%${SEARCH_TERM}%'
" 2>/dev/null || echo "")

if [ -n "$USERS" ] && [ "$USERS" != "id" ]; then
    echo -e "${GREEN}✅ Found in users table:${NC}"
    db_table "
    SELECT id, email, name, created_at
    FROM users
    WHERE email LIKE '%${SEARCH_TERM}%'
    OR name LIKE '%${SEARCH_TERM}%'
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in users${NC}"
fi
echo ""

#==========================================================================
# SEARCH 8: Failed Jobs Queue
#==========================================================================
echo -e "${BLUE}[8/10] Searching failed_jobs...${NC}"

FAILED=$(db_query "
SELECT id, queue, exception, failed_at
FROM failed_jobs
WHERE payload LIKE '%${SEARCH_TERM}%'
OR exception LIKE '%${SEARCH_TERM}%'
" 2>/dev/null || echo "")

if [ -n "$FAILED" ] && [ "$FAILED" != "id" ]; then
    echo -e "${RED}⚠️  Found in failed_jobs (errors):${NC}"
    db_table "
    SELECT id, queue, LEFT(exception, 100) as error_preview, failed_at
    FROM failed_jobs
    WHERE payload LIKE '%${SEARCH_TERM}%'
    OR exception LIKE '%${SEARCH_TERM}%'
    ORDER BY failed_at DESC
    LIMIT 5
    "
    FOUND_SOMETHING=true
else
    echo -e "${YELLOW}⚠️  Not found in failed_jobs${NC}"
fi
echo ""

#==========================================================================
# SEARCH 9: Laravel Logs (Last 7 Days)
#==========================================================================
echo -e "${BLUE}[9/10] Searching Laravel logs (last 7 days)...${NC}"

LOG_MATCHES=0
for i in {0..6}; do
    LOG_DATE=$(date -v-${i}d +%Y-%m-%d 2>/dev/null || date -d "-${i} days" +%Y-%m-%d)
    LOG_FILE="storage/logs/laravel-${LOG_DATE}.log"

    if [ -f "$LOG_FILE" ]; then
        MATCHES=$(grep -i "$SEARCH_TERM" "$LOG_FILE" 2>/dev/null | wc -l | xargs)
        if [ "$MATCHES" -gt 0 ]; then
            echo -e "${GREEN}  ✅ Found ${MATCHES} matches in ${LOG_DATE}${NC}"
            LOG_MATCHES=$((LOG_MATCHES + MATCHES))

            # Show first few matches
            echo -e "${CYAN}    Sample entries:${NC}"
            grep -i "$SEARCH_TERM" "$LOG_FILE" 2>/dev/null | head -n 3 | sed 's/^/    /'
            echo ""
            FOUND_SOMETHING=true
        fi
    fi
done

if [ "$LOG_MATCHES" -eq 0 ]; then
    echo -e "${YELLOW}⚠️  Not found in Laravel logs (last 7 days)${NC}"
fi
echo ""

#==========================================================================
# SEARCH 10: Payment Gateway Logs (if exists)
#==========================================================================
echo -e "${BLUE}[10/10] Searching payment-specific logs...${NC}"

if [ -f "storage/logs/payment-intents.log" ]; then
    PAYMENT_LOG_MATCHES=$(grep -i "$SEARCH_TERM" storage/logs/payment-intents.log 2>/dev/null | wc -l | xargs)
    if [ "$PAYMENT_LOG_MATCHES" -gt 0 ]; then
        echo -e "${GREEN}✅ Found ${PAYMENT_LOG_MATCHES} matches in payment logs${NC}"
        echo -e "${CYAN}Sample entries:${NC}"
        grep -i "$SEARCH_TERM" storage/logs/payment-intents.log 2>/dev/null | head -n 5 | sed 's/^/  /'
        FOUND_SOMETHING=true
    else
        echo -e "${YELLOW}⚠️  Not found in payment logs${NC}"
    fi
else
    echo -e "${YELLOW}⚠️  No payment-specific log file found${NC}"
fi
echo ""

#==========================================================================
# SUMMARY
#==========================================================================
echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}  Search Summary${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""

if [ "$FOUND_SOMETHING" = true ]; then
    echo -e "${GREEN}✅ Found traces of customer in system${NC}"
    echo ""
    echo "Next steps:"
    echo "  1. Review tables where found"
    echo "  2. Check payment gateway directly (Stripe/Revolut dashboard)"
    echo "  3. Analyze log entries for errors"
    echo "  4. Determine if order creation failed after payment"
else
    echo -e "${RED}❌ NO TRACES FOUND${NC}"
    echo ""
    echo "This indicates:"
    echo "  • Payment may not have reached our system"
    echo "  • Customer may have mistyped email"
    echo "  • Payment gateway webhook failed"
    echo "  • Data was purged/deleted"
    echo ""
    echo "Next steps:"
    echo "  1. Check payment gateway dashboard directly"
    echo "  2. Ask customer for:"
    echo "     - Exact date/time of booking"
    echo "     - Bank transaction ID"
    echo "     - Screenshot of charge"
    echo "  3. Check webhook logs in payment gateway"
fi

echo ""