import { useState, useMemo } from "react"; import { useSQLQuery } from "@motherduck/react-sql-query"; export const REQUIRED_DATABASES = [ { type: 'share', path: 'md:_share/tartan_army/b3b758f2-1eea-407b-ac39-9d49acf87314', alias: 'tartan' } ]; // --- Brand --------------------------------------------------------------- const C = { saltire: "#0065BD", // Scottish flag blue saltireDeep: "#004B8C", navy: "#0A1F3E", cream: "#F8F6EE", paper: "#FFFFFF", ink: "#0F1A2C", text: "#1a1a2e", muted: "#5a6478", line: "#E1DDD0", tartanRed: "#C8102E", gold: "#D9B43A", green: "#1e7a3d", }; const TABS = [ { id: "map", label: "The Map" }, { id: "match", label: "Match Day" }, { id: "week", label: "This Week" }, { id: "takeover", label: "Tartan Takeover" }, ]; const CAT_COLOR = { "Scottish": C.saltire, "Sports Bar": C.green, "Irish": "#15803d", "Italian Cafe": "#a52a2a", "Dive": "#5a6478", "Tourist": "#b45309", "Whisky Bar": "#5a2d8a", }; const CAT_ICON = { "Scottish": "🏴󠁧󠁢󠁳󠁣󠁴󠁿", "Sports Bar": "⚽", "Irish": "🍀", "Italian Cafe": "☕", "Dive": "🍺", "Tourist": "📸", "Whisky Bar": "🥃", }; const EVT_CAT_COLOR = { "World Cup": C.tartanRed, "Tartan Army": C.saltire, "Watch Party": C.saltire, "Transport": C.navy, "Baseball": "#b45309", "Concert": "#5a2d8a", "Comedy": "#b45309", "Parade": C.green, "Tourist": C.muted, }; // --- Map projection ------------------------------------------------------ // Bounding box covers downtown Boston + Cambridge + Somerville + JP + Dorchester const MAP_BOUNDS = { minLat: 42.295, maxLat: 42.405, minLng: -71.145, maxLng: -71.040 }; const MAP_W = 920; const MAP_H = 720; function project(lat, lng) { const x = ((lng - MAP_BOUNDS.minLng) / (MAP_BOUNDS.maxLng - MAP_BOUNDS.minLng)) * MAP_W; const y = MAP_H - ((lat - MAP_BOUNDS.minLat) / (MAP_BOUNDS.maxLat - MAP_BOUNDS.minLat)) * MAP_H; return [x, y]; } // --- Helpers ------------------------------------------------------------- function fmtTime(t) { if (!t) return ""; const s = String(t).slice(0, 5); const [h, m] = s.split(":").map(Number); const ampm = h >= 12 ? "PM" : "AM"; const hh = h === 0 ? 12 : h > 12 ? h - 12 : h; return `${hh}:${m.toString().padStart(2, "0")} ${ampm}`; } // Parse "YYYY-MM-DD" as a local date (avoids UTC shift that bumps dates back a day in EST) function parseLocalDate(d) { if (!d) return null; const s = String(d).slice(0, 10); const [y, m, day] = s.split("-").map(Number); return new Date(y, m - 1, day); } function fmtDate(d) { const dt = parseLocalDate(d); if (!dt) return ""; return dt.toLocaleDateString("en-US", { weekday: "long", month: "long", day: "numeric" }); } function dayShort(d) { const dt = parseLocalDate(d); if (!dt) return ""; return dt.toLocaleDateString("en-US", { weekday: "short" }); } function dayNumber(d) { const dt = parseLocalDate(d); if (!dt) return ""; return dt.getDate(); } const TODAY = parseLocalDate("2026-06-15"); // --- Atomic UI ----------------------------------------------------------- function Chip({ label, active, onClick, color }) { return ( ); } function ScoreBadge({ score }) { const fill = Math.max(0, Math.min(10, score || 0)); return (

{fill}

); } function SaltireBg({ opacity = 1 }) { // The Saltire (Scottish flag): blue field, white diagonal cross return ( ); } function TartanBand({ height = 8 }) { // Decorative tartan stripe return ( ); } function Card({ children, padded = true, accent = null, style = {} }) { return (

{accent &&

}

{children}

); } function SectionLabel({ children, kicker }) { return (

{kicker &&

{kicker}

}

{children}

); } // --- Tab: The Map -------------------------------------------------------- function MapTab({ pubs, selectedPubId, setSelectedPubId, categoryFilter, setCategoryFilter, minScore, setMinScore, showOnlyMatchPubs, setShowOnlyMatchPubs }) { const cats = ["all", "Scottish", "Sports Bar", "Irish", "Italian Cafe", "Dive", "Tourist", "Whisky Bar"]; const filtered = pubs.filter((p) => { if (categoryFilter !== "all" && p.category !== categoryFilter) return false; if (minScore > 0 && (p.scotland_score || 0) < minScore) return false; if (showOnlyMatchPubs && !p.shows_football) return false; return true; }); const selected = pubs.find((p) => p.pub_id === selectedPubId); // Stylized landmarks const landmarks = [ { label: "BOSTON COMMON", lat: 42.3554, lng: -71.0656 }, { label: "FENWAY", lat: 42.3467, lng: -71.0972 }, { label: "FANEUIL HALL", lat: 42.3601, lng: -71.0556 }, { label: "HARVARD SQ", lat: 42.3736, lng: -71.1190 }, { label: "DAVIS SQ", lat: 42.3953, lng: -71.1224 }, { label: "SOUTH STATION", lat: 42.3522, lng: -71.0552 }, { label: "JAMAICA PLAIN", lat: 42.3094, lng: -71.1100 }, ]; // Stylized Charles River: rough curve through Boston/Cambridge // Path encodes [lat, lng] points; project then build SVG path const charlesPts = [ [42.3520, -71.1450], [42.3490, -71.1180], [42.3580, -71.1010], [42.3625, -71.0870], [42.3650, -71.0750], [42.3620, -71.0650], [42.3530, -71.0530], [42.3450, -71.0420], ]; const charlesPath = charlesPts .map(([la, ln], i) => { const [x, y] = project(la, ln); return `${i === 0 ? "M" : "L"} ${x.toFixed(1)} ${y.toFixed(1)}`; }) .join(" "); return (

{/* MAP */}

Where the Scots will be welcome

{cats.map((c) => ( setCategoryFilter(c)} color={CAT_COLOR[c] || C.saltire} /> ))}

MIN SCOTLAND SCORE setMinScore(Number(e.target.value))} style={{ width: 120, accentColor: C.saltire }} /> {minScore}

setShowOnlyMatchPubs(e.target.checked)} style={{ accentColor: C.saltire }} /> Only bars showing matches

{filtered.length} pub{filtered.length === 1 ? "" : "s"} shown

{/* Saltire ribbon at top-left of map */}

{/* SIDEBAR — pub list / detail */}

{selected ? (

{CAT_ICON[selected.category]} {selected.category}

{selected.name}

{selected.address}

{selected.why_go}

Order

{selected.signature_drink}

T Stop

{selected.t_stop}

Football

{selected.shows_football ? "✓ Match nights" : "—"}

Vibe

{selected.vibe}

Open in Google Maps → ) : (

{filtered.length} Pubs

{filtered.map((p) => ( ))}

)}

); } // --- Tab: Match Day ------------------------------------------------------ function MatchDayTab({ fixtures, pubs }) { const big = fixtures.find((f) => f.fixture_id === 2); const hqs = [ { name: "Scotland House @ The Anchor", tag: "OFFICIAL HQ", address: "1 Shipyard Park, Charlestown Navy Yard", blurb: "The official Scotland House. 24-foot screen, every match on, daily Tartan Army activations Jun 12-18 — skills contests, Scottish merch, live entertainment. Tennent's on tap from the 300-keg Boston haul. Free. Walk-ins welcome.", source: "theanchorboston.com", color: C.saltire, }, { name: "The Dubliner", tag: "FIFA FAN FEST-ADJACENT", address: "2 Center Plaza, Government Center", blurb: "Directly across the street from the FIFA Fan Festival on City Hall Plaza. Converted to a full Scottish pub for the tournament. 150 kegs of Tennent's in the first week.", source: "WBUR / Must Love Futbol", color: C.gold, }, { name: "The Haven", tag: "TARTAN ARMY HQ (JP)", address: "2 Perkins St, Jamaica Plain", blurb: "Scottish-owned. Hosted HOMEGROUND@THEHAVEN Jun 12-14 (live bands, whisky tastings, comedy, podcasts). Still the cultural HQ for the duration.", source: "Must Love Futbol", color: C.tartanRed, }, ]; const transport = [ { kind: "MBTA Commuter Rail", headline: "$75–$95 · From South Station", detail: "14 trains per match direct to Gillette. mTicket app only. Match ticket required to purchase. Sells out — book ahead. ~1hr each way.", }, { kind: "Stadium Express Buses", headline: "From major Boston hotels", detail: "Coach service direct to the stadium. Ticket holders only. Routes serve the Route 128 belt.", }, { kind: "Tartan Army School Bus Convoy", headline: "Organized by ATAC", detail: "Coordinated school-bus convoy of Tartan Army fans. Departure points published by Association of Tartan Army Clubs.", }, { kind: "From Providence", headline: "RICC coach service", detail: "Buses from Rhode Island Convention Centre (Providence). 30 min from Gillette, cheaper accommodation if you're based south.", }, ]; const watchOptions = [ { name: "FIFA Fan Festival", where: "Boston City Hall Plaza", detail: "Free with registration. 5,000 capacity. The official option. 15-min walk from South Station.", color: C.saltire, }, { name: "Inman Square Street Party", where: "Cambridge St (Inman, Cambridge)", detail: "Free outdoor screen, food, music. 4–9pm. The neighborhood option.", color: C.green, }, { name: "The Greatest Bar", where: "262 Friend St (near TD Garden)", detail: "4 floors of screens. Boston's official soccer-match HQ. Free.", color: C.gold, }, ]; return (

{/* MAIN COLUMN */}

{/* Match hero */}

WORLD CUP 2026 — GROUP C

{big ? fmtDate(big.match_date) : "Friday, June 19"} · Kickoff {big ? fmtTime(big.kickoff_local) : "6:00 PM"}

🏴󠁧󠁢󠁳󠁣󠁴󠁿 SCOTLAND

🇲🇦 MOROCCO

Gillette Stadium · Foxborough, MA · FIFA renamed it "Boston Stadium" for the tournament

{/* HQs the Tartan Army is meeting at */} Pre-match HQs (already organized)

These aren't suggestions — they're the spots the Tartan Army has actually been running their week from. Sourced to the venues themselves and the press.

{hqs.map((hq) => (

{hq.name}

{hq.tag}

{hq.address}

{hq.blurb}

Source: {hq.source}

))}

{/* Getting to Gillette */} Boston → Foxborough (~22 miles south)

{transport.map((t) => (

{t.kind}

{t.headline}

{t.detail}

))}

Gillette rules: cashless venue, no re-entry, clear bags. FIFA approved sporrans on entry — verified.

{/* SIDE — watch-party alternatives + fixtures */}

Where to watch in town

{watchOptions.map((w) => (

{w.name}

{w.where}

{w.detail}

))}

All three matches

{fixtures.map((f, i) => { const isBig = f.fixture_id === 2; const isDone = !!f.scotland_result; return (

{dayShort(f.match_date)} {dayNumber(f.match_date)} JUN · {fmtTime(f.kickoff_local)}

{f.home_team === "Scotland" ? "🏴󠁧󠁢󠁳󠁣󠁴󠁿" : ""} {f.home_team} v {f.away_team} {f.away_team === "Scotland" ? "🏴󠁧󠁢󠁳󠁣󠁴󠁿" : ""}

{f.venue}

{isDone && (

{f.scotland_result}

)}

{f.notes && (

{f.notes}

)}

); })}

); } // --- Tab: This Week ------------------------------------------------------ function ThisWeekTab({ events }) { const days = useMemo(() => { const map = new Map(); for (const e of events) { const k = String(e.event_date).slice(0, 10); if (!map.has(k)) map.set(k, []); map.get(k).push(e); } return Array.from(map.entries()).map(([date, evts]) => ({ date, events: evts.sort((a, b) => String(a.start_time).localeCompare(String(b.start_time))) })); }, [events]); return (

Boston is your oyster this week. Center of gravity: Friday's Scotland match. Around it: Red Sox at Fenway, a folk-rock concert at the same Fenway, and a parade that loves a kilt.

{days.map((d) => { const dt = parseLocalDate(d.date); const dayName = dt.toLocaleDateString("en-US", { weekday: "long" }); const dayLabel = dt.toLocaleDateString("en-US", { month: "long", day: "numeric" }); const isMatchDay = d.date === "2026-06-19"; const isToday = d.date === "2026-06-15"; return (

{dayNumber(d.date)}

{dayName.slice(0, 3)}

{dayLabel}

{isMatchDay && (

MATCH DAY

)} {isToday && !isMatchDay && (

TODAY

)}

{d.events.map((e) => { const color = EVT_CAT_COLOR[e.category] || C.muted; return (

{fmtTime(e.start_time)}

{e.category}

{e.title}

{e.venue}{e.neighborhood && e.neighborhood !== e.venue ? ` · ${e.neighborhood}` : ""}{e.price_range ? ` · ${e.price_range}` : ""}

{e.why_scots_will_love_it}

); })}

); } // --- Tab: Tartan Takeover (documentary) ---------------------------------- const KIND_COLOR = { "HQ": C.saltire, "Match Hub": C.saltire, "Gathering Point": C.tartanRed, "Documented March": C.tartanRed, "Official Fan Zone": C.gold, "Match-Day Hub": C.navy, "Tourist Takeover": C.green, }; function TartanTakeoverTab({ landmarks, pubs }) { // Build the Jun 14 documented march route: Evans Way Park → Lansdowne St → Fenway const evansWay = landmarks.find((l) => l.landmark_id === 4); const lansdowne = landmarks.find((l) => l.landmark_id === 8); const fenway = landmarks.find((l) => l.landmark_id === 5); const marchPath = (evansWay && lansdowne && fenway) ? (() => { const [x1, y1] = project(evansWay.lat, evansWay.lng); const [x2, y2] = project(lansdowne.lat, lansdowne.lng); const [x3, y3] = project(fenway.lat, fenway.lng); return `M ${x1.toFixed(1)} ${y1.toFixed(1)} L ${x2.toFixed(1)} ${y2.toFixed(1)} L ${x3.toFixed(1)} ${y3.toFixed(1)}`; })() : ""; return (

{/* Left: the landmarks list */}

The Tartan Takeover

Where the army is actually going

Documented spots — what the Tartan Army has already organized, gathered at, or marched through. Sourced to ATAC, the venues themselves, and Boston/Scottish press.

{landmarks.map((lm) => { const color = KIND_COLOR[lm.kind] || C.saltire; return (

{lm.landmark_id}

{lm.name}

{lm.address}

{lm.kind}

{lm.what_happened}

Source: {lm.source} {lm.status}

); })}

{/* Right: the takeover map */}

Documented locations

The dashed red line is the documented Jun 14 march: Evans Way Park (Burns statue) → Lansdowne Street → Fenway Park.

{/* Legend */}

{Object.entries(KIND_COLOR).map(([k, c]) => (

{k}

))}

); } // --- Main ---------------------------------------------------------------- export default function TartanArmyDive() { const [tab, setTab] = useState("map"); const [selectedPubId, setSelectedPubId] = useState(null); const [categoryFilter, setCategoryFilter] = useState("all"); const [minScore, setMinScore] = useState(0); const [showOnlyMatchPubs, setShowOnlyMatchPubs] = useState(false); const pubsQ = useSQLQuery("SELECT * FROM tartan.main.pubs ORDER BY scotland_score DESC, name"); const fixQ = useSQLQuery("SELECT f.*, p.name AS watch_pub_name, p.address AS watch_pub_address FROM tartan.main.fixtures f LEFT JOIN tartan.main.pubs p ON f.watch_party_pub_id = p.pub_id ORDER BY match_date"); const evtQ = useSQLQuery("SELECT * FROM tartan.main.events ORDER BY event_date, start_time"); const landmarksQ = useSQLQuery("SELECT * FROM tartan.main.tartan_landmarks ORDER BY landmark_id"); const pubs = Array.isArray(pubsQ.data) ? pubsQ.data : []; const fixtures = Array.isArray(fixQ.data) ? fixQ.data : []; const events = Array.isArray(evtQ.data) ? evtQ.data : []; const landmarks = Array.isArray(landmarksQ.data) ? landmarksQ.data : []; const loading = pubsQ.isLoading || fixQ.isLoading || evtQ.isLoading || landmarksQ.isLoading; return (

{/* HEADER */}

{/* Saltire diagonal background */}

An unofficial Boston guide · World Cup 2026

The Tartan Army's Boston Playbook

A wee guide for the lads from Edinburgh — built on MotherDuck. Pubs to claim, matches to watch, things to do around Friday's big one at Boston Stadium.

All times Eastern (ET) · Add 5 hours for Edinburgh

{/* TAB STRIP */}

{TABS.map((t) => ( ))}

{/* CONTENT */}

{loading && (

Loading the trail…

)} {!loading && tab === "map" && ( )} {!loading && tab === "match" && } {!loading && tab === "week" && } {!loading && tab === "takeover" && }

{/* FOOTER */}

Built by Dermott @ MotherDuck for the lads from Edinburgh · Slàinte mhath

Data: curated. Map: stylized. Whisky: highly recommended.

); }

The Tartan Army's Boston Playbook

Comments