#!/usr/bin/env python3
"""
Review and update NPC drops in pk_npcdrops_update.sql based on NpcDrops.java data.

Usage:
    python update_drops.py show <npc_id_or_name>
    python update_drops.py use-java <npc_id_or_name> [--allow-f2p]
    python update_drops.py use-sql <npc_id_or_name>
    python update_drops.py add-java <npc_id_or_name> [--allow-f2p]
    python update_drops.py status
    python update_drops.py pending
    python update_drops.py bulk-p2p [--dry-run]

Environment:
    PK_NPCDROPS_FILE   Override path to the SQL file being edited (default: pk_npcdrops_update.sql).
                       Use this to run conversions against a preview/scratch copy.

Safety:
    use-java and add-java refuse to touch F2P NPCs (pk_npcdef.f2p=1) unless
    --allow-f2p is passed. This protects hand-tweaked F2P data.
"""

import re
import sys
import os
import json
import math
from collections import defaultdict
from fractions import Fraction

# Reuse parsing logic from compare_drops.py
from compare_drops import (
    parse_enum_file, load_enums, parse_sub_tables, SubTable,
    expand_npc_tables, normalize_drops, normalize_drops_expanded,
    compare_npc, BONE_ITEM_IDS, BONE_ITEM_NAMES,
    _remove_openpk_blocks,
)

# ─── ANSI colors ────────────────────────────────────────────────────────────
GREEN  = "\033[92m"
YELLOW = "\033[93m"
RED    = "\033[91m"
CYAN   = "\033[96m"
BOLD   = "\033[1m"
DIM    = "\033[2m"
RESET  = "\033[0m"

# ─── File paths ─────────────────────────────────────────────────────────────
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
UPDATE_SQL_PATH = os.environ.get(
    "PK_NPCDROPS_FILE",
    os.path.join(SCRIPT_DIR, "pk_npcdrops_update.sql"),
)
JAVA_PATH = os.path.join(SCRIPT_DIR, "server/src/com/openrsc/server/constants/NpcDrops.java")
NPC_PATH  = os.path.join(SCRIPT_DIR, "server/src/com/openrsc/server/constants/NpcId.java")
ITEM_PATH = os.path.join(SCRIPT_DIR, "server/src/com/openrsc/server/constants/ItemId.java")
NPCDEF_SQL_PATH = os.path.join(SCRIPT_DIR, "pk_npcdef.sql")
STATUS_FILE = os.path.join(SCRIPT_DIR, ".drop_review_status.json")

# ─── Bone type -> item ID mapping ───────────────────────────────────────────
BONE_TYPE_TO_ITEM = {
    "normal_bones": 20,     # Bones
    "big_bones": 413,       # Big Bones
    "bat_bones": 604,       # Bat Bones
    "dragon_bones": 814,    # Dragon Bones
    "ashes": 181,           # Ashes
}


# ─── F2P/P2P scope guard ────────────────────────────────────────────────────

def load_npc_f2p_flags():
    """
    Parse pk_npcdef.sql and return {npc_id: f2p_flag} where f2p_flag is 1 (F2P) or 0 (P2P).
    f2p is the second-to-last column in each INSERT row, immediately before def_creator.
    """
    flags = {}
    if not os.path.exists(NPCDEF_SQL_PATH):
        return flags
    with open(NPCDEF_SQL_PATH, "r", encoding="utf-8", errors="replace") as f:
        for line in f:
            head = re.match(r"\s*\((\d+),", line)
            if not head:
                continue
            tail = re.search(r",\s*([01]),\s*([01])\)[,;]?\s*$", line)
            if not tail:
                continue
            flags[int(head.group(1))] = int(tail.group(1))
    return flags


def assert_p2p_or_allowed(npc_id, npc_name, f2p_flags, allow_f2p):
    """Refuse to modify F2P NPCs unless --allow-f2p is passed."""
    flag = f2p_flags.get(npc_id)
    if flag is None:
        print(f"\n{YELLOW}WARNING: NPC #{npc_id} ({npc_name}) not found in pk_npcdef.sql — proceeding anyway.{RESET}")
        return
    if flag == 1 and not allow_f2p:
        print(
            f"\n{RED}REFUSED: NPC #{npc_id} ({npc_name}) is F2P (pk_npcdef.f2p=1).{RESET}\n"
            f"This script defaults to P2P-only to protect hand-tweaked F2P data.\n"
            f"Pass --allow-f2p if you really want to overwrite F2P drops.\n"
        )
        sys.exit(2)


# ═══════════════════════════════════════════════════════════════════════════
# SQL file reading/writing
# ═══════════════════════════════════════════════════════════════════════════

def parse_update_sql():
    """
    Parse pk_npcdrops_update.sql.
    Returns:
        header: str - everything before the INSERT VALUES
        rows: list of (npcdef_id, amount_str, item_id, weight, db_index) tuples
        footer: str - everything after the INSERT block (from the semicolon onward)
    """
    with open(UPDATE_SQL_PATH, "r") as f:
        content = f.read()

    # Find the INSERT INTO line
    insert_match = re.search(
        r"(INSERT INTO `pk_npcdrops`[^V]*VALUES\s*\n)",
        content
    )
    if not insert_match:
        print(f"{RED}ERROR: Could not find INSERT INTO block in {UPDATE_SQL_PATH}{RESET}")
        sys.exit(1)

    header_end = insert_match.end()

    # Everything before and including "VALUES\n"
    header = content[:header_end]

    # Now parse the value rows from header_end until we hit the final semicolon
    rest = content[header_end:]

    rows = []
    for m in re.finditer(r"\((\d+),\s*'(-?\d+)',\s*(-?\d+),\s*(\d+),\s*(\d+)\)", rest):
        npcdef_id = int(m.group(1))
        amount_str = m.group(2)
        item_id = int(m.group(3))
        weight = int(m.group(4))
        db_index = int(m.group(5))
        rows.append((npcdef_id, amount_str, item_id, weight, db_index))

    # Find where the INSERT block ends (the final semicolon after last row)
    last_row_match = None
    for m in re.finditer(r"\([^)]+\)", rest):
        last_row_match = m
    if last_row_match:
        # After the last row, find the semicolon
        after_rows = rest[last_row_match.end():]
        semi_pos = after_rows.find(';')
        if semi_pos >= 0:
            footer_start = header_end + last_row_match.end() + semi_pos + 1
        else:
            footer_start = len(content)
    else:
        footer_start = len(content)

    footer = content[footer_start:]

    return header, rows, footer


def sql_drops_for_npc(rows, npc_id):
    """Extract drops for a specific NPC from parsed rows."""
    return [(item_id, int(amount_str), weight)
            for npcdef_id, amount_str, item_id, weight, _ in rows
            if npcdef_id == npc_id]


def write_update_sql(header, rows, footer):
    """Write the updated SQL file."""
    # Reassign db_index sequentially
    lines = []
    for i, (npcdef_id, amount_str, item_id, weight, _) in enumerate(rows):
        db_index = i + 1
        lines.append(f"({npcdef_id}, '{amount_str}', {item_id}, {weight}, {db_index})")

    # Join with commas and newlines, ending with semicolon
    values_block = ",\n".join(lines) + ";"

    # Update the AUTO_INCREMENT value in footer
    max_db_index = len(rows)
    footer_updated = re.sub(
        r"AUTO_INCREMENT=\d+",
        f"AUTO_INCREMENT={max_db_index + 1}",
        footer
    )

    with open(UPDATE_SQL_PATH, "w") as f:
        f.write(header)
        f.write(values_block)
        f.write(footer_updated)


# ═══════════════════════════════════════════════════════════════════════════
# Java parsing (reuses compare_drops functions)
# ═══════════════════════════════════════════════════════════════════════════

def parse_java_drops(item_name_to_id, npc_name_to_id):
    """
    Parse NpcDrops.java — same as compare_drops.parse_java but with explicit path.
    Returns: (npc_drops, bone_sets, table_refs, sub_tables)
    """
    with open(JAVA_PATH, "r") as f:
        content = f.read()

    sub_tables = parse_sub_tables(content, item_name_to_id)

    def resolve_item(token):
        m = re.match(r'ItemId\.([A-Z_0-9]+)\.id\(\)', token.strip())
        if m:
            return item_name_to_id.get(m.group(1))
        try:
            return int(token.strip())
        except ValueError:
            return None

    def resolve_npc(token):
        m = re.match(r'NpcId\.([A-Z_0-9]+)\.id\(\)', token.strip())
        if m:
            return npc_name_to_id.get(m.group(1))
        try:
            return int(token.strip())
        except ValueError:
            return None

    # Parse bone sets
    bone_sets = {}
    for m in re.finditer(r'this\.bonelessNpcs\.add\(NpcId\.([A-Z_0-9]+)\.id\(\)\)', content):
        nid = npc_name_to_id.get(m.group(1))
        if nid is not None:
            bone_sets[nid] = "boneless"
    for m in re.finditer(r'this\.batBonedNpcs\.add\(NpcId\.([A-Z_0-9]+)\.id\(\)\)', content):
        nid = npc_name_to_id.get(m.group(1))
        if nid is not None:
            bone_sets[nid] = "bat_bones"
    for m in re.finditer(r'this\.bigBoneNpcs\.add\(NpcId\.([A-Z_0-9]+)\.id\(\)\)', content):
        nid = npc_name_to_id.get(m.group(1))
        if nid is not None:
            bone_sets[nid] = "big_bones"
    for m in re.finditer(r'this\.dragonNpcs\.add\(NpcId\.([A-Z_0-9]+)\.id\(\)\)', content):
        nid = npc_name_to_id.get(m.group(1))
        if nid is not None:
            bone_sets[nid] = "dragon_bones"
    for m in re.finditer(r'this\.ashesNpcs\.add\(NpcId\.([A-Z_0-9]+)\.id\(\)\)', content):
        nid = npc_name_to_id.get(m.group(1))
        if nid is not None:
            bone_sets[nid] = "ashes"

    # Parse generateNpcDrops()
    method_match = re.search(r'private void generateNpcDrops\(\)\s*\{', content)
    if not method_match:
        print(f"{RED}ERROR: Could not find generateNpcDrops() method{RESET}")
        sys.exit(1)

    start = method_match.end()
    depth = 1
    pos = start
    while pos < len(content) and depth > 0:
        if content[pos] == '{':
            depth += 1
        elif content[pos] == '}':
            depth -= 1
        pos += 1
    method_body = content[start:pos - 1]

    cleaned = _remove_openpk_blocks(method_body)

    npc_drops = {}
    table_refs = {}
    current_drops = []
    current_tables = []
    assigned_npc_ids = []

    def finalize():
        for nid in assigned_npc_ids:
            npc_drops[nid] = list(current_drops)
            if current_tables:
                table_refs[nid] = list(current_tables)

    for line in cleaned.split('\n'):
        line = line.strip()
        if not line or line.startswith('//'):
            continue

        m = re.match(r'currentNpcDrops\s*=\s*new\s+DropTable\(', line)
        if m:
            finalize()
            current_drops = []
            current_tables = []
            assigned_npc_ids = []
            continue

        m = re.match(r'currentNpcDrops\s*=\s*currentNpcDrops\.clone\(', line)
        if m:
            finalize()
            current_drops = list(current_drops)
            current_tables = list(current_tables)
            assigned_npc_ids = []
            continue

        m = re.match(r'currentNpcDrops\.addItemDrop\((.+?),\s*(.+?),\s*(\d+)', line)
        if m:
            item_id = resolve_item(m.group(1))
            amount_str = m.group(2).strip()
            ternary = re.match(r'config\.WANT_OPENPK_POINTS\s*\?\s*\d+\s*:\s*(\d+)', amount_str)
            if ternary:
                amount = int(ternary.group(1))
            else:
                try:
                    amount = int(amount_str)
                except ValueError:
                    amount = 1
            weight = int(m.group(3))
            if item_id is not None:
                current_drops.append((item_id, amount, weight))
            continue

        m = re.match(r'currentNpcDrops\.addEmptyDrop\(', line)
        if m:
            total = sum(w for _, _, w in current_drops) + sum(w for _, w in current_tables)
            empty_weight = 128 - total
            if empty_weight > 0:
                current_drops.append((-1, -1, empty_weight))
            continue

        m = re.match(r'currentNpcDrops\.addTableDrop\((\w+),\s*(\d+)\)', line)
        if m:
            table_var = m.group(1)
            weight = int(m.group(2))
            current_tables.append((table_var, weight))
            continue

        m = re.match(r'this\.npcDrops\.put\((.+?),\s*currentNpcDrops\)', line)
        if m:
            npc_id = resolve_npc(m.group(1))
            if npc_id is not None:
                assigned_npc_ids.append(npc_id)
            continue

    finalize()

    return npc_drops, bone_sets, table_refs, sub_tables


# ═══════════════════════════════════════════════════════════════════════════
# Java -> SQL conversion
# ═══════════════════════════════════════════════════════════════════════════

def _lcm(a, b):
    """Least common multiple of two integers."""
    return abs(a * b) // math.gcd(a, b)


def _collect_subtable_total_weights(table_var, sub_tables, visited=None):
    """
    Recursively collect the total_weight denominators from a sub-table and
    any nested sub-tables it references.
    Returns a list of integer total weights.
    """
    if visited is None:
        visited = set()
    if table_var in visited or table_var not in sub_tables:
        return []
    visited = visited | {table_var}

    st = sub_tables[table_var]
    totals = [st.total_weight]
    for ref_name, _ in st.table_refs:
        totals.extend(_collect_subtable_total_weights(ref_name, sub_tables, visited))
    return totals


def _expand_subtable_exact(table_var, sub_tables, ref_weight_numer, ref_weight_denom, visited=None):
    """
    Recursively expand a sub-table into (item_id, amount, weight_fraction) tuples
    where weight_fraction is a Fraction representing the exact effective weight
    relative to the NPC's top-level drop table.

    ref_weight_numer / ref_weight_denom is the fraction of the parent table's
    weight that selects this sub-table.
    """
    if visited is None:
        visited = set()
    if table_var in visited or table_var not in sub_tables:
        return [], Fraction(0)
    visited = visited | {table_var}

    st = sub_tables[table_var]
    st_total = st.total_weight
    if st_total <= 0:
        return [], Fraction(0)

    ref_frac = Fraction(ref_weight_numer, ref_weight_denom)
    result = []

    # Direct items in this sub-table
    for item_id, amount, weight in st.items:
        if item_id == -1:
            continue
        eff = ref_frac * Fraction(weight, st_total)
        result.append((item_id, amount, eff))

    # Nested sub-table references
    for nested_var, nested_weight in st.table_refs:
        # The nested table gets (ref_frac * nested_weight / st_total) of the parent
        nested_items, _ = _expand_subtable_exact(
            nested_var, sub_tables,
            ref_weight_numer * nested_weight,
            ref_weight_denom * st_total,
            visited
        )
        result.extend(nested_items)

    # Calculate empty weight within this sub-table
    items_weight_sum = sum(w for _, _, w in st.items) + sum(w for _, w in st.table_refs)
    empty_in_table = st_total - items_weight_sum if st_total > items_weight_sum else st.empty_weight
    empty_frac = Fraction(0)
    if empty_in_table > 0:
        empty_frac = ref_frac * Fraction(empty_in_table, st_total)

    return result, empty_frac


def java_to_sql_rows(npc_id, java_drops_list, table_refs_list, sub_tables, bone_sets):
    """
    Convert Java drops for a single NPC into SQL row tuples.
    Returns list of (npcdef_id, amount_str, item_id, weight) — without db_index.

    Weight conversion uses exact fractions to preserve sub-table ratios:
    - All weights (direct and expanded) are computed as exact Fractions
    - A common scale factor (LCM of all sub-table denominators) is applied
      to make all weights integers
    - All weights are then divided by their GCD to get the smallest integers
    - Guaranteed drops (weight=0) and bone drops are unaffected by scaling
    """
    guaranteed = []   # (npcdef_id, amount_str, item_id, 0)
    weighted = []     # (npcdef_id, amount_str, item_id, Fraction)

    # Add bone drop first (weight=0, guaranteed)
    bone_type = bone_sets.get(npc_id, "normal_bones")
    if bone_type != "boneless":
        bone_item_id = BONE_TYPE_TO_ITEM.get(bone_type)
        if bone_item_id is not None:
            guaranteed.append((npc_id, '1', bone_item_id, 0))

    # Process direct drops
    for item_id, amount, weight in java_drops_list:
        if weight == 0:
            # Guaranteed drop
            if item_id != -1:
                guaranteed.append((npc_id, str(amount), item_id, 0))
        elif item_id == -1:
            # Nothing/empty drop — exact integer weight
            weighted.append((npc_id, '-1', -1, Fraction(weight)))
        else:
            weighted.append((npc_id, str(amount), item_id, Fraction(weight)))

    # Process sub-table references — expand into individual items with exact fractions
    nothing_frac = Fraction(0)
    if table_refs_list:
        for table_var, ref_weight in table_refs_list:
            if table_var not in sub_tables:
                continue

            expanded, empty_frac = _expand_subtable_exact(
                table_var, sub_tables, ref_weight, 1
            )

            for item_id, amount, eff_frac in expanded:
                weighted.append((npc_id, str(amount), item_id, eff_frac))

            nothing_frac += empty_frac

    if nothing_frac > 0:
        weighted.append((npc_id, '-1', -1, nothing_frac))

    # Now scale all fractional weights to integers.
    # Find the LCM of all denominators to clear fractions.
    if weighted:
        common_denom = 1
        for _, _, _, frac in weighted:
            common_denom = _lcm(common_denom, frac.denominator)

        # Scale all weights by common_denom to get integers
        int_weights = []
        for npcdef_id, amount_str, item_id, frac in weighted:
            w = frac * common_denom
            assert w.denominator == 1, f"Weight {frac} * {common_denom} = {w} is not integer"
            int_weights.append((npcdef_id, amount_str, item_id, int(w)))

        # Simplify by dividing all by their GCD
        all_w = [w for _, _, _, w in int_weights if w > 0]
        if all_w:
            overall_gcd = all_w[0]
            for w in all_w[1:]:
                overall_gcd = math.gcd(overall_gcd, w)
            if overall_gcd > 1:
                int_weights = [
                    (n, a, i, w // overall_gcd) for n, a, i, w in int_weights
                ]

        # Merge duplicate nothing entries
        nothing_weight = 0
        other_rows = []
        for row in int_weights:
            if row[2] == -1 and row[1] == '-1':
                nothing_weight += row[3]
            else:
                other_rows.append(row)

        if nothing_weight > 0:
            other_rows.append((npc_id, '-1', -1, nothing_weight))

        return guaranteed + other_rows
    else:
        return guaranteed


# ═══════════════════════════════════════════════════════════════════════════
# Review status tracking
# ═══════════════════════════════════════════════════════════════════════════

def load_status():
    """Load review status from JSON file."""
    if os.path.exists(STATUS_FILE):
        with open(STATUS_FILE, "r") as f:
            return json.load(f)
    return {"reviewed": {}}


def save_status(status):
    """Save review status to JSON file."""
    with open(STATUS_FILE, "w") as f:
        json.dump(status, f, indent=2)


def record_review(npc_id, decision):
    """Record a review decision for an NPC."""
    status = load_status()
    status["reviewed"][str(npc_id)] = decision
    save_status(status)


# ═══════════════════════════════════════════════════════════════════════════
# NPC resolution helper
# ═══════════════════════════════════════════════════════════════════════════

def resolve_npc_query(query, npc_name_to_id, npc_id_to_name):
    """Resolve a user query to an NPC ID. Returns (npc_id, npc_name) or exits."""
    try:
        npc_id = int(query)
        npc_name = npc_id_to_name.get(npc_id, "Unknown")
        return npc_id, npc_name
    except ValueError:
        pass

    query_lower = query.lower().replace(" ", "_")
    matches = []
    for name, nid in npc_name_to_id.items():
        if query_lower in name.lower():
            matches.append((name, nid))

    if len(matches) == 1:
        return matches[0][1], matches[0][0]
    elif len(matches) > 1:
        print(f"\n{YELLOW}Multiple NPCs match '{query}':{RESET}")
        for name, nid in sorted(matches, key=lambda x: x[1]):
            print(f"  {nid:>5}  {name}")
        print(f"\nPlease be more specific or use the numeric ID.")
        sys.exit(1)
    else:
        print(f"\n{RED}No NPC found matching '{query}'{RESET}")
        sys.exit(1)


# ═══════════════════════════════════════════════════════════════════════════
# Commands
# ═══════════════════════════════════════════════════════════════════════════

def item_str(item_id, amount, item_id_to_name):
    """Format item display string."""
    if item_id == -1:
        return "Nothing (empty)"
    name = item_id_to_name.get(item_id, f"Item#{item_id}")
    if amount is not None and amount != 1:
        return f"{name} x{amount}"
    return name


def cmd_show(query, npc_name_to_id, npc_id_to_name, item_id_to_name,
             java_drops, java_bone_sets, java_tables, sub_tables):
    """Show drops for an NPC from both the update SQL and Java side by side."""
    npc_id, npc_name = resolve_npc_query(query, npc_name_to_id, npc_id_to_name)

    header, rows, footer = parse_update_sql()
    sql_drops = sql_drops_for_npc(rows, npc_id)
    has_sql = len(sql_drops) > 0
    has_java = npc_id in java_drops

    print(f"\n{BOLD}NPC #{npc_id}: {npc_name}{RESET}")
    print(f"{'=' * 70}")

    if not has_sql and not has_java:
        print(f"  {DIM}No drop data found in either source.{RESET}")
        return

    # Status info
    status_data = load_status()
    review = status_data.get("reviewed", {}).get(str(npc_id))
    if review:
        print(f"  Review status: {GREEN}{review}{RESET}")
    else:
        print(f"  Review status: {YELLOW}Not reviewed{RESET}")

    # Bone info
    java_bone = java_bone_sets.get(npc_id, "normal_bones")
    bone_name_map = {
        "boneless": "No bones",
        "bat_bones": "Bat Bones (814)",
        "big_bones": "Big Bones (466)",
        "dragon_bones": "Dragon Bones (274)",
        "ashes": "Ashes (23)",
        "normal_bones": "Bones (20)",
    }

    sql_bone_items = set()
    for iid, amt, w in sql_drops:
        if iid in BONE_ITEM_IDS:
            sql_bone_items.add(iid)

    sql_bone_str = ", ".join(BONE_ITEM_NAMES.get(b, str(b)) for b in sorted(sql_bone_items)) if sql_bone_items else "None"
    java_bone_str = bone_name_map.get(java_bone, java_bone)

    print(f"\n  {BOLD}Bone Type:{RESET}")
    print(f"    Update SQL: {sql_bone_str}")
    print(f"    Java:       {java_bone_str}")

    # Sub-table references
    TABLE_DISPLAY = {
        "herbDropTable": "[Herb Table]",
        "rareDropTable": "[Rare Table]",
        "megaRareDropTable": "[Mega Rare Table]",
        "ultraRareDropTable": "[Ultra Rare Table]",
        "kbdTableCustom": "[KBD Custom Table]",
    }
    npc_table_list = java_tables.get(npc_id, [])
    if npc_table_list:
        print(f"\n  {BOLD}Java Sub-Table References:{RESET}")
        for tvar, tw in npc_table_list:
            display = TABLE_DISPLAY.get(tvar, f'[{tvar}]')
            print(f"    {display}: weight {tw}")

    # Expand Java drops
    java_drop_list = java_drops.get(npc_id, [])
    expanded_drops, table_expanded = expand_npc_tables(
        npc_id, java_drop_list, npc_table_list, sub_tables
    )

    # Normalize both
    sql_guar, sql_probs, sql_tw, _ = normalize_drops(sql_drops)
    java_guar, java_probs, java_tw, _ = normalize_drops_expanded(expanded_drops)

    # Guaranteed drops
    sql_guar_set = set(sql_guar)
    java_guar_set = set(java_guar)
    all_guar = sorted(sql_guar_set | java_guar_set, key=lambda x: (x[0], x[1]))

    if all_guar:
        print(f"\n  {BOLD}Guaranteed Drops (weight=0):{RESET}")
        print(f"    {'Item':<35} {'SQL':>6} {'Java':>6}")
        print(f"    {'---':<35} {'---':>6} {'---':>6}")
        for key in all_guar:
            in_sql = key in sql_guar_set
            in_java = key in java_guar_set
            iid, amt = key
            name = item_str(iid, amt, item_id_to_name)
            sql_mark = f"{GREEN}  YES{RESET}" if in_sql else f"{RED}   NO{RESET}"
            java_mark = f"{GREEN}  YES{RESET}" if in_java else f"{RED}   NO{RESET}"
            print(f"    {name:<35} {sql_mark} {java_mark}")

    # Weighted drops side by side
    all_keys = set(sql_probs.keys()) | set(java_probs.keys())
    sorted_keys = sorted(all_keys, key=lambda k: max(sql_probs.get(k, 0), java_probs.get(k, 0)), reverse=True)

    if sorted_keys:
        print(f"\n  {BOLD}Weighted Drops:{RESET}")
        print(f"    SQL total weight: {sql_tw}   Java total weight: {java_tw:.1f}")
        print(f"    {'Item':<35} {'SQL %':>8} {'Java %':>8} {'Diff':>8}")
        print(f"    {'---':<35} {'---':>8} {'---':>8} {'---':>8}")
        for key in sorted_keys:
            iid, amt = key
            sp = sql_probs.get(key, 0.0)
            jp = java_probs.get(key, 0.0)
            diff = abs(sp - jp)
            name = item_str(iid, amt, item_id_to_name)
            if len(name) > 35:
                name = name[:32] + "..."

            sp_str = f"{sp:>7.2f}%" if sp > 0 else f"{'---':>8}"
            jp_str = f"{jp:>7.2f}%" if jp > 0 else f"{'---':>8}"

            if diff <= 1.0:
                color = GREEN
            elif diff <= 5.0:
                color = YELLOW
            else:
                color = RED
            print(f"    {name:<35} {sp_str} {jp_str} {color}{diff:>+7.2f}%{RESET}")

    print()


def cmd_use_java(query, npc_name_to_id, npc_id_to_name, item_id_to_name,
                 java_drops, java_bone_sets, java_tables, sub_tables,
                 f2p_flags, allow_f2p=False):
    """Replace NPC's SQL drops with converted Java drops."""
    npc_id, npc_name = resolve_npc_query(query, npc_name_to_id, npc_id_to_name)

    if npc_id not in java_drops:
        print(f"\n{RED}NPC #{npc_id} ({npc_name}) has no drops in Java.{RESET}")
        sys.exit(1)

    assert_p2p_or_allowed(npc_id, npc_name, f2p_flags, allow_f2p)

    header, rows, footer = parse_update_sql()

    # Remove existing rows for this NPC
    rows = [(n, a, i, w, d) for n, a, i, w, d in rows if n != npc_id]

    # Generate new rows from Java
    java_drop_list = java_drops[npc_id]
    table_list = java_tables.get(npc_id, [])
    new_rows = java_to_sql_rows(npc_id, java_drop_list, table_list, sub_tables, java_bone_sets)

    # Add new rows (db_index will be reassigned)
    for npcdef_id, amount_str, item_id, weight in new_rows:
        rows.append((npcdef_id, amount_str, item_id, weight, 0))

    # Sort rows by npcdef_id, then by weight (guaranteed first), then item_id
    rows.sort(key=lambda r: (r[0], 0 if r[3] == 0 else 1, r[3], r[2]))

    write_update_sql(header, rows, footer)

    # Record review
    record_review(npc_id, "use-java")

    # Show what was added
    print(f"\n{GREEN}Replaced drops for NPC #{npc_id} ({npc_name}) with Java data.{RESET}")
    print(f"  Added {len(new_rows)} drop entries from Java.")

    # Show the new drops
    for npcdef_id, amount_str, item_id, weight in new_rows:
        name = item_str(item_id, int(amount_str), item_id_to_name)
        wtype = "guaranteed" if weight == 0 else f"weight={weight}"
        print(f"    {name:<40} ({wtype})")
    print()


def cmd_use_sql(query, npc_name_to_id, npc_id_to_name):
    """Mark NPC as 'keep SQL' — no file changes, just records decision."""
    npc_id, npc_name = resolve_npc_query(query, npc_name_to_id, npc_id_to_name)

    record_review(npc_id, "use-sql")
    print(f"\n{GREEN}Marked NPC #{npc_id} ({npc_name}) as 'keep SQL'. No changes made.{RESET}\n")


def cmd_add_java(query, npc_name_to_id, npc_id_to_name, item_id_to_name,
                 java_drops, java_bone_sets, java_tables, sub_tables,
                 f2p_flags, allow_f2p=False):
    """Add Java drops for an NPC that doesn't exist in the SQL file."""
    npc_id, npc_name = resolve_npc_query(query, npc_name_to_id, npc_id_to_name)

    if npc_id not in java_drops:
        print(f"\n{RED}NPC #{npc_id} ({npc_name}) has no drops in Java.{RESET}")
        sys.exit(1)

    assert_p2p_or_allowed(npc_id, npc_name, f2p_flags, allow_f2p)

    header, rows, footer = parse_update_sql()

    # Check if NPC already exists
    existing = [r for r in rows if r[0] == npc_id]
    if existing:
        print(f"\n{YELLOW}NPC #{npc_id} ({npc_name}) already has {len(existing)} drops in SQL.{RESET}")
        print(f"Use 'use-java' instead to replace them.")
        sys.exit(1)

    # Generate new rows from Java
    java_drop_list = java_drops[npc_id]
    table_list = java_tables.get(npc_id, [])
    new_rows = java_to_sql_rows(npc_id, java_drop_list, table_list, sub_tables, java_bone_sets)

    # Add new rows
    for npcdef_id, amount_str, item_id, weight in new_rows:
        rows.append((npcdef_id, amount_str, item_id, weight, 0))

    # Sort rows
    rows.sort(key=lambda r: (r[0], 0 if r[3] == 0 else 1, r[3], r[2]))

    write_update_sql(header, rows, footer)

    record_review(npc_id, "add-java")

    print(f"\n{GREEN}Added drops for NPC #{npc_id} ({npc_name}) from Java.{RESET}")
    print(f"  Added {len(new_rows)} drop entries.")
    for npcdef_id, amount_str, item_id, weight in new_rows:
        name = item_str(item_id, int(amount_str), item_id_to_name)
        wtype = "guaranteed" if weight == 0 else f"weight={weight}"
        print(f"    {name:<40} ({wtype})")
    print()


def cmd_status(npc_id_to_name, java_drops, sql_npc_ids):
    """Show review progress."""
    status = load_status()
    reviewed = status.get("reviewed", {})

    # All NPCs that need review = union of SQL and Java NPCs
    all_npcs = sql_npc_ids | set(java_drops.keys())

    use_java = []
    use_sql = []
    add_java = []

    for npc_id_str, decision in reviewed.items():
        npc_id = int(npc_id_str)
        npc_name = npc_id_to_name.get(npc_id, "Unknown")
        entry = (npc_id, npc_name)
        if decision == "use-java":
            use_java.append(entry)
        elif decision == "use-sql":
            use_sql.append(entry)
        elif decision == "add-java":
            add_java.append(entry)

    reviewed_ids = set(int(k) for k in reviewed.keys())
    pending = all_npcs - reviewed_ids

    print(f"\n{BOLD}Review Progress{RESET}")
    print(f"{'=' * 70}")
    print(f"  Total NPCs:     {len(all_npcs)}")
    print(f"  Reviewed:        {len(reviewed)} ({len(reviewed)/max(len(all_npcs),1)*100:.1f}%)")
    print(f"  Pending:         {len(pending)}")
    print()
    print(f"  {GREEN}Use Java:{RESET}  {len(use_java)}")
    for npc_id, name in sorted(use_java):
        print(f"    {npc_id:>5}  {name}")
    print(f"  {CYAN}Keep SQL:{RESET}  {len(use_sql)}")
    for npc_id, name in sorted(use_sql):
        print(f"    {npc_id:>5}  {name}")
    print(f"  {YELLOW}Add Java:{RESET} {len(add_java)}")
    for npc_id, name in sorted(add_java):
        print(f"    {npc_id:>5}  {name}")
    print()


def cmd_pending(npc_id_to_name, item_id_to_name,
                java_drops, java_bone_sets, java_tables, sub_tables, sql_npc_ids, sql_rows):
    """List NPCs that still need review, sorted by difference."""
    status = load_status()
    reviewed_ids = set(int(k) for k in status.get("reviewed", {}).keys())

    all_npcs = sql_npc_ids | set(java_drops.keys())
    pending_ids = all_npcs - reviewed_ids

    # Build SQL drops dict
    sql_data = defaultdict(list)
    for npcdef_id, amount_str, item_id, weight, _ in sql_rows:
        sql_data[npcdef_id].append((item_id, int(amount_str), weight))

    results = []
    for npc_id in pending_ids:
        npc_name = npc_id_to_name.get(npc_id, "Unknown")
        has_sql = npc_id in sql_data
        has_java = npc_id in java_drops

        if has_sql and has_java:
            s, max_diff = compare_npc(
                sql_data[npc_id], java_drops[npc_id],
                java_tables.get(npc_id), sub_tables
            )
            results.append((max_diff, s, npc_id, npc_name))
        elif has_java and not has_sql:
            results.append((999.0, "JAVA_ONLY", npc_id, npc_name))
        elif has_sql and not has_java:
            # Only show SQL-only if they have non-bone drops
            non_bone = [d for d in sql_data[npc_id]
                        if d[0] not in BONE_ITEM_IDS and not (d[0] == -1 and d[1] == -1)]
            if non_bone:
                results.append((0.0, "SQL_ONLY", npc_id, npc_name))

    # Sort by difference descending (biggest first)
    results.sort(key=lambda x: (-x[0], x[2]))

    print(f"\n{BOLD}Pending NPCs ({len(results)} remaining){RESET}")
    print(f"{'=' * 70}")
    print(f"  {'ID':>5}  {'Status':<12}  {'Max Diff':>9}  {'NPC Name'}")
    print(f"  {'---':>5}  {'---':<12}  {'---':>9}  {'---'}")

    for max_diff, status_str, npc_id, npc_name in results:
        if status_str == "DIFFERENT":
            color = RED
        elif status_str == "SIMILAR":
            color = YELLOW
        elif status_str == "MATCH":
            color = GREEN
        elif status_str == "JAVA_ONLY":
            color = CYAN
        else:
            color = ""
        diff_str = f"{max_diff:>7.1f}%" if status_str not in ("SQL_ONLY", "JAVA_ONLY") else "     N/A"
        print(f"  {npc_id:>5}  {color}{status_str:<12}{RESET}  {diff_str}  {npc_name}")

    print(f"\n  Total pending: {len(results)}")
    print()


def cmd_bulk_p2p(npc_id_to_name, item_id_to_name,
                 java_drops, java_bone_sets, java_tables, sub_tables,
                 f2p_flags, dry_run=False):
    """
    Run add-java / use-java for every P2P NPC that has a Java drop table.
    - If the NPC has no SQL rows yet → add-java
    - If the NPC already has SQL rows → use-java (replace)
    - F2P NPCs are skipped (no --allow-f2p override here; use the per-NPC commands for that)
    - NPCs not present in pk_npcdef.sql are skipped with a warning
    Writes everything in a single pass to UPDATE_SQL_PATH.
    """
    header, rows, footer = parse_update_sql()
    sql_npc_ids = set(r[0] for r in rows)

    p2p_candidates = []
    skipped_f2p = []
    skipped_unknown = []
    for npc_id, drop_list in java_drops.items():
        flag = f2p_flags.get(npc_id)
        if flag is None:
            skipped_unknown.append(npc_id)
            continue
        if flag == 1:
            skipped_f2p.append(npc_id)
            continue
        p2p_candidates.append(npc_id)

    p2p_candidates.sort()

    print(f"\n{BOLD}Bulk P2P conversion plan{RESET}")
    print(f"{'=' * 70}")
    print(f"  Java NPCs total:         {len(java_drops)}")
    print(f"  Skipped (F2P):           {len(skipped_f2p)}")
    print(f"  Skipped (not in npcdef): {len(skipped_unknown)}")
    print(f"  P2P NPCs to convert:     {len(p2p_candidates)}")
    print(f"  Target SQL file:         {UPDATE_SQL_PATH}")
    if dry_run:
        print(f"  Mode:                    {YELLOW}DRY RUN — no files written{RESET}")
    print()

    if skipped_unknown:
        print(f"  {YELLOW}NPCs not in pk_npcdef.sql (skipped):{RESET} {sorted(skipped_unknown)}")
        print()

    use_java_ids = [n for n in p2p_candidates if n in sql_npc_ids]
    add_java_ids = [n for n in p2p_candidates if n not in sql_npc_ids]

    print(f"  use-java (replace existing): {len(use_java_ids)} NPCs")
    print(f"  add-java (new to SQL):       {len(add_java_ids)} NPCs")
    print()

    # Build the new row set
    new_rows = [r for r in rows if r[0] not in set(p2p_candidates)]
    converted = []
    for npc_id in p2p_candidates:
        java_drop_list = java_drops[npc_id]
        table_list = java_tables.get(npc_id, [])
        rows_for_npc = java_to_sql_rows(npc_id, java_drop_list, table_list, sub_tables, java_bone_sets)
        for npcdef_id, amount_str, item_id, weight in rows_for_npc:
            new_rows.append((npcdef_id, amount_str, item_id, weight, 0))
        converted.append((npc_id, len(rows_for_npc)))

    new_rows.sort(key=lambda r: (r[0], 0 if r[3] == 0 else 1, r[3], r[2]))

    print(f"{BOLD}Per-NPC conversion summary:{RESET}")
    for npc_id, n in converted:
        name = npc_id_to_name.get(npc_id, "?")
        kind = "use-java" if npc_id in sql_npc_ids else "add-java"
        print(f"  {npc_id:>5}  {name:<40}  {kind}  ({n} rows)")
    print()

    if dry_run:
        print(f"{YELLOW}Dry run complete — no files written.{RESET}\n")
        return

    write_update_sql(header, new_rows, footer)

    status = load_status()
    for npc_id in p2p_candidates:
        decision = "use-java" if npc_id in sql_npc_ids else "add-java"
        status["reviewed"][str(npc_id)] = decision
    save_status(status)

    print(f"{GREEN}Wrote {len(new_rows)} total rows to {UPDATE_SQL_PATH}.{RESET}")
    print(f"{GREEN}Recorded {len(p2p_candidates)} review decisions.{RESET}\n")


# ═══════════════════════════════════════════════════════════════════════════
# Main
# ═══════════════════════════════════════════════════════════════════════════

def _extract_flag(argv, flag):
    """Remove --flag from argv (in place) and return True if it was present."""
    if flag in argv:
        argv.remove(flag)
        return True
    return False


def main():
    if len(sys.argv) < 2:
        print(__doc__)
        sys.exit(1)

    argv = list(sys.argv)
    allow_f2p = _extract_flag(argv, "--allow-f2p")
    dry_run = _extract_flag(argv, "--dry-run")

    cmd = argv[1].lower()

    # Load enums
    npc_name_to_id, npc_id_to_name, item_name_to_id, item_id_to_name = load_enums()

    # Parse Java drops
    java_drops, java_bone_sets, java_tables, sub_tables = parse_java_drops(
        item_name_to_id, npc_name_to_id
    )

    # Load F2P/P2P flags for the scope guard
    f2p_flags = load_npc_f2p_flags()

    if cmd == "show":
        if len(argv) < 3:
            print("Usage: python update_drops.py show <npc_id_or_name>")
            sys.exit(1)
        query = " ".join(argv[2:])
        cmd_show(query, npc_name_to_id, npc_id_to_name, item_id_to_name,
                 java_drops, java_bone_sets, java_tables, sub_tables)

    elif cmd == "use-java":
        if len(argv) < 3:
            print("Usage: python update_drops.py use-java <npc_id_or_name> [--allow-f2p]")
            sys.exit(1)
        query = " ".join(argv[2:])
        cmd_use_java(query, npc_name_to_id, npc_id_to_name, item_id_to_name,
                     java_drops, java_bone_sets, java_tables, sub_tables,
                     f2p_flags, allow_f2p=allow_f2p)

    elif cmd == "use-sql":
        if len(argv) < 3:
            print("Usage: python update_drops.py use-sql <npc_id_or_name>")
            sys.exit(1)
        query = " ".join(argv[2:])
        cmd_use_sql(query, npc_name_to_id, npc_id_to_name)

    elif cmd == "add-java":
        if len(argv) < 3:
            print("Usage: python update_drops.py add-java <npc_id_or_name> [--allow-f2p]")
            sys.exit(1)
        query = " ".join(argv[2:])
        cmd_add_java(query, npc_name_to_id, npc_id_to_name, item_id_to_name,
                     java_drops, java_bone_sets, java_tables, sub_tables,
                     f2p_flags, allow_f2p=allow_f2p)

    elif cmd == "status":
        header, rows, footer = parse_update_sql()
        sql_npc_ids = set(r[0] for r in rows)
        cmd_status(npc_id_to_name, java_drops, sql_npc_ids)

    elif cmd == "pending":
        header, rows, footer = parse_update_sql()
        sql_npc_ids = set(r[0] for r in rows)
        cmd_pending(npc_id_to_name, item_id_to_name,
                    java_drops, java_bone_sets, java_tables, sub_tables,
                    sql_npc_ids, rows)

    elif cmd == "bulk-p2p":
        cmd_bulk_p2p(npc_id_to_name, item_id_to_name,
                     java_drops, java_bone_sets, java_tables, sub_tables,
                     f2p_flags, dry_run=dry_run)

    else:
        print(f"Unknown command: {cmd}")
        print(__doc__)
        sys.exit(1)


if __name__ == "__main__":
    main()