package items

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

// LoadCharacterItems loads all items for a character from the database
func (idb *ItemDatabase) LoadCharacterItems(charID uint32, masterList *MasterItemList) (*PlayerItemList, *EquipmentItemList, error) {
	log.Printf("Loading items for character %d", charID)

	inventory := NewPlayerItemList()
	equipment := NewEquipmentItemList()

	stmt := idb.queries["load_character_items"]
	if stmt == nil {
		return nil, nil, fmt.Errorf("load_character_items query not prepared")
	}

	rows, err := stmt.Query(charID)
	if err != nil {
		return nil, nil, fmt.Errorf("failed to query character items: %v", err)
	}
	defer rows.Close()

	itemCount := 0
	for rows.Next() {
		characterItem, err := idb.scanCharacterItemFromRow(rows, masterList)
		if err != nil {
			log.Printf("Error scanning character item from row: %v", err)
			continue
		}

		if characterItem == nil {
			continue // Item template not found
		}

		// Place item in appropriate container based on inv_slot_id
		if characterItem.Details.InvSlotID >= 0 && characterItem.Details.InvSlotID < 100 {
			// Equipment slots (0-25)
			if characterItem.Details.InvSlotID < NumSlots {
				equipment.SetItem(int8(characterItem.Details.InvSlotID), characterItem, false)
			}
		} else {
			// Inventory, bank, or special slots
			inventory.AddItem(characterItem)
		}

		itemCount++
	}

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

	log.Printf("Loaded %d items for character %d", itemCount, charID)
	return inventory, equipment, nil
}

// scanCharacterItemFromRow scans a character item row and creates an item instance
func (idb *ItemDatabase) scanCharacterItemFromRow(rows *sql.Rows, masterList *MasterItemList) (*Item, error) {
	var itemID int32
	var uniqueID int64
	var invSlotID, slotID int32
	var appearanceType int8
	var icon, icon2, count, tier int16
	var bagID int32
	var detailsCount int16
	var creator sql.NullString
	var adorn0, adorn1, adorn2 int32
	var groupID int32
	var creatorApp sql.NullString
	var randomSeed int32

	err := rows.Scan(
		&itemID, &uniqueID, &invSlotID, &slotID, &appearanceType,
		&icon, &icon2, &count, &tier, &bagID, &detailsCount,
		&creator, &adorn0, &adorn1, &adorn2, &groupID,
		&creatorApp, &randomSeed,
	)

	if err != nil {
		return nil, fmt.Errorf("failed to scan character item row: %v", err)
	}

	// Get item template from master list
	template := masterList.GetItem(itemID)
	if template == nil {
		log.Printf("Warning: Item template %d not found for character item", itemID)
		return nil, nil
	}

	// Create item instance from template
	item := NewItemFromTemplate(template)

	// Update with character-specific data
	item.Details.UniqueID = uniqueID
	item.Details.InvSlotID = invSlotID
	item.Details.SlotID = int16(slotID)
	item.Details.AppearanceType = int16(appearanceType)
	item.Details.Icon = icon
	item.Details.ClassicIcon = icon2
	item.Details.Count = count
	item.Details.Tier = int8(tier)
	item.Details.BagID = bagID

	// Set creator if present
	if creator.Valid {
		item.Creator = creator.String
	}

	// Set adornment slots
	item.Adorn0 = adorn0
	item.Adorn1 = adorn1
	item.Adorn2 = adorn2

	// TODO: Handle group items (heirloom items shared between characters)
	// TODO: Handle creator appearance
	// TODO: Handle random seed for item variations

	return item, nil
}

// SaveCharacterItems saves all items for a character to the database
func (idb *ItemDatabase) SaveCharacterItems(charID uint32, inventory *PlayerItemList, equipment *EquipmentItemList) error {
	log.Printf("Saving items for character %d", charID)

	// Start transaction
	tx, err := idb.db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %v", err)
	}
	defer tx.Rollback()

	// Delete existing items for this character
	_, err = tx.Exec("DELETE FROM character_items WHERE char_id = ?", charID)
	if err != nil {
		return fmt.Errorf("failed to delete existing character items: %v", err)
	}

	// Prepare insert statement
	insertStmt, err := tx.Prepare(`
		INSERT INTO character_items
		(char_id, item_id, unique_id, inv_slot_id, slot_id, appearance_type, icon, icon2,
		 count, tier, bag_id, details_count, creator, adornment_slot0, adornment_slot1,
		 adornment_slot2, group_id, creator_app, random_seed, created)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`)
	if err != nil {
		return fmt.Errorf("failed to prepare insert statement: %v", err)
	}
	defer insertStmt.Close()

	itemCount := 0

	// Save equipped items
	if equipment != nil {
		for slotID, item := range equipment.GetAllEquippedItems() {
			if item != nil {
				if err := idb.saveCharacterItem(insertStmt, charID, item, int32(slotID)); err != nil {
					return fmt.Errorf("failed to save equipped item: %v", err)
				}
				itemCount++
			}
		}
	}

	// Save inventory items
	if inventory != nil {
		allItems := inventory.GetAllItems()
		for _, item := range allItems {
			if item != nil {
				if err := idb.saveCharacterItem(insertStmt, charID, item, item.Details.InvSlotID); err != nil {
					return fmt.Errorf("failed to save inventory item: %v", err)
				}
				itemCount++
			}
		}
	}

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

	log.Printf("Saved %d items for character %d", itemCount, charID)
	return nil
}

// saveCharacterItem saves a single character item
func (idb *ItemDatabase) saveCharacterItem(stmt *sql.Stmt, charID uint32, item *Item, invSlotID int32) error {
	// Handle null creator
	var creator sql.NullString
	if item.Creator != "" {
		creator.String = item.Creator
		creator.Valid = true
	}

	// Handle null creator app
	var creatorApp sql.NullString
	// TODO: Set creator app if needed

	_, err := stmt.Exec(
		charID,
		item.Details.ItemID,
		item.Details.UniqueID,
		invSlotID,
		item.Details.SlotID,
		item.Details.AppearanceType,
		item.Details.Icon,
		item.Details.ClassicIcon,
		item.Details.Count,
		item.Details.Tier,
		item.Details.BagID,
		item.Details.Count, // details_count (same as count for now)
		creator,
		item.Adorn0,
		item.Adorn1,
		item.Adorn2,
		0, // group_id (TODO: implement heirloom groups)
		creatorApp,
		0, // random_seed (TODO: implement item variations)
		time.Now().Format("2006-01-02 15:04:05"),
	)

	return err
}

// DeleteCharacterItem deletes a specific item from a character's inventory
func (idb *ItemDatabase) DeleteCharacterItem(charID uint32, uniqueID int64) error {
	stmt := idb.queries["delete_character_item"]
	if stmt == nil {
		return fmt.Errorf("delete_character_item query not prepared")
	}

	result, err := stmt.Exec(charID, uniqueID)
	if err != nil {
		return fmt.Errorf("failed to delete character item: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %v", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("no item found with unique_id %d for character %d", uniqueID, charID)
	}

	log.Printf("Deleted item %d for character %d", uniqueID, charID)
	return nil
}

// DeleteAllCharacterItems deletes all items for a character
func (idb *ItemDatabase) DeleteAllCharacterItems(charID uint32) error {
	stmt := idb.queries["delete_character_items"]
	if stmt == nil {
		return fmt.Errorf("delete_character_items query not prepared")
	}

	result, err := stmt.Exec(charID)
	if err != nil {
		return fmt.Errorf("failed to delete character items: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %v", err)
	}

	log.Printf("Deleted %d items for character %d", rowsAffected, charID)
	return nil
}

// SaveSingleCharacterItem saves a single character item (for updates)
func (idb *ItemDatabase) SaveSingleCharacterItem(charID uint32, item *Item) error {
	stmt := idb.queries["save_character_item"]
	if stmt == nil {
		return fmt.Errorf("save_character_item query not prepared")
	}

	// Handle null creator
	var creator sql.NullString
	if item.Creator != "" {
		creator.String = item.Creator
		creator.Valid = true
	}

	// Handle null creator app
	var creatorApp sql.NullString

	_, err := stmt.Exec(
		charID,
		item.Details.ItemID,
		item.Details.UniqueID,
		item.Details.InvSlotID,
		item.Details.SlotID,
		item.Details.AppearanceType,
		item.Details.Icon,
		item.Details.ClassicIcon,
		item.Details.Count,
		item.Details.Tier,
		item.Details.BagID,
		item.Details.Count, // details_count
		creator,
		item.Adorn0,
		item.Adorn1,
		item.Adorn2,
		0, // group_id
		creatorApp,
		0, // random_seed
		time.Now().Format("2006-01-02 15:04:05"),
	)

	if err != nil {
		return fmt.Errorf("failed to save character item: %v", err)
	}

	return nil
}

// LoadTemporaryItems loads temporary items that may have expired
func (idb *ItemDatabase) LoadTemporaryItems(charID uint32, masterList *MasterItemList) ([]*Item, error) {
	query := `
		SELECT ci.item_id, ci.unique_id, ci.inv_slot_id, ci.slot_id, ci.appearance_type, 
			   ci.icon, ci.icon2, ci.count, ci.tier, ci.bag_id, ci.details_count,
			   ci.creator, ci.adornment_slot0, ci.adornment_slot1, ci.adornment_slot2,
			   ci.group_id, ci.creator_app, ci.random_seed, ci.created
		FROM character_items ci
		JOIN items i ON ci.item_id = i.id
		WHERE ci.char_id = ? AND (i.generic_info_item_flags & ?) > 0
	`

	rows, err := idb.db.Query(query, charID, Temporary)
	if err != nil {
		return nil, fmt.Errorf("failed to query temporary items: %v", err)
	}
	defer rows.Close()

	var tempItems []*Item
	for rows.Next() {
		item, err := idb.scanCharacterItemFromRow(rows, masterList)
		if err != nil {
			log.Printf("Error scanning temporary item: %v", err)
			continue
		}

		if item != nil {
			tempItems = append(tempItems, item)
		}
	}

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

	return tempItems, nil
}

// CleanupExpiredItems removes expired temporary items from the database
func (idb *ItemDatabase) CleanupExpiredItems(charID uint32) error {
	// This would typically check item expiration times and remove expired items
	// For now, this is a placeholder implementation
	
	query := `
		DELETE FROM character_items 
		WHERE char_id = ? 
		AND item_id IN (
			SELECT id FROM items 
			WHERE (generic_info_item_flags & ?) > 0
			AND created < datetime('now', '-1 day')
		)
	`

	result, err := idb.db.Exec(query, charID, Temporary)
	if err != nil {
		return fmt.Errorf("failed to cleanup expired items: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %v", err)
	}

	if rowsAffected > 0 {
		log.Printf("Cleaned up %d expired items for character %d", rowsAffected, charID)
	}

	return nil
}

// UpdateItemLocation updates an item's location in the database
func (idb *ItemDatabase) UpdateItemLocation(charID uint32, uniqueID int64, invSlotID int32, slotID int16, bagID int32) error {
	query := `
		UPDATE character_items 
		SET inv_slot_id = ?, slot_id = ?, bag_id = ?
		WHERE char_id = ? AND unique_id = ?
	`

	result, err := idb.db.Exec(query, invSlotID, slotID, bagID, charID, uniqueID)
	if err != nil {
		return fmt.Errorf("failed to update item location: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %v", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("no item found with unique_id %d for character %d", uniqueID, charID)
	}

	return nil
}

// UpdateItemCount updates an item's count in the database
func (idb *ItemDatabase) UpdateItemCount(charID uint32, uniqueID int64, count int16) error {
	query := `
		UPDATE character_items 
		SET count = ?, details_count = ?
		WHERE char_id = ? AND unique_id = ?
	`

	result, err := idb.db.Exec(query, count, count, charID, uniqueID)
	if err != nil {
		return fmt.Errorf("failed to update item count: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %v", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("no item found with unique_id %d for character %d", uniqueID, charID)
	}

	return nil
}

// GetCharacterItemCount returns the number of items a character has
func (idb *ItemDatabase) GetCharacterItemCount(charID uint32) (int32, error) {
	query := `SELECT COUNT(*) FROM character_items WHERE char_id = ?`

	var count int32
	err := idb.db.QueryRow(query, charID).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("failed to get character item count: %v", err)
	}

	return count, nil
}

// GetCharacterItemsByBag returns all items in a specific bag for a character
func (idb *ItemDatabase) GetCharacterItemsByBag(charID uint32, bagID int32, masterList *MasterItemList) ([]*Item, error) {
	query := `
		SELECT item_id, unique_id, inv_slot_id, slot_id, appearance_type, icon, icon2,
			   count, tier, bag_id, details_count, creator, adornment_slot0, adornment_slot1,
			   adornment_slot2, group_id, creator_app, random_seed
		FROM character_items
		WHERE char_id = ? AND bag_id = ?
		ORDER BY slot_id
	`

	rows, err := idb.db.Query(query, charID, bagID)
	if err != nil {
		return nil, fmt.Errorf("failed to query character items by bag: %v", err)
	}
	defer rows.Close()

	var items []*Item
	for rows.Next() {
		item, err := idb.scanCharacterItemFromRow(rows, masterList)
		if err != nil {
			log.Printf("Error scanning character item from row: %v", err)
			continue
		}

		if item != nil {
			items = append(items, item)
		}
	}

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

	return items, nil
}