package heroic_ops

import (
	"context"
	"fmt"
	"time"

	"zombiezen.com/go/sqlite"
	"zombiezen.com/go/sqlite/sqlitex"
)

// DatabaseHeroicOPManager implements HeroicOPDatabase interface using sqlitex.Pool
type DatabaseHeroicOPManager struct {
	pool *sqlitex.Pool
}

// NewDatabaseHeroicOPManager creates a new database heroic OP manager
func NewDatabaseHeroicOPManager(pool *sqlitex.Pool) *DatabaseHeroicOPManager {
	return &DatabaseHeroicOPManager{
		pool: pool,
	}
}

// LoadStarters retrieves all starters from database
func (dhom *DatabaseHeroicOPManager) LoadStarters(ctx context.Context) ([]HeroicOPData, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `SELECT id, ho_type, starter_class, starter_icon, 0 as starter_link_id,
			  0 as chain_order, 0 as shift_icon, 0 as spell_id, 0.0 as chance,
			  ability1, ability2, ability3, ability4, ability5, ability6,
			  name, description FROM heroic_ops WHERE ho_type = ?`

	var starters []HeroicOPData
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{HOTypeStarter},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			var starter HeroicOPData
			starter.ID = int32(stmt.ColumnInt64(0))
			starter.HOType = stmt.ColumnText(1)
			starter.StarterClass = int8(stmt.ColumnInt64(2))
			starter.StarterIcon = int16(stmt.ColumnInt64(3))
			starter.StarterLinkID = int32(stmt.ColumnInt64(4))
			starter.ChainOrder = int8(stmt.ColumnInt64(5))
			starter.ShiftIcon = int16(stmt.ColumnInt64(6))
			starter.SpellID = int32(stmt.ColumnInt64(7))
			starter.Chance = float32(stmt.ColumnFloat(8))
			starter.Ability1 = int16(stmt.ColumnInt64(9))
			starter.Ability2 = int16(stmt.ColumnInt64(10))
			starter.Ability3 = int16(stmt.ColumnInt64(11))
			starter.Ability4 = int16(stmt.ColumnInt64(12))
			starter.Ability5 = int16(stmt.ColumnInt64(13))
			starter.Ability6 = int16(stmt.ColumnInt64(14))
			if stmt.ColumnType(15) != sqlite.TypeNull {
				starter.Name = stmt.ColumnText(15)
			}
			if stmt.ColumnType(16) != sqlite.TypeNull {
				starter.Description = stmt.ColumnText(16)
			}
			starters = append(starters, starter)
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to query heroic op starters: %w", err)
	}

	return starters, nil
}

// LoadStarter retrieves a specific starter from database
func (dhom *DatabaseHeroicOPManager) LoadStarter(ctx context.Context, starterID int32) (*HeroicOPData, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `SELECT id, ho_type, starter_class, starter_icon, 0 as starter_link_id,
			  0 as chain_order, 0 as shift_icon, 0 as spell_id, 0.0 as chance,
			  ability1, ability2, ability3, ability4, ability5, ability6,
			  name, description FROM heroic_ops WHERE id = ? AND ho_type = ?`

	var starter HeroicOPData
	var found bool
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{starterID, HOTypeStarter},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			found = true
			starter.ID = int32(stmt.ColumnInt64(0))
			starter.HOType = stmt.ColumnText(1)
			starter.StarterClass = int8(stmt.ColumnInt64(2))
			starter.StarterIcon = int16(stmt.ColumnInt64(3))
			starter.StarterLinkID = int32(stmt.ColumnInt64(4))
			starter.ChainOrder = int8(stmt.ColumnInt64(5))
			starter.ShiftIcon = int16(stmt.ColumnInt64(6))
			starter.SpellID = int32(stmt.ColumnInt64(7))
			starter.Chance = float32(stmt.ColumnFloat(8))
			starter.Ability1 = int16(stmt.ColumnInt64(9))
			starter.Ability2 = int16(stmt.ColumnInt64(10))
			starter.Ability3 = int16(stmt.ColumnInt64(11))
			starter.Ability4 = int16(stmt.ColumnInt64(12))
			starter.Ability5 = int16(stmt.ColumnInt64(13))
			starter.Ability6 = int16(stmt.ColumnInt64(14))
			if stmt.ColumnType(15) != sqlite.TypeNull {
				starter.Name = stmt.ColumnText(15)
			}
			if stmt.ColumnType(16) != sqlite.TypeNull {
				starter.Description = stmt.ColumnText(16)
			}
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to load heroic op starter %d: %w", starterID, err)
	}

	if !found {
		return nil, fmt.Errorf("heroic op starter %d not found", starterID)
	}

	return &starter, nil
}

// LoadWheels retrieves all wheels from database
func (dhom *DatabaseHeroicOPManager) LoadWheels(ctx context.Context) ([]HeroicOPData, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `SELECT id, ho_type, 0 as starter_class, 0 as starter_icon, starter_link_id,
			  chain_order, shift_icon, spell_id, chance,
			  ability1, ability2, ability3, ability4, ability5, ability6,
			  name, description FROM heroic_ops WHERE ho_type = ?`

	var wheels []HeroicOPData
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{HOTypeWheel},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			var wheel HeroicOPData
			wheel.ID = int32(stmt.ColumnInt64(0))
			wheel.HOType = stmt.ColumnText(1)
			wheel.StarterClass = int8(stmt.ColumnInt64(2))
			wheel.StarterIcon = int16(stmt.ColumnInt64(3))
			wheel.StarterLinkID = int32(stmt.ColumnInt64(4))
			wheel.ChainOrder = int8(stmt.ColumnInt64(5))
			wheel.ShiftIcon = int16(stmt.ColumnInt64(6))
			wheel.SpellID = int32(stmt.ColumnInt64(7))
			wheel.Chance = float32(stmt.ColumnFloat(8))
			wheel.Ability1 = int16(stmt.ColumnInt64(9))
			wheel.Ability2 = int16(stmt.ColumnInt64(10))
			wheel.Ability3 = int16(stmt.ColumnInt64(11))
			wheel.Ability4 = int16(stmt.ColumnInt64(12))
			wheel.Ability5 = int16(stmt.ColumnInt64(13))
			wheel.Ability6 = int16(stmt.ColumnInt64(14))
			if stmt.ColumnType(15) != sqlite.TypeNull {
				wheel.Name = stmt.ColumnText(15)
			}
			if stmt.ColumnType(16) != sqlite.TypeNull {
				wheel.Description = stmt.ColumnText(16)
			}
			wheels = append(wheels, wheel)
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to query heroic op wheels: %w", err)
	}

	return wheels, nil
}

// LoadWheelsForStarter retrieves wheels for a specific starter
func (dhom *DatabaseHeroicOPManager) LoadWheelsForStarter(ctx context.Context, starterID int32) ([]HeroicOPData, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `SELECT id, ho_type, 0 as starter_class, 0 as starter_icon, starter_link_id,
			  chain_order, shift_icon, spell_id, chance,
			  ability1, ability2, ability3, ability4, ability5, ability6,
			  name, description FROM heroic_ops WHERE starter_link_id = ? AND ho_type = ?`

	var wheels []HeroicOPData
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{starterID, HOTypeWheel},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			var wheel HeroicOPData
			wheel.ID = int32(stmt.ColumnInt64(0))
			wheel.HOType = stmt.ColumnText(1)
			wheel.StarterClass = int8(stmt.ColumnInt64(2))
			wheel.StarterIcon = int16(stmt.ColumnInt64(3))
			wheel.StarterLinkID = int32(stmt.ColumnInt64(4))
			wheel.ChainOrder = int8(stmt.ColumnInt64(5))
			wheel.ShiftIcon = int16(stmt.ColumnInt64(6))
			wheel.SpellID = int32(stmt.ColumnInt64(7))
			wheel.Chance = float32(stmt.ColumnFloat(8))
			wheel.Ability1 = int16(stmt.ColumnInt64(9))
			wheel.Ability2 = int16(stmt.ColumnInt64(10))
			wheel.Ability3 = int16(stmt.ColumnInt64(11))
			wheel.Ability4 = int16(stmt.ColumnInt64(12))
			wheel.Ability5 = int16(stmt.ColumnInt64(13))
			wheel.Ability6 = int16(stmt.ColumnInt64(14))
			if stmt.ColumnType(15) != sqlite.TypeNull {
				wheel.Name = stmt.ColumnText(15)
			}
			if stmt.ColumnType(16) != sqlite.TypeNull {
				wheel.Description = stmt.ColumnText(16)
			}
			wheels = append(wheels, wheel)
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to query wheels for starter %d: %w", starterID, err)
	}

	return wheels, nil
}

// LoadWheel retrieves a specific wheel from database
func (dhom *DatabaseHeroicOPManager) LoadWheel(ctx context.Context, wheelID int32) (*HeroicOPData, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `SELECT id, ho_type, 0 as starter_class, 0 as starter_icon, starter_link_id,
			  chain_order, shift_icon, spell_id, chance,
			  ability1, ability2, ability3, ability4, ability5, ability6,
			  name, description FROM heroic_ops WHERE id = ? AND ho_type = ?`

	var wheel HeroicOPData
	var found bool
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{wheelID, HOTypeWheel},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			found = true
			wheel.ID = int32(stmt.ColumnInt64(0))
			wheel.HOType = stmt.ColumnText(1)
			wheel.StarterClass = int8(stmt.ColumnInt64(2))
			wheel.StarterIcon = int16(stmt.ColumnInt64(3))
			wheel.StarterLinkID = int32(stmt.ColumnInt64(4))
			wheel.ChainOrder = int8(stmt.ColumnInt64(5))
			wheel.ShiftIcon = int16(stmt.ColumnInt64(6))
			wheel.SpellID = int32(stmt.ColumnInt64(7))
			wheel.Chance = float32(stmt.ColumnFloat(8))
			wheel.Ability1 = int16(stmt.ColumnInt64(9))
			wheel.Ability2 = int16(stmt.ColumnInt64(10))
			wheel.Ability3 = int16(stmt.ColumnInt64(11))
			wheel.Ability4 = int16(stmt.ColumnInt64(12))
			wheel.Ability5 = int16(stmt.ColumnInt64(13))
			wheel.Ability6 = int16(stmt.ColumnInt64(14))
			if stmt.ColumnType(15) != sqlite.TypeNull {
				wheel.Name = stmt.ColumnText(15)
			}
			if stmt.ColumnType(16) != sqlite.TypeNull {
				wheel.Description = stmt.ColumnText(16)
			}
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to load heroic op wheel %d: %w", wheelID, err)
	}

	if !found {
		return nil, fmt.Errorf("heroic op wheel %d not found", wheelID)
	}

	return &wheel, nil
}

// SaveStarter saves a heroic op starter
func (dhom *DatabaseHeroicOPManager) SaveStarter(ctx context.Context, starter *HeroicOPStarter) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `INSERT OR REPLACE INTO heroic_ops
			  (id, ho_type, starter_class, starter_icon, starter_link_id, chain_order,
			   shift_icon, spell_id, chance, ability1, ability2, ability3, ability4,
			   ability5, ability6, name, description)
			  VALUES (?, ?, ?, ?, 0, 0, 0, 0, 0.0, ?, ?, ?, ?, ?, ?, ?, ?)`

	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{
			starter.ID,
			HOTypeStarter,
			starter.StartClass,
			starter.StarterIcon,
			starter.Abilities[0],
			starter.Abilities[1],
			starter.Abilities[2],
			starter.Abilities[3],
			starter.Abilities[4],
			starter.Abilities[5],
			starter.Name,
			starter.Description,
		},
	})
	if err != nil {
		return fmt.Errorf("failed to save heroic op starter %d: %w", starter.ID, err)
	}

	return nil
}

// SaveWheel saves a heroic op wheel
func (dhom *DatabaseHeroicOPManager) SaveWheel(ctx context.Context, wheel *HeroicOPWheel) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `INSERT OR REPLACE INTO heroic_ops
			  (id, ho_type, starter_class, starter_icon, starter_link_id, chain_order,
			   shift_icon, spell_id, chance, ability1, ability2, ability3, ability4,
			   ability5, ability6, name, description)
			  VALUES (?, ?, 0, 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{
			wheel.ID,
			HOTypeWheel,
			wheel.StarterLinkID,
			wheel.Order,
			wheel.ShiftIcon,
			wheel.SpellID,
			wheel.Chance,
			wheel.Abilities[0],
			wheel.Abilities[1],
			wheel.Abilities[2],
			wheel.Abilities[3],
			wheel.Abilities[4],
			wheel.Abilities[5],
			wheel.Name,
			wheel.Description,
		},
	})
	if err != nil {
		return fmt.Errorf("failed to save heroic op wheel %d: %w", wheel.ID, err)
	}

	return nil
}

// DeleteStarter removes a starter from database
func (dhom *DatabaseHeroicOPManager) DeleteStarter(ctx context.Context, starterID int32) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	// Use a transaction to delete starter and associated wheels
	err = sqlitex.Execute(conn, "BEGIN", nil)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer sqlitex.Execute(conn, "ROLLBACK", nil)

	// Delete associated wheels first
	err = sqlitex.Execute(conn, "DELETE FROM heroic_ops WHERE starter_link_id = ? AND ho_type = ?", &sqlitex.ExecOptions{
		Args: []any{starterID, HOTypeWheel},
	})
	if err != nil {
		return fmt.Errorf("failed to delete wheels for starter %d: %w", starterID, err)
	}

	// Delete the starter
	err = sqlitex.Execute(conn, "DELETE FROM heroic_ops WHERE id = ? AND ho_type = ?", &sqlitex.ExecOptions{
		Args: []any{starterID, HOTypeStarter},
	})
	if err != nil {
		return fmt.Errorf("failed to delete starter %d: %w", starterID, err)
	}

	return sqlitex.Execute(conn, "COMMIT", nil)
}

// DeleteWheel removes a wheel from database
func (dhom *DatabaseHeroicOPManager) DeleteWheel(ctx context.Context, wheelID int32) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	err = sqlitex.Execute(conn, "DELETE FROM heroic_ops WHERE id = ? AND ho_type = ?", &sqlitex.ExecOptions{
		Args: []any{wheelID, HOTypeWheel},
	})
	if err != nil {
		return fmt.Errorf("failed to delete wheel %d: %w", wheelID, err)
	}

	return nil
}

// SaveHOInstance saves a heroic opportunity instance
func (dhom *DatabaseHeroicOPManager) SaveHOInstance(ctx context.Context, ho *HeroicOP) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `INSERT OR REPLACE INTO heroic_op_instances
			  (id, encounter_id, starter_id, wheel_id, state, start_time, wheel_start_time,
			   time_remaining, total_time, complete, countered_1, countered_2, countered_3,
			   countered_4, countered_5, countered_6, shift_used, starter_progress,
			   completed_by, spell_name, spell_description)
			  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	startTimeUnix := ho.StartTime.Unix()
	wheelStartTimeUnix := ho.WheelStartTime.Unix()

	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{
			ho.ID,
			ho.EncounterID,
			ho.StarterID,
			ho.WheelID,
			ho.State,
			startTimeUnix,
			wheelStartTimeUnix,
			ho.TimeRemaining,
			ho.TotalTime,
			ho.Complete,
			ho.Countered[0],
			ho.Countered[1],
			ho.Countered[2],
			ho.Countered[3],
			ho.Countered[4],
			ho.Countered[5],
			ho.ShiftUsed,
			ho.StarterProgress,
			ho.CompletedBy,
			ho.SpellName,
			ho.SpellDescription,
		},
	})
	if err != nil {
		return fmt.Errorf("failed to save HO instance %d: %w", ho.ID, err)
	}

	return nil
}

// LoadHOInstance retrieves a heroic opportunity instance
func (dhom *DatabaseHeroicOPManager) LoadHOInstance(ctx context.Context, instanceID int64) (*HeroicOP, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `SELECT id, encounter_id, starter_id, wheel_id, state, start_time, wheel_start_time,
			  time_remaining, total_time, complete, countered_1, countered_2, countered_3,
			  countered_4, countered_5, countered_6, shift_used, starter_progress,
			  completed_by, spell_name, spell_description
			  FROM heroic_op_instances WHERE id = ?`

	var ho HeroicOP
	var found bool
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{instanceID},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			found = true
			ho.ID = stmt.ColumnInt64(0)
			ho.EncounterID = int32(stmt.ColumnInt64(1))
			ho.StarterID = int32(stmt.ColumnInt64(2))
			ho.WheelID = int32(stmt.ColumnInt64(3))
			ho.State = int8(stmt.ColumnInt64(4))
			startTimeUnix := stmt.ColumnInt64(5)
			wheelStartTimeUnix := stmt.ColumnInt64(6)
			ho.TimeRemaining = int32(stmt.ColumnInt64(7))
			ho.TotalTime = int32(stmt.ColumnInt64(8))
			ho.Complete = int8(stmt.ColumnInt64(9))
			ho.Countered[0] = int8(stmt.ColumnInt64(10))
			ho.Countered[1] = int8(stmt.ColumnInt64(11))
			ho.Countered[2] = int8(stmt.ColumnInt64(12))
			ho.Countered[3] = int8(stmt.ColumnInt64(13))
			ho.Countered[4] = int8(stmt.ColumnInt64(14))
			ho.Countered[5] = int8(stmt.ColumnInt64(15))
			ho.ShiftUsed = int8(stmt.ColumnInt64(16))
			ho.StarterProgress = int8(stmt.ColumnInt64(17))
			ho.CompletedBy = int32(stmt.ColumnInt64(18))
			if stmt.ColumnType(19) != sqlite.TypeNull {
				ho.SpellName = stmt.ColumnText(19)
			}
			if stmt.ColumnType(20) != sqlite.TypeNull {
				ho.SpellDescription = stmt.ColumnText(20)
			}

			// Convert timestamps
			ho.StartTime = time.Unix(startTimeUnix, 0)
			ho.WheelStartTime = time.Unix(wheelStartTimeUnix, 0)

			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to load HO instance %d: %w", instanceID, err)
	}

	if !found {
		return nil, fmt.Errorf("HO instance %d not found", instanceID)
	}

	// Initialize maps
	ho.Participants = make(map[int32]bool)
	ho.CurrentStarters = make([]int32, 0)

	return &ho, nil
}

// DeleteHOInstance removes a heroic opportunity instance
func (dhom *DatabaseHeroicOPManager) DeleteHOInstance(ctx context.Context, instanceID int64) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	err = sqlitex.Execute(conn, "DELETE FROM heroic_op_instances WHERE id = ?", &sqlitex.ExecOptions{
		Args: []any{instanceID},
	})
	if err != nil {
		return fmt.Errorf("failed to delete HO instance %d: %w", instanceID, err)
	}

	return nil
}

// SaveHOEvent saves a heroic opportunity event
func (dhom *DatabaseHeroicOPManager) SaveHOEvent(ctx context.Context, event *HeroicOPEvent) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `INSERT INTO heroic_op_events
			  (id, instance_id, event_type, character_id, ability_icon, timestamp, data)
			  VALUES (?, ?, ?, ?, ?, ?, ?)`

	timestampUnix := event.Timestamp.Unix()

	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{
			event.ID,
			event.InstanceID,
			event.EventType,
			event.CharacterID,
			event.AbilityIcon,
			timestampUnix,
			event.Data,
		},
	})
	if err != nil {
		return fmt.Errorf("failed to save HO event %d: %w", event.ID, err)
	}

	return nil
}

// LoadHOEvents retrieves events for a heroic opportunity instance
func (dhom *DatabaseHeroicOPManager) LoadHOEvents(ctx context.Context, instanceID int64) ([]HeroicOPEvent, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := `SELECT id, instance_id, event_type, character_id, ability_icon, timestamp, data
			  FROM heroic_op_events WHERE instance_id = ? ORDER BY timestamp ASC`

	var events []HeroicOPEvent
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{instanceID},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			var event HeroicOPEvent
			event.ID = stmt.ColumnInt64(0)
			event.InstanceID = stmt.ColumnInt64(1)
			event.EventType = int(stmt.ColumnInt64(2))
			event.CharacterID = int32(stmt.ColumnInt64(3))
			event.AbilityIcon = int16(stmt.ColumnInt64(4))
			timestampUnix := stmt.ColumnInt64(5)
			event.Timestamp = time.Unix(timestampUnix, 0)
			if stmt.ColumnType(6) != sqlite.TypeNull {
				event.Data = stmt.ColumnText(6)
			}
			events = append(events, event)
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to query HO events for instance %d: %w", instanceID, err)
	}

	return events, nil
}

// GetHOStatistics retrieves statistics for a character
func (dhom *DatabaseHeroicOPManager) GetHOStatistics(ctx context.Context, characterID int32) (*HeroicOPStatistics, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	// This is a simplified implementation - in practice you'd want more complex statistics
	stats := &HeroicOPStatistics{
		ParticipationStats: make(map[int32]int64),
	}

	// Count total HOs started by this character
	query := `SELECT COUNT(*) FROM heroic_op_events
			  WHERE character_id = ? AND event_type = ?`
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{characterID, EventHOStarted},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			stats.TotalHOsStarted = stmt.ColumnInt64(0)
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get HO started count: %w", err)
	}

	// Count total HOs completed by this character
	query = `SELECT COUNT(*) FROM heroic_op_events
			 WHERE character_id = ? AND event_type = ?`
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{characterID, EventHOCompleted},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			stats.TotalHOsCompleted = stmt.ColumnInt64(0)
			return nil
		},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get HO completed count: %w", err)
	}

	// Calculate success rate
	if stats.TotalHOsStarted > 0 {
		stats.SuccessRate = float64(stats.TotalHOsCompleted) / float64(stats.TotalHOsStarted) * 100.0
	}

	stats.ParticipationStats[characterID] = stats.TotalHOsStarted

	return stats, nil
}

// GetNextStarterID returns the next available starter ID
func (dhom *DatabaseHeroicOPManager) GetNextStarterID(ctx context.Context) (int32, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return 0, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := "SELECT COALESCE(MAX(id), 0) + 1 FROM heroic_ops WHERE ho_type = ?"

	var nextID int32
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{HOTypeStarter},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			nextID = int32(stmt.ColumnInt64(0))
			return nil
		},
	})
	if err != nil {
		return 0, fmt.Errorf("failed to get next starter ID: %w", err)
	}

	return nextID, nil
}

// GetNextWheelID returns the next available wheel ID
func (dhom *DatabaseHeroicOPManager) GetNextWheelID(ctx context.Context) (int32, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return 0, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := "SELECT COALESCE(MAX(id), 0) + 1 FROM heroic_ops WHERE ho_type = ?"

	var nextID int32
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		Args: []any{HOTypeWheel},
		ResultFunc: func(stmt *sqlite.Stmt) error {
			nextID = int32(stmt.ColumnInt64(0))
			return nil
		},
	})
	if err != nil {
		return 0, fmt.Errorf("failed to get next wheel ID: %w", err)
	}

	return nextID, nil
}

// GetNextInstanceID returns the next available instance ID
func (dhom *DatabaseHeroicOPManager) GetNextInstanceID(ctx context.Context) (int64, error) {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return 0, fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	query := "SELECT COALESCE(MAX(id), 0) + 1 FROM heroic_op_instances"

	var nextID int64
	err = sqlitex.Execute(conn, query, &sqlitex.ExecOptions{
		ResultFunc: func(stmt *sqlite.Stmt) error {
			nextID = stmt.ColumnInt64(0)
			return nil
		},
	})
	if err != nil {
		return 0, fmt.Errorf("failed to get next instance ID: %w", err)
	}

	return nextID, nil
}

// EnsureHOTables creates the heroic opportunity tables if they don't exist
func (dhom *DatabaseHeroicOPManager) EnsureHOTables(ctx context.Context) error {
	conn, err := dhom.pool.Take(ctx)
	if err != nil {
		return fmt.Errorf("failed to get database connection: %w", err)
	}
	defer dhom.pool.Put(conn)

	queries := []string{
		`CREATE TABLE IF NOT EXISTS heroic_ops (
			id INTEGER NOT NULL,
			ho_type TEXT NOT NULL CHECK(ho_type IN ('Starter', 'Wheel')),
			starter_class INTEGER NOT NULL DEFAULT 0,
			starter_icon INTEGER NOT NULL DEFAULT 0,
			starter_link_id INTEGER NOT NULL DEFAULT 0,
			chain_order INTEGER NOT NULL DEFAULT 0,
			shift_icon INTEGER NOT NULL DEFAULT 0,
			spell_id INTEGER NOT NULL DEFAULT 0,
			chance REAL NOT NULL DEFAULT 1.0,
			ability1 INTEGER NOT NULL DEFAULT 0,
			ability2 INTEGER NOT NULL DEFAULT 0,
			ability3 INTEGER NOT NULL DEFAULT 0,
			ability4 INTEGER NOT NULL DEFAULT 0,
			ability5 INTEGER NOT NULL DEFAULT 0,
			ability6 INTEGER NOT NULL DEFAULT 0,
			name TEXT DEFAULT '',
			description TEXT DEFAULT '',
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id, ho_type)
		)`,
		`CREATE TABLE IF NOT EXISTS heroic_op_instances (
			id INTEGER PRIMARY KEY,
			encounter_id INTEGER NOT NULL,
			starter_id INTEGER NOT NULL DEFAULT 0,
			wheel_id INTEGER NOT NULL DEFAULT 0,
			state INTEGER NOT NULL DEFAULT 0,
			start_time INTEGER NOT NULL,
			wheel_start_time INTEGER NOT NULL DEFAULT 0,
			time_remaining INTEGER NOT NULL DEFAULT 0,
			total_time INTEGER NOT NULL DEFAULT 0,
			complete INTEGER NOT NULL DEFAULT 0,
			countered_1 INTEGER NOT NULL DEFAULT 0,
			countered_2 INTEGER NOT NULL DEFAULT 0,
			countered_3 INTEGER NOT NULL DEFAULT 0,
			countered_4 INTEGER NOT NULL DEFAULT 0,
			countered_5 INTEGER NOT NULL DEFAULT 0,
			countered_6 INTEGER NOT NULL DEFAULT 0,
			shift_used INTEGER NOT NULL DEFAULT 0,
			starter_progress INTEGER NOT NULL DEFAULT 0,
			completed_by INTEGER NOT NULL DEFAULT 0,
			spell_name TEXT DEFAULT '',
			spell_description TEXT DEFAULT '',
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS heroic_op_events (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			instance_id INTEGER NOT NULL,
			event_type INTEGER NOT NULL,
			character_id INTEGER NOT NULL,
			ability_icon INTEGER NOT NULL DEFAULT 0,
			timestamp INTEGER NOT NULL,
			data TEXT DEFAULT '',
			FOREIGN KEY (instance_id) REFERENCES heroic_op_instances(id) ON DELETE CASCADE
		)`,
	}

	for i, query := range queries {
		err := sqlitex.Execute(conn, query, nil)
		if err != nil {
			return fmt.Errorf("failed to create HO table %d: %w", i+1, err)
		}
	}

	// Create indexes for better performance
	indexes := []string{
		`CREATE INDEX IF NOT EXISTS idx_heroic_ops_type ON heroic_ops(ho_type)`,
		`CREATE INDEX IF NOT EXISTS idx_heroic_ops_class ON heroic_ops(starter_class)`,
		`CREATE INDEX IF NOT EXISTS idx_heroic_ops_link ON heroic_ops(starter_link_id)`,
		`CREATE INDEX IF NOT EXISTS idx_ho_instances_encounter ON heroic_op_instances(encounter_id)`,
		`CREATE INDEX IF NOT EXISTS idx_ho_instances_state ON heroic_op_instances(state)`,
		`CREATE INDEX IF NOT EXISTS idx_ho_events_instance ON heroic_op_events(instance_id)`,
		`CREATE INDEX IF NOT EXISTS idx_ho_events_character ON heroic_op_events(character_id)`,
		`CREATE INDEX IF NOT EXISTS idx_ho_events_type ON heroic_op_events(event_type)`,
		`CREATE INDEX IF NOT EXISTS idx_ho_events_timestamp ON heroic_op_events(timestamp)`,
	}

	for i, query := range indexes {
		err := sqlitex.Execute(conn, query, nil)
		if err != nil {
			return fmt.Errorf("failed to create HO index %d: %w", i+1, err)
		}
	}

	return nil
}
