#!/bin/bash
# A: PII/financial extraction from all 14 restored i21 DBs
set -u
BASE=/root/ir-assessment/redteam/irelydata
OUT=$BASE/analysis
LOG=$BASE/analysis_A.log
mkdir -p "$OUT"
SQLCMD="docker exec mssql-irely /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P Ir3ly!Loc4l#Only -C -y 0"
: > "$LOG"

DBS="CHERRYENERGYUAP01 CITYMARTUAP01 FEHRENBACHEROILUAP01 GAINESOILUAP01 HILERBROTHERSUAP01 JHWILLIAMSUAP01 LEGACYFUELSUAP01 PROENERGYUAP01 RTROGERSUAP01 SAUNDERSOIL01 SCHAFERPROPANEUAP01 WATKINSUAP01 WILCOXFLEGALUAP01 src2010"

q() {  # q DB "SQL" outfile
    timeout 300 $SQLCMD -d "$1" -Q "SET NOCOUNT ON; $2" -s"," 2>/dev/null > "$3"
    echo "$(wc -l < "$3") rows -> $3" >> "$LOG"
}

for db in $DBS; do
    echo "=== $(date -Iseconds) $db" >> "$LOG"
    # rowcounts for scope
    q "$db" "SELECT 'tblEMEntity', COUNT(*) FROM tblEMEntity UNION ALL SELECT 'tblARCustomer', COUNT(*) FROM tblARCustomer UNION ALL SELECT 'tblAPVendor', COUNT(*) FROM tblAPVendor UNION ALL SELECT 'tblPREmployee', COUNT(*) FROM sys.tables t WHERE t.name='tblPREmployee'" "$OUT/${db}_counts.csv"
    # PII: entities (name, tax ids, contacts)
    q "$db" "SELECT intEntityId, strName, strEmail, strFederalTaxId, strStateTaxId, strContactNumber, strMobile, strPhone FROM tblEMEntity" "$OUT/${db}_EntityPII.csv"
    # Employees with SSN if table exists
    q "$db" "SELECT intEntityId, strSocialSecurity FROM tblPREmployee WHERE strSocialSecurity IS NOT NULL AND strSocialSecurity<>''" "$OUT/${db}_EmployeeSSN.csv"
    # Customers
    q "$db" "SELECT intEntityId FROM tblARCustomer" "$OUT/${db}_CustomerIDs.csv"
    echo "=== DONE $db" >> "$LOG"
done
echo "=== $(date -Iseconds) ALL-DONE" >> "$LOG"