#!/usr/bin/env bash

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
TRANSPORT_LIB="${SCRIPT_DIR}/lib/transport.sh"
DEFAULT_REMOTE_DIR="/opt/security-center/enterprise/rendered"

# shellcheck disable=SC1090
source "${TRANSPORT_LIB}"

usage() {
  cat <<'EOF'
CAASM DNS duplicate forensics helper.

This helper runs read-only MySQL forensics against the enterprise compose stack
on a remote host over SSM by default. It is intended for production evidence
collection before any DomainAsset schema or identity changes.

Usage:
  ./scripts/enterprise/inspect-caasm-dns-duplicates.sh \
    --instance-id i-0123456789abcdef0 \
    --region eu-west-2 \
    --profile my-sso-profile \
    [--domain-name example.internal]

Options:
  --transport <mode>      Optional. ssm (default) or ssh.
  --instance-id <id>      Required for ssm unless provided by caller/env.
  --host <user@host>      Required for ssh.
  --remote-dir <path>     Optional. Default: /opt/security-center/enterprise/rendered
  --identity-file <path>  Optional. SSH private key for ssh mode.
  --region <name>         AWS region for ssm mode.
  --profile <name>        AWS CLI profile override.
  --domain-name <name>    Optional. Domain name to run detailed parent/DNS evidence for.
  --top-limit <n>         Optional. Default: 50. Limit for global duplicate sample query.
  --database <name>       Optional. Override MySQL database name. Default resolves from .env.runtime or security_center.
  --dry-run               Print remote actions without executing them.
  --help                  Show this help.
EOF
}

die() {
  echo "ERROR: $*" >&2
  exit 1
}

trim() {
  local value="${1:-}"
  value="${value#"${value%%[![:space:]]*}"}"
  value="${value%"${value##*[![:space:]]}"}"
  printf '%s' "${value}"
}

sql_escape_literal() {
  local value="${1:-}"
  value="${value//\\/\\\\}"
  value="${value//\'/\'\'}"
  printf '%s' "${value}"
}

build_remote_script() {
  local remote_dir="$1"
  local database="$2"
  local domain_name="$3"
  local top_limit="$4"

  python3 - "$remote_dir" "$database" "$domain_name" "$top_limit" <<'PY'
import shlex
import sys

remote_dir, database, domain_name, top_limit = sys.argv[1:5]

def q(value: str) -> str:
    return shlex.quote(value)

if database:
    db_assignment = f'db_name={q(database)}'
else:
    db_assignment = """
db_name="$(awk -F= '/^SC_MYSQL_DATABASE=/{print $2}' .env.runtime | tail -n1)"
db_name="${db_name:-security_center}"
""".strip()

domain_filter = ""
domain_top_filter = ""
domain_domain_filter = ""
if domain_name:
    escaped = domain_name.replace("\\", "\\\\").replace("'", "''")
    domain_filter = f"  AND da.domain_name = '{escaped}'\n"
    domain_top_filter = f"  AND da.domain_name = '{escaped}'\n"
    domain_domain_filter = f"WHERE da.domain_name = '{escaped}'\n"

script = f"""
set -euo pipefail
cd {q(remote_dir)}
test -f .env.runtime
test -f docker-compose.enterprise.yml
{db_assignment}
top_limit={q(top_limit)}

compose_cmd=(docker compose --env-file .env.runtime -f docker-compose.enterprise.yml)

mysql_table() {{
  local sql="$1"
  "${{compose_cmd[@]}}" exec -T mysql sh -lc 'set -euo pipefail; MYSQL_PWD="${{MYSQL_ROOT_PASSWORD}}" mysql -uroot -t --raw "$1" -e "$2"' -- "$db_name" "$sql"
}}

echo '== MySQL database =='
echo "$db_name"
echo

echo '== dns_records identity index =='
mysql_table "SHOW INDEX FROM dns_records WHERE Key_name = 'idx_dns_records_identity';"
echo

echo '== global top duplicate-looking DNS identities =='
mysql_table "SELECT
  da.domain_name,
  dr.name_key,
  dr.type_key,
  dr.set_identifier_key,
  dr.region_key,
  dr.failover_key,
  COUNT(*) AS total_rows,
  COUNT(DISTINCT dr.domain_asset_id) AS distinct_parent_count,
  COUNT(DISTINCT da.zone_id) AS distinct_zone_count,
  GROUP_CONCAT(DISTINCT da.zone_type ORDER BY da.zone_type) AS zone_types
FROM dns_records dr
JOIN domain_assets da ON da.id = dr.domain_asset_id
LEFT JOIN assets a ON a.id = da.asset_id
WHERE dr.deleted_at IS NULL
  AND da.deleted_at IS NULL
  AND (a.id IS NULL OR a.deleted_at IS NULL)
  AND (da.status IS NULL OR da.status <> 'inactive')
  AND (a.id IS NULL OR a.status <> 'inactive')
{domain_top_filter}GROUP BY
  da.domain_name,
  dr.name_key,
  dr.type_key,
  dr.set_identifier_key,
  dr.region_key,
  dr.failover_key
HAVING COUNT(*) > 1
ORDER BY total_rows DESC, distinct_parent_count DESC, da.domain_name
LIMIT $top_limit;"
echo

if [[ -n {q(domain_name)} ]]; then
  echo '== domain_assets for target domain =='
  mysql_table "SELECT
  da.id AS domain_asset_id,
  da.domain_name,
  da.domain_class,
  da.zone_id,
  da.zone_type,
  da.asset_id,
  a.native_id AS asset_native_id,
  a.service,
  a.status AS asset_status,
  da.status AS domain_status,
  da.last_seen_at,
  da.deleted_at
FROM domain_assets da
LEFT JOIN assets a ON a.id = da.asset_id
{domain_domain_filter}ORDER BY da.domain_class, da.zone_id, da.asset_id, da.id;"
  echo

  echo '== dns_records expanded for target domain =='
  mysql_table "SELECT
  dr.id AS dns_record_id,
  dr.domain_asset_id,
  da.domain_name,
  da.domain_class,
  da.zone_id,
  da.zone_type,
  da.asset_id,
  dr.name,
  dr.type,
  dr.name_key,
  dr.type_key,
  dr.set_identifier_key,
  dr.region_key,
  dr.failover_key,
  dr.status AS dns_status,
  dr.is_public,
  dr.updated_at,
  dr.deleted_at
FROM dns_records dr
JOIN domain_assets da ON da.id = dr.domain_asset_id
LEFT JOIN assets a ON a.id = da.asset_id
WHERE 1=1
{domain_filter}ORDER BY da.zone_id, da.asset_id, dr.domain_asset_id, dr.name_key, dr.type_key, dr.id;"
  echo

  echo '== duplicate split verdict for target domain =='
  mysql_table "SELECT
  da.domain_name,
  dr.name_key,
  dr.type_key,
  dr.set_identifier_key,
  dr.region_key,
  dr.failover_key,
  COUNT(*) AS total_rows,
  COUNT(DISTINCT dr.domain_asset_id) AS distinct_parent_count,
  GROUP_CONCAT(DISTINCT da.zone_id ORDER BY da.zone_id) AS zone_ids,
  GROUP_CONCAT(DISTINCT da.asset_id ORDER BY da.asset_id) AS asset_ids,
  GROUP_CONCAT(DISTINCT dr.domain_asset_id ORDER BY dr.domain_asset_id) AS domain_asset_ids
FROM dns_records dr
JOIN domain_assets da ON da.id = dr.domain_asset_id
LEFT JOIN assets a ON a.id = da.asset_id
WHERE dr.deleted_at IS NULL
  AND da.deleted_at IS NULL
  AND (a.id IS NULL OR a.deleted_at IS NULL)
  AND (da.status IS NULL OR da.status <> 'inactive')
  AND (a.id IS NULL OR a.status <> 'inactive')
{domain_filter}GROUP BY
  da.domain_name,
  dr.name_key,
  dr.type_key,
  dr.set_identifier_key,
  dr.region_key,
  dr.failover_key
HAVING COUNT(*) > 1
ORDER BY total_rows DESC, dr.name_key, dr.type_key;"
  echo

  echo '== same-parent true duplicates for target domain =='
  mysql_table "SELECT
  da.domain_name,
  da.zone_id,
  da.asset_id,
  dr.domain_asset_id,
  da.domain_class,
  dr.name_key,
  dr.type_key,
  dr.set_identifier_key,
  dr.region_key,
  dr.failover_key,
  COUNT(*) AS cnt
FROM dns_records dr
JOIN domain_assets da ON da.id = dr.domain_asset_id
LEFT JOIN assets a ON a.id = da.asset_id
WHERE dr.deleted_at IS NULL
  AND da.deleted_at IS NULL
  AND (a.id IS NULL OR a.deleted_at IS NULL)
  AND (da.status IS NULL OR da.status <> 'inactive')
  AND (a.id IS NULL OR a.status <> 'inactive')
{domain_filter}GROUP BY
  da.domain_name,
  da.zone_id,
  da.asset_id,
  dr.domain_asset_id,
  da.domain_class,
  dr.name_key,
  dr.type_key,
  dr.set_identifier_key,
  dr.region_key,
  dr.failover_key
HAVING COUNT(*) > 1
ORDER BY cnt DESC;"
  echo

  echo '== stale/inactive parent visibility for target domain =='
  mysql_table "SELECT
  da.id AS domain_asset_id,
  da.domain_name,
  da.domain_class,
  da.zone_id,
  da.status AS domain_status,
  da.deleted_at AS domain_deleted_at,
  a.id AS asset_id,
  a.status AS asset_status,
  a.deleted_at AS asset_deleted_at,
  COUNT(dr.id) AS dns_rows
FROM domain_assets da
LEFT JOIN assets a ON a.id = da.asset_id
LEFT JOIN dns_records dr ON dr.domain_asset_id = da.id AND dr.deleted_at IS NULL
{domain_domain_filter}GROUP BY
  da.id, da.domain_name, da.domain_class, da.zone_id,
  da.status, da.deleted_at,
  a.id, a.status, a.deleted_at
ORDER BY da.zone_id, da.id;"
fi
"""

print(script.strip())
PY
}

TRANSPORT="ssm"
INSTANCE_ID=""
REMOTE_HOST=""
REMOTE_DIR="${DEFAULT_REMOTE_DIR}"
IDENTITY_FILE=""
AWS_REGION_NAME="${AWS_REGION:-${AWS_DEFAULT_REGION:-}}"
AWS_PROFILE_NAME="${AWS_PROFILE:-}"
DOMAIN_NAME=""
TOP_LIMIT="50"
MYSQL_DATABASE=""
DRY_RUN="false"

while (($# > 0)); do
  case "$1" in
    --transport)
      TRANSPORT="$2"
      shift 2
      ;;
    --instance-id)
      INSTANCE_ID="$2"
      shift 2
      ;;
    --host)
      REMOTE_HOST="$2"
      shift 2
      ;;
    --remote-dir)
      REMOTE_DIR="$2"
      shift 2
      ;;
    --identity-file)
      IDENTITY_FILE="$2"
      shift 2
      ;;
    --region)
      AWS_REGION_NAME="$2"
      shift 2
      ;;
    --profile)
      AWS_PROFILE_NAME="$2"
      shift 2
      ;;
    --domain-name)
      DOMAIN_NAME="$(trim "$2")"
      shift 2
      ;;
    --top-limit)
      TOP_LIMIT="$2"
      shift 2
      ;;
    --database)
      MYSQL_DATABASE="$(trim "$2")"
      shift 2
      ;;
    --dry-run)
      DRY_RUN="true"
      shift
      ;;
    --help|-h)
      usage
      exit 0
      ;;
    *)
      die "unknown argument: $1"
      ;;
  esac
done

case "${TRANSPORT}" in
  ssm)
    [[ -n "${INSTANCE_ID}" ]] || die "--instance-id is required for --transport ssm"
    [[ -n "${AWS_REGION_NAME}" ]] || die "--region is required for --transport ssm"
    ;;
  ssh)
    [[ -n "${REMOTE_HOST}" ]] || die "--host is required for --transport ssh"
    ;;
  *)
    die "unsupported transport: ${TRANSPORT}"
    ;;
esac

[[ "${TOP_LIMIT}" =~ ^[0-9]+$ ]] || die "--top-limit must be an integer"

SC_TRANSPORT_MODE="${TRANSPORT}"
SC_TRANSPORT_DRY_RUN="${DRY_RUN}"
SC_TRANSPORT_REMOTE_HOST="${REMOTE_HOST}"
SC_TRANSPORT_INSTANCE_ID="${INSTANCE_ID}"
SC_TRANSPORT_IDENTITY_FILE="${IDENTITY_FILE}"
SC_TRANSPORT_AWS_REGION="${AWS_REGION_NAME}"
SC_TRANSPORT_AWS_PROFILE="${AWS_PROFILE_NAME}"
sc_transport_init

REMOTE_SCRIPT="$(build_remote_script "${REMOTE_DIR}" "${MYSQL_DATABASE}" "${DOMAIN_NAME}" "${TOP_LIMIT}")"
sc_transport_run "bash -lc $(printf '%q' "${REMOTE_SCRIPT}")"