use crate::test_fixtures::cube_bridge::MockSchema;
use crate::test_fixtures::test_utils::TestContext;
use indoc::indoc;

fn create_context() -> TestContext {
    let schema = MockSchema::from_yaml_file("common/integration_multi_fact.yaml");
    TestContext::new(schema).unwrap()
}

const SEED: &str = "integration_multi_fact_tables.sql";

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_totals_no_dimensions() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - orders.total_amount
          - returns.count
          - returns.total_refund
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_counts_by_customer_name() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_sums_by_customer_name() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.total_amount
          - returns.total_refund
        dimensions:
          - customers.name
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_grouped_by_city() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.city
        order:
          - id: customers.city
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_filter_on_shared_dimension() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - orders.total_amount
          - returns.count
          - returns.total_refund
        dimensions:
          - customers.name
        filters:
          - dimension: customers.city
            operator: equals
            values:
              - New York
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_filter_on_fact_dimension() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - orders.total_amount
          - returns.count
          - returns.total_refund
        dimensions:
          - customers.name
        filters:
          - dimension: orders.status
            operator: equals
            values:
              - completed
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_count_distinct_control() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - orders.unique_customers
          - returns.count
          - returns.unique_customers
        dimensions:
          - customers.city
        order:
          - id: customers.city
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_three_fact_tables() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
          - reviews.count
        dimensions:
          - customers.name
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_hub_measure_by_fact_dimension() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - customers.count
        dimensions:
          - orders.status
        order:
          - id: orders.status
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_hub_and_fact_measures() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - customers.count
          - orders.count
          - orders.total_amount
        dimensions:
          - orders.status
        order:
          - id: orders.status
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_with_segment() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        segments:
          - orders.completed_orders
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_with_measure_filter() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        filters:
          - member: orders.count
            operator: gt
            values:
              - \"1\"
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_measure_filter_on_second_fact() {
    let ctx = create_context();

    // HAVING returns.count > 1 in multi-fact context
    // returns by customer: Alice=1, Bob=2, Charlie=2, Diana=0
    // After filter: Bob(orders=3, returns=2), Charlie(orders=0/NULL, returns=2)
    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        filters:
          - member: returns.count
            operator: gt
            values:
              - \"1\"
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_with_time_granularity() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        time_dimensions:
          - dimension: customers.created_at
            granularity: month
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_with_time_and_daterange() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        time_dimensions:
          - dimension: customers.created_at
            granularity: month
            dateRange:
              - \"2025-01-01\"
              - \"2025-02-28\"
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_with_time_and_filter() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        time_dimensions:
          - dimension: customers.created_at
            granularity: month
        filters:
          - dimension: customers.city
            operator: equals
            values:
              - New York
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_with_time_and_dimension() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.city
        time_dimensions:
          - dimension: customers.created_at
            granularity: month
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_full_combo() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        time_dimensions:
          - dimension: customers.created_at
            granularity: month
            dateRange:
              - \"2025-01-01\"
              - \"2025-03-31\"
        segments:
          - orders.completed_orders
        filters:
          - dimension: customers.city
            operator: equals
            values:
              - New York
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_with_order_and_limit() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - returns.count
        dimensions:
          - customers.name
        order:
          - id: orders.count
            desc: true
        row_limit: \"2\"
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_aggregate_hub_sum_measure() {
    let ctx = create_context();

    // customers.total_lifetime_value (SUM) by orders.status
    // customers is multiplied in customers→orders join (one_to_many)
    // SUM is not additive in multiplied context → AggregateMultipliedSubquery
    // Measure references only customers → source = Cube (no MeasureSubquery)
    let query = indoc! {"
        measures:
          - customers.total_lifetime_value
        dimensions:
          - orders.status
        order:
          - id: orders.status
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_aggregate_filtered_count_on_pk() {
    let ctx = create_context();

    // customers.active_count (type: count, sql: "{CUBE}.id", filters: [...])
    // grouped by orders.status.
    // customers→orders is one_to_many, so customers is multiplied → AggregateMultipliedSubquery
    // The measure references only customers → source = Cube branch.
    // Regression: the Cube branch used to render the right side of the FK-aggregate
    // rejoin via Expr::Member, which the outer factory's render_references mapped
    // back to the inner `keys` subquery alias — yielding a tautological
    // `ON keys.<pk> = keys.<pk>` that cross-joined the source table and over-counted.
    let query = indoc! {"
        measures:
          - customers.active_count
        dimensions:
          - orders.status
        order:
          - id: orders.status
    "};

    let sql = ctx.build_sql(query).unwrap();

    // The buggy output contained `keys.customers__id = keys.customers__id`. Flatten
    // the SQL so the assertion survives any future line-wrapping in the planner's
    // formatter, then walk every ON predicate and assert no tautological equality
    // survives. The snapshot below is the airtight guard; this is a targeted
    // structural sanity check pinned to the bug shape.
    let flat = sql.replace('\n', " ");
    let flat_upper = flat.to_ascii_uppercase();
    let mut cursor = 0;
    while let Some(rel) = flat_upper[cursor..].find(" ON ") {
        let on_end = cursor + rel + 4;
        let after = &flat[on_end..];
        let after_upper = &flat_upper[on_end..];
        let bound = [
            " GROUP BY ",
            " ORDER BY ",
            " LEFT JOIN ",
            " INNER JOIN ",
            " RIGHT JOIN ",
            " UNION ",
        ]
        .iter()
        .filter_map(|kw| after_upper.find(kw))
        .min()
        .unwrap_or(after.len());
        for chunk in after[..bound].split(" AND ") {
            if let Some((lhs, rhs)) = chunk.split_once('=') {
                let lhs = lhs.trim().trim_matches('(').trim();
                let rhs = rhs.trim().trim_matches(')').trim();
                assert!(
                    lhs != rhs,
                    "tautological join condition `{lhs} = {rhs}` in:\n{sql}"
                );
            }
        }
        cursor = on_end;
    }

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_aggregate_with_measure_subquery() {
    let ctx = create_context();

    // customers.total_lifetime_value_for_east (SUM with filter {regions.name} = 'East')
    // customers is multiplied in customers→orders join → AggregateMultipliedSubquery
    // Measure filter references regions cube → MeasureSubquery (customers→regions join,
    // where customers is NOT multiplied since many_to_one)
    let query = indoc! {"
        measures:
          - customers.total_lifetime_value_for_east
        dimensions:
          - orders.status
        order:
          - id: orders.status
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_non_multiplied_multi_join() {
    let ctx = create_context();

    let query = indoc! {"
        measures:
          - orders.count
          - orders.total_amount
        dimensions:
          - customers.name
          - customers.city
        order:
          - id: customers.name
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multiplied_aggregate_grouped_by_own_primary_key() {
    let ctx = create_context();

    // customers.total_lifetime_value is multiplied by the customers→orders
    // join, so it is read through the keys subquery and re-joined to customers
    // by customers' primary key. That key is also a query dimension here, so it
    // plays both roles at once and the keys subquery has to project it exactly
    // once - two columns under one alias make every reference to it from the
    // re-join ambiguous.
    let query = indoc! {"
        measures:
          - customers.total_lifetime_value
          - orders.count
        dimensions:
          - customers.id
          - orders.status
        order:
          - id: customers.id
          - id: orders.status
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx.try_execute_pg(query, SEED).await {
        insta::assert_snapshot!(result);
    }
}

#[tokio::test(flavor = "multi_thread")]
async fn test_multi_fact_view_two_facts_with_measure_filter() {
    let schema = MockSchema::from_yaml_file("common/integration_multi_fact_view.yaml");
    let ctx = TestContext::new(schema).unwrap();

    let query = indoc! {"
        measures:
          - activity.impressions_total_clicks
        dimensions:
          - activity.date_id
        filters:
          - member: activity.total_amount
            operator: set
        order:
          - id: activity.date_id
    "};

    ctx.build_sql(query).unwrap();

    if let Some(result) = ctx
        .try_execute_pg(query, "integration_multi_fact_view_tables.sql")
        .await
    {
        insta::assert_snapshot!(result);
    }
}
