-- This test compares the NEW dbt dim_clip model (which replaces the Snowflake procedure)
-- with the EXISTING production dim_clip table (loaded via the old procedure/task)
-- It uses audit_helper to compare row by row and classifies results into:
-- - identical: the rows are identical
-- - different: the rows are different
-- - missing: the rows are missing from one of the tables
-- It then returns a table with the results.
--
-- The test uses:
-- - Hardcoded path for prod: suno_prod.prod.dim_clip
-- - dbt ref for dev: {{ ref('dim_clip') }} (resolves to the dev database based on dbt target)
--
-- To run this test:
-- 1. Compile the query: dbt compile --select test_dim_clip_migration
-- 2. Execute the compiled SQL from target/compiled/analytics/analyses/migrations/test_dim_clip_migration.sql
--    Or use: uv run python query_snowflake.py --file target/compiled/analytics/analyses/migrations/test_dim_clip_migration.sql

{% set old_query %}
    SELECT 
        clip_id,
        p_date,
        p_hour,
        created_at,
        user_id,
        request_id,
        s3_id,
        status,
        platform,
        display_index,
        initial_title,
        time_used_sec,
        type,
        task,
        priority,
        duration_sec,
        is_refund_credits,
        is_bot,
        credit_cost,
        lyrics_at_gen,
        simple_prompt,
        lyrics_final,
        is_simple,
        is_make_instrumental,
        style_tags,
        negative_style_tags,
        request_models,
        model_name,
        experiment_inf_param,
        experiment_version,
        history,
        concat_history,
        error_type,
        error_message,
        upload_has_vocal,
        edit_session_id,
        edited_clip_id,
        task_details,
        creation_source,
        augmented_tags,
        free_quota_category,
        lyrics_language,
        simple_lyrics_model,
        created_session_id,
        clip_uid,
        is_audio_watermark,
        is_audio_watermarked,
        user_tier,
        is_deleted,
        create_mode,
        combined_from_clip_ids
    FROM suno_prod.prod_int.int_dim_clip
    WHERE 1=1
    -- Test with only one hour of data (2025-10-11 hour 10)
    AND {{ partition_filter_hourly('p_date', 'p_hour') }}
{% endset %}

{% set new_query %}
    SELECT 
        clip_id,
        p_date,
        p_hour,
        created_at,
        user_id,
        request_id,
        s3_id,
        status,
        platform,
        display_index,
        initial_title,
        time_used_sec,
        type,
        task,
        priority,
        duration_sec,
        is_refund_credits,
        is_bot,
        credit_cost,
        lyrics_at_gen,
        simple_prompt,
        lyrics_final,
        is_simple,
        is_make_instrumental,
        style_tags,
        negative_style_tags,
        request_models,
        model_name,
        experiment_inf_param,
        experiment_version,
        history,
        concat_history,
        error_type,
        error_message,
        upload_has_vocal,
        edit_session_id,
        edited_clip_id,
        task_details,
        creation_source,
        augmented_tags,
        free_quota_category,
        lyrics_language,
        simple_lyrics_model,
        created_session_id,
        clip_uid,
        is_audio_watermark,
        is_audio_watermarked,
        user_tier,
        is_deleted,
        create_mode,
        combined_from_clip_ids
    FROM {{ ref('int_dim_clip') }}
    WHERE 1=1
    -- Test with only one hour of data (2025-10-11 hour 10)
    AND {{ partition_filter_hourly('p_date', 'p_hour') }}
{% endset %}

{{ 
  audit_helper.compare_and_classify_query_results(
    old_query, 
    new_query, 
    primary_key_columns=['clip_id'], 
    columns=[
        'p_date',
        'p_hour',
        'created_at',
        'user_id',
        'request_id',
        's3_id',
        'status',
        'platform',
        'display_index',
        'initial_title',
        'time_used_sec',
        'type',
        'task',
        'priority',
        'duration_sec',
        'is_refund_credits',
        'is_bot',
        'credit_cost',
        'lyrics_at_gen',
        'simple_prompt',
        'lyrics_final',
        'is_simple',
        'is_make_instrumental',
        'style_tags',
        'negative_style_tags',
        'request_models',
        'model_name',
        'experiment_inf_param',
        'experiment_version',
        'history',
        'concat_history',
        'error_type',
        'error_message',
        'upload_has_vocal',
        'edit_session_id',
        'edited_clip_id',
        'task_details',
        'creation_source',
        'augmented_tags',
        'free_quota_category',
        'lyrics_language',
        'simple_lyrics_model',
        'created_session_id',
        'clip_uid',
        'is_audio_watermark',
        'is_audio_watermarked',
        'user_tier',
        'is_deleted',
        'create_mode',
        'combined_from_clip_ids'
    ]
  )
}}

