Replicate Basic Fields in Editor.sql

create local temp table sample_canonicals on commit preserve rows as
(
 with canonicals as
 (
  select c.id as canonical_place_id,
  		   count(distinct place_id) as places
  from canonical_places c inner join place_mappings m on c.id = m.canonical_place_id
  where enabled
  group by c.id
  order by random()
  )
  select canonical_place_id
  from canonicals
  --where places > 10
  --limit 1000
 )
;

create local temp table relevant_info on commit preserve rows as
(
  --with sample_canonicals as (select '33717'::int as canonical_place_id)
 select c.canonical_place_id,
    		m.place_id,
     		m.owner_verified,
     		s.priority,
     		m.updated_at,
    		row_number() over (partition by canonical_place_id
                           order     by m.owner_verified desc,
                                        s.priority       asc,
                                        m.updated_at     asc,
                                        m.place_id       asc) as layer_order
 from sample_canonicals c inner join place_mappings m using (canonical_place_id)
                          inner join places         p on m.place_id = p.id
                          inner join sources        s on p.source_id = s.id
 order by c.canonical_place_id,
 	    	  m.owner_verified desc,
 	    	  s.priority       asc, 
 	    	  m.updated_at     asc,
 	    	  m.place_id 	     asc
 )
;

create local temp table details on commit preserve rows as 
(
 select i.*,
        p.name,
        regexp_replace(p.description, ',|\n', '', 'g') as description,
        p.subtitle,
        p.alt_names,
        d.permanently_closed,
        p.enabled
 from relevant_info i inner join places        p on i.place_id = p.id
                      left  join place_details d using (place_id)
 order by i.canonical_place_id, i.layer_order
 )
;

create local temp table details_enabled on commit preserve rows as
(
 select * from details where enabled is true
 )
;

create local temp table top_values on commit preserve rows as
(
 select canonical_place_id,
        first_value(name)        over (partition by canonical_place_id order by layer_order asc, name        is null, ts) as name,
        first_value(description) over (partition by canonical_place_id order by layer_order asc, description is null, ts) as description,
        first_value(subtitle)    over (partition by canonical_place_id order by layer_order asc, subtitle    is null, ts) as subtitle,
        first_value(alt_names)   over (partition by canonical_place_id order by layer_order asc, alt_names   is null, ts) as alt_names
 from details
 order by canonical_place_id
 )
;