use std::marker::PhantomData;

use crate::condition::Condition;
use crate::values::Value;

/// A typed column reference for a model field.
///
/// `Column<T>` does not carry condition-builder methods (no `.eq()`, `.gt()`, etc.).
/// Equality conditions use tuple syntax: `(column, value)`.
/// Operator conditions use the `sql!()` macro.
#[derive(Debug)]
pub struct Column<T> {
    table_name: &'static str,
    column_name: &'static str,
    _phantom: PhantomData<T>,
}

impl<T> Copy for Column<T> {}

impl<T> Clone for Column<T> {
    fn clone(&self) -> Self {
        *self
    }
}

impl<T> Column<T> {
    pub const fn new(table_name: &'static str, column_name: &'static str) -> Self {
        Self {
            table_name,
            column_name,
            _phantom: PhantomData,
        }
    }

    pub fn table(&self) -> &'static str {
        self.table_name
    }

    pub fn name(&self) -> &'static str {
        self.column_name
    }

    /// Qualified name: `"table_name"."column_name"`
    pub fn qualified(&self) -> String {
        format!("{}.{}", self.table_name, self.column_name)
    }

    pub fn asc(&self) -> OrderSpec {
        OrderSpec {
            column_name: self.column_name,
            table_name: self.table_name,
            direction: OrderDirection::Asc,
            nulls: None,
        }
    }

    pub fn desc(&self) -> OrderSpec {
        OrderSpec {
            column_name: self.column_name,
            table_name: self.table_name,
            direction: OrderDirection::Desc,
            nulls: None,
        }
    }

    pub fn asc_nulls_first(&self) -> OrderSpec {
        OrderSpec {
            column_name: self.column_name,
            table_name: self.table_name,
            direction: OrderDirection::Asc,
            nulls: Some(NullsOrder::First),
        }
    }

    pub fn desc_nulls_last(&self) -> OrderSpec {
        OrderSpec {
            column_name: self.column_name,
            table_name: self.table_name,
            direction: OrderDirection::Desc,
            nulls: Some(NullsOrder::Last),
        }
    }
}

impl<T> Column<T> {
    /// Create a column-value pair for INSERT/UPDATE.
    /// Accepts any type that converts to Value (e.g. `&str`, `i64`, `bool`).
    pub fn set(&self, value: impl Into<Value>) -> ColumnValue {
        ColumnValue {
            column_name: self.column_name,
            table_name: self.table_name,
            value: ColumnValueKind::Value(value.into()),
        }
    }

    /// Skip this column in INSERT/UPDATE (exclude from SQL entirely).
    pub fn skip(&self) -> ColumnValue {
        ColumnValue {
            column_name: self.column_name,
            table_name: self.table_name,
            value: ColumnValueKind::Skip,
        }
    }

    /// Set if Some, skip if None. For declarative SKIP pattern.
    pub fn optional(&self, value: Option<impl Into<Value>>) -> ColumnValue {
        match value {
            Some(v) => self.set(v),
            None => self.skip(),
        }
    }

    /// Set this column to NULL in INSERT/UPDATE.
    pub fn set_null(&self) -> ColumnValue {
        ColumnValue {
            column_name: self.column_name,
            table_name: self.table_name,
            value: ColumnValueKind::Value(Value::Null),
        }
    }
}

/// Equality condition from tuple `(Column<T>, value)`.
impl<T: Into<Value>> Column<T> {
    pub fn eq_condition(&self, value: T) -> Condition {
        Condition::Eq {
            column: self.column_name.to_string(),
            table: Some(self.table_name.to_string()),
            value: value.into(),
        }
    }
}

// ============================================
// ColumnValue (for INSERT/UPDATE SET clauses)
// ============================================

#[derive(Debug, Clone)]
pub struct ColumnValue {
    pub column_name: &'static str,
    pub table_name: &'static str,
    pub value: ColumnValueKind,
}

#[derive(Debug, Clone)]
pub enum ColumnValueKind {
    Value(Value),
    Skip,
}

impl ColumnValue {
    pub fn is_skip(&self) -> bool {
        matches!(self.value, ColumnValueKind::Skip)
    }
}

// ============================================
// OrderSpec
// ============================================

#[derive(Debug, Clone)]
pub struct OrderSpec {
    pub column_name: &'static str,
    pub table_name: &'static str,
    pub direction: OrderDirection,
    pub nulls: Option<NullsOrder>,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum OrderDirection {
    Asc,
    Desc,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum NullsOrder {
    First,
    Last,
}

impl OrderSpec {
    pub fn to_sql(&self) -> String {
        let dir = match self.direction {
            OrderDirection::Asc => " ASC",
            OrderDirection::Desc => " DESC",
        };
        let nulls = match self.nulls {
            Some(NullsOrder::First) => " NULLS FIRST",
            Some(NullsOrder::Last) => " NULLS LAST",
            None => "",
        };
        format!("{}{}{}", self.column_name, dir, nulls)
    }
}

/// Combine multiple OrderSpecs into ORDER BY clause.
pub fn order_to_sql(specs: &[OrderSpec]) -> String {
    specs
        .iter()
        .map(|s| s.to_sql())
        .collect::<Vec<_>>()
        .join(", ")
}

// ============================================
// IntoCondition trait for tuple API
// ============================================

/// Trait that converts `(Column<T>, value)` tuples into `Condition`.
pub trait IntoCondition {
    fn into_condition(self) -> Condition;
}

impl<T: Into<Value>> IntoCondition for (&Column<T>, T) {
    fn into_condition(self) -> Condition {
        let (col, val) = self;
        Condition::Eq {
            column: col.column_name.to_string(),
            table: Some(col.table_name.to_string()),
            value: val.into(),
        }
    }
}

impl<T, V: Into<Value>> IntoCondition for (Column<T>, V) {
    fn into_condition(self) -> Condition {
        let (col, val) = self;
        Condition::Eq {
            column: col.column_name.to_string(),
            table: Some(col.table_name.to_string()),
            value: val.into(),
        }
    }
}

impl IntoCondition for Condition {
    fn into_condition(self) -> Condition {
        self
    }
}

/// Build a `Vec<Condition>` from tuple conditions, `sql!()` results, or raw `Condition` values.
///
/// Eliminates the need to call `.into_condition()` on each item manually.
///
/// ```ignore
/// let c = User::columns();
/// let conds = conds![
///     (c.is_active, true),
///     (c.email, "john@example.com"),
///     sql!(c.age > {18}),
/// ];
/// ```
#[macro_export]
macro_rules! conds {
    [ $($item:expr),* $(,)? ] => {
        vec![ $($crate::column::IntoCondition::into_condition($item)),* ]
    };
}

/// Build a `Vec<ColumnValue>` from `[column, value]` pairs for INSERT/UPDATE.
///
/// Use `?` prefix for Option values: set if Some, skip if None.
///
/// ```ignore
/// let c = User::columns();
/// create::<User, _>(&tx, &set![
///     [c.name, "John"],
///     [c.email, ?input.email],  // Option<String> → set if Some, skip if None
///     [c.is_active, true],
/// ], None).await?;
/// ```
#[macro_export]
macro_rules! set {
    [] => { vec![] };
    [ $($items:tt),* $(,)? ] => {{
        #[allow(clippy::vec_init_then_push)]
        {
            let mut _result: Vec<$crate::column::ColumnValue> = Vec::new();
            $(
                set!(@item _result, $items);
            )*
            _result
        }
    }};
    (@item $result:ident, [$col:expr, ? $val:expr]) => {
        match $val {
            Some(_v) => $result.push($col.set(_v)),
            None => $result.push($col.skip()),
        }
    };
    (@item $result:ident, [$col:expr, $val:expr]) => {
        $result.push($col.set($val));
    };
}

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

    #[test]
    fn test_column_basic() {
        let col: Column<i64> = Column::new("users", "id");
        assert_eq!(col.table(), "users");
        assert_eq!(col.name(), "id");
        assert_eq!(col.qualified(), "users.id");
    }

    #[test]
    fn test_column_order() {
        let col: Column<String> = Column::new("users", "name");
        let asc = col.asc();
        assert_eq!(asc.to_sql(), "name ASC");
        let desc = col.desc();
        assert_eq!(desc.to_sql(), "name DESC");
        let asc_nf = col.asc_nulls_first();
        assert_eq!(asc_nf.to_sql(), "name ASC NULLS FIRST");
    }

    #[test]
    fn test_order_spec_desc_nulls_last() {
        let col: Column<i64> = Column::new("items", "created_at");
        let spec = col.desc_nulls_last();
        assert_eq!(spec.to_sql(), "created_at DESC NULLS LAST");
    }

    #[test]
    fn test_order_to_sql() {
        let col1: Column<i64> = Column::new("users", "id");
        let col2: Column<String> = Column::new("users", "name");
        let specs = [col1.asc(), col2.desc()];
        assert_eq!(order_to_sql(&specs), "id ASC, name DESC");
    }

    #[test]
    fn test_column_qualified() {
        let col: Column<bool> = Column::new("posts", "is_published");
        assert_eq!(col.qualified(), "posts.is_published");
    }

    #[test]
    fn test_column_set() {
        let col: Column<i64> = Column::new("users", "id");
        let cv = col.set(42i64);
        assert_eq!(cv.column_name, "id");
        assert!(!cv.is_skip());
    }

    #[test]
    fn test_column_set_string() {
        let col: Column<String> = Column::new("users", "email");
        let cv = col.set("a@b.com".to_string());
        assert!(!cv.is_skip());
        match &cv.value {
            ColumnValueKind::Value(v) => assert_eq!(v, &Value::Text("a@b.com".to_string())),
            ColumnValueKind::Skip => panic!("Expected Value"),
        }
    }

    #[test]
    fn test_column_set_null() {
        let col: Column<i64> = Column::new("users", "parent_id");
        let cv = col.set_null();
        assert!(!cv.is_skip());
        match &cv.value {
            ColumnValueKind::Value(v) => assert!(v.is_null()),
            ColumnValueKind::Skip => panic!("Expected Value"),
        }
    }

    #[test]
    fn test_column_skip() {
        let col: Column<i64> = Column::new("users", "id");
        let cv = col.skip();
        assert!(cv.is_skip());
    }

    #[test]
    fn test_tuple_into_condition() {
        let col: Column<i64> = Column::new("users", "id");
        let cond = (&col, 42i64).into_condition();
        match cond {
            Condition::Eq {
                column,
                table,
                value,
            } => {
                assert_eq!(column, "id");
                assert_eq!(table, Some("users".to_string()));
                assert_eq!(value, Value::Int(42));
            }
            _ => panic!("Expected Eq condition"),
        }
    }

    #[test]
    fn test_tuple_into_condition_string() {
        let col: Column<String> = Column::new("users", "name");
        let cond = (&col, "Alice".to_string()).into_condition();
        match cond {
            Condition::Eq {
                column,
                table,
                value,
            } => {
                assert_eq!(column, "name");
                assert_eq!(table, Some("users".to_string()));
                assert_eq!(value, Value::Text("Alice".to_string()));
            }
            _ => panic!("Expected Eq condition"),
        }
    }

    #[test]
    fn test_eq_condition() {
        let col: Column<bool> = Column::new("posts", "is_active");
        let cond = col.eq_condition(true);
        match cond {
            Condition::Eq {
                column,
                table,
                value,
            } => {
                assert_eq!(column, "is_active");
                assert_eq!(table, Some("posts".to_string()));
                assert_eq!(value, Value::Bool(true));
            }
            _ => panic!("Expected Eq condition"),
        }
    }

    #[test]
    fn test_owned_tuple_into_condition() {
        let col: Column<bool> = Column::new("users", "is_active");
        let cond = (col, true).into_condition();
        match cond {
            Condition::Eq {
                column,
                table,
                value,
            } => {
                assert_eq!(column, "is_active");
                assert_eq!(table, Some("users".to_string()));
                assert_eq!(value, Value::Bool(true));
            }
            _ => panic!("Expected Eq condition"),
        }
    }

    #[test]
    fn test_owned_tuple_str_into_condition() {
        let col: Column<String> = Column::new("users", "email");
        let cond = (col, "john@example.com").into_condition();
        match cond {
            Condition::Eq { column, value, .. } => {
                assert_eq!(column, "email");
                assert_eq!(value, Value::Text("john@example.com".to_string()));
            }
            _ => panic!("Expected Eq condition"),
        }
    }

    #[test]
    fn test_column_is_copy() {
        let col: Column<i64> = Column::new("users", "id");
        let _a = col;
        let _b = col;
        assert_eq!(_a.name(), _b.name());
    }

    #[test]
    fn test_conds_macro() {
        let col_active: Column<bool> = Column::new("users", "is_active");
        let col_name: Column<String> = Column::new("users", "name");
        let conditions = conds![(col_active, true), (col_name, "Alice"),];
        assert_eq!(conditions.len(), 2);
        match &conditions[0] {
            Condition::Eq { column, value, .. } => {
                assert_eq!(column, "is_active");
                assert_eq!(value, &Value::Bool(true));
            }
            _ => panic!("Expected Eq"),
        }
        match &conditions[1] {
            Condition::Eq { column, value, .. } => {
                assert_eq!(column, "name");
                assert_eq!(value, &Value::Text("Alice".to_string()));
            }
            _ => panic!("Expected Eq"),
        }
    }

    #[test]
    fn test_conds_macro_with_raw_condition() {
        let col: Column<bool> = Column::new("t", "a");
        let raw = Condition::Raw {
            sql: "x > 1".to_string(),
            params: vec![],
        };
        let conditions = conds![(col, true), raw];
        assert_eq!(conditions.len(), 2);
    }

    #[test]
    fn test_conds_macro_empty() {
        let conditions: Vec<Condition> = conds![];
        assert!(conditions.is_empty());
    }

    #[test]
    fn test_set_macro() {
        let col_name: Column<String> = Column::new("users", "name");
        let col_active: Column<bool> = Column::new("users", "is_active");
        let values = set![[col_name, "Alice"], [col_active, true],];
        assert_eq!(values.len(), 2);
        assert_eq!(values[0].column_name, "name");
        assert_eq!(values[1].column_name, "is_active");
    }

    #[test]
    fn test_set_macro_empty() {
        let values: Vec<ColumnValue> = set![];
        assert!(values.is_empty());
    }

    #[test]
    fn test_set_macro_optional_some() {
        let col_name: Column<String> = Column::new("users", "name");
        let col_email: Column<String> = Column::new("users", "email");
        let input_email: Option<String> = Some("alice@example.com".to_string());
        let values = set![
            [col_name, "Alice"],
            [col_email, ?input_email],
        ];
        assert_eq!(values.len(), 2);
        assert_eq!(values[0].column_name, "name");
        assert_eq!(values[1].column_name, "email");
        assert!(!values[1].is_skip());
    }

    #[test]
    fn test_set_macro_optional_none() {
        let col_name: Column<String> = Column::new("users", "name");
        let col_email: Column<String> = Column::new("users", "email");
        let input_email: Option<String> = None;
        let values = set![
            [col_name, "Alice"],
            [col_email, ?input_email],
        ];
        assert_eq!(values.len(), 2);
        assert_eq!(values[0].column_name, "name");
        assert_eq!(values[1].column_name, "email");
        assert!(values[1].is_skip());
    }

    #[test]
    fn test_column_optional_method() {
        let col: Column<String> = Column::new("users", "email");
        let some_val = col.optional(Some("test@example.com".to_string()));
        assert!(!some_val.is_skip());
        let none_val: ColumnValue = col.optional(None::<String>);
        assert!(none_val.is_skip());
    }

    #[test]
    fn test_condition_into_condition_passthrough() {
        let raw = Condition::Raw {
            sql: "1=1".to_string(),
            params: vec![],
        };
        let result = raw.into_condition();
        match result {
            Condition::Raw { sql, params } => {
                assert_eq!(sql, "1=1");
                assert!(params.is_empty());
            }
            _ => panic!("Expected Raw condition"),
        }
    }
}
