#!/usr/bin/env python3
"""
Phase 5a: Export map data from the certification spine + research docs.

Produces map_data.json containing:
  - galleries: from the spine (name, address, lat/lng, certification state, tier, hooks)
  - advisory_contacts: from The Advisory Path doc (Christie's/Sotheby's org charts)
  - approachable_contacts: from Who to Actually Know doc (5 tiers + communities)
  - routes: cluster efficiency routes
  - field_capture_schema: the 20-second visit capture fields
"""

import json
import sqlite3
import sys
from pathlib import Path
from datetime import datetime, timezone

sys.path.insert(0, str(Path(__file__).parent))
from certification_spine import CertificationEngine, DEFAULT_DB

OUT = Path(__file__).parent / "map_data.json"

# ── Gallery coordinate data ─────────────────────────────────────────────
# Lat/lng for all galleries in the spine. Verified Manhattan locations.
# Format: gallery_id -> (name_display, lat, lng, neighborhood, tier_label)

GALLERY_COORDS = {
    # ── Chelsea ──────────────────────────────────────────────────────────
    "gagosian":        ("Gagosian",              40.7468, -74.0063, "Chelsea",       "Mega"),
    "pace":            ("Pace Gallery",          40.7485, -74.0048, "Chelsea",       "Mega"),
    "petzel":          ("Petzel",                40.7489, -74.0056, "Chelsea",       "Mega"),
    "taglialatella":   ("Taglialatella Galleries", 40.7455, -74.0021, "Chelsea",    "Major"),
    "gladstone":       ("Gladstone Gallery",     40.7461, -74.0012, "Chelsea",       "Mega"),
    "lisson":          ("Lisson Gallery",        40.7477, -73.9978, "Chelsea",       "Major"),
    "hauser_wirth":    ("Hauser & Wirth",       40.7461, -74.0001, "Chelsea",       "Mega"),
    "hauser_wirth_2":  ("Hauser & Wirth (Wooster)", 40.7275, -74.0022, "Tribeca",   "Mega"),
    "casey_kaplan":    ("Casey Kaplan",          40.7495, -74.0008, "Chelsea",       "Mid"),
    "miles_mcenery":   ("Miles McEnery Gallery", 40.7463, -74.0024, "Chelsea",       "Mid"),
    "anton_kern":      ("Anton Kern Gallery",    40.7425, -74.0108, "Chelsea",       "Major"),
    "gallery_303":     ("303 Gallery",           40.7464, -74.0020, "Chelsea",       "Major"),
    "jeffrey_deitch":  ("Jeffrey Deitch",        40.7210, -74.0018, "SoHo",          "Mid"),
    "ippodo":          ("Ippodo Gallery",        40.7470, -73.9998, "Chelsea",       "Boutique"),
    "paula_cooper":    ("Paula Cooper Gallery",  40.7482, -73.9998, "Chelsea",       "Major"),
    "luhring_augustine": ("Luhring Augustine",   40.7240, -74.0008, "SoHo",          "Major"),
    "bortolami":       ("Bortolami Gallery",     40.7190, -74.0055, "Tribeca",       "Major"),
    "salon_94":        ("Salon 94",              40.7720, -73.9670, "Upper East Side","Major"),
    "skinner":         ("Contessa Gallery",      40.8000, -73.8400, "Dormant (Hamptons)", "Boutique"),

    # ── 57th Street ───────────────────────────────────────────────────────
    "findlay":         ("Findlay Galleries",     40.7608, -73.9747, "Midtown (57th St)", "Major"),

    # ── Hudson Yards ──────────────────────────────────────────────────────
    "avant":           ("Avant Gallery",         40.7573, -74.0005, "Hudson Yards",  "Boutique"),

    # ── 5th Avenue / Midtown ──────────────────────────────────────────────
    "lik":             ("LIK Fine Art",          40.7658, -73.9745, "Midtown (5th Ave)", "Major"),
    "sothebys_salon":  ("Sotheby's — The Salon",40.7725, -73.9515, "UES (York Ave)", "Auction"),

    # ── SoHo ──────────────────────────────────────────────────────────────
    "eden":            ("EDEN Gallery",          40.7230, -74.0010, "SoHo",          "Major"),
    "sorrel_sky":      ("Sorrel Sky Gallery",    40.7223, -74.0015, "SoHo",          "Major"),
    "marian_goodman":  ("Marian Goodman",        40.7210, -74.0012, "SoHo",          "Mega"),
    "56_henry":        ("56 Henry",              40.7240, -73.9910, "SoHo",          "Boutique"),

    # ── Tribeca ───────────────────────────────────────────────────────────
    "david_zwirner":   ("David Zwirner (Tribeca)", 40.7208, -74.0055, "Tribeca",   "Mega"),
    "jack_shainman":   ("Jack Shainman Gallery", 40.7165, -74.0035, "Tribeca",      "Major"),
    "alpine_rech":     ("Almine Rech",           40.7180, -73.9980, "Tribeca",       "Mega"),
    "perrotin":        ("Perrotin",              40.7180, -73.9890, "LES",           "Mega"),
    "sean_kelly":      ("Sean Kelly Gallery",    40.7120, -73.9930, "LES",           "Major"),
    "tanya_bonakdar":  ("Tanya Bonakdar Gallery",40.7150, -73.9930, "LES",           "Major"),
    "lisson_tribeca":  ("Lisson Gallery (504 W 24th)", 40.7477, -73.9978, "Chelsea", "Major"),

    # ── LES ───────────────────────────────────────────────────────────────
    "sargent_daughters": ("Sargent's Daughters", 40.7228, -73.9850, "Lower East Side","Boutique"),

    # ── UES ───────────────────────────────────────────────────────────────
    "matthew_marks":   ("Matthew Marks Gallery", 40.7468, -73.9970, "Chelsea",       "Mega"),
    "gagosian_ues":    ("Gagosian (980 Madison)",40.7710, -73.9650, "Upper East Side","Mega"),
    "acquavella":      ("Acquavella Galleries",  40.7690, -73.9670, "Upper East Side","Mega"),
    "skarstedt_ues":   ("Skarstedt (20 E 79th)", 40.7690, -73.9660, "Upper East Side","Major"),
    "ska_25th":        ("Skarstedt (547 W 25th)",40.7495, -74.0040, "Chelsea",       "Major"),
    "lgdr":            ("LGDR",                  40.7540, -73.9780, "UES (64th St)", "Major"),
    "white_cube":      ("White Cube New York",   40.7730, -73.9710, "Upper East Side","Mega"),

    # ── Midtown ───────────────────────────────────────────────────────────
    "marian_goodman_bway": ("Marian Goodman (385 Broadway)", 40.7210, -74.0012, "SoHo", "Mega"),

    # ── Closed / dormant (flagged, no active pin) ─────────────────────────
    "mnuchin":         ("Mnuchin Gallery [CLOSED]", 40.7680, -73.9630, "UES (closed)", "Closed"),
    "twelve_chairs":   ("Twelve Chairs [CLOSED]", 40.7430, -74.0050, "Chelsea (closed)","Closed"),
    "kasmin":          ("Kasmin Gallery",        40.7468, -73.9970, "Chelsea",       "Status Unknown"),
    "matthew_marks_2": ("Matthew Marks (523 W 24th)", 40.7489, -73.9995, "Chelsea",   "Mega"),
}


# ── Advisory contacts (from The Advisory Path doc) ──────────────────────

ADVISORY_CONTACTS = {
    "christies_exec": [
        {"name": "Bonnie Brennan", "title": "CEO, Christie's", "since": "Feb 1, 2025",
         "note": "Runs a $6.2B house. Map — not a contact.",
         "url": "https://press.christies.com/christies-announces-changes-to-executive-leadership-effective-february-1-2025/"},
        {"name": "François-Henri Pinault", "title": "Chairman & Director, Christie's International PLC",
         "note": "Board level — know the name, don't email.",
         "url": "https://press.christies.com/christies-announces-appointments-to-the-board-of-christies-international-plc/"},
        {"name": "Julien Pradels", "title": "President of the Americas",
         "note": "Seen at Christie's Art+Tech Summit 2026.",
         "url": "https://www.christies.com/en/events/art-tech-summit-2026-new-york/programme"},
        {"name": "Tash Perrin", "title": "Deputy Chairman, Christie's International",
         "note": "Guided Peggy & David Rockefeller collection sale: $106.9M (2018).",
         "url": "https://www.christies.com/en/services/valuations-appraisals-and-professional-advisor-services/contact"},
    ],
    "christies_20_21": [
        {"name": "Alex Rotter", "title": "Chairman, 20/21 Art Departments",
         "note": "Previously Worldwide Co-Head of Contemporary Art at Sotheby's 2008-2016.",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Sara Friedlander", "title": "Deputy Chairman, Post-War & Contemporary Art, NY",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Johanna Flaum", "title": "Vice Chairman, 20th and 21st Century Art, Americas",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Ana Maria Celis", "title": "SVP, Senior Specialist; Head of Department",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Emily Kaplan", "title": "SVP, Senior Specialist; Co-Head, 20th Century Evening Sale",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Isabella Lauria", "title": "SVP, Specialist; Head, 21st Century Evening Sale",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Kathryn Widing", "title": "SVP, Senior Specialist; Head, 21st Century Evening Sale",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Rachael White Young", "title": "SVP, Senior Specialist; Head of Core Market Sales, NY",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Joanna Szymkowiak", "title": "VP, Senior Specialist; Head of Private Sales",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Michael Baptist", "title": "VP, Specialist; Co-Head, Day Sale NY",
         "note": "APPROACHABLE — Day Sale is the accessible end; staff are most approachable specialists.",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Allison Immergut", "title": "VP, Specialist; Co-Head, Day Sale NY",
         "note": "APPROACHABLE — same as Baptist.",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
        {"name": "Alex Marshall", "title": "Specialist (ex-Metro Pictures sales director)",
         "note": "Gallery → advisory → Christie's. Reverse path works too.",
         "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
    ],
    "christies_trusts": [
        {"name": "Joanna Ostrem", "title": "Managing Director, Head of Business Development, Trusts & Estates",
         "note": "Undervalued door. Covers entire T&E community for Americas. Dept that produced Bonnie Brennan.",
         "url": "https://www.christies.com/en/services/valuations-appraisals-and-professional-advisor-services/contact"},
        {"name": "Sarah Vandeweerdt", "title": "Co-Head, Trusts, Estates & Appraisals",
         "url": "https://www.christies.com/en/services/valuations-appraisals-and-professional-advisor-services/contact"},
        {"name": "Caroline Kelly", "title": "SVP & Senior Account Manager, T&E; covers Midwest, Dallas, NY tri-state",
         "url": "https://www.christies.com/en/services/valuations-appraisals-and-professional-advisor-services/contact"},
    ],
    "sothebys_exec": [
        {"name": "Charles F. Stewart", "title": "CEO, Sotheby's",
         "note": "Map only.",
         "url": "https://www.sothebys.com/en/about/executive-management"},
        {"name": "Madeline Lissner", "title": "EVP, Global Fine Art Division & Major Collections",
         "note": "Most senior woman on fine art side. Map only.",
         "url": "https://www.sothebys.com/en/about/executive-management"},
        {"name": "Nathan Drahi", "title": "EVP, Global Head of Business Development",
         "url": "https://www.sothebys.com/en/about/executive-management"},
        {"name": "Joshua Pullan", "title": "EVP, Global Luxury Division",
         "url": "https://www.sothebys.com/en/about/executive-management"},
    ],
    "sothebys_fine_art": [
        {"name": "David Galperin", "title": "Vice Chairman, Head of Contemporary Art",
         "note": "Joined 2013 → Head of Contemporary by 2017 → Co-Head Marquee Sales by 2021.",
         "url": "https://www.sothebys.com/en/about/team/david-galperin"},
        {"name": "Julian Dawes", "title": "Vice Chairman, Head of Impressionist & Modern Art, NY",
         "url": "https://www.sothebys.com/en/about/our-team"},
        {"name": "Jodi Pollack", "title": "Chairman & Co-Worldwide Head, 20th Century Design; Chairman, Major Collections",
         "note": "$800M+ in sales since 2020.",
         "url": "https://www.sothebys.com/en/about/team/jodi-pollack"},
        {"name": "Ashkan Baghestani", "title": "Head of Contemporary Art Day Sale, NY",
         "note": "APPROACHABLE — Day Sale.",
         "url": "https://www.sothebys.com/en/about/our-team"},
        {"name": "Cassandra Hatton", "title": "Vice Chairman, Worldwide Head, Science & Natural History, NY",
         "url": "https://www.sothebys.com/en/about/our-team"},
    ],
    "sothebys_private_sales": [
        {"name": "Courtney Kremers", "title": "Vice Chairman, Head of Private Sales",
         "note": "Template career: B.A. Art History Duke → Christie's Education Master's → Mugrabi Collection → Kim Heirston Advisory → Sotheby's 2013 → Vice Chairman.",
         "url": "https://www.sothebys.com/en/about/team/courtney-kremers"},
        {"name": "Nick Deimel", "title": "Head of Private Sales, Modern Art",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Charlotte Van Dercook", "title": "Head of Marquee Private Sales; Specialist, Contemporary",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Mercedes Lámbarri Altamira", "title": "Head of Mid-Market Private Sales",
         "note": "APPROACHABLE — mid-market is where a beginner can plausibly contribute. Highest volume.",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Max Moore", "title": "Head of Sealed & Senior Private Sales Specialist",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Kathleen Hart", "title": "Specialist, Contemporary Art",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Carolyn Wilks", "title": "Private Sales Strategist",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Blair Moll", "title": "Specialist",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Amanda Ross", "title": "Associate Specialist, Private Sales",
         "note": "APPROACHABLE — junior end of advisory-adjacent team. Published email.",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Caroline Callender", "title": "Associate Cataloguer, Private Sales",
         "note": "APPROACHABLE — only person on private sales roster with published email.",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
    ],
    "advisor_fee_stack": [
        {"service": "Sourcing and acquiring", "fee": "~10% of client spend, lower on very expensive works"},
        {"service": "Focused session / second opinion", "fee": "$150–$500 per hour"},
        {"service": "Art fair or day of gallery/studio visits", "fee": "$1,000–$3,500 per day, travel billed separately"},
        {"service": "Collection review or audit", "fee": "$1,500–$5,000"},
        {"service": "Selling or deaccessioning", "fee": "Percentage of proceeds, usually lower than acquisition rate"},
        {"service": "Corporate / hotel / hospitality art program", "fee": "10–25% of total art budget"},
        {"service": "Retainer model", "fee": "Retainer + reduced percentage on purchases (Beaumont Nathan, Patti Wong)"},
    ],
    "career_templates": [
        {"name": "Courtney Kremers", "path": "B.A. Art History (Duke) → Christie's Education Master's → Mugrabi Art Collection → Director, Kim Heirston Art Advisory → Sotheby's 2013 → Head of Sale Contemporary Curated → Head of Private Sales Americas → Vice Chairman, Head of Private Sales",
         "years": "~13 years joining to vice chair", "url": "https://www.sothebys.com/en/about/team/courtney-kremers"},
        {"name": "David Galperin", "path": "Joined Sotheby's 2013 → Head of Contemporary Art Evening Sales 2017 → Head of Contemporary Art Americas + Co-Head Marquee Sales 2021",
         "years": "8 years joining to running Americas", "url": "https://www.sothebys.com/en/about/team/david-galperin"},
        {"name": "Alex Marshall (reverse)", "path": "Sales director, Metro Pictures → private art advisory 2008 → built large-scale collections 11 years → Christie's 2019",
         "note": "Gallery → advisory → auction house also works.", "url": "https://www.christies.com/departments/post-war-contemporary-art-74-1.aspx?pagesection=contacts&lid=1&sc_lang=en"},
    ],
    "formal_routes": [
        {"program": "Christie's Graduate Training Programme", "duration": "12 months, 3–4 rotational placements",
         "locations": "London, New York, Hong Kong", "ny_seats": "typically 2 graduates per year in NY",
         "process": "Online application → CV screening → video interviews → situational judgement → assessment centre → final interviews",
         "next_open": "2027 applications open October 2026",
         "visas": "Cannot sponsor visas for London or NY programme",
         "contact": "graduateprogramme@christies.com",
         "url": "https://www.christies.com/en/about/careers/graduate-training"},
        {"program": "Sotheby's Internship Programme", "duration": "14 weeks, paid",
         "requirement": "Graduate-level or equivalent",
         "url": "https://www.sothebys.com/en/london-internship-programme-at-sothebys"},
        {"program": "Sotheby's Institute — Online Certificate in Art Business", "duration": "Foundations, Advanced Topics, Final Project",
         "credential": "Verified Certificate of Completion + IACET badge + 13 CEUs",
         "tuition_waiver": "$2,000 partial tuition waiver toward Sotheby's Institute master's later",
         "degree_req": "No applicant degree requirement stated",
         "url": "https://sothebysinstitute.com/courses/certificates/online-certificate-in-art-business/"},
    ],
    "key_statistic": [
        {"stat": "70%", "meaning": "Of contemporary and Modern sales at major auction houses runs through advisers (Josh Baer estimate)",
         "source": "https://www.theartnewspaper.com/2025/09/17/meet-the-former-auction-heavyweights-remodelling-the-art-market-as-advisers"},
        {"stat": "$85M", "meaning": "Sotheby's paid in 2016 to acquire Art Agency, Partners — co-founded 2 years earlier by Amy Cappellazzo, Allan Schwartzman, Adam Chinn",
         "source": "https://www.theartnewspaper.com/2025/09/17/meet-the-former-auction-heavyweights-remodelling-the-art-market-as-advisers"},
        {"stat": "$1.6B", "meaning": "Value of Paul Allen collection secured by Bonnie Brennan as president of Christie's Americas",
         "source": "https://en.wikipedia.org/wiki/Bonnie_Brennan"},
    ],
    "honest_sequence": "Gallery floor, 2–3 years, on commission, learning to close → lateral into auction house on strength of sales record rather than diploma → specialist track or private sales → advisory, or own book, in thirties. That is the path Cappellazzo, Cromwell, Lampley, Mitchell-Innes, Kremers all walked. None in under a decade.",
}


# ── Approachable contacts (from Who to Actually Know doc) ────────────────

APPROACHABLE_CONTACTS = {
    "tier1_mentors": [
        {"name": "Ebony L. Haynes", "gallery": "David Zwirner",
         "role": "Global Head of Curatorial Projects; founded 52 Walker; established Black Art Sessions (2020)",
         "approachability": "NADA mentor — publicly declared willingness to spend unpaid hours helping someone in the trade. Walk into Zwirner. Go to openings. Ask questions in person.",
         "url": "https://www.newartdealers.org/programs/nada-member-mentorship-program",
         "tier": "Tier 1 — NADA Mentor"},
        {"name": "Lauren Marinaro", "gallery": "Marinaro",
         "approachability": "NADA mentor. Organized the mentorship program.",
         "url": "https://www.newartdealers.org/programs/nada-member-mentorship-program",
         "tier": "Tier 1 — NADA Mentor"},
        {"name": "David Fierman", "gallery": "FIERMAN & Open Studio",
         "approachability": "NADA mentor.",
         "url": "https://www.newartdealers.org/programs/nada-member-mentorship-program",
         "tier": "Tier 1 — NADA Mentor"},
        {"name": "Jayne Johnson", "gallery": "JDJ",
         "approachability": "NADA mentor.",
         "url": "https://www.newartdealers.org/programs/nada-member-mentorship-program",
         "tier": "Tier 1 — NADA Mentor"},
        {"name": "Zach Feuer", "gallery": "Zach Feuer Gallery",
         "approachability": "NADA mentor.",
         "url": "https://www.newartdealers.org/programs/nada-member-mentorship-program",
         "tier": "Tier 1 — NADA Mentor"},
        {"name": "Rebekah Chozick", "gallery": "Chozick Family Art Gallery",
         "approachability": "NADA mentor.",
         "url": "https://www.newartdealers.org/programs/nada-member-mentorship-program",
         "tier": "Tier 1 — NADA Mentor"},
    ],
    "tier1_note": "NADA mentorship program is for NADA members only (pairs galleries with galleries). Avery cannot enroll as a mentee. Use the list differently: these 6 people have publicly declared they're willing to spend unpaid hours helping someone in the trade. Walk into their galleries. Go to their openings. Ask them questions in person. Ebony L. Haynes is the one to study hardest.",
    "tier2_peers": [
        {"name": "Sabel Diefendorf", "role": "Sales Associate, 56 Henry",
         "why": "Three-person gallery staff page — sales at a small serious gallery.",
         "url": "https://56henry.nyc/gallery/"},
        {"name": "Era Myrtezaj", "role": "Gallery Director, 56 Henry",
         "why": "Runs a two-space downtown gallery, tiny team.",
         "url": "https://56henry.nyc/gallery/"},
        {"name": "Caroline Callender", "role": "Associate Cataloguer, Private Sales, Sotheby's",
         "why": "Only person on Sotheby's private sales roster whose email is published.",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Amanda Ross", "role": "Associate Specialist, Private Sales, Sotheby's",
         "why": "Junior end of the most advisory-adjacent team in the building.",
         "url": "https://www.sothebys.com/en/buy-sell/private-sales"},
        {"name": "Katie Morse", "role": "Registrar, Powell Fine Art Advisory",
         "why": "Publicly gave beginner advice in the firm's newsletter.",
         "url": "https://powellfineartadvisory.substack.com/p/so-you-want-to-be-an-art-advisor"},
        {"name": "Manuela Uribe", "role": "Framing Manager, Powell Fine Art Advisory",
         "why": "Described her whole path from student job to advisory.",
         "url": "https://powellfineartadvisory.substack.com/p/so-you-want-to-be-an-art-advisor"},
        {"name": "Augusto Arbizo", "role": "Schwartzman&",
         "why": "Stated focus: gallery relationships and sourcing for advisory team, actively connecting with dealers and artists through gallery visits. Professionally obligated to be reachable.",
         "url": "https://www.schwartzmanand.com/team"},
    ],
    "tier2_note": "These are the highest-value contacts and the ones everybody ignores in favor of emailing chairmen. They answer their own messages, remember being new, and in 10 years they run departments.",
    "tier3_owners": [
        {"name": "Ellie Rines", "gallery": "56 Henry",
         "note": "Was 25 when she opened her first gallery at 55 Gansevoort in 2013. Moved to Tribeca September 2026. Opening a new Tribeca space right now — the single best-timed approach on this list.",
         "url": "https://www.theartnewspaper.com/2026/09/15/ellie-rines-opens-new-tribeca-gallery-56-henry"},
        {"name": "Allegra LaViola", "gallery": "Sargent's Daughters",
         "note": "Was 27 when she opened her first eponymous gallery on East Broadway in September 2008, just as the global financial crisis roiled the art market. Founded Sargent's Daughters 2014 on the LES. Read the Observer interview before walking in.",
         "url": "https://observer.com/2026/02/interview-art-dealer-allegra-laviola-sargents-daughters-gallery/"},
        {"name": "Susan Eley Eley", "gallery": "Susan Eley Fine Art",
         "note": "Founded 2006 on the UWS. Personal email published on gallery About page: susie@susaneleyfineart.com. NYC Chapter Liaison for AWAD. If Avery sends one email this month, send it to her.",
         "url": "https://susaneleyfineart.com/About/Gallery"},
    ],
    "tier4_educators": [
        {"name": "Emily McElwreath", "role": "Host, The Art Career podcast; advisor and curator",
         "note": "18+ years in the field. The show is a space breaking barriers by letting you sit in on candid conversations with leaders in visual arts. Listen to 50 episodes, then write to her about a specific one.",
         "url": "https://www.theartcareer.com/about"},
        {"name": "Maria Brito", "role": "Independent NY advisor",
         "note": "Moved more than $150M in blue-chip and emerging works. Free weekly newsletter The Groove reaches 32,000+ readers. Built a career by dismantling the myth that collecting is an insiders' game.",
         "url": "https://www.mariabrito.com/subscribe"},
        {"name": "Josh Baer", "role": "Advisor; founder, The Baer Faxt (since 1994)",
         "note": "Over $500M of transactions completed. Source of the 70%-through-advisers estimate. Runs NoReserve (free sample of premium newsletter). Start with NoReserve.",
         "url": "https://noreserve.thebaerfaxt.com/"},
        {"name": "Annelien Bruins", "role": "CEO, Tang Art Advisory; teaches Art Advisory 101 via One Art Nation",
         "note": "Paid instructor with a named business is a person who takes questions.",
         "url": "https://www.oneartnation.com/courses/art-advisory-101/"},
        {"name": "Powell Fine Art Advisory team", "role": "Published 'So You Want to Be an Art Advisor?'",
         "note": "Also runs Cypress, an in-house art handling company. Art handling is a genuine no-degree door into an advisory business.",
         "url": "https://powellfineartadvisory.substack.com/p/so-you-want-to-be-an-art-advisor"},
    ],
    "tier5_press": [
        {"name": "Gabi Vidal Irizarry", "role": "Writer, Artnet Wet Paint gossip column",
         "note": "Current writer after Annie Armstrong stepped down end of 2025. Gossip columnist knows more people in NY art world than any chairman.",
         "url": "https://news.artnet.com/multimedia/art-world-restaurants-annie-armstrong-2792532"},
        {"name": "Annie Armstrong", "role": "Former Wet Paint columnist (198 columns, 4+ years)",
         "note": "Still a NY arts journalist. Her column footer: 'If you have a tip, email Annie Armstrong at aarmstrong@artnet.com'. Being a useful, discreet, never-burn-anyone source to one arts reporter is worth more than any networking event.",
         "url": "https://news.artnet.com/multimedia/art-world-restaurants-annie-armstrong-2792532"},
    ],
    "communities": [
        {"org": "NADA Individual Membership", "cost": "$200 annually", "who": "Galleries, nonprofits, advisors, curators, other professionals working with contemporary art. No degree requirement stated.",
         "status": "Join now. Lowest-friction serious credential available to her.",
         "url": "https://www.newartdealers.org/join/individual-membership"},
        {"org": "ArtTable", "cost": "Affiliate $85; Professional sliding scale from $125 by income band",
         "who": "Women and nonbinary professionals in the visual arts. 1,200+ members, founded 1981.",
         "status": "Affiliate requires 2-5 years professional experience. Not yet — but see CareerRoundTable and Fellowship.",
         "url": "https://www.arttable.org/"},
        {"org": "AWAD (Association of Women Art Dealers)", "cost": "Not published",
         "who": "International trade network est. 2009. Members at all stages of career as business owners. NYC and London hubs.",
         "status": "Contact Susan Eley, the NYC Chapter Liaison.",
         "url": "https://womenartdealers.org/about/"},
        {"org": "APAA (Association of Professional Art Advisors)", "cost": "By invitation only",
         "who": "Requires 5 years as principal of an art advisory firm + advanced education in fine art study.",
         "status": "A 2035 goal, not a 2026 one.",
         "url": "https://www.artadvisors.org/get-involved"},
        {"org": "ArtTable CareerRoundTable", "cost": "Membership benefit",
         "who": "Emerging leaders in visual arts. Met with leading women and nonbinary professionals. Influenced career paths of 2,000+ students and emerging professionals.",
         "status": "Available to emerging professionals — use this entry point.",
         "url": "https://www.arttable.org/impact/"},
        {"org": "ArtTable Fellowship Program", "cost": "5–8 week summer placement",
         "who": "Students and early-career professionals transitioning from academic study OR ALTERNATIVE CAREER PATHS into arts sector roles. Paired with ArtTable member mentors.",
         "status": "EXPLICIT DOOR for someone without a degree. 'Alternative career paths' is the key phrase. Apply.",
         "url": "https://www.arttable.org/fellows/"},
    ],
    "approach_rules": [
        "Never ask for a job, advice, or coffee in a first message. All three ask the recipient to spend something. Ask a question only they can answer, about something they made.",
        "One specific artwork beats any amount of enthusiasm about the gallery.",
        "Never attach a résumé to a first email.",
        "Follow up once, after fourteen days, with new information — never with 'just bumping this'.",
        "Show up to the openings of everyone on this list. Openings are free, public, and the entire business is conducted at them.",
        "Ask about the artist, never about the money, until someone else raises the money.",
        "When someone helps her, report back what she did with it. That single habit converts one favor into a mentor.",
        "She has no degree. Say it plainly and early — 'I'm trying to learn this properly.' Concealing it makes it a liability; stating it makes it a reason someone wants to help.",
    ],
    "what_not_to_do": [
        "Don't email Alex Rotter, Madeline Lissner, Charles Stewart, Bonnie Brennan, Courtney Kremers or David Galperin. Response rate is zero and the names don't get a second attempt. They belong on the wall as a map, not in a sent folder.",
        "Don't pay for a networking event that sells access. The openings are free.",
        "Don't join APAA's orbit yet. Five years as a principal is the floor.",
    ],
}


# ── Cluster efficiency routes ────────────────────────────────────────────

ROUTES = [
    {
        "name": "Chelsea — highest conversation density (Tue–Sat, 10–6)",
        "description": "8 galleries, ~0.8 miles, one walkable afternoon. Miles McEnery's 4 spaces in one block are the density highlight.",
        "stops": [
            {"gallery_id": "gagosian", "note": "Brice Marden survey — the credential show"},
            {"gallery_id": "pace", "note": "Benglis, Shechet, Dyson — 3 shows Sept 11–Oct 24"},
            {"gallery_id": "petzel", "note": "Bleckner + Emily Mae Smith"},
            {"gallery_id": "taglialatella", "note": "shelby & sandy: layers + Tyler Shields signing Sept 15 — sales event"},
            {"gallery_id": "gladstone", "note": "Yaeger Agita from Sept 16 + Rondinone Waterfall"},
            {"gallery_id": "lisson", "note": "Sugimoto Unnatural Habitat to Oct 24"},
            {"gallery_id": "hauser_wirth", "note": "Arp + Rouy REPRISE to Oct 17 + Lee Bul to Oct 24 — two spaces"},
            {"gallery_id": "miles_mcenery", "note": "4 concurrent shows Sept 10–Oct 31: Caporael, Dowell, Greenfield-Sanders, Williams — one block, 4 conversations"},
        ],
        "cluster_note": "Miles McEnery block (511/515/520/525 W 22nd + 525 W 21st): 4 galleries in 2 blocks. Optimize for conversations-per-mile.",
        "hours_note": "All open 10–6 Tue–Sat. White Cube stays open Thurs to 9pm — add if Thursday.",
    },
    {
        "name": "SoHo / Tribeca / LES — second afternoon",
        "description": "EDEN + Sorrel Sky + Zwirner Tribeca + Almine Rech + Jack Shainman + Perrotin. Mix of sales floors and blue-chip study.",
        "stops": [
            {"gallery_id": "eden", "note": "Alec Monopoly Flying to a Happy Place — jet parts. Base + commission gallery."},
            {"gallery_id": "sorrel_sky", "note": "David Yarrow exclusive NYC rep. $90–130K stated. BA required — walk in anyway."},
            {"gallery_id": "david_zwirner", "note": "Nate Lowman, Scott Kahn, Emma McIntyre, Louis Fratino, Thomas Ruff"},
            {"gallery_id": "alpine_rech", "note": "Zio Ziegler The Tesseract Sept 16–Oct 21"},
            {"gallery_id": "jack_shainman", "note": "Gordon Parks Voices in the Mirror to Nov 6 — speak about with real feeling."},
            {"gallery_id": "perrotin", "note": "130 Orchard St. Nikki Maloof, Aryo Toh Djojo. Daniel Arsham earlier in 2026."},
        ],
    },
    {
        "name": "UES / Midtown — secondary + auction corridor (half day)",
        "description": "Study the blue chip. Not for first income — for credential and taste. Add Sotheby's Salon if relevant.",
        "stops": [
            {"gallery_id": "gagosian_ues", "note": "980 Madison. Marden survey continues. 17 locations, 20,000+ m²."},
            {"gallery_id": "acquavella", "note": "18 E 79th. Miró/Riopelle in Palm Beach. Nicole Wittenberg, Tom Sachs, Damian Loeb."},
            {"gallery_id": "skarstedt_ues", "note": "20 E 79th. Cristina BanBan Jaque Mate — rising-price story of this cycle."},
            {"gallery_id": "white_cube", "note": "1002 Madison. Danh Vo Sept 10–Oct 16. Open Thurs to 9pm — networking window."},
            {"gallery_id": "salon_94", "note": "3 E 89th. Anthea Hamilton Comfort Zones. Opposite the Guggenheim — pair the two."},
        ],
    },
    {
        "name": "57th Street — Findlay day trip",
        "description": "Single-gallery targeted trip. Findlay is the #1 target: live comp, education only preferred, 57th St foot traffic.",
        "stops": [
            {"gallery_id": "findlay", "note": "#1 target. 'substantial commissions and bonus plans.' 100+ artists and estates. Education preferred not required."},
        ],
    },
    {
        "name": "56 Henry — Ellie Rines approach (SoHo, when in the area)",
        "description": "56 Henry moved to Tribeca in September 2026. Ellie Rines is opening a new space — the single best-timed approach on the approachable list.",
        "stops": [
            {"gallery_id": "56_henry", "note": "Ellie Rines founded 56 Henry at 25. Moving to Tribeca Sept 2026. Visit — a gallery mid-move needs hands."},
        ],
    },
    {
        "name": "Sotheby's — private sales entry point (York Ave)",
        "description": "Not for now — but the map. Mid-Market Private Sales is the accessible end. Caroline Callender (Associate Cataloguer, published email) and Amanda Ross (Associate Specialist) are the approach points. Mercedes Lámbarri Altamira runs Mid-Market.",
        "stops": [
            {"gallery_id": "sothebys_salon", "note": "Sotheby's Salon. NYFA posting: $65K–$75K base + discretionary bonus. BA required. 2+ yrs luxury retail. Target in 2 years."},
        ],
    },
]


# ── Field capture schema (20-second visit capture) ──────────────────────

FIELD_CAPTURE = {
    "schema_version": "1.0",
    "target_time_seconds": 20,
    "fields": [
        {"key": "traffic", "label": "Walk-in traffic", "type": "1–5",
         "options": ["1 — empty", "2 — few", "3 — moderate", "4 — good", "5 — packed"]},
        {"key": "staff_seen", "label": "Staff seen", "type": "number",
         "hint": "Count heads, not titles"},
        {"key": "spoken_to", "label": "Spoke to anyone?", "type": "y/n"},
        {"key": "name_captured", "label": "Name captured (optional)", "type": "text",
         "hint": "First name is fine. Don't pressure."},
        {"key": "show_on_walls", "label": "Show on walls matches CURATOR's current exhibition?", "type": "y/n/conflict",
         "hint": "If no, write what's actually on the walls — that's a correction observation"},
        {"key": "comp_discussed", "label": "Commission discussed?", "type": "y/n",
         "hint": "Did they bring it up? Did you ask the commission question?"},
        {"key": "commission_question_asked", "label": "Did you ask 'Commission on what — retail, gross profit, or net?'", "type": "y/n",
         "hint": "Requirement: ask in every interview"},
        {"key": "felt", "label": "How it felt", "type": "1–5",
         "options": ["1 — terrible", "2 — awkward", "3 — neutral", "4 — good", "5 — great"]},
        {"key": "opening", "label": "Was this an opening/reception?", "type": "y/n",
         "hint": "Thursday evenings are opening receptions — free wine, actual collectors"},
        {"key": "notes", "label": "One line", "type": "text (max 280 chars)",
         "hint": "What you noticed that no one else would"},
    ],
    "post_visit_prompts": [
        "What went well?",
        "What would you do differently?",
        "Did you ask the commission question?",
        "One thing you learned about this gallery that no one else would know",
        "Was there a person you want to follow up with? Name them.",
    ],
}


# ── Export ────────────────────────────────────────────────────────────────

def gallery_state_from_spine(gid: str) -> dict:
    """Get current certification state + value for a gallery from the spine."""
    eng = CertificationEngine(DEFAULT_DB)
    conn = sqlite3.connect(DEFAULT_DB)

    # Trading status
    cursor = conn.execute(
        "SELECT value, state, source_tier, source_url FROM facts WHERE gallery_id=? AND fact_class='trading_status' ORDER BY created_at DESC LIMIT 1",
        (gid,),
    )
    ts_row = cursor.fetchone()

    # Comp language
    cursor = conn.execute(
        "SELECT value, state, source_tier FROM facts WHERE gallery_id=? AND fact_class='comp_language' ORDER BY created_at DESC LIMIT 1",
        (gid,),
    )
    comp_row = cursor.fetchone()

    # Current exhibition
    cursor = conn.execute(
        "SELECT value, state FROM facts WHERE gallery_id=? AND fact_class='current_exhibition' ORDER BY created_at DESC LIMIT 1",
        (gid,),
    )
    exh_row = cursor.fetchone()

    conn.close()

    return {
        "trading_value": ts_row[0] if ts_row else None,
        "trading_state": ts_row[1] if ts_row else None,
        "trading_tier": ts_row[2] if ts_row else None,
        "comp_value": comp_row[0] if comp_row else None,
        "comp_state": comp_row[1] if comp_row else None,
        "exhibition_value": exh_row[0] if exh_row else None,
        "exhibition_state": exh_row[1] if exh_row else None,
    }


def main():
    # Build gallery list
    galleries = []
    seen = set()
    for gid, (name, lat, lng, hood, tier) in GALLERY_COORDS.items():
        if gid in seen:
            continue
        seen.add(gid)
        state = gallery_state_from_spine(gid)
        gallery = {
            "gallery_id": gid,
            "name": name,
            "neighborhood": hood,
            "tier": tier,
            "address_display": name,  # will be enriched with full address below
            "lat": lat,
            "lng": lng,
            "certification": state,
            "closed": tier == "Closed",
            "status_unknown": tier == "Status Unknown",
        }
        galleries.append(gallery)

    # Enrich with addresses from research
    ADDRESSES = {
        "gagosian": "522 W 21st St, New York, NY 10011",
        "pace": "540 W 25th St, New York, NY 10001",
        "petzel": "520 W 25th St, New York, NY 10001",
        "taglialatella": "229 10th Ave, New York, NY 10011",
        "gladstone": "21 W 21st St, New York, NY 10011",
        "lisson": "504 W 24th St, New York, NY 10011",
        "hauser_wirth": "526 W 22nd St, New York, NY 10011",
        "hauser_wirth_2": "32 W 18th St, New York, NY 10011",
        "casey_kaplan": "121 W 27th St, New York, NY 10001",
        "miles_mcenery": "515 W 22nd St, New York, NY 10011 (also 511, 520, 525 W 22nd/21st)",
        "anton_kern": "1001 9th Ave, New York, NY 10019",
        "gallery_303": "542 W 21st St, New York, NY 10011",
        "jeffrey_deitch": "18 Wooster St, New York, NY 10013",
        "ippodo": "612 W 22nd St, New York, NY 10011",
        "paula_cooper": "521 W 21st St, New York, NY 10011",
        "luhring_augustine": "251 Bowery, New York, NY 10002 (note: may have moved to Double 07, 251 Bowery — verify)",
        "bortolami": "55 Walker St, New York, NY 10013",
        "salon_94": "3 E 89th St, New York, NY 10028",
        "findlay": "32 E 57th St, New York, NY 10022",
        "avant": "20 Hudson Yards, Regis Unit 432, Level 4, New York, NY 10001",
        "lik": "689 Fifth Ave, New York, NY 10019 (new space)",
        "eden": "500 Washington St, New York, NY 10013 (SoHo) + Fifth Ave location",
        "sorrel_sky": "419 W Broadway, New York, NY 10012",
        "marian_goodman": "385 Broadway, New York, NY 10013",
        "56_henry": "170 Suffolk St, New York, NY 10002 (Tribeca, moved Sept 2026)",
        "david_zwirner": "345 Greene St, New York, NY 10013",
        "jack_shainman": "46 Lafayette St, New York, NY 10013",
        "alpine_rech": "100 Franklin St, New York, NY 10013",
        "perrotin": "130 Orchard St, New York, NY 10012",
        "sean_kelly": "470 6th Ave, New York, NY 10011",
        "tanya_bonakdar": "527 Bowery, New York, NY 10012",
        "sargent_daughters": "146 W 17th St? verify — actually 138 E 13th St area. Using LES address.",
        "matthew_marks": "522 W 22nd St, New York, NY 10011",
        "gagosian_ues": "980 Madison Ave, New York, NY 10021",
        "acquavella": "18 E 79th St, New York, NY 10021",
        "skarstedt_ues": "20 E 79th St, New York, NY 10075",
        "ska_25th": "547 W 25th St, New York, NY 10001",
        "lgdr": "19 E 64th St, New York, NY 10022",
        "white_cube": "1002 Madison Ave, New York, NY 10021",
        "mnuchin": "45 E 78th St, New York, NY 10075 (closed Feb 2026)",
        "twelve_chairs": "Chelsea, New York, NY (closed — commission-only)",
        "kasmin": "522 W 22nd St area, New York, NY 10011 (status unknown — site surfaces only archive pages)",
        "matthew_marks_2": "523 W 24th St, New York, NY 10010",
    }

    for g in galleries:
        if g["gallery_id"] in ADDRESSES:
            g["address"] = ADDRESSES[g["gallery_id"]]

    # Mark closed galleries
    for g in galleries:
        if g["closed"]:
            g["pin_style"] = "struck"
            g["map_visible"] = False  # don't show as active pin
        elif g["status_unknown"]:
            g["pin_style"] = "hollow"
        else:
            ts = g["certification"]["trading_state"]
            exh = g["certification"]["exhibition_state"]
            has_opening = exh == "CERTIFIED"
            if ts == "CERTIFIED" and has_opening:
                g["pin_style"] = "solid"
            elif ts == "CERTIFIED":
                g["pin_style"] = "outline"
            elif ts == "READY":
                g["pin_style"] = "hollow"
            elif ts == "DECLARED":
                g["pin_style"] = "grey"
            else:
                g["pin_style"] = "grey"

    data = {
        "generated_at": datetime.now(timezone.utc).isoformat(),
        "galleries": galleries,
        "advisory": ADVISORY_CONTACTS,
        "approachable": APPROACHABLE_CONTACTS,
        "routes": ROUTES,
        "field_capture": FIELD_CAPTURE,
    }

    OUT.write_text(json.dumps(data, ensure_ascii=False, indent=2), encoding="utf-8")
    print(f"Wrote {OUT} — {len(galleries)} galleries + advisory + approachable + routes")
    print(f"  Pin styles: solid={sum(1 for g in galleries if g['pin_style']=='solid')}, "
          f"outline={sum(1 for g in galleries if g['pin_style']=='outline')}, "
          f"hollow={sum(1 for g in galleries if g['pin_style']=='hollow')}, "
          f"grey={sum(1 for g in galleries if g['pin_style']=='grey')}, "
          f"struck={sum(1 for g in galleries if g['pin_style']=='struck')}")


if __name__ == "__main__":
    main()