"""Additional factual catalogs from the retained Classic snapshot; no narrative text."""
import collections,re

def extract(connection, quests, items, npcs, clean):
    iby={i['id']:i for i in items}; nby={n['id']:n for n in npcs}
    object_quests=collections.defaultdict(dict)
    for q in quests:
        for group in ('startSources','endSources'):
            for source in q.get(group,[]):
                if source['type']=='Object':object_quests[source['id']][q['id']]=q
        for target in q.get('objectives',[]):
            if target['type']=='Object':object_quests[target['id']][q['id']]=q
    spawned={r[0] for r in connection.execute('SELECT DISTINCT id FROM gameobject')}
    loot=collections.defaultdict(set)
    for row in connection.execute('SELECT entry,item FROM gameobject_loot_template WHERE item>0 AND mincountOrRef>=0'):
        if row['item'] in iby:loot[row['entry']].add(row['item'])
    objects=[]; item_objects=collections.defaultdict(list)
    types={2:'Quest objects',3:'Containers & gathering',10:'Interactive objects',25:'Fishing holes'}
    for raw in connection.execute('SELECT * FROM gameobject_template'):
        row=dict(raw);oid=row['entry']
        if not clean(row['name']) or re.search(r'\btest\b|^\(OLD\)',row['name'],re.I):continue
        if oid not in object_quests and not (oid in spawned and row['type'] in types):continue
        linked=list(object_quests[oid].values())
        record={'id':oid,'name':row['name'],'type':types.get(row['type'],'Quest objects'),'category':types.get(row['type'],'Quest objects'),'zones':list(dict.fromkeys(q['zone'] for q in linked)),'questLinks':[{'name':q['name'],'recordUrl':q['recordUrl']} for q in linked],'itemIds':sorted(loot[row['data1']]) if row['type']==3 else [],'recordUrl':f'/objects/{oid}/','sourceUrl':f'https://www.wowhead.com/classic/object={oid}','verificationStatus':'classic-baseline'}
        objects.append(record)
        for iid in record['itemIds']:item_objects[iid].append(record)
    rawspells={r['Id']:dict(r) for r in connection.execute('SELECT * FROM spell_template')}
    trainers=collections.defaultdict(set);levels=collections.defaultdict(list)
    for row in connection.execute('SELECT entry,spell,reqlevel FROM npc_trainer WHERE reqlevel<=60'):
        if row['entry'] in nby and row['spell']>0:
            trainers[row['spell']].add(row['entry']);levels[row['spell']].append(row['reqlevel'])
    template_npcs=collections.defaultdict(set)
    for row in connection.execute('SELECT Entry,TrainerTemplateId FROM creature_template WHERE TrainerTemplateId>0'):
        if row['Entry'] in nby:template_npcs[row['TrainerTemplateId']].add(row['Entry'])
    for row in connection.execute('SELECT entry,spell,reqlevel FROM npc_trainer_template WHERE reqlevel<=60'):
        if template_npcs[row['entry']] and row['spell']>0:
            trainers[row['spell']].update(template_npcs[row['entry']]);levels[row['spell']].append(row['reqlevel'])
    recipes=collections.defaultdict(set)
    for row in connection.execute('SELECT * FROM item_template WHERE class=9'):
        if row['entry'] not in iby:continue
        for n in range(1,6):
            sid=row[f'spellid_{n}']
            if sid in rawspells and rawspells[sid]['SpellName']!='Learning':recipes[sid].add(row['entry'])
    starters={row[0] for row in connection.execute('SELECT Spell FROM playercreateinfo_spell') if row[0]>0}
    # Trainer/recipe rows can point at a learn-spell wrapper (effect 36).
    # Retain those factual effects explicitly, and attach player-facing facts
    # and source relationships to the actual spell learned.
    learned={sid:sorted({row[f'EffectTriggerSpell{n}'] for n in range(1,4)
        if row[f'Effect{n}']==36 and row[f'EffectTriggerSpell{n}'] in rawspells}) for sid,row in rawspells.items()}
    original_ids=set(trainers)|set(recipes)|starters
    for wrapper in list(original_ids):
        for target in learned[wrapper]:
            trainers[target].update(trainers[wrapper]);levels[target].extend(levels[wrapper]);recipes[target].update(recipes[wrapper])
    school={0:'Physical',1:'Holy',2:'Fire',3:'Nature',4:'Frost',5:'Shadow',6:'Arcane'}
    spells=[];item_spells=collections.defaultdict(list);npc_spells=collections.defaultdict(list)
    creation={sid for sid,row in rawspells.items() if any(row[f'Effect{n}']==24 and row[f'EffectItemType{n}'] in iby for n in range(1,4))}
    for sid in sorted(set(trainers)|set(recipes)|starters|creation):
        row=rawspells.get(sid)
        if not row or not clean(row['SpellName']) or re.search(r'\btest\b|^\(OLD\)',row['SpellName'],re.I) or row['IsServerSide']:continue
        outputs=list(dict.fromkeys(row[f'EffectItemType{n}'] for n in range(1,4) if row[f'Effect{n}']==24 and row[f'EffectItemType{n}'] in iby))
        reagents=[{'id':row[f'Reagent{n}'],'name':iby[row[f'Reagent{n}']]['name'] if row[f'Reagent{n}'] in iby else str(row[f'Reagent{n}']),'count':row[f'ReagentCount{n}']} for n in range(1,9) if row[f'Reagent{n}']>0 and row[f'ReagentCount{n}']>0]
        record={'id':sid,'name':('Learn: ' if learned[sid] else '')+row['SpellName']+(' ('+row['Rank1']+')' if row['Rank1'] else ''),'baseName':row['SpellName'],'rank':row['Rank1'],'category':'Learning effects' if learned[sid] else 'Crafting & conjuration' if outputs else 'Recipe spells' if recipes[sid] else 'Trained & starting spells','school':'Learning effect' if learned[sid] else school.get(row['School'],'Other'),'learnedSpellIds':learned[sid],'level':min(levels[sid]) if levels[sid] else row.get('SpellLevel',0),'trainerIds':sorted(trainers[sid]),'recipeItemIds':sorted(recipes[sid]),'reagents':reagents,'outputItemIds':outputs,'powerType':row['PowerType'],'manaCost':row['ManaCost'],'cooldownSeconds':row['RecoveryTime']/1000,'recordUrl':f'/spells/{sid}/','sourceUrl':f'https://www.wowhead.com/classic/spell={sid}','verificationStatus':'classic-baseline'}
        spells.append(record)
        for iid in {r['id'] for r in reagents}|set(outputs)|recipes[sid]:
            if iid in iby:item_spells[iid].append(record)
        for nid in record['trainerIds']:npc_spells[nid].append(record)
    return {'objects':objects,'spells':spells,'item_objects':item_objects,'item_spells':item_spells,'npc_spells':npc_spells}