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

# Superset Production Region Cost Trend Dashboard bootstrap (local execution)
# - Create or update virtual dataset: vds_prod_region_cost_trend
# - Create or update dashboard: Production Region Cost Trend
# - Create or update 6 static charts (region trend + ranking, account×region trend +
#   top services, service trend + detail table)
# - Dynamically create per-region small multiples charts (one bar chart per region)
# - Tracks cost across all AWS Production accounts with Region → Service drill-down
#
# 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_prod_region_cost_trend.sh
#
# Optional:
#   export DASHBOARD_TITLE="Production Region Cost Trend"
#   export DASHBOARD_SLUG="prod-region-cost-trend"
#   export VDS_NAME="vds_prod_region_cost_trend"
#   export CURL_NO_PROXY="*"
#   export ENABLE_NATIVE_FILTERS="true"
#   export DEFAULT_TIME_RANGE="DATEADD(DATETRUNC(DATETIME(\"today\"), month), -12, month) : DATETRUNC(DATETIME(\"today\"), month)"

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:-Production Region Cost Trend}"
DASHBOARD_SLUG="${DASHBOARD_SLUG:-prod-region-cost-trend}"
VDS_NAME="${VDS_NAME:-vds_prod_region_cost_trend}"
CURL_NO_PROXY="${CURL_NO_PROXY:-*}"
ENABLE_NATIVE_FILTERS="${ENABLE_NATIVE_FILTERS:-true}"
DEFAULT_TIME_RANGE="${DEFAULT_TIME_RANGE:-DATEADD(DATETRUNC(DATETIME(\"today\"), month), -12, month) : DATETRUNC(DATETIME(\"today\"), month)}"
# Monthly cost threshold (USD) for marking a region as idle.
# Regions with avg monthly cost below this in the last 3 months are flagged as idle.
IDLE_MONTHLY_THRESHOLD_USD="${IDLE_MONTHLY_THRESHOLD_USD:-15}"

read -r -d '' DASHBOARD_METADATA_JSON <<'JSON' || true
{
  "color_scheme": "",
  "label_colors": {},
  "shared_label_colors": {},
  "native_filter_configuration": [],
  "cross_filters_enabled": true
}
JSON

read -r -d '' VDS_SQL_TEMPLATE <<'SQL' || true
SELECT
  m.billing_period_month,
  m.cloud_provider,
  m.region,
  CONCAT(m.region, ' | ', CASE m.region
    WHEN 'us-east-1'      THEN 'N. Virginia'
    WHEN 'us-east-2'      THEN 'Ohio'
    WHEN 'us-west-1'      THEN 'N. California'
    WHEN 'us-west-2'      THEN 'Oregon'
    WHEN 'ap-east-1'      THEN 'Hong Kong'
    WHEN 'ap-south-1'     THEN 'Mumbai'
    WHEN 'ap-south-2'     THEN 'Hyderabad'
    WHEN 'ap-southeast-1' THEN 'Singapore'
    WHEN 'ap-southeast-2' THEN 'Sydney'
    WHEN 'ap-southeast-3' THEN 'Jakarta'
    WHEN 'ap-southeast-4' THEN 'Melbourne'
    WHEN 'ap-southeast-5' THEN 'Malaysia'
    WHEN 'ap-southeast-7' THEN 'Thailand'
    WHEN 'ap-northeast-1' THEN 'Tokyo'
    WHEN 'ap-northeast-2' THEN 'Seoul'
    WHEN 'ap-northeast-3' THEN 'Osaka'
    WHEN 'ca-central-1'   THEN 'Canada'
    WHEN 'ca-west-1'      THEN 'Calgary'
    WHEN 'eu-central-1'   THEN 'Frankfurt'
    WHEN 'eu-central-2'   THEN 'Zurich'
    WHEN 'eu-west-1'      THEN 'Ireland'
    WHEN 'eu-west-2'      THEN 'London'
    WHEN 'eu-west-3'      THEN 'Paris'
    WHEN 'eu-south-1'     THEN 'Milan'
    WHEN 'eu-south-2'     THEN 'Spain'
    WHEN 'eu-north-1'     THEN 'Stockholm'
    WHEN 'af-south-1'     THEN 'Cape Town'
    WHEN 'me-south-1'     THEN 'Bahrain'
    WHEN 'me-central-1'   THEN 'UAE'
    WHEN 'sa-east-1'      THEN 'São Paulo'
    WHEN 'il-central-1'   THEN 'Tel Aviv'
    WHEN 'global'         THEN 'Global'
    ELSE m.region
  END) AS region_label,
  m.account_id,
  m.account_display_name,
  CONCAT(m.account_id, ' | ', m.account_display_name) AS account_label,
  m.account_owner,
  m.service,
  m.service_category,
  SUM(m.unblended_cost_usd) AS cost_usd
FROM `__BQ_PROJECT__.__BQ_SCHEMA__.mart_cost_summary` AS m
WHERE m.cloud_provider = 'aws'
  AND m.account_purpose = 'Production'
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
SQL

read -r -d '' CHART_SPECS_JSON <<'JSON' || true
[
  {
    "slice_name": "Prod Region Cost Trend",
    "viz_type": "echarts_timeseries_bar",
    "form_data": {
      "query_mode": "aggregate",
      "granularity_sqla": "billing_period_month",
      "time_grain_sqla": "P1M",
      "time_range": "No filter",
      "groupby": ["region_label"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(cost_usd)","label":"cost_usd"}
      ],
      "adhoc_filters": [],
      "stack": "Stack",
      "x_axis_time_format": "%b %Y",
      "row_limit": 10000
    }
  },
  {
    "slice_name": "Prod Region Cost Ranking",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "groupby": [
        "region_label"
      ],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(cost_usd)","label":"cost_usd"}
      ],
      "adhoc_filters": [],
      "order_desc": true,
      "row_limit": 100
    }
  },
  {
    "slice_name": "Prod Account Region Cost Trend",
    "viz_type": "echarts_timeseries_bar",
    "form_data": {
      "query_mode": "aggregate",
      "granularity_sqla": "billing_period_month",
      "time_grain_sqla": "P1M",
      "time_range": "No filter",
      "groupby": ["account_display_name"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(cost_usd)","label":"cost_usd"}
      ],
      "adhoc_filters": [],
      "stack": "Stack",
      "x_axis_time_format": "%b %Y",
      "row_limit": 10000
    }
  },
  {
    "slice_name": "Prod Top Services by Region",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "groupby": [
        "region_label",
        "service"
      ],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(cost_usd)","label":"cost_usd"}
      ],
      "adhoc_filters": [],
      "order_desc": true,
      "row_limit": 500
    }
  },
  {
    "slice_name": "Prod Service Cost Trend Top 15",
    "viz_type": "echarts_timeseries_bar",
    "form_data": {
      "query_mode": "aggregate",
      "granularity_sqla": "billing_period_month",
      "time_grain_sqla": "P1M",
      "time_range": "No filter",
      "groupby": ["service"],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(cost_usd)","label":"cost_usd"}
      ],
      "adhoc_filters": [],
      "stack": "Stack",
      "x_axis_time_format": "%b %Y",
      "row_limit": 15
    }
  },
  {
    "slice_name": "Prod Region Cost Detail",
    "viz_type": "table",
    "form_data": {
      "query_mode": "aggregate",
      "time_range": "No filter",
      "groupby": [
        "billing_period_month",
        "region_label",
        "account_id",
        "account_display_name",
        "service"
      ],
      "metrics": [
        {"expressionType":"SQL","sqlExpression":"SUM(cost_usd)","label":"cost_usd"}
      ],
      "adhoc_filters": [],
      "order_desc": true,
      "row_limit": 500
    }
  }
]
JSON

# ===========================================================================
# Helper functions (reused from create_superset_aws_dev_account_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_prod_region_cost_trend_$$.txt"
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 "Default time range: $DEFAULT_TIME_RANGE"

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 "{\"username\":\"$SUPERSET_USER\",\"password\":\"$SUPERSET_PASS\",\"provider\":\"db\",\"refresh\":true}"
)"

ACCESS_TOKEN="$(json_get "$LOGIN_RESP" '.access_token // empty')"
if [[ -z "$ACCESS_TOKEN" ]]; then
  echo "ERROR: Superset login failed. Response:" >&2
  echo "$LOGIN_RESP" >&2
  exit 1
fi

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"

VDS_SQL="$(
  printf '%s\n' "$VDS_SQL_TEMPLATE" \
    | sed "s/__BQ_PROJECT__/$BQ_PROJECT/g" \
    | sed "s/__BQ_SCHEMA__/$BQ_SCHEMA/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:"billing_period_month"}'
    )"
    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:"billing_period_month"}'
      )"
      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 region_excluded_ids="${3:-[]}"
  local time_excluded_ids="${4:-[]}"
  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" \
      --argjson region_excluded "$region_excluded_ids" \
      --argjson time_excluded "$time_excluded_ids" '
      [
        {
          id: "NATIVE_FILTER-billing-period-month",
          required: false,
          controlValues: {},
          name: "Time Range",
          filterType: "filter_time",
          targets: [{datasetId:($ds|tonumber), column:{name:"billing_period_month"}}],
          defaultDataMask: {
            extraFormData: {time_range: $tr},
            filterState: {label: $tr, value: $tr}
          },
          cascadeParentIds: [],
          scope: {rootPath:["ROOT_ID"], excluded:$time_excluded}
        },
        {
          id: "NATIVE_FILTER-account-display-name",
          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-region",
          required: false,
          controlValues: {multiSelect:true, enableEmptyFilter:false, defaultToFirstItem:false, inverseSelection:false, searchAllOptions:true},
          name: "Region",
          filterType: "filter_select",
          targets: [{datasetId:($ds|tonumber), column:{name:"region"}}],
          defaultDataMask: {extraFormData:{}, filterState:{}},
          cascadeParentIds: [],
          scope: {rootPath:["ROOT_ID"], excluded:$region_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-region"],
          scope: {rootPath:["ROOT_ID"], excluded:[]}
        },
        {
          id: "NATIVE_FILTER-service-category",
          required: false,
          controlValues: {multiSelect:true, enableEmptyFilter:false, defaultToFirstItem:false, inverseSelection:false, searchAllOptions:true},
          name: "Service Category",
          filterType: "filter_select",
          targets: [{datasetId:($ds|tonumber), column:{name:"service_category"}}],
          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
}

region_display_name() {
  case "$1" in
    us-east-1)      echo "N. Virginia" ;;
    us-east-2)      echo "Ohio" ;;
    us-west-1)      echo "N. California" ;;
    us-west-2)      echo "Oregon" ;;
    ap-east-1)      echo "Hong Kong" ;;
    ap-south-1)     echo "Mumbai" ;;
    ap-south-2)     echo "Hyderabad" ;;
    ap-southeast-1) echo "Singapore" ;;
    ap-southeast-2) echo "Sydney" ;;
    ap-southeast-3) echo "Jakarta" ;;
    ap-southeast-4) echo "Melbourne" ;;
    ap-southeast-5) echo "Malaysia" ;;
    ap-southeast-7) echo "Thailand" ;;
    ap-northeast-1) echo "Tokyo" ;;
    ap-northeast-2) echo "Seoul" ;;
    ap-northeast-3) echo "Osaka" ;;
    ca-central-1)   echo "Canada" ;;
    ca-west-1)      echo "Calgary" ;;
    eu-central-1)   echo "Frankfurt" ;;
    eu-central-2)   echo "Zurich" ;;
    eu-west-1)      echo "Ireland" ;;
    eu-west-2)      echo "London" ;;
    eu-west-3)      echo "Paris" ;;
    eu-south-1)     echo "Milan" ;;
    eu-south-2)     echo "Spain" ;;
    eu-north-1)     echo "Stockholm" ;;
    af-south-1)     echo "Cape Town" ;;
    me-south-1)     echo "Bahrain" ;;
    me-central-1)   echo "UAE" ;;
    sa-east-1)      echo "São Paulo" ;;
    il-central-1)   echo "Tel Aviv" ;;
    global)         echo "Global" ;;
    *)              echo "$1" ;;
  esac
}

query_regions() {
  local sql payload resp
  sql="SELECT region,
  ROUND(SUM(unblended_cost_usd) / 3, 2) AS avg_monthly_cost
FROM \`${BQ_PROJECT}.${BQ_SCHEMA}.mart_cost_summary\`
WHERE cloud_provider = 'aws'
  AND account_purpose = 'Production'
  AND billing_period_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 3 MONTH)
GROUP BY 1
HAVING avg_monthly_cost > 0
ORDER BY 2 DESC"

  payload="$(
    jq -cn \
      --arg db "$DB_ID" \
      --arg sql "$sql" \
      --arg schema "$BQ_SCHEMA" \
      '{database_id:($db|tonumber), sql:$sql, schema:$schema, runAsync:false}'
  )"
  resp="$(api_post_json "/api/v1/sqllab/execute/" "$payload")"
  if has_api_errors "$resp"; then
    echo "ERROR: failed to query regions via SQL Lab" >&2
    echo "$resp" >&2
    return 1
  fi

  # Output: region<TAB>avg_monthly_cost
  echo "$resp" | jq -r '.data[]? | "\(.region)\t\(.avg_monthly_cost)"'
}

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

  local existing_id datasource_type datasource_uid
  local params_json payload resp chart_id

  existing_id="$(
    echo "$chart_list_cache" \
      | 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
      echo "ERROR: failed to create chart: $slice_name" >&2
      echo "$resp" >&2
      return 1
    fi
    chart_id="$(extract_id "$resp")"
    echo "created chart: $slice_name (id=$chart_id)" >&2
  else
    chart_id="$existing_id"
    resp="$(api_put_json "/api/v1/chart/$existing_id" "$payload")"
    if has_api_errors "$resp"; then
      echo "ERROR: failed to update chart id=$existing_id name=$slice_name" >&2
      echo "$resp" >&2
      return 1
    fi
    echo "updated chart: $slice_name (id=$existing_id)" >&2
  fi

  echo "$chart_id"
}

# ===========================================================================
# 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

echo "Creating/updating static 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 '.[]')

# Idle regions chart — data-driven, auto-updates with billing data
IDLE_CHART_NAME="Prod Idle Regions (Avg Monthly < \$${IDLE_MONTHLY_THRESHOLD_USD})"
IDLE_FORM_DATA="$(
  jq -cn --arg threshold "$IDLE_MONTHLY_THRESHOLD_USD" '
    {
      query_mode: "aggregate",
      granularity_sqla: "billing_period_month",
      time_range: "No filter",
      groupby: ["region_label"],
      metrics: [
        {expressionType:"SQL", sqlExpression:"ROUND(SUM(cost_usd) / NULLIF(COUNT(DISTINCT billing_period_month), 0), 2)", label:"Avg Monthly Cost (USD)"}
      ],
      adhoc_filters: [
        {expressionType:"SQL", sqlExpression:"billing_period_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 3 MONTH)", clause:"WHERE", isExtra:false},
        {expressionType:"SQL", sqlExpression:("SUM(cost_usd) / NULLIF(COUNT(DISTINCT billing_period_month), 0) < " + $threshold), clause:"HAVING", isExtra:false}
      ],
      order_desc: false,
      row_limit: 100
    }
  '
)"
upsert_chart "$IDLE_CHART_NAME" "table" "$IDLE_FORM_DATA" "$VDS_ID" "$DASHBOARD_ID"

# Get idle chart ID for Time Range filter exclusion
IDLE_CHART_LIST="$(api_get "/api/v1/chart/?page_size=5000")"
IDLE_CHART_ID="$(
  echo "$IDLE_CHART_LIST" \
    | jq -r --arg n "$IDLE_CHART_NAME" --arg ds "$VDS_ID" \
      '.result[]? | select(.slice_name == $n and ((.datasource_id|tostring) == $ds)) | .id' \
    | head -n1
)"
echo "Idle regions chart id=$IDLE_CHART_ID"

echo
echo "Discovering production regions via SQL Lab..."
echo "Idle threshold: avg monthly cost < \$${IDLE_MONTHLY_THRESHOLD_USD}/mo"
REGIONS="$(query_regions)"
REGION_COUNT="$(echo "$REGIONS" | wc -l | tr -d ' ')"
echo "Found $REGION_COUNT regions (sorted by avg monthly cost desc):"
echo "$REGIONS" | while IFS=$'\t' read -r r c; do
  printf "  - %-20s \$%s/mo\n" "$r" "$c"
done

echo
echo "Caching chart list for per-region upserts..."
CHART_LIST_CACHE="$(api_get "/api/v1/chart/?page_size=5000")"

echo "Creating/updating per-region charts..."
REGION_CHART_IDS=()
while IFS=$'\t' read -r region avg_monthly_cost; do
  [[ -z "$region" ]] && continue
  display_name="$(region_display_name "$region")"
  chart_name="Prod: ${display_name} (${region})"
  form_data="$(
    jq -cn --arg region "$region" '
      {
        query_mode: "aggregate",
        granularity_sqla: "billing_period_month",
        time_grain_sqla: "P1M",
        time_range: "No filter",
        groupby: [],
        metrics: [
          {expressionType:"SQL", sqlExpression:"SUM(cost_usd)", label:"Cost (USD)"}
        ],
        adhoc_filters: [
          {expressionType:"SIMPLE", subject:"region", operator:"==", comparator:$region, clause:"WHERE", isExtra:false}
        ],
        show_legend: false,
        x_axis_time_format: "%b %Y",
        row_limit: 1000
      }
    '
  )"
  chart_id="$(upsert_chart_cached "$CHART_LIST_CACHE" "$chart_name" "echarts_timeseries_bar" "$form_data" "$VDS_ID" "$DASHBOARD_ID")"
  if [[ -n "$chart_id" ]]; then
    REGION_CHART_IDS+=("$chart_id")
  fi
done <<< "$REGIONS"

REGION_CHART_IDS_JSON="$(printf '%s\n' "${REGION_CHART_IDS[@]}" | jq -s '[.[] | tonumber]')"
echo "Per-region chart IDs: $REGION_CHART_IDS_JSON"

# Build Time Range filter exclusion list (idle chart uses fixed 3-month window)
TIME_EXCLUDED_IDS="[]"
if [[ -n "$IDLE_CHART_ID" ]]; then
  TIME_EXCLUDED_IDS="[$IDLE_CHART_ID]"
fi

if [[ "$ENABLE_NATIVE_FILTERS" == "true" ]]; then
  echo
  echo "Upserting native filters..."
  echo "  Region filter excludes: per-region charts ($REGION_CHART_IDS_JSON)"
  echo "  Time Range filter excludes: idle chart ($TIME_EXCLUDED_IDS)"
  if ! upsert_native_filters "$DASHBOARD_ID" "$VDS_ID" "$REGION_CHART_IDS_JSON" "$TIME_EXCLUDED_IDS"; then
    exit 1
  fi
fi

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 "- Static charts: 6"
echo "- Per-region charts: ${#REGION_CHART_IDS[@]}"
echo "- Total charts in dashboard: $CHART_COUNT"
echo "- URL: $SUPERSET_URL/superset/dashboard/$DASHBOARD_SLUG/"
echo
echo "Note: Dashboard layout needs manual arrangement in UI (4-column grid for per-region charts)."