package transmute

import (
	"fmt"
	"time"

	"eq2emu/internal/database"
)

// DatabaseImpl provides a default implementation of the Database interface
type DatabaseImpl struct {
	db *database.DB
}

// NewDatabase creates a new database implementation
func NewDatabase(db *database.DB) *DatabaseImpl {
	return &DatabaseImpl{db: db}
}

// LoadTransmutingTiers loads transmuting tiers from the database
func (dbi *DatabaseImpl) LoadTransmutingTiers() ([]*TransmutingTier, error) {
	// Create transmuting_tiers table if it doesn't exist
	if err := dbi.db.Exec(`
		CREATE TABLE IF NOT EXISTS transmuting_tiers (
			min_level INTEGER NOT NULL,
			max_level INTEGER NOT NULL,
			fragment_id INTEGER NOT NULL,
			powder_id INTEGER NOT NULL,
			infusion_id INTEGER NOT NULL,
			mana_id INTEGER NOT NULL,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (min_level, max_level)
		)
	`); err != nil {
		return nil, fmt.Errorf("failed to create transmuting_tiers table: %w", err)
	}

	// Check if table is empty and populate with default data
	var count int
	row, err := dbi.db.QueryRow("SELECT COUNT(*) FROM transmuting_tiers")
	if err != nil {
		return nil, fmt.Errorf("failed to count transmuting tiers: %w", err)
	}
	if row != nil {
		count = row.Int(0)
		row.Close()
	}

	// If empty, populate with default EQ2 transmuting tiers
	if count == 0 {
		if err := dbi.populateDefaultTiers(); err != nil {
			return nil, fmt.Errorf("failed to populate default tiers: %w", err)
		}
	}

	// Load all tiers from database
	var tiers []*TransmutingTier
	err = dbi.db.Query("SELECT min_level, max_level, fragment_id, powder_id, infusion_id, mana_id FROM transmuting_tiers ORDER BY min_level", 
		func(row *database.Row) error {
			tier := &TransmutingTier{
				MinLevel:   int32(row.Int64(0)),
				MaxLevel:   int32(row.Int64(1)),
				FragmentID: int32(row.Int64(2)),
				PowderID:   int32(row.Int64(3)),
				InfusionID: int32(row.Int64(4)),
				ManaID:     int32(row.Int64(5)),
			}
			tiers = append(tiers, tier)
			return nil
		})

	if err != nil {
		return nil, fmt.Errorf("failed to load transmuting tiers: %w", err)
	}

	return tiers, nil
}

// populateDefaultTiers populates the database with default transmuting tiers
func (dbi *DatabaseImpl) populateDefaultTiers() error {
	defaultTiers := []struct {
		minLevel, maxLevel             int32
		fragmentID, powderID, infusionID, manaID int32
	}{
		{1, 9, 1001, 1002, 1003, 1004},
		{10, 19, 1005, 1006, 1007, 1008},
		{20, 29, 1009, 1010, 1011, 1012},
		{30, 39, 1013, 1014, 1015, 1016},
		{40, 49, 1017, 1018, 1019, 1020},
		{50, 59, 1021, 1022, 1023, 1024},
		{60, 69, 1025, 1026, 1027, 1028},
		{70, 79, 1029, 1030, 1031, 1032},
		{80, 89, 1033, 1034, 1035, 1036},
		{90, 100, 1037, 1038, 1039, 1040},
	}

	return dbi.db.Transaction(func(txDB *database.DB) error {
		for _, tier := range defaultTiers {
			err := txDB.Exec(`
				INSERT INTO transmuting_tiers (min_level, max_level, fragment_id, powder_id, infusion_id, mana_id)
				VALUES (?, ?, ?, ?, ?, ?)
			`, tier.minLevel, tier.maxLevel, tier.fragmentID, tier.powderID, tier.infusionID, tier.manaID)

			if err != nil {
				return fmt.Errorf("failed to insert tier %d-%d: %w", tier.minLevel, tier.maxLevel, err)
			}
		}
		return nil
	})
}

// TODO: When integrating with a real database system, replace this with actual database queries
// Example SQL implementation would look like:
/*
func (db *DatabaseImpl) LoadTransmutingTiers() ([]*TransmutingTier, error) {
	query := `SELECT min_level, max_level, fragment, powder, infusion, mana FROM transmuting ORDER BY min_level`

	rows, err := db.connection.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to query transmuting tiers: %w", err)
	}
	defer rows.Close()

	var tiers []*TransmutingTier

	for rows.Next() {
		tier := &TransmutingTier{}
		err := rows.Scan(
			&tier.MinLevel,
			&tier.MaxLevel,
			&tier.FragmentID,
			&tier.PowderID,
			&tier.InfusionID,
			&tier.ManaID,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan transmuting tier: %w", err)
		}

		tiers = append(tiers, tier)
	}

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

	return tiers, nil
}
*/

// SaveTransmutingTier saves a transmuting tier to the database
func (dbi *DatabaseImpl) SaveTransmutingTier(tier *TransmutingTier) error {
	if tier == nil {
		return fmt.Errorf("tier cannot be nil")
	}

	// Validate tier data
	if tier.MinLevel <= 0 || tier.MaxLevel <= 0 {
		return fmt.Errorf("invalid level range: %d-%d", tier.MinLevel, tier.MaxLevel)
	}

	if tier.MinLevel > tier.MaxLevel {
		return fmt.Errorf("min level (%d) cannot be greater than max level (%d)", tier.MinLevel, tier.MaxLevel)
	}

	if tier.FragmentID <= 0 || tier.PowderID <= 0 || tier.InfusionID <= 0 || tier.ManaID <= 0 {
		return fmt.Errorf("all material IDs must be positive")
	}

	err := dbi.db.Exec(`
		INSERT OR REPLACE INTO transmuting_tiers (min_level, max_level, fragment_id, powder_id, infusion_id, mana_id)
		VALUES (?, ?, ?, ?, ?, ?)
	`, tier.MinLevel, tier.MaxLevel, tier.FragmentID, tier.PowderID, tier.InfusionID, tier.ManaID)

	if err != nil {
		return fmt.Errorf("failed to save transmuting tier %d-%d: %w", tier.MinLevel, tier.MaxLevel, err)
	}

	return nil
}

// DeleteTransmutingTier deletes a transmuting tier from the database
func (dbi *DatabaseImpl) DeleteTransmutingTier(minLevel, maxLevel int32) error {
	if minLevel <= 0 || maxLevel <= 0 {
		return fmt.Errorf("invalid level range: %d-%d", minLevel, maxLevel)
	}

	err := dbi.db.Exec("DELETE FROM transmuting_tiers WHERE min_level = ? AND max_level = ?", minLevel, maxLevel)
	if err != nil {
		return fmt.Errorf("failed to delete transmuting tier %d-%d: %w", minLevel, maxLevel, err)
	}

	return nil
}

// GetTransmutingTierByLevel gets a specific transmuting tier by level range
func (dbi *DatabaseImpl) GetTransmutingTierByLevel(itemLevel int32) (*TransmutingTier, error) {
	row, err := dbi.db.QueryRow("SELECT min_level, max_level, fragment_id, powder_id, infusion_id, mana_id FROM transmuting_tiers WHERE min_level <= ? AND max_level >= ?", itemLevel, itemLevel)
	if err != nil {
		return nil, fmt.Errorf("failed to query transmuting tier for level %d: %w", itemLevel, err)
	}

	if row == nil {
		return nil, fmt.Errorf("no transmuting tier found for level %d", itemLevel)
	}

	defer row.Close()

	tier := &TransmutingTier{
		MinLevel:   int32(row.Int64(0)),
		MaxLevel:   int32(row.Int64(1)),
		FragmentID: int32(row.Int64(2)),
		PowderID:   int32(row.Int64(3)),
		InfusionID: int32(row.Int64(4)),
		ManaID:     int32(row.Int64(5)),
	}

	return tier, nil
}

// UpdateTransmutingTier updates an existing transmuting tier
func (dbi *DatabaseImpl) UpdateTransmutingTier(oldMinLevel, oldMaxLevel int32, newTier *TransmutingTier) error {
	if newTier == nil {
		return fmt.Errorf("new tier cannot be nil")
	}

	// Validate tier data first
	if newTier.MinLevel <= 0 || newTier.MaxLevel <= 0 {
		return fmt.Errorf("invalid level range: %d-%d", newTier.MinLevel, newTier.MaxLevel)
	}

	if newTier.MinLevel > newTier.MaxLevel {
		return fmt.Errorf("min level (%d) cannot be greater than max level (%d)", newTier.MinLevel, newTier.MaxLevel)
	}

	if newTier.FragmentID <= 0 || newTier.PowderID <= 0 || newTier.InfusionID <= 0 || newTier.ManaID <= 0 {
		return fmt.Errorf("all material IDs must be positive")
	}

	err := dbi.db.Exec(`
		UPDATE transmuting_tiers 
		SET min_level=?, max_level=?, fragment_id=?, powder_id=?, infusion_id=?, mana_id=? 
		WHERE min_level=? AND max_level=?
	`, newTier.MinLevel, newTier.MaxLevel, newTier.FragmentID, newTier.PowderID, 
	   newTier.InfusionID, newTier.ManaID, oldMinLevel, oldMaxLevel)

	if err != nil {
		return fmt.Errorf("failed to update transmuting tier %d-%d: %w", oldMinLevel, oldMaxLevel, err)
	}

	return nil
}

// TransmutingTierExists checks if a transmuting tier exists for the given level range
func (db *DatabaseImpl) TransmutingTierExists(minLevel, maxLevel int32) (bool, error) {
	// Placeholder implementation
	// In a real implementation:
	// SELECT COUNT(*) FROM transmuting WHERE min_level = ? AND max_level = ?

	tiers, err := db.LoadTransmutingTiers()
	if err != nil {
		return false, err
	}

	for _, tier := range tiers {
		if tier.MinLevel == minLevel && tier.MaxLevel == maxLevel {
			return true, nil
		}
	}

	return false, nil
}
