#!/usr/bin/env python3
"""Generate dragon_drops_table.xlsx — proper Excel spreadsheet.

Columns: NPC | Combat Lvl | Item | Amount | Weight | Rate (1/x) | Percentage | Category

One row per (NPC, item) pair. NPCs grouped together with their dragon items
first, then other weighted drops, then guaranteed drops.
"""
import sys, os, re
sys.path.insert(0, '/Users/tomassimkus/VS/openrsc-develop')
os.chdir('/Users/tomassimkus/VS/openrsc-develop')

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

from update_drops import parse_update_sql
from compare_drops import load_enums
from collections import defaultdict

npc_name_to_id, npc_id_to_name, item_name_to_id, item_id_to_name = load_enums()
_, rows, _ = parse_update_sql()

sql_data = defaultdict(list)
for nid, amt, iid, w, _ in rows:
    sql_data[nid].append((iid, int(amt), w))


def parse_combat_levels():
    levels = {}
    with open('pk_npcdef.sql') as f:
        content = f.read()
    for ins in re.finditer(r"INSERT INTO `pk_npcdef`[^;]*?VALUES\s*(.+?);", content, re.DOTALL):
        body = ins.group(1)
        i, n = 0, len(body)
        while i < n:
            while i < n and body[i] in ' \t\n\r,':
                i += 1
            if i >= n or body[i] != '(':
                break
            depth, j, in_str = 1, i + 1, False
            while j < n and depth > 0:
                c = body[j]
                if in_str:
                    if c == '\\' and j + 1 < n:
                        j += 2; continue
                    if c == "'":
                        if j + 1 < n and body[j + 1] == "'":
                            j += 2; continue
                        in_str = False
                elif c == "'": in_str = True
                elif c == '(': depth += 1
                elif c == ')': depth -= 1
                j += 1
            tb = body[i + 1:j - 1]
            i = j
            cols, k, m, cur, in_str = [], 0, len(tb), [], False
            while k < m:
                c = tb[k]
                if in_str:
                    if c == '\\' and k + 1 < m:
                        cur.append(c); cur.append(tb[k+1]); k += 2; continue
                    if c == "'":
                        if k + 1 < m and tb[k+1] == "'":
                            cur.append("''"); k += 2; continue
                        in_str = False; cur.append(c)
                    else: cur.append(c)
                else:
                    if c == "'": in_str = True; cur.append(c)
                    elif c == ',': cols.append(''.join(cur).strip()); cur = []
                    else: cur.append(c)
                k += 1
            if cur: cols.append(''.join(cur).strip())
            if len(cols) >= 9:
                try:
                    nid = int(cols[0]); lvl = int(cols[8])
                    levels[nid] = lvl
                except ValueError:
                    pass
    return levels


combat_levels = parse_combat_levels()


def is_dragon_item(iid, iname):
    if iname == "DRAGONSTONE":
        return True
    if iid in (1276, 1277):
        return True
    if iname and "DRAGON" in iname and not iname.endswith("_BONES"):
        return True
    return False


npcs_with_dragon = set()
for nid, drops in sql_data.items():
    for iid, _, _ in drops:
        if is_dragon_item(iid, item_id_to_name.get(iid, "")):
            npcs_with_dragon.add(nid)
            break

PRIMARY = [477, 291, 290, 344, 201, 202, 184, 22, 181]
others = sorted(npcs_with_dragon - set(PRIMARY))
ORDER = PRIMARY + others


# Build workbook
wb = Workbook()
ws = wb.active
ws.title = "Dragon NPC Drops"

# Styling
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True)
dragon_fill = PatternFill(start_color="FFD966", end_color="FFD966", fill_type="solid")
guaranteed_fill = PatternFill(start_color="A9D08E", end_color="A9D08E", fill_type="solid")
empty_fill = PatternFill(start_color="D9D9D9", end_color="D9D9D9", fill_type="solid")
npc_separator_fill = PatternFill(start_color="305496", end_color="305496", fill_type="solid")
npc_separator_font = Font(color="FFFFFF", bold=True, size=12)
thin = Side(border_style="thin", color="BFBFBF")
border = Border(left=thin, right=thin, top=thin, bottom=thin)

headers = ["NPC", "Combat Lvl", "Item", "Amount", "Weight", "Rate (1/x)", "Percentage", "Category"]
for col, h in enumerate(headers, 1):
    c = ws.cell(row=1, column=col, value=h)
    c.fill = header_fill
    c.font = header_font
    c.alignment = Alignment(horizontal="center")
    c.border = border

ws.freeze_panes = "A2"

row_state = [2]

def get_row():
    return row_state[0]

def advance_row():
    row_state[0] += 1

for nid in ORDER:
    name = npc_id_to_name.get(nid, f"#{nid}")
    lvl = combat_levels.get(nid, "?")
    drops = sql_data.get(nid, [])
    if not drops:
        continue
    total_w = sum(w for _, _, w in drops if w > 0)

    # NPC separator row
    sep = ws.cell(row=get_row(), column=1,
                  value=f"{name}  (id={nid}, combat lvl {lvl}, total weight {total_w:,})")
    sep.fill = npc_separator_fill
    sep.font = npc_separator_font
    sep.alignment = Alignment(horizontal="left")
    ws.merge_cells(start_row=get_row(), start_column=1,
                   end_row=get_row(), end_column=len(headers))
    for col in range(1, len(headers) + 1):
        ws.cell(row=get_row(), column=col).fill = npc_separator_fill
    advance_row()

    # Partition drops
    guaranteed = []
    dragon = []
    other = []
    empty_w = 0
    for iid, amt, weight in drops:
        iname = item_id_to_name.get(iid, f"Item#{iid}")
        if iid == -1 and amt == -1:
            empty_w += weight
            continue
        if weight == 0:
            guaranteed.append((iid, amt, iname))
        elif is_dragon_item(iid, iname):
            dragon.append((iid, amt, weight, iname))
        else:
            other.append((iid, amt, weight, iname))

    # Aggregate same (item, amount)
    def aggregate(items):
        m = defaultdict(int)
        for iid, amt, weight, iname in items:
            m[(iid, amt, iname)] += weight
        return [(iid, amt, w, iname) for (iid, amt, iname), w in
                sorted(m.items(), key=lambda x: -x[1])]

    dragon_agg = aggregate(dragon)
    other_agg = aggregate(other)

    def write_drop_row(iname, amt, weight, total_w, category, fill=None):
        if weight == 0:
            rate_str = "guaranteed"
            pct = 100.0
        else:
            rate_str = f"1/{round(total_w/weight):,}"
            pct = weight / total_w * 100.0
        vals = [name, lvl, iname, amt, weight, rate_str, pct / 100.0, category]
        r = get_row()
        for col, v in enumerate(vals, 1):
            c = ws.cell(row=r, column=col, value=v)
            c.border = border
            if fill:
                c.fill = fill
            if col == 7:
                c.number_format = "0.0000%"
            if col in (4, 5):
                c.alignment = Alignment(horizontal="right")
        advance_row()

    # Dragon items first
    for iid, amt, w, iname in dragon_agg:
        write_drop_row(iname, amt, w, total_w, "DRAGON", dragon_fill)

    # Other weighted drops
    for iid, amt, w, iname in other_agg:
        write_drop_row(iname, amt, w, total_w, "other weighted")

    # Guaranteed
    for iid, amt, iname in guaranteed:
        note = " [= Dragon Bones in-game]" if iid == 274 else ""
        write_drop_row(iname + note, amt, 0, total_w, "guaranteed", guaranteed_fill)

    # Empty
    if empty_w > 0:
        write_drop_row("(Nothing — empty drop)", "—", empty_w, total_w, "empty", empty_fill)


# Column widths
widths = {1: 28, 2: 10, 3: 42, 4: 8, 5: 14, 6: 14, 7: 12, 8: 18}
for col, w in widths.items():
    ws.column_dimensions[get_column_letter(col)].width = w

out_path = os.path.join(os.getcwd(), "dragon_drops_table.xlsx")
wb.save(out_path)
print(f"Wrote {get_row() - 1} rows to {out_path}")
print(f"NPCs covered: {len(ORDER)}")