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

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

use crate::backend_trait::{CodegenBackend, ResolvedColumn, ResolvedParam};
use crate::backends::typescript_common::{TsRowType, generate_zod_enum, generate_zod_row_struct};
use crate::singularize;

const DEFAULT_MANIFEST_TOML: &str = include_str!("../../manifests/typescript-postgres.toml");
const DEFAULT_MANIFEST_REDSHIFT: &str =
    include_str!("../../manifests/typescript-postgres.redshift.toml");

pub struct TypescriptPostgresBackend {
    manifest: BackendManifest,
    row_type: TsRowType,
}

impl TypescriptPostgresBackend {
    pub fn new(engine: &str) -> Result<Self, ScytheError> {
        let default_toml = match engine {
            "postgresql" | "postgres" | "pg" => DEFAULT_MANIFEST_TOML,
            "redshift" => DEFAULT_MANIFEST_REDSHIFT,
            _ => {
                return Err(ScytheError::new(
                    ErrorCode::InternalError,
                    format!(
                        "typescript-postgres only supports PostgreSQL/Redshift, got engine '{}'",
                        engine
                    ),
                ));
            }
        };
        let manifest = super::load_or_default_manifest(
            "backends/typescript-postgres/manifest.toml",
            default_toml,
        )?;
        Ok(Self {
            manifest,
            row_type: TsRowType::default(),
        })
    }
}

impl CodegenBackend for TypescriptPostgresBackend {
    fn name(&self) -> &str {
        "typescript-postgres"
    }

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

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

    fn file_header(&self) -> String {
        let mut header =
            "/** Auto-generated by scythe. Do not edit. */\n\nimport type { Sql } from \"postgres\";\n"
                .to_string();
        if self.row_type == TsRowType::Zod {
            header.push_str("import { z } from \"zod\";\n");
        }
        header
    }

    fn generate_row_struct(
        &self,
        query_name: &str,
        columns: &[ResolvedColumn],
    ) -> Result<String, ScytheError> {
        let struct_name = row_struct_name(query_name, &self.manifest.naming);
        if self.row_type == TsRowType::Zod {
            return Ok(generate_zod_row_struct(&struct_name, query_name, columns));
        }
        let mut out = String::new();
        let _ = writeln!(out, "/** Row type for {} queries. */", query_name);
        let _ = writeln!(out, "export interface {} {{", struct_name);
        for col in columns {
            let _ = writeln!(out, "\t{}: {};", col.field_name, col.full_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 name = to_pascal_case(&singular);
        self.generate_row_struct(&name, columns)
    }

    fn generate_query_fn(
        &self,
        analyzed: &AnalyzedQuery,
        struct_name: &str,
        _columns: &[ResolvedColumn],
        params: &[ResolvedParam],
    ) -> Result<String, ScytheError> {
        let func_name = fn_name(&analyzed.name, &self.manifest.naming);
        let mut out = String::new();

        // Build parameter list
        let param_list = params
            .iter()
            .map(|p| format!("{}: {}", p.field_name, p.full_type))
            .collect::<Vec<_>>()
            .join(", ");
        let _sep = if param_list.is_empty() { "" } else { ", " };

        // Clean SQL and rewrite $1, $2 to ${paramName} for postgres.js tagged template
        let sql_clean = super::clean_sql_with_optional(
            &analyzed.sql,
            &analyzed.optional_params,
            &analyzed.params,
        );
        let name_map: std::collections::HashMap<u32, String> = analyzed
            .params
            .iter()
            .zip(params.iter())
            .map(|(ap, rp)| (ap.position as u32, rp.field_name.clone()))
            .collect();
        let sql_template = super::rewrite_pg_placeholders(&sql_clean, |n| {
            format!("${{{}}}", name_map.get(&n).map_or("?", |s| s.as_str()))
        });

        // Build function params: inline if short, multi-line if long (biome compliance)
        let inline_params = if params.is_empty() {
            "sql: Sql".to_string()
        } else {
            format!("sql: Sql, {}", param_list)
        };

        // We'll decide inline vs multi-line per call site based on the full signature length

        // Helper: write function signature, inline or multi-line based on length
        let write_fn_sig = |out: &mut String, name: &str, params_inline: &str, ret: &str| {
            let oneliner = format!(
                "export async function {}({}): {} {{",
                name, params_inline, ret
            );
            if oneliner.len() <= 80 {
                let _ = writeln!(out, "{}", oneliner);
            } else {
                // Multi-line params
                let mut parts = vec!["\tsql: Sql".to_string()];
                for p in params {
                    parts.push(format!("\t{}: {}", p.field_name, p.full_type));
                }
                let _ = writeln!(out, "export async function {}(", name);
                for part in &parts {
                    let _ = writeln!(out, "{},", part);
                }
                let _ = writeln!(out, "): {} {{", ret);
            }
        };

        match &analyzed.command {
            QueryCommand::One | QueryCommand::Opt => {
                let _ = writeln!(out, "/** Fetch a single {} or null. */", struct_name);
                let ret = format!("Promise<{} | null>", struct_name);
                write_fn_sig(&mut out, &func_name, &inline_params, &ret);
                let _ = writeln!(out, "\tconst rows = await sql<{}[]>`", struct_name);
                let _ = writeln!(out, "    {}", sql_template);
                let _ = writeln!(out, "  `;");
                let _ = writeln!(out, "\treturn rows[0] ?? null;");
                let _ = write!(out, "}}");
            }
            QueryCommand::Batch => {
                let batch_fn_name = format!("{}Batch", func_name);
                if params.len() > 1 {
                    let params_type_name = format!("{}BatchParams", struct_name);
                    let _ = writeln!(out, "/** Params for {} batch operation. */", struct_name);
                    let _ = writeln!(out, "export interface {} {{", params_type_name);
                    for p in params {
                        let _ = writeln!(out, "\t{}: {};", p.field_name, p.full_type);
                    }
                    let _ = writeln!(out, "}}");
                    let _ = writeln!(out);
                    let _ = writeln!(
                        out,
                        "/** Execute {} for each item in the batch within a transaction. */",
                        analyzed.name
                    );
                    let batch_params = format!("sql: Sql, items: {}[]", params_type_name);
                    write_fn_sig(&mut out, &batch_fn_name, &batch_params, "Promise<void>");
                    let _ = writeln!(out, "\tawait sql.begin(async (tx) => {{");
                    let _ = writeln!(out, "\t\tfor (const item of items) {{");
                    // Build template with item.fieldName
                    let batch_sql = {
                        let mut s = sql_clean.clone();
                        let mut indexed: Vec<(i64, &str)> = analyzed
                            .params
                            .iter()
                            .zip(params.iter())
                            .map(|(ap, rp)| (ap.position, rp.field_name.as_str()))
                            .collect();
                        indexed.sort_by_key(|b| std::cmp::Reverse(b.0));
                        for (pos, field_name) in indexed {
                            let placeholder = format!("${}", pos);
                            let replacement = format!("${{item.{}}}", field_name);
                            s = s.replace(&placeholder, &replacement);
                        }
                        s
                    };
                    let _ = writeln!(out, "\t\t\tawait tx`");
                    let _ = writeln!(out, "    {}", batch_sql);
                    let _ = writeln!(out, "  `;");
                    let _ = writeln!(out, "\t\t}}");
                    let _ = writeln!(out, "\t}});");
                    let _ = write!(out, "}}");
                } else if params.len() == 1 {
                    let _ = writeln!(
                        out,
                        "/** Execute {} for each item in the batch within a transaction. */",
                        analyzed.name
                    );
                    let batch_params = format!("sql: Sql, items: {}[]", params[0].full_type);
                    write_fn_sig(&mut out, &batch_fn_name, &batch_params, "Promise<void>");
                    let _ = writeln!(out, "\tawait sql.begin(async (tx) => {{");
                    let _ = writeln!(out, "\t\tfor (const item of items) {{");
                    let batch_sql =
                        sql_template.replace(&format!("${{{}}}", params[0].field_name), "${item}");
                    let _ = writeln!(out, "\t\t\tawait tx`");
                    let _ = writeln!(out, "    {}", batch_sql);
                    let _ = writeln!(out, "  `;");
                    let _ = writeln!(out, "\t\t}}");
                    let _ = writeln!(out, "\t}});");
                    let _ = write!(out, "}}");
                } else {
                    let _ = writeln!(
                        out,
                        "/** Execute {} for each item in the batch within a transaction. */",
                        analyzed.name
                    );
                    write_fn_sig(
                        &mut out,
                        &batch_fn_name,
                        "sql: Sql, count: number",
                        "Promise<void>",
                    );
                    let _ = writeln!(out, "\tawait sql.begin(async (tx) => {{");
                    let _ = writeln!(out, "\t\tfor (let i = 0; i < count; i++) {{");
                    let _ = writeln!(out, "\t\t\tawait tx`");
                    let _ = writeln!(out, "    {}", sql_template);
                    let _ = writeln!(out, "  `;");
                    let _ = writeln!(out, "\t\t}}");
                    let _ = writeln!(out, "\t}});");
                    let _ = write!(out, "}}");
                }
            }
            QueryCommand::Many => {
                let _ = writeln!(out, "/** Fetch all {} rows. */", struct_name);
                let ret = format!("Promise<{}[]>", struct_name);
                write_fn_sig(&mut out, &func_name, &inline_params, &ret);
                let _ = writeln!(out, "\tconst rows = await sql<{}[]>`", struct_name);
                let _ = writeln!(out, "    {}", sql_template);
                let _ = writeln!(out, "  `;");
                let _ = writeln!(out, "\treturn rows;");
                let _ = write!(out, "}}");
            }
            QueryCommand::Exec => {
                let _ = writeln!(out, "/** Execute a query returning no rows. */");
                write_fn_sig(&mut out, &func_name, &inline_params, "Promise<void>");
                let _ = writeln!(out, "\tawait sql`");
                let _ = writeln!(out, "    {}", sql_template);
                let _ = writeln!(out, "  `;");
                let _ = write!(out, "}}");
            }
            QueryCommand::ExecResult | QueryCommand::ExecRows => {
                let _ = writeln!(
                    out,
                    "/** Execute a query and return the number of affected rows. */"
                );
                write_fn_sig(&mut out, &func_name, &inline_params, "Promise<number>");
                let _ = writeln!(out, "\tconst result = await sql`");
                let _ = writeln!(out, "    {}", sql_template);
                let _ = writeln!(out, "  `;");
                let _ = writeln!(out, "\treturn result.count;");
                let _ = write!(out, "}}");
            }
            QueryCommand::Grouped => {
                unreachable!("Grouped is rewritten to Many before codegen")
            }
        }

        Ok(out)
    }

    fn generate_enum_def(&self, enum_info: &EnumInfo) -> Result<String, ScytheError> {
        let type_name = enum_type_name(&enum_info.sql_name, &self.manifest.naming);
        if self.row_type == TsRowType::Zod {
            return Ok(generate_zod_enum(&type_name, &enum_info.values));
        }
        let mut out = String::new();
        let _ = writeln!(out, "export enum {} {{", type_name);
        for value in &enum_info.values {
            let variant = enum_variant_name(value, &self.manifest.naming);
            let _ = writeln!(out, "\t{} = \"{}\",", variant, value);
        }
        let _ = write!(out, "}}");
        Ok(out)
    }

    fn generate_composite_def(&self, composite: &CompositeInfo) -> Result<String, ScytheError> {
        let name = to_pascal_case(&composite.sql_name);
        let mut out = String::new();
        let _ = writeln!(out, "/** Composite type {}. */", composite.sql_name);
        let _ = writeln!(out, "export interface {} {{", name);
        if composite.fields.is_empty() {
            // empty interface
        } else {
            for field in &composite.fields {
                let ts_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, "\t{}: {};", to_camel_case(&field.name), ts_type);
            }
        }
        let _ = write!(out, "}}");
        Ok(out)
    }

    fn apply_options(
        &mut self,
        options: &std::collections::HashMap<String, String>,
    ) -> Result<(), ScytheError> {
        if let Some(value) = options.get("row_type") {
            self.row_type = TsRowType::from_option(value)?;
        }
        Ok(())
    }
}
