package repositories_astrologer

import (
	"fmt"
	"strings"
	"sync"
	"time"

	dto "astrology-api/dto_astrologer"
)

///////////////////////////////////////////////////////////
// Wallet Transaction History
///////////////////////////////////////////////////////////
//
// GET /api/astrologer/wallet/transactions - the astrologer app's
// "Transaction History" list (All / Earnings / Withdrawals).
//
// wallet_ledger alone cannot answer it: a paid chat or call is one
// consultations row whose astrologerEarning is owed, not credited, until a
// settlement run pays it out, and an astromall order's earning lives on
// astromall_orders. So the list is a UNION of three sources:
//
//   consultations     billed sessions (settlementStatus <> 'NA')
//   astromall_orders  orders with an earning, not cancelled
//   wallet_ledger     everything else that moved the wallet
//
// Ledger rows that would repeat the other two are left out: SETTLEMENT credits
// are the payout of sessions already listed, and CHAT / CALL / ASTROMALL rows
// are never written by the API (only seeded demo data carries them). A
// SETTLEMENT *debit* is a reversal of a settled earning and does show.

// Transaction filters accepted in ?type=, case-insensitive.
const (
	TxnFilterAll         = "ALL"
	TxnFilterEarnings    = "EARNINGS"
	TxnFilterWithdrawals = "WITHDRAWALS"
	TxnFilterChat        = "CHAT"
	TxnFilterCall        = "CALL"
	TxnFilterAudioCall   = "AUDIO_CALL"
	TxnFilterVideoCall   = "VIDEO_CALL"
	TxnFilterOrder       = "ORDER"
)

// historyRow is one row of the UNION, before it is shaped for the app.
type historyRow struct {
	Source       string
	RowID        uint
	ReferenceID  uint
	ReferenceNo  string
	TxnType      string
	Amount       float64
	IsCredit     bool
	RawStatus    string
	HappenedAt   *time.Time
	Counterparty string
	Detail       string
	Minutes      int
	BalanceAfter float64
	Remarks      string
}

// Every text column is converted to one collation: consultations, astromall_orders
// and wallet_ledger were created with different ones, and MySQL refuses to
// UNION them otherwise ("Illegal mix of collations").

// historyBranch is one SELECT of the UNION with its bind arguments.
type historyBranch struct {
	sql  string
	args []interface{}
}

var (
	consultationsTableOnce   sync.Once
	consultationsTableExists bool
)

// hasConsultations reports whether the consultations table exists. It is
// created by migrations/2026_09_11_consultation_settlement.sql, applied by
// hand, so an environment without it lists the ledger and orders rather than
// failing the whole screen.
func (r *walletRepository) hasConsultations() bool {

	consultationsTableOnce.Do(func() {
		consultationsTableExists = r.db.Migrator().HasTable("consultations")
	})

	return consultationsTableExists
}

// normalizeTxnFilter maps the app's tab names and their spellings onto one
// filter value.
func normalizeTxnFilter(filter string) string {

	value := strings.ToUpper(strings.TrimSpace(filter))

	switch value {
	case "", "ALL":
		return TxnFilterAll
	case "EARNING", "EARNINGS", "CREDIT", "CREDITS":
		return TxnFilterEarnings
	case "WITHDRAW", "WITHDRAWAL", "WITHDRAWALS", "DEBIT", "DEBITS":
		return TxnFilterWithdrawals
	case "CHAT", "CHATS":
		return TxnFilterChat
	case "CALL", "CALLS":
		return TxnFilterCall
	case "AUDIO", "AUDIO_CALL", "AUDIOCALL":
		return TxnFilterAudioCall
	case "VIDEO", "VIDEO_CALL", "VIDEOCALL":
		return TxnFilterVideoCall
	case "ORDER", "ORDERS", "ASTROMALL", "ASTROSHOP":
		return TxnFilterOrder
	}

	// Any other ledger type: GIFT, REPORT, REFUND, ADMIN, SETTLEMENT.
	return value
}

func (r *walletRepository) consultationBranch(astrologerID uint, filter string) *historyBranch {

	var mediums []string

	switch filter {
	case TxnFilterAll, TxnFilterEarnings:
		mediums = nil
	case TxnFilterChat:
		mediums = []string{"CHAT"}
	case TxnFilterCall:
		mediums = []string{"AUDIO", "VIDEO"}
	case TxnFilterAudioCall:
		mediums = []string{"AUDIO"}
	case TxnFilterVideoCall:
		mediums = []string{"VIDEO"}
	default:
		return nil
	}

	if !r.hasConsultations() {
		return nil
	}

	sql := `
		SELECT
			CONVERT('CONSULTATION' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source,
			c.id                                             AS row_id,
			c.id                                             AS reference_id,
			CONVERT(COALESCE(c.consultationNo, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS reference_no,
			CONVERT(CASE UPPER(c.medium)
				WHEN 'CHAT'  THEN 'CHAT'
				WHEN 'AUDIO' THEN 'AUDIO_CALL'
				WHEN 'VIDEO' THEN 'VIDEO_CALL'
				ELSE UPPER(c.medium)
			END USING utf8mb4) COLLATE utf8mb4_unicode_ci AS txn_type,
			c.astrologerEarning                              AS amount,
			1                                                AS is_credit,
			CONVERT(c.settlementStatus USING utf8mb4) COLLATE utf8mb4_unicode_ci AS raw_status,
			COALESCE(c.endedAt, c.updated_at, c.created_at)  AS happened_at,
			CONVERT(COALESCE(NULLIF(u.name, ''), c.name, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS counterparty,
			CONVERT('' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS detail,
			COALESCE(c.billedMinutes, 0)                     AS minutes,
			0                                                AS balance_after,
			CONVERT('' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS remarks
		FROM consultations c
		LEFT JOIN users u ON u.id = c.userId
		WHERE c.astrologerId = ?
		  AND c.isDelete = 0
		  AND c.settlementStatus <> 'NA'
		  AND c.astrologerEarning > 0`

	args := []interface{}{astrologerID}

	if len(mediums) > 0 {
		sql += " AND UPPER(c.medium) IN ?"
		args = append(args, mediums)
	}

	return &historyBranch{sql: sql, args: args}
}

func orderBranch(astrologerID uint, filter string) *historyBranch {

	if filter != TxnFilterAll && filter != TxnFilterEarnings && filter != TxnFilterOrder {
		return nil
	}

	return &historyBranch{
		sql: `
		SELECT
			CONVERT('ORDER' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source,
			ao.id                                    AS row_id,
			ao.id                                    AS reference_id,
			CONVERT(CONCAT('ORD', ao.id) USING utf8mb4) COLLATE utf8mb4_unicode_ci AS reference_no,
			CONVERT('ORDER' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS txn_type,
			ao.astrologer_earning                    AS amount,
			1                                        AS is_credit,
			CONVERT(COALESCE(ao.status, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS raw_status,
			ao.created_at                            AS happened_at,
			CONVERT(COALESCE(u.name, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS counterparty,
			CONVERT(COALESCE(ap.name, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS detail,
			0                                        AS minutes,
			0                                        AS balance_after,
			CONVERT('' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS remarks
		FROM astromall_orders ao
		LEFT JOIN users u ON u.id = ao.user_id
		LEFT JOIN astromall_products ap ON ap.id = ao.product_id
		WHERE ao.astrologer_id = ?
		  AND ao.astrologer_earning > 0
		  AND (ao.status IS NULL OR ao.status <> 'Cancelled')`,
		args: []interface{}{astrologerID},
	}
}

func (r *walletRepository) ledgerBranch(astrologerID uint, filter string) *historyBranch {

	refExpr, detailExpr := r.withdrawColumns()

	sql := `
		SELECT
			CONVERT('LEDGER' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source,
			wl.id                                                     AS row_id,
			COALESCE(wl.reference_id, 0)                              AS reference_id,
			CONVERT(` + refExpr + ` USING utf8mb4) COLLATE utf8mb4_unicode_ci AS reference_no,
			CONVERT(wl.transaction_type USING utf8mb4) COLLATE utf8mb4_unicode_ci AS txn_type,
			IF(wl.credit > 0, wl.credit, wl.debit)                    AS amount,
			IF(wl.credit > 0, 1, 0)                                   AS is_credit,
			CONVERT(COALESCE(w.status, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS raw_status,
			wl.created_at                                             AS happened_at,
			CONVERT('' USING utf8mb4) COLLATE utf8mb4_unicode_ci AS counterparty,
			CONVERT(` + detailExpr + ` USING utf8mb4) COLLATE utf8mb4_unicode_ci AS detail,
			0                                                         AS minutes,
			COALESCE(wl.balance_after, 0)                             AS balance_after,
			CONVERT(COALESCE(wl.remarks, '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS remarks
		FROM wallet_ledger wl
		LEFT JOIN withdrawrequest w
		       ON wl.transaction_type = 'WITHDRAW' AND w.id = wl.reference_id
		WHERE wl.astrologer_id = ?`

	args := []interface{}{astrologerID}

	// Rows the other two branches already list.
	const notDuplicated = `
		  AND wl.transaction_type NOT IN ('CHAT', 'CALL', 'ASTROMALL')
		  AND NOT (wl.transaction_type = 'SETTLEMENT' AND wl.credit > 0)`

	switch filter {
	case TxnFilterAll:
		sql += notDuplicated
	case TxnFilterEarnings:
		sql += notDuplicated + " AND wl.credit > 0"
	case TxnFilterWithdrawals:
		sql += " AND wl.transaction_type = 'WITHDRAW'"
	case TxnFilterChat, TxnFilterCall, TxnFilterAudioCall, TxnFilterVideoCall, TxnFilterOrder:
		return nil
	default:
		// An explicit ledger type shows all of its rows, SETTLEMENT included.
		sql += " AND wl.transaction_type = ?"
		args = append(args, filter)
	}

	return &historyBranch{sql: sql, args: args}
}

func (r *walletRepository) GetTransactions(
	astrologerID uint,
	filter string,
	page int,
	limit int,
) ([]dto.WalletTransactionResponse, int64, error) {

	filter = normalizeTxnFilter(filter)

	var (
		parts []string
		args  []interface{}
	)

	for _, branch := range []*historyBranch{
		r.consultationBranch(astrologerID, filter),
		orderBranch(astrologerID, filter),
		r.ledgerBranch(astrologerID, filter),
	} {

		if branch == nil {
			continue
		}

		parts = append(parts, branch.sql)
		args = append(args, branch.args...)
	}

	if len(parts) == 0 {
		return []dto.WalletTransactionResponse{}, 0, nil
	}

	union := strings.Join(parts, "\nUNION ALL\n")

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

	var total int64

	if err := r.db.
		Raw("SELECT COUNT(*) FROM ("+union+") history", args...).
		Scan(&total).Error; err != nil {
		return nil, 0, err
	}

	if total == 0 {
		return []dto.WalletTransactionResponse{}, 0, nil
	}

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

	var rows []historyRow

	pageArgs := append(append([]interface{}{}, args...), limit, (page-1)*limit)

	if err := r.db.
		Raw(
			"SELECT * FROM ("+union+") history "+
				"ORDER BY happened_at DESC, source ASC, row_id DESC LIMIT ? OFFSET ?",
			pageArgs...,
		).
		Scan(&rows).Error; err != nil {
		return nil, 0, err
	}

	response := make([]dto.WalletTransactionResponse, 0, len(rows))

	for _, row := range rows {
		response = append(response, shapeHistoryRow(row))
	}

	return response, total, nil
}

///////////////////////////////////////////////////////////
// Presentation
///////////////////////////////////////////////////////////

func shapeHistoryRow(row historyRow) dto.WalletTransactionResponse {

	item := dto.WalletTransactionResponse{
		ID:              row.RowID,
		Source:          row.Source,
		ReferenceID:     row.ReferenceID,
		ReferenceNo:     row.ReferenceNo,
		TransactionType: row.TxnType,
		Amount:          row.Amount,
		IsCredit:        row.IsCredit,
		BalanceAfter:    row.BalanceAfter,
		Remarks:         row.Remarks,
		CustomerName:    row.Counterparty,
		DurationMinutes: row.Minutes,
	}

	if row.IsCredit {
		item.CreditAmount = row.Amount
		item.Category = "EARNING"
	} else {
		item.DebitAmount = row.Amount
		item.Category = "WITHDRAWAL"
	}

	if row.HappenedAt != nil {
		at := row.HappenedAt.Local()
		item.TransactionDate = at.Format("02 Jan 2006")
		item.TransactionTime = at.Format("03:04 PM")
		item.TransactionDateTime = at.Format("02 Jan 2006, 03:04 PM")
		item.CreatedAt = at.Format(time.RFC3339)
	}

	switch row.Source {

	case "CONSULTATION":
		item.TransactionID = fmt.Sprintf("CON-%d", row.RowID)
		shapeConsultation(&item, row)

	case "ORDER":
		item.TransactionID = fmt.Sprintf("ORD-%d", row.RowID)
		item.Title = "Astromall Order"
		item.Icon = "shopping"
		item.Description = row.Detail
		item.Status, item.StatusCode = orderStatus(row.RawStatus)

	default:
		item.TransactionID = fmt.Sprintf("TXN-%d", row.RowID)
		shapeLedger(&item, row)
	}

	return item
}

func shapeConsultation(item *dto.WalletTransactionResponse, row historyRow) {

	switch row.TxnType {
	case "CHAT":
		item.Title = "Chat Consultation"
		item.Icon = "chat"
	case "AUDIO_CALL":
		item.Title = "Audio Call Consultation"
		item.Icon = "call"
	case "VIDEO_CALL":
		item.Title = "Video Call Consultation"
		item.Icon = "video"
	default:
		item.Title = "Consultation"
		item.Icon = "wallet"
	}

	var parts []string

	if row.Counterparty != "" {
		parts = append(parts, "with "+row.Counterparty)
	}

	if row.Minutes > 0 {
		parts = append(parts, fmt.Sprintf("%d min", row.Minutes))
	}

	item.Description = strings.Join(parts, " · ")

	// The session itself is complete; settlementStatus says whether its
	// earning has reached the wallet yet.
	item.SettlementStatus = row.RawStatus

	switch row.RawStatus {
	case "SETTLED":
		item.Status, item.StatusCode = "Completed", "COMPLETED"
		item.SettlementLabel = "Settled"
	case "ON_HOLD":
		item.Status, item.StatusCode = "On Hold", "ON_HOLD"
		item.SettlementLabel = "On hold"
	case "REJECTED":
		item.Status, item.StatusCode = "Rejected", "REJECTED"
		item.SettlementLabel = "Rejected"
	default: // PENDING, TO_BE_SETTLED
		item.Status, item.StatusCode = "Completed", "COMPLETED"
		item.SettlementLabel = "Settlement pending"
	}
}

func shapeLedger(item *dto.WalletTransactionResponse, row historyRow) {

	item.Description = row.Remarks

	switch row.TxnType {

	case "WITHDRAW":
		item.Title = "Withdrawal to Bank"

		if strings.EqualFold(row.Detail, "UPI") {
			item.Title = "Withdrawal to UPI"
		}

		item.Icon = "withdraw"
		item.Status, item.StatusCode = withdrawStatus(row.RawStatus)

		return

	case "SETTLEMENT":
		if row.IsCredit {
			item.Title = "Settlement Credited"
		} else {
			item.Title = "Earning Reversed"
		}
		item.Icon = "settlement"

	case "GIFT":
		item.Title = "Gift Received"
		item.Icon = "gift"

	case "REPORT":
		item.Title = "Report Earnings"
		item.Icon = "report"

	case "REFUND":
		item.Title = "Refund"
		item.Icon = "refund"

	case "ADMIN":
		item.Title = "Admin Adjustment"
		item.Icon = "wallet"

	default:
		item.Title = row.TxnType
		item.Icon = "wallet"
	}

	item.Status, item.StatusCode = "Completed", "COMPLETED"
}

// withdrawStatus maps withdrawrequest.status onto the badge in the design:
// Paid shows as "Processed". A ledger row with no request is an old instant
// withdrawal and was paid.
func withdrawStatus(raw string) (string, string) {

	switch strings.ToLower(raw) {
	case "pending":
		return "Pending", "PENDING"
	case "processing", "approved":
		return "Processing", "PROCESSING"
	case "rejected":
		return "Rejected", "REJECTED"
	case "failed":
		return "Failed", "FAILED"
	}

	return "Processed", "PROCESSED"
}

func orderStatus(raw string) (string, string) {

	switch strings.ToLower(raw) {
	case "pending":
		return "Pending", "PENDING"
	case "confirmed":
		return "Processing", "PROCESSING"
	case "delivered":
		return "Completed", "COMPLETED"
	}

	return "Completed", "COMPLETED"
}

///////////////////////////////////////////////////////////
// withdrawrequest columns
///////////////////////////////////////////////////////////
//
// The admin panel and migrations/2026_09_10_astrologer_withdraw_types.sql name
// the same facts differently (request_no vs requestNo, and bankName exists
// only on the Go side), and an environment may carry either or both. The
// history query only reads them for display, so it uses whichever exist.

var (
	withdrawColumnsOnce sync.Once
	withdrawRefExpr     = "''"
	withdrawDetailExpr  = "''"
)

func (r *walletRepository) withdrawColumns() (string, string) {

	withdrawColumnsOnce.Do(func() {

		has := func(column string) bool {
			return r.db.Migrator().HasColumn("withdrawrequest", column)
		}

		var refs, details []string

		for _, column := range []string{"requestNo", "request_no"} {
			if has(column) {
				refs = append(refs, "NULLIF(w."+column+", '')")
			}
		}

		for _, column := range []string{"bankName", "paymentMethod"} {
			if has(column) {
				details = append(details, "NULLIF(w."+column+", '')")
			}
		}

		if len(refs) > 0 {
			withdrawRefExpr = "COALESCE(" + strings.Join(refs, ", ") + ", '')"
		}

		if len(details) > 0 {
			withdrawDetailExpr = "COALESCE(" + strings.Join(details, ", ") + ", '')"
		}
	})

	return withdrawRefExpr, withdrawDetailExpr
}