ERP/internal/models/invoice.go

package models

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

type Invoice struct {
	ID            int
	OrderID       sql.NullInt64
	CustomerID    int
	CustomerName  string
	InvoiceNumber string
	Status        string
	Amount        float64
	DueDate       string
	PaidDate      sql.NullString
	CreatedAt     time.Time
}

func InvoiceGetAll(db *sql.DB, status string) ([]Invoice, error) {
	query := `SELECT i.id, i.order_id, i.customer_id, c.name, i.invoice_number, i.status, i.amount, i.due_date, i.paid_date, i.created_at
		FROM invoices i JOIN customers c ON i.customer_id = c.id`
	args := []interface{}{}
	if status != "" {
		query += " WHERE i.status = ?"
		args = append(args, status)
	}
	query += " ORDER BY i.created_at DESC"

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

	var invoices []Invoice
	for rows.Next() {
		var inv Invoice
		if err := rows.Scan(&inv.ID, &inv.OrderID, &inv.CustomerID, &inv.CustomerName, &inv.InvoiceNumber, &inv.Status, &inv.Amount, &inv.DueDate, &inv.PaidDate, &inv.CreatedAt); err != nil {
			return nil, err
		}
		invoices = append(invoices, inv)
	}
	return invoices, nil
}

func InvoiceGetPaginated(db *sql.DB, status string, page, limit int) ([]Invoice, int, error) {
	offset := (page - 1) * limit

	// Base queries
	query := `SELECT i.id, i.order_id, i.customer_id, c.name, i.invoice_number, i.status, i.amount, i.due_date, i.paid_date, i.created_at
		FROM invoices i JOIN customers c ON i.customer_id = c.id`
	countQuery := "SELECT COUNT(*) FROM invoices i"

	args := []interface{}{}
	if status != "" {
		where := " WHERE i.status = ?"
		query += where
		countQuery += where
		args = append(args, status)
	}

	// Get total count
	var total int
	if err := db.QueryRow(countQuery, args...).Scan(&total); err != nil {
		return nil, 0, err
	}

	query += " ORDER BY i.created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var invoices []Invoice
	for rows.Next() {
		var inv Invoice
		if err := rows.Scan(&inv.ID, &inv.OrderID, &inv.CustomerID, &inv.CustomerName, &inv.InvoiceNumber, &inv.Status, &inv.Amount, &inv.DueDate, &inv.PaidDate, &inv.CreatedAt); err != nil {
			return nil, 0, err
		}
		invoices = append(invoices, inv)
	}
	return invoices, total, nil
}

func InvoiceGetByID(db *sql.DB, id int) (*Invoice, error) {
	inv := &Invoice{}
	err := db.QueryRow(
		`SELECT i.id, i.order_id, i.customer_id, c.name, i.invoice_number, i.status, i.amount, i.due_date, i.paid_date, i.created_at
		FROM invoices i JOIN customers c ON i.customer_id = c.id WHERE i.id = ?`, id,
	).Scan(&inv.ID, &inv.OrderID, &inv.CustomerID, &inv.CustomerName, &inv.InvoiceNumber, &inv.Status, &inv.Amount, &inv.DueDate, &inv.PaidDate, &inv.CreatedAt)
	if err != nil {
		return nil, err
	}
	return inv, nil
}

func InvoiceMarkPaid(db *sql.DB, id int) error {
	inv, err := InvoiceGetByID(db, id)
	if err != nil {
		return err
	}

	// Update invoice status
	_, err = db.Exec("UPDATE invoices SET status = 'paid', paid_date = date('now') WHERE id = ?", id)
	if err != nil {
		return err
	}

	// Create GL journal entry: Debit Cash, Credit AR
	result, err := db.Exec(
		"INSERT INTO journal_entries (entry_date, description, reference) VALUES (date('now'), ?, ?)",
		fmt.Sprintf("Payment received for %s", inv.InvoiceNumber),
		inv.InvoiceNumber,
	)
	if err != nil {
		return err
	}
	jeID, _ := result.LastInsertId()

	// Debit Cash (1000)
	var cashID int
	db.QueryRow("SELECT id FROM gl_accounts WHERE code = '1000'").Scan(&cashID)
	db.Exec("INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit) VALUES (?, ?, ?, 0)", jeID, cashID, inv.Amount)

	// Credit AR (1100)
	var arID int
	db.QueryRow("SELECT id FROM gl_accounts WHERE code = '1100'").Scan(&arID)
	db.Exec("INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit) VALUES (?, ?, 0, ?)", jeID, arID, inv.Amount)

	// Update account balances
	db.Exec("UPDATE gl_accounts SET balance = balance + ? WHERE code = '1000'", inv.Amount) // Cash increases
	db.Exec("UPDATE gl_accounts SET balance = balance - ? WHERE code = '1100'", inv.Amount) // AR decreases

	return nil
}

func InvoiceCountByStatus(db *sql.DB, status string) int {
	var count int
	db.QueryRow("SELECT COUNT(*) FROM invoices WHERE status = ?", status).Scan(&count)
	return count
}

func InvoiceTotalOutstanding(db *sql.DB) float64 {
	var total float64
	db.QueryRow("SELECT COALESCE(SUM(amount), 0) FROM invoices WHERE status = 'pending'").Scan(&total)
	return total
}

func InvoiceGetByCustomerID(db *sql.DB, customerID int) ([]Invoice, error) {
	rows, err := db.Query(
		`SELECT i.id, i.order_id, i.customer_id, c.name, i.invoice_number, i.status, i.amount, i.due_date, i.paid_date, i.created_at
		FROM invoices i JOIN customers c ON i.customer_id = c.id WHERE i.customer_id = ? ORDER BY i.created_at DESC`, customerID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var invoices []Invoice
	for rows.Next() {
		var inv Invoice
		if err := rows.Scan(&inv.ID, &inv.OrderID, &inv.CustomerID, &inv.CustomerName, &inv.InvoiceNumber, &inv.Status, &inv.Amount, &inv.DueDate, &inv.PaidDate, &inv.CreatedAt); err != nil {
			return nil, err
		}
		invoices = append(invoices, inv)
	}
	return invoices, nil
}