-- This test compares the NEW dbt clip_initial_metrics model (which replaces the Snowflake procedure)
-- with the EXISTING production clip_initial_metrics table (loaded via the old procedure/task)
-- It uses audit_helper.compare_and_classify_query_results to compare row by row and classifies results into:
-- - identical: the rows are identical
-- - modified: the rows are different
-- - added: the rows are missing from the old table
-- - removed: the rows are missing from the new table
--
-- For column-level comparison, see: test_clip_initial_metrics_column_comparison.sql
--
-- The test uses:
-- - Hardcoded path for prod: suno_prod.prod.clip_initial_metrics
-- - dbt ref for dev: {{ ref('int_clip_initial_metrics') }} (resolves to the dev database based on dbt target)
--
-- To run this test:
-- 1. Compile the query: dbt compile --select test_clip_initial_metrics_migration --vars '{"partition_start_date": "2025-11-12", "partition_start_hour": 18}'
-- 2. Execute the compiled SQL from target/compiled/analytics/analyses/migrations/test_clip_initial_metrics_migration.sql
--    Or use: uv run python query_snowflake.py --file target/compiled/analytics/analyses/migrations/test_clip_initial_metrics_migration.sql

{% set old_query %}
    SELECT 
        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
    FROM suno_prod.prod.clip_initial_metrics
    WHERE p_date = '{{ var("partition_start_date", "1900-01-01") }}'
    AND p_hour = {{ var("partition_start_hour", 0) }}
{% endset %}

{% set new_query %}
    SELECT 
        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
    FROM {{ ref('int_clip_initial_metrics') }}
    WHERE p_date = '{{ var("partition_start_date", "1900-01-01") }}'
    AND p_hour = {{ var("partition_start_hour", 0) }}
{% endset %}

{{ 
  audit_helper.compare_and_classify_query_results(
    old_query, 
    new_query, 
    primary_key_columns=['clip_id'], 
    columns=[
        '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'
    ]
  )
}}
