ERP/internal/models/ledger.go

package models

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

type GLAccount struct {
	ID        int
	Code      string
	Name      string
	Type      string
	Balance   float64
	CreatedAt time.Time
}

type JournalEntry struct {
	ID          int
	EntryDate   string
	Description string
	Reference   string
	CreatedAt   time.Time
	Lines       []JournalLine
	TotalDebit  float64
	TotalCredit float64
}

type JournalLine struct {
	ID             int
	JournalEntryID int
	AccountID      int
	AccountCode    string // joined
	AccountName    string // joined
	Debit          float64
	Credit         float64
}

type TrialBalanceRow struct {
	Code   string
	Name   string
	Type   string
	Debit  float64
	Credit float64
}

func GLAccountGetAll(db *sql.DB) ([]GLAccount, error) {
	rows, err := db.Query("SELECT id, code, name, type, balance, created_at FROM gl_accounts ORDER BY code")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

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

func GLAccountGetByID(db *sql.DB, id int) (*GLAccount, error) {
	a := &GLAccount{}
	err := db.QueryRow("SELECT id, code, name, type, balance, created_at FROM gl_accounts WHERE id = ?", id).
		Scan(&a.ID, &a.Code, &a.Name, &a.Type, &a.Balance, &a.CreatedAt)
	if err != nil {
		return nil, err
	}
	return a, nil
}

func JournalEntryGetAll(db *sql.DB) ([]JournalEntry, error) {
	rows, err := db.Query(`
		SELECT 
			je.id, 
			je.entry_date, 
			je.description, 
			je.reference, 
			je.created_at,
			COALESCE(SUM(jl.debit), 0),
			COALESCE(SUM(jl.credit), 0)
		FROM journal_entries je
		LEFT JOIN journal_lines jl ON je.id = jl.journal_entry_id
		GROUP BY je.id
		ORDER BY je.created_at DESC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var entries []JournalEntry
	for rows.Next() {
		var je JournalEntry
		if err := rows.Scan(&je.ID, &je.EntryDate, &je.Description, &je.Reference, &je.CreatedAt, &je.TotalDebit, &je.TotalCredit); err != nil {
			return nil, err
		}
		entries = append(entries, je)
	}
	return entries, nil
}

func JournalEntryGetPaginated(db *sql.DB, page, limit int) ([]JournalEntry, int, error) {
	offset := (page - 1) * limit

	var total int
	if err := db.QueryRow("SELECT COUNT(*) FROM journal_entries").Scan(&total); err != nil {
		return nil, 0, err
	}

	query := `
		SELECT 
			je.id, 
			je.entry_date, 
			je.description, 
			je.reference, 
			je.created_at,
			COALESCE(SUM(jl.debit), 0),
			COALESCE(SUM(jl.credit), 0)
		FROM journal_entries je
		LEFT JOIN journal_lines jl ON je.id = jl.journal_entry_id
		GROUP BY je.id
		ORDER BY je.created_at DESC
		LIMIT ? OFFSET ?
	`

	rows, err := db.Query(query, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var entries []JournalEntry
	for rows.Next() {
		var je JournalEntry
		if err := rows.Scan(&je.ID, &je.EntryDate, &je.Description, &je.Reference, &je.CreatedAt, &je.TotalDebit, &je.TotalCredit); err != nil {
			return nil, 0, err
		}
		entries = append(entries, je)
	}
	return entries, total, nil
}

func JournalEntryGetByID(db *sql.DB, id int) (*JournalEntry, error) {
	je := &JournalEntry{}
	err := db.QueryRow("SELECT id, entry_date, description, reference, created_at FROM journal_entries WHERE id = ?", id).
		Scan(&je.ID, &je.EntryDate, &je.Description, &je.Reference, &je.CreatedAt)
	if err != nil {
		return nil, err
	}

	// Load lines
	rows, err := db.Query(
		`SELECT jl.id, jl.journal_entry_id, jl.account_id, a.code, a.name, jl.debit, jl.credit
		FROM journal_lines jl JOIN gl_accounts a ON jl.account_id = a.id
		WHERE jl.journal_entry_id = ? ORDER BY jl.id`, id,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var l JournalLine
		if err := rows.Scan(&l.ID, &l.JournalEntryID, &l.AccountID, &l.AccountCode, &l.AccountName, &l.Debit, &l.Credit); err != nil {
			return nil, err
		}
		je.TotalDebit += l.Debit
		je.TotalCredit += l.Credit
		je.Lines = append(je.Lines, l)
	}

	return je, nil
}

// JournalEntryInsert creates a journal entry with its lines. Validates debits == credits.
func JournalEntryInsert(db *sql.DB, je *JournalEntry) error {
	var totalDebit, totalCredit float64
	for _, l := range je.Lines {
		totalDebit += l.Debit
		totalCredit += l.Credit
	}

	// Allow small floating point differences
	diff := totalDebit - totalCredit
	if diff > 0.01 || diff < -0.01 {
		return fmt.Errorf("debits (%.2f) must equal credits (%.2f)", totalDebit, totalCredit)
	}

	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	result, err := tx.Exec(
		"INSERT INTO journal_entries (entry_date, description, reference) VALUES (?, ?, ?)",
		je.EntryDate, je.Description, je.Reference,
	)
	if err != nil {
		return err
	}
	jeID, _ := result.LastInsertId()
	je.ID = int(jeID)

	for _, l := range je.Lines {
		_, err := tx.Exec(
			"INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit) VALUES (?, ?, ?, ?)",
			jeID, l.AccountID, l.Debit, l.Credit,
		)
		if err != nil {
			return err
		}

		// Update account balance
		// For assets/expenses: balance increases with debit, decreases with credit
		// For liabilities/equity/revenue: balance increases with credit, decreases with debit
		netEffect := l.Debit - l.Credit
		_, err = tx.Exec("UPDATE gl_accounts SET balance = balance + ? WHERE id = ?", netEffect, l.AccountID)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

func GetTrialBalance(db *sql.DB) ([]TrialBalanceRow, error) {
	rows, err := db.Query(`
		SELECT a.code, a.name, a.type,
			COALESCE(SUM(jl.debit), 0) as total_debit,
			COALESCE(SUM(jl.credit), 0) as total_credit
		FROM gl_accounts a
		LEFT JOIN journal_lines jl ON a.id = jl.account_id
		GROUP BY a.id, a.code, a.name, a.type
		ORDER BY a.code
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []TrialBalanceRow
	for rows.Next() {
		var r TrialBalanceRow
		if err := rows.Scan(&r.Code, &r.Name, &r.Type, &r.Debit, &r.Credit); err != nil {
			return nil, err
		}
		result = append(result, r)
	}
	return result, nil
}

func RevenueThisMonth(db *sql.DB) float64 {
	var total float64
	db.QueryRow(`
		SELECT COALESCE(SUM(jl.credit), 0)
		FROM journal_lines jl
		JOIN gl_accounts a ON jl.account_id = a.id
		JOIN journal_entries je ON jl.journal_entry_id = je.id
		WHERE a.type = 'revenue'
		AND je.entry_date >= date('now', 'start of month')
	`).Scan(&total)
	return total
}