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

readonly MODE="${1:-precheck}"
readonly SOURCE_DB="aizhct"
readonly TARGET_DB="aizhct_school"
readonly PHP_BIN="/usr/local/php7.3/bin/php"
readonly CONFIG_FILE="/workspace/wwwroot/zhct_school/project/application/config/prod/zhct_school/database.php"
readonly LOG_DIR="/workspace/wwwroot/log/aizhct_school"

case "$MODE" in
  precheck|copy|postcheck) ;;
  *)
    echo "Unsupported mode: $MODE" >&2
    exit 64
    ;;
esac

test -f "$CONFIG_FILE"
command -v mysql >/dev/null
command -v mysqldump >/dev/null

eval "$("$PHP_BIN" -r '
$config = [];
require $argv[1];
$ai = $config["ai_zhct"] ?? [];
foreach (["hostname", "username", "password"] as $required) {
    if (!isset($ai[$required]) || $ai[$required] === "") {
        fwrite(STDERR, "Missing AI database configuration: {$required}\n");
        exit(1);
    }
}
echo "AI_DB_HOST=" . escapeshellarg($ai["hostname"]) . PHP_EOL;
echo "AI_DB_PORT=" . escapeshellarg($ai["hostport"] ?? "3306") . PHP_EOL;
echo "AI_DB_USER=" . escapeshellarg($ai["username"]) . PHP_EOL;
echo "AI_DB_PASS=" . escapeshellarg($ai["password"]) . PHP_EOL;
' "$CONFIG_FILE")"

trap 'unset AI_DB_HOST AI_DB_PORT AI_DB_USER AI_DB_PASS MYSQL_PWD' EXIT

mysql_query() {
  MYSQL_PWD="$AI_DB_PASS" mysql \
    --protocol=TCP \
    --connect-timeout=10 \
    --default-character-set=utf8mb4 \
    -h "$AI_DB_HOST" \
    -P "$AI_DB_PORT" \
    -u "$AI_DB_USER" \
    -N -B \
    -e "$1"
}

schema_exists() {
  mysql_query "SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name='${1}';"
}

source_precheck() {
  echo "PRECHECK_AT=$(date '+%F %T %z')"
  echo "MYSQL_VERSION=$(mysql_query 'SELECT VERSION();')"
  echo "SOURCE_EXISTS=$(schema_exists "$SOURCE_DB")"
  echo "TARGET_EXISTS=$(schema_exists "$TARGET_DB")"

  mysql_query "
SELECT CONCAT('SOURCE_SCHEMA|', schema_name, '|', default_character_set_name, '|', default_collation_name)
FROM information_schema.schemata
WHERE schema_name='${SOURCE_DB}';
SELECT CONCAT('SOURCE_OBJECTS|', table_type, '|', COUNT(*))
FROM information_schema.tables
WHERE table_schema='${SOURCE_DB}'
GROUP BY table_type
ORDER BY table_type;
SELECT CONCAT('SOURCE_NON_INNODB|', COUNT(*))
FROM information_schema.tables
WHERE table_schema='${SOURCE_DB}'
  AND table_type='BASE TABLE'
  AND engine <> 'InnoDB';
SELECT CONCAT('SOURCE_TRIGGERS|', COUNT(*))
FROM information_schema.triggers
WHERE trigger_schema='${SOURCE_DB}';
SELECT CONCAT('SOURCE_ROUTINES|', COUNT(*))
FROM information_schema.routines
WHERE routine_schema='${SOURCE_DB}';
SELECT CONCAT('SOURCE_EVENTS|', COUNT(*))
FROM information_schema.events
WHERE event_schema='${SOURCE_DB}';
SELECT CONCAT('SOURCE_LOGICAL_BYTES|', COALESCE(SUM(data_length + index_length), 0))
FROM information_schema.tables
WHERE table_schema='${SOURCE_DB}';"
}

postcheck() {
  echo "POSTCHECK_AT=$(date '+%F %T %z')"
  mysql_query "
SELECT CONCAT('TARGET_SCHEMA|', schema_name, '|', default_character_set_name, '|', default_collation_name)
FROM information_schema.schemata
WHERE schema_name='${TARGET_DB}';
SELECT CONCAT('TARGET_OBJECTS|', table_type, '|', COUNT(*))
FROM information_schema.tables
WHERE table_schema='${TARGET_DB}'
GROUP BY table_type
ORDER BY table_type;
SELECT CONCAT('TARGET_TRIGGERS|', COUNT(*))
FROM information_schema.triggers
WHERE trigger_schema='${TARGET_DB}';
SELECT CONCAT('TARGET_ROUTINES|', COUNT(*))
FROM information_schema.routines
WHERE routine_schema='${TARGET_DB}';
SELECT CONCAT('TARGET_EVENTS|', COUNT(*))
FROM information_schema.events
WHERE event_schema='${TARGET_DB}';
SELECT CONCAT('TARGET_LOGICAL_BYTES|', COALESCE(SUM(data_length + index_length), 0))
FROM information_schema.tables
WHERE table_schema='${TARGET_DB}';
SELECT CONCAT('MISSING_TARGET_OBJECTS|', COUNT(*))
FROM information_schema.tables s
LEFT JOIN information_schema.tables t
  ON t.table_schema='${TARGET_DB}'
 AND t.table_name=s.table_name
 AND t.table_type=s.table_type
WHERE s.table_schema='${SOURCE_DB}'
  AND t.table_name IS NULL;
SELECT CONCAT('EXTRA_TARGET_OBJECTS|', COUNT(*))
FROM information_schema.tables t
LEFT JOIN information_schema.tables s
  ON s.table_schema='${SOURCE_DB}'
 AND s.table_name=t.table_name
 AND s.table_type=t.table_type
WHERE t.table_schema='${TARGET_DB}'
  AND s.table_name IS NULL;"

  MYSQL_PWD="$AI_DB_PASS" AI_DB_HOST="$AI_DB_HOST" AI_DB_PORT="$AI_DB_PORT" AI_DB_USER="$AI_DB_USER" \
    "$PHP_BIN" -r '
$source = "aizhct";
$target = "aizhct_school";
$dsn = "mysql:host=" . getenv("AI_DB_HOST") . ";port=" . getenv("AI_DB_PORT") . ";charset=utf8mb4";
$pdo = new PDO($dsn, getenv("AI_DB_USER"), getenv("MYSQL_PWD"), [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$tables = $pdo->query("SELECT table_name FROM information_schema.tables WHERE table_schema=" . $pdo->quote($source) . " AND table_type=\"BASE TABLE\" ORDER BY table_name")->fetchAll(PDO::FETCH_COLUMN);
$mismatches = [];
$sourceTotal = 0;
$targetTotal = 0;
foreach ($tables as $table) {
    $quoted = "`" . str_replace("`", "``", $table) . "`";
    $sourceCount = (int)$pdo->query("SELECT COUNT(*) FROM `{$source}`.{$quoted}")->fetchColumn();
    $targetCount = (int)$pdo->query("SELECT COUNT(*) FROM `{$target}`.{$quoted}")->fetchColumn();
    $sourceTotal += $sourceCount;
    $targetTotal += $targetCount;
    if ($sourceCount !== $targetCount) {
        $mismatches[] = $table . ":" . $sourceCount . "->" . $targetCount;
    }
}
echo "EXACT_SOURCE_ROWS={$sourceTotal}\n";
echo "EXACT_TARGET_ROWS={$targetTotal}\n";
echo "ROW_COUNT_MISMATCH_TABLES=" . count($mismatches) . "\n";
foreach (array_slice($mismatches, 0, 20) as $mismatch) {
    echo "ROW_COUNT_MISMATCH|{$mismatch}\n";
}
' 
}

source_precheck

if [ "$MODE" = "precheck" ]; then
  exit 0
fi

if [ "$MODE" = "postcheck" ]; then
  test "$(schema_exists "$TARGET_DB")" = "1"
  postcheck
  exit 0
fi

test "$(schema_exists "$SOURCE_DB")" = "1"
test "$(schema_exists "$TARGET_DB")" = "0"

source_schema_row="$(mysql_query "SELECT CONCAT(default_character_set_name, '|', default_collation_name) FROM information_schema.schemata WHERE schema_name='${SOURCE_DB}';")"
source_charset="${source_schema_row%%|*}"
source_collation="${source_schema_row#*|}"

[[ "$source_charset" =~ ^[A-Za-z0-9_]+$ ]]
[[ "$source_collation" =~ ^[A-Za-z0-9_]+$ ]]

mkdir -p "$LOG_DIR"
chmod 755 "$LOG_DIR"
readonly LOG_FILE="$LOG_DIR/database-copy-$(date '+%Y%m%d-%H%M%S').log"
touch "$LOG_FILE"
chmod 600 "$LOG_FILE"
exec > >(tee -a "$LOG_FILE") 2>&1

echo "COPY_STARTED_AT=$(date '+%F %T %z')"
echo "SOURCE_DB=$SOURCE_DB"
echo "TARGET_DB=$TARGET_DB"
echo "SOURCE_CHARSET=$source_charset"
echo "SOURCE_COLLATION=$source_collation"

mysql_query "CREATE DATABASE \`${TARGET_DB}\` DEFAULT CHARACTER SET ${source_charset} DEFAULT COLLATE ${source_collation};"
echo "TARGET_CREATED=$(schema_exists "$TARGET_DB")"

set +e
MYSQL_PWD="$AI_DB_PASS" mysqldump \
  --protocol=TCP \
  --host="$AI_DB_HOST" \
  --port="$AI_DB_PORT" \
  --user="$AI_DB_USER" \
  --single-transaction \
  --quick \
  --skip-lock-tables \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  --set-gtid-purged=OFF \
  "$SOURCE_DB" | \
MYSQL_PWD="$AI_DB_PASS" mysql \
  --protocol=TCP \
  --connect-timeout=10 \
  --default-character-set=utf8mb4 \
  -h "$AI_DB_HOST" \
  -P "$AI_DB_PORT" \
  -u "$AI_DB_USER" \
  "$TARGET_DB"
copy_status=("${PIPESTATUS[@]}")
set -e

echo "DUMP_EXIT=${copy_status[0]}"
echo "IMPORT_EXIT=${copy_status[1]}"
test "${copy_status[0]}" -eq 0
test "${copy_status[1]}" -eq 0

echo "COPY_FINISHED_AT=$(date '+%F %T %z')"
postcheck
echo "COPY_VERDICT=PASS"