#!/usr/bin/env python3
"""
Comprehensive Deathwatch rulebook scraper.
Extracts weapons, armour, gear, talents, traits, psychic powers, and bestiary
from PDFs and imports them into MariaDB.
"""
import os, re, json, sys
import pdfplumber
import fitz
import mysql.connector
from pathlib import Path

REPO   = Path(__file__).resolve().parent.parent
RULES  = REPO / 'database' / 'rules'

def load_env_file(path):
    if not path.exists():
        return
    for line in path.read_text().splitlines():
        line = line.strip()
        if not line or line.startswith('#') or '=' not in line:
            continue
        key, value = line.split('=', 1)
        os.environ.setdefault(key.strip(), value.strip().strip('"').strip("'"))

load_env_file(REPO / 'database' / '.env')
load_env_file(REPO / '.env')
if not os.environ.get('DB_PASSWORD'):
    raise RuntimeError('DB_PASSWORD must be set in database/.env or .env')

DB = dict(
    host=os.environ.get('DB_HOST', '192.168.1.113'),
    port=int(os.environ.get('DB_PORT', '3307')),
    user=os.environ.get('DB_USER', 'deathwatch'),
    password=os.environ['DB_PASSWORD'],
    database=os.environ.get('DB_NAME', 'deathwatch'),
)

def clean(s):
    if not s: return ''
    return re.sub(r'\s+', ' ', str(s).replace('\n', ' ')).strip()

def req_clean(s):
    if not s: return 0
    s = re.sub(r'[†‡\*]','', str(s)).strip()
    try: return int(s)
    except: return 0

# ── DB helpers ─────────────────────────────────────────────────────────────────

def get_db():
    return mysql.connector.connect(**DB)

def upsert_weapon(cur, name, category, stats_dict, source):
    cur.execute("""
        INSERT INTO weapons (name, category, stats, source)
        VALUES (%s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            category=VALUES(category), stats=VALUES(stats), source=VALUES(source)
    """, (name, category, json.dumps(stats_dict), source))

def upsert_rule(cur, rule_id, title, content, page, source, source_abbr, category):
    cur.execute("""
        INSERT INTO rules (rule_id, title, content, page, source, source_abbr, category)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            title=VALUES(title), content=VALUES(content), page=VALUES(page),
            source=VALUES(source), source_abbr=VALUES(source_abbr), category=VALUES(category)
    """, (rule_id, title, content, page, source, source_abbr, category))

def upsert_bestiary(cur, name, stats, profile_dict, book, page_num):
    snippet = profile_dict.get('description', '')[:500]
    cur.execute("""
        INSERT INTO bestiary (name, book, page, pdf, stats, profile, snippet)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            stats=VALUES(stats), profile=VALUES(profile), snippet=VALUES(snippet), page=VALUES(page)
    """, (name, book, str(page_num), book[:3].lower(),
          json.dumps(stats), json.dumps(profile_dict), snippet))

# ── Weapon extraction from CR-style tables (pdfplumber) ───────────────────────

def parse_weapon_row(row):
    """Parse a weapon table row (8-col melee or 12-col ranged)."""
    if not row or len(row) < 7: return None
    name = clean(row[0])
    if not name or len(name) < 3: return None
    if re.match(r'^[\d†‡\*\s]+$', name): return None
    if re.match(r'^(Name|Table|Bolt|Plasma|Melta|Flamer|Las|Solid|Roll|Result)', name): return None

    # 12-col ranged: Name, Class, Range, RoF, Dmg, Pen, Clip, Rld, Special, Wt, Req, Renown
    if len(row) >= 12:
        return {
            'name':    name,
            'class':   clean(row[1]),
            'range':   clean(row[2]),
            'rof':     clean(row[3]),
            'damage':  clean(row[4]),
            'pen':     clean(row[5]),
            'clip':    clean(row[6]),
            'rld':     clean(row[7]),
            'special': clean(row[8]),
            'wt':      clean(row[9]),
            'req':     req_clean(row[10]),
            'renown':  clean(row[11]).replace('–','-').replace('—','-'),
        }
    # 8-col melee: Name, Class, Dmg, Pen, Special, Wt, Req, Renown
    if len(row) >= 8:
        return {
            'name':    name,
            'class':   clean(row[1]),
            'range':   '-',
            'rof':     '-',
            'damage':  clean(row[2]),
            'pen':     clean(row[3]),
            'clip':    '-',
            'rld':     '-',
            'special': clean(row[4]),
            'wt':      clean(row[5]),
            'req':     req_clean(row[6]),
            'renown':  clean(row[7]).replace('–','-').replace('—','-'),
        }
    return None

def detect_category_from_text(text, current):
    """Detect weapon category from surrounding text."""
    t = text.lower()
    if re.search(r'melee weapons?|close combat', t): return 'Melee Weapon'
    if re.search(r'ranged weapons?|bolt weapon|plasma|melta|flamer|las weapon|solid projectile|launcher', t): return 'Ranged Weapon'
    if re.search(r'grenade|explosive', t): return 'Grenade'
    if re.search(r'armour|carapace|power armour', t): return 'Armour'
    if re.search(r'wargear|equipment|gear', t): return 'Gear'
    return current

def extract_weapons_pdfplumber(pdf_path, page_start, page_end, source, source_abbr):
    """Extract weapon tables using pdfplumber (works well for CR)."""
    weapons = []
    category = 'Ranged Weapon'
    with pdfplumber.open(pdf_path) as pdf:
        for pg_idx in range(page_start-1, min(page_end, len(pdf.pages))):
            page = pdf.pages[pg_idx]
            text = page.extract_text() or ''
            category = detect_category_from_text(text, category)
            for table in page.extract_tables():
                for row in table:
                    w = parse_weapon_row(row)
                    if w:
                        w['category'] = category
                        w['source'] = source
                        w['source_abbr'] = source_abbr
                        weapons.append(w)
    seen = {}
    for w in weapons:
        seen[w['name'].lower()] = w
    return list(seen.values())

# ── Weapon extraction from text (for FF/HtC where tables are text-based) ──────

def dedupe_doubled(s):
    """Fix doubled OCR text like 'MMeelleeee' -> 'Melee'."""
    if not s: return s
    # If every character is doubled, halve it
    if len(s) % 2 == 0:
        half = len(s) // 2
        if s[:half] == s[half:]:
            return s[:half]
    # Try to fix character-by-character doubling
    result = re.sub(r'(.)\1', r'\1', s)
    return result if len(result) < len(s) * 0.7 else s

def extract_weapons_from_text(pdf_path, page_start, page_end, source, source_abbr):
    """Extract weapons from text-based tables (FF, HtC)."""
    doc = fitz.open(str(pdf_path))
    weapons = []
    category = 'Ranged Weapon'

    # Pattern: weapon entry in a table row
    # "Name   Class   Dmg   Pen   Special   Wt   Req   Renown"
    # or "Name  Class  Range  RoF  Dmg  Pen  Clip  Rld  Special  Wt  Req  Renown"
    weapon_line_re = re.compile(
        r'^(.+?)\s{2,}(Melee|Pistol|Basic|Heavy|Mounted|Thrown)\s{2,}(.+?)\s{2,}(\d+)\s{2,}(.+?)\s{2,}(\d+)\s{2,}(.+?)\s{2,}([\w\s]+)$'
    )
    # Simpler pattern for melee: Name  Melee  Dmg  Pen  Special  Wt  Req  Renown
    melee_re = re.compile(
        r'^(.{3,40}?)\s{2,}(?:Melee|melee)\s{2,}([\dd+\s\w]+)\s{2,}(\d+)\s{2,}(.+?)\s{2,}(\d+\.?\d*)\s{2,}(\d+)\s{2,}(.+)$'
    )

    for pg_idx in range(page_start-1, min(page_end, doc.page_count)):
        page = doc[pg_idx]
        text = page.get_text()
        category = detect_category_from_text(text, category)

        # Parse line by line
        for line in text.split('\n'):
            line = clean(line)
            if not line or len(line) < 10: continue

            # Detect category headers
            if re.match(r'Table \d+[-–]\d+.*Ranged', line, re.I): category = 'Ranged Weapon'
            elif re.match(r'Table \d+[-–]\d+.*Melee', line, re.I): category = 'Melee Weapon'
            elif re.match(r'Table \d+[-–]\d+.*Armour', line, re.I): category = 'Armour'
            elif re.match(r'Table \d+[-–]\d+.*Wargear', line, re.I): category = 'Gear'

            # Try to match weapon rows with 2+ spaces between columns
            parts = re.split(r'\s{2,}', line)
            if len(parts) >= 7:
                name = dedupe_doubled(parts[0])
                if not name or len(name) < 3: continue
                if re.match(r'^(Name|Table|Chapter|\d+)', name): continue
                cls = dedupe_doubled(parts[1]) if len(parts) > 1 else ''
                if cls.lower() not in ('melee','pistol','basic','heavy','mounted','thrown','–','-'):
                    # For armour/gear tables
                    if category in ('Armour', 'Gear') and req_clean(parts[-2]) > 0:
                        weapons.append({
                            'name': name, 'class': cls, 'range': '-', 'rof': '-',
                            'damage': '-', 'pen': '0', 'clip': '-', 'rld': '-',
                            'special': ' '.join(parts[2:-2]) if len(parts)>4 else '',
                            'wt': '0', 'req': req_clean(parts[-2]),
                            'renown': parts[-1] if parts else '-',
                            'category': category, 'source': source, 'source_abbr': source_abbr
                        })
                    continue
                dmg = dedupe_doubled(parts[2]) if len(parts) > 2 else ''
                pen = dedupe_doubled(parts[3]) if len(parts) > 3 else '0'
                special = dedupe_doubled(parts[4]) if len(parts) > 4 else ''
                wt = parts[5] if len(parts) > 5 else '0'
                req = req_clean(parts[6]) if len(parts) > 6 else 0
                renown = parts[7] if len(parts) > 7 else '-'
                if not re.search(r'\d', dmg): continue
                weapons.append({
                    'name': name, 'class': cls, 'range': '-', 'rof': '-',
                    'damage': dmg, 'pen': pen, 'clip': '-', 'rld': '-',
                    'special': special, 'wt': wt, 'req': req, 'renown': renown,
                    'category': category, 'source': source, 'source_abbr': source_abbr
                })

    doc.close()
    seen = {}
    for w in weapons:
        if w['name'] and len(w['name']) > 2:
            seen[w['name'].lower()] = w
    return list(seen.values())

# ── Rule/Talent/Trait extraction ───────────────────────────────────────────────

def extract_rules_section(pdf_path, page_start, page_end, category, source, source_abbr):
    """Extract rules entries as text blocks."""
    doc = fitz.open(str(pdf_path))
    entries = []
    current_title = None
    current_content = []
    current_page = page_start

    for pg_idx in range(page_start-1, min(page_end, doc.page_count)):
        page = doc[pg_idx]
        blocks = page.get_text("blocks")
        for block in blocks:
            text = clean(block[4])
            if not text or len(text) < 3: continue
            if re.match(r'^\d{1,3}$', text): continue
            if re.match(r'^[IVX]+\s*:', text): continue
            if re.match(r'^(Table|Contents|Credits|Introduction)', text, re.I): continue

            is_title = (
                len(text) < 80 and
                len(text.split()) <= 8 and
                not text.endswith('.') and
                not text.startswith('Prerequisites') and
                not re.match(r'^[a-z]', text) and
                not re.search(r'[0-9]{3,}', text) and
                not re.match(r'^(The |A |An |In |At |For |With |This |These |When )', text)
            )

            if is_title and current_title and len(' '.join(current_content)) > 30:
                entries.append({
                    'title': current_title,
                    'content': ' '.join(current_content).strip(),
                    'page': current_page,
                    'source': source,
                    'source_abbr': source_abbr,
                    'category': category
                })
                current_content = []

            if is_title:
                current_title = text
                current_page = pg_idx + 1
            elif current_title:
                current_content.append(text)

    if current_title and len(' '.join(current_content)) > 30:
        entries.append({
            'title': current_title,
            'content': ' '.join(current_content).strip(),
            'page': current_page,
            'source': source,
            'source_abbr': source_abbr,
            'category': category
        })

    doc.close()
    return entries

# ── Bestiary extraction (MoX format) ──────────────────────────────────────────

STAT_HEADER = re.compile(r'WS\s+[bB]S\s+S\s+T\s+Ag\s+Int\s+Per\s+WP\s+Fel')
STAT_VALUES = re.compile(r'^[\d\s\(\)\-–]+$')
CREATURE_NAME = re.compile(r'^([A-Z][A-Z\s\'\-]{2,50})$|^([a-z][A-Z]{2,}[a-z\s]+)$')

def extract_bestiary_mox(pdf_path):
    """Extract bestiary entries from Mark of the Xenos format."""
    doc = fitz.open(str(pdf_path))
    entries = []
    current = None

    for pg_idx in range(8, doc.page_count):  # skip intro pages
        page = doc[pg_idx]
        text = page.get_text()
        lines = [l.strip() for l in text.split('\n') if l.strip()]

        i = 0
        while i < len(lines):
            line = lines[i]

            # Detect creature name: ALL CAPS or mixed OCR caps, short, no numbers
            is_creature_name = (
                len(line) > 3 and len(line) < 60 and
                len(line.split()) <= 6 and
                not re.search(r'\d', line) and
                not re.search(r'[.,:;]', line) and
                (line.upper() == line or re.match(r'^[A-Z][a-zA-Z\s]+[A-Z]$', line)) and
                not re.match(r'^(WS|BS|Skills|Talents|Traits|Armour|Weapons|Gear|Movement|Wounds|Special|The |A |An |In )', line)
            )

            # Detect stat block header
            if STAT_HEADER.search(line):
                # Next non-empty line should be stat values
                j = i + 1
                stat_values = []
                while j < len(lines) and j < i+5:
                    vals = re.findall(r'[\d\(\)]+', lines[j])
                    if vals:
                        stat_values.extend(vals)
                        j += 1
                        if len(stat_values) >= 9: break
                    elif not lines[j].strip(): j += 1
                    else: break

                if current and len(stat_values) >= 9:
                    keys = ['ws','bs','s','t','ag','int','per','wp','fel']
                    for k, v in zip(keys, stat_values[:9]):
                        try: current['stats'][k] = int(re.sub(r'[()]','',v))
                        except: pass
                i = j
                continue

            # Parse profile fields
            if current:
                if line.startswith('Movement:'):
                    m = re.search(r'(\d+)/(\d+)/(\d+)/(\d+)', line)
                    if m: current['stats']['movement'] = m.group(1)
                    m2 = re.search(r'Wounds:\s*(\d+)', line)
                    if m2: current['stats']['wounds'] = int(m2.group(1))
                elif line.startswith('Wounds:'):
                    m = re.search(r'(\d+)', line)
                    if m: current['stats']['wounds'] = int(m.group(1))
                elif line.startswith('Skills:'):
                    current['profile']['skills'] = line
                elif line.startswith('Talents:'):
                    current['profile']['talents'] = line
                elif line.startswith('Traits:'):
                    current['profile']['traits'] = line
                elif line.startswith('Armour:'):
                    current['profile']['armour'] = line
                elif line.startswith('Weapons:'):
                    current['profile']['weapons'] = line
                elif line.startswith('Gear:'):
                    current['profile']['gear'] = line
                elif line.startswith('Special Rules'):
                    current['profile']['special_rules'] = ''
                elif 'special_rules' in current['profile'] and not line.startswith(('WS','BS','Movement','Wounds','Skills','Talents','Traits','Armour','Weapons','Gear')):
                    current['profile']['special_rules'] = (current['profile'].get('special_rules','') + ' ' + line).strip()
                elif not any(line.startswith(x) for x in ('WS','BS','Movement','Wounds','Skills','Talents','Traits','Armour','Weapons','Gear')):
                    current['profile']['description'] = (current['profile'].get('description','') + ' ' + line).strip()

            if is_creature_name:
                # Normalise name: fix bROADSIDE bATTLESUIT -> Broadside Battlesuit
                name = ' '.join(w.capitalize() for w in line.split())
                if current and (current['stats'] or current['profile'].get('description','')):
                    entries.append(current)
                current = {
                    'name': name,
                    'page': pg_idx + 1,
                    'stats': {},
                    'profile': {}
                }

            i += 1

    if current and (current['stats'] or current['profile'].get('description','')):
        entries.append(current)

    doc.close()
    return entries

# ── Wargear table extraction (Req + Renown only) ───────────────────────────────

def extract_wargear_table(pdf_path, page_start, page_end, source, source_abbr):
    """Extract wargear entries with just Req and Renown."""
    weapons = []
    with pdfplumber.open(pdf_path) as pdf:
        for pg_idx in range(page_start-1, min(page_end, len(pdf.pages))):
            page = pdf.pages[pg_idx]
            text = page.extract_text() or ''
            if not re.search(r'Req|Renown', text): continue
            for line in text.split('\n'):
                parts = re.split(r'\s{2,}', line.strip())
                if len(parts) >= 2:
                    name = clean(parts[0])
                    req = req_clean(parts[-2]) if len(parts) >= 2 else 0
                    renown = clean(parts[-1]) if parts else '-'
                    if name and len(name) > 3 and req > 0 and not re.match(r'^(Name|Table|\d)', name):
                        weapons.append({
                            'name': name, 'class': 'Gear', 'range': '-', 'rof': '-',
                            'damage': '-', 'pen': '0', 'clip': '-', 'rld': '-',
                            'special': '-', 'wt': '0', 'req': req, 'renown': renown,
                            'category': 'Gear', 'source': source, 'source_abbr': source_abbr
                        })
    seen = {}
    for w in weapons:
        seen[w['name'].lower()] = w
    return list(seen.values())

# ── Main ────────────────────────────────────────────────────────────────────────

def make_rid(prefix, title):
    rid = prefix + re.sub(r'[^a-z0-9]', '_', title.lower())
    return re.sub(r'_+', '_', rid).strip('_')

def run():
    print("=== Deathwatch Rulebook Scraper ===\n")
    db = get_db()
    cur = db.cursor()
    total_weapons = 0
    total_rules = 0
    total_bestiary = 0

    # ── 1. CR Ranged Weapons (p145-155) ───────────────────────────────────────
    print("CR: Ranged weapons (p145-155)...")
    cr_ranged = extract_weapons_pdfplumber(RULES/'CR.pdf', 145, 156, 'Core Rulebook', 'CR')
    for w in cr_ranged:
        upsert_weapon(cur, w['name'], 'Ranged Weapon', {
            'damage':w['damage'], 'pen':w['pen'], 'range':w['range'],
            'rof':w['rof'], 'clip':w['clip'], 'rld':w['rld'],
            'special':w['special'], 'wt':w['wt'], 'req':w['req'],
            'renown':w['renown'], 'source':'CR'
        }, 'Core Rulebook')
    db.commit()
    print(f"  {len(cr_ranged)} ranged weapons")
    total_weapons += len(cr_ranged)

    # ── 2. CR Melee Weapons (p154-158) ────────────────────────────────────────
    print("CR: Melee weapons (p154-158)...")
    cr_melee = extract_weapons_pdfplumber(RULES/'CR.pdf', 154, 159, 'Core Rulebook', 'CR')
    for w in cr_melee:
        upsert_weapon(cur, w['name'], 'Melee Weapon', {
            'damage':w['damage'], 'pen':w['pen'], 'range':'-',
            'rof':'-', 'clip':'-', 'rld':'-',
            'special':w['special'], 'wt':w['wt'], 'req':w['req'],
            'renown':w['renown'], 'source':'CR'
        }, 'Core Rulebook')
    db.commit()
    print(f"  {len(cr_melee)} melee weapons")
    total_weapons += len(cr_melee)

    # ── 3. CR Armour (p163-175) ───────────────────────────────────────────────
    print("CR: Armour & Gear (p163-185)...")
    cr_armour = extract_weapons_pdfplumber(RULES/'CR.pdf', 163, 186, 'Core Rulebook', 'CR')
    for w in cr_armour:
        cat = 'Armour' if 'armour' in w['category'].lower() else 'Gear'
        upsert_weapon(cur, w['name'], cat, {
            'damage':w['damage'], 'pen':w['pen'], 'range':w['range'],
            'rof':w['rof'], 'clip':w['clip'], 'rld':w['rld'],
            'special':w['special'], 'wt':w['wt'], 'req':w['req'],
            'renown':w['renown'], 'source':'CR'
        }, 'Core Rulebook')
    db.commit()
    print(f"  {len(cr_armour)} armour/gear entries")
    total_weapons += len(cr_armour)

    # ── 4. FF Weapons & Wargear (p97-115) ─────────────────────────────────────
    print("FF: Weapons & wargear (p97-115)...")
    ff_weapons = extract_weapons_from_text(RULES/'FF.pdf', 97, 116, 'First Founding', 'FF')
    for w in ff_weapons:
        upsert_weapon(cur, w['name'], w['category'], {
            'damage':w['damage'], 'pen':w['pen'], 'range':w['range'],
            'rof':w['rof'], 'clip':w['clip'], 'rld':w['rld'],
            'special':w['special'], 'wt':w['wt'], 'req':w['req'],
            'renown':w['renown'], 'source':'FF'
        }, 'First Founding')
    db.commit()
    print(f"  {len(ff_weapons)} items from FF")
    total_weapons += len(ff_weapons)

    # ── 5. CR Talents (p114-131) ──────────────────────────────────────────────
    print("CR: Talents (p114-131)...")
    cr_talents = extract_rules_section(RULES/'CR.pdf', 114, 131, 'talents', 'Core Rulebook', 'CR')
    cnt = 0
    for e in cr_talents:
        if len(e['content']) < 30: continue
        upsert_rule(cur, make_rid('talent_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'talents')
        cnt += 1
    db.commit()
    print(f"  {cnt} talents")
    total_rules += cnt

    # ── 6. CR Traits (p131-138) ───────────────────────────────────────────────
    print("CR: Traits (p131-138)...")
    cr_traits = extract_rules_section(RULES/'CR.pdf', 131, 139, 'traits', 'Core Rulebook', 'CR')
    cnt = 0
    for e in cr_traits:
        if len(e['content']) < 30: continue
        upsert_rule(cur, make_rid('trait_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'traits')
        cnt += 1
    db.commit()
    print(f"  {cnt} traits")
    total_rules += cnt

    # ── 7. CR Weapon Special Qualities (p143-146) ─────────────────────────────
    print("CR: Weapon special qualities (p143-146)...")
    cr_sq = extract_rules_section(RULES/'CR.pdf', 143, 146, 'weapon_qualities', 'Core Rulebook', 'CR')
    cnt = 0
    for e in cr_sq:
        if len(e['content']) < 20: continue
        upsert_rule(cur, make_rid('wq_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'weapon_qualities')
        cnt += 1
    db.commit()
    print(f"  {cnt} weapon qualities")
    total_rules += cnt

    # ── 8. CR Psychic Powers (p195-240) ───────────────────────────────────────
    print("CR: Psychic powers (p195-240)...")
    cr_psychic = extract_rules_section(RULES/'CR.pdf', 195, 241, 'psychic', 'Core Rulebook', 'CR')
    cnt = 0
    for e in cr_psychic:
        if len(e['content']) < 30: continue
        upsert_rule(cur, make_rid('psychic_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'psychic')
        cnt += 1
    db.commit()
    print(f"  {cnt} psychic powers")
    total_rules += cnt

    # ── 9. CR Chapter lore (p23-56) ───────────────────────────────────────────
    print("CR: Chapter lore (p23-56)...")
    cr_chap = extract_rules_section(RULES/'CR.pdf', 23, 57, 'lore', 'Core Rulebook', 'CR')
    cnt = 0
    for e in cr_chap:
        if len(e['content']) < 50: continue
        upsert_rule(cur, make_rid('lore_cr_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'lore')
        cnt += 1
    db.commit()
    print(f"  {cnt} chapter lore entries from CR")
    total_rules += cnt

    # ── 10. CR Specialities (p57-92) ──────────────────────────────────────────
    print("CR: Specialities (p57-92)...")
    cr_spec = extract_rules_section(RULES/'CR.pdf', 57, 93, 'speciality', 'Core Rulebook', 'CR')
    cnt = 0
    for e in cr_spec:
        if len(e['content']) < 50: continue
        upsert_rule(cur, make_rid('spec_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'speciality')
        cnt += 1
    db.commit()
    print(f"  {cnt} speciality entries from CR")
    total_rules += cnt

    # ── 11. MoX Bestiary ──────────────────────────────────────────────────────
    print("MoX: Bestiary...")
    mox_entries = extract_bestiary_mox(RULES/'MoX.pdf')
    cnt = 0
    for e in mox_entries:
        if not e['stats'] and not e['profile'].get('description'): continue
        upsert_bestiary(cur, e['name'], e['stats'], e['profile'], 'Mark of the Xenos', e['page'])
        cnt += 1
    db.commit()
    print(f"  {cnt} bestiary entries from MoX")
    total_bestiary += cnt

    # ── 12. FF Chapter lore (p7-96) ───────────────────────────────────────────
    print("FF: Chapter lore (p7-96)...")
    ff_lore = extract_rules_section(RULES/'FF.pdf', 7, 97, 'lore', 'First Founding', 'FF')
    cnt = 0
    for e in ff_lore:
        if len(e['content']) < 50: continue
        upsert_rule(cur, make_rid('lore_ff_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'lore')
        cnt += 1
    db.commit()
    print(f"  {cnt} lore entries from FF")
    total_rules += cnt

    # ── 13. HtC Chapter lore & talents (p9-120) ───────────────────────────────
    print("HtC: Chapter content (p9-120)...")
    htc_content = extract_rules_section(RULES/'HtC.pdf', 9, 121, 'lore', 'Honour the Chapter', 'HtC')
    cnt = 0
    for e in htc_content:
        if len(e['content']) < 50: continue
        upsert_rule(cur, make_rid('lore_htc_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'lore')
        cnt += 1
    db.commit()
    print(f"  {cnt} entries from HtC")
    total_rules += cnt

    # ── 14. RoB lore & rules ──────────────────────────────────────────────────
    print("RoB: Rules & lore...")
    rob_content = extract_rules_section(RULES/'RoB.pdf', 1, 256, 'lore', 'Rites of Battle', 'RoB')
    cnt = 0
    for e in rob_content:
        if len(e['content']) < 50: continue
        upsert_rule(cur, make_rid('rob_', e['title']), e['title'], e['content'],
                    e['page'], e['source'], e['source_abbr'], 'lore')
        cnt += 1
    db.commit()
    print(f"  {cnt} entries from RoB")
    total_rules += cnt

    # ── Final stats ───────────────────────────────────────────────────────────
    cur.execute("SELECT COUNT(*) FROM weapons")
    wcount = cur.fetchone()[0]
    cur.execute("SELECT COUNT(*) FROM rules")
    rcount = cur.fetchone()[0]
    cur.execute("SELECT COUNT(*) FROM bestiary")
    bcount = cur.fetchone()[0]
    cur.execute("SELECT category, COUNT(*) FROM rules GROUP BY category ORDER BY COUNT(*) DESC LIMIT 15")
    cats = cur.fetchall()
    cur.execute("SELECT source, COUNT(*) FROM weapons GROUP BY source ORDER BY COUNT(*) DESC")
    wsrc = cur.fetchall()

    print(f"\n=== Done ===")
    print(f"  Weapons in DB:  {wcount}")
    print(f"  Rules in DB:    {rcount}")
    print(f"  Bestiary in DB: {bcount}")
    print("\nWeapons by source:")
    for src, cnt in wsrc:
        print(f"  {src}: {cnt}")
    print("\nRules by category:")
    for cat, cnt2 in cats:
        print(f"  {cat}: {cnt2}")

    cur.close()
    db.close()

if __name__ == '__main__':
    run()