qail-core 0.27.9

use super::dialect::Dialect;
use crate::ast::*;
// use super::traits::SqlGenerator;

/// Generate CREATE TABLE SQL.
pub fn build_create_table(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let mut sql = String::new();
    sql.push_str("CREATE TABLE ");
    sql.push_str(&generator.quote_identifier(&cmd.table));
    sql.push_str(" (\n");

    let composite_pk_columns: Vec<String> = cmd
        .columns
        .iter()
        .filter_map(|col| match col {
            Expr::Def {
                name, constraints, ..
            } if constraints.contains(&Constraint::PrimaryKey) => Some(name.clone()),
            _ => None,
        })
        .collect();
    let use_composite_pk = composite_pk_columns.len() > 1;

    let mut defs = Vec::new();
    for col in &cmd.columns {
        if let Expr::Def {
            name,
            data_type,
            constraints,
        } = col
        {
            let sql_type = map_type(data_type);
            let mut line = format!("    {} {}", generator.quote_identifier(name), sql_type);

            // Default to NOT NULL unless Nullable (?) constraint is present
            let is_nullable = constraints.contains(&Constraint::Nullable);
            if !is_nullable {
                line.push_str(" NOT NULL");
            }

            for constraint in constraints {
                if let Constraint::Default(val) = constraint {
                    line.push_str(" DEFAULT ");
                    // Map common functions to SQL equivalents
                    let sql_default = match val.as_str() {
                        "uuid()" => "gen_random_uuid()",
                        "now()" => "NOW()",
                        other => other,
                    };
                    line.push_str(sql_default);
                }
                if let Constraint::Generated(generation) = constraint {
                    match generation {
                        ColumnGeneration::Stored(expr) if expr == "identity" => {
                            line.push_str(" GENERATED ALWAYS AS IDENTITY");
                        }
                        ColumnGeneration::Stored(expr) if expr == "identity_by_default" => {
                            line.push_str(" GENERATED BY DEFAULT AS IDENTITY");
                        }
                        ColumnGeneration::Stored(expr) => {
                            line.push_str(&format!(" GENERATED ALWAYS AS ({expr}) STORED"));
                        }
                        ColumnGeneration::Virtual(expr) => {
                            line.push_str(&format!(" GENERATED ALWAYS AS ({expr})"));
                        }
                    }
                }
            }

            if constraints.contains(&Constraint::PrimaryKey) && !use_composite_pk {
                line.push_str(" PRIMARY KEY");
            }
            if constraints.contains(&Constraint::Unique) {
                line.push_str(" UNIQUE");
            }

            for constraint in constraints {
                if let Constraint::Check(vals) = constraint {
                    if vals.len() == 1
                        && vals[0]
                            .trim_start()
                            .to_ascii_uppercase()
                            .starts_with("CONSTRAINT ")
                    {
                        line.push(' ');
                        line.push_str(&vals[0]);
                        continue;
                    }

                    let raw_check = vals.join(" ");
                    let looks_like_expr = vals.len() == 1
                        || vals.iter().any(|v| {
                            v.chars().any(|c| {
                                c.is_whitespace() || matches!(c, '<' | '>' | '=' | '!' | '(' | ')')
                            })
                        });

                    if looks_like_expr {
                        line.push_str(&format!(" CHECK ({raw_check})"));
                    } else {
                        line.push_str(&format!(
                            " CHECK ({} IN ({}))",
                            generator.quote_identifier(name),
                            vals.iter()
                                .map(|v| format!("'{}'", v.replace('\'', "''")))
                                .collect::<Vec<_>>()
                                .join(", ")
                        ));
                    }
                }
                if let Constraint::References(target) = constraint {
                    line.push_str(&format!(" REFERENCES {target}"));
                }
            }

            defs.push(line);
        }
    }

    if use_composite_pk {
        let cols = composite_pk_columns
            .iter()
            .map(|c| generator.quote_identifier(c))
            .collect::<Vec<_>>()
            .join(", ");
        defs.push(format!("    PRIMARY KEY ({cols})"));
    }

    for tc in &cmd.table_constraints {
        match tc {
            TableConstraint::Unique(cols) => {
                let col_list = cols
                    .iter()
                    .map(|c| generator.quote_identifier(c))
                    .collect::<Vec<_>>()
                    .join(", ");
                defs.push(format!("    UNIQUE ({})", col_list));
            }
            TableConstraint::PrimaryKey(cols) => {
                let col_list = cols
                    .iter()
                    .map(|c| generator.quote_identifier(c))
                    .collect::<Vec<_>>()
                    .join(", ");
                defs.push(format!("    PRIMARY KEY ({})", col_list));
            }
        }
    }

    sql.push_str(&defs.join(",\n"));
    sql.push_str("\n)");

    let mut comments = Vec::new();
    for col in &cmd.columns {
        if let Expr::Def {
            name, constraints, ..
        } = col
        {
            for c in constraints {
                if let Constraint::Comment(text) = c {
                    comments.push(format!(
                        "COMMENT ON COLUMN {}.{} IS '{}'",
                        generator.quote_identifier(&cmd.table),
                        generator.quote_identifier(name),
                        text.replace('\'', "''")
                    ));
                }
            }
        }
    }
    if !comments.is_empty() {
        sql.push_str(";\n");
        sql.push_str(&comments.join(";\n"));
    }

    sql
}

/// Generate ALTER TABLE SQL.
pub fn build_alter_table(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let mut stmts = Vec::new();
    let table_name = generator.quote_identifier(&cmd.table);

    for col in &cmd.columns {
        match col {
            Expr::Mod { kind, col } => match kind {
                ModKind::Add => {
                    if let Expr::Def {
                        name,
                        data_type,
                        constraints,
                    } = col.as_ref()
                    {
                        let sql_type = map_type(data_type);
                        let mut line = format!(
                            "ALTER TABLE {} ADD COLUMN {} {}",
                            table_name,
                            generator.quote_identifier(name),
                            sql_type
                        );

                        let is_nullable = constraints.contains(&Constraint::Nullable);
                        if !is_nullable {
                            line.push_str(" NOT NULL");
                        }

                        if constraints.contains(&Constraint::Unique) {
                            line.push_str(" UNIQUE");
                        }
                        stmts.push(line);
                    }
                }
                ModKind::Drop => {
                    if let Expr::Named(name) = col.as_ref() {
                        stmts.push(format!(
                            "ALTER TABLE {} DROP COLUMN {}",
                            table_name,
                            generator.quote_identifier(name)
                        ));
                    }
                }
            },
            Expr::Named(rename_expr) if rename_expr.contains(" -> ") => {
                let parts: Vec<&str> = rename_expr.split(" -> ").collect();
                if parts.len() == 2 {
                    let old_name = parts[0].trim();
                    let new_name = parts[1].trim();
                    stmts.push(format!(
                        "ALTER TABLE {} RENAME COLUMN {} TO {}",
                        table_name,
                        generator.quote_identifier(old_name),
                        generator.quote_identifier(new_name)
                    ));
                }
            }
            _ => {}
        }
    }
    stmts.join(";\n")
}

/// Generate CREATE INDEX SQL.
pub fn build_create_index(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    match &cmd.index_def {
        Some(idx) => {
            let unique = if idx.unique { "UNIQUE " } else { "" };
            let cols = idx
                .columns
                .iter()
                .map(|c| {
                    if is_simple_identifier(c) {
                        generator.quote_identifier(c)
                    } else {
                        c.clone()
                    }
                })
                .collect::<Vec<_>>()
                .join(", ");
            let mut sql = format!(
                "CREATE {}INDEX {} ON {}",
                unique,
                generator.quote_identifier(&idx.name),
                generator.quote_identifier(&idx.table)
            );
            if let Some(method) = &idx.index_type
                && !method.trim().is_empty()
            {
                sql.push_str(" USING ");
                sql.push_str(method.trim());
            }
            sql.push_str(" (");
            sql.push_str(&cols);
            sql.push(')');
            if let Some(where_clause) = &idx.where_clause {
                sql.push_str(" WHERE ");
                sql.push_str(where_clause);
            }
            sql
        }
        None => String::new(),
    }
}

fn map_type(t: &str) -> &str {
    match t {
        "str" | "text" | "string" => "VARCHAR(255)",
        "int" | "i32" => "INT",
        "bigint" | "i64" => "BIGINT",
        "uuid" => "UUID",
        "bool" | "boolean" => "BOOLEAN",
        "dec" | "decimal" => "DECIMAL",
        "float" | "f64" => "DOUBLE PRECISION",
        "serial" => "SERIAL",
        "timestamp" | "time" => "TIMESTAMP",
        "json" | "jsonb" => "JSONB",
        _ => t,
    }
}

fn is_simple_identifier(s: &str) -> bool {
    !s.is_empty()
        && s.chars()
            .all(|c| c.is_ascii_alphanumeric() || c == '_' || c == '.')
}

/// Generate ALTER COLUMN SQL (drop or rename column).
pub fn build_alter_column(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let table = generator.quote_identifier(&cmd.table);

    // Identified columns (target column)
    let cols: Vec<String> = cmd
        .columns
        .iter()
        .filter_map(|c| match c {
            Expr::Named(n) => Some(n.clone()),
            _ => None,
        })
        .collect();

    if cols.is_empty() {
        return "/* ERROR: Column required */".to_string();
    }
    let col_name = &cols[0];
    let quoted_col = generator.quote_identifier(col_name);

    match cmd.action {
        Action::DropCol => {
            format!("ALTER TABLE {} DROP COLUMN {}", table, quoted_col)
        }
        Action::RenameCol => {
            // Find "to" or "new" in cages
            // Syntax: rename::users:old[to=new]
            let new_name_opt = cmd
                .cages
                .iter()
                .flat_map(|c| &c.conditions)
                .find(|c| {
                    let col = match &c.left {
                        Expr::Named(n) => n.as_str(),
                        _ => "",
                    };
                    matches!(col, "to" | "new" | "rename")
                })
                .map(|c| match &c.value {
                    Value::String(s) => s.clone(),
                    Value::Param(_) => "PARAM".to_string(), // unsupported
                    _ => c.value.to_string(),
                });

            if let Some(new_name) = new_name_opt {
                let quoted_new = generator.quote_identifier(&new_name);
                format!(
                    "ALTER TABLE {} RENAME COLUMN {} TO {}",
                    table, quoted_col, quoted_new
                )
            } else {
                "/* ERROR: New name required (e.g. [to=new_name]) */".to_string()
            }
        }
        _ => "/* ERROR: Unknown Column Action */".to_string(),
    }
}

/// Generate ALTER TABLE ADD COLUMN SQL (for migrations).
pub fn build_alter_add_column(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let table = generator.quote_identifier(&cmd.table);

    let mut parts = Vec::new();

    for col in &cmd.columns {
        if let Expr::Def {
            name,
            data_type,
            constraints,
        } = col
        {
            let sql_type = map_type(data_type);
            let quoted_name = generator.quote_identifier(name);

            let mut col_def = format!("{} {}", quoted_name, sql_type);

            let is_nullable = constraints.contains(&Constraint::Nullable);
            if !is_nullable {
                col_def.push_str(" NOT NULL");
            }

            for constraint in constraints {
                if let Constraint::Default(val) = constraint {
                    col_def.push_str(" DEFAULT ");
                    let sql_default = match val.as_str() {
                        "uuid()" => "gen_random_uuid()",
                        "now()" => "NOW()",
                        other => other,
                    };
                    col_def.push_str(sql_default);
                }
            }

            parts.push(format!("ALTER TABLE {} ADD COLUMN {}", table, col_def));
        }
    }

    parts.join(";\n")
}

/// Generate ALTER TABLE DROP COLUMN SQL (for migrations).
pub fn build_alter_drop_column(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let table = generator.quote_identifier(&cmd.table);

    let mut parts = Vec::new();

    for col in &cmd.columns {
        let col_name = match col {
            Expr::Named(n) => n.clone(),
            Expr::Def { name, .. } => name.clone(),
            _ => continue,
        };

        let quoted_col = generator.quote_identifier(&col_name);
        parts.push(format!("ALTER TABLE {} DROP COLUMN {}", table, quoted_col));
    }

    parts.join(";\n")
}

/// Generate ALTER TABLE ALTER COLUMN TYPE SQL (for migrations).
pub fn build_alter_column_type(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let table = generator.quote_identifier(&cmd.table);

    let mut parts = Vec::new();

    for col in &cmd.columns {
        let (col_name, new_type) = match col {
            Expr::Def {
                name, data_type, ..
            } => (name.clone(), data_type.clone()),
            _ => continue,
        };

        let quoted_col = generator.quote_identifier(&col_name);
        parts.push(format!(
            "ALTER TABLE {} ALTER COLUMN {} TYPE {}",
            table, quoted_col, new_type
        ));
    }

    parts.join(";\n")
}

// ============================================================================
// Phase 7: Extensions, Comments, Sequences
// ============================================================================

/// Generate CREATE EXTENSION IF NOT EXISTS SQL.
pub fn build_create_extension(cmd: &Qail, _dialect: Dialect) -> String {
    // table field holds extension name, columns[0] may hold schema, columns[1] may hold version
    let mut sql = format!(
        "CREATE EXTENSION IF NOT EXISTS \"{}\"",
        cmd.table.replace('"', "\"\"")
    );

    for col in &cmd.columns {
        match col {
            Expr::Named(val) if val.starts_with("SCHEMA ") => {
                sql.push_str(&format!(" {}", val));
            }
            Expr::Named(val) if val.starts_with("VERSION ") => {
                sql.push_str(&format!(" {}", val));
            }
            _ => {}
        }
    }

    sql
}

/// Generate DROP EXTENSION IF EXISTS SQL.
pub fn build_drop_extension(cmd: &Qail, _dialect: Dialect) -> String {
    format!(
        "DROP EXTENSION IF EXISTS \"{}\"",
        cmd.table.replace('"', "\"\"")
    )
}

/// Generate COMMENT ON SQL.
pub fn build_comment_on(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();

    // table field holds the target: "TABLE tablename" or "COLUMN table.column"
    // columns[0] holds the comment text as Expr::Named
    let comment_text = cmd
        .columns
        .first()
        .map(|c| match c {
            Expr::Named(s) => s.clone(),
            _ => String::new(),
        })
        .unwrap_or_default();

    // Escape single quotes in comment text
    let escaped = comment_text.replace('\'', "''");

    let trimmed = cmd.table.trim();
    let upper = trimmed.to_ascii_uppercase();
    let has_explicit_kind = upper.starts_with("TABLE ")
        || upper.starts_with("COLUMN ")
        || upper.starts_with("FUNCTION ")
        || upper.starts_with("TYPE ")
        || upper.starts_with("POLICY ")
        || upper.starts_with("CONSTRAINT ")
        || upper.starts_with("INDEX ")
        || upper.starts_with("SEQUENCE ")
        || upper.starts_with("VIEW ")
        || upper.starts_with("MATERIALIZED VIEW ")
        || upper.starts_with("SCHEMA ");

    if has_explicit_kind {
        format!("COMMENT ON {} IS '{}'", trimmed, escaped)
    } else if cmd.table.contains('.') {
        // COMMENT ON COLUMN table.column IS '...'
        let parts: Vec<&str> = cmd.table.splitn(2, '.').collect();
        format!(
            "COMMENT ON COLUMN {}.{} IS '{}'",
            generator.quote_identifier(parts[0]),
            generator.quote_identifier(parts[1]),
            escaped
        )
    } else {
        // COMMENT ON TABLE table IS '...'
        format!(
            "COMMENT ON TABLE {} IS '{}'",
            generator.quote_identifier(&cmd.table),
            escaped
        )
    }
}

/// Generate CREATE SEQUENCE SQL.
pub fn build_create_sequence(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let mut sql = format!("CREATE SEQUENCE {}", generator.quote_identifier(&cmd.table));

    for col in &cmd.columns {
        if let Expr::Named(opt) = col {
            sql.push(' ');
            sql.push_str(opt);
        }
    }

    sql
}

/// Generate DROP SEQUENCE SQL.
pub fn build_drop_sequence(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    format!(
        "DROP SEQUENCE IF EXISTS {}",
        generator.quote_identifier(&cmd.table)
    )
}

/// Generate `CREATE TYPE ... AS ENUM` SQL from an AST command.
///
/// Enum values are taken from `cmd.columns` (each as a `Named` expression).
///
/// # Arguments
///
/// * `cmd` — Qail AST command whose `table` is the type name and `columns` are enum values.
/// * `dialect` — Target SQL dialect for identifier quoting.
pub fn build_create_enum(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let values: Vec<String> = cmd
        .columns
        .iter()
        .filter_map(|c| match c {
            Expr::Named(v) => Some(format!("'{}'", v.replace('\'', "''"))),
            _ => None,
        })
        .collect();

    format!(
        "CREATE TYPE {} AS ENUM ({})",
        generator.quote_identifier(&cmd.table),
        values.join(", ")
    )
}

/// Generate DROP TYPE SQL.
pub fn build_drop_enum(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    format!(
        "DROP TYPE IF EXISTS {}",
        generator.quote_identifier(&cmd.table)
    )
}

/// Generate `ALTER TYPE ... ADD VALUE` SQL for one or more new enum values.
///
/// Uses `IF NOT EXISTS` to be idempotent.
///
/// # Arguments
///
/// * `cmd` — Qail AST command whose `table` is the type name and `columns` are the new values.
/// * `dialect` — Target SQL dialect for identifier quoting.
pub fn build_alter_enum_add_value(cmd: &Qail, dialect: Dialect) -> String {
    let generator = dialect.generator();
    let mut parts = Vec::new();

    for col in &cmd.columns {
        if let Expr::Named(val) = col {
            parts.push(format!(
                "ALTER TYPE {} ADD VALUE IF NOT EXISTS '{}'",
                generator.quote_identifier(&cmd.table),
                val.replace('\'', "''")
            ));
        }
    }

    parts.join(";\n")
}