#!/usr/bin/env bash
set -euo pipefail

DB_URL=${DB_URL:-"postgresql://bizone:%2BQ%26xN%2486LbSA%3Cav%3C@127.0.0.1:5432/wa_dashboard"}

run_psql() {
  psql "$DB_URL" -c "$1"
}

run_with_docker() {
  local container="$1"
  docker exec -i "$container" psql "$DB_URL" -c "$2"
}

if command -v psql >/dev/null 2>&1; then
  echo "=== WEBHOOK EVENTS (DB) ==="
  run_psql "SELECT event_id,event_type,processing_status,verified,created_at,sender_phone,recipient_phone FROM webhook_events ORDER BY created_at DESC LIMIT 20;"

  echo "=== WEBHOOK JOBS ==="
  run_psql "SELECT id,queue_name,job_type,status,attempts,max_attempts,error_message,created_at FROM jobs WHERE queue_name='webhooks' ORDER BY created_at DESC LIMIT 20;"

  echo "=== WEBHOOK SUMMARY ==="
  run_psql "SELECT COUNT(*) FILTER (WHERE event_type='message.inbound') AS inbound_count, COUNT(*) FILTER (WHERE processing_status='received') AS received_count, COUNT(*) FILTER (WHERE processing_status='queued') AS queued_count, COUNT(*) FILTER (WHERE processing_status='processed') AS processed_count, COUNT(*) FILTER (WHERE processing_status='failed') AS failed_count FROM webhook_events;"

  echo "=== INBOUND EVENTS VS CONVERSATION MESSAGES ==="
  run_psql "SELECT w.event_id, w.sender_phone, w.recipient_phone, w.created_at AS event_at, w.processing_status, w.verified, c.id AS contact_id, c.phone_number, cm.id AS conversation_message_id, cm.direction, cm.body, cm.occurred_at AS message_at FROM webhook_events w LEFT JOIN conversation_messages cm ON cm.webhook_event_id = w.event_id LEFT JOIN contacts c ON c.id = cm.contact_id WHERE w.event_type = 'message.inbound' ORDER BY w.created_at DESC LIMIT 20;"

  echo "=== CONTACTS TO CHECK (for manual validation) ==="
  run_psql "SELECT id, phone_number, name, created_at, updated_at FROM contacts ORDER BY updated_at DESC LIMIT 20;"
elif command -v docker >/dev/null 2>&1; then
  CONTAINER=$(docker ps --filter "ancestor=postgres" --format '{{.Names}}' | head -n 1)

  if [ -z "${CONTAINER}" ]; then
    CONTAINER=$(docker ps --filter "name=postgres" --format '{{.Names}}' | head -n 1)
  fi

  if [ -z "${CONTAINER}" ]; then
    echo "Container postgres tidak ketemu via docker ps."
    echo "Isi container yang ada:"
    docker ps --format '{{.Names}}\t{{.Image}}'
    exit 1
  fi

  echo "Menggunakan container: ${CONTAINER}"
  echo "=== WEBHOOK EVENTS (DB via container) ==="
  run_with_docker "$CONTAINER" "SELECT event_id,event_type,processing_status,verified,created_at,sender_phone,recipient_phone FROM webhook_events ORDER BY created_at DESC LIMIT 20;"

  echo "=== WEBHOOK JOBS (DB via container) ==="
  run_with_docker "$CONTAINER" "SELECT id,queue_name,job_type,status,attempts,max_attempts,error_message,created_at FROM jobs WHERE queue_name='webhooks' ORDER BY created_at DESC LIMIT 20;"

  echo "=== WEBHOOK SUMMARY (DB via container) ==="
  run_with_docker "$CONTAINER" "SELECT COUNT(*) FILTER (WHERE event_type='message.inbound') AS inbound_count, COUNT(*) FILTER (WHERE processing_status='received') AS received_count, COUNT(*) FILTER (WHERE processing_status='queued') AS queued_count, COUNT(*) FILTER (WHERE processing_status='processed') AS processed_count, COUNT(*) FILTER (WHERE processing_status='failed') AS failed_count FROM webhook_events;"

  echo "=== INBOUND EVENTS VS CONVERSATION MESSAGES (via container) ==="
  run_with_docker "$CONTAINER" "SELECT w.event_id, w.sender_phone, w.recipient_phone, w.created_at AS event_at, w.processing_status, w.verified, c.id AS contact_id, c.phone_number, cm.id AS conversation_message_id, cm.direction, cm.body, cm.occurred_at AS message_at FROM webhook_events w LEFT JOIN conversation_messages cm ON cm.webhook_event_id = w.event_id LEFT JOIN contacts c ON c.id = cm.contact_id WHERE w.event_type = 'message.inbound' ORDER BY w.created_at DESC LIMIT 20;"

  echo "=== CONTACTS TO CHECK (via container) ==="
  run_with_docker "$CONTAINER" "SELECT id, phone_number, name, created_at, updated_at FROM contacts ORDER BY updated_at DESC LIMIT 20;"
else
  echo "Tidak ada psql dan docker CLI di server ini."
  exit 1
fi