DeadFace CTF - Epicsales

set -euo pipefail

HOST="env01.deadface.io"
PORT=3306
USER="epicsales"
PASS="Slighted3-Charting-Valium"
DB="epicsales_db"

MYSQL_OPTS=(
  --host="$HOST"
  --port="$PORT"
  -u"$USER"
  -p"$PASS"
  --ssl=0
  --batch
  --skip-column-names
  --default-character-set=utf8mb4
  -D "$DB"
)

run_sql() {
  mysql "${MYSQL_OPTS[@]}" -e "$1"
}

run_sql "SELECT 1;" >/dev/null

echo "Connecting to EpicSales database to fetch flags..."

SIGNUPS_COUNT="$(run_sql "
  SELECT COUNT(*)
  FROM customers
  WHERE join_date >= '2025-09-01';
")"
echo "Signups Flag: deadface{${SIGNUPS_COUNT}}"

TOP_PRODUCT_NAME="$(run_sql "
  SELECT p.product_name
  FROM products p
  JOIN reviews r ON p.product_id = r.product_id
  GROUP BY p.product_id, p.product_name
  ORDER BY AVG(r.rating) DESC, COUNT(*) DESC, p.product_id ASC
  LIMIT 1;
")"
echo "Top Product Flag: deadface{${TOP_PRODUCT_NAME}}"

LOW_STOCK_ROW="$(run_sql "
  SELECT p.product_name, f.facility_num
  FROM inventories i
  JOIN products p   ON p.product_id = i.product_id
  JOIN facilities f ON f.facility_id = i.facility_id
  GROUP BY i.product_id, i.facility_id
  HAVING SUM(i.quantity) < 5
  ORDER BY SUM(i.quantity) ASC, p.product_name ASC, f.facility_num ASC
  LIMIT 1;
")"
LOW_STOCK_PRODUCT="$(printf "%s" "$LOW_STOCK_ROW" | awk -F'\t' '{print $1}')"
LOW_STOCK_FACILITY_NUM="$(printf "%s" "$LOW_STOCK_ROW" | awk -F'\t' '{print $2}')"
echo "Low Stock Product Flag: deadface{${LOW_STOCK_PRODUCT} ${LOW_STOCK_FACILITY_NUM}}"

CSUITE_SUM_RAW="$(run_sql "
  SELECT ROUND(SUM(pay_rate), 2)
  FROM employees
  WHERE role IN ('CEO','CTO','CFO');
")"
printf -v CSUITE_SUM_FMT "%.2f" "${CSUITE_SUM_RAW:-0}"
echo "C-Suite Payroll Flag: deadface{\$${CSUITE_SUM_FMT}}"

HIGH_ENGAGEMENT_EMAIL="$(run_sql "
  SELECT c.email
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  LEFT JOIN reviews r ON r.customer_id = c.customer_id
  WHERE r.review_id IS NULL
  GROUP BY c.customer_id, c.email
  ORDER BY COUNT(DISTINCT o.order_id) DESC, c.customer_id ASC
  LIMIT 1;
")"
echo "High Engagement Customer Flag: deadface{${HIGH_ENGAGEMENT_EMAIL}}"

TOP_SPENDER_ROW="$(run_sql "
  SELECT c.first_name, c.last_name
  FROM customers c
  JOIN orders o      ON o.customer_id = c.customer_id
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p    ON p.product_id = oi.product_id
  GROUP BY c.customer_id, c.first_name, c.last_name
  ORDER BY SUM(oi.quantity * p.price) DESC, c.customer_id ASC
  LIMIT 1;
")"
TOP_SPENDER_FIRST="$(printf "%s" "$TOP_SPENDER_ROW" | awk -F'\t' '{print $1}')"
TOP_SPENDER_LAST="$(printf "%s" "$TOP_SPENDER_ROW" | awk -F'\t' '{print $2}')"
echo "Top Spending Customer Flag: deadface{${TOP_SPENDER_FIRST} ${TOP_SPENDER_LAST}}"


HAS_EA_TABLE="$(run_sql "
  SELECT COUNT(*)
  FROM information_schema.tables
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_assignments';
")"

if [[ "$HAS_EA_TABLE" != "1" ]]; then
  echo "ERROR: Required table 'employee_assignments' not found in database '$DB'." >&2
  echo "Cannot compute 'Undervalued' flag without employee_assignments." >&2
  UNDERVALUE_FLAG=""
else
  FACILITY_ROW="$(run_sql "
    SELECT i.facility_id, AVG(i.quantity) AS avg_qty
    FROM inventories i
    GROUP BY i.facility_id
    ORDER BY avg_qty ASC, i.facility_id ASC
    LIMIT 1;
  ")"
  FACILITY_ID="$(printf "%s" "$FACILITY_ROW" | awk -F'\t' '{print $1}')"
  AVG_QTY_RAW="$(printf "%s" "$FACILITY_ROW" | awk -F'\t' '{print $2}')"
  printf -v AVG_QTY_FMT \"%.4f\" "${AVG_QTY_RAW:-0}"

  ITM_EMAIL="$(run_sql "
    SELECT e.email
    FROM employee_assignments ea
    JOIN employees e ON e.employee_id = ea.employee_id
    WHERE ea.facility_id = ${FACILITY_ID}
      AND e.role LIKE '%IT Manager%'
    ORDER BY e.start_date DESC, e.employee_id ASC
    LIMIT 1;
  ")"

  if [[ -n "$ITM_EMAIL" ]]; then
    UNDERVALUE_FLAG="deadface{${ITM_EMAIL} ${AVG_QTY_FMT}}"
  else
    UNDERVALUE_FLAG=""
  fi
fi

echo "IT Manager at Lowest-Average Facility Flag: ${UNDERVALUE_FLAG}"

echo "All flags fetched successfully."