gogobee/internal/plugin/adventure_character.go

package plugin

import (
	"database/sql"
	"errors"
	"strings"
	"time"

	"gogobee/internal/db"
	"maunium.net/go/mautrix/id"
)

// ── Equipment Slot Constants ─────────────────────────────────────────────────

type EquipmentSlot string

const (
	SlotWeapon EquipmentSlot = "weapon"
	SlotArmor  EquipmentSlot = "armor"
	SlotHelmet EquipmentSlot = "helmet"
	SlotBoots  EquipmentSlot = "boots"
	SlotTool   EquipmentSlot = "tool"
)

var allSlots = []EquipmentSlot{SlotWeapon, SlotArmor, SlotHelmet, SlotBoots, SlotTool}

// ── Core Types ───────────────────────────────────────────────────────────────

type AdventureCharacter struct {
	UserID                  id.UserID
	DisplayName             string
	CombatLevel             int
	MiningSkill             int
	ForagingSkill           int
	FishingSkill            int // v2
	CombatXP                int
	MiningXP                int
	ForagingXP              int
	FishingXP               int // v2
	Alive                   bool
	DeadUntil               *time.Time
	ActionTakenToday        bool
	HolidayActionTaken      bool
	CombatActionsUsed       int
	HarvestActionsUsed      int
	ArenaWins               int    // v2
	ArenaLosses             int    // v2
	InvasionScore           int    // v2
	Title                   string // v2
	CurrentStreak           int
	BestStreak              int
	LastActionDate          string
	GrudgeLocation          string
	CreatedAt               time.Time
	LastActiveAt            time.Time
	DeathReprieveLast       *time.Time
	MasterworkDropsReceived int
	RivalPool               int
	RivalUnlockedNotified   bool
	BabysitActive           bool
	BabysitExpiresAt        *time.Time
	BabysitSkillFocus       string
	HospitalVisits          int
	RobbieVisitCount        int
	LastDeathDate           string
	LastPardonUsed          *time.Time
	MistyLastSeen           *time.Time
	ArinaLastSeen           *time.Time
	MistyBuffExpires        *time.Time
	MistyDebuffExpires      *time.Time
	ArinaBuffExpires        *time.Time
	NPCMsgCount             int
	NPCMsgCountDate         string
	MistyRollTarget         int
	ArinaRollTarget         int
	// Housing
	HouseTier           int
	HouseLoanBalance    int
	HouseLoanFrozen     bool
	HouseMissedPayments int
	HouseAutopay        bool
	HouseCurrentRate    float64
	// Pets
	PetType               string
	PetName               string
	PetXP                 int
	PetLevel              int
	PetArmorTier          int
	PetChasedAway         bool
	PetReactivated        bool
	PetArrived            bool
	MistyEncounterCount   int
	MistyDonatedCount     int
	ThomAnimalLineFired   bool
	PetSupplyShopUnlocked bool
	PetLevel10Date        string
	PetMorningDefense     bool
	// Second pet (N4/E1, Tier-4 Estate). A parallel slot, not a rewrite of the
	// pet-1 fields above — see HasPet2. Carries only identity/level/barding; the
	// morning-defense, ditch-recovery and supply-shop mechanics stay pet-1-only.
	Pet2Type         string
	Pet2Name         string
	Pet2XP           int
	Pet2Level        int
	Pet2ArmorTier    int
	Pet2ChasedAway   bool
	Pet2Reactivated  bool
	Pet2Arrived      bool
	Pet2Level10Date  string
	AutoBabysit      bool
	AutoBabysitFocus string // mining|fishing|foraging — preferred skill for auto-babysit; "" defaults to weakest
	TreasuresLocked  bool   // when true, treasure drops at cap are refused instead of auto-swapping
	StreakDecayed    bool
	CraftsSucceeded  int
	DeathSource      string // "adventure" | "arena" | "" (legacy/unknown — treated as adventure)
	DeathLocation    string // human-readable location of last death; cleared on revive is not required
	// N5/D1 the Hollow King campaign. Bitmask of discovered journal pages (bit i
	// == page i+1). Read-only overlay from player_meta.journal_pages; writes go
	// through the atomic grantJournalPageDB, never the bulk character save.
	JournalPages int64
	// N5/D1c the finale reward-once flag. True after the first finale clear;
	// overlay-read, written by the atomic markEpilogueClearedDB.
	EpilogueCleared bool
	// N7/B2 Renown — cumulative overflow XP earned past the L20 cap. Overlay-read
	// from player_meta.renown_xp, written by the atomic addRenownXP, never the
	// bulk character save. RenownLevel() derives the prestige level from it.
	RenownXP int
}

// RenownLevel is the derived prestige level (renown_xp / renownXPPerLevel).
func (c *AdventureCharacter) RenownLevel() int {
	if c == nil {
		return 0
	}
	return renownLevelFor(c.RenownXP)
}

type AdvEquipment struct {
	Slot        EquipmentSlot
	Tier        int
	Condition   int
	Name        string
	ActionsUsed int
	ArenaTier   int
	ArenaSet    string
	Masterwork  bool
	SkillSource string
}

type AdvItem struct {
	ID          int64
	Name        string
	Type        string // ore, wood, fruit, treasure, gem, MasterworkGear
	Tier        int
	Value       int64
	Slot        EquipmentSlot // non-empty for MasterworkGear
	SkillSource string        // non-empty for MasterworkGear
	Temper      int           // rarity steps above base; magic_item rows only
}

type AdvBuff struct {
	ID        int64
	UserID    id.UserID
	BuffType  string
	BuffName  string
	Modifier  float64
	ExpiresAt time.Time
}

// ── Equipment Tier Definitions ───────────────────────────────────────────────

type EquipmentDef struct {
	Name        string
	Tier        int
	Description string
	Price       float64
}

var equipmentTiers = map[EquipmentSlot][]EquipmentDef{
	SlotWeapon: {
		{Name: "Basic Ass Sword", Tier: 0, Description: "It's a sword in the same way a parking ticket is legal documentation. A profound object of shame.", Price: 0},
		{Name: "Sad Iron Sword", Tier: 1, Description: "It's iron. It holds an edge if you squint. An improvement over the last thing in the same way a bruise improves on a fracture.", Price: 100},
		{Name: "Dull Steel Sword of Mediocrity", Tier: 2, Description: "Steel, technically. Holds an edge longer than the last one, which isn't saying much. A sword for someone who has given up dreaming but not yet given up entirely.", Price: 450},
		{Name: "Sword (It's Fine)", Tier: 3, Description: "Fine. It's a fine sword. Not good. Not impressive. Nobody will write songs about it. It will not let you down in a straightforward fight.", Price: 1500},
		{Name: "Enchanted Blade", Tier: 4, Description: "Enchanted. Glows faintly, hums with something that feels like intent. For the first time in your miserable adventuring career, the weapon is not the problem.", Price: 7500},
		{Name: "Vorpal Sword", Tier: 5, Description: "Vorpal. You know what it does. The things in the dark know too. They remember the last person who carried this.", Price: 30000},
	},
	SlotArmor: {
		{Name: "Shitty Armor", Tier: 0, Description: "Offers the protection of a strongly-worded letter. Looks worse than it sounds.", Price: 0},
		{Name: "Leather Scraps (Stitched)", Tier: 1, Description: "Leather. Most of it. Keeps the wind out and occasionally a very polite blade.", Price: 100},
		{Name: "Embarrassing Chain Mail", Tier: 2, Description: "Chain mail. Heavy, loud, and genuinely better than dying, which is its only selling point and honestly sufficient.", Price: 450},
		{Name: "Armor (Functional, Ugly)", Tier: 3, Description: "Plate armor. Heavy as bad decisions, protective as a real piece of equipment. Doesn't fit great. Works fine.", Price: 1500},
		{Name: "Enchanted Plate", Tier: 4, Description: "Enchanted plate. Lighter than it has any right to be, tougher than physics should allow. You feel, for the first time, like someone who is supposed to be doing this.", Price: 7500},
		{Name: "Dragonscale", Tier: 5, Description: "Dragonscale. An actual dragon died for this. Someone killed it. Maybe you, eventually. For now you wear the proof that such things are possible.", Price: 30000},
	},
	SlotHelmet: {
		{Name: "Goddamn Offensive Helmet", Tier: 0, Description: "Bad for your head. An insult to everyone in the immediate vicinity.", Price: 0},
		{Name: "Iron Pot with Eyeholes", Tier: 1, Description: "Someone used this as a chamber pot before it was a helmet. There are theories. It has eyeholes now.", Price: 75},
		{Name: "Helmet of Questionable Provenance", Tier: 2, Description: "The scratches were there when you bought it. The steel is sound. Nobody will compliment this helmet.", Price: 350},
		{Name: "Helm of Unremarkable Adequacy", Tier: 3, Description: "Reinforced. Fitted, roughly. Doesn't make you look competent but stops the top of your head from becoming someone else's problem.", Price: 1200},
		{Name: "Guardian's Helm", Tier: 4, Description: "Guardian-grade. This helm has seen real battles, kept real heads intact, and carries itself with the quiet dignity you are only just beginning to deserve.", Price: 6000},
		{Name: "Crown of the Fallen", Tier: 5, Description: "Crown of the Fallen. Every previous owner died in it. None of them died because of it. It will outlast you too.", Price: 25000},
	},
	SlotBoots: {
		{Name: "Knobby-Ass Boots", Tier: 0, Description: "The knobs are not a feature. Nobody knows what they are. Stop looking at them.", Price: 0},
		{Name: "Dead Man's Boots", Tier: 1, Description: "Taken off a corpse. The corpse didn't need them. You do. Don't think about it too hard.", Price: 75},
		{Name: "Boots of Mild Discomfort", Tier: 2, Description: "They've been places. Bad places. Places that did things to the leather you'd rather not examine. They'll hold together. Probably.", Price: 350},
		{Name: "Boots of Getting There Eventually", Tier: 3, Description: "Light enough. Grip is decent. Built for someone who moves with purpose, which you are in the process of becoming.", Price: 1200},
		{Name: "Ranger's Boots", Tier: 4, Description: "Ranger's boots. You move quieter. Faster. Longer. The ground cooperates. The forest notices. Something has shifted.", Price: 6000},
		{Name: "Boots of the Wind", Tier: 5, Description: "The wind doesn't slow you. Terrain offers suggestions you are free to decline. These boots are an affront to the concept of obstacles.", Price: 25000},
	},
	SlotTool: {
		{Name: "Rusted PoS Pickaxe", Tier: 0, Description: "Technically a pickaxe. That is the single nicest thing anyone can say about it.", Price: 0},
		{Name: "Dull Copper Pickaxe", Tier: 1, Description: "Copper. Soft. Gets the job done if you hit very hard and the ore is feeling cooperative.", Price: 100},
		{Name: "Chipped Iron Pickaxe", Tier: 2, Description: "Iron. Chipped to hell but bites the rock with something approaching intention. A pickaxe that exists and functions.", Price: 450},
		{Name: "Serviceable Steel Pickaxe", Tier: 3, Description: "Steel, properly weighted, properly edged. The mountain will acknowledge this pickaxe. Not respect it. Acknowledge it.", Price: 1500},
		{Name: "Mithril Pickaxe", Tier: 4, Description: "Mithril. Weighs nothing. Hits like consequence. Ores don't resist so much as rearrange themselves out of respect.", Price: 7500},
		{Name: "Diamond Pickaxe", Tier: 5, Description: "Diamond. Breaks anything short of fate and occasionally that too. The only limits left are your arm, your nerve, and the number of hours in a day.", Price: 30000},
	},
}

// tier0Equipment returns the name for a given slot at tier 0.
func tier0Equipment(slot EquipmentSlot) string {
	return equipmentTiers[slot][0].Name
}

// equipmentDefByTier returns the definition for a slot at a given tier.
func equipmentDefByTier(slot EquipmentSlot, tier int) EquipmentDef {
	defs := equipmentTiers[slot]
	if tier < 0 || tier >= len(defs) {
		return defs[0]
	}
	return defs[tier]
}

// ── Arena Gear Helpers ──────────────────────────────────────────────────────

// advEquippedArenaSets returns the unique arena set names currently equipped.
func advEquippedArenaSets(equip map[EquipmentSlot]*AdvEquipment) map[string]bool {
	sets := make(map[string]bool)
	for _, eq := range equip {
		if eq.ArenaSet != "" {
			sets[eq.ArenaSet] = true
		}
	}
	return sets
}

// advMasterworkSkillBonus returns true if any equipped masterwork piece's
// SkillSource matches the given activity type.
func advMasterworkSkillBonus(equip map[EquipmentSlot]*AdvEquipment, activity AdvActivityType) bool {
	source := ""
	switch activity {
	case AdvActivityMining:
		source = "mining"
	case AdvActivityFishing:
		source = "fishing"
	case AdvActivityForaging:
		source = "foraging"
	}
	if source == "" {
		return false
	}
	for _, eq := range equip {
		if eq.Masterwork && eq.SkillSource == source {
			return true
		}
	}
	return false
}

// DeathReprieveAvailable returns true if the Sovereign Death's Reprieve
// cooldown has expired (or was never triggered).
func (c *AdventureCharacter) DeathReprieveAvailable() bool {
	if c.DeathReprieveLast == nil {
		return true
	}
	return time.Since(*c.DeathReprieveLast) >= 168*time.Hour
}

// PardonAvailable returns true if the chat level death pardon cooldown
// has expired (or was never triggered). 7-day rolling cooldown.
func (c *AdventureCharacter) PardonAvailable() bool {
	if c.LastPardonUsed == nil {
		return true
	}
	return time.Since(*c.LastPardonUsed) >= 168*time.Hour
}

// Kill marks the character as dead with a 6-hour respawn timer. source is
// "adventure" or "arena"; location is a human-readable place name used by the
// daily report and standout-loss flavor.
func (c *AdventureCharacter) Kill(source, location string) {
	c.Alive = false
	deadUntil := time.Now().UTC().Add(6 * time.Hour)
	c.DeadUntil = &deadUntil
	c.LastDeathDate = time.Now().UTC().Format("2006-01-02")
	c.DeathSource = source
	c.DeathLocation = location
}

// HasPet returns true if the player has an active pet (not chased away).
func (c *AdventureCharacter) HasPet() bool {
	return c.PetType != "" && c.PetArrived && !c.PetChasedAway
}

// HasPet2 returns true if the player has an active second pet.
func (c *AdventureCharacter) HasPet2() bool {
	return c.Pet2Type != "" && c.Pet2Arrived && !c.Pet2ChasedAway
}

// HasHouse returns true if the player has purchased at least a base house.
func (c *AdventureCharacter) HasHouse() bool {
	return c.HouseTier > 0 || c.HouseLoanBalance > 0
}

// HouseHPBonus returns the HP bonus percentage from housing tier.
// Tier 1 (Base) = +0%, Tier 2 (Livable) = +5%, Tier 3 (Comfortable) = +12%, Tier 4 (Established) = +20%
func (c *AdventureCharacter) HouseHPBonus() float64 {
	switch c.HouseTier {
	case 2:
		return 0.05
	case 3:
		return 0.12
	case 4:
		return 0.20
	default:
		return 0
	}
}

// ── Action Economy ──────────────────────────────────────────────────────────

const maxCombatActions = 1
const maxHarvestActions = 3

func (c *AdventureCharacter) CanDoCombat(isHoliday bool) bool {
	max := maxCombatActions
	if isHoliday {
		max++
	}
	return c.CombatActionsUsed < max
}

func (c *AdventureCharacter) CanDoHarvest(isHoliday bool) bool {
	max := maxHarvestActions
	if isHoliday {
		max++
	}
	return c.HarvestActionsUsed < max
}

func (c *AdventureCharacter) HasActedToday() bool {
	if c.CombatActionsUsed > 0 || c.HarvestActionsUsed > 0 {
		return true
	}
	// DnD-side flows (expedition / zone / rest / autopilot) don't touch
	// the legacy action counters; they credit the day via LastActionDate
	// instead. Honor that so a player who ran an expedition all day and
	// extracted before midnight still counts as having acted.
	return c.LastActionDate == time.Now().UTC().Format("2006-01-02")
}

// markActedToday stamps the player's LastActionDate to today so the
// midnight reset credits the day. Safe to call on every DnD-side action;
// no-ops if the date is already today.
func markActedToday(userID id.UserID) {
	today := time.Now().UTC().Format("2006-01-02")
	c, err := loadAdvCharacter(userID)
	if err != nil || c == nil {
		return
	}
	if c.LastActionDate == today {
		return
	}
	c.LastActionDate = today
	_ = saveAdvCharacter(c)
}

func (c *AdventureCharacter) AllActionsUsed(isHoliday bool) bool {
	return !c.CanDoCombat(isHoliday) && !c.CanDoHarvest(isHoliday)
}

func isCombatActivity(activity AdvActivityType) bool {
	return activity == AdvActivityDungeon
}

func isHarvestActivity(activity AdvActivityType) bool {
	return activity == AdvActivityMining || activity == AdvActivityForaging || activity == AdvActivityFishing
}

// ── Equipment Score ──────────────────────────────────────────────────────────

func advEquipmentScore(equip map[EquipmentSlot]*AdvEquipment) float64 {
	score := 0.0
	arenaSets := advEquippedArenaSets(equip)
	for _, slot := range allSlots {
		eq, ok := equip[slot]
		if !ok {
			continue
		}
		contrib := advEffectiveTier(eq)
		if slot == SlotWeapon {
			contrib *= 2
		}
		// Condition modifier: below 50 halves contribution
		if eq.Condition < 50 {
			contrib /= 2
		}
		score += contrib
	}
	// Champion's set: Commanding Presence — +10% equipment score
	if arenaSets["champions"] {
		score *= 1.10
	}
	return score
}

// advEffectiveTier returns the effective tier of a piece of equipment,
// accounting for Masterwork (1.25x) and Arena (1.5x) bonuses.
func advEffectiveTier(eq *AdvEquipment) float64 {
	if eq == nil {
		return 0
	}
	if eq.ArenaTier > 0 {
		return float64(eq.Tier) * 1.5
	}
	if eq.Masterwork {
		return float64(eq.Tier) * 1.25
	}
	return float64(eq.Tier)
}

// ── XP & Level-Up ────────────────────────────────────────────────────────────

const maxAdvLevel = 50

// xpToNextLevel returns XP needed to advance from level to level+1.
func xpToNextLevel(level int) int {
	return 100 + (level * 3)
}

// checkAdvLevelUp checks if a character leveled up in the given skill and applies it.
// Returns whether a level-up occurred and the new level.
//
// Combat is special-cased: once a player has confirmed a D&D character,
// combat_level freezes. dnd_level (driven by dnd_xp via grantDnDXP) is
// canonical going forward. Combat XP still accrues into combat_xp for
// historical display, but never causes combat_level to advance.
// Skill levels (mining/foraging/fishing) are unaffected and continue to
// progress on their own track per v1.1 §4.
func checkAdvLevelUp(char *AdventureCharacter, skill string) (bool, int) {
	if skill == "combat" && HasCompletedSetup(char.UserID) {
		return false, char.CombatLevel
	}
	var xp *int
	var level *int
	switch skill {
	case "combat":
		xp = &char.CombatXP
		level = &char.CombatLevel
	case "mining":
		xp = &char.MiningXP
		level = &char.MiningSkill
	case "foraging":
		xp = &char.ForagingXP
		level = &char.ForagingSkill
	case "fishing":
		xp = &char.FishingXP
		level = &char.FishingSkill
	default:
		return false, 0
	}

	if *level >= maxAdvLevel {
		return false, *level
	}

	leveled := false
	for *level < maxAdvLevel {
		needed := xpToNextLevel(*level)
		if *xp < needed {
			break
		}
		*xp -= needed
		*level++
		leveled = true
	}
	return leveled, *level
}

// ── DB CRUD ──────────────────────────────────────────────────────────────────

func loadAdvCharacter(userID id.UserID) (*AdventureCharacter, error) {
	d := db.Get()
	var uid string
	if err := d.QueryRow(
		`SELECT user_id FROM player_meta WHERE user_id = ?`,
		string(userID),
	).Scan(&uid); err != nil {
		return nil, err
	}
	c := &AdventureCharacter{UserID: id.UserID(uid), Alive: true}
	applyPlayerMetaOverlay(c)
	return c, nil
}

func createAdvCharacter(userID id.UserID, displayName string) error {
	d := db.Get()
	tx, err := d.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Adv 2.0 Phase L5 close-out: player_meta is the canonical seed row.
	// adventure_characters is no longer written; loadAdvCharacter sources
	// the user_id from player_meta and overlays every subsystem's state.
	if _, err = tx.Exec(
		`INSERT INTO player_meta (user_id, display_name, created_at, last_active_at, alive)
		 VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1)
		 ON CONFLICT(user_id) DO UPDATE SET display_name = excluded.display_name`,
		string(userID), displayName,
	); err != nil {
		return err
	}

	// Create tier-0 equipment in all slots
	for _, slot := range allSlots {
		def := equipmentTiers[slot][0]
		_, err = tx.Exec(`
			INSERT INTO adventure_equipment (user_id, slot, tier, condition, name, actions_used, arena_tier, arena_set, masterwork, skill_source)
			VALUES (?, ?, 0, 100, ?, 0, 0, '', 0, '')`, string(userID), string(slot), def.Name)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

// ensurePlayerMetaSeed guarantees the canonical player_meta seed row (and tier-0
// equipment) exists for userID, creating it only when absent. The auto-migration
// path (ensureDnDCharacterForCombat) writes a confirmed dnd_character without
// touching the legacy layer; without this, a brand-new player whose first-ever
// action auto-migrates — e.g. !rest or !cast before !setup — ends up with a
// player_meta-less character that fails every legacy-layer command with
// "sql: no rows" (the camcast straggler). Conditional on absence and thus
// idempotent: legacy players who already have player_meta keep their equipment
// untouched — createAdvCharacter's tier-0 equipment insert has no conflict guard
// and would otherwise duplicate their gear.
func ensurePlayerMetaSeed(userID id.UserID) error {
	d := db.Get()
	var one int
	err := d.QueryRow(`SELECT 1 FROM player_meta WHERE user_id = ?`, string(userID)).Scan(&one)
	if err == nil {
		return nil // already seeded
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return err
	}
	return createAdvCharacter(userID, localpartOf(userID))
}

// localpartOf returns the mxid localpart (between @ and :) as a display-name
// fallback — matches Base.DisplayName's offline behavior. The seed's display
// name is overlaid by later player_meta upserts; this is just a sane default
// for a character born without a Matrix client in reach.
func localpartOf(userID id.UserID) string {
	s := string(userID)
	if i := strings.Index(s, ":"); i > 0 {
		return s[1:i]
	}
	return strings.TrimPrefix(s, "@")
}

// saveAdvCharacter persists every mutable AdventureCharacter field to
// player_meta. Phase L5h: the legacy adventure_characters UPDATE has been
// retired — the row is now read-only after createAdvCharacter seeds it,
// and saveAdvCharacter routes the entire mutable state set through the
// per-subsystem player_meta upserts. LastActiveAt is auto-bumped to mirror
// the previous CURRENT_TIMESTAMP semantics on save.
func saveAdvCharacter(char *AdventureCharacter) error {
	char.LastActiveAt = time.Now().UTC()
	return upsertAllPlayerMetaFromAdvChar(char)
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

func loadAdvEquipment(userID id.UserID) (map[EquipmentSlot]*AdvEquipment, error) {
	d := db.Get()
	rows, err := d.Query(`
		SELECT slot, tier, condition, name, actions_used, arena_tier, arena_set, masterwork, skill_source
		FROM adventure_equipment WHERE user_id = ?`, string(userID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	equip := make(map[EquipmentSlot]*AdvEquipment)
	for rows.Next() {
		e := &AdvEquipment{}
		var slot string
		var mw int
		if err := rows.Scan(&slot, &e.Tier, &e.Condition, &e.Name, &e.ActionsUsed, &e.ArenaTier, &e.ArenaSet, &mw, &e.SkillSource); err != nil {
			return nil, err
		}
		e.Slot = EquipmentSlot(slot)
		e.Masterwork = mw == 1
		equip[e.Slot] = e
	}
	return equip, rows.Err()
}

func saveAdvEquipment(userID id.UserID, eq *AdvEquipment) error {
	d := db.Get()
	mw := 0
	if eq.Masterwork {
		mw = 1
	}
	_, err := d.Exec(`
		UPDATE adventure_equipment
		SET tier = ?, condition = ?, name = ?, actions_used = ?, arena_tier = ?, arena_set = ?, masterwork = ?, skill_source = ?
		WHERE user_id = ? AND slot = ?`,
		eq.Tier, eq.Condition, eq.Name, eq.ActionsUsed, eq.ArenaTier, eq.ArenaSet, mw, eq.SkillSource,
		string(userID), string(eq.Slot))
	return err
}

func loadAdvInventory(userID id.UserID) ([]AdvItem, error) {
	d := db.Get()
	rows, err := d.Query(`
		SELECT id, name, item_type, tier, value, slot, skill_source, temper
		FROM adventure_inventory WHERE user_id = ? AND in_vault = 0
		ORDER BY tier DESC, value DESC`, string(userID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []AdvItem
	for rows.Next() {
		var it AdvItem
		var slot string
		if err := rows.Scan(&it.ID, &it.Name, &it.Type, &it.Tier, &it.Value, &slot, &it.SkillSource, &it.Temper); err != nil {
			return nil, err
		}
		it.Slot = EquipmentSlot(slot)
		items = append(items, it)
	}
	return items, rows.Err()
}

func addAdvInventoryItem(userID id.UserID, item AdvItem) error {
	d := db.Get()
	_, err := d.Exec(`
		INSERT INTO adventure_inventory (user_id, name, item_type, tier, value, slot, skill_source, temper)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		string(userID), item.Name, item.Type, item.Tier, item.Value, string(item.Slot), item.SkillSource, item.Temper)
	return err
}

// temperInventoryItem records a tempering step on an un-equipped magic item.
// Tier and value move with the item's new effective rarity so it keeps sorting
// and selling correctly.
func temperInventoryItem(itemID int64, temper, tier int, value int64) error {
	_, err := db.Get().Exec(
		`UPDATE adventure_inventory SET temper = ?, tier = ?, value = ? WHERE id = ?`,
		temper, tier, value, itemID)
	return err
}

func removeAdvInventoryItem(itemID int64) error {
	d := db.Get()
	_, err := d.Exec(`DELETE FROM adventure_inventory WHERE id = ?`, itemID)
	return err
}

func clearAdvInventory(userID id.UserID) ([]AdvItem, error) {
	items, err := loadAdvInventory(userID)
	if err != nil {
		return nil, err
	}
	d := db.Get()
	// Delete only what loadAdvInventory returned — vaulted rows are out of play
	// and must survive a bulk clear, or the vault's whole promise (safe from
	// !sell all) breaks the moment any caller routes sell-all through here.
	_, err = d.Exec(`DELETE FROM adventure_inventory WHERE user_id = ? AND in_vault = 0`, string(userID))
	if err != nil {
		return nil, err
	}
	return items, nil
}

func advInventoryCount(userID id.UserID) int {
	d := db.Get()
	var count int
	_ = d.QueryRow(`SELECT COUNT(*) FROM adventure_inventory WHERE user_id = ? AND in_vault = 0`, string(userID)).Scan(&count)
	return count
}

// loadAdvVault returns the items the player has stowed in their housing vault
// (N4/E1). These are excluded from loadAdvInventory — a vaulted item is out of
// play (unsellable, uncraftable, unusable in combat) until it is taken back.
func loadAdvVault(userID id.UserID) ([]AdvItem, error) {
	d := db.Get()
	rows, err := d.Query(`
		SELECT id, name, item_type, tier, value, slot, skill_source, temper
		FROM adventure_inventory WHERE user_id = ? AND in_vault = 1
		ORDER BY tier DESC, value DESC`, string(userID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []AdvItem
	for rows.Next() {
		var it AdvItem
		var slot string
		if err := rows.Scan(&it.ID, &it.Name, &it.Type, &it.Tier, &it.Value, &slot, &it.SkillSource, &it.Temper); err != nil {
			return nil, err
		}
		it.Slot = EquipmentSlot(slot)
		items = append(items, it)
	}
	return items, rows.Err()
}

// advVaultCount returns how many of a user's items are stowed in the vault.
func advVaultCount(userID id.UserID) int {
	d := db.Get()
	var count int
	_ = d.QueryRow(`SELECT COUNT(*) FROM adventure_inventory WHERE user_id = ? AND in_vault = 1`, string(userID)).Scan(&count)
	return count
}

// setItemVaulted flips a single inventory row's vault flag. Scoped to the
// owning user so a mistyped id can never move another player's item.
func setItemVaulted(userID id.UserID, itemID int64, vaulted bool) (bool, error) {
	v := 0
	if vaulted {
		v = 1
	}
	res, err := db.Get().Exec(
		`UPDATE adventure_inventory SET in_vault = ? WHERE id = ? AND user_id = ?`,
		v, itemID, string(userID))
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	return n > 0, nil
}

func loadAllAdvCharacters() ([]AdventureCharacter, error) {
	d := db.Get()
	rows, err := d.Query(`SELECT user_id FROM player_meta`)
	if err != nil {
		return nil, err
	}
	var uids []string
	for rows.Next() {
		var uid string
		if err := rows.Scan(&uid); err != nil {
			rows.Close()
			return nil, err
		}
		uids = append(uids, uid)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return nil, err
	}

	chars := make([]AdventureCharacter, 0, len(uids))
	for _, uid := range uids {
		c := AdventureCharacter{UserID: id.UserID(uid), Alive: true}
		applyPlayerMetaOverlay(&c)
		chars = append(chars, c)
	}
	return chars, nil
}

func logAdvActivity(userID id.UserID, activityType, location, outcome string, lootValue int64, xpGained int, flavorKey string) {
	db.Exec("adventure: log activity",
		`INSERT INTO adventure_activity_log (user_id, activity_type, location, outcome, loot_value, xp_gained, flavor_key)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		string(userID), activityType, location, outcome, lootValue, xpGained, flavorKey)
}

// ── Buff CRUD ────────────────────────────────────────────────────────────────

func loadAdvActiveBuffs(userID id.UserID) ([]AdvBuff, error) {
	d := db.Get()
	rows, err := d.Query(`
		SELECT id, user_id, buff_type, buff_name, modifier, expires_at
		FROM adventure_buffs
		WHERE user_id = ? AND expires_at > CURRENT_TIMESTAMP`, string(userID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var buffs []AdvBuff
	for rows.Next() {
		var b AdvBuff
		if err := rows.Scan(&b.ID, &b.UserID, &b.BuffType, &b.BuffName, &b.Modifier, &b.ExpiresAt); err != nil {
			return nil, err
		}
		buffs = append(buffs, b)
	}
	return buffs, rows.Err()
}

func addAdvBuff(userID id.UserID, buffType, buffName string, modifier float64, expiresAt time.Time) error {
	d := db.Get()
	_, err := d.Exec(`
		INSERT INTO adventure_buffs (user_id, buff_type, buff_name, modifier, expires_at)
		VALUES (?, ?, ?, ?, ?)`,
		string(userID), buffType, buffName, modifier, expiresAt)
	return err
}

func pruneAdvExpiredBuffs() error {
	d := db.Get()
	_, err := d.Exec(`DELETE FROM adventure_buffs WHERE expires_at < CURRENT_TIMESTAMP`)
	return err
}

// ── Today's Activity Log ─────────────────────────────────────────────────────

type AdvDayLog struct {
	UserID       id.UserID
	ActivityType string
	Location     string
	Outcome      string
	LootValue    int64
	XPGained     int
}

func loadAdvLogsForDate(date string) ([]AdvDayLog, error) {
	d := db.Get()
	rows, err := d.Query(`
		SELECT user_id, activity_type, COALESCE(location,''), outcome, loot_value, xp_gained
		FROM adventure_activity_log
		WHERE logged_at >= ? AND logged_at < DATE(?, '+1 day')
		ORDER BY logged_at`, date, date)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var logs []AdvDayLog
	for rows.Next() {
		var l AdvDayLog
		if err := rows.Scan(&l.UserID, &l.ActivityType, &l.Location, &l.Outcome, &l.LootValue, &l.XPGained); err != nil {
			return nil, err
		}
		logs = append(logs, l)
	}
	return logs, rows.Err()
}