athena_rs 2.8.0

//! Translators that turn query builders into backend SQL/CQL.
use crate::client::backend::{BackendError, BackendResult, QueryLanguage, TranslatedQuery};
use crate::client::query_builder::{
    Condition, ConditionOperator, DeleteQuery, InsertQuery, OrderDirection, SelectQuery,
    UpdateQuery,
};
use serde_json::Value;
use std::fmt::Write;
use supabase_rs::query::Query;

/// Trait that translators implement to convert builder metadata into SQL/CQL and parameters.
pub(crate) trait QueryTranslator {
    fn translate_select(&self, query: &SelectQuery) -> BackendResult<TranslatedQuery>;

    fn translate_insert(&self, query: &InsertQuery) -> BackendResult<TranslatedQuery>;

    fn translate_update(&self, query: &UpdateQuery) -> BackendResult<TranslatedQuery>;

    fn translate_delete(&self, query: &DeleteQuery) -> BackendResult<TranslatedQuery>;
}

/// SQL translator suitable for PostgreSQL/Supabase/Neon.
pub struct SqlTranslator;

/// PostgREST translator suitable for Supabase backend (REST).
pub struct PostgrestTranslator;

/// CQL translator suitable for ScyllaDB.
pub struct CqlTranslator;

impl QueryTranslator for SqlTranslator {
    fn translate_select(&self, query: &SelectQuery) -> BackendResult<TranslatedQuery> {
        let select_nodes = parse_select(&query.raw_select, &query.columns)?;
        let mut emitter = SqlEmitter::default();
        emitter.emit_root(&query.table, select_nodes, query)
    }

    fn translate_insert(&self, query: &InsertQuery) -> BackendResult<TranslatedQuery> {
        let (columns, values, params) = build_insert_fragments(&query.payload);

        let sql = format!(
            "INSERT INTO {} ({}) VALUES ({})",
            query.table,
            columns.join(", "),
            values.join(", ")
        );

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Sql,
            params,
            Some(query.table.clone()),
        ))
    }

    fn translate_update(&self, query: &UpdateQuery) -> BackendResult<TranslatedQuery> {
        let (assignments, mut params) = build_update_fragments(&query.payload);
        let mut sql = format!("UPDATE {} SET {}", query.table, assignments.join(", "));

        if let Some(row_id) = &query.row_id {
            let literal = format_value(&Value::String(row_id.clone()));
            sql.push_str(&format!(" WHERE id = {}", literal));
            params.push(Value::String(row_id.clone()));
        }

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Sql,
            params,
            Some(query.table.clone()),
        ))
    }

    fn translate_delete(&self, query: &DeleteQuery) -> BackendResult<TranslatedQuery> {
        let mut sql = format!("DELETE FROM {}", query.table);
        let mut params = Vec::new();

        if let Some(row_id) = &query.row_id {
            let literal = format_value(&Value::String(row_id.clone()));
            sql.push_str(&format!(" WHERE id = {}", literal));
            params.push(Value::String(row_id.clone()));
        }

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Sql,
            params,
            Some(query.table.clone()),
        ))
    }
}

impl QueryTranslator for CqlTranslator {
    fn translate_select(&self, query: &SelectQuery) -> BackendResult<TranslatedQuery> {
        let columns: String = if query.columns.is_empty() {
            "*".to_string()
        } else {
            query.columns.join(", ")
        };

        let mut sql: String = format!("SELECT {} FROM {}", columns, query.table);
        let (where_clause, params) = build_where_clause(&query.conditions);
        sql.push_str(&where_clause);

        if let Some(limit) = query.limit {
            sql.push_str(&format!(" LIMIT {}", limit));
        }

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Cql,
            params,
            Some(query.table.clone()),
        ))
    }

    fn translate_insert(&self, query: &InsertQuery) -> BackendResult<TranslatedQuery> {
        let (columns, values, params) = build_insert_fragments(&query.payload);

        let sql: String = format!(
            "INSERT INTO {} ({}) VALUES ({})",
            query.table,
            columns.join(", "),
            values.join(", ")
        );

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Cql,
            params,
            Some(query.table.clone()),
        ))
    }

    fn translate_update(&self, query: &UpdateQuery) -> BackendResult<TranslatedQuery> {
        let (assignments, mut params) = build_update_fragments(&query.payload);
        let mut sql: String = format!("UPDATE {} SET {}", query.table, assignments.join(", "));

        if let Some(row_id) = &query.row_id {
            let literal = format_value(&Value::String(row_id.clone()));
            sql.push_str(&format!(" WHERE id = {}", literal));
            params.push(Value::String(row_id.clone()));
        }

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Cql,
            params,
            Some(query.table.clone()),
        ))
    }

    fn translate_delete(&self, query: &DeleteQuery) -> BackendResult<TranslatedQuery> {
        let mut sql = format!("DELETE FROM {}", query.table);
        let mut params = Vec::new();

        if let Some(row_id) = &query.row_id {
            let literal = format_value(&Value::String(row_id.clone()));
            sql.push_str(&format!(" WHERE id = {}", literal));
            params.push(Value::String(row_id.clone()));
        }

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Cql,
            params,
            Some(query.table.clone()),
        ))
    }
}

impl QueryTranslator for PostgrestTranslator {
    fn translate_select(&self, query: &SelectQuery) -> BackendResult<TranslatedQuery> {
        let mut q = Query::new();
        let select = if let Some(raw) = &query.raw_select {
            raw.clone()
        } else if query.columns.is_empty() {
            "*".to_string()
        } else {
            query.columns.join(",")
        };
        q.add_param("select", &select);

        for condition in &query.conditions {
            if let Some(value) = condition.values.first() {
                let encoded = postgrest_operator(condition.operator, value)?;
                q.add_param(&condition.column, &encoded);
            }
        }

        for (column, direction) in &query.order_by {
            let dir_str = match direction {
                OrderDirection::Asc => "asc",
                OrderDirection::Desc => "desc",
            };
            q.add_param("order", &format!("{column}.{dir_str}"));
        }

        if let Some(limit) = query.limit {
            q.add_param("limit", &limit.to_string());
        }

        if let Some(offset) = query.offset {
            q.add_param("offset", &offset.to_string());
        }

        let query_string = q.build();
        Ok(TranslatedQuery::new(
            query_string,
            QueryLanguage::Postgrest,
            Vec::new(),
            Some(query.table.clone()),
        ))
    }

    fn translate_insert(&self, _query: &InsertQuery) -> BackendResult<TranslatedQuery> {
        Err(BackendError::Generic(
            "Inserts are not supported via PostgREST translator".to_string(),
        ))
    }

    fn translate_update(&self, _query: &UpdateQuery) -> BackendResult<TranslatedQuery> {
        Err(BackendError::Generic(
            "Updates are not supported via PostgREST translator".to_string(),
        ))
    }

    fn translate_delete(&self, _query: &DeleteQuery) -> BackendResult<TranslatedQuery> {
        Err(BackendError::Generic(
            "Deletes are not supported via PostgREST translator".to_string(),
        ))
    }
}

fn build_where_clause(conditions: &[Condition]) -> (String, Vec<Value>) {
    if conditions.is_empty() {
        return (String::new(), Vec::new());
    }

    let mut clauses: Vec<String> = Vec::new();
    let mut params: Vec<Value> = Vec::new();

    for condition in conditions {
        match condition.operator {
            ConditionOperator::In => {
                let formatted: Vec<String> = condition.values.iter().map(format_value).collect();
                clauses.push(format!(
                    "{} IN ({})",
                    condition.column,
                    formatted.join(", ")
                ));
                params.extend(condition.values.clone());
            }
            _ => {
                if let Some(value) = condition.values.first() {
                    let operator = match condition.operator {
                        ConditionOperator::Eq => "=",
                        ConditionOperator::Neq => "<>",
                        ConditionOperator::Gt => ">",
                        ConditionOperator::Lt => "<",
                        _ => "=",
                    };

                    clauses.push(format!(
                        "{} {} {}",
                        condition.column,
                        operator,
                        format_value(value)
                    ));
                    params.extend(condition.values.clone());
                }
            }
        }
    }

    (format!(" WHERE {}", clauses.join(" AND ")), params)
}

fn build_insert_fragments(payload: &Value) -> (Vec<String>, Vec<String>, Vec<Value>) {
    if let Value::Object(map) = payload {
        let mut columns = Vec::new();
        let mut values = Vec::new();
        let mut params = Vec::new();

        for (column, value) in map {
            columns.push(column.clone());
            values.push(format_value(value));
            params.push(value.clone());
        }

        (columns, values, params)
    } else {
        (Vec::new(), Vec::new(), Vec::new())
    }
}

fn build_update_fragments(payload: &Value) -> (Vec<String>, Vec<Value>) {
    if let Value::Object(map) = payload {
        let mut assignments: Vec<String> = Vec::new();
        let mut params: Vec<Value> = Vec::new();

        for (column, value) in map {
            assignments.push(format!("{} = {}", column, format_value(value)));
            params.push(value.clone());
        }

        (assignments, params)
    } else {
        (Vec::new(), Vec::new())
    }
}

fn format_value(value: &Value) -> String {
    match value {
        Value::String(text) => format!("'{}'", text.replace('\'', "''")),
        Value::Number(num) => num.to_string(),
        Value::Bool(flag) => flag.to_string(),
        Value::Null => "NULL".to_string(),
        other => serde_json::to_string(other).unwrap_or_else(|_| "NULL".to_string()),
    }
}

fn postgrest_operator(op: ConditionOperator, value: &Value) -> BackendResult<String> {
    let val = match value {
        Value::String(s) => s.clone(),
        other => other.to_string(),
    };
    let encoded = match op {
        ConditionOperator::Eq => format!("eq.{val}"),
        ConditionOperator::Neq => format!("neq.{val}"),
        ConditionOperator::Gt => format!("gt.{val}"),
        ConditionOperator::Lt => format!("lt.{val}"),
        ConditionOperator::In => format!("in.({val})"),
    };
    Ok(encoded)
}

// ----- Supabase-style select parsing and SQL emission -----

#[derive(Debug, Clone, PartialEq, Eq)]
enum JoinKind {
    Left,
    Inner,
}

#[derive(Debug, Clone)]
enum SelectNode {
    Column(String),
    Relation(RelationNode),
}

#[derive(Debug, Clone)]
struct RelationNode {
    name: String,
    alias: Option<String>,
    join: JoinKind,
    foreign_key: Option<String>,
    children: Vec<SelectNode>,
    conditions: Vec<Condition>,
}

impl RelationNode {
    fn display_name(&self) -> &str {
        self.alias.as_deref().unwrap_or(&self.name)
    }
}

fn parse_select(raw: &Option<String>, columns: &[String]) -> BackendResult<Vec<SelectNode>> {
    if let Some(raw) = raw {
        parse_select_string(raw)
    } else {
        Ok(columns
            .iter()
            .map(|c| SelectNode::Column(c.clone()))
            .collect())
    }
}

fn parse_select_string(input: &str) -> BackendResult<Vec<SelectNode>> {
    let mut chars = input.chars().peekable();
    parse_nodes(&mut chars)
}

fn parse_nodes<I>(iter: &mut std::iter::Peekable<I>) -> BackendResult<Vec<SelectNode>>
where
    I: Iterator<Item = char>,
{
    let mut nodes = Vec::new();
    loop {
        skip_ws(iter);
        if matches!(iter.peek(), Some(')')) || iter.peek().is_none() {
            break;
        }
        nodes.push(parse_node(iter)?);
        skip_ws(iter);
        if matches!(iter.peek(), Some(',')) {
            iter.next();
            continue;
        }
        if matches!(iter.peek(), Some(')')) || iter.peek().is_none() {
            break;
        }
    }
    Ok(nodes)
}

fn parse_node<I>(iter: &mut std::iter::Peekable<I>) -> BackendResult<SelectNode>
where
    I: Iterator<Item = char>,
{
    let mut token = String::new();
    while let Some(&ch) = iter.peek() {
        if matches!(ch, '!' | ':' | '(' | ')' | ',') {
            break;
        }
        token.push(ch);
        iter.next();
    }
    let token = token.trim().to_string();
    if token.is_empty() {
        return Err(BackendError::Generic(
            "invalid select syntax: empty token".to_string(),
        ));
    }

    let mut alias = None;
    let mut name_owned = token.clone();

    if matches!(iter.peek(), Some(':')) {
        iter.next();
        let mut relation = String::new();
        while let Some(&ch) = iter.peek() {
            if matches!(ch, '!' | '(' | ')' | ',') {
                break;
            }
            relation.push(ch);
            iter.next();
        }
        alias = Some(token.clone());
        name_owned = relation;
    }
    let name = name_owned.as_str();

    let mut join = JoinKind::Left;
    let mut foreign_key = None;

    if matches!(iter.peek(), Some('!')) {
        iter.next();
        let mut modifier = String::new();
        while let Some(&ch) = iter.peek() {
            if matches!(ch, '(' | ')' | ',') {
                break;
            }
            modifier.push(ch);
            iter.next();
        }
        let modifier = modifier.trim();
        if modifier.eq_ignore_ascii_case("inner") {
            join = JoinKind::Inner;
        } else if !modifier.is_empty() {
            foreign_key = Some(modifier.to_string());
        }
    }

    if matches!(iter.peek(), Some('(')) {
        iter.next();
        let children = parse_nodes(iter)?;
        if iter.next() != Some(')') {
            return Err(BackendError::Generic(
                "invalid select syntax: missing ')'".to_string(),
            ));
        }
        Ok(SelectNode::Relation(RelationNode {
            name: name.to_string(),
            alias,
            join,
            foreign_key,
            children,
            conditions: Vec::new(),
        }))
    } else {
        Ok(SelectNode::Column(name.to_string()))
    }
}

fn skip_ws<I>(iter: &mut std::iter::Peekable<I>)
where
    I: Iterator<Item = char>,
{
    while matches!(iter.peek(), Some(ch) if ch.is_whitespace()) {
        iter.next();
    }
}

/// Attach dotted conditions (`relation.column`) to the corresponding relation node tree.
fn distribute_conditions(nodes: &mut [SelectNode], conditions: &[Condition]) -> Vec<Condition> {
    let mut remaining = Vec::new();
    for cond in conditions {
        if let Some((head, rest)) = cond.column.split_once('.')
            && let Some(node) = nodes.iter_mut().find_map(|n| match n {
                SelectNode::Relation(r) if r.display_name() == head => Some(r),
                _ => None,
            })
        {
            push_condition(node, rest, cond.clone());
            continue;
        }
        remaining.push(cond.clone());
    }
    remaining
}

fn push_condition(target: &mut RelationNode, path: &str, condition: Condition) {
    if let Some((head, rest)) = path.split_once('.') {
        if let Some(child) = target.children.iter_mut().find_map(|n| match n {
            SelectNode::Relation(r) if r.display_name() == head => Some(r),
            _ => None,
        }) {
            push_condition(child, rest, condition);
        }
    } else {
        target.conditions.push(condition);
    }
}

#[derive(Default)]
struct SqlEmitter {
    alias_counter: usize,
}

impl SqlEmitter {
    fn emit_root(
        &mut self,
        table: &str,
        mut nodes: Vec<SelectNode>,
        query: &SelectQuery,
    ) -> BackendResult<TranslatedQuery> {
        let base_alias = "t0".to_string();
        let base_conditions = distribute_conditions(&mut nodes, &query.conditions);

        let mut select_exprs = Vec::new();
        let mut joins = Vec::new();

        for node in nodes {
            self.emit_node(&node, &base_alias, table, &mut select_exprs, &mut joins)?;
        }

        if select_exprs.is_empty() {
            select_exprs.push("*".to_string());
        }

        let mut sql = format!(
            "SELECT {select} FROM {table} {alias}",
            select = select_exprs.join(", "),
            table = table,
            alias = base_alias
        );

        if !joins.is_empty() {
            sql.push('\n');
            sql.push_str(&joins.join("\n"));
        }

        if !base_conditions.is_empty() {
            let adapted: Vec<Condition> = base_conditions
                .iter()
                .cloned()
                .map(|mut c| {
                    c.column = format!("{base_alias}.{}", c.column);
                    c
                })
                .collect();
            let (where_clause, _) = build_where_clause(&adapted);
            sql.push_str(&where_clause);
        }

        if !query.order_by.is_empty() {
            let ordering: Vec<String> = query
                .order_by
                .iter()
                .map(|(column, direction)| {
                    let dir_str = match direction {
                        OrderDirection::Asc => "ASC",
                        OrderDirection::Desc => "DESC",
                    };
                    if column.contains('.') {
                        format!("{column} {dir_str}")
                    } else {
                        format!("{base_alias}.{column} {dir_str}")
                    }
                })
                .collect();
            sql.push_str(&format!(" ORDER BY {}", ordering.join(", ")));
        }

        if let Some(limit) = query.limit {
            sql.push_str(&format!(" LIMIT {}", limit));
        }

        if let Some(offset) = query.offset {
            sql.push_str(&format!(" OFFSET {}", offset));
        }

        Ok(TranslatedQuery::new(
            sql,
            QueryLanguage::Sql,
            Vec::new(),
            Some(table.to_string()),
        ))
    }

    fn emit_node(
        &mut self,
        node: &SelectNode,
        parent_alias: &str,
        parent_table: &str,
        select_exprs: &mut Vec<String>,
        joins: &mut Vec<String>,
    ) -> BackendResult<()> {
        match node {
            SelectNode::Column(col) => {
                if col == "*" {
                    select_exprs.push(format!("{parent_alias}.*"));
                } else {
                    select_exprs.push(format!("{parent_alias}.{col}"));
                }
            }
            SelectNode::Relation(rel) => {
                let alias = self.next_alias(&rel.name);
                let agg_alias = format!("{alias}_agg");

                let mut relation_clone = rel.clone();
                let _ = distribute_conditions(&mut relation_clone.children, &rel.conditions);
                let (row_expr, child_joins) = self.emit_relation_row(&relation_clone, &alias)?;

                let (join_condition, many_to_one) =
                    relation_join_condition(&relation_clone, parent_alias, parent_table, &alias);

                let mut subquery = String::new();

                if many_to_one {
                    writeln!(
                        subquery,
                        "SELECT {row_expr} AS data FROM {table} {alias}",
                        table = relation_clone.name,
                        alias = alias,
                        row_expr = row_expr
                    )
                    .unwrap();
                } else {
                    write!(
                        subquery,
                        "SELECT COALESCE(jsonb_agg({row_expr} ORDER BY {alias}.id) FILTER (WHERE {alias}.id IS NOT NULL), '[]'::jsonb) AS data\nFROM {table} {alias}\n",
                        row_expr = row_expr,
                        alias = alias,
                        table = relation_clone.name
                    )
                    .unwrap();
                }

                if !child_joins.is_empty() {
                    subquery.push_str(&child_joins.join("\n"));
                    subquery.push('\n');
                }

                write!(subquery, "WHERE {join_condition}").unwrap();

                if !relation_clone.conditions.is_empty() {
                    let adapted: Vec<Condition> = relation_clone
                        .conditions
                        .iter()
                        .cloned()
                        .map(|mut c| {
                            c.column = format!("{alias}.{}", c.column);
                            c
                        })
                        .collect();
                    let (where_clause, _) = build_where_clause(&adapted);
                    subquery.push_str(&where_clause);
                }

                if many_to_one {
                    subquery.push_str(" LIMIT 1");
                }

                let join_type = match rel.join {
                    JoinKind::Left => "LEFT JOIN",
                    JoinKind::Inner => "JOIN",
                };

                let mut join_sql = format!("{join_type} LATERAL (\n{subquery}\n) {agg_alias} ON ");

                if rel.join == JoinKind::Inner {
                    join_sql.push_str(&format!("{agg_alias}.data IS NOT NULL"));
                } else {
                    join_sql.push_str("TRUE");
                }

                joins.push(join_sql);
                select_exprs.push(format!("{} as {}", agg_alias, rel.display_name()));
            }
        }
        Ok(())
    }

    fn emit_relation_row(
        &mut self,
        relation: &RelationNode,
        alias: &str,
    ) -> BackendResult<(String, Vec<String>)> {
        let mut field_pairs = Vec::new();
        let mut joins = Vec::new();

        for child in &relation.children {
            match child {
                SelectNode::Column(col) => {
                    field_pairs.push(format!("'{}', {}.{}", col, alias, col));
                }
                SelectNode::Relation(rel) => {
                    let child_alias = self.next_alias(&rel.name);
                    let mut rel_clone = rel.clone();
                    let _ = distribute_conditions(&mut rel_clone.children, &rel.conditions);
                    let (row_expr, nested_joins) =
                        self.emit_relation_row(&rel_clone, &child_alias)?;

                    let (join_condition, many_to_one) =
                        relation_join_condition(&rel_clone, alias, &relation.name, &child_alias);

                    let mut subquery = String::new();
                    if many_to_one {
                        writeln!(
                            subquery,
                            "SELECT {row_expr} AS data FROM {table} {child_alias}",
                            row_expr = row_expr,
                            table = rel_clone.name,
                            child_alias = child_alias
                        )
                        .unwrap();
                    } else {
                        write!(
                            subquery,
                            "SELECT COALESCE(jsonb_agg({row_expr} ORDER BY {child_alias}.id) FILTER (WHERE {child_alias}.id IS NOT NULL), '[]'::jsonb) AS data\nFROM {table} {child_alias}\n",
                            row_expr = row_expr,
                            child_alias = child_alias,
                            table = rel_clone.name
                        )
                        .unwrap();
                    }

                    if !nested_joins.is_empty() {
                        subquery.push_str(&nested_joins.join("\n"));
                        subquery.push('\n');
                    }

                    write!(subquery, "WHERE {join_condition}").unwrap();

                    if !rel_clone.conditions.is_empty() {
                        let adapted: Vec<Condition> = rel_clone
                            .conditions
                            .iter()
                            .cloned()
                            .map(|mut c| {
                                c.column = format!("{child_alias}.{}", c.column);
                                c
                            })
                            .collect();
                        let (where_clause, _) = build_where_clause(&adapted);
                        subquery.push_str(&where_clause);
                    }

                    if many_to_one {
                        subquery.push_str(" LIMIT 1");
                    }

                    let join_type = match rel.join {
                        JoinKind::Left => "LEFT JOIN",
                        JoinKind::Inner => "JOIN",
                    };

                    let agg_alias = format!("{child_alias}_agg");
                    let mut join_sql =
                        format!("{join_type} LATERAL (\n{subquery}\n) {agg_alias} ON ");
                    if rel.join == JoinKind::Inner {
                        join_sql.push_str(&format!("{agg_alias}.data IS NOT NULL"));
                    } else {
                        join_sql.push_str("TRUE");
                    }
                    joins.push(join_sql);

                    field_pairs.push(format!("'{}', {agg_alias}.data", rel.display_name()));
                }
            }
        }

        let row_expr = if field_pairs.is_empty() {
            format!("to_jsonb({alias})")
        } else {
            format!("jsonb_build_object({})", field_pairs.join(", "))
        };

        Ok((row_expr, joins))
    }

    fn next_alias(&mut self, base: &str) -> String {
        let alias = format!("{}_{}", base, self.alias_counter);
        self.alias_counter += 1;
        alias
    }
}

/// Determine join condition and whether the relationship should be treated as many-to-one.
/// `child_alias` is the alias used for the relation table in the subquery (e.g. "posts_0").
fn relation_join_condition(
    relation: &RelationNode,
    parent_alias: &str,
    parent_table: &str,
    child_alias: &str,
) -> (String, bool) {
    if let Some(fk) = &relation.foreign_key {
        let fk_lower = fk.to_lowercase();
        if let Some(stripped) = fk_lower.strip_prefix("parent.") {
            return (
                format!("{parent_alias}.{stripped} = {child_alias}.id"),
                true,
            );
        }
        if let Some(stripped) = fk_lower.strip_prefix("child.") {
            return (
                format!("{child_alias}.{stripped} = {parent_alias}.id"),
                false,
            );
        }

        // Explicit FK without prefix: infer placement from naming.
        // If FK name contains the relation (child) table name, assume parent-side FK;
        // otherwise assume child-side FK (e.g. posts!author_id where author_id is on posts).
        let rel_singular = relation.name.trim_end_matches('s').to_lowercase();
        let rel_lower = relation.name.to_lowercase();
        let fk_suggests_parent_side =
            fk_lower.contains(&rel_lower) || fk_lower.contains(&rel_singular);

        return if fk_suggests_parent_side {
            (format!("{parent_alias}.{fk} = {child_alias}.id"), true)
        } else {
            (format!("{child_alias}.{fk} = {parent_alias}.id"), false)
        };
    }

    let child_fk = format!("{}_id", parent_table.trim_end_matches('s'));
    let parent_fk = format!("{}_id", relation.name.trim_end_matches('s'));

    // Heuristic: when the relation name is longer (often parent tables), prefer many-to-one.
    let many_to_one = relation.name.len() >= parent_table.len();

    if many_to_one {
        (
            format!("{parent_alias}.{parent_fk} = {child_alias}.id"),
            true,
        )
    } else {
        (
            format!("{child_alias}.{child_fk} = {parent_alias}.id"),
            false,
        )
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn parses_nested_supabase_select() {
        let select = "id,name,instruments!inner(id,name),start_scan:scans!scan_id_start(id)";
        let nodes = parse_select(&Some(select.to_string()), &[]).expect("parse ok");
        assert_eq!(nodes.len(), 4);
        match &nodes[2] {
            SelectNode::Relation(rel) => {
                assert_eq!(rel.name, "instruments");
                assert!(matches!(rel.join, JoinKind::Inner));
                assert_eq!(rel.children.len(), 2);
            }
            _ => panic!("expected relation"),
        }
    }

    #[test]
    fn postgrest_translator_builds_query_string() {
        let translator = PostgrestTranslator;
        let query = SelectQuery {
            table: "orchestral_sections".to_string(),
            columns: vec![],
            raw_select: Some("id,name,instruments(id,name)".to_string()),
            conditions: vec![Condition::new(
                "instruments.name",
                ConditionOperator::Eq,
                vec![Value::String("flute".to_string())],
            )],
            order_by: vec![],
            limit: Some(10),
            offset: Some(5),
        };

        let translated = translator.translate_select(&query).expect("translate ok");
        assert_eq!(translated.language, QueryLanguage::Postgrest);
        assert!(
            translated
                .sql
                .contains("select=id,name,instruments(id,name)")
        );
        assert!(translated.sql.contains("instruments.name=eq.flute"));
        assert!(translated.sql.contains("limit=10"));
        assert!(translated.sql.contains("offset=5"));
    }

    #[test]
    fn sql_translator_builds_lateral_join() {
        let translator = SqlTranslator;
        let query = SelectQuery {
            table: "orchestral_sections".to_string(),
            columns: vec![],
            raw_select: Some("id,name,instruments(id,name)".to_string()),
            conditions: vec![],
            order_by: vec![],
            limit: None,
            offset: None,
        };

        let translated = translator.translate_select(&query).expect("translate ok");
        assert_eq!(translated.language, QueryLanguage::Sql);
        assert!(translated.sql.contains("jsonb_agg"));
        assert!(translated.sql.contains("FROM instruments instruments_0"));
        assert!(translated.sql.contains("instruments_0_agg as instruments"));
    }

    #[test]
    fn sql_translator_child_side_fk_uses_jsonb_agg() {
        // authors -> posts: author_id is on posts (child). One author has many posts.
        // posts!author_id should produce posts.author_id = authors.id and jsonb_agg (not LIMIT 1).
        let translator = SqlTranslator;
        let query = SelectQuery {
            table: "authors".to_string(),
            columns: vec![],
            raw_select: Some("id,name,posts!author_id(id,title)".to_string()),
            conditions: vec![],
            order_by: vec![],
            limit: None,
            offset: None,
        };

        let translated = translator.translate_select(&query).expect("translate ok");
        assert_eq!(translated.language, QueryLanguage::Sql);
        // Child-side FK: join must be posts_0.author_id = t0.id
        assert!(
            translated.sql.contains("posts_0.author_id = t0.id"),
            "expected child-side join condition, got: {}",
            translated.sql
        );
        // One-to-many: must use jsonb_agg, not LIMIT 1
        assert!(
            translated.sql.contains("jsonb_agg"),
            "expected jsonb_agg for one-to-many, got: {}",
            translated.sql
        );
        assert!(
            !translated.sql.contains("LIMIT 1"),
            "one-to-many should not use LIMIT 1, got: {}",
            translated.sql
        );
    }
}