RentalManager/internal/transaction/transaction.go

package transaction

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

	"github.com/google/uuid"
	"github.com/gorilla/mux"
)

// ── Models ────────────────────────────────────────────────────────────────────

type Transaction struct {
	ID          string    `json:"id"`
	PropertyID  string    `json:"property_id"`
	CategoryID  string    `json:"category_id"`
	Type        string    `json:"type"` // income | expense
	Amount      float64   `json:"amount"`
	Date        string    `json:"date"`
	Description string    `json:"description"`
	CreatedBy   string    `json:"created_by"`
	CreatedAt   time.Time `json:"created_at"`
	// Champs joints
	CategoryName string `json:"category_name,omitempty"`
	PropertyName string `json:"property_name,omitempty"`
}

type Summary struct {
	PropertyID   string  `json:"property_id"`
	PropertyName string  `json:"property_name"`
	Year         int     `json:"year"`
	TotalIncome  float64 `json:"total_income"`
	TotalExpense float64 `json:"total_expense"`
	Balance      float64 `json:"balance"`
}

// ── Store ─────────────────────────────────────────────────────────────────────

type Store struct{ db *sql.DB }

func NewStore(db *sql.DB) *Store { return &Store{db: db} }

func (s *Store) List(propertyID, txType, year, month string) ([]Transaction, error) {
	query := `
		SELECT t.id, t.property_id, COALESCE(t.category_id,''), t.type, t.amount, strftime('%Y-%m-%d', t.date) as date,
		       COALESCE(t.description,''), COALESCE(t.created_by,''), t.created_at,
		       COALESCE(c.name,''), p.name
		FROM transactions t
		LEFT JOIN categories c ON c.id = t.category_id
		LEFT JOIN properties p ON p.id = t.property_id
		WHERE 1=1`
	args := []any{}
	if propertyID != "" {
		query += " AND t.property_id = ?"
		args = append(args, propertyID)
	}
	if txType != "" {
		query += " AND t.type = ?"
		args = append(args, txType)
	}
	if year != "" {
		query += " AND strftime('%Y', t.date) = ?"
		args = append(args, year)
	}
	if month != "" {
		query += " AND strftime('%m', t.date) = ?"
		args = append(args, fmt.Sprintf("%02s", month))
	}
	query += " ORDER BY t.date DESC"

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var txs []Transaction
	for rows.Next() {
		var t Transaction
		if err := rows.Scan(&t.ID, &t.PropertyID, &t.CategoryID, &t.Type, &t.Amount,
			&t.Date, &t.Description, &t.CreatedBy, &t.CreatedAt, &t.CategoryName, &t.PropertyName); err != nil {
			return nil, err
		}
		txs = append(txs, t)
	}
	return txs, nil
}

func (s *Store) Get(id string) (*Transaction, error) {
	var t Transaction
	err := s.db.QueryRow(`
		SELECT t.id, t.property_id, COALESCE(t.category_id,''), t.type, t.amount, strftime('%Y-%m-%d', t.date) as date,
		       COALESCE(t.description,''), COALESCE(t.created_by,''), t.created_at,
		       COALESCE(c.name,''), p.name
		FROM transactions t
		LEFT JOIN categories c ON c.id = t.category_id
		LEFT JOIN properties p ON p.id = t.property_id
		WHERE t.id=?`, id,
	).Scan(&t.ID, &t.PropertyID, &t.CategoryID, &t.Type, &t.Amount,
		&t.Date, &t.Description, &t.CreatedBy, &t.CreatedAt, &t.CategoryName, &t.PropertyName)
	return &t, err
}

func (s *Store) Create(t *Transaction) error {
	t.ID = uuid.NewString()
	_, err := s.db.Exec(
		`INSERT INTO transactions (id, property_id, category_id, type, amount, date, description, created_by) VALUES (?,?,?,?,?,?,?,?)`,
		t.ID, t.PropertyID, nullStr(t.CategoryID), t.Type, t.Amount, t.Date, t.Description, nullStr(t.CreatedBy),
	)
	return err
}

func (s *Store) Update(t *Transaction) error {
	_, err := s.db.Exec(
		`UPDATE transactions SET property_id=?, category_id=?, type=?, amount=?, date=?, description=? WHERE id=?`,
		t.PropertyID, nullStr(t.CategoryID), t.Type, t.Amount, t.Date, t.Description, t.ID,
	)
	return err
}

func (s *Store) Delete(id string) error {
	_, err := s.db.Exec(`DELETE FROM transactions WHERE id=?`, id)
	return err
}

func (s *Store) GetSummary(propertyID, year, month string) ([]Summary, error) {
	query := `
		SELECT t.property_id, p.name,
		       strftime('%Y', t.date) as yr,
		       SUM(CASE WHEN t.type='income'  THEN t.amount ELSE 0 END) as income,
		       SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END) as expense
		FROM transactions t
		JOIN properties p ON p.id = t.property_id
		WHERE 1=1`
	args := []any{}
	if propertyID != "" {
		query += " AND t.property_id=?"
		args = append(args, propertyID)
	}
	if year != "" {
		query += " AND strftime('%Y', t.date)=?"
		args = append(args, year)
	}
	if month != "" {
		query += " AND strftime('%m', t.date)=?"
		args = append(args, fmt.Sprintf("%02s", month))
	}
	query += " GROUP BY t.property_id, yr ORDER BY yr DESC"

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var summaries []Summary
	for rows.Next() {
		var s Summary
		var yr string
		if err := rows.Scan(&s.PropertyID, &s.PropertyName, &yr, &s.TotalIncome, &s.TotalExpense); err != nil {
			return nil, err
		}
		s.Balance = s.TotalIncome - s.TotalExpense
		summaries = append(summaries, s)
	}
	return summaries, nil
}

func (s *Store) GetByPropertyAndYear(propertyID string, year int) ([]Transaction, error) {
	rows, err := s.db.Query(`
		SELECT t.id, t.property_id, COALESCE(t.category_id,''), t.type, t.amount, strftime('%Y-%m-%d', t.date) as date,
		       COALESCE(t.description,''), COALESCE(t.created_by,''), t.created_at,
		       COALESCE(c.name,''), p.name
		FROM transactions t
		LEFT JOIN categories c ON c.id = t.category_id
		LEFT JOIN properties p ON p.id = t.property_id
		WHERE t.property_id=? AND strftime('%Y', t.date)=?
		ORDER BY t.date`,
		propertyID, year,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var txs []Transaction
	for rows.Next() {
		var t Transaction
		if err := rows.Scan(&t.ID, &t.PropertyID, &t.CategoryID, &t.Type, &t.Amount,
			&t.Date, &t.Description, &t.CreatedBy, &t.CreatedAt, &t.CategoryName, &t.PropertyName); err != nil {
			return nil, err
		}
		txs = append(txs, t)
	}
	return txs, nil
}

// ── Handler ───────────────────────────────────────────────────────────────────

type Handler struct{ store *Store }

func NewHandler(store *Store) *Handler { return &Handler{store: store} }

func (h *Handler) List(w http.ResponseWriter, r *http.Request) {
	q := r.URL.Query()
	txs, err := h.store.List(q.Get("property_id"), q.Get("type"), q.Get("year"), q.Get("month"))
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	if txs == nil {
		txs = []Transaction{}
	}
	respond(w, txs)
}

func (h *Handler) Get(w http.ResponseWriter, r *http.Request) {
	t, err := h.store.Get(mux.Vars(r)["id"])
	if err == sql.ErrNoRows {
		http.Error(w, "not found", http.StatusNotFound)
		return
	} else if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	respond(w, t)
}

func (h *Handler) Create(w http.ResponseWriter, r *http.Request) {
	var t Transaction
	if err := json.NewDecoder(r.Body).Decode(&t); err != nil {
		http.Error(w, "invalid body", http.StatusBadRequest)
		return
	}
	if err := h.store.Create(&t); err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	w.WriteHeader(http.StatusCreated)
	respond(w, t)
}

func (h *Handler) Update(w http.ResponseWriter, r *http.Request) {
	var t Transaction
	if err := json.NewDecoder(r.Body).Decode(&t); err != nil {
		http.Error(w, "invalid body", http.StatusBadRequest)
		return
	}
	t.ID = mux.Vars(r)["id"]
	if err := h.store.Update(&t); err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	respond(w, t)
}

func (h *Handler) Delete(w http.ResponseWriter, r *http.Request) {
	if err := h.store.Delete(mux.Vars(r)["id"]); err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	w.WriteHeader(http.StatusNoContent)
}

func (h *Handler) Summary(w http.ResponseWriter, r *http.Request) {
	q := r.URL.Query()
	summaries, err := h.store.GetSummary(q.Get("property_id"), q.Get("year"), q.Get("month"))
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	if summaries == nil {
		summaries = []Summary{}
	}
	respond(w, summaries)
}

func respond(w http.ResponseWriter, v any) {
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(v)
}

func nullStr(s string) any {
	if s == "" {
		return nil
	}
	return s
}

// ── Données mensuelles pour graphiques ───────────────────────────────────────

type MonthlyData struct {
	Month   string  `json:"month"` // "2026-01"
	Income  float64 `json:"income"`
	Expense float64 `json:"expense"`
	Balance float64 `json:"balance"`
}

type CategoryBreakdown struct {
	Category string  `json:"category"`
	Amount   float64 `json:"amount"`
	Type     string  `json:"type"`
}

func (s *Store) GetMonthlyData(propertyID, year string) ([]MonthlyData, error) {
	query := `
		SELECT strftime('%Y-%m', t.date) as month,
		       SUM(CASE WHEN t.type='income'  THEN t.amount ELSE 0 END) as income,
		       SUM(CASE WHEN t.type='expense' THEN t.amount ELSE 0 END) as expense
		FROM transactions t
		WHERE strftime('%Y', t.date) = ?`
	args := []any{year}
	if propertyID != "" {
		query += " AND t.property_id = ?"
		args = append(args, propertyID)
	}
	query += " GROUP BY month ORDER BY month"

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

	// Pré-remplir les 12 mois avec zéros
	data := make(map[string]*MonthlyData)
	for m := 1; m <= 12; m++ {
		key := fmt.Sprintf("%s-%02d", year, m)
		data[key] = &MonthlyData{Month: key}
	}

	for rows.Next() {
		var d MonthlyData
		if err := rows.Scan(&d.Month, &d.Income, &d.Expense); err != nil {
			return nil, err
		}
		d.Balance = d.Income - d.Expense
		data[d.Month] = &d
	}

	// Retourner dans l'ordre
	result := make([]MonthlyData, 12)
	for m := 1; m <= 12; m++ {
		key := fmt.Sprintf("%s-%02d", year, m)
		result[m-1] = *data[key]
	}
	return result, nil
}

func (s *Store) GetCategoryBreakdown(propertyID, year, month, txType string) ([]CategoryBreakdown, error) {
	query := `
		SELECT COALESCE(c.name, 'Sans catégorie') as category,
		       SUM(t.amount) as amount,
		       t.type
		FROM transactions t
		LEFT JOIN categories c ON c.id = t.category_id
		WHERE strftime('%Y', t.date) = ?`
	args := []any{year}
	if propertyID != "" {
		query += " AND t.property_id = ?"
		args = append(args, propertyID)
	}
	if month != "" {
		query += " AND strftime('%m', t.date) = ?"
		args = append(args, fmt.Sprintf("%02s", month))
	}
	if txType != "" {
		query += " AND t.type = ?"
		args = append(args, txType)
	}
	query += " GROUP BY category, t.type ORDER BY amount DESC"

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

	var result []CategoryBreakdown
	for rows.Next() {
		var d CategoryBreakdown
		if err := rows.Scan(&d.Category, &d.Amount, &d.Type); err != nil {
			return nil, err
		}
		result = append(result, d)
	}
	return result, nil
}

func (h *Handler) Monthly(w http.ResponseWriter, r *http.Request) {
	q := r.URL.Query()
	year := q.Get("year")
	if year == "" {
		year = fmt.Sprintf("%d", time.Now().Year())
	}
	data, err := h.store.GetMonthlyData(q.Get("property_id"), year)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	respond(w, data)
}

func (h *Handler) CategoryBreakdown(w http.ResponseWriter, r *http.Request) {
	q := r.URL.Query()
	year := q.Get("year")
	if year == "" {
		year = fmt.Sprintf("%d", time.Now().Year())
	}
	data, err := h.store.GetCategoryBreakdown(q.Get("property_id"), year, q.Get("month"), q.Get("type"))
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	if data == nil {
		data = []CategoryBreakdown{}
	}
	respond(w, data)
}

// ── Split transaction ─────────────────────────────────────────────────────────

type SplitRequest struct {
	SourceID string  `json:"source_id"`
	Splits   []Split `json:"splits"`
}

type Split struct {
	PropertyID  string  `json:"property_id"`
	CategoryID  string  `json:"category_id"`
	Type        string  `json:"type"` // income | expense — si vide, hérite du type source
	Amount      float64 `json:"amount"`
	Description string  `json:"description"`
}

func (h *Handler) SplitTransaction(w http.ResponseWriter, r *http.Request) {
	var req SplitRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, "invalid body", http.StatusBadRequest)
		return
	}
	if len(req.Splits) < 2 {
		http.Error(w, "au moins 2 parts requises", http.StatusBadRequest)
		return
	}

	// Récupérer la transaction source
	source, err := h.store.Get(req.SourceID)
	if err != nil {
		http.Error(w, "transaction source introuvable", http.StatusNotFound)
		return
	}

	// Créer les nouvelles transactions
	created := []Transaction{}
	for _, s := range req.Splits {
		txType := source.Type
		if s.Type == "income" || s.Type == "expense" {
			txType = s.Type
		}
		t := &Transaction{
			PropertyID:  s.PropertyID,
			CategoryID:  source.CategoryID,
			Type:        txType,
			Amount:      s.Amount,
			Date:        source.Date,
			Description: s.Description,
		}
		if s.CategoryID != "" {
			t.CategoryID = s.CategoryID
		}
		if err := h.store.Create(t); err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		created = append(created, *t)
	}

	// Supprimer la transaction source
	h.store.Delete(req.SourceID)

	respond(w, created)
}