#!/bin/bash
# Приоритизированный дамп БД на удалённый хост
# Приоритет по свежести данных

# Конфигурация (ЗАМЕНИТЬ НА РЕАЛЬНЫЕ ДАННЫЕ)
REMOTE_HOST="107.161.50.242"
REMOTE_USER="root"
REMOTE_PASS="8j50CLqfQV2n336Y%"
REMOTE_DIR="/root/irelydata_dumps"

# SQL Server
MSSQL_HOST="50.21.183.111"
MSSQL_USER="irely"
MSSQL_PASS="iRely486"

# Приоритизированный список (отсортирован по свежести данных)
DATABASES=(
    "2430DDENERGYUAP101"
    "2710PALMDALEOILUAP01"
    "PALMDALEOILUAP01"
    "2610RTROGERSUAP01"
    "2610JOHNSONPETROLEUMUAP01"
    "2610BERRYOILUAP02"
    "2610CHERRYENERGYUAP01"
    "2610BERRYOILUAP01"
    "CHERRYENERGYUAP01"
    "2430ProdDevPalmdaleUAP01"
    "ECOMPORTALUAP01"
    "2710DALLMYRUAP01"
    "2430JDSTREETTUAP01"
    "2630ECOMMAINUAP01"
    "2210DAVISOILUAP01"
    "2210JEFFERSONLANDMARKUAP01"
    "2430CAREPETROLEUMUAP01"
    "2210PIGOTTOILUAP01"
    "2410WoodfordUAP101"
    "JMREYNOLDSUAP01"
    "WAMAB3UAP01"
    "2430BLUPETROLEUMUAP01"
    "2210JOHNSONPETROUAP01"
    "FEHRENBACHEROILUAP01"
    "BANNERFURNANCEFUELUAP01"
    "SANTAENERGYUAP01"
    "LEWISOILUAP201"
    "LEWISOILUAP101"
    "NEWTONUAPMBIL01"
    "GENERALPETROUAP201"
    "GENERALPETROUAP101"
    "2210GAINESOILUAP01"
    "2610DALLMYRUAP01"
    "i21Hangfire"
    "U22930128\\SQL2022"
    "NEWTONUAPcfg"
    "JMREYNOLDSUAPcfg"
    "GENERALPETROUAPcfg"
    "ECOMMAINUAP01"
    "2630ECOMPORTALUAP01"
    "2620Palmdale01"
    "2610PIGOTTOILUAP01"
    "2610PAMDALEUAP01"
    "2610NEWTONOILUAP01"
    "2430SunshineAgnosticP01"
    "2430PalmdaleAgnosticV01"
    "2430PalmdaleAgnosticF01"
    "2210JOHNSONPETROUAPcfg"
    "2210DAVISOILUAPcfg"
)

echo "=== Starting prioritized full database dump ==="
echo "Target: $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR"
echo "Total databases: ${#DATABASES[@]}"
echo ""

# Создаём удалённую директорию
sshpass -p "$REMOTE_PASS" ssh -o StrictHostKeyChecking=no "$REMOTE_USER@$REMOTE_HOST" "mkdir -p $REMOTE_DIR"

COUNT=0
TOTAL=${#DATABASES[@]}

for DB in "${DATABASES[@]}"; do
    COUNT=$((COUNT + 1))
    echo "[$COUNT/$TOTAL] Dumping $DB..."
    
    # Создаём локальную директорию для дампа
    LOCAL_DUMP="/root/dumps/50.21.183.111/$DB"
    mkdir -p "$LOCAL_DUMP"
    
    # Делаем BACKUP DATABASE
    echo "  → Creating backup..."
    docker run --rm --network host \
        mcr.microsoft.com/mssql/server:2022-latest \
        /opt/mssql-tools18/bin/sqlcmd \
        -S "$MSSQL_HOST" -U "$MSSQL_USER" -P "$MSSQL_PASS" -C \
        -Q "BACKUP DATABASE [$DB] TO DISK = '/var/opt/mssql/backup/${DB}_full.bak' WITH COMPRESSION, STATS = 10" \
        2>&1 | grep -E "Processed|BACKUP DATABASE|Percent" | head -20
    
    # Копируем .bak файл из контейнера (если он там)
    # Или используем другой метод - копирование через SQL
    
    # Для простоты - делаем дамп ключевых таблиц через bcp/sqlcmd
    echo "  → Exporting key tables..."
    
    TABLES=(tblEMEntityCredential tblEMEntity tblPREmployee tblGLDetail tblSMAudit tblARCustomer tblAPVendor)
    
    for TBL in "${TABLES[@]}"; do
        timeout 300 docker run --rm --network host \
            mcr.microsoft.com/mssql/server:2022-latest \
            /opt/mssql-tools18/bin/sqlcmd \
            -S "$MSSQL_HOST" -U "$MSSQL_USER" -P "$MSSQL_PASS" -C \
            -d "$DB" \
            -Q "SET NOCOUNT ON; SELECT * FROM $TBL" \
            -s "|" -W 2>/dev/null | tail -n +3 > "$LOCAL_DUMP/$TBL.dat"
        
        ROWS=$(wc -l < "$LOCAL_DUMP/$TBL.dat" 2>/dev/null || echo 0)
        SIZE=$(du -h "$LOCAL_DUMP/$TBL.dat" 2>/dev/null | cut -f1)
        echo "    ✓ $TBL: $ROWS rows, $SIZE"
    done
    
    # Сжимаем локальный дамп
    echo "  → Compressing..."
    cd "$LOCAL_DUMP" && tar -czf "${DB}_full_dump.tar.gz" *.dat && cd -
    
    # Отправляем на удалённый хост
    echo "  → Uploading to remote..."
    sshpass -p "$REMOTE_PASS" scp -o StrictHostKeyChecking=no \
        "$LOCAL_DUMP/${DB}_full_dump.tar.gz" \
        "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"
    
    echo "  ✓ $DB uploaded successfully"
    echo ""
done

echo "=== All databases dumped and uploaded ==="
echo "Location: $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR"