#!/usr/bin/env python3 """ fetch_fighters.py ----------------- Downloads ALL fighters + their fight history from Firestore into fighters.db (SQLite). Usage: python3 fetch_fighters.py Requirements: pip3 install requests Output: fighters.db — SQLite database in the same directory """ import sqlite3 import requests import json import sys import os BASE_FIGHTERS_URL = ( "https://firestore.googleapis.com/v1/projects/nakmuay-techbouts" "/databases/(default)/documents/techbouts_fighters" ) BASE_FIGHTS_URL = ( "https://firestore.googleapis.com/v1/projects/nakmuay-techbouts" "/databases/(default)/documents/pmt_fights" ) PAGE_SIZE = 300 DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "fighters.db") # ── Firestore helpers ────────────────────────────────────────────────────────── def extract_value(field_val): if not field_val: return None for vtype in ("stringValue", "integerValue", "doubleValue", "booleanValue", "timestampValue"): if vtype in field_val: return field_val[vtype] if "arrayValue" in field_val: items = field_val["arrayValue"].get("values", []) return json.dumps([extract_value(v) for v in items]) if "mapValue" in field_val: m = field_val["mapValue"].get("fields", {}) return json.dumps({k: extract_value(v) for k, v in m.items()}) if "nullValue" in field_val: return None return None def extract(fields, *keys, default=""): for k in keys: v = fields.get(k) if v is not None: val = extract_value(v) if val is not None and str(val).strip(): return str(val).strip() return default def extract_int(fields, *keys): val = extract(fields, *keys, default="0") try: return int(float(val)) except (ValueError, TypeError): return 0 def flatten_fields(fields): return {k: extract_value(v) for k, v in fields.items()} # ── Weight class helpers ─────────────────────────────────────────────────────── def normalise_weight_class(val: str) -> str: """ Store weight class as a plain lbs string: "105", "115", "130", "147" etc. If the value is numeric (possibly with decimals), strip to integer string. If it is already a non-numeric name, return it title-cased. Returns "" for empty/None. """ if not val: return "" val = val.strip() if not val: return "" try: lbs = float(val) return str(int(lbs)) # "147.0" -> "147" except ValueError: return val.title() if val.isupper() else val # ── Fighter parser ───────────────────────────────────────────────────────────── def parse_fighter(doc): f = doc.get("fields", {}) doc_id = doc["name"].split("/")[-1] first = extract(f, "first_name", "firstName", "FirstName", "first", "First") last = extract(f, "last_name", "lastName", "LastName", "last", "Last") name = extract(f, "name", "Name", "fighter_name") or f"{first} {last}".strip() or doc_id # Try direct scalar fields first weight_class = extract( f, "weightClass", "weight_class", "WeightClass", "weightclass", "Weightclass", "division", "Division", default="" ) # Fallback: pmt_weightclasses is an array like ["130"] — grab first element if not weight_class: wc_arr_raw = extract(f, "pmt_weightclasses", "weightClasses", default="") if wc_arr_raw: try: wc_arr = json.loads(wc_arr_raw) if isinstance(wc_arr, list) and wc_arr: weight_class = str(wc_arr[0]) except Exception: pass weight_class = normalise_weight_class(weight_class) or "Unknown" return { "id": doc_id, "name": name, "first_name": first, "last_name": last, "gym": extract(f, "gym", "Gym", "gym_name", "gymName", default=""), "weight_class": weight_class, "wins": extract_int(f, "wins", "Wins"), "losses": extract_int(f, "losses", "Losses"), "draws": extract_int(f, "draws", "Draws"), "age": extract_int(f, "age", "Age"), "city": extract(f, "city", "City", default=""), "state": extract(f, "state", "State", default=""), "email": extract(f, "email", "Email", default=""), "phone": extract(f, "phone", "Phone", "phoneNumber", "phone_number", default=""), "photo_url": extract(f, "photo_url", "photoUrl", "photo", "image", "imageUrl", "profilePhoto", default=""), "dob": extract(f, "dob", "DOB", "date_of_birth", "dateOfBirth", "birthdate", default=""), "gender": extract(f, "gender", "Gender", "sex", default=""), "nationality": extract(f, "nationality", "Nationality", "country", "Country", default=""), "height": extract(f, "height", "Height", default=""), "weight": extract(f, "weight", "Weight", "walk_weight", "walkWeight", default=""), "experience": extract(f, "experience", "Experience", "exp", "years_exp", default=""), "trainer": extract(f, "trainer", "Trainer", "coach", "Coach", "coach_name", default=""), "facebook": extract(f, "facebook", "Facebook", "fb", default=""), "instagram": extract(f, "instagram", "Instagram", "ig", default=""), "pmt_id": extract(f, "pmt_id", "pmtId", "PMT_ID", "fighter_id", default="") or doc_id, "raw_json": json.dumps(flatten_fields(f)), } # ── Fight parser ─────────────────────────────────────────────────────────────── def parse_corner(corner_raw): """Parse a red/blue corner value — may be a nested JSON string.""" if isinstance(corner_raw, str): try: corner_raw = json.loads(corner_raw) except Exception: return {} return corner_raw if isinstance(corner_raw, dict) else {} def parse_fight_item(item, doc_pmt_id): """ Parse one fight dict (already decoded from JSON string). doc_pmt_id = the Firestore document ID, which IS the fighter's pmt_id. """ # The fighter who owns this document fighter_pmt_id = doc_pmt_id # result at top level belongs to the doc owner result = item.get("result", "") opponent_id = item.get("opponent_id", "") # Identify which corner is the doc owner vs opponent red = parse_corner(item.get("red", {})) blue = parse_corner(item.get("blue", {})) red_id = red.get("fighter_id", "") blue_id = blue.get("fighter_id", "") # Owner corner = whichever fighter_id matches doc pmt_id if red_id == fighter_pmt_id: owner_corner = red opponent_corner = blue if not opponent_id: opponent_id = blue_id elif blue_id == fighter_pmt_id: owner_corner = blue opponent_corner = red if not opponent_id: opponent_id = red_id else: # Fallback: use pmt_id field on item, opponent from blue corner owner_corner = red opponent_corner = blue if not opponent_id: opponent_id = blue_id opponent_name = f"{opponent_corner.get('first','')} {opponent_corner.get('last','')}".strip() opponent_gym = opponent_corner.get("gym", "") # Height from owner corner: e.g. 5'10" h_foot = owner_corner.get("heightFoot", "") h_inch = owner_corner.get("heightInch", "") height_str = f"{h_foot}'{h_inch}\"" if h_foot else "" return { "bout_id": item.get("boutId", "") or item.get("bout_id", ""), "fighter_id": fighter_pmt_id, # will be resolved to DB id later "fighter_pmt_id": fighter_pmt_id, "opponent_id": opponent_id, "opponent_name": opponent_name, "opponent_gym": opponent_gym, "result": result, "date": item.get("date", ""), "event_name": item.get("event_name", ""), "event_id": item.get("eventId", ""), "promotion": item.get("promotionName", "") or item.get("promotionId", ""), "city": item.get("city", ""), "state": item.get("state", ""), "weight_class": str(item.get("weightclass", "") or item.get("weight_class", "")), "bout_type": item.get("bout_type", "") or item.get("bout_ruleset", ""), "sanctioning": item.get("sanctioning", ""), "method": item.get("methodOfVictory", "") or item.get("method", ""), "bout_num": str(item.get("boutNum", "") or item.get("bout", "")), "professional": str(item.get("professional", False)), "raw_json": json.dumps(item), } def _append_fight(fights, item, doc_id, field_key=""): """Parse one fight dict and append, ensuring a unique bout_id.""" fight = parse_fight_item(item, doc_id) if not fight["bout_id"]: fight["bout_id"] = ( f"{doc_id}|{fight['event_id'] or field_key}|" f"{fight['date']}|{fight['opponent_id']}|{fight['bout_num']}" ) fights.append(fight) def parse_fight_doc(doc): """ Firestore structure: Document ID = fighter pmt_id (e.g. "BRADDAVIDE09072000") Fields = one key per event_id, value = arrayValue[ stringValue(json), ... ] Each array element is a JSON-encoded fight object string. Also handles mapValue and bare stringValue fields for older doc formats. """ fights = [] doc_id = doc["name"].split("/")[-1] # this IS the fighter's pmt_id fields = doc.get("fields", {}) for field_key, field_val in fields.items(): # Case 1: arrayValue of stringValue(json) — the standard format if "arrayValue" in field_val: array_items = field_val["arrayValue"].get("values", []) for av in array_items: raw_str = av.get("stringValue", "") if not raw_str: # Maybe a mapValue element if "mapValue" in av: m = av["mapValue"].get("fields", {}) item = {k: extract_value(v) for k, v in m.items()} if item: _append_fight(fights, item, doc_id, field_key) continue try: item = json.loads(raw_str) except Exception: continue if isinstance(item, dict): _append_fight(fights, item, doc_id, field_key) elif isinstance(item, list): # The string itself is a JSON array of fight objects for sub in item: if isinstance(sub, dict): _append_fight(fights, sub, doc_id, field_key) # Case 2: mapValue — fight stored directly as a Firestore map elif "mapValue" in field_val: m = field_val["mapValue"].get("fields", {}) item = {k: extract_value(v) for k, v in m.items()} if item and ("result" in item or "date" in item): _append_fight(fights, item, doc_id, field_key) # Case 3: stringValue containing a JSON fight object elif "stringValue" in field_val: raw_str = field_val["stringValue"] if raw_str and raw_str.strip().startswith("{"): try: item = json.loads(raw_str) if isinstance(item, dict): _append_fight(fights, item, doc_id, field_key) except Exception: pass return fights # ── Fetch helpers ────────────────────────────────────────────────────────────── def fetch_collection(url, label): docs = [] page_token = None page = 0 while True: page += 1 params = {"pageSize": PAGE_SIZE} if page_token: params["pageToken"] = page_token print(f" [{label}] Page {page}…", end=" ", flush=True) resp = requests.get(url, params=params, timeout=30) resp.raise_for_status() data = resp.json() batch = data.get("documents", []) print(f"{len(batch)} docs") docs.extend(batch) page_token = data.get("nextPageToken") if not page_token: break return docs # ── SQLite ───────────────────────────────────────────────────────────────────── def create_schema(conn): """Create tables and indexes on a fresh connection.""" conn.executescript(""" CREATE TABLE fighters ( id TEXT PRIMARY KEY, name TEXT NOT NULL, first_name TEXT, last_name TEXT, gym TEXT, weight_class TEXT, wins INTEGER DEFAULT 0, losses INTEGER DEFAULT 0, draws INTEGER DEFAULT 0, age INTEGER DEFAULT 0, city TEXT, state TEXT, email TEXT, phone TEXT, photo_url TEXT, dob TEXT, gender TEXT, nationality TEXT, height TEXT, weight TEXT, experience TEXT, trainer TEXT, facebook TEXT, instagram TEXT, pmt_id TEXT, raw_json TEXT ); CREATE TABLE fights ( bout_id TEXT PRIMARY KEY, fighter_id TEXT, fighter_pmt_id TEXT, opponent_id TEXT, opponent_name TEXT, opponent_gym TEXT, result TEXT, date TEXT, event_name TEXT, event_id TEXT, promotion TEXT, city TEXT, state TEXT, weight_class TEXT, bout_type TEXT, sanctioning TEXT, method TEXT, bout_num TEXT, professional TEXT, raw_json TEXT ); CREATE INDEX idx_fighters_wc ON fighters(weight_class); CREATE INDEX idx_fighters_name ON fighters(name); CREATE INDEX idx_fighters_gym ON fighters(gym); CREATE INDEX idx_fighters_pmt_id ON fighters(pmt_id); CREATE INDEX idx_fights_fid ON fights(fighter_id); CREATE INDEX idx_fights_pmt_id ON fights(fighter_pmt_id); CREATE INDEX idx_fights_date ON fights(date); CREATE INDEX idx_fights_opp ON fights(opponent_id); """) conn.commit() def insert_fighters(conn, fighters): conn.executemany(""" INSERT INTO fighters ( id, name, first_name, last_name, gym, weight_class, wins, losses, draws, age, city, state, email, phone, photo_url, dob, gender, nationality, height, weight, experience, trainer, facebook, instagram, pmt_id, raw_json ) VALUES ( :id, :name, :first_name, :last_name, :gym, :weight_class, :wins, :losses, :draws, :age, :city, :state, :email, :phone, :photo_url, :dob, :gender, :nationality, :height, :weight, :experience, :trainer, :facebook, :instagram, :pmt_id, :raw_json ) """, fighters) conn.commit() def insert_fights(conn, fights): conn.executemany(""" INSERT OR REPLACE INTO fights ( bout_id, fighter_id, fighter_pmt_id, opponent_id, opponent_name, opponent_gym, result, date, event_name, event_id, promotion, city, state, weight_class, bout_type, sanctioning, method, bout_num, professional, raw_json ) VALUES ( :bout_id, :fighter_id, :fighter_pmt_id, :opponent_id, :opponent_name, :opponent_gym, :result, :date, :event_name, :event_id, :promotion, :city, :state, :weight_class, :bout_type, :sanctioning, :method, :bout_num, :professional, :raw_json ) """, fights) conn.commit() # ── Main ─────────────────────────────────────────────────────────────────────── def main(): import tempfile, shutil # ── 1. Fetch ALL data from Firestore first (before touching the DB) ── print("[1/3] Fetching fighters from Firestore…") try: fighter_docs = fetch_collection(BASE_FIGHTERS_URL, "fighters") except Exception as e: print(f"\n✗ Fighters fetch failed: {e}"); sys.exit(1) fighters = [parse_fighter(d) for d in fighter_docs] pmt_to_id = {} for f in fighters: pmt_to_id[f["id"]] = f["id"] if f["pmt_id"]: pmt_to_id[f["pmt_id"]] = f["id"] print("\n[2/3] Fetching fight history from Firestore…") try: fight_docs = fetch_collection(BASE_FIGHTS_URL, "fights") except requests.HTTPError as e: print(f" WARNING: pmt_fights fetch failed ({e}) — fights will be empty") fight_docs = [] except Exception as e: print(f" WARNING: fights fetch error: {e} — fights will be empty") fight_docs = [] all_fights_map = {} # bout_id -> fight dict (last write wins = richest data) for doc in fight_docs: parsed = parse_fight_doc(doc) for fight in parsed: fid = fight["fighter_pmt_id"] if fid in pmt_to_id: fight["fighter_id"] = pmt_to_id[fid] # Prefer entries with a real opponent name/event over blanks bid = fight["bout_id"] existing = all_fights_map.get(bid) if existing is None: all_fights_map[bid] = fight else: # Merge: take the richer of the two records for k in ("opponent_name", "opponent_gym", "event_name", "city", "state", "method"): if not existing.get(k) and fight.get(k): existing[k] = fight[k] all_fights = list(all_fights_map.values()) # ── 2. Build fresh DB in a temp file ── # This means fighters.db is NEVER in a broken/partial state. # The swap at the end is atomic on Linux (same filesystem). print(f"\n[3/3] Building fresh database ({len(fighters)} fighters, {len(all_fights)} fights)…") db_dir = os.path.dirname(os.path.abspath(DB_PATH)) tmp_fd, tmp_path = tempfile.mkstemp(dir=db_dir, suffix=".db.tmp") os.close(tmp_fd) try: tmp_conn = sqlite3.connect(tmp_path) create_schema(tmp_conn) insert_fighters(tmp_conn, fighters) if all_fights: insert_fights(tmp_conn, all_fights) tmp_conn.close() # Atomic replace: old DB stays readable until the exact moment of swap shutil.move(tmp_path, DB_PATH) print(f" Swapped in fresh DB → {DB_PATH}") except Exception as e: # Clean up temp file on failure — old DB is untouched try: os.unlink(tmp_path) except: pass print(f"\n✗ DB build failed: {e}") raise # ── Summary ── conn2 = sqlite3.connect(DB_PATH) cur = conn2.execute( "SELECT weight_class, COUNT(*) AS cnt FROM fighters GROUP BY weight_class ORDER BY cnt DESC" ) rows = cur.fetchall() fight_count = conn2.execute("SELECT COUNT(*) FROM fights").fetchone()[0] conn2.close() print(f"\nDone! Summary:") print(f" {'Weight Class':<30} {'Fighters':>8}") print(f" {'-'*40}") for wc, cnt in rows: print(f" {wc:<30} {cnt:>8}") print(f"\n Total fighters : {len(fighters)}") print(f" Total fights : {fight_count}") print(f" DB : {DB_PATH}") if __name__ == "__main__": main()