boc/backend/handlers/subscriptions.go

package handlers

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

type SubscriptionHandler struct {
	DB *sql.DB
}

func NewSubscriptionHandler(db *sql.DB) *SubscriptionHandler {
	return &SubscriptionHandler{DB: db}
}

type SubscriptionPlan struct {
	ID            string                 `json:"id"`
	Name          string                 `json:"name"`
	Description   string                 `json:"description"`
	ProductID     *string                `json:"product_id"`
	Interval      string                 `json:"interval"`
	IntervalCount int                    `json:"interval_count"`
	Price         float64                `json:"price"`
	Currency      string                 `json:"currency"`
	TrialDays     int                    `json:"trial_days"`
	SetupFee      float64                `json:"setup_fee"`
	Status        string                 `json:"status"`
	CreatedAt     time.Time              `json:"created_at"`
}

type Subscription struct {
	ID               string     `json:"id"`
	CustomerID       string     `json:"customer_id"`
	PlanID           string     `json:"plan_id"`
	Status           string     `json:"status"`
	StartDate        time.Time  `json:"start_date"`
	EndDate          *time.Time `json:"end_date"`
	TrialEnd         *time.Time `json:"trial_end"`
	CurrentPeriodStart *time.Time `json:"current_period_start"`
	CurrentPeriodEnd   *time.Time `json:"current_period_end"`
	Price            float64    `json:"price"`
	Currency         string     `json:"currency"`
	CreatedAt        time.Time  `json:"created_at"`
}

func (h *SubscriptionHandler) ListPlans(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.Query(`
		SELECT id, name, description, product_id, interval, interval_count, price, currency, trial_days, setup_fee, status, created_at
		FROM boc_subscription_plans WHERE status = 'active' ORDER BY name
	`)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	plans := []SubscriptionPlan{}
	for rows.Next() {
		var p SubscriptionPlan
		if err := rows.Scan(&p.ID, &p.Name, &p.Description, &p.ProductID, &p.Interval, &p.IntervalCount, &p.Price, &p.Currency, &p.TrialDays, &p.SetupFee, &p.Status, &p.CreatedAt); err != nil {
			continue
		}
		plans = append(plans, p)
	}

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

func (h *SubscriptionHandler) CreatePlan(w http.ResponseWriter, r *http.Request) {
	var req SubscriptionPlan
	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_subscription_plans (name, description, product_id, interval, interval_count, price, currency, trial_days, setup_fee)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		RETURNING id
	`, req.Name, req.Description, req.ProductID, req.Interval, req.IntervalCount, req.Price, req.Currency, req.TrialDays, req.SetupFee).Scan(&id)

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

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

func (h *SubscriptionHandler) ListSubscriptions(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, plan_id, status, start_date, end_date, trial_end, current_period_start, current_period_end, price, currency, created_at FROM boc_subscriptions ORDER BY created_at DESC LIMIT 100`
	} else {
		query = `SELECT id, customer_id, plan_id, status, start_date, end_date, trial_end, current_period_start, current_period_end, price, currency, created_at FROM boc_subscriptions 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()

	subs := []Subscription{}
	for rows.Next() {
		var s Subscription
		if err := rows.Scan(&s.ID, &s.CustomerID, &s.PlanID, &s.Status, &s.StartDate, &s.EndDate, &s.TrialEnd, &s.CurrentPeriodStart, &s.CurrentPeriodEnd, &s.Price, &s.Currency, &s.CreatedAt); err != nil {
			continue
		}
		subs = append(subs, s)
	}

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

func (h *SubscriptionHandler) CreateSubscription(w http.ResponseWriter, r *http.Request) {
	var req struct {
		CustomerID string    `json:"customer_id"`
		PlanID     string    `json:"plan_id"`
		StartDate  time.Time `json:"start_date"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		writeError(w, http.StatusBadRequest, "invalid request")
		return
	}

	// Get plan details
	var planPrice float64
	var planCurrency string
	var trialDays int
	err := h.DB.QueryRow(`SELECT price, currency, trial_days FROM boc_subscription_plans WHERE id = $1`, req.PlanID).Scan(&planPrice, &planCurrency, &trialDays)
	if err != nil {
		writeError(w, http.StatusNotFound, "plan not found")
		return
	}

	// Calculate dates
	var trialEnd, periodStart, periodEnd *time.Time
	start := req.StartDate
	periodStart = &start

	if trialDays > 0 {
		t := start.AddDate(0, 0, trialDays)
		trialEnd = &t
		periodStart = trialEnd
	}

	pe := periodStart.AddDate(0, 1, 0) // Monthly default
	periodEnd = &pe

	var id string
	err = h.DB.QueryRow(`
		INSERT INTO boc_subscriptions (customer_id, plan_id, status, start_date, end_date, trial_end, current_period_start, current_period_end, price, currency)
		VALUES ($1, $2, 'active', $3, NULL, $4, $5, $6, $7, $8)
		RETURNING id
	`, req.CustomerID, req.PlanID, start, trialEnd, periodStart, periodEnd, planPrice, planCurrency).Scan(&id)

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

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

func (h *SubscriptionHandler) GenerateRecurringInvoices(w http.ResponseWriter, r *http.Request) {
	// Find subscriptions with period ending soon
	rows, err := h.DB.Query(`
		SELECT s.id, s.customer_id, s.plan_id, s.price, s.currency, s.current_period_end
		FROM boc_subscriptions s
		WHERE s.status = 'active'
		AND s.current_period_end <= NOW() + INTERVAL '7 days'
		AND NOT EXISTS (
			SELECT 1 FROM boc_recurring_invoices ri
			WHERE ri.subscription_id = s.id
			AND ri.scheduled_date = s.current_period_end
		)
	`)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	generated := 0
	for rows.Next() {
		var subID, customerID, planID string
		var price float64
		var currency string
		var periodEnd time.Time
		if err := rows.Scan(&subID, &customerID, &planID, &price, &currency, &periodEnd); err != nil {
			continue
		}

		invoiceNumber := fmt.Sprintf("SUB-%d-%s", time.Now().Unix(), subID[:8])
		_, err = h.DB.Exec(`
			INSERT INTO boc_recurring_invoices (customer_id, subscription_id, plan_id, invoice_number, amount, currency, scheduled_date)
			VALUES ($1, $2, $3, $4, $5, $6, $7)
		`, customerID, subID, planID, invoiceNumber, price, currency, periodEnd)
		if err == nil {
			generated++
		}
	}

	writeJSON(w, http.StatusOK, map[string]interface{}{
		"generated": generated,
		"message":   fmt.Sprintf("Generated %d recurring invoices", generated),
	})
}

func (h *SubscriptionHandler) ListRecurringInvoices(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.Query(`
		SELECT id, customer_id, subscription_id, plan_id, invoice_number, amount, currency, status, scheduled_date, generated_at, sent_at
		FROM boc_recurring_invoices
		ORDER BY scheduled_date DESC
		LIMIT 100
	`)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "database error")
		return
	}
	defer rows.Close()

	invoices := []map[string]interface{}{}
	for rows.Next() {
		var id, customerID, subID, planID, invNumber, status, currency string
		var amount float64
		var scheduledDate time.Time
		var generatedAt, sentAt *time.Time
		if err := rows.Scan(&id, &customerID, &subID, &planID, &invNumber, &amount, &currency, &status, &scheduledDate, &generatedAt, &sentAt); err != nil {
			continue
		}
		invoices = append(invoices, map[string]interface{}{
			"id":             id,
			"customer_id":    customerID,
			"subscription_id": subID,
			"invoice_number": invNumber,
			"amount":         amount,
			"currency":       currency,
			"status":         status,
			"scheduled_date": scheduledDate,
			"generated_at":   generatedAt,
			"sent_at":        sentAt,
		})
	}

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