//! Index analysis scenario tests.
//!
//! Tests verify that `pgcrate indexes` correctly detects:
//! - Duplicate indexes (same columns, same order)
//! - Missing FK indexes (foreign keys without supporting index)
//!
//! Note: Unused index detection is not tested here because it requires
//! accumulated pg_stat_user_indexes data, which is flaky in test environments.

use crate::common::{parse_json, stdout, TestDatabase, TestProject};

// ============================================================================
// Duplicate index detection
// ============================================================================

#[test]
fn test_indexes_detects_duplicate() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    // Create a table with duplicate indexes (same columns, same order)
    db.run_sql_ok(
        "CREATE TABLE dup_test (
            id SERIAL PRIMARY KEY,
            user_id INTEGER,
            created_at TIMESTAMPTZ
        )",
    );
    db.run_sql_ok("CREATE INDEX dup_test_user_id_idx1 ON dup_test(user_id)");
    db.run_sql_ok("CREATE INDEX dup_test_user_id_idx2 ON dup_test(user_id)");

    let output = project.run_pgcrate(&["dba", "indexes"]);

    let out = stdout(&output);

    // Should detect the duplicate
    assert!(
        out.contains("duplicate") || out.contains("Duplicate") || out.contains("dup_test_user_id"),
        "Should detect duplicate indexes: {}",
        out
    );
}

#[test]
fn test_indexes_no_false_duplicate_for_different_columns() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    // Create indexes on different column orders - NOT duplicates
    db.run_sql_ok(
        "CREATE TABLE order_test (
            id SERIAL PRIMARY KEY,
            user_id INTEGER,
            created_at TIMESTAMPTZ
        )",
    );
    db.run_sql_ok("CREATE INDEX order_test_user_created_idx ON order_test(user_id, created_at)");
    db.run_sql_ok("CREATE INDEX order_test_created_user_idx ON order_test(created_at, user_id)");

    let output = project.run_pgcrate(&["dba", "indexes", "--json"]);

    let out = stdout(&output);
    let json: serde_json::Value = serde_json::from_str(&out)
        .unwrap_or_else(|e| panic!("Invalid JSON: {}\nOutput: {}", e, out));

    // Check duplicates array - should not flag these as duplicates
    if let Some(duplicates) = json.get("duplicates").and_then(|d| d.as_array()) {
        let has_order_test_dup = duplicates.iter().any(|d| {
            d.to_string().contains("order_test_user_created")
                && d.to_string().contains("order_test_created_user")
        });
        assert!(
            !has_order_test_dup,
            "Different column order should not be flagged as duplicate: {:?}",
            duplicates
        );
    }
}

// ============================================================================
// Missing FK index detection
// ============================================================================

#[test]
fn test_indexes_detects_missing_fk_index() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    // Create parent and child tables with FK but no index on FK column
    db.run_sql_ok(
        "CREATE TABLE parent_tbl (
            id SERIAL PRIMARY KEY,
            name TEXT
        )",
    );
    db.run_sql_ok(
        "CREATE TABLE child_tbl (
            id SERIAL PRIMARY KEY,
            parent_id INTEGER REFERENCES parent_tbl(id),
            value TEXT
        )",
    );
    // Note: No index on child_tbl(parent_id) - should be flagged

    let output = project.run_pgcrate(&["dba", "indexes"]);

    let out = stdout(&output);

    // Should suggest missing index on FK column
    assert!(
        out.contains("missing")
            || out.contains("Missing")
            || out.contains("parent_id")
            || out.contains("child_tbl")
            || out.contains("foreign"),
        "Should detect missing FK index: {}",
        out
    );
}

#[test]
fn test_indexes_no_missing_when_fk_has_index() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    // Create tables with FK that HAS an index
    db.run_sql_ok(
        "CREATE TABLE indexed_parent (
            id SERIAL PRIMARY KEY,
            name TEXT
        )",
    );
    db.run_sql_ok(
        "CREATE TABLE indexed_child (
            id SERIAL PRIMARY KEY,
            parent_id INTEGER REFERENCES indexed_parent(id),
            value TEXT
        )",
    );
    db.run_sql_ok("CREATE INDEX indexed_child_parent_id_idx ON indexed_child(parent_id)");

    let output = project.run_pgcrate(&["dba", "indexes", "--json"]);

    let out = stdout(&output);
    let json: serde_json::Value = serde_json::from_str(&out)
        .unwrap_or_else(|e| panic!("Invalid JSON: {}\nOutput: {}", e, out));

    // Check missing array - should not flag indexed_child
    if let Some(missing) = json.get("missing").and_then(|m| m.as_array()) {
        let has_indexed_child = missing
            .iter()
            .any(|m| m.to_string().contains("indexed_child"));
        assert!(
            !has_indexed_child,
            "FK with index should not be flagged as missing: {:?}",
            missing
        );
    }
}

// ============================================================================
// JSON structure
// ============================================================================

#[test]
fn test_indexes_json_structure() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    let output = project.run_pgcrate_ok(&["dba", "indexes", "--json"]);

    let json = parse_json(&output);

    // Should have expected keys (nested in data object)
    let data = json.get("data").expect("JSON should have data field");
    assert!(
        data.get("missing").is_some()
            || data.get("unused").is_some()
            || data.get("duplicates").is_some(),
        "JSON should have data.missing, data.unused, or data.duplicates: {}",
        json
    );
}

// ============================================================================
// Limits
// ============================================================================

#[test]
fn test_indexes_respects_missing_limit() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    // Create multiple FKs without indexes
    db.run_sql_ok("CREATE TABLE limit_parent (id SERIAL PRIMARY KEY)");
    for i in 1..=5 {
        db.run_sql_ok(&format!(
            "CREATE TABLE limit_child_{} (
                id SERIAL PRIMARY KEY,
                parent_id INTEGER REFERENCES limit_parent(id)
            )",
            i
        ));
    }

    // Request only 2 missing indexes
    let output = project.run_pgcrate(&["dba", "indexes", "--missing-limit", "2", "--json"]);

    let out = stdout(&output);
    let json: serde_json::Value = serde_json::from_str(&out)
        .unwrap_or_else(|e| panic!("Invalid JSON: {}\nOutput: {}", e, out));

    // Should respect limit
    if let Some(missing) = json.get("missing").and_then(|m| m.as_array()) {
        assert!(
            missing.len() <= 2,
            "Should respect --missing-limit 2, got {} items",
            missing.len()
        );
    }
}

// ============================================================================
// Primary key handling
// ============================================================================

#[test]
fn test_indexes_excludes_primary_keys_from_duplicates() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    // Create table with PK - the PK creates an index, we should NOT flag it
    db.run_sql_ok(
        "CREATE TABLE pk_test (
            id SERIAL PRIMARY KEY,
            name TEXT
        )",
    );
    // Add another index on id - this IS a duplicate of the PK index
    db.run_sql_ok("CREATE INDEX pk_test_id_idx ON pk_test(id)");

    let output = project.run_pgcrate(&["dba", "indexes", "--json"]);

    let out = stdout(&output);
    let json: serde_json::Value = serde_json::from_str(&out)
        .unwrap_or_else(|e| panic!("Invalid JSON: {}\nOutput: {}", e, out));

    // The pk_test_id_idx duplicates the PK index - implementation may or may not flag this.
    // Primary goal: verify the command handles this edge case without error.
    // The JSON should be valid and contain a duplicates key (nested in data object).
    let data = json.get("data").expect("JSON should have data field");
    assert!(
        data.get("duplicates").is_some(),
        "JSON should contain data.duplicates key: {}",
        json
    );
}

// ============================================================================
// Output content
// ============================================================================

#[test]
fn test_indexes_healthy_no_issues() {
    skip_if_no_db!();
    let db = TestDatabase::new();
    let project = TestProject::from_fixture("with_migrations", &db);

    project.run_pgcrate_ok(&["migrate", "up"]);

    // The with_migrations fixture should have proper indexes
    let output = project.run_pgcrate(&["dba", "indexes"]);

    // Should succeed (exit 0) when no issues
    // Note: May have warnings about missing FK indexes from fixture
    assert!(
        output.status.code().is_some(),
        "indexes command should complete"
    );
}
