boc/backend/repository/finance.go

package repository

import (
	"context"
	"database/sql"
	"time"
)

// Invoice represents a financial invoice
type Invoice struct {
	ID         string    `json:"id"`
	CustomerID string    `json:"customer_id"`
	Amount     float64   `json:"amount"`
	Currency   string    `json:"currency"`
	Status     string    `json:"status"`
	DueDate    *time.Time `json:"due_date"`
	PaidAt     *time.Time `json:"paid_at"`
	CreatedAt  time.Time `json:"created_at"`
}

// Expense represents a business expense
type Expense struct {
	ID          string    `json:"id"`
	Category    string    `json:"category"`
	Description string    `json:"description"`
	Amount      float64   `json:"amount"`
	Currency    string    `json:"currency"`
	Date        time.Time `json:"date"`
	Status      string    `json:"status"`
	CreatedAt   time.Time `json:"created_at"`
}

// Account represents a ledger account
type Account struct {
	Code    string  `json:"code"`
	Name    string  `json:"name"`
	Type    string  `json:"type"`
	Balance float64 `json:"balance"`
}

// FinanceRepository handles all finance-related database operations
type FinanceRepository struct {
	db *sql.DB
}

func NewFinanceRepository(db *sql.DB) *FinanceRepository {
	return &FinanceRepository{db: db}
}

// ListInvoices returns all invoices
func (r *FinanceRepository) ListInvoices(ctx context.Context) ([]Invoice, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id, customer_id, amount, currency, status, due_date, paid_at, created_at
		FROM boc_invoices
		ORDER BY created_at DESC
		LIMIT 100
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var invoices []Invoice
	for rows.Next() {
		var i Invoice
		if err := rows.Scan(&i.ID, &i.CustomerID, &i.Amount, &i.Currency, &i.Status, &i.DueDate, &i.PaidAt, &i.CreatedAt); err != nil {
			continue
		}
		invoices = append(invoices, i)
	}
	return invoices, rows.Err()
}

// ListExpenses returns all expenses
func (r *FinanceRepository) ListExpenses(ctx context.Context) ([]Expense, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id, category, description, amount, currency, date, status, created_at
		FROM boc_expenses
		ORDER BY date DESC
		LIMIT 100
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var expenses []Expense
	for rows.Next() {
		var e Expense
		if err := rows.Scan(&e.ID, &e.Category, &e.Description, &e.Amount, &e.Currency, &e.Date, &e.Status, &e.CreatedAt); err != nil {
			continue
		}
		expenses = append(expenses, e)
	}
	return expenses, rows.Err()
}

// CreateExpense creates a new expense
func (r *FinanceRepository) CreateExpense(ctx context.Context, e *Expense) error {
	return r.db.QueryRowContext(ctx, `
		INSERT INTO boc_expenses (category, description, amount, currency, date, status)
		VALUES ($1, $2, $3, $4, $5, $6)
		RETURNING id, created_at
	`, e.Category, e.Description, e.Amount, e.Currency, e.Date, e.Status).Scan(&e.ID, &e.CreatedAt)
}

// GetCashFlow returns income, outstanding, and expenses for the last month
func (r *FinanceRepository) GetCashFlow(ctx context.Context) (income, outstanding, expenses float64, err error) {
	err = r.db.QueryRowContext(ctx, `
		SELECT COALESCE(SUM(amount), 0)
		FROM boc_invoices
		WHERE status = 'paid' AND paid_at >= NOW() - INTERVAL '1 month'
	`).Scan(&income)
	if err != nil {
		return 0, 0, 0, err
	}

	err = r.db.QueryRowContext(ctx, `
		SELECT COALESCE(SUM(amount), 0)
		FROM boc_invoices
		WHERE status = 'sent'
	`).Scan(&outstanding)
	if err != nil {
		return 0, 0, 0, err
	}

	err = r.db.QueryRowContext(ctx, `
		SELECT COALESCE(SUM(amount), 0)
		FROM boc_expenses
		WHERE status = 'approved' AND created_at >= NOW() - INTERVAL '1 month'
	`).Scan(&expenses)
	if err != nil {
		return 0, 0, 0, err
	}

	return income, outstanding, expenses, nil
}

// GetAccounts returns all ledger accounts
func (r *FinanceRepository) GetAccounts(ctx context.Context) ([]Account, error) {
	// This should ideally query aamos-ledger, but for now return from local cache
	rows, err := r.db.QueryContext(ctx, `
		SELECT code, name, type, COALESCE(balance, 0)
		FROM boc_accounts
		ORDER BY code
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var accounts []Account
	for rows.Next() {
		var a Account
		if err := rows.Scan(&a.Code, &a.Name, &a.Type, &a.Balance); err != nil {
			continue
		}
		accounts = append(accounts, a)
	}
	return accounts, rows.Err()
}