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

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

/// PostgreSQL-specific SQL builder.
pub struct PostgresSqlBuilder;

impl PostgresSqlBuilder {
    fn get_type<'a>(column_types: Option<&'a [&'a str]>, index: usize) -> &'a str {
        column_types
            .and_then(|types| types.get(index).copied())
            .unwrap_or("text")
    }
}

impl SqlBuilder for PostgresSqlBuilder {
    fn driver_type(&self) -> DriverType {
        DriverType::Postgres
    }

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

    // NOTE: TS version uses ? placeholders and converts to $N at the driver layer,
    // same as this Rust implementation. The resulting SQL is equivalent.
    fn rewrite_placeholders(&self, sql: &str) -> String {
        let mut result = String::with_capacity(sql.len());
        let mut param_index = 0u32;
        let mut chars = sql.chars().peekable();

        while let Some(ch) = chars.next() {
            if ch == '?' {
                param_index += 1;
                result.push('$');
                result.push_str(&param_index.to_string());
            } else if ch == '\'' {
                result.push(ch);
                for c in chars.by_ref() {
                    result.push(c);
                    if c == '\'' {
                        break;
                    }
                }
            } else {
                result.push(ch);
            }
        }

        result
    }

    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(", ");

        // Multi-row: UNNEST-based insert (matching TS postgresSqlBuilder.buildInsert)
        if row_count > 1 {
            let total_params = columns.len();
            let mut unnest_parts = Vec::with_capacity(columns.len());
            let mut select_exprs = Vec::with_capacity(columns.len());

            for (i, col) in columns.iter().enumerate() {
                let pg_type = Self::get_type(column_types, i);
                unnest_parts.push(format!("${}::{}[]", i + 1, pg_type));
                select_exprs.push(format!("v.{}", col));
            }

            let mut sql = format!(
                "INSERT INTO {} ({}) SELECT {} FROM UNNEST({}) AS v({})",
                table,
                cols,
                select_exprs.join(", "),
                unnest_parts.join(", "),
                cols,
            );

            append_pg_on_conflict(&mut sql, on_conflict, columns);

            if let Some(ret_cols) = returning_columns {
                sql.push_str(&format!(" RETURNING {}", ret_cols.join(", ")));
            }

            return (sql, total_params);
        }

        // Single row: VALUES-based insert
        let total_params = columns.len();
        let placeholders: Vec<String> = (1..=columns.len()).map(|i| format!("${}", i)).collect();

        let mut sql = format!(
            "INSERT INTO {} ({}) VALUES ({})",
            table,
            cols,
            placeholders.join(", "),
        );

        append_pg_on_conflict(&mut sql, on_conflict, columns);

        if let Some(ret_cols) = returning_columns {
            sql.push_str(&format!(" RETURNING {}", ret_cols.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) {
        let all_columns: Vec<&str> = key_columns
            .iter()
            .chain(value_columns.iter())
            .copied()
            .collect();
        let mut total_params = all_columns.len();

        // Collect skip columns needed
        let skip_columns_needed: Vec<String> = skip_map
            .map(|m| {
                let mut set = HashSet::new();
                for skip_set in m.values() {
                    for col in skip_set {
                        set.insert(col.clone());
                    }
                }
                let mut v: Vec<String> = set.into_iter().collect();
                v.sort();
                v
            })
            .unwrap_or_default();

        let mut unnest_parts: Vec<String> = all_columns
            .iter()
            .enumerate()
            .map(|(i, _)| {
                let pg_type = Self::get_type(column_types, i);
                format!("${}::{}[]", i + 1, pg_type)
            })
            .collect();

        // Add skip flag columns to UNNEST
        for _ in &skip_columns_needed {
            total_params += 1;
            unnest_parts.push(format!("${}::boolean[]", total_params));
        }

        let all_unnest_cols: Vec<String> = all_columns
            .iter()
            .map(|s| (*s).to_string())
            .chain(skip_columns_needed.iter().map(|c| format!("_skip_{}", c)))
            .collect();

        let set_clause: Vec<String> = value_columns
            .iter()
            .map(|col| {
                if skip_columns_needed.contains(&(*col).to_string()) {
                    format!(
                        "{} = CASE WHEN v._skip_{} THEN t.{} ELSE v.{} END",
                        col, col, col, col
                    )
                } else {
                    format!("{} = v.{}", col, col)
                }
            })
            .collect();

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

        let mut sql = format!(
            "UPDATE {} AS t SET {} FROM UNNEST({}) AS v({}) WHERE {}",
            table,
            set_clause.join(", "),
            unnest_parts.join(", "),
            all_unnest_cols.join(", "),
            where_clause.join(" AND "),
        );

        if let Some(ret_cols) = returning_columns {
            let prefixed: Vec<String> = ret_cols.iter().map(|c| format!("t.{}", 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 {
            let pg_type = Self::get_type(column_types, 0);
            let sql = format!(
                "SELECT {} FROM {} WHERE {} = ANY($1::{}[])",
                select_column, table, pkey_columns[0], pg_type
            );
            return (sql, 1);
        }

        // Composite PK: WHERE (col1, col2) IN (SELECT * FROM UNNEST($1::type[], $2::type[]))
        let total_params = pkey_columns.len();
        let unnest_parts: Vec<String> = pkey_columns
            .iter()
            .enumerate()
            .map(|(i, _)| {
                let pg_type = Self::get_type(column_types, i);
                format!("${}::{}[]", i + 1, pg_type)
            })
            .collect();

        let sql = format!(
            "SELECT {} FROM {} WHERE ({}) IN (SELECT * FROM UNNEST({}))",
            select_column,
            table,
            pkey_columns.join(", "),
            unnest_parts.join(", "),
        );

        (sql, total_params)
    }
}

fn append_pg_on_conflict(sql: &mut String, on_conflict: Option<&OnConflict>, columns: &[&str]) {
    if let Some(oc) = on_conflict {
        sql.push_str(&format!(" ON CONFLICT ({})", oc.columns.join(", ")));
        match &oc.action {
            OnConflictAction::DoNothing => sql.push_str(" DO NOTHING"),
            OnConflictAction::DoUpdate(update_cols) => {
                let set_parts: Vec<String> = if update_cols.is_empty() {
                    columns
                        .iter()
                        .map(|c| format!("{} = EXCLUDED.{}", c, c))
                        .collect()
                } else {
                    update_cols
                        .iter()
                        .map(|c| format!("{} = EXCLUDED.{}", c, c))
                        .collect()
                };
                sql.push_str(&format!(" DO UPDATE SET {}", set_parts.join(", ")));
            }
        }
    }
}

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

    #[test]
    fn test_rewrite_placeholders() {
        let builder = PostgresSqlBuilder;
        assert_eq!(
            builder.rewrite_placeholders("SELECT * FROM users WHERE id = ? AND name = ?"),
            "SELECT * FROM users WHERE id = $1 AND name = $2"
        );
    }

    #[test]
    fn test_rewrite_placeholders_with_string_literal() {
        let builder = PostgresSqlBuilder;
        assert_eq!(
            builder.rewrite_placeholders("SELECT * FROM users WHERE name = 'what?' AND id = ?"),
            "SELECT * FROM users WHERE name = 'what?' AND id = $1"
        );
    }

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

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

    #[test]
    fn test_build_insert_single_with_returning() {
        let builder = PostgresSqlBuilder;
        let (sql, params) =
            builder.build_insert("users", &["name", "email"], 1, None, Some(&["id"]), None);
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_multi_unnest() {
        // TS: INSERT INTO users (name, email) SELECT v.name, v.email
        //     FROM UNNEST($1::text[], $2::text[]) AS v(name, email)
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_insert(
            "users",
            &["name", "email"],
            3,
            None,
            None,
            Some(&["text", "text"]),
        );
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) SELECT v.name, v.email FROM UNNEST($1::text[], $2::text[]) AS v(name, email)"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_multi_unnest_with_returning() {
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_insert(
            "users",
            &["name", "email"],
            3,
            None,
            Some(&["id", "name"]),
            Some(&["text", "text"]),
        );
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) SELECT v.name, v.email FROM UNNEST($1::text[], $2::text[]) AS v(name, email) RETURNING id, name"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_multi_unnest_mixed_types() {
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_insert(
            "users",
            &["id", "name", "score"],
            5,
            None,
            None,
            Some(&["int", "text", "numeric"]),
        );
        assert_eq!(
            sql,
            "INSERT INTO users (id, name, score) SELECT v.id, v.name, v.score FROM UNNEST($1::int[], $2::text[], $3::numeric[]) AS v(id, name, score)"
        );
        assert_eq!(params, 3);
    }

    #[test]
    fn test_build_insert_on_conflict_do_nothing() {
        // TS: INSERT INTO ... VALUES ... ON CONFLICT (email) DO NOTHING
        let builder = PostgresSqlBuilder;
        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 INTO users (name, email) VALUES ($1, $2) ON CONFLICT (email) DO NOTHING"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_on_conflict_do_update() {
        // TS: INSERT INTO ... VALUES ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
        let builder = PostgresSqlBuilder;
        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 ($1, $2) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_multi_unnest_on_conflict() {
        // TS: UNNEST-based insert with ON CONFLICT
        let builder = PostgresSqlBuilder;
        let oc = OnConflict {
            columns: &["email"],
            action: OnConflictAction::DoNothing,
        };
        let (sql, params) = builder.build_insert(
            "users",
            &["name", "email"],
            3,
            Some(&oc),
            None,
            Some(&["text", "text"]),
        );
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) SELECT v.name, v.email FROM UNNEST($1::text[], $2::text[]) AS v(name, email) ON CONFLICT (email) DO NOTHING"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_insert_multi_unnest_on_conflict_update_with_returning() {
        let builder = PostgresSqlBuilder;
        let oc = OnConflict {
            columns: &["email"],
            action: OnConflictAction::DoUpdate(&["name"]),
        };
        let (sql, params) = builder.build_insert(
            "users",
            &["name", "email"],
            3,
            Some(&oc),
            Some(&["id"]),
            Some(&["text", "text"]),
        );
        assert_eq!(
            sql,
            "INSERT INTO users (name, email) SELECT v.name, v.email FROM UNNEST($1::text[], $2::text[]) AS v(name, email) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name RETURNING id"
        );
        assert_eq!(params, 2);
    }

    // ========================================
    // UPDATE MANY tests
    // ========================================

    #[test]
    fn test_build_update_many() {
        // TS: UPDATE users AS t SET name = v.name, email = v.email
        //     FROM UNNEST($1::int[], $2::text[], $3::text[]) AS v(id, name, email)
        //     WHERE t.id = v.id
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_update_many(
            "users",
            &["id"],
            &["name", "email"],
            3,
            None,
            Some(&["int", "text", "text"]),
            None,
        );
        assert_eq!(
            sql,
            "UPDATE users AS t SET name = v.name, email = v.email FROM UNNEST($1::int[], $2::text[], $3::text[]) AS v(id, name, email) WHERE t.id = v.id"
        );
        assert_eq!(params, 3);
    }

    #[test]
    fn test_build_update_many_composite_key() {
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_update_many(
            "user_roles",
            &["user_id", "role_id"],
            &["level"],
            2,
            None,
            Some(&["int", "int", "int"]),
            None,
        );
        assert_eq!(
            sql,
            "UPDATE user_roles AS t SET level = v.level FROM UNNEST($1::int[], $2::int[], $3::int[]) AS v(user_id, role_id, level) WHERE t.user_id = v.user_id AND t.role_id = v.role_id"
        );
        assert_eq!(params, 3);
    }

    #[test]
    fn test_build_update_many_with_returning() {
        // TS: ... RETURNING t.id, t.name (prefixed with t. for UPDATE AS t alias)
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_update_many(
            "users",
            &["id"],
            &["name"],
            2,
            Some(&["id", "name"]),
            Some(&["int", "text"]),
            None,
        );
        assert_eq!(
            sql,
            "UPDATE users AS t SET name = v.name FROM UNNEST($1::int[], $2::text[]) AS v(id, name) WHERE t.id = v.id RETURNING t.id, t.name"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_update_many_default_types() {
        let builder = PostgresSqlBuilder;
        let (sql, params) =
            builder.build_update_many("users", &["id"], &["name"], 2, None, None, None);
        assert_eq!(
            sql,
            "UPDATE users AS t SET name = v.name FROM UNNEST($1::text[], $2::text[]) AS v(id, name) WHERE t.id = v.id"
        );
        assert_eq!(params, 2);
    }

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

    #[test]
    fn test_build_find_by_pkeys_single() {
        // TS: SELECT * FROM users WHERE id = ANY($1::int[])
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_find_by_pkeys("users", &["id"], 5, "*", Some(&["int"]));
        assert_eq!(sql, "SELECT * FROM users WHERE id = ANY($1::int[])");
        assert_eq!(params, 1);
    }

    #[test]
    fn test_build_find_by_pkeys_composite() {
        // TS: SELECT * FROM user_roles
        //     WHERE (user_id, role_id) IN (SELECT * FROM UNNEST($1::int[], $2::int[]))
        let builder = PostgresSqlBuilder;
        let (sql, params) = builder.build_find_by_pkeys(
            "user_roles",
            &["user_id", "role_id"],
            3,
            "*",
            Some(&["int", "int"]),
        );
        assert_eq!(
            sql,
            "SELECT * FROM user_roles WHERE (user_id, role_id) IN (SELECT * FROM UNNEST($1::int[], $2::int[]))"
        );
        assert_eq!(params, 2);
    }

    #[test]
    fn test_build_find_by_pkeys_with_select_column() {
        let builder = PostgresSqlBuilder;
        let (sql, params) =
            builder.build_find_by_pkeys("users", &["id"], 3, "id, name, email", Some(&["int"]));
        assert_eq!(
            sql,
            "SELECT id, name, email FROM users WHERE id = ANY($1::int[])"
        );
        assert_eq!(params, 1);
    }
}
