Task: workarena.servicenow.filter-hardware-list
Goal: Create a filter for the hardware list to extract all entries where:
    - "Substate" is "Stolen" and 
    - "Asset function" is "Shared" and 
    - "Display name" is "Apple MacBook Pro 15"" and 
    - "Serial number" is "" and 
    - "Model category" is "Computer"
Task configuration: {'table_name': 'alm_hardware', 'filter_kind': 'AND', 'filter_len': 5, 'filter_columns': ['substatus', 'asset_function', 'display_name', 'serial_number', 'model_category'], 'filter_values': ['Stolen', 'Shared', 'Apple MacBook Pro 15"', '', 'Computer'], 'filter_operators': ['is', 'is', 'is', 'is', 'is'], 'columns': {'asset_function': {'base_type': 'string', 'cangroup': True, 'canmatch': True, 'cansort': True, 'choice_type': 1, 'choices': {'': '-- None --', 'primary': 'Primary', 'secondary': 'Secondary', 'shared': 'Shared'}, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Defines the function of the asset record', 'internal_type': 'choice', 'label': 'Asset function', 'mandatory': False, 'max_length': 40, 'max_unit': 'days', 'name': 'asset_function', 'read_only': False, 'type': 'choice'}, 'asset_tag': {'attributes': 'edge_encryption_enabled=true,no_audit=true', 'base_type': 'string', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Bar code for tracking the asset', 'internal_type': 'string', 'label': 'Asset tag', 'mandatory': False, 'max_length': 40, 'max_unit': 'days', 'name': 'asset_tag', 'read_only': False, 'type': 'string'}, 'assigned_to': {'attributes': 'encode_utf8=false,readonly_clickthrough=true', 'base_type': 'GUID', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Person using or primarily responsible for this item', 'internal_type': 'reference', 'label': 'Assigned to', 'mandatory': False, 'max_length': 32, 'max_unit': 'days', 'name': 'assigned_to', 'read_only': False, 'reference': 'sys_user', 'reference_attributes': {'display_field': 'name', 'ref_ac_columns': ['email'], 'ref_ac_columns_search': 'true', 'ref_ac_display_value': True, 'ref_auto_completer': 'AJAXTableCompleter'}, 'reference_display_field': 'name', 'type': 'reference'}, 'ci': {'attributes': 'encode_utf8=false,no_audit=true,readonly_clickthrough=true', 'base_type': 'GUID', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Associated CI this asset got automatically created along with based on model category', 'internal_type': 'reference', 'label': 'Configuration Item', 'mandatory': False, 'max_length': 32, 'max_unit': 'days', 'name': 'ci', 'read_only': True, 'reference': 'cmdb_ci', 'reference_attributes': {'display_field': 'name', 'ref_ac_columns': [], 'ref_ac_columns_search': '', 'ref_ac_display_value': True, 'ref_auto_completer': ''}, 'reference_display_field': 'name', 'type': 'reference'}, 'company': {'attributes': 'encode_utf8=false,no_audit=true,readonly_clickthrough=true', 'base_type': 'GUID', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Company to which this asset belongs', 'internal_type': 'reference', 'label': 'Company', 'mandatory': False, 'max_length': 32, 'max_unit': 'days', 'name': 'company', 'read_only': False, 'reference': 'core_company', 'reference_attributes': {'display_field': 'name', 'ref_ac_columns': [], 'ref_ac_columns_search': '', 'ref_ac_display_value': True, 'ref_auto_completer': ''}, 'reference_display_field': 'name', 'type': 'reference'}, 'cost': {'attributes': 'no_audit=true,omit_sys_original=true', 'base_type': 'decimal', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '0', 'filterable': True, 'hint': 'Price at which the asset was purchased', 'internal_type': 'currency', 'label': 'Cost', 'mandatory': False, 'max_length': 20, 'max_unit': 'days', 'name': 'cost', 'read_only': False, 'type': 'currency'}, 'display_name': {'attributes': 'edge_encryption_enabled=true,no_audit=true', 'base_type': 'string', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Name of the asset as it appears in record lists. Automatically set at asset creation based on Asset Tag and Model', 'internal_type': 'string', 'label': 'Display name', 'mandatory': False, 'max_length': 255, 'max_unit': 'days', 'name': 'display_name', 'read_only': True, 'type': 'string'}, 'install_status': {'base_type': 'string', 'cangroup': True, 'canmatch': True, 'cansort': True, 'choice_type': 3, 'choices': {'1': 'In use', '10': 'Consumed', '11': 'Build', '2': 'On order', '3': 'In maintenance', '6': 'In stock', '7': 'Retired', '8': 'Missing', '9': 'In transit'}, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Current state of the asset', 'internal_type': 'integer', 'label': 'State', 'mandatory': False, 'max_length': 40, 'max_unit': 'days', 'name': 'install_status', 'read_only': False, 'type': 'choice'}, 'model_category': {'attributes': 'encode_utf8=false,no_audit=true,readonly_clickthrough=true', 'base_type': 'GUID', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Model category of the asset. Controls whether or not the asset is linked to a CI', 'internal_type': 'reference', 'label': 'Model category', 'mandatory': True, 'max_length': 32, 'max_unit': 'days', 'name': 'model_category', 'read_only': False, 'reference': 'cmdb_model_category', 'reference_attributes': {'display_field': 'name', 'ref_ac_columns': [], 'ref_ac_columns_search': '', 'ref_ac_display_value': True, 'ref_auto_completer': ''}, 'reference_display_field': 'name', 'type': 'reference'}, 'serial_number': {'attributes': 'edge_encryption_enabled=true,no_audit=true', 'base_type': 'string', 'cangroup': True, 'canmatch': True, 'cansort': True, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Serial number of this asset', 'internal_type': 'string', 'label': 'Serial number', 'mandatory': False, 'max_length': 100, 'max_unit': 'days', 'name': 'serial_number', 'read_only': False, 'type': 'string'}, 'substatus': {'attributes': 'edge_encryption_enabled=true', 'base_type': 'string', 'cangroup': True, 'canmatch': True, 'cansort': True, 'choice_type': 1, 'choices': {'': '-- None --', 'available': 'Available', 'defective': 'Defective', 'disposed': 'Disposed', 'donated': 'Donated', 'lost': 'Lost', 'pending_disposal': 'Pending disposal', 'pending_evaluation': 'Pending evaluation', 'pending_install': 'Pending install', 'pending_repair': 'Pending repair', 'pending_resale': 'Pending resale', 'pending_transfer': 'Pending transfer', 'pre_allocated': 'Pre-allocated', 'reserved': 'Reserved', 'sold': 'Sold', 'stolen': 'Stolen', 'vendor_credit': 'Vendor credit'}, 'column_type': 'element', 'default': '', 'filterable': True, 'hint': 'Current substate of the asset. Dependent on State', 'internal_type': 'string', 'label': 'Substate', 'mandatory': False, 'max_length': 40, 'max_unit': 'days', 'name': 'substatus', 'read_only': False, 'type': 'choice'}}}

Validation code:
import json
import os
import re
import urllib.parse as parse
from typing import Any, Dict, List, Tuple

import requests


def validate(messages: List[Dict[str, str]], config: Dict[str, Any]) -> int:
    """
    Validate a ServiceNow-style filter encoded in the 'sysparm_query' of the first URL
    found in the content of the LAST message.

    Config schema (example keys; provide what you need):
      {
        # URL check (optional; if present, enforce)
        "expected_url_prefix": "https://instance.service-now.com/now/nav/ui/classic/...",  # optional
        "expected_url_suffix": "/nav_to.do?uri=...",                                        # optional

        # Filter expectations
        "filter_kind": "AND" or "OR",
        "filter_len": 2,
        "filter_columns": ["priority", "assigned_to"],
        "filter_values": ["High", "Jane Doe"],  # display values after expansion

        # Column metadata for value expansion (optional, but needed for refs/choices)
        "columns": {
          "assigned_to": {
            "type": "reference",
            "reference_display_map": {  # sys_id -> display_value
              "6816f79cc0a8016400b98a06818d57c7": "Jane Doe"
            }
          },
          "priority": {
            "type": "choice",
            "choices": {"1": "Critical", "2": "High", "": "-- None --"}
          }
        },

        # Operators (optional; defaults provided)
        "operators": {
          "EQUALS": "=",
          "ISEMPTY": "ISEMPTY",
          "EMPTYSTRING": "EMPTYSTRING",
          "NOT_EQUALS": "!=",
          "STARTSWITH": "STARTSWITH"
        }
      }

    Returns:
      (score, passed, message, meta)
        score: 1 or 0
    """

    # Define constants
    OPERATOR_EQUALS = "="
    OPERATOR_NOT_EQUALS = "!="
    OPERATOR_STARTSWITH = "STARTSWITH"
    OPERATOR_LIKE = "LIKE"
    OPERATOR_CONTAINS = "CONTAINS"
    OPERATOR_ISEMPTY = "ISEMPTY"
    OPERATOR_EMPTYSTRING = "EMPTYSTRING"

    # check whether we have the necessary information to validate
    if (
        not "table_name" in config
        or not "filter_kind" in config
        or not "filter_len" in config
        or not "filter_columns" in config
        or not "filter_values" in config
    ):
        raise ValueError(
            "Target must contain 'table_name', 'filter_kind', 'filter_len', 'filter_columns', and 'filter_values'"
        )

    # check whether we have messages
    if not messages:
        raise ValueError("Messages must not be empty")

    # --- Step 1: Extract last message content ---
    content = messages[-1].get("content", "")

    # --- Step 2: Extract all URLs — markdown links first, then bare URLs ---
    expected_table = config["table_name"]
    urls = []
    for md in re.finditer(r"\[.*?\]\((https?://[^)]+)\)", content):
        urls.append(md.group(1))
    for bare in re.finditer(r"https?://\S+", content):
        candidate = bare.group(0).rstrip(")].,")
        if candidate not in urls:
            urls.append(candidate)
    if not urls:
        return 0

    # --- Step 3: Pick the URL that contains the expected table name, fall back to first ---
    raw_url = urls[0]
    for u in urls:
        if f"{expected_table}_list.do" in parse.unquote(u):
            raw_url = u
            break
    page_url = parse.unquote(raw_url)

    # --- Step 4: Parse query params — handle both direct and nav_to.do?uri=... formats ---
    parsed = parse.urlparse(page_url)
    page_qs = parse.parse_qs(parsed.query)

    # If URL is nav_to.do?uri=..., extract the inner URI and parse it
    if "uri" in page_qs:
        inner_url = parse.unquote(page_qs["uri"][0])
        inner_parsed = parse.urlparse(inner_url if "://" in inner_url else "http://x/" + inner_url)
        page_qs = parse.parse_qs(inner_parsed.query)
        page_url = inner_url  # use inner URL for table name extraction

    if "sysparm_query" not in page_qs:
        return 0
    page_sysparam_query = page_qs["sysparm_query"][0]

    if not page_sysparam_query:
        return 0

    # get table name from url
    table_match = re.search(r"(\w+)_list\.do", page_url)
    if not table_match:
        return 0
    table_name = table_match.group(1)
    if table_name != config["table_name"]:
        return 0

    # Strip trailing ^EQ (query terminator, not an operator)
    page_sysparam_query = page_sysparam_query.removesuffix("^EQ")

    # Normalize empty-field operators to = (e.g. fieldISEMPTY -> field=)
    page_sysparam_query = page_sysparam_query.replace("ISEMPTY", "=")
    page_sysparam_query = page_sysparam_query.replace("EMPTYSTRING", "=")

    # Normalize NQ to OR, EQ to plain AND separator
    page_sysparam_query = page_sysparam_query.replace("^NQ", "^OR")
    page_sysparam_query = page_sysparam_query.replace("^EQ", "^")

    # Determine AND/OR kind
    # LIMITATION: we only support AND and OR, not mixed
    if "^OR" in page_sysparam_query:
        current_kind = "OR"
        current_sep = "^OR"
    else:
        current_kind = "AND"
        current_sep = "^"

    # validate filter kind
    if current_kind != config.get("filter_kind"):
        return 0

    parts = page_sysparam_query.split(current_sep)
    parts = [p for p in parts if p]

    # validate filter length
    expected_len = int(config.get("filter_len"))
    if len(parts) != expected_len:
        return 0

    # Parse column names, operators, and values
    current_columns, current_values, current_operators = [], [], []

    for predicate in parts:
        pred = predicate.strip()

        if OPERATOR_EMPTYSTRING in pred:
            current_columns.append(pred.replace(OPERATOR_EMPTYSTRING, "").strip())
            current_operators.append("=")
            current_values.append("")
        elif OPERATOR_ISEMPTY in pred:
            current_columns.append(pred.replace(OPERATOR_ISEMPTY, "").strip())
            current_operators.append("=")
            current_values.append("")
        elif OPERATOR_NOT_EQUALS in pred or OPERATOR_STARTSWITH in pred:
            # Unsupported operators
            return 0
        elif OPERATOR_CONTAINS in pred:
            col, val = pred.split(OPERATOR_CONTAINS, 1)
            current_columns.append(col.strip())
            current_operators.append("=")
            current_values.append(val.strip())
        elif OPERATOR_LIKE in pred:
            col, val = pred.split(OPERATOR_LIKE, 1)
            current_columns.append(col.strip())
            current_operators.append("=")
            current_values.append(val.strip())
        elif OPERATOR_EQUALS in pred:
            col, val = pred.split(OPERATOR_EQUALS, 1)
            current_columns.append(col.strip())
            current_operators.append("=")
            current_values.append(val.strip())
        else:
            # Unsupported operators
            return 0

    # Validate columns
    expected_columns = config.get("filter_columns", [])
    if set(current_columns) != set(expected_columns):
        return 0

    # Value expansion for reference/choice columns (if metadata provided)
    col_meta = config.get("columns", {})
    # Detect homogeneous multi-row filter on same column
    is_homogeneous = len(set(current_columns)) < len(current_columns)
    if is_homogeneous and len(set(current_columns)) != 1:
        return 0

    for i, (col, val) in enumerate(zip(current_columns, current_values)):
        meta = col_meta.get(col, {})
        col_type = meta.get("type", "string")

        if col_type == "reference" and val != "":
            ref_map = meta.get("reference_display_map", {})
            if ref_map:
                # Use pre-computed map if available
                display = ref_map.get(val, val)
            else:
                # Query the instance to resolve sys_id -> display value
                ref_table = meta.get("reference")
                ref_field = meta.get("reference_attributes", {}).get("display_field")
                if ref_table and ref_field:
                    response = requests.request(
                        method="GET",
                        url=os.getenv("SERVICENOW_INSTANCE_URL", "").rstrip("/") + f"/api/now/table/{ref_table}",
                        headers={
                            "Content-Type": "application/json",
                            "Accept": "application/json",
                            **json.loads(os.getenv("SERVICENOW_EXTRA_HTTP_HEADERS", "{}")),
                        },
                        params={
                            "sysparm_query": f"sys_id={val}",
                            "sysparm_fields": ref_field,
                            "sysparm_display_value": "all",
                        },
                    )
                    response.raise_for_status()
                    result = response.json().get("result", [])
                    if result:
                        field_val = result[0].get(ref_field, {})
                        if isinstance(field_val, dict) and "display_value" in field_val:
                            display = field_val["display_value"]
                        else:
                            display = field_val
                    else:
                        display = val  # fallback to raw if not found
                else:
                    display = val  # fallback to raw if no reference info

            if is_homogeneous:
                current_values[i] = display
            else:
                current_values[current_columns.index(col)] = display

        elif col_type == "choice":
            choices = meta.get("choices", {})
            display = choices.get(val, "-- None --")
            current_values[current_columns.index(col)] = display

        # strings/numbers/booleans: keep as-is

    expected_values = config.get("filter_values", [])
    if set(current_values) != set(expected_values):
        return 0

    return 1