package race_types

import (
	"database/sql"
	"fmt"
	"log"
)

// DatabaseLoader provides database operations for race types
type DatabaseLoader struct {
	db *sql.DB
}

// NewDatabaseLoader creates a new database loader
func NewDatabaseLoader(db *sql.DB) *DatabaseLoader {
	return &DatabaseLoader{db: db}
}

// LoadRaceTypes loads all race types from the database
// Converted from C++ WorldDatabase::LoadRaceTypes
func (dl *DatabaseLoader) LoadRaceTypes(masterList *MasterRaceTypeList) error {
	query := `
		SELECT model_type, race_id, category, subcategory, model_name 
		FROM race_types
		WHERE race_id > 0
	`
	
	rows, err := dl.db.Query(query)
	if err != nil {
		return fmt.Errorf("failed to query race types: %w", err)
	}
	defer rows.Close()
	
	count := 0
	for rows.Next() {
		var modelType, raceID int16
		var category, subcategory, modelName sql.NullString
		
		err := rows.Scan(&modelType, &raceID, &category, &subcategory, &modelName)
		if err != nil {
			log.Printf("Error scanning race type row: %v", err)
			continue
		}
		
		// Convert null strings to empty strings
		categoryStr := ""
		if category.Valid {
			categoryStr = category.String
		}
		
		subcategoryStr := ""
		if subcategory.Valid {
			subcategoryStr = subcategory.String
		}
		
		modelNameStr := ""
		if modelName.Valid {
			modelNameStr = modelName.String
		}
		
		// Add to master list
		if masterList.AddRaceType(modelType, raceID, categoryStr, subcategoryStr, modelNameStr, false) {
			count++
		}
	}
	
	if err := rows.Err(); err != nil {
		return fmt.Errorf("error iterating race type rows: %w", err)
	}
	
	log.Printf("Loaded %d race types from database", count)
	return nil
}

// SaveRaceType saves a single race type to the database
func (dl *DatabaseLoader) SaveRaceType(modelType int16, raceType *RaceType) error {
	if raceType == nil || !raceType.IsValid() {
		return fmt.Errorf("invalid race type")
	}
	
	query := `
		INSERT OR REPLACE INTO race_types (model_type, race_id, category, subcategory, model_name)
		VALUES (?, ?, ?, ?, ?)
	`
	
	_, err := dl.db.Exec(query, modelType, raceType.RaceTypeID, raceType.Category, raceType.Subcategory, raceType.ModelName)
	if err != nil {
		return fmt.Errorf("failed to save race type: %w", err)
	}
	
	return nil
}

// DeleteRaceType removes a race type from the database
func (dl *DatabaseLoader) DeleteRaceType(modelType int16) error {
	query := `DELETE FROM race_types WHERE model_type = ?`
	
	result, err := dl.db.Exec(query, modelType)
	if err != nil {
		return fmt.Errorf("failed to delete race type: %w", err)
	}
	
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get affected rows: %w", err)
	}
	
	if rowsAffected == 0 {
		return fmt.Errorf("race type with model_type %d not found", modelType)
	}
	
	return nil
}

// CreateRaceTypesTable creates the race_types table if it doesn't exist
func (dl *DatabaseLoader) CreateRaceTypesTable() error {
	query := `
		CREATE TABLE IF NOT EXISTS race_types (
			model_type INTEGER PRIMARY KEY,
			race_id INTEGER NOT NULL,
			category TEXT,
			subcategory TEXT,
			model_name TEXT,
			CHECK (race_id > 0)
		)
	`
	
	_, err := dl.db.Exec(query)
	if err != nil {
		return fmt.Errorf("failed to create race_types table: %w", err)
	}
	
	// Create index on race_id for faster lookups
	indexQuery := `CREATE INDEX IF NOT EXISTS idx_race_types_race_id ON race_types(race_id)`
	_, err = dl.db.Exec(indexQuery)
	if err != nil {
		return fmt.Errorf("failed to create race_id index: %w", err)
	}
	
	// Create index on category for category-based queries
	categoryIndexQuery := `CREATE INDEX IF NOT EXISTS idx_race_types_category ON race_types(category)`
	_, err = dl.db.Exec(categoryIndexQuery)
	if err != nil {
		return fmt.Errorf("failed to create category index: %w", err)
	}
	
	return nil
}