ERP/internal/models/order.go

package models

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

type Order struct {
	ID           int
	CustomerID   int
	CustomerName string // joined field
	Status       string
	OrderDate    string
	TotalAmount  float64
	Notes        string
	CreatedAt    time.Time
	UpdatedAt    time.Time
	Lines        []OrderLine
}

type OrderLine struct {
	ID          int
	OrderID     int
	Description string
	Quantity    float64
	UnitPrice   float64
	LineTotal   float64
}

func OrderGetAll(db *sql.DB, status string) ([]Order, error) {
	query := `SELECT o.id, o.customer_id, c.name, o.status, o.order_date, o.total_amount, o.notes, o.created_at, o.updated_at
		FROM orders o JOIN customers c ON o.customer_id = c.id`
	args := []interface{}{}
	if status != "" {
		query += " WHERE o.status = ?"
		args = append(args, status)
	}
	query += " ORDER BY o.created_at DESC"

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

	var orders []Order
	for rows.Next() {
		var o Order
		if err := rows.Scan(&o.ID, &o.CustomerID, &o.CustomerName, &o.Status, &o.OrderDate, &o.TotalAmount, &o.Notes, &o.CreatedAt, &o.UpdatedAt); err != nil {
			return nil, err
		}
		orders = append(orders, o)
	}
	return orders, nil
}

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

	// Base queries
	query := `SELECT o.id, o.customer_id, c.name, o.status, o.order_date, o.total_amount, o.notes, o.created_at, o.updated_at
		FROM orders o JOIN customers c ON o.customer_id = c.id`
	countQuery := "SELECT COUNT(*) FROM orders o"

	args := []interface{}{}
	if status != "" {
		where := " WHERE o.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 o.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 orders []Order
	for rows.Next() {
		var o Order
		if err := rows.Scan(&o.ID, &o.CustomerID, &o.CustomerName, &o.Status, &o.OrderDate, &o.TotalAmount, &o.Notes, &o.CreatedAt, &o.UpdatedAt); err != nil {
			return nil, 0, err
		}
		orders = append(orders, o)
	}
	return orders, total, nil
}

func OrderGetByID(db *sql.DB, id int) (*Order, error) {
	o := &Order{}
	err := db.QueryRow(
		`SELECT o.id, o.customer_id, c.name, o.status, o.order_date, o.total_amount, o.notes, o.created_at, o.updated_at
		FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.id = ?`, id,
	).Scan(&o.ID, &o.CustomerID, &o.CustomerName, &o.Status, &o.OrderDate, &o.TotalAmount, &o.Notes, &o.CreatedAt, &o.UpdatedAt)
	if err != nil {
		return nil, err
	}

	// Load lines
	lines, err := OrderLinesGetByOrderID(db, id)
	if err != nil {
		return nil, err
	}
	o.Lines = lines
	return o, nil
}

func OrderInsert(db *sql.DB, o *Order) error {
	result, err := db.Exec(
		"INSERT INTO orders (customer_id, status, order_date, total_amount, notes) VALUES (?, ?, ?, ?, ?)",
		o.CustomerID, o.Status, o.OrderDate, o.TotalAmount, o.Notes,
	)
	if err != nil {
		return err
	}
	id, _ := result.LastInsertId()
	o.ID = int(id)
	return nil
}

func OrderUpdate(db *sql.DB, o *Order) error {
	_, err := db.Exec(
		"UPDATE orders SET customer_id = ?, order_date = ?, notes = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
		o.CustomerID, o.OrderDate, o.Notes, o.ID,
	)
	return err
}

func OrderUpdateStatus(db *sql.DB, id int, status string) error {
	_, err := db.Exec("UPDATE orders SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?", status, id)
	return err
}

func OrderRecalcTotal(db *sql.DB, orderID int) error {
	_, err := db.Exec(
		"UPDATE orders SET total_amount = (SELECT COALESCE(SUM(line_total), 0) FROM order_lines WHERE order_id = ?), updated_at = CURRENT_TIMESTAMP WHERE id = ?",
		orderID, orderID,
	)
	return err
}

func OrderDelete(db *sql.DB, id int) error {
	_, err := db.Exec("DELETE FROM orders WHERE id = ?", id)
	return err
}

func OrderCount(db *sql.DB) int {
	var count int
	db.QueryRow("SELECT COUNT(*) FROM orders WHERE status NOT IN ('cancelled')").Scan(&count)
	return count
}

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

// Order Lines

func OrderLinesGetByOrderID(db *sql.DB, orderID int) ([]OrderLine, error) {
	rows, err := db.Query("SELECT id, order_id, description, quantity, unit_price, line_total FROM order_lines WHERE order_id = ? ORDER BY id", orderID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var lines []OrderLine
	for rows.Next() {
		var l OrderLine
		if err := rows.Scan(&l.ID, &l.OrderID, &l.Description, &l.Quantity, &l.UnitPrice, &l.LineTotal); err != nil {
			return nil, err
		}
		lines = append(lines, l)
	}
	return lines, nil
}

func OrderLineInsert(db *sql.DB, l *OrderLine) error {
	l.LineTotal = l.Quantity * l.UnitPrice
	result, err := db.Exec(
		"INSERT INTO order_lines (order_id, description, quantity, unit_price, line_total) VALUES (?, ?, ?, ?, ?)",
		l.OrderID, l.Description, l.Quantity, l.UnitPrice, l.LineTotal,
	)
	if err != nil {
		return err
	}
	id, _ := result.LastInsertId()
	l.ID = int(id)
	return OrderRecalcTotal(db, l.OrderID)
}

func OrderLineDelete(db *sql.DB, lineID, orderID int) error {
	_, err := db.Exec("DELETE FROM order_lines WHERE id = ?", lineID)
	if err != nil {
		return err
	}
	return OrderRecalcTotal(db, orderID)
}

// GenerateInvoiceFromOrder creates an invoice and GL entries when an order is fulfilled
func GenerateInvoiceFromOrder(db *sql.DB, orderID int) error {
	order, err := OrderGetByID(db, orderID)
	if err != nil {
		return err
	}

	// Generate invoice number
	var maxID int
	db.QueryRow("SELECT COALESCE(MAX(id), 0) FROM invoices").Scan(&maxID)
	invoiceNumber := fmt.Sprintf("INV-%05d", maxID+1)

	// Create invoice (due in 30 days)
	_, err = db.Exec(
		`INSERT INTO invoices (order_id, customer_id, invoice_number, status, amount, due_date)
		VALUES (?, ?, ?, 'pending', ?, date('now', '+30 days'))`,
		order.ID, order.CustomerID, invoiceNumber, order.TotalAmount,
	)
	if err != nil {
		return fmt.Errorf("creating invoice: %w", err)
	}

	// Create GL journal entry: Debit AR, Credit Revenue
	result, err := db.Exec(
		"INSERT INTO journal_entries (entry_date, description, reference) VALUES (date('now'), ?, ?)",
		fmt.Sprintf("Invoice %s - Order #%d fulfilled", invoiceNumber, orderID),
		invoiceNumber,
	)
	if err != nil {
		return fmt.Errorf("creating journal entry: %w", err)
	}
	jeID, _ := result.LastInsertId()

	// Debit Accounts Receivable (1100)
	var arID int
	db.QueryRow("SELECT id FROM gl_accounts WHERE code = '1100'").Scan(&arID)
	_, err = db.Exec("INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit) VALUES (?, ?, ?, 0)", jeID, arID, order.TotalAmount)
	if err != nil {
		return fmt.Errorf("creating AR debit line: %w", err)
	}

	// Credit Sales Revenue (4000)
	var revID int
	db.QueryRow("SELECT id FROM gl_accounts WHERE code = '4000'").Scan(&revID)
	_, err = db.Exec("INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit) VALUES (?, ?, 0, ?)", jeID, revID, order.TotalAmount)
	if err != nil {
		return fmt.Errorf("creating revenue credit line: %w", err)
	}

	// Update account balances
	db.Exec("UPDATE gl_accounts SET balance = balance + ? WHERE code = '1100'", order.TotalAmount) // AR increases (debit)
	db.Exec("UPDATE gl_accounts SET balance = balance + ? WHERE code = '4000'", order.TotalAmount) // Revenue increases (credit)

	return nil
}