Search, filtering and faceting on date range and sum price

[milovindo (mark)]

I index with these mappings

{
  "mappings": {
    "properties": {
      "coords": {
        "type": "geo_point"
      },
      "id": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "reviews": {
        "type": "short"
      },
      "rooms": {
        "type": "nested",
        "properties": {
          "adults": {
            "type": "short"
          },
          "id": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "maxPerson": {
            "type": "short"
          },
          "name": {
            "type": "text"
          },
          "rateAvailability": {
            "type": "nested",
            "properties": {
              "available": {
                "type": "short"
              },
              "onDate": {
                "type": "date"
              },
              "price": {
                "type": "short"
              }
            }
          }
        }
      },
      "title": {
        "type": "text"
      }
    }
  }
}

And with example document like this

       {
          "available": 1,
          "onDate": "2024-05-04",
          "price": "50"
        }
        ..........................
      ]
    },
    {
      "adults": "2",
      "id": "1",
      "maxPerson": "2",
      "name": "4 Bed apartman",
      "rateAvailability": [
        {
          "available": 1,
          "onDate": "2024-05-01",
          "price": "50"
        },
        {
          "available": 1,
          "onDate": "2024-05-02",
          "price": "50"
        },
        {
          "available": 1,
          "onDate": "2024-05-03",
          "price": "50"
        },
        {
          "available": 1,
          "onDate": "2024-05-04",
          "price": "50"
        }
        ..........................
      ]
    }
  ],
  "slug": "hotel-one",
  "title": "Hotel One"
}

I need to filter documents like this based on the the total price for some date range period. For example, return all the hotels that have available at least one room from 2024-06-20 till 2024-06-30 and that room i less than $500 for that period.

This is what I got so far, but this is not working and I'm stuck.

        'body' => [
            'query' => [
                'nested' => [
                    'path' => 'rooms.rateAvailability',
                    'query' => [
                        'bool' => [
                            'must' => [
                                'range' => [
                                    'rooms.rateAvailability.onDate' => [
                                        'gte' => '2024-06-20',
                                        'lte' => '2024-06-30',
                                    ],
                                ],
                                'script' => [
                                    'script' => [
                                        'source' => '
                                            def totalPrice = 0;
                                            for (availability in params._source.rooms) {
                                                if (availability.rateAvailability.onDate >= params.startDate &&
                                                    availability.rateAvailability.onDate <= params.endDate) {
                                                    totalPrice += availability.rateAvailability.price;
                                                }
                                            }
                                            return totalPrice <= params.maxTotalPrice;
                                        ',
                                        'params' => [
                                            'startDate' => '2024-06-20',
                                            'endDate' => '2024-06-30',
                                            'maxTotalPrice' => 500,
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
            ],
        ],