#!/bin/sh
set -eu

embedded_tidb_log=/tmp/nexuscrm-embedded-tidb.log

tidb_tls_enabled() {
  case "$(printf '%s' "${TIDB_TLS:-}" | tr '[:upper:]' '[:lower:]')" in
    1|true|yes|on) return 0 ;;
    *) return 1 ;;
  esac
}

bool_enabled() {
  case "$(printf '%s' "${1:-}" | tr '[:upper:]' '[:lower:]')" in
    1|true|yes|on) return 0 ;;
    *) return 1 ;;
  esac
}

run_mysql() {
  if tidb_tls_enabled; then
    if mysql --help 2>&1 | grep -q -- '--ssl-mode'; then
      set -- --ssl-mode=REQUIRED "$@"
    else
      set -- --ssl "$@"
    fi
  fi

  if [ -n "${TIDB_PASSWORD:-}" ]; then
    mysql -h"${TIDB_HOST}" -P"${TIDB_PORT}" -u"${TIDB_USER}" -p"${TIDB_PASSWORD}" "$@"
  else
    mysql -h"${TIDB_HOST}" -P"${TIDB_PORT}" -u"${TIDB_USER}" "$@"
  fi
}

sql_escape() {
  printf '%s' "$1" | sed -e 's/\\/\\\\/g' -e "s/'/''/g"
}

wait_for_embedded_tidb() {
  attempt=0
  until wget -qO- "http://127.0.0.1:${NEXUSCRM_EMBEDDED_TIDB_STATUS_PORT}/status" >/dev/null 2>&1; do
    attempt=$((attempt + 1))
    if [ "${attempt}" -ge 120 ]; then
      echo "Embedded TiDB did not become ready in time" >&2
      tail -n 200 "${embedded_tidb_log}" >&2 || true
      exit 1
    fi
    sleep 1
  done
}

embedded_pack_is_active() {
  pack_name="$1"
  count="$(
    run_mysql -N -B \
      "${TIDB_DATABASE}" \
      -e "SELECT COUNT(*) FROM _System_InstalledPack WHERE Name='${pack_name}' AND Status='active' AND IsDeleted=0;"
  )"
  [ "${count}" != "0" ]
}

embedded_pack_is_current() {
  pack_name="$1"
  /usr/local/bin/nexuscrm-setup verify-pack "${pack_name}" >/tmp/"${pack_name}"-verify.log 2>&1
}

install_bootstrap_pack_if_needed() {
  bootstrap_pack_name="${NEXUSCRM_BOOTSTRAP_PACK_NAME:-}"
  bootstrap_pack_source="${NEXUSCRM_BOOTSTRAP_PACK_SOURCE:-}"

  if [ -n "${bootstrap_pack_source}" ]; then
    if [ -z "${bootstrap_pack_name}" ]; then
      echo "NEXUSCRM_BOOTSTRAP_PACK_SOURCE requires NEXUSCRM_BOOTSTRAP_PACK_NAME" >&2
      exit 1
    fi
    if ! embedded_pack_is_active "${bootstrap_pack_name}" || ! embedded_pack_is_current "${bootstrap_pack_name}"; then
      echo "Installing bootstrap pack from ${bootstrap_pack_source}..."
      /usr/local/bin/nexuscrm-setup install-pack --source "${bootstrap_pack_source}"
    fi
    return
  fi

  if [ "${NEXUSCRM_INSTALL_STANDARD_PACK}" = "1" ]; then
    if ! embedded_pack_is_active "salesforce-crm-standard" || ! embedded_pack_is_current "salesforce-crm-standard"; then
      echo "Installing embedded standard pack..."
      /usr/local/bin/nexuscrm-setup install-pack --source official_repo:salesforce-crm-standard
    fi
  fi
}

reapply_bootstrap_pack_after_apply_packs_if_requested() {
  if [ "${NEXUSCRM_REAPPLY_BOOTSTRAP_PACK_AFTER_APPLY_PACKS:-0}" != "1" ]; then
    return
  fi

  bootstrap_pack_name="${NEXUSCRM_BOOTSTRAP_PACK_NAME:-}"
  bootstrap_pack_source="${NEXUSCRM_BOOTSTRAP_PACK_SOURCE:-}"
  if [ -z "${bootstrap_pack_source}" ]; then
    return
  fi
  if [ -z "${bootstrap_pack_name}" ]; then
    echo "NEXUSCRM_REAPPLY_BOOTSTRAP_PACK_AFTER_APPLY_PACKS requires NEXUSCRM_BOOTSTRAP_PACK_NAME" >&2
    exit 1
  fi
  if ! embedded_pack_is_active "${bootstrap_pack_name}"; then
    echo "Cannot reapply inactive bootstrap pack ${bootstrap_pack_name}" >&2
    exit 1
  fi

  echo "Reapplying bootstrap pack from ${bootstrap_pack_source} after embedded reconciliation..."
  /usr/local/bin/nexuscrm-setup reapply-pack --source "${bootstrap_pack_source}"
}

reapply_active_bootstrap_pack_before_apply_if_requested() {
  if [ "${NEXUSCRM_REAPPLY_BOOTSTRAP_PACK_BEFORE_APPLY:-1}" != "1" ]; then
    return
  fi

  bootstrap_pack_name="${NEXUSCRM_BOOTSTRAP_PACK_NAME:-}"
  bootstrap_pack_source="${NEXUSCRM_BOOTSTRAP_PACK_SOURCE:-}"
  if [ -z "${bootstrap_pack_source}" ]; then
    return
  fi
  if [ -z "${bootstrap_pack_name}" ]; then
    echo "NEXUSCRM_REAPPLY_BOOTSTRAP_PACK_BEFORE_APPLY requires NEXUSCRM_BOOTSTRAP_PACK_NAME" >&2
    exit 1
  fi
  if ! embedded_pack_is_active "${bootstrap_pack_name}"; then
    return
  fi

  echo "Reapplying active bootstrap pack from ${bootstrap_pack_source} before setup apply..."
  /usr/local/bin/nexuscrm-setup reapply-pack --source "${bootstrap_pack_source}" --verify-pack
}

rebind_bootstrap_pack_source_before_apply_if_needed() {
  bootstrap_pack_name="${NEXUSCRM_BOOTSTRAP_PACK_NAME:-}"
  bootstrap_pack_source="${NEXUSCRM_BOOTSTRAP_PACK_SOURCE:-}"
  source_revision="${NEXUSCRM_BOOTSTRAP_PACK_SOURCE_REVISION:-}"

  case "${bootstrap_pack_source}" in
    local_path:*)
      bootstrap_pack_source_uri="${bootstrap_pack_source#local_path:}"
      ;;
    *)
      return
      ;;
  esac

  if [ -z "${bootstrap_pack_name}" ]; then
    echo "NEXUSCRM_BOOTSTRAP_PACK_SOURCE requires NEXUSCRM_BOOTSTRAP_PACK_NAME" >&2
    exit 1
  fi

  installed_pack_table_count="$(
    run_mysql -N -B \
      "${TIDB_DATABASE}" \
      -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='_System_InstalledPack';"
  )"
  if [ "${installed_pack_table_count}" = "0" ]; then
    return
  fi

  echo "Rebinding active bootstrap pack source to ${bootstrap_pack_source} before setup apply..."
  cat >/tmp/rebind_bootstrap_pack_source.sql <<EOF
UPDATE _System_InstalledPack
   SET SourceKind = 'local_path',
       SourceURI = '$(sql_escape "${bootstrap_pack_source_uri}")',
       SourceRevision = CASE
         WHEN '$(sql_escape "${source_revision}")' = '' THEN SourceRevision
         ELSE '$(sql_escape "${source_revision}")'
       END,
       InstallSource = 'local_path:$(sql_escape "${bootstrap_pack_source_uri}")',
       LastModifiedDate = UTC_TIMESTAMP()
 WHERE Name = '$(sql_escape "${bootstrap_pack_name}")'
   AND Status = 'active'
   AND COALESCE(IsDeleted, 0) = 0;
EOF
  run_mysql "${TIDB_DATABASE}" </tmp/rebind_bootstrap_pack_source.sql
}

stack_lock_file() {
  printf '%s\n' "${NEXUSCRM_STACK_LOCK_FILE:-/stack-runtime/stack.lock.json}"
}

stack_lock_convergence_enabled() {
  bool_enabled "${NEXUSCRM_STACK_LOCK_CONVERGENCE:-0}" || return 1
  stack_lock="$(stack_lock_file)"
  if [ ! -f "${stack_lock}" ]; then
    echo "NEXUSCRM_STACK_LOCK_CONVERGENCE=1 but stack lock is missing: ${stack_lock}" >&2
    exit 1
  fi
  return 0
}

stack_pack_entries() {
  jq -r '.packs[]?' "$(stack_lock_file)"
}

stack_pack_entry_is_official_repo() {
  case "$1" in
    official_repo:*) return 0 ;;
    *) return 1 ;;
  esac
}

stack_pack_entry_runtime_path() {
  case "$1" in
    /*) printf '%s\n' "$1" ;;
    *) printf '/%s\n' "$1" ;;
  esac
}

stack_pack_entry_name() {
  stack_pack_entry="$1"
  if stack_pack_entry_is_official_repo "${stack_pack_entry}"; then
    printf '%s\n' "${stack_pack_entry#official_repo:}"
    return
  fi

  stack_pack_json="$(stack_pack_entry_runtime_path "${stack_pack_entry}")/pack.json"
  if [ ! -f "${stack_pack_json}" ]; then
    echo "stack lock pack is missing pack.json: ${stack_pack_json}" >&2
    exit 1
  fi

  stack_pack_name="$(jq -r '.name // empty' "${stack_pack_json}")"
  if [ -z "${stack_pack_name}" ]; then
    echo "stack lock pack has no name: ${stack_pack_json}" >&2
    exit 1
  fi
  printf '%s\n' "${stack_pack_name}"
}

stack_pack_entry_source() {
  stack_pack_entry="$1"
  if stack_pack_entry_is_official_repo "${stack_pack_entry}"; then
    printf '%s\n' "${stack_pack_entry}"
    return
  fi

  printf 'local_path:%s\n' "$(stack_pack_entry_runtime_path "${stack_pack_entry}")"
}

stack_pack_entry_dir() {
  stack_pack_entry="$1"
  if stack_pack_entry_is_official_repo "${stack_pack_entry}"; then
    stack_official_root="${NEXUSCRM_OFFICIAL_APP_PACKS_ROOT:-/nexuscrm-app-packs}"
    printf '%s/%s\n' "${stack_official_root%/}" "${stack_pack_entry#official_repo:}"
    return
  fi

  stack_pack_entry_runtime_path "${stack_pack_entry}"
}

stack_pack_entry_source_revision() {
  stack_pack_entry="$1"
  if stack_pack_entry_is_official_repo "${stack_pack_entry}"; then
    printf '%s\n' "${OFFICIAL_APP_PACKS_REF:-}"
    return
  fi

  printf '%s\n' "${DOWNSTREAM_REF:-}"
}

installed_pack_table_exists() {
  installed_pack_table_count="$(
    run_mysql -N -B \
      "${TIDB_DATABASE}" \
      -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='_System_InstalledPack';"
  )"
  [ "${installed_pack_table_count}" != "0" ]
}

installed_pack_status() {
  installed_pack_name="$1"
  if ! installed_pack_table_exists; then
    return
  fi

  run_mysql -N -B \
    "${TIDB_DATABASE}" \
    -e "SELECT Status FROM _System_InstalledPack WHERE Name='$(sql_escape "${installed_pack_name}")' AND COALESCE(IsDeleted, 0) = 0 ORDER BY CASE Status WHEN 'active' THEN 0 WHEN 'installing' THEN 1 WHEN 'failed' THEN 2 ELSE 3 END, LastModifiedDate DESC LIMIT 1;"
}

installed_pack_column_exists() {
  installed_pack_column="$1"
  installed_pack_column_count="$(
    run_mysql -N -B \
      "${TIDB_DATABASE}" \
      -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='_System_InstalledPack' AND column_name='$(sql_escape "${installed_pack_column}")';"
  )"
  [ "${installed_pack_column_count}" != "0" ]
}

installed_pack_runtime_boundary() {
  installed_pack_name="$1"
  run_mysql -N -B \
    "${TIDB_DATABASE}" \
    -e "SELECT JSON_OBJECT('category', COALESCE(Category, ''), 'manifest_format_version', COALESCE(ManifestFormatVersion, ''), 'source_revision', COALESCE(SourceRevision, ''), 'applied_payload_hash', COALESCE(AppliedPayloadHash, '')) FROM _System_InstalledPack WHERE Name='$(sql_escape "${installed_pack_name}")' AND Status IN ('active', 'installing', 'failed') AND COALESCE(IsDeleted, 0) = 0 ORDER BY CASE Status WHEN 'active' THEN 0 WHEN 'installing' THEN 1 WHEN 'failed' THEN 2 ELSE 3 END, LastModifiedDate DESC LIMIT 1;"
}

sql_value_or_null() {
  sql_value="$1"
  sql_valid="$2"
  if [ "${sql_valid}" = "true" ] && [ -n "${sql_value}" ]; then
    printf "'%s'" "$(sql_escape "${sql_value}")"
    return
  fi
  printf 'NULL'
}

is_valid_pack_payload_hash_v1() {
  printf '%s' "$1" | grep -Eq '^nexus-pack-sha256-v1:[0-9a-f]{64}$'
}

derive_stale_pack_payload_hash() {
  current_hash="$1"
  if ! is_valid_pack_payload_hash_v1 "${current_hash}"; then
    echo "unexpected pack payload hash format: ${current_hash}" >&2
    exit 1
  fi
  hash_prefix="nexus-pack-sha256-v1:"
  hash_digest="${current_hash#${hash_prefix}}"
  hash_base="${hash_digest%?}"
  hash_last="${hash_digest#${hash_base}}"
  if [ "${hash_last}" = "0" ]; then
    hash_replacement="1"
  else
    hash_replacement="0"
  fi
  printf '%s%s%s\n' "${hash_prefix}" "${hash_base}" "${hash_replacement}"
}

sync_stack_runtime_pack_metadata_with_apply_state() {
  mark_applied="$1"
  stack_phase="$2"
  if ! installed_pack_table_exists; then
    return
  fi

  stack_pack_entries | while IFS= read -r stack_pack_entry; do
    if [ -z "${stack_pack_entry}" ]; then
      continue
    fi

    stack_pack_name="$(stack_pack_entry_name "${stack_pack_entry}")"
    stack_pack_dir="$(stack_pack_entry_dir "${stack_pack_entry}")"
    stack_status="$(installed_pack_status "${stack_pack_name}" || true)"
    if [ -z "${stack_status}" ]; then
      continue
    fi

    echo "Syncing stack runtime pack metadata (${stack_phase}): ${stack_pack_name}"
    boundary_json="$(installed_pack_runtime_boundary "${stack_pack_name}" || true)"
    pack_category="$(printf '%s' "${boundary_json}" | jq -r '.category // ""')"
    manifest_format_version="$(printf '%s' "${boundary_json}" | jq -r '.manifest_format_version // ""')"
    applied_payload_hash="$(printf '%s' "${boundary_json}" | jq -r '.applied_payload_hash // ""')"
    source_revision="$(stack_pack_entry_source_revision "${stack_pack_entry}")"

    metadata_file="/tmp/stack-pack-runtime-metadata.json"
    /usr/local/bin/nexuscrm-pack-runtime-metadata \
      "${stack_pack_dir}" \
      "${stack_pack_name}" \
      "${pack_category}" \
      "${manifest_format_version}" \
      "${source_revision}" >"${metadata_file}"

    payload_hash="$(jq -r '.payload_hash' "${metadata_file}")"
    if [ "${mark_applied}" = "1" ]; then
      applied_payload_hash_sql="'$(sql_escape "${payload_hash}")'"
    elif is_valid_pack_payload_hash_v1 "${applied_payload_hash}" && [ "${applied_payload_hash}" != "${payload_hash}" ]; then
      applied_payload_hash_sql="AppliedPayloadHash"
    else
      applied_payload_hash_sql="'$(sql_escape "$(derive_stale_pack_payload_hash "${payload_hash}")")'"
    fi

    pack_version="$(jq -r '.pack_version' "${metadata_file}")"
    computed_manifest_format_version="$(jq -r '.manifest_format_version' "${metadata_file}")"
    platform_compatibility="$(jq -r '.platform_compatibility // ""' "${metadata_file}")"
    platform_compatibility_valid="$(jq -r '.platform_compatibility_valid' "${metadata_file}")"
    payload_hash_algorithm="$(jq -r '.payload_hash_algorithm' "${metadata_file}")"
    source_kind="$(jq -r '.source_kind // ""' "${metadata_file}")"
    source_kind_valid="$(jq -r '.source_kind_valid' "${metadata_file}")"
    source_uri="$(jq -r '.source_uri // ""' "${metadata_file}")"
    source_uri_valid="$(jq -r '.source_uri_valid' "${metadata_file}")"
    computed_source_revision="$(jq -r '.source_revision // ""' "${metadata_file}")"
    source_revision_valid="$(jq -r '.source_revision_valid' "${metadata_file}")"
    if stack_pack_entry_is_official_repo "${stack_pack_entry}"; then
      source_kind="official_repo"
      source_kind_valid="true"
      source_uri="${stack_pack_entry#official_repo:}"
      source_uri_valid="true"
      computed_source_revision="${source_revision}"
      if [ -n "${computed_source_revision}" ]; then
        source_revision_valid="true"
      else
        source_revision_valid="false"
      fi
    fi
    runtime_object_spec_declarations="$(jq -r '.runtime_object_spec_declarations // "[]"' "${metadata_file}")"
    runtime_related_list_rules="$(jq -r '.runtime_related_list_rules // "[]"' "${metadata_file}")"
    runtime_search_scope_order="$(jq -r '.runtime_search_scope_order // "[]"' "${metadata_file}")"
    runtime_startup_shallow_declarations="$(jq -r '.runtime_startup_shallow_declarations // "[]"' "${metadata_file}")"

    runtime_search_all_default_fields_update=""
    if installed_pack_column_exists RuntimeSearchAllDefaultFields; then
      runtime_search_all_default_fields="$(jq -r '.runtime_search_all_default_fields // "[]"' "${metadata_file}")"
      runtime_search_all_default_fields_update="RuntimeSearchAllDefaultFields='$(sql_escape "${runtime_search_all_default_fields}")',"
    fi

    cat >/tmp/sync_stack_runtime_pack_metadata.sql <<EOF
UPDATE _System_InstalledPack
   SET Status='active',
       PackVersion='$(sql_escape "${pack_version}")',
       ManifestFormatVersion='$(sql_escape "${computed_manifest_format_version}")',
       PlatformCompatibility=$(sql_value_or_null "${platform_compatibility}" "${platform_compatibility_valid}"),
       DesiredPayloadHash='$(sql_escape "${payload_hash}")',
       AppliedPayloadHash=${applied_payload_hash_sql},
       PayloadHashAlgorithm='$(sql_escape "${payload_hash_algorithm}")',
       SourceKind=$(sql_value_or_null "${source_kind}" "${source_kind_valid}"),
       SourceURI=$(sql_value_or_null "${source_uri}" "${source_uri_valid}"),
       SourceRevision=$(sql_value_or_null "${computed_source_revision}" "${source_revision_valid}"),
       RuntimeObjectSpecDeclarations='$(sql_escape "${runtime_object_spec_declarations}")',
       RuntimeRelatedListRules='$(sql_escape "${runtime_related_list_rules}")',
       RuntimeSearchScopeOrder='$(sql_escape "${runtime_search_scope_order}")',
       ${runtime_search_all_default_fields_update}
       RuntimeStartupShallowDeclarations='$(sql_escape "${runtime_startup_shallow_declarations}")',
       LastError=NULL,
       LastAppliedDate=UTC_TIMESTAMP(),
       LastModifiedDate=UTC_TIMESTAMP()
 WHERE Name='$(sql_escape "${stack_pack_name}")'
   AND Status IN ('active', 'installing', 'failed')
   AND COALESCE(IsDeleted, 0) = 0;
EOF
    run_mysql "${TIDB_DATABASE}" </tmp/sync_stack_runtime_pack_metadata.sql
  done
}

sync_stack_runtime_pack_metadata_before_setup_apply() {
  sync_stack_runtime_pack_metadata_with_apply_state 0 before-setup-apply
}

sync_stack_runtime_pack_metadata_after_setup_apply() {
  sync_stack_runtime_pack_metadata_with_apply_state 1 after-setup-apply
}

rebind_stack_pack_source_identity() {
  stack_pack_entry="$1"
  stack_pack_name="$2"
  stack_status_predicate="$3"
  stack_source_revision="$(stack_pack_entry_source_revision "${stack_pack_entry}")"

  if stack_pack_entry_is_official_repo "${stack_pack_entry}"; then
    stack_source_kind="official_repo"
    stack_source_uri="${stack_pack_entry#official_repo:}"
    stack_install_source="${stack_pack_entry}"
  else
    stack_source_kind="local_path"
    stack_source_uri="$(stack_pack_entry_runtime_path "${stack_pack_entry}")"
    stack_install_source="local_path:${stack_source_uri}"
  fi

  cat >/tmp/rebind_stack_pack_source.sql <<EOF
UPDATE _System_InstalledPack
   SET SourceKind = '$(sql_escape "${stack_source_kind}")',
       SourceURI = '$(sql_escape "${stack_source_uri}")',
       SourceRevision = CASE
         WHEN '$(sql_escape "${stack_source_revision}")' = '' THEN SourceRevision
         ELSE '$(sql_escape "${stack_source_revision}")'
       END,
       InstallSource = '$(sql_escape "${stack_install_source}")',
       LastModifiedDate = UTC_TIMESTAMP()
 WHERE Name = '$(sql_escape "${stack_pack_name}")'
   AND ${stack_status_predicate}
   AND COALESCE(IsDeleted, 0) = 0;
EOF
  run_mysql "${TIDB_DATABASE}" </tmp/rebind_stack_pack_source.sql
}

rebind_active_stack_pack_sources_before_apply() {
  stack_lock="$(stack_lock_file)"
  echo "Rebinding active stack pack sources from ${stack_lock} before setup apply..."
  if ! installed_pack_table_exists; then
    return
  fi

  stack_pack_entries | while IFS= read -r stack_pack_entry; do
    if [ -z "${stack_pack_entry}" ]; then
      continue
    fi
    stack_pack_name="$(stack_pack_entry_name "${stack_pack_entry}")"
    stack_pack_source="$(stack_pack_entry_source "${stack_pack_entry}")"
    stack_status="$(installed_pack_status "${stack_pack_name}" || true)"
    if [ "${stack_status}" != "active" ]; then
      continue
    fi
    echo "Rebinding active stack pack ${stack_pack_name} to ${stack_pack_source}..."
    rebind_stack_pack_source_identity "${stack_pack_entry}" "${stack_pack_name}" "Status = 'active'"
  done
}

apply_stack_pack_named_credential_runbook() {
  stack_pack_entry="$1"
  stack_pack_name="$2"
  stack_pack_dir="$(stack_pack_entry_dir "${stack_pack_entry}")"
  stack_named_credential_sql="${stack_pack_dir}/runbooks/provision_named_credential.sql"
  if [ ! -f "${stack_named_credential_sql}" ]; then
    return
  fi

  echo "Applying stack pack named credential runbook for ${stack_pack_name}..."
  run_mysql "${TIDB_DATABASE}" <"${stack_named_credential_sql}"
}

provision_hubspot_named_credential_token_secret() {
  if [ -z "${EXP_HUBSPOT_ACCESS_TOKEN:-}" ]; then
    return
  fi

  echo "Provisioning HubSpot NamedCredential token secret..."
  hubspot_named_credential_id="$(
    run_mysql -N -B \
      "${TIDB_DATABASE}" \
      -e "SELECT Id FROM _System_NamedCredential WHERE DeveloperName='HubSpotPrivateApp' LIMIT 1;"
  )"
  if [ -z "${hubspot_named_credential_id}" ]; then
    echo "cannot provision HubSpot NamedCredential token: HubSpotPrivateApp definition is missing" >&2
    exit 1
  fi

  hubspot_secret_json="$(/usr/local/bin/nexuscrm-pack-runtime-metadata encrypt-hubspot-token)"
  hubspot_secret_value="$(printf '%s' "${hubspot_secret_json}" | jq -r '.secret_value')"
  hubspot_secret_masked_hint="$(printf '%s' "${hubspot_secret_json}" | jq -r '.masked_hint')"

  cat >/tmp/provision_hubspot_named_credential_secret.sql <<EOF
INSERT INTO _System_NamedCredentialSecret
    (Id, NamedCredentialId, SecretKind, SecretValue, MaskedHint,
     RotatedAt, CreatedDate, LastModifiedDate)
VALUES
    (
      'nc_secret_hubspot_crm_connector_private_app_token',
      '$(sql_escape "${hubspot_named_credential_id}")',
      'Token',
      '$(sql_escape "${hubspot_secret_value}")',
      '$(sql_escape "${hubspot_secret_masked_hint}")',
      UTC_TIMESTAMP(),
      UTC_TIMESTAMP(),
      UTC_TIMESTAMP()
    )
ON DUPLICATE KEY UPDATE
    SecretValue = VALUES(SecretValue),
    MaskedHint = VALUES(MaskedHint),
    RotatedAt = VALUES(RotatedAt),
    LastModifiedDate = VALUES(LastModifiedDate);
EOF
  run_mysql "${TIDB_DATABASE}" </tmp/provision_hubspot_named_credential_secret.sql
}

apply_stack_pack_named_credential_artifacts() {
  stack_pack_entry="$1"
  stack_pack_name="$2"
  apply_stack_pack_named_credential_runbook "${stack_pack_entry}" "${stack_pack_name}"
  if [ "${stack_pack_name}" = "hubspot-crm-connector" ]; then
    provision_hubspot_named_credential_token_secret
  fi
}

apply_stack_named_credential_artifacts_before_setup_apply() {
  stack_pack_entries | while IFS= read -r stack_pack_entry; do
    if [ -z "${stack_pack_entry}" ]; then
      continue
    fi
    stack_pack_name="$(stack_pack_entry_name "${stack_pack_entry}")"
    apply_stack_pack_named_credential_artifacts "${stack_pack_entry}" "${stack_pack_name}"
  done
}

install_stack_runtime_packs_if_needed() {
  stack_lock="$(stack_lock_file)"
  echo "Validating stack runtime packs from ${stack_lock}..."

  stack_pack_entries | while IFS= read -r stack_pack_entry; do
    if [ -z "${stack_pack_entry}" ]; then
      continue
    fi

    stack_pack_name="$(stack_pack_entry_name "${stack_pack_entry}")"
    stack_pack_source="$(stack_pack_entry_source "${stack_pack_entry}")"

    echo "Validating stack runtime pack ${stack_pack_name} from ${stack_pack_source}..."
    /usr/local/bin/nexuscrm-setup validate-pack --source "${stack_pack_source}"

    stack_status="$(installed_pack_status "${stack_pack_name}" || true)"
    if [ "${stack_status}" = "active" ]; then
      echo "Stack runtime pack already active: ${stack_pack_name}"
      apply_stack_pack_named_credential_artifacts "${stack_pack_entry}" "${stack_pack_name}"
      continue
    fi

    if [ "${stack_status}" = "installing" ] || [ "${stack_status}" = "failed" ]; then
      echo "Rebinding retryable stack pack ${stack_pack_name} before install retry..."
      rebind_stack_pack_source_identity "${stack_pack_entry}" "${stack_pack_name}" "Status IN ('installing', 'failed')"
    fi

    echo "Installing stack runtime pack ${stack_pack_name} from ${stack_pack_source}..."
    /usr/local/bin/nexuscrm-setup install-pack --source "${stack_pack_source}"
    apply_stack_pack_named_credential_artifacts "${stack_pack_entry}" "${stack_pack_name}"
  done
}

normalize_bootstrap_pack_official_identity_if_requested() {
  if [ "${NEXUSCRM_NORMALIZE_BOOTSTRAP_PACK_OFFICIAL_IDENTITY:-0}" != "1" ]; then
    return
  fi

  bootstrap_pack_name="${NEXUSCRM_BOOTSTRAP_PACK_NAME:-}"
  source_revision="${NEXUSCRM_BOOTSTRAP_PACK_SOURCE_REVISION:-}"
  if [ -z "${bootstrap_pack_name}" ]; then
    echo "NEXUSCRM_NORMALIZE_BOOTSTRAP_PACK_OFFICIAL_IDENTITY requires NEXUSCRM_BOOTSTRAP_PACK_NAME" >&2
    exit 1
  fi
  if [ -z "${source_revision}" ]; then
    echo "NEXUSCRM_NORMALIZE_BOOTSTRAP_PACK_OFFICIAL_IDENTITY requires NEXUSCRM_BOOTSTRAP_PACK_SOURCE_REVISION" >&2
    exit 1
  fi

  echo "Normalizing bootstrap pack source identity for CRM runtime validation..."
  cat >/tmp/normalize_bootstrap_pack_identity.sql <<EOF
UPDATE _System_InstalledPack
   SET SourceKind = 'official_repo',
       SourceURI = '$(sql_escape "${bootstrap_pack_name}")',
       SourceRevision = '$(sql_escape "${source_revision}")',
       InstallSource = 'official_repo:$(sql_escape "${bootstrap_pack_name}")',
       LastModifiedDate = UTC_TIMESTAMP()
 WHERE Name = '$(sql_escape "${bootstrap_pack_name}")'
   AND Status = 'active'
   AND COALESCE(IsDeleted, 0) = 0;
EOF
  run_mysql "${TIDB_DATABASE}" </tmp/normalize_bootstrap_pack_identity.sql
}

verify_control_plane_before_server_if_requested() {
  if [ "${NEXUSCRM_VERIFY_BEFORE_SERVER:-0}" != "1" ]; then
    return
  fi

  echo "Verifying control-plane readiness before server start..."
  /usr/local/bin/nexuscrm-setup verify
}

run_branch_backed_post_apply() {
  case "${NEXUSCRM_BRANCH_BACKED_POST_APPLY_MODE:-legacy}" in
    legacy)
      /usr/local/bin/nexuscrm-setup apply-packs
      install_bootstrap_pack_if_needed
      /usr/local/bin/nexuscrm-setup apply-packs
      /usr/local/bin/nexuscrm-setup rebuild
      /usr/local/bin/nexuscrm-setup ensure-indexes
      ;;
    staging)
      /usr/local/bin/nexuscrm-setup rebuild
      ;;
    *)
      echo "Unsupported NEXUSCRM_BRANCH_BACKED_POST_APPLY_MODE=${NEXUSCRM_BRANCH_BACKED_POST_APPLY_MODE}" >&2
      exit 1
      ;;
  esac

  reapply_bootstrap_pack_after_apply_packs_if_requested
  normalize_bootstrap_pack_official_identity_if_requested
  seed_stack_app_shell
}

run_setup_apply() {
  if bool_enabled "${SETUP_SKIP_POLYMORPHIC_BACKFILL:-}"; then
    set +e
    /usr/local/bin/nexuscrm-setup apply --skip-polymorphic-backfill
    status="$?"
    set -e
    if [ "${status}" = "3" ]; then
      echo "setup apply completed with polymorphic discriminator backfill skipped; accepting operator partial exit 3"
      return 0
    fi
    if [ "${status}" != "0" ]; then
      return "${status}"
    fi
    return 0
  fi

  /usr/local/bin/nexuscrm-setup apply
}

seed_stack_app_shell() {
  stack_app_shell_config="${NEXUSCRM_STACK_APP_SHELL_CONFIG:-}"
  if [ -z "${stack_app_shell_config}" ] || [ ! -f "${stack_app_shell_config}" ]; then
    return
  fi

  app_shell_apply_script="${NEXUSCRM_APP_SHELL_APPLY_SCRIPT:-/usr/local/bin/apply-runtime-app-shell.py}"
  if [ ! -x "${app_shell_apply_script}" ]; then
    echo "runtime app-shell apply script not found: ${app_shell_apply_script}" >&2
    exit 1
  fi

  echo "Applying and verifying release app-shell metadata..."
  "${app_shell_apply_script}" \
    --config "${stack_app_shell_config}" \
    --output /tmp/runtime-app-shell.json
}

bootstrap_embedded_tidb() {
  export NEXUSCRM_EMBEDDED_TIDB_STATUS_PORT="${NEXUSCRM_EMBEDDED_TIDB_STATUS_PORT:-10080}"
  export TIDB_HOST=127.0.0.1
  export TIDB_PORT="${TIDB_PORT:-4000}"
  export TIDB_USER="${TIDB_USER:-root}"
  export TIDB_PASSWORD="${TIDB_PASSWORD:-}"
  export TIDB_DATABASE="${TIDB_DATABASE:-nexuscrm}"
  export TIDB_TLS=false
  export NEXUSCRM_STACK_APP_SHELL_CONFIG="${NEXUSCRM_STACK_APP_SHELL_CONFIG:-/stack-runtime/app-shell.json}"
  export NEXUSCRM_INSTALL_STANDARD_PACK="${NEXUSCRM_INSTALL_STANDARD_PACK:-1}"

  mkdir -p /var/lib/nexuscrm/tidb
  echo "Starting embedded TiDB..."
  /usr/local/bin/tidb-server \
    --store=unistore \
    --path=/var/lib/nexuscrm/tidb \
    --host=0.0.0.0 \
    --advertise-address=127.0.0.1 \
    --status="${NEXUSCRM_EMBEDDED_TIDB_STATUS_PORT}" \
    >"${embedded_tidb_log}" 2>&1 &

  wait_for_embedded_tidb

  run_mysql -e "CREATE DATABASE IF NOT EXISTS \`${TIDB_DATABASE}\`;"

  echo "Running embedded bootstrap..."
  /usr/local/bin/nexuscrm-setup init-l0

  # Embedded deploys must still reconcile runtime packs against the live DB.
  # Skipping remote DB bootstrap only means "do not recreate the database"
  # from the deploy runner; it must not suppress pack/layout/action drift repair.
  /usr/local/bin/nexuscrm-setup apply-metadata
  /usr/local/bin/nexuscrm-setup repair
  /usr/local/bin/nexuscrm-setup apply-packs
  install_bootstrap_pack_if_needed

  # Standard-pack install can introduce live drift; reconcile once more.
  /usr/local/bin/nexuscrm-setup apply-packs
  /usr/local/bin/nexuscrm-setup rebuild
  /usr/local/bin/nexuscrm-setup ensure-indexes
  reapply_bootstrap_pack_after_apply_packs_if_requested
  normalize_bootstrap_pack_official_identity_if_requested
  seed_stack_app_shell
}

# Production migration escape hatch (PR hotfix after PR #1140 cutover):
# legacy rows produced by pre-v2 autonumber listener may no longer
# match the v2 format spec's derived regex (e.g. Contract.ContractNumber
# value "CON-00000694" vs spec "{00000000}"). Strict mode aborts the
# whole seed on any such row, crash-looping every pod. Deploy
# environments opt in to tolerant skip + WARN log by default; CI and
# local dev keep strict behavior (env unset).
export AUTONUMBER_SEED_TOLERATE_LEGACY="${AUTONUMBER_SEED_TOLERATE_LEGACY:-true}"

if [ "${NEXUSCRM_EMBEDDED_TIDB:-0}" = "1" ]; then
  bootstrap_embedded_tidb
else
  echo "Running branch-backed setup convergence..."
  /usr/local/bin/nexuscrm-setup apply-metadata
  if stack_lock_convergence_enabled; then
    rebind_active_stack_pack_sources_before_apply
    apply_stack_named_credential_artifacts_before_setup_apply
    sync_stack_runtime_pack_metadata_before_setup_apply
  else
    rebind_bootstrap_pack_source_before_apply_if_needed
    reapply_active_bootstrap_pack_before_apply_if_requested
  fi
  run_setup_apply
  if stack_lock_convergence_enabled; then
    install_stack_runtime_packs_if_needed
    sync_stack_runtime_pack_metadata_after_setup_apply
  fi
  run_branch_backed_post_apply
fi

# Tenant overlay selection and tenant overlay apply are intentionally
# separate controls:
# - NEXUSCRM_TENANT_OVERLAY_DIR selects runtime tenant metadata for server
#   describe/provenance paths. Tenant deployments should set it on every boot.
# - NEXUSCRM_APPLY_TENANT_OVERLAY=1 controls whether this boot mutates the DB
#   by running apply-tenant.
# Greenfield orgs leave both vars unset and get a clean platform baseline.
if [ "${NEXUSCRM_APPLY_TENANT_OVERLAY:-0}" = "1" ]; then
  if [ -z "${NEXUSCRM_TENANT_OVERLAY_DIR:-}" ]; then
    echo "NEXUSCRM_APPLY_TENANT_OVERLAY=1 requires NEXUSCRM_TENANT_OVERLAY_DIR" >&2
    exit 1
  fi
  echo "Running tenant overlay apply..."
  /usr/local/bin/nexuscrm-setup apply-tenant
fi

verify_control_plane_before_server_if_requested

echo "Starting server..."
exec /usr/local/bin/nexuscrm-server "$@"