//! EXPLAIN Pre-check Integration Test
//!
//! Tests the EXPLAIN cost estimation against a live PostgreSQL database,
//! using the same PgDriver pattern as rls_integration.rs.
//!
//! Run with:
//! ```sh
//! DATABASE_URL="postgresql://qail_user@localhost:5432/qail_test" \
//!     cargo test -p qail-pg --test explain_integration -- --nocapture --ignored
//! ```
//!
//! Note: Uses `qail_user` (superuser) because EXPLAIN requires SELECT permission
//! on the target tables. The `qail_app` role is an operator-level app user
//! with restricted grants.

use qail_core::ast::{Qail, JoinKind};
use qail_core::rls::RlsContext;
use qail_core::transpiler::ToSql;
use qail_pg::PgDriver;
use qail_pg::explain::{ExplainConfig, ExplainMode, ExplainCache, check_estimate, parse_explain_json};

use std::time::Duration;

/// Known test operator: Operator A (has vessels in local DB)
const OPERATOR_A_ID: &str = "00000000-0000-0000-0000-000000000001";

async fn connect() -> PgDriver {
    PgDriver::connect_env()
        .await
        .expect("DATABASE_URL must point to qail_test with qail_user (superuser)")
}

/// Run EXPLAIN (FORMAT JSON) via driver's fetch_raw and parse the result
async fn run_explain(driver: &mut PgDriver, cmd: &Qail) -> Option<qail_pg::explain::ExplainEstimate> {
    let sql = cmd.to_sql();
    let explain_sql = format!("EXPLAIN (FORMAT JSON) {}", sql);
    let rows = driver.fetch_raw(&explain_sql).await.unwrap();

    let mut json_output = String::new();
    for row in &rows {
        if let Some(Some(val)) = row.columns.first() {
            if let Ok(text) = std::str::from_utf8(val) {
                json_output.push_str(text);
            }
        }
    }

    if !json_output.is_empty() {
        println!("  EXPLAIN JSON: {}", &json_output[..json_output.len().min(200)]);
    }

    parse_explain_json(&json_output)
}

#[tokio::test]
#[ignore = "Requires local PostgreSQL with live data"]
async fn test_explain_simple_select() {
    let mut driver = connect().await;

    // Set operator context for RLS
    driver
        .set_rls_context(RlsContext::operator(OPERATOR_A_ID))
        .await
        .unwrap();

    // Simple SELECT — should be cheap
    let cmd = Qail::get("vessels").columns(["id", "name"]).limit(10);
    println!("SQL: {}", cmd.to_sql());

    let est = run_explain(&mut driver, &cmd).await
        .expect("Should parse EXPLAIN output");

    println!("✅ Simple SELECT vessels LIMIT 10");
    println!("   Cost: {:.2}, Rows: {}", est.total_cost, est.plan_rows);

    // Should be well under default thresholds
    let config = ExplainConfig::default();
    let decision = check_estimate(&est, &config);
    assert!(!decision.is_rejected(), "Simple limited query should be ALLOWED");
    println!("   Decision: ALLOW ✓");
}

#[tokio::test]
#[ignore = "Requires local PostgreSQL with live data"]
async fn test_explain_multi_join() {
    let mut driver = connect().await;

    // Operator A context
    driver
        .set_rls_context(RlsContext::operator(OPERATOR_A_ID))
        .await
        .unwrap();

    // Multi-table join: orders + agents (2 JOINs)
    let cmd = Qail::get("orders")
        .join(JoinKind::Left, "agents", "orders.agent_id", "agents.id")
        .join(JoinKind::Left, "destinations", "orders.id", "destinations.id");

    println!("SQL: {}", cmd.to_sql());

    let est = run_explain(&mut driver, &cmd).await
        .expect("Should parse EXPLAIN output");

    println!("✅ Multi-join: orders + agents + destinations");
    println!("   Cost: {:.2}, Rows: {}", est.total_cost, est.plan_rows);

    // With very strict thresholds → should be rejected
    let strict_config = ExplainConfig {
        mode: ExplainMode::Enforce,
        depth_threshold: 1,
        max_total_cost: 1.0,    // Extremely low
        max_plan_rows: 1,       // Extremely low
        cache_ttl: Duration::from_secs(60),
    };
    let decision = check_estimate(&est, &strict_config);
    assert!(decision.is_rejected(), "Multi-join with strict=1.0 should be REJECTED");
    println!("   Strict(max_cost=1.0) → REJECTED ✓");
    if let Some(msg) = decision.rejection_message() {
        println!("   Message: {}", msg);
    }

    // With default thresholds — show the result
    let default_config = ExplainConfig::default();
    let default_decision = check_estimate(&est, &default_config);
    println!(
        "   Default(max_cost={:.0}) → {}",
        default_config.max_total_cost,
        if default_decision.is_rejected() { "REJECTED" } else { "ALLOW" }
    );
}

#[tokio::test]
#[ignore = "Requires local PostgreSQL with live data"]
async fn test_explain_cache_roundtrip() {
    let mut driver = connect().await;

    driver
        .set_rls_context(RlsContext::operator(OPERATOR_A_ID))
        .await
        .unwrap();

    let cache = ExplainCache::new(Duration::from_secs(300));
    let cmd = Qail::get("vessels").columns(["id"]).limit(5);

    use std::collections::hash_map::DefaultHasher;
    use std::hash::{Hash, Hasher};

    let sql = cmd.to_sql();
    let mut hasher = DefaultHasher::new();
    sql.hash(&mut hasher);
    let shape_hash = hasher.finish();

    // Cache miss
    assert!(cache.get(shape_hash, None).is_none(), "Should be cache miss initially");

    // Run EXPLAIN and cache
    let est = run_explain(&mut driver, &cmd).await.unwrap();
    cache.insert(shape_hash, est.clone());

    // Cache hit
    let cached = cache.get(shape_hash, None).unwrap();
    assert!((cached.total_cost - est.total_cost).abs() < 0.01);
    assert_eq!(cached.plan_rows, est.plan_rows);

    println!("✅ Cache roundtrip verified");
    println!("   MISS → EXPLAIN → INSERT → HIT");
    println!("   cost={:.2}, rows={}", cached.total_cost, cached.plan_rows);
}

#[tokio::test]
#[ignore = "Requires local PostgreSQL with live data"]
async fn test_explain_full_table_scan() {
    let mut driver = connect().await;

    // Operator context — RLS will filter, but no LIMIT = seq scan
    driver
        .set_rls_context(RlsContext::operator(OPERATOR_A_ID))
        .await
        .unwrap();

    // SELECT * FROM orders — no filters, no limit
    let cmd = Qail::get("orders");
    println!("SQL: {}", cmd.to_sql());

    let est = run_explain(&mut driver, &cmd).await
        .expect("Should parse EXPLAIN output");

    println!("✅ Full scan: SELECT * FROM orders (no filters, no limit)");
    println!("   Cost: {:.2}, Rows: {}", est.total_cost, est.plan_rows);

    // With tight thresholds, full scan should be rejected
    let config = ExplainConfig {
        mode: ExplainMode::Enforce,
        depth_threshold: 0,
        max_total_cost: 5.0,    // Very tight
        max_plan_rows: 5,       // Very tight
        cache_ttl: Duration::from_secs(60),
    };
    let decision = check_estimate(&est, &config);
    println!(
        "   Tight threshold(5.0 cost, 5 rows) → {}",
        if decision.is_rejected() { "REJECTED ✓" } else { "ALLOW" }
    );
    if let Some(msg) = decision.rejection_message() {
        println!("   Message: {}", msg);
    }
}
