use std::collections::{HashMap, HashSet};

use super::{OnConflict, OnConflictAction, SqlBuilder};
use crate::types::DriverType;

/// SQLite-specific SQL builder.
pub struct SqliteSqlBuilder;

impl SqlBuilder for SqliteSqlBuilder {
    fn driver_type(&self) -> DriverType {
        DriverType::Sqlite
    }

    fn supports_returning(&self) -> bool {
        true
    }

    fn rewrite_placeholders(&self, sql: &str) -> String {
        sql.to_string()
    }

    fn build_insert(
        &self,
        table: &str,
        columns: &[&str],
        row_count: usize,
        on_conflict: Option<&OnConflict>,
        returning_columns: Option<&[&str]>,
        _column_types: Option<&[&str]>,
    ) -> (String, usize) {
        let cols = columns.join(", ");
        let total_params = columns.len() * row_count;

        let row_placeholder = format!("({})", vec!["?"; columns.len()].join(", "));
        let value_rows: Vec<&str> = (0..row_count).map(|_| row_placeholder.as_str()).collect();

        let mut sql = if let Some(oc) = on_conflict {
            match &oc.action {
                OnConflictAction::DoNothing => {
                    // SQLite: INSERT OR IGNORE INTO ...
                    format!(
                        "INSERT OR IGNORE INTO {} ({}) VALUES {}",
                        table,
                        cols,
                        value_rows.join(", ")
                    )
                }
                OnConflictAction::DoUpdate(update_cols) => {
                    // SQLite: ... ON CONFLICT (cols) DO UPDATE SET col = excluded.col (lowercase)
                    // Fallback: when update_cols is empty, use all inserted columns (matches PG driver)
                    let cols_to_update: Vec<&str> = if update_cols.is_empty() {
                        columns.to_vec()
                    } else {
                        update_cols.to_vec()
                    };
                    let update_clause: Vec<String> = cols_to_update
                        .iter()
                        .map(|c| format!("{} = excluded.{}", c, c))
                        .collect();
                    format!(
                        "INSERT INTO {} ({}) VALUES {} ON CONFLICT ({}) DO UPDATE SET {}",
                        table,
                        cols,
                        value_rows.join(", "),
                        oc.columns.join(", "),
                        update_clause.join(", ")
                    )
                }
            }
        } else {
            format!(
                "INSERT INTO {} ({}) VALUES {}",
                table,
                cols,
                value_rows.join(", ")
            )
        };

        // SQLite RETURNING uses table.col format (matching TS buildReturning)
        if let Some(ret_cols) = returning_columns {
            let prefixed: Vec<String> = ret_cols
                .iter()
                .map(|c| format!("{}.{}", table, c))
                .collect();
            sql.push_str(&format!(" RETURNING {}", prefixed.join(", ")));
        }

        (sql, total_params)
    }

    fn build_update_many(
        &self,
        table: &str,
        key_columns: &[&str],
        value_columns: &[&str],
        row_count: usize,
        returning_columns: Option<&[&str]>,
        _column_types: Option<&[&str]>,
        skip_map: Option<&HashMap<usize, HashSet<String>>>,
    ) -> (String, usize) {
        // SQLite uses CASE WHEN for batch updates (matching TS sqliteSqlBuilder.buildUpdateMany)
        // When skipped: WHEN keyCondition THEN table.col (keep existing value)
        // When not skipped: WHEN keyCondition THEN ?
        //
        // UPDATE users SET
        //   name = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END,
        //   email = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END
        // WHERE id IN (?, ?)

        let mut total_params = 0;

        let set_clauses: Vec<String> = value_columns
            .iter()
            .map(|col| {
                let when_clauses: Vec<String> = (0..row_count)
                    .map(|row_idx| {
                        let key_cond = if key_columns.len() == 1 {
                            total_params += 1;
                            format!("{} = ?", key_columns[0])
                        } else {
                            let parts: Vec<String> = key_columns
                                .iter()
                                .map(|k| {
                                    total_params += 1;
                                    format!("{} = ?", k)
                                })
                                .collect();
                            format!("({})", parts.join(" AND "))
                        };
                        let is_skipped = skip_map
                            .and_then(|m| m.get(&row_idx))
                            .map(|s| s.contains(*col))
                            .unwrap_or(false);
                        if is_skipped {
                            format!("WHEN {} THEN {}.{}", key_cond, table, col)
                        } else {
                            total_params += 1;
                            format!("WHEN {} THEN ?", key_cond)
                        }
                    })
                    .collect();
                format!("{} = CASE {} END", col, when_clauses.join(" "))
            })
            .collect();

        let where_clause = if key_columns.len() == 1 {
            let placeholders = vec!["?"; row_count].join(", ");
            total_params += row_count;
            format!("{} IN ({})", key_columns[0], placeholders)
        } else {
            let tuple_ph = format!("({})", vec!["?"; key_columns.len()].join(", "));
            let tuples: Vec<&str> = (0..row_count).map(|_| tuple_ph.as_str()).collect();
            total_params += row_count * key_columns.len();
            format!("({}) IN ({})", key_columns.join(", "), tuples.join(", "))
        };

        let mut sql = format!(
            "UPDATE {} SET {} WHERE {}",
            table,
            set_clauses.join(", "),
            where_clause,
        );

        // SQLite RETURNING uses table.col format
        if let Some(ret_cols) = returning_columns {
            let prefixed: Vec<String> = ret_cols
                .iter()
                .map(|c| format!("{}.{}", table, c))
                .collect();
            sql.push_str(&format!(" RETURNING {}", prefixed.join(", ")));
        }

        (sql, total_params)
    }

    fn build_find_by_pkeys(
        &self,
        table: &str,
        pkey_columns: &[&str],
        pkey_count: usize,
        select_column: &str,
        _column_types: Option<&[&str]>,
    ) -> (String, usize) {
        if pkey_columns.len() == 1 {
            // Single PK: WHERE id IN (?, ?, ...)
            let placeholders = vec!["?"; pkey_count].join(", ");
            let sql = format!(
                "SELECT {} FROM {} WHERE {} IN ({})",
                select_column, table, pkey_columns[0], placeholders
            );
            return (sql, pkey_count);
        }

        // Composite PK: WITH v(cols) AS (VALUES (...), (...))
        //               SELECT t.* FROM table AS t JOIN v ON t.col = v.col
        let total_params = pkey_columns.len() * pkey_count;
        let row_placeholder = format!("({})", vec!["?"; pkey_columns.len()].join(", "));
        let value_rows: Vec<&str> = (0..pkey_count).map(|_| row_placeholder.as_str()).collect();

        let on_clause: Vec<String> = pkey_columns
            .iter()
            .map(|col| format!("t.{} = v.{}", col, col))
            .collect();

        let select_col = if select_column == "*" {
            "t.*"
        } else {
            select_column
        };

        let sql = format!(
            "WITH v({}) AS (VALUES {}) SELECT {} FROM {} AS t JOIN v ON {}",
            pkey_columns.join(", "),
            value_rows.join(", "),
            select_col,
            table,
            on_clause.join(" AND "),
        );

        (sql, total_params)
    }
}

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

    // ========================================
    // INSERT tests
    // ========================================

    #[test]
    fn test_build_insert_single() {
        let builder = SqliteSqlBuilder;
        let (sql, params) = builder.build_insert("users", &["name", "email"], 1, None, None, None);
        assert_eq!(sql, "INSERT INTO users (name, email) VALUES (?, ?)");
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_multi() {
        let builder = SqliteSqlBuilder;
        let (sql, params) = builder.build_insert("users", &["name", "email"], 2, None, None, None);
        assert_eq!(sql, "INSERT INTO users (name, email) VALUES (?, ?), (?, ?)");
        assert_eq!(params, 4);
    }

    #[test]
    fn test_build_insert_with_returning() {
        // SQLite RETURNING uses table.col format (matching TS buildReturning)
        let builder = SqliteSqlBuilder;
        let (sql, params) = builder.build_insert("users", &["name"], 1, None, Some(&["id"]), None);
        assert_eq!(
            sql,
            "INSERT INTO users (name) VALUES (?) RETURNING users.id"
        );
        assert_eq!(params, 1);
    }

    #[test]
    fn test_build_insert_or_ignore() {
        // TS: INSERT OR IGNORE INTO users (name, email) VALUES (?, ?)
        let builder = SqliteSqlBuilder;
        let oc = OnConflict {
            columns: &["email"],
            action: OnConflictAction::DoNothing,
        };
        let (sql, params) =
            builder.build_insert("users", &["name", "email"], 1, Some(&oc), None, None);
        assert_eq!(
            sql,
            "INSERT OR IGNORE INTO users (name, email) VALUES (?, ?)"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_on_conflict_do_update() {
        // TS: INSERT INTO users (...) VALUES (...)
        //     ON CONFLICT (email) DO UPDATE SET name = excluded.name
        let builder = SqliteSqlBuilder;
        let oc = OnConflict {
            columns: &["email"],
            action: OnConflictAction::DoUpdate(&["name"]),
        };
        let (sql, params) =
            builder.build_insert("users", &["name", "email"], 1, Some(&oc), None, None);
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) VALUES (?, ?) ON CONFLICT (email) DO UPDATE SET name = excluded.name"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_on_conflict_do_update_empty_cols_fallback() {
        // DoUpdate(&[]) should fall back to all inserted columns (matches PG driver)
        let builder = SqliteSqlBuilder;
        let oc = OnConflict {
            columns: &["email"],
            action: OnConflictAction::DoUpdate(&[]),
        };
        let (sql, params) =
            builder.build_insert("users", &["name", "email"], 1, Some(&oc), None, None);
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) VALUES (?, ?) ON CONFLICT (email) DO UPDATE SET name = excluded.name, email = excluded.email"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_on_conflict_with_returning() {
        let builder = SqliteSqlBuilder;
        let oc = OnConflict {
            columns: &["email"],
            action: OnConflictAction::DoUpdate(&["name"]),
        };
        let (sql, params) = builder.build_insert(
            "users",
            &["name", "email"],
            1,
            Some(&oc),
            Some(&["id", "name"]),
            None,
        );
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) VALUES (?, ?) ON CONFLICT (email) DO UPDATE SET name = excluded.name RETURNING users.id, users.name"
        );
        assert_eq!(params, 2);
    }

    // ========================================
    // UPDATE MANY tests (CASE WHEN pattern)
    // ========================================

    #[test]
    fn test_build_update_many_single_key() {
        // TS: UPDATE users SET
        //       name = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END,
        //       email = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END
        //     WHERE id IN (?, ?)
        let builder = SqliteSqlBuilder;
        let (sql, params) =
            builder.build_update_many("users", &["id"], &["name", "email"], 2, None, None, None);
        assert_eq!(
            sql,
            "UPDATE users SET name = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END, email = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END WHERE id IN (?, ?)"
        );
        // 2 update cols * 2 rows * (1 key + 1 value) + 2 WHERE params = 10
        assert_eq!(params, 10);
    }

    #[test]
    fn test_build_update_many_composite_key() {
        // TS: UPDATE user_roles SET
        //       level = CASE WHEN (user_id = ? AND role_id = ?) THEN ?
        //                    WHEN (user_id = ? AND role_id = ?) THEN ? END
        //     WHERE (user_id, role_id) IN ((?, ?), (?, ?))
        let builder = SqliteSqlBuilder;
        let (sql, params) = builder.build_update_many(
            "user_roles",
            &["user_id", "role_id"],
            &["level"],
            2,
            None,
            None,
            None,
        );
        assert_eq!(
            sql,
            "UPDATE user_roles SET level = CASE WHEN (user_id = ? AND role_id = ?) THEN ? WHEN (user_id = ? AND role_id = ?) THEN ? END WHERE (user_id, role_id) IN ((?, ?), (?, ?))"
        );
        // 1 update col * 2 rows * (2 keys + 1 value) + 2 rows * 2 keys = 10
        assert_eq!(params, 10);
    }

    #[test]
    fn test_build_update_many_with_returning() {
        // SQLite RETURNING uses table.col format
        let builder = SqliteSqlBuilder;
        let (sql, params) = builder.build_update_many(
            "users",
            &["id"],
            &["name"],
            2,
            Some(&["id", "name"]),
            None,
            None,
        );
        assert_eq!(
            sql,
            "UPDATE users SET name = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? END WHERE id IN (?, ?) RETURNING users.id, users.name"
        );
        // 1 update col * 2 rows * (1 key + 1 value) + 2 WHERE params = 6
        assert_eq!(params, 6);
    }

    #[test]
    fn test_build_update_many_single_row() {
        let builder = SqliteSqlBuilder;
        let (sql, params) =
            builder.build_update_many("users", &["id"], &["name", "email"], 1, None, None, None);
        assert_eq!(
            sql,
            "UPDATE users SET name = CASE WHEN id = ? THEN ? END, email = CASE WHEN id = ? THEN ? END WHERE id IN (?)"
        );
        // 2 update cols * 1 row * (1 key + 1 value) + 1 WHERE param = 5
        assert_eq!(params, 5);
    }

    // ========================================
    // FIND BY PKEYS tests
    // ========================================

    #[test]
    fn test_build_find_by_pkeys_single() {
        // TS: SELECT * FROM users WHERE id IN (?, ?, ?)
        let builder = SqliteSqlBuilder;
        let (sql, params) = builder.build_find_by_pkeys("users", &["id"], 3, "*", None);
        assert_eq!(sql, "SELECT * FROM users WHERE id IN (?, ?, ?)");
        assert_eq!(params, 3);
    }

    #[test]
    fn test_build_find_by_pkeys_composite() {
        // TS: WITH v(user_id, role_id) AS (VALUES (?, ?), (?, ?))
        //     SELECT t.* FROM user_roles AS t JOIN v ON t.user_id = v.user_id AND t.role_id = v.role_id
        let builder = SqliteSqlBuilder;
        let (sql, params) =
            builder.build_find_by_pkeys("user_roles", &["user_id", "role_id"], 2, "*", None);
        assert_eq!(
            sql,
            "WITH v(user_id, role_id) AS (VALUES (?, ?), (?, ?)) SELECT t.* FROM user_roles AS t JOIN v ON t.user_id = v.user_id AND t.role_id = v.role_id"
        );
        assert_eq!(params, 4);
    }

    #[test]
    fn test_build_find_by_pkeys_with_select_column() {
        let builder = SqliteSqlBuilder;
        let (sql, params) = builder.build_find_by_pkeys("users", &["id"], 2, "id, name", None);
        assert_eq!(sql, "SELECT id, name FROM users WHERE id IN (?, ?)");
        assert_eq!(params, 2);
    }
}
