use crate::ast::Expr;
use crate::catalog::Catalog;
use crate::error::DbError;
use crate::storage::Storage;
use crate::types::Value;

use super::super::super::clock::Clock;
use super::super::super::context::ExecutionContext;
use super::super::super::evaluator::eval_expr;
use super::super::super::model::ContextTable;

pub(crate) fn eval_len(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 1 {
        return Err(DbError::Execution("LEN expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    match val {
        Value::Null => Ok(Value::Null),
        Value::Char(s) | Value::VarChar(s) | Value::NChar(s) | Value::NVarChar(s) => {
            Ok(Value::Int(s.trim_end().len() as i32))
        }
        _ => {
            let s = val.to_string_value();
            Ok(Value::Int(s.trim_end().len() as i32))
        }
    }
}

pub(crate) fn eval_substring(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 3 {
        return Err(DbError::Execution("SUBSTRING expects 3 arguments".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    let start = eval_expr(&args[1], row, ctx, catalog, storage, clock)?;
    let length = eval_expr(&args[2], row, ctx, catalog, storage, clock)?;

    let s = val.to_string_value();
    let start_i = start.to_integer_i64().unwrap_or(1) as i32;
    let len_i = length.to_integer_i64().unwrap_or(0) as i32;

    let chars: Vec<char> = s.chars().collect();
    let start_idx = if start_i <= 0 {
        0
    } else {
        (start_i as usize - 1).min(chars.len())
    };
    let end_idx = (start_idx + len_i.max(0) as usize).min(chars.len());
    let result: String = chars[start_idx..end_idx].iter().collect();

    match val {
        Value::NVarChar(_) | Value::NChar(_) => Ok(Value::NVarChar(result)),
        _ => Ok(Value::VarChar(result)),
    }
}

pub(crate) fn eval_upper(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 1 {
        return Err(DbError::Execution("UPPER expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    match val {
        Value::Null => Ok(Value::Null),
        Value::VarChar(s) | Value::NVarChar(s) | Value::Char(s) | Value::NChar(s) => {
            Ok(Value::VarChar(s.to_uppercase()))
        }
        _ => Ok(Value::VarChar(val.to_string_value().to_uppercase())),
    }
}

pub(crate) fn eval_lower(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 1 {
        return Err(DbError::Execution("LOWER expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    match val {
        Value::Null => Ok(Value::Null),
        Value::VarChar(s) | Value::NVarChar(s) | Value::Char(s) | Value::NChar(s) => {
            Ok(Value::VarChar(s.to_lowercase()))
        }
        _ => Ok(Value::VarChar(val.to_string_value().to_lowercase())),
    }
}

#[allow(clippy::too_many_arguments)]
pub(crate) fn eval_trim(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
    left: bool,
    right: bool,
) -> Result<Value, DbError> {
    if args.len() != 1 {
        return Err(DbError::Execution("TRIM expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    let s = val.to_string_value();
    let mut result = s.as_str();
    if left {
        result = result.trim_start();
    }
    if right {
        result = result.trim_end();
    }
    Ok(Value::VarChar(result.to_string()))
}

pub(crate) fn eval_replace(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 3 {
        return Err(DbError::Execution("REPLACE expects 3 arguments".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    let from = eval_expr(&args[1], row, ctx, catalog, storage, clock)?;
    let to = eval_expr(&args[2], row, ctx, catalog, storage, clock)?;

    if val.is_null() || from.is_null() || to.is_null() {
        return Ok(Value::Null);
    }

    let s = val.to_string_value();
    let f = from.to_string_value();
    let t = to.to_string_value();
    Ok(Value::VarChar(s.replace(&f, &t)))
}

pub(crate) fn eval_left(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 2 {
        return Err(DbError::Execution("LEFT expects 2 arguments".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    let count = eval_expr(&args[1], row, ctx, catalog, storage, clock)?;

    if val.is_null() {
        return Ok(Value::Null);
    }

    let s = val.to_string_value();
    let n = count.to_integer_i64().unwrap_or(0).max(0) as usize;
    let result: String = s.chars().take(n).collect();
    Ok(Value::VarChar(result))
}

pub(crate) fn eval_right(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 2 {
        return Err(DbError::Execution("RIGHT expects 2 arguments".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    let count = eval_expr(&args[1], row, ctx, catalog, storage, clock)?;

    if val.is_null() {
        return Ok(Value::Null);
    }

    let s = val.to_string_value();
    let n = count.to_integer_i64().unwrap_or(0).max(0) as usize;
    let chars: Vec<char> = s.chars().collect();
    let start = chars.len().saturating_sub(n);
    let result: String = chars[start..].iter().collect();
    Ok(Value::VarChar(result))
}

pub(crate) fn eval_ascii(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.is_empty() {
        return Err(DbError::Execution("ASCII expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    if val.is_null() {
        return Ok(Value::Null);
    }
    let s = val.to_string_value();
    if let Some(c) = s.chars().next() {
        Ok(Value::Int(c as i32))
    } else {
        Ok(Value::Null)
    }
}

pub(crate) fn eval_char(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.is_empty() {
        return Err(DbError::Execution("CHAR expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    if val.is_null() {
        return Ok(Value::Null);
    }
    let code = val.to_integer_i64().unwrap_or(0) as u8;
    Ok(Value::VarChar((code as char).to_string()))
}

pub(crate) fn eval_nchar(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.is_empty() {
        return Err(DbError::Execution("NCHAR expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    if val.is_null() {
        return Ok(Value::Null);
    }
    let code = val.to_integer_i64().unwrap_or(0) as u32;
    if let Some(c) = std::char::from_u32(code) {
        Ok(Value::NVarChar(c.to_string()))
    } else {
        Ok(Value::Null)
    }
}

pub(crate) fn eval_datalength(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.len() != 1 {
        return Err(DbError::Execution("DATALENGTH expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    match val {
        Value::Null => Ok(Value::Null),
        Value::Bit(_) | Value::TinyInt(_) => Ok(Value::Int(1)),
        Value::SmallInt(_) => Ok(Value::Int(2)),
        Value::Int(_) => Ok(Value::Int(4)),
        Value::BigInt(_) | Value::Float(_) | Value::DateTime(_) | Value::DateTime2(_) => {
            Ok(Value::Int(8))
        }
        Value::Decimal(_, _) => Ok(Value::Int(17)), // Max precision i128
        Value::Money(_) => Ok(Value::Int(8)),
        Value::SmallMoney(_) => Ok(Value::Int(4)),
        Value::Char(s) | Value::VarChar(s) => Ok(Value::Int(s.len() as i32)),
        Value::NChar(s) | Value::NVarChar(s) => Ok(Value::Int((s.len() * 2) as i32)),
        Value::Binary(v) | Value::VarBinary(v) => Ok(Value::Int(v.len() as i32)),
        Value::Vector(v) => Ok(Value::Int((v.len() * 4) as i32)),
        Value::Date(_) => Ok(Value::Int(3)),
        Value::Time(_) => Ok(Value::Int(5)), // Simplified
        Value::SmallDateTime(_) => Ok(Value::Int(4)),
        Value::DateTimeOffset(_) => Ok(Value::Int(10)),
        Value::UniqueIdentifier(_) => Ok(Value::Int(16)),
        Value::SqlVariant(inner) => {
            // Recurse for SqlVariant
            eval_datalength_internal(&inner)
        }
    }
}

fn eval_datalength_internal(val: &Value) -> Result<Value, DbError> {
    match val {
        Value::Null => Ok(Value::Null),
        Value::Bit(_) | Value::TinyInt(_) => Ok(Value::Int(1)),
        Value::SmallInt(_) => Ok(Value::Int(2)),
        Value::Int(_) => Ok(Value::Int(4)),
        Value::BigInt(_) | Value::Float(_) | Value::DateTime(_) | Value::DateTime2(_) => {
            Ok(Value::Int(8))
        }
        Value::Decimal(_, _) => Ok(Value::Int(17)),
        Value::Money(_) => Ok(Value::Int(8)),
        Value::SmallMoney(_) => Ok(Value::Int(4)),
        Value::Char(s) | Value::VarChar(s) => Ok(Value::Int(s.len() as i32)),
        Value::NChar(s) | Value::NVarChar(s) => Ok(Value::Int((s.len() * 2) as i32)),
        Value::Binary(v) | Value::VarBinary(v) => Ok(Value::Int(v.len() as i32)),
        Value::Vector(v) => Ok(Value::Int((v.len() * 4) as i32)),
        Value::Date(_) => Ok(Value::Int(3)),
        Value::Time(_) => Ok(Value::Int(5)),
        Value::SmallDateTime(_) => Ok(Value::Int(4)),
        Value::DateTimeOffset(_) => Ok(Value::Int(10)),
        Value::UniqueIdentifier(_) => Ok(Value::Int(16)),
        Value::SqlVariant(inner) => eval_datalength_internal(inner),
    }
}

pub(crate) fn eval_unicode(
    args: &[Expr],
    row: &[ContextTable],
    ctx: &mut ExecutionContext,
    catalog: &dyn Catalog,
    storage: &dyn Storage,
    clock: &dyn Clock,
) -> Result<Value, DbError> {
    if args.is_empty() {
        return Err(DbError::Execution("UNICODE expects 1 argument".into()));
    }
    let val = eval_expr(&args[0], row, ctx, catalog, storage, clock)?;
    if val.is_null() {
        return Ok(Value::Null);
    }
    let s = val.to_string_value();
    if let Some(c) = s.chars().next() {
        Ok(Value::Int(c as i32))
    } else {
        Ok(Value::Int(0))
    }
}
