pgroles_core/

sql.rs

//! SQL generation from [`Change`] operations.
//!
//! Each [`Change`] variant is rendered into one or more PostgreSQL DDL
//! statements. All identifiers are double-quoted to handle names containing
//! hyphens, dots, `@` signs, etc.

use std::collections::{BTreeMap, BTreeSet};
use std::fmt::Write;

use crate::diff::Change;
use crate::manifest::{ObjectType, Privilege};
use crate::model::{RoleAttribute, RoleState};

// ---------------------------------------------------------------------------
// Identifier quoting
// ---------------------------------------------------------------------------

/// Double-quote a PostgreSQL identifier, escaping any embedded double quotes.
///
/// ```
/// use pgroles_core::sql::quote_ident;
/// assert_eq!(quote_ident("simple"), r#""simple""#);
/// assert_eq!(quote_ident("has\"quote"), r#""has""quote""#);
/// assert_eq!(quote_ident("user@example.com"), r#""user@example.com""#);
/// ```
pub fn quote_ident(identifier: &str) -> String {
    format!("\"{}\"", identifier.replace('"', "\"\""))
}

// ---------------------------------------------------------------------------
// SQL context for version-dependent rendering
// ---------------------------------------------------------------------------

/// Context controlling version-dependent SQL generation.
#[derive(Debug, Clone)]
pub struct SqlContext {
    /// PostgreSQL major version (e.g., 14, 15, 16, 17).
    /// Controls syntax differences like `WITH INHERIT` in membership grants.
    pub pg_major_version: i32,
    /// Optional schema-scoped catalog inventory used to safely expand wildcard
    /// relation statements without leaking across relation subtypes.
    pub relation_inventory: BTreeMap<(ObjectType, String), Vec<String>>,
}

impl SqlContext {
    /// Create a context for a specific PG version number (from `server_version_num`).
    pub fn from_version_num(version_num: i32) -> Self {
        Self {
            pg_major_version: version_num / 10000,
            relation_inventory: BTreeMap::new(),
        }
    }

    /// Attach live relation inventory for safer wildcard rendering.
    pub fn with_relation_inventory(
        mut self,
        relation_inventory: BTreeMap<(ObjectType, String), Vec<String>>,
    ) -> Self {
        self.relation_inventory = relation_inventory;
        self
    }

    /// Whether PG supports `GRANT ... WITH INHERIT TRUE/FALSE` (PG 16+).
    pub fn supports_grant_with_options(&self) -> bool {
        self.pg_major_version >= 16
    }
}

impl Default for SqlContext {
    fn default() -> Self {
        Self {
            pg_major_version: 16, // Default to PG 16+ (current minimum)
            relation_inventory: BTreeMap::new(),
        }
    }
}

// ---------------------------------------------------------------------------
// SQL rendering
// ---------------------------------------------------------------------------

/// Render a single [`Change`] into a SQL statement (including trailing `;`).
/// Uses default context (PG 16+).
pub fn render(change: &Change) -> String {
    render_statements(change).join("\n")
}

/// Render a single [`Change`] into one or more SQL statements.
/// Uses default context (PG 16+).
pub fn render_statements(change: &Change) -> Vec<String> {
    render_statements_with_context(change, &SqlContext::default())
}

/// Render a single [`Change`] into one or more SQL statements,
/// using the given [`SqlContext`] for version-dependent syntax.
pub fn render_statements_with_context(change: &Change, ctx: &SqlContext) -> Vec<String> {
    match change {
        Change::CreateRole { name, state } => render_create_role(name, state),
        Change::AlterRole { name, attributes } => render_alter_role(name, attributes),
        Change::SetComment { name, comment } => render_set_comment(name, comment),
        Change::Grant {
            role,
            privileges,
            object_type,
            schema,
            name,
        } => render_grant(
            role,
            privileges,
            *object_type,
            schema.as_deref(),
            name.as_deref(),
            ctx,
        ),
        Change::Revoke {
            role,
            privileges,
            object_type,
            schema,
            name,
        } => render_revoke(
            role,
            privileges,
            *object_type,
            schema.as_deref(),
            name.as_deref(),
            ctx,
        ),
        Change::SetDefaultPrivilege {
            owner,
            schema,
            on_type,
            grantee,
            privileges,
        } => render_set_default_privilege(owner, schema, *on_type, grantee, privileges),
        Change::RevokeDefaultPrivilege {
            owner,
            schema,
            on_type,
            grantee,
            privileges,
        } => render_revoke_default_privilege(owner, schema, *on_type, grantee, privileges),
        Change::AddMember {
            role,
            member,
            inherit,
            admin,
        } => render_add_member(role, member, *inherit, *admin, ctx),
        Change::RemoveMember { role, member } => render_remove_member(role, member),
        Change::ReassignOwned { from_role, to_role } => render_reassign_owned(from_role, to_role),
        Change::DropOwned { role } => render_drop_owned(role),
        Change::TerminateSessions { role } => render_terminate_sessions(role),
        Change::SetPassword { name, password } => render_set_password(name, password),
        Change::DropRole { name } => vec![format!("DROP ROLE IF EXISTS {};", quote_ident(name))],
    }
}

/// Render all changes into a single SQL script (default context, PG 16+).
pub fn render_all(changes: &[Change]) -> String {
    render_all_with_context(changes, &SqlContext::default())
}

/// Render all changes into a single SQL script with version context.
pub fn render_all_with_context(changes: &[Change], ctx: &SqlContext) -> String {
    changes
        .iter()
        .flat_map(|c| render_statements_with_context(c, ctx))
        .collect::<Vec<_>>()
        .join("\n")
}

// ---------------------------------------------------------------------------
// CREATE ROLE
// ---------------------------------------------------------------------------

// NOTE: PostgreSQL does not support `CREATE ROLE IF NOT EXISTS` natively.
// The PL/pgSQL idiom `DO $$ BEGIN CREATE ROLE ...; EXCEPTION WHEN
// duplicate_object THEN NULL; END $$;` would work but changes the SQL
// profile significantly. Instead, we rely on the plan-phase lifecycle
// (Pending -> Approved -> Applying -> Applied) and transaction atomicity
// to guarantee that a CREATE ROLE is not re-executed after a successful
// commit. The only edge case is a committed transaction whose K8s status
// update failed, which is handled by the plan's sql_hash deduplication.
fn render_create_role(name: &str, state: &RoleState) -> Vec<String> {
    let mut sql = format!("CREATE ROLE {}", quote_ident(name));
    let mut options = vec![
        bool_option("LOGIN", "NOLOGIN", state.login),
        bool_option("SUPERUSER", "NOSUPERUSER", state.superuser),
        bool_option("CREATEDB", "NOCREATEDB", state.createdb),
        bool_option("CREATEROLE", "NOCREATEROLE", state.createrole),
        bool_option("INHERIT", "NOINHERIT", state.inherit),
        bool_option("REPLICATION", "NOREPLICATION", state.replication),
        bool_option("BYPASSRLS", "NOBYPASSRLS", state.bypassrls),
    ];

    if state.connection_limit != -1 {
        options.push(format!("CONNECTION LIMIT {}", state.connection_limit));
    }

    if let Some(valid_until) = &state.password_valid_until {
        options.push(format!("VALID UNTIL {}", quote_literal(valid_until)));
    }

    let _ = write!(sql, " {}", options.join(" "));
    sql.push(';');

    let mut statements = vec![sql];
    if let Some(comment) = &state.comment {
        statements.push(format!(
            "COMMENT ON ROLE {} IS {};",
            quote_ident(name),
            quote_literal(comment)
        ));
    }

    statements
}

fn bool_option(positive: &str, negative: &str, value: bool) -> String {
    if value {
        positive.to_string()
    } else {
        negative.to_string()
    }
}

// ---------------------------------------------------------------------------
// ALTER ROLE
// ---------------------------------------------------------------------------

fn render_alter_role(name: &str, attributes: &[RoleAttribute]) -> Vec<String> {
    let mut options = Vec::new();
    for attr in attributes {
        match attr {
            RoleAttribute::Login(v) => options.push(bool_option("LOGIN", "NOLOGIN", *v)),
            RoleAttribute::Superuser(v) => {
                options.push(bool_option("SUPERUSER", "NOSUPERUSER", *v));
            }
            RoleAttribute::Createdb(v) => {
                options.push(bool_option("CREATEDB", "NOCREATEDB", *v));
            }
            RoleAttribute::Createrole(v) => {
                options.push(bool_option("CREATEROLE", "NOCREATEROLE", *v));
            }
            RoleAttribute::Inherit(v) => options.push(bool_option("INHERIT", "NOINHERIT", *v)),
            RoleAttribute::Replication(v) => {
                options.push(bool_option("REPLICATION", "NOREPLICATION", *v));
            }
            RoleAttribute::Bypassrls(v) => {
                options.push(bool_option("BYPASSRLS", "NOBYPASSRLS", *v));
            }
            RoleAttribute::ConnectionLimit(v) => {
                options.push(format!("CONNECTION LIMIT {v}"));
            }
            RoleAttribute::ValidUntil(v) => match v {
                Some(ts) => options.push(format!("VALID UNTIL {}", quote_literal(ts))),
                None => options.push("VALID UNTIL 'infinity'".to_string()),
            },
        }
    }
    vec![format!(
        "ALTER ROLE {} {};",
        quote_ident(name),
        options.join(" ")
    )]
}

// ---------------------------------------------------------------------------
// COMMENT ON ROLE
// ---------------------------------------------------------------------------

fn render_set_comment(name: &str, comment: &Option<String>) -> Vec<String> {
    vec![match comment {
        Some(text) => format!(
            "COMMENT ON ROLE {} IS {};",
            quote_ident(name),
            quote_literal(text)
        ),
        None => format!("COMMENT ON ROLE {} IS NULL;", quote_ident(name)),
    }]
}

// ---------------------------------------------------------------------------
// GRANT / REVOKE
// ---------------------------------------------------------------------------

fn render_grant(
    role: &str,
    privileges: &BTreeSet<Privilege>,
    object_type: ObjectType,
    schema: Option<&str>,
    name: Option<&str>,
    ctx: &SqlContext,
) -> Vec<String> {
    let privilege_list = format_privileges(privileges);
    render_privilege_statements(
        "GRANT",
        role,
        &privilege_list,
        object_type,
        schema,
        name,
        ctx,
    )
}

fn render_revoke(
    role: &str,
    privileges: &BTreeSet<Privilege>,
    object_type: ObjectType,
    schema: Option<&str>,
    name: Option<&str>,
    ctx: &SqlContext,
) -> Vec<String> {
    let privilege_list = format_privileges(privileges);
    render_privilege_statements(
        "REVOKE",
        role,
        &privilege_list,
        object_type,
        schema,
        name,
        ctx,
    )
}

fn render_privilege_statements(
    action: &str,
    role: &str,
    privilege_list: &str,
    object_type: ObjectType,
    schema: Option<&str>,
    name: Option<&str>,
    ctx: &SqlContext,
) -> Vec<String> {
    let subject_preposition = if action == "GRANT" { "TO" } else { "FROM" };
    if matches!(
        object_type,
        ObjectType::Table | ObjectType::View | ObjectType::MaterializedView
    ) && name == Some("*")
    {
        return render_relation_wildcard(
            action,
            subject_preposition,
            role,
            privilege_list,
            object_type,
            schema,
            ctx,
        );
    }

    let target = format_object_target(object_type, schema, name);
    vec![format!(
        "{action} {privilege_list} ON {target} {subject_preposition} {};",
        quote_ident(role)
    )]
}

fn render_relation_wildcard(
    action: &str,
    subject_preposition: &str,
    role: &str,
    privilege_list: &str,
    object_type: ObjectType,
    schema: Option<&str>,
    ctx: &SqlContext,
) -> Vec<String> {
    let schema_name = schema.unwrap_or("public");

    if let Some(object_names) = ctx
        .relation_inventory
        .get(&(object_type, schema_name.to_string()))
    {
        return object_names
            .iter()
            .map(|object_name| {
                format!(
                    "{action} {privilege_list} ON TABLE {}.{} {subject_preposition} {};",
                    quote_ident(schema_name),
                    quote_ident(object_name),
                    quote_ident(role),
                )
            })
            .collect();
    }

    vec![format!(
        "DO $pgroles$\nDECLARE obj record;\nBEGIN\n  FOR obj IN\n    SELECT n.nspname AS schema_name, c.relname AS object_name\n    FROM pg_class c\n    JOIN pg_namespace n ON n.oid = c.relnamespace\n    WHERE c.relkind IN ({})\n      AND n.nspname = {}\n    ORDER BY c.relname\n  LOOP\n    EXECUTE format('{} {} ON TABLE %I.%I {} %I;', obj.schema_name, obj.object_name, {});\n  END LOOP;\nEND\n$pgroles$;",
        relation_relkinds_sql(object_type),
        quote_literal(schema_name),
        action,
        privilege_list,
        subject_preposition,
        quote_literal(role),
    )]
}

fn relation_relkinds_sql(object_type: ObjectType) -> &'static str {
    match object_type {
        ObjectType::Table => "'r', 'p'",
        ObjectType::View => "'v'",
        ObjectType::MaterializedView => "'m'",
        _ => unreachable!("relation_relkinds_literal only supports relation object types"),
    }
}

/// Format the object target for GRANT/REVOKE statements.
///
/// - Schema-level: `SCHEMA "myschema"` — object_type=Schema, name=Some("myschema")
/// - Wildcard: `ALL TABLES IN SCHEMA "myschema"` — name=Some("*")
/// - Specific: `TABLE "myschema"."mytable"` — name=Some("mytable")
/// - Database: `DATABASE "mydb"` — object_type=Database, name=Some("mydb")
fn format_object_target(
    object_type: ObjectType,
    schema: Option<&str>,
    name: Option<&str>,
) -> String {
    let type_keyword = sql_object_type_keyword(object_type);

    match object_type {
        ObjectType::Schema => {
            // Schema grants: name is the schema name itself
            let schema_name = name.unwrap_or("public");
            format!("{type_keyword} {}", quote_ident(schema_name))
        }
        ObjectType::Database => {
            let db_name = name.unwrap_or("postgres");
            format!("{type_keyword} {}", quote_ident(db_name))
        }
        ObjectType::Function => match name {
            Some("*") => {
                let schema_name = schema.unwrap_or("public");
                format!("ALL FUNCTIONS IN SCHEMA {}", quote_ident(schema_name))
            }
            Some(function_name) => format_function_target(schema, function_name),
            None => {
                let schema_name = schema.unwrap_or("public");
                format!("{type_keyword} {}", quote_ident(schema_name))
            }
        },
        _ => {
            match name {
                Some("*") => {
                    // Wildcard: ALL TABLES IN SCHEMA "schema"
                    let plural = sql_object_type_plural(object_type);
                    let schema_name = schema.unwrap_or("public");
                    format!("ALL {plural} IN SCHEMA {}", quote_ident(schema_name))
                }
                Some(obj_name) => {
                    // Specific object: TABLE "schema"."table"
                    let schema_name = schema.unwrap_or("public");
                    format!(
                        "{type_keyword} {}.{}",
                        quote_ident(schema_name),
                        quote_ident(obj_name)
                    )
                }
                None => {
                    // Shouldn't happen for non-schema/database types, but handle gracefully
                    let schema_name = schema.unwrap_or("public");
                    format!("{type_keyword} {}", quote_ident(schema_name))
                }
            }
        }
    }
}

fn format_function_target(schema: Option<&str>, function_name: &str) -> String {
    let schema_name = schema.unwrap_or("public");

    match function_name.rfind('(') {
        Some(paren_idx) if function_name.ends_with(')') => {
            let base_name = &function_name[..paren_idx];
            let args = &function_name[paren_idx..];
            format!(
                "FUNCTION {}.{}{}",
                quote_ident(schema_name),
                quote_ident(base_name),
                args
            )
        }
        _ => format!(
            "FUNCTION {}.{}",
            quote_ident(schema_name),
            quote_ident(function_name)
        ),
    }
}

/// Map ObjectType to the SQL keyword used in GRANT/REVOKE.
fn sql_object_type_keyword(object_type: ObjectType) -> &'static str {
    match object_type {
        ObjectType::Table => "TABLE",
        ObjectType::View => "TABLE", // PostgreSQL treats views as tables for GRANT
        ObjectType::MaterializedView => "TABLE", // Same
        ObjectType::Sequence => "SEQUENCE",
        ObjectType::Function => "FUNCTION",
        ObjectType::Schema => "SCHEMA",
        ObjectType::Database => "DATABASE",
        ObjectType::Type => "TYPE",
    }
}

/// Map ObjectType to the SQL plural keyword used in ALL ... IN SCHEMA.
fn sql_object_type_plural(object_type: ObjectType) -> &'static str {
    match object_type {
        ObjectType::Table | ObjectType::View | ObjectType::MaterializedView => "TABLES",
        ObjectType::Sequence => "SEQUENCES",
        ObjectType::Function => "FUNCTIONS",
        // PostgreSQL has no ALL TYPES IN SCHEMA syntax. Type grants should use
        // specific object names, not wildcards. If we get here the manifest is
        // likely misconfigured, but produce the closest valid SQL anyway.
        ObjectType::Type => "TABLES",
        // Schema/Database don't use ALL ... IN SCHEMA syntax
        ObjectType::Schema | ObjectType::Database => "TABLES",
    }
}

/// Format a privilege set as a comma-separated string.
fn format_privileges(privileges: &BTreeSet<Privilege>) -> String {
    privileges
        .iter()
        .map(|p| p.to_string())
        .collect::<Vec<_>>()
        .join(", ")
}

// ---------------------------------------------------------------------------
// ALTER DEFAULT PRIVILEGES
// ---------------------------------------------------------------------------

fn render_set_default_privilege(
    owner: &str,
    schema: &str,
    on_type: ObjectType,
    grantee: &str,
    privileges: &BTreeSet<Privilege>,
) -> Vec<String> {
    let privilege_list = format_privileges(privileges);
    let type_plural = sql_object_type_plural(on_type);
    vec![format!(
        "ALTER DEFAULT PRIVILEGES FOR ROLE {} IN SCHEMA {} GRANT {} ON {} TO {};",
        quote_ident(owner),
        quote_ident(schema),
        privilege_list,
        type_plural,
        quote_ident(grantee)
    )]
}

fn render_revoke_default_privilege(
    owner: &str,
    schema: &str,
    on_type: ObjectType,
    grantee: &str,
    privileges: &BTreeSet<Privilege>,
) -> Vec<String> {
    let privilege_list = format_privileges(privileges);
    let type_plural = sql_object_type_plural(on_type);
    vec![format!(
        "ALTER DEFAULT PRIVILEGES FOR ROLE {} IN SCHEMA {} REVOKE {} ON {} FROM {};",
        quote_ident(owner),
        quote_ident(schema),
        privilege_list,
        type_plural,
        quote_ident(grantee)
    )]
}

// ---------------------------------------------------------------------------
// Membership
// ---------------------------------------------------------------------------

fn render_add_member(
    role: &str,
    member: &str,
    inherit: bool,
    admin: bool,
    ctx: &SqlContext,
) -> Vec<String> {
    let mut sql = format!("GRANT {} TO {}", quote_ident(role), quote_ident(member));

    if ctx.supports_grant_with_options() {
        // PostgreSQL 16+: use WITH INHERIT / ADMIN syntax.
        let mut options = Vec::new();
        if inherit {
            options.push("INHERIT TRUE");
        } else {
            options.push("INHERIT FALSE");
        }
        if admin {
            options.push("ADMIN TRUE");
        }
        if !options.is_empty() {
            let _ = write!(sql, " WITH {}", options.join(", "));
        }
    } else {
        // PostgreSQL < 16: use legacy WITH ADMIN OPTION syntax.
        // INHERIT is controlled by the member role's attribute, not the grant.
        if admin {
            sql.push_str(" WITH ADMIN OPTION");
        }
    }

    sql.push(';');
    vec![sql]
}

fn render_remove_member(role: &str, member: &str) -> Vec<String> {
    vec![format!(
        "REVOKE {} FROM {};",
        quote_ident(role),
        quote_ident(member)
    )]
}

fn render_reassign_owned(from_role: &str, to_role: &str) -> Vec<String> {
    vec![format!(
        "REASSIGN OWNED BY {} TO {};",
        quote_ident(from_role),
        quote_ident(to_role)
    )]
}

fn render_drop_owned(role: &str) -> Vec<String> {
    vec![format!("DROP OWNED BY {};", quote_ident(role))]
}

fn render_terminate_sessions(role: &str) -> Vec<String> {
    vec![format!(
        "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = {} AND pid <> pg_backend_pid();",
        quote_literal(role)
    )]
}

/// Render `ALTER ROLE ... PASSWORD` using a pre-computed SCRAM-SHA-256 verifier.
///
/// The `password` parameter is expected to be a SCRAM-SHA-256 verifier string
/// (starting with `SCRAM-SHA-256$`). PostgreSQL detects this prefix and stores
/// the verifier directly, so the cleartext password never appears in the SQL.
fn render_set_password(name: &str, password: &str) -> Vec<String> {
    vec![format!(
        "ALTER ROLE {} PASSWORD {};",
        quote_ident(name),
        quote_literal(password)
    )]
}

// ---------------------------------------------------------------------------
// String quoting
// ---------------------------------------------------------------------------

/// Single-quote a SQL string literal, escaping single quotes.
fn quote_literal(value: &str) -> String {
    format!("'{}'", value.replace('\'', "''"))
}

// ---------------------------------------------------------------------------
// Tests
// ---------------------------------------------------------------------------

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

    #[test]
    fn quote_ident_simple() {
        assert_eq!(quote_ident("simple"), "\"simple\"");
    }

    #[test]
    fn quote_ident_with_hyphen() {
        assert_eq!(quote_ident("inventory-editor"), "\"inventory-editor\"");
    }

    #[test]
    fn quote_ident_with_email() {
        assert_eq!(quote_ident("user@example.com"), "\"user@example.com\"");
    }

    #[test]
    fn quote_ident_with_embedded_quotes() {
        assert_eq!(quote_ident("has\"quote"), "\"has\"\"quote\"");
    }

    #[test]
    fn quote_literal_simple() {
        assert_eq!(quote_literal("hello"), "'hello'");
    }

    #[test]
    fn quote_literal_with_embedded_quotes() {
        assert_eq!(quote_literal("it's"), "'it''s'");
    }

    #[test]
    fn render_create_role_basic() {
        let change = Change::CreateRole {
            name: "inventory-editor".to_string(),
            state: RoleState::default(),
        };
        let sql = render(&change);
        assert!(sql.starts_with("CREATE ROLE \"inventory-editor\""));
        assert!(sql.contains("NOLOGIN"));
        assert!(sql.contains("NOSUPERUSER"));
        assert!(sql.contains("INHERIT")); // default is INHERIT
        assert!(sql.ends_with(';'));
    }

    #[test]
    fn render_create_role_with_login_and_comment() {
        let change = Change::CreateRole {
            name: "analytics".to_string(),
            state: RoleState {
                login: true,
                comment: Some("Analytics readonly role".to_string()),
                ..RoleState::default()
            },
        };
        let sql = render(&change);
        assert!(sql.contains("LOGIN"));
        assert!(sql.contains("COMMENT ON ROLE \"analytics\" IS 'Analytics readonly role';"));
    }

    #[test]
    fn render_alter_role() {
        let change = Change::AlterRole {
            name: "r1".to_string(),
            attributes: vec![RoleAttribute::Login(true), RoleAttribute::Createdb(true)],
        };
        let sql = render(&change);
        assert_eq!(sql, "ALTER ROLE \"r1\" LOGIN CREATEDB;");
    }

    #[test]
    fn render_drop_role() {
        let change = Change::DropRole {
            name: "old-role".to_string(),
        };
        assert_eq!(render(&change), "DROP ROLE IF EXISTS \"old-role\";");
    }

    #[test]
    fn render_grant_schema_usage() {
        let change = Change::Grant {
            role: "inventory-editor".to_string(),
            privileges: BTreeSet::from([Privilege::Usage]),
            object_type: ObjectType::Schema,
            schema: None,
            name: Some("inventory".to_string()),
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "GRANT USAGE ON SCHEMA \"inventory\" TO \"inventory-editor\";"
        );
    }

    #[test]
    fn render_grant_all_tables() {
        let change = Change::Grant {
            role: "inventory-editor".to_string(),
            privileges: BTreeSet::from([Privilege::Select, Privilege::Insert]),
            object_type: ObjectType::Table,
            schema: Some("inventory".to_string()),
            name: Some("*".to_string()),
        };
        let sql = render_statements_with_context(
            &change,
            &SqlContext::default().with_relation_inventory(BTreeMap::from([(
                (ObjectType::Table, "inventory".to_string()),
                vec!["orders".to_string(), "widgets".to_string()],
            )])),
        )
        .join("\n");
        assert_eq!(
            sql,
            "GRANT INSERT, SELECT ON TABLE \"inventory\".\"orders\" TO \"inventory-editor\";\nGRANT INSERT, SELECT ON TABLE \"inventory\".\"widgets\" TO \"inventory-editor\";"
        );
    }

    #[test]
    fn render_grant_specific_table() {
        let change = Change::Grant {
            role: "r1".to_string(),
            privileges: BTreeSet::from([Privilege::Select]),
            object_type: ObjectType::Table,
            schema: Some("public".to_string()),
            name: Some("users".to_string()),
        };
        let sql = render(&change);
        assert_eq!(sql, "GRANT SELECT ON TABLE \"public\".\"users\" TO \"r1\";");
    }

    #[test]
    fn render_grant_specific_function() {
        let change = Change::Grant {
            role: "r1".to_string(),
            privileges: BTreeSet::from([Privilege::Execute]),
            object_type: ObjectType::Function,
            schema: Some("public".to_string()),
            name: Some("refresh_users(integer, text)".to_string()),
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "GRANT EXECUTE ON FUNCTION \"public\".\"refresh_users\"(integer, text) TO \"r1\";"
        );
    }

    #[test]
    fn render_revoke_all_sequences() {
        let change = Change::Revoke {
            role: "inventory-editor".to_string(),
            privileges: BTreeSet::from([Privilege::Usage, Privilege::Select]),
            object_type: ObjectType::Sequence,
            schema: Some("inventory".to_string()),
            name: Some("*".to_string()),
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "REVOKE SELECT, USAGE ON ALL SEQUENCES IN SCHEMA \"inventory\" FROM \"inventory-editor\";"
        );
    }

    #[test]
    fn render_set_default_privilege() {
        let change = Change::SetDefaultPrivilege {
            owner: "app_owner".to_string(),
            schema: "inventory".to_string(),
            on_type: ObjectType::Table,
            grantee: "inventory-editor".to_string(),
            privileges: BTreeSet::from([Privilege::Select, Privilege::Insert]),
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "ALTER DEFAULT PRIVILEGES FOR ROLE \"app_owner\" IN SCHEMA \"inventory\" GRANT INSERT, SELECT ON TABLES TO \"inventory-editor\";"
        );
    }

    #[test]
    fn render_revoke_default_privilege() {
        let change = Change::RevokeDefaultPrivilege {
            owner: "app_owner".to_string(),
            schema: "inventory".to_string(),
            on_type: ObjectType::Function,
            grantee: "inventory-editor".to_string(),
            privileges: BTreeSet::from([Privilege::Execute]),
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "ALTER DEFAULT PRIVILEGES FOR ROLE \"app_owner\" IN SCHEMA \"inventory\" REVOKE EXECUTE ON FUNCTIONS FROM \"inventory-editor\";"
        );
    }

    #[test]
    fn render_add_member_basic() {
        let change = Change::AddMember {
            role: "inventory-editor".to_string(),
            member: "user@example.com".to_string(),
            inherit: true,
            admin: false,
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "GRANT \"inventory-editor\" TO \"user@example.com\" WITH INHERIT TRUE;"
        );
    }

    #[test]
    fn render_add_member_with_admin() {
        let change = Change::AddMember {
            role: "inventory-editor".to_string(),
            member: "admin@example.com".to_string(),
            inherit: true,
            admin: true,
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "GRANT \"inventory-editor\" TO \"admin@example.com\" WITH INHERIT TRUE, ADMIN TRUE;"
        );
    }

    #[test]
    fn render_add_member_no_inherit() {
        let change = Change::AddMember {
            role: "inventory-editor".to_string(),
            member: "noinherit@example.com".to_string(),
            inherit: false,
            admin: false,
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "GRANT \"inventory-editor\" TO \"noinherit@example.com\" WITH INHERIT FALSE;"
        );
    }

    #[test]
    fn render_remove_member() {
        let change = Change::RemoveMember {
            role: "inventory-editor".to_string(),
            member: "user@example.com".to_string(),
        };
        let sql = render(&change);
        assert_eq!(
            sql,
            "REVOKE \"inventory-editor\" FROM \"user@example.com\";"
        );
    }

    #[test]
    fn render_reassign_owned() {
        let change = Change::ReassignOwned {
            from_role: "legacy-owner".to_string(),
            to_role: "app-owner".to_string(),
        };
        assert_eq!(
            render(&change),
            "REASSIGN OWNED BY \"legacy-owner\" TO \"app-owner\";"
        );
    }

    #[test]
    fn render_drop_owned() {
        let change = Change::DropOwned {
            role: "legacy-owner".to_string(),
        };
        assert_eq!(render(&change), "DROP OWNED BY \"legacy-owner\";");
    }

    #[test]
    fn render_terminate_sessions() {
        let change = Change::TerminateSessions {
            role: "legacy-owner".to_string(),
        };
        assert_eq!(
            render(&change),
            "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'legacy-owner' AND pid <> pg_backend_pid();"
        );
    }

    #[test]
    fn render_set_comment_some() {
        let change = Change::SetComment {
            name: "r1".to_string(),
            comment: Some("A test role".to_string()),
        };
        assert_eq!(render(&change), "COMMENT ON ROLE \"r1\" IS 'A test role';");
    }

    #[test]
    fn render_set_comment_none() {
        let change = Change::SetComment {
            name: "r1".to_string(),
            comment: None,
        };
        assert_eq!(render(&change), "COMMENT ON ROLE \"r1\" IS NULL;");
    }

    // -----------------------------------------------------------------------
    // PG version-dependent rendering
    // -----------------------------------------------------------------------

    #[test]
    fn render_add_member_pg15_legacy_syntax() {
        let ctx = SqlContext {
            pg_major_version: 15,
            ..Default::default()
        };
        let change = Change::AddMember {
            role: "editors".to_string(),
            member: "user@example.com".to_string(),
            inherit: true,
            admin: false,
        };
        let sql = render_statements_with_context(&change, &ctx).join("\n");
        assert_eq!(sql, "GRANT \"editors\" TO \"user@example.com\";");
    }

    #[test]
    fn render_add_member_pg15_with_admin() {
        let ctx = SqlContext {
            pg_major_version: 15,
            ..Default::default()
        };
        let change = Change::AddMember {
            role: "editors".to_string(),
            member: "admin@example.com".to_string(),
            inherit: true,
            admin: true,
        };
        let sql = render_statements_with_context(&change, &ctx).join("\n");
        assert_eq!(
            sql,
            "GRANT \"editors\" TO \"admin@example.com\" WITH ADMIN OPTION;"
        );
    }

    #[test]
    fn render_add_member_pg16_with_options() {
        let ctx = SqlContext {
            pg_major_version: 16,
            ..Default::default()
        };
        let change = Change::AddMember {
            role: "editors".to_string(),
            member: "user@example.com".to_string(),
            inherit: false,
            admin: true,
        };
        let sql = render_statements_with_context(&change, &ctx).join("\n");
        assert_eq!(
            sql,
            "GRANT \"editors\" TO \"user@example.com\" WITH INHERIT FALSE, ADMIN TRUE;"
        );
    }

    #[test]
    fn render_materialized_view_wildcard_with_inventory_expands_per_object() {
        let ctx = SqlContext::default().with_relation_inventory(BTreeMap::from([(
            (ObjectType::MaterializedView, "reporting".to_string()),
            vec!["daily_sales".to_string(), "weekly_sales".to_string()],
        )]));
        let change = Change::Revoke {
            role: "analytics".to_string(),
            privileges: [Privilege::Select].into_iter().collect(),
            object_type: ObjectType::MaterializedView,
            schema: Some("reporting".to_string()),
            name: Some("*".to_string()),
        };

        let sql = render_statements_with_context(&change, &ctx);
        assert_eq!(
            sql,
            vec![
                "REVOKE SELECT ON TABLE \"reporting\".\"daily_sales\" FROM \"analytics\";"
                    .to_string(),
                "REVOKE SELECT ON TABLE \"reporting\".\"weekly_sales\" FROM \"analytics\";"
                    .to_string(),
            ]
        );
    }

    #[test]
    fn render_materialized_view_wildcard_without_inventory_uses_catalog_loop() {
        let change = Change::Revoke {
            role: "analytics".to_string(),
            privileges: [Privilege::Select].into_iter().collect(),
            object_type: ObjectType::MaterializedView,
            schema: Some("reporting".to_string()),
            name: Some("*".to_string()),
        };

        let sql = render_statements_with_context(&change, &SqlContext::default());
        assert_eq!(sql.len(), 1);
        assert!(sql[0].contains("WHERE c.relkind IN ('m')"));
        assert!(sql[0].contains("REVOKE SELECT ON TABLE %I.%I FROM %I;"));
    }

    // -----------------------------------------------------------------------
    // JSON serialization of changes
    // -----------------------------------------------------------------------

    #[test]
    fn change_serializes_to_json() {
        let change = Change::CreateRole {
            name: "test".to_string(),
            state: RoleState::default(),
        };
        let json = serde_json::to_string(&change).unwrap();
        assert!(json.contains("CreateRole"));
        assert!(json.contains("test"));
    }

    /// Full integration: manifest → expand → model → diff → SQL
    #[test]
    fn full_pipeline_manifest_to_sql() {
        use crate::diff::diff;
        use crate::manifest::{expand_manifest, parse_manifest};
        use crate::model::RoleGraph;

        let yaml = r#"
default_owner: app_owner

profiles:
  editor:
    grants:
      - privileges: [USAGE]
        object: { type: schema }
      - privileges: [SELECT, INSERT, UPDATE, DELETE]
        object: { type: table, name: "*" }
    default_privileges:
      - privileges: [SELECT, INSERT, UPDATE, DELETE]
        on_type: table

schemas:
  - name: inventory
    profiles: [editor]

memberships:
  - role: inventory-editor
    members:
      - name: "user@example.com"
"#;
        let manifest = parse_manifest(yaml).unwrap();
        let expanded = expand_manifest(&manifest).unwrap();
        let desired =
            RoleGraph::from_expanded(&expanded, manifest.default_owner.as_deref()).unwrap();
        let current = RoleGraph::default();

        let changes = diff(&current, &desired);
        let sql = render_all(&changes);

        // Smoke test: the output should contain key SQL statements
        assert!(sql.contains("CREATE ROLE \"inventory-editor\""));
        assert!(sql.contains("GRANT USAGE ON SCHEMA \"inventory\" TO \"inventory-editor\""));
        assert!(sql.contains("GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE"));
        assert!(sql.contains("ALTER DEFAULT PRIVILEGES"));
        assert!(sql.contains("GRANT \"inventory-editor\" TO \"user@example.com\""));

        // Print for manual inspection during development
        #[cfg(test)]
        {
            eprintln!("--- Generated SQL ---\n{sql}\n--- End ---");
        }
    }

    #[test]
    fn render_set_password() {
        let change = Change::SetPassword {
            name: "app-service".to_string(),
            password: "s3cret!".to_string(),
        };
        let sql = render(&change);
        assert_eq!(sql, "ALTER ROLE \"app-service\" PASSWORD 's3cret!';");
    }

    #[test]
    fn render_set_password_escapes_quotes() {
        let change = Change::SetPassword {
            name: "r1".to_string(),
            password: "pass'word".to_string(),
        };
        let sql = render(&change);
        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pass''word';");
    }

    #[test]
    fn render_set_password_with_backslash() {
        let change = Change::SetPassword {
            name: "r1".to_string(),
            password: r"pass\word".to_string(),
        };
        let sql = render(&change);
        assert_eq!(sql, r#"ALTER ROLE "r1" PASSWORD 'pass\word';"#);
    }

    #[test]
    fn render_set_password_with_dollar_signs() {
        let change = Change::SetPassword {
            name: "r1".to_string(),
            password: "pa$$word".to_string(),
        };
        let sql = render(&change);
        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pa$$word';");
    }

    #[test]
    fn render_set_password_with_unicode() {
        let change = Change::SetPassword {
            name: "r1".to_string(),
            password: "pässwörd_日本語".to_string(),
        };
        let sql = render(&change);
        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pässwörd_日本語';");
    }

    #[test]
    fn render_set_password_with_newline() {
        let change = Change::SetPassword {
            name: "r1".to_string(),
            password: "line1\nline2".to_string(),
        };
        let sql = render(&change);
        // Newlines are passed through in single-quoted strings — PostgreSQL handles them.
        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'line1\nline2';");
    }

    #[test]
    fn quote_literal_with_backslash() {
        // PostgreSQL standard_conforming_strings=on (default since 9.1):
        // backslashes are literal in standard strings.
        assert_eq!(quote_literal(r"back\slash"), r"'back\slash'");
    }

    #[test]
    fn quote_literal_with_multiple_quotes() {
        assert_eq!(quote_literal("it's a 'test'"), "'it''s a ''test'''");
    }

    #[test]
    fn render_create_role_with_valid_until() {
        let change = Change::CreateRole {
            name: "expiring-role".to_string(),
            state: RoleState {
                login: true,
                password_valid_until: Some("2025-12-31T00:00:00Z".to_string()),
                ..RoleState::default()
            },
        };
        let sql = render(&change);
        assert!(sql.contains("LOGIN"));
        assert!(sql.contains("VALID UNTIL '2025-12-31T00:00:00Z'"));
    }

    #[test]
    fn render_alter_role_valid_until_set() {
        let change = Change::AlterRole {
            name: "r1".to_string(),
            attributes: vec![RoleAttribute::ValidUntil(Some(
                "2025-06-01T00:00:00Z".to_string(),
            ))],
        };
        let sql = render(&change);
        assert_eq!(sql, "ALTER ROLE \"r1\" VALID UNTIL '2025-06-01T00:00:00Z';");
    }

    #[test]
    fn render_alter_role_valid_until_remove() {
        let change = Change::AlterRole {
            name: "r1".to_string(),
            attributes: vec![RoleAttribute::ValidUntil(None)],
        };
        let sql = render(&change);
        assert_eq!(sql, "ALTER ROLE \"r1\" VALID UNTIL 'infinity';");
    }
}