proxmox/scripts/archive/consolidated/verify/check-npmplus-certificates-node.sh

#!/usr/bin/env bash
# Check NPMplus certificates using Node.js (better-sqlite3)
# Analyzes certificates before cleanup

set -euo pipefail

# Load IP configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(cd "$SCRIPT_DIR/.." && pwd)"
source "${PROJECT_ROOT}/config/ip-addresses.conf" 2>/dev/null || true


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

log_info() { echo -e "${BLUE}[INFO]${NC} $1"; }
log_success() { echo -e "${GREEN}[✓]${NC} $1"; }
log_warn() { echo -e "${YELLOW}[⚠]${NC} $1"; }
log_error() { echo -e "${RED}[✗]${NC} $1"; }

PROXMOX_HOST="${1:-192.168.11.11}"
CONTAINER_ID="${2:-10233}"

echo ""
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo "🔍 NPMplus Certificate Analysis"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo ""

# Check container status
log_info "Checking NPMplus container..."
CONTAINER_STATUS=$(ssh root@"$PROXMOX_HOST" "pct exec $CONTAINER_ID -- docker ps --filter 'name=npmplus' --format '{{.Status}}' 2>/dev/null || echo 'not running'")

if echo "$CONTAINER_STATUS" | grep -q "not running\|Error"; then
    log_error "NPMplus container is not running"
    exit 1
fi
log_success "Container status: $CONTAINER_STATUS"
echo ""

# Query certificates using Node.js
log_info "Querying certificates from database..."
echo ""

NODE_SCRIPT=$(cat << 'NODE_EOF'
const Database = require('better-sqlite3');
const dbPath = '/data/npmplus/database.sqlite';
let db;

try {
  db = new Database(dbPath, { readonly: true });
  
  // Get all tables
  const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
  console.log('TABLES:', JSON.stringify(tables.map(t => t.name)));
  
  // Try to find certificates table
  let certTable = null;
  for (const table of tables) {
    if (table.name.toLowerCase().includes('cert')) {
      certTable = table.name;
      break;
    }
  }
  
  if (!certTable) {
    // Try common names
    const commonNames = ['certificates', 'certificate', 'ssl_certificates', 'ssl_certificate'];
    for (const name of commonNames) {
      const exists = db.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`).get(name);
      if (exists) {
        certTable = name;
        break;
      }
    }
  }
  
  if (!certTable) {
    console.log('ERROR: No certificate table found');
    process.exit(1);
  }
  
  console.log('CERT_TABLE:', certTable);
  
  // Get schema
  const schema = db.prepare(`PRAGMA table_info(${certTable})`).all();
  console.log('SCHEMA:', JSON.stringify(schema));
  
  // Query certificates
  const certs = db.prepare(`SELECT * FROM ${certTable} ORDER BY id`).all();
  console.log('CERTIFICATES:', JSON.stringify(certs));
  
  db.close();
} catch (error) {
  console.log('ERROR:', error.message);
  process.exit(1);
}
NODE_EOF
)

RESULT=$(ssh root@"$PROXMOX_HOST" "pct exec $CONTAINER_ID -- docker exec npmplus node -e '$NODE_SCRIPT' 2>&1" || echo "")

if echo "$RESULT" | grep -q "ERROR"; then
    ERROR_MSG=$(echo "$RESULT" | grep "ERROR" | head -1)
    log_error "$ERROR_MSG"
    echo "$RESULT"
    exit 1
fi

# Parse results
TABLES=$(echo "$RESULT" | grep "^TABLES:" | sed 's/TABLES: //' | jq -r '.[]' 2>/dev/null || echo "")
CERT_TABLE=$(echo "$RESULT" | grep "^CERT_TABLE:" | sed 's/CERT_TABLE: //' || echo "")
CERTIFICATES_JSON=$(echo "$RESULT" | grep "^CERTIFICATES:" | sed 's/CERTIFICATES: //' || echo "")

if [ -z "$CERTIFICATES_JSON" ] || [ "$CERTIFICATES_JSON" = "[]" ]; then
    log_warn "No certificates found in database"
    log_info "Tables: $TABLES"
    log_info "Certificate table: $CERT_TABLE"
    exit 0
fi

log_info "Found certificate table: $CERT_TABLE"
echo ""

# Display certificates
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo "📋 All Certificates:"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo ""

CERT_COUNT=$(echo "$CERTIFICATES_JSON" | jq 'length' 2>/dev/null || echo "0")
log_info "Total certificates: $CERT_COUNT"
echo ""

declare -A CERT_GROUPS
declare -A CERT_INFO

echo "$CERTIFICATES_JSON" | jq -c '.[]' 2>/dev/null | while IFS= read -r cert_json; do
    cert_id=$(echo "$cert_json" | jq -r '.id // empty' 2>/dev/null || echo "")
    domain_names=$(echo "$cert_json" | jq -r '.domain_names // .domainNames // [] | if type == "array" then join(",") else . end' 2>/dev/null || echo "")
    provider=$(echo "$cert_json" | jq -r '.provider // "unknown"' 2>/dev/null || echo "unknown")
    valid_from=$(echo "$cert_json" | jq -r '.valid_from // .validFrom // "N/A"' 2>/dev/null || echo "N/A")
    valid_to=$(echo "$cert_json" | jq -r '.valid_to // .validTo // "N/A"' 2>/dev/null || echo "N/A")
    created_on=$(echo "$cert_json" | jq -r '.created_on // .createdOn // .created_at // "N/A"' 2>/dev/null || echo "N/A")
    
    if [ -n "$cert_id" ]; then
        echo "  ID: $cert_id"
        echo "    Domains: $domain_names"
        echo "    Provider: $provider"
        echo "    Valid From: $valid_from"
        echo "    Valid To: $valid_to"
        echo "    Created: $created_on"
        echo ""
        
        # Normalize for grouping
        normalized=$(echo "$domain_names" | tr '[:upper:]' '[:lower:]' | tr ',' ' ' | xargs -n1 | sort | xargs | tr ' ' ',')
        
        if [ -z "${CERT_GROUPS[$normalized]:-}" ]; then
            CERT_GROUPS[$normalized]="$cert_id"
        else
            CERT_GROUPS[$normalized]="${CERT_GROUPS[$normalized]},$cert_id"
        fi
    fi
done

# Analyze duplicates
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo "🔍 Duplicate Analysis:"
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo ""

duplicate_found=false

for domains in "${!CERT_GROUPS[@]}"; do
    cert_ids="${CERT_GROUPS[$domains]}"
    cert_array=(${cert_ids//,/ })
    
    if [ ${#cert_array[@]} -gt 1 ]; then
        duplicate_found=true
        log_warn "Duplicate certificates found for: $domains"
        echo ""
        log_info "  Certificate IDs: ${cert_array[*]}"
        log_info "  → Keep the most recent one, delete others"
        echo ""
    fi
done

if [ "$duplicate_found" = false ]; then
    log_success "✅ No duplicate certificates found!"
else
    log_warn "⚠️  Duplicates found. Run cleanup script to remove them."
fi

echo ""