package repositories_admin

import (
	"astrology-api/constants"
	astrologermodel "astrology-api/models/astrologermodel"
	usermodel "astrology-api/models/usermodel"
	"errors"
	"fmt"
	"math"
	"strings"
	"time"

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

// The admin finance manager's data layer.
//
// It reads `consultations` joined to the customer, the astrologer and the
// customer's review — one query per screen rather than a row-by-row lookup,
// because the pending list is the busiest screen in the panel.
//
// It also owns the settlement write: the one place in this codebase where
// money moves *into* an astrologer wallet.

// ConsultationRow is a list/detail row flattened across the join. It is not a
// model — nothing writes through it.
type ConsultationRow struct {
	ID             uint   `gorm:"column:id"`
	ConsultationNo string `gorm:"column:consultationNo"`

	UserID     uint   `gorm:"column:userId"`
	UserName   string `gorm:"column:userName"`
	UserMobile string `gorm:"column:userMobile"`
	UserEmail  string `gorm:"column:userEmail"`

	AstrologerID     uint   `gorm:"column:astrologerId"`
	AstrologerName   string `gorm:"column:astrologerName"`
	AstrologerMobile string `gorm:"column:astrologerMobile"`

	Medium    string `gorm:"column:medium"`
	Status    string `gorm:"column:status"`
	EndReason string `gorm:"column:endReason"`

	ChannelName string `gorm:"column:channelName"`

	DurationSeconds    int `gorm:"column:durationSeconds"`
	BilledSeconds      int `gorm:"column:billedSeconds"`
	BilledMinutes      int `gorm:"column:billedMinutes"`
	MaxBillableSeconds int `gorm:"column:maxBillableSeconds"`

	RatePerMinute      float64 `gorm:"column:ratePerMinute"`
	GrossAmount        float64 `gorm:"column:grossAmount"`
	PlatformFeePercent float64 `gorm:"column:platformFeePercent"`
	PlatformFeeAmount  float64 `gorm:"column:platformFeeAmount"`
	AstrologerEarning  float64 `gorm:"column:astrologerEarning"`

	SettlementStatus string     `gorm:"column:settlementStatus"`
	SettlementID     *uint      `gorm:"column:settlementId"`
	SettlementNo     string     `gorm:"column:settlementNo"`
	SettledAt        *time.Time `gorm:"column:settledAt"`

	AdminRemarks string `gorm:"column:adminRemarks"`

	// The customer's review of this astrologer. Not per consultation —
	// user_reviews has no consultation column — so this is the customer's
	// latest review of that astrologer, which is what an admin judging the
	// session would look at anyway.
	ReviewRating float64 `gorm:"column:reviewRating"`
	ReviewText   string  `gorm:"column:reviewText"`

	ConsulteeName string     `gorm:"column:name"`
	BirthDate     *time.Time `gorm:"column:birthDate"`
	BirthTime     string     `gorm:"column:birthTime"`
	BirthPlace    string     `gorm:"column:birthPlace"`
	Gender        string     `gorm:"column:gender"`

	StartedAt *time.Time `gorm:"column:startedAt"`
	EndedAt   *time.Time `gorm:"column:endedAt"`
	CreatedAt *time.Time `gorm:"column:created_at"`
}

// ConsultationTotals is the header of a filtered list: the totals over every
// matching row, not just the page being shown.
type ConsultationTotals struct {
	Gross      float64 `gorm:"column:gross"`
	PlatformFee float64 `gorm:"column:platformFee"`
	Earning    float64 `gorm:"column:earning"`
}

// ListFilter mirrors dto_admin.SettlementListFilter, kept separate so the
// repository does not depend on the transport DTOs.
type ListFilter struct {
	Page  int
	Limit int
	Search string

	SettlementStatus string
	Medium           string

	AstrologerID uint
	UserID       uint

	FromDate string
	ToDate   string

	SortBy  string
	SortDir string
}

// StatusAggregate is one settlementStatus bucket of the finance summary.
type StatusAggregate struct {
	SettlementStatus string  `gorm:"column:settlementStatus"`
	Count            int64   `gorm:"column:rowCount"`
	Amount           float64 `gorm:"column:amount"`
}

// SettlementBatchRow is a wallet_settlements row joined to its astrologer.
type SettlementBatchRow struct {
	ID           uint   `gorm:"column:id"`
	SettlementNo string `gorm:"column:settlement_no"`

	AstrologerID   uint   `gorm:"column:astrologer_id"`
	AstrologerName string `gorm:"column:astrologerName"`

	ConsultationCount int     `gorm:"column:consultation_count"`
	GrossAmount       float64 `gorm:"column:gross_amount"`
	PlatformFee       float64 `gorm:"column:platform_fee"`
	Amount            float64 `gorm:"column:amount"`

	SettlementStatus string `gorm:"column:settlement_status"`

	PeriodFrom *time.Time `gorm:"column:period_from"`
	PeriodTo   *time.Time `gorm:"column:period_to"`

	ProcessedBy     uint   `gorm:"column:processed_by"`
	ProcessedByName string `gorm:"column:processedByName"`

	Remarks string `gorm:"column:remarks"`

	SettlementDate *time.Time `gorm:"column:settlement_date"`
	CreatedAt      *time.Time `gorm:"column:created_at"`
}

// BatchOutcome is what settling one astrologer produced.
type BatchOutcome struct {
	SettlementID   uint
	SettlementNo   string
	WalletBalance  float64
	Count          int
	Gross          float64
	PlatformFee    float64
	Amount         float64
	PeriodFrom     *time.Time
	PeriodTo       *time.Time
}

type SettlementRepository interface {

	//------------------------------------------------
	// Consultation lists
	//------------------------------------------------

	ListConsultations(filter ListFilter) ([]ConsultationRow, int64, ConsultationTotals, error)

	GetConsultationRow(id uint) (*ConsultationRow, error)

	GetConsultationsBySettlementID(settlementID uint) ([]ConsultationRow, error)

	GetSettlementLogs(consultationID uint) ([]usermodel.ConsultationSettlementLog, error)

	// GetWalletDebit reads what actually left the customer's wallet for a
	// session, matched on the consultation number the billing path wrote into
	// wallettransaction.orderId.
	GetWalletDebit(consultationNo string) (float64, error)

	//------------------------------------------------
	// Review actions
	//------------------------------------------------

	// GetConsultationsByIDs loads the rows an admin action names, so the
	// service can decide per row whether the transition is allowed.
	GetConsultationsByIDs(ids []uint) ([]usermodel.Consultation, error)

	// ApplyReview moves the named rows to toStatus and writes one log entry
	// each, in a single transaction.
	ApplyReview(
		ids []uint,
		toStatus string,
		fromStatuses map[uint]string,
		remarks string,
		adminID uint,
		actionSource string,
	) error

	//------------------------------------------------
	// Settlement run
	//------------------------------------------------

	// GetSettlableAstrologerIDs lists the astrologers with money waiting,
	// so the run can be batched one astrologer at a time.
	GetSettlableAstrologerIDs(
		statuses []string,
		astrologerID uint,
		upTo *time.Time,
	) ([]uint, error)

	GetSettlableConsultations(
		astrologerID uint,
		statuses []string,
		upTo *time.Time,
	) ([]usermodel.Consultation, error)

	// SettleAstrologer is the money-moving write, in one transaction:
	// re-claim the consultations under a row lock, create the settlement
	// batch, credit the astrologer wallet, write the ledger and wallet
	// transaction rows, and stamp the consultations SETTLED.
	//
	// Returns nil, nil when there was nothing left to settle — another run
	// got there first, which is not an error.
	SettleAstrologer(
		astrologerID uint,
		statuses []string,
		upTo *time.Time,
		processedBy uint,
		actionSource string,
		remarks string,
	) (*BatchOutcome, error)

	//------------------------------------------------
	// Settlement batches
	//------------------------------------------------

	ListSettlementBatches(
		astrologerID uint,
		search string,
		fromDate string,
		toDate string,
		page int,
		limit int,
	) ([]SettlementBatchRow, int64, float64, error)

	GetSettlementBatch(id uint) (*SettlementBatchRow, error)

	//------------------------------------------------
	// Summary
	//------------------------------------------------

	GetStatusAggregates() ([]StatusAggregate, error)

	// GetBilledTotals sums gross and platform fee over billed consultations,
	// optionally from a date onwards.
	GetBilledTotals(from *time.Time) (float64, float64, error)

	GetSettledAmount(from *time.Time) (float64, error)

	GetAstrologerWalletLiability() (float64, error)

	GetLastSettlementAt() (*time.Time, error)

	//------------------------------------------------
	// Astrologer
	//------------------------------------------------

	GetAstrologer(astrologerID uint) (*astrologermodel.Astrologer, error)

	//------------------------------------------------
	// Configuration
	//------------------------------------------------

	GetSystemFlags(names []string) (map[string]string, error)

	// SetSystemFlag writes a flag, inserting the row when the configuration
	// migration has not created it yet.
	SetSystemFlag(name string, value string, valueType string, displayName string) error
}

type settlementRepository struct {
	db *gorm.DB
}

func NewSettlementRepository(db *gorm.DB) SettlementRepository {
	return &settlementRepository{db: db}
}

//////////////////////////////////////////////////////////////
// Consultation lists
//////////////////////////////////////////////////////////////

// baseQuery is the join every consultation screen reads through.
//
// The review is a correlated subquery rather than a join, because a customer
// can have several reviews of one astrologer and a join would multiply the
// consultation rows.
func (r *settlementRepository) baseQuery() *gorm.DB {

	return r.db.
		Table("consultations AS c").
		Joins("LEFT JOIN users AS u ON u.id = c.userId").
		Joins("LEFT JOIN astrologers AS a ON a.id = c.astrologerId").
		Joins("LEFT JOIN wallet_settlements AS ws ON ws.id = c.settlementId").
		Where("c.isDelete = ?", false)
}

const consultationSelect = `
	c.id, c.consultationNo, c.userId, c.astrologerId, c.medium, c.status,
	c.endReason, c.channelName, c.durationSeconds, c.billedSeconds,
	c.billedMinutes, c.maxBillableSeconds, c.ratePerMinute, c.grossAmount,
	c.platformFeePercent, c.platformFeeAmount, c.astrologerEarning,
	c.settlementStatus, c.settlementId, c.settledAt, c.adminRemarks,
	c.name, c.birthDate, c.birthTime, c.birthPlace, c.gender,
	c.startedAt, c.endedAt, c.created_at,
	u.name AS userName, u.contactNo AS userMobile, u.email AS userEmail,
	COALESCE(NULLIF(a.displayName, ''), a.name) AS astrologerName,
	a.contactNo AS astrologerMobile,
	ws.settlement_no AS settlementNo,
	(SELECT r.rating FROM user_reviews r
	  WHERE r.userId = c.userId AND r.astrologerId = c.astrologerId
	    AND r.isDelete = 0
	  ORDER BY r.id DESC LIMIT 1) AS reviewRating,
	(SELECT r.review FROM user_reviews r
	  WHERE r.userId = c.userId AND r.astrologerId = c.astrologerId
	    AND r.isDelete = 0
	  ORDER BY r.id DESC LIMIT 1) AS reviewText
`

func (r *settlementRepository) applyFilter(
	query *gorm.DB,
	filter ListFilter,
) *gorm.DB {

	//------------------------------------------------
	// Settlement Status
	//------------------------------------------------

	status := strings.ToUpper(strings.TrimSpace(filter.SettlementStatus))

	switch status {

	case "", "ALL":

		// Rows that were never billed have nothing to review, so they stay
		// out of the finance screens unless asked for by name.
		query = query.Where("c.settlementStatus <> ?", constants.SettlementStatusNA)

	default:
		query = query.Where("c.settlementStatus = ?", status)
	}

	//------------------------------------------------
	// Medium
	//------------------------------------------------

	if medium := strings.ToUpper(strings.TrimSpace(filter.Medium)); medium != "" && medium != "ALL" {
		query = query.Where("c.medium = ?", medium)
	}

	//------------------------------------------------
	// Parties
	//------------------------------------------------

	if filter.AstrologerID > 0 {
		query = query.Where("c.astrologerId = ?", filter.AstrologerID)
	}

	if filter.UserID > 0 {
		query = query.Where("c.userId = ?", filter.UserID)
	}

	//------------------------------------------------
	// Date Range
	//------------------------------------------------

	if from := strings.TrimSpace(filter.FromDate); from != "" {
		query = query.Where("DATE(c.created_at) >= ?", from)
	}

	if to := strings.TrimSpace(filter.ToDate); to != "" {
		query = query.Where("DATE(c.created_at) <= ?", to)
	}

	//------------------------------------------------
	// Search
	//------------------------------------------------
	//
	// Consultation number, customer or astrologer — whichever the admin has
	// in front of them from a complaint.

	if search := strings.TrimSpace(filter.Search); search != "" {

		like := "%" + search + "%"

		query = query.Where(
			r.db.Where("c.consultationNo LIKE ?", like).
				Or("u.name LIKE ?", like).
				Or("u.contactNo LIKE ?", like).
				Or("a.name LIKE ?", like).
				Or("a.displayName LIKE ?", like),
		)
	}

	return query
}

func (r *settlementRepository) ListConsultations(
	filter ListFilter,
) ([]ConsultationRow, int64, ConsultationTotals, error) {

	var rows []ConsultationRow
	var total int64
	var totals ConsultationTotals

	//------------------------------------------------
	// Count
	//------------------------------------------------

	countQuery := r.applyFilter(r.baseQuery(), filter)

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

	//------------------------------------------------
	// Totals
	//------------------------------------------------

	totalsQuery := r.applyFilter(r.baseQuery(), filter)

	err := totalsQuery.
		Select(`COALESCE(SUM(c.grossAmount),0) AS gross,
		        COALESCE(SUM(c.platformFeeAmount),0) AS platformFee,
		        COALESCE(SUM(c.astrologerEarning),0) AS earning`).
		Scan(&totals).Error

	if err != nil {
		return nil, 0, totals, err
	}

	//------------------------------------------------
	// Page
	//------------------------------------------------

	page := filter.Page
	limit := filter.Limit

	if page <= 0 {
		page = 1
	}

	if limit <= 0 {
		limit = 10
	}

	query := r.applyFilter(r.baseQuery(), filter)

	err = query.
		Select(consultationSelect).
		Order(orderClause(filter.SortBy, filter.SortDir)).
		Offset((page - 1) * limit).
		Limit(limit).
		Scan(&rows).Error

	if err != nil {
		return nil, 0, totals, err
	}

	return rows, total, totals, nil
}

// orderClause whitelists the sortable columns. The value comes off a query
// string, so it is never interpolated without being matched first.
func orderClause(sortBy string, sortDir string) string {

	column := "c.created_at"

	switch strings.ToLower(strings.TrimSpace(sortBy)) {

	case "earning", "astrologerearning":
		column = "c.astrologerEarning"

	case "gross", "grossamount":
		column = "c.grossAmount"

	case "duration":
		column = "c.durationSeconds"
	}

	direction := "DESC"

	if strings.EqualFold(strings.TrimSpace(sortDir), "asc") {
		direction = "ASC"
	}

	return column + " " + direction + ", c.id " + direction
}

func (r *settlementRepository) GetConsultationRow(
	id uint,
) (*ConsultationRow, error) {

	var row ConsultationRow

	err := r.baseQuery().
		Select(consultationSelect).
		Where("c.id = ?", id).
		Limit(1).
		Scan(&row).Error

	if err != nil {
		return nil, err
	}

	if row.ID == 0 {
		return nil, nil
	}

	return &row, nil
}

func (r *settlementRepository) GetConsultationsBySettlementID(
	settlementID uint,
) ([]ConsultationRow, error) {

	var rows []ConsultationRow

	err := r.baseQuery().
		Select(consultationSelect).
		Where("c.settlementId = ?", settlementID).
		Order("c.id ASC").
		Scan(&rows).Error

	if err != nil {
		return nil, err
	}

	return rows, nil
}

func (r *settlementRepository) GetSettlementLogs(
	consultationID uint,
) ([]usermodel.ConsultationSettlementLog, error) {

	var logs []usermodel.ConsultationSettlementLog

	err := r.db.
		Where("consultationId = ?", consultationID).
		Order("id ASC").
		Find(&logs).Error

	if err != nil {
		return nil, err
	}

	return logs, nil
}

func (r *settlementRepository) GetWalletDebit(
	consultationNo string,
) (float64, error) {

	if strings.TrimSpace(consultationNo) == "" {
		return 0, nil
	}

	var amount float64

	err := r.db.
		Model(&usermodel.WalletTransaction{}).
		Select("COALESCE(SUM(amount),0)").
		Where("orderId = ?", consultationNo).
		Where("isCredit = ?", false).
		Scan(&amount).Error

	if err != nil {
		return 0, err
	}

	return amount, nil
}

//////////////////////////////////////////////////////////////
// Review actions
//////////////////////////////////////////////////////////////

func (r *settlementRepository) GetConsultationsByIDs(
	ids []uint,
) ([]usermodel.Consultation, error) {

	if len(ids) == 0 {
		return nil, nil
	}

	var records []usermodel.Consultation

	err := r.db.
		Where("id IN ?", ids).
		Where("isDelete = ?", false).
		Find(&records).Error

	if err != nil {
		return nil, err
	}

	return records, nil
}

func (r *settlementRepository) ApplyReview(
	ids []uint,
	toStatus string,
	fromStatuses map[uint]string,
	remarks string,
	adminID uint,
	actionSource string,
) error {

	if len(ids) == 0 {
		return nil
	}

	now := time.Now()

	return r.db.Transaction(func(tx *gorm.DB) error {

		//------------------------------------------------
		// Move The Rows
		//------------------------------------------------
		//
		// The WHERE still names the statuses the service validated, so a row
		// that changed underneath between the read and this write is left
		// alone rather than jumped from an unexpected state.

		allowed := make([]string, 0, len(fromStatuses))
		seen := map[string]bool{}

		for _, status := range fromStatuses {

			if !seen[status] {
				seen[status] = true
				allowed = append(allowed, status)
			}
		}

		updates := map[string]interface{}{
			"settlementStatus": toStatus,
			"adminActionBy":    adminID,
			"adminActionAt":    now,
			"updated_at":       now,
		}

		if strings.TrimSpace(remarks) != "" {
			updates["adminRemarks"] = remarks
		}

		err := tx.
			Model(&usermodel.Consultation{}).
			Where("id IN ?", ids).
			Where("settlementStatus IN ?", allowed).
			Updates(updates).Error

		if err != nil {
			return err
		}

		//------------------------------------------------
		// Audit Trail
		//------------------------------------------------

		logs := make([]usermodel.ConsultationSettlementLog, 0, len(ids))

		for _, id := range ids {

			logs = append(logs, usermodel.ConsultationSettlementLog{
				ConsultationID: id,
				FromStatus:     fromStatuses[id],
				ToStatus:       toStatus,
				Remarks:        remarks,
				ActionBy:       adminID,
				ActionSource:   actionSource,
				CreatedAt:      &now,
			})
		}

		return tx.Create(&logs).Error
	})
}

//////////////////////////////////////////////////////////////
// Settlement run
//////////////////////////////////////////////////////////////

func (r *settlementRepository) GetSettlableAstrologerIDs(
	statuses []string,
	astrologerID uint,
	upTo *time.Time,
) ([]uint, error) {

	var ids []uint

	query := r.db.
		Model(&usermodel.Consultation{}).
		Where("settlementStatus IN ?", statuses).
		Where("astrologerEarning > ?", 0).
		Where("isDelete = ?", false)

	if astrologerID > 0 {
		query = query.Where("astrologerId = ?", astrologerID)
	}

	if upTo != nil {
		query = query.Where("COALESCE(endedAt, created_at) <= ?", *upTo)
	}

	// GROUP BY rather than DISTINCT: Pluck builds its own SELECT, and the
	// grouped form leaves no doubt about which column is being de-duplicated.
	err := query.
		Group("astrologerId").
		Pluck("astrologerId", &ids).Error

	if err != nil {
		return nil, err
	}

	return ids, nil
}

func (r *settlementRepository) GetSettlableConsultations(
	astrologerID uint,
	statuses []string,
	upTo *time.Time,
) ([]usermodel.Consultation, error) {

	var records []usermodel.Consultation

	query := r.db.
		Where("astrologerId = ?", astrologerID).
		Where("settlementStatus IN ?", statuses).
		Where("astrologerEarning > ?", 0).
		Where("isDelete = ?", false)

	if upTo != nil {
		query = query.Where("COALESCE(endedAt, created_at) <= ?", *upTo)
	}

	err := query.
		Order("id ASC").
		Find(&records).Error

	if err != nil {
		return nil, err
	}

	return records, nil
}

func (r *settlementRepository) SettleAstrologer(
	astrologerID uint,
	statuses []string,
	upTo *time.Time,
	processedBy uint,
	actionSource string,
	remarks string,
) (*BatchOutcome, error) {

	var outcome *BatchOutcome

	err := r.db.Transaction(func(tx *gorm.DB) error {

		now := time.Now()

		//------------------------------------------------
		// Claim The Consultations
		//------------------------------------------------
		//
		// Re-read under SELECT ... FOR UPDATE inside the transaction. Two
		// concurrent runs — the cron firing while an admin presses Run —
		// would otherwise both see the same rows and credit the wallet
		// twice.

		query := tx.
			Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("astrologerId = ?", astrologerID).
			Where("settlementStatus IN ?", statuses).
			Where("astrologerEarning > ?", 0).
			Where("isDelete = ?", false)

		if upTo != nil {
			query = query.Where("COALESCE(endedAt, created_at) <= ?", *upTo)
		}

		var records []usermodel.Consultation

		if err := query.Order("id ASC").Find(&records).Error; err != nil {
			return err
		}

		if len(records) == 0 {

			// Another run got here first. Not an error.
			return nil
		}

		//------------------------------------------------
		// Totals
		//------------------------------------------------

		ids := make([]uint, 0, len(records))

		var gross, fee, amount float64
		var periodFrom, periodTo *time.Time

		for index := range records {

			record := records[index]

			ids = append(ids, record.ID)

			gross += record.GrossAmount
			fee += record.PlatformFeeAmount
			amount += record.AstrologerEarning

			moment := record.EndedAt

			if moment == nil {
				moment = record.CreatedAt
			}

			if moment != nil {

				if periodFrom == nil || moment.Before(*periodFrom) {
					periodFrom = moment
				}

				if periodTo == nil || moment.After(*periodTo) {
					periodTo = moment
				}
			}
		}

		gross = round2(gross)
		fee = round2(fee)
		amount = round2(amount)

		//------------------------------------------------
		// Astrologer And Wallet
		//------------------------------------------------
		//
		// The wallet is keyed by the astrologer's *user* id — astrologers.userId
		// — which is what the astrologer app's own wallet endpoints read.

		var astrologer astrologermodel.Astrologer

		err := tx.
			Where("id = ?", astrologerID).
			First(&astrologer).Error

		if err != nil {
			return err
		}

		walletUserID := astrologer.UserID

		if walletUserID == 0 {
			return fmt.Errorf("astrologer %d has no linked user account", astrologerID)
		}

		var wallet usermodel.UserWallet

		err = tx.
			Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("userId = ?", walletUserID).
			Where("isDelete = ?", false).
			First(&wallet).Error

		if err != nil {

			if !errors.Is(err, gorm.ErrRecordNotFound) {
				return err
			}

			// First settlement for this astrologer: the wallet row is
			// created here rather than failing the batch.
			zero := float64(0)

			wallet = usermodel.UserWallet{
				UserID:    walletUserID,
				Amount:    &zero,
				IsActive:  true,
				CreatedAt: &now,
				UpdatedAt: &now,
			}

			if err := tx.Create(&wallet).Error; err != nil {
				return err
			}
		}

		balance := float64(0)

		if wallet.Amount != nil {
			balance = *wallet.Amount
		}

		//------------------------------------------------
		// The Settlement Batch
		//------------------------------------------------

		settlement := astrologermodel.WalletSettlement{
			UserID:            walletUserID,
			AstrologerID:      astrologerID,
			SettlementNo:      settlementNumber(now, astrologerID),
			SettlementType:    constants.SettlementTypeConsultationBatch,
			Amount:            amount,
			SettlementStatus:  constants.BatchStatusCompleted,
			PaymentMode:       "WALLET",
			SettlementDate:    &now,
			Remarks:           remarks,
			ConsultationCount: len(records),
			PeriodFrom:        periodFrom,
			PeriodTo:          periodTo,
			GrossAmount:       gross,
			PlatformFee:       fee,
			ProcessedBy:       processedBy,
			CreatedAt:         &now,
			UpdatedAt:         &now,
		}

		if err := tx.Create(&settlement).Error; err != nil {
			return err
		}

		//------------------------------------------------
		// Credit The Astrologer Wallet
		//------------------------------------------------
		//
		// This is the moment the earning becomes the astrologer's money and
		// therefore withdrawable. Nothing earlier in the flow touches this
		// balance.

		newBalance := round2(balance + amount)

		err = tx.
			Model(&usermodel.UserWallet{}).
			Where("id = ?", wallet.ID).
			Updates(map[string]interface{}{
				"amount":     newBalance,
				"updated_at": now,
			}).Error

		if err != nil {
			return err
		}

		//------------------------------------------------
		// Ledger And Transaction
		//------------------------------------------------
		//
		// Both, because the astrologer app reads its transaction list from
		// wallet_ledger and the shared wallettransaction table is what the
		// admin panel's existing reports read.

		ledger := astrologermodel.WalletLedger{
			UserID:          walletUserID,
			AstrologerID:    astrologerID,
			TransactionType: constants.TxnTypeSettlement,
			ReferenceID:     settlement.ID,
			Credit:          amount,
			BalanceAfter:    newBalance,
			Remarks: fmt.Sprintf(
				"Settlement %s for %d consultation(s)",
				settlement.SettlementNo,
				len(records),
			),
			CreatedAt: &now,
			UpdatedAt: &now,
		}

		if err := tx.Create(&ledger).Error; err != nil {
			return err
		}

		transaction := astrologermodel.WalletTransaction{
			UserID:          walletUserID,
			AstrologerID:    astrologerID,
			Amount:          amount,
			TransactionType: constants.TxnTypeSettlement,
			OrderID:         settlement.ID,
			IsCredit:        true,
			CreatedAt:       now,
			UpdatedAt:       now,
			CreatedBy:       processedBy,
			ModifiedBy:      processedBy,
		}

		if err := tx.Create(&transaction).Error; err != nil {
			return err
		}

		//------------------------------------------------
		// Close The Consultations
		//------------------------------------------------

		err = tx.
			Model(&usermodel.Consultation{}).
			Where("id IN ?", ids).
			Updates(map[string]interface{}{
				"settlementStatus": constants.SettlementStatusSettled,
				"settlementId":     settlement.ID,
				"settledAt":        now,
				"updated_at":       now,
			}).Error

		if err != nil {
			return err
		}

		//------------------------------------------------
		// Audit Trail
		//------------------------------------------------

		logs := make([]usermodel.ConsultationSettlementLog, 0, len(records))

		settlementID := settlement.ID

		for index := range records {

			logs = append(logs, usermodel.ConsultationSettlementLog{
				ConsultationID: records[index].ID,
				FromStatus:     records[index].SettlementStatus,
				ToStatus:       constants.SettlementStatusSettled,
				Remarks:        remarks,
				ActionBy:       processedBy,
				ActionSource:   actionSource,
				SettlementID:   &settlementID,
				CreatedAt:      &now,
			})
		}

		if err := tx.Create(&logs).Error; err != nil {
			return err
		}

		outcome = &BatchOutcome{
			SettlementID:  settlement.ID,
			SettlementNo:  settlement.SettlementNo,
			WalletBalance: newBalance,
			Count:         len(records),
			Gross:         gross,
			PlatformFee:   fee,
			Amount:        amount,
			PeriodFrom:    periodFrom,
			PeriodTo:      periodTo,
		}

		return nil
	})

	if err != nil {
		return nil, err
	}

	return outcome, nil
}

//////////////////////////////////////////////////////////////
// Settlement batches
//////////////////////////////////////////////////////////////

func (r *settlementRepository) batchQuery() *gorm.DB {

	return r.db.
		Table("wallet_settlements AS ws").
		Joins("LEFT JOIN astrologers AS a ON a.id = ws.astrologer_id").
		Joins("LEFT JOIN users AS u ON u.id = ws.processed_by").
		Where("ws.settlement_type = ?", constants.SettlementTypeConsultationBatch)
}

const batchSelect = `
	ws.id, ws.settlement_no, ws.astrologer_id, ws.consultation_count,
	ws.gross_amount, ws.platform_fee, ws.amount, ws.settlement_status,
	ws.period_from, ws.period_to, ws.processed_by, ws.remarks,
	ws.settlement_date, ws.created_at,
	COALESCE(NULLIF(a.displayName, ''), a.name) AS astrologerName,
	u.name AS processedByName
`

func (r *settlementRepository) ListSettlementBatches(
	astrologerID uint,
	search string,
	fromDate string,
	toDate string,
	page int,
	limit int,
) ([]SettlementBatchRow, int64, float64, error) {

	build := func() *gorm.DB {

		query := r.batchQuery()

		if astrologerID > 0 {
			query = query.Where("ws.astrologer_id = ?", astrologerID)
		}

		if value := strings.TrimSpace(search); value != "" {

			like := "%" + value + "%"

			query = query.Where(
				r.db.Where("ws.settlement_no LIKE ?", like).
					Or("a.name LIKE ?", like).
					Or("a.displayName LIKE ?", like),
			)
		}

		if value := strings.TrimSpace(fromDate); value != "" {
			query = query.Where("DATE(ws.created_at) >= ?", value)
		}

		if value := strings.TrimSpace(toDate); value != "" {
			query = query.Where("DATE(ws.created_at) <= ?", value)
		}

		return query
	}

	var total int64

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

	var totalAmount float64

	err := build().
		Select("COALESCE(SUM(ws.amount),0)").
		Scan(&totalAmount).Error

	if err != nil {
		return nil, 0, 0, err
	}

	if page <= 0 {
		page = 1
	}

	if limit <= 0 {
		limit = 10
	}

	var rows []SettlementBatchRow

	err = build().
		Select(batchSelect).
		Order("ws.id DESC").
		Offset((page - 1) * limit).
		Limit(limit).
		Scan(&rows).Error

	if err != nil {
		return nil, 0, 0, err
	}

	return rows, total, totalAmount, nil
}

func (r *settlementRepository) GetSettlementBatch(
	id uint,
) (*SettlementBatchRow, error) {

	var row SettlementBatchRow

	err := r.batchQuery().
		Select(batchSelect).
		Where("ws.id = ?", id).
		Limit(1).
		Scan(&row).Error

	if err != nil {
		return nil, err
	}

	if row.ID == 0 {
		return nil, nil
	}

	return &row, nil
}

//////////////////////////////////////////////////////////////
// Summary
//////////////////////////////////////////////////////////////

func (r *settlementRepository) GetStatusAggregates() ([]StatusAggregate, error) {

	var rows []StatusAggregate

	err := r.db.
		Model(&usermodel.Consultation{}).
		Select(`settlementStatus,
		        COUNT(*) AS rowCount,
		        COALESCE(SUM(astrologerEarning),0) AS amount`).
		Where("isDelete = ?", false).
		Group("settlementStatus").
		Scan(&rows).Error

	if err != nil {
		return nil, err
	}

	return rows, nil
}

func (r *settlementRepository) GetBilledTotals(
	from *time.Time,
) (float64, float64, error) {

	var result struct {
		Gross       float64 `gorm:"column:gross"`
		PlatformFee float64 `gorm:"column:platformFee"`
	}

	query := r.db.
		Model(&usermodel.Consultation{}).
		Select(`COALESCE(SUM(grossAmount),0) AS gross,
		        COALESCE(SUM(platformFeeAmount),0) AS platformFee`).
		Where("isDelete = ?", false)

	if from != nil {
		query = query.Where("created_at >= ?", *from)
	}

	if err := query.Scan(&result).Error; err != nil {
		return 0, 0, err
	}

	return result.Gross, result.PlatformFee, nil
}

func (r *settlementRepository) GetSettledAmount(
	from *time.Time,
) (float64, error) {

	var amount float64

	query := r.db.
		Model(&astrologermodel.WalletSettlement{}).
		Select("COALESCE(SUM(amount),0)").
		Where("settlement_type = ?", constants.SettlementTypeConsultationBatch).
		Where("settlement_status = ?", constants.BatchStatusCompleted)

	if from != nil {
		query = query.Where("created_at >= ?", *from)
	}

	if err := query.Scan(&amount).Error; err != nil {
		return 0, err
	}

	return amount, nil
}

// GetAstrologerWalletLiability is money sitting in astrologer wallets: settled
// but not yet withdrawn.
func (r *settlementRepository) GetAstrologerWalletLiability() (float64, error) {

	var amount float64

	err := r.db.
		Table("user_wallets AS w").
		Joins("INNER JOIN astrologers AS a ON a.userId = w.userId").
		Where("w.isDelete = ?", false).
		Where("a.isDelete = ?", false).
		Select("COALESCE(SUM(w.amount),0)").
		Scan(&amount).Error

	if err != nil {
		return 0, err
	}

	return amount, nil
}

func (r *settlementRepository) GetLastSettlementAt() (*time.Time, error) {

	var settlement astrologermodel.WalletSettlement

	err := r.db.
		Where("settlement_type = ?", constants.SettlementTypeConsultationBatch).
		Order("id DESC").
		First(&settlement).Error

	if err != nil {

		if errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, nil
		}

		return nil, err
	}

	if settlement.SettlementDate != nil {
		return settlement.SettlementDate, nil
	}

	return settlement.CreatedAt, nil
}

//////////////////////////////////////////////////////////////
// Astrologer
//////////////////////////////////////////////////////////////

func (r *settlementRepository) GetAstrologer(
	astrologerID uint,
) (*astrologermodel.Astrologer, error) {

	var astrologer astrologermodel.Astrologer

	err := r.db.
		Where("id = ?", astrologerID).
		First(&astrologer).Error

	if err != nil {

		if errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, nil
		}

		return nil, err
	}

	return &astrologer, nil
}

//////////////////////////////////////////////////////////////
// Configuration
//////////////////////////////////////////////////////////////

func (r *settlementRepository) GetSystemFlags(
	names []string,
) (map[string]string, error) {

	values := map[string]string{}

	if len(names) == 0 {
		return values, nil
	}

	var flags []usermodel.SystemFlag

	err := r.db.
		Where("name IN ?", names).
		Where("isDelete = ?", false).
		Find(&flags).Error

	if err != nil {
		return nil, err
	}

	for _, flag := range flags {
		values[flag.Name] = strings.TrimSpace(flag.Value)
	}

	return values, nil
}

func (r *settlementRepository) SetSystemFlag(
	name string,
	value string,
	valueType string,
	displayName string,
) error {

	var flag usermodel.SystemFlag

	err := r.db.
		Where("name = ?", name).
		First(&flag).Error

	if err != nil {

		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return err
		}

		now := time.Now()

		flag = usermodel.SystemFlag{
			Name:        name,
			Value:       value,
			ValueType:   valueType,
			DisplayName: displayName,
			IsActive:    true,
			ViewEnable:  true,
			CreatedAt:   now,
			UpdatedAt:   now,
		}

		return r.db.Create(&flag).Error
	}

	return r.db.
		Model(&usermodel.SystemFlag{}).
		Where("id = ?", flag.ID).
		Updates(map[string]interface{}{
			"value":      value,
			"isActive":   true,
			"isDelete":   false,
			"updated_at": time.Now(),
		}).Error
}

// settlementNumber is the batch's human-readable reference. Unique per
// astrologer per second, which is finer than a batch can be produced.
func settlementNumber(now time.Time, astrologerID uint) string {

	return fmt.Sprintf("STL%s%04d", now.Format("060102150405"), astrologerID%10000)
}

func round2(value float64) float64 {

	return math.Round(value*100) / 100
}