#!/usr/bin/env bash

set -euo pipefail

source ./0-config_var_setup.sh

echo "======================================"
echo "91 ANALYZE SUPPRESSIONS"
echo "======================================"

RUN_ID=$(mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-Nse "
SELECT MAX(id)
FROM suppression_import_runs;
")

echo "Analizando Run ID: $RUN_ID"

mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-e "
DELETE FROM suppression_analysis
WHERE run_id = $RUN_ID;
"

for TYPE in \
asm_unsubscribe \
bounce \
block \
spam_report \
invalid_email
do

TOTAL=$(mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-Nse "
SELECT COUNT(*)
FROM sendgrid_suppressions
WHERE suppression_type='$TYPE';
")

FOUND=$(mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-Nse "
SELECT COUNT(*)
FROM sendgrid_suppressions s
INNER JOIN contacts c
ON s.email = c.email
WHERE s.suppression_type='$TYPE';
")

ACTIVE=$(mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-Nse "
SELECT COUNT(*)
FROM sendgrid_suppressions s
INNER JOIN contacts c
ON s.email = c.email
WHERE s.suppression_type='$TYPE'
AND c.status='active';
")

INACTIVE=$(mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-Nse "
SELECT COUNT(*)
FROM sendgrid_suppressions s
INNER JOIN contacts c
ON s.email = c.email
WHERE s.suppression_type='$TYPE'
AND c.status<>'active';
")

MISSING=$((TOTAL-FOUND))

mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-e "
INSERT INTO suppression_analysis
(
run_id,
suppression_type,
total_suppressions,
found_in_contacts,
active_contacts,
inactive_contacts,
missing_from_contacts
)
VALUES
(
$RUN_ID,
'$TYPE',
$TOTAL,
$FOUND,
$ACTIVE,
$INACTIVE,
$MISSING
);
"

done

echo ""
echo "======================================"
echo "ANALYSIS"
echo "======================================"

mysql \
-u"$MYSQL_USER" \
-p"$MYSQL_PASS" \
"$MYSQL_DB" \
-e "
SELECT
suppression_type,
total_suppressions,
found_in_contacts,
active_contacts,
inactive_contacts,
missing_from_contacts
FROM suppression_analysis
WHERE run_id=$RUN_ID
ORDER BY total_suppressions DESC;
"