with settlements as (
    select * from {{ ref('stg_network_settlements') }}
),

authorizations as (
    select *
    from {{ ref('int_authorizations') }}
    where auth_status = 'approved'
),

cards as (
    select card_id, card_token, company_id from {{ ref('stg_cards') }}
),

-- the network only ever sends a token; resolve it back to a card
settlements_with_card as (
    select
        s.*,
        c.card_id,
        c.company_id
    from settlements s
    left join cards c on s.card_token = c.card_token
),

-- ── Tier 1: authorization code, scoped to the card ──────────────────────────
--
-- An auth_code is SIX characters. It is not a globally unique key and was
-- never meant to be one -- the network guarantees uniqueness only within a
-- card over a short window, then recycles. Across 187k authorizations the
-- birthday bound predicts ~1,045 collisions and the data contains 1,015.
--
-- Joining on auth_code alone therefore attributes ~0.6% of settled volume to
-- the wrong company. It looks exact, it passes every not_null and every
-- relationships test, and it is wrong. scripts/validate_matching.py is what
-- caught it; no schema test would have.
tier_1 as (
    select
        s.network_txn_id,
        a.auth_id,
        'auth_code' as match_method
    from settlements_with_card s
    inner join authorizations a
        on  s.auth_code    = a.auth_code
        and s.card_id      = a.card_id
        and s.settled_date between a.auth_date and a.auth_date + 40
    qualify row_number() over (
        partition by s.network_txn_id
        order by a.auth_date desc, a.auth_id
    ) = 1
),

-- ── Tier 2: fuzzy match for records the network sent with no auth_code ──────
needs_fuzzy as (
    select s.*
    from settlements_with_card s
    left join tier_1 t on s.network_txn_id = t.network_txn_id
    where t.network_txn_id is null
),

fuzzy_candidates as (
    select
        u.network_txn_id,
        a.auth_id,
        abs(abs(u.amount_usd) - a.amount_usd)                as amount_gap,
        date_diff('day', a.auth_date, u.settled_date)        as day_gap
    from needs_fuzzy u
    inner join authorizations a
        on  u.card_id      = a.card_id
        and u.mcc          = a.mcc
        -- a settlement never precedes its authorization, and the network
        -- gives up on unsettled holds after about 40 days
        and u.settled_date between a.auth_date and a.auth_date + 40
        -- you cannot capture more than you authorized (1% tolerance for tips)
        and abs(u.amount_usd) <= a.amount_usd * 1.01
),

-- best candidate per settlement
fuzzy_best as (
    select *
    from fuzzy_candidates
    qualify row_number() over (
        partition by network_txn_id
        order by amount_gap asc, day_gap asc, auth_id asc
    ) = 1
),

-- How much of each authorization has already been captured by tier 1? An
-- authorization for $400 that tier 1 already matched to $380 of settlements
-- has $20 of room left, and a fuzzy candidate for $150 does not belong to it
-- no matter how close the merchant and date look.
capacity as (
    select
        a.auth_id,
        a.amount_usd                                                    as auth_amount_usd,
        coalesce(sum(case when not s.is_reversal then s.amount_usd else 0 end), 0)
                                                                        as tier_1_captured_usd
    from authorizations a
    left join tier_1 t                on a.auth_id = t.auth_id
    left join settlements_with_card s on t.network_txn_id = s.network_txn_id
    group by 1, 2
),

-- Fill the remaining room best-match-first, and stop when it runs out.
fuzzy_within_capacity as (
    select
        f.network_txn_id,
        f.auth_id,
        c.auth_amount_usd,
        c.tier_1_captured_usd,
        sum(case when not s.is_reversal then s.amount_usd else 0 end) over (
            partition by f.auth_id
            order by f.amount_gap asc, f.day_gap asc, f.network_txn_id asc
            rows between unbounded preceding and current row
        )                                                               as running_fuzzy_usd
    from fuzzy_best f
    inner join settlements_with_card s on f.network_txn_id = s.network_txn_id
    inner join capacity c              on f.auth_id = c.auth_id
),

-- A settlement that does not fit is left UNMATCHED rather than forced onto
-- the nearest authorization. That is a deliberate trade: an unattributed
-- settlement is visible and lands in a review queue, while a wrongly
-- attributed one silently bills the wrong company and shows up as a
-- reconciliation break weeks later. Precision over recall, because the two
-- failure modes do not cost the same.
tier_2 as (
    select
        network_txn_id,
        auth_id,
        'fuzzy_card_amount_window' as match_method
    from fuzzy_within_capacity
    where tier_1_captured_usd + running_fuzzy_usd <= auth_amount_usd * 1.01
),

all_matches as (
    select * from tier_1
    union all
    select * from tier_2
),

-- ── Every settlement, matched or not ────────────────────────────────────────
settlement_rows as (
    select
        case when m.auth_id is null then 'force_post' else 'matched' end as record_type,
        s.network_txn_id,
        m.auth_id,
        coalesce(a.company_id, s.company_id)            as company_id,
        coalesce(a.card_id, s.card_id)                  as card_id,
        s.merchant_descriptor,
        s.mcc,
        a.amount_usd                                    as auth_amount_usd,
        s.amount_usd                                    as settled_amount_usd,
        a.auth_date,
        s.settled_date,
        s.file_date,
        s.file_lag_days,
        s.is_reversal,
        coalesce(m.match_method, 'unmatched')           as match_method,
        date_diff('day', a.auth_date, s.settled_date)   as days_auth_to_settle
    from settlements_with_card s
    left join all_matches m    on s.network_txn_id = m.network_txn_id
    left join authorizations a on m.auth_id = a.auth_id
),

-- ── Approved authorizations that never settled ──────────────────────────────
open_authorization_rows as (
    select
        'open_auth'                                     as record_type,
        cast(null as varchar)                           as network_txn_id,
        a.auth_id,
        a.company_id,
        a.card_id,
        a.merchant_raw_name                             as merchant_descriptor,
        a.mcc,
        a.amount_usd                                    as auth_amount_usd,
        cast(null as decimal(18, 2))                    as settled_amount_usd,
        a.auth_date,
        cast(null as date)                              as settled_date,
        cast(null as date)                              as file_date,
        cast(null as bigint)                            as file_lag_days,
        false                                           as is_reversal,
        'no_settlement'                                 as match_method,
        cast(null as bigint)                            as days_auth_to_settle
    from authorizations a
    left join all_matches m on a.auth_id = m.auth_id
    where m.auth_id is null
)

select * from settlement_rows
union all
select * from open_authorization_rows