with

    b1 as (select '2025-04-08'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom,

                false as is_current
           from adhoc.scci_bduk_mdu_geom_20250408 b
                    inner join basedata.abp on b.postcode = abp.postcode
           limit 100


           )
,b2 as (

select '2025-05-19'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom,

                false as is_current
from adhoc.scci_cfl_mdu_pia_distance_with_bduk_geom_20250519 b
inner join basedata.abp on b.toid = abp.cross_reference
where b.voucher like '%Y%'
limit 100
)
,b3 as (
select '2025-08-07'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom,

                false as is_current
from adhoc.bduk_go_list_address_view_geom b
inner join basedata.abp on b.uprn = abp.uprn

limit 100
)
,b4 as (
select '2025-10-07'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom,

                false as is_current
from adhoc.bduk_go_list_fmp_work_20251003_address_geom b
inner join basedata.abp on b.uprn = abp.uprn
limit 100
)


,b5 as (select '2026-02-17'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom,

                false as is_current
        from adhoc.bduk_go_list_fmp_work_20260217_geom b
                 inner join basedata.abp on b.uprn = abp.uprn
        limit 100
        )


,b6 as (

select '2026-06-01'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom,

                true as is_current
        from adhoc.bduk_fmp_work_may_2026_20260601 b
                 inner join basedata.abp on b.uprn = abp.uprn
limit 100
        )

select * from b1

union all

select * from b2


union all

select * from b3


union all

select * from b4


union all

select * from b5


union all

select * from b6






;

-- ============================================================
-- UPRN Tracking Summary
-- 1 row per UPRN: is_current, date_first_seen, date_last_seen, times_seen
-- NOTE: no LIMIT clauses here -- full datasets required for correct aggregation
-- ============================================================
with all_bduk as (

    -- b1: April 2025 -- joined on postcode (brings in all UPRNs at matching postcodes)
    select '2025-04-08'::date as bduk_date, abp.uprn
    from adhoc.scci_bduk_mdu_geom_20250408 b
             inner join basedata.abp on b.postcode = abp.postcode

    union all

    -- b2: May 2025 -- joined on TOID, voucher-filtered
    select '2025-05-19'::date, abp.uprn
    from adhoc.scci_cfl_mdu_pia_distance_with_bduk_geom_20250519 b
             inner join basedata.abp on b.toid = abp.cross_reference
    where b.voucher like '%Y%'

    union all

    -- b3: August 2025 -- joined on UPRN
    select '2025-08-07'::date, abp.uprn
    from adhoc.bduk_go_list_address_view_geom b
             inner join basedata.abp on b.uprn = abp.uprn

    union all

    -- b4: October 2025 -- joined on UPRN
    select '2025-10-07'::date, abp.uprn
    from adhoc.bduk_go_list_fmp_work_20251003_address_geom b
             inner join basedata.abp on b.uprn = abp.uprn

    union all

    -- b5: February 2026 -- joined on UPRN
    select '2026-02-17'::date, abp.uprn
    from adhoc.bduk_go_list_fmp_work_20260217_geom b
             inner join basedata.abp on b.uprn = abp.uprn

    union all

    -- b6: June 2026 (latest / current) -- joined on UPRN
    select '2026-06-01'::date, abp.uprn
    from adhoc.bduk_fmp_work_may_2026_20260601 b
             inner join basedata.abp on b.uprn = abp.uprn

)

-- deduplicate within each dataset so a 1-to-many join does not
-- inflate times_seen (one row per uprn per dataset date)
, deduped as (
    select distinct
        uprn,
        bduk_date
    from all_bduk
)

-- aggregate to 1 row per UPRN
, summary as (
    select
        uprn,
        bool_or(bduk_date = '2026-06-01'::date)  as is_current,       -- present in latest dataset?
        min(bduk_date)                            as date_first_seen,
        max(bduk_date)                            as date_last_seen,
        count(*)                                  as times_seen         -- # distinct datasets
    from deduped
    group by uprn
)

-- re-join ABP to recover stable address attributes (postcode, toid, geom)
-- avoids having to GROUP BY geometry
select
    s.uprn,
    abp.address,
    abp.postcode,
    bt.building_type,
       bt.building_use,
       bt.building_premises,
       bt.building_premises_residential,
    abp.cross_reference                          as toid,
    abp.geom,
    s.is_current,
    s.date_first_seen,
    s.date_last_seen,
    s.times_seen
into adhoc.bduk_uprn_tracking_v2
from summary s
         inner join basedata.abp on s.uprn = abp.uprn
            inner join basedata.toid_building_type bt on abp.cross_reference = bt.toid
--order by s.uprn
;
create index bduk_uprn_tracking_v2_geom_idx on adhoc.bduk_uprn_tracking_v2 using gist (geom);

select * from adhoc.bduk_uprn_tracking_v2;