package repository

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"

	"apskel-pos-be/internal/constants"
	"apskel-pos-be/internal/entities"

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

// cashAdvanceSettledAmountExpr sums the spending charged to an advance straight from
// the purchase orders and expenses that point at it. Keeping it as an expression
// rather than a column means an advance can never disagree with the purchases behind it,
// whichever screen edited them. Cancelled spending never accounted for anything.
const cashAdvanceSettledAmountExpr = `(
	COALESCE((SELECT SUM(po.total_amount) FROM purchase_orders po
		WHERE po.cash_advance_id = cash_advances.id AND po.status <> 'cancelled'), 0)
	+ COALESCE((SELECT SUM(e.total) FROM expenses e
		WHERE e.cash_advance_id = cash_advances.id AND e.status <> 'cancel'), 0)
)`

// Money is stored to two decimals, so half a cent is the smallest gap that means
// anything. The filters use it for the same reason the mapper does.
const cashAdvanceAmountEpsilon = 0.005

type CashAdvanceRepositoryImpl struct {
	db *gorm.DB
}

func NewCashAdvanceRepositoryImpl(db *gorm.DB) *CashAdvanceRepositoryImpl {
	return &CashAdvanceRepositoryImpl{db: db}
}

func (r *CashAdvanceRepositoryImpl) Create(ctx context.Context, cashAdvance *entities.CashAdvance) error {
	return r.db.WithContext(ctx).Create(cashAdvance).Error
}

func (r *CashAdvanceRepositoryImpl) GetByID(ctx context.Context, id uuid.UUID) (*entities.CashAdvance, error) {
	var cashAdvance entities.CashAdvance
	err := r.db.WithContext(ctx).
		Model(&entities.CashAdvance{}).
		Select("cash_advances.*, "+cashAdvanceSettledAmountExpr+" AS settled_amount").
		Preload("Outlet").
		Preload("TeamCategory").
		Where("cash_advances.id = ?", id).
		First(&cashAdvance).Error
	if err != nil {
		return nil, err
	}
	return &cashAdvance, nil
}

func (r *CashAdvanceRepositoryImpl) GetByIDAndOrganizationID(ctx context.Context, id, organizationID uuid.UUID) (*entities.CashAdvance, error) {
	var cashAdvance entities.CashAdvance
	err := r.db.WithContext(ctx).
		Model(&entities.CashAdvance{}).
		Select("cash_advances.*, "+cashAdvanceSettledAmountExpr+" AS settled_amount").
		Preload("Outlet").
		Preload("TeamCategory").
		Where("cash_advances.id = ? AND cash_advances.organization_id = ?", id, organizationID).
		First(&cashAdvance).Error
	if err != nil {
		return nil, err
	}
	return &cashAdvance, nil
}

func (r *CashAdvanceRepositoryImpl) GetByCodeNumber(ctx context.Context, codeNumber string, organizationID uuid.UUID) (*entities.CashAdvance, error) {
	var cashAdvance entities.CashAdvance
	err := r.db.WithContext(ctx).
		Where("code_number = ? AND organization_id = ?", codeNumber, organizationID).
		First(&cashAdvance).Error
	if err != nil {
		return nil, err
	}
	return &cashAdvance, nil
}

func (r *CashAdvanceRepositoryImpl) Update(ctx context.Context, cashAdvance *entities.CashAdvance) error {
	// Omit associations so a preloaded TeamCategory or Outlet is not written back
	// over the row it came from.
	return r.db.WithContext(ctx).Omit(clause.Associations).Save(cashAdvance).Error
}

func (r *CashAdvanceRepositoryImpl) Delete(ctx context.Context, id uuid.UUID) error {
	return r.db.WithContext(ctx).Delete(&entities.CashAdvance{}, "id = ?", id).Error
}

func (r *CashAdvanceRepositoryImpl) List(ctx context.Context, organizationID uuid.UUID, filters map[string]interface{}, limit, offset int) ([]*entities.CashAdvance, int64, error) {
	var cashAdvances []*entities.CashAdvance
	var total int64

	// Count on its own query: the select list carries a correlated subquery, which
	// GORM would otherwise drag into the COUNT.
	countQuery := applyCashAdvanceFilters(r.db.WithContext(ctx).Model(&entities.CashAdvance{}).Where("cash_advances.organization_id = ?", organizationID), filters)
	if err := countQuery.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	query := applyCashAdvanceFilters(r.db.WithContext(ctx).Model(&entities.CashAdvance{}).Where("cash_advances.organization_id = ?", organizationID), filters)
	err := query.
		Select("cash_advances.*, " + cashAdvanceSettledAmountExpr + " AS settled_amount").
		Preload("Outlet").
		Preload("TeamCategory").
		Order("cash_advances.issued_date DESC, cash_advances.created_at DESC").
		Limit(limit).
		Offset(offset).
		Find(&cashAdvances).Error

	return cashAdvances, total, err
}

func applyCashAdvanceFilters(query *gorm.DB, filters map[string]interface{}) *gorm.DB {
	for key, value := range filters {
		switch key {
		case "search":
			if search, ok := value.(string); ok && search != "" {
				pattern := "%" + strings.ToLower(search) + "%"
				query = query.Where("LOWER(cash_advances.code_number) LIKE ? OR LOWER(cash_advances.description) LIKE ?", pattern, pattern)
			}
		case "status":
			if status, ok := value.(string); ok && status != "" {
				query = query.Where("cash_advances.status = ?", status)
			}
		case "outlet_id":
			if outletID, ok := value.(uuid.UUID); ok {
				query = query.Where("cash_advances.outlet_id = ?", outletID)
			}
		case "team_scope":
			if teamScope, ok := value.(string); ok && teamScope != "" {
				query = query.Where("cash_advances.team_scope = ?", teamScope)
			}
		case "team_category_id":
			if teamCategoryID, ok := value.(uuid.UUID); ok {
				query = query.Where("cash_advances.team_category_id = ?", teamCategoryID)
			}
		case "settlement_status":
			query = applyCashAdvanceSettlementFilter(query, value)
		case "start_date":
			if startDate, ok := value.(time.Time); ok {
				query = query.Where("cash_advances.issued_date >= ?", startDate)
			}
		case "end_date":
			if endDate, ok := value.(time.Time); ok {
				query = query.Where("cash_advances.issued_date <= ?", endDate)
			}
		}
	}

	return query
}

// applyCashAdvanceSettlementFilter reproduces in SQL what the mapper computes in Go:
// how much of the advance has been accounted for, by spending plus cash returned.
func applyCashAdvanceSettlementFilter(query *gorm.DB, value interface{}) *gorm.DB {
	status, ok := value.(string)
	if !ok || status == "" {
		return query
	}

	accounted := cashAdvanceSettledAmountExpr + " + cash_advances.returned_amount"

	switch status {
	case constants.CashAdvanceSettlementOpen:
		return query.Where(accounted+" <= ?", cashAdvanceAmountEpsilon)
	case constants.CashAdvanceSettlementPartial:
		return query.
			Where(accounted+" > ?", cashAdvanceAmountEpsilon).
			Where("cash_advances.amount - ("+accounted+") > ?", cashAdvanceAmountEpsilon)
	case constants.CashAdvanceSettlementSettled:
		return query.
			Where(accounted+" > ?", cashAdvanceAmountEpsilon).
			Where("cash_advances.amount - ("+accounted+") <= ?", cashAdvanceAmountEpsilon)
	}

	return query
}

// ListSettlements returns the spending charged to an advance, newest first. Purchase
// orders and expenses are two tables recording the same thing here, so they are
// read as one list.
func (r *CashAdvanceRepositoryImpl) ListSettlements(ctx context.Context, cashAdvanceID uuid.UUID) ([]*entities.CashAdvanceSettlement, error) {
	query := fmt.Sprintf(`
		SELECT '%s' AS type, po.id AS id, po.po_number AS number,
		       po.transaction_date AS date, po.total_amount AS amount, po.status AS status
		FROM purchase_orders po
		WHERE po.cash_advance_id = ?
		UNION ALL
		SELECT '%s' AS type, e.id AS id, e.code_number AS number,
		       e.transaction_date AS date, e.total AS amount, e.status AS status
		FROM expenses e
		WHERE e.cash_advance_id = ?
		ORDER BY date DESC`,
		constants.CashAdvanceSettlementTypePurchaseOrder,
		constants.CashAdvanceSettlementTypeExpense,
	)

	var settlements []*entities.CashAdvanceSettlement
	err := r.db.WithContext(ctx).Raw(query, cashAdvanceID, cashAdvanceID).Scan(&settlements).Error
	return settlements, err
}

// CountSettlements is what stops an advance being deleted once spending has been
// charged to it; the foreign keys would refuse anyway, but not with a readable error.
func (r *CashAdvanceRepositoryImpl) CountSettlements(ctx context.Context, cashAdvanceID uuid.UUID) (int64, error) {
	var count int64
	err := r.db.WithContext(ctx).Raw(`
		SELECT (SELECT COUNT(*) FROM purchase_orders WHERE cash_advance_id = ?)
		     + (SELECT COUNT(*) FROM expenses WHERE cash_advance_id = ?)`,
		cashAdvanceID, cashAdvanceID).Scan(&count).Error
	return count, err
}