package exporter

import (
	"context"
	"fmt"
	"strconv"
	"time"

	"gorm.io/gorm"

	"github.com/break/junhong_cmp_fiber/pkg/constants"
)

const (
	deviceExportBaseHeaderCount = 6
	deviceExportCardGroupSize   = 5
	deviceExportTailHeaderCount = 5
	deviceExportMinCardGroups   = 1
)

// DeviceDataSource 设备导出数据源。
type DeviceDataSource struct {
	db *gorm.DB
}

// NewDeviceDataSource 创建设备导出数据源。
func NewDeviceDataSource(db *gorm.DB) *DeviceDataSource {
	return &DeviceDataSource{db: db}
}

// Scene 返回导出场景编码。
func (s *DeviceDataSource) Scene() string {
	return constants.ExportTaskSceneDevice
}

// Count 统计设备导出行数。
func (s *DeviceDataSource) Count(ctx context.Context, params ExportParams) (int, error) {
	var total int64
	query := s.applyFilters(s.baseQuery(ctx), params)
	if err := query.Count(&total).Error; err != nil {
		return 0, err
	}
	return int(total), nil
}

// Headers 返回设备导出表头。
func (s *DeviceDataSource) Headers(ctx context.Context, params ExportParams) ([]string, error) {
	cardGroups, err := s.resolveCardGroupCount(ctx, params)
	if err != nil {
		return nil, err
	}
	return buildDeviceExportHeaders(cardGroups), nil
}

// Fetch 按 offset/limit 查询设备导出数据。
func (s *DeviceDataSource) Fetch(ctx context.Context, params ExportParams, offset, limit int) ([][]string, error) {
	if limit <= 0 {
		return [][]string{}, nil
	}

	cardGroups := cardGroupCountFromHeaders(params.ResolvedHeaders)
	if cardGroups <= 0 {
		resolved, err := s.resolveCardGroupCount(ctx, params)
		if err != nil {
			return nil, err
		}
		cardGroups = resolved
	}

	devices, err := s.fetchDevices(ctx, params, offset, limit)
	if err != nil {
		return nil, err
	}
	if len(devices) == 0 {
		return [][]string{}, nil
	}

	deviceIDs := make([]uint, 0, len(devices))
	for _, item := range devices {
		deviceIDs = append(deviceIDs, item.ID)
	}

	cardMap, err := s.fetchBoundCards(ctx, deviceIDs)
	if err != nil {
		return nil, err
	}
	packageMap, err := s.fetchActivePackages(ctx, deviceIDs)
	if err != nil {
		return nil, err
	}

	rows := make([][]string, 0, len(devices))
	for _, item := range devices {
		rows = append(rows, buildDeviceExportRow(item, cardMap[item.ID], packageMap[item.ID], cardGroups))
	}
	return rows, nil
}

func (s *DeviceDataSource) baseQuery(ctx context.Context) *gorm.DB {
	return s.db.WithContext(ctx).
		Table("tb_device AS d").
		Where("d.deleted_at IS NULL")
}

// applyFilters 应用与 /api/admin/devices 一致的筛选条件。
// 导出任务运行在 Worker 中，权限只能使用任务创建时保存的店铺范围快照。
func (s *DeviceDataSource) applyFilters(query *gorm.DB, params ExportParams) *gorm.DB {
	query = applyExportShopScope(query, params, "d.shop_id")

	if virtualNo, ok := filterString(params.Filters, "virtual_no"); ok {
		query = query.Where("d.virtual_no LIKE ?", "%"+virtualNo+"%")
	}
	if imei, ok := filterString(params.Filters, "imei"); ok {
		query = query.Where("d.imei LIKE ?", "%"+imei+"%")
	}
	if keyword, ok := filterString(params.Filters, "keyword"); ok {
		like := "%" + keyword + "%"
		query = query.Where("d.virtual_no LIKE ? OR d.imei LIKE ?", like, like)
	}
	if deviceName, ok := filterString(params.Filters, "device_name"); ok {
		query = query.Where("d.device_name LIKE ?", "%"+deviceName+"%")
	}
	if status, ok := filterInt(params.Filters, "status"); ok && status > 0 {
		query = query.Where("d.status = ?", status)
	}
	if activationStatus, ok := filterInt(params.Filters, "activation_status"); ok {
		query = s.applyActivationStatusFilter(query, activationStatus)
	}
	if shopIDs, ok := filterUintSlice(params.Filters, "shop_ids"); ok {
		if len(shopIDs) == 0 {
			query = query.Where("1 = 0")
		} else {
			query = query.Where("d.shop_id IN ?", shopIDs)
		}
	} else if shopID, ok := filterInt(params.Filters, "shop_id"); ok {
		if shopID == 0 {
			query = query.Where("1 = 0")
		} else if shopID > 0 {
			query = query.Where("d.shop_id = ?", shopID)
		}
	}
	if batchNo, ok := filterString(params.Filters, "batch_no"); ok {
		query = query.Where("d.batch_no = ?", batchNo)
	}
	if deviceType, ok := filterString(params.Filters, "device_type"); ok {
		query = query.Where("d.device_type = ?", deviceType)
	}
	if manufacturer, ok := filterString(params.Filters, "manufacturer"); ok {
		query = query.Where("d.manufacturer LIKE ?", "%"+manufacturer+"%")
	}
	if start, ok := filterTime(params.Filters, "created_at_start"); ok {
		query = query.Where("d.created_at >= ?", start)
	}
	if end, ok := filterTime(params.Filters, "created_at_end"); ok {
		query = query.Where("d.created_at <= ?", end)
	}
	if seriesID, ok := filterUint(params.Filters, "series_id"); ok {
		query = query.Where("d.series_id = ?", seriesID)
	}
	if hasActivePackage, ok := filterOptionalBool(params.Filters, "has_active_package"); ok {
		query = s.applyHasActivePackageFilter(query, hasActivePackage)
	}
	return query
}

// applyHasActivePackageFilter 按设备是否有生效中的主套餐过滤。
func (s *DeviceDataSource) applyHasActivePackageFilter(query *gorm.DB, hasActive bool) *gorm.DB {
	subQuery := `SELECT 1 FROM tb_package_usage AS pu
		WHERE pu.device_id = d.id
		  AND pu.status = ?
		  AND pu.master_usage_id IS NULL
		  AND pu.deleted_at IS NULL`
	if hasActive {
		return query.Where("EXISTS ("+subQuery+")", constants.PackageUsageStatusActive)
	}
	return query.Where("NOT EXISTS ("+subQuery+")", constants.PackageUsageStatusActive)
}

func (s *DeviceDataSource) applyActivationStatusFilter(query *gorm.DB, activationStatus int) *gorm.DB {
	activeCondition := `EXISTS (
		SELECT 1
		FROM tb_package_usage AS pu
		JOIN tb_device_sim_binding AS b
			ON b.device_id = pu.device_id
			AND b.bind_status = ?
			AND b.deleted_at IS NULL
		JOIN tb_iot_card AS c
			ON c.id = b.iot_card_id
			AND c.real_name_status = ?
			AND c.deleted_at IS NULL
		WHERE pu.device_id = d.id
			AND pu.status = ?
			AND pu.master_usage_id IS NULL
			AND pu.deleted_at IS NULL
	)`
	args := []any{
		constants.BindStatusBound,
		constants.RealNameStatusVerified,
		constants.PackageUsageStatusActive,
	}
	if activationStatus == constants.ActivationStatusActive {
		return query.Where(activeCondition, args...)
	}
	if activationStatus == constants.ActivationStatusInactive {
		return query.Where("NOT "+activeCondition, args...)
	}
	return query
}

func (s *DeviceDataSource) resolveCardGroupCount(ctx context.Context, params ExportParams) (int, error) {
	var count int64
	query := s.applyFilters(s.baseQuery(ctx), params).
		Select("COALESCE(MAX(bound_cards.card_count), 0)").
		Joins(`
			LEFT JOIN LATERAL (
				SELECT COUNT(*) AS card_count
				FROM tb_device_sim_binding AS b
				WHERE b.device_id = d.id
					AND b.bind_status = ?
					AND b.deleted_at IS NULL
			) AS bound_cards ON TRUE
		`, constants.BindStatusBound)
	if err := query.Scan(&count).Error; err != nil {
		return 0, err
	}
	if count < deviceExportMinCardGroups {
		return deviceExportMinCardGroups, nil
	}
	return int(count), nil
}

func (s *DeviceDataSource) fetchDevices(ctx context.Context, params ExportParams, offset, limit int) ([]deviceExportRow, error) {
	var devices []deviceExportRow
	query := s.applyFilters(s.baseQuery(ctx), params).
		Joins("LEFT JOIN tb_shop AS sh ON sh.id = d.shop_id").
		Joins("LEFT JOIN tb_package_series AS ps ON ps.id = d.series_id AND ps.deleted_at IS NULL").
		Joins(`
			LEFT JOIN tb_asset_wallet AS aw
				ON aw.resource_type = ?
				AND aw.resource_id = d.id
				AND aw.deleted_at IS NULL
		`, constants.AssetWalletResourceTypeDevice).
		Select(`
			d.id,
			d.virtual_no,
			d.imei,
			d.device_name,
			d.device_model,
			COALESCE(sh.shop_name, '') AS shop_name,
			COALESCE(ps.series_name, '') AS series_name,
			COALESCE(aw.balance, 0) AS wallet_balance
		`).
		Order("d.id ASC").
		Limit(limit).
		Offset(offset)
	if err := query.Scan(&devices).Error; err != nil {
		return nil, err
	}
	return devices, nil
}

func (s *DeviceDataSource) fetchBoundCards(ctx context.Context, deviceIDs []uint) (map[uint][]deviceExportCardRow, error) {
	result := make(map[uint][]deviceExportCardRow, len(deviceIDs))
	if len(deviceIDs) == 0 {
		return result, nil
	}

	var cards []deviceExportCardRow
	if err := s.db.WithContext(ctx).
		Table("tb_device_sim_binding AS b").
		Select(`
			b.device_id,
			b.slot_position,
			c.iccid,
			c.msisdn,
			c.carrier_name,
			c.current_month_usage_mb,
			c.network_status
		`).
		Joins("JOIN tb_iot_card AS c ON c.id = b.iot_card_id AND c.deleted_at IS NULL").
		Where("b.device_id IN ?", deviceIDs).
		Where("b.bind_status = ?", constants.BindStatusBound).
		Where("b.deleted_at IS NULL").
		Order("b.device_id ASC, b.slot_position ASC, b.id ASC").
		Scan(&cards).Error; err != nil {
		return nil, err
	}

	for _, card := range cards {
		result[card.DeviceID] = append(result[card.DeviceID], card)
	}
	return result, nil
}

func (s *DeviceDataSource) fetchActivePackages(ctx context.Context, deviceIDs []uint) (map[uint]deviceExportPackageRow, error) {
	result := make(map[uint]deviceExportPackageRow, len(deviceIDs))
	if len(deviceIDs) == 0 {
		return result, nil
	}

	var packages []deviceExportPackageRow
	if err := s.db.WithContext(ctx).
		Table("tb_package_usage AS pu").
		Select(`
			pu.device_id,
			COALESCE(NULLIF(pu.package_name, ''), p.package_name, '') AS package_name,
			pu.activated_at,
			pu.expires_at
		`).
		Joins("LEFT JOIN tb_package AS p ON p.id = pu.package_id AND p.deleted_at IS NULL").
		Where("pu.device_id IN ?", deviceIDs).
		Where("pu.status = ?", constants.PackageUsageStatusActive).
		Where("pu.master_usage_id IS NULL").
		Where("pu.deleted_at IS NULL").
		Order("pu.device_id ASC, pu.priority ASC, pu.activated_at ASC, pu.id ASC").
		Scan(&packages).Error; err != nil {
		return nil, err
	}

	for _, item := range packages {
		if _, exists := result[item.DeviceID]; exists {
			continue
		}
		result[item.DeviceID] = item
	}
	return result, nil
}

type deviceExportRow struct {
	ID            uint   `gorm:"column:id"`
	VirtualNo     string `gorm:"column:virtual_no"`
	IMEI          string `gorm:"column:imei"`
	DeviceName    string `gorm:"column:device_name"`
	DeviceModel   string `gorm:"column:device_model"`
	ShopName      string `gorm:"column:shop_name"`
	SeriesName    string `gorm:"column:series_name"`
	WalletBalance int64  `gorm:"column:wallet_balance"`
}

type deviceExportCardRow struct {
	DeviceID            uint    `gorm:"column:device_id"`
	SlotPosition        int     `gorm:"column:slot_position"`
	ICCID               string  `gorm:"column:iccid"`
	MSISDN              string  `gorm:"column:msisdn"`
	CarrierName         string  `gorm:"column:carrier_name"`
	CurrentMonthUsageMB float64 `gorm:"column:current_month_usage_mb"`
	NetworkStatus       int     `gorm:"column:network_status"`
}

type deviceExportPackageRow struct {
	DeviceID    uint       `gorm:"column:device_id"`
	PackageName string     `gorm:"column:package_name"`
	ActivatedAt *time.Time `gorm:"column:activated_at"`
	ExpiresAt   *time.Time `gorm:"column:expires_at"`
}

func buildDeviceExportHeaders(cardGroups int) []string {
	if cardGroups < deviceExportMinCardGroups {
		cardGroups = deviceExportMinCardGroups
	}

	headers := []string{"设备号", "IMEI", "设备名称", "店铺名称", "套餐系列", "设备型号"}
	for i := 1; i <= cardGroups; i++ {
		headers = append(headers,
			fmt.Sprintf("ICCID%d", i),
			fmt.Sprintf("接入号%d", i),
			fmt.Sprintf("ICCID%d对应的运营商", i),
			fmt.Sprintf("ICCID%d使用的流量", i),
			fmt.Sprintf("卡状态%d", i),
		)
	}
	headers = append(headers,
		"设备合计",
		"套餐的生效时间",
		"套餐的到期时间",
		"当前套餐",
		"钱包余额",
	)
	return headers
}

func buildDeviceExportRow(item deviceExportRow, cards []deviceExportCardRow, pkg deviceExportPackageRow, cardGroups int) []string {
	if cardGroups < deviceExportMinCardGroups {
		cardGroups = deviceExportMinCardGroups
	}

	row := []string{
		item.VirtualNo,
		item.IMEI,
		item.DeviceName,
		item.ShopName,
		item.SeriesName,
		item.DeviceModel,
	}

	totalUsage := 0.0
	for i := 0; i < cardGroups; i++ {
		if i >= len(cards) {
			row = append(row, "", "", "", "", "")
			continue
		}
		card := cards[i]
		totalUsage += card.CurrentMonthUsageMB
		row = append(row,
			card.ICCID,
			card.MSISDN,
			card.CarrierName,
			formatMB(card.CurrentMonthUsageMB),
			constants.GetNetworkStatusName(card.NetworkStatus),
		)
	}
	// 如果表头列数被历史任务固定得比当前绑定卡少，仍保证合计覆盖当前查到的全部卡。
	for i := cardGroups; i < len(cards); i++ {
		totalUsage += cards[i].CurrentMonthUsageMB
	}

	row = append(row,
		formatMB(totalUsage),
		formatOptionalTime(pkg.ActivatedAt),
		formatOptionalTime(pkg.ExpiresAt),
		pkg.PackageName,
		formatMoneyYuan(item.WalletBalance),
	)
	return row
}

func cardGroupCountFromHeaders(headers []string) int {
	if len(headers) < deviceExportBaseHeaderCount+deviceExportTailHeaderCount {
		return 0
	}
	cardColumnCount := len(headers) - deviceExportBaseHeaderCount - deviceExportTailHeaderCount
	if cardColumnCount <= 0 || cardColumnCount%deviceExportCardGroupSize != 0 {
		return 0
	}
	return cardColumnCount / deviceExportCardGroupSize
}

func formatMB(value float64) string {
	return strconv.FormatFloat(value, 'f', 2, 64)
}

func formatMoneyYuan(cents int64) string {
	sign := ""
	if cents < 0 {
		sign = "-"
		cents = -cents
	}
	return fmt.Sprintf("%s%d.%02d", sign, cents/100, cents%100)
}