sentinela-go/internal/db/apikeys.go

package db

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

	"github.com/google/uuid"
)

type APIKey struct {
	ID            int64      `json:"id"`
	Key           string     `json:"key"`
	Name          string     `json:"name"`
	Email         string     `json:"email"`
	Plan          string     `json:"plan"`
	RateLimit     int        `json:"rate_limit"`
	RequestsToday int        `json:"requests_today"`
	RequestsMonth int        `json:"requests_month"`
	LastRequestAt *time.Time `json:"last_request_at,omitempty"`
	CreatedAt     time.Time  `json:"created_at"`
	ExpiresAt     *time.Time `json:"expires_at,omitempty"`
	Active        bool       `json:"active"`
}

type UsageStats struct {
	TotalRequests int            `json:"total_requests"`
	DailyBreakdown []DailyUsage  `json:"daily_breakdown"`
	TopEndpoints  []EndpointStat `json:"top_endpoints"`
}

type DailyUsage struct {
	Date     string `json:"date"`
	Requests int    `json:"requests"`
}

type EndpointStat struct {
	Endpoint    string  `json:"endpoint"`
	Requests    int     `json:"requests"`
	AvgLatencyMs float64 `json:"avg_latency_ms"`
}

func planRateLimit(plan string) int {
	switch plan {
	case "bronze":
		return 100
	case "gold":
		return 500
	case "platinum":
		return 2000
	default:
		return 30
	}
}

func (d *DB) CreateAPIKey(name, email, plan string) (*APIKey, error) {
	key := uuid.New().String()
	rl := planRateLimit(plan)
	res, err := d.Conn.Exec(
		`INSERT INTO api_keys (key, name, email, plan, rate_limit) VALUES (?, ?, ?, ?, ?)`,
		key, name, email, plan, rl,
	)
	if err != nil {
		return nil, fmt.Errorf("create api key: %w", err)
	}
	id, _ := res.LastInsertId()
	return &APIKey{
		ID: id, Key: key, Name: name, Email: email,
		Plan: plan, RateLimit: rl, Active: true, CreatedAt: time.Now(),
	}, nil
}

func (d *DB) CreateAPIKeyWithValue(key, name, email, plan string) (*APIKey, error) {
	rl := planRateLimit(plan)
	res, err := d.Conn.Exec(
		`INSERT OR IGNORE INTO api_keys (key, name, email, plan, rate_limit) VALUES (?, ?, ?, ?, ?)`,
		key, name, email, plan, rl,
	)
	if err != nil {
		return nil, fmt.Errorf("create api key: %w", err)
	}
	id, _ := res.LastInsertId()
	return &APIKey{
		ID: id, Key: key, Name: name, Email: email,
		Plan: plan, RateLimit: rl, Active: true, CreatedAt: time.Now(),
	}, nil
}

func (d *DB) GetAPIKey(key string) (*APIKey, error) {
	row := d.Conn.QueryRow(
		`SELECT id, key, name, email, plan, rate_limit, requests_today, requests_month, last_request_at, created_at, expires_at, active
		 FROM api_keys WHERE key = ? AND active = 1`, key,
	)
	var ak APIKey
	var lastReq, expiresAt sql.NullString
	var activeInt int
	err := row.Scan(&ak.ID, &ak.Key, &ak.Name, &ak.Email, &ak.Plan, &ak.RateLimit,
		&ak.RequestsToday, &ak.RequestsMonth, &lastReq, &ak.CreatedAt, &expiresAt, &activeInt)
	if err != nil {
		return nil, err
	}
	ak.Active = activeInt == 1
	if lastReq.Valid {
		t, _ := time.Parse("2006-01-02 15:04:05", lastReq.String)
		ak.LastRequestAt = &t
	}
	if expiresAt.Valid {
		t, _ := time.Parse("2006-01-02 15:04:05", expiresAt.String)
		ak.ExpiresAt = &t
	}
	return &ak, nil
}

func (d *DB) ListAPIKeys() ([]APIKey, error) {
	rows, err := d.Conn.Query(`SELECT id, key, name, email, plan, rate_limit, requests_today, requests_month, active FROM api_keys`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var keys []APIKey
	for rows.Next() {
		var ak APIKey
		var activeInt int
		if err := rows.Scan(&ak.ID, &ak.Key, &ak.Name, &ak.Email, &ak.Plan, &ak.RateLimit, &ak.RequestsToday, &ak.RequestsMonth, &activeInt); err != nil {
			continue
		}
		ak.Active = activeInt == 1
		keys = append(keys, ak)
	}
	return keys, nil
}

func (d *DB) UpdatePlan(keyID int64, plan string) error {
	rl := planRateLimit(plan)
	_, err := d.Conn.Exec(`UPDATE api_keys SET plan = ?, rate_limit = ? WHERE id = ?`, plan, rl, keyID)
	return err
}

func (d *DB) IncrementUsage(keyID int64, endpoint string, statusCode int, responseTimeMs int, ip string) error {
	now := time.Now()
	date := now.Format("2006-01-02")

	_, err := d.Conn.Exec(`UPDATE api_keys SET requests_today = requests_today + 1, requests_month = requests_month + 1, last_request_at = ? WHERE id = ?`, now, keyID)
	if err != nil {
		return err
	}

	_, err = d.Conn.Exec(`INSERT INTO usage_log (api_key_id, endpoint, method, status_code, response_time_ms, ip) VALUES (?, ?, 'GET', ?, ?, ?)`,
		keyID, endpoint, statusCode, responseTimeMs, ip)
	if err != nil {
		return err
	}

	_, err = d.Conn.Exec(`INSERT INTO usage_daily (api_key_id, date, requests) VALUES (?, ?, 1) ON CONFLICT(api_key_id, date) DO UPDATE SET requests = requests + 1`,
		keyID, date)
	return err
}

func (d *DB) GetUsageStats(keyID int64, from, to string) (*UsageStats, error) {
	stats := &UsageStats{}

	// Total
	d.Conn.QueryRow(`SELECT COALESCE(SUM(requests), 0) FROM usage_daily WHERE api_key_id = ? AND date >= ? AND date <= ?`, keyID, from, to).Scan(&stats.TotalRequests)

	// Daily
	rows, err := d.Conn.Query(`SELECT date, requests FROM usage_daily WHERE api_key_id = ? AND date >= ? AND date <= ? ORDER BY date`, keyID, from, to)
	if err == nil {
		defer rows.Close()
		for rows.Next() {
			var du DailyUsage
			rows.Scan(&du.Date, &du.Requests)
			stats.DailyBreakdown = append(stats.DailyBreakdown, du)
		}
	}

	// Top endpoints
	rows2, err := d.Conn.Query(`SELECT endpoint, COUNT(*) as cnt, AVG(response_time_ms) as avg_ms FROM usage_log WHERE api_key_id = ? AND created_at >= ? AND created_at <= ? GROUP BY endpoint ORDER BY cnt DESC LIMIT 10`, keyID, from, to+" 23:59:59")
	if err == nil {
		defer rows2.Close()
		for rows2.Next() {
			var es EndpointStat
			rows2.Scan(&es.Endpoint, &es.Requests, &es.AvgLatencyMs)
			stats.TopEndpoints = append(stats.TopEndpoints, es)
		}
	}

	return stats, nil
}

func (d *DB) ResetDailyCounters() error {
	_, err := d.Conn.Exec(`UPDATE api_keys SET requests_today = 0`)
	return err
}

func (d *DB) ResetMonthlyCounters() error {
	_, err := d.Conn.Exec(`UPDATE api_keys SET requests_month = 0`)
	return err
}