package postgres

import (
	"context"
	"time"

	"github.com/jmoiron/sqlx"

	"git.threesix.ai/jordan/slack5-1770529463/services/preferences-api/internal/port"
)

// Compile-time verification that PreferenceRepository implements port.PreferenceRepository.
var _ port.PreferenceRepository = (*PreferenceRepository)(nil)

// preferenceRow is the database representation of a preference row.
type preferenceRow struct {
	UserID    string    `db:"user_id"`
	Key       string    `db:"key"`
	Value     string    `db:"value"`
	CreatedAt time.Time `db:"created_at"`
	UpdatedAt time.Time `db:"updated_at"`
}

// PreferenceRepository is a PostgreSQL implementation of port.PreferenceRepository.
type PreferenceRepository struct {
	db *sqlx.DB
}

// NewPreferenceRepository creates a new PostgreSQL preference repository.
func NewPreferenceRepository(db *sqlx.DB) *PreferenceRepository {
	return &PreferenceRepository{db: db}
}

// GetByUserID returns all stored preferences for a user.
func (r *PreferenceRepository) GetByUserID(ctx context.Context, userID string) ([]port.PreferenceRow, error) {
	var rows []preferenceRow
	err := r.db.SelectContext(ctx, &rows,
		`SELECT user_id, key, value, created_at, updated_at FROM user_preferences WHERE user_id = $1`,
		userID,
	)
	if err != nil {
		return nil, err
	}

	result := make([]port.PreferenceRow, len(rows))
	for i, row := range rows {
		result[i] = port.PreferenceRow{
			UserID:    row.UserID,
			Key:       row.Key,
			Value:     row.Value,
			CreatedAt: row.CreatedAt,
			UpdatedAt: row.UpdatedAt,
		}
	}
	return result, nil
}

// Upsert creates or updates a single preference for a user.
func (r *PreferenceRepository) Upsert(ctx context.Context, userID string, key string, value string) error {
	_, err := r.db.ExecContext(ctx,
		`INSERT INTO user_preferences (user_id, key, value, created_at, updated_at)
		 VALUES ($1, $2, $3, NOW(), NOW())
		 ON CONFLICT (user_id, key)
		 DO UPDATE SET value = EXCLUDED.value, updated_at = NOW()`,
		userID, key, value,
	)
	return err
}