package repository

import (
	"errors"
	"time"

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

type CompetitorRepository struct {
	db *gorm.DB
}

func NewCompetitorRepository(db *gorm.DB) *CompetitorRepository {
	return &CompetitorRepository{db: db}
}

func (r *CompetitorRepository) List() ([]models.Competitor, error) {
	var competitors []models.Competitor
	if err := r.db.Order("name ASC").Find(&competitors).Error; err != nil {
		return nil, err
	}
	return competitors, nil
}

func (r *CompetitorRepository) GetByID(id uint64) (*models.Competitor, error) {
	var c models.Competitor
	if err := r.db.First(&c, id).Error; err != nil {
		return nil, err
	}
	return &c, nil
}

func (r *CompetitorRepository) Create(c *models.Competitor) error {
	return r.db.Create(c).Error
}

func (r *CompetitorRepository) Update(c *models.Competitor) error {
	return r.db.Save(c).Error
}

func (r *CompetitorRepository) Delete(id uint64) error {
	return r.db.Delete(&models.Competitor{}, id).Error
}

func (r *CompetitorRepository) SetActive(id uint64, active bool) error {
	return r.db.Model(&models.Competitor{}).Where("id = ?", id).Update("is_active", active).Error
}

// GetLastQuoteForPN returns the most recent quote for (competitor_id, partnumber).
// Used for deduplication at import time: skip if price and qty unchanged.
func (r *CompetitorRepository) GetLastQuoteForPN(competitorID uint64, partnumber string) (price float64, qty float64, found bool, err error) {
	var quote models.CompetitorQuote
	dbErr := r.db.
		Where("competitor_id = ? AND partnumber = ?", competitorID, partnumber).
		Order("date DESC, id DESC").
		First(&quote).Error
	if errors.Is(dbErr, gorm.ErrRecordNotFound) {
		return 0, 0, false, nil
	}
	if dbErr != nil {
		return 0, 0, false, dbErr
	}
	return quote.Price, quote.Qty, true, nil
}

// InsertQuote inserts a new competitor quote row.
func (r *CompetitorRepository) InsertQuote(q *models.CompetitorQuote) error {
	return r.db.Create(q).Error
}

// BulkInsertQuotes inserts multiple quotes in batches of 500.
func (r *CompetitorRepository) BulkInsertQuotes(quotes []*models.CompetitorQuote) error {
	if len(quotes) == 0 {
		return nil
	}
	return r.db.CreateInBatches(quotes, 500).Error
}

// GetLatestQuotesByPN returns the most recent (price, qty) per partnumber for a competitor.
// Used when building the pricelist; p/n → lot resolution happens in the service layer.
func (r *CompetitorRepository) GetLatestQuotesByPN(competitorID uint64) ([]models.CompetitorQuote, error) {
	var quotes []models.CompetitorQuote
	err := r.db.Raw(`
		SELECT plc.*
		FROM partnumber_log_competitors plc
		INNER JOIN (
			SELECT partnumber, MAX(date) AS max_date
			FROM partnumber_log_competitors
			WHERE competitor_id = ? AND qty > 0
			GROUP BY partnumber
		) latest ON plc.partnumber = latest.partnumber
			AND plc.date = latest.max_date
		WHERE plc.competitor_id = ? AND plc.qty > 0
		ORDER BY plc.partnumber ASC
	`, competitorID, competitorID).Scan(&quotes).Error
	return quotes, err
}

// GetLatestQuotesAllCompetitors returns the most recent quote per (competitor_id, partnumber)
// across ALL active competitors. Used when building the combined competitor pricelist.
func (r *CompetitorRepository) GetLatestQuotesAllCompetitors() ([]models.CompetitorQuote, error) {
	var quotes []models.CompetitorQuote
	err := r.db.Raw(`
		SELECT plc.*
		FROM partnumber_log_competitors plc
		INNER JOIN (
			SELECT competitor_id, partnumber, MAX(date) AS max_date
			FROM partnumber_log_competitors
			GROUP BY competitor_id, partnumber
		) latest ON plc.competitor_id = latest.competitor_id
			AND plc.partnumber = latest.partnumber
			AND plc.date = latest.max_date
		INNER JOIN qt_competitors c ON c.id = plc.competitor_id AND c.is_active = 1
		ORDER BY plc.competitor_id, plc.partnumber ASC
	`).Scan(&quotes).Error
	return quotes, err
}

// CompetitorQuoteCounts holds aggregate quote statistics for one competitor.
type CompetitorQuoteCounts struct {
	CompetitorID uint64 `gorm:"column:competitor_id"`
	UniquePN     int64  `gorm:"column:unique_pn"`
	TotalQuotes  int64  `gorm:"column:total_quotes"`
}

// GetQuoteCountsByCompetitor returns unique p/n count and total quote count per competitor.
func (r *CompetitorRepository) GetQuoteCountsByCompetitor() ([]CompetitorQuoteCounts, error) {
	var results []CompetitorQuoteCounts
	err := r.db.Raw(`
		SELECT competitor_id,
		       COUNT(DISTINCT partnumber) AS unique_pn,
		       COUNT(*) AS total_quotes
		FROM partnumber_log_competitors
		GROUP BY competitor_id
	`).Scan(&results).Error
	return results, err
}

// ListQuotes returns all quotes for a competitor since a given date.
func (r *CompetitorRepository) ListQuotes(competitorID uint64, since time.Time) ([]models.CompetitorQuote, error) {
	var quotes []models.CompetitorQuote
	q := r.db.Where("competitor_id = ?", competitorID)
	if !since.IsZero() {
		q = q.Where("date >= ?", since)
	}
	if err := q.Order("date DESC, partnumber ASC").Find(&quotes).Error; err != nil {
		return nil, err
	}
	return quotes, nil
}