package heroic_ops

import (
	"context"
	"fmt"
	"time"

	"eq2emu/internal/database"
)

// DatabaseHeroicOPManager implements HeroicOPDatabase interface using the existing database wrapper
type DatabaseHeroicOPManager struct {
	db *database.DB
}

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

// LoadStarters retrieves all starters from database
func (dhom *DatabaseHeroicOPManager) LoadStarters(ctx context.Context) ([]HeroicOPData, error) {
	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 = ?`

	rows, err := dhom.db.QueryContext(ctx, query, HOTypeStarter)
	if err != nil {
		return nil, fmt.Errorf("failed to query heroic op starters: %w", err)
	}
	defer rows.Close()

	var starters []HeroicOPData
	for rows.Next() {
		var starter HeroicOPData
		var name, description *string

		err := rows.Scan(
			&starter.ID,
			&starter.HOType,
			&starter.StarterClass,
			&starter.StarterIcon,
			&starter.StarterLinkID,
			&starter.ChainOrder,
			&starter.ShiftIcon,
			&starter.SpellID,
			&starter.Chance,
			&starter.Ability1,
			&starter.Ability2,
			&starter.Ability3,
			&starter.Ability4,
			&starter.Ability5,
			&starter.Ability6,
			&name,
			&description,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan heroic op starter row: %w", err)
		}

		// Handle nullable fields
		if name != nil {
			starter.Name = *name
		}
		if description != nil {
			starter.Description = *description
		}

		starters = append(starters, starter)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating heroic op starter rows: %w", err)
	}

	return starters, nil
}

// LoadStarter retrieves a specific starter from database
func (dhom *DatabaseHeroicOPManager) LoadStarter(ctx context.Context, starterID int32) (*HeroicOPData, error) {
	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 name, description *string

	err := dhom.db.QueryRowContext(ctx, query, starterID, HOTypeStarter).Scan(
		&starter.ID,
		&starter.HOType,
		&starter.StarterClass,
		&starter.StarterIcon,
		&starter.StarterLinkID,
		&starter.ChainOrder,
		&starter.ShiftIcon,
		&starter.SpellID,
		&starter.Chance,
		&starter.Ability1,
		&starter.Ability2,
		&starter.Ability3,
		&starter.Ability4,
		&starter.Ability5,
		&starter.Ability6,
		&name,
		&description,
	)
	if err != nil {
		return nil, fmt.Errorf("failed to load heroic op starter %d: %w", starterID, err)
	}

	// Handle nullable fields
	if name != nil {
		starter.Name = *name
	}
	if description != nil {
		starter.Description = *description
	}

	return &starter, nil
}

// LoadWheels retrieves all wheels from database
func (dhom *DatabaseHeroicOPManager) LoadWheels(ctx context.Context) ([]HeroicOPData, error) {
	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 = ?`

	rows, err := dhom.db.QueryContext(ctx, query, HOTypeWheel)
	if err != nil {
		return nil, fmt.Errorf("failed to query heroic op wheels: %w", err)
	}
	defer rows.Close()

	var wheels []HeroicOPData
	for rows.Next() {
		var wheel HeroicOPData
		var name, description *string

		err := rows.Scan(
			&wheel.ID,
			&wheel.HOType,
			&wheel.StarterClass,
			&wheel.StarterIcon,
			&wheel.StarterLinkID,
			&wheel.ChainOrder,
			&wheel.ShiftIcon,
			&wheel.SpellID,
			&wheel.Chance,
			&wheel.Ability1,
			&wheel.Ability2,
			&wheel.Ability3,
			&wheel.Ability4,
			&wheel.Ability5,
			&wheel.Ability6,
			&name,
			&description,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan heroic op wheel row: %w", err)
		}

		// Handle nullable fields
		if name != nil {
			wheel.Name = *name
		}
		if description != nil {
			wheel.Description = *description
		}

		wheels = append(wheels, wheel)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating heroic op wheel rows: %w", err)
	}

	return wheels, nil
}

// LoadWheelsForStarter retrieves wheels for a specific starter
func (dhom *DatabaseHeroicOPManager) LoadWheelsForStarter(ctx context.Context, starterID int32) ([]HeroicOPData, error) {
	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 = ?`

	rows, err := dhom.db.QueryContext(ctx, query, starterID, HOTypeWheel)
	if err != nil {
		return nil, fmt.Errorf("failed to query wheels for starter %d: %w", starterID, err)
	}
	defer rows.Close()

	var wheels []HeroicOPData
	for rows.Next() {
		var wheel HeroicOPData
		var name, description *string

		err := rows.Scan(
			&wheel.ID,
			&wheel.HOType,
			&wheel.StarterClass,
			&wheel.StarterIcon,
			&wheel.StarterLinkID,
			&wheel.ChainOrder,
			&wheel.ShiftIcon,
			&wheel.SpellID,
			&wheel.Chance,
			&wheel.Ability1,
			&wheel.Ability2,
			&wheel.Ability3,
			&wheel.Ability4,
			&wheel.Ability5,
			&wheel.Ability6,
			&name,
			&description,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan wheel row: %w", err)
		}

		// Handle nullable fields
		if name != nil {
			wheel.Name = *name
		}
		if description != nil {
			wheel.Description = *description
		}

		wheels = append(wheels, wheel)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating wheel rows: %w", err)
	}

	return wheels, nil
}

// LoadWheel retrieves a specific wheel from database
func (dhom *DatabaseHeroicOPManager) LoadWheel(ctx context.Context, wheelID int32) (*HeroicOPData, error) {
	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 name, description *string

	err := dhom.db.QueryRowContext(ctx, query, wheelID, HOTypeWheel).Scan(
		&wheel.ID,
		&wheel.HOType,
		&wheel.StarterClass,
		&wheel.StarterIcon,
		&wheel.StarterLinkID,
		&wheel.ChainOrder,
		&wheel.ShiftIcon,
		&wheel.SpellID,
		&wheel.Chance,
		&wheel.Ability1,
		&wheel.Ability2,
		&wheel.Ability3,
		&wheel.Ability4,
		&wheel.Ability5,
		&wheel.Ability6,
		&name,
		&description,
	)
	if err != nil {
		return nil, fmt.Errorf("failed to load heroic op wheel %d: %w", wheelID, err)
	}

	// Handle nullable fields
	if name != nil {
		wheel.Name = *name
	}
	if description != nil {
		wheel.Description = *description
	}

	return &wheel, nil
}

// SaveStarter saves a heroic op starter
func (dhom *DatabaseHeroicOPManager) SaveStarter(ctx context.Context, starter *HeroicOPStarter) error {
	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 := dhom.db.ExecContext(ctx, query,
		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 {
	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 := dhom.db.ExecContext(ctx, query,
		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 {
	// Use a transaction to delete starter and associated wheels
	tx, err := dhom.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Delete associated wheels first
	_, err = tx.ExecContext(ctx, "DELETE FROM heroic_ops WHERE starter_link_id = ? AND ho_type = ?",
		starterID, HOTypeWheel)
	if err != nil {
		return fmt.Errorf("failed to delete wheels for starter %d: %w", starterID, err)
	}

	// Delete the starter
	_, err = tx.ExecContext(ctx, "DELETE FROM heroic_ops WHERE id = ? AND ho_type = ?",
		starterID, HOTypeStarter)
	if err != nil {
		return fmt.Errorf("failed to delete starter %d: %w", starterID, err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	return nil
}

// DeleteWheel removes a wheel from database
func (dhom *DatabaseHeroicOPManager) DeleteWheel(ctx context.Context, wheelID int32) error {
	_, err := dhom.db.ExecContext(ctx, "DELETE FROM heroic_ops WHERE id = ? AND ho_type = ?",
		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 {
	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 := dhom.db.ExecContext(ctx, query,
		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) {
	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 startTimeUnix, wheelStartTimeUnix int64
	var spellName, spellDescription *string

	err := dhom.db.QueryRowContext(ctx, query, instanceID).Scan(
		&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,
		&spellName,
		&spellDescription,
	)
	if err != nil {
		return nil, fmt.Errorf("failed to load HO instance %d: %w", instanceID, err)
	}

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

	// Handle nullable fields
	if spellName != nil {
		ho.SpellName = *spellName
	}
	if spellDescription != nil {
		ho.SpellDescription = *spellDescription
	}

	// 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 {
	_, err := dhom.db.ExecContext(ctx, "DELETE FROM heroic_op_instances WHERE id = ?", 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 {
	query := `INSERT INTO heroic_op_events
			  (id, instance_id, event_type, character_id, ability_icon, timestamp, data)
			  VALUES (?, ?, ?, ?, ?, ?, ?)`

	timestampUnix := event.Timestamp.Unix()

	_, err := dhom.db.ExecContext(ctx, query,
		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) {
	query := `SELECT id, instance_id, event_type, character_id, ability_icon, timestamp, data
			  FROM heroic_op_events WHERE instance_id = ? ORDER BY timestamp ASC`

	rows, err := dhom.db.QueryContext(ctx, query, instanceID)
	if err != nil {
		return nil, fmt.Errorf("failed to query HO events for instance %d: %w", instanceID, err)
	}
	defer rows.Close()

	var events []HeroicOPEvent
	for rows.Next() {
		var event HeroicOPEvent
		var timestampUnix int64
		var data *string

		err := rows.Scan(
			&event.ID,
			&event.InstanceID,
			&event.EventType,
			&event.CharacterID,
			&event.AbilityIcon,
			&timestampUnix,
			&data,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan HO event row: %w", err)
		}

		event.Timestamp = time.Unix(timestampUnix, 0)
		if data != nil {
			event.Data = *data
		}

		events = append(events, event)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating HO event rows: %w", err)
	}

	return events, nil
}

// GetHOStatistics retrieves statistics for a character
func (dhom *DatabaseHeroicOPManager) GetHOStatistics(ctx context.Context, characterID int32) (*HeroicOPStatistics, error) {
	// 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 := dhom.db.QueryRowContext(ctx, query, characterID, EventHOStarted).Scan(&stats.TotalHOsStarted)
	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 = dhom.db.QueryRowContext(ctx, query, characterID, EventHOCompleted).Scan(&stats.TotalHOsCompleted)
	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) {
	query := "SELECT COALESCE(MAX(id), 0) + 1 FROM heroic_ops WHERE ho_type = ?"

	var nextID int32
	err := dhom.db.QueryRowContext(ctx, query, HOTypeStarter).Scan(&nextID)
	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) {
	query := "SELECT COALESCE(MAX(id), 0) + 1 FROM heroic_ops WHERE ho_type = ?"

	var nextID int32
	err := dhom.db.QueryRowContext(ctx, query, HOTypeWheel).Scan(&nextID)
	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) {
	query := "SELECT COALESCE(MAX(id), 0) + 1 FROM heroic_op_instances"

	var nextID int64
	err := dhom.db.QueryRowContext(ctx, query).Scan(&nextID)
	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 {
	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 := dhom.db.ExecContext(ctx, query)
		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 := dhom.db.ExecContext(ctx, query)
		if err != nil {
			return fmt.Errorf("failed to create HO index %d: %w", i+1, err)
		}
	}

	return nil
}
