use anyhow::Result;
use serde_json::Value;
use std::str::Chars;

/// Determines the placeholder and bind values for inserts, treating JSON null as SQL NULL.
pub fn build_insert_placeholders<'a>(values: &[&'a Value]) -> (Vec<String>, Vec<&'a Value>) {
    let mut placeholders: Vec<String> = Vec::with_capacity(values.len());
    let mut bind_values: Vec<&Value> = Vec::new();
    let mut next_param_index: i32 = 1;

    for value in values {
        if value.is_null() {
            placeholders.push("NULL".to_string());
        } else {
            placeholders.push(format!("${}", next_param_index));
            bind_values.push(value);
            next_param_index += 1;
        }
    }

    (placeholders, bind_values)
}

/// Supported comparison operators used to describe a condition.
#[derive(Debug, Clone, Copy)]
pub enum ConditionOperator {
    Eq,
    Neq,
    Gt,
    Lt,
    In,
    Gte,
    Lte,
    Like,
    ILike,
    Is,
    Contains,
    Contained,
}

/// Represents a filter condition used for building SQL queries.
#[derive(Debug)]
pub struct Condition {
    pub column: String,
    pub operator: ConditionOperator,
    pub values: Vec<Value>,
    pub negated: bool,
}

impl Condition {
    /// Builds an equality condition for the given column and value.
    pub fn eq(column: impl Into<String>, value: impl Into<Value>) -> Self {
        Self {
            column: column.into(),
            operator: ConditionOperator::Eq,
            values: vec![value.into()],
            negated: false,
        }
    }

    /// Builds a condition using the provided values and operator.
    pub fn new(
        column: impl Into<String>,
        operator: ConditionOperator,
        values: Vec<Value>,
        negated: bool,
    ) -> Self {
        Self {
            column: column.into(),
            operator,
            values,
            negated,
        }
    }
}

/// Quotes identifiers to keep SQL generation safe.
pub fn sanitize_identifier(identifier: &str) -> Option<String> {
    let mut chars: Chars<'_> = identifier.chars();
    let first: char = chars.next()?;
    if !(first.is_ascii_alphabetic() || first == '_') {
        return None;
    }
    if !chars.all(|c| c.is_ascii_alphanumeric() || c == '_') {
        return None;
    }
    // Quote the identifier to handle reserved keywords and mixed case
    Some(format!("\"{}\"", identifier))
}

/// Builds a WHERE clause for a list of filter conditions.
pub fn build_where_clause(
    conditions: &[Condition],
    start_index: usize,
) -> Result<(String, Vec<Value>)> {
    let mut clause_parts: Vec<String> = Vec::new();
    let mut values: Vec<Value> = Vec::new();
    let mut idx: usize = start_index;

    for condition in conditions {
        if let Some(column) = sanitize_identifier(&condition.column) {
            if let Some(single_clause) =
                format_condition_clause(&column, condition, &mut idx, &mut values)
            {
                clause_parts.push(single_clause);
            }
        }
    }

    let clause = if clause_parts.is_empty() {
        String::new()
    } else {
        format!(" WHERE {}", clause_parts.join(" AND "))
    };

    Ok((clause, values))
}

pub(crate) fn format_condition_clause(
    column: &str,
    condition: &Condition,
    idx: &mut usize,
    values: &mut Vec<Value>,
) -> Option<String> {
    let clause = match condition.operator {
        ConditionOperator::Eq => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, "=")?
        }
        ConditionOperator::Neq => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, "<>")?
        }
        ConditionOperator::Gt => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, ">")?
        }
        ConditionOperator::Gte => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, ">=")?
        }
        ConditionOperator::Lt => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, "<")?
        }
        ConditionOperator::Lte => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, "<=")?
        }
        ConditionOperator::Like => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, "LIKE")?
        }
        ConditionOperator::ILike => {
            create_placeholder_clause(column, idx, values, condition.values.first()?, "ILIKE")?
        }
        ConditionOperator::Is => build_is_clause(column, condition, idx, values)?,
        ConditionOperator::In => build_in_clause(column, condition, idx, values)?,
        ConditionOperator::Contains => build_array_clause(column, condition, idx, values, "@>")?,
        ConditionOperator::Contained => build_array_clause(column, condition, idx, values, "<@")?,
    };

    let clause = if condition.negated {
        format!("NOT ({})", clause)
    } else {
        clause
    };

    Some(clause)
}

fn create_placeholder_clause(
    column: &str,
    idx: &mut usize,
    values: &mut Vec<Value>,
    value: &Value,
    comparator: &str,
) -> Option<String> {
    let placeholder = format!("${}", idx);
    values.push(value.clone());
    *idx += 1;
    Some(format!("t.{} {} {}", column, comparator, placeholder))
}

fn build_is_clause(
    column: &str,
    condition: &Condition,
    idx: &mut usize,
    values: &mut Vec<Value>,
) -> Option<String> {
    match condition.values.first() {
        Some(Value::Null) => Some(format!("t.{} IS NULL", column)),
        Some(Value::Bool(true)) => Some(format!("t.{} IS TRUE", column)),
        Some(Value::Bool(false)) => Some(format!("t.{} IS FALSE", column)),
        Some(other) => {
            let placeholder = format!("${}", idx);
            values.push(other.clone());
            *idx += 1;
            Some(format!("t.{} IS {}", column, placeholder))
        }
        None => Some(format!("t.{} IS NULL", column)),
    }
}

fn build_in_clause(
    column: &str,
    condition: &Condition,
    idx: &mut usize,
    values: &mut Vec<Value>,
) -> Option<String> {
    if condition.values.is_empty() {
        return None;
    }

    let mut placeholders: Vec<String> = Vec::new();
    for value in &condition.values {
        placeholders.push(format!("${}", idx));
        values.push(value.clone());
        *idx += 1;
    }
    Some(format!("t.{} IN ({})", column, placeholders.join(", ")))
}

fn build_array_clause(
    column: &str,
    condition: &Condition,
    idx: &mut usize,
    values: &mut Vec<Value>,
    operator: &str,
) -> Option<String> {
    let array_value = condition.values.first()?;
    let placeholder = format!("${}", idx);
    values.push(array_value.clone());
    *idx += 1;
    Some(format!("t.{} {} {}", column, operator, placeholder))
}
