package postgres

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"time"

	"github.com/jmoiron/sqlx"

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

// PreferenceRepository implements port.PreferenceRepository using PostgreSQL.
type PreferenceRepository struct {
	db *sqlx.DB
}

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

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

// preferenceRow represents a database row from the preferences table.
type preferenceRow struct {
	UserID      string    `db:"user_id"`
	Preferences []byte    `db:"preferences"`
	CreatedAt   time.Time `db:"created_at"`
	UpdatedAt   time.Time `db:"updated_at"`
}

// Get returns preferences for a user.
// Returns nil, nil when no row exists.
func (r *PreferenceRepository) Get(ctx context.Context, userID string) (*domain.UserPreferences, error) {
	var row preferenceRow
	err := r.db.GetContext(ctx, &row,
		`SELECT user_id, preferences, created_at, updated_at FROM preferences WHERE user_id = $1`,
		userID,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}

	prefs := make(map[string]any)
	if err := json.Unmarshal(row.Preferences, &prefs); err != nil {
		return nil, err
	}

	return &domain.UserPreferences{
		UserID:      row.UserID,
		Preferences: prefs,
		CreatedAt:   row.CreatedAt,
		UpdatedAt:   row.UpdatedAt,
	}, nil
}

// Upsert creates or updates preferences for a user.
func (r *PreferenceRepository) Upsert(ctx context.Context, prefs *domain.UserPreferences) error {
	prefsJSON, err := json.Marshal(prefs.Preferences)
	if err != nil {
		return err
	}

	_, err = r.db.ExecContext(ctx,
		`INSERT INTO preferences (user_id, preferences, updated_at)
		VALUES ($1, $2, NOW())
		ON CONFLICT (user_id) DO UPDATE
		SET preferences = $2, updated_at = NOW()`,
		prefs.UserID, prefsJSON,
	)
	return err
}