felt/internal/template/payout.go

package template

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

// PayoutStructure represents a reusable payout configuration with entry-count brackets.
type PayoutStructure struct {
	ID        int64           `json:"id"`
	Name      string          `json:"name"`
	IsBuiltin bool            `json:"is_builtin"`
	Brackets  []PayoutBracket `json:"brackets,omitempty"`
	CreatedAt time.Time       `json:"created_at"`
	UpdatedAt time.Time       `json:"updated_at"`
}

// PayoutBracket defines a payout tier set for a range of entry counts.
type PayoutBracket struct {
	ID          int64        `json:"id,omitempty"`
	StructureID int64        `json:"structure_id,omitempty"`
	MinEntries  int          `json:"min_entries"`
	MaxEntries  int          `json:"max_entries"`
	Tiers       []PayoutTier `json:"tiers"`
}

// PayoutTier represents a single position's payout percentage within a bracket.
type PayoutTier struct {
	ID                     int64 `json:"id,omitempty"`
	BracketID              int64 `json:"bracket_id,omitempty"`
	Position               int   `json:"position"`
	PercentageBasisPoints  int64 `json:"percentage_basis_points"`
}

// PayoutService provides CRUD operations for payout structures.
type PayoutService struct {
	db *sql.DB
}

// NewPayoutService creates a new PayoutService.
func NewPayoutService(db *sql.DB) *PayoutService {
	return &PayoutService{db: db}
}

// validateBrackets checks bracket and tier validity.
func validateBrackets(brackets []PayoutBracket) error {
	if len(brackets) == 0 {
		return fmt.Errorf("at least one bracket is required")
	}

	for i, b := range brackets {
		if b.MinEntries > b.MaxEntries {
			return fmt.Errorf("bracket %d: min_entries (%d) > max_entries (%d)", i, b.MinEntries, b.MaxEntries)
		}
		if len(b.Tiers) == 0 {
			return fmt.Errorf("bracket %d: at least one tier is required", i)
		}

		// Check tier sum = 10000 basis points (100.00%)
		var sum int64
		for _, t := range b.Tiers {
			if t.PercentageBasisPoints <= 0 {
				return fmt.Errorf("bracket %d: tier position %d has non-positive percentage", i, t.Position)
			}
			sum += t.PercentageBasisPoints
		}
		if sum != 10000 {
			return fmt.Errorf("bracket %d: tier percentages sum to %d basis points, expected 10000 (100.00%%)", i, sum)
		}

		// Check contiguous with previous bracket
		if i > 0 {
			prev := brackets[i-1]
			if b.MinEntries != prev.MaxEntries+1 {
				return fmt.Errorf("bracket %d: min_entries (%d) is not contiguous with previous bracket max_entries (%d)", i, b.MinEntries, prev.MaxEntries)
			}
		}
	}

	return nil
}

// CreatePayoutStructure creates a new payout structure with brackets and tiers.
func (s *PayoutService) CreatePayoutStructure(ctx context.Context, name string, brackets []PayoutBracket) (*PayoutStructure, error) {
	if name == "" {
		return nil, fmt.Errorf("payout structure name is required")
	}
	if err := validateBrackets(brackets); err != nil {
		return nil, err
	}

	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, fmt.Errorf("begin transaction: %w", err)
	}
	defer tx.Rollback()

	now := time.Now().Unix()
	res, err := tx.ExecContext(ctx,
		"INSERT INTO payout_structures (name, is_builtin, created_at, updated_at) VALUES (?, 0, ?, ?)",
		name, now, now,
	)
	if err != nil {
		return nil, fmt.Errorf("insert payout structure: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("get payout structure id: %w", err)
	}

	if err := insertBrackets(ctx, tx, id, brackets); err != nil {
		return nil, err
	}

	if err := tx.Commit(); err != nil {
		return nil, fmt.Errorf("commit: %w", err)
	}

	return s.GetPayoutStructure(ctx, id)
}

// insertBrackets inserts brackets and tiers within a transaction.
func insertBrackets(ctx context.Context, tx *sql.Tx, structureID int64, brackets []PayoutBracket) error {
	for i, b := range brackets {
		res, err := tx.ExecContext(ctx,
			"INSERT INTO payout_brackets (structure_id, min_entries, max_entries) VALUES (?, ?, ?)",
			structureID, b.MinEntries, b.MaxEntries,
		)
		if err != nil {
			return fmt.Errorf("insert bracket %d: %w", i, err)
		}
		bracketID, err := res.LastInsertId()
		if err != nil {
			return fmt.Errorf("get bracket %d id: %w", i, err)
		}

		for j, t := range b.Tiers {
			_, err := tx.ExecContext(ctx,
				"INSERT INTO payout_tiers (bracket_id, position, percentage_basis_points) VALUES (?, ?, ?)",
				bracketID, t.Position, t.PercentageBasisPoints,
			)
			if err != nil {
				return fmt.Errorf("insert tier %d/%d: %w", i, j, err)
			}
		}
	}
	return nil
}

// GetPayoutStructure retrieves a payout structure by ID, including brackets and tiers.
func (s *PayoutService) GetPayoutStructure(ctx context.Context, id int64) (*PayoutStructure, error) {
	ps := &PayoutStructure{}
	var isBuiltin int
	var createdAt, updatedAt int64

	err := s.db.QueryRowContext(ctx,
		"SELECT id, name, is_builtin, created_at, updated_at FROM payout_structures WHERE id = ?", id,
	).Scan(&ps.ID, &ps.Name, &isBuiltin, &createdAt, &updatedAt)
	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("payout structure not found: %d", id)
	}
	if err != nil {
		return nil, fmt.Errorf("get payout structure: %w", err)
	}
	ps.IsBuiltin = isBuiltin != 0
	ps.CreatedAt = time.Unix(createdAt, 0)
	ps.UpdatedAt = time.Unix(updatedAt, 0)

	// Load brackets
	bracketRows, err := s.db.QueryContext(ctx,
		"SELECT id, structure_id, min_entries, max_entries FROM payout_brackets WHERE structure_id = ? ORDER BY min_entries", id,
	)
	if err != nil {
		return nil, fmt.Errorf("get brackets: %w", err)
	}
	defer bracketRows.Close()

	for bracketRows.Next() {
		var b PayoutBracket
		if err := bracketRows.Scan(&b.ID, &b.StructureID, &b.MinEntries, &b.MaxEntries); err != nil {
			return nil, fmt.Errorf("scan bracket: %w", err)
		}
		ps.Brackets = append(ps.Brackets, b)
	}
	if err := bracketRows.Err(); err != nil {
		return nil, fmt.Errorf("iterate brackets: %w", err)
	}

	// Load tiers for each bracket
	for i := range ps.Brackets {
		tierRows, err := s.db.QueryContext(ctx,
			"SELECT id, bracket_id, position, percentage_basis_points FROM payout_tiers WHERE bracket_id = ? ORDER BY position",
			ps.Brackets[i].ID,
		)
		if err != nil {
			return nil, fmt.Errorf("get tiers for bracket %d: %w", i, err)
		}

		for tierRows.Next() {
			var t PayoutTier
			if err := tierRows.Scan(&t.ID, &t.BracketID, &t.Position, &t.PercentageBasisPoints); err != nil {
				tierRows.Close()
				return nil, fmt.Errorf("scan tier: %w", err)
			}
			ps.Brackets[i].Tiers = append(ps.Brackets[i].Tiers, t)
		}
		tierRows.Close()
		if err := tierRows.Err(); err != nil {
			return nil, fmt.Errorf("iterate tiers: %w", err)
		}
	}

	return ps, nil
}

// ListPayoutStructures returns all payout structures (without nested data).
func (s *PayoutService) ListPayoutStructures(ctx context.Context) ([]PayoutStructure, error) {
	rows, err := s.db.QueryContext(ctx,
		"SELECT id, name, is_builtin, created_at, updated_at FROM payout_structures ORDER BY is_builtin DESC, name",
	)
	if err != nil {
		return nil, fmt.Errorf("list payout structures: %w", err)
	}
	defer rows.Close()

	var structs []PayoutStructure
	for rows.Next() {
		var ps PayoutStructure
		var isBuiltin int
		var createdAt, updatedAt int64
		if err := rows.Scan(&ps.ID, &ps.Name, &isBuiltin, &createdAt, &updatedAt); err != nil {
			return nil, fmt.Errorf("scan payout structure: %w", err)
		}
		ps.IsBuiltin = isBuiltin != 0
		ps.CreatedAt = time.Unix(createdAt, 0)
		ps.UpdatedAt = time.Unix(updatedAt, 0)
		structs = append(structs, ps)
	}
	return structs, rows.Err()
}

// UpdatePayoutStructure updates a payout structure and replaces its brackets/tiers.
func (s *PayoutService) UpdatePayoutStructure(ctx context.Context, id int64, name string, brackets []PayoutBracket) error {
	if name == "" {
		return fmt.Errorf("payout structure name is required")
	}
	if err := validateBrackets(brackets); err != nil {
		return err
	}

	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer tx.Rollback()

	now := time.Now().Unix()
	res, err := tx.ExecContext(ctx,
		"UPDATE payout_structures SET name = ?, updated_at = ? WHERE id = ?",
		name, now, id,
	)
	if err != nil {
		return fmt.Errorf("update payout structure: %w", err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return fmt.Errorf("payout structure not found: %d", id)
	}

	// Delete old brackets (cascades to tiers)
	if _, err := tx.ExecContext(ctx, "DELETE FROM payout_brackets WHERE structure_id = ?", id); err != nil {
		return fmt.Errorf("delete old brackets: %w", err)
	}

	if err := insertBrackets(ctx, tx, id, brackets); err != nil {
		return err
	}

	return tx.Commit()
}

// DeletePayoutStructure deletes a payout structure. Built-in structures cannot be deleted.
func (s *PayoutService) DeletePayoutStructure(ctx context.Context, id int64) error {
	var isBuiltin int
	err := s.db.QueryRowContext(ctx, "SELECT is_builtin FROM payout_structures WHERE id = ?", id).Scan(&isBuiltin)
	if err == sql.ErrNoRows {
		return fmt.Errorf("payout structure not found: %d", id)
	}
	if err != nil {
		return fmt.Errorf("check payout structure: %w", err)
	}
	if isBuiltin != 0 {
		return fmt.Errorf("cannot delete built-in payout structure")
	}

	// Check if referenced by active tournaments
	var refCount int
	err = s.db.QueryRowContext(ctx,
		"SELECT COUNT(*) FROM tournaments WHERE payout_structure_id = ? AND status NOT IN ('completed', 'cancelled')", id,
	).Scan(&refCount)
	if err != nil {
		return fmt.Errorf("check references: %w", err)
	}
	if refCount > 0 {
		return fmt.Errorf("payout structure is referenced by %d active tournament(s)", refCount)
	}

	res, err := s.db.ExecContext(ctx, "DELETE FROM payout_structures WHERE id = ?", id)
	if err != nil {
		return fmt.Errorf("delete payout structure: %w", err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return fmt.Errorf("payout structure not found: %d", id)
	}
	return nil
}

// DuplicatePayoutStructure creates an independent copy of a payout structure.
func (s *PayoutService) DuplicatePayoutStructure(ctx context.Context, id int64, newName string) (*PayoutStructure, error) {
	original, err := s.GetPayoutStructure(ctx, id)
	if err != nil {
		return nil, fmt.Errorf("get original: %w", err)
	}

	if newName == "" {
		newName = original.Name + " (Copy)"
	}

	return s.CreatePayoutStructure(ctx, newName, original.Brackets)
}