package repository

import (
	"database/sql"
	"errors"
	"fmt"
	"time"

	"github.com/jmoiron/sqlx"

	"github.com/daniil/homelab-api/internal/model"
)

var (
	ErrCategoryNotFound    = errors.New("category not found")
	ErrTransactionNotFound = errors.New("transaction not found")
	ErrNotAuthorized       = errors.New("not authorized")
)

type SavingsRepository struct {
	db *sqlx.DB
}

func NewSavingsRepository(db *sqlx.DB) *SavingsRepository {
	return &SavingsRepository{db: db}
}

// ==================== CATEGORIES ====================

func (r *SavingsRepository) ListCategories(userID int64) ([]model.SavingsCategory, error) {
	query := `
		SELECT DISTINCT c.* FROM savings_categories c
		LEFT JOIN savings_category_members m ON c.id = m.category_id
		WHERE c.user_id = $1 OR m.user_id = $1
		ORDER BY c.created_at DESC
	`

	var categories []model.SavingsCategory
	err := r.db.Select(&categories, query, userID)
	if err != nil {
		return nil, err
	}

	// Load members for each category
	for i := range categories {
		members, err := r.GetCategoryMembers(categories[i].ID)
		if err == nil {
			categories[i].Members = members
		}
	}

	return categories, nil
}

func (r *SavingsRepository) GetCategory(id, userID int64) (*model.SavingsCategory, error) {
	query := `
		SELECT DISTINCT c.* FROM savings_categories c
		LEFT JOIN savings_category_members m ON c.id = m.category_id
		WHERE c.id = $1 AND (c.user_id = $2 OR m.user_id = $2)
	`

	var category model.SavingsCategory
	err := r.db.Get(&category, query, id, userID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrCategoryNotFound
		}
		return nil, err
	}

	// Load members
	members, err := r.GetCategoryMembers(id)
	if err == nil {
		category.Members = members
	}

	return &category, nil
}

func (r *SavingsRepository) CreateCategory(userID int64, req *model.CreateSavingsCategoryRequest) (*model.SavingsCategory, error) {
	query := `
		INSERT INTO savings_categories (
			user_id, name, description, is_deposit, is_credit, is_account, is_recurring, is_multi,
			initial_capital, deposit_amount, interest_rate, deposit_start_date, deposit_term,
			credit_amount, credit_term, credit_rate, credit_start_date,
			recurring_amount, recurring_day, recurring_start_date
		) VALUES (
			$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20
		) RETURNING id, created_at, updated_at
	`

	var depositStartDate, creditStartDate, recurringStartDate *time.Time
	if req.DepositStartDate != nil {
		t, _ := time.Parse("2006-01-02", *req.DepositStartDate)
		depositStartDate = &t
	}
	if req.CreditStartDate != nil {
		t, _ := time.Parse("2006-01-02", *req.CreditStartDate)
		creditStartDate = &t
	}
	if req.RecurringStartDate != nil {
		t, _ := time.Parse("2006-01-02", *req.RecurringStartDate)
		recurringStartDate = &t
	}

	category := &model.SavingsCategory{
		UserID:          userID,
		Name:            req.Name,
		Description:     req.Description,
		IsDeposit:       req.IsDeposit,
		IsCredit:        req.IsCredit,
		IsAccount:       req.IsAccount,
		IsRecurring:     req.IsRecurring,
		IsMulti:         req.IsMulti,
		InitialCapital:  req.InitialCapital,
		DepositAmount:   req.DepositAmount,
		InterestRate:    req.InterestRate,
		DepositTerm:     req.DepositTerm,
		CreditAmount:    req.CreditAmount,
		CreditTerm:      req.CreditTerm,
		CreditRate:      req.CreditRate,
		RecurringAmount: req.RecurringAmount,
		RecurringDay:    req.RecurringDay,
	}

	err := r.db.QueryRow(
		query,
		userID, req.Name, req.Description, req.IsDeposit, req.IsCredit, req.IsAccount, req.IsRecurring, req.IsMulti,
		req.InitialCapital, req.DepositAmount, req.InterestRate, depositStartDate, req.DepositTerm,
		req.CreditAmount, req.CreditTerm, req.CreditRate, creditStartDate,
		req.RecurringAmount, req.RecurringDay, recurringStartDate,
	).Scan(&category.ID, &category.CreatedAt, &category.UpdatedAt)

	if err != nil {
		return nil, err
	}

	// Add creator as member if multi
	if req.IsMulti {
		r.AddCategoryMember(category.ID, userID)
		for _, memberID := range req.MemberIDs {
			if memberID != userID {
				r.AddCategoryMember(category.ID, memberID)
			}
		}
	}

	return r.GetCategory(category.ID, userID)
}

func (r *SavingsRepository) UpdateCategory(id, userID int64, req *model.UpdateSavingsCategoryRequest) (*model.SavingsCategory, error) {
	// Check ownership
	var ownerID int64
	err := r.db.Get(&ownerID, "SELECT user_id FROM savings_categories WHERE id = $1", id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrCategoryNotFound
		}
		return nil, err
	}
	if ownerID != userID {
		return nil, ErrNotAuthorized
	}

	// Build dynamic update query
	updates := []string{}
	args := []interface{}{}
	argNum := 1

	if req.Name != nil {
		updates = append(updates, fmt.Sprintf("name = $%d", argNum))
		args = append(args, *req.Name)
		argNum++
	}
	if req.Description != nil {
		updates = append(updates, fmt.Sprintf("description = $%d", argNum))
		args = append(args, *req.Description)
		argNum++
	}
	if req.IsDeposit != nil {
		updates = append(updates, fmt.Sprintf("is_deposit = $%d", argNum))
		args = append(args, *req.IsDeposit)
		argNum++
	}
	if req.IsCredit != nil {
		updates = append(updates, fmt.Sprintf("is_credit = $%d", argNum))
		args = append(args, *req.IsCredit)
		argNum++
	}
	if req.IsAccount != nil {
		updates = append(updates, fmt.Sprintf("is_account = $%d", argNum))
		args = append(args, *req.IsAccount)
		argNum++
	}
	if req.IsRecurring != nil {
		updates = append(updates, fmt.Sprintf("is_recurring = $%d", argNum))
		args = append(args, *req.IsRecurring)
		argNum++
	}
	if req.IsMulti != nil {
		updates = append(updates, fmt.Sprintf("is_multi = $%d", argNum))
		args = append(args, *req.IsMulti)
		argNum++
	}
	if req.IsClosed != nil {
		updates = append(updates, fmt.Sprintf("is_closed = $%d", argNum))
		args = append(args, *req.IsClosed)
		argNum++
	}
	if req.InitialCapital != nil {
		updates = append(updates, fmt.Sprintf("initial_capital = $%d", argNum))
		args = append(args, *req.InitialCapital)
		argNum++
	}
	if req.DepositAmount != nil {
		updates = append(updates, fmt.Sprintf("deposit_amount = $%d", argNum))
		args = append(args, *req.DepositAmount)
		argNum++
	}
	if req.InterestRate != nil {
		updates = append(updates, fmt.Sprintf("interest_rate = $%d", argNum))
		args = append(args, *req.InterestRate)
		argNum++
	}
	if req.DepositStartDate != nil {
		t, _ := time.Parse("2006-01-02", *req.DepositStartDate)
		updates = append(updates, fmt.Sprintf("deposit_start_date = $%d", argNum))
		args = append(args, t)
		argNum++
	}
	if req.DepositTerm != nil {
		updates = append(updates, fmt.Sprintf("deposit_term = $%d", argNum))
		args = append(args, *req.DepositTerm)
		argNum++
	}
	if req.FinalAmount != nil {
		updates = append(updates, fmt.Sprintf("final_amount = $%d", argNum))
		args = append(args, *req.FinalAmount)
		argNum++
	}
	if req.RecurringAmount != nil {
		updates = append(updates, fmt.Sprintf("recurring_amount = $%d", argNum))
		args = append(args, *req.RecurringAmount)
		argNum++
	}
	if req.RecurringDay != nil {
		updates = append(updates, fmt.Sprintf("recurring_day = $%d", argNum))
		args = append(args, *req.RecurringDay)
		argNum++
	}

	if len(updates) == 0 {
		return r.GetCategory(id, userID)
	}

	updates = append(updates, "updated_at = NOW()")
	args = append(args, id)

	query := fmt.Sprintf("UPDATE savings_categories SET %s WHERE id = $%d",
		joinStrings(updates, ", "), argNum)

	_, err = r.db.Exec(query, args...)
	if err != nil {
		return nil, err
	}

	return r.GetCategory(id, userID)
}

func joinStrings(s []string, sep string) string {
	if len(s) == 0 {
		return ""
	}
	result := s[0]
	for i := 1; i < len(s); i++ {
		result += sep + s[i]
	}
	return result
}

func (r *SavingsRepository) DeleteCategory(id, userID int64) error {
	result, err := r.db.Exec("DELETE FROM savings_categories WHERE id = $1 AND user_id = $2", id, userID)
	if err != nil {
		return err
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrCategoryNotFound
	}

	return nil
}

// ==================== TRANSACTIONS ====================

func (r *SavingsRepository) ListTransactions(userID int64, categoryID *int64, limit, offset int) ([]model.SavingsTransaction, error) {
	query := `
		SELECT DISTINCT ON (t.id) t.*, c.name as category_name, u.username as user_name
		FROM savings_transactions t
		JOIN savings_categories c ON t.category_id = c.id
		JOIN users u ON t.user_id = u.id
		LEFT JOIN savings_category_members m ON c.id = m.category_id
		WHERE (c.user_id = $1 OR m.user_id = $1)
	`
	args := []interface{}{userID}
	argNum := 2

	if categoryID != nil {
		query += fmt.Sprintf(" AND t.category_id = $%d", argNum)
		args = append(args, *categoryID)
		argNum++
	}

	query += " ORDER BY t.id, t.date DESC"

	// Wrap for proper ordering
	query = fmt.Sprintf("SELECT * FROM (%s) sub ORDER BY date DESC, id DESC", query)

	if limit > 0 {
		query += fmt.Sprintf(" LIMIT $%d", argNum)
		args = append(args, limit)
		argNum++
	}
	if offset > 0 {
		query += fmt.Sprintf(" OFFSET $%d", argNum)
		args = append(args, offset)
	}

	var transactions []model.SavingsTransaction
	err := r.db.Select(&transactions, query, args...)
	return transactions, err
}

func (r *SavingsRepository) GetTransaction(id, userID int64) (*model.SavingsTransaction, error) {
	query := `
		SELECT t.*, c.name as category_name, u.username as user_name
		FROM savings_transactions t
		JOIN savings_categories c ON t.category_id = c.id
		JOIN users u ON t.user_id = u.id
		LEFT JOIN savings_category_members m ON c.id = m.category_id
		WHERE t.id = $1 AND (c.user_id = $2 OR m.user_id = $2 OR t.user_id = $2)
	`

	var transaction model.SavingsTransaction
	err := r.db.Get(&transaction, query, id, userID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrTransactionNotFound
		}
		return nil, err
	}
	return &transaction, nil
}

func (r *SavingsRepository) CreateTransaction(userID int64, req *model.CreateSavingsTransactionRequest) (*model.SavingsTransaction, error) {
	// Verify user has access to category
	query := `
		SELECT 1 FROM savings_categories c
		LEFT JOIN savings_category_members m ON c.id = m.category_id
		WHERE c.id = $1 AND (c.user_id = $2 OR m.user_id = $2)
	`
	var exists int
	err := r.db.Get(&exists, query, req.CategoryID, userID)
	if err != nil {
		return nil, ErrCategoryNotFound
	}

	date, err := time.Parse("2006-01-02", req.Date)
	if err != nil {
		return nil, fmt.Errorf("invalid date format")
	}

	insertQuery := `
		INSERT INTO savings_transactions (category_id, user_id, amount, type, description, date)
		VALUES ($1, $2, $3, $4, $5, $6)
		RETURNING id, created_at, updated_at
	`

	tx := &model.SavingsTransaction{
		CategoryID:  req.CategoryID,
		UserID:      userID,
		Amount:      req.Amount,
		Type:        req.Type,
		Description: req.Description,
		Date:        date,
	}

	err = r.db.QueryRow(insertQuery, req.CategoryID, userID, req.Amount, req.Type, req.Description, date).
		Scan(&tx.ID, &tx.CreatedAt, &tx.UpdatedAt)
	if err != nil {
		return nil, err
	}

	return r.GetTransaction(tx.ID, userID)
}

func (r *SavingsRepository) UpdateTransaction(id, userID int64, req *model.UpdateSavingsTransactionRequest) (*model.SavingsTransaction, error) {
	// Verify ownership
	var txUserID int64
	err := r.db.Get(&txUserID, "SELECT user_id FROM savings_transactions WHERE id = $1", id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrTransactionNotFound
		}
		return nil, err
	}
	if txUserID != userID {
		return nil, ErrNotAuthorized
	}

	updates := []string{}
	args := []interface{}{}
	argNum := 1

	if req.Amount != nil {
		updates = append(updates, fmt.Sprintf("amount = $%d", argNum))
		args = append(args, *req.Amount)
		argNum++
	}
	if req.Type != nil {
		updates = append(updates, fmt.Sprintf("type = $%d", argNum))
		args = append(args, *req.Type)
		argNum++
	}
	if req.Description != nil {
		updates = append(updates, fmt.Sprintf("description = $%d", argNum))
		args = append(args, *req.Description)
		argNum++
	}
	if req.Date != nil {
		date, _ := time.Parse("2006-01-02", *req.Date)
		updates = append(updates, fmt.Sprintf("date = $%d", argNum))
		args = append(args, date)
		argNum++
	}

	if len(updates) == 0 {
		return r.GetTransaction(id, userID)
	}

	updates = append(updates, "updated_at = NOW()")
	args = append(args, id)

	query := fmt.Sprintf("UPDATE savings_transactions SET %s WHERE id = $%d",
		joinStrings(updates, ", "), argNum)

	_, err = r.db.Exec(query, args...)
	if err != nil {
		return nil, err
	}

	return r.GetTransaction(id, userID)
}

func (r *SavingsRepository) DeleteTransaction(id, userID int64) error {
	result, err := r.db.Exec("DELETE FROM savings_transactions WHERE id = $1 AND user_id = $2", id, userID)
	if err != nil {
		return err
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrTransactionNotFound
	}

	return nil
}

// ==================== CATEGORY MEMBERS ====================

func (r *SavingsRepository) GetCategoryMembers(categoryID int64) ([]model.SavingsCategoryMember, error) {
	query := `
		SELECT m.*, u.username as user_name
		FROM savings_category_members m
		JOIN users u ON m.user_id = u.id
		WHERE m.category_id = $1
	`
	var members []model.SavingsCategoryMember
	err := r.db.Select(&members, query, categoryID)
	return members, err
}

func (r *SavingsRepository) AddCategoryMember(categoryID, userID int64) error {
	_, err := r.db.Exec(`
		INSERT INTO savings_category_members (category_id, user_id)
		VALUES ($1, $2)
		ON CONFLICT (category_id, user_id) DO NOTHING
	`, categoryID, userID)
	return err
}

func (r *SavingsRepository) RemoveCategoryMember(categoryID, userID int64) error {
	_, err := r.db.Exec("DELETE FROM savings_category_members WHERE category_id = $1 AND user_id = $2", categoryID, userID)
	return err
}

// ==================== RECURRING PLANS ====================

func (r *SavingsRepository) ListRecurringPlans(categoryID int64) ([]model.SavingsRecurringPlan, error) {
	query := "SELECT * FROM savings_recurring_plans WHERE category_id = $1 ORDER BY effective DESC"
	var plans []model.SavingsRecurringPlan
	err := r.db.Select(&plans, query, categoryID)
	if err != nil {
		return nil, err
	}
	for i := range plans {
		plans[i].ProcessForJSON()
	}
	return plans, nil
}

func (r *SavingsRepository) CreateRecurringPlan(categoryID int64, req *model.CreateRecurringPlanRequest) (*model.SavingsRecurringPlan, error) {
	effective, err := time.Parse("2006-01-02", req.Effective)
	if err != nil {
		return nil, fmt.Errorf("invalid effective date")
	}

	day := req.Day
	if day == 0 {
		day = 1
	}

	query := `
		INSERT INTO savings_recurring_plans (category_id, user_id, effective, amount, day)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING id, created_at, updated_at
	`

	plan := &model.SavingsRecurringPlan{
		CategoryID: categoryID,
		Effective:  effective,
		Amount:     req.Amount,
		Day:        day,
	}

	err = r.db.QueryRow(query, categoryID, req.UserID, effective, req.Amount, day).
		Scan(&plan.ID, &plan.CreatedAt, &plan.UpdatedAt)
	if err != nil {
		return nil, err
	}

	if req.UserID != nil {
		plan.UserID = sql.NullInt64{Int64: *req.UserID, Valid: true}
	}
	plan.ProcessForJSON()

	return plan, nil
}

func (r *SavingsRepository) DeleteRecurringPlan(id int64) error {
	_, err := r.db.Exec("DELETE FROM savings_recurring_plans WHERE id = $1", id)
	return err
}

func (r *SavingsRepository) UpdateRecurringPlan(id int64, req *model.UpdateRecurringPlanRequest) (*model.SavingsRecurringPlan, error) {
	updates := []string{}
	args := []interface{}{}
	argNum := 1

	if req.Effective != nil {
		effective, err := time.Parse("2006-01-02", *req.Effective)
		if err != nil {
			return nil, fmt.Errorf("invalid effective date")
		}
		updates = append(updates, fmt.Sprintf("effective = $%d", argNum))
		args = append(args, effective)
		argNum++
	}
	if req.Amount != nil {
		updates = append(updates, fmt.Sprintf("amount = $%d", argNum))
		args = append(args, *req.Amount)
		argNum++
	}
	if req.Day != nil {
		updates = append(updates, fmt.Sprintf("day = $%d", argNum))
		args = append(args, *req.Day)
		argNum++
	}

	if len(updates) == 0 {
		return r.GetRecurringPlan(id)
	}

	updates = append(updates, "updated_at = NOW()")
	args = append(args, id)

	query := fmt.Sprintf("UPDATE savings_recurring_plans SET %s WHERE id = $%d",
		joinStrings(updates, ", "), argNum)

	_, err := r.db.Exec(query, args...)
	if err != nil {
		return nil, err
	}

	return r.GetRecurringPlan(id)
}

func (r *SavingsRepository) GetRecurringPlan(id int64) (*model.SavingsRecurringPlan, error) {
	var plan model.SavingsRecurringPlan
	err := r.db.Get(&plan, "SELECT * FROM savings_recurring_plans WHERE id = $1", id)
	if err != nil {
		return nil, err
	}
	plan.ProcessForJSON()
	return &plan, nil
}

// ==================== STATS ====================

func (r *SavingsRepository) GetCategoryBalance(categoryID int64) (float64, error) {
	var balance float64
	err := r.db.Get(&balance, `
		SELECT COALESCE(
			(SELECT initial_capital FROM savings_categories WHERE id = $1), 0
		) + COALESCE(
			(SELECT SUM(CASE WHEN type = 'deposit' THEN amount ELSE -amount END)
			 FROM savings_transactions WHERE category_id = $1), 0
		)
	`, categoryID)
	return balance, err
}

func (r *SavingsRepository) GetStats(userID int64) (*model.SavingsStats, error) {
	stats := &model.SavingsStats{
		ByCategory: []model.CategoryStats{},
	}

	// Get categories for user
	categories, err := r.ListCategories(userID)
	if err != nil {
		return nil, err
	}

	stats.CategoriesCount = len(categories)

	for _, cat := range categories {
		balance, _ := r.GetCategoryBalance(cat.ID)

		catStats := model.CategoryStats{
			CategoryID:   cat.ID,
			CategoryName: cat.Name,
			Balance:      balance,
			IsDeposit:    cat.IsDeposit,
			IsRecurring:  cat.IsRecurring,
		}
		stats.ByCategory = append(stats.ByCategory, catStats)
		stats.TotalBalance += balance
	}

	// Total deposits and withdrawals
	err = r.db.Get(&stats.TotalDeposits, `
		SELECT COALESCE(SUM(t.amount), 0)
		FROM savings_transactions t
		JOIN savings_categories c ON t.category_id = c.id
		WHERE t.user_id = $1 AND t.type = 'deposit' AND c.is_account = false
	`, userID)
	if err != nil {
		return nil, err
	}

	err = r.db.Get(&stats.TotalWithdrawals, `
		SELECT COALESCE(SUM(t.amount), 0)
		FROM savings_transactions t
		JOIN savings_categories c ON t.category_id = c.id
		WHERE t.user_id = $1 AND t.type = 'withdrawal' AND c.is_account = false
	`, userID)
	if err != nil {
		return nil, err
	}

	// Get monthly payments (only for current user, only unpaid)
	monthly, details, _ := r.GetCurrentMonthlyPayments(userID)
	stats.MonthlyPayments = monthly
	stats.MonthlyPaymentDetails = details

	// Get overdues (all past months)
	overdues, _ := r.GetOverdues(userID)
	stats.Overdues = overdues

	return stats, nil
}

// GetRecurringTotalAmount calculates the total recurring target for a category
func (r *SavingsRepository) GetRecurringTotalAmount(categoryID int64) (float64, error) {
	plans, err := r.ListRecurringPlans(categoryID)
	if err != nil {
		return 0, err
	}

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

	var total float64
	now := time.Now()

	for _, plan := range plans {
		monthsActive := 0
		checkDate := plan.Effective

		for i, nextPlan := range plans {
			if i == 0 {
				continue
			}
			endDate := nextPlan.Effective
			if i == len(plans)-1 {
				endDate = now
			}
			for checkDate.Before(endDate) {
				monthsActive++
				checkDate = checkDate.AddDate(0, 1, 0)
			}
			total += float64(monthsActive) * plan.Amount
			monthsActive = 0
		}
	}

	if len(plans) > 0 {
		lastPlan := plans[len(plans)-1]
		checkDate := lastPlan.Effective
		monthsActive := 0
		for checkDate.Before(now) {
			monthsActive++
			checkDate = checkDate.AddDate(0, 1, 0)
		}
		total += float64(monthsActive) * lastPlan.Amount
	}

	return total, nil
}

// GetCurrentMonthlyPayments returns pending recurring payments for current user this month
// GetCurrentMonthlyPayments returns pending recurring payments for current user this month
func (r *SavingsRepository) GetCurrentMonthlyPayments(userID int64) (float64, []model.MonthlyPaymentDetail, error) {
	now := time.Now()
	currentMonth := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)

	categories, err := r.ListCategories(userID)
	if err != nil {
		return 0, nil, err
	}

	var totalMonthly float64
	var details []model.MonthlyPaymentDetail

	for _, cat := range categories {
		if !cat.IsRecurring || cat.IsClosed {
			continue
		}

		// Get plans for current user
		var plans []struct {
			Effective time.Time `db:"effective"`
			Amount    float64   `db:"amount"`
			Day       int       `db:"day"`
		}
		err := r.db.Select(&plans, `
			SELECT effective, amount, day 
			FROM savings_recurring_plans 
			WHERE category_id = $1 AND user_id = $2
			ORDER BY effective ASC
		`, cat.ID, userID)
		if err != nil || len(plans) == 0 {
			continue
		}

		// Determine start month (same logic as old Savings)
		// Start from category.CreatedAt month
		start := time.Date(cat.CreatedAt.Year(), cat.CreatedAt.Month(), 1, 0, 0, 0, 0, time.UTC)
		
		// For multi categories, use earliest plan effective if earlier
		if cat.IsMulti && len(plans) > 0 {
			earliestEffective := time.Date(plans[0].Effective.Year(), plans[0].Effective.Month(), 1, 0, 0, 0, 0, time.UTC)
			if earliestEffective.Before(start) {
				start = earliestEffective
			}
		}

		// Build months list from start to current
		var months []time.Time
		for m := start; !m.After(currentMonth); m = m.AddDate(0, 1, 0) {
			months = append(months, m)
		}
		if len(months) == 0 {
			continue
		}

		// Calculate required amounts per month from plans
		required := make([]float64, len(months))
		days := make([]int, len(months))
		for i, ms := range months {
			for _, p := range plans {
				planMonth := time.Date(p.Effective.Year(), p.Effective.Month(), 1, 0, 0, 0, 0, time.UTC)
				if !planMonth.After(ms) {
					required[i] = p.Amount
					days[i] = p.Day
					if days[i] < 1 || days[i] > 28 {
						days[i] = 1
					}
				}
			}
		}

		// Remaining amounts
		remaining := make([]float64, len(required))
		copy(remaining, required)

		// Get deposits for this user and category since start
		var deposits []struct {
			Date   time.Time `db:"date"`
			Amount float64   `db:"amount"`
		}
		r.db.Select(&deposits, `
			SELECT date, amount FROM savings_transactions
			WHERE category_id = $1 AND user_id = $2 AND type = 'deposit' AND date >= $3
			ORDER BY date ASC
		`, cat.ID, userID, start)

		// Helper: find month index for date
		findIdx := func(d time.Time) int {
			ds := time.Date(d.Year(), d.Month(), 1, 0, 0, 0, 0, time.UTC)
			for i, ms := range months {
				if ms.Equal(ds) {
					return i
				}
			}
			if ds.Before(months[0]) {
				return -1
			}
			return len(months) - 1
		}

		// Allocate deposits: first to current month, then to older months (newest first)
		for _, dep := range deposits {
			amt := dep.Amount
			idx := findIdx(dep.Date)
			if idx == -1 {
				continue
			}

			// Pay current month first
			if remaining[idx] > 0 {
				use := amt
				if use > remaining[idx] {
					use = remaining[idx]
				}
				remaining[idx] -= use
				amt -= use
			}

			// Then pay older months (newest to oldest)
			for k := idx - 1; k >= 0 && amt > 0; k-- {
				if remaining[k] <= 0 {
					continue
				}
				use := amt
				if use > remaining[k] {
					use = remaining[k]
				}
				remaining[k] -= use
				amt -= use
			}
		}

		// Check current month (last in list)
		last := len(months) - 1
		if days[last] == 0 {
			days[last] = 1
		}
		dueDate := time.Date(months[last].Year(), months[last].Month(), days[last], 0, 0, 0, 0, time.UTC)

		// Only show if due date passed and not fully paid
		if !dueDate.After(now) && remaining[last] > 0.01 {
			details = append(details, model.MonthlyPaymentDetail{
				CategoryID:   cat.ID,
				CategoryName: cat.Name,
				Amount:       remaining[last],
				Day:          days[last],
			})
			totalMonthly += remaining[last]
		}
	}

	return totalMonthly, details, nil
}

// GetOverdues returns overdue payments using the same allocation algorithm as old Savings
func (r *SavingsRepository) GetOverdues(userID int64) ([]model.OverduePayment, error) {
	now := time.Now()
	currentMonth := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)

	categories, err := r.ListCategories(userID)
	if err != nil {
		return nil, err
	}

	var overdues []model.OverduePayment

	for _, cat := range categories {
		if !cat.IsRecurring || cat.IsClosed {
			continue
		}

		// Get plans for current user only
		var plans []struct {
			Effective time.Time `db:"effective"`
			Amount    float64   `db:"amount"`
			Day       int       `db:"day"`
		}
		err := r.db.Select(&plans, `
			SELECT effective, amount, day 
			FROM savings_recurring_plans 
			WHERE category_id = $1 AND user_id = $2
			ORDER BY effective ASC
		`, cat.ID, userID)
		if err != nil || len(plans) == 0 {
			continue
		}

		// Determine start month (same logic as old Savings)
		start := time.Date(cat.CreatedAt.Year(), cat.CreatedAt.Month(), 1, 0, 0, 0, 0, time.UTC)
		
		// For multi categories, use earliest plan effective if earlier
		if cat.IsMulti && len(plans) > 0 {
			earliestEffective := time.Date(plans[0].Effective.Year(), plans[0].Effective.Month(), 1, 0, 0, 0, 0, time.UTC)
			if earliestEffective.Before(start) {
				start = earliestEffective
			}
		}

		// Build months list
		var months []time.Time
		for m := start; !m.After(currentMonth); m = m.AddDate(0, 1, 0) {
			months = append(months, m)
		}
		if len(months) == 0 {
			continue
		}

		// Calculate required amounts per month
		required := make([]float64, len(months))
		days := make([]int, len(months))
		for i, ms := range months {
			for _, p := range plans {
				planMonth := time.Date(p.Effective.Year(), p.Effective.Month(), 1, 0, 0, 0, 0, time.UTC)
				if !planMonth.After(ms) {
					required[i] = p.Amount
					days[i] = p.Day
					if days[i] < 1 || days[i] > 28 {
						days[i] = 1
					}
				}
			}
		}

		// Remaining amounts
		remaining := make([]float64, len(required))
		copy(remaining, required)

		// Get deposits for this user and category since start
		var deposits []struct {
			Date   time.Time `db:"date"`
			Amount float64   `db:"amount"`
		}
		r.db.Select(&deposits, `
			SELECT date, amount FROM savings_transactions
			WHERE category_id = $1 AND user_id = $2 AND type = 'deposit' AND date >= $3
			ORDER BY date ASC
		`, cat.ID, userID, start)

		// Helper: find month index
		findIdx := func(d time.Time) int {
			ds := time.Date(d.Year(), d.Month(), 1, 0, 0, 0, 0, time.UTC)
			for i, ms := range months {
				if ms.Equal(ds) {
					return i
				}
			}
			if ds.Before(months[0]) {
				return -1
			}
			return len(months) - 1
		}

		// Allocate deposits
		for _, dep := range deposits {
			amt := dep.Amount
			idx := findIdx(dep.Date)
			if idx == -1 {
				continue
			}

			if remaining[idx] > 0 {
				use := amt
				if use > remaining[idx] {
					use = remaining[idx]
				}
				remaining[idx] -= use
				amt -= use
			}

			for k := idx - 1; k >= 0 && amt > 0; k-- {
				if remaining[k] <= 0 {
					continue
				}
				use := amt
				if use > remaining[k] {
					use = remaining[k]
				}
				remaining[k] -= use
				amt -= use
			}
		}

		// Get username
		var userName string
		r.db.Get(&userName, "SELECT username FROM users WHERE id = $1", userID)

		// All previous months (before current) with remaining balance are overdues
		last := len(months) - 1
		for i := 0; i < last; i++ {
			if remaining[i] > 0.01 {
				if days[i] == 0 {
					days[i] = 1
				}
				dueDate := time.Date(months[i].Year(), months[i].Month(), days[i], 0, 0, 0, 0, time.UTC)
				daysOverdue := int(now.Sub(dueDate).Hours() / 24)

				overdues = append(overdues, model.OverduePayment{
					CategoryID:   cat.ID,
					CategoryName: cat.Name,
					UserID:       userID,
					UserName:     userName,
					Amount:       remaining[i],
					DueDay:       days[i],
					DaysOverdue:  daysOverdue,
					Month:        months[i].Format("2006-01"),
				})
			}
		}
	}

	return overdues, nil
}