use crate::column::OrderSpec;
use crate::values::Value;

// ============================================
// Database Configuration
// ============================================

#[derive(Debug, Clone)]
pub struct DbConfig {
    pub driver: DriverType,
    pub host: Option<String>,
    pub port: Option<u16>,
    pub database: String,
    pub user: Option<String>,
    pub password: Option<String>,
    pub max_connections: Option<u32>,
    pub min_connections: Option<u32>,
    pub idle_timeout: Option<std::time::Duration>,
    pub max_lifetime: Option<std::time::Duration>,
    pub query_timeout: Option<u64>,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum DriverType {
    Postgres,
    Mysql,
    Sqlite,
}

impl DbConfig {
    pub fn postgres(host: &str, port: u16, database: &str, user: &str, password: &str) -> Self {
        Self {
            driver: DriverType::Postgres,
            host: Some(host.to_string()),
            port: Some(port),
            database: database.to_string(),
            user: Some(user.to_string()),
            password: Some(password.to_string()),
            max_connections: None,
            min_connections: None,
            idle_timeout: None,
            max_lifetime: None,
            query_timeout: None,
        }
    }

    pub fn mysql(host: &str, port: u16, database: &str, user: &str, password: &str) -> Self {
        Self {
            driver: DriverType::Mysql,
            host: Some(host.to_string()),
            port: Some(port),
            database: database.to_string(),
            user: Some(user.to_string()),
            password: Some(password.to_string()),
            max_connections: None,
            min_connections: None,
            idle_timeout: None,
            max_lifetime: None,
            query_timeout: None,
        }
    }

    pub fn sqlite(path: &str) -> Self {
        Self {
            driver: DriverType::Sqlite,
            host: None,
            port: None,
            database: path.to_string(),
            user: None,
            password: None,
            max_connections: None,
            min_connections: None,
            idle_timeout: None,
            max_lifetime: None,
            query_timeout: None,
        }
    }

    pub fn connection_string(&self) -> String {
        match self.driver {
            DriverType::Postgres => {
                format!(
                    "postgres://{}:{}@{}:{}/{}",
                    self.user.as_deref().unwrap_or("postgres"),
                    self.password.as_deref().unwrap_or(""),
                    self.host.as_deref().unwrap_or("localhost"),
                    self.port.unwrap_or(5432),
                    self.database,
                )
            }
            DriverType::Mysql => {
                format!(
                    "mysql://{}:{}@{}:{}/{}",
                    self.user.as_deref().unwrap_or("root"),
                    self.password.as_deref().unwrap_or(""),
                    self.host.as_deref().unwrap_or("localhost"),
                    self.port.unwrap_or(3306),
                    self.database,
                )
            }
            DriverType::Sqlite => {
                format!("sqlite:{}", self.database)
            }
        }
    }
}

// ============================================
// Database Configuration Options
// ============================================

#[derive(Debug, Clone)]
pub struct DbConfigOptions {
    pub writer_config: Option<DbConfig>,
    pub find_hard_limit: Option<usize>,
    pub has_many_hard_limit: Option<usize>,
    pub use_writer_after_transaction: bool,
    pub writer_sticky_duration_ms: u64,
}

impl Default for DbConfigOptions {
    fn default() -> Self {
        Self {
            writer_config: None,
            find_hard_limit: None,
            has_many_hard_limit: None,
            use_writer_after_transaction: true,
            writer_sticky_duration_ms: 5000,
        }
    }
}

// ============================================
// Query Options
// ============================================

/// Macro to build SelectOptions with minimal syntax.
///
/// ```ignore
/// db.find::<User>(&conds, Some(&opts!(limit: 100, order: c.created_at.desc()))).await?;
/// db.find::<User>(&conds, Some(&opts!(limit: 10, offset: 20, for_update: true))).await?;
/// ```
#[macro_export]
macro_rules! opts {
    ( $($key:ident : $val:expr),* $(,)? ) => {{
        #[allow(unused_mut)]
        let mut _opts = $crate::types::SelectOptions::default();
        $( opts!(@field _opts, $key, $val); )*
        _opts
    }};
    (@field $o:ident, limit, $val:expr) => { $o.limit = Some($val); };
    (@field $o:ident, offset, $val:expr) => { $o.offset = Some($val); };
    (@field $o:ident, order, [$($val:expr),* $(,)?]) => { $o.order = Some(vec![$($val),*]); };
    (@field $o:ident, order, $val:expr) => { $o.order = Some(vec![$val]); };
    (@field $o:ident, for_update, $val:expr) => { $o.for_update = Some($val); };
    (@field $o:ident, for_share, $val:expr) => { $o.for_share = Some($val); };
    (@field $o:ident, select, $val:expr) => { $o.select = Some($val.to_string()); };
    (@field $o:ident, group, $val:expr) => { $o.group = Some($val.to_string()); };
    (@field $o:ident, having, $val:expr) => { $o.having = Some($val.to_string()); };
    (@field $o:ident, table_name, $val:expr) => { $o.table_name = Some($val.to_string()); };
    (@field $o:ident, order_raw, $val:expr) => { $o.order_raw = Some($val.to_string()); };
}

#[derive(Debug, Clone, Default)]
pub struct SelectOptions {
    pub order: Option<Vec<OrderSpec>>,
    pub order_raw: Option<String>,
    pub limit: Option<usize>,
    pub offset: Option<usize>,
    pub select: Option<String>,
    pub group: Option<String>,
    pub having: Option<String>,
    pub table_name: Option<String>,
    pub append: Option<String>,
    pub for_update: Option<bool>,
    pub for_share: Option<bool>,
    /// JOIN clauses (e.g. "JOIN profiles ON users.id = profiles.user_id")
    pub join: Option<Vec<String>>,
    /// LEFT JOIN clauses (e.g. "LEFT JOIN other ON users.id = other.user_id")
    pub left_join: Option<Vec<String>>,
    pub join_params: Vec<Value>,
    /// CTE clauses for WITH clause. When multiple, generates: WITH cte1 AS (...), cte2 AS (...)
    pub cte: Option<Vec<CteClause>>,
}

#[derive(Debug, Clone)]
pub struct CteClause {
    pub name: String,
    pub sql: String,
    pub params: Vec<Value>,
}

/// Create SelectOptions with a parameterized query as a CTE.
/// Use for query-based models with dynamic parameters (e.g. date ranges).
/// The CTE is named "t"; the model's table_name in options should match if using a different alias.
///
/// # Example
/// ```ignore
/// let opts = with_query(
///     "SELECT user_id, SUM(amount) as total FROM orders WHERE created_at >= ? AND created_at < ? GROUP BY user_id",
///     vec![Value::Text("2024-01-01".into()), Value::Text("2024-02-01".into())],
/// );
/// let reports = find::<MonthlyReport, _>(executor, &[], Some(&opts), None).await?;
/// ```
pub fn with_query(query: &str, params: Vec<Value>) -> SelectOptions {
    SelectOptions {
        cte: Some(vec![CteClause {
            name: "t".to_string(),
            sql: query.to_string(),
            params,
        }]),
        table_name: Some("t".to_string()),
        ..Default::default()
    }
}

#[derive(Debug, Clone, Default)]
pub struct InsertOptions {
    pub table_name: Option<String>,
    pub returning: bool,
    pub on_conflict_columns: Vec<String>,
    pub on_conflict_update: Option<ConflictUpdate>,
    pub on_conflict_ignore: bool,
}

#[derive(Debug, Clone)]
pub enum ConflictUpdate {
    All,
    Columns(Vec<String>),
}

#[derive(Debug, Clone, Default)]
pub struct UpdateOptions {
    pub table_name: Option<String>,
    pub returning: bool,
}

#[derive(Debug, Clone, Default)]
pub struct DeleteOptions {
    pub table_name: Option<String>,
    pub returning: bool,
}

#[derive(Debug, Clone)]
pub struct UpdateManyOptions {
    pub key_columns: Vec<String>,
    pub returning: bool,
}

impl UpdateManyOptions {
    /// Create with `&[&str]` for key columns (avoids `vec!["id".to_string()]`).
    pub fn new(key_columns: &[&str], returning: bool) -> Self {
        Self {
            key_columns: key_columns.iter().map(|s| (*s).to_string()).collect(),
            returning,
        }
    }
}

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

    #[test]
    fn test_opts_macro_limit() {
        let opts = opts!(limit: 100);
        assert_eq!(opts.limit, Some(100));
        assert!(opts.order.is_none());
    }

    #[test]
    fn test_opts_macro_limit_offset() {
        let opts = opts!(limit: 10, offset: 20);
        assert_eq!(opts.limit, Some(10));
        assert_eq!(opts.offset, Some(20));
    }

    #[test]
    fn test_opts_macro_order() {
        let col: Column<i64> = Column::new("users", "created_at");
        let opts = opts!(order: col.desc());
        assert_eq!(opts.order.as_ref().map(|v| v.len()), Some(1));
    }

    #[test]
    fn test_update_many_options_new() {
        let opts = UpdateManyOptions::new(&["id"], false);
        assert_eq!(opts.key_columns, vec!["id"]);
        assert!(!opts.returning);
    }
}

// ============================================
// Transaction Options
// ============================================

#[derive(Debug, Clone)]
pub struct TransactionOptions {
    pub retry_on_error: bool,
    pub retry_limit: u32,
    pub retry_duration_ms: u64,
    pub rollback_only: bool,
    pub use_writer_after_transaction: Option<bool>,
}

impl Default for TransactionOptions {
    fn default() -> Self {
        Self {
            retry_on_error: false,
            retry_limit: 3,
            retry_duration_ms: 100,
            rollback_only: false,
            use_writer_after_transaction: None,
        }
    }
}

// ============================================
// Query Result Types
// ============================================

/// Result of a write operation returning primary keys.
#[derive(Debug, Clone)]
pub struct PkeyResult {
    pub key_columns: Vec<String>,
    pub values: Vec<Vec<Value>>,
}

impl PkeyResult {
    pub fn is_empty(&self) -> bool {
        self.values.is_empty()
    }

    pub fn len(&self) -> usize {
        self.values.len()
    }
}

/// Result of a raw SQL execution.
#[derive(Debug, Clone)]
pub struct ExecuteResult {
    pub rows_affected: u64,
}

// ============================================
// Limit Configuration
// ============================================

#[derive(Debug, Clone, Default)]
pub struct LimitConfig {
    pub find_hard_limit: Option<usize>,
    pub has_many_hard_limit: Option<usize>,
}
