begin-handler /add-customer
     out-header use content-type "application/json"
     get-param first_name
     get-param last_name
     // Add a customer SQL
     run-query @db ="insert into customers (firstName, lastName) \
             values ('%s', '%s') returning customerID" output customerID : \
             first_name, last_name
         @"<<print-out customerID>>"
     end-query
 end-handler

 begin-handler /add-item
     out-header use content-type "application/json"
     get-param name
     get-param description
     // Add an item to inventory SQL
     run-query @db ="insert into items (name, description) \
         values ('%s', '%s') returning itemID" output item_id : name, description
         @"<<print-out item_id>>"
     end-query
 end-handler

 begin-handler /add-to-order
     out-header use content-type "application/json"
     get-param order_id
     get-param item_id
     get-param quantity
     // SQL to add an item to an order
     run-query @db ="insert into orderItems (orderId, itemID, quantity) values  ('%s', '%s', '%s')" \
         : order_id, item_id, quantity no-loop affected-rows arows
     @"<<print-out arows>>"
 end-handler

 begin-handler /create-order
     out-header use content-type "application/json"
     get-param customer_id
     // SQL to create an order
     run-query @db ="insert into orders (customerId) \
         values ('%s') returning orderID" output order_id : customer_id
         @"<<print-out order_id>>"
     end-query
 end-handler

 begin-handler /delete-order
     out-header use content-type "application/json"
     get-param order_id
     begin-transaction
     run-query @db ="delete from orders where orderID='%s'" : order_id \
         no-loop affected-rows order_rows
     run-query @db ="delete from orderItems where orderID='%s'" : order_id \
         no-loop affected-rows item_rows
     commit-transaction
     @{ "orders":"<<print-out order_rows>>", "items":"<<print-out item_rows>>" }
 end-handler

 begin-handler /json_from_order
     get-param order_id type string
     get-param curr_order type number
     get-param order_count type number
     get-param customer_id type string
     get-param first_name type string
     get-param last_name type string
     @   {
     @       "orderID": "<<print-out order_id>>",
     @       "customer":
     @       {
     @           "customerID": "<<print-out customer_id>>",
     @           "firstName": "<<print-out first_name>>",
     @           "lastName": "<<print-out last_name>>"
     @       },
     @       "items": [
     set-number curr_item = 0
     // Query to get all items in an order
     run-query @db ="select i.itemID, t.name, t.description, i.quantity \
             from orderItems i, items t where i.orderID='%s' \
                 and t.itemID=i.itemID" \
             output itemID, itemName, itemDescription, itemQuantity : order_id \
             row-count item_count
         @       {
         @           "itemID": "<<print-out itemID>>",
         @           "itemName": "<<print-out itemName>>",
         @           "itemDescription": "<<print-out itemDescription>>",
         @           "itemQuantity": "<<print-out itemQuantity>>"
         // add a comma if there are more items after this
         set-number curr_item=curr_item+1
         if-true curr_item lesser-than item_count
             @       },
         else-if
             @       }
         end-if
     end-query
     @   ]
     // add a comma if there are more orders after this
     set-number curr_order = curr_order+1
     if-true curr_order lesser-than order_count
         @},
     else-if
         @}
     end-if
 end-handler

 begin-handler /list-orders
     out-header use content-type "application/json"
     get-param order_id default-value ""
     set-number curr_order = 0
     // Start JSON output
     @{ "orders": [
     if-true order_id not-equal ""
         // Query just a specific order
         run-query @db = "select o.orderID, c.customerID, c.firstName, c.lastName \
                 from orders o, customers c \
                 where o.customerID=c.customerID and o.orderId='%s'" \
                 output customer_id, first_name, last_name \
                 row-count order_count : order_id
                 set-param order_id, curr_order, order_count, customer_id, first_name, last_name
                 call-handler "/json_from_order"
         end-query
     else-if
         // Query to get all orders
         run-query @db ="select o.orderID, c.customerID, c.firstName, c.lastName \
                 from orders o, customers c \
                 where o.customerID=c.customerID order by o.orderId" \
                 output order_id, customer_id, first_name, last_name \
                 row-count order_count
                 set-param order_id, curr_order, order_count, customer_id, first_name, last_name
                 call-handler "/json_from_order"
         end-query
     end-if
     // Finish JSON output
     @   ]
     @}
 end-handler

 begin-handler /update-order
     out-header use content-type "application/json"
     get-param order_id, item_id, quantity
     set-number arows
     // If quantity update is 0, issue SQL to delete an item from order, otherwise update 
     if-true quantity equal "0"
         run-query @db ="delete from orderItems where orderID='%s' and itemID='%s'" \
             : order_id, item_id no-loop affected-rows arows
     else-if
         run-query @db ="update orderItems set quantity='%s' where orderID='%s' and itemID='%s'" \
             : quantity, order_id, item_id no-loop affected-rows arows
     end-if
     @"<<print-out arows>>"
 end-handler