`db/seed-satellites.sql`

69 lines
-- Seed satellite tables (parcel / structure / media_asset) for the BH addresses.

-- Parcels — fictional APNs in the LA County 4349-xxx-xxx range used by BH.
WITH listings_with_apn AS (
  SELECT * FROM (VALUES
    ('1100-elden-way',         '4349-020-012'),
    ('814-roxbury-drive',      '4349-013-007'),
    ('512-camden-drive',       '4349-009-021'),
    ('1037-loma-vista-drive',  '4349-031-004'),
    ('600-bedford-drive',      '4349-010-018'),
    ('230-palm-drive',         '4349-022-009'),
    ('925-benedict-canyon',    '4349-040-002'),
    ('410-rexford-drive',      '4349-011-014')
  ) AS x(slug, apn)
)
INSERT INTO parcel (apn, jurisdiction, current_listing_id, notes)
SELECT lwa.apn, 'Los Angeles County', l.id, 'Seeded for UI development; replace on first county sync.'
FROM listings_with_apn lwa
JOIN listing l ON l.slug = lwa.slug
ON CONFLICT (apn) DO NOTHING;

-- Structures — pull seed values from the listing rows themselves.
INSERT INTO structure (listing_id, year_built, property_type, bedrooms, bathrooms, source_tier, source_label)
SELECT id,
       CASE slug
         WHEN '1100-elden-way' THEN 1928
         WHEN '814-roxbury-drive' THEN 1928
         WHEN '512-camden-drive' THEN 1957
         WHEN '1037-loma-vista-drive' THEN 1971
         WHEN '600-bedford-drive' THEN 1934
         WHEN '230-palm-drive' THEN 1923
         WHEN '925-benedict-canyon' THEN 1962
         WHEN '410-rexford-drive' THEN 1939
       END,
       property_type,
       bedrooms,
       bathrooms,
       'A',
       'BH Assessor'
FROM listing
WHERE city = 'Beverly Hills'
ON CONFLICT DO NOTHING;

-- Media assets — Sanborn maps, archival photos, postcards.
-- Storage URLs are placeholder Unsplash images until real archive scans land.
WITH bh AS (SELECT id, slug FROM listing WHERE city = 'Beverly Hills')
INSERT INTO media_asset (listing_id, kind, year, caption, storage_url, source_tier, source_label, source_url, rights, public_visible)
SELECT bh.id, m.kind, m.year, m.caption, m.url, m.tier, m.label, m.src_url, m.rights, true
FROM bh
CROSS JOIN LATERAL (
  VALUES
    ('historic_map',   1923, 'Sanborn fire insurance map sheet covering this block.',
       'https://images.unsplash.com/photo-1524661135-423995f22d0b?w=1200',
       'B', 'Sanborn Map Company / Library of Congress',
       'https://www.loc.gov/collections/sanborn-maps/',
       'Public domain (LoC)'),
    ('archival_photo', 1938, 'Streetscape photograph from the WPA neighborhood survey.',
       'https://images.unsplash.com/photo-1497366216548-37526070297c?w=1200',
       'B', 'BH Public Library — local history',
       'https://beverlyhills.org/library',
       'Research use only — credit Beverly Hills Public Library'),
    ('postcard',       1954, 'Color-tinted postcard, distributed by a Sunset Boulevard newsstand.',
       'https://images.unsplash.com/photo-1517502884422-41eaead166d4?w=1200',
       'C', 'Private collection',
       NULL,
       'Editorial use')
) AS m(kind, year, caption, url, tier, label, src_url, rights)
ON CONFLICT DO NOTHING;