#!/bin/bash
#
# Run database investigation using sqlcmd
#
set -e

# Database connection details
SERVER="10.10.10.5"
DATABASE="LRS43"
USERNAME="root"
PASSWORD="5bad89a3"

echo "=========================================="
echo "Database Investigation"
echo "=========================================="
echo ""

# Check if sqlcmd is available
if command -v sqlcmd &> /dev/null; then
    echo "✅ Using sqlcmd"
    SQL_CMD="sqlcmd -S $SERVER -d $DATABASE -U $USERNAME -P $PASSWORD -W -s'|' -w 1000"
elif command -v tsql &> /dev/null; then
    echo "✅ Using tsql (FreeTDS)"
    SQL_CMD="tsql -S MSSQL_LRS43 -D $DATABASE -U $USERNAME -P $PASSWORD"
else
    echo "❌ Neither sqlcmd nor tsql found"
    echo ""
    echo "Install options:"
    echo "  Ubuntu/Debian: sudo apt-get install freetds-bin"
    echo "  Or: sudo apt-get install mssql-tools"
    exit 1
fi

echo "Server: $SERVER"
echo "Database: $DATABASE"
echo ""

# Run investigation queries
echo "Running investigation queries..."
echo ""

# Query 1: Show associations for each document
echo "=========================================="
echo "Query 1: Current Associations"
echo "=========================================="

$SQL_CMD << 'EOF'
SELECT 
    np.string_value AS document_number,
    n.id AS node_id,
    cu.content_url,
    cu.content_size
FROM LRSAdmin.alf_node_properties np
JOIN LRSAdmin.alf_qname q ON q.id = np.qname_id
JOIN LRSAdmin.alf_node n ON n.id = np.node_id AND n.node_deleted = 0
LEFT JOIN LRSAdmin.alf_content_data cd ON cd.id = n.id
LEFT JOIN LRSAdmin.alf_content_url cu ON cu.id = cd.content_url_id
WHERE np.string_value IN ('PL11089', 'PL689', 'BP102', 'PL6204', 'PL12321')
AND q.local_name = 'sourceRids'
ORDER BY n.id;
GO
EOF

echo ""
echo "=========================================="
echo "Query 2: Node Order Analysis"
echo "=========================================="

$SQL_CMD << 'EOF'
SELECT 
    n.id AS node_id,
    np.string_value AS tagged_as,
    cu.content_url,
    SUBSTRING(cu.content_url, 9, 10) AS file_date
FROM LRSAdmin.alf_node_properties np
JOIN LRSAdmin.alf_qname q ON q.id = np.qname_id
JOIN LRSAdmin.alf_node n ON n.id = np.node_id AND n.node_deleted = 0
LEFT JOIN LRSAdmin.alf_content_data cd ON cd.id = n.id
LEFT JOIN LRSAdmin.alf_content_url cu ON cu.id = cd.content_url_id
WHERE np.string_value IN ('PL11089', 'PL689', 'BP102', 'PL6204', 'PL12321')
AND q.local_name = 'sourceRids'
ORDER BY n.id;
GO
EOF

echo ""
echo "=========================================="
echo "Analysis Complete"
echo "=========================================="
echo ""

echo "📊 Look for patterns in the output above:"
echo "  1. Are node IDs sequential?"
echo "  2. Are document numbers in the same order as node IDs?"
echo "  3. If not, there's a rotation/shift pattern"
echo ""