package products

import (
	"bullet-cloud-api/internal/models"
	"context"
	"errors"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

var (
	ErrProductNotFound = errors.New("product not found")
)

// ProductRepository defines the interface for product data operations.
type ProductRepository interface {
	Create(ctx context.Context, product *models.Product) (*models.Product, error)
	FindByID(ctx context.Context, id uuid.UUID) (*models.Product, error)
	FindAll(ctx context.Context /* TODO: Add filtering/pagination params */) ([]models.Product, error)
	Search(ctx context.Context, query string) ([]models.Product, error)
	Update(ctx context.Context, id uuid.UUID, product *models.Product) (*models.Product, error)
	Delete(ctx context.Context, id uuid.UUID) error
}

// postgresProductRepository implements ProductRepository using PostgreSQL.
type postgresProductRepository struct {
	db *pgxpool.Pool
}

// NewPostgresProductRepository creates a new instance of postgresProductRepository.
func NewPostgresProductRepository(db *pgxpool.Pool) ProductRepository {
	return &postgresProductRepository{db: db}
}

// Create inserts a new product into the database.
func (r *postgresProductRepository) Create(ctx context.Context, product *models.Product) (*models.Product, error) {
	query := `
		INSERT INTO products (name, description, price, category_id)
		VALUES ($1, $2, $3, $4)
		RETURNING id, created_at, updated_at
	`
	err := r.db.QueryRow(ctx, query,
		product.Name,
		product.Description,
		product.Price,
		product.CategoryID,
	).Scan(&product.ID, &product.CreatedAt, &product.UpdatedAt)

	if err != nil {
		// TODO: Handle specific DB errors if necessary (e.g., FK violation if category_id becomes non-null)
		return nil, err
	}
	return product, nil
}

// FindByID retrieves a product by its ID.
func (r *postgresProductRepository) FindByID(ctx context.Context, id uuid.UUID) (*models.Product, error) {
	query := `
		SELECT id, name, description, price, category_id, created_at, updated_at
		FROM products
		WHERE id = $1
	`
	product := &models.Product{}
	err := r.db.QueryRow(ctx, query, id).Scan(
		&product.ID,
		&product.Name,
		&product.Description,
		&product.Price,
		&product.CategoryID,
		&product.CreatedAt,
		&product.UpdatedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrProductNotFound
		}
		return nil, err
	}
	return product, nil
}

// FindAll retrieves all products (potentially with pagination/filtering in the future).
func (r *postgresProductRepository) FindAll(ctx context.Context) ([]models.Product, error) {
	query := `
		SELECT id, name, description, price, category_id, created_at, updated_at
		FROM products
		ORDER BY created_at DESC -- Example ordering
		-- TODO: Add LIMIT and OFFSET for pagination
	`
	return r.scanProductRows(ctx, query)
}

// Search retrieves products that match the search query (name or description).
func (r *postgresProductRepository) Search(ctx context.Context, query string) ([]models.Product, error) {
	searchQuery := `
		SELECT id, name, description, price, category_id, created_at, updated_at
		FROM products
		WHERE name ILIKE $1 OR description ILIKE $1
		ORDER BY
			CASE
				WHEN name ILIKE $1 THEN 1
				ELSE 2
			END,
			created_at DESC
	`
	searchPattern := "%" + query + "%"
	return r.scanProductRows(ctx, searchQuery, searchPattern)
}

// scanProductRows is a helper function to scan multiple product rows and reduce code duplication.
func (r *postgresProductRepository) scanProductRows(ctx context.Context, query string, args ...interface{}) ([]models.Product, error) {
	rows, err := r.db.Query(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	products := make([]models.Product, 0)
	for rows.Next() {
		var product models.Product
		err := rows.Scan(
			&product.ID,
			&product.Name,
			&product.Description,
			&product.Price,
			&product.CategoryID,
			&product.CreatedAt,
			&product.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		products = append(products, product)
	}

	if rows.Err() != nil {
		return nil, rows.Err()
	}

	return products, nil
}

// Update modifies an existing product in the database.
func (r *postgresProductRepository) Update(ctx context.Context, id uuid.UUID, product *models.Product) (*models.Product, error) {
	query := `
		UPDATE products
		SET name = $1, description = $2, price = $3, category_id = $4, updated_at = NOW()
		WHERE id = $5
		RETURNING updated_at
	`
	// Note: We fetch updated_at generated by the DB trigger (or NOW() if no trigger)
	err := r.db.QueryRow(ctx, query,
		product.Name,
		product.Description,
		product.Price,
		product.CategoryID,
		id,
	).Scan(&product.UpdatedAt)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			// The WHERE id = $5 clause found no row, meaning the product doesn't exist
			return nil, ErrProductNotFound
		}
		// TODO: Handle other potential errors (e.g., FK violation)
		return nil, err
	}

	// Fill in the rest of the potentially unchanged data for the returned object
	product.ID = id
	// product.CreatedAt would need to be fetched separately if needed after update
	return product, nil
}

// Delete removes a product from the database.
func (r *postgresProductRepository) Delete(ctx context.Context, id uuid.UUID) error {
	query := `DELETE FROM products WHERE id = $1`
	result, err := r.db.Exec(ctx, query, id)
	if err != nil {
		return err
	}

	// Check if any row was actually deleted
	if result.RowsAffected() == 0 {
		return ErrProductNotFound
	}

	return nil
}
