package repository

import (
	"time"

	"git.mchus.pro/mchus/priceforge/internal/models"
	"gorm.io/gorm"
)

type StatsRepository struct {
	db *gorm.DB
}

func NewStatsRepository(db *gorm.DB) *StatsRepository {
	return &StatsRepository{db: db}
}

func (r *StatsRepository) GetByLotName(lotName string) (*models.ComponentUsageStats, error) {
	var stats models.ComponentUsageStats
	err := r.db.Where("lot_name = ?", lotName).First(&stats).Error
	if err != nil {
		return nil, err
	}
	return &stats, nil
}

func (r *StatsRepository) Upsert(stats *models.ComponentUsageStats) error {
	return r.db.Save(stats).Error
}

func (r *StatsRepository) IncrementUsage(lotName string, quantity int, revenue float64) error {
	now := time.Now()

	result := r.db.Model(&models.ComponentUsageStats{}).
		Where("lot_name = ?", lotName).
		Updates(map[string]interface{}{
			"quotes_total":    gorm.Expr("quotes_total + 1"),
			"quotes_last30d": gorm.Expr("quotes_last30d + 1"),
			"quotes_last7d":  gorm.Expr("quotes_last7d + 1"),
			"total_quantity":  gorm.Expr("total_quantity + ?", quantity),
			"total_revenue":   gorm.Expr("total_revenue + ?", revenue),
			"last_used_at":    now,
		})

	if result.RowsAffected == 0 {
		stats := &models.ComponentUsageStats{
			LotName:       lotName,
			QuotesTotal:   1,
			QuotesLast30d: 1,
			QuotesLast7d:  1,
			TotalQuantity: quantity,
			TotalRevenue:  revenue,
			LastUsedAt:    &now,
		}
		return r.db.Create(stats).Error
	}

	return result.Error
}

func (r *StatsRepository) GetTopComponents(limit int) ([]models.ComponentUsageStats, error) {
	var stats []models.ComponentUsageStats
	err := r.db.
		Order("quotes_last30d DESC").
		Limit(limit).
		Find(&stats).Error
	return stats, err
}

func (r *StatsRepository) GetTrendingComponents(limit int) ([]models.ComponentUsageStats, error) {
	var stats []models.ComponentUsageStats
	err := r.db.
		Where("trend_direction = ? AND trend_percent > ?", models.TrendUp, 20).
		Order("trend_percent DESC").
		Limit(limit).
		Find(&stats).Error
	return stats, err
}

// ResetWeeklyCounters resets quotes_last_7d (run weekly via cron)
func (r *StatsRepository) ResetWeeklyCounters() error {
	return r.db.Model(&models.ComponentUsageStats{}).
		Where("1 = 1").
		Update("quotes_last7d", 0).Error
}

// ResetMonthlyCounters resets quotes_last30d (run monthly via cron)
func (r *StatsRepository) ResetMonthlyCounters() error {
	return r.db.Model(&models.ComponentUsageStats{}).
		Where("1 = 1").
		Update("quotes_last30d", 0).Error
}

// UpdatePopularityScores recalculates popularity_score in qt_lot_metadata
// based on supplier quotes from lot_log table
func (r *StatsRepository) UpdatePopularityScores() error {
	// Formula: popularity_score = quotes_last_30d * 3 + quotes_last_90d * 1 + quotes_total * 0.1
	// This gives more weight to recent supplier activity
	return r.db.Exec(`
		UPDATE qt_lot_metadata m
		LEFT JOIN (
			SELECT
				lot,
				COUNT(*) as quotes_total,
				SUM(CASE WHEN date >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as quotes_last_30d,
				SUM(CASE WHEN date >= DATE_SUB(NOW(), INTERVAL 90 DAY) THEN 1 ELSE 0 END) as quotes_last_90d
			FROM lot_log
			GROUP BY lot
		) s ON m.lot_name = s.lot
		SET m.popularity_score = COALESCE(
			s.quotes_last_30d * 3 + s.quotes_last_90d * 1 + s.quotes_total * 0.1,
			0
		)
		WHERE m.pricelist_type = 'estimate'
	`).Error
}