hfb_sys/backend/internal/modules/listing/query.go

package listing

import (
	"context"
	"fmt"
	"math"
	"strconv"
	"strings"
	"time"

	"hfb_sys/backend/internal/model"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

const (
	sourceChannelWebsite         = "站内发布"
	sourceChannelExternalUnknown = "未填写"
)

func (r *Repository) ListAdmin(ctx context.Context, query AdminListQuery) (*AdminListResult, error) {
	page := query.Page
	if page <= 0 {
		page = 1
	}
	pageSize := query.PageSize
	if pageSize <= 0 {
		pageSize = query.Limit
	}
	if pageSize <= 0 {
		pageSize = 20
	}
	if pageSize > 100 {
		pageSize = 100
	}

	if hasAdminAssetFilters(query) {
		var rows []listingRow
		err := r.applyAdminListFilters(r.baseQuery(ctx), query).
			Order("l.id DESC").
			Scan(&rows).Error
		if err != nil {
			return nil, err
		}
		items := filterAdminListings(rowsToDTO(rows), query)
		total := int64(len(items))
		start := (page - 1) * pageSize
		if start < 0 {
			start = 0
		}
		if start >= len(items) {
			items = []ListingDTO{}
		} else {
			end := start + pageSize
			if end > len(items) {
				end = len(items)
			}
			items = items[start:end]
		}
		return &AdminListResult{
			Items:    items,
			Total:    total,
			Page:     page,
			PageSize: pageSize,
		}, nil
	}

	db := r.applyAdminListFilters(r.baseQuery(ctx), query)
	offset := (page - 1) * pageSize
	if offset < 0 {
		offset = 0
	}
	var rows []listingRow
	err := db.Order("l.id DESC").Limit(pageSize).Offset(offset).Scan(&rows).Error
	if err != nil {
		return nil, err
	}

	var total int64
	// 首页查询且返回条数小于 pageSize 时，总数等于当前返回条数，彻底免除第二次 COUNT(*) 扫描
	if page == 1 && len(rows) < pageSize {
		total = int64(len(rows))
	} else {
		countDB := r.applyAdminListFilters(r.db.WithContext(ctx).Table("rental_listings AS l"), query)
		if err := countDB.Count(&total).Error; err != nil {
			return nil, err
		}
	}
	return &AdminListResult{
		Items:    rowsToDTO(rows),
		Total:    total,
		Page:     page,
		PageSize: pageSize,
	}, nil
}

func (r *Repository) applyAdminListFilters(db *gorm.DB, query AdminListQuery) *gorm.DB {
	if query.OwnerID > 0 {
		db = db.Where("l.owner_id = ?", query.OwnerID)
	}
	if keyword := strings.TrimSpace(query.Keyword); keyword != "" {
		upper := strings.ToUpper(keyword)
		if strings.HasPrefix(upper, "RO") || strings.HasPrefix(upper, "ORD") {
			// 订单号：命中 rental_orders(order_no) 唯一索引
			db = db.Where("EXISTS (SELECT 1 FROM rental_orders AS o WHERE o.listing_id = l.id AND o.order_no = ?)", keyword)
		} else if strings.HasPrefix(upper, "L") || (len(keyword) >= 12 && isAllDigits(keyword)) {
			// 商品编号（如 L前缀或时间计算生成的 12位纯数字如 202609040184）：命中 rental_listings(listing_no) 唯一索引
			db = db.Where("l.listing_no = ?", keyword)
		} else if id, err := strconv.ParseUint(keyword, 10, 64); err == nil && id > 0 && keyword[0] != '0' && id < 100000000 {
			db = db.Where(
				`(l.listing_no = ? OR EXISTS (SELECT 1 FROM rental_orders AS o WHERE o.listing_id = l.id AND o.order_no = ?) OR l.id = ? OR l.account_id = ? OR l.owner_id = ?)`,
				keyword,
				keyword,
				id,
				id,
				id,
			)
		} else {
			like := "%" + keyword + "%"
			db = db.Where(
				`(l.listing_no LIKE ? OR EXISTS (SELECT 1 FROM rental_orders AS o WHERE o.listing_id = l.id AND o.order_no LIKE ?))`,
				like,
				like,
			)
		}
	}
	if query.Status != "" {
		db = db.Where("l.status = ?", query.Status)
	}
	if query.ReviewStatus != "" {
		db = db.Where("l.review_status = ?", query.ReviewStatus)
	}
	if query.MinCoin != nil {
		db = db.Where(
			"EXISTS (SELECT 1 FROM game_accounts AS coin_account WHERE coin_account.id = l.account_id AND coin_account.haf_coin_amount >= ?)",
			coinMToAdminAmount(*query.MinCoin),
		)
	}
	if query.MaxCoin != nil {
		db = db.Where(
			"EXISTS (SELECT 1 FROM game_accounts AS coin_account WHERE coin_account.id = l.account_id AND coin_account.haf_coin_amount <= ?)",
			coinMToAdminAmount(*query.MaxCoin),
		)
	}
	if query.MinDeposit != nil {
		db = db.Where("l.deposit_amount_cent >= ?", yuanToAdminCent(*query.MinDeposit))
	}
	if query.MaxDeposit != nil {
		db = db.Where("l.deposit_amount_cent <= ?", yuanToAdminCent(*query.MaxDeposit))
	}
	return db
}

func coinMToAdminAmount(value float64) int64 {
	if value <= 0 || math.IsNaN(value) || math.IsInf(value, 0) {
		return 0
	}
	return int64(math.Round(value * 1000000))
}

func yuanToAdminCent(value float64) int64 {
	if value <= 0 || math.IsNaN(value) || math.IsInf(value, 0) {
		return 0
	}
	return int64(math.Round(value * 100))
}

func (r *Repository) FindAdmin(ctx context.Context, listingID uint64) (*ListingDTO, error) {
	return r.findDTO(ctx, "l.id = ?", listingID)
}

func (r *Repository) ListMine(ctx context.Context, ownerID uint64, siteCode ...string) ([]ListingDTO, error) {
	var rows []listingRow
	base := r.baseQuery(ctx).Where("l.owner_id = ?", ownerID)
	if len(siteCode) > 0 {
		code := strings.TrimSpace(siteCode[0])
		if code != "" {
			base = base.Joins("INNER JOIN distribution_sites AS my_ds ON my_ds.id = l.distribution_site_id AND my_ds.site_code = ?", code)
		} else {
			base = base.Where("l.distribution_site_id IS NULL OR l.distribution_site_id = 0")
		}
	}
	err := base.Order("l.id DESC").Scan(&rows).Error
	if err != nil {
		return nil, err
	}
	return sellerListings(rowsToDTO(rows)), nil
}

func (r *Repository) FindMine(ctx context.Context, ownerID uint64, id uint64) (*ListingDTO, error) {
	dto, err := r.findDTO(ctx, "l.id = ? AND l.owner_id = ?", id, ownerID)
	if err != nil {
		return nil, err
	}
	applySellerListingPrice(dto)
	return dto, nil
}

func (r *Repository) findOwnedForUpdate(tx *gorm.DB, ownerID uint64, listingID uint64) (*model.RentalListing, *model.GameAccount, error) {
	var listing model.RentalListing
	if err := tx.Where("id = ? AND owner_id = ?", listingID, ownerID).First(&listing).Error; err != nil {
		return nil, nil, err
	}
	var account model.GameAccount
	if err := tx.Where("id = ? AND owner_id = ?", listing.AccountID, ownerID).First(&account).Error; err != nil {
		return nil, nil, err
	}
	return &listing, &account, nil
}

func (r *Repository) findDTO(ctx context.Context, where string, args ...any) (*ListingDTO, error) {
	var row listingRow
	err := r.baseQuery(ctx).
		Where(where, args...).
		First(&row).Error
	if err != nil {
		return nil, err
	}
	dto := row.toDTO()
	return &dto, nil
}

func (r *Repository) baseQuery(ctx context.Context) *gorm.DB {
	return r.db.WithContext(ctx).Table("rental_listings AS l").
		Select(`l.*, a.title, a.description, a.game_name, a.server_region, a.login_platform, a.rank_level,
			a.haf_coin_amount, a.asset_summary, a.screenshot_urls, COALESCE(u.phone, '') AS owner_phone, COALESCE(u.nickname, '') AS owner_nickname,
			CASE WHEN lu.id IS NULL THEN 0 ELSE 1 END AS is_external_upload,
			COALESCE(src_site.site_code, '') AS distribution_site_code,
			COALESCE(src_site.site_name, '') AS distribution_site_name,
			CASE
				WHEN l.distribution_site_id IS NOT NULL AND src_site.id IS NOT NULL THEN CONCAT('分销站 - ', src_site.site_name, ' (', src_site.site_code, ')')
				WHEN lu.id IS NULL THEN ?
				WHEN COALESCE(lu.source_channel, '') = '' THEN ?
				ELSE lu.source_channel
			END AS display_source_channel`, sourceChannelWebsite, sourceChannelExternalUnknown).
		Joins("JOIN game_accounts AS a ON a.id = l.account_id").
		Joins("LEFT JOIN users AS u ON u.id = l.owner_id").
		Joins("LEFT JOIN distribution_sites AS src_site ON src_site.id = l.distribution_site_id").
		// 逐商品按 listing_id 索引定位最后一次导入，避免每次列表查询都对全量导入记录分组。
		Joins(`LEFT JOIN listing_uploads AS lu ON lu.id = (
			SELECT MAX(lu_latest.id) FROM listing_uploads AS lu_latest WHERE lu_latest.listing_id = l.id
		)`)
}

func (r *Repository) findForReviewUpdate(tx *gorm.DB, listingID uint64) (*model.RentalListing, *model.GameAccount, error) {
	var listing model.RentalListing
	if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).First(&listing, listingID).Error; err != nil {
		return nil, nil, err
	}
	var account model.GameAccount
	if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).First(&account, listing.AccountID).Error; err != nil {
		return nil, nil, err
	}
	return &listing, &account, nil
}

func (r *Repository) nextListingNo(tx *gorm.DB, now time.Time) (string, error) {
	bizDate := now.Format("20060102")
	if tx.Dialector.Name() == "mysql" {
		if err := tx.Exec(`
			INSERT INTO listing_no_sequences (biz_date, next_seq)
			VALUES (?, LAST_INSERT_ID(1))
			ON DUPLICATE KEY UPDATE next_seq = LAST_INSERT_ID(next_seq + 1)
		`, bizDate).Error; err != nil {
			return "", err
		}
		var seq int
		if err := tx.Raw("SELECT LAST_INSERT_ID()").Scan(&seq).Error; err != nil {
			return "", err
		}
		return fmt.Sprintf("%s%04d", bizDate, seq), nil
	}

	var maxNo string
	if err := tx.Table("rental_listings").
		Select("COALESCE(MAX(listing_no), '')").
		Where("listing_no LIKE ?", bizDate+"%").
		Scan(&maxNo).Error; err != nil {
		return "", err
	}
	seq := 1
	if len(maxNo) > len(bizDate) {
		if parsed, err := strconv.Atoi(maxNo[len(bizDate):]); err == nil {
			seq = parsed + 1
		}
	}
	return fmt.Sprintf("%s%04d", bizDate, seq), nil
}

func isAllDigits(s string) bool {
	if s == "" {
		return false
	}
	for i := 0; i < len(s); i++ {
		if s[i] < '0' || s[i] > '9' {
			return false
		}
	}
	return true
}