#!/bin/bash

# Database audit script to analyze table usage
echo "=== DATABASE AUDIT REPORT ==="
echo "Date: $(date)"
echo ""

# Get list of all tables
mysql -u tilbudgivern_service -p"${DB_PASSWORD}" tilbudgivern -e "SHOW TABLES;" > /tmp/all_tables.txt

echo "=== ALL TABLES IN DATABASE ==="
cat /tmp/all_tables.txt
echo ""

# Check which tables are referenced in the unified server
echo "=== TABLES REFERENCED IN UNIFIED-SERVER.JS ==="
grep -E "(FROM|JOIN|INTO|UPDATE|DELETE FROM)" /data/tilbudgivern/unified-server.js | \
  grep -oE '[a-zA-Z_][a-zA-Z0-9_]*' | \
  grep -v -E '^(FROM|JOIN|INTO|UPDATE|DELETE|LEFT|RIGHT|INNER|OUTER|ON|WHERE|SET|VALUES)$' | \
  sort -u | \
  while read table; do
    if grep -q "^$table$" /tmp/all_tables.txt 2>/dev/null || 
       grep -q "^Tables_in_tilbudgivern$" /tmp/all_tables.txt && 
       grep -q "^$table$" <(mysql -u tilbudgivern_service -p"${DB_PASSWORD}" tilbudgivern -e "SHOW TABLES;" 2>/dev/null | tail -n +2); then
      echo "✓ $table (used)"
    fi
  done

echo ""
echo "=== POTENTIALLY UNUSED TABLES ==="
# This is a simple check - some tables might be used in ways not easily detected
mysql -u tilbudgivern_service -p"${DB_PASSWORD}" tilbudgivern -e "SHOW TABLES;" | tail -n +2 | while read table; do
  if ! grep -q "$table" /data/tilbudgivern/unified-server.js 2>/dev/null; then
    echo "? $table (possibly unused)"
  fi
done

echo ""
echo "=== TABLE SIZES ==="
mysql -u tilbudgivern_service -p"${DB_PASSWORD}" information_schema -e "
SELECT 
  table_name as 'Table',
  table_rows as 'Rows',
  ROUND(((data_length + index_length) / 1024 / 1024), 2) as 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'tilbudgivern' 
ORDER BY (data_length + index_length) DESC;
"

rm -f /tmp/all_tables.txt