-- This test compares COLUMN VALUES between the NEW dbt clip_initial_metrics model
-- and the EXISTING production clip_initial_metrics table, for a specific partition.
-- It explicitly checks the listed columns using audit_helper.compare_column_values_verbose for each column.
--
-- The test uses:
-- - Production relation: suno_prod.prod.clip_initial_metrics
-- - dbt ref for dev: {{ ref('int_clip_initial_metrics') }}
--
-- To run this test:
-- 1. Compile: dbt compile --select test_clip_initial_metrics_column_comparison
-- 2. Execute the compiled SQL from target/compiled/analytics/analyses/migrations/test_clip_initial_metrics_column_comparison.sql
--    Or use: uv run python query_snowflake.py --file target/compiled/analytics/analyses/migrations/test_clip_initial_metrics_column_comparison.sql

{% set partition_start_date = var('partition_start_date', '1900-01-01') %}
{% set partition_start_hour = var('partition_start_hour', 0) %}

-- Set the columns to compare (matching the actual clip_initial_metrics schema)
{% set columns_to_compare = [
    'clip_id',
    'user_id',
    'user_uid',
    'clip_created_at',
    'window_minutes',
    'window_start_time',
    'window_end_time',
    'play_cnt',
    'play_duration_seconds',
    'n_like_events',
    'n_undolike_events',
    'net_like_events',
    'n_dislike_events',
    'n_undodislike_events',
    'net_dislike_events',
    'n_share_events',
    'n_download_events',
    'n_makepublic_events',
    'n_makeprivate_events',
    'net_public_toggle_events',
    'net_positive_interactions',
    'is_satisfactory_indirect',
    'is_satisfactory_direct',
    'is_satisfactory',
    'metadata',
    'p_date',
    'p_hour'
] %}

with
a_query as (
    select
        *,
        clip_id as dbt_audit_helper_pk
    from suno_prod.prod.clip_initial_metrics
    where p_date = '{{ partition_start_date }}'
      and p_hour = {{ partition_start_hour }}
),
b_query as (
    select
        *,
        clip_id as dbt_audit_helper_pk
    from {{ ref('int_clip_initial_metrics') }}
    where p_date = '{{ partition_start_date }}'
      and p_hour = {{ partition_start_hour }}
),

/*
    For each column in columns_to_compare, create a unioned query using
    audit_helper.compare_column_values_verbose, with the queries referencing
    the above CTEs. The final result is summarized per column.
*/
main as (

{% for column_name in columns_to_compare %}
(
    {{ audit_helper.compare_column_values_verbose(
        a_query="select * from a_query",
        b_query="select * from b_query",
        primary_key="dbt_audit_helper_pk",
        column_to_compare=column_name
    ) }}
)
{%- if not loop.last %}
    union all
{%- endif %}
{% endfor %}
),

final as (
  select
    upper(column_name) as column_name,
    sum(case when perfect_match then 1 else 0 end) as perfect_match,
    sum(case when null_in_a then 1 else 0 end) as null_in_a,
    sum(case when null_in_b then 1 else 0 end) as null_in_b,
    sum(case when missing_from_a then 1 else 0 end) as missing_from_a,
    sum(case when missing_from_b then 1 else 0 end) as missing_from_b,
    sum(case when conflicting_values then 1 else 0 end) as conflicting_values
  from main
  group by 1
  order by column_name
)

select * from final
