fraiseql_cli/commands/

generate_views.rs

//! Generate DDL for Arrow views (va_*, tv_*, ta_*)
//!
//! This command generates SQL DDL statements for creating Arrow-optimized views
//! from a compiled FraiseQL schema. It supports multiple view types:
//!
//! - `va_*` (Vector Arrow views) - For vector search and analytics
//! - `tv_*` (Table Vector views) - For materialized table vectors
//! - `ta_*` (Table Arrow views) - For Arrow Flight table streaming
//!
//! The command validates the schema, entity, and view configuration before generation.

use std::{fs, path::Path};

use anyhow::{Context, Result};
use fraiseql_core::schema::CompiledSchema;

use crate::output::OutputFormatter;

/// Refresh strategy for view updates
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
#[non_exhaustive]
pub enum RefreshStrategy {
    /// Update via database triggers on fact table changes
    TriggerBased,
    /// Update on a scheduled interval
    Scheduled,
}

impl RefreshStrategy {
    /// Parse from string
    ///
    /// # Errors
    ///
    /// Returns an error if the string does not match a known refresh strategy.
    pub fn parse(s: &str) -> std::result::Result<Self, String> {
        match s.to_lowercase().as_str() {
            "trigger-based" | "trigger" => Ok(Self::TriggerBased),
            "scheduled" => Ok(Self::Scheduled),
            _ => Err(format!("Invalid refresh strategy '{s}', expected: trigger-based, scheduled")),
        }
    }
}

impl std::fmt::Display for RefreshStrategy {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            Self::TriggerBased => write!(f, "trigger-based"),
            Self::Scheduled => write!(f, "scheduled"),
        }
    }
}

/// Configuration for view generation
#[derive(Debug, Clone)]
pub struct GenerateViewsConfig {
    /// Path to schema.json file
    pub schema_path: String,
    /// Entity name (e.g., "User", "Order")
    pub entity: String,
    /// View name (e.g., "tv_user_profile", "ta_orders")
    pub view: String,
    /// Refresh strategy for view updates
    pub refresh_strategy: RefreshStrategy,
    /// Output file path (or None for stdout)
    pub output: Option<String>,
    /// Include helper/composition views
    pub include_composition_views: bool,
    /// Include monitoring functions (performance tracking, etc.)
    pub include_monitoring: bool,
    /// Validate only, don't write file
    pub validate_only: bool,
    /// Show generation steps
    pub verbose: bool,
}

/// Run the generate-views command
///
/// # Arguments
///
/// * `config` - Generation configuration
///
/// # Errors
///
/// Returns error if:
/// - Schema file doesn't exist or can't be read
/// - JSON parsing fails
/// - Entity doesn't exist in schema
/// - View name validation fails
/// - `--validate` is given and the generated DDL fails to execute against `PostgreSQL` (or no
///   `DATABASE_URL` is available to execute it)
/// - Output file can't be written
pub async fn run(config: GenerateViewsConfig, formatter: &OutputFormatter) -> Result<()> {
    if config.verbose {
        formatter.progress("Generating views...");
        formatter.progress(&format!("   Schema: {}", config.schema_path));
        formatter.progress(&format!("   Entity: {}", config.entity));
        formatter.progress(&format!("   View: {}", config.view));
        formatter.progress(&format!("   Refresh strategy: {}", config.refresh_strategy));
    }

    // 1. Load schema
    let schema_path = Path::new(&config.schema_path);
    if !schema_path.exists() {
        anyhow::bail!("Schema file not found: {}", config.schema_path);
    }

    let schema_json = fs::read_to_string(schema_path).context("Failed to read schema.json")?;

    // 2. Parse compiled schema
    if config.verbose {
        formatter.progress("   ok: Reading schema...");
    }
    let schema =
        CompiledSchema::from_json(&schema_json, false).context("Failed to parse schema.json")?;

    // 3. Validate entity exists in schema
    if config.verbose {
        formatter.progress("   ok: Validating entity...");
    }
    let sql_source = resolve_entity_sql_source(&schema, &config.entity)?;

    // 4. Validate view name
    if config.verbose {
        formatter.progress("   ok: Validating view name...");
    }
    let view_type = validate_view_name(&config.view)?;

    if config.verbose {
        formatter.progress(&format!("   ok: View type: {view_type}"));
    }

    // 5. Generate SQL DDL
    if config.verbose {
        formatter.progress("   ok: Generating SQL DDL...");
    }
    let sql = generate_view_sql(
        &config.entity,
        &sql_source,
        &config.view,
        view_type,
        config.refresh_strategy,
        config.include_composition_views,
        config.include_monitoring,
    );

    // 6. If validate-only, execute the DDL for real and stop here. A validator that inspects
    //    nothing cannot fail — the previous implementation printed "View DDL is valid" for files
    //    PostgreSQL rejected (#821).
    if config.validate_only {
        validate_ddl_against_database(&sql).await?;
        println!("✓ View DDL is valid (executed against PostgreSQL, rolled back)");
        println!("  Entity: {}", config.entity);
        println!("  View: {}", config.view);
        println!("  Type: {view_type}");
        println!("  Refresh strategy: {}", config.refresh_strategy);
        println!("  Lines: {}", sql.lines().count());
        return Ok(());
    }

    // 7. Write output
    if config.verbose {
        formatter.progress("   ok: Writing output...");
    }
    let output_path = config.output.unwrap_or_else(|| format!("{}.sql", config.view));

    fs::write(&output_path, sql.clone()).context("Failed to write output file")?;

    // 8. Success message
    println!("✓ View DDL generated successfully");
    println!("  Entity: {}", config.entity);
    println!("  View: {}", config.view);
    println!("  Type: {view_type}");
    println!("  Output: {output_path}");
    println!("  Lines: {}", sql.lines().count());

    if config.include_composition_views {
        println!("  ✓ Includes composition views");
    }

    if config.include_monitoring {
        println!("  ✓ Includes monitoring functions");
    }

    if config.verbose {
        formatter.progress("\nGenerated SQL preview (first 5 lines):");
        for line in sql.lines().take(5) {
            formatter.progress(&format!("  {line}"));
        }
    }

    Ok(())
}

/// Execute the generated DDL inside a transaction that is always rolled back.
///
/// This is what makes `--validate` honest: the statements run against a real
/// `PostgreSQL` (the same engine that will execute the file), so a syntax
/// error, a missing source relation, or a column mismatch fails the command.
/// Nothing is committed. Requires `DATABASE_URL`, like `fraiseql setup`.
///
/// # Errors
///
/// Returns an error when `DATABASE_URL` is unset, the connection fails, or any
/// statement in the generated DDL is rejected by the database.
async fn validate_ddl_against_database(sql: &str) -> Result<()> {
    let db_url = std::env::var("DATABASE_URL").map_err(|_| {
        anyhow::anyhow!(
            "--validate executes the generated DDL against PostgreSQL and needs DATABASE_URL \
             (everything runs in a rolled-back transaction; a scratch database is fine)"
        )
    })?;
    // The shared pool: the URL's `sslmode` applies, and the server floor is checked.
    let pool = crate::connection::postgres_pool(
        &db_url,
        "--validate",
        &fraiseql_db::postgres::PostgresTlsConfig::default(),
    )
    .await?;
    let mut client = pool.get().await.context("--validate: failed to connect to DATABASE_URL")?;

    let txn = client.transaction().await.context("--validate: failed to open transaction")?;
    txn.batch_execute(sql)
        .await
        .context("generated DDL was rejected by PostgreSQL — the file is NOT valid")?;
    txn.rollback().await.context("--validate: rollback failed")?;
    Ok(())
}

/// Look up an entity in the schema and return its SQL source table/view name.
///
/// # Errors
///
/// Returns an error if the entity does not exist in the schema.
fn resolve_entity_sql_source(schema: &CompiledSchema, entity: &str) -> Result<String> {
    if let Some(type_def) = schema.types.iter().find(|t| t.name == entity) {
        Ok(type_def.sql_source.as_str().to_string())
    } else {
        let available = schema.types.iter().map(|t| t.name.as_str()).collect::<Vec<_>>().join(", ");
        anyhow::bail!("Entity '{entity}' not found in schema. Available types: {available}")
    }
}

/// Determine view type from view name and validate naming convention
///
/// Valid prefixes:
/// - `va_` - Vector Arrow view
/// - `tv_` - Table Vector view
/// - `ta_` - Table Arrow view
pub(crate) fn validate_view_name(view_name: &str) -> Result<&'static str> {
    if view_name.starts_with("va_") {
        Ok("Vector Arrow (va_)")
    } else if view_name.starts_with("tv_") {
        Ok("Table Vector (tv_)")
    } else if view_name.starts_with("ta_") {
        Ok("Table Arrow (ta_)")
    } else {
        anyhow::bail!("Invalid view name '{view_name}'. Must start with va_, tv_, or ta_")
    }
}

/// Generate SQL DDL for the view
///
/// # Arguments
///
/// * `entity` - Entity/type name from schema
/// * `sql_source` - Actual SQL table/view name from the schema (e.g., `v_user`)
/// * `view_name` - Full view name (e.g., "tv_user_profile")
/// * `view_type` - View type string for documentation
/// * `refresh_strategy` - How the view is kept up-to-date
/// * `include_composition_views` - Whether to include helper views
/// * `include_monitoring` - Whether to include monitoring functions
pub(crate) fn generate_view_sql(
    entity: &str,
    sql_source: &str,
    view_name: &str,
    view_type: &str,
    refresh_strategy: RefreshStrategy,
    include_composition_views: bool,
    include_monitoring: bool,
) -> String {
    let mut sql = String::new();

    // Header
    sql.push_str("-- Auto-generated Arrow view DDL\n");
    sql.push_str(&format!("-- Entity: {entity}\n"));
    sql.push_str(&format!("-- View: {view_name}\n"));
    sql.push_str(&format!("-- Type: {view_type}\n"));
    sql.push_str(&format!("-- Refresh strategy: {refresh_strategy}\n"));
    sql.push_str("-- Generated by: fraiseql generate-views\n\n");

    // Drop existing view if it exists. The drop must match the object class:
    // tv_ targets are MATERIALIZED views, and a plain `DROP VIEW` on one errors
    // ("… is not a view"), making the file non-idempotent (#821 adjunct).
    if view_name.starts_with("tv_") {
        sql.push_str(&format!("DROP MATERIALIZED VIEW IF EXISTS {view_name} CASCADE;\n\n"));
    } else {
        sql.push_str(&format!("DROP VIEW IF EXISTS {view_name} CASCADE;\n\n"));
    }

    // Main view definition.
    // `validate_view_name` guarantees the prefix is one of va_/tv_/ta_ before
    // this function is called, so the wildcard arm is unreachable.
    #[allow(clippy::unreachable)]
    // Reason: `validate_view_name` (called upstream of this function) restricts
    // the prefix to `va`/`tv`/`ta`; any other value would be a programmer
    // error, not user input.
    match view_name.split('_').next() {
        Some("va") => {
            generate_vector_arrow_view(&mut sql, entity, sql_source, view_name);
        },
        Some("tv") => {
            generate_table_vector_view(&mut sql, entity, sql_source, view_name);
        },
        Some("ta") => {
            generate_table_arrow_view(&mut sql, entity, sql_source, view_name);
        },
        _ => unreachable!("view name validated by validate_view_name before generate_view_sql"),
    }

    // Composition views (optional)
    if include_composition_views {
        sql.push_str("\n-- Composition views\n");
        generate_composition_views(&mut sql, view_name);
    }

    // Monitoring functions (optional)
    if include_monitoring {
        sql.push_str("\n-- Monitoring functions\n");
        generate_monitoring_functions(&mut sql, view_name);
    }

    sql
}

/// Generate a Vector Arrow (va_*) view for vector search and analytics
fn generate_vector_arrow_view(sql: &mut String, entity: &str, sql_source: &str, view_name: &str) {
    sql.push_str(&format!("CREATE VIEW {view_name} AS\n"));
    sql.push_str("SELECT\n");
    sql.push_str("    id,\n");
    sql.push_str(&format!("    -- {entity} entity fields\n"));
    sql.push_str("    created_at,\n");
    sql.push_str("    updated_at\n");
    sql.push_str(&format!("FROM {sql_source}\n"));
    sql.push_str("WHERE archived_at IS NULL;\n");
}

/// Generate a Table Vector (tv_*) view for materialized table vectors
fn generate_table_vector_view(sql: &mut String, entity: &str, sql_source: &str, view_name: &str) {
    sql.push_str(&format!("CREATE MATERIALIZED VIEW {view_name} AS\n"));
    sql.push_str("SELECT\n");
    sql.push_str("    id,\n");
    sql.push_str(&format!("    -- {entity} entity vector representation\n"));
    sql.push_str("    CURRENT_TIMESTAMP as materialized_at\n");
    sql.push_str(&format!("FROM {sql_source}\n"));
    sql.push_str("WHERE archived_at IS NULL;\n");
    sql.push('\n');
    let base_name = view_name.trim_start_matches("tv_");
    sql.push_str(&format!("CREATE INDEX idx_{base_name}_id ON {view_name} (id);\n"));
}

/// Generate a Table Arrow (ta_*) view for Arrow Flight streaming
fn generate_table_arrow_view(sql: &mut String, entity: &str, sql_source: &str, view_name: &str) {
    sql.push_str(&format!("CREATE VIEW {view_name} AS\n"));
    sql.push_str("SELECT\n");
    sql.push_str("    id,\n");
    sql.push_str(&format!("    -- {entity} entity fields optimized for Arrow\n"));
    sql.push_str("    created_at,\n");
    sql.push_str("    updated_at\n");
    sql.push_str(&format!("FROM {sql_source}\n"));
    sql.push_str("WHERE archived_at IS NULL\n");
    sql.push_str("ORDER BY id;\n");
}

/// Generate helper composition views.
///
/// They read the **generated** view (not the entity source), so the main
/// view's `DROP … CASCADE` removes them on re-run and the whole file stays
/// idempotent. A previous version emitted the literal placeholder `{}` for the
/// relation name — both statements were PostgreSQL syntax errors on every
/// invocation, and `--validate` reported the file valid anyway (#821).
fn generate_composition_views(sql: &mut String, view_name: &str) {
    let base_name = view_name
        .trim_start_matches("va_")
        .trim_start_matches("tv_")
        .trim_start_matches("ta_");

    // The tv_ view exposes `materialized_at` (see `generate_table_vector_view`);
    // va_/ta_ views project the source's `updated_at`.
    let ts_column = if view_name.starts_with("tv_") {
        "materialized_at"
    } else {
        "updated_at"
    };

    // Recent items view
    sql.push_str(&format!("CREATE VIEW {base_name}_recent AS\n"));
    sql.push_str(&format!("SELECT * FROM {view_name}\n"));
    sql.push_str(&format!("WHERE {ts_column} > NOW() - INTERVAL '7 days'\n"));
    sql.push_str(&format!("ORDER BY {ts_column} DESC;\n\n"));

    // Count view
    sql.push_str(&format!("CREATE VIEW {base_name}_count AS\n"));
    sql.push_str(&format!("SELECT COUNT(*) as total FROM {view_name};\n"));
}

/// Generate monitoring functions for the view.
///
/// STABLE, not IMMUTABLE: the function reads a relation, and IMMUTABLE would
/// license the planner to constant-fold the row count inside a cached or
/// prepared plan (#821 adjunct).
fn generate_monitoring_functions(sql: &mut String, view_name: &str) {
    let func_name = format!("monitor_{view_name}");

    sql.push_str(&format!("CREATE OR REPLACE FUNCTION {func_name}()\n"));
    sql.push_str("RETURNS TABLE (\n");
    sql.push_str("    metric_name TEXT,\n");
    sql.push_str("    metric_value BIGINT\n");
    sql.push_str(") AS $$\n");
    sql.push_str("BEGIN\n");
    sql.push_str("    RETURN QUERY\n");
    sql.push_str(&format!("    SELECT 'row_count'::TEXT, COUNT(*)::BIGINT FROM {view_name};\n"));
    sql.push_str("END;\n");
    sql.push_str("$$ LANGUAGE plpgsql STABLE;\n");
}