"""Reproducible Overture building extraction; never uses Overpass for bulk data."""
import argparse, pathlib, duckdb, json, time
ROOT=pathlib.Path(__file__).resolve().parents[1]
REGIONS={
 # --- added by Claude (supervisor) 2026-09-30 22:00, owner request: more film cities ---
 'brussels':[4.25,50.75,4.5,50.95], 'luxembourg':[6.05,49.55,6.2,49.67], 'bratislava':[17.0,48.08,17.2,48.22],
 'ljubljana':[14.43,46.0,14.6,46.1], 'zagreb':[15.85,45.75,16.1,45.87], 'athens':[23.6,37.88,23.85,38.08],
 'bucharest':[25.95,44.35,26.25,44.53], 'sofia':[23.2,42.62,23.45,42.76], 'valletta':[14.4,35.84,14.56,35.95],
 'oslo':[10.6,59.85,10.9,59.98], 'manchester':[-2.35,53.4,-2.1,53.55], 'edinburgh':[-3.35,55.9,-3.1,55.99],
 'toronto':[-79.6,43.58,-79.2,43.85], 'vancouver':[-123.25,49.18,-123.0,49.32], 'montreal':[-73.75,45.42,-73.47,45.6],
 'sydney':[151.0,-34.0,151.3,-33.75], 'melbourne':[144.85,-37.9,145.05,-37.75],
 'miami':[-80.35,25.7,-80.1,25.9], 'austin':[-97.85,30.18,-97.65,30.38], 'new-orleans':[-90.15,29.9,-89.95,30.05],
 'baltics':[20.7,53.85,28.25,59.85],
 'poland-east':[19.0,48.95,24.2,54.9], 'poland-west':[13.9,48.95,19.0,54.9],
 'london':[-0.55,51.25,0.35,51.75], 'paris':[2.05,48.65,2.65,49.05],
 'berlin':[13.05,52.30,13.8,52.7], 'rome':[12.2,41.65,12.8,42.15],
 'madrid':[-3.95,40.2,-3.4,40.65], 'barcelona':[1.95,41.25,2.35,41.6],
 'prague':[14.2,49.9,14.7,50.25], 'budapest':[18.85,47.3,19.4,47.7],
 'vienna':[16.15,48.05,16.65,48.35], 'amsterdam':[4.65,52.2,5.1,52.5],
 'warsaw':[20.75,52.05,21.3,52.4], 'dublin':[-6.5,53.15,-6.05,53.5],
 'lisbon':[-9.4,38.55,-9.0,38.85], 'copenhagen':[12.25,55.5,12.75,55.85],
 'stockholm':[17.65,59.15,18.4,59.55], 'helsinki':[24.6,60.1,25.3,60.4],
 'new-york':[-74.3,40.45,-73.65,40.95], 'los-angeles':[-118.7,33.7,-118.1,34.35],
 'san-francisco':[-122.55,37.65,-122.3,37.85], 'chicago':[-87.95,41.65,-87.5,42.05],
 'atlanta':[-84.65,33.5,-84.1,34.1],
}
def main():
 p=argparse.ArgumentParser(); p.add_argument('regions',nargs='*'); p.add_argument('--release',default='2026-09-23.1'); p.add_argument('--storage',choices=['aws','azure'],default='aws'); p.add_argument('--threads',type=int,default=8); a=p.parse_args()
 if a.release!='2026-09-23.1':raise ValueError('Update the STAC index, release and REV together for a new snapshot')
 c=duckdb.connect(); c.execute('INSTALL httpfs; LOAD httpfs; INSTALL spatial; LOAD spatial;')
 c.execute("SET s3_region='us-west-2'; SET memory_limit='3GB'; SET preserve_insertion_order=false; SET http_timeout=120; SET http_retries=2;")
 c.execute(f'SET threads={max(1,min(16,a.threads))}')
 dest=ROOT/'data'/'extracts'; dest.mkdir(parents=True,exist_ok=True)
 for name in a.regions or REGIONS:
  b=REGIONS[name]; f=dest/(name+'.parquet')
  if f.exists(): print(name,'already extracted',flush=True); continue
  # Query covers an expanded box; tile builder only advertises the inner complete area.
  x0,y0,x1,y1=b; x0-=.03; y0-=.03; x1+=.03; y1+=.03
  index=ROOT/'data'/'source-index.json'
  if not index.exists(): raise RuntimeError('Run source_index.py first to avoid global Parquet scans')
  hits=[r['url'] for r in json.loads(index.read_text()) if r['bbox'][0]<=x1 and r['bbox'][2]>=x0 and r['bbox'][1]<=y1 and r['bbox'][3]>=y0]
  if a.storage=='azure':hits=[u.replace('https://overturemaps-us-west-2.s3.us-west-2.amazonaws.com/','https://overturemapswestus2.blob.core.windows.net/') for u in hits]
  source='['+','.join("'"+u+"'" for u in hits)+']'
  print(name,a.storage,'STAC-selected files',len(hits),flush=True)
  q=f"""SELECT id, ST_AsWKB(geometry) AS geometry, height, num_floors, min_height,
    has_parts, to_json(sources) AS sources FROM read_parquet({source})
    WHERE bbox.xmin <= {x1} AND bbox.xmax >= {x0} AND bbox.ymin <= {y1} AND bbox.ymax >= {y0}
    AND coalesce(is_underground,false)=false"""
  print(name,'starting',flush=True); start=time.time()
  c.execute(f"COPY ({q}) TO '{f}.partial' (FORMAT PARQUET, COMPRESSION ZSTD)")
  pathlib.Path(str(f)+'.partial').rename(f)
  count=c.execute(f"SELECT count(*) FROM '{f}'").fetchone()[0]
  print(name,json.dumps({'buildings':count,'bytes':f.stat().st_size,'seconds':round(time.time()-start)}),flush=True)
if __name__=='__main__':main()