#!/bin/bash

# Comprehensive Backup Integrity Verification Script
# Validates backup contains all expected data for Showprima ticketing system

set -e

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"
BACKUP_DIR="$PROJECT_ROOT/database/backups"

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

echo -e "${CYAN}================================================================${NC}"
echo -e "${CYAN}Showprima Backup Integrity Verification${NC}"
echo -e "${CYAN}================================================================${NC}"
echo ""

# Check if backup file is provided
if [ -z "$1" ]; then
    echo -e "${RED}Error: No backup file specified${NC}"
    echo ""
    echo "Usage: $0 <backup_filename>"
    echo ""
    echo -e "${BLUE}Available backups:${NC}"
    ls -lh "$BACKUP_DIR"/backup_*.sql 2>/dev/null | awk '{print "  " $9 " (" $5 ", " $6 " " $7 ")"}' || echo "  No backups found"
    echo ""
    exit 1
fi

BACKUP_NAME="$1"
BACKUP_PATH="$BACKUP_DIR/$BACKUP_NAME"

if [ ! -f "$BACKUP_PATH" ]; then
    echo -e "${RED}Error: Backup not found: $BACKUP_PATH${NC}"
    exit 1
fi

echo -e "${BLUE}Backup File:${NC} $BACKUP_NAME"
echo -e "${BLUE}File Size:${NC} $(du -h "$BACKUP_PATH" | cut -f1)"
echo -e "${BLUE}Line Count:${NC} $(wc -l < "$BACKUP_PATH") lines"
echo ""

# Track pass/fail
CHECKS_PASSED=0
CHECKS_FAILED=0
CHECKS_WARNING=0

# Helper function to check condition
check() {
    local description="$1"
    local condition=$2
    local expected="$3"
    local actual="$4"
    local severity="${5:-error}"  # error or warning

    printf "%-50s " "$description"

    if [ $condition -eq 1 ]; then
        echo -e "${GREEN}✓ PASS${NC} ($actual)"
        ((CHECKS_PASSED++))
        return 0
    else
        if [ "$severity" = "warning" ]; then
            echo -e "${YELLOW}⚠ WARNING${NC} (expected: $expected, got: $actual)"
            ((CHECKS_WARNING++))
        else
            echo -e "${RED}✗ FAIL${NC} (expected: $expected, got: $actual)"
            ((CHECKS_FAILED++))
        fi
        return 1
    fi
}

echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}1. FILE STRUCTURE CHECKS${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

# Check file is not empty
FILE_SIZE=$(stat -f%z "$BACKUP_PATH" 2>/dev/null || stat -c%s "$BACKUP_PATH" 2>/dev/null)
check "Backup file is not empty" $((FILE_SIZE > 0)) "> 0 bytes" "$FILE_SIZE bytes"

# Check contains SQL statements
grep -q "CREATE TABLE" "$BACKUP_PATH" && HAS_CREATE=1 || HAS_CREATE=0
check "Contains CREATE TABLE statements" $HAS_CREATE "yes" "$([ $HAS_CREATE -eq 1 ] && echo 'yes' || echo 'no')"

grep -q "INSERT INTO" "$BACKUP_PATH" && HAS_INSERT=1 || HAS_INSERT=0
check "Contains INSERT statements" $HAS_INSERT "yes" "$([ $HAS_INSERT -eq 1 ] && echo 'yes' || echo 'no')"

# Check metadata file exists
[ -f "$BACKUP_PATH.meta.json" ] && HAS_META=1 || HAS_META=0
check "Metadata file exists" $HAS_META "yes" "$([ $HAS_META -eq 1 ] && echo 'yes' || echo 'no')"

echo ""
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}2. CORE TABLE STRUCTURE CHECKS${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

# Critical tables that MUST exist
CRITICAL_TABLES=(
    "events"
    "venue_templates"
    "seats"
    "orders"
    "order_line_items"
    "seat_reservations"
    "users"
    "admins"
)

for table in "${CRITICAL_TABLES[@]}"; do
    grep -q "CREATE TABLE.*\`$table\`" "$BACKUP_PATH" && TABLE_EXISTS=1 || TABLE_EXISTS=0
    check "Table '$table' structure exists" $TABLE_EXISTS "yes" "$([ $TABLE_EXISTS -eq 1 ] && echo 'yes' || echo 'no')"
done

echo ""
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}3. DATA CONTENT CHECKS${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

# Check for INSERT statements in critical tables
for table in "events" "seats" "orders" "admins"; do
    grep -q "INSERT INTO \`$table\`" "$BACKUP_PATH" && HAS_DATA=1 || HAS_DATA=0
    check "Table '$table' contains data" $HAS_DATA "yes" "$([ $HAS_DATA -eq 1 ] && echo 'yes' || echo 'no')"
done

echo ""
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}4. METADATA VALIDATION${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

if [ -f "$BACKUP_PATH.meta.json" ]; then
    # Extract counts from metadata
    EVENTS_META=$(jq -r '.table_counts.events // 0' "$BACKUP_PATH.meta.json" 2>/dev/null)
    ORDERS_META=$(jq -r '.table_counts.orders // 0' "$BACKUP_PATH.meta.json" 2>/dev/null)
    SEATS_META=$(jq -r '.table_counts.seat_reservations // 0' "$BACKUP_PATH.meta.json" 2>/dev/null)
    USERS_META=$(jq -r '.table_counts.users // 0' "$BACKUP_PATH.meta.json" 2>/dev/null)

    # Verify expected data volumes based on user requirements
    check "Events count (expect 2)" $((EVENTS_META == 2)) "2" "$EVENTS_META"
    check "Has multiple orders (expect many)" $((ORDERS_META >= 100)) "≥100" "$ORDERS_META" "warning"
    check "Has seat reservations" $((SEATS_META > 0)) ">0" "$SEATS_META"
    check "Has users" $((USERS_META > 0)) ">0" "$USERS_META"

    # Display all metadata
    echo ""
    echo -e "${BLUE}Full Metadata Information:${NC}"
    jq '.' "$BACKUP_PATH.meta.json" 2>/dev/null | sed 's/^/  /'
else
    echo -e "${RED}Metadata file not found - skipping metadata checks${NC}"
    ((CHECKS_FAILED++))
fi

echo ""
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}5. VENUE & SEATING VALIDATION${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

# Check for large venue (1000+ seats)
SEAT_INSERTS=$(grep -c "INSERT INTO \`seats\`" "$BACKUP_PATH" || echo 0)
check "Has large venue data (1000+ seats)" $((SEAT_INSERTS > 0)) "≥1" "$SEAT_INSERTS"

# Check for venue templates with sections
VENUE_TEMPLATE_INSERTS=$(grep -c "INSERT INTO \`venue_templates\`" "$BACKUP_PATH" || echo 0)
check "Has venue templates" $((VENUE_TEMPLATE_INSERTS > 0)) ">0" "$VENUE_TEMPLATE_INSERTS"

# Check for section data in venue templates (looking for JSON with sections)
grep -q "sections" "$BACKUP_PATH" && HAS_SECTIONS=1 || HAS_SECTIONS=0
check "Venue templates contain sections" $HAS_SECTIONS "yes" "$([ $HAS_SECTIONS -eq 1 ] && echo 'yes' || echo 'no')"

echo ""
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}6. BOOKING & ORDER VALIDATION${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

# Check for order line items (modern booking system)
ORDER_LINE_ITEMS=$(grep -c "INSERT INTO \`order_line_items\`" "$BACKUP_PATH" || echo 0)
check "Has order line items" $((ORDER_LINE_ITEMS > 0)) ">0" "$ORDER_LINE_ITEMS"

# Check for seat reservations
RESERVATIONS=$(grep -c "INSERT INTO \`seat_reservations\`" "$BACKUP_PATH" || echo 0)
check "Has seat reservations" $((RESERVATIONS > 0)) ">0" "$RESERVATIONS"

echo ""
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}7. ADMIN & SECURITY VALIDATION${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

# Check for admin accounts (staff)
ADMINS=$(grep -c "INSERT INTO \`admins\`" "$BACKUP_PATH" || echo 0)
check "Has admin/staff accounts" $((ADMINS > 0)) ">0" "$ADMINS"

# Check for audit logs
AUDIT_TABLES=("order_status_audit_logs" "payment_audit_logs" "ticket_audit_logs" "mbs_activity_logs")
for table in "${AUDIT_TABLES[@]}"; do
    grep -q "CREATE TABLE.*\`$table\`" "$BACKUP_PATH" && HAS_AUDIT=1 || HAS_AUDIT=0
    if [ $HAS_AUDIT -eq 1 ]; then
        check "Audit table '$table' exists" 1 "yes" "yes" "warning"
    fi
done

echo ""
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
echo -e "${YELLOW}8. EMAIL SYSTEM VALIDATION${NC}"
echo -e "${YELLOW}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"

# Check for email-related tables
EMAIL_TABLES=("email_accounts" "email_logs" "email_templates" "newsletter_campaigns")
for table in "${EMAIL_TABLES[@]}"; do
    grep -q "CREATE TABLE.*\`$table\`" "$BACKUP_PATH" && HAS_EMAIL_TABLE=1 || HAS_EMAIL_TABLE=0
    if [ $HAS_EMAIL_TABLE -eq 1 ]; then
        check "Email table '$table' exists" 1 "yes" "yes" "warning"
    fi
done

# Summary
echo ""
echo -e "${CYAN}================================================================${NC}"
echo -e "${CYAN}VERIFICATION SUMMARY${NC}"
echo -e "${CYAN}================================================================${NC}"
echo ""
echo -e "${GREEN}✓ Checks Passed:  $CHECKS_PASSED${NC}"
echo -e "${YELLOW}⚠ Warnings:       $CHECKS_WARNING${NC}"
echo -e "${RED}✗ Checks Failed:  $CHECKS_FAILED${NC}"
echo ""

if [ $CHECKS_FAILED -eq 0 ]; then
    echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo -e "${GREEN}✓ BACKUP INTEGRITY VERIFIED${NC}"
    echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo ""
    echo "This backup contains:"
    echo "  • Core database structure (all critical tables)"
    echo "  • Event and venue data"
    echo "  • Booking and order history"
    echo "  • User and admin accounts"
    echo "  • Audit logs and system data"
    echo ""
    echo "The backup is suitable for:"
    echo "  ✓ Production restoration"
    echo "  ✓ Development environment setup"
    echo "  ✓ Disaster recovery"
    echo ""
    exit 0
else
    echo -e "${RED}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo -e "${RED}✗ BACKUP INTEGRITY ISSUES DETECTED${NC}"
    echo -e "${RED}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo ""
    echo "⚠ WARNING: This backup may be incomplete or corrupted."
    echo "Please review the failed checks above and consider creating a new backup."
    echo ""
    exit 1
fi