#!/bin/bash
# Momentry Release Package — Verify Script
# Usage: bash verify.sh

set -euo pipefail
DIR="$(cd "$(dirname "$0")" && pwd)"
UUID=$(basename "$DIR")
PG_BIN="${PG_BIN:-/Users/accusys/pgsql/18.3/bin}"
DB_NAME="${DB_NAME:-momentry}"
DB_USER="${DB_USER:-accusys}"

echo "=== Package Verification ==="
echo "UUID: $UUID"
echo ""

# Check files
FILES=("data.sql" "file_info.json" "$UUID.sqlite" "$UUID.identities.json" "$UUID.asr.json" "$UUID.face.json" "$UUID.speaker_map.json")
echo "## 1. File Integrity"
for f in "${FILES[@]}"; do
    if [ -f "$DIR/$f" ]; then
        SIZE=$(ls -lh "$DIR/$f" | awk '{print $5}')
        echo "  ✅ $f ($SIZE)"
    else
        echo "  ⚠️  $f (not found)"
    fi
done

# Check DB (if accessible)
if "$PG_BIN/psql" -U "$DB_USER" -d "$DB_NAME" -c "SELECT 1" >/dev/null 2>&1; then
    echo ""
    echo "## 2. Database"
    for tbl in chunk face_detections tkg_nodes tkg_edges identities identity_bindings; do
        COUNT=$("$PG_BIN/psql" -U "$DB_USER" -d "$DB_NAME" -t -A -c "SELECT COUNT(*) FROM dev.$tbl WHERE file_uuid='$UUID' OR uuid='$UUID'" 2>/dev/null || echo "N/A")
        echo "  $tbl: $COUNT"
    done
else
    echo ""
    echo "## 2. Database (offline — check $UUID.sqlite)"
    if [ -f "$DIR/$UUID.sqlite" ]; then
        python3 -c "
import sqlite3
conn = sqlite3.connect('$DIR/$UUID.sqlite')
c = conn.cursor()
for tbl in ['chunk', 'face_detections', 'identities', 'tkg_nodes', 'tkg_edges']:
    c.execute(f'SELECT COUNT(*) FROM {tbl}')
    print(f'  {tbl}: {c.fetchone()[0]}')
conn.close()
" 2>/dev/null || echo "  (sqlite3 unavailable)"
    fi
fi

echo ""
echo "## 3. Pipeline Status"
echo "  $("$PG_BIN/psql" -U "$DB_USER" -d "$DB_NAME" -t -A -c "SELECT status FROM dev.videos WHERE file_uuid='$UUID'" 2>/dev/null || echo "unknown")"

echo ""
echo "=== Verification Complete ==="