boc/backend/handlers/finance.go

package handlers

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"net/http"
	"time"

	"boc/email"
	"boc/pdf"
)

type FinanceHandler struct {
	DB         *sql.DB
	EmailClient *email.Client
}

func NewFinanceHandler(db *sql.DB) *FinanceHandler {
	return &FinanceHandler{DB: db}
}

func (h *FinanceHandler) SetEmailClient(client *email.Client) {
	h.EmailClient = client
}

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    sql.NullString `json:"due_date"`
	PaidAt     sql.NullString `json:"paid_at"`
	CreatedAt  string         `json:"created_at"`
}

type Expense struct {
	ID          string    `json:"id"`
	Category    string    `json:"category"`
	Description string    `json:"description"`
	Amount      float64   `json:"amount"`
	Currency    string    `json:"currency"`
	Vendor      string    `json:"vendor"`
	Status      string    `json:"status"`
	CreatedAt   time.Time `json:"created_at"`
}

func (h *FinanceHandler) GetCashFlow(w http.ResponseWriter, r *http.Request) {
	// Get paid invoices this month
	var income float64
	err := h.DB.QueryRow(`
		SELECT COALESCE(SUM(amount), 0)
		FROM boc_invoices
		WHERE status = 'paid'
		AND paid_at >= NOW() - INTERVAL '1 month'
	`).Scan(&income)
	if err != nil {
		income = 0
	}

	// Get outstanding invoices
	var outstanding float64
	err = h.DB.QueryRow(`
		SELECT COALESCE(SUM(amount), 0)
		FROM boc_invoices
		WHERE status = 'sent'
	`).Scan(&outstanding)
	if err != nil {
		outstanding = 0
	}

	// Get expenses this month
	var expenses float64
	err = h.DB.QueryRow(`
		SELECT COALESCE(SUM(amount), 0)
		FROM boc_expenses
		WHERE status = 'approved'
		AND created_at >= NOW() - INTERVAL '1 month'
	`).Scan(&expenses)
	if err != nil {
		expenses = 0
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"income_this_month": income,
		"outstanding":       outstanding,
		"expenses":          expenses,
		"net_cashflow":      income - expenses,
		"currency":          "USD",
	})
}

func (h *FinanceHandler) GetBudget(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.Query(`
		SELECT name, fiscal_year, category, amount, spent, currency
		FROM boc_budgets
		WHERE status = 'active'
		ORDER BY fiscal_year DESC, category
	`)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	budgets := []map[string]interface{}{}
	for rows.Next() {
		var name, category, currency string
		var fiscalYear int
		var amount, spent float64
		if err := rows.Scan(&name, &fiscalYear, &category, &amount, &spent, &currency); err != nil {
			continue
		}
		budgets = append(budgets, map[string]interface{}{
			"name":        name,
			"fiscal_year": fiscalYear,
			"category":    category,
			"amount":      amount,
			"spent":       spent,
			"remaining":   amount - spent,
			"currency":    currency,
		})
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"budgets": budgets,
		"total":   len(budgets),
	})
}

func (h *FinanceHandler) ListInvoices(w http.ResponseWriter, r *http.Request) {
	status := r.URL.Query().Get("status")
	if status == "" {
		status = "all"
	}

	var query string
	var args []interface{}
	if status == "all" {
		query = `
			SELECT id, customer_id, amount, currency, status, due_date, paid_at, created_at
			FROM boc_invoices
			ORDER BY created_at DESC
			LIMIT 100
		`
	} else {
		query = `
			SELECT id, customer_id, amount, currency, status, due_date, paid_at, created_at
			FROM boc_invoices
			WHERE status = $1
			ORDER BY created_at DESC
			LIMIT 100
		`
		args = append(args, status)
	}

	rows, err := h.DB.Query(query, args...)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	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)
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"invoices": invoices,
		"total":    len(invoices),
	})
}

func (h *FinanceHandler) CreateExpense(w http.ResponseWriter, r *http.Request) {
	var req Expense
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		writeError(w, http.StatusBadRequest, "invalid request")
		return
	}

	var id string
	err := h.DB.QueryRow(`
		INSERT INTO boc_expenses (category, description, amount, currency, vendor, status)
		VALUES ($1, $2, $3, $4, $5, 'pending')
		RETURNING id
	`, req.Category, req.Description, req.Amount, req.Currency, req.Vendor).Scan(&id)

	if err != nil {
		writeError(w, http.StatusInternalServerError, "failed to create expense")
		return
	}

	writeJSON(w, http.StatusCreated, map[string]interface{}{
		"id":      id,
		"message": "Expense created",
	})
}

func (h *FinanceHandler) ListExpenses(w http.ResponseWriter, r *http.Request) {
	status := r.URL.Query().Get("status")
	if status == "" {
		status = "all"
	}

	var query string
	var args []interface{}
	if status == "all" {
		query = `
			SELECT id, category, description, amount, currency, vendor, status, created_at
			FROM boc_expenses
			ORDER BY created_at DESC
			LIMIT 100
		`
	} else {
		query = `
			SELECT id, category, description, amount, currency, vendor, status, created_at
			FROM boc_expenses
			WHERE status = $1
			ORDER BY created_at DESC
			LIMIT 100
		`
		args = append(args, status)
	}

	rows, err := h.DB.Query(query, args...)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

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

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"expenses": expenses,
		"total":    len(expenses),
	})
}

// GenerateInvoicePDF generates a PDF for an invoice
func (h *FinanceHandler) GenerateInvoicePDF(w http.ResponseWriter, r *http.Request) {
	invoiceID := r.URL.Query().Get("id")
	if invoiceID == "" {
		writeError(w, http.StatusBadRequest, "invoice id required")
		return
	}

	var customerID, currency, status string
	var amount float64
	var dueDate sql.NullString
	err := h.DB.QueryRow(`
		SELECT customer_id, amount, currency, status, due_date
		FROM boc_invoices WHERE id = $1
	`, invoiceID).Scan(&customerID, &amount, &currency, &status, &dueDate)
	if err != nil {
		writeError(w, http.StatusNotFound, "invoice not found")
		return
	}

	var customerName, customerAddress, customerOrgNr string
	h.DB.QueryRow(`
		SELECT name, address, org_nr FROM boc_customers WHERE id = $1
	`, customerID).Scan(&customerName, &customerAddress, &customerOrgNr)

	data := pdf.InvoiceData{
		InvoiceNumber:   invoiceID[:8],
		InvoiceDate:     time.Now(),
		DueDate:         time.Now().AddDate(0, 0, 30),
		CustomerName:    customerName,
		CustomerAddress: customerAddress,
		CustomerOrgNr:   customerOrgNr,
		Items: []pdf.InvoiceItem{
			{
				Description: "Tjänst",
				Quantity:    1,
				Unit:        "st",
				UnitPrice:   amount,
				Total:       amount,
			},
		},
		Subtotal:        amount,
		VATRate:         0.25,
		VATAmount:       amount * 0.25,
		Total:           amount * 1.25,
		Currency:        currency,
		CompanyName:     "Landvex Inc",
		CompanyAddress:  "Houston, TX",
		CompanyOrgNr:    "559141-7042",
		CompanyBankgiro: "1234-5678",
		Notes:           fmt.Sprintf("Status: %s | Betalningsvillkor: 30 dagar", status),
	}

	pdfBytes, err := pdf.GenerateInvoice(data)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "pdf generation failed")
		return
	}

	w.Header().Set("Content-Type", "application/pdf")
	w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=\"faktura-%s.pdf\"", invoiceID[:8]))
	w.Write(pdfBytes)
}

// SendInvoiceEmail sends an invoice via email with PDF attachment
func (h *FinanceHandler) SendInvoiceEmail(w http.ResponseWriter, r *http.Request) {
	if h.EmailClient == nil {
		writeError(w, http.StatusServiceUnavailable, "email not configured")
		return
	}

	var req struct {
		InvoiceID string   `json:"invoice_id"`
		To        []string `json:"to"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		writeError(w, http.StatusBadRequest, "invalid request")
		return
	}

	// Generate PDF first
	var customerID, currency, status string
	var amount float64
	err := h.DB.QueryRow(`
		SELECT customer_id, amount, currency, status, due_date
		FROM boc_invoices WHERE id = $1
	`, req.InvoiceID).Scan(&customerID, &amount, &currency, &status)
	if err != nil {
		writeError(w, http.StatusNotFound, "invoice not found")
		return
	}

	var customerName, customerAddress, customerOrgNr string
	h.DB.QueryRow(`
		SELECT name, address, org_nr FROM boc_customers WHERE id = $1
	`, customerID).Scan(&customerName, &customerAddress, &customerOrgNr)

	data := pdf.InvoiceData{
		InvoiceNumber:   req.InvoiceID[:8],
		InvoiceDate:     time.Now(),
		DueDate:         time.Now().AddDate(0, 0, 30),
		CustomerName:    customerName,
		CustomerAddress: customerAddress,
		CustomerOrgNr:   customerOrgNr,
		Items: []pdf.InvoiceItem{
			{
				Description: "Tjänst",
				Quantity:    1,
				Unit:        "st",
				UnitPrice:   amount,
				Total:       amount,
			},
		},
		Subtotal:        amount,
		VATRate:         0.25,
		VATAmount:       amount * 0.25,
		Total:           amount * 1.25,
		Currency:        currency,
		CompanyName:     "Landvex Inc",
		CompanyAddress:  "Houston, TX",
		CompanyOrgNr:    "559141-7042",
		CompanyBankgiro: "1234-5678",
		Notes:           fmt.Sprintf("Status: %s", status),
	}

	pdfBytes, err := pdf.GenerateInvoice(data)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "pdf generation failed")
		return
	}

	err = h.EmailClient.SendInvoice(req.To, req.InvoiceID[:8], pdfBytes, "")
	if err != nil {
		writeError(w, http.StatusInternalServerError, fmt.Sprintf("email failed: %v", err))
		return
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"message": "Invoice sent",
		"to":      req.To,
	})
}