WebApp: Query Parameter Filters in ORDS with AutoREST Tables

Demo, show me the demo! Make your power points and blog posts, come to life! Demonstrating REST APIs vis cURL or Postman can get kind of tedious, or even boring.

If I had time, I would just build a local web app to show it off. But since I don’t have that much time, I’ll do the next best thing, have my AI Agent build the app for me!

The setup

I have an Oracle AI Database (26ai, but 19c, 21c, or 23ai would also be fine.) I have Oracle REST Data Services (ORDS) 25.4. I have a table, EMPLOYEES. It is registered for ORDS, to enable the AutoREST feature.

Our Oracle AI Database table REST APIs make working with your data, easy. I can GET

all my rows
one row
some rows, based on some filters
above, but sorted

This query parameter filter syntax is described and/or demonstrated:

in the ORDS Docs
here on my blog

My REST API is described here http://localhost:8080/ords/hr/open-api-catalog/employees/, and looks like this:

JSON

{
"openapi": "3.0.0",
"info": {
"title": "ORDS generated API for EMPLOYEES",
"version": "1.0.0"
},
"servers": [
{
"url": "http://localhost:8080/ords/hr/employees"
}
],
"paths": {
"/": {
"delete": {
"description": "Remove a record from EMPLOYEES",
"responses": {
"204": {
"description": "Deleted result."
}
},
"parameters": [
{
"name": "q",
"in": "query",
"schema": {
"type": "string"
},
"description": "JSON QBE parameter."
}
]
},
"get": {
"description": "Retrieve records from EMPLOYEES",
"responses": {
"200": {
"description": "The queried record.",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"items": {
"type": "array",
"items": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
}
}
}
},
"parameters": [
{
"name": "q",
"in": "query",
"schema": {
"type": "string"
},
"description": "JSON QBE parameter."
}
]
},
"post": {
"description": "Create a new record on EMPLOYEES",
"responses": {
"201": {
"description": "The successfully created record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
},
"parameters": [],
"requestBody": {
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
}
},
"/batchload": {
"post": {
"description": "Create new records on EMPLOYEES",
"responses": {
"200": {
"description": "The status of the processed records.",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {}
}
}
}
}
},
"parameters": [],
"requestBody": {
"content": {
"text/csv": {
"schema": {}
}
}
}
}
},
"/{id}": {
"delete": {
"description": "Remove a record from EMPLOYEES",
"responses": {
"204": {
"description": "Deleted result."
}
},
"parameters": [
{
"name": "id",
"in": "path",
"required": true,
"schema": {
"type": "string",
"pattern": "^[^/]+$"
},
"description": "Primary key(s):  employee_id"
}
]
},
"get": {
"description": "Retrieve a record from EMPLOYEES",
"responses": {
"200": {
"description": "The queried record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
},
"parameters": [
{
"name": "id",
"in": "path",
"required": true,
"schema": {
"type": "string",
"pattern": "^[^/]+$"
},
"description": "Primary key(s):  employee_id"
}
]
},
"put": {
"description": "Create or update a record on EMPLOYEES",
"responses": {
"200": {
"description": "The successfully updated record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
},
"201": {
"description": "The successfully created record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
},
"parameters": [
{
"name": "id",
"in": "path",
"required": true,
"schema": {
"type": "string",
"pattern": "^[^/]+$"
},
"description": "Primary key(s):  employee_id"
}
],
"requestBody": {
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
}
}
},
"components": {
"schemas": {
"DATE": {
"type": "string",
"format": "date-time",
"pattern": "^\\d{4}-[01]\\d-[0123]\\dT[012]\\d:[0-5]\\d:[0-5]\\d(.\\d+)?(Z|([-+][012]\\d:[0-5]\\d))$"
},
"NUMBER": {
"type": "number"
},
"VARCHAR2": {
"type": "string"
},
"employees_ITEM": {
"properties": {
"commission_pct": {
"$ref": "#/components/schemas/NUMBER"
},
"department_id": {
"$ref": "#/components/schemas/NUMBER"
},
"email": {
"$ref": "#/components/schemas/VARCHAR2"
},
"employee_id": {
"x-ords-primary-key": "true",
"$ref": "#/components/schemas/NUMBER"
},
"first_name": {
"$ref": "#/components/schemas/VARCHAR2"
},
"hire_date": {
"$ref": "#/components/schemas/DATE"
},
"job_id": {
"$ref": "#/components/schemas/VARCHAR2"
},
"last_name": {
"$ref": "#/components/schemas/VARCHAR2"
},
"manager_id": {
"$ref": "#/components/schemas/NUMBER"
},
"phone_number": {
"$ref": "#/components/schemas/VARCHAR2"
},
"salary": {
"$ref": "#/components/schemas/NUMBER"
}
}
}
}
}
}

{
"openapi": "3.0.0",
"info": {
"title": "ORDS generated API for EMPLOYEES",
"version": "1.0.0"
},
"servers": [
{
"url": "http://localhost:8080/ords/hr/employees"
}
],
"paths": {
"/": {
"delete": {
"description": "Remove a record from EMPLOYEES",
"responses": {
"204": {
"description": "Deleted result."
}
},
"parameters": [
{
"name": "q",
"in": "query",
"schema": {
"type": "string"
},
"description": "JSON QBE parameter."
}
]
},
"get": {
"description": "Retrieve records from EMPLOYEES",
"responses": {
"200": {
"description": "The queried record.",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"items": {
"type": "array",
"items": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
}
}
}
},
"parameters": [
{
"name": "q",
"in": "query",
"schema": {
"type": "string"
},
"description": "JSON QBE parameter."
}
]
},
"post": {
"description": "Create a new record on EMPLOYEES",
"responses": {
"201": {
"description": "The successfully created record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
},
"parameters": [],
"requestBody": {
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
}
},
"/batchload": {
"post": {
"description": "Create new records on EMPLOYEES",
"responses": {
"200": {
"description": "The status of the processed records.",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {}
}
}
}
}
},
"parameters": [],
"requestBody": {
"content": {
"text/csv": {
"schema": {}
}
}
}
}
},
"/{id}": {
"delete": {
"description": "Remove a record from EMPLOYEES",
"responses": {
"204": {
"description": "Deleted result."
}
},
"parameters": [
{
"name": "id",
"in": "path",
"required": true,
"schema": {
"type": "string",
"pattern": "^[^/]+$"
},
"description": "Primary key(s):  employee_id"
}
]
},
"get": {
"description": "Retrieve a record from EMPLOYEES",
"responses": {
"200": {
"description": "The queried record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
},
"parameters": [
{
"name": "id",
"in": "path",
"required": true,
"schema": {
"type": "string",
"pattern": "^[^/]+$"
},
"description": "Primary key(s):  employee_id"
}
]
},
"put": {
"description": "Create or update a record on EMPLOYEES",
"responses": {
"200": {
"description": "The successfully updated record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
},
"201": {
"description": "The successfully created record.",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
},
"parameters": [
{
"name": "id",
"in": "path",
"required": true,
"schema": {
"type": "string",
"pattern": "^[^/]+$"
},
"description": "Primary key(s):  employee_id"
}
],
"requestBody": {
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/employees_ITEM"
}
}
}
}
}
}
},
"components": {
"schemas": {
"DATE": {
"type": "string",
"format": "date-time",
"pattern": "^\\d{4}-[01]\\d-[0123]\\dT[012]\\d:[0-5]\\d:[0-5]\\d(.\\d+)?(Z|([-+][012]\\d:[0-5]\\d))$"
},
"NUMBER": {
"type": "number"
},
"VARCHAR2": {
"type": "string"
},
"employees_ITEM": {
"properties": {
"commission_pct": {
"$ref": "#/components/schemas/NUMBER"
},
"department_id": {
"$ref": "#/components/schemas/NUMBER"
},
"email": {
"$ref": "#/components/schemas/VARCHAR2"
},
"employee_id": {
"x-ords-primary-key": "true",
"$ref": "#/components/schemas/NUMBER"
},
"first_name": {
"$ref": "#/components/schemas/VARCHAR2"
},
"hire_date": {
"$ref": "#/components/schemas/DATE"
},
"job_id": {
"$ref": "#/components/schemas/VARCHAR2"
},
"last_name": {
"$ref": "#/components/schemas/VARCHAR2"
},
"manager_id": {
"$ref": "#/components/schemas/NUMBER"
},
"phone_number": {
"$ref": "#/components/schemas/VARCHAR2"
},
"salary": {
"$ref": "#/components/schemas/NUMBER"
}
}
}
}
}
}

My AI Agent Prompt

The Result

About 5 minutes later, I pretty much had a working app.

Pretty much, meaning I needed to step in for the following corrections in follow-up prompts:

remind the Agent the ORDER BY clause was part of QBE, not part of the LIMIT clause
enable scrolling on my results grid, and add pagination Next/Previous links

My app lets me toggle/input filters and sorting criteria, and it pretty prints the QBE JSON, and previews the complete ORDS URL for the GET request.

As an example, above I asked for ALL employees:

having a last name starting with the letter ‘S’
whose JOB_ID was ‘SA_REP’
with a minimum salary of 7000
with any COMMISSION PCT value (except for NULL)
hired between Jan 1 2014 and June 1 2015
ordered by LAST NAME desc

And our app took those inputs and decided that could be acquired via this QBE JSON payload on a query parameter:

JSON

{
  "$and": [
    {
      "last_name": {
        "$like": "S%"
      }
    },
    {
      "job_id": {
        "$eq": "SA_REP"
      }
    },
    {
      "salary": {
        "$gte": 7000
      }
    },
    {
      "hire_date": {
        "$gte": {
          "$date": "2014-01-01T00:00:00Z"
        }
      }
    },
    {
      "hire_date": {
        "$lte": {
          "$date": "2015-06-01T23:59:59Z"
        }
      }
    },
    {
      "commission_pct": {
        "$notnull": null
      }
    }
  ],
  "$orderby": {
    "LAST_NAME": "desc"
  }
}

{
  "$and": [
    {
      "last_name": {
        "$like": "S%"
      }
    },
    {
      "job_id": {
        "$eq": "SA_REP"
      }
    },
    {
      "salary": {
        "$gte": 7000
      }
    },
    {
      "hire_date": {
        "$gte": {
          "$date": "2014-01-01T00:00:00Z"
        }
      }
    },
    {
      "hire_date": {
        "$lte": {
          "$date": "2015-06-01T23:59:59Z"
        }
      }
    },
    {
      "commission_pct": {
        "$notnull": null
      }
    }
  ],
  "$orderby": {
    "LAST_NAME": "desc"
  }
}

All the code

My web page source is below. I QA’d this for about 10 minutes, so I’m sure there’s more clean-up required, but it’s a great start.

The interesting part of the code above is the FILTER BUILDER section, that starts on line 700.

HTML

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>ORDS Query Filter Explorer — HR.EMPLOYEES</title>
<link rel="preconnect" href="https://fonts.googleapis.com">
<link href="https://fonts.googleapis.com/css2?family=Syne:wght@400;600;700;800&family=DM+Sans:ital,opsz,wght@0,9..40,300;0,9..40,400;0,9..40,500;1,9..40,300&family=JetBrains+Mono:wght@300;400;500;600&display=swap" rel="stylesheet">
<style>
:root {
  --bg:     #080a0d;
  --bg1:    #0e1218;
  --bg2:    #141b24;
  --bg3:    #1c2530;
  --border: #232d3a;
  --border2:#2e3d50;
  --red:    #c94236;
  --red-hl: #e04535;
  --cyan:   #22d3ee;
  --amber:  #f59e0b;
  --green:  #22c55e;
  --purple: #a78bfa;
  --text:   #dde5f0;
  --text2:  #8fa3b8;
  --text3:  #404f62;
  --syne:   'Syne', sans-serif;
  --body:   'DM Sans', sans-serif;
  --mono:   'JetBrains Mono', monospace;
}

*,*::before,*::after{box-sizing:border-box;margin:0;padding:0}

body {
  font-family: var(--body);
  background: var(--bg);
  color: var(--text);
  min-height: 100vh;
  font-size: 14px;
  line-height: 1.6;
}

/* ── SCROLLBAR ── */
::-webkit-scrollbar{width:4px;height:4px}
::-webkit-scrollbar-track{background:var(--bg1)}
::-webkit-scrollbar-thumb{background:var(--border2);border-radius:2px}

/* ── HEADER ── */
.header {
  background: var(--bg1);
  border-bottom: 1px solid var(--border);
  padding: 0 28px;
  height: 58px;
  display: flex;
  align-items: center;
  justify-content: space-between;
  position: sticky;
  top: 0;
  z-index: 100;
}
.hb { display:flex; align-items:center; gap:14px }
.oracle-badge {
  background: var(--red);
  color: #fff;
  font-family: var(--syne);
  font-size: 10px;
  font-weight: 800;
  letter-spacing: .12em;
  padding: 4px 9px;
  text-transform: uppercase;
  clip-path: polygon(0 0,calc(100% - 6px) 0,100% 100%,0 100%);
}
.header-title { font-family:var(--syne); font-size:15px; font-weight:700; letter-spacing:.02em }
.header-sub   { font-family:var(--mono); font-size:10px; color:var(--text3); letter-spacing:.05em }
.endpoint-pill {
  display:flex; align-items:center; gap:8px;
  background:var(--bg2); border:1px solid var(--border);
  padding:5px 12px; border-radius:2px;
  font-family:var(--mono); font-size:11px; color:var(--cyan);
}
.dot-live { width:6px;height:6px;background:var(--green);border-radius:50%;animation:blink 2s ease-in-out infinite }
@keyframes blink{0%,100%{opacity:1}50%{opacity:.3}}

/* ── LAYOUT ── */
.app {
  display: grid;
  grid-template-columns: var(--sidebar-w, 320px) 4px 1fr;
  grid-template-rows: auto 1fr;
  height: calc(100vh - 58px);
}

/* ── RESIZE HANDLE ── */
.resizer {
  grid-row: 1 / 3;
  background: var(--border);
  cursor: col-resize;
  transition: background .15s;
  position: relative;
  z-index: 10;
}
.resizer:hover, .resizer.dragging { background: var(--cyan); }
.resizer::after {
  content: '';
  position: absolute;
  inset: 0 -4px;   /* wider invisible hit target */
}

/* ── SIDEBAR ── */
.sidebar {
  grid-row: 1 / 3;
  background: var(--bg1);
  border-right: none;
  overflow-y: auto;
  padding: 20px 18px;
  display: flex;
  flex-direction: column;
  gap: 18px;
  min-width: 220px;
  max-width: 600px;
}
.sec-label {
  font-family:var(--syne); font-size:9px; font-weight:800;
  letter-spacing:.14em; text-transform:uppercase; color:var(--text3);
  display:flex; align-items:center; gap:8px; margin-bottom:10px;
}
.sec-label::after{content:'';flex:1;height:1px;background:var(--border)}
.fg { display:flex; flex-direction:column; gap:10px }
.fr { display:grid; grid-template-columns:1fr 1fr; gap:8px }
.field { display:flex; flex-direction:column; gap:3px }
.fl { font-family:var(--mono); font-size:9px; color:var(--text2); letter-spacing:.06em; text-transform:uppercase }
.field input, .field select {
  background:var(--bg2); border:1px solid var(--border);
  color:var(--text); padding:6px 9px;
  font-family:var(--mono); font-size:12px;
  border-radius:2px; outline:none; width:100%;
  transition:border-color .15s, box-shadow .15s;
}
.field input:focus,.field select:focus{border-color:var(--cyan);box-shadow:0 0 0 2px rgba(34,211,238,.08)}
.field input::placeholder{color:var(--text3)}
.field select option{background:var(--bg2)}
.help{font-family:var(--mono);font-size:9px;color:var(--text3);line-height:1.5;margin-top:1px}

/* operator inline select */
.op-sel {
  background:var(--bg2); border:1px solid var(--border);
  color:var(--amber); padding:6px 5px;
  font-family:var(--mono); font-size:10px;
  border-radius:2px; outline:none; cursor:pointer; flex-shrink:0;
}
/* full-width stacked operator select */
.full-op {
  width: 100%;
  padding: 6px 9px;
  font-size: 11px;
  color: var(--amber);
  background: var(--bg2);
  border: 1px solid var(--border);
  border-radius: 2px;
  outline: none;
  cursor: pointer;
  transition: border-color .15s;
}
.full-op:focus { border-color: var(--cyan); box-shadow: 0 0 0 2px rgba(34,211,238,.08); }
.full-op option { background: var(--bg2); }
.inline-pair { display:flex; gap:4px; align-items:stretch }
.inline-pair .op-sel { min-width:82px }
.inline-pair input { flex:1; min-width:0 }

/* toggle pairs */
.toggle {
  display:flex; background:var(--bg2);
  border:1px solid var(--border); border-radius:2px; overflow:hidden;
}
.tgl-btn {
  flex:1; padding:5px 8px;
  background:transparent; border:none;
  color:var(--text2); font-family:var(--syne); font-size:10px;
  font-weight:700; letter-spacing:.08em; cursor:pointer; transition:all .15s;
  text-align:center;
}
.tgl-btn.on{background:var(--red);color:#fff}

/* commission row */
.comm-row { display:flex; gap:5px }
.comm-btn {
  flex:1; padding:5px; background:var(--bg2);
  border:1px solid var(--border); color:var(--text2);
  font-family:var(--mono); font-size:9px; cursor:pointer;
  transition:all .15s; text-align:center; border-radius:2px;
}
.comm-btn.on{border-color:var(--cyan);color:var(--cyan);background:rgba(34,211,238,.06)}

/* action buttons */
.btn-exec {
  width:100%; padding:10px; background:var(--red); color:#fff;
  border:none; font-family:var(--syne); font-size:12px; font-weight:800;
  letter-spacing:.07em; text-transform:uppercase; cursor:pointer;
  transition:all .15s; border-radius:2px;
}
.btn-exec:hover{background:var(--red-hl);box-shadow:0 4px 22px rgba(201,66,54,.4)}
.btn-reset {
  width:100%; padding:7px; background:transparent; color:var(--text2);
  border:1px solid var(--border); font-family:var(--syne); font-size:10px;
  font-weight:700; letter-spacing:.07em; text-transform:uppercase;
  cursor:pointer; transition:all .15s; border-radius:2px;
}
.btn-reset:hover{border-color:var(--border2);color:var(--text)}

/* ── QUERY PANEL ── */
.qpanel {
  background:var(--bg1); border-bottom:1px solid var(--border);
  padding:14px 22px; grid-column: 3;
}
.qpanel-inner { display:flex; gap:14px; align-items:flex-start }
.qblocks { flex:1; display:flex; flex-direction:column; gap:8px }
.qblock {
  background:var(--bg); border:1px solid var(--border);
  border-radius:2px; padding:10px 14px; position:relative;
}
.qblock.cyan{ border-left:3px solid var(--cyan) }
.qblock.amber{ border-left:3px solid var(--amber) }
.qbl { font-family:var(--mono); font-size:8px; letter-spacing:.12em; color:var(--text3); text-transform:uppercase; margin-bottom:5px }
.qtext {
  font-family:var(--mono); font-size:11px; color:var(--cyan);
  word-break:break-all; max-height:56px; overflow-y:auto;
  white-space:pre-wrap; line-height:1.55;
}
.utext { font-family:var(--mono); font-size:10px; color:var(--text2); word-break:break-all; line-height:1.5 }
.utext .ub{color:var(--text3)} .utext .up{color:var(--amber)} .utext .uv{color:var(--cyan)}
.badges{ display:flex; flex-wrap:wrap; gap:5px; margin-top:2px }
.badge {
  display:inline-flex; align-items:center; gap:4px;
  background:var(--bg3); border:1px solid var(--border2);
  padding:2px 7px; border-radius:2px;
  font-family:var(--mono); font-size:9px;
}
.badge .bc{color:var(--amber)} .badge .bo{color:var(--text3)} .badge .bv{color:var(--cyan)}

.qactions { display:flex; flex-direction:column; gap:5px }
.cbtn {
  padding:6px 12px; background:var(--bg2); border:1px solid var(--border);
  color:var(--text2); font-family:var(--mono); font-size:10px;
  cursor:pointer; transition:all .15s; border-radius:2px; white-space:nowrap;
}
.cbtn:hover{border-color:var(--cyan);color:var(--cyan)}
.cbtn.open:hover{border-color:var(--amber);color:var(--amber)}

/* ── MAIN ── */
.main {
  padding: 14px 22px 0 22px;
  overflow: hidden;                  /* main itself does NOT scroll */
  display: flex;
  flex-direction: column;
  gap: 10px;
  grid-column: 3;
  min-height: 0;                     /* critical: lets flex children shrink below content size */
}

.results-bar { display:flex; align-items:center; justify-content:space-between; flex-shrink:0 }
.count-pill {
  display:flex; align-items:center; gap:6px;
  background:var(--bg2); border:1px solid var(--border);
  padding:4px 12px; border-radius:2px;
  font-family:var(--mono); font-size:11px;
}
.count-num{color:var(--amber);font-weight:500}
.page-loc{font-family:var(--mono);font-size:10px;color:var(--text2)}

/* table wrapper — fills remaining height, clips overflow */
.tbl-wrap {
  background:var(--bg1); border:1px solid var(--border);
  border-radius:2px;
  display: flex;
  flex-direction: column;
  flex: 1 1 0;                       /* grow to fill, but never push beyond parent */
  min-height: 0;
  overflow: hidden;
  margin-bottom: 14px;
}

/* scrollable zone — only the rows scroll, thead stays put */
.tbl-scroll {
  overflow-x: auto;
  overflow-y: auto;
  flex: 1 1 0;
  min-height: 0;
}

table { width:100%; border-collapse:collapse }
thead th {
  background:var(--bg2); padding:9px 13px;
  font-family:var(--syne); font-size:9px; font-weight:800;
  letter-spacing:.11em; text-transform:uppercase;
  color:var(--text3); border-bottom:1px solid var(--border);
  white-space:nowrap; cursor:pointer; user-select:none;
  transition:color .12s;
  position: sticky; top: 0; z-index: 2;  /* thead sticks to top of scroll area */
}
thead th:hover{color:var(--text2)}
thead th.sorted{color:var(--cyan)}
.si{margin-left:3px;font-size:9px;opacity:.5}
tbody tr{border-bottom:1px solid var(--border);transition:background .08s}
tbody tr:last-child{border-bottom:none}
tbody tr:hover{background:rgba(34,211,238,.025)}
tbody td{padding:8px 13px;font-family:var(--mono);font-size:11px;white-space:nowrap}
.tid{color:var(--text3)}
.tname{font-family:var(--body);font-size:13px;font-weight:500;color:var(--text)}
.tjob{
  display:inline-block; background:var(--bg2);
  border:1px solid var(--border); padding:1px 6px;
  font-size:9px; letter-spacing:.05em; border-radius:2px; color:var(--text2);
}
.tsal{color:var(--green);font-weight:500}
.tdate{color:var(--text2)}
.tnull{color:var(--text3);font-style:italic;font-size:10px}
.tpct{color:var(--purple)}

/* pagination — pinned to bottom of tbl-wrap, never scrolls away */
.pg {
  display:flex; align-items:center; justify-content:space-between;
  padding:10px 14px; background:var(--bg2); border-top:1px solid var(--border);
  flex-shrink: 0;
}
.pg-info{font-family:var(--mono);font-size:10px;color:var(--text2)}
.pg-btns{display:flex;gap:5px}
.pg-btn{
  padding:4px 12px; background:var(--bg1);
  border:1px solid var(--border); color:var(--text2);
  font-family:var(--mono); font-size:10px; cursor:pointer;
  transition:all .12s; border-radius:2px;
}
.pg-btn:hover:not(:disabled){border-color:var(--red);color:var(--red)}
.pg-btn:disabled{opacity:.25;cursor:not-allowed}

/* states */
.loading-st{display:flex;align-items:center;justify-content:center;padding:64px;gap:12px;color:var(--text2);font-family:var(--mono);font-size:12px}
.spin{width:16px;height:16px;border:2px solid var(--border2);border-top-color:var(--red);border-radius:50%;animation:rotate .65s linear infinite}
@keyframes rotate{to{transform:rotate(360deg)}}
.error-st{padding:28px;background:rgba(201,66,54,.04);border:1px solid rgba(201,66,54,.18);border-radius:2px}
.error-title{color:var(--red);font-family:var(--syne);font-size:13px;font-weight:700;margin-bottom:8px}
.error-body{font-family:var(--mono);font-size:11px;color:var(--text2);line-height:1.6}
.empty-st{display:flex;flex-direction:column;align-items:center;justify-content:center;padding:64px;gap:6px;color:var(--text3)}
.empty-ico{font-size:28px;opacity:.3;margin-bottom:6px}
.welcome-st{display:flex;flex-direction:column;align-items:center;padding:72px 40px;gap:14px;text-align:center}
.welcome-ico{
  width:60px;height:60px;background:var(--bg2);border:1px solid var(--border);
  display:flex;align-items:center;justify-content:center;font-size:26px;
  margin-bottom:4px; border-radius:2px;
}
.welcome-h{font-family:var(--syne);font-size:19px;font-weight:800;color:var(--text)}
.welcome-p{color:var(--text2);max-width:420px;font-size:13px;line-height:1.6}
.welcome-p code{font-family:var(--mono);font-size:11px;background:var(--bg2);padding:1px 5px;border-radius:2px;color:var(--cyan)}

/* toast */
.toast{
  position:fixed;bottom:22px;right:22px;
  background:var(--green);color:#fff;
  padding:7px 16px;border-radius:2px;
  font-family:var(--mono);font-size:11px;
  z-index:9999;pointer-events:none;
  transition:opacity .25s; opacity:0;
}
.toast.show{opacity:1}
</style>
</head>
<body>

<header class="header">
  <div class="hb">
    <div class="oracle-badge">Oracle ORDS</div>
    <div>
      <div class="header-title">AutoREST Query Filter Explorer</div>
      <div class="header-sub">HR.EMPLOYEES · Dynamic <em>q=</em> Parameter Showcase</div>
    </div>
  </div>
  <div class="endpoint-pill">
    <span class="dot-live"></span>
    localhost:8080/ords/hr/employees/
  </div>
</header>

<div class="app">

  <!-- ══════════════════════════════════════════════
       SIDEBAR
       ══════════════════════════════════════════════ -->
  <aside class="sidebar" id="sidebar">

    <!-- Logic mode -->
    <div>
      <div class="sec-label">Top-level Logic</div>
      <div class="fg">
        <div class="toggle">
          <button class="tgl-btn on" id="btn-and" onclick="setLogic('and')">AND</button>
          <button class="tgl-btn"    id="btn-or"  onclick="setLogic('or')">OR</button>
        </div>
        <span class="help">AND = implicit object merge (default)<br>OR = $or:[…] array wrapper</span>
      </div>
    </div>

    <!-- Identity -->
    <div>
      <div class="sec-label">Identity</div>
      <div class="fg">
        <div class="fr">
          <div class="field">
            <span class="fl">employee_id</span>
            <input type="number" id="f-empid" placeholder="100–206" min="100" max="206">
          </div>
          <div class="field">
            <span class="fl">emp_id op</span>
            <select id="f-empop">
              <option value="$eq">= eq</option>
              <option value="$gt">> gt</option>
              <option value="$gte">>= gte</option>
              <option value="$lt">< lt</option>
              <option value="$lte"><= lte</option>
            </select>
          </div>
        </div>
        <div class="fr">
          <div class="field">
            <span class="fl">department_id</span>
            <input type="number" id="f-deptid" placeholder="e.g. 90">
          </div>
          <div class="field">
            <span class="fl">manager_id</span>
            <input type="number" id="f-mgrid" placeholder="e.g. 100">
          </div>
        </div>
      </div>
    </div>

    <!-- Name -->
    <div>
      <div class="sec-label">Name Search</div>
      <div class="fg">
        <div class="field">
          <span class="fl">first_name</span>
          <select class="op-sel full-op" id="f-fnop">
            <option value="$like">LIKE (wildcard)</option>
            <option value="$eq">= eq (exact)</option>
            <option value="$ne">≠ ne (not equal)</option>
          </select>
          <input type="text" id="f-fn" placeholder="%Steven%">
          <span class="help">% is wildcard — e.g. %en% matches "Steven"</span>
        </div>
        <div class="field">
          <span class="fl">last_name</span>
          <select class="op-sel full-op" id="f-lnop">
            <option value="$like">LIKE (wildcard)</option>
            <option value="$eq">= eq (exact)</option>
            <option value="$ne">≠ ne (not equal)</option>
          </select>
          <input type="text" id="f-ln" placeholder="K%">
        </div>
      </div>
    </div>

    <!-- Job -->
    <div>
      <div class="sec-label">Job</div>
      <div class="fg">
        <div class="field">
          <span class="fl">job_id</span>
          <select id="f-jobid">
            <option value="">— any —</option>
            <optgroup label="Accounting">
              <option>AC_ACCOUNT</option><option>AC_MGR</option>
            </optgroup>
            <optgroup label="Administration">
              <option>AD_ASST</option><option>AD_PRES</option><option>AD_VP</option>
            </optgroup>
            <optgroup label="Finance">
              <option>FI_ACCOUNT</option><option>FI_MGR</option>
            </optgroup>
            <optgroup label="Human Resources">
              <option>HR_REP</option>
            </optgroup>
            <optgroup label="IT">
              <option>IT_PROG</option>
            </optgroup>
            <optgroup label="Marketing">
              <option>MK_MAN</option><option>MK_REP</option>
            </optgroup>
            <optgroup label="Public Relations">
              <option>PR_REP</option>
            </optgroup>
            <optgroup label="Purchasing">
              <option>PU_CLERK</option><option>PU_MAN</option>
            </optgroup>
            <optgroup label="Sales">
              <option>SA_MAN</option><option>SA_REP</option>
            </optgroup>
            <optgroup label="Shipping">
              <option>SH_CLERK</option><option>ST_CLERK</option><option>ST_MAN</option>
            </optgroup>
            <option value="__PAT__">Custom LIKE pattern…</option>
          </select>
        </div>
        <div class="field" id="job-pat-wrap" style="display:none">
          <span class="fl">job_id LIKE pattern</span>
          <input type="text" id="f-jobpat" placeholder="%CLERK">
          <span class="help">e.g. %CLERK — matches all clerks</span>
        </div>
      </div>
    </div>

    <!-- Salary -->
    <div>
      <div class="sec-label">Salary</div>
      <div class="fg">
        <div class="fr">
          <div class="field">
            <span class="fl">min ($gte)</span>
            <input type="number" id="f-salmin" placeholder="2100" min="2100" max="24000">
          </div>
          <div class="field">
            <span class="fl">max ($lte)</span>
            <input type="number" id="f-salmax" placeholder="24000" min="2100" max="24000">
          </div>
        </div>
        <span class="help">Dataset range: $2,100 – $24,000</span>
      </div>
    </div>

    <!-- Hire Date -->
    <div>
      <div class="sec-label">Hire Date</div>
      <div class="fg">
        <div class="field">
          <span class="fl">hired after ($gte)</span>
          <input type="date" id="f-hfrom" min="2011-01-13" max="2018-04-21">
        </div>
        <div class="field">
          <span class="fl">hired before ($lte)</span>
          <input type="date" id="f-hto" min="2011-01-13" max="2018-04-21">
        </div>
        <span class="help">Uses ORDS date wrapper: {"$date":"ISO-Z"}</span>
      </div>
    </div>

    <!-- Commission -->
    <div>
      <div class="sec-label">Commission PCT</div>
      <div class="comm-row">
        <button class="comm-btn on" id="cb-all"  onclick="setComm('all')">All</button>
        <button class="comm-btn"    id="cb-has"  onclick="setComm('has')">Has ($notnull)</button>
        <button class="comm-btn"    id="cb-none" onclick="setComm('none')">None ($null)</button>
      </div>
    </div>

    <!-- Display -->
    <div>
      <div class="sec-label">Display & Sort</div>
      <div class="fg">
        <div class="fr">
          <div class="field">
            <span class="fl">limit</span>
            <select id="f-limit">
              <option value="10">10</option>
              <option value="25" selected>25</option>
              <option value="50">50</option>
              <option value="100">100</option>
            </select>
          </div>
          <div class="field">
            <span class="fl">order by</span>
            <select id="f-orderby">
              <option value="">— default —</option>
              <option value="EMPLOYEE_ID">Employee ID</option>
              <option value="LAST_NAME">Last Name</option>
              <option value="SALARY">Salary</option>
              <option value="HIRE_DATE">Hire Date</option>
              <option value="DEPARTMENT_ID">Dept ID</option>
            </select>
          </div>
        </div>
        <div class="toggle">
          <button class="tgl-btn on" id="btn-asc"  onclick="setOrd('ASC')">ASC ↑</button>
          <button class="tgl-btn"    id="btn-desc" onclick="setOrd('DESC')">DESC ↓</button>
        </div>
      </div>
    </div>

    <div style="display:flex;flex-direction:column;gap:6px">
      <button class="btn-exec" onclick="runQuery()">▶ Execute Query</button>
      <button class="btn-reset" onclick="resetAll()">↺ Reset All Filters</button>
    </div>

  </aside>

  <div class="resizer" id="resizer"></div>

  <!-- ══════════════════════════════════════════════
       QUERY PREVIEW PANEL
       ══════════════════════════════════════════════ -->
  <div class="qpanel">
    <div class="qpanel-inner">
      <div class="qblocks">
        <div class="qblock cyan">
          <div class="qbl">q= filter parameter (ORDS JSON grammar)</div>
          <div class="qtext" id="qprev">{ }</div>
        </div>
        <div class="qblock amber">
          <div class="qbl">Full ORDS Request URL</div>
          <div class="utext" id="uprev"><span class="ub">http://localhost:8080/ords/hr/employees/</span></div>
        </div>
        <div class="badges" id="badges-row"></div>
      </div>
      <div class="qactions">
        <button class="cbtn" onclick="copyQ()">⎘ Copy q=</button>
        <button class="cbtn" onclick="copyURL()">⎘ Copy URL</button>
        <button class="cbtn open" onclick="openURL()">↗ Open URL</button>
      </div>
    </div>
  </div>

  <!-- ══════════════════════════════════════════════
       MAIN RESULTS
       ══════════════════════════════════════════════ -->
  <main class="main">
    <div class="results-bar">
      <div class="count-pill">
        <span class="count-num" id="rcount">—</span>
        <span>rows returned</span>
      </div>
      <span class="page-loc" id="ploc"></span>
    </div>
    <div class="tbl-wrap" id="tbl-wrap">
      <div class="welcome-st">
        <div class="welcome-ico">⚡</div>
        <div class="welcome-h">ORDS AutoREST Query Filter Explorer</div>
        <p class="welcome-p">
          Build a filter using the controls on the left, then click <strong>Execute Query</strong>.<br>
          Watch the <code>q=</code> parameter assemble in real time — showcasing how a single
          AutoREST endpoint handles every query shape without any custom SQL.
        </p>
      </div>
    </div>
  </main>

</div>

<div class="toast" id="toast"></div>

<script>
// ─────────────────────────────────────────────
// STATE
// ─────────────────────────────────────────────
const BASE = 'http://localhost:8080/ords/hr/employees/';

const S = {
  logic: 'and',
  ord:   'ASC',
  comm:  'all',
  offset: 0
};

// ─────────────────────────────────────────────
// WIRE UP LIVE PREVIEW
// ─────────────────────────────────────────────
document.querySelectorAll('input, select').forEach(el => {
  el.addEventListener('input',  livePreview);
  el.addEventListener('change', livePreview);
});

document.getElementById('f-jobid').addEventListener('change', function() {
  const wrap = document.getElementById('job-pat-wrap');
  wrap.style.display = this.value === '__PAT__' ? 'flex' : 'none';
  if (this.value === '__PAT__') wrap.style.flexDirection = 'column';
});

// ─────────────────────────────────────────────
// TOGGLE HELPERS
// ─────────────────────────────────────────────
function setLogic(v) {
  S.logic = v;
  q('btn-and').classList.toggle('on', v==='and');
  q('btn-or').classList.toggle('on',  v==='or');
  livePreview();
}

function setOrd(v) {
  S.ord = v;
  q('btn-asc').classList.toggle('on',  v==='ASC');
  q('btn-desc').classList.toggle('on', v==='DESC');
  livePreview();
}

function setComm(v) {
  S.comm = v;
  ['all','has','none'].forEach(id => q('cb-'+id).classList.toggle('on', id===v));
  livePreview();
}

function q(id) { return document.getElementById(id) }
function val(id) { return q(id).value.trim() }

// ─────────────────────────────────────────────
// FILTER BUILDER
// ─────────────────────────────────────────────
function collectFilters() {
  const fs = [];

  const empid = val('f-empid');
  if (empid) fs.push({ col:'employee_id', op:val('f-empop'), v:+empid, lbl:`employee_id ${val('f-empop')} ${empid}` });

  const deptid = val('f-deptid');
  if (deptid) fs.push({ col:'department_id', op:'$eq', v:+deptid, lbl:`department_id = ${deptid}` });

  const mgrid = val('f-mgrid');
  if (mgrid)  fs.push({ col:'manager_id', op:'$eq', v:+mgrid, lbl:`manager_id = ${mgrid}` });

  const fn = val('f-fn');
  if (fn) fs.push({ col:'first_name', op:val('f-fnop'), v:fn, lbl:`first_name ${val('f-fnop')} "${fn}"` });

  const ln = val('f-ln');
  if (ln) fs.push({ col:'last_name', op:val('f-lnop'), v:ln, lbl:`last_name ${val('f-lnop')} "${ln}"` });

  const jobid = val('f-jobid');
  if (jobid && jobid !== '__PAT__') {
    fs.push({ col:'job_id', op:'$eq', v:jobid, lbl:`job_id = "${jobid}"` });
  } else if (jobid === '__PAT__') {
    const pat = val('f-jobpat');
    if (pat) fs.push({ col:'job_id', op:'$like', v:pat, lbl:`job_id LIKE "${pat}"` });
  }

  const salmin = val('f-salmin');
  if (salmin) fs.push({ col:'salary', op:'$gte', v:+salmin, lbl:`salary >= ${salmin}` });

  const salmax = val('f-salmax');
  if (salmax) fs.push({ col:'salary', op:'$lte', v:+salmax, lbl:`salary <= ${salmax}` });

  const hfrom = val('f-hfrom');
  if (hfrom) fs.push({ col:'hire_date', op:'$gte', v:{ $date: hfrom+'T00:00:00Z' }, lbl:`hire_date >= ${hfrom}`, isDate:true });

  const hto = val('f-hto');
  if (hto)   fs.push({ col:'hire_date', op:'$lte', v:{ $date: hto+'T23:59:59Z' },  lbl:`hire_date <= ${hto}`,   isDate:true });

  if (S.comm === 'has')  fs.push({ col:'commission_pct', op:'$notnull', v:null, lbl:'commission_pct IS NOT NULL', isNull:true });
  if (S.comm === 'none') fs.push({ col:'commission_pct', op:'$null',    v:null, lbl:'commission_pct IS NULL',     isNull:true });

  return fs;
}

function buildQ(fs) {
  const orderby = val('f-orderby');

  // Build atomic predicate objects
  function pred(f) {
    if (f.op === '$null')    return { [f.col]: { $null:    null } };
    if (f.op === '$notnull') return { [f.col]: { $notnull: null } };
    return { [f.col]: { [f.op]: f.v } };
  }

  // $orderby clause — lives inside q= JSON per ORDS grammar
  const orderClause = orderby
    ? { $orderby: { [orderby]: S.ord.toLowerCase() } }
    : null;

  if (!fs.length) {
    // No filters — return just the orderby clause (or null if nothing at all)
    return orderClause || null;
  }

  const preds = fs.map(pred);

  let qObj;
  if (S.logic === 'or') {
    qObj = { $or: preds };
  } else {
    // AND: if any column is repeated we must use explicit $and array
    const cols = fs.map(f => f.col);
    const hasDup = cols.some((c,i) => cols.indexOf(c) !== i);
    qObj = hasDup ? { $and: preds } : Object.assign({}, ...preds);
  }

  // Merge $orderby into the top-level q object
  if (orderClause) Object.assign(qObj, orderClause);
  return qObj;
}

// ─────────────────────────────────────────────
// PARAMS & URL
// ─────────────────────────────────────────────
function buildParams(qObj, offset) {
  const ps = [];
  const limit = +val('f-limit') || 25;
  if (qObj) ps.push(['q', JSON.stringify(qObj)]);
  ps.push(['limit', limit]);
  if (offset > 0) ps.push(['offset', offset]);
  // $orderby lives inside q= per ORDS grammar — no separate URL params needed
  return ps;
}

function buildURL(qObj, offset) {
  const ps = buildParams(qObj, offset);
  if (!ps.length) return BASE;
  return BASE + '?' + ps.map(([k,v]) => `${k}=${encodeURIComponent(v)}`).join('&');
}

// ─────────────────────────────────────────────
// LIVE PREVIEW
// ─────────────────────────────────────────────
function livePreview() {
  const fs  = collectFilters();
  const qObj = buildQ(fs);
  const pretty = qObj ? JSON.stringify(qObj, null, 2) : '{ }';

  q('qprev').textContent = pretty;

  // URL with colored spans
  const ps = buildParams(qObj, 0);
  if (ps.length <= 1 && !qObj) {
    q('uprev').innerHTML = `<span class="ub">${BASE}</span>`;
  } else {
    let html = `<span class="ub">${BASE}?</span>`;
    html += ps.map(([k,v]) =>
      `<span class="up">${k}</span><span class="ub">=</span><span class="uv">${encodeURIComponent(v)}</span>`
    ).join('<span class="ub">&</span>');
    q('uprev').innerHTML = html;
  }

  // Active filter badges
  q('badges-row').innerHTML = fs.map(f =>
    `<span class="badge"><span class="bc">${f.col}</span><span class="bo"> ${f.op.replace('$','')} </span><span class="bv">${f.isNull ? 'NULL' : (f.isDate ? f.lbl.split(/[<>]=? /)[1] : String(f.v).substring(0,24))}</span></span>`
  ).join('');
}

// ─────────────────────────────────────────────
// QUERY EXECUTION
// ─────────────────────────────────────────────
async function runQuery(offset=0) {
  S.offset = offset;
  livePreview();
  const fs   = collectFilters();
  const qObj = buildQ(fs);
  const url  = buildURL(qObj, offset);
  const limit = +val('f-limit') || 25;

  q('tbl-wrap').innerHTML = `<div class="loading-st"><div class="spin"></div><span>Calling ORDS AutoREST endpoint…</span></div>`;
  q('rcount').textContent = '…';
  q('ploc').textContent   = '';

  try {
    const res = await fetch(url, { headers:{ Accept:'application/json' } });
    if (!res.ok) throw new Error(`HTTP ${res.status}: ${res.statusText}`);
    const data = await res.json();
    renderTable(data, offset, limit);
  } catch(err) {
    const isCors = err.message.toLowerCase().includes('fetch') || err.message.toLowerCase().includes('failed');
    q('tbl-wrap').innerHTML = `
      <div class="error-st">
        <div class="error-title">⚠ Request Failed</div>
        <div class="error-body">
          ${err.message}<br><br>
          <strong>Attempted URL:</strong><br>
          <span style="color:var(--amber)">${escHtml(url)}</span>
          ${isCors ? `<br><br><strong>CORS tip:</strong> Serve this file from a local server (e.g. <code>python -m http.server 3000</code>) so the browser allows cross-origin requests to localhost:8080. Alternatively confirm ORDS has <code>cors.allowed.origins=*</code> or the matching origin in its config.` : ''}
        </div>
      </div>`;
    q('rcount').textContent = '—';
  }
}

function renderTable(data, offset, limit) {
  const items   = data.items || [];
  const hasMore = !!data.hasMore;
  const count   = data.count ?? items.length;

  q('rcount').textContent = count;
  if (items.length) {
    q('ploc').textContent = `rows ${offset+1}–${offset+items.length}${hasMore?' (more available)':''}`;
  }

  if (!items.length) {
    q('tbl-wrap').innerHTML = `
      <div class="empty-st">
        <div class="empty-ico">🔍</div>
        <p style="font-family:var(--mono);font-size:12px">No employees matched the current filters.</p>
        <p style="font-family:var(--mono);font-size:10px;margin-top:4px;color:var(--text3)">Relax one or more conditions and try again.</p>
      </div>`;
    return;
  }

  const cols = [
    { k:'employee_id',   h:'EMP_ID',   fn: v => `<td class="tid">${v}</td>` },
    { k:'first_name',    h:'FIRST',    fn: v => `<td class="tname">${v??'<span class="tnull">null</span>'}</td>` },
    { k:'last_name',     h:'LAST',     fn: v => `<td class="tname">${v??'<span class="tnull">null</span>'}</td>` },
    { k:'email',         h:'EMAIL',    fn: v => `<td>${v??''}</td>` },
    { k:'job_id',        h:'JOB_ID',   fn: v => `<td><span class="tjob">${v}</span></td>` },
    { k:'department_id', h:'DEPT',     fn: v => v!=null?`<td class="tid">${v}</td>`:`<td><span class="tnull">null</span></td>` },
    { k:'salary',        h:'SALARY',   fn: v => `<td class="tsal">$${Number(v).toLocaleString()}</td>` },
    { k:'commission_pct',h:'COMM%',    fn: v => v!=null?`<td class="tpct">${(v*100).toFixed(0)}%</td>`:`<td><span class="tnull">—</span></td>` },
    { k:'hire_date',     h:'HIRE_DATE',fn: v => `<td class="tdate">${fmtDate(v)}</td>` },
    { k:'manager_id',    h:'MGR',      fn: v => v!=null?`<td class="tid">${v}</td>`:`<td><span class="tnull">—</span></td>` },
  ];

  const thead = '<tr>' + cols.map(c =>
    `<th onclick="clickSort('${c.k}')" id="th-${c.k}">${c.h}<span class="si">↕</span></th>`
  ).join('') + '</tr>';

  const tbody = items.map(row =>
    '<tr>' + cols.map(c => c.fn(row[c.k])).join('') + '</tr>'
  ).join('');

  const pg = `
    <div class="pg">
      <span class="pg-info">Page ${Math.floor(offset/limit)+1} · ${items.length} row(s) this page</span>
      <div class="pg-btns">
        <button class="pg-btn" onclick="runQuery(${Math.max(0,offset-limit)})" ${offset===0?'disabled':''}>← Prev</button>
        <button class="pg-btn" onclick="runQuery(${offset+limit})" ${!hasMore?'disabled':''}>Next →</button>
      </div>
    </div>`;

  q('tbl-wrap').innerHTML = `<div class="tbl-scroll"><table><thead>${thead}</thead><tbody>${tbody}</tbody></table></div>${pg}`;

  // Mark sorted column
  const ob = val('f-orderby');
  if (ob) {
    const el = document.getElementById('th-'+ob.toLowerCase());
    if (el) { el.classList.add('sorted'); el.querySelector('.si').textContent = S.ord==='ASC'?' ↑':' ↓'; }
  }
}

function clickSort(col) {
  const current = val('f-orderby');
  const colUC = col.toUpperCase();
  if (current === colUC) {
    setOrd(S.ord==='ASC'?'DESC':'ASC');
  } else {
    q('f-orderby').value = colUC;
    setOrd('ASC');
  }
  runQuery(0);
}

// ─────────────────────────────────────────────
// COPY / OPEN
// ─────────────────────────────────────────────
function copyQ() {
  const qObj = buildQ(collectFilters());
  navigator.clipboard.writeText(qObj ? JSON.stringify(qObj) : '').then(()=>toast('q= parameter copied!'));
}

function copyURL() {
  navigator.clipboard.writeText(buildURL(buildQ(collectFilters()),0)).then(()=>toast('URL copied!'));
}

function openURL() {
  window.open(buildURL(buildQ(collectFilters()),0), '_blank');
}

// ─────────────────────────────────────────────
// RESET
// ─────────────────────────────────────────────
function resetAll() {
  ['f-empid','f-deptid','f-mgrid','f-fn','f-ln','f-jobpat','f-salmin','f-salmax','f-hfrom','f-hto']
    .forEach(id => q(id).value = '');
  q('f-empop').value   = '$eq';
  q('f-fnop').value    = '$like';
  q('f-lnop').value    = '$like';
  q('f-jobid').value   = '';
  q('f-limit').value   = '25';
  q('f-orderby').value = '';
  q('job-pat-wrap').style.display = 'none';
  setLogic('and');
  setOrd('ASC');
  setComm('all');
  livePreview();
}

// ─────────────────────────────────────────────
// UTILS
// ─────────────────────────────────────────────
function fmtDate(v) {
  if (!v) return '<span class="tnull">—</span>';
  try {
    const d = new Date(v);
    return d.toLocaleDateString('en-US',{year:'numeric',month:'short',day:'numeric'});
  } catch { return v; }
}

function escHtml(s) {
  return String(s).replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>');
}

let toastTimer;
function toast(msg) {
  const t = q('toast');
  t.textContent = msg;
  t.classList.add('show');
  clearTimeout(toastTimer);
  toastTimer = setTimeout(()=>t.classList.remove('show'), 2200);
}

// ─────────────────────────────────────────────
// SIDEBAR RESIZE
// ─────────────────────────────────────────────
(function() {
  const resizer  = document.getElementById('resizer');
  const app      = resizer.closest('.app');
  const sidebar  = document.getElementById('sidebar');
  const MIN_W    = 220;
  const MAX_W    = 600;
  let dragging   = false;
  let startX, startW;

  resizer.addEventListener('mousedown', e => {
    dragging = true;
    startX   = e.clientX;
    startW   = sidebar.getBoundingClientRect().width;
    resizer.classList.add('dragging');
    document.body.style.cursor    = 'col-resize';
    document.body.style.userSelect = 'none';
    e.preventDefault();
  });

  document.addEventListener('mousemove', e => {
    if (!dragging) return;
    const newW = Math.min(MAX_W, Math.max(MIN_W, startW + (e.clientX - startX)));
    app.style.setProperty('--sidebar-w', newW + 'px');
  });

  document.addEventListener('mouseup', () => {
    if (!dragging) return;
    dragging = false;
    resizer.classList.remove('dragging');
    document.body.style.cursor     = '';
    document.body.style.userSelect = '';
  });
})();

// ─────────────────────────────────────────────
// INIT
// ─────────────────────────────────────────────
livePreview();
</script>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>ORDS Query Filter Explorer — HR.EMPLOYEES</title>
<link rel="preconnect" href="https://fonts.googleapis.com">
<link href="https://fonts.googleapis.com/css2?family=Syne:wght@400;600;700;800&family=DM+Sans:ital,opsz,wght@0,9..40,300;0,9..40,400;0,9..40,500;1,9..40,300&family=JetBrains+Mono:wght@300;400;500;600&display=swap" rel="stylesheet">
<style>
:root {
  --bg:     #080a0d;
  --bg1:    #0e1218;
  --bg2:    #141b24;
  --bg3:    #1c2530;
  --border: #232d3a;
  --border2:#2e3d50;
  --red:    #c94236;
  --red-hl: #e04535;
  --cyan:   #22d3ee;
  --amber:  #f59e0b;
  --green:  #22c55e;
  --purple: #a78bfa;
  --text:   #dde5f0;
  --text2:  #8fa3b8;
  --text3:  #404f62;
  --syne:   'Syne', sans-serif;
  --body:   'DM Sans', sans-serif;
  --mono:   'JetBrains Mono', monospace;
}

*,*::before,*::after{box-sizing:border-box;margin:0;padding:0}

body {
  font-family: var(--body);
  background: var(--bg);
  color: var(--text);
  min-height: 100vh;
  font-size: 14px;
  line-height: 1.6;
}

/* ── SCROLLBAR ── */
::-webkit-scrollbar{width:4px;height:4px}
::-webkit-scrollbar-track{background:var(--bg1)}
::-webkit-scrollbar-thumb{background:var(--border2);border-radius:2px}

/* ── HEADER ── */
.header {
  background: var(--bg1);
  border-bottom: 1px solid var(--border);
  padding: 0 28px;
  height: 58px;
  display: flex;
  align-items: center;
  justify-content: space-between;
  position: sticky;
  top: 0;
  z-index: 100;
}
.hb { display:flex; align-items:center; gap:14px }
.oracle-badge {
  background: var(--red);
  color: #fff;
  font-family: var(--syne);
  font-size: 10px;
  font-weight: 800;
  letter-spacing: .12em;
  padding: 4px 9px;
  text-transform: uppercase;
  clip-path: polygon(0 0,calc(100% - 6px) 0,100% 100%,0 100%);
}
.header-title { font-family:var(--syne); font-size:15px; font-weight:700; letter-spacing:.02em }
.header-sub   { font-family:var(--mono); font-size:10px; color:var(--text3); letter-spacing:.05em }
.endpoint-pill {
  display:flex; align-items:center; gap:8px;
  background:var(--bg2); border:1px solid var(--border);
  padding:5px 12px; border-radius:2px;
  font-family:var(--mono); font-size:11px; color:var(--cyan);
}
.dot-live { width:6px;height:6px;background:var(--green);border-radius:50%;animation:blink 2s ease-in-out infinite }
@keyframes blink{0%,100%{opacity:1}50%{opacity:.3}}

/* ── LAYOUT ── */
.app {
  display: grid;
  grid-template-columns: var(--sidebar-w, 320px) 4px 1fr;
  grid-template-rows: auto 1fr;
  height: calc(100vh - 58px);
}

/* ── RESIZE HANDLE ── */
.resizer {
  grid-row: 1 / 3;
  background: var(--border);
  cursor: col-resize;
  transition: background .15s;
  position: relative;
  z-index: 10;
}
.resizer:hover, .resizer.dragging { background: var(--cyan); }
.resizer::after {
  content: '';
  position: absolute;
  inset: 0 -4px;   /* wider invisible hit target */
}

/* ── SIDEBAR ── */
.sidebar {
  grid-row: 1 / 3;
  background: var(--bg1);
  border-right: none;
  overflow-y: auto;
  padding: 20px 18px;
  display: flex;
  flex-direction: column;
  gap: 18px;
  min-width: 220px;
  max-width: 600px;
}
.sec-label {
  font-family:var(--syne); font-size:9px; font-weight:800;
  letter-spacing:.14em; text-transform:uppercase; color:var(--text3);
  display:flex; align-items:center; gap:8px; margin-bottom:10px;
}
.sec-label::after{content:'';flex:1;height:1px;background:var(--border)}
.fg { display:flex; flex-direction:column; gap:10px }
.fr { display:grid; grid-template-columns:1fr 1fr; gap:8px }
.field { display:flex; flex-direction:column; gap:3px }
.fl { font-family:var(--mono); font-size:9px; color:var(--text2); letter-spacing:.06em; text-transform:uppercase }
.field input, .field select {
  background:var(--bg2); border:1px solid var(--border);
  color:var(--text); padding:6px 9px;
  font-family:var(--mono); font-size:12px;
  border-radius:2px; outline:none; width:100%;
  transition:border-color .15s, box-shadow .15s;
}
.field input:focus,.field select:focus{border-color:var(--cyan);box-shadow:0 0 0 2px rgba(34,211,238,.08)}
.field input::placeholder{color:var(--text3)}
.field select option{background:var(--bg2)}
.help{font-family:var(--mono);font-size:9px;color:var(--text3);line-height:1.5;margin-top:1px}

/* operator inline select */
.op-sel {
  background:var(--bg2); border:1px solid var(--border);
  color:var(--amber); padding:6px 5px;
  font-family:var(--mono); font-size:10px;
  border-radius:2px; outline:none; cursor:pointer; flex-shrink:0;
}
/* full-width stacked operator select */
.full-op {
  width: 100%;
  padding: 6px 9px;
  font-size: 11px;
  color: var(--amber);
  background: var(--bg2);
  border: 1px solid var(--border);
  border-radius: 2px;
  outline: none;
  cursor: pointer;
  transition: border-color .15s;
}
.full-op:focus { border-color: var(--cyan); box-shadow: 0 0 0 2px rgba(34,211,238,.08); }
.full-op option { background: var(--bg2); }
.inline-pair { display:flex; gap:4px; align-items:stretch }
.inline-pair .op-sel { min-width:82px }
.inline-pair input { flex:1; min-width:0 }

/* toggle pairs */
.toggle {
  display:flex; background:var(--bg2);
  border:1px solid var(--border); border-radius:2px; overflow:hidden;
}
.tgl-btn {
  flex:1; padding:5px 8px;
  background:transparent; border:none;
  color:var(--text2); font-family:var(--syne); font-size:10px;
  font-weight:700; letter-spacing:.08em; cursor:pointer; transition:all .15s;
  text-align:center;
}
.tgl-btn.on{background:var(--red);color:#fff}

/* commission row */
.comm-row { display:flex; gap:5px }
.comm-btn {
  flex:1; padding:5px; background:var(--bg2);
  border:1px solid var(--border); color:var(--text2);
  font-family:var(--mono); font-size:9px; cursor:pointer;
  transition:all .15s; text-align:center; border-radius:2px;
}
.comm-btn.on{border-color:var(--cyan);color:var(--cyan);background:rgba(34,211,238,.06)}

/* action buttons */
.btn-exec {
  width:100%; padding:10px; background:var(--red); color:#fff;
  border:none; font-family:var(--syne); font-size:12px; font-weight:800;
  letter-spacing:.07em; text-transform:uppercase; cursor:pointer;
  transition:all .15s; border-radius:2px;
}
.btn-exec:hover{background:var(--red-hl);box-shadow:0 4px 22px rgba(201,66,54,.4)}
.btn-reset {
  width:100%; padding:7px; background:transparent; color:var(--text2);
  border:1px solid var(--border); font-family:var(--syne); font-size:10px;
  font-weight:700; letter-spacing:.07em; text-transform:uppercase;
  cursor:pointer; transition:all .15s; border-radius:2px;
}
.btn-reset:hover{border-color:var(--border2);color:var(--text)}

/* ── QUERY PANEL ── */
.qpanel {
  background:var(--bg1); border-bottom:1px solid var(--border);
  padding:14px 22px; grid-column: 3;
}
.qpanel-inner { display:flex; gap:14px; align-items:flex-start }
.qblocks { flex:1; display:flex; flex-direction:column; gap:8px }
.qblock {
  background:var(--bg); border:1px solid var(--border);
  border-radius:2px; padding:10px 14px; position:relative;
}
.qblock.cyan{ border-left:3px solid var(--cyan) }
.qblock.amber{ border-left:3px solid var(--amber) }
.qbl { font-family:var(--mono); font-size:8px; letter-spacing:.12em; color:var(--text3); text-transform:uppercase; margin-bottom:5px }
.qtext {
  font-family:var(--mono); font-size:11px; color:var(--cyan);
  word-break:break-all; max-height:56px; overflow-y:auto;
  white-space:pre-wrap; line-height:1.55;
}
.utext { font-family:var(--mono); font-size:10px; color:var(--text2); word-break:break-all; line-height:1.5 }
.utext .ub{color:var(--text3)} .utext .up{color:var(--amber)} .utext .uv{color:var(--cyan)}
.badges{ display:flex; flex-wrap:wrap; gap:5px; margin-top:2px }
.badge {
  display:inline-flex; align-items:center; gap:4px;
  background:var(--bg3); border:1px solid var(--border2);
  padding:2px 7px; border-radius:2px;
  font-family:var(--mono); font-size:9px;
}
.badge .bc{color:var(--amber)} .badge .bo{color:var(--text3)} .badge .bv{color:var(--cyan)}

.qactions { display:flex; flex-direction:column; gap:5px }
.cbtn {
  padding:6px 12px; background:var(--bg2); border:1px solid var(--border);
  color:var(--text2); font-family:var(--mono); font-size:10px;
  cursor:pointer; transition:all .15s; border-radius:2px; white-space:nowrap;
}
.cbtn:hover{border-color:var(--cyan);color:var(--cyan)}
.cbtn.open:hover{border-color:var(--amber);color:var(--amber)}

/* ── MAIN ── */
.main {
  padding: 14px 22px 0 22px;
  overflow: hidden;                  /* main itself does NOT scroll */
  display: flex;
  flex-direction: column;
  gap: 10px;
  grid-column: 3;
  min-height: 0;                     /* critical: lets flex children shrink below content size */
}

.results-bar { display:flex; align-items:center; justify-content:space-between; flex-shrink:0 }
.count-pill {
  display:flex; align-items:center; gap:6px;
  background:var(--bg2); border:1px solid var(--border);
  padding:4px 12px; border-radius:2px;
  font-family:var(--mono); font-size:11px;
}
.count-num{color:var(--amber);font-weight:500}
.page-loc{font-family:var(--mono);font-size:10px;color:var(--text2)}

/* table wrapper — fills remaining height, clips overflow */
.tbl-wrap {
  background:var(--bg1); border:1px solid var(--border);
  border-radius:2px;
  display: flex;
  flex-direction: column;
  flex: 1 1 0;                       /* grow to fill, but never push beyond parent */
  min-height: 0;
  overflow: hidden;
  margin-bottom: 14px;
}

/* scrollable zone — only the rows scroll, thead stays put */
.tbl-scroll {
  overflow-x: auto;
  overflow-y: auto;
  flex: 1 1 0;
  min-height: 0;
}

table { width:100%; border-collapse:collapse }
thead th {
  background:var(--bg2); padding:9px 13px;
  font-family:var(--syne); font-size:9px; font-weight:800;
  letter-spacing:.11em; text-transform:uppercase;
  color:var(--text3); border-bottom:1px solid var(--border);
  white-space:nowrap; cursor:pointer; user-select:none;
  transition:color .12s;
  position: sticky; top: 0; z-index: 2;  /* thead sticks to top of scroll area */
}
thead th:hover{color:var(--text2)}
thead th.sorted{color:var(--cyan)}
.si{margin-left:3px;font-size:9px;opacity:.5}
tbody tr{border-bottom:1px solid var(--border);transition:background .08s}
tbody tr:last-child{border-bottom:none}
tbody tr:hover{background:rgba(34,211,238,.025)}
tbody td{padding:8px 13px;font-family:var(--mono);font-size:11px;white-space:nowrap}
.tid{color:var(--text3)}
.tname{font-family:var(--body);font-size:13px;font-weight:500;color:var(--text)}
.tjob{
  display:inline-block; background:var(--bg2);
  border:1px solid var(--border); padding:1px 6px;
  font-size:9px; letter-spacing:.05em; border-radius:2px; color:var(--text2);
}
.tsal{color:var(--green);font-weight:500}
.tdate{color:var(--text2)}
.tnull{color:var(--text3);font-style:italic;font-size:10px}
.tpct{color:var(--purple)}

/* pagination — pinned to bottom of tbl-wrap, never scrolls away */
.pg {
  display:flex; align-items:center; justify-content:space-between;
  padding:10px 14px; background:var(--bg2); border-top:1px solid var(--border);
  flex-shrink: 0;
}
.pg-info{font-family:var(--mono);font-size:10px;color:var(--text2)}
.pg-btns{display:flex;gap:5px}
.pg-btn{
  padding:4px 12px; background:var(--bg1);
  border:1px solid var(--border); color:var(--text2);
  font-family:var(--mono); font-size:10px; cursor:pointer;
  transition:all .12s; border-radius:2px;
}
.pg-btn:hover:not(:disabled){border-color:var(--red);color:var(--red)}
.pg-btn:disabled{opacity:.25;cursor:not-allowed}

/* states */
.loading-st{display:flex;align-items:center;justify-content:center;padding:64px;gap:12px;color:var(--text2);font-family:var(--mono);font-size:12px}
.spin{width:16px;height:16px;border:2px solid var(--border2);border-top-color:var(--red);border-radius:50%;animation:rotate .65s linear infinite}
@keyframes rotate{to{transform:rotate(360deg)}}
.error-st{padding:28px;background:rgba(201,66,54,.04);border:1px solid rgba(201,66,54,.18);border-radius:2px}
.error-title{color:var(--red);font-family:var(--syne);font-size:13px;font-weight:700;margin-bottom:8px}
.error-body{font-family:var(--mono);font-size:11px;color:var(--text2);line-height:1.6}
.empty-st{display:flex;flex-direction:column;align-items:center;justify-content:center;padding:64px;gap:6px;color:var(--text3)}
.empty-ico{font-size:28px;opacity:.3;margin-bottom:6px}
.welcome-st{display:flex;flex-direction:column;align-items:center;padding:72px 40px;gap:14px;text-align:center}
.welcome-ico{
  width:60px;height:60px;background:var(--bg2);border:1px solid var(--border);
  display:flex;align-items:center;justify-content:center;font-size:26px;
  margin-bottom:4px; border-radius:2px;
}
.welcome-h{font-family:var(--syne);font-size:19px;font-weight:800;color:var(--text)}
.welcome-p{color:var(--text2);max-width:420px;font-size:13px;line-height:1.6}
.welcome-p code{font-family:var(--mono);font-size:11px;background:var(--bg2);padding:1px 5px;border-radius:2px;color:var(--cyan)}

/* toast */
.toast{
  position:fixed;bottom:22px;right:22px;
  background:var(--green);color:#fff;
  padding:7px 16px;border-radius:2px;
  font-family:var(--mono);font-size:11px;
  z-index:9999;pointer-events:none;
  transition:opacity .25s; opacity:0;
}
.toast.show{opacity:1}
</style>
</head>
<body>

<header class="header">
  <div class="hb">
    <div class="oracle-badge">Oracle ORDS</div>
    <div>
      <div class="header-title">AutoREST Query Filter Explorer</div>
      <div class="header-sub">HR.EMPLOYEES · Dynamic <em>q=</em> Parameter Showcase</div>
    </div>
  </div>
  <div class="endpoint-pill">
    <span class="dot-live"></span>
    localhost:8080/ords/hr/employees/
  </div>
</header>

<div class="app">

  <!-- ══════════════════════════════════════════════
       SIDEBAR
       ══════════════════════════════════════════════ -->
  <aside class="sidebar" id="sidebar">

    <!-- Logic mode -->
    <div>
      <div class="sec-label">Top-level Logic</div>
      <div class="fg">
        <div class="toggle">
          <button class="tgl-btn on" id="btn-and" onclick="setLogic('and')">AND</button>
          <button class="tgl-btn"    id="btn-or"  onclick="setLogic('or')">OR</button>
        </div>
        <span class="help">AND = implicit object merge (default)<br>OR = $or:[…] array wrapper</span>
      </div>
    </div>

    <!-- Identity -->
    <div>
      <div class="sec-label">Identity</div>
      <div class="fg">
        <div class="fr">
          <div class="field">
            <span class="fl">employee_id</span>
            <input type="number" id="f-empid" placeholder="100–206" min="100" max="206">
          </div>
          <div class="field">
            <span class="fl">emp_id op</span>
            <select id="f-empop">
              <option value="$eq">= eq</option>
              <option value="$gt">> gt</option>
              <option value="$gte">>= gte</option>
              <option value="$lt">< lt</option>
              <option value="$lte"><= lte</option>
            </select>
          </div>
        </div>
        <div class="fr">
          <div class="field">
            <span class="fl">department_id</span>
            <input type="number" id="f-deptid" placeholder="e.g. 90">
          </div>
          <div class="field">
            <span class="fl">manager_id</span>
            <input type="number" id="f-mgrid" placeholder="e.g. 100">
          </div>
        </div>
      </div>
    </div>

    <!-- Name -->
    <div>
      <div class="sec-label">Name Search</div>
      <div class="fg">
        <div class="field">
          <span class="fl">first_name</span>
          <select class="op-sel full-op" id="f-fnop">
            <option value="$like">LIKE (wildcard)</option>
            <option value="$eq">= eq (exact)</option>
            <option value="$ne">≠ ne (not equal)</option>
          </select>
          <input type="text" id="f-fn" placeholder="%Steven%">
          <span class="help">% is wildcard — e.g. %en% matches "Steven"</span>
        </div>
        <div class="field">
          <span class="fl">last_name</span>
          <select class="op-sel full-op" id="f-lnop">
            <option value="$like">LIKE (wildcard)</option>
            <option value="$eq">= eq (exact)</option>
            <option value="$ne">≠ ne (not equal)</option>
          </select>
          <input type="text" id="f-ln" placeholder="K%">
        </div>
      </div>
    </div>

    <!-- Job -->
    <div>
      <div class="sec-label">Job</div>
      <div class="fg">
        <div class="field">
          <span class="fl">job_id</span>
          <select id="f-jobid">
            <option value="">— any —</option>
            <optgroup label="Accounting">
              <option>AC_ACCOUNT</option><option>AC_MGR</option>
            </optgroup>
            <optgroup label="Administration">
              <option>AD_ASST</option><option>AD_PRES</option><option>AD_VP</option>
            </optgroup>
            <optgroup label="Finance">
              <option>FI_ACCOUNT</option><option>FI_MGR</option>
            </optgroup>
            <optgroup label="Human Resources">
              <option>HR_REP</option>
            </optgroup>
            <optgroup label="IT">
              <option>IT_PROG</option>
            </optgroup>
            <optgroup label="Marketing">
              <option>MK_MAN</option><option>MK_REP</option>
            </optgroup>
            <optgroup label="Public Relations">
              <option>PR_REP</option>
            </optgroup>
            <optgroup label="Purchasing">
              <option>PU_CLERK</option><option>PU_MAN</option>
            </optgroup>
            <optgroup label="Sales">
              <option>SA_MAN</option><option>SA_REP</option>
            </optgroup>
            <optgroup label="Shipping">
              <option>SH_CLERK</option><option>ST_CLERK</option><option>ST_MAN</option>
            </optgroup>
            <option value="__PAT__">Custom LIKE pattern…</option>
          </select>
        </div>
        <div class="field" id="job-pat-wrap" style="display:none">
          <span class="fl">job_id LIKE pattern</span>
          <input type="text" id="f-jobpat" placeholder="%CLERK">
          <span class="help">e.g. %CLERK — matches all clerks</span>
        </div>
      </div>
    </div>

    <!-- Salary -->
    <div>
      <div class="sec-label">Salary</div>
      <div class="fg">
        <div class="fr">
          <div class="field">
            <span class="fl">min ($gte)</span>
            <input type="number" id="f-salmin" placeholder="2100" min="2100" max="24000">
          </div>
          <div class="field">
            <span class="fl">max ($lte)</span>
            <input type="number" id="f-salmax" placeholder="24000" min="2100" max="24000">
          </div>
        </div>
        <span class="help">Dataset range: $2,100 – $24,000</span>
      </div>
    </div>

    <!-- Hire Date -->
    <div>
      <div class="sec-label">Hire Date</div>
      <div class="fg">
        <div class="field">
          <span class="fl">hired after ($gte)</span>
          <input type="date" id="f-hfrom" min="2011-01-13" max="2018-04-21">
        </div>
        <div class="field">
          <span class="fl">hired before ($lte)</span>
          <input type="date" id="f-hto" min="2011-01-13" max="2018-04-21">
        </div>
        <span class="help">Uses ORDS date wrapper: {"$date":"ISO-Z"}</span>
      </div>
    </div>

    <!-- Commission -->
    <div>
      <div class="sec-label">Commission PCT</div>
      <div class="comm-row">
        <button class="comm-btn on" id="cb-all"  onclick="setComm('all')">All</button>
        <button class="comm-btn"    id="cb-has"  onclick="setComm('has')">Has ($notnull)</button>
        <button class="comm-btn"    id="cb-none" onclick="setComm('none')">None ($null)</button>
      </div>
    </div>

    <!-- Display -->
    <div>
      <div class="sec-label">Display & Sort</div>
      <div class="fg">
        <div class="fr">
          <div class="field">
            <span class="fl">limit</span>
            <select id="f-limit">
              <option value="10">10</option>
              <option value="25" selected>25</option>
              <option value="50">50</option>
              <option value="100">100</option>
            </select>
          </div>
          <div class="field">
            <span class="fl">order by</span>
            <select id="f-orderby">
              <option value="">— default —</option>
              <option value="EMPLOYEE_ID">Employee ID</option>
              <option value="LAST_NAME">Last Name</option>
              <option value="SALARY">Salary</option>
              <option value="HIRE_DATE">Hire Date</option>
              <option value="DEPARTMENT_ID">Dept ID</option>
            </select>
          </div>
        </div>
        <div class="toggle">
          <button class="tgl-btn on" id="btn-asc"  onclick="setOrd('ASC')">ASC ↑</button>
          <button class="tgl-btn"    id="btn-desc" onclick="setOrd('DESC')">DESC ↓</button>
        </div>
      </div>
    </div>

    <div style="display:flex;flex-direction:column;gap:6px">
      <button class="btn-exec" onclick="runQuery()">▶ Execute Query</button>
      <button class="btn-reset" onclick="resetAll()">↺ Reset All Filters</button>
    </div>

  </aside>

  <div class="resizer" id="resizer"></div>

  <!-- ══════════════════════════════════════════════
       QUERY PREVIEW PANEL
       ══════════════════════════════════════════════ -->
  <div class="qpanel">
    <div class="qpanel-inner">
      <div class="qblocks">
        <div class="qblock cyan">
          <div class="qbl">q= filter parameter (ORDS JSON grammar)</div>
          <div class="qtext" id="qprev">{ }</div>
        </div>
        <div class="qblock amber">
          <div class="qbl">Full ORDS Request URL</div>
          <div class="utext" id="uprev"><span class="ub">http://localhost:8080/ords/hr/employees/</span></div>
        </div>
        <div class="badges" id="badges-row"></div>
      </div>
      <div class="qactions">
        <button class="cbtn" onclick="copyQ()">⎘ Copy q=</button>
        <button class="cbtn" onclick="copyURL()">⎘ Copy URL</button>
        <button class="cbtn open" onclick="openURL()">↗ Open URL</button>
      </div>
    </div>
  </div>

  <!-- ══════════════════════════════════════════════
       MAIN RESULTS
       ══════════════════════════════════════════════ -->
  <main class="main">
    <div class="results-bar">
      <div class="count-pill">
        <span class="count-num" id="rcount">—</span>
        <span>rows returned</span>
      </div>
      <span class="page-loc" id="ploc"></span>
    </div>
    <div class="tbl-wrap" id="tbl-wrap">
      <div class="welcome-st">
        <div class="welcome-ico">⚡</div>
        <div class="welcome-h">ORDS AutoREST Query Filter Explorer</div>
        <p class="welcome-p">
          Build a filter using the controls on the left, then click <strong>Execute Query</strong>.<br>
          Watch the <code>q=</code> parameter assemble in real time — showcasing how a single
          AutoREST endpoint handles every query shape without any custom SQL.
        </p>
      </div>
    </div>
  </main>

</div>

<div class="toast" id="toast"></div>

<script>
// ─────────────────────────────────────────────
// STATE
// ─────────────────────────────────────────────
const BASE = 'http://localhost:8080/ords/hr/employees/';

const S = {
  logic: 'and',
  ord:   'ASC',
  comm:  'all',
  offset: 0
};

// ─────────────────────────────────────────────
// WIRE UP LIVE PREVIEW
// ─────────────────────────────────────────────
document.querySelectorAll('input, select').forEach(el => {
  el.addEventListener('input',  livePreview);
  el.addEventListener('change', livePreview);
});

document.getElementById('f-jobid').addEventListener('change', function() {
  const wrap = document.getElementById('job-pat-wrap');
  wrap.style.display = this.value === '__PAT__' ? 'flex' : 'none';
  if (this.value === '__PAT__') wrap.style.flexDirection = 'column';
});

// ─────────────────────────────────────────────
// TOGGLE HELPERS
// ─────────────────────────────────────────────
function setLogic(v) {
  S.logic = v;
  q('btn-and').classList.toggle('on', v==='and');
  q('btn-or').classList.toggle('on',  v==='or');
  livePreview();
}

function setOrd(v) {
  S.ord = v;
  q('btn-asc').classList.toggle('on',  v==='ASC');
  q('btn-desc').classList.toggle('on', v==='DESC');
  livePreview();
}

function setComm(v) {
  S.comm = v;
  ['all','has','none'].forEach(id => q('cb-'+id).classList.toggle('on', id===v));
  livePreview();
}

function q(id) { return document.getElementById(id) }
function val(id) { return q(id).value.trim() }

// ─────────────────────────────────────────────
// FILTER BUILDER
// ─────────────────────────────────────────────
function collectFilters() {
  const fs = [];

  const empid = val('f-empid');
  if (empid) fs.push({ col:'employee_id', op:val('f-empop'), v:+empid, lbl:`employee_id ${val('f-empop')} ${empid}` });

  const deptid = val('f-deptid');
  if (deptid) fs.push({ col:'department_id', op:'$eq', v:+deptid, lbl:`department_id = ${deptid}` });

  const mgrid = val('f-mgrid');
  if (mgrid)  fs.push({ col:'manager_id', op:'$eq', v:+mgrid, lbl:`manager_id = ${mgrid}` });

  const fn = val('f-fn');
  if (fn) fs.push({ col:'first_name', op:val('f-fnop'), v:fn, lbl:`first_name ${val('f-fnop')} "${fn}"` });

  const ln = val('f-ln');
  if (ln) fs.push({ col:'last_name', op:val('f-lnop'), v:ln, lbl:`last_name ${val('f-lnop')} "${ln}"` });

  const jobid = val('f-jobid');
  if (jobid && jobid !== '__PAT__') {
    fs.push({ col:'job_id', op:'$eq', v:jobid, lbl:`job_id = "${jobid}"` });
  } else if (jobid === '__PAT__') {
    const pat = val('f-jobpat');
    if (pat) fs.push({ col:'job_id', op:'$like', v:pat, lbl:`job_id LIKE "${pat}"` });
  }

  const salmin = val('f-salmin');
  if (salmin) fs.push({ col:'salary', op:'$gte', v:+salmin, lbl:`salary >= ${salmin}` });

  const salmax = val('f-salmax');
  if (salmax) fs.push({ col:'salary', op:'$lte', v:+salmax, lbl:`salary <= ${salmax}` });

  const hfrom = val('f-hfrom');
  if (hfrom) fs.push({ col:'hire_date', op:'$gte', v:{ $date: hfrom+'T00:00:00Z' }, lbl:`hire_date >= ${hfrom}`, isDate:true });

  const hto = val('f-hto');
  if (hto)   fs.push({ col:'hire_date', op:'$lte', v:{ $date: hto+'T23:59:59Z' },  lbl:`hire_date <= ${hto}`,   isDate:true });

  if (S.comm === 'has')  fs.push({ col:'commission_pct', op:'$notnull', v:null, lbl:'commission_pct IS NOT NULL', isNull:true });
  if (S.comm === 'none') fs.push({ col:'commission_pct', op:'$null',    v:null, lbl:'commission_pct IS NULL',     isNull:true });

  return fs;
}

function buildQ(fs) {
  const orderby = val('f-orderby');

  // Build atomic predicate objects
  function pred(f) {
    if (f.op === '$null')    return { [f.col]: { $null:    null } };
    if (f.op === '$notnull') return { [f.col]: { $notnull: null } };
    return { [f.col]: { [f.op]: f.v } };
  }

  // $orderby clause — lives inside q= JSON per ORDS grammar
  const orderClause = orderby
    ? { $orderby: { [orderby]: S.ord.toLowerCase() } }
    : null;

  if (!fs.length) {
    // No filters — return just the orderby clause (or null if nothing at all)
    return orderClause || null;
  }

  const preds = fs.map(pred);

  let qObj;
  if (S.logic === 'or') {
    qObj = { $or: preds };
  } else {
    // AND: if any column is repeated we must use explicit $and array
    const cols = fs.map(f => f.col);
    const hasDup = cols.some((c,i) => cols.indexOf(c) !== i);
    qObj = hasDup ? { $and: preds } : Object.assign({}, ...preds);
  }

  // Merge $orderby into the top-level q object
  if (orderClause) Object.assign(qObj, orderClause);
  return qObj;
}

// ─────────────────────────────────────────────
// PARAMS & URL
// ─────────────────────────────────────────────
function buildParams(qObj, offset) {
  const ps = [];
  const limit = +val('f-limit') || 25;
  if (qObj) ps.push(['q', JSON.stringify(qObj)]);
  ps.push(['limit', limit]);
  if (offset > 0) ps.push(['offset', offset]);
  // $orderby lives inside q= per ORDS grammar — no separate URL params needed
  return ps;
}

function buildURL(qObj, offset) {
  const ps = buildParams(qObj, offset);
  if (!ps.length) return BASE;
  return BASE + '?' + ps.map(([k,v]) => `${k}=${encodeURIComponent(v)}`).join('&');
}

// ─────────────────────────────────────────────
// LIVE PREVIEW
// ─────────────────────────────────────────────
function livePreview() {
  const fs  = collectFilters();
  const qObj = buildQ(fs);
  const pretty = qObj ? JSON.stringify(qObj, null, 2) : '{ }';

  q('qprev').textContent = pretty;

  // URL with colored spans
  const ps = buildParams(qObj, 0);
  if (ps.length <= 1 && !qObj) {
    q('uprev').innerHTML = `<span class="ub">${BASE}</span>`;
  } else {
    let html = `<span class="ub">${BASE}?</span>`;
    html += ps.map(([k,v]) =>
      `<span class="up">${k}</span><span class="ub">=</span><span class="uv">${encodeURIComponent(v)}</span>`
    ).join('<span class="ub">&</span>');
    q('uprev').innerHTML = html;
  }

  // Active filter badges
  q('badges-row').innerHTML = fs.map(f =>
    `<span class="badge"><span class="bc">${f.col}</span><span class="bo"> ${f.op.replace('$','')} </span><span class="bv">${f.isNull ? 'NULL' : (f.isDate ? f.lbl.split(/[<>]=? /)[1] : String(f.v).substring(0,24))}</span></span>`
  ).join('');
}

// ─────────────────────────────────────────────
// QUERY EXECUTION
// ─────────────────────────────────────────────
async function runQuery(offset=0) {
  S.offset = offset;
  livePreview();
  const fs   = collectFilters();
  const qObj = buildQ(fs);
  const url  = buildURL(qObj, offset);
  const limit = +val('f-limit') || 25;

  q('tbl-wrap').innerHTML = `<div class="loading-st"><div class="spin"></div><span>Calling ORDS AutoREST endpoint…</span></div>`;
  q('rcount').textContent = '…';
  q('ploc').textContent   = '';

  try {
    const res = await fetch(url, { headers:{ Accept:'application/json' } });
    if (!res.ok) throw new Error(`HTTP ${res.status}: ${res.statusText}`);
    const data = await res.json();
    renderTable(data, offset, limit);
  } catch(err) {
    const isCors = err.message.toLowerCase().includes('fetch') || err.message.toLowerCase().includes('failed');
    q('tbl-wrap').innerHTML = `
      <div class="error-st">
        <div class="error-title">⚠ Request Failed</div>
        <div class="error-body">
          ${err.message}<br><br>
          <strong>Attempted URL:</strong><br>
          <span style="color:var(--amber)">${escHtml(url)}</span>
          ${isCors ? `<br><br><strong>CORS tip:</strong> Serve this file from a local server (e.g. <code>python -m http.server 3000</code>) so the browser allows cross-origin requests to localhost:8080. Alternatively confirm ORDS has <code>cors.allowed.origins=*</code> or the matching origin in its config.` : ''}
        </div>
      </div>`;
    q('rcount').textContent = '—';
  }
}

function renderTable(data, offset, limit) {
  const items   = data.items || [];
  const hasMore = !!data.hasMore;
  const count   = data.count ?? items.length;

  q('rcount').textContent = count;
  if (items.length) {
    q('ploc').textContent = `rows ${offset+1}–${offset+items.length}${hasMore?' (more available)':''}`;
  }

  if (!items.length) {
    q('tbl-wrap').innerHTML = `
      <div class="empty-st">
        <div class="empty-ico">🔍</div>
        <p style="font-family:var(--mono);font-size:12px">No employees matched the current filters.</p>
        <p style="font-family:var(--mono);font-size:10px;margin-top:4px;color:var(--text3)">Relax one or more conditions and try again.</p>
      </div>`;
    return;
  }

  const cols = [
    { k:'employee_id',   h:'EMP_ID',   fn: v => `<td class="tid">${v}</td>` },
    { k:'first_name',    h:'FIRST',    fn: v => `<td class="tname">${v??'<span class="tnull">null</span>'}</td>` },
    { k:'last_name',     h:'LAST',     fn: v => `<td class="tname">${v??'<span class="tnull">null</span>'}</td>` },
    { k:'email',         h:'EMAIL',    fn: v => `<td>${v??''}</td>` },
    { k:'job_id',        h:'JOB_ID',   fn: v => `<td><span class="tjob">${v}</span></td>` },
    { k:'department_id', h:'DEPT',     fn: v => v!=null?`<td class="tid">${v}</td>`:`<td><span class="tnull">null</span></td>` },
    { k:'salary',        h:'SALARY',   fn: v => `<td class="tsal">$${Number(v).toLocaleString()}</td>` },
    { k:'commission_pct',h:'COMM%',    fn: v => v!=null?`<td class="tpct">${(v*100).toFixed(0)}%</td>`:`<td><span class="tnull">—</span></td>` },
    { k:'hire_date',     h:'HIRE_DATE',fn: v => `<td class="tdate">${fmtDate(v)}</td>` },
    { k:'manager_id',    h:'MGR',      fn: v => v!=null?`<td class="tid">${v}</td>`:`<td><span class="tnull">—</span></td>` },
  ];

  const thead = '<tr>' + cols.map(c =>
    `<th onclick="clickSort('${c.k}')" id="th-${c.k}">${c.h}<span class="si">↕</span></th>`
  ).join('') + '</tr>';

  const tbody = items.map(row =>
    '<tr>' + cols.map(c => c.fn(row[c.k])).join('') + '</tr>'
  ).join('');

  const pg = `
    <div class="pg">
      <span class="pg-info">Page ${Math.floor(offset/limit)+1} · ${items.length} row(s) this page</span>
      <div class="pg-btns">
        <button class="pg-btn" onclick="runQuery(${Math.max(0,offset-limit)})" ${offset===0?'disabled':''}>← Prev</button>
        <button class="pg-btn" onclick="runQuery(${offset+limit})" ${!hasMore?'disabled':''}>Next →</button>
      </div>
    </div>`;

  q('tbl-wrap').innerHTML = `<div class="tbl-scroll"><table><thead>${thead}</thead><tbody>${tbody}</tbody></table></div>${pg}`;

  // Mark sorted column
  const ob = val('f-orderby');
  if (ob) {
    const el = document.getElementById('th-'+ob.toLowerCase());
    if (el) { el.classList.add('sorted'); el.querySelector('.si').textContent = S.ord==='ASC'?' ↑':' ↓'; }
  }
}

function clickSort(col) {
  const current = val('f-orderby');
  const colUC = col.toUpperCase();
  if (current === colUC) {
    setOrd(S.ord==='ASC'?'DESC':'ASC');
  } else {
    q('f-orderby').value = colUC;
    setOrd('ASC');
  }
  runQuery(0);
}

// ─────────────────────────────────────────────
// COPY / OPEN
// ─────────────────────────────────────────────
function copyQ() {
  const qObj = buildQ(collectFilters());
  navigator.clipboard.writeText(qObj ? JSON.stringify(qObj) : '').then(()=>toast('q= parameter copied!'));
}

function copyURL() {
  navigator.clipboard.writeText(buildURL(buildQ(collectFilters()),0)).then(()=>toast('URL copied!'));
}

function openURL() {
  window.open(buildURL(buildQ(collectFilters()),0), '_blank');
}

// ─────────────────────────────────────────────
// RESET
// ─────────────────────────────────────────────
function resetAll() {
  ['f-empid','f-deptid','f-mgrid','f-fn','f-ln','f-jobpat','f-salmin','f-salmax','f-hfrom','f-hto']
    .forEach(id => q(id).value = '');
  q('f-empop').value   = '$eq';
  q('f-fnop').value    = '$like';
  q('f-lnop').value    = '$like';
  q('f-jobid').value   = '';
  q('f-limit').value   = '25';
  q('f-orderby').value = '';
  q('job-pat-wrap').style.display = 'none';
  setLogic('and');
  setOrd('ASC');
  setComm('all');
  livePreview();
}

// ─────────────────────────────────────────────
// UTILS
// ─────────────────────────────────────────────
function fmtDate(v) {
  if (!v) return '<span class="tnull">—</span>';
  try {
    const d = new Date(v);
    return d.toLocaleDateString('en-US',{year:'numeric',month:'short',day:'numeric'});
  } catch { return v; }
}

function escHtml(s) {
  return String(s).replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>');
}

let toastTimer;
function toast(msg) {
  const t = q('toast');
  t.textContent = msg;
  t.classList.add('show');
  clearTimeout(toastTimer);
  toastTimer = setTimeout(()=>t.classList.remove('show'), 2200);
}

// ─────────────────────────────────────────────
// SIDEBAR RESIZE
// ─────────────────────────────────────────────
(function() {
  const resizer  = document.getElementById('resizer');
  const app      = resizer.closest('.app');
  const sidebar  = document.getElementById('sidebar');
  const MIN_W    = 220;
  const MAX_W    = 600;
  let dragging   = false;
  let startX, startW;

  resizer.addEventListener('mousedown', e => {
    dragging = true;
    startX   = e.clientX;
    startW   = sidebar.getBoundingClientRect().width;
    resizer.classList.add('dragging');
    document.body.style.cursor    = 'col-resize';
    document.body.style.userSelect = 'none';
    e.preventDefault();
  });

  document.addEventListener('mousemove', e => {
    if (!dragging) return;
    const newW = Math.min(MAX_W, Math.max(MIN_W, startW + (e.clientX - startX)));
    app.style.setProperty('--sidebar-w', newW + 'px');
  });

  document.addEventListener('mouseup', () => {
    if (!dragging) return;
    dragging = false;
    resizer.classList.remove('dragging');
    document.body.style.cursor     = '';
    document.body.style.userSelect = '';
  });
})();

// ─────────────────────────────────────────────
// INIT
// ─────────────────────────────────────────────
livePreview();
</script>
</body>
</html>

My last ORDS post on filtering GET requests?

One would think I had done it all, but nope. We have some really cool stuff cooking in version 26.1 about to drop in a few weeks. Think…VECTOR SEARCH.

The setup

My AI Agent Prompt

The Result

All the code

My last ORDS post on filtering GET requests?

Where’s Your Agency? People Aren’t Using AI—But we (Oracle) Just Dropped 100+ New Skills to Make It Impossible to Ignore

Related Posts

Vibe coding a web console, complete with AI Vector Search

Code & Tune an Oracle DB REST API w/Claude, MCP, & ORDS

More on PARs and a note re: APEX RESTful Data Services

Write A Comment Cancel Reply