package repository

import (
	"time"

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

type ComponentRepository struct {
	db *gorm.DB
}

func NewComponentRepository(db *gorm.DB) *ComponentRepository {
	return &ComponentRepository{db: db}
}

type ComponentFilter struct {
	Category     string
	Search       string
	HasPrice     bool
	ExcludeHidden bool
	SortField    string
	SortDir      string
}

func (r *ComponentRepository) List(filter ComponentFilter, offset, limit int) ([]models.LotMetadata, int64, error) {
	var total int64

	baseQuery := r.db.Table("qt_lot_metadata AS m").
		Joins("LEFT JOIN lot AS l ON l.lot_name = m.lot_name").
		Joins("LEFT JOIN qt_categories AS c ON c.id = m.category_id").
		Where("m.pricelist_type = 'estimate'")

	if filter.Category != "" {
		baseQuery = baseQuery.Where("c.code = ?", filter.Category)
	}
	if filter.Search != "" {
		search := "%" + filter.Search + "%"
		baseQuery = baseQuery.Where("m.lot_name LIKE ? OR m.model LIKE ?", search, search)
	}
	if filter.HasPrice {
		baseQuery = baseQuery.Where("m.current_price IS NOT NULL AND m.current_price > 0")
	}
	if filter.ExcludeHidden {
		baseQuery = baseQuery.Where("m.is_hidden = ? OR m.is_hidden IS NULL", false)
	}

	if err := baseQuery.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	// Apply sorting
	sortDir := "ASC"
	if filter.SortDir == "desc" {
		sortDir = "DESC"
	}

	query := baseQuery.Session(&gorm.Session{})
	switch filter.SortField {
	case "popularity_score":
		query = query.Order("m.popularity_score " + sortDir)
	case "current_price":
		query = query.Order("CASE WHEN m.current_price IS NULL OR m.current_price = 0 THEN 1 ELSE 0 END").
			Order("m.current_price " + sortDir)
	case "lot_name":
		query = query.Order("m.lot_name " + sortDir)
	case "quote_count":
		query = query.
			Select("m.*, l.lot_description AS lot_description, c.id AS category_join_id, c.code AS category_code, (SELECT COUNT(*) FROM lot_log WHERE lot_log.lot = m.lot_name) as quote_count_sort").
			Order("quote_count_sort " + sortDir)
	default:
		query = query.
			Order("CASE WHEN m.current_price IS NULL OR m.current_price = 0 THEN 1 ELSE 0 END").
			Order("m.popularity_score DESC")
	}

	type componentRow struct {
		models.LotMetadata
		LotDescription string  `gorm:"column:lot_description"`
		CategoryJoinID *uint   `gorm:"column:category_join_id"`
		CategoryCode   *string `gorm:"column:category_code"`
	}
	var rows []componentRow
	selectQuery := query
	if filter.SortField != "quote_count" {
		selectQuery = selectQuery.Select("m.*, l.lot_description AS lot_description, c.id AS category_join_id, c.code AS category_code")
	}
	err := selectQuery.
		Offset(offset).
		Limit(limit).
		Scan(&rows).Error
	if err != nil {
		return nil, total, err
	}

	components := make([]models.LotMetadata, len(rows))
	for i, row := range rows {
		comp := row.LotMetadata
		comp.Lot = &models.Lot{
			LotName:        comp.LotName,
			LotDescription: row.LotDescription,
		}
		if row.CategoryCode != nil || row.CategoryJoinID != nil {
			comp.Category = &models.Category{
				ID:   0,
				Code: "",
			}
			if row.CategoryJoinID != nil {
				comp.Category.ID = *row.CategoryJoinID
			}
			if row.CategoryCode != nil {
				comp.Category.Code = *row.CategoryCode
			}
		}
		components[i] = comp
	}

	return components, total, err
}

func (r *ComponentRepository) GetByLotName(lotName string) (*models.LotMetadata, error) {
	var component models.LotMetadata
	err := r.db.
		Preload("Lot").
		Preload("Category").
		Where("lot_name = ? AND pricelist_type = 'estimate'", lotName).
		First(&component).Error
	if err != nil {
		return nil, err
	}
	return &component, nil
}

func (r *ComponentRepository) GetMultiple(lotNames []string) ([]models.LotMetadata, error) {
	var components []models.LotMetadata
	err := r.db.
		Preload("Lot").
		Preload("Category").
		Where("lot_name IN ? AND pricelist_type = 'estimate'", lotNames).
		Find(&components).Error
	return components, err
}

func (r *ComponentRepository) Update(component *models.LotMetadata) error {
	return r.db.Save(component).Error
}

func (r *ComponentRepository) DB() *gorm.DB {
	return r.db
}

func (r *ComponentRepository) Create(component *models.LotMetadata) error {
	return r.db.Create(component).Error
}

func (r *ComponentRepository) IncrementRequestCount(lotName string) error {
	now := time.Now()
	return r.db.Model(&models.LotMetadata{}).
		Where("lot_name = ? AND pricelist_type = 'estimate'", lotName).
		Updates(map[string]interface{}{
			"request_count":     gorm.Expr("request_count + 1"),
			"last_request_date": now,
		}).Error
}

// GetAllLots returns all lots from the existing lot table
func (r *ComponentRepository) GetAllLots() ([]models.Lot, error) {
	var lots []models.Lot
	err := r.db.Find(&lots).Error
	return lots, err
}

// GetLotsWithoutMetadata returns lots that don't have qt_lot_metadata entries (estimate type)
func (r *ComponentRepository) GetLotsWithoutMetadata() ([]models.Lot, error) {
	var lots []models.Lot
	err := r.db.
		Where("lot_name NOT IN (SELECT lot_name FROM qt_lot_metadata WHERE pricelist_type = 'estimate')").
		Find(&lots).Error
	return lots, err
}