lti-api/internal/modules/repports/repositories/customer_payment.repository.go

package repositories

import (
	"context"
	"strings"
	"time"

	"gorm.io/gorm"
)

type CustomerPaymentTransaction struct {
	TransactionType string     `gorm:"column:transaction_type"`
	TransactionID   int64      `gorm:"column:transaction_id"`
	CustomerID      int64      `gorm:"column:customer_id"`
	TransDate       time.Time  `gorm:"column:trans_date"`
	DeliveryDate    *time.Time `gorm:"column:delivery_date"`
	Reference       string     `gorm:"column:reference"`
	VehicleNumbers  string     `gorm:"column:vehicle_numbers"`
	Qty             float64    `gorm:"column:qty"`
	Weight          float64    `gorm:"column:weight"`
	AverageWeight   float64    `gorm:"column:average_weight"`
	Price           float64    `gorm:"column:price"`
	FinalPrice      float64    `gorm:"column:final_price"`
	TotalPrice      float64    `gorm:"column:total_price"`
	PaymentAmount   float64    `gorm:"column:payment_amount"`
	PickupInfo      string     `gorm:"column:pickup_info"`
	SalesPerson     string     `gorm:"column:sales_person"`
}

type CustomerPaymentRepository interface {
	GetCustomerPaymentTransactions(ctx context.Context, customerID *uint) ([]CustomerPaymentTransaction, error)
	GetInitialBalanceByCustomer(ctx context.Context, customerID uint) (float64, error)
	GetCustomerIDsWithTransactions(ctx context.Context, limit, offset int, allowedCustomerIDs []uint, sortBy, sortOrder string) ([]uint, int64, error)
}

type customerPaymentRepositoryImpl struct {
	db *gorm.DB
}

func NewCustomerPaymentRepository(db *gorm.DB) CustomerPaymentRepository {
	return &customerPaymentRepositoryImpl{db: db}
}

func (r *customerPaymentRepositoryImpl) GetCustomerPaymentTransactions(ctx context.Context, customerID *uint) ([]CustomerPaymentTransaction, error) {
	salesQuery := r.db.WithContext(ctx).
		Table("marketing_delivery_products mdp").
		Select(`
			'SALES' AS transaction_type,
			mdp.id::BIGINT AS transaction_id,
			c.id::BIGINT AS customer_id,
			m.so_date::DATE AS trans_date,
			mdp.delivery_date::DATE AS delivery_date,
			m.so_number || '-' || TO_CHAR(mdp.delivery_date, 'YYYYMMDD') || '-' || CAST(pw.warehouse_id AS VARCHAR) AS reference,
			COALESCE(mdp.vehicle_number, '') AS vehicle_numbers,

			COALESCE(mdp.usage_qty, 0)::NUMERIC(15,3) AS qty,
			COALESCE(mdp.total_weight, 0)::NUMERIC(15,3) AS weight,
			COALESCE(mdp.avg_weight, 0)::NUMERIC(15,3) AS average_weight,
			COALESCE(mdp.unit_price, 0)::NUMERIC(15,3) AS price,
			COALESCE(mdp.total_price, 0)::NUMERIC(15,3) AS final_price,
			COALESCE(mdp.total_price, 0)::NUMERIC(15,3) AS total_price,
			0::NUMERIC(15,3) AS payment_amount,
			w.name AS pickup_info,
			u.name AS sales_person
		`).
		Joins("INNER JOIN marketing_products mp ON mp.id = mdp.marketing_product_id").
		Joins("INNER JOIN marketings m ON m.id = mp.marketing_id").
		Joins("INNER JOIN customers c ON c.id = m.customer_id").
		Joins("INNER JOIN product_warehouses pw ON pw.id = mdp.product_warehouse_id").
		Joins("INNER JOIN warehouses w ON w.id = pw.warehouse_id").
		Joins("INNER JOIN users u ON u.id = m.sales_person_id").
		Where("mdp.delivery_date IS NOT NULL").
		Where("m.deleted_at IS NULL").
		Where("c.deleted_at IS NULL")

	if customerID != nil {
		salesQuery = salesQuery.Where("c.id = ?", *customerID)
	}

	paymentQuery := r.db.WithContext(ctx).
		Table("payments p").
		Select(`
			'PAYMENT' AS transaction_type,
			p.id::BIGINT AS transaction_id,
			c.id::BIGINT AS customer_id,
			p.payment_date::DATE AS trans_date,
			NULL AS delivery_date,
			COALESCE(p.reference_number, p.payment_code) AS reference,
			'-' AS vehicle_numbers,
			0::NUMERIC(15,3) AS qty,
			0::NUMERIC(15,3) AS weight,
			0::NUMERIC(15,3) AS average_weight,
			0::NUMERIC(15,3) AS price,
			0::NUMERIC(15,3) AS final_price,
			0::NUMERIC(15,3) AS total_price,
			p.nominal::NUMERIC(15,3) AS payment_amount,
			'-' AS pickup_info,
			'-' AS sales_person
		`).
		Joins("INNER JOIN customers c ON c.id = p.party_id").
		Where("p.party_type = ?", "CUSTOMER").
		Where("p.direction = ?", "IN").
		Where("p.transaction_type = ?", "PENJUALAN").
		Where("p.deleted_at IS NULL").
		Where("c.deleted_at IS NULL")

	if customerID != nil {
		paymentQuery = paymentQuery.Where("c.id = ?", *customerID)
	}

	var results []CustomerPaymentTransaction
	err := r.db.WithContext(ctx).
		Raw("? UNION ALL ? ORDER BY customer_id, trans_date, transaction_type DESC, transaction_id",
			salesQuery,
			paymentQuery,
		).
		Scan(&results).
		Error

	if err != nil {
		return nil, err
	}

	return results, nil
}

func (r *customerPaymentRepositoryImpl) GetInitialBalanceByCustomer(ctx context.Context, customerID uint) (float64, error) {
	var result struct {
		Nominal float64
	}

	err := r.db.WithContext(ctx).
		Table("payments").
		Select("COALESCE(SUM(nominal), 0) as nominal").
		Where("party_type = ?", "CUSTOMER").
		Where("party_id = ?", customerID).
		Where("transaction_type = ?", "SALDO_AWAL").
		Where("deleted_at IS NULL").
		Scan(&result).
		Error

	if err != nil {
		return 0, err
	}

	return result.Nominal, nil
}

func resolveCustomerPaymentSortClause(sortBy, sortOrder string) string {
	direction := "ASC"
	if strings.EqualFold(strings.TrimSpace(sortOrder), "desc") {
		direction = "DESC"
	}
	switch strings.ToLower(strings.TrimSpace(sortBy)) {
	case "customer":
		return "customer_name " + direction
	default:
		return "customer_name ASC"
	}
}

func (r *customerPaymentRepositoryImpl) GetCustomerIDsWithTransactions(ctx context.Context, limit, offset int, allowedCustomerIDs []uint, sortBy, sortOrder string) ([]uint, int64, error) {
	unionSQL := "(" +
		"SELECT DISTINCT c.id as customer_id, c.name as customer_name FROM marketing_delivery_products mdp " +
		"INNER JOIN marketing_products mp ON mp.id = mdp.marketing_product_id " +
		"INNER JOIN marketings m ON m.id = mp.marketing_id " +
		"INNER JOIN customers c ON c.id = m.customer_id " +
		"WHERE mdp.delivery_date IS NOT NULL AND m.deleted_at IS NULL AND c.deleted_at IS NULL " +
		"UNION " +
		"SELECT DISTINCT c.id as customer_id, c.name as customer_name FROM payments p " +
		"INNER JOIN customers c ON c.id = p.party_id " +
		"WHERE p.party_type = 'CUSTOMER' AND p.direction = 'IN' " +
		"AND p.transaction_type = 'PENJUALAN' AND p.deleted_at IS NULL AND c.deleted_at IS NULL" +
		") as customer_ids"

	subQuery := r.db.WithContext(ctx).Table(unionSQL)
	if len(allowedCustomerIDs) > 0 {
		subQuery = subQuery.Where("customer_id IN ?", allowedCustomerIDs)
	}

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

	query := r.db.WithContext(ctx).Table(unionSQL).Select("customer_id")
	if len(allowedCustomerIDs) > 0 {
		query = query.Where("customer_id IN ?", allowedCustomerIDs)
	}

	var customerIDs []uint
	err := query.
		Order(resolveCustomerPaymentSortClause(sortBy, sortOrder)).
		Limit(limit).
		Offset(offset).
		Pluck("customer_id", &customerIDs).
		Error

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

	return customerIDs, total, nil
}