# Check if files exist
ls tgaz.db query_tgaz.py 2>/dev/null

# If not found, clone the repo (requires git-lfs for the 228 MB database)
git lfs install
git clone https://github.com/kltng/tgaz-sqlite.git /tmp/tgaz-sqlite
cp /tmp/tgaz-sqlite/tgaz.db /tmp/tgaz-sqlite/query_tgaz.py .

# Search by name (Chinese or romanized)
python3 query_tgaz.py "北京"
python3 query_tgaz.py "Chang'an"

# Name + temporal filter
python3 query_tgaz.py "长安" --year -200

# Full-text search (searches names, feature types, parent names)
python3 query_tgaz.py --fts "county Beijing"

# Spatial query: bounding box (lon_min,lat_min,lon_max,lat_max)
python3 query_tgaz.py --bbox 108,34,110,36 --year -200

# Feature type + year
python3 query_tgaz.py --feature-type county --year 1820 --limit 20

# Children of a jurisdiction
python3 query_tgaz.py --parent hvd_9659 --year 1820

# All name forms for a place (simplified, traditional, pinyin)
python3 query_tgaz.py --spellings hvd_70621

# Full history: parents, predecessors, present location, notes
python3 query_tgaz.py --history hvd_70621

# Output as JSON (for further processing or LLM context)
python3 query_tgaz.py "长安" --format json

# Database statistics
python3 query_tgaz.py --stats

# Raw SQL
python3 query_tgaz.py --sql "SELECT name, transcription, beg_yr, end_yr FROM mv_pn_srch WHERE ftype_tr = 'fu' AND beg_yr <= 1400 AND end_yr >= 1400 ORDER BY name"

SELECT name, transcription, ftype_tr, beg_yr, end_yr
FROM mv_pn_srch
WHERE CAST(x_coord AS REAL) BETWEEN 116.0 AND 117.0
  AND CAST(y_coord AS REAL) BETWEEN 39.5 AND 40.5
  AND beg_yr <= 1400 AND end_yr >= 1400
ORDER BY ftype_tr;

-- Get the place and all its parents over time
SELECT m.name AS child_name, m.transcription AS child_trans,
       po.begin_year, po.end_year,
       pm.name AS parent_name, pm.transcription AS parent_trans,
       pm.ftype_tr AS parent_type
FROM part_of po
JOIN placename c ON po.child_id = c.id
JOIN placename p ON po.parent_id = p.id
JOIN mv_pn_srch m ON m.sys_id = c.sys_id
JOIN mv_pn_srch pm ON pm.sys_id = p.sys_id
WHERE c.sys_id = 'hvd_70621'
ORDER BY po.begin_year;

-- Predecessor chain
SELECT prev.sys_id, m.name, m.transcription, m.beg_yr, m.end_yr
FROM prec_by pb
JOIN placename curr ON pb.placename_id = curr.id
JOIN placename prev ON pb.prec_id = prev.id
JOIN mv_pn_srch m ON m.sys_id = prev.sys_id
WHERE curr.sys_id = 'hvd_70626';

SELECT s.written_form, sc.name AS script, sc.lang, tr.name AS trsys
FROM spelling s
JOIN placename p ON s.placename_id = p.id
LEFT JOIN script sc ON s.script_id = sc.id
LEFT JOIN trsys tr ON s.trsys_id = tr.id
WHERE p.sys_id = 'hvd_70621'
ORDER BY sc.lang;

SELECT
  CASE
    WHEN beg_yr BETWEEN -221 AND -207 THEN 'Qin'
    WHEN beg_yr BETWEEN -206 AND 8 THEN 'Western Han'
    WHEN beg_yr BETWEEN 25 AND 220 THEN 'Eastern Han'
    WHEN beg_yr BETWEEN 618 AND 907 THEN 'Tang'
    WHEN beg_yr BETWEEN 960 AND 1279 THEN 'Song'
    WHEN beg_yr BETWEEN 1271 AND 1368 THEN 'Yuan'
    WHEN beg_yr BETWEEN 1368 AND 1644 THEN 'Ming'
    WHEN beg_yr BETWEEN 1644 AND 1911 THEN 'Qing'
    ELSE 'Other'
  END AS dynasty,
  COUNT(*) AS county_count
FROM mv_pn_srch
WHERE ftype_tr = 'xian'
GROUP BY dynasty
ORDER BY MIN(beg_yr);