fraiseql-db 2.16.0

//! PostgreSQL SQL dialect implementation.

use std::{borrow::Cow, fmt::Write};

use super::trait_def::{RowViewColumnType, SqlDialect, UnsupportedOperator};
use crate::types::sql_hints::ScalarFieldType;

/// PostgreSQL dialect for [`GenericWhereGenerator`].
///
/// [`GenericWhereGenerator`]: crate::where_generator::GenericWhereGenerator
pub struct PostgresDialect;

impl SqlDialect for PostgresDialect {
    fn name(&self) -> &'static str {
        "PostgreSQL"
    }

    fn quote_identifier(&self, name: &str) -> String {
        format!("\"{}\"", name.replace('"', "\"\""))
    }

    fn json_extract_scalar(&self, column: &str, path: &[String]) -> String {
        use crate::path_escape::{escape_postgres_jsonb_path, escape_postgres_jsonb_segment};

        if path.len() == 1 {
            let escaped = escape_postgres_jsonb_segment(&path[0]);
            format!("{column}->>'{escaped}'")
        } else {
            let escaped_path = escape_postgres_jsonb_path(path);
            let mut result = column.to_owned();
            for (i, segment) in escaped_path.iter().enumerate() {
                if i < escaped_path.len() - 1 {
                    write!(result, "->'{segment}'").expect("write to String");
                } else {
                    write!(result, "->>'{segment}'").expect("write to String");
                }
            }
            result
        }
    }

    fn placeholder(&self, n: usize) -> String {
        format!("${n}")
    }

    fn cast_type_name(&self, ty: ScalarFieldType) -> Option<&'static str> {
        match ty {
            ScalarFieldType::Text => None,
            ScalarFieldType::Integer => Some("bigint"),
            ScalarFieldType::Numeric => Some("numeric"),
            ScalarFieldType::Boolean => Some("boolean"),
            ScalarFieldType::DateTime => Some("timestamptz"),
            ScalarFieldType::Date => Some("date"),
            ScalarFieldType::Time => Some("time"),
            // Deliberately uncast — see `ScalarFieldType::Uuid`. A `::uuid` cast
            // on the left-hand side is evaluated per row and would raise 22P02
            // on any row whose identity is not a UUID.
            ScalarFieldType::Uuid => None,
            // Also deliberately uncast — see `ScalarFieldType::Vector`. The
            // variant tells the distance predicates that a native column exists
            // to read (#1117); it is not a cast, and naming one here would
            // change what every OTHER operator on a vector field compares.
            ScalarFieldType::Vector => None,
        }
    }

    fn cast_expr_as<'a>(&self, expr: &'a str, ty: ScalarFieldType) -> Cow<'a, str> {
        match self.cast_type_name(ty) {
            Some(name) => Cow::Owned(format!("({expr})::{name}")),
            None => Cow::Borrowed(expr),
        }
    }

    fn cast_param_as<'a>(&self, placeholder: &'a str, ty: ScalarFieldType) -> Cow<'a, str> {
        match ty {
            // A JSON boolean is bound as `QueryParam::Bool`, which already has
            // the correct binary encoding. Routing it through `text` makes the
            // server resolve the parameter as `text` and then reject the bool
            // encoding with SQLSTATE 22P02.
            ScalarFieldType::Boolean => Cow::Borrowed(placeholder),
            _ => match self.cast_type_name(ty) {
                // Two-step: force the server to resolve the bind as `text`
                // before casting. Every other JSON scalar is bound as
                // `QueryParam::Text`, so a direct `$1::timestamptz` would make
                // the server expect that type's binary encoding.
                Some(name) => Cow::Owned(format!("({placeholder}::text)::{name}")),
                None => Cow::Borrowed(placeholder),
            },
        }
    }

    fn cast_native_param(&self, placeholder: &str, native_type: &str) -> String {
        match native_type.to_lowercase().as_str() {
            // bool uses QueryParam::Bool which encodes correctly in binary — no intermediate text.
            "boolean" | "bool" => format!("{placeholder}::bool"),
            // text/varchar/char(n) — no cast needed.
            "text" | "varchar" | "character varying" | "char" | "bpchar" | "name" => {
                placeholder.to_string()
            },
            // Everything else: two-step cast forces $N to be resolved as text by the
            // server, avoiding binary-encoding mismatches for uuid, timestamps, ints, etc.
            _ => format!("{placeholder}::text::{native_type}"),
        }
    }

    fn ilike_sql(&self, lhs: &str, rhs: &str) -> String {
        format!("{lhs} ILIKE {rhs}")
    }

    fn json_array_length(&self, expr: &str) -> String {
        format!("jsonb_array_length(({expr})::jsonb)")
    }

    fn array_contains_sql(&self, lhs: &str, rhs: &str) -> Result<String, UnsupportedOperator> {
        Ok(format!("({lhs})::jsonb @> {rhs}::jsonb"))
    }

    fn array_contained_by_sql(&self, lhs: &str, rhs: &str) -> Result<String, UnsupportedOperator> {
        Ok(format!("({lhs})::jsonb <@ {rhs}::jsonb"))
    }

    fn array_overlaps_sql(&self, lhs: &str, rhs: &str) -> Result<String, UnsupportedOperator> {
        // PostgreSQL has no `jsonb && jsonb` — `&&` is an array/range operator —
        // so the emitted predicate could never run. `?|` would work but only
        // matches STRING elements (`'[1,2]'::jsonb ?| ARRAY['1']` is false),
        // which would silently under-match numeric arrays. Element-wise
        // intersection is the general form.
        Ok(format!(
            "EXISTS (SELECT 1 FROM jsonb_array_elements(({lhs})::jsonb) AS _l(v) \
             JOIN jsonb_array_elements({rhs}::jsonb) AS _r(v) ON _l.v = _r.v)"
        ))
    }

    fn fts_matches_sql(&self, expr: &str, param: &str) -> Result<String, UnsupportedOperator> {
        Ok(format!("to_tsvector({expr}) @@ to_tsquery({param})"))
    }

    fn fts_plain_query_sql(&self, expr: &str, param: &str) -> Result<String, UnsupportedOperator> {
        Ok(format!("to_tsvector({expr}) @@ plainto_tsquery({param})"))
    }

    fn fts_phrase_query_sql(&self, expr: &str, param: &str) -> Result<String, UnsupportedOperator> {
        Ok(format!("to_tsvector({expr}) @@ phraseto_tsquery({param})"))
    }

    fn fts_websearch_query_sql(
        &self,
        expr: &str,
        param: &str,
    ) -> Result<String, UnsupportedOperator> {
        Ok(format!("to_tsvector({expr}) @@ websearch_to_tsquery({param})"))
    }

    fn fts_rank_sql(
        &self,
        document_expr: &str,
        query_param: &str,
    ) -> Result<String, UnsupportedOperator> {
        Ok(format!(
            "ts_rank(to_tsvector({document_expr}), websearch_to_tsquery({query_param}))"
        ))
    }

    fn regex_sql(
        &self,
        lhs: &str,
        rhs: &str,
        case_insensitive: bool,
        negate: bool,
    ) -> Result<String, UnsupportedOperator> {
        let op = match (case_insensitive, negate) {
            (false, false) => "~",
            (true, false) => "~*",
            (false, true) => "!~",
            (true, true) => "!~*",
        };
        Ok(format!("{lhs} {op} {rhs}"))
    }

    // ── PostgreSQL-only operators ──────────────────────────────────────────────
    // Vector distance emission moved into `GenericWhereGenerator::vector_threshold_sql`
    // (#386): the old `vector_distance_sql`/`jaccard_distance_sql` dialect seam emitted
    // a non-boolean fragment over a mis-parenthesised cast and had no other caller.

    fn inet_check_sql(&self, lhs: &str, check_name: &str) -> Result<String, UnsupportedOperator> {
        match check_name {
            "IsIPv4" => Ok(format!("family(({lhs})::inet) = 4")),
            "IsNotIPv4" => Ok(format!("NOT (family(({lhs})::inet) = 4)")),
            "IsIPv6" => Ok(format!("family(({lhs})::inet) = 6")),
            "IsNotIPv6" => Ok(format!("NOT (family(({lhs})::inet) = 6)")),
            "IsPrivate" => Ok(format!(
                "(({lhs})::inet << '10.0.0.0/8'::inet OR ({lhs})::inet << '172.16.0.0/12'::inet OR ({lhs})::inet << '192.168.0.0/16'::inet OR ({lhs})::inet << 'fc00::/7'::inet)"
            )),
            "IsPublic" => Ok(format!(
                "NOT (({lhs})::inet << '10.0.0.0/8'::inet OR ({lhs})::inet << '172.16.0.0/12'::inet OR ({lhs})::inet << '192.168.0.0/16'::inet OR ({lhs})::inet << 'fc00::/7'::inet)"
            )),
            "IsLoopback" => Ok(format!(
                "(({lhs})::inet << '127.0.0.0/8'::inet OR ({lhs})::inet << '::1/128'::inet)"
            )),
            "IsNotLoopback" => Ok(format!(
                "NOT (({lhs})::inet << '127.0.0.0/8'::inet OR ({lhs})::inet << '::1/128'::inet)"
            )),
            "IsMulticast" => Ok(format!(
                "(({lhs})::inet << '224.0.0.0/4'::inet OR ({lhs})::inet << 'ff00::/8'::inet)"
            )),
            "IsNotMulticast" => Ok(format!(
                "NOT (({lhs})::inet << '224.0.0.0/4'::inet OR ({lhs})::inet << 'ff00::/8'::inet)"
            )),
            "IsLinkLocal" => Ok(format!(
                "(({lhs})::inet << '169.254.0.0/16'::inet OR ({lhs})::inet << 'fe80::/10'::inet)"
            )),
            "IsNotLinkLocal" => Ok(format!(
                "NOT (({lhs})::inet << '169.254.0.0/16'::inet OR ({lhs})::inet << 'fe80::/10'::inet)"
            )),
            "IsDocumentation" => Ok(format!(
                "(({lhs})::inet << '192.0.2.0/24'::inet OR ({lhs})::inet << '198.51.100.0/24'::inet OR ({lhs})::inet << '203.0.113.0/24'::inet OR ({lhs})::inet << '2001:db8::/32'::inet)"
            )),
            "IsNotDocumentation" => Ok(format!(
                "NOT (({lhs})::inet << '192.0.2.0/24'::inet OR ({lhs})::inet << '198.51.100.0/24'::inet OR ({lhs})::inet << '203.0.113.0/24'::inet OR ({lhs})::inet << '2001:db8::/32'::inet)"
            )),
            "IsCarrierGrade" => Ok(format!("(({lhs})::inet << '100.64.0.0/10'::inet)")),
            "IsNotCarrierGrade" => Ok(format!("NOT (({lhs})::inet << '100.64.0.0/10'::inet)")),
            _ => Err(UnsupportedOperator {
                dialect:  self.name(),
                operator: "InetCheck",
            }),
        }
    }

    fn inet_binary_sql(
        &self,
        pg_op: &str,
        lhs: &str,
        rhs: &str,
    ) -> Result<String, UnsupportedOperator> {
        Ok(format!("({lhs})::inet {pg_op} {rhs}::text::inet"))
    }

    fn ltree_binary_sql(
        &self,
        pg_op: &str,
        lhs: &str,
        rhs: &str,
        rhs_type: &str,
    ) -> Result<String, UnsupportedOperator> {
        Ok(format!("({lhs})::ltree {pg_op} {rhs}::{rhs_type}"))
    }

    fn ltree_any_lquery_sql(
        &self,
        lhs: &str,
        placeholders: &[String],
    ) -> Result<String, UnsupportedOperator> {
        Ok(format!("({lhs})::ltree ? ARRAY[{}]", placeholders.join(", ")))
    }

    fn ltree_depth_sql(
        &self,
        op: &str,
        lhs: &str,
        rhs: &str,
    ) -> Result<String, UnsupportedOperator> {
        // `{rhs}::text::int`, not a bare `{rhs}`: `nlevel()` returns integer, so a
        // bare placeholder is inferred as int4, and this crate's parameter
        // convention sends JSON numbers as text (see `QueryParam::from`). The
        // mismatch surfaces as the wire error "insufficient data left in
        // message". The double cast pins the parameter as text and converts
        // server-side, as `$n::text::vector` does for pgvector.
        Ok(format!("nlevel(({lhs})::ltree) {op} {rhs}::text::int"))
    }

    fn ltree_lca_sql(
        &self,
        lhs: &str,
        placeholders: &[String],
    ) -> Result<String, UnsupportedOperator> {
        Ok(format!("({lhs})::ltree = lca(ARRAY[{}])", placeholders.join(", ")))
    }

    fn ltree_id_subquery_sql(
        &self,
        pg_op: &str,
        field_expr: &str,
        table: &str,
        path_column: &str,
        fk_column: Option<&str>,
        param: &str,
    ) -> Result<String, UnsupportedOperator> {
        // A configured table may be schema-qualified (`app.tb_node`); quoting it as one
        // identifier named a relation that does not exist (#1396).
        let qt = crate::quote_postgres_identifier(table);
        let qp = self.quote_identifier(path_column);
        let qi = self.quote_identifier("id");
        let path_subquery = format!("SELECT {qp} FROM {qt} WHERE {qi} = {param}");

        if let Some(fk) = fk_column {
            // Cross-table: fk IN (SELECT id FROM t WHERE path <op> (subquery))
            let qfk = self.quote_identifier(fk);
            Ok(format!("{qfk} IN (SELECT {qi} FROM {qt} WHERE {qp} {pg_op} ({path_subquery}))"))
        } else {
            // Self-referencing: field_expr <op> (SELECT path FROM t WHERE id = $N)
            Ok(format!("({field_expr})::ltree {pg_op} ({path_subquery})"))
        }
    }

    fn row_view_column_expr(
        &self,
        json_column: &str,
        field_name: &str,
        col_type: &RowViewColumnType,
    ) -> String {
        let pg_type = match col_type {
            RowViewColumnType::Text => "text",
            RowViewColumnType::Int32 => "int",
            RowViewColumnType::Int64 => "bigint",
            RowViewColumnType::Float64 => "double precision",
            RowViewColumnType::Boolean => "boolean",
            RowViewColumnType::Uuid => "uuid",
            RowViewColumnType::Timestamptz => "timestamptz",
            RowViewColumnType::Date => "date",
            RowViewColumnType::Json => "jsonb",
        };
        format!("({json_column}->>'{field_name}')::{pg_type}")
    }
}