use scythe_backend::manifest::BackendManifest;
use scythe_backend::naming::{
    enum_type_name, enum_variant_name, fn_name, row_struct_name, to_pascal_case, to_snake_case,
};
use std::fmt::Write;

use scythe_core::analyzer::{AnalyzedColumn, AnalyzedQuery, CompositeInfo, EnumInfo};
use scythe_core::errors::{ErrorCode, ScytheError};
use scythe_core::parser::QueryCommand;

use crate::backend_trait::{CodegenBackend, ResolvedColumn, ResolvedParam};
use crate::singularize;

/// Default embedded manifest TOML for rust-sqlx, used as fallback.
const DEFAULT_MANIFEST_TOML: &str = include_str!("../../manifests/rust-sqlx.toml");
const DEFAULT_MANIFEST_MARIADB: &str = include_str!("../../manifests/rust-sqlx.mariadb.toml");
const DEFAULT_MANIFEST_REDSHIFT: &str = include_str!("../../manifests/rust-sqlx.redshift.toml");

/// SqlxBackend generates Rust code targeting the sqlx crate.
pub struct SqlxBackend {
    manifest: BackendManifest,
    engine: String,
    /// When true, only emit struct/enum definitions (no query functions).
    /// This avoids the `sqlx::query_as!()` macro which requires `DATABASE_URL` at compile time.
    structs_only: bool,
}

impl SqlxBackend {
    pub fn new(engine: &str) -> Result<Self, ScytheError> {
        // Multi-DB backend — accept all engines, load PG manifest as default
        // TODO: Load engine-specific manifests once they exist
        match engine {
            "postgresql" | "postgres" | "pg" | "mysql" | "mariadb" | "sqlite" | "sqlite3"
            | "redshift" => {}
            _ => {
                return Err(ScytheError::new(
                    ErrorCode::InternalError,
                    format!("unsupported engine '{}' for rust-sqlx backend", engine),
                ));
            }
        }
        let manifest = match engine {
            "mariadb" => super::load_or_default_manifest(
                "backends/rust-sqlx/manifest.toml",
                DEFAULT_MANIFEST_MARIADB,
            )?,
            "redshift" => super::load_or_default_manifest(
                "backends/rust-sqlx/manifest.toml",
                DEFAULT_MANIFEST_REDSHIFT,
            )?,
            _ => super::load_or_default_manifest(
                "backends/rust-sqlx/manifest.toml",
                DEFAULT_MANIFEST_TOML,
            )?,
        };
        Ok(Self {
            manifest,
            engine: engine.to_string(),
            structs_only: false,
        })
    }
}

impl SqlxBackend {
    /// Return true if this engine uses inline ENUMs (not named custom types).
    ///
    /// MySQL, MariaDB, and SQLite represent ENUMs as plain strings at the wire
    /// level. sqlx's `#[derive(sqlx::Type)]` generates `type_info()` returning
    /// `MySqlTypeInfo::__enum()` (ColumnType::String + ENUM flag), but the
    /// server sends `ColumnType::Enum`. The PartialEq check in MySqlTypeInfo
    /// fails because the r#type fields differ, producing a runtime
    /// "mismatched types" ColumnDecode error.
    ///
    /// For these engines, row struct fields must use `String` (or `Option<String>`)
    /// instead of the generated Rust enum type.
    fn uses_inline_enums(&self) -> bool {
        matches!(
            self.engine.as_str(),
            "mysql" | "mariadb" | "sqlite" | "sqlite3"
        )
    }

    /// Resolve the field type for a row struct column.
    ///
    /// For engines that use inline ENUMs, enum-typed columns are mapped to
    /// `String` / `Option<String>` because sqlx cannot type-check them against
    /// the generated Rust enum at runtime (see `uses_inline_enums`).
    fn row_field_type<'a>(&self, col: &'a ResolvedColumn) -> &'a str {
        if self.uses_inline_enums() && col.neutral_type.starts_with("enum::") {
            if col.nullable {
                "Option<String>"
            } else {
                "String"
            }
        } else {
            &col.full_type
        }
    }

    /// Return the sqlx pool type for the configured engine.
    fn pool_type(&self) -> &str {
        match self.engine.as_str() {
            "mysql" | "mariadb" => "sqlx::MySqlPool",
            "sqlite" | "sqlite3" => "sqlx::SqlitePool",
            _ => "sqlx::PgPool",
        }
    }

    /// Return the sqlx query-result type for the configured engine.
    fn query_result_type(&self) -> &str {
        match self.engine.as_str() {
            "mysql" | "mariadb" => "sqlx::mysql::MySqlQueryResult",
            "sqlite" | "sqlite3" => "sqlx::sqlite::SqliteQueryResult",
            _ => "sqlx::postgres::PgQueryResult",
        }
    }
}

impl CodegenBackend for SqlxBackend {
    fn name(&self) -> &str {
        "rust-sqlx"
    }

    fn manifest(&self) -> &scythe_backend::manifest::BackendManifest {
        &self.manifest
    }

    fn supported_engines(&self) -> &[&str] {
        &["postgresql", "mysql", "mariadb", "sqlite", "redshift"]
    }

    fn file_header(&self) -> String {
        "// Auto-generated by scythe. Do not edit.\n#![allow(dead_code, unused_imports, clippy::needless_question_mark, clippy::redundant_closure)]"
            .to_string()
    }

    fn apply_options(
        &mut self,
        options: &std::collections::HashMap<String, String>,
    ) -> Result<(), ScytheError> {
        if options.get("structs_only").is_some_and(|v| v == "true") {
            self.structs_only = true;
        }
        Ok(())
    }

    fn generate_row_struct(
        &self,
        query_name: &str,
        columns: &[ResolvedColumn],
    ) -> Result<String, ScytheError> {
        let struct_name = row_struct_name(query_name, &self.manifest.naming);
        let mut out = String::new();

        let _ = writeln!(out, "#[derive(Debug, Clone, sqlx::FromRow)]");
        let _ = writeln!(out, "pub struct {} {{", struct_name);

        for col in columns {
            let field_type = self.row_field_type(col);
            let _ = writeln!(out, "    pub {}: {},", col.field_name, field_type);
        }

        let _ = write!(out, "}}");
        Ok(out)
    }

    fn generate_model_struct(
        &self,
        table_name: &str,
        columns: &[ResolvedColumn],
    ) -> Result<String, ScytheError> {
        let singular = singularize(table_name);
        let struct_name = to_pascal_case(&singular).into_owned();
        let mut out = String::new();

        let _ = writeln!(out, "#[derive(Debug, Clone, sqlx::FromRow)]");
        let _ = writeln!(out, "pub struct {} {{", struct_name);

        for col in columns {
            let field_type = self.row_field_type(col);
            let _ = writeln!(out, "    pub {}: {},", col.field_name, field_type);
        }

        let _ = write!(out, "}}");
        Ok(out)
    }

    fn generate_query_fn(
        &self,
        analyzed: &AnalyzedQuery,
        struct_name: &str,
        _columns: &[ResolvedColumn],
        params: &[ResolvedParam],
    ) -> Result<String, ScytheError> {
        // In structs_only mode, skip all function generation (avoids sqlx::query!() macros
        // which require DATABASE_URL at compile time).
        if self.structs_only {
            return Ok(String::new());
        }

        let func_name = fn_name(&analyzed.name, &self.manifest.naming);
        let mut out = String::new();

        // Deprecated annotation
        if let Some(ref msg) = analyzed.deprecated {
            let _ = writeln!(out, "#[deprecated(note = \"{}\")]", msg);
        }

        // Build parameter list
        let pool_type = self.pool_type();
        let mut param_parts: Vec<String> = vec![format!("pool: &{}", pool_type)];
        for param in params {
            param_parts.push(format!("{}: {}", param.field_name, param.borrowed_type));
        }

        // Clean SQL
        let sql_raw = super::clean_sql_with_optional(
            &analyzed.sql,
            &analyzed.optional_params,
            &analyzed.params,
        );
        let sql = rewrite_sql_for_enums(&sql_raw, &analyzed.columns, &self.manifest);

        // Build bind params string
        let bind_params: String = analyzed
            .params
            .iter()
            .map(|p| {
                let param_name = to_snake_case(&p.name);
                if p.neutral_type.starts_with("enum::") {
                    let enum_name = p.neutral_type.strip_prefix("enum::").unwrap();
                    let rust_type = enum_type_name(enum_name, &self.manifest.naming);
                    format!(", {} as &{}", param_name, rust_type)
                } else {
                    format!(", {}", param_name)
                }
            })
            .collect();

        // Handle :batch separately — generates a different function signature
        if matches!(analyzed.command, QueryCommand::Batch) {
            let batch_fn_name = format!("{}_batch", func_name);

            // Generate params struct if >1 param
            if params.len() > 1 {
                let params_struct_name = format!("{}BatchParams", struct_name);
                let _ = writeln!(out, "#[derive(Debug, Clone)]");
                let _ = writeln!(out, "pub struct {} {{", params_struct_name);
                for param in params {
                    let _ = writeln!(out, "    pub {}: {},", param.field_name, param.full_type);
                }
                let _ = writeln!(out, "}}");
                let _ = writeln!(out);

                // Batch function takes &[ParamsStruct]
                let _ = writeln!(
                    out,
                    "pub async fn {}(pool: &{}, items: &[{}]) -> Result<(), sqlx::Error> {{",
                    batch_fn_name, pool_type, params_struct_name
                );
                let _ = writeln!(out, "    let mut tx = pool.begin().await?;");
                let _ = writeln!(out, "    for item in items {{");

                // Build bind params from struct fields
                let struct_bind_params: String = params
                    .iter()
                    .map(|p| {
                        if p.neutral_type.starts_with("enum::") {
                            let enum_name = p.neutral_type.strip_prefix("enum::").unwrap();
                            let rust_type = enum_type_name(enum_name, &self.manifest.naming);
                            format!(", item.{} as &{}", p.field_name, rust_type)
                        } else {
                            format!(", item.{}", p.field_name)
                        }
                    })
                    .collect();

                let _ = writeln!(
                    out,
                    "        sqlx::query!(\"{}\"{})",
                    sql, struct_bind_params
                );
                let _ = writeln!(out, "            .execute(&mut *tx)");
                let _ = writeln!(out, "            .await?;");
                let _ = writeln!(out, "    }}");
                let _ = writeln!(out, "    tx.commit().await?;");
                let _ = writeln!(out, "    Ok(())");
            } else if params.len() == 1 {
                // Single param — takes a slice of that type
                let param = &params[0];
                let _ = writeln!(
                    out,
                    "pub async fn {}(pool: &{}, items: &[{}]) -> Result<(), sqlx::Error> {{",
                    batch_fn_name, pool_type, param.full_type
                );
                let _ = writeln!(out, "    let mut tx = pool.begin().await?;");
                let _ = writeln!(out, "    for item in items {{");
                let _ = writeln!(out, "        sqlx::query!(\"{}\", item)", sql);
                let _ = writeln!(out, "            .execute(&mut *tx)");
                let _ = writeln!(out, "            .await?;");
                let _ = writeln!(out, "    }}");
                let _ = writeln!(out, "    tx.commit().await?;");
                let _ = writeln!(out, "    Ok(())");
            } else {
                // No params — just execute N times (unusual but valid)
                let _ = writeln!(
                    out,
                    "pub async fn {}(pool: &{}, count: usize) -> Result<(), sqlx::Error> {{",
                    batch_fn_name, pool_type
                );
                let _ = writeln!(out, "    let mut tx = pool.begin().await?;");
                let _ = writeln!(out, "    for _ in 0..count {{");
                let _ = writeln!(out, "        sqlx::query!(\"{}\")", sql);
                let _ = writeln!(out, "            .execute(&mut *tx)");
                let _ = writeln!(out, "            .await?;");
                let _ = writeln!(out, "    }}");
                let _ = writeln!(out, "    tx.commit().await?;");
                let _ = writeln!(out, "    Ok(())");
            }

            let _ = write!(out, "}}");
            return Ok(out);
        }

        // Return type for non-batch commands
        let return_type = match &analyzed.command {
            QueryCommand::One | QueryCommand::Opt => struct_name.to_string(),
            QueryCommand::Many => format!("Vec<{}>", struct_name),
            QueryCommand::Exec => "()".to_string(),
            QueryCommand::ExecResult => self.query_result_type().to_string(),
            QueryCommand::ExecRows => "u64".to_string(),
            QueryCommand::Batch => unreachable!(),
            QueryCommand::Grouped => {
                return Err(ScytheError::new(
                    ErrorCode::InternalError,
                    "Grouped queries should be rewritten before codegen".to_string(),
                ));
            }
        };

        // Function signature
        let _ = writeln!(
            out,
            "pub async fn {}({}) -> Result<{}, sqlx::Error> {{",
            func_name,
            param_parts.join(", "),
            return_type
        );

        // Query body
        let has_row_struct = matches!(analyzed.command, QueryCommand::One | QueryCommand::Many);

        let is_exec_rows = matches!(analyzed.command, QueryCommand::ExecRows);

        if is_exec_rows {
            if has_row_struct && !analyzed.columns.is_empty() {
                let _ = write!(
                    out,
                    "    let result = sqlx::query_as!({}, \"{}\"{})",
                    struct_name, sql, bind_params
                );
            } else {
                let _ = write!(
                    out,
                    "    let result = sqlx::query!(\"{}\"{})",
                    sql, bind_params
                );
            }
        } else if has_row_struct && !analyzed.columns.is_empty() {
            let _ = write!(
                out,
                "    sqlx::query_as!({}, \"{}\"{})",
                struct_name, sql, bind_params
            );
        } else {
            let _ = write!(out, "    sqlx::query!(\"{}\"{})", sql, bind_params);
        }

        let _ = writeln!(out);

        // Fetch method
        let fetch_method = match &analyzed.command {
            QueryCommand::One | QueryCommand::Opt => ".fetch_one(pool)",
            QueryCommand::Many => ".fetch_all(pool)",
            QueryCommand::Exec => ".execute(pool)",
            QueryCommand::ExecResult => ".execute(pool)",
            QueryCommand::ExecRows => ".execute(pool)",
            QueryCommand::Batch => unreachable!(),
            QueryCommand::Grouped => {
                return Err(ScytheError::new(
                    ErrorCode::InternalError,
                    "Grouped queries should be rewritten before codegen".to_string(),
                ));
            }
        };

        let _ = write!(out, "        {}", fetch_method);
        let _ = writeln!(out);

        // Post-processing for exec variants
        match &analyzed.command {
            QueryCommand::Exec => {
                let _ = writeln!(out, "        .await?;");
                let _ = writeln!(out, "    Ok(())");
            }
            QueryCommand::ExecRows => {
                let _ = writeln!(out, "        .await?;");
                let _ = writeln!(out, "    Ok(result.rows_affected())");
            }
            _ => {
                let _ = writeln!(out, "        .await");
            }
        }

        let _ = write!(out, "}}");
        Ok(out)
    }

    fn generate_enum_def(&self, enum_info: &EnumInfo) -> Result<String, ScytheError> {
        let mut out = String::with_capacity(256);
        let type_name = enum_type_name(&enum_info.sql_name, &self.manifest.naming);

        let _ = writeln!(out, "#[derive(Debug, Clone, PartialEq, Eq, sqlx::Type)]");
        // MySQL/MariaDB/SQLite use inline ENUMs — sqlx decodes them by value matching only.
        // The `type_name` annotation is PostgreSQL-specific (for named custom types) and
        // causes a "mismatched types" error on MySQL at runtime.
        match self.engine.as_str() {
            "mysql" | "mariadb" | "sqlite" | "sqlite3" => {
                let _ = writeln!(out, "#[sqlx(rename_all = \"snake_case\")]");
            }
            _ => {
                let _ = writeln!(
                    out,
                    "#[sqlx(type_name = \"{}\", rename_all = \"snake_case\")]",
                    enum_info.sql_name
                );
            }
        }
        let _ = writeln!(out, "pub enum {type_name} {{");

        for value in &enum_info.values {
            let variant = enum_variant_name(value, &self.manifest.naming);
            let _ = writeln!(out, "    {variant},");
        }

        let _ = write!(out, "}}");
        Ok(out)
    }

    fn generate_composite_def(&self, composite: &CompositeInfo) -> Result<String, ScytheError> {
        use scythe_backend::types::resolve_type;

        let struct_name = to_pascal_case(&composite.sql_name).into_owned();
        let mut out = String::new();

        let _ = writeln!(out, "#[derive(Debug, Clone, sqlx::Type)]");
        let _ = writeln!(out, "#[sqlx(type_name = \"{}\")]", composite.sql_name);
        let _ = writeln!(out, "pub struct {} {{", struct_name);
        for field in &composite.fields {
            let rust_type = resolve_type(&field.neutral_type, &self.manifest, false)
                .map(|t| t.into_owned())
                .map_err(|e| {
                    ScytheError::new(
                        ErrorCode::InternalError,
                        format!("composite field type error: {}", e),
                    )
                })?;
            let _ = writeln!(
                out,
                "    pub {}: {},",
                to_snake_case(&field.name),
                rust_type
            );
        }
        let _ = write!(out, "}}");
        Ok(out)
    }
}

// ---------------------------------------------------------------------------
// Internal helpers (moved from old modules)
// ---------------------------------------------------------------------------

/// Rewrite SQL to add enum type annotations for sqlx.
fn rewrite_sql_for_enums(
    sql: &str,
    columns: &[AnalyzedColumn],
    manifest: &BackendManifest,
) -> String {
    let enum_cols: Vec<(&str, String)> = columns
        .iter()
        .filter_map(|col| {
            if let Some(enum_name) = col.neutral_type.strip_prefix("enum::") {
                let rust_type = enum_type_name(enum_name, &manifest.naming);
                let annotation = if col.nullable {
                    format!("Option<{}>", rust_type)
                } else {
                    rust_type
                };
                Some((col.name.as_str(), annotation))
            } else {
                None
            }
        })
        .collect();

    if enum_cols.is_empty() {
        return sql.to_string();
    }

    let mut result = sql.to_string();
    for (col_name, annotation) in &enum_cols {
        let alias = format!("{} AS \\\"{}: {}\\\"", col_name, col_name, annotation);
        if let Some(from_pos) = result.to_uppercase().find(" FROM ") {
            let select_part = &result[..from_pos];
            let rest = &result[from_pos..];
            let new_select = replace_column_in_select(select_part, col_name, &alias);
            result = format!("{}{}", new_select, rest);
        }
    }
    result
}

fn replace_column_in_select(select: &str, col_name: &str, replacement: &str) -> String {
    let mut result = select.to_string();
    let patterns = [format!(", {}", col_name), format!(" {}", col_name)];
    for pattern in &patterns {
        if let Some(pos) = result.rfind(pattern.as_str()) {
            let after = pos + pattern.len();
            let next_char = result[after..].chars().next();
            if next_char.is_none() || matches!(next_char, Some(' ') | Some(',') | Some('\n')) {
                let prefix = &result[..pos + pattern.len() - col_name.len()];
                let suffix = &result[after..];
                result = format!("{}{}{}", prefix, replacement, suffix);
                break;
            }
        }
    }
    result
}
