#!/bin/bash
# Recover Order from Orphaned Payment (Revolut/Stripe)
#
# This script handles the case where payment succeeded but order creation failed
# (usually due to webhook failure or race condition)
#
# Usage: ./scripts/recover-orphaned-payment.sh --revolut-order-id=<id>
#        ./scripts/recover-orphaned-payment.sh --reservation-id=<id>
#        ./scripts/recover-orphaned-payment.sh --session-id=<id>
#
# Example: ./scripts/recover-orphaned-payment.sh --revolut-order-id=69171609-480e-adb0-8c67-816037b4fa78
#
# Author: Charlie / Claude Code
# Date: 2025-11-14
# Incident: INCIDENT_2025-11-14_REVOLUT_WEBHOOK_FAILURE.md

set -e

# Load database helper
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source "$SCRIPT_DIR/_db-helper.sh"

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

# Parse arguments
REVOLUT_ORDER_ID=""
RESERVATION_ID=""
SESSION_ID=""
DRY_RUN=false
SEND_EMAIL=true

while [[ $# -gt 0 ]]; do
    case $1 in
        --revolut-order-id=*)
            REVOLUT_ORDER_ID="${1#*=}"
            shift
            ;;
        --reservation-id=*)
            RESERVATION_ID="${1#*=}"
            shift
            ;;
        --session-id=*)
            SESSION_ID="${1#*=}"
            shift
            ;;
        --dry-run)
            DRY_RUN=true
            shift
            ;;
        --no-email)
            SEND_EMAIL=false
            shift
            ;;
        *)
            echo "Unknown option: $1"
            echo "Usage: $0 --revolut-order-id=<id> | --reservation-id=<id> | --session-id=<id> [--dry-run] [--no-email]"
            exit 1
            ;;
    esac
done

# Validate input
if [ -z "$REVOLUT_ORDER_ID" ] && [ -z "$RESERVATION_ID" ] && [ -z "$SESSION_ID" ]; then
    echo -e "${RED}ERROR: Must provide one of: --revolut-order-id, --reservation-id, or --session-id${NC}"
    echo ""
    echo "Usage examples:"
    echo "  $0 --revolut-order-id=69171609-480e-adb0-8c67-816037b4fa78"
    echo "  $0 --reservation-id=4172"
    echo "  $0 --session-id=admin_69167aec1b509"
    echo ""
    echo "Options:"
    echo "  --dry-run    Show what would happen without making changes"
    echo "  --no-email   Skip sending confirmation email"
    exit 1
fi

echo -e "${BLUE}=== Orphaned Payment Recovery ===${NC}"
echo ""

# Step 1: Find reservations
echo -e "${YELLOW}Step 1: Finding affected reservations...${NC}"

if [ -n "$REVOLUT_ORDER_ID" ]; then
    WHERE_CLAUSE="revolut_order_id = '$REVOLUT_ORDER_ID'"
    IDENTIFIER="Revolut Order ID: $REVOLUT_ORDER_ID"
elif [ -n "$RESERVATION_ID" ]; then
    WHERE_CLAUSE="id = $RESERVATION_ID"
    IDENTIFIER="Reservation ID: $RESERVATION_ID"
else
    WHERE_CLAUSE="session_id = '$SESSION_ID'"
    IDENTIFIER="Session ID: $SESSION_ID"
fi

# Get reservation details
RESERVATION_DATA=$(db_table "
SELECT
    id, event_id, user_id, admin_user_id, session_id, status, order_id,
    revolut_order_id, payment_intent_id, payment_mode, payment_gateway,
    currency, include_booking_fee, include_tax, price_snapshot,
    created_at
FROM seat_reservations
WHERE $WHERE_CLAUSE
LIMIT 1
")

if [ -z "$RESERVATION_DATA" ]; then
    echo -e "${RED}ERROR: No reservations found for $IDENTIFIER${NC}"
    exit 1
fi

# Get all reservation IDs for this payment
RESERVATION_IDS=$(db_query "SELECT GROUP_CONCAT(id) FROM seat_reservations WHERE $WHERE_CLAUSE")
RESERVATION_COUNT=$(db_query "SELECT COUNT(*) FROM seat_reservations WHERE $WHERE_CLAUSE")

echo -e "${GREEN}✓ Found $RESERVATION_COUNT reservation(s)${NC}"
echo "  Reservation IDs: $RESERVATION_IDS"
echo ""

# Step 2: Check if order already exists
echo -e "${YELLOW}Step 2: Checking for existing order...${NC}"

# Build order search query
if [ -n "$REVOLUT_ORDER_ID" ]; then
    ORDER_SEARCH="revolut_order_id = '$REVOLUT_ORDER_ID'"
else
    # Use session_id to find order
    SEARCH_SESSION=$(db_query "SELECT session_id FROM seat_reservations WHERE $WHERE_CLAUSE LIMIT 1")
    SEARCH_EVENT=$(db_query "SELECT event_id FROM seat_reservations WHERE $WHERE_CLAUSE LIMIT 1")
    ORDER_SEARCH="session_id = '$SEARCH_SESSION' AND event_id = $SEARCH_EVENT"
fi

EXISTING_ORDER=$(db_query "SELECT id FROM orders WHERE $ORDER_SEARCH LIMIT 1")

if [ -n "$EXISTING_ORDER" ]; then
    echo -e "${YELLOW}⚠ Order already exists: ID $EXISTING_ORDER${NC}"

    # Check if reservations are linked
    LINKED_COUNT=$(db_query "SELECT COUNT(*) FROM seat_reservations WHERE $WHERE_CLAUSE AND order_id = $EXISTING_ORDER")

    if [ "$LINKED_COUNT" -eq "$RESERVATION_COUNT" ]; then
        echo -e "${GREEN}✓ All reservations already linked to order $EXISTING_ORDER${NC}"
        ORDER_ID=$EXISTING_ORDER

        # Check if tickets generated
        TICKETS_GENERATED=$(db_query "SELECT tickets_generated FROM orders WHERE id = $ORDER_ID")

        if [ "$TICKETS_GENERATED" = "1" ]; then
            echo -e "${GREEN}✓ Tickets already generated${NC}"

            if [ "$SEND_EMAIL" = true ]; then
                echo ""
                echo -e "${YELLOW}Resending confirmation email with apology...${NC}"

                if [ "$DRY_RUN" = false ]; then
                    php artisan tickets:regenerate-and-resend --order-id=$ORDER_ID --with-apology --force
                    echo -e "${GREEN}✓ Email queued${NC}"
                else
                    echo -e "${BLUE}[DRY RUN] Would send email for order $ORDER_ID${NC}"
                fi
            fi

            exit 0
        else
            echo -e "${YELLOW}⚠ Tickets not generated, will generate now${NC}"
        fi
    else
        echo -e "${YELLOW}⚠ Only $LINKED_COUNT/$RESERVATION_COUNT reservations linked${NC}"
        echo "  Will link remaining reservations..."
    fi

    ORDER_ID=$EXISTING_ORDER
else
    echo -e "${GREEN}✓ No existing order found (will create new)${NC}"
    echo ""

    # Step 3: Get customer details
    echo -e "${YELLOW}Step 3: Gathering customer and event data...${NC}"

    # Parse first reservation for customer data
    FIRST_RES_ID=$(echo "$RESERVATION_IDS" | cut -d',' -f1)

    # Get customer from user table
    USER_ID=$(db_query "SELECT user_id FROM seat_reservations WHERE id = $FIRST_RES_ID")

    if [ -n "$USER_ID" ] && [ "$USER_ID" != "NULL" ]; then
        CUSTOMER_NAME=$(db_query "SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) FROM users WHERE id = $USER_ID" | tr -s ' ')
        CUSTOMER_EMAIL=$(db_query "SELECT email FROM users WHERE id = $USER_ID")
        CUSTOMER_PHONE=$(db_query "SELECT phone FROM users WHERE id = $USER_ID")
    else
        CUSTOMER_NAME="Guest Customer"
        CUSTOMER_EMAIL="noreply@globalgala.com"
        CUSTOMER_PHONE=""
        USER_ID="NULL"
    fi

    # Get other details from reservation
    EVENT_ID=$(db_query "SELECT event_id FROM seat_reservations WHERE id = $FIRST_RES_ID")
    SESSION_ID=$(db_query "SELECT session_id FROM seat_reservations WHERE id = $FIRST_RES_ID")
    ADMIN_USER_ID=$(db_query "SELECT admin_user_id FROM seat_reservations WHERE id = $FIRST_RES_ID")
    PAYMENT_MODE=$(db_query "SELECT payment_mode FROM seat_reservations WHERE id = $FIRST_RES_ID")
    PAYMENT_GATEWAY=$(db_query "SELECT payment_gateway FROM seat_reservations WHERE id = $FIRST_RES_ID")
    CURRENCY=$(db_query "SELECT currency FROM seat_reservations WHERE id = $FIRST_RES_ID")
    INCLUDE_BOOKING_FEE=$(db_query "SELECT include_booking_fee FROM seat_reservations WHERE id = $FIRST_RES_ID")
    INCLUDE_TAX=$(db_query "SELECT include_tax FROM seat_reservations WHERE id = $FIRST_RES_ID")

    # Get payment reference
    if [ -n "$REVOLUT_ORDER_ID" ]; then
        PAYMENT_REFERENCE="$REVOLUT_ORDER_ID"
        REVOLUT_PAYMENT_ID=$(db_query "SELECT payment_intent_id FROM seat_reservations WHERE id = $FIRST_RES_ID")
    else
        PAYMENT_INTENT_ID=$(db_query "SELECT payment_intent_id FROM seat_reservations WHERE id = $FIRST_RES_ID")
        PAYMENT_REFERENCE="${PAYMENT_INTENT_ID:-N/A}"
        REVOLUT_ORDER_ID="NULL"
        REVOLUT_PAYMENT_ID="NULL"
    fi

    # Calculate total
    TOTAL=$(db_query "SELECT SUM(price_snapshot) FROM seat_reservations WHERE $WHERE_CLAUSE")

    # Get event name for display
    EVENT_NAME=$(db_query "SELECT name FROM events WHERE id = $EVENT_ID")

    echo -e "${GREEN}✓ Customer: $CUSTOMER_NAME <$CUSTOMER_EMAIL>${NC}"
    echo "  Event: $EVENT_NAME (ID: $EVENT_ID)"
    echo "  Total: $CURRENCY$TOTAL"
    echo "  Payment Gateway: $PAYMENT_GATEWAY"
    echo "  Payment Mode: $PAYMENT_MODE"
    echo ""

    # Step 4: Create order
    echo -e "${YELLOW}Step 4: Creating order record...${NC}"

    if [ "$DRY_RUN" = true ]; then
        echo -e "${BLUE}[DRY RUN] Would create order with:${NC}"
        echo "  Customer: $CUSTOMER_NAME"
        echo "  Email: $CUSTOMER_EMAIL"
        echo "  Total: $CURRENCY$TOTAL"
        echo "  Gateway: $PAYMENT_GATEWAY"
        ORDER_ID="DRY_RUN"
    else
        # Create order SQL
        cat > /tmp/create_order_$$.sql << EOF
INSERT INTO orders (
    event_id,
    user_id,
    created_by_admin_id,
    session_id,
    customer_name,
    customer_email,
    customer_phone,
    total_amount,
    status,
    payment_status,
    payment_method,
    payment_mode,
    revolut_order_id,
    revolut_payment_id,
    revolut_status,
    revolut_payment_completed_at,
    payment_reference,
    include_booking_fee,
    include_tax,
    idempotency_key,
    created_at,
    updated_at
) VALUES (
    $EVENT_ID,
    $USER_ID,
    ${ADMIN_USER_ID:-NULL},
    '$SESSION_ID',
    '$CUSTOMER_NAME',
    '$CUSTOMER_EMAIL',
    '${CUSTOMER_PHONE:-}',
    $TOTAL,
    'completed',
    'paid',
    '$PAYMENT_GATEWAY',
    '$PAYMENT_MODE',
    ${REVOLUT_ORDER_ID:+\'$REVOLUT_ORDER_ID\'},
    ${REVOLUT_PAYMENT_ID:+\'$REVOLUT_PAYMENT_ID\'},
    'completed',
    NOW(),
    '$PAYMENT_REFERENCE',
    $INCLUDE_BOOKING_FEE,
    $INCLUDE_TAX,
    UUID(),
    (SELECT created_at FROM seat_reservations WHERE id = $FIRST_RES_ID),
    NOW()
);
SELECT LAST_INSERT_ID() as order_id;
EOF

        ORDER_ID=$(mysql --host="$DB_HOST" --port="${DB_PORT:-3306}" --user="$DB_USERNAME" --password="$DB_PASSWORD" --database="$DB_DATABASE" < /tmp/create_order_$$.sql 2>&1 | grep -v "Using a password" | tail -1)
        rm -f /tmp/create_order_$$.sql

        echo -e "${GREEN}✓ Order created: ID $ORDER_ID${NC}"
    fi
fi

echo ""

# Step 5: Link reservations
echo -e "${YELLOW}Step 5: Linking reservations to order...${NC}"

if [ "$DRY_RUN" = true ]; then
    echo -e "${BLUE}[DRY RUN] Would link $RESERVATION_COUNT reservations to order $ORDER_ID${NC}"
else
    db_query "
    UPDATE seat_reservations
    SET
        order_id = $ORDER_ID,
        status = 'booked',
        updated_at = NOW()
    WHERE $WHERE_CLAUSE
    "

    echo -e "${GREEN}✓ Linked $RESERVATION_COUNT reservations${NC}"
fi

echo ""

# Step 6: Create/verify line items
echo -e "${YELLOW}Step 6: Creating order line items with complete metadata...${NC}"

EXISTING_LINE_ITEMS=$(db_query "SELECT COUNT(*) FROM order_line_items WHERE order_id = $ORDER_ID")

if [ "$EXISTING_LINE_ITEMS" -eq "0" ]; then
    if [ "$DRY_RUN" = true ]; then
        echo -e "${BLUE}[DRY RUN] Would create $RESERVATION_COUNT line items${NC}"
    else
        db_query "
        INSERT INTO order_line_items (
            order_id, item_type, description, quantity, unit_price, line_total,
            seat_id, metadata, created_at, updated_at
        )
        SELECT
            $ORDER_ID,
            'ticket',
            CONCAT('Seat ', COALESCE(s.seat_number, sr.seat_id)),
            1,
            sr.price_snapshot,
            sr.price_snapshot,
            sr.seat_id,
            JSON_OBJECT(
                'reservation_id', sr.id,
                'currency', sr.currency,
                'seat_number', s.seat_number,
                'zone', s.zone,
                'tier_prefix', COALESCE(pt.seat_prefix, 'General'),
                'tier_color', pt.color
            ),
            NOW(),
            NOW()
        FROM seat_reservations sr
        JOIN seats s ON sr.seat_id = s.seat_id AND sr.event_id = s.event_id
        LEFT JOIN price_tiers pt ON s.price_tier_id = pt.id
        WHERE $WHERE_CLAUSE
        "

        echo -e "${GREEN}✓ Created $RESERVATION_COUNT line items with complete metadata${NC}"
    fi
else
    echo -e "${YELLOW}⚠ Line items already exist ($EXISTING_LINE_ITEMS)${NC}"

    # Check if metadata is complete
    INCOMPLETE_METADATA=$(db_query "
        SELECT COUNT(*)
        FROM order_line_items
        WHERE order_id = $ORDER_ID
        AND (
            JSON_EXTRACT(metadata, '$.seat_number') IS NULL
            OR JSON_EXTRACT(metadata, '$.zone') IS NULL
        )
    ")

    if [ "$INCOMPLETE_METADATA" -gt "0" ]; then
        echo -e "${YELLOW}⚠ Found $INCOMPLETE_METADATA line items with incomplete metadata${NC}"
        echo "  Updating metadata..."

        if [ "$DRY_RUN" = false ]; then
            db_query "
            UPDATE order_line_items oli
            INNER JOIN seats s ON oli.seat_id = s.seat_id
            INNER JOIN seat_reservations sr ON oli.seat_id = sr.seat_id AND sr.order_id = oli.order_id
            LEFT JOIN price_tiers pt ON s.price_tier_id = pt.id
            SET oli.metadata = JSON_OBJECT(
                'reservation_id', JSON_UNQUOTE(JSON_EXTRACT(oli.metadata, '$.reservation_id')),
                'currency', JSON_UNQUOTE(JSON_EXTRACT(oli.metadata, '$.currency')),
                'seat_number', s.seat_number,
                'zone', s.zone,
                'tier_prefix', COALESCE(pt.seat_prefix, 'General'),
                'tier_color', pt.color
            )
            WHERE oli.order_id = $ORDER_ID
            AND s.event_id = sr.event_id
            "

            echo -e "${GREEN}✓ Updated metadata for all line items${NC}"
        else
            echo -e "${BLUE}[DRY RUN] Would update metadata${NC}"
        fi
    else
        echo -e "${GREEN}✓ All line items have complete metadata${NC}"
    fi
fi

echo ""

# Step 7: Generate tickets
echo -e "${YELLOW}Step 7: Generating tickets...${NC}"

TICKETS_GENERATED=$(db_query "SELECT tickets_generated FROM orders WHERE id = $ORDER_ID")

if [ "$TICKETS_GENERATED" = "0" ] || [ -z "$TICKETS_GENERATED" ]; then
    if [ "$DRY_RUN" = true ]; then
        echo -e "${BLUE}[DRY RUN] Would generate tickets for order $ORDER_ID${NC}"
    else
        php artisan tinker --execute="
            \$order = \App\Model\Order::find($ORDER_ID);
            if (!\$order) {
                echo 'ERROR: Order not found\n';
                exit(1);
            }
            \$service = app(\App\Services\ModernTicketService::class);
            \$result = \$service->generateOrderTickets(\$order);
            if (\$result['success']) {
                echo 'Tickets generated: ' . \$result['pdf_path'] . '\n';
            } else {
                echo 'ERROR: ' . (\$result['error'] ?? 'Unknown error') . '\n';
                exit(1);
            }
        " 2>&1 | grep -v "DEPRECATED"

        echo -e "${GREEN}✓ Tickets generated successfully${NC}"
    fi
else
    echo -e "${GREEN}✓ Tickets already generated${NC}"
fi

echo ""

# Step 8: Send confirmation email
if [ "$SEND_EMAIL" = true ]; then
    echo -e "${YELLOW}Step 8: Sending confirmation email with apology...${NC}"

    if [ "$DRY_RUN" = true ]; then
        echo -e "${BLUE}[DRY RUN] Would send confirmation email to $CUSTOMER_EMAIL${NC}"
    else
        php artisan tickets:regenerate-and-resend --order-id=$ORDER_ID --with-apology --force

        echo -e "${GREEN}✓ Email queued for sending${NC}"
        echo ""
        echo -e "${YELLOW}Processing email queue...${NC}"
        php artisan queue:work --once --timeout=120 2>&1 | grep -E "Processing|Processed|Failed" || true

        echo -e "${GREEN}✓ Email sent to $CUSTOMER_EMAIL${NC}"
    fi
else
    echo -e "${YELLOW}Skipping email (--no-email flag)${NC}"
fi

echo ""
echo -e "${GREEN}=== RECOVERY COMPLETE ===${NC}"
echo ""
echo "Summary:"
echo "  Order ID: $ORDER_ID"
echo "  Reservations: $RESERVATION_COUNT (IDs: $RESERVATION_IDS)"
echo "  Customer: $CUSTOMER_NAME <$CUSTOMER_EMAIL>"
echo "  Total: $CURRENCY$TOTAL"
echo ""

if [ "$DRY_RUN" = false ]; then
    echo "Next steps:"
    echo "  1. Verify order in admin: https://admin.globalgala.com/admin/orders/$ORDER_ID"
    echo "  2. Check customer email: $CUSTOMER_EMAIL"
    echo "  3. View tickets PDF: storage/app/tickets/order_${ORDER_ID}_*.pdf"
    echo ""
    echo "To view order details:"
    echo "  db_table \"SELECT * FROM orders WHERE id = $ORDER_ID\G\""
else
    echo -e "${BLUE}DRY RUN COMPLETE - No changes made${NC}"
    echo "Remove --dry-run flag to execute recovery"
fi

echo ""