apskel-pos-backend/internal/repository/expense_repository.go

package repository

import (
	"context"
	"strings"
	"time"

	"github.com/google/uuid"

	"apskel-pos-be/internal/entities"

	"gorm.io/gorm"
)

type ExpenseRepositoryImpl struct {
	db *gorm.DB
}

func NewExpenseRepositoryImpl(db *gorm.DB) *ExpenseRepositoryImpl {
	return &ExpenseRepositoryImpl{
		db: db,
	}
}

func (r *ExpenseRepositoryImpl) Create(ctx context.Context, expense *entities.Expense) error {
	return r.db.WithContext(ctx).Create(expense).Error
}

func (r *ExpenseRepositoryImpl) GetByID(ctx context.Context, id uuid.UUID) (*entities.Expense, error) {
	var expense entities.Expense
	err := r.db.WithContext(ctx).
		Preload("Items.ChartOfAccount").
		Preload("Items.PurchaseCategory").
		First(&expense, "id = ?", id).Error
	if err != nil {
		return nil, err
	}
	return &expense, nil
}

func (r *ExpenseRepositoryImpl) GetByIDAndOrganizationID(ctx context.Context, id, organizationID uuid.UUID) (*entities.Expense, error) {
	var expense entities.Expense
	err := r.db.WithContext(ctx).
		Preload("Items.ChartOfAccount").
		Preload("Items.PurchaseCategory").
		Where("id = ? AND organization_id = ?", id, organizationID).
		First(&expense).Error
	if err != nil {
		return nil, err
	}
	return &expense, nil
}

func (r *ExpenseRepositoryImpl) Update(ctx context.Context, expense *entities.Expense) error {
	return r.db.WithContext(ctx).Save(expense).Error
}

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

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

	query := r.db.WithContext(ctx).Model(&entities.Expense{}).Where("organization_id = ?", organizationID)

	for key, value := range filters {
		switch key {
		case "search":
			if searchStr, ok := value.(string); ok && searchStr != "" {
				searchPattern := "%" + strings.ToLower(searchStr) + "%"
				query = query.Where(`
					LOWER(receiver) LIKE ?
					OR LOWER(code_number) LIKE ?
					OR LOWER(description) LIKE ?
					OR EXISTS (
						SELECT 1
						FROM expense_items ei
						WHERE ei.expense_id = expenses.id
						  AND LOWER(ei.item) LIKE ?
					)
				`, searchPattern, searchPattern, searchPattern, searchPattern)
			}
		case "outlet_id":
			if outletID, ok := value.(uuid.UUID); ok {
				query = query.Where("outlet_id = ?", outletID)
			}
		case "status":
			if status, ok := value.(string); ok && status != "" {
				query = query.Where("status = ?", status)
			}
		case "start_date":
			if startDate, ok := value.(time.Time); ok {
				query = query.Where("transaction_date >= ?", startDate)
			}
		case "end_date":
			if endDate, ok := value.(time.Time); ok {
				query = query.Where("transaction_date <= ?", endDate)
			}
		default:
			query = query.Where(key+" = ?", value)
		}
	}

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

	err := query.
		Preload("Items.ChartOfAccount").
		Preload("Items.PurchaseCategory").
		Order("created_at DESC").
		Limit(limit).
		Offset(offset).
		Find(&expenses).Error
	return expenses, total, err
}

func (r *ExpenseRepositoryImpl) GetAnalytics(ctx context.Context, organizationID uuid.UUID, outletID *uuid.UUID, dateFrom, dateTo time.Time, groupBy string) (*entities.ExpenseAnalytics, error) {
	var summary entities.ExpenseAnalyticsSummary

	summaryQuery := r.db.WithContext(ctx).
		Table("expenses e").
		Select(`
			COALESCE(SUM(e.total), 0) as total_expenses,
			COUNT(e.id) as total_expense_count,
			COALESCE(SUM(e.tax), 0) as total_tax,
			COALESCE(AVG(e.total), 0) as average_expense_value
		`).
		Where("e.organization_id = ?", organizationID).
		Where("e.status = ?", "approved").
		Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo)
	if outletID != nil {
		summaryQuery = summaryQuery.Where("e.outlet_id = ?", *outletID)
	}
	if err := summaryQuery.Scan(&summary).Error; err != nil {
		return nil, err
	}

	countsQuery := r.db.WithContext(ctx).
		Table("expense_items ei").
		Select(`
			COUNT(ei.id) as total_items,
			COUNT(DISTINCT ei.purchase_category_id) as total_categories
		`).
		Joins("JOIN expenses e ON ei.expense_id = e.id").
		Where("e.organization_id = ?", organizationID).
		Where("e.status = ?", "approved").
		Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo)
	if outletID != nil {
		countsQuery = countsQuery.Where("e.outlet_id = ?", *outletID)
	}
	if err := countsQuery.Scan(&summary).Error; err != nil {
		return nil, err
	}

	dateFormat := "DATE_TRUNC('day', e.transaction_date)"
	switch groupBy {
	case "hour":
		dateFormat = "DATE_TRUNC('hour', e.transaction_date)"
	case "week":
		dateFormat = "DATE_TRUNC('week', e.transaction_date)"
	case "month":
		dateFormat = "DATE_TRUNC('month', e.transaction_date)"
	}

	var data []entities.ExpenseAnalyticsData
	dataQuery := r.db.WithContext(ctx).
		Table("expenses e").
		Select(`
			`+dateFormat+` as date,
			COALESCE(SUM(e.total), 0) as expenses,
			COUNT(e.id) as expense_count,
			COALESCE(SUM(e.tax), 0) as tax,
			COALESCE(SUM(item_counts.items), 0) as items,
			COALESCE(SUM(item_counts.categories), 0) as categories
		`).
		Joins(`LEFT JOIN (
			SELECT expense_id, COUNT(id) as items, COUNT(DISTINCT purchase_category_id) as categories
			FROM expense_items
			GROUP BY expense_id
		) item_counts ON item_counts.expense_id = e.id`).
		Where("e.organization_id = ?", organizationID).
		Where("e.status = ?", "approved").
		Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo).
		Group(dateFormat).
		Order(dateFormat)
	if outletID != nil {
		dataQuery = dataQuery.Where("e.outlet_id = ?", *outletID)
	}
	if err := dataQuery.Scan(&data).Error; err != nil {
		return nil, err
	}

	var categoryData []entities.ExpenseAnalyticsCategoryData
	categoryQuery := r.db.WithContext(ctx).
		Table("expense_items ei").
		Select(`
			pc.id as purchase_category_id,
			pc.name as purchase_category_name,
			pc.type as purchase_category_type,
			COALESCE(SUM(ei.amount), 0) as total_amount,
			COUNT(DISTINCT e.id) as expense_count,
			COUNT(ei.id) as item_count
		`).
		Joins("JOIN expenses e ON ei.expense_id = e.id").
		Joins("JOIN purchase_categories pc ON ei.purchase_category_id = pc.id").
		Where("e.organization_id = ?", organizationID).
		Where("pc.type = ?", entities.PurchaseCategoryTypeExpense).
		Where("e.status = ?", "approved").
		Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo).
		Group("pc.id, pc.name, pc.type").
		Order("total_amount DESC")
	if outletID != nil {
		categoryQuery = categoryQuery.Where("e.outlet_id = ?", *outletID)
	}
	if err := categoryQuery.Scan(&categoryData).Error; err != nil {
		return nil, err
	}

	var chartOfAccountData []entities.ExpenseAnalyticsChartOfAccountData
	chartOfAccountQuery := r.db.WithContext(ctx).
		Table("expense_items ei").
		Select(`
			COALESCE(parent_coa.id, coa.id) as chart_of_account_id,
			COALESCE(parent_coa.name, coa.name, 'Lain-lain') as chart_of_account_name,
			COALESCE(SUM(ei.amount), 0) as total_amount,
			COUNT(DISTINCT e.id) as expense_count,
			COUNT(ei.id) as item_count
		`).
		Joins("JOIN expenses e ON ei.expense_id = e.id").
		Joins("JOIN chart_of_accounts coa ON ei.chart_of_account_id = coa.id").
		Joins("LEFT JOIN chart_of_accounts parent_coa ON coa.parent_id = parent_coa.id").
		Where("e.organization_id = ?", organizationID).
		Where("e.status = ?", "approved").
		Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo).
		Group("COALESCE(parent_coa.id, coa.id), COALESCE(parent_coa.name, coa.name, 'Lain-lain')").
		Order("total_amount DESC")
	if outletID != nil {
		chartOfAccountQuery = chartOfAccountQuery.Where("e.outlet_id = ?", *outletID)
	}
	if err := chartOfAccountQuery.Scan(&chartOfAccountData).Error; err != nil {
		return nil, err
	}

	var itemData []entities.ExpenseAnalyticsItemData
	itemQuery := r.db.WithContext(ctx).
		Table("expense_items ei").
		Select(`
			COALESCE(NULLIF(ei.item, ''), ei.description, coa.name) as item,
			COALESCE(SUM(ei.amount), 0) as total_amount,
			COUNT(DISTINCT e.id) as expense_count,
			COUNT(ei.id) as item_count
		`).
		Joins("JOIN expenses e ON ei.expense_id = e.id").
		Joins("JOIN chart_of_accounts coa ON ei.chart_of_account_id = coa.id").
		Where("e.organization_id = ?", organizationID).
		Where("e.status = ?", "approved").
		Where("e.transaction_date >= ? AND e.transaction_date <= ?", dateFrom, dateTo).
		Group("COALESCE(NULLIF(ei.item, ''), ei.description, coa.name)").
		Order("total_amount DESC")
	if outletID != nil {
		itemQuery = itemQuery.Where("e.outlet_id = ?", *outletID)
	}
	if err := itemQuery.Scan(&itemData).Error; err != nil {
		return nil, err
	}

	return &entities.ExpenseAnalytics{
		Summary:            summary,
		Data:               data,
		CategoryData:       categoryData,
		ChartOfAccountData: chartOfAccountData,
		ItemData:           itemData,
	}, nil
}

func (r *ExpenseRepositoryImpl) CreateItem(ctx context.Context, item *entities.ExpenseItem) error {
	return r.db.WithContext(ctx).Create(item).Error
}

func (r *ExpenseRepositoryImpl) DeleteItemsByExpenseID(ctx context.Context, expenseID uuid.UUID) error {
	return r.db.WithContext(ctx).Delete(&entities.ExpenseItem{}, "expense_id = ?", expenseID).Error
}