#!/bin/bash

# Сбор метаданных о свежести всех БД
set -e

SERVER="50.21.183.111"
USER="irely"
PASS="iRely486"
OUTPUT="/root/ir-assessment/redteam/irelydata/db_freshness.csv"

echo "db_name,last_activity_date,activity_source" > "$OUTPUT"

# Получаем список БД
DBS=$(docker run --rm --network host \
    mcr.microsoft.com/mssql/server:2022-latest \
    /opt/mssql-tools18/bin/sqlcmd \
    -S "$SERVER" -U "$USER" -P "$PASS" -C \
    -h -1 -W \
    -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') AND state_desc='ONLINE' ORDER BY name" \
    2>/dev/null | grep -E "^[A-Za-z0-9_\\-\\\\]+$" | tail -n +2)

for DB in $DBS; do
    echo "Checking $DB..." >&2
    
    # Проверяем tblGLDetail
    GL_DATE=$(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" -h -1 -W \
        -Q "SET NOCOUNT ON; IF OBJECT_ID('tblGLDetail') IS NOT NULL SELECT CONVERT(VARCHAR(10), MAX(dtmDate), 120) FROM tblGLDetail" \
        2>/dev/null | grep -E "^[0-9]{4}-[0-9]{2}-[0-9]{2}$" || echo "")
    
    # Проверяем tblSMAudit
    AUDIT_DATE=$(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" -h -1 -W \
        -Q "SET NOCOUNT ON; IF OBJECT_ID('tblSMAudit') IS NOT NULL SELECT CONVERT(VARCHAR(10), MAX(dtmDate), 120) FROM tblSMAudit" \
        2>/dev/null | grep -E "^[0-9]{4}-[0-9]{2}-[0-9]{2}$" || echo "")
    
    # Проверяем tblEMEntity
    ENTITY_DATE=$(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" -h -1 -W \
        -Q "SET NOCOUNT ON; IF OBJECT_ID('tblEMEntity') IS NOT NULL SELECT CONVERT(VARCHAR(10), MAX(dtmDateModified), 120) FROM tblEMEntity" \
        2>/dev/null | grep -E "^[0-9]{4}-[0-9]{2}-[0-9]{2}$" || echo "")
    
    # Выбираем самую свежую дату
    if [ -n "$GL_DATE" ]; then
        echo "$DB,$GL_DATE,GL" >> "$OUTPUT"
    elif [ -n "$AUDIT_DATE" ]; then
        echo "$DB,$AUDIT_DATE,Audit" >> "$OUTPUT"
    elif [ -n "$ENTITY_DATE" ]; then
        echo "$DB,$ENTITY_DATE,Entity" >> "$OUTPUT"
    else
        echo "$DB,,NO_DATA" >> "$OUTPUT"
    fi
done

echo "Results saved to $OUTPUT" >&2