package store

import (
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"
)

type UserShoppingItem struct {
	ID        int64
	Name      string
	Store     string
	Section   string
	Checked   bool
	CreatedAt time.Time
}

// SaveUserShoppingItem saves a new user shopping item
func (s *Store) SaveUserShoppingItem(name, store string) error {
	return s.SaveUserShoppingItemInSection(name, store, "")
}

// SaveUserShoppingItemInSection saves a new item filed under a section.
func (s *Store) SaveUserShoppingItemInSection(name, store, section string) error {
	_, err := s.db.Exec(
		`INSERT INTO user_shopping_items (name, store, section) VALUES (?, ?, ?)`,
		strings.TrimSpace(name), strings.TrimSpace(store), strings.TrimSpace(section))
	return err
}

// UpdateUserShoppingItem renames an item and/or moves it between stores and
// sections. Everything an edit can change goes through here, so a partial
// update cannot leave an item half-moved.
func (s *Store) UpdateUserShoppingItem(id int64, name, store, section string) error {
	name = strings.TrimSpace(name)
	store = strings.TrimSpace(store)
	section = strings.TrimSpace(section)
	if name == "" {
		return fmt.Errorf("item name is required")
	}
	if store == "" {
		return fmt.Errorf("store is required")
	}

	res, err := s.db.Exec(
		`UPDATE user_shopping_items SET name = ?, store = ?, section = ? WHERE id = ?`,
		name, store, section, id)
	if err != nil {
		return err
	}
	// An UPDATE that matches nothing succeeds at the SQL level, so a stale or
	// wrong id would otherwise look like a successful edit.
	if n, err := res.RowsAffected(); err == nil && n == 0 {
		return ErrItemNotFound
	}
	return nil
}

// GetUserShoppingItem returns a single item, or ErrItemNotFound.
func (s *Store) GetUserShoppingItem(id int64) (*UserShoppingItem, error) {
	var item UserShoppingItem
	err := s.db.QueryRow(
		`SELECT id, name, store, COALESCE(section, ''), checked, created_at
		 FROM user_shopping_items WHERE id = ?`, id).
		Scan(&item.ID, &item.Name, &item.Store, &item.Section, &item.Checked, &item.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrItemNotFound
	}
	if err != nil {
		return nil, err
	}
	return &item, nil
}

// ErrItemNotFound is returned when an item id matches nothing.
var ErrItemNotFound = errors.New("shopping item not found")

// GetUserShoppingItems retrieves all user shopping items
func (s *Store) GetUserShoppingItems() ([]UserShoppingItem, error) {
	rows, err := s.db.Query(`SELECT id, name, store, COALESCE(section, ''), checked, created_at FROM user_shopping_items ORDER BY store, created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var items []UserShoppingItem
	for rows.Next() {
		var item UserShoppingItem
		if err := rows.Scan(&item.ID, &item.Name, &item.Store, &item.Section, &item.Checked, &item.CreatedAt); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

// ToggleUserShoppingItem toggles the checked state of a user shopping item
func (s *Store) ToggleUserShoppingItem(id int64, checked bool) error {
	_, err := s.db.Exec(`UPDATE user_shopping_items SET checked = ? WHERE id = ?`, checked, id)
	return err
}

// DeleteUserShoppingItem removes a user shopping item
func (s *Store) DeleteUserShoppingItem(id int64) error {
	_, err := s.db.Exec(`DELETE FROM user_shopping_items WHERE id = ?`, id)
	return err
}

// SetShoppingItemChecked sets the checked state for an external shopping item
func (s *Store) SetShoppingItemChecked(source, itemID string, checked bool) error {
	checkedInt := 0
	if checked {
		checkedInt = 1
	}
	_, err := s.db.Exec(`
		INSERT INTO shopping_item_checks (source, item_id, checked, updated_at)
		VALUES (?, ?, ?, CURRENT_TIMESTAMP)
		ON CONFLICT(source, item_id) DO UPDATE SET checked = ?, updated_at = CURRENT_TIMESTAMP
	`, source, itemID, checkedInt, checkedInt)
	return err
}

// GetShoppingItemChecks returns a map of item_id -> checked for a given source
func (s *Store) GetShoppingItemChecks(source string) (map[string]bool, error) {
	rows, err := s.db.Query(`SELECT item_id, checked FROM shopping_item_checks WHERE source = ?`, source)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	checks := make(map[string]bool)
	for rows.Next() {
		var itemID string
		var checked int
		if err := rows.Scan(&itemID, &checked); err != nil {
			return nil, err
		}
		checks[itemID] = checked == 1
	}
	return checks, rows.Err()
}