product_os_service_handler/

lib.rs

//! Product OS Service Handler
//!
//! A FeathersJS-compatible service handler that provides CRUD operations for database tables
//! through a REST API interface compatible with FeathersJS clients.
//!
//! # Overview
//!
//! This crate implements the [FeathersJS](https://feathersjs.com/) service pattern, allowing
//! seamless integration between Rust backends and FeathersJS clients. It provides:
//!
//! - **find**: Query multiple records with pagination, filtering, and sorting
//! - **get**: Retrieve a single record by ID
//! - **create**: Insert new records into the database
//! - **patch**: Partially update existing records  
//! - **remove**: Delete records from the database
//! - **update**: Full record replacement (not yet implemented)
//!
//! # FeathersJS Compatibility
//!
//! The service handler implements the standard FeathersJS query syntax including:
//! - Query operators: `$limit`, `$skip`, `$sort`, `$select`
//! - Comparison operators: `$lt`, `$lte`, `$gt`, `$gte`, `$ne`, `$in`, `$nin`
//! - Logical operators: `$or`
//! - Wildcard search with `*`
//!
//! # Example
//!
//! ```rust,no_run
//! use std::sync::Arc;
//! use parking_lot::Mutex;
//! use product_os_service_handler::ProductOSServiceHandler;
//! # use product_os_command_control::ProductOSController;
//! # use product_os_router::ProductOSRouter;
//!
//! # async fn example(controller: Arc<Mutex<ProductOSController>>) -> Result<(), Box<dyn std::error::Error>> {
//! // Create a service for the "users" table
//! let service = ProductOSServiceHandler::new(
//!     "users".to_string(),
//!     "/api/users".to_string(),
//!     "users".to_string(),
//!     "id".to_string(),
//!     controller
//! );
//!
//! // Load the service into a router
//! let mut router = ProductOSRouter::new();
//! service.load_service(&mut router).await;
//! # Ok(())
//! # }
//! ```
//!
//! # Features
//!
//! - `postgres_store` - Enable PostgreSQL database support
//! - `sqlite_store` - Enable SQLite database support
//!
//! # Architecture
//!
//! The handler follows a layered architecture:
//! 1. **HTTP Layer**: Route handlers process HTTP requests
//! 2. **Conversion Layer**: Transform between JSON and database formats
//! 3. **Database Layer**: Execute queries via `ProductOSRelationalStore`

use std::collections::HashMap;
use std::sync::Arc;
use parking_lot::Mutex;
use product_os_capabilities::{Feature, RegistryFeature, What};
use product_os_command_control::ProductOSController;
use product_os_router::{Body, State, ProductOSRouterError, Json, MethodRouter, Path, Query, Response, StatusCode};
use product_os_store::{ProductOSRelationalStore, RelationalRow, Expression, RelationalError};

use lazy_static::lazy_static;
use regex::Regex;
use std::str::FromStr;

pub use async_trait::async_trait;
use serde_json::Value;

lazy_static! {
    /// Regex pattern for parsing array and object notation in query parameters.
    ///
    /// Matches patterns like `[key]` or `[]` in query strings to support nested
    /// object and array access in FeathersJS style queries.
    ///
    /// Examples:
    /// - `user[name]` - Object property access
    /// - `tags[]` - Array element
    /// - `filters[age][$gt]` - Nested object access
    static ref OBJECT_ARRAY_ELEMENT_REGEX: Regex = Regex::new(r"\[([%$a-zA-Z0-9_-]*)\]").unwrap();
}

/// Product OS Service Handler
///
/// A FeathersJS-compatible service handler that manages CRUD operations for a specific
/// database table. Each handler instance is bound to a single table and provides
/// RESTful endpoints following the FeathersJS service pattern.
///
/// # Fields
///
/// - `name`: Service identifier (e.g., "users")
/// - `path`: URL path for the service (e.g., "/api/users")
/// - `table_name`: Database table name
/// - `identifier_name`: Primary key column name (e.g., "id")
/// - `controller`: Reference to the ProductOS controller
/// - `store`: Database connection and query interface
/// - `default_limit`: Default pagination limit
/// - `default_offset`: Default pagination offset
///
/// # Thread Safety
///
/// This struct is thread-safe and can be safely shared across threads using `Arc`.
/// The internal `ProductOSRelationalStore` uses connection pooling for concurrent access.
///
/// # Example
///
/// ```rust,no_run
/// # use std::sync::Arc;
/// # use parking_lot::Mutex;
/// # use product_os_service_handler::ProductOSServiceHandler;
/// # use product_os_command_control::ProductOSController;
/// # async fn example(controller: Arc<Mutex<ProductOSController>>) {
/// let service = ProductOSServiceHandler::new(
///     "products".to_string(),
///     "/api/products".to_string(),
///     "products".to_string(),
///     "product_id".to_string(),
///     controller
/// );
/// # }
/// ```
pub struct ProductOSServiceHandler {
    name: String,
    path: String,

    table_name: String,
    identifier_name: String,

    controller: Option<Arc<Mutex<ProductOSController>>>,
    store: Arc<ProductOSRelationalStore>,

    default_limit: Option<u32>,
    default_offset: Option<u32>
}

impl ProductOSServiceHandler {
    /// Creates a new service handler instance
    ///
    /// This method initializes a service handler bound to a specific database table.
    /// It extracts configuration from the controller including default pagination
    /// settings and database connection information.
    ///
    /// # Arguments
    ///
    /// * `name` - The service identifier (e.g., "users", "products")
    /// * `path` - The URL path where this service will be mounted (e.g., "/api/users")
    /// * `table_name` - The name of the database table this service manages
    /// * `identifier_name` - The primary key column name (e.g., "id", "user_id")
    /// * `controller` - Shared reference to the ProductOS controller
    ///
    /// # Returns
    ///
    /// A new `ProductOSServiceHandler` instance configured with the specified parameters.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use std::sync::Arc;
    /// # use parking_lot::Mutex;
    /// # use product_os_service_handler::ProductOSServiceHandler;
    /// # use product_os_command_control::ProductOSController;
    /// # async fn example(controller: Arc<Mutex<ProductOSController>>) {
    /// let service = ProductOSServiceHandler::new(
    ///     "users".to_string(),
    ///     "/api/users".to_string(),
    ///     "users".to_string(),
    ///     "id".to_string(),
    ///     controller
    /// );
    /// # }
    /// ```
    pub fn new(name: String, path: String, table_name: String, identifier_name: String, controller: Arc<Mutex<ProductOSController>>) -> Self {
        let controller_unlocked = controller.clone();
        let mut controller_locked = controller_unlocked.lock();

        let store = controller_locked.get_relational_store();

        let default_limit = Some(controller_locked.get_configuration().get_store_relational_default_limit());
        let default_offset = Some(controller_locked.get_configuration().get_store_relational_default_offset());

        Self {
            name,
            path,

            table_name,
            identifier_name,

            controller: Some(controller),
            store,

            default_limit,
            default_offset
        }
    }

    /// Registers this service with a router
    ///
    /// This method registers the service with the ProductOS controller and adds
    /// all necessary routes to the provided router. It sets up both collection
    /// routes (for find/create) and individual resource routes (for get/update/patch/remove).
    ///
    /// # Arguments
    ///
    /// * `router` - Mutable reference to the ProductOS router where routes will be registered
    ///
    /// # Routes Created
    ///
    /// - `GET {path}` - Find multiple records
    /// - `POST {path}` - Create a new record
    /// - `GET {path}/:id` - Get a single record
    /// - `PUT {path}/:id` - Update a record (not implemented)
    /// - `PATCH {path}/:id` - Patch a record
    /// - `DELETE {path}/:id` - Remove a record
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use std::sync::Arc;
    /// # use parking_lot::Mutex;
    /// # use product_os_service_handler::ProductOSServiceHandler;
    /// # use product_os_command_control::ProductOSController;
    /// # use product_os_router::ProductOSRouter;
    /// # async fn example(controller: Arc<Mutex<ProductOSController>>) {
    /// let service = ProductOSServiceHandler::new(
    ///     "users".to_string(),
    ///     "/api/users".to_string(),
    ///     "users".to_string(),
    ///     "id".to_string(),
    ///     controller
    /// );
    ///
    /// let mut router = ProductOSRouter::new();
    /// service.load_service(&mut router).await;
    /// # }
    /// ```
    #[allow(clippy::await_holding_lock)] // Controller method requires the lock across the await
    pub async fn load_service(self, router: &mut product_os_router::ProductOSRouter) {
        if let Some(ctr) = self.controller.clone() {
            let path = self.get_path().to_string();
            let name = self.get_identifier_name().to_string();
            let service_arc = Arc::new(self);

            let controller_unlocked = Arc::clone(&ctr);
            let mut controller = controller_unlocked.lock();
            controller.add_feature(service_arc.clone(), path, router).await;

            tracing::info!("Service {} successfully added to server", name);
        }
    }

    /// Returns a reference to the relational store
    ///
    /// Provides access to the underlying database connection for executing queries.
    pub fn get_store(&self) -> Arc<ProductOSRelationalStore> {
        self.store.clone()
    }

    /// Returns the database table name
    ///
    /// This is the name of the table in the database that this service manages.
    pub fn get_table_name(&self) -> String {
        self.table_name.to_owned()
    }

    /// Returns the identifier column name
    ///
    /// This is the name of the primary key column used for identifying records.
    pub fn get_identifier_name(&self) -> String {
        self.identifier_name.to_owned()
    }

    /// Returns the service path
    ///
    /// This is the URL path where the service is mounted (e.g., "/api/users").
    pub fn get_path(&self) -> String {
        self.path.to_owned()
    }

    /// Returns the default query limit
    ///
    /// This limit is used when no explicit `$limit` is provided in queries.
    pub fn get_default_limit(&self) -> Option<u32> {
        self.default_limit
    }

    /// Returns the default query offset
    ///
    /// This offset is used when no explicit `$skip` is provided in queries.
    pub fn get_default_offset(&self) -> Option<u32> {
        self.default_offset
    }

    /// Internal: Registers HTTP routes with the router
    ///
    /// This private method handles the actual route registration, setting up
    /// both collection and individual resource endpoints.
    fn service_handler(path: String, router: &mut product_os_router::ProductOSRouter, service_handler: Arc<ProductOSServiceHandler>) {
        let service_state = service_handler.clone();
        
        router.add_route(path.as_str(), MethodRouter::new()
            .get(ProductOSServiceHandler::find)
            .post(ProductOSServiceHandler::create)
            .with_state(service_state.clone()));

        let mut specific_path = path.to_string();
        specific_path.push_str("/:id");

        router.add_route(specific_path.as_str(), MethodRouter::new()
            .get(ProductOSServiceHandler::get)
            .put(ProductOSServiceHandler::update)
            .patch(ProductOSServiceHandler::patch)
            .delete(ProductOSServiceHandler::remove)
            .with_state(service_state));
    }

    /*

    const myService = {
      async find(params) {
        return [];
      },
      async get(id, params) {},
      async create(data, params) {},
      async update(id, data, params) {},
      async patch(id, data, params) {},
      async remove(id, params) {},
      setup(app, path) {}
    }

    params contain additional information for the service method call. Some properties in params can be set by Feathers already. Commonly used are:

    params.query - the query parameters from the client, either passed as URL query parameters (see the REST chapter) or through websockets (see Socket.io or Primus).
    params.provider - The transport (rest, socketio or primus) used for this service call. Will be undefined for internal calls from the server (unless passed explicitly).
    params.authentication - The authentication information to use for the authentication service
    params.user - The authenticated user, either set by Feathers authentication or passed explicitly.
    params.connection - If the service call has been made by a real-time transport (e.g. through websockets), params.connection is the connection object that can be used with channels.
    params.headers - The HTTP headers connected to this service call if available. This is either the headers of the REST call or the headers passed when initializing a websocket connection.

    */
    async fn find(State(service): State<Arc<ProductOSServiceHandler>>,
                  Query(query_params): Query<HashMap<String, Value>>) -> Response<Body> {
        tracing::info!("FIND request received:\n{:?}", query_params);

        let store: Arc<ProductOSRelationalStore> = service.get_store();
        let table_name: String = service.get_table_name();
        let identifier_name: String = service.get_identifier_name();

        tracing::info!("Query params: {:?}", query_params);

        let mut params: Vec<(String, String)> = vec!();
        for (k, val) in query_params.clone() {
            match val {
                Value::String(str) => { params.push((k.to_string(), str)); }
                Value::Array(arr) => {
                    for v in arr {
                        match v {
                            Value::String(str) => { params.push((k.to_string(), str)); }
                            _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                        }
                    }
                }
                _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
            }
        }

        // Find
        /*
        {
          "total": "<total number of records>",
          "limit": "<max number of items per page>",
          "skip": "<number of skipped items (offset)>",
          "data": [/* data */]
        }
        */
        let query_object = query_to_object(params);
        match query_object_to_query_expression(table_name.clone(), query_object, service.get_default_offset(), service.get_default_limit()) {
            Ok(query_expression) => {
                if let product_os_store::Query::QueryComplete(
                    _, _, _, _, _, _, _, _,
                    Some(product_os_store::Fetch::CountWithOffset(offset, limit)),
                    _) = query_expression {
                    let result = store.get_all_specific(&query_expression.clone()).await;

                    match result {
                        Ok(rows) => {
                            let mut values_vec = vec!();

                            for row in rows {
                                match row_to_object(row) {
                                    Ok(row_object) => {
                                        let mut object = row_object.clone();
                                        if let serde_json::Value::Object(mut obj) = row_object {
                                            match obj.get(identifier_name.as_str()) {
                                                None => {}
                                                Some(id) => {
                                                    obj.insert("_id".to_string(), id.clone());
                                                    object = serde_json::Value::Object(obj);
                                                }
                                            }

                                            values_vec.push(object);
                                        }
                                        else {
                                            ProductOSRouterError::error_response(ProductOSRouterError::Process("Error making request - problem with object".to_string()), &StatusCode::INTERNAL_SERVER_ERROR);
                                        }
                                    }
                                    Err(e) => return ProductOSRouterError::error_response(e, &StatusCode::INTERNAL_SERVER_ERROR)
                                }
                            }

                            let rows_object = serde_json::Value::Array(values_vec);

                            let total_count = match store.count(&query_expression, identifier_name.as_str()).await {
                                Ok(value) => serde_json::Number::from(value),
                                Err(e) => return ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Count error: {:?}", e)), &StatusCode::INTERNAL_SERVER_ERROR)
                            };

                            let mut response_object = serde_json::Map::new();

                            response_object.insert(String::from("data"), rows_object);
                            response_object.insert(String::from("total"), serde_json::Value::Number(total_count));
                            response_object.insert(String::from("limit"), serde_json::Value::Number(serde_json::Number::from(limit)));
                            response_object.insert(String::from("offset"), serde_json::Value::Number(serde_json::Number::from(offset)));

                            let response = serde_json::Value::Object(response_object);

                            Response::builder()
                                .status(StatusCode::OK)
                                .header("content-type", "application/json")
                                .body(Body::from(response.to_string()))
                                .unwrap()
                        }
                        Err(e) => ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Unable to fetch records: {:?}", e)), &StatusCode::INTERNAL_SERVER_ERROR)
                    }
                }
                else {
                    ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                }
            }
            Err(e) => ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Problem with query: {:?}", e)), &StatusCode::BAD_REQUEST)
        }
    }

    async fn get(State(service): State<Arc<ProductOSServiceHandler>>,
                 Path(identifier): Path<String>,
                 Query(query_params): Query<HashMap<String, Value>>) -> Response<Body> {
        tracing::info!("GET request received:\n{:?}\n{:?}", identifier, query_params);

        let store: Arc<ProductOSRelationalStore> = service.get_store();
        let table_name: String = service.get_table_name();
        let identifier_name: String = service.get_identifier_name();

        let mut select_fields = product_os_store::Fields::All;
        let mut sort_by = vec!((identifier_name.clone(), product_os_store::SortOrder::DESC));

        tracing::info!("Query params: {:?}", query_params);

        let mut params: Vec<(String, String)> = vec!();
        for (k, val) in query_params.clone() {
            match val {
                Value::String(str) => { params.push((k.to_string(), str)); }
                Value::Array(arr) => {
                    for v in arr {
                        match v {
                            Value::String(str) => { params.push((k.to_string(), str)); }
                            _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                        }
                    }
                }
                _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
            }
        }

        let query_object = query_to_object(params);
        if let Ok(product_os_store::Query::QueryComplete(
            _, _,
            fields,
            _, _, _, _,
            sort,
            _, _)) = query_object_to_query_expression(table_name.clone(), query_object, service.get_default_offset(), service.get_default_limit()) {
            if let Some(f) = fields {
                select_fields = f;
            }

            if let Some(s) = sort {
                sort_by = s;
            }
        }

        // Get
        let result = store.get_one_specific(&product_os_store::Query::QueryComplete(
            product_os_store::Table::Table(table_name),
            None,
            Some(select_fields),
            None,
            Some(product_os_store::Expression::EqualTo(identifier_name.clone(), identifier)),
            None,
            None,
            Some(sort_by),
            None,
            None)).await;

        match result {
            Ok(res) => {
                match res {
                    None => ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Not found: {:?}", service.get_identifier_name())), &StatusCode::NOT_FOUND),
                    Some(row) => {
                        match row_to_object(row) {
                            Ok(row_object) => {
                                let mut object = row_object.clone();
                                if let serde_json::Value::Object(mut obj) = row_object {
                                    match obj.get(identifier_name.as_str()) {
                                        None => {}
                                        Some(id) => {
                                            obj.insert("_id".to_string(), id.clone());
                                            object = serde_json::Value::Object(obj);
                                        }
                                    }

                                    Response::builder()
                                        .status(StatusCode::OK)
                                        .header("content-type", "application/json")
                                        .body(Body::from(object.to_string()))
                                        .unwrap()
                                }
                                else {
                                    ProductOSRouterError::error_response(ProductOSRouterError::Process("Error making request - problem with object".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                                }
                            },
                            Err(e) => {
                                tracing::error!("Error encountered: {:?}", e);
                                ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Error making request: {:?}", e)), &StatusCode::INTERNAL_SERVER_ERROR)
                            }
                        }
                    }
                }
            }
            Err(e) => {
                tracing::error!("Error encountered: {:?}", e);
                ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Error making request: {:?}", e)), &StatusCode::INTERNAL_SERVER_ERROR)
            }
        }
    }

    async fn create(State(service): State<Arc<ProductOSServiceHandler>>,
                    Query(query_params): Query<HashMap<String, Value>>,
                    Json(payload): Json<serde_json::Value>) -> Response<Body> {
        tracing::info!("CREATE request received:\n{:?}\n{:?}", payload, query_params);

        let store: Arc<ProductOSRelationalStore> = service.get_store();
        let table_name: String = service.get_table_name();
        // let identifier_name: String = service.get_identifier_name();

        let mut params: Vec<(String, String)> = vec!();
        for (k, val) in query_params.clone() {
            match val {
                Value::String(str) => { params.push((k.to_string(), str)); }
                Value::Array(arr) => {
                    for v in arr {
                        match v {
                            Value::String(str) => { params.push((k.to_string(), str)); }
                            _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                        }
                    }
                }
                _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
            }
        }

        let mut fields = vec!();
        let mut values = vec!();
        for (name, value) in payload.as_object().unwrap().iter() {
            fields.push(name.to_string());
            match value {
                serde_json::Value::Null => { values.push(product_os_store::Value::Null); }
                serde_json::Value::Bool(b) => { values.push(product_os_store::Value::Boolean(b.to_owned())) }
                serde_json::Value::Number(n) => {
                    values.push(product_os_store::Value::BigNumber(i128::from(n.as_i64().unwrap())));
                },
                serde_json::Value::String(s) => { values.push(product_os_store::Value::String(s.to_string())); }
                serde_json::Value::Array(arr) => {
                    let mut a = vec!();
                    for e in arr {
                        match e {
                            serde_json::Value::Bool(b) => { a.push(product_os_store::Value::Boolean(b.to_owned())) },
                            serde_json::Value::Number(n) => {
                                a.push(product_os_store::Value::BigNumber(i128::from(n.as_i64().unwrap())));
                            }
                            serde_json::Value::String(s) => { a.push(product_os_store::Value::String(s.to_string())) }
                            _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Error processing request data".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                        }
                    };
                    values.push(product_os_store::Value::Array(a.to_owned()));
                },
                serde_json::Value::Object(o) => { values.push(product_os_store::Value::Json(serde_json::Value::Object(o.to_owned()))); }
            }
        }

        let insert_instruction = product_os_store::Instruction::InstructionComplete(
            product_os_store::InstructionType::Insert,
            table_name,
            Some(product_os_store::Fields::SpecificString(fields)),
            Some(vec!(values)),
            None,
            None,
            None,
            None,
            None,
            None,
            None
        );

        match store.execute(&insert_instruction).await {
            Ok(_) => {
                Response::builder()
                    .status(StatusCode::OK)
                    .header("content-type", "application/json")
                    .body(Body::from(payload.to_string()))
                    .unwrap()
            }
            Err(e) => ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Error making request: {:?}", e)), &StatusCode::INTERNAL_SERVER_ERROR)
        }
    }

    async fn update(State(_service): State<Arc<ProductOSServiceHandler>>,
                    Path(identifier): Path<String>,
                    Query(query_params): Query<HashMap<String, Value>>,
                    Json(payload): Json<serde_json::Value>) -> Response<Body> {
        tracing::info!("UPDATE request received:\n{:?}\n{:?}\n{:?}", identifier, payload, query_params);

        Response::builder()
            .status(StatusCode::NOT_IMPLEMENTED)
            .header("content-type", "application/json")
            .body(Body::from("{}"))
            .unwrap()
    }

    async fn patch(State(service): State<Arc<ProductOSServiceHandler>>,
                   Path(identifier): Path<String>,
                   Query(query_params): Query<HashMap<String, Value>>,
                   Json(payload): Json<serde_json::Value>) -> Response<Body> {
        tracing::info!("PATCH request received:\n{:?}\n{:?}\n{:?}", identifier, payload, query_params);

        let store: Arc<ProductOSRelationalStore> = service.get_store();
        let table_name: String = service.get_table_name();
        let identifier_name: String = service.get_identifier_name();

        let mut params: Vec<(String, String)> = vec!();
        for (k, val) in query_params.clone() {
            match val {
                Value::String(str) => { params.push((k.to_string(), str)); }
                Value::Array(arr) => {
                    for v in arr {
                        match v {
                            Value::String(str) => { params.push((k.to_string(), str)); }
                            _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                        }
                    }
                }
                _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
            }
        }

        match update_object_to_fields_values(payload.clone()) {
            Ok((fields, values)) => {
                let update_instruction = product_os_store::Instruction::InstructionComplete(
                    product_os_store::InstructionType::Update,
                    table_name,
                    Some(product_os_store::Fields::SpecificString(fields)),
                    Some(vec!(values)),
                    None,
                    Some(product_os_store::Expression::EqualTo(identifier_name, identifier)),
                    None,
                    None,
                    None,
                    None,
                    None
                );

                match store.execute(&update_instruction).await {
                    Ok(_) => {
                        Response::builder()
                            .status(StatusCode::OK)
                            .header("content-type", "application/json")
                            .body(Body::from(payload.to_string()))
                            .unwrap()
                    }
                    Err(e) => ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Error making request: {:?}", e)), &StatusCode::INTERNAL_SERVER_ERROR)
                }
            }
            Err(e) => ProductOSRouterError::error_response(e, &StatusCode::BAD_REQUEST)
        }
    }

    async fn remove(State(service): State<Arc<ProductOSServiceHandler>>,
                    Path(identifier): Path<String>,
                    Query(query_params): Query<HashMap<String, Value>>) -> Response<Body> {
        tracing::info!("REMOVE request received:\n{:?}\n{:?}", identifier, query_params);

        let store: Arc<ProductOSRelationalStore> = service.get_store();
        let table_name: String = service.get_table_name();
        let identifier_name: String = service.get_identifier_name();

        let mut params: Vec<(String, String)> = vec!();
        for (k, val) in query_params.clone() {
            match val {
                Value::String(str) => { params.push((k.to_string(), str)); }
                Value::Array(arr) => {
                    for v in arr {
                        match v {
                            Value::String(str) => { params.push((k.to_string(), str)); }
                            _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
                        }
                    }
                }
                _ => return ProductOSRouterError::error_response(ProductOSRouterError::Process("Query type error".to_string()), &StatusCode::INTERNAL_SERVER_ERROR)
            }
        }

        let delete_instruction = product_os_store::Instruction::InstructionComplete(
            product_os_store::InstructionType::Delete,
            table_name,
            None,
            None,
            None,
            Some(product_os_store::Expression::EqualTo(identifier_name, identifier)),
            None,
            None,
            None,
            None,
            None
        );

        match store.execute(&delete_instruction).await {
            Ok(_) => {
                Response::builder()
                    .status(StatusCode::OK)
                    .header("content-type", "application/json")
                    .body(Body::from("{}"))
                    .unwrap()
            }
            Err(e) => ProductOSRouterError::error_response(ProductOSRouterError::Process(format!("Error making request: {:?}", e)), &StatusCode::INTERNAL_SERVER_ERROR)
        }
    }
}


#[async_trait]
impl Feature for ProductOSServiceHandler {
    async fn register(&self, feature: Arc<dyn Feature>, base_path: String, router: &mut product_os_router::ProductOSRouter) -> RegistryFeature {
        // Downcast the feature to ProductOSServiceHandler
        // This is safe because we know the feature is a ProductOSServiceHandler when registering
        let service_handler = Arc::new(ProductOSServiceHandler {
            name: self.name.clone(),
            path: self.path.clone(),
            table_name: self.table_name.clone(),
            identifier_name: self.identifier_name.clone(),
            controller: self.controller.clone(),
            store: self.store.clone(),
            default_limit: self.default_limit,
            default_offset: self.default_offset,
        });
        
        ProductOSServiceHandler::service_handler(base_path.clone(), router, service_handler);

        let mut identifier= self.name.to_owned();
        identifier.push_str("Handler");

        RegistryFeature {
            identifier,
            paths: vec!(base_path),
            feature: Some(feature),
            feature_mut: None
        }
    }

    async fn register_mut(&self, _feature: Arc<Mutex<dyn Feature>>, _base_path: String, _router: &mut product_os_router::ProductOSRouter) -> RegistryFeature {
        panic!("Mutable service handler not allowed to be registered")
    }

    fn identifier(&self) -> String {
        "Content".to_string()
    }

    async fn request(&self, _action: &What, _input: &Option<serde_json::Value>, _semver: &str) -> Response<Body> {
        Response::builder()
            .status(StatusCode::NOT_IMPLEMENTED)
            .header("content-type", "application/json")
            .body(Body::from("{}"))
            .unwrap()
    }

    async fn request_mut(&mut self, action: &What, input: &Option<serde_json::Value>, semver: &str) -> Response<Body> {
        self.request(action, input, semver).await
    }
}




fn row_to_object(row: RelationalRow) -> Result<serde_json::Value, ProductOSRouterError> {
    let mut values = serde_json::Map::new();

    let columns = row.columns();

    for column in columns {
        let name = column.name().to_string();
        let position = column.ordinal();

        let column_type = column.type_str();

        // https://docs.rs/sqlx/latest/sqlx/postgres/types/index.html
        match column_type.as_str() {
            "BOOL" | "BOOL[]" => {
                match row.try_get::<bool>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::Bool(value)) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "\"CHAR\"" | "\"CHAR\"[]" => {
                match row.try_get::<i8>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::Number(serde_json::Number::from(value))) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "INT2" | "SMALLINT" | "SMALLSERIAL" => {
                match row.try_get::<i16>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::Number(serde_json::Number::from(value))) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "INT4" | "INT" | "SERIAL" => {
                match row.try_get::<i32>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::Number(serde_json::Number::from(value))) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "INT8" | "BIGINT" | "BIGSERIAL" => {
                match row.try_get::<i64>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::Number(serde_json::Number::from(value))) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "FLOAT4" | "REAL" => {
                match row.try_get::<f32>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::Number(serde_json::Number::from_f64(f64::from(value)).unwrap())) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "FLOAT8" | "DOUBLE PRECISION" => {
                match row.try_get::<f64>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::Number(serde_json::Number::from_f64(value).unwrap())) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "VARCHAR" | "CHAR" | "VARCHAR[]" | "CHAR[]" | "NAME" | "TEXT" => {
                match row.try_get::<String>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::String(value)) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            "BYTEA" | "BYTEA[]" => {
                match row.try_get::<Vec<u8>>(position) {
                    Ok(value) => {
                        let mut arr = vec!();
                        for v in value {
                            arr.push(serde_json::Value::Number(serde_json::Number::from(v)))
                        }
                        values.insert(name, serde_json::Value::Array(arr))
                    }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            #[cfg(any(feature = "postgres_store", feature = "sqlite_store"))]
            "TIMESTAMPTZ" | "TIMESTAMPTZ[]" => {
                match row.try_get::<product_os_store::types::chrono::DateTime<product_os_store::types::chrono::Utc>>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::String(value.to_rfc3339())) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            #[cfg(any(feature = "postgres_store", feature = "sqlite_store"))]
            "TIMESTAMP" | "TIMESTAMP[]" => {
                match row.try_get::<product_os_store::types::chrono::NaiveDateTime>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::String(value.to_string())) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            #[cfg(any(feature = "postgres_store", feature = "sqlite_store"))]
            "DATE" | "DATE[]" => {
                match row.try_get::<product_os_store::types::chrono::NaiveDate>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::String(value.to_string())) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            #[cfg(any(feature = "postgres_store", feature = "sqlite_store"))]
            "TIME" | "TIME[]" => {
                match row.try_get::<product_os_store::types::chrono::NaiveTime>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::String(value.to_string())) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            #[cfg(any(feature = "postgres_store", feature = "sqlite_store"))]
            "UUID" | "UUID[]" => {
                match row.try_get::<product_os_store::types::uuid::Uuid>(position) {
                    Ok(value) => { values.insert(name, serde_json::Value::String(value.to_string())) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            #[cfg(any(feature = "postgres_store", feature = "sqlite_store"))]
            "JSON" | "JSON[]" | "JSONB" | "JSONB[]" => {
                match row.try_get::<product_os_store::types::Json<serde_json::value::Value>>(position) {
                    Ok(value) => { values.insert(name, value.0) }
                    Err(e) => {
                        match e {
                            RelationalError::DecodeError => { values.insert(name, serde_json::Value::Null) }
                            _ => return Err(ProductOSRouterError::Process(format!("Error translating value: {:?}", e)))
                        }
                    }
                };
            }
            _ => {}
        };
    }

    let response = serde_json::Value::Object(values);
    Ok(response)
}



/// Converts a vector of query parameters to a JSON object
///
/// This function transforms URL query parameters into a nested JSON structure,
/// supporting FeathersJS query syntax including array and object notation.
///
/// # Arguments
///
/// * `query` - Vector of (key, value) tuples representing query parameters
///
/// # Query Syntax
///
/// The function supports FeathersJS query syntax:
/// - Simple keys: `name=value` becomes `{"name": "value"}`
/// - Array notation: `tags[]=a&tags[]=b` becomes `{"tags": ["a", "b"]}`
/// - Object notation: `user[name]=John` becomes `{"user": {"name": "John"}}`
/// - Nested objects: `filter[age][$gt]=21` becomes `{"filter": {"age": {"$gt": "21"}}}`
///
/// # Returns
///
/// A `serde_json::Value` representing the query as a JSON object.
///
/// # Example
///
/// ```rust
/// # use product_os_service_handler::query_to_object;
/// let query = vec![
///     ("name".to_string(), "John".to_string()),
///     ("age[$gt]".to_string(), "21".to_string()),
/// ];
/// let result = query_to_object(query);
/// // result: {"name": "John", "age": {"$gt": "21"}}
/// ```
pub fn query_to_object(query: Vec<(String, String)>) -> serde_json::Value {
    let mut object = serde_json::Value::Object(serde_json::Map::new());

    for (full_key, value) in query {
        let key = full_key.split("[").collect::<Vec<&str>>().first().unwrap().to_owned();

        let mut object_ref = &mut object;
        let mut object_key_name = None;

        let mut is_object = false;
        let mut is_array = false;

        for capture in OBJECT_ARRAY_ELEMENT_REGEX.captures_iter(full_key.as_str()) {
            tracing::info!("Capture {:?}", capture);
            if capture[1].is_empty() {
                if object_ref.as_object().unwrap().get(key).is_none() {
                    object_ref.as_object_mut().unwrap().insert(key.to_string(), serde_json::Value::Array(vec!()));
                }

                object_ref = object_ref.as_object_mut().unwrap().get_mut(key).unwrap();
                object_key_name = None;

                is_object = false;
                is_array = true;
            }
            else {
                if object_ref.as_object().unwrap().get(key).is_none() {
                    object_ref.as_object_mut().unwrap().insert(key.to_string(), serde_json::Value::Object(serde_json::Map::new()));
                }

                object_ref = object_ref.as_object_mut().unwrap().get_mut(key).unwrap();
                object_key_name = Some(capture[1].to_string());

                is_object = true;
                is_array = false;
            }
        }

        if is_object {
            object_ref.as_object_mut().unwrap().insert(object_key_name.unwrap(), serde_json::Value::String(value.to_string()));
        }
        else if is_array {
            tracing::info!("Array: {:?}", object_ref);
            object_ref.as_array_mut().unwrap().push(serde_json::Value::String(value.to_string()));
        }
        else {
            object_ref.as_object_mut().unwrap().insert(key.to_string(), serde_json::Value::String(value.to_string()));
        }
    }

    object
}



/// Converts a query string to a JSON object
///
/// Parses a URL query string in the format `key1=value1&key2=value2` and converts
/// it to a nested JSON structure using the same logic as [`query_to_object`].
///
/// # Arguments
///
/// * `query_string` - A URL-encoded query string (e.g., "name=John&age[$gt]=21")
///
/// # Returns
///
/// A `serde_json::Value` representing the query as a JSON object.
///
/// # Example
///
/// ```rust
/// # use product_os_service_handler::query_string_to_object;
/// let query_str = "name=John&age[$gt]=21".to_string();
/// let result = query_string_to_object(query_str);
/// // result: {"name": "John", "age": {"$gt": "21"}}
/// ```
pub fn query_string_to_object(query_string: String) -> serde_json::Value {
    let query_string_elements = query_string.split("&").collect::<Vec<&str>>();

    let mut query_array = Vec::new();
    for element in query_string_elements {
        let key_value = element.split("=").collect::<Vec<&str>>();
        if key_value.len() == 2 {
            let full_key = key_value.first().unwrap().to_string();
            let value = key_value.get(1).unwrap().to_string();

            query_array.push((full_key, value));
        }
    }

    query_to_object(query_array)
}



/// Converts a JSON object back to a query string
///
/// This is the inverse operation of [`query_to_object`], converting a nested JSON
/// structure back into a URL query string format compatible with FeathersJS.
///
/// # Arguments
///
/// * `query_object` - A JSON value to convert to query string format
/// * `prefix` - A prefix for nested keys (use empty string for top-level)
///
/// # Returns
///
/// A URL query string representation of the JSON object.
///
/// # Example
///
/// ```rust
/// # use product_os_service_handler::object_to_query_string;
/// # use serde_json::json;
/// let obj = json!({"name": "John", "age": {"$gt": 21}});
/// let query_str = object_to_query_string(obj, String::new());
/// // result: "name=John&age[$gt]=21"
/// ```
pub fn object_to_query_string(query_object: serde_json::Value, prefix: String) -> String {
    let mut query_string = String::new();

    match query_object {
        serde_json::Value::Object(object) => {
            for (mut key, value) in object {
                match value.clone() {
                    serde_json::Value::Array(array) => {
                        if !prefix.is_empty() {
                            key = format!("{prefix}[{key}]");
                        }

                        for (counter, val) in array.iter().enumerate() {
                            match val {
                                serde_json::Value::Object(_) | serde_json::Value::Array(_) => {
                                    let array_prefix = if !prefix.is_empty() {
                                        format!("{prefix}[{counter}]")
                                    }
                                    else {
                                        format!("{key}[{counter}]")
                                    };

                                    query_string.push_str(object_to_query_string(val.clone(), array_prefix).as_str());
                                    query_string.push('&');
                                }
                                serde_json::Value::Bool(boolean) => {
                                    let b = boolean.to_string();
                                    query_string.push_str(format!("{key}[]={b}&").as_str())
                                },
                                serde_json::Value::Number(number) => {
                                    let n = number.to_string();
                                    query_string.push_str(format!("{key}[]={n}&").as_str())
                                },
                                serde_json::Value::String(string) => {
                                    let s = string;
                                    query_string.push_str(format!("{key}[]={s}&").as_str())
                                }
                                serde_json::Value::Null => {}
                            }
                        }
                    }
                    serde_json::Value::Object(object) => {
                        if object.contains_key("_bsontype") {
                            #[allow(clippy::collapsible_match)] // Clearer as separate steps
                            match object.get("_bsontype") {
                                None => {}
                                Some(value) => {
                                    if let serde_json::Value::String(val) = value {
                                        if val.as_str() == "ObjectID" {
                                            match object.get("id") {
                                                None => {}
                                                Some(identifier) => {
                                                    if let serde_json::Value::Array(array) = identifier {
                                                        let mut hex_value = String::new();
                                                        for hex in array {
                                                            if let serde_json::Value::Number(number) = hex {
                                                                hex_value.push_str(format!("{:x}", number.as_i64().unwrap()).as_str());
                                                            }
                                                        }

                                                        if !prefix.is_empty() {
                                                            key = format!("{prefix}[{key}]");
                                                        }

                                                        query_string.push_str(format!("{key}={hex_value}&").as_str());
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        else {
                            let object_prefix = if !prefix.is_empty() {
                                format!("{prefix}[{key}]")
                            }
                            else {
                                key
                            };

                            query_string.push_str(object_to_query_string(value.clone(), object_prefix).as_str());
                            query_string.push('&');
                        }
                    }
                    serde_json::Value::Bool(boolean) => {
                        if !prefix.is_empty() {
                            key = format!("{prefix}[{key}]");
                        }

                        let b = boolean.to_string();
                        query_string.push_str(format!("{key}={b}&").as_str())
                    }
                    serde_json::Value::Number(number) => {
                        if !prefix.is_empty() {
                            key = format!("{prefix}[{key}]");
                        }

                        let n = number.to_string();
                        query_string.push_str(format!("{key}={n}&").as_str())
                    }
                    serde_json::Value::String(string) => {
                        if !prefix.is_empty() {
                            key = format!("{prefix}[{key}]");
                        }

                        let s = string;
                        query_string.push_str(format!("{key}={s}&").as_str())
                    }
                    serde_json::Value::Null => {
                        if !prefix.is_empty() {
                            key = format!("{prefix}[{key}]");
                        }

                        query_string.push_str(format!("{key}=&").as_str())
                    }
                }
            }

            query_string.trim_matches(&['&'] as &[_]).to_string()
        }
        _ => query_string
    }
}


fn query_object_to_query_expression(table_name: String, object: serde_json::Value, default_offset: Option<u32>, default_limit: Option<u32>) -> Result<product_os_store::Query, ProductOSRouterError> {
    let table = product_os_store::Table::Table(table_name);
    let join = None;
    let mut fields = Some(product_os_store::Fields::All);
    let distinct = None;
    // let mut expression = Some(product_os_store::Expression::Empty);
    let group_by = None;
    let having = None;
    let mut sort_by = None;
    let offset = default_offset.unwrap_or_default();
    let limit = default_limit.unwrap_or(100);
    let mut fetch = Some(product_os_store::Fetch::CountWithOffset(offset, limit));
    let table_creation = None;


    let mut filter_expression = vec!();

    tracing::info!("Expression object {:?}", object);
    match object.as_object() {
        None => (),
        Some(query_object) => {
            for (key, value) in query_object {
                tracing::info!("Working through expression: {:?} {:?}", key, value);
                match key.as_str() {
                    "$limit" => {
                        if let Some(product_os_store::Fetch::CountWithOffset(offset, _)) = fetch {
                            fetch = Some(product_os_store::Fetch::CountWithOffset(offset, u32::from_str(value.as_str().unwrap()).unwrap()));
                        }
                    },
                    "$skip" => {
                        if let Some(product_os_store::Fetch::CountWithOffset(_, count)) = fetch {
                            fetch = Some(product_os_store::Fetch::CountWithOffset(u32::from_str(value.as_str().unwrap()).unwrap(), count));
                        }
                    },
                    "$sort" => {
                        let mut sort_vec = vec!();
                        for (key, value) in value.as_object().unwrap() {
                            match i32::from_str(value.as_str().unwrap()).unwrap() {
                                -1 => sort_vec.push((key.to_string(), product_os_store::SortOrder::DESC)),
                                _ => sort_vec.push((key.to_string(), product_os_store::SortOrder::ASC))
                            }
                        }
                        sort_by = Some(sort_vec);
                    }
                    "$select" => {
                        let mut array = vec!();
                        let value_array = value.as_array().unwrap();

                        for value in value_array {
                            array.push(value.as_str().unwrap().to_string());
                        }

                        fields = Some(product_os_store::Fields::SpecificString(array));
                    }
                    "$or" => {
                        match query_object_to_expression(value.to_owned()) {
                            Ok(expression) => filter_expression.push(expression),
                            Err(e) => return Err(e)
                        };
                    },
                    key /* $eq */ => {
                        build_query(&mut filter_expression, key, value);
                    }
                }
            }
        }
    }

    tracing::info!("Expression query: {:?}", filter_expression);
    let and_expression_count = filter_expression.len();

    let expression = match and_expression_count {
        0 => None,
        1 => Some(filter_expression.remove(0)),
        2 => Some(product_os_store::Expression::And(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        3 => Some(product_os_store::Expression::And3(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        4 => Some(product_os_store::Expression::And4(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        5 => Some(product_os_store::Expression::And5(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        _ => return Err(ProductOSRouterError::Process(String::from("Unable to handle more than 5 expressions joined")))
    };

    Ok(product_os_store::Query::QueryComplete(
        table,
        join,
        fields,
        distinct,
        expression,
        group_by,
        having,
        sort_by,
        fetch,
        table_creation))

    /*
        Table, // table, Option is for providing an alias
        Option<Join>, // join
        Option<Fields>, // fields
        Option<(DistinctType, Vec<String>)>, // distinct
        Option<Expression>, // where conditions
        Option<Vec<String>>, // group_by, PostgreSQL evaluates the GROUP BY clause after the FROM and WHERE clauses and before the HAVING SELECT, DISTINCT, ORDER BY and LIMIT/FETCH clauses
        Option<Expression>, // having, PostgreSQL evaluates the HAVING clause after the FROM, WHERE, GROUP BY, and before the SELECT, DISTINCT, ORDER BY and LIMIT/FETCH clauses
        Option<Vec<(String, SortOrder)>>, // sort_by
        Option<Fetch>, // fetch
        Option<(bool, String)> // Into -> temporary/permanent table name creation
    */
}



fn build_query(filter_expression: &mut Vec<Expression>, key: &str, value: &serde_json::Value) {
    match value {
        serde_json::Value::Object(obj) => {
            if obj.contains_key("$in") {
                let mut array = vec!();
                let value_array = obj.get("$in").unwrap().as_array().unwrap();

                for value in value_array {
                    array.push(product_os_store::Value::String(value.as_str().unwrap().to_string()));
                }

                filter_expression.push(product_os_store::Expression::In(key.to_string(), array));
            }
            if obj.contains_key("$nin") {
                let mut array = vec!();
                let value_array = obj.get("$nin").unwrap().as_array().unwrap();

                for value in value_array {
                    array.push(product_os_store::Value::String(value.as_str().unwrap().to_string()));
                }

                filter_expression.push(product_os_store::Expression::Not(Box::new(product_os_store::Expression::In(key.to_string(), array))));
            }
            if obj.contains_key("$lt") { filter_expression.push(product_os_store::Expression::LessThan(key.to_string(), obj.get("$lt").unwrap().as_str().unwrap().to_string())); }
            if obj.contains_key("$lte") { filter_expression.push(product_os_store::Expression::LessThanEqualTo(key.to_string(), obj.get("$lte").unwrap().as_str().unwrap().to_string())); }
            if obj.contains_key("$gt") { filter_expression.push(product_os_store::Expression::GreaterThan(key.to_string(), obj.get("$gt").unwrap().as_str().unwrap().to_string())); }
            if obj.contains_key("$gte") { filter_expression.push(product_os_store::Expression::GreaterThanEqualTo(key.to_string(), obj.get("$gte").unwrap().as_str().unwrap().to_string())); }
            if obj.contains_key("$ne") { filter_expression.push(product_os_store::Expression::NotEqualTo(key.to_string(), obj.get("$ne").unwrap().as_str().unwrap().to_string())); }

            tracing::info!("Looping through object value: {:?}", obj);
            for (sub_key, sub_value) in obj {
                if !sub_key.starts_with("$") {
                    let mut extended_key = key.to_string();

                    if extended_key.contains(">>") {
                        extended_key = extended_key.replace(">>", ">");
                    }

                    extended_key.push_str("->>'");
                    extended_key.push_str(sub_key.as_str());
                    extended_key.push('\'');

                    tracing::info!("Looping through subs {:?} {:?}", extended_key, sub_value);

                    match sub_value {
                        serde_json::Value::Object(_) => {
                            build_query(filter_expression, extended_key.as_str(), sub_value);
                        },
                        serde_json::Value::Array(_) => {
                            build_query(filter_expression, extended_key.as_str(), sub_value);
                        }
                        _ => {
                            if query_key_value_to_expression(filter_expression, extended_key.as_str(), sub_value).is_ok() {};
                        }
                    }
                }
            }
        }
        serde_json::Value::Array(value_array) => {
            let mut array = vec!();

            for value in value_array {
                array.push(product_os_store::Value::String(value.as_str().unwrap().to_string()));
            }

            filter_expression.push(product_os_store::Expression::In(key.to_string(), array));
        }
        /*
        serde_json::Value::Null => {}
        serde_json::Value::Bool(_) => {}
        serde_json::Value::Number(_) => {}
        serde_json::Value::String(_) => {}
        */
        _ => {
            tracing::info!("Expression evaluating: {:?} {:?}", key, value);
            if query_key_value_to_expression(filter_expression, key, value).is_ok() {};
        }
    }
}




fn query_object_to_expression(object: serde_json::Value) -> Result<product_os_store::Expression, ProductOSRouterError> {
    let mut filter_expression = vec!();

    match object.as_object() {
        None => (),
        Some(query_object) => {
            for (key, value) in query_object {
                if query_key_value_to_expression(&mut filter_expression, key.as_str(), value).is_ok() {};
            }
        }
    }

    let and_expression_count = filter_expression.len();

    match and_expression_count {
        0 => Ok(product_os_store::Expression::Empty),
        1 => Ok(filter_expression.remove(0)),
        2 => Ok(product_os_store::Expression::And(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        3 => Ok(product_os_store::Expression::And3(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        4 => Ok(product_os_store::Expression::And4(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        5 => Ok(product_os_store::Expression::And5(Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)), Box::new(filter_expression.remove(0)))),
        _ => Err(ProductOSRouterError::Process(String::from("Unable to handle more than 5 expressions joined")))
    }
}

fn query_key_value_to_expression(filter_expression: &mut Vec<Expression>, key: &str, value: &serde_json::Value) -> Result<(), ProductOSRouterError> {
    match key {
        "$in" => {
            let mut array = vec!();
            let value_array = value.as_array().unwrap();

            for value in value_array {
                array.push(product_os_store::Value::String(value.as_str().unwrap().to_string()));
            }

            key.to_string(); drop(array);
        }
        "$nin" => {
            let mut array = vec!();
            let value_array = value.as_array().unwrap();

            for value in value_array {
                array.push(product_os_store::Value::String(value.as_str().unwrap().to_string()));
            }

            filter_expression.push(product_os_store::Expression::Not(Box::new(product_os_store::Expression::In(key.to_string(), array))));
        }
        "$lt" => { filter_expression.push(product_os_store::Expression::LessThan(key.to_string(), value.as_str().unwrap().to_string())) },
        "$lte" => { filter_expression.push(product_os_store::Expression::LessThanEqualTo(key.to_string(), value.as_str().unwrap().to_string())) },
        "$gt" => { filter_expression.push(product_os_store::Expression::GreaterThan(key.to_string(), value.as_str().unwrap().to_string())) },
        "$gte" => { filter_expression.push(product_os_store::Expression::GreaterThanEqualTo(key.to_string(), value.as_str().unwrap().to_string())) },
        "$or" => {
            match query_object_to_expression(value.to_owned()) {
                Ok(expression) => filter_expression.push(expression),
                Err(e) => return Err(e)
            }
        },
        "$ne" => { filter_expression.push(product_os_store::Expression::NotEqualTo(key.to_string(), value.as_str().unwrap().to_string())) },
        "$eq" => { filter_expression.push(product_os_store::Expression::EqualTo(key.to_string(), value.as_str().unwrap().to_string())) },
        _ => {
            let search_value = value.as_str().unwrap().to_string().replace("*", "%");
            filter_expression.push(product_os_store::Expression::Like(key.to_string(), search_value))
        }
    }

    Ok(())
}


fn update_object_to_fields_values(object: serde_json::Value) -> Result<(Vec<String>, Vec<product_os_store::Value>), ProductOSRouterError> {
    let mut fields = vec!();
    let mut values = vec!();

    match object.as_object() {
        None => (),
        Some(update_object) => {
            for (key, value) in update_object {
                match build_update_object(&mut fields, &mut values, key, value, false, None) {
                    Ok(_) => {}
                    Err(e) => return Err(e)
                }
            }
        }
    }

    Ok((fields, values))
}


fn build_update_object(fields: &mut Vec<String>, values: &mut Vec<product_os_store::Value>, key: &str, value: &serde_json::Value, is_json: bool, json_key: Option<&str>) -> Result<(), ProductOSRouterError> {
    match value {
        serde_json::Value::Object(obj) => {
            tracing::info!("Looping through object value: {:?}", obj);
            for (sub_key, sub_value) in obj {
                match sub_key.as_str() {
                    "$push" => {
                        if let serde_json::Value::Object(obj) = sub_value {
                            let mut extended_key = String::new();
                            let mut sub_value = serde_json::Value::Object(serde_json::Map::new());

                            for (s_key, s_value) in obj {
                                extended_key = s_key.clone();
                                sub_value = s_value.clone();
                            }

                            extended_key.push(',');
                            extended_key.push_str("999999999");

                            match update_key_value_to_fields_values(fields, values, key, &sub_value, true, Some(extended_key.as_str())) {
                                Ok(_) => {}
                                Err(e) => return Err(e)
                            }
                        }
                    }
                    "$pull" => {
                        /*
                        Remove the last tag:
                        UPDATE test SET data = data #- '{tags,-1}'
                        */
                    }
                    _ => {
                        let extended_key = match json_key {
                            None => {
                                String::from(sub_key)
                            },
                            Some(jk) => {
                                let mut extended_key = jk.to_string();

                                extended_key.push(',');
                                extended_key.push_str(sub_key.as_str());

                                extended_key
                            }
                        };

                        tracing::info!("Looping through subs {:?} {:?}", extended_key, sub_value);

                        match update_key_value_to_fields_values(fields, values, key, sub_value, true, Some(extended_key.as_str())) {
                            Ok(_) => {}
                            Err(e) => return Err(e)
                        }

                        /*
                        match sub_value {
                            serde_json::Value::Object(obj) => {
                                if obj.keys().len() > 1 {
                                    match update_key_value_to_fields_values(fields, values, key, sub_value, true, Some(extended_key.as_str())) {
                                        Ok(_) => {}
                                        Err(e) => return Err(e)
                                    }
                                }
                                else {
                                    build_update_object(fields, values, key, sub_value, true, Some(extended_key.as_str()));
                                }
                            },
                            _ => {
                                match update_key_value_to_fields_values(fields, values, key, sub_value, true, Some(extended_key.as_str())) {
                                    Ok(_) => {}
                                    Err(e) => return Err(e)
                                }
                            }
                        }
                        */
                    }
                }
            }
        }
        _ => {
            match update_key_value_to_fields_values(fields, values, key, value, is_json, json_key) {
                Ok(_) => {}
                Err(e) => return Err(e)
            }
        }
    }

    Ok(())
}

fn update_key_value_to_fields_values(fields: &mut Vec<String>, values: &mut Vec<product_os_store::Value>, key: &str, value: &serde_json::Value, is_json: bool, json_key: Option<&str>) -> Result<(), ProductOSRouterError> {
    if fields.contains(&key.to_string()) && is_json {
        match fields.binary_search(&key.to_string()) {
            Ok(field_position) => {
                let current_value = values.remove(field_position);

                if let product_os_store::Value::JsonSet(key, jkey, jvalue) = current_value {
                    let store_value = match value {
                        serde_json::Value::Null =>product_os_store::Value::Null,
                        serde_json::Value::Bool(b) => product_os_store::Value::Boolean(b.to_owned()),
                        serde_json::Value::Number(n) => { product_os_store::Value::BigNumber(i128::from(n.as_i64().unwrap())) }
                        serde_json::Value::String(s) => product_os_store::Value::String(s.to_string()),
                        serde_json::Value::Array(arr) => {
                            let mut a = vec!();
                            for e in arr {
                                match e {
                                    serde_json::Value::Bool(b) => { a.push(product_os_store::Value::Boolean(b.to_owned())) },
                                    serde_json::Value::Number(n) => { a.push(product_os_store::Value::BigNumber(i128::from(n.as_i64().unwrap()))) }
                                    serde_json::Value::String(s) => { a.push(product_os_store::Value::String(s.to_string())) }
                                    _ => return Err(ProductOSRouterError::Process("Error processing request data".to_string()))
                                }
                            };

                            product_os_store::Value::Array(a.to_owned())
                        }
                        obj => {
                            product_os_store::Value::Json(obj.clone())
                        }
                    };

                    values.insert(field_position,
                    product_os_store::Value::JsonSet(
                        Box::new(product_os_store::Value::JsonSet(key.clone(), jkey.clone(), jvalue.clone())),
                        json_key.unwrap().to_string(),
                        Box::new(store_value)
                    ));
                }
            },
            Err(_) => return Err(ProductOSRouterError::Process("Error processing request data".to_string()))
        }
    }
    else {
        fields.push(key.to_string());

        match value {
            serde_json::Value::Null => {
                if is_json { values.push(product_os_store::Value::JsonSet(Box::new(product_os_store::Value::Raw(key.to_string())), json_key.unwrap().to_string(), Box::new(product_os_store::Value::Null))); }
                else { values.push(product_os_store::Value::Null); }
            }
            serde_json::Value::Bool(b) => {
                if is_json { values.push(product_os_store::Value::JsonSet(Box::new(product_os_store::Value::Raw(key.to_string())), json_key.unwrap().to_string(), Box::new(product_os_store::Value::Boolean(b.to_owned())))); }
                else { values.push(product_os_store::Value::Boolean(b.to_owned())); }
            }
            serde_json::Value::Number(n) => {
                if is_json { values.push(product_os_store::Value::JsonSet(Box::new(product_os_store::Value::Raw(key.to_string())), json_key.unwrap().to_string(), Box::new(product_os_store::Value::BigNumber(i128::from(n.as_i64().unwrap()))))); }
                else { values.push(product_os_store::Value::BigNumber(i128::from(n.as_i64().unwrap()))); }
            }
            serde_json::Value::String(s) => {
                if is_json { values.push(product_os_store::Value::JsonSet(Box::new(product_os_store::Value::Raw(key.to_string())), json_key.unwrap().to_string(), Box::new(product_os_store::Value::String(s.to_string())))); }
                else { values.push(product_os_store::Value::String(s.to_string())); }
            }
            serde_json::Value::Array(arr) => {
                let mut a = vec!();
                for e in arr {
                    match e {
                        serde_json::Value::Bool(b) => { a.push(product_os_store::Value::Boolean(b.to_owned())) },
                        serde_json::Value::Number(n) => { a.push(product_os_store::Value::BigNumber(i128::from(n.as_i64().unwrap()))) }
                        serde_json::Value::String(s) => { a.push(product_os_store::Value::String(s.to_string())) }
                        _ => return Err(ProductOSRouterError::Process("Error processing request data".to_string()))
                    }
                };

                if is_json { values.push(product_os_store::Value::JsonSet(Box::new(product_os_store::Value::Raw(key.to_string())), json_key.unwrap().to_string(), Box::new(product_os_store::Value::Array(a.to_owned())))); }
                else { values.push(product_os_store::Value::Array(a.to_owned())); }
            }
            obj => {
                if is_json { values.push(product_os_store::Value::JsonSet(Box::new(product_os_store::Value::Raw(key.to_string())), json_key.unwrap().to_string(), Box::new(product_os_store::Value::Json(obj.clone())))); }
                else { values.push(product_os_store::Value::Json(obj.clone())); }
            }
        }
    }



    Ok(())
}


/*
    jsonb_set(
        jsonb_set(
            '{age:26}'::jsonb,
            '{age}',
            '"30"'::jsonb)::jsonb,
        '{city}',
        '"new york city"'::jsonb
    )
*/