#!/bin/bash
# Mass restore all 16 unique irelydata backups + export key tables per DB
set -u
BASE=/root/ir-assessment/redteam/irelydata
DL=$BASE/downloads
OUT=$BASE/extracted
LOG=$BASE/restore_all.log
SQLCMD="docker exec mssql-irely /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P Ir3ly!Loc4l#Only -C -y 0"
: > "$LOG"

declare -A MAP=(
  [2010Source0809.bak]=src2010   # already restored, will skip restore
  [CHERRYENERGYUAP01_022624.bak]=CHERRYENERGYUAP01
  [CITYMARTUAP01_063023.bak]=CITYMARTUAP01
  [FEHRENBACHEROILUAP01_INITIAL.bak]=FEHRENBACHEROILUAP01
  [GAINESOILUAP01_022624.bak]=GAINESOILUAP01
  [HILERBROTHERSUAP01_INITIAL.bak]=HILERBROTHERSUAP01
  [JHWILLIAMSUAP01_INITIAL.bak]=JHWILLIAMSUAP01
  [LEGACYFUELSUAP01_INITIAL.bak]=LEGACYFUELSUAP01
  [PROENERGYUAP01_INITIAL.bak]=PROENERGYUAP01
  [RTROGERSUAP.bak]=RTROGERSUAP01
  [SAUNDERSOIL01_102723.bak]=SAUNDERSOIL01
  [SCHAFERPROPANEUAP01_022824.bak]=SCHAFERPROPANEUAP01
  [WILCOXFLEGALUAP01.bak]=WILCOXFLEGALUAP01
  [Watkins_010224.bak]=WATKINSUAP01
)

for f in "${!MAP[@]}"; do
    db="${MAP[$f]}"
    echo "=== $(date -Iseconds) FILE $f -> DB $db" >> "$LOG"
    if [ "$f" = "2010Source0809.bak" ]; then
        echo "=== SKIP restore (already present)" >> "$LOG"
    else
        # get logical names
        fl=$(timeout 300 $SQLCMD -Q "SET NOCOUNT ON; RESTORE FILELISTONLY FROM DISK='/bak/$f'" -s"," 2>/dev/null | awk -F, 'NR>0 && NF>5')
        dlog=$(echo "$fl" | awk -F, '$3=="D"{print $1; exit}')
        llog=$(echo "$fl" | awk -F, '$3=="L"{print $1; exit}')
        if [ -z "$dlog" ]; then echo "=== FAIL filelist $f" >> "$LOG"; continue; fi
        q="RESTORE DATABASE [$db] FROM DISK='/bak/$f' WITH MOVE '$dlog' TO '/var/opt/mssql/data/${db}.mdf'"
        [ -n "$llog" ] && q="$q, MOVE '$llog' TO '/var/opt/mssql/data/${db}.ldf'"
        q="$q, REPLACE, RECOVERY"
        echo "=== $(date -Iseconds) RESTORE $db data='$dlog' log='$llog'" >> "$LOG"
        timeout 3000 $SQLCMD -Q "$q" >> "$LOG" 2>&1
        echo "=== $(date -Iseconds) RESTORE $db rc=$?" >> "$LOG"
    fi
    # export credentials (works for src2010 too)
    echo "=== $(date -Iseconds) EXPORT $db" >> "$LOG"
    timeout 300 $SQLCMD -d "$db" -Q "SET NOCOUNT ON; SELECT intEntityCredentialId, intEntityId, strUserName, strPassword, strApiKey, strApiSecret, strTFASecretKey, ysnTFAEnabled, ysnNotEncrypted FROM tblEMEntityCredential" -s"," 2>/dev/null > "$OUT/${db}_EMEntityCredential.csv"
    wc -l "$OUT/${db}_EMEntityCredential.csv" >> "$LOG" 2>&1
    # table stats
    timeout 120 $SQLCMD -d "$db" -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM sys.tables" -h -1 -W 2>/dev/null | tr -d ' \r\n' > "$OUT/${db}_tablecount.txt"
    echo "" >> "$OUT/${db}_tablecount.txt"
    echo "=== $(date -Iseconds) DONE $db" >> "$LOG"
done
echo "=== $(date -Iseconds) ALL-DONE" >> "$LOG"