fraiseql-cli 2.0.0-rc.13

//! Introspect database for fact tables and output suggestions.
//!
//! This command discovers `tf_*` tables in the database and outputs:
//! - Python decorator suggestions (@`fraiseql.fact_table`)
//! - JSON metadata for manual review
//!
//! **Purpose**: Help developers discover and declare fact tables.
//! **Does NOT auto-modify schema** - outputs suggestions only.

use anyhow::Result;
use deadpool_postgres::{Config, ManagerConfig, RecyclingMethod, Runtime};
use fraiseql_core::{
    compiler::fact_table::{DatabaseIntrospector, FactTableDetector, FactTableMetadata},
    db::PostgresIntrospector,
};
use serde_json::json;
use tokio_postgres::NoTls;

/// Output format for introspection results.
#[derive(Debug, Clone, Copy)]
pub enum OutputFormat {
    /// Python decorator format
    Python,
    /// JSON format
    Json,
}

impl OutputFormat {
    /// Parse from string
    pub fn parse(s: &str) -> std::result::Result<Self, String> {
        match s.to_lowercase().as_str() {
            "python" | "py" => Ok(Self::Python),
            "json" => Ok(Self::Json),
            _ => Err(format!("Invalid format '{s}', expected: python, json")),
        }
    }
}

/// Create a PostgreSQL introspector from a database URL
async fn create_introspector(database_url: &str) -> Result<PostgresIntrospector> {
    let mut cfg = Config::new();
    cfg.url = Some(database_url.to_string());
    cfg.manager = Some(ManagerConfig {
        recycling_method: RecyclingMethod::Fast,
    });
    cfg.pool = Some(deadpool_postgres::PoolConfig::new(2));

    let pool = cfg
        .create_pool(Some(Runtime::Tokio1), NoTls)
        .map_err(|e| anyhow::anyhow!("Failed to create database pool: {e}"))?;

    // Test connection
    let _client = pool
        .get()
        .await
        .map_err(|e| anyhow::anyhow!("Failed to connect to database: {e}"))?;

    Ok(PostgresIntrospector::new(pool))
}

/// Introspect database for fact tables and output suggestions.
///
/// # Arguments
///
/// * `database_url` - Database connection string (e.g., "postgresql://...")
/// * `format` - Output format (Python or JSON)
///
/// # Returns
///
/// Success or error
///
/// # Example
///
/// ```bash
/// fraiseql introspect facts --database postgresql://localhost/mydb --format python
/// ```
pub async fn run(database_url: &str, format: OutputFormat) -> Result<()> {
    eprintln!("🔍 Introspecting database for fact tables...");
    eprintln!("   Database: {database_url}");

    // Create database introspector
    let introspector = create_introspector(database_url).await?;

    // List all fact tables
    let fact_tables = introspector
        .list_fact_tables()
        .await
        .map_err(|e| anyhow::anyhow!("Failed to list fact tables: {e}"))?;

    if fact_tables.is_empty() {
        eprintln!("\n⚠️  No fact tables found (tables starting with 'tf_')");
        eprintln!("   Fact tables should be named like: tf_sales, tf_events, tf_orders");
        return Ok(());
    }

    eprintln!("\n📋 Found {} fact table(s):", fact_tables.len());
    for table in &fact_tables {
        eprintln!("   - {table}");
    }
    eprintln!();

    // Introspect each fact table
    let mut metadata_list: Vec<FactTableMetadata> = Vec::new();
    let mut errors: Vec<(String, String)> = Vec::new();

    for table_name in &fact_tables {
        match FactTableDetector::introspect(&introspector, table_name).await {
            Ok(metadata) => {
                metadata_list.push(metadata);
            },
            Err(e) => {
                errors.push((table_name.clone(), e.to_string()));
            },
        }
    }

    // Report any errors
    if !errors.is_empty() {
        eprintln!("⚠️  Failed to introspect {} table(s):", errors.len());
        for (table, error) in &errors {
            eprintln!("   - {table}: {error}");
        }
        eprintln!();
    }

    // Output results
    match format {
        OutputFormat::Python => {
            println!("\n# Suggested fact table decorators:");
            println!("# (Copy and paste into your Python schema)");
            println!("# Generated by: fraiseql introspect facts");
            println!();
            println!("import fraiseql");
            println!();

            for metadata in &metadata_list {
                println!("{}", format_as_python(metadata));
                println!();
            }
        },
        OutputFormat::Json => {
            let output: serde_json::Value = metadata_list
                .iter()
                .map(|m| {
                    (
                        m.table_name.clone(),
                        json!({
                            "table_name": m.table_name,
                            "measures": m.measures.iter().map(|measure| {
                                json!({
                                    "name": measure.name,
                                    "sql_type": format!("{:?}", measure.sql_type),
                                    "nullable": measure.nullable
                                })
                            }).collect::<Vec<_>>(),
                            "dimensions": {
                                "name": m.dimensions.name,
                                "paths": m.dimensions.paths.iter().map(|p| {
                                    json!({
                                        "name": p.name,
                                        "json_path": p.json_path,
                                        "data_type": p.data_type
                                    })
                                }).collect::<Vec<_>>()
                            },
                            "denormalized_filters": m.denormalized_filters.iter().map(|f| {
                                json!({
                                    "name": f.name,
                                    "sql_type": format!("{:?}", f.sql_type),
                                    "indexed": f.indexed
                                })
                            }).collect::<Vec<_>>(),
                            "calendar_dimensions": m.calendar_dimensions.iter().map(|c| {
                                json!({
                                    "source_column": c.source_column,
                                    "granularities": c.granularities.iter().map(|g| {
                                        json!({
                                            "column_name": g.column_name,
                                            "buckets": g.buckets.iter().map(|b| {
                                                json!({
                                                    "json_key": b.json_key,
                                                    "bucket_type": format!("{:?}", b.bucket_type)
                                                })
                                            }).collect::<Vec<_>>()
                                        })
                                    }).collect::<Vec<_>>()
                                })
                            }).collect::<Vec<_>>()
                        }),
                    )
                })
                .collect::<serde_json::Map<String, serde_json::Value>>()
                .into();

            println!("{}", serde_json::to_string_pretty(&output)?);
        },
    }

    eprintln!("\n✅ Introspection complete");
    eprintln!("   {} table(s) introspected successfully", metadata_list.len());
    if !errors.is_empty() {
        eprintln!("   {} table(s) failed", errors.len());
    }

    Ok(())
}

/// Format metadata as Python decorator.
fn format_as_python(metadata: &FactTableMetadata) -> String {
    let mut output = String::new();

    // Extract measure names
    let measures: Vec<String> = metadata.measures.iter().map(|m| format!("'{}'", m.name)).collect();

    // Extract filter names
    let filters: Vec<String> =
        metadata.denormalized_filters.iter().map(|f| format!("'{}'", f.name)).collect();

    // Extract class name from table name (tf_sales -> Sales)
    let class_name = metadata
        .table_name
        .strip_prefix("tf_")
        .unwrap_or(&metadata.table_name)
        .split('_')
        .map(|s| {
            let mut c = s.chars();
            match c.next() {
                None => String::new(),
                Some(f) => f.to_uppercase().collect::<String>() + c.as_str(),
            }
        })
        .collect::<String>();

    // Format decorator
    output.push_str(&format!("# Fact table: {}\n", metadata.table_name));
    output.push_str("@fraiseql.fact_table(\n");
    output.push_str(&format!("    measures=[{}],\n", measures.join(", ")));
    output.push_str(&format!("    dimensions='{}',\n", metadata.dimensions.name));

    if !filters.is_empty() {
        output.push_str(&format!("    filters=[{}],\n", filters.join(", ")));
    }

    // Add calendar dimensions if present
    if !metadata.calendar_dimensions.is_empty() {
        let calendar_cols: Vec<String> = metadata
            .calendar_dimensions
            .iter()
            .map(|c| format!("'{}'", c.source_column))
            .collect();
        output.push_str(&format!("    calendar_columns=[{}],\n", calendar_cols.join(", ")));
    }

    output.push_str(")\n");
    output.push_str(&format!("class {class_name}:\n"));
    output.push_str(&format!(
        "    \"\"\"Fact table: {} ({} measures, {} filters)\"\"\"\n",
        metadata.table_name,
        metadata.measures.len(),
        metadata.denormalized_filters.len()
    ));
    output.push_str("    pass");

    output
}

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

    #[test]
    fn test_output_format_from_str() {
        assert!(matches!(OutputFormat::parse("python"), Ok(OutputFormat::Python)));
        assert!(matches!(OutputFormat::parse("json"), Ok(OutputFormat::Json)));
        assert!(OutputFormat::parse("invalid").is_err());
    }

    #[test]
    fn test_format_as_python() {
        use fraiseql_core::compiler::fact_table::{
            DimensionColumn, FilterColumn, MeasureColumn, SqlType,
        };

        let metadata = FactTableMetadata {
            table_name:           "tf_sales".to_string(),
            measures:             vec![
                MeasureColumn {
                    name:     "revenue".to_string(),
                    sql_type: SqlType::Decimal,
                    nullable: false,
                },
                MeasureColumn {
                    name:     "quantity".to_string(),
                    sql_type: SqlType::Int,
                    nullable: false,
                },
            ],
            dimensions:           DimensionColumn {
                name:  "data".to_string(),
                paths: vec![],
            },
            denormalized_filters: vec![FilterColumn {
                name:     "customer_id".to_string(),
                sql_type: SqlType::Uuid,
                indexed:  true,
            }],
            calendar_dimensions:  vec![],
        };

        let output = format_as_python(&metadata);
        assert!(output.contains("@fraiseql.fact_table"));
        assert!(output.contains("'revenue'"));
        assert!(output.contains("'quantity'"));
        assert!(output.contains("'customer_id'"));
        assert!(output.contains("class Sales:"));
    }
}