build_reference_workbook.py

# -*- coding: utf-8 -*-
"""Concise English reference workbook for the REGA non-Saudi ownership zones.

Three sheets, deliberately concise:
  Zones           - one row per zone (930), REGA's own fields + official area.
  Summary         - counts by category and by region (COUNTIFS/SUMIFS, live).
  Source & Method - full provenance so the file defends itself: what REGA is,
                    the portal/API, exact endpoints, how it was fetched, the
                    909-vs-930 split, the area-unit rule, the 6 self-conflicts,
                    and what is NOT in the data.

Area = REGA's OWN zoneArea field, never recomputed. REGA stores it in mixed
units per record (m2 / km2, no structural rule), so the raw value is ambiguous
by 1e6; the published polygon is used ONLY to pick the unit. Unknown -> n/a.
"""
import json, math, os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# --- sources: REGA "Saudi Properties" portal API -----------------------------
# Paths resolve relative to this file, or override via env vars:
#   REGA_DATA_DIR  directory holding the raw REGA API dumps (default: ./data)
#   OUT_DIR        where the workbook is written           (default: ./output)
HERE     = os.path.dirname(os.path.abspath(__file__))
DATA_DIR = os.environ.get('REGA_DATA_DIR', os.path.join(HERE, 'data'))
OUT_DIR  = os.environ.get('OUT_DIR', os.path.join(HERE, 'output'))
RAW  = os.path.join(DATA_DIR, 'rega_zones_api_raw.json')       # 909 public, WITH geometry
FULL = os.path.join(DATA_DIR, 'official_zones_full_930.json')  # all 930
OUT  = os.path.join(OUT_DIR, 'REGA_KSA_Ownership_Zones.xlsx')
RETRIEVED = '30 June 2026'

CATLABEL = {1:'Urban boundaries', 2:'Giga / mega-projects', 3:'Cities & economic zones',
            4:'Riyadh City', 5:'Jeddah Governorate', 6:'Makkah City',
            7:'Al Madinah', 8:'AlUla Governorate'}
CAT_ORDER = [4, 5, 6, 7, 8, 2, 3, 1]

# ---- spherical polygon area (km2): used ONLY to pick the unit & flag conflicts ----
R = 6371.0088
def ring_area(ring):
    if len(ring) < 4: return 0.0
    s = 0.0
    for i in range(len(ring) - 1):
        lo1, la1 = ring[i]; lo2, la2 = ring[i + 1]
        s += math.radians(lo2 - lo1) * (2 + math.sin(math.radians(la1)) + math.sin(math.radians(la2)))
    return abs(s * R * R / 2.0)
def mp_area(coords):
    return sum(ring_area(p[0]) - sum(ring_area(h) for h in p[1:]) for p in coords if p)

def lst(raw, k):
    v = raw[k]['data']; return (v.get('data') if isinstance(v, dict) else v) or []

# ---- public zones: REGA's official area in km2 + polygon area (for conflict flag) ----
raw = json.load(open(RAW, encoding='utf-8'))
pub = {}
for z in lst(raw, 'subZones'):
    g = z.get('geometry')
    if not g or g.get('type') != 'MultiPolygon': continue
    za = z.get('zoneArea'); geom = mp_area(g['coordinates'])
    if za is None or geom <= 0:
        area, unit = None, None
    else:
        area = min((za, za / 1e6), key=lambda v: abs(math.log((v + 1e-12) / (geom + 1e-12))))
        unit = 'sq km' if area == za else 'sq m'
    pub[z.get('zoneCode')] = {'area': round(area, 6) if area is not None else None,
                              'unit': unit, 'geom': geom, 'raw': za}

# ---- master list of all 930 --------------------------------------------------
full = json.load(open(FULL, encoding='utf-8'))
flagged, records = [], []
for z in full:
    code = z.get('zoneCode'); cid = int(z.get('mainZoneCategoryId'))
    public = str(z.get('isShow')).lower() == 'true'
    za = z.get('zoneArea'); za = float(za) if za not in (None, '') else None
    if code in pub:                                   # public zone with polygon
        area, unit, geom = pub[code]['area'], pub[code]['unit'], pub[code]['geom']
        if area and geom > 0 and abs(area - geom) / max(area, 1e-9) > 0.15:
            flagged.append((z.get('name'), code, area, geom, abs(area - geom) / max(area, 1e-9)))
        unit_lbl = unit
    elif za is not None and za > 5e4:                 # hidden zone, no polygon -> must be sq m
        area, unit_lbl = round(za / 1e6, 6), 'sq m (no polygon)'
    else:
        area, unit_lbl = None, 'n/a'
    records.append({
        'code': code, 'name': z.get('name'), 'nameAr': z.get('nameAr'),
        'region': z.get('region'), 'cat': CATLABEL[cid], 'catId': cid,
        'status': 'Public' if public else 'Pending',
        'area_km2': area, 'area_sqm': (area * 1e6 if area is not None else None),
        'raw': za, 'unit': unit_lbl,
    })
records.sort(key=lambda r: (r['status'] != 'Public', CAT_ORDER.index(r['catId']), -(r['area_km2'] or 0)))
N_PUB  = sum(1 for r in records if r['status'] == 'Public')
N_PEND = len(records) - N_PUB

# ---------------- styling helpers ----------------
TEAL   = '0F766E'
HEAD   = Font(bold=True, color='FFFFFF', size=11)
HFILL  = PatternFill('solid', fgColor=TEAL)
TITLE  = Font(bold=True, size=14, color='0F766E')
SUB    = Font(italic=True, size=10, color='555555')
BOLD   = Font(bold=True)
LABEL  = Font(bold=True, color='0F766E')
thin   = Side(style='thin', color='D9D9D9')
BORDER = Border(left=thin, right=thin, top=thin, bottom=thin)
def header(ws, headers, row=1):
    for c, h in enumerate(headers, 1):
        cell = ws.cell(row, c, h); cell.font = HEAD; cell.fill = HFILL
        cell.alignment = Alignment(vertical='center', horizontal='left')
def safe(v):   # openpyxl treats any string starting with '=' as a formula
    return ("'" + v) if isinstance(v, str) and v.startswith('=') else v

wb = Workbook()

# ============ Sheet 1: Zones ============
ws = wb.active; ws.title = 'Zones'
cols = ['Zone code', 'Name (EN)', 'Name (AR)', 'Region', 'Category', 'Status',
        'Area (sq km)', 'Area (sq m)', 'REGA zoneArea (raw)', 'Unit stored by REGA']
header(ws, cols); ws.freeze_panes = 'A2'
for r in records:
    ws.append([safe(r['code']), safe(r['name']), safe(r['nameAr']), safe(r['region']),
               r['cat'], r['status'], r['area_km2'], r['area_sqm'], r['raw'], r['unit']])
for row in ws.iter_rows(min_row=2, min_col=7, max_col=9):
    row[0].number_format = '#,##0.000'; row[1].number_format = '#,##0'; row[2].number_format = '#,##0.###'
for cell in ws['C'][1:]:
    cell.alignment = Alignment(horizontal='right')
ws.auto_filter.ref = f"A1:{get_column_letter(len(cols))}{ws.max_row}"
for i, w in enumerate([11, 42, 24, 30, 22, 10, 12, 14, 19, 20], 1):
    ws.column_dimensions[get_column_letter(i)].width = w

# ============ Sheet 2: Summary ============
sm = wb.create_sheet('Summary')
sm.column_dimensions['A'].width = 30
for col in 'BCDE': sm.column_dimensions[col].width = 13
sm.cell(1, 1, 'KSA non-Saudi property ownership zones — REGA').font = TITLE
sm.cell(2, 1, f'Royal Decree M/14 (in force 22 Jan 2026). Source: REGA "Saudi Properties" portal, retrieved {RETRIEVED}.').font = SUB
sm.cell(3, 1, f'{len(records)} zones = {N_PUB} public (mapped) + {N_PEND} pending "Destination" master-plans (registered, not yet displayed).').font = SUB

# Categories table (formula-driven off the Zones sheet)
r = 5
sm.cell(r, 1, 'By category').font = LABEL; r += 1
header(sm, ['Category', 'Public', 'Pending', 'Total'], r); r += 1
cat_first = r
for cid in CAT_ORDER:
    sm.cell(r, 1, CATLABEL[cid])
    sm.cell(r, 2).value = f'=COUNTIFS(Zones!$E:$E,$A{r},Zones!$F:$F,"Public")'
    sm.cell(r, 3).value = f'=COUNTIFS(Zones!$E:$E,$A{r},Zones!$F:$F,"Pending")'
    sm.cell(r, 4).value = f'=COUNTIF(Zones!$E:$E,$A{r})'
    r += 1
cat_last = r - 1
sm.cell(r, 1, 'TOTAL').font = BOLD
for c in 'BCD':
    sm[f'{c}{r}'] = f'=SUM({c}{cat_first}:{c}{cat_last})'; sm[f'{c}{r}'].font = BOLD
r += 2

# Regions table (counts + summed public area via SUMIFS)
sm.cell(r, 1, 'By region').font = LABEL; r += 1
header(sm, ['Region', 'Public', 'Pending', 'Total', 'Public area (sq km)'], r); r += 1
reg_first = r
regions = sorted({rec['region'] for rec in records},
                 key=lambda rg: -sum(1 for rec in records if rec['region'] == rg))
for rg in regions:
    sm.cell(r, 1, rg)
    sm.cell(r, 2).value = f'=COUNTIFS(Zones!$D:$D,$A{r},Zones!$F:$F,"Public")'
    sm.cell(r, 3).value = f'=COUNTIFS(Zones!$D:$D,$A{r},Zones!$F:$F,"Pending")'
    sm.cell(r, 4).value = f'=COUNTIF(Zones!$D:$D,$A{r})'
    sm.cell(r, 5).value = f'=SUMIFS(Zones!$G:$G,Zones!$D:$D,$A{r},Zones!$F:$F,"Public")'
    sm.cell(r, 5).number_format = '#,##0'
    r += 1
reg_last = r - 1
sm.cell(r, 1, 'TOTAL').font = BOLD
for c in 'BCD':
    sm[f'{c}{r}'] = f'=SUM({c}{reg_first}:{c}{reg_last})'; sm[f'{c}{r}'].font = BOLD
sm[f'E{r}'] = f'=SUM(E{reg_first}:E{reg_last})'; sm[f'E{r}'].font = BOLD; sm[f'E{r}'].number_format = '#,##0'
r += 1
sm.cell(r, 1, 'Area = REGA official zoneArea (public zones with a polygon). Pending zones carry no polygon, so no area is summed.').font = SUB
sm.merge_cells(start_row=r, start_column=1, end_row=r, end_column=5)

# ============ Sheet 3: Source & Method ============
src = wb.create_sheet('Source & Method')
src.column_dimensions['A'].width = 26
src.column_dimensions['B'].width = 104
src.cell(1, 1, 'Source & Method — how this dataset was obtained').font = TITLE
rows = [
 ('What this is',
  'Every geographic zone in which non-Saudis may hold real-estate rights under the Law of Real Estate Ownership '
  'by Non-Saudis (Royal Decree M/14). One authoritative record per zone: code, English & Arabic name, region, '
  'category, public/pending status and REGA\'s official area.'),
 ('Legal instrument',
  'Royal Decree M/14 (promulgated 14 Jul 2025) — in force 22 Jan 2026. Executive regulations and the designated '
  'zones were approved by the Cabinet on 23 Jun 2026; the "Saudi Properties" application portal opened late Jun 2026.'),
 ('Publisher / authority',
  'REGA — the Real Estate General Authority (الهيئة العامة للعقار), the Saudi government regulator that defines and '
  'publishes the zones. This is the primary, official source: not a press list, broker list or third-party dataset.'),
 ('Primary source',
  'REGA "Saudi Properties" portal — https://saudiproperties.rega.gov.sa (the official public portal for the law). '
  'It is an Angular single-page app; its runtime config (/assets/config.json) points at the JSON API below.'),
 ('Data API',
  'https://saudipropertiesApi.rega.gov.sa — the portal\'s own backend. The data is served as structured JSON '
  '(not scraped from rendered HTML), so it is exact and machine-readable, including the zone geometries.'),
 ('Endpoints used',
  'GET /api/lookups/sub-zone-categories  (all zones + geometry; unfiltered = 930, ?isShow=true = 909 public)\n'
  'GET /api/lookups/main-zone-categories/GetMainZonesGroups  (the 8 categories)\n'
  'GET /api/lookups/regions  (the 13 administrative regions)\n'
  'GET /api/FAQ/GetAllFAQs?languageId=1  (42 official REGA Q&A, reference only)'),
 ('How it was fetched',
  'The portal was opened in a real browser (Playwright); the API endpoints were then called with a page-context '
  'fetch() from inside that page. Running the request in the page\'s own context uses the portal\'s headers and '
  'origin, so there are no CORS or authentication issues. REGA\'s main site (rega.gov.sa) blocks datacenter IPs, '
  'but this official portal API responds normally.'),
 ('Retrieved', f'{RETRIEVED}. The raw JSON response was saved verbatim and retained for audit.'),
 ('Coverage',
  f'{len(records)} zones total = {N_PUB} public + {N_PEND} pending, across 8 categories and all 13 regions. '
  'Each zone has 12 fixed fields (id, regionId, zoneTypeId, cityId, zoneCode, name, nameAr, geometry, zoneArea, '
  'isShow, mainZoneCategoryId, createdAt).'),
 ('Public vs pending',
  f'"Public" = REGA flag isShow=true ({N_PUB} zones, each with a polygon — these appear on the public map). '
  f'"Pending" = isShow=false ({N_PEND} zones): pre-launch "Destination" master-plans that REGA has registered but '
  'not yet displayed, and which carry no public polygon. Always query the API unfiltered, or the 21 hidden zones '
  'are silently dropped.'),
 ('Area handling',
  'Area is REGA\'s own zoneArea value, never recomputed. REGA stores it in mixed units per record (square metres for '
  'some zones, square kilometres for others, with no structural rule), so the raw number is ambiguous by a factor '
  'of 1,000,000. For public zones the unit is fixed by comparing the value to REGA\'s own published polygon; the '
  'polygon is used ONLY to choose the unit, the figure shown is REGA\'s. Pending zones (no polygon) are square '
  'metres (every value far exceeds any plausible km2). Unknown area -> n/a.'),
 ('Known REGA quirks',
  'In 6 public zones REGA\'s stated zoneArea disagrees with its own polygon by >15%. We show REGA\'s published '
  'figure as-is (we do not "fix" it): Sports Boulevard & Arts District (L038), Diriyah Gate (L065), Al Azhim '
  '(L925), Al Mudhaylif (L452), Rashidah (L739), Takhayel (L721).'),
 ('Not in this data',
  'The API does not carry a per-zone ownership percentage cap, usufruct duration, or the allowed rights/uses — '
  'those live in REGA\'s "Geographic Scope Document" (PDF) and the "Ownership Matrix" (مصفوفة التملك: beneficiary '
  'type x property use x holy-city rule). City names are also not resolvable (cityId has no public lookup).'),
 ('Reproducibility',
  'Anyone can reproduce this by issuing the same GET requests to saudipropertiesApi.rega.gov.sa; the JSON is '
  'deterministic. Raw response, build script and this workbook are retained.'),
]
r = 3
for label, text in rows:
    a = src.cell(r, 1, label); a.font = LABEL; a.alignment = Alignment(vertical='top', wrap_text=True)
    b = src.cell(r, 2, text); b.alignment = Alignment(vertical='top', wrap_text=True)
    a.border = BORDER; b.border = BORDER
    src.row_dimensions[r].height = 15 * (1 + text.count('\n') + len(text) // 100)
    r += 1

os.makedirs(OUT_DIR, exist_ok=True)
wb.save(OUT)
print('WROTE', OUT)
print(f'Zones: {len(records)} ({N_PUB} public + {N_PEND} pending) | flagged: {len(flagged)} | '
      f'size KB: {round(os.path.getsize(OUT) / 1024, 1)}')