use crate::{
    auth::{Auth, Authentication},
    error::AppError,
    request::{
        EntryRequestBody, PathParamRead, PathParamWrite, QueryParamRead, RequestBody,
        RequestHeader, RequestHeaderInsert, RequestHeaderUpdate, RequestParams, RequestPathUpdate,
        RequestQueryUpdate, WorkspaceRequest,
    },
    workspace::{
        Workspace, WorkspaceEntry, WorkspaceEntryBase, WorkspaceEntryCreate, WorkspaceEntryDisplay,
        WorkspaceEntryType, WorkspaceEnvVariable, WorkspaceEnvironment,
    },
    AppResult,
};
use serde::Deserialize;
use sqlx::{
    sqlite::{SqliteConnectOptions, SqlitePool},
    types::Json,
    ConnectOptions, QueryBuilder,
};
use std::{collections::HashMap, str::FromStr};

/// Used in update DTOs for **optional** properties. A value indicates a parameter needs to be updated to whatever is contained in it, a null indicates to set the field to null.
#[derive(Debug, Deserialize)]
pub enum Update<T> {
    Value(T),
    Null,
}

impl<T: Clone> Clone for Update<T> {
    fn clone(&self) -> Self {
        match self {
            Self::Value(arg0) => Self::Value(arg0.clone()),
            Self::Null => Self::Null,
        }
    }
}

impl<T: Copy> Copy for Update<T> {}

pub async fn init(url: &str) -> SqlitePool {
    let mut opts = SqliteConnectOptions::from_str(url).unwrap();

    opts = ConnectOptions::log_statements(opts, tauri_plugin_log::log::LevelFilter::Off);

    let pool = SqlitePool::connect_with(opts)
        .await
        .expect("error while connecting to db");

    sqlx::migrate!()
        .run(&pool)
        .await
        .expect("error in migrations");

    pool
}

pub async fn create_workspace(db: SqlitePool, name: String) -> Result<Workspace, String> {
    match sqlx::query_as!(
        Workspace,
        "INSERT INTO workspaces (name) VALUES (?) RETURNING id, name",
        name
    )
    .fetch_one(&db)
    .await
    {
        Ok(workspace) => Ok(workspace),
        Err(e) => Err(e.to_string()),
    }
}

pub async fn list_workspaces(db: SqlitePool) -> AppResult<Vec<Workspace>> {
    Ok(
        sqlx::query_as!(Workspace, "SELECT id, name FROM workspaces")
            .fetch_all(&db)
            .await?,
    )
}

/// Check whether the entry whose `id` == `parent_id` supports children.
async fn check_parent(db: &SqlitePool, parent_id: Option<i64>) -> AppResult<()> {
    let Some(parent_id) = parent_id else {
        return Ok(());
    };

    let ty = sqlx::query!("SELECT type FROM workspace_entries WHERE id = ?", parent_id)
        .fetch_one(db)
        .await?
        .r#type;

    if !matches!(WorkspaceEntryType::from(ty), WorkspaceEntryType::Collection) {
        return Err(AppError::InvalidUpdate(format!(
            "{parent_id} is not a valid parent ID (type: {ty})"
        )));
    }

    Ok(())
}

pub async fn create_workspace_entry(
    db: SqlitePool,
    entry: WorkspaceEntryCreate,
) -> AppResult<WorkspaceEntryBase> {
    match entry {
        WorkspaceEntryCreate::Collection {
            name,
            workspace_id,
            parent_id,
            auth_inherit,
        } => {
            check_parent(&db, parent_id).await?;
            let entry = sqlx::query_as!(
                WorkspaceEntryBase,
                r#"INSERT INTO workspace_entries(name, workspace_id, parent_id, type, auth_inherit) VALUES (?, ?, ?, ?, ?) 
                   RETURNING id, workspace_id, parent_id, name, type, auth, auth_inherit"#,
                name,
                workspace_id,
                parent_id,
                1,
                auth_inherit,
            )
            .fetch_one(&db).await?;

            Ok(entry)
        }
        WorkspaceEntryCreate::Request {
            name,
            workspace_id,
            parent_id,
            method,
            url,
            auth_inherit,
        } => {
            check_parent(&db, parent_id).await?;

            let mut tx = db.begin().await?;

            let entry = match sqlx::query_as!(
                WorkspaceEntryBase,
                r#"INSERT INTO workspace_entries(name, workspace_id, parent_id, type, auth_inherit) VALUES (?, ?, ?, ?, ?) 
                   RETURNING id, workspace_id, name, parent_id, type, auth, auth_inherit"#,
                name,
                workspace_id,
                parent_id,
                0,
                auth_inherit
            )
            .fetch_one(&mut *tx).await {
                Ok(entry) => entry,
                Err(e) => {
                    tx.rollback().await?;
                    return Err(e.into());
                }

            };

            match sqlx::query!(
                "INSERT INTO request_params(workspace_id, request_id, method, url) VALUES (?, ?, ?, ?)",
                workspace_id,
                entry.id,
                method,
                url
            )
            .execute(&mut *tx)
            .await {
                Ok(_) => {},
                Err(e) => {
                    tx.rollback().await?;
                    return Err(e.into());
                }
            }
            ;

            tx.commit().await?;

            Ok(entry)
        }
    }
}

pub async fn insert_request_body(
    db: SqlitePool,
    entry_id: i64,
    body: RequestBody,
) -> AppResult<EntryRequestBody> {
    Ok(sqlx::query_as!(EntryRequestBody, r#"INSERT INTO request_bodies(request_id, ty, content) VALUES (?, ?, ?) RETURNING id, ty AS "ty: _", content"#, entry_id, body.ty, body.content).fetch_one(&db).await?)
}

pub async fn update_request_body(
    db: SqlitePool,
    id: i64,
    body: Update<RequestBody>,
) -> AppResult<()> {
    match body {
        Update::Value(body) => {
            sqlx::query!(
                "UPDATE request_bodies SET ty = ?, content = ? WHERE id = ?",
                body.ty,
                body.content,
                id,
            )
            .execute(&db)
            .await?;
        }
        Update::Null => {
            sqlx::query!("DELETE FROM request_bodies WHERE id = ?", id)
                .execute(&db)
                .await?;
        }
    }
    Ok(())
}

pub async fn update_request_method(db: SqlitePool, request_id: i64, method: &str) -> AppResult<()> {
    sqlx::query!(
        "UPDATE request_params SET method = ? WHERE request_id = ?",
        method,
        request_id
    )
    .execute(&db)
    .await?;

    Ok(())
}

pub async fn get_query_param(db: &SqlitePool, qp_id: i64) -> AppResult<QueryParamRead> {
    Ok(sqlx::query_as!(
        QueryParamRead,
        "SELECT id, position, key, value FROM request_query_params WHERE id = ?",
        qp_id
    )
    .fetch_one(db)
    .await?)
}

pub async fn delete_query_param(db: &SqlitePool, id: i64) -> AppResult<()> {
    sqlx::query!("DELETE FROM request_query_params  WHERE id = ?", id)
        .execute(db)
        .await?;

    Ok(())
}

pub async fn update_query_param_values(
    db: &SqlitePool,
    id: i64,
    key: &str,
    value: &str,
) -> AppResult<()> {
    sqlx::query!(
        "UPDATE request_query_params SET key = ?, value = ? WHERE id = ?",
        key,
        value,
        id
    )
    .execute(db)
    .await?;

    Ok(())
}

pub async fn update_query_param_enabled(
    db: &SqlitePool,
    qp_id: i64,
    position: Option<i64>,
) -> AppResult<QueryParamRead> {
    Ok(sqlx::query_as!(
        QueryParamRead,
        "UPDATE request_query_params SET position = ? WHERE id = ? RETURNING id, position, key, value",
        position,
        qp_id
    )
    .fetch_one(db)
    .await?)
}

pub async fn update_query_param_position(
    db: &SqlitePool,
    req_id: i64,
    // Offset by amount
    offset: i64,
    // Start from position
    position: i64,
) -> AppResult<()> {
    sqlx::query!(
        "UPDATE request_query_params SET position = position + ? WHERE request_id = ? AND position > ?",
        offset,
        req_id,
        position,
    )
    .execute(db)
    .await?;

    Ok(())
}

/// Return only the active request params.
pub async fn get_request_url_params(
    db: &SqlitePool,
    request_id: i64,
) -> AppResult<(Vec<PathParamRead>, Vec<QueryParamRead>)> {
    let mut path = vec![];
    let mut query = vec![];

    let params = sqlx::query!(
        r#"
        SELECT id, position, name, value, 0 AS type
        FROM request_path_params WHERE request_id = ?
        UNION
        SELECT id, position, key AS "name", value, 1 AS type
        FROM request_query_params WHERE request_id = ?
      "#,
        request_id,
        request_id
    )
    .fetch_all(db)
    .await?;

    for param in params {
        match param.r#type {
            0 => path.push(PathParamRead::new(
                param.id,
                // Path positions can never be null
                param.position.unwrap(),
                param.name,
                param.value,
            )),
            1 => query.push(QueryParamRead::new(
                param.id,
                param.position,
                param.name,
                param.value,
            )),
            _ => unreachable!(),
        }
    }

    Ok((path, query))
}

pub async fn update_request_url(
    db: &SqlitePool,
    request_id: i64,
    url: &str,
    path_params: Option<Vec<RequestPathUpdate<'_>>>,
    query_params: Option<Vec<RequestQueryUpdate<'_>>>,
) -> AppResult<()> {
    let mut tx = db.begin().await?;

    sqlx::query!(
        "UPDATE request_params SET url = ? WHERE request_id = ?",
        url,
        request_id
    )
    .execute(&mut *tx)
    .await?;

    if let Some(path_params) = path_params {
        // Empty path params means delete everything since they cannot be toggled
        // and their position is ALWAYS unique
        if path_params.is_empty() {
            sqlx::query!(
                "DELETE FROM request_path_params WHERE request_id = ?",
                request_id
            )
            .execute(&mut *tx)
            .await?;
        } else {
            let mut sql = QueryBuilder::new(
                "INSERT INTO request_path_params(position, request_id, name, value) ",
            );

            sql.push_values(path_params.iter(), |mut b, path| {
                b.push_bind(path.position as i64)
                    .push_bind(request_id)
                    .push_bind(path.name)
                    .push("COALESCE(")
                    .push_bind_unseparated(path.value)
                    .push_unseparated(
                        ", (SELECT value FROM request_path_params WHERE request_id = ",
                    )
                    .push_bind_unseparated(request_id)
                    .push_unseparated("AND position = ")
                    .push_bind_unseparated(path.position as i64)
                    .push_unseparated("), '')");
            });

            // Delete any conflicting positions

            sql.push(
                r#"
                ON CONFLICT(position, request_id) DO UPDATE 
                SET 
                    value = excluded.value,
                    name = excluded.name;

                DELETE FROM request_path_params 
                WHERE request_id = "#,
            )
            .push_bind(request_id)
            .push(" AND position NOT IN (");

            let mut sep = sql.separated(", ");

            for param in path_params.iter() {
                sep.push_bind(param.position as i64);
            }

            sep.push_unseparated(")");

            sql.build().execute(&mut *tx).await?;
        }
    }

    if let Some(query_params) = query_params {
        // Query param updates consider only the enabled QPs since toggling any
        // disabled ones always adds them to the end of the list

        if query_params.is_empty() {
            sqlx::query!(
                "DELETE FROM request_query_params WHERE request_id = ? AND position IS NOT NULL",
                request_id
            )
            .execute(&mut *tx)
            .await?;
        } else {
            let mut sql = QueryBuilder::new(
                "INSERT INTO request_query_params(position, request_id, key, value) ",
            );

            sql.push_values(query_params.iter(), |mut b, qp| {
                b.push_bind(qp.position as i64)
                    .push_bind(request_id)
                    .push_bind(qp.key)
                    .push_bind(qp.value);
            });

            // Query params are unique by position and req_id

            sql.push(
                r#"
                ON CONFLICT(position, request_id) DO UPDATE 
                SET 
                    value = excluded.value,
                    key = excluded.key;

                DELETE FROM request_query_params 
                WHERE request_id = "#,
            )
            .push_bind(request_id)
            .push(" AND position IS NOT NULL AND position NOT IN (");

            let mut sep = sql.separated(", ");

            for param in query_params.iter() {
                sep.push_bind(param.position as i64);
            }

            sep.push_unseparated(")");

            sql.build().execute(&mut *tx).await?;
        }
    }

    tx.commit().await?;

    Ok(())
}

pub async fn update_entry_name(db: SqlitePool, entry_id: i64, name: &str) -> AppResult<()> {
    sqlx::query!(
        "UPDATE workspace_entries SET name = ? WHERE id = ?",
        name,
        entry_id
    )
    .execute(&db)
    .await?;
    Ok(())
}

pub async fn get_workspace_request(db: SqlitePool, id: i64) -> AppResult<WorkspaceRequest> {
    let entry = sqlx::query_as!(
        WorkspaceEntryBase,
        "SELECT id, workspace_id, parent_id, name, type, auth, auth_inherit FROM workspace_entries WHERE id = ?",
        id,
    )
    .fetch_one(&db)
    .await?;

    let params = sqlx::query_as!(
        RequestParams,
        r#"
           SELECT 
            rp.request_id as id,
            method as 'method!',
            url as 'url!',
            rb.ty as "ty: _",
            rb.content AS "content: _",
            rb.id AS "body_id: _"
           FROM request_params rp
           LEFT JOIN request_bodies rb ON rp.request_id = rb.request_id
           WHERE rp.request_id = ? 
        "#,
        id
    )
    .fetch_one(&db)
    .await?;

    let headers = sqlx::query_as!(
        RequestHeader,
        "SELECT id, name, value, enabled FROM request_headers WHERE request_id = ?",
        entry.id
    )
    .fetch_all(&db)
    .await?;

    let (path_params, query_params) = get_request_url_params(&db, id).await?;

    Ok(WorkspaceRequest::from_entry(
        entry,
        params,
        headers,
        path_params,
        query_params,
    ))
}

pub async fn get_workspace_entry(db: SqlitePool, id: i64) -> AppResult<WorkspaceEntry> {
    let entry = sqlx::query_as!(
        WorkspaceEntryBase,
        r#"
        SELECT id, workspace_id, parent_id, name, type, auth, auth_inherit 
        FROM workspace_entries 
        WHERE id = ? 
        LIMIT 1
        "#,
        id,
    )
    .fetch_one(&db)
    .await?;

    match entry.r#type {
        WorkspaceEntryType::Request => Ok(WorkspaceEntry::new_req(
            get_workspace_request(db, id).await?,
        )),
        WorkspaceEntryType::Collection => Ok(WorkspaceEntry::new_col(entry)),
    }
}

pub async fn list_workspace_entries_display(
    db: SqlitePool,
    workspace_id: i64,
) -> AppResult<Vec<WorkspaceEntryDisplay>> {
    let mut sql = QueryBuilder::new(
        r#"
        SELECT e.id, e.workspace_id, e.parent_id, e.name, e.type, e.auth, e.auth_inherit, rp.method
        FROM workspace_entries e
        LEFT JOIN request_params rp ON e.id = rp.request_id
        WHERE e.workspace_id = "#,
    );

    Ok(sql
        .push_bind(workspace_id)
        .push("ORDER BY type DESC")
        .build_query_as()
        .fetch_all(&db)
        .await?)
}

pub async fn list_workspace_entries(
    db: SqlitePool,
    workspace_id: i64,
) -> AppResult<Vec<WorkspaceEntryBase>> {
    Ok(sqlx::query_as!(
        WorkspaceEntryBase,
        "SELECT id, workspace_id, parent_id, name, type, auth, auth_inherit FROM workspace_entries WHERE workspace_id = ? ORDER BY type DESC",
        workspace_id,
    )
    .fetch_all(&db)
    .await?)
}

pub async fn list_environments(
    db: SqlitePool,
    workspace_id: i64,
) -> AppResult<Vec<WorkspaceEnvironment>> {
    let records = sqlx::query!(
        r#"
          SELECT 
            env.workspace_id,
            env.id AS env_id,
            env.name AS env_name,
            var.id AS "var_id?",
            var.name AS "var_name?",
            var.value AS "var_value?",
            var.secret AS "var_secret?"
          FROM workspace_envs env
          LEFT JOIN workspace_env_variables var ON env.id = var.env_id
          WHERE env.workspace_id = $1"#,
        workspace_id
    )
    .fetch_all(&db)
    .await?;

    let mut environments: HashMap<i64, WorkspaceEnvironment> = HashMap::new();

    for record in records {
        if let Some(env) = environments.get_mut(&record.env_id) {
            if record.var_id.is_some() {
                env.variables.push(WorkspaceEnvVariable {
                    id: record.var_id.unwrap(),
                    workspace_id,
                    env_id: record.env_id,
                    name: record.var_name.unwrap(),
                    value: record.var_value.unwrap(),
                    secret: record.var_secret.unwrap(),
                })
            }
        } else {
            let mut env = WorkspaceEnvironment {
                id: record.env_id,
                name: record.env_name,
                workspace_id,
                variables: vec![],
            };
            if record.var_id.is_some() {
                env.variables.push(WorkspaceEnvVariable {
                    id: record.var_id.unwrap(),
                    workspace_id,
                    env_id: record.env_id,
                    name: record.var_name.unwrap(),
                    value: record.var_value.unwrap(),
                    secret: record.var_secret.unwrap(),
                })
            }
            environments.insert(record.env_id, env);
        }
    }

    Ok(environments.into_values().collect())
}

pub async fn get_env_variables(
    db: &SqlitePool,
    env_id: i64,
    names: &[&str],
) -> AppResult<Vec<(String, String)>> {
    let mut query =
        QueryBuilder::new("SELECT name, value FROM workspace_env_variables WHERE env_id = ");

    query.push_bind(env_id);

    let mut separated = query.push(" AND name IN (").separated(", ");

    for name in names {
        separated.push_bind(name);
    }

    separated.push_unseparated(")");

    Ok(query
        .build_query_as::<(String, String)>()
        .fetch_all(db)
        .await?)
}

pub async fn create_environment(
    db: SqlitePool,
    workspace_id: i64,
    name: String,
) -> AppResult<WorkspaceEnvironment> {
    let row = sqlx::query!(
        r#"
        INSERT INTO workspace_envs (workspace_id, name)
        VALUES (?, ?)
        RETURNING id, workspace_id, name
        "#,
        workspace_id,
        name
    )
    .fetch_one(&db)
    .await?;

    Ok(WorkspaceEnvironment {
        id: row.id,
        workspace_id: row.workspace_id,
        name: row.name,
        variables: vec![],
    })
}

pub async fn update_environment(db: SqlitePool, env_id: i64, name: String) -> AppResult<()> {
    sqlx::query!(
        r#"
        UPDATE workspace_envs SET name = ? WHERE id = ?
        "#,
        name,
        env_id,
    )
    .execute(&db)
    .await?;

    Ok(())
}

pub async fn insert_env_var(
    db: SqlitePool,
    workspace_id: i64,
    env_id: i64,
    name: String,
    value: String,
    secret: bool,
) -> AppResult<WorkspaceEnvVariable> {
    Ok(sqlx::query_as!(
        WorkspaceEnvVariable,
        r#"
        INSERT INTO workspace_env_variables (workspace_id, env_id, name, value, secret)
        VALUES (?, ?, ?, ?, ?)
        RETURNING id, workspace_id, env_id, name, value, secret
        "#,
        workspace_id,
        env_id,
        name,
        value,
        secret,
    )
    .fetch_one(&db)
    .await?)
}

pub async fn update_env_var(
    db: SqlitePool,
    id: i64,
    name: Option<String>,
    value: Option<String>,
    secret: Option<bool>,
) -> AppResult<()> {
    sqlx::query_as!(
        WorkspaceEnvVariable,
        r#"
        UPDATE workspace_env_variables
        SET
            name   = COALESCE(?, name),
            value  = COALESCE(?, value),
            secret = COALESCE(?, secret)
        WHERE id = ?
        "#,
        name,
        value,
        secret,
        id,
    )
    .execute(&db)
    .await?;
    Ok(())
}

pub async fn delete_env_var(db: SqlitePool, id: i64) -> AppResult<()> {
    sqlx::query_as!(
        WorkspaceEnvVariable,
        r#"
        DELETE FROM workspace_env_variables
        WHERE id = ?
        "#,
        id,
    )
    .execute(&db)
    .await?;
    Ok(())
}

pub async fn list_request_path_params(db: SqlitePool, id: i64) -> AppResult<Vec<PathParamWrite>> {
    Ok(sqlx::query_as!(
        PathParamWrite,
        "SELECT position, name, value FROM request_path_params WHERE request_id = ?",
        id
    )
    .fetch_all(&db)
    .await?)
}

pub async fn insert_headers(
    db: SqlitePool,
    entry_id: i64,
    headers: Vec<RequestHeaderInsert>,
) -> AppResult<RequestHeader> {
    let mut insert = QueryBuilder::new("INSERT INTO request_headers(request_id, name, value) ");
    insert.push_values(headers, |mut b, header| {
        b.push_bind(entry_id)
            .push_bind(header.name)
            .push_bind(header.value);
    });
    Ok(insert
        .push("RETURNING id, name, value, enabled")
        .build_query_as()
        .fetch_one(&db)
        .await?)
}

pub async fn update_header_enabled(db: SqlitePool, id: i64, enabled: bool) -> AppResult<()> {
    sqlx::query!(
        "UPDATE request_headers SET enabled = ? WHERE id = ?",
        enabled,
        id
    )
    .execute(&db)
    .await?;

    Ok(())
}

pub async fn update_header(db: SqlitePool, header: RequestHeaderUpdate) -> AppResult<()> {
    sqlx::query!(
        "UPDATE request_headers SET name = COALESCE(?, ''), value = COALESCE(?, '') WHERE id = ?",
        header.name,
        header.value,
        header.id
    )
    .execute(&db)
    .await?;
    Ok(())
}

pub async fn delete_header(db: SqlitePool, header_id: i64) -> AppResult<()> {
    sqlx::query!("DELETE FROM request_headers WHERE id = ?", header_id)
        .execute(&db)
        .await?;
    Ok(())
}

pub async fn insert_auth(
    db: SqlitePool,
    workspace_id: i64,
    params: Auth,
) -> AppResult<Authentication> {
    let json = Json(&params);

    let record = sqlx::query!(
        "INSERT INTO auth(workspace_id, name, params) VALUES (?, 'New authentication', ?) RETURNING id, name",
        workspace_id,
        json
    )
    .fetch_one(&db)
    .await?;

    Ok(Authentication {
        id: record.id,
        workspace_id,
        name: record.name,
        params,
    })
}

pub async fn delete_auth(db: SqlitePool, id: i64) -> AppResult<()> {
    sqlx::query!("DELETE FROM auth WHERE id = ?", id)
        .execute(&db)
        .await?;
    Ok(())
}

pub async fn list_auth(db: SqlitePool, workspace_id: i64) -> AppResult<Vec<Authentication>> {
    let records = sqlx::query!(
        r#"
        SELECT id, name, workspace_id, params as "params: Json<Auth>"
        FROM auth
        WHERE workspace_id = ?
        "#,
        workspace_id
    )
    .fetch_all(&db)
    .await?;

    Ok(records
        .into_iter()
        .map(|record| Authentication {
            id: record.id,
            name: record.name,
            workspace_id: record.workspace_id,
            params: record.params.0,
        })
        .collect())
}

pub async fn get_auth(db: SqlitePool, id: i64) -> AppResult<Authentication> {
    let record = sqlx::query!(
        r#"
        SELECT id, workspace_id, name, params as "params: Json<Auth>"
        FROM auth
        WHERE id = ?
        "#,
        id
    )
    .fetch_one(&db)
    .await?;

    Ok(Authentication {
        id: record.id,
        name: record.name,
        workspace_id: record.workspace_id,
        params: record.params.0,
    })
}

pub async fn set_workspace_entry_auth(
    db: SqlitePool,
    entry_id: i64,
    auth_id: Option<i64>,
    inherit: Option<bool>,
) -> AppResult<()> {
    sqlx::query!(
        "UPDATE workspace_entries SET auth = ?, auth_inherit = COALESCE(?, auth_inherit) WHERE id = ?",
        auth_id,
        inherit,
        entry_id
    )
    .execute(&db)
    .await?;

    Ok(())
}

pub async fn update_auth(db: SqlitePool, auth_id: i64, params: Auth) -> AppResult<()> {
    let params = Json(params);

    sqlx::query!("UPDATE auth SET params = ? WHERE id = ?", params, auth_id)
        .execute(&db)
        .await?;

    Ok(())
}

pub async fn rename_auth(db: SqlitePool, auth_id: i64, name: String) -> AppResult<()> {
    sqlx::query!("UPDATE auth SET name = ? WHERE id = ?", name, auth_id)
        .execute(&db)
        .await?;

    Ok(())
}

/// Check for the existence of an auth ID in the workspace entry. If one does not exist,
/// traverse its parents and attempt to find the first one that is present. If none exist,
/// returns `None`.
pub async fn get_auth_inherited(
    db: SqlitePool,
    mut parent_id: Option<i64>,
) -> AppResult<Option<i64>> {
    while let Some(id) = parent_id {
        let record = sqlx::query!(
            "SELECT auth, auth_inherit, parent_id FROM workspace_entries WHERE id = ?",
            id
        )
        .fetch_one(&db)
        .await?;

        if !record.auth_inherit {
            return Ok(record.auth);
        }

        parent_id = record.parent_id;
    }

    Ok(None)
}