use crate::models::OrmModel;
use crate::orm::column::Column;
use crate::utils::db_placeholder;
use quote::{__private::TokenStream, format_ident, quote};

pub fn generate_save(executor_type: &TokenStream, model: &OrmModel) -> syn::Result<TokenStream> {
    let save_trait_ident = format_ident!("{}SaveTrait", model.struct_name);
    let struct_name = model.struct_name;
    let struct_visibility = model.struct_visibility;
    let table_name = &model.table_name;

    let full_select_columns = model.full_column_select();

    // Primary key
    let primary_key = model.primary_key();
    let is_manual = !primary_key.is_generated();
    let primary_key_var = quote! {primary_key};

    // --- pk_is_set and pk_code ---
    let (pk_is_set, pk_code): (TokenStream, TokenStream) = if is_manual {
        (quote! { true }, quote! {})
    } else {
        let pk_col = primary_key.generated_column();
        let pk_val = pk_col.self_accessor();
        let is_set = pk_col
            .column_properties
            .is_set(quote! { #pk_val }, &pk_col.ty);
        let code = if pk_col.column_properties.readonly {
            quote! {}
        } else {
            let new_method = &pk_col.column_properties.new_expression;
            quote! { let #primary_key_var = #new_method; }
        };
        (is_set, code)
    };

    // --- WHERE clause and binds for UPDATE (appended after SET ...) ---
    let update_col_count = model.update_columns().count();
    let is_full_key = update_col_count == 0;
    let pk_fields = primary_key.fields();
    let pk_update_where: String = pk_fields
        .iter()
        .enumerate()
        .map(|(i, col)| {
            Ok::<_, syn::Error>(format!(
                "{} = {}",
                col.column_name,
                db_placeholder(col.base_field, update_col_count + i + 1)?
            ))
        })
        .collect::<syn::Result<Vec<_>>>()?
        .join(" AND ");

    // For UPDATE WHERE: always use self_accessor (existing pk values)
    let pk_update_bind_accessors: Vec<TokenStream> =
        pk_fields.iter().map(|col| col.self_accessor()).collect();

    // --- WHERE clause for SELECT by pk (MySQL fetch after INSERT/UPDATE) ---
    let pk_select_where: String = pk_fields
        .iter()
        .enumerate()
        .map(|(i, col)| {
            Ok::<_, syn::Error>(format!(
                "{} = {}",
                col.column_name,
                db_placeholder(col.base_field, i + 1)?
            ))
        })
        .collect::<syn::Result<Vec<_>>>()?
        .join(" AND ");

    // For SELECT after INSERT in MySQL manual pk path: use self_accessor
    // For generated non-readonly pk: bind primary_key_var (the locally generated value)
    let pk_select_bind_accessors_insert: Vec<TokenStream> = if is_manual {
        pk_fields.iter().map(|col| col.self_accessor()).collect()
    } else {
        vec![quote! { #primary_key_var }]
    };

    // For SELECT after UPDATE in MySQL: use self_accessor (existing pk values)
    let pk_select_bind_accessors_update: Vec<TokenStream> =
        pk_fields.iter().map(|col| col.self_accessor()).collect();

    // Created at
    let created_at_var = quote! {created_at};
    let created_at_code = match model.created_at() {
        None => quote! {},
        Some(column) => {
            if column.column_properties.readonly {
                quote! {}
            } else {
                let new_method = &column.column_properties.new_expression;
                quote! {
                    let #created_at_var = #new_method;
                }
            }
        }
    };

    // Updated at
    let updated_at_var = quote! {updated_at};
    let updated_at_code = match model.updated_at() {
        None => quote! {},
        Some(column) => {
            if column.column_properties.readonly {
                quote! {}
            } else {
                let new_method = &column.column_properties.new_expression;
                quote! {
                    let #updated_at_var = #new_method;
                }
            }
        }
    };

    // For generated pk: use `primary_key_var` (the locally generated value).
    // For manual pk: fall through to self_accessor() since pk_code is empty and
    // there is no local `primary_key` variable.
    let column_value = |column: &Column, use_created_at_var: bool| {
        if column.column_properties.created_at && use_created_at_var {
            created_at_var.clone()
        } else if column.column_properties.updated_at {
            updated_at_var.clone()
        } else if column.column_properties.primary_key && !is_manual {
            primary_key_var.clone()
        } else {
            let accessor = column.self_accessor();
            if column.column_properties.use_json {
                quote! { sqlx::types::Json(#accessor) }
            } else {
                accessor
            }
        }
    };

    // prepare `insertable` fields
    let insert_value_placeholders =
        create_insert_placeholders(&model.insert_columns().collect::<Vec<_>>());
    let insert_values = model
        .insert_columns()
        .map(|col| column_value(col, true))
        .collect::<Vec<_>>();
    let insert_columns = model
        .insert_columns()
        .map(|col| col.column_name.as_str())
        .collect::<Vec<_>>()
        .join(",");

    // find `updatable` fields
    let update_value_placeholders =
        create_update_placeholders(&model.update_columns().collect::<Vec<_>>());
    let update_values = model
        .update_columns()
        .map(|col| column_value(col, false))
        .collect::<Vec<_>>();

    let insert_sql_returning = format!(
        "INSERT INTO {table_name} ({insert_columns}) VALUES ({insert_value_placeholders}) RETURNING {full_select_columns}"
    );
    let update_sql_returning = format!(
        "UPDATE {table_name} SET {update_value_placeholders} WHERE {pk_update_where} RETURNING {full_select_columns}"
    );

    let insert_sql_no_returning =
        format!("INSERT INTO {table_name} ({insert_columns}) VALUES ({insert_value_placeholders})");
    let update_sql_no_returning =
        format!("UPDATE {table_name} SET {update_value_placeholders} WHERE {pk_update_where}");
    let select_by_pk_sql =
        format!("SELECT {full_select_columns} from {table_name} WHERE {pk_select_where}");

    let mysql_insert_fetch = if is_manual {
        // Manual pk: INSERT then SELECT using all pk field self-accessors
        quote! {
            sqlx::query(#insert_sql_no_returning)
            #(
                .bind(#insert_values)
            )*
            .execute(executor).await?;
            let r = sqlx::query_as::<_, #struct_name>(#select_by_pk_sql)
            #(
                .bind(#pk_select_bind_accessors_insert)
            )*
            .fetch_one(executor).await?;
        }
    } else if primary_key.generated_column().column_properties.readonly {
        quote! {
            let insert_result = sqlx::query(#insert_sql_no_returning)
            #(
                .bind(#insert_values)
            )*
            .execute(executor).await?;
            let last_id = insert_result.last_insert_id() as i64;
            let r = sqlx::query_as::<_, #struct_name>(#select_by_pk_sql)
                .bind(last_id)
                .fetch_one(executor).await?;
        }
    } else {
        quote! {
            sqlx::query(#insert_sql_no_returning)
            #(
                .bind(#insert_values)
            )*
            .execute(executor).await?;
            let r = sqlx::query_as::<_, #struct_name>(#select_by_pk_sql)
                .bind(#primary_key_var)
                .fetch_one(executor).await?;
        }
    };

    let pk_col_names: Vec<&str> = pk_fields.iter().map(|c| c.column_name.as_str()).collect();
    let pk_cols_list = pk_col_names.join(", ");

    let excluded_updates: String = model
        .update_columns()
        .map(|col| format!("{0} = EXCLUDED.{0}", col.column_name))
        .collect::<Vec<_>>()
        .join(", ");

    let mysql_updates: String = model
        .update_columns()
        .map(|col| format!("{0} = VALUES({0})", col.column_name))
        .collect::<Vec<_>>()
        .join(", ");

    let (executor_bound, save_body) = if is_manual {
        if cfg!(feature = "mysql") {
            let (upsert_sql, select_sql) = if is_full_key {
                (
                    format!(
                        "INSERT IGNORE INTO {table_name} ({insert_columns}) VALUES ({insert_value_placeholders})"
                    ),
                    format!(
                        "SELECT {full_select_columns} from {table_name} WHERE {pk_select_where}"
                    ),
                )
            } else {
                (
                    format!(
                        "INSERT INTO {table_name} ({insert_columns}) VALUES ({insert_value_placeholders}) ON DUPLICATE KEY UPDATE {mysql_updates}"
                    ),
                    format!(
                        "SELECT {full_select_columns} from {table_name} WHERE {pk_select_where}"
                    ),
                )
            };

            let mysql_upsert_fetch = quote! {
                sqlx::query(#upsert_sql)
                #(
                    .bind(#insert_values)
                )*
                .execute(executor).await?;
                let r = sqlx::query_as::<_, #struct_name>(#select_sql)
                #(
                    .bind(#pk_select_bind_accessors_insert)
                )*
                .fetch_one(executor).await?;
            };

            (
                quote! { E: #executor_type + Copy },
                quote! {
                    #updated_at_code
                    #mysql_upsert_fetch
                    Ok(r)
                },
            )
        } else if is_full_key {
            let upsert_sql_do_nothing = format!(
                "INSERT INTO {table_name} ({insert_columns}) VALUES ({insert_value_placeholders}) ON CONFLICT ({pk_cols_list}) DO NOTHING RETURNING {full_select_columns}"
            );
            let select_by_pk_sql_val =
                format!("SELECT {full_select_columns} from {table_name} WHERE {pk_select_where}");
            (
                quote! { E: #executor_type + Copy },
                quote! {
                    #updated_at_code
                    let r_opt = sqlx::query_as::<_, #struct_name>(#upsert_sql_do_nothing)
                    #(
                        .bind(#insert_values)
                    )*
                    .fetch_optional(executor).await?;
                    let r = if let Some(row) = r_opt {
                        row
                    } else {
                        sqlx::query_as::<_, #struct_name>(#select_by_pk_sql_val)
                        #(
                            .bind(#pk_select_bind_accessors_insert)
                        )*
                        .fetch_one(executor).await?
                    };
                    Ok(r)
                },
            )
        } else {
            let upsert_sql_do_update = format!(
                "INSERT INTO {table_name} ({insert_columns}) VALUES ({insert_value_placeholders}) ON CONFLICT ({pk_cols_list}) DO UPDATE SET {excluded_updates} RETURNING {full_select_columns}"
            );
            (
                quote! { E: #executor_type },
                quote! {
                    #updated_at_code
                    let r = sqlx::query_as::<_, #struct_name>(#upsert_sql_do_update)
                    #(
                        .bind(#insert_values)
                    )*
                    .fetch_one(executor).await?;
                    Ok(r)
                },
            )
        }
    } else if cfg!(feature = "mysql") {
        (
            quote! { E: #executor_type + Copy },
            quote! {
                #updated_at_code
                match #pk_is_set {
                    true => {
                        #pk_code
                        #created_at_code
                        #mysql_insert_fetch
                        Ok(r)
                    },
                    false => {
                        sqlx::query(#update_sql_no_returning)
                        #(
                            .bind(#update_values)
                        )*
                        #(
                            .bind(#pk_update_bind_accessors)
                        )*
                        .execute(executor).await?;
                        let r = sqlx::query_as::<_, #struct_name>(#select_by_pk_sql)
                        #(
                            .bind(#pk_select_bind_accessors_update)
                        )*
                        .fetch_one(executor).await?;
                        Ok(r)
                    }
                }
            },
        )
    } else {
        (
            quote! { E: #executor_type },
            quote! {
                #updated_at_code
                match #pk_is_set {
                    true => {
                        #pk_code
                        #created_at_code
                        let r = sqlx::query_as::<_, #struct_name>(#insert_sql_returning)
                        #(
                            .bind(#insert_values)
                        )*
                        .fetch_one(executor).await?;
                        Ok(r)
                    },
                    false => {
                        let r = sqlx::query_as::<_, #struct_name>(#update_sql_returning)
                        #(
                            .bind(#update_values)
                        )*
                        #(
                            .bind(#pk_update_bind_accessors)
                        )*
                        .fetch_one(executor).await?;
                        Ok(r)
                    }
                }
            },
        )
    };

    Ok(quote! {
        #struct_visibility trait #save_trait_ident<'e, #executor_bound>: Sized {
            async fn save(&self, executor: E) -> lorm::errors::Result<#struct_name>;
        }

        #[automatically_derived]
        impl<'e, #executor_bound> #save_trait_ident<'e, E> for #struct_name
        {
            async fn save(&self, executor: E) -> lorm::errors::Result<#struct_name>
            {
                #save_body
            }
        }
    })
}

/// Creates SQL placeholders for INSERT statements.
///
/// Generates database-specific placeholders: `"$1, $2, $3"` for PostgreSQL/SQLite or `"?, ?, ?"` for MySQL.
pub(crate) fn create_insert_placeholders<'a>(columns: &[&Column<'a>]) -> String {
    columns
        .iter()
        .enumerate()
        .map(|(i, c)| db_placeholder(c.base_field, i + 1).unwrap())
        .collect::<Vec<_>>()
        .join(",")
}

/// Creates SQL placeholders for UPDATE statements.
///
/// Generates database-specific SET clauses: `"name = $1, email = $2"` for PostgreSQL/SQLite
/// or `"name = ?, email = ?"` for MySQL.
pub(crate) fn create_update_placeholders<'a>(fields: &[&Column<'a>]) -> String {
    fields
        .iter()
        .enumerate()
        .map(|(i, c)| {
            format!(
                "{} = {}",
                c.column_name,
                db_placeholder(c.base_field, i + 1).unwrap()
            )
        })
        .collect::<Vec<_>>()
        .join(",")
}
