foundry-rs 0.6.21

//! PostgreSQL dialect: maps canonical types to DDL strings, SQL cast names, and type categories.
//!
//! Free functions are kept for zero-overhead internal use. `PostgresDialect` implements the
//! `Dialect` trait for use via `Arc<dyn Dialect>` in AppState.

use super::dialect::{escape_literal, Dialect};
use super::types::{CanonicalType, TypeCategory, TypeSupport};

/// Zero-sized marker for the PostgreSQL dialect.
pub struct PostgresDialect;

// ─── DDL type ────────────────────────────────────────────────────────────────

/// Return the PostgreSQL DDL type string for use in `CREATE TABLE`.
///
/// Called once per column during migration — not on the hot path.
pub fn ddl_type(t: &CanonicalType) -> String {
    match t {
        CanonicalType::Text => "TEXT".to_string(),
        CanonicalType::Varchar(Some(n)) => format!("VARCHAR({})", n),
        CanonicalType::Varchar(None) => "TEXT".to_string(),
        CanonicalType::Char(Some(n)) => format!("CHAR({})", n),
        CanonicalType::Char(None) => "CHAR(1)".to_string(),
        CanonicalType::SmallInt => "SMALLINT".to_string(),
        CanonicalType::Int => "INTEGER".to_string(),
        CanonicalType::BigInt => "BIGINT".to_string(),
        CanonicalType::Real => "REAL".to_string(),
        CanonicalType::Double => "DOUBLE PRECISION".to_string(),
        CanonicalType::Decimal(Some((p, s))) => format!("NUMERIC({}, {})", p, s),
        CanonicalType::Decimal(None) => "NUMERIC".to_string(),
        CanonicalType::Boolean => "BOOLEAN".to_string(),
        CanonicalType::Uuid => "UUID".to_string(),
        // Prefer JSONB over JSON in Postgres: binary storage, indexable.
        CanonicalType::Json => "JSONB".to_string(),
        CanonicalType::Jsonb => "JSONB".to_string(),
        CanonicalType::Timestamp => "TIMESTAMPTZ".to_string(),
        CanonicalType::TimestampNtz => "TIMESTAMP".to_string(),
        CanonicalType::Date => "DATE".to_string(),
        CanonicalType::Time => "TIME".to_string(),
        CanonicalType::Timetz => "TIMETZ".to_string(),
        CanonicalType::Bytes => "BYTEA".to_string(),
        CanonicalType::Serial => "SERIAL".to_string(),
        CanonicalType::BigSerial => "BIGSERIAL".to_string(),
        CanonicalType::Asset => "TEXT".to_string(),
        CanonicalType::AssetArray => "JSONB".to_string(),
        CanonicalType::Array(inner) => format!("{}[]", ddl_type(inner)),
        CanonicalType::Custom(s) => s.clone(),
    }
}

// ─── Cast name ───────────────────────────────────────────────────────────────

/// Return the Postgres type name used in `$n::type` parameter casts, or `None` when no cast
/// is needed (i.e. text/varchar/char columns, which match the TEXT OID we use for all params).
///
/// Because all `BindValue` parameters are sent with TEXT OID (25), PostgreSQL requires an
/// explicit SQL cast (`$n::integer`, `$n::bigint`, etc.) for every non-text column type.
/// Without it, PostgreSQL rejects the query with "expression is of type text" errors.
///
/// This drives `ColumnInfo.pg_type` in the resolved model and is used throughout the query
/// builder whenever a value needs an explicit type annotation in SQL.
pub fn cast_name(t: &CanonicalType) -> Option<String> {
    let s = match t {
        // Numeric types: must be cast since parameters arrive as TEXT.
        // Serial/BigSerial differ from Int/BigInt only in their DDL default (the sequence);
        // in value comparisons (WHERE/IN/=) they are plain integer/bigint and need the same cast.
        CanonicalType::SmallInt => "smallint",
        CanonicalType::Int | CanonicalType::Serial => "integer",
        CanonicalType::BigInt | CanonicalType::BigSerial => "bigint",
        CanonicalType::Real => "real",
        CanonicalType::Double => "double precision",
        CanonicalType::Decimal(_) => "numeric",
        // Other non-text types that need explicit casts.
        CanonicalType::Boolean => "boolean",
        CanonicalType::Uuid => "uuid",
        CanonicalType::Json | CanonicalType::Jsonb | CanonicalType::AssetArray => "jsonb",
        CanonicalType::Timestamp => "timestamptz",
        CanonicalType::TimestampNtz => "timestamp",
        CanonicalType::Date => "date",
        CanonicalType::Time => "time",
        CanonicalType::Timetz => "timetz",
        CanonicalType::Bytes => "bytea",
        // Arrays: cast to the element type followed by [].
        CanonicalType::Array(inner) => {
            let inner_cast = cast_name(inner).unwrap_or_else(|| ddl_type(inner).to_lowercase());
            return Some(format!("{}[]", inner_cast));
        }
        // Schema-qualified custom types (enums like schema.my_enum) cast directly to the type name.
        CanonicalType::Custom(s) if s.contains('.') => {
            return Some(s.clone());
        }
        CanonicalType::Custom(_) => return None,
        // Text-compatible types (TEXT, VARCHAR, CHAR, ASSET) bind fine
        // as-is because the parameter OID is already TEXT.
        _ => return None,
    };
    Some(s.to_string())
}

// ─── Type support ─────────────────────────────────────────────────────────────

/// Describe how well Postgres supports a canonical type.
/// Callers log a warning for [`TypeSupport::Degraded`] at startup.
pub fn type_support(t: &CanonicalType) -> TypeSupport {
    match t {
        CanonicalType::Text
        | CanonicalType::Varchar(_)
        | CanonicalType::Char(_)
        | CanonicalType::SmallInt
        | CanonicalType::Int
        | CanonicalType::BigInt
        | CanonicalType::Real
        | CanonicalType::Double
        | CanonicalType::Decimal(_)
        | CanonicalType::Boolean
        | CanonicalType::Uuid
        | CanonicalType::Jsonb
        | CanonicalType::Timestamp
        | CanonicalType::TimestampNtz
        | CanonicalType::Date
        | CanonicalType::Time
        | CanonicalType::Timetz
        | CanonicalType::Bytes
        | CanonicalType::Serial
        | CanonicalType::BigSerial
        | CanonicalType::Array(_)
        | CanonicalType::Custom(_) => TypeSupport::Native(ddl_type(t).leak()),
        // Json is stored as JSONB — same semantics, richer storage.
        CanonicalType::Json => TypeSupport::Native("JSONB"),
        // SDK pseudo-types are emulated via TEXT / JSONB.
        CanonicalType::Asset => TypeSupport::Emulated("TEXT"),
        CanonicalType::AssetArray => TypeSupport::Emulated("JSONB"),
    }
}

// ─── Type category ────────────────────────────────────────────────────────────

/// Delegates to the shared impl in [`super::types`].
pub fn type_category(t: &CanonicalType) -> TypeCategory {
    super::types::type_category(t)
}

/// Delegates to the shared impl in [`super::types`].
pub fn type_category_from_cast(cast: &str) -> TypeCategory {
    super::types::type_category_from_cast(cast)
}

// ─── Dialect impl ─────────────────────────────────────────────────────────────

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

    fn ddl_type(&self, t: &CanonicalType) -> String {
        ddl_type(t)
    }

    fn cast_name(&self, t: &CanonicalType) -> Option<String> {
        cast_name(t)
    }

    fn type_category(&self, t: &CanonicalType) -> TypeCategory {
        type_category(t)
    }

    fn type_support(&self, t: &CanonicalType) -> TypeSupport {
        type_support(t)
    }

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

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

    fn cast_expr(&self, placeholder: &str, cast: &str) -> String {
        format!("{}::{}", placeholder, cast)
    }

    fn now_fn(&self) -> &'static str {
        "NOW()"
    }

    fn uuid_default_expr(&self) -> &'static str {
        "gen_random_uuid()"
    }

    fn returning_clause(&self, cols: &str) -> String {
        format!("RETURNING {}", cols)
    }

    fn upsert_conflict(&self, conflict_cols: &[&str], set_pairs: &str) -> String {
        let cols = conflict_cols
            .iter()
            .map(|c| format!("\"{}\"", c.replace('"', "\"\"")))
            .collect::<Vec<_>>()
            .join(", ");
        format!("ON CONFLICT ({}) DO UPDATE SET {}", cols, set_pairs)
    }

    fn to_one_subquery(&self, col_exprs: &[String], from_clause: &str) -> String {
        let cols = col_exprs.join(", ");
        format!(
            "(SELECT row_to_json(sub) FROM (SELECT {} FROM {}) sub)",
            cols, from_clause
        )
    }

    fn to_many_subquery(&self, col_exprs: &[String], from_clause: &str) -> String {
        let cols = col_exprs.join(", ");
        format!(
            "(SELECT COALESCE(json_agg(row_to_json(sub)), '[]'::json) FROM (SELECT {} FROM {}) sub)",
            cols, from_clause
        )
    }

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

    fn json_extract_typed(&self, col: &str, key: &str, t: &CanonicalType) -> String {
        let base = self.json_extract_text(col, key);
        match cast_name(t) {
            Some(cast) => format!("{}::{}", base, cast),
            None => base,
        }
    }

    fn case_insensitive_like(&self, col: &str, placeholder: &str) -> String {
        format!("{} ILIKE {}", col, placeholder)
    }

    fn sys_json_type(&self) -> &'static str {
        "JSONB"
    }

    fn sys_timestamp_type(&self) -> &'static str {
        "TIMESTAMPTZ"
    }

    fn sys_bigserial_type(&self) -> &'static str {
        "BIGSERIAL"
    }

    fn sys_bytes_type(&self) -> &'static str {
        "BYTEA"
    }

    fn audit_timestamp_type(&self) -> &'static str {
        "TIMESTAMPTZ"
    }

    fn supports_rls(&self) -> bool {
        true
    }

    fn supports_named_enum_types(&self) -> bool {
        true
    }

    fn supports_index_include(&self) -> bool {
        true
    }

    fn set_tenant_session_sql(&self, tenant_id: &str) -> Option<String> {
        Some(format!(
            "SET LOCAL app.tenant_id = '{}'",
            tenant_id.replace('\'', "''")
        ))
    }

    fn set_read_only_sql(&self) -> Option<String> {
        Some("SET TRANSACTION READ ONLY".to_string())
    }

    fn set_statement_timeout_sql(&self, ms: u64) -> Option<String> {
        Some(format!("SET LOCAL statement_timeout = {}", ms))
    }

    fn set_role_sql(&self, role: &str) -> Option<String> {
        Some(format!("SET LOCAL ROLE {}", self.quote_ident(role)))
    }

    // ── Idempotent DDL / introspection ────────────────────────────────────────

    fn supports_add_column_if_not_exists(&self) -> bool {
        true
    }

    fn is_duplicate_object_code(&self, code: &str) -> bool {
        matches!(
            code,
            // duplicate_column | duplicate_table (also index/relation) | duplicate_object
            // (constraint, type) | duplicate_schema | duplicate_alias
            "42701" | "42P07" | "42710" | "42P06" | "42712"
        )
    }

    fn introspect_columns_sql(&self, schema: &str) -> String {
        // pg_catalog rather than information_schema: information_schema exposes `is_nullable`
        // as the `yes_or_no` domain, which sqlx cannot decode without an explicit cast, and it
        // hides columns from tables the connected role has no privileges on.
        format!(
            "SELECT c.relname::text, a.attname::text, \
                    format_type(a.atttypid, a.atttypmod)::text, \
                    CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END, \
                    CASE WHEN a.atthasdef THEN 'YES' ELSE 'NO' END \
             FROM pg_attribute a \
             JOIN pg_class c ON c.oid = a.attrelid \
             JOIN pg_namespace n ON n.oid = c.relnamespace \
             WHERE n.nspname = '{}' AND c.relkind IN ('r', 'p') \
               AND a.attnum > 0 AND NOT a.attisdropped",
            escape_literal(schema)
        )
    }

    fn introspect_indexes_sql(&self, schema: &str) -> Option<String> {
        Some(format!(
            "SELECT c.relname::text FROM pg_class c \
             JOIN pg_namespace n ON n.oid = c.relnamespace \
             WHERE n.nspname = '{}' AND c.relkind IN ('i', 'I')",
            escape_literal(schema)
        ))
    }

    fn introspect_constraints_sql(&self, schema: &str) -> Option<String> {
        Some(format!(
            "SELECT t.relname::text, con.conname::text FROM pg_constraint con \
             JOIN pg_class t ON t.oid = con.conrelid \
             JOIN pg_namespace n ON n.oid = t.relnamespace \
             WHERE n.nspname = '{}'",
            escape_literal(schema)
        ))
    }
}

#[cfg(test)]
mod cast_name_tests {
    use super::cast_name;
    use crate::db::CanonicalType;

    #[test]
    fn serial_types_cast_as_their_integer_width() {
        // Regression: SERIAL/BIGSERIAL are integer/bigint in value comparisons. Without a cast,
        // an include/filter on such a column fails with `operator does not exist: bigint = text`
        // because all params bind as TEXT.
        assert_eq!(
            cast_name(&CanonicalType::Serial).as_deref(),
            Some("integer")
        );
        assert_eq!(
            cast_name(&CanonicalType::BigSerial).as_deref(),
            Some("bigint")
        );
        assert_eq!(cast_name(&CanonicalType::Int).as_deref(), Some("integer"));
        assert_eq!(cast_name(&CanonicalType::BigInt).as_deref(), Some("bigint"));
    }

    #[test]
    fn text_types_remain_uncast() {
        assert_eq!(cast_name(&CanonicalType::Text), None);
    }
}