-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    dbt_audit_row_status,id,dbt_audit_num_rows_in_status, 'actual' as "actual_or_expected"
  from (
    
    
    

    

    with 

    
    

     __dbt__cte__unit_test_model_a as (

-- Fixture for unit_test_model_a
select 
    
    cast(null as integer)
 as id, cast(null as integer) as id_2, 
    
    cast('abc' as character varying(3))
 as col1, 
    
    cast('def' as character varying(3))
 as col2, cast(null as integer) as col3, cast(null as timestamp without time zone) as created_at
union all
select 
    
    cast(null as integer)
 as id, cast(null as integer) as id_2, 
    
    cast('hij' as character varying(3))
 as col1, 
    
    cast('klm' as character varying(3))
 as col2, cast(null as integer) as col3, cast(null as timestamp without time zone) as created_at
union all
select 
    
    cast(3 as integer)
 as id, cast(null as integer) as id_2, 
    
    cast('nop' as character varying(3))
 as col1, 
    
    cast('qrs' as character varying(3))
 as col2, cast(null as integer) as col3, cast(null as timestamp without time zone) as created_at
),  __dbt__cte__unit_test_model_b as (

-- Fixture for unit_test_model_b
select 
    
    cast(null as integer)
 as id, cast(null as integer) as id_2, 
    
    cast('abc' as character varying(3))
 as col1, 
    
    cast('def' as character varying(3))
 as col2, cast(null as integer) as col3, cast(null as timestamp without time zone) as created_at
union all
select 
    
    cast(null as integer)
 as id, cast(null as integer) as id_2, 
    
    cast('hij' as character varying(3))
 as col1, 
    
    cast('klm' as character varying(3))
 as col2, cast(null as integer) as col3, cast(null as timestamp without time zone) as created_at
union all
select 
    
    cast(3 as integer)
 as id, cast(null as integer) as id_2, 
    
    cast('nop' as character varying(3))
 as col1, 
    
    cast('qrs' as character varying(3))
 as col2, cast(null as integer) as col3, cast(null as timestamp without time zone) as created_at
), a_base as (
        select 
            id, col1, col2, 
            md5(cast(coalesce(cast(id as TEXT), '_dbt_audit_helper_surrogate_key_null_') as TEXT)) as dbt_audit_surrogate_key
        from (select * from __dbt__cte__unit_test_model_a) a_base_subq
        
    ),

    b_base as (
        select 
            id, col1, col2, 
            md5(cast(coalesce(cast(id as TEXT), '_dbt_audit_helper_surrogate_key_null_') as TEXT)) as dbt_audit_surrogate_key
        from (select * from __dbt__cte__unit_test_model_b) b_base_subq
        
    ),

    a as (
        select 
            *, 
            row_number() over (partition by dbt_audit_surrogate_key order by dbt_audit_surrogate_key) as dbt_audit_pk_row_num
        from a_base
    ),

    b as (
        select 
            *, 
            row_number() over (partition by dbt_audit_surrogate_key order by dbt_audit_surrogate_key) as dbt_audit_pk_row_num
        from b_base
    ),

    a_intersect_b as (

        select * from a
        

    intersect


        select * from b

    ),

    a_except_b as (

        select * from a
        

    except


        select * from b

    ),

    b_except_a as (

        select * from b
        

    except


        select * from a

    )

    
    ,

    all_records as (

        select
            *,
            true as in_a,
            true as in_b
        from a_intersect_b

        union all

        select
            *,
            true as in_a,
            false as in_b
        from a_except_b

        union all

        select
            *,
            false as in_a,
            true as in_b
        from b_except_a

    ),


    classified as (
        select 
            *,
            case 
        when max(dbt_audit_pk_row_num) over (partition by dbt_audit_surrogate_key) > 1 then 'nonunique_pk'
        when in_a and in_b then 'identical'
        when max(case when in_a then 1 else 0 end) over (partition by dbt_audit_surrogate_key, dbt_audit_pk_row_num) = 1
            and max(case when in_b then 1 else 0 end) over (partition by dbt_audit_surrogate_key, dbt_audit_pk_row_num) = 1
            then 'modified'
        when in_a then 'removed'
        when in_b then 'added'
    end as dbt_audit_row_status
        from all_records
    ),

    final as (
        select 
            *,
            (count(*) over (partition by dbt_audit_row_status)) / case when dbt_audit_row_status = 'modified' then 2 else 1 end
 as dbt_audit_num_rows_in_status,
            dense_rank() over (partition by dbt_audit_row_status order by dbt_audit_surrogate_key, dbt_audit_pk_row_num) as dbt_audit_sample_number
        from classified
    )

    select * from final
    
        where dbt_audit_sample_number <= 20
    
    order by dbt_audit_row_status, dbt_audit_sample_number


  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    dbt_audit_row_status, id, dbt_audit_num_rows_in_status, 'expected' as "actual_or_expected"
  from (
    select 
    
    cast('identical' as character varying(12))
 as dbt_audit_row_status, 
    
    cast(3 as character varying(1))
 as id, 
    
    cast(1 as bigint)
 as dbt_audit_num_rows_in_status
union all
select 
    
    cast('nonunique_pk' as character varying(12))
 as dbt_audit_row_status, 
    
    cast(null as character varying(1))
 as id, 
    
    cast(2 as bigint)
 as dbt_audit_num_rows_in_status
union all
select 
    
    cast('nonunique_pk' as character varying(12))
 as dbt_audit_row_status, 
    
    cast(null as character varying(1))
 as id, 
    
    cast(2 as bigint)
 as dbt_audit_num_rows_in_status
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected