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

# Superset Dev Account Anomaly Monitor Dashboard bootstrap (local execution)
# - Create or update virtual dataset: vds_dev_cost_anomaly
# - Create or update dashboard: Dev Cost Anomaly Monitor
# - Create or update 7 charts (anomaly alerts, cost trends, WoW overview,
#   service spikes, service trend, new services/regions, unapproved regions)
# - Detects WoW/DoD cost spikes, new services/regions, and unapproved regions
#   in development accounts
#
# Usage:
#   export SUPERSET_URL="http://127.0.0.1:8088"
#   export SUPERSET_USER="admin"
#   export SUPERSET_PASS="admin"
#   export DB_NAME_IN_SUPERSET="FinOPSGoogleBigQuery"
#   export BQ_PROJECT="gcp-rd-research-dev"
#   export BQ_SCHEMA="finops_cloud_billing"
#   bash dbt/scripts/create_superset_dev_cost_anomaly.sh
#
# Optional:
#   export DASHBOARD_TITLE="Dev Cost Anomaly Monitor"
#   export DASHBOARD_SLUG="dev-cost-anomaly"
#   export VDS_NAME="vds_dev_cost_anomaly"
#   export ALLOWED_REGIONS="us-east-1,us-west-2"
#   export CURL_NO_PROXY="*"
#   export ENABLE_NATIVE_FILTERS="true"
#   export DEFAULT_TIME_RANGE="DATEADD(DATETIME(\"today\"), -14, day) : DATETIME(\"today\")"

SUPERSET_URL="${SUPERSET_URL:-http://127.0.0.1:8088}"
SUPERSET_USER="${SUPERSET_USER:-admin}"
SUPERSET_PASS="${SUPERSET_PASS:-admin}"
DB_NAME_IN_SUPERSET="${DB_NAME_IN_SUPERSET:-FinOPSGoogleBigQuery}"
BQ_PROJECT="${BQ_PROJECT:-gcp-rd-research-dev}"
BQ_SCHEMA="${BQ_SCHEMA:-finops_cloud_billing}"

DASHBOARD_TITLE="${DASHBOARD_TITLE:-Dev Cost Anomaly Monitor}"
DASHBOARD_SLUG="${DASHBOARD_SLUG:-dev-cost-anomaly}"
VDS_NAME="${VDS_NAME:-vds_dev_cost_anomaly}"
ALLOWED_REGIONS="${ALLOWED_REGIONS:-us-east-1,us-west-2}"
CURL_NO_PROXY="${CURL_NO_PROXY:-*}"
ENABLE_NATIVE_FILTERS="${ENABLE_NATIVE_FILTERS:-true}"
DEFAULT_TIME_RANGE="${DEFAULT_TIME_RANGE:-DATEADD(DATETIME(\"today\"), -14, day) : DATETIME(\"today\")}"

read -r -d '' DASHBOARD_METADATA_JSON <<'JSON' || true
{
  "color_scheme": "",
  "label_colors": {
    "wow_spike": "#E04355",
    "dod_spike": "#FF8C00",
    "unapproved_region": "#9C27B0",
    "new_service": "#1FA8C9",
    "new_region": "#454E7C",
    "normal": "#999999"
  },
  "shared_label_colors": {
    "wow_spike": "#E04355",
    "dod_spike": "#FF8C00",
    "unapproved_region": "#9C27B0",
    "new_service": "#1FA8C9",
    "new_region": "#454E7C",
    "normal": "#999999"
  },
  "native_filter_configuration": [],
  "cross_filters_enabled": true
}
JSON

read -r -d '' VDS_SQL_TEMPLATE <<'SQL' || true
WITH daily_agg AS (
  SELECT
    usage_date,
    account_id,
    account_display_name,
    CONCAT(account_id, ' | ', account_display_name) AS account_label,
    account_owner,
    region,
    service,
    SUM(unblended_cost_usd) AS daily_cost_usd
  FROM `__BQ_PROJECT__.__BQ_SCHEMA__.mart_cost_daily`
  WHERE cloud_provider = 'aws'
    AND account_purpose = 'Development'
  GROUP BY 1,2,3,4,5,6,7
),

with_window AS (
  SELECT
    *,
    LAG(daily_cost_usd, 1) OVER (
      PARTITION BY account_id, service, region ORDER BY usage_date
    ) AS prev_day_cost_usd,
    LAG(daily_cost_usd, 7) OVER (
      PARTITION BY account_id, service, region ORDER BY usage_date
    ) AS prev_week_cost_usd,
    AVG(daily_cost_usd) OVER (
      PARTITION BY account_id, service, region
      ORDER BY usage_date
      ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
    ) AS moving_avg_7d_usd
  FROM daily_agg
),

service_history AS (
  SELECT DISTINCT account_id, service
  FROM daily_agg
  WHERE usage_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY)
                       AND DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
),

region_history AS (
  SELECT DISTINCT account_id, region
  FROM daily_agg
  WHERE usage_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY)
                       AND DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
)

SELECT
  w.usage_date,
  w.account_id,
  w.account_display_name,
  w.account_label,
  w.account_owner,
  w.region,
  w.service,
  w.daily_cost_usd,
  w.prev_day_cost_usd,
  w.prev_week_cost_usd,
  w.moving_avg_7d_usd,
  ROUND(SAFE_DIVIDE(w.daily_cost_usd - w.prev_day_cost_usd, w.prev_day_cost_usd) * 100, 1) AS dod_change_pct,
  ROUND(SAFE_DIVIDE(w.daily_cost_usd - w.prev_week_cost_usd, w.prev_week_cost_usd) * 100, 1) AS wow_change_pct,
  ROUND(SAFE_DIVIDE(w.daily_cost_usd - w.moving_avg_7d_usd, w.moving_avg_7d_usd) * 100, 1) AS avg_deviation_pct,
  CASE
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_week_cost_usd, w.prev_week_cost_usd) > 0.50
         AND (w.daily_cost_usd - w.prev_week_cost_usd) > 50
    THEN TRUE ELSE FALSE
  END AS is_wow_anomaly,
  CASE
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_day_cost_usd, w.prev_day_cost_usd) > 1.0
         AND (w.daily_cost_usd - w.prev_day_cost_usd) > 20
    THEN TRUE ELSE FALSE
  END AS is_dod_anomaly,
  CASE WHEN sh.account_id IS NULL AND w.daily_cost_usd > 1
       AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    THEN TRUE ELSE FALSE
  END AS is_new_service,
  CASE WHEN rh.account_id IS NULL AND w.daily_cost_usd > 1
       AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    THEN TRUE ELSE FALSE
  END AS is_new_region,
  CASE
    WHEN w.region NOT IN ('__ALLOWED_REGION_1__', '__ALLOWED_REGION_2__', 'global', '')
         AND w.daily_cost_usd > 0.01
    THEN TRUE ELSE FALSE
  END AS is_unapproved_region,
  CASE
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_week_cost_usd, w.prev_week_cost_usd) > 0.50
         AND (w.daily_cost_usd - w.prev_week_cost_usd) > 50 THEN 'wow_spike'
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_day_cost_usd, w.prev_day_cost_usd) > 1.0
         AND (w.daily_cost_usd - w.prev_day_cost_usd) > 20 THEN 'dod_spike'
    WHEN w.region NOT IN ('__ALLOWED_REGION_1__', '__ALLOWED_REGION_2__', 'global', '')
         AND w.daily_cost_usd > 0.01 THEN 'unapproved_region'
    WHEN sh.account_id IS NULL AND w.daily_cost_usd > 1
         AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN 'new_service'
    WHEN rh.account_id IS NULL AND w.daily_cost_usd > 1
         AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN 'new_region'
    ELSE 'normal'
  END AS anomaly_type
FROM with_window w
LEFT JOIN service_history sh ON w.account_id = sh.account_id AND w.service = sh.service
LEFT JOIN region_history rh ON w.account_id = rh.account_id AND w.region = rh.region
SQL

read -r -d '' CHART_SPECS_JSON <<'JSON' || true
[
  {
    "slice_name": "Dev Anomaly Alerts",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "granularity_sqla": "usage_date",
      "groupby": ["usage_date", "account_label", "service", "region", "anomaly_type"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(daily_cost_usd)","label":"daily_cost_usd"},
        {"expressionType":"SQL","sqlExpression":"AVG(wow_change_pct)","label":"wow_change_pct"},
        {"expressionType":"SQL","sqlExpression":"AVG(dod_change_pct)","label":"dod_change_pct"}
      ],
      "adhoc_filters": [
        {
          "expressionType": "SIMPLE",
          "subject": "anomaly_type",
          "operator": "!=",
          "comparator": "normal",
          "clause": "WHERE",
          "isExtra": false
        }
      ],
      "order_desc": true,
      "row_limit": 50
    }
  },
  {
    "slice_name": "Dev Daily Cost Trend",
    "viz_type": "echarts_area",
    "form_data": {
      "query_mode": "aggregate",
      "granularity_sqla": "usage_date",
      "time_grain_sqla": "P1D",
      "time_range": "No filter",
      "groupby": ["account_label"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(daily_cost_usd)","label":"daily_cost_usd"}
      ],
      "adhoc_filters": [
        {"expressionType":"SQL","sqlExpression":"usage_date < DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)","clause":"WHERE","filterOptionName":"filter_billing_lag"}
      ],
      "stack": "Stack",
      "opacity": 0.7,
      "x_axis_time_format": "%Y-%m-%d",
      "row_limit": 10000
    }
  },
  {
    "slice_name": "Dev WoW Change by Account",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "granularity_sqla": "usage_date",
      "groupby": ["account_label"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"AVG(wow_change_pct)","label":"wow_change_pct"},
        {"expressionType":"SQL","sqlExpression":"SUM(daily_cost_usd)","label":"daily_cost_usd"},
        {"expressionType":"SQL","sqlExpression":"AVG(moving_avg_7d_usd)","label":"moving_avg_7d_usd"}
      ],
      "adhoc_filters": [],
      "order_desc": true,
      "row_limit": 50
    }
  },
  {
    "slice_name": "Dev Top Service Spikes",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "granularity_sqla": "usage_date",
      "groupby": ["usage_date", "account_label", "service"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(daily_cost_usd)","label":"daily_cost_usd"},
        {"expressionType":"SQL","sqlExpression":"AVG(wow_change_pct)","label":"wow_change_pct"},
        {"expressionType":"SQL","sqlExpression":"AVG(dod_change_pct)","label":"dod_change_pct"}
      ],
      "adhoc_filters": [
        {
          "expressionType": "SQL",
          "sqlExpression": "is_wow_anomaly = TRUE OR is_dod_anomaly = TRUE",
          "clause": "WHERE",
          "isExtra": false
        }
      ],
      "order_desc": true,
      "row_limit": 100
    }
  },
  {
    "slice_name": "Dev Daily Cost by Service Top 10",
    "viz_type": "echarts_timeseries_bar",
    "form_data": {
      "query_mode": "aggregate",
      "granularity_sqla": "usage_date",
      "time_grain_sqla": "P1D",
      "time_range": "No filter",
      "groupby": ["service"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(daily_cost_usd)","label":"daily_cost_usd"}
      ],
      "adhoc_filters": [
        {"expressionType":"SQL","sqlExpression":"usage_date < DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)","clause":"WHERE","filterOptionName":"filter_billing_lag"}
      ],
      "stack": "Stack",
      "x_axis_time_format": "%Y-%m-%d",
      "series_limit": 10,
      "row_limit": 10000
    }
  },
  {
    "slice_name": "Dev New Services & Regions",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "granularity_sqla": "usage_date",
      "groupby": ["usage_date", "account_label", "service", "region"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(daily_cost_usd)","label":"daily_cost_usd"}
      ],
      "adhoc_filters": [
        {
          "expressionType": "SQL",
          "sqlExpression": "is_new_service = TRUE OR is_new_region = TRUE",
          "clause": "WHERE",
          "isExtra": false
        }
      ],
      "order_desc": true,
      "row_limit": 100
    }
  },
  {
    "slice_name": "Dev Unapproved Region Cost",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "granularity_sqla": "usage_date",
      "groupby": ["usage_date", "account_label", "region", "service"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(daily_cost_usd)","label":"daily_cost_usd"}
      ],
      "adhoc_filters": [
        {
          "expressionType": "SQL",
          "sqlExpression": "is_unapproved_region = TRUE",
          "clause": "WHERE",
          "isExtra": false
        }
      ],
      "order_desc": true,
      "row_limit": 100
    }
  }
]
JSON

# ===========================================================================
# Helper functions (reused from create_superset_tenant_cost_trend.sh)
# ===========================================================================

require_cmd() {
  local cmd="$1"
  if ! command -v "$cmd" >/dev/null 2>&1; then
    echo "ERROR: required command not found: $cmd" >&2
    exit 1
  fi
}

require_cmd curl
require_cmd jq

COOKIE_JAR="${TMPDIR:-/tmp}/superset_api_cookies_dev_cost_anomaly_$$.txt"
: > "$COOKIE_JAR"
trap 'rm -f "$COOKIE_JAR"' EXIT

json_get() {
  local json="$1"
  local expr="$2"
  echo "$json" | jq -r "$expr"
}

has_api_errors() {
  local json="$1"
  if ! echo "$json" | jq -e . >/dev/null 2>&1; then
    return 0
  fi
  echo "$json" | jq -e '
    ((.errors // []) | length > 0)
    or ((.message | type) == "object")
    or (((.message | type) == "string") and ((.message | ascii_downcase) | test("fatal|error|exception|traceback")))
    or ((.error // null) != null)
  ' >/dev/null
}

extract_id() {
  local json="$1"
  json_get "$json" '.result.id // .result.table_id // .id // .table_id // empty'
}

response_not_registered() {
  local json="$1"
  if ! echo "$json" | jq -e . >/dev/null 2>&1; then
    return 1
  fi
  echo "$json" | jq -e '
    [
      (.message // ""),
      (.errors[]?.message // ""),
      (.errors[]?.extra?.message // "")
    ]
    | map(tostring | ascii_downcase)
    | any(test("not registered|not found|unknown"))
  ' >/dev/null
}

response_dataset_exists() {
  local json="$1"
  if ! echo "$json" | jq -e . >/dev/null 2>&1; then
    return 1
  fi
  echo "$json" | jq -e '
    [
      (.message // ""),
      (.message.table[]? // ""),
      (.errors[]?.message // ""),
      (.errors[]?.extra?.message // "")
    ]
    | map(tostring | ascii_downcase)
    | any(test("already exists|dataset .* exists"))
  ' >/dev/null
}

api_get() {
  local path="$1"
  curl --noproxy "$CURL_NO_PROXY" -sS "$SUPERSET_URL$path" \
    -c "$COOKIE_JAR" \
    -b "$COOKIE_JAR" \
    -H "Authorization: Bearer $ACCESS_TOKEN"
}

api_post_json() {
  local path="$1"
  local payload="$2"
  curl --noproxy "$CURL_NO_PROXY" -sS -X POST "$SUPERSET_URL$path" \
    -c "$COOKIE_JAR" \
    -b "$COOKIE_JAR" \
    -H "Authorization: Bearer $ACCESS_TOKEN" \
    -H "X-CSRFToken: $CSRF_TOKEN" \
    -H "Referer: $SUPERSET_URL" \
    -H "Content-Type: application/json" \
    -d "$payload"
}

api_put_json() {
  local path="$1"
  local payload="$2"
  curl --noproxy "$CURL_NO_PROXY" -sS -X PUT "$SUPERSET_URL$path" \
    -c "$COOKIE_JAR" \
    -b "$COOKIE_JAR" \
    -H "Authorization: Bearer $ACCESS_TOKEN" \
    -H "X-CSRFToken: $CSRF_TOKEN" \
    -H "Referer: $SUPERSET_URL" \
    -H "Content-Type: application/json" \
    -d "$payload"
}

# ===========================================================================
# Main execution
# ===========================================================================

echo "Superset URL: $SUPERSET_URL"
echo "Database: $DB_NAME_IN_SUPERSET"
echo "Schema: $BQ_SCHEMA"
echo "Virtual dataset: $VDS_NAME"
echo "Allowed regions: $ALLOWED_REGIONS"
echo "Default time range: $DEFAULT_TIME_RANGE"

# Auth: form login → JWT → CSRF (uses jq for JSON to handle special chars in password)
# Step 1: GET /login/ to obtain session cookie + WTF CSRF token
LOGIN_PAGE="$(curl --noproxy "$CURL_NO_PROXY" -sS "$SUPERSET_URL/login/" \
  -c "$COOKIE_JAR" -b "$COOKIE_JAR")"
WTF_CSRF="$(echo "$LOGIN_PAGE" | python3 -c "
import sys, re; html = sys.stdin.read()
m = re.search(r'name=\"csrf_token\"[^>]*value=\"([^\"]+)\"', html)
if not m: m = re.search(r'value=\"([^\"]+)\"[^>]*name=\"csrf_token\"', html)
print(m.group(1) if m else '')
")"

# Step 2: POST /login/ with form data (do NOT follow redirects — 302 is success)
ENCODED_PASS="$(python3 -c "import urllib.parse,sys; print(urllib.parse.quote(sys.argv[1]))" "$SUPERSET_PASS")"
curl --noproxy "$CURL_NO_PROXY" -sS -X POST "$SUPERSET_URL/login/" \
  -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
  -d "username=${SUPERSET_USER}&password=${ENCODED_PASS}&csrf_token=${WTF_CSRF}" \
  -o /dev/null 2>/dev/null || true

# Step 3: JWT token
LOGIN_PAYLOAD="$(jq -cn --arg u "$SUPERSET_USER" --arg p "$SUPERSET_PASS" \
  '{username:$u, password:$p, provider:"db", refresh:true}')"
LOGIN_RESP="$(curl --noproxy "$CURL_NO_PROXY" -sS -X POST "$SUPERSET_URL/api/v1/security/login" \
  -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
  -H "Content-Type: application/json" \
  -d "$LOGIN_PAYLOAD")"
ACCESS_TOKEN="$(echo "$LOGIN_RESP" | jq -r '.access_token // empty')"
if [[ -z "$ACCESS_TOKEN" ]]; then
  echo "ERROR: Superset login failed. Response:" >&2
  echo "$LOGIN_RESP" >&2
  exit 1
fi

# Step 4: CSRF token
CSRF_RESP="$(api_get "/api/v1/security/csrf_token/")"
CSRF_TOKEN="$(json_get "$CSRF_RESP" '.result // empty')"
if [[ -z "$CSRF_TOKEN" ]]; then
  echo "ERROR: Failed to get CSRF token. Response:" >&2
  echo "$CSRF_RESP" >&2
  exit 1
fi

DB_LIST_RESP="$(api_get "/api/v1/database/?page_size=1000")"
DB_ID="$(
  echo "$DB_LIST_RESP" \
    | jq -r --arg n "$DB_NAME_IN_SUPERSET" '.result[]? | select(.database_name == $n) | .id' \
    | head -n1
)"
if [[ -z "$DB_ID" ]]; then
  echo "ERROR: Database not found in Superset: $DB_NAME_IN_SUPERSET" >&2
  echo "Available databases:" >&2
  echo "$DB_LIST_RESP" | jq -r '.result[]?.database_name' >&2
  exit 1
fi
echo "Using database_id=$DB_ID"

# Parse allowed regions from comma-separated env var
IFS=',' read -ra REGION_ARRAY <<< "$ALLOWED_REGIONS"
ALLOWED_REGION_1="${REGION_ARRAY[0]:-us-east-1}"
ALLOWED_REGION_2="${REGION_ARRAY[1]:-us-west-2}"

VDS_SQL="$(
  printf '%s\n' "$VDS_SQL_TEMPLATE" \
    | sed "s/__BQ_PROJECT__/$BQ_PROJECT/g" \
    | sed "s/__BQ_SCHEMA__/$BQ_SCHEMA/g" \
    | sed "s/__ALLOWED_REGION_1__/$ALLOWED_REGION_1/g" \
    | sed "s/__ALLOWED_REGION_2__/$ALLOWED_REGION_2/g"
)"

find_dataset_id() {
  local list_resp
  local dbs
  dbs="$DB_ID"
  list_resp="$(api_get "/api/v1/dataset/?page_size=5000")"
  echo "$list_resp" \
    | jq -r \
      --arg s "$BQ_SCHEMA" \
      --arg sp "$BQ_PROJECT.$BQ_SCHEMA" \
      --arg t "$VDS_NAME" \
      --arg dbs "$dbs" '
      .result[]?
      | select(.table_name == $t)
      | select((.database_id // .database.id // .database // 0 | tostring) == $dbs)
      | select(
          (.schema == $s)
          or (.schema == $sp)
          or ((.schema // "") | endswith("." + $s))
          or ((.schema // "") == "")
        )
      | .id
    ' \
    | head -n1
}

find_dataset_id_relaxed() {
  local list_resp
  list_resp="$(api_get "/api/v1/dataset/?page_size=5000")"
  echo "$list_resp" \
    | jq -r --arg t "$VDS_NAME" --arg s "$BQ_SCHEMA" '
      .result[]?
      | select(.table_name == $t)
      | select(
          (.schema == $s)
          or ((.schema // "") | endswith("." + $s))
          or (.schema == null)
          or (.schema == "")
        )
      | .id
    ' \
    | head -n1
}

find_dataset_id_by_name_only() {
  local list_resp
  list_resp="$(api_get "/api/v1/dataset/?page_size=5000")"
  echo "$list_resp" \
    | jq -r --arg t "$VDS_NAME" '
      .result[]?
      | select(.table_name == $t)
      | .id
    ' \
    | head -n1
}

create_virtual_dataset() {
  local payload resp dsid
  local existing_id

  payload="$(
    jq -cn \
      --arg db "$DB_ID" \
      --arg s "$BQ_SCHEMA" \
      --arg t "$VDS_NAME" \
      --arg sql "$VDS_SQL" \
      '{database:($db|tonumber), schema:$s, table_name:$t, sql:$sql}'
  )"
  resp="$(api_post_json "/api/v1/dataset/" "$payload")"
  dsid="$(extract_id "$resp")"

  if [[ -z "$dsid" ]] && response_dataset_exists "$resp"; then
    existing_id="$(find_dataset_id)"
    if [[ -z "$existing_id" ]]; then
      existing_id="$(find_dataset_id_relaxed)"
    fi
    if [[ -z "$existing_id" ]]; then
      existing_id="$(find_dataset_id_by_name_only)"
    fi
    if [[ -n "$existing_id" ]]; then
      echo "$existing_id"
      return 0
    fi
  fi

  if has_api_errors "$resp" || [[ -z "$dsid" ]]; then
    echo "ERROR: failed to create virtual dataset: $VDS_NAME" >&2
    echo "$resp" >&2
    return 1
  fi

  echo "$dsid"
}

upsert_virtual_dataset() {
  local dataset_id payload resp refresh_resp

  dataset_id="$(find_dataset_id)"
  if [[ -z "$dataset_id" ]]; then
    dataset_id="$(create_virtual_dataset)" || return 1
    echo "created virtual dataset: $VDS_NAME (id=$dataset_id)" >&2
  else
    payload="$(
      jq -cn \
        --arg s "$BQ_SCHEMA" \
        --arg t "$VDS_NAME" \
        --arg sql "$VDS_SQL" \
        '{schema:$s, table_name:$t, sql:$sql, main_dttm_col:"usage_date"}'
    )"
    resp="$(api_put_json "/api/v1/dataset/$dataset_id" "$payload")"
    if has_api_errors "$resp"; then
      payload="$(
        jq -cn \
          --arg sql "$VDS_SQL" \
          '{sql:$sql, main_dttm_col:"usage_date"}'
      )"
      resp="$(api_put_json "/api/v1/dataset/$dataset_id" "$payload")"
    fi
    if has_api_errors "$resp"; then
      echo "ERROR: failed to update virtual dataset id=$dataset_id" >&2
      echo "$resp" >&2
      return 1
    fi
    echo "updated virtual dataset: $VDS_NAME (id=$dataset_id)" >&2
  fi

  refresh_resp="$(api_put_json "/api/v1/dataset/$dataset_id/refresh" '{}')"
  if has_api_errors "$refresh_resp"; then
    refresh_resp="$(api_post_json "/api/v1/dataset/$dataset_id/refresh" '{}')"
  fi
  if has_api_errors "$refresh_resp"; then
    echo "WARN: failed to refresh dataset id=$dataset_id. Continue." >&2
  fi

  echo "$dataset_id"
}

upsert_dashboard() {
  local list_resp existing_id payload resp dashboard_id
  list_resp="$(api_get "/api/v1/dashboard/?page_size=1000")"
  existing_id="$(
    echo "$list_resp" \
      | jq -r --arg t "$DASHBOARD_TITLE" '.result[]? | select(.dashboard_title == $t) | .id' \
      | head -n1
  )"

  payload="$(
    jq -cn \
      --arg title "$DASHBOARD_TITLE" \
      --arg slug "$DASHBOARD_SLUG" \
      --argjson meta "$DASHBOARD_METADATA_JSON" \
      '{
        dashboard_title: $title,
        slug: $slug,
        published: true,
        json_metadata: ($meta|tojson)
      }'
  )"

  if [[ -z "$existing_id" ]]; then
    resp="$(api_post_json "/api/v1/dashboard/" "$payload")"
    dashboard_id="$(extract_id "$resp")"
    if has_api_errors "$resp" || [[ -z "$dashboard_id" ]]; then
      echo "ERROR: failed to create dashboard: $DASHBOARD_TITLE" >&2
      echo "$resp" >&2
      return 1
    fi
    echo "created dashboard: $DASHBOARD_TITLE (id=$dashboard_id, slug=$DASHBOARD_SLUG)" >&2
  else
    dashboard_id="$existing_id"
    resp="$(api_put_json "/api/v1/dashboard/$dashboard_id" "$payload")"
    if has_api_errors "$resp"; then
      echo "ERROR: failed to update dashboard id=$dashboard_id" >&2
      echo "$resp" >&2
      return 1
    fi
    echo "updated dashboard: $DASHBOARD_TITLE (id=$dashboard_id, slug=$DASHBOARD_SLUG)" >&2
  fi

  echo "$dashboard_id"
}

upsert_native_filters() {
  local dashboard_id="$1"
  local dataset_id="$2"
  local detail_resp current_meta_obj native_filters merged_meta payload resp

  detail_resp="$(api_get "/api/v1/dashboard/$dashboard_id")"
  if has_api_errors "$detail_resp"; then
    echo "WARN: failed to fetch dashboard detail for native filters. Skip." >&2
    return 0
  fi

  current_meta_obj="$(
    echo "$detail_resp" | jq -c '
      (.result.json_metadata // {})
      | if type == "string" then (try fromjson catch {}) else . end
    '
  )"

  native_filters="$(
    jq -cn --arg ds "$dataset_id" --arg tr "$DEFAULT_TIME_RANGE" '
      [
        {
          id: "NATIVE_FILTER-usage-date",
          required: false,
          controlValues: {},
          name: "Time Range",
          filterType: "filter_time",
          targets: [{datasetId:($ds|tonumber), column:{name:"usage_date"}}],
          defaultDataMask: {
            extraFormData: {time_range: $tr},
            filterState: {label: $tr, value: $tr}
          },
          cascadeParentIds: [],
          scope: {rootPath:["ROOT_ID"], excluded:[]}
        },
        {
          id: "NATIVE_FILTER-account-label",
          required: false,
          controlValues: {multiSelect:true, enableEmptyFilter:false, defaultToFirstItem:false, inverseSelection:false, searchAllOptions:true},
          name: "Account",
          filterType: "filter_select",
          targets: [{datasetId:($ds|tonumber), column:{name:"account_label"}}],
          defaultDataMask: {extraFormData:{}, filterState:{}},
          cascadeParentIds: [],
          scope: {rootPath:["ROOT_ID"], excluded:[]}
        },
        {
          id: "NATIVE_FILTER-service",
          required: false,
          controlValues: {multiSelect:true, enableEmptyFilter:false, defaultToFirstItem:false, inverseSelection:false, searchAllOptions:true},
          name: "Service",
          filterType: "filter_select",
          targets: [{datasetId:($ds|tonumber), column:{name:"service"}}],
          defaultDataMask: {extraFormData:{}, filterState:{}},
          cascadeParentIds: ["NATIVE_FILTER-account-label"],
          scope: {rootPath:["ROOT_ID"], excluded:[]}
        },
        {
          id: "NATIVE_FILTER-anomaly-type",
          required: false,
          controlValues: {multiSelect:false, enableEmptyFilter:false, defaultToFirstItem:false, inverseSelection:false, searchAllOptions:false},
          name: "Anomaly Type",
          filterType: "filter_select",
          targets: [{datasetId:($ds|tonumber), column:{name:"anomaly_type"}}],
          defaultDataMask: {extraFormData:{}, filterState:{}},
          cascadeParentIds: [],
          scope: {rootPath:["ROOT_ID"], excluded:[]}
        }
      ]
    '
  )"

  merged_meta="$(
    jq -cn \
      --argjson cur "$current_meta_obj" \
      --argjson nf "$native_filters" \
      '$cur + {native_filter_configuration:$nf}'
  )"

  payload="$(
    jq -cn \
      --argjson meta "$merged_meta" \
      '{json_metadata:($meta|tojson)}'
  )"
  resp="$(api_put_json "/api/v1/dashboard/$dashboard_id" "$payload")"
  if has_api_errors "$resp"; then
    echo "WARN: failed to upsert native filters automatically. Please configure in UI." >&2
    echo "$resp" >&2
    return 0
  fi

  echo "upserted native filters on dashboard id=$dashboard_id" >&2
}

upsert_chart() {
  local slice_name="$1"
  local viz_type="$2"
  local form_data="$3"
  local dataset_id="$4"
  local dashboard_id="$5"

  local chart_list_resp existing_id datasource_type datasource_uid
  local params_json payload resp

  chart_list_resp="$(api_get "/api/v1/chart/?page_size=5000")"
  existing_id="$(
    echo "$chart_list_resp" \
      | jq -r --arg n "$slice_name" --arg ds "$dataset_id" \
        '.result[]? | select(.slice_name == $n and ((.datasource_id|tostring) == $ds)) | .id' \
      | head -n1
  )"

  datasource_type="table"
  datasource_uid="${dataset_id}__${datasource_type}"

  params_json="$(
    jq -cn \
      --argjson fd "$form_data" \
      --arg ds "$datasource_uid" \
      --arg vt "$viz_type" \
      '$fd + {datasource:$ds, viz_type:$vt}'
  )"

  payload="$(
    jq -cn \
      --arg sn "$slice_name" \
      --arg vt "$viz_type" \
      --arg params "$params_json" \
      --arg dsid "$dataset_id" \
      --arg dst "$datasource_type" \
      --argjson dashboards "[$dashboard_id]" \
      '{
        slice_name: $sn,
        viz_type: $vt,
        params: $params,
        datasource_id: ($dsid|tonumber),
        datasource_type: $dst,
        dashboards: $dashboards,
        query_context_generation: true
      }'
  )"

  if [[ -z "$existing_id" ]]; then
    resp="$(api_post_json "/api/v1/chart/" "$payload")"
    if has_api_errors "$resp"; then
      if response_not_registered "$resp"; then
        local fallback_viz fallback_params fallback_payload
        fallback_viz="table"
        fallback_params="$(
          jq -cn \
            --argjson fd "$form_data" \
            --arg ds "$datasource_uid" \
            --arg vt "$fallback_viz" \
            '$fd + {datasource:$ds, viz_type:$vt}'
        )"
        fallback_payload="$(
          jq -cn \
            --arg sn "$slice_name" \
            --arg vt "$fallback_viz" \
            --arg params "$fallback_params" \
            --arg dsid "$dataset_id" \
            --arg dst "$datasource_type" \
            --argjson dashboards "[$dashboard_id]" \
            '{
              slice_name: $sn,
              viz_type: $vt,
              params: $params,
              datasource_id: ($dsid|tonumber),
              datasource_type: $dst,
              dashboards: $dashboards,
              query_context_generation: true
            }'
        )"
        resp="$(api_post_json "/api/v1/chart/" "$fallback_payload")"
        if has_api_errors "$resp"; then
          echo "ERROR: failed to create chart (fallback table also failed): $slice_name" >&2
          echo "$resp" >&2
          return 1
        fi
        echo "created chart with fallback viz=table: $slice_name"
      else
        echo "ERROR: failed to create chart: $slice_name" >&2
        echo "$resp" >&2
        return 1
      fi
    fi
    if ! has_api_errors "$resp"; then
      echo "created chart: $slice_name"
    fi
  else
    resp="$(api_put_json "/api/v1/chart/$existing_id" "$payload")"
    if has_api_errors "$resp"; then
      if response_not_registered "$resp"; then
        local fallback_viz fallback_params fallback_payload
        fallback_viz="table"
        fallback_params="$(
          jq -cn \
            --argjson fd "$form_data" \
            --arg ds "$datasource_uid" \
            --arg vt "$fallback_viz" \
            '$fd + {datasource:$ds, viz_type:$vt}'
        )"
        fallback_payload="$(
          jq -cn \
            --arg sn "$slice_name" \
            --arg vt "$fallback_viz" \
            --arg params "$fallback_params" \
            --arg dsid "$dataset_id" \
            --arg dst "$datasource_type" \
            --argjson dashboards "[$dashboard_id]" \
            '{
              slice_name: $sn,
              viz_type: $vt,
              params: $params,
              datasource_id: ($dsid|tonumber),
              datasource_type: $dst,
              dashboards: $dashboards,
              query_context_generation: true
            }'
        )"
        resp="$(api_put_json "/api/v1/chart/$existing_id" "$fallback_payload")"
        if has_api_errors "$resp"; then
          echo "ERROR: failed to update chart (fallback table also failed) id=$existing_id name=$slice_name" >&2
          echo "$resp" >&2
          return 1
        fi
        echo "updated chart with fallback viz=table: $slice_name (id=$existing_id)"
      else
        echo "ERROR: failed to update chart id=$existing_id name=$slice_name" >&2
        echo "$resp" >&2
        return 1
      fi
    fi
    if ! has_api_errors "$resp"; then
      echo "updated chart: $slice_name (id=$existing_id)"
    fi
  fi
}

# ===========================================================================
# Orchestration
# ===========================================================================

if ! VDS_ID="$(upsert_virtual_dataset)"; then
  exit 1
fi
if [[ -z "$VDS_ID" ]]; then
  echo "ERROR: virtual dataset id is empty" >&2
  exit 1
fi

if ! DASHBOARD_ID="$(upsert_dashboard)"; then
  exit 1
fi
if [[ -z "$DASHBOARD_ID" ]]; then
  echo "ERROR: dashboard id is empty" >&2
  exit 1
fi

if [[ "$ENABLE_NATIVE_FILTERS" == "true" ]]; then
  if ! upsert_native_filters "$DASHBOARD_ID" "$VDS_ID"; then
    exit 1
  fi
fi

echo "Creating/updating charts..."
while IFS= read -r spec; do
  slice_name="$(echo "$spec" | jq -r '.slice_name')"
  viz_type="$(echo "$spec" | jq -r '.viz_type')"
  form_data="$(echo "$spec" | jq -c '.form_data')"
  upsert_chart "$slice_name" "$viz_type" "$form_data" "$VDS_ID" "$DASHBOARD_ID"
done < <(echo "$CHART_SPECS_JSON" | jq -c '.[]')

CHARTS_RESP="$(api_get "/api/v1/dashboard/$DASHBOARD_ID/charts")"
CHART_COUNT="$(echo "$CHARTS_RESP" | jq -r '.result | length // 0')"

echo
echo "Done."
echo "- Dataset: $VDS_NAME (id=$VDS_ID)"
echo "- Dashboard: $DASHBOARD_TITLE (id=$DASHBOARD_ID, slug=$DASHBOARD_SLUG)"
echo "- Charts in dashboard: $CHART_COUNT"
echo "- Allowed regions: $ALLOWED_REGIONS"
echo "- URL: $SUPERSET_URL/superset/dashboard/$DASHBOARD_SLUG/"
echo
echo "Note: Dashboard layout may still need manual arrangement in UI."