use flowscope_core::{
    completion_items, ColumnSchema, CompletionRequest, Dialect, SchemaMetadata, SchemaTable,
};
use insta::assert_json_snapshot;

fn request_at_cursor(sql: &str, schema: Option<SchemaMetadata>) -> CompletionRequest {
    let cursor_offset = sql.find('|').expect("sql must contain cursor marker '|'");
    let clean_sql = sql.replace('|', "");
    CompletionRequest {
        sql: clean_sql,
        dialect: Dialect::Duckdb,
        cursor_offset,
        schema,
    }
}

fn request_at_cursor_with_dialect(
    sql: &str,
    schema: Option<SchemaMetadata>,
    dialect: Dialect,
) -> CompletionRequest {
    let cursor_offset = sql.find('|').expect("sql must contain cursor marker '|'");
    let clean_sql = sql.replace('|', "");
    CompletionRequest {
        sql: clean_sql,
        dialect,
        cursor_offset,
        schema,
    }
}

fn sample_schema() -> SchemaMetadata {
    SchemaMetadata {
        default_catalog: None,
        default_schema: Some("public".to_string()),
        search_path: None,
        case_sensitivity: None,
        allow_implied: true,
        tables: vec![
            SchemaTable {
                catalog: None,
                schema: Some("public".to_string()),
                name: "users".to_string(),
                columns: vec![
                    ColumnSchema {
                        name: "id".to_string(),
                        data_type: Some("integer".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                    ColumnSchema {
                        name: "email".to_string(),
                        data_type: Some("varchar".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                    ColumnSchema {
                        name: "name".to_string(),
                        data_type: Some("varchar".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                ],
            },
            SchemaTable {
                catalog: None,
                schema: Some("public".to_string()),
                name: "orders".to_string(),
                columns: vec![
                    ColumnSchema {
                        name: "id".to_string(),
                        data_type: Some("integer".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                    ColumnSchema {
                        name: "total".to_string(),
                        data_type: Some("decimal".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                    ColumnSchema {
                        name: "user_id".to_string(),
                        data_type: Some("integer".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                ],
            },
            SchemaTable {
                catalog: None,
                schema: Some("public".to_string()),
                name: "products".to_string(),
                columns: vec![
                    ColumnSchema {
                        name: "id".to_string(),
                        data_type: Some("integer".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                    ColumnSchema {
                        name: "name".to_string(),
                        data_type: Some("varchar".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                    ColumnSchema {
                        name: "price".to_string(),
                        data_type: Some("decimal".to_string()),
                        is_primary_key: None,
                        foreign_key: None,
                    },
                ],
            },
        ],
    }
}

#[test]
fn snap_select_with_schema() {
    let request = request_at_cursor("SELECT | FROM users", Some(sample_schema()));
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_from_clause_tables() {
    let request = request_at_cursor("SELECT * FROM |", Some(sample_schema()));
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_join_on_condition() {
    let request = request_at_cursor(
        "SELECT * FROM users u JOIN orders o ON |",
        Some(sample_schema()),
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_qualified_alias() {
    let request = request_at_cursor(
        "SELECT u.| FROM users u JOIN orders o ON u.id = o.user_id",
        Some(sample_schema()),
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_qualified_schema() {
    let request = request_at_cursor("SELECT * FROM public.|", Some(sample_schema()));
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_three_way_join() {
    let request = request_at_cursor(
        "SELECT | FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id",
        Some(sample_schema()),
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_ambiguous_columns() {
    let request = request_at_cursor(
        "SELECT | FROM users JOIN orders ON users.id = orders.user_id",
        Some(sample_schema()),
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_multi_statement() {
    let request = request_at_cursor(
        "SELECT * FROM users; SELECT | FROM orders",
        Some(sample_schema()),
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_cte_reference() {
    let request = request_at_cursor(
        "WITH active_users AS (SELECT id, email FROM users WHERE active = true) SELECT | FROM active_users",
        Some(sample_schema()),
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_empty_schema() {
    let request = request_at_cursor("SELECT | FROM users", None);
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_duckdb_dialect() {
    let request = request_at_cursor_with_dialect(
        "SELECT | FROM users",
        Some(sample_schema()),
        Dialect::Duckdb,
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_postgres_dialect() {
    let request = request_at_cursor_with_dialect(
        "SELECT | FROM users",
        Some(sample_schema()),
        Dialect::Postgres,
    );
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_keywords_select_clause() {
    let request = request_at_cursor("SELECT |", None);
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_keywords_where_clause() {
    let request = request_at_cursor("SELECT * FROM t WHERE |", None);
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}

#[test]
fn snap_keywords_order_by_clause() {
    let request = request_at_cursor("SELECT * FROM t ORDER BY |", None);
    let result = completion_items(&request);
    assert_json_snapshot!(result);
}
