package store

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

// ShoppingStore is a registered store, with its sections.
type ShoppingStore struct {
	ID       int64
	Name     string
	Sections []ShoppingSection
}

// ShoppingSection is an aisle or grouping within a store.
type ShoppingSection struct {
	ID      int64
	StoreID int64
	Name    string
}

// ErrStoreNotFound is returned when an operation names a store that is not
// registered. Callers surface this rather than silently creating one, so a
// typo does not quietly produce a second store.
var ErrStoreNotFound = errors.New("store not found")

// GetShoppingStores returns every registered store with its sections.
func (s *Store) GetShoppingStores() ([]ShoppingStore, error) {
	rows, err := s.db.Query(`SELECT id, name FROM shopping_stores ORDER BY sort_order, name`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var stores []ShoppingStore
	byID := map[int64]int{}
	for rows.Next() {
		var st ShoppingStore
		if err := rows.Scan(&st.ID, &st.Name); err != nil {
			return nil, err
		}
		byID[st.ID] = len(stores)
		stores = append(stores, st)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	secRows, err := s.db.Query(`SELECT id, store_id, name FROM shopping_sections ORDER BY sort_order, name`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = secRows.Close() }()

	for secRows.Next() {
		var sec ShoppingSection
		if err := secRows.Scan(&sec.ID, &sec.StoreID, &sec.Name); err != nil {
			return nil, err
		}
		if idx, ok := byID[sec.StoreID]; ok {
			stores[idx].Sections = append(stores[idx].Sections, sec)
		}
	}
	return stores, secRows.Err()
}

// AddShoppingStore registers a store. Adding one that already exists is a
// no-op rather than an error — the intent ("there should be a store called
// this") is already satisfied.
func (s *Store) AddShoppingStore(name string) error {
	name = strings.TrimSpace(name)
	if name == "" {
		return fmt.Errorf("store name is required")
	}
	_, err := s.db.Exec(`INSERT OR IGNORE INTO shopping_stores (name) VALUES (?)`, name)
	return err
}

// RenameShoppingStore renames a store and moves its items with it.
//
// Items reference a store by name, so the two have to change together or every
// item in the store is orphaned. Done in one transaction for that reason.
func (s *Store) RenameShoppingStore(id int64, newName string) error {
	newName = strings.TrimSpace(newName)
	if newName == "" {
		return fmt.Errorf("store name is required")
	}

	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	var oldName string
	if err := tx.QueryRow(`SELECT name FROM shopping_stores WHERE id = ?`, id).Scan(&oldName); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrStoreNotFound
		}
		return err
	}
	if _, err := tx.Exec(`UPDATE shopping_stores SET name = ? WHERE id = ?`, newName, id); err != nil {
		return err
	}
	if _, err := tx.Exec(`UPDATE user_shopping_items SET store = ? WHERE store = ?`, newName, oldName); err != nil {
		return err
	}
	return tx.Commit()
}

// DeleteShoppingStore removes a store. Its items are not deleted; they are
// moved to unassigned so nothing silently disappears with the store.
func (s *Store) DeleteShoppingStore(id int64) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	var name string
	if err := tx.QueryRow(`SELECT name FROM shopping_stores WHERE id = ?`, id).Scan(&name); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrStoreNotFound
		}
		return err
	}
	if _, err := tx.Exec(
		`UPDATE user_shopping_items SET store = 'Unassigned', section = '' WHERE store = ?`, name); err != nil {
		return err
	}
	// Sections go with the store via ON DELETE CASCADE.
	if _, err := tx.Exec(`DELETE FROM shopping_stores WHERE id = ?`, id); err != nil {
		return err
	}
	return tx.Commit()
}

// AddShoppingSection adds a section to a store.
func (s *Store) AddShoppingSection(storeID int64, name string) error {
	name = strings.TrimSpace(name)
	if name == "" {
		return fmt.Errorf("section name is required")
	}
	_, err := s.db.Exec(
		`INSERT OR IGNORE INTO shopping_sections (store_id, name) VALUES (?, ?)`, storeID, name)
	return err
}

// DeleteShoppingSection removes a section, clearing it from its items rather
// than deleting them.
func (s *Store) DeleteShoppingSection(id int64) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	var storeName, sectionName string
	err = tx.QueryRow(`
		SELECT st.name, se.name
		FROM shopping_sections se JOIN shopping_stores st ON st.id = se.store_id
		WHERE se.id = ?`, id).Scan(&storeName, &sectionName)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil // already gone
		}
		return err
	}
	if _, err := tx.Exec(
		`UPDATE user_shopping_items SET section = '' WHERE store = ? AND section = ?`,
		storeName, sectionName); err != nil {
		return err
	}
	if _, err := tx.Exec(`DELETE FROM shopping_sections WHERE id = ?`, id); err != nil {
		return err
	}
	return tx.Commit()
}

// ShoppingStoreNames returns just the registered store names, for pickers.
func (s *Store) ShoppingStoreNames() ([]string, error) {
	stores, err := s.GetShoppingStores()
	if err != nil {
		return nil, err
	}
	names := make([]string, 0, len(stores))
	for _, st := range stores {
		names = append(names, st.Name)
	}
	return names, nil
}