mae 0.3.9

//! Query builder infrastructure for the `mae` repository layer.
//!
//! This module provides the trait hierarchy that powers type-safe SQL query construction
//! generated by the `#[schema]` macro. Any struct annotated with `#[schema]` automatically
//! gets `InsertRow`, `UpdateRow`, `Field`, and `PatchField` types, which slot into the
//! five type parameters of [`Build`].
//!
//! ## Five type parameters — why?
//!
//! The original builder had four params (`C`, `I`, `U`, `F`). The fifth, `P` ([`ToPatch`]),
//! was added to support **partial updates** via [`Interface::patch`]. Unlike
//! [`Interface::update_many`] — which generates a full `UPDATE` that overwrites every
//! non-locked column and requires all fields to be present — `patch` only touches the
//! columns you explicitly specify. This distinction is important: calling `update_many`
//! with a partially-filled `UpdateRow` is an error; `patch` is the safe alternative for
//! partial mutations.
//!
//! ## Trait hierarchy
//!
//! ```text
//! Build<C,I,U,F,P>        — core: produces a Builder + knows the schema name
//!   └── Interface<…>      — blanket impl; exposes insert_one / select / update_many / patch
//!         └── Builder<…>  — holds statement + filters; implements ToSql + Execute
//! ```

use anyhow::{Ok, Result, anyhow};
use num::Zero;
use std::fmt::Debug;
use std::marker::PhantomData;

use sqlx::{Arguments, Executor, Postgres};

use crate::repo::filter::Filter;
use crate::request_context::ContextAccessor;

use super::map_util::{BindArgs, FilterOp, SqlStatement, concat_sql_parts, sql_where};
use super::type_def::{Context, QueryAs, ToField, ToInsertRow, ToPatch, ToUpdateRow};

// /////
// INTERFACE TO THE SCHEMA def
//  ////

/// Builder trait for type-safe SQL query construction.
///
/// Implement this on a domain struct (usually via `#[schema]`) to gain access to the
/// full [`Interface`] API. The five type parameters map directly to the types the macro
/// generates:
///
/// - `C` — Request context (implements [`ContextAccessor`] for DB pool / session access)
/// - `I` — Insert row type (generated as `InsertRow`; contains all writable fields)
/// - `U` — Update row type (generated as `UpdateRow`; replaces ALL non-locked fields)
/// - `F` — Field enum (generated as `Field`; drives SELECT column lists and WHERE clauses)
/// - `P` — Patch type (generated as `PatchField`; updates ONLY the supplied fields —
///   prefer this over `update_many` for partial mutations)
pub trait Build<C: Context, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch>:
    QueryAs + KeyAuths<F>
{
    /// Construct a [`Builder`] pre-loaded with the given SQL statement variant.
    ///
    /// The builder is seeded with the schema-level key-auth filters returned by
    /// [`KeyAuths::keys`], so callers only need to add any additional WHERE conditions
    /// via [`Builder::filter`].
    fn build(ctx: &C, statement: SqlStatement<I, U, F, P>) -> Builder<'_, C, Self, I, U, F, P> {
        Builder::<C, Self, I, U, F, P> {
            statement,
            filters: Self::keys(),
            schema: Self::schema(),
            ctx,
            query_as: PhantomData,
            returning: None
        }
    }
    /// Return the fully-qualified SQL schema/table name for this domain type
    /// (e.g. `"public.users"`). Implemented automatically by `#[schema]`.
    fn schema() -> String;
}

/// Provides the row-level authorization key filters for a domain type.
///
/// `keys()` returns the base [`FilterOp`] set that is always appended to every query
/// produced by this domain's builder — typically a `sys_client` equality filter that
/// scopes all DB access to the current tenant. The `#[schema]` macro generates a
/// default (empty) implementation; override it to enforce row-level auth.
pub trait KeyAuths<F: ToField> {
    fn keys() -> Vec<FilterOp<F>>;
}

/// High-level CRUD API for domain structs.
///
/// Automatically implemented for any type that implements [`Build`]. Callers should
/// use these methods instead of constructing [`Builder`] instances directly.
///
/// ## Choosing `update_many` vs `patch`
///
/// | Method | SQL variant | Behaviour |
/// |---|---|---|
/// | `update_many` | `SqlStatement::Update` | Writes **all** `UpdateRow` fields; errors if every field is `None` |
/// | `patch` | `SqlStatement::Patch` | Writes **only** the supplied `PatchField` variants; safe for partial edits |
///
/// Both require at least one `.filter(…)` before execution to prevent accidental full-table
/// updates. Attempting to execute either without a filter returns an error.
// TODO: the recs / rec needs to be borrowed -- this brings in lifetimes
pub trait Interface<C: Context, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch>:
    Build<C, I, U, F, P>
{
    /// Build an `INSERT INTO … VALUES (…) RETURNING *` statement for a single row.
    ///
    /// `created_by` is automatically appended from the session user — callers do not
    /// need to set it on `rec`.
    fn insert_one(ctx: &C, rec: I) -> Builder<'_, C, Self, I, U, F, P> {
        Self::build(ctx, SqlStatement::<I, U, F, P>::InsertOne(rec))
    }

    /// Build an `INSERT INTO … VALUES …` statement for multiple rows.
    ///
    /// **Not yet implemented** — calling `.fetch_all()` on the returned builder will
    /// panic at runtime until bulk-insert support is added.
    fn insert_many(ctx: &C, recs: Vec<I>) -> Builder<'_, C, Self, I, U, F, P> {
        Self::build(ctx, SqlStatement::<I, U, F, P>::InsertMany(recs))
    }

    /// Build a `SELECT <fields> FROM <schema> [WHERE …]` statement.
    ///
    /// Pass `vec![Field::All]` to select every column, or enumerate specific [`ToField`]
    /// variants to project only those columns. Add WHERE conditions via `.filter(…)`.
    fn select(ctx: &C, rec: Vec<F>) -> Builder<'_, C, Self, I, U, F, P> {
        Self::build(ctx, SqlStatement::<I, U, F, P>::Select(rec))
    }

    /// Build a full-row `UPDATE … SET … FROM (VALUES …) RETURNING *` statement.
    ///
    /// Uses [`SqlStatement::Update`], which sets **every** field in `UpdateRow`.
    /// Requires at least one `.filter(…)` call before execution and will error if
    /// all `Option` fields in `rec` are `None`.
    ///
    /// Prefer [`patch`](Self::patch) when only a subset of fields should change.
    fn update_many(ctx: &C, rec: U) -> Builder<'_, C, Self, I, U, F, P> {
        Self::build(ctx, SqlStatement::Update(rec))
    }

    /// Build a partial `UPDATE … SET … FROM (VALUES …) RETURNING *` statement.
    ///
    /// Uses [`SqlStatement::Patch`], which generates SQL only for the [`ToPatch`]
    /// variants present in `recs`. This is the preferred update path when you need
    /// to change a few fields without touching the rest — in contrast to
    /// [`update_many`](Self::update_many), which always writes every column.
    ///
    /// Requires at least one `.filter(…)` call and at least one element in `recs`.
    fn patch(ctx: &C, recs: Vec<P>) -> Builder<'_, C, Self, I, U, F, P> {
        Self::build(ctx, SqlStatement::Patch(recs))
    }
}

// Blanket impl: every type that satisfies Build automatically gets Interface for free.
// This means domain structs never need to manually implement Interface.
impl<C: Context, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch, B: Build<C, I, U, F, P>>
    Interface<C, I, U, F, P> for B
{
}

/// Holds the accumulated state for a single pending SQL operation.
///
/// Created by [`Build::build`] (or the [`Interface`] helpers) and consumed by the
/// [`Execute`] methods (`fetch_all`, `fetch_one`, etc.). Chain [`Builder::filter`] and
/// [`Builder::returning`] before executing.
pub struct Builder<
    'a,
    C: Context,
    A: QueryAs,
    I: ToInsertRow,
    U: ToUpdateRow,
    F: ToField,
    P: ToPatch
> {
    /// The SQL operation variant (SELECT, INSERT, UPDATE, PATCH) with its payload.
    statement: SqlStatement<I, U, F, P>,
    /// Accumulated WHERE filters; seeded from [`KeyAuths::keys`] and extended by callers.
    filters: Vec<FilterOp<F>>,
    /// Fully-qualified table/schema name (e.g. `"public.my_table"`).
    schema: String,
    ctx: &'a C,
    query_as: PhantomData<fn() -> A>,
    returning: Option<Vec<F>>
}

impl<C: Context, A: QueryAs, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch>
    Builder<'_, C, A, I, U, F, P>
{
    /// Append additional WHERE filters to this builder.
    ///
    /// Filters are combined with the schema-level key-auth filters already present.
    /// Call this before any `fetch_*` / `execute` method.
    pub fn filter(mut self, mut values: Vec<FilterOp<F>>) -> Self {
        self.filters.append(&mut values);
        self
    }
    /// Override the default `RETURNING *` clause with a specific column list.
    pub fn returning(mut self, values: Vec<F>) -> Self {
        self.returning = Some(values);
        self
    }
}

impl<C: Context, A: QueryAs, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch> ContextAccessor
    for Builder<'_, C, A, I, U, F, P>
{
    fn db_pool(&self) -> &sqlx::PgPool {
        self.ctx.db_pool()
    }
    fn session(&self) -> &crate::session::Session {
        self.ctx.session()
    }
    fn session_user(&self) -> &i32 {
        self.ctx.session_user()
    }
}

impl<C: Context, A: QueryAs, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch>
    ToSql<I, U, F, P> for Builder<'_, C, A, I, U, F, P>
{
    fn statement(&self) -> &SqlStatement<I, U, F, P> {
        &self.statement
    }
    fn schema(&self) -> &String {
        &self.schema
    }
    fn filters(&self) -> &Vec<FilterOp<F>> {
        &self.filters
    }
}

impl<C: Context, A: QueryAs, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch>
    Execute<C, A, I, U, F, P> for Builder<'_, C, A, I, U, F, P>
where
    Self: ToSql<I, U, F, P>
{
}

/// Renders the accumulated builder state into a SQL string and bound arguments.
///
/// Separating SQL generation (`to_sql`) from execution ([`Execute`]) keeps the builder
/// testable: call `to_sql()` to inspect the generated query without hitting the DB.
pub trait ToSql<I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch> {
    fn statement(&self) -> &SqlStatement<I, U, F, P>;
    fn filters(&self) -> &Vec<FilterOp<F>>;
    fn schema(&self) -> &String;
    fn to_sql(&self) -> Result<String> {
        Ok(match &self.statement() {
            SqlStatement::Select(field_blocks) => {
                let where_str = sql_where(self.filters(), self.statement().bind_len(), None);
                let fields = field_blocks
                    .iter()
                    .map(|field| -> Result<String> {
                        let (mut str_value, _) = field.to_sql_parts();
                        str_value.pop().ok_or_else(|| anyhow!("cannot find binding index"))
                    })
                    .collect::<Result<Vec<_>>>()?
                    .join(",\n\t");
                format!("SELECT\n\t{}\nFROM {}{};", &fields, self.schema(), where_str,)
            }
            SqlStatement::InsertOne(row) => {
                let (mut fields, bind_idx_option) = row.to_sql_parts();
                let mut bind_idx =
                    bind_idx_option.ok_or_else(|| anyhow!("cannot find binding index"))?;
                // Append `created_by` automatically — it is always sourced from the session
                // user and must not be set by the caller.
                fields.push("created_by".into());
                let last_idx = bind_idx.len();
                bind_idx.push(format!("${}", last_idx + 1));
                let fields_str = fields.join(",\n\t ");
                let bind_idx_str = bind_idx.join(", ");
                format!(
                    "INSERT INTO {}\n\t(\n\t {}\n\t)\n\tVALUES ({})\nRETURNING *;",
                    self.schema(),
                    fields_str,
                    bind_idx_str,
                )
            }
            SqlStatement::InsertMany(_) => {
                unimplemented!();
            }
            // SqlStatement::Update: full-row update — every column in UpdateRow is written.
            // Uses a VALUES CTE (_z_) to bind all fields as positional params safely.
            // The WHERE clause targets the original table alias (_x_) via sql_where.
            SqlStatement::Update(row) => {
                let where_str =
                    sql_where(self.filters(), self.statement().bind_len() + 1, Some("_x_".into()));
                let (mut fields, bind_idx_option) = row.to_sql_parts();
                let mut bind_idx =
                    bind_idx_option.ok_or_else(|| anyhow!("cannot find binding index"))?;
                // Append `updated_by` automatically — sourced from session user.
                fields.push("updated_by".into());
                let last_idx = bind_idx.len();
                bind_idx.push(format!("${}", last_idx + 1));
                let f = fields.join(",\n\t\t ");
                let f_f = fields
                    .into_iter()
                    .map(|f| format!("\n\t\t{f} = _z_.{f}"))
                    .collect::<Vec<_>>()
                    .join(", ");
                let v = bind_idx.join(", ");
                let schema = self.schema();
                format!(
                    "UPDATE {schema} _x_\n\tSET {f_f}\n\tFROM\n\t\t(VALUES ({v}))\n\tAS _z_ (\n\t\t {f}\n\t\t){where_str}\nRETURNING *;"
                )
            }
            // SqlStatement::Patch: PARTIAL update — only the PatchField variants supplied
            // by the caller are included in the SET clause. This is the key difference from
            // SqlStatement::Update: callers specify exactly which columns change rather than
            // providing every column. Use patch() when you want to update 1–N fields without
            // overwriting the rest of the row.
            SqlStatement::Patch(fields) => {
                let where_str =
                    sql_where(self.filters(), self.statement().bind_len() + 1, Some("_x_".into()));
                let (mut fields, _) =
                    concat_sql_parts(fields.iter().map(|f| f.to_sql_parts()).collect::<Vec<_>>());
                let mut bind_idx =
                    (0..fields.len()).map(|i| format!("${:?}", i + 1)).collect::<Vec<_>>();
                // Append `updated_by` — always sourced from session user, not from PatchField.
                fields.push("updated_by".into());
                let last_idx = bind_idx.len();
                bind_idx.push(format!("${}", last_idx + 1));
                let f = fields.join(",\n\t\t ");
                let f_f = fields
                    .into_iter()
                    .map(|f| format!("\n\t\t{f} = _z_.{f}"))
                    .collect::<Vec<_>>()
                    .join(", ");
                let v = bind_idx.join(", ");
                let schema = self.schema();
                format!(
                    "UPDATE {schema} _x_\n\tSET {f_f}\n\tFROM\n\t\t(VALUES ({v}))\n\tAS _z_ (\n\t\t {f}\n\t\t){where_str}\nRETURNING *;"
                )
            }
        })
    }
    fn args(&self, session_user: &i32) -> sqlx::postgres::PgArguments {
        let mut args = sqlx::postgres::PgArguments::default();
        match self.statement() {
            SqlStatement::Select(_) => {}
            _ => {
                self.statement().bind(&mut args);
                // Always bind session_user last for INSERT (created_by) and UPDATE/PATCH
                // (updated_by). The placeholder is the final positional param in to_sql().
                let _ = args.add(session_user);
            }
        };
        for w in self.filters().iter() {
            w.bind(&mut args);
        }
        args
    }
}

/// Async execution layer for a built query.
///
/// Implemented automatically for any [`Builder`] that also implements [`ToSql`].
/// The `fetch_all` method is the primary entry point for most callers; `execute` is
/// for fire-and-forget mutations that don't need returned rows.
pub trait Execute<C: Context, A: QueryAs, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch>:
    ToSql<I, U, F, P> + ContextAccessor
{
    /// Execute the statement without returning rows (e.g. a side-effect-only mutation).
    fn execute(&self, _ctx: &C) -> impl std::future::Future<Output = anyhow::Result<()>> + Send
    where
        Self: Sync
    {
        async { todo!() }
    }
    fn fetch_optional(
        &self,
        _ctx: &C
    ) -> impl std::future::Future<Output = anyhow::Result<Option<A>>> + Send
    where
        Self: Sync
    {
        async { todo!() }
    }
    /// Execute and return exactly one row, erroring if zero or more than one row is returned.
    fn fetch_one(&self, _ctx: &C) -> impl std::future::Future<Output = anyhow::Result<A>> + Send
    where
        Self: Sync
    {
        async { todo!() }
    }
    /// Execute and return all matching rows.
    ///
    /// Runs [`authenticate_request`](Self::authenticate_request) first — this guards against
    /// Update/Patch without filters and SELECT with the wrong field count.
    ///
    /// Pass a `&mut Transaction` to participate in the caller's transaction, or the pool
    /// directly for auto-commit behaviour.
    fn fetch_all<'c>(
        &self,
        exec: impl Executor<'c, Database = Postgres>
    ) -> impl std::future::Future<Output = anyhow::Result<Vec<A>>> + Send
    where
        Self: Sync + Send
    {
        async move {
            self.authenticate_request()?;
            let sql = self.to_sql()?;
            let req = sqlx::query_as_with::<'_, sqlx::Postgres, A, sqlx::postgres::PgArguments>(
                &sql,
                self.args(self.session_user())
            );
            let res: anyhow::Result<Vec<A>> = req
                .fetch_all(exec)
                .await
                .map_err(|e| anyhow::anyhow!("Unable to fetch all: {}", e));
            res
        }
    }
    /// Validate the request before hitting the DB.
    ///
    /// - `Update` / `Patch`: requires at least one filter (prevents full-table mutations)
    ///   and at least one non-empty field (prevents no-op updates).
    /// - `Select`: requires exactly one field-block when using `fetch_all`; use
    ///   `fetch_all_raw` for multi-column projections.
    fn authenticate_request(&self) -> Result<()> {
        match self.statement() {
            SqlStatement::Update(_) | SqlStatement::Patch(_) => {
                if self.filters().is_empty() {
                    return Err(anyhow!("Unable to Update/Patch without filters"));
                }
                if self.statement().bind_len() < 1 {
                    return Err(anyhow!("Unable to Update/Patch with all fields empty"));
                }
                Ok(())
            }
            SqlStatement::Select(field_blocks) => {
                if field_blocks.len() != 1 {
                    return Err(anyhow!(
                        "Unable to use the fetch_all method while choosing which fields to return. Use the fetch_all_raw() method."
                    ));
                }
                Ok(())
            }
            _ => Ok(())
        }
    }
}

impl<C: Context, A: QueryAs, I: ToInsertRow, U: ToUpdateRow, F: ToField, P: ToPatch> Debug
    for Builder<'_, C, A, I, U, F, P>
where
    Self: ToSql<I, U, F, P>
{
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        write!(f, "\n{}\n\tSQL\n{}\n\n", "*".repeat(18), "*".repeat(18),)?;
        write!(f, "{}", self.to_sql().map_err(|_| std::fmt::Error)?)?;
        let mut bind_len = self.statement.bind_len();
        let mut _has_bindings = false;
        if !bind_len.is_zero() {
            _has_bindings = true;
            write!(f, "\n\n{}\n{}BINDINGS\n{}\n", "*".repeat(18), " ".repeat(5), "*".repeat(18),)?;
        }
        if !bind_len.is_zero() {
            self.statement.fmt(f)?;
        }
        match self.statement {
            SqlStatement::Select(_) => writeln!(f),
            _ => {
                bind_len += 1;
                return write!(f, "\n\t${} = [session_user]\n", bind_len);
            }
        }?;

        let mut filter_has_bindings = false;
        let mut filter_bindings_string = String::from("");

        for (i, filter) in self.filters.iter().enumerate() {
            match filter {
                FilterOp::And(_c, v) | FilterOp::Or(_c, v) | FilterOp::Begin(_c, v) => match v {
                    Filter::IsNull => {}
                    _ => {
                        _has_bindings = true;
                        filter_has_bindings = true;
                        filter_bindings_string.push_str(&format!(
                            "\n\t${} = {:?}",
                            i + bind_len + 1,
                            &filter
                        ));
                    }
                }
            }
        }
        if filter_has_bindings {
            if bind_len.is_zero() {
                write!(
                    f,
                    "\n\n{}\n{}BINDINGS\n{}\n",
                    "*".repeat(18),
                    " ".repeat(5),
                    "*".repeat(18),
                )?;
            }
            write!(f, "{}", filter_bindings_string)?;
            writeln!(f)?;
        }

        if bind_len.is_zero() {
            writeln!(f)?;
        }

        write!(f, "\n{}", "*".repeat(18))?;
        write!(f, "\n{}\n", "*".repeat(18))?;
        std::fmt::Result::Ok(())
    }
}