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

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

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

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

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

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

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

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

        let mut sql = format!("SELECT {} FROM {}", columns, query.table);
        let (where_clause, params) = build_where_clause(&query.conditions);
        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",
                    };
                    format!("{} {}", 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));
        }

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

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

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

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

    fn translate_update(&self, query: &UpdateQuery) -> 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()));
        }

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

    fn translate_delete(&self, query: &DeleteQuery) -> 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()));
        }

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

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

        let mut sql = 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));
        }

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

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

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

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

    fn translate_update(&self, query: &UpdateQuery) -> 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()));
        }

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

    fn translate_delete(&self, query: &DeleteQuery) -> 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()));
        }

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

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

    let mut clauses = Vec::new();
    let mut params = Vec::new();

    for condition in conditions {
        match condition.operator {
            ConditionOperator::In => {
                let formatted: Vec<String> = condition
                    .values
                    .iter()
                    .map(|value| format_value(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::new();
        let mut params = 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()),
    }
}
