package db

import (
	"context"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
)

// ItemMacro represents a row in the item_macros table.
type ItemMacro struct {
	ID             string
	ItemID         string
	Filename       string
	Trigger        string
	Content        string
	RevisionNumber int
	CreatedAt      time.Time
}

// ItemMacroRepository provides item_macros database operations.
type ItemMacroRepository struct {
	db *DB
}

// NewItemMacroRepository creates a new item macro repository.
func NewItemMacroRepository(db *DB) *ItemMacroRepository {
	return &ItemMacroRepository{db: db}
}

// ReplaceForItem atomically replaces all macros for an item.
// Deletes existing rows and inserts the new set.
func (r *ItemMacroRepository) ReplaceForItem(ctx context.Context, itemID string, revisionNumber int, macros []*ItemMacro) error {
	return r.db.Tx(ctx, func(tx pgx.Tx) error {
		_, err := tx.Exec(ctx, `DELETE FROM item_macros WHERE item_id = $1`, itemID)
		if err != nil {
			return fmt.Errorf("deleting old macros: %w", err)
		}

		for _, m := range macros {
			_, err := tx.Exec(ctx, `
				INSERT INTO item_macros (item_id, filename, trigger, content, revision_number)
				VALUES ($1, $2, $3, $4, $5)
			`, itemID, m.Filename, m.Trigger, m.Content, revisionNumber)
			if err != nil {
				return fmt.Errorf("inserting macro %s: %w", m.Filename, err)
			}
		}
		return nil
	})
}

// ListByItem returns all macros for an item (without content), ordered by filename.
func (r *ItemMacroRepository) ListByItem(ctx context.Context, itemID string) ([]*ItemMacro, error) {
	rows, err := r.db.pool.Query(ctx, `
		SELECT id, item_id, filename, trigger, revision_number, created_at
		FROM item_macros
		WHERE item_id = $1
		ORDER BY filename
	`, itemID)
	if err != nil {
		return nil, fmt.Errorf("listing macros: %w", err)
	}
	defer rows.Close()

	var macros []*ItemMacro
	for rows.Next() {
		m := &ItemMacro{}
		if err := rows.Scan(&m.ID, &m.ItemID, &m.Filename, &m.Trigger, &m.RevisionNumber, &m.CreatedAt); err != nil {
			return nil, fmt.Errorf("scanning macro: %w", err)
		}
		macros = append(macros, m)
	}
	return macros, nil
}

// GetByFilename returns a single macro by item ID and filename, including content.
func (r *ItemMacroRepository) GetByFilename(ctx context.Context, itemID string, filename string) (*ItemMacro, error) {
	m := &ItemMacro{}
	err := r.db.pool.QueryRow(ctx, `
		SELECT id, item_id, filename, trigger, content, revision_number, created_at
		FROM item_macros
		WHERE item_id = $1 AND filename = $2
	`, itemID, filename).Scan(&m.ID, &m.ItemID, &m.Filename, &m.Trigger, &m.Content, &m.RevisionNumber, &m.CreatedAt)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("getting macro: %w", err)
	}
	return m, nil
}