#!/usr/bin/env bash
set -euo pipefail

# Safe NGBVD restore helper.
#
# IMPORTANT:
# - Restore into a NEW database first.
# - Do not point this at the live production schema unless you intentionally
#   want the dump's DROP TABLE statements to replace production tables.
#
# Usage:
#   DB_USER=root \
#   DB_HOST=127.0.0.1 \
#   SOURCE_ZIP=/path/ngbvd_20261018.sql.zip \
#   TARGET_DB=ngbvd_restore_20261018 \
#   bash scripts/restore_ngbvd_dump_safely.sh
#
# The script prompts for the MySQL password.

DB_USER="${DB_USER:-root}"
DB_HOST="${DB_HOST:-127.0.0.1}"
SOURCE_ZIP="${SOURCE_ZIP:-ngbvd_20261018.sql.zip}"
TARGET_DB="${TARGET_DB:-ngbvd_restore_20261018}"

if [ ! -f "$SOURCE_ZIP" ]; then
    echo "ERROR: SQL ZIP not found: $SOURCE_ZIP" >&2
    exit 1
fi

echo "Source:   $SOURCE_ZIP"
echo "Target:   $TARGET_DB"
echo "MySQL:    $DB_USER@$DB_HOST"
echo
echo "This creates/recreates a RESTORE database, not the live production database."
read -r -p "Continue? [y/N] " reply

case "$reply" in
    y|Y|yes|YES) ;;
    *) echo "Cancelled."; exit 0 ;;
esac

read -r -s -p "MySQL password for $DB_USER: " DB_PASSWORD
echo

MYSQL=(mysql
    "--host=$DB_HOST"
    "--user=$DB_USER"
    "--password=$DB_PASSWORD"
    "--max_allowed_packet=1G"
    "--binary-mode=1"
    "--connect-timeout=60"
    "--default-character-set=utf8mb4"
)

echo "Checking MySQL connectivity..."
"${MYSQL[@]}" -e "SELECT VERSION() AS mysql_version;"

echo "Temporarily increasing server-side import limits..."
"${MYSQL[@]}" -e "
SET GLOBAL max_allowed_packet = 1073741824;
SET GLOBAL net_read_timeout = 600;
SET GLOBAL net_write_timeout = 600;
"

echo "Recreating clean target database..."
"${MYSQL[@]}" -e "
DROP DATABASE IF EXISTS \`$TARGET_DB\`;
CREATE DATABASE \`$TARGET_DB\`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
"

echo "Starting restore using ONE persistent mysql client session..."
echo "This is important because LOCK TABLES state is connection-specific."

# Using unzip -p avoids creating a 4+ GB temporary .sql file.
# pipefail makes this script fail if either unzip or mysql fails.
unzip -p "$SOURCE_ZIP" | "${MYSQL[@]}" "$TARGET_DB"

echo
echo "Restore completed. Running basic validation..."

"${MYSQL[@]}" "$TARGET_DB" -e "
SELECT DATABASE() AS restored_database;
SHOW TABLES LIKE 'users';
SHOW TABLES LIKE 'victim';
SHOW TABLES LIKE 'incident_data';
SHOW TABLES LIKE 'actions';
SHOW TABLES LIKE 'services';
SHOW TABLES LIKE 'action_services';
SHOW TABLES LIKE 'password_resets';
"

echo
echo "Row counts:"
"${MYSQL[@]}" "$TARGET_DB" -e "
SELECT 'victim' AS table_name, COUNT(*) AS row_count FROM victim
UNION ALL
SELECT 'incident_data', COUNT(*) FROM incident_data
UNION ALL
SELECT 'actions', COUNT(*) FROM actions
UNION ALL
SELECT 'services', COUNT(*) FROM services;
"

echo
echo "Restore validation finished."
