diff options
Diffstat (limited to 'backend/internal/store/postgres.go')
| -rw-r--r-- | backend/internal/store/postgres.go | 324 |
1 files changed, 324 insertions, 0 deletions
diff --git a/backend/internal/store/postgres.go b/backend/internal/store/postgres.go new file mode 100644 index 0000000..a8d072a --- /dev/null +++ b/backend/internal/store/postgres.go @@ -0,0 +1,324 @@ +package store + +import ( + "context" + "fmt" + "time" + + "github.com/jackc/pgx/v5" + "github.com/jackc/pgx/v5/pgxpool" + + "yaum/internal/model" +) + +type PostgresStore struct { + pool *pgxpool.Pool +} + +func NewPostgresStore(ctx context.Context, connString string) (*PostgresStore, error) { + pool, err := pgxpool.New(ctx, connString) + if err != nil { + return nil, fmt.Errorf("erro ao conectar no postgres: %w", err) + } + if err := pool.Ping(ctx); err != nil { + return nil, fmt.Errorf("erro ao pingar postgres: %w", err) + } + return &PostgresStore{pool: pool}, nil +} + +func (s *PostgresStore) Close() { + s.pool.Close() +} + +// --------------------------------------------------------------------------- +// Services +// --------------------------------------------------------------------------- + +func (s *PostgresStore) ListServices() ([]model.ServiceWithHeartbeat, error) { + query := ` + SELECT + s.id, s.name, s.url, s.group_name, s.interval_seconds, s.is_active, s.created_at, + h.id AS heartbeat_id, + h.status_code, + h.response_time_ms, + h.is_up, + h.error_message, + h.tested_at + FROM services s + LEFT JOIN LATERAL ( + SELECT id, status_code, response_time_ms, is_up, error_message, tested_at + FROM heartbeats + WHERE service_id = s.id + ORDER BY tested_at DESC + LIMIT 1 + ) h ON true + ORDER BY s.id + ` + + rows, err := s.pool.Query(context.Background(), query) + if err != nil { + return nil, fmt.Errorf("list services: %w", err) + } + defer rows.Close() + + return pgx.CollectRows(rows, func(row pgx.CollectableRow) (model.ServiceWithHeartbeat, error) { + var ( + swh model.ServiceWithHeartbeat + heartbeatID *int + statusCode *int + responseTimeMs *int64 + isUp *bool + errorMessage *string + testedAt *time.Time + ) + err := row.Scan( + &swh.ID, &swh.Name, &swh.URL, &swh.GroupName, &swh.IntervalSeconds, &swh.IsActive, &swh.CreatedAt, + &heartbeatID, &statusCode, &responseTimeMs, &isUp, &errorMessage, &testedAt, + ) + if err != nil { + return swh, err + } + if heartbeatID != nil { + swh.LastHeartbeat = &model.Heartbeat{ + ID: *heartbeatID, + ServiceID: swh.ID, + StatusCode: *statusCode, + ResponseTimeMs: *responseTimeMs, + IsUp: *isUp, + ErrorMessage: *errorMessage, + TestedAt: *testedAt, + } + } + return swh, nil + }) +} + +func (s *PostgresStore) ListActiveServices() ([]model.Service, error) { + query := ` + SELECT id, name, url, group_name, interval_seconds, is_active, discord_webhook_url, alert_email, keyword_to_find, created_at + FROM services + WHERE is_active = true + ORDER BY id + ` + + rows, err := s.pool.Query(context.Background(), query) + if err != nil { + return nil, fmt.Errorf("list active services: %w", err) + } + defer rows.Close() + + return pgx.CollectRows(rows, func(row pgx.CollectableRow) (model.Service, error) { + var svc model.Service + err := row.Scan(&svc.ID, &svc.Name, &svc.URL, &svc.GroupName, &svc.IntervalSeconds, &svc.IsActive, &svc.DiscordWebhookURL, &svc.AlertEmail, &svc.KeywordToFind, &svc.CreatedAt) + return svc, err + }) +} + +func (s *PostgresStore) GetService(id int) (*model.Service, error) { + query := ` + SELECT id, name, url, group_name, interval_seconds, is_active, discord_webhook_url, alert_email, keyword_to_find, created_at + FROM services + WHERE id = $1 + ` + var svc model.Service + err := s.pool.QueryRow(context.Background(), query, id). + Scan(&svc.ID, &svc.Name, &svc.URL, &svc.GroupName, &svc.IntervalSeconds, &svc.IsActive, &svc.DiscordWebhookURL, &svc.AlertEmail, &svc.KeywordToFind, &svc.CreatedAt) + if err != nil { + if err == pgx.ErrNoRows { + return nil, nil + } + return nil, fmt.Errorf("get service: %w", err) + } + return &svc, nil +} + +func (s *PostgresStore) AddService(svc model.Service) (*model.Service, error) { + query := ` + INSERT INTO services (name, url, group_name, interval_seconds, discord_webhook_url, alert_email, keyword_to_find) + VALUES ($1, $2, $3, $4, $5, $6, $7) + RETURNING id, is_active, created_at + ` + err := s.pool.QueryRow(context.Background(), query, svc.Name, svc.URL, svc.GroupName, svc.IntervalSeconds, svc.DiscordWebhookURL, svc.AlertEmail, svc.KeywordToFind). + Scan(&svc.ID, &svc.IsActive, &svc.CreatedAt) + if err != nil { + return nil, fmt.Errorf("add service: %w", err) + } + return &svc, nil +} + +func (s *PostgresStore) ToggleService(id int) (*model.Service, error) { + query := ` + UPDATE services + SET is_active = NOT is_active + WHERE id = $1 + RETURNING id, name, url, group_name, interval_seconds, is_active, discord_webhook_url, alert_email, keyword_to_find, created_at + ` + var svc model.Service + err := s.pool.QueryRow(context.Background(), query, id). + Scan(&svc.ID, &svc.Name, &svc.URL, &svc.GroupName, &svc.IntervalSeconds, &svc.IsActive, &svc.DiscordWebhookURL, &svc.AlertEmail, &svc.KeywordToFind, &svc.CreatedAt) + if err != nil { + if err == pgx.ErrNoRows { + return nil, fmt.Errorf("servico nao encontrado") + } + return nil, fmt.Errorf("toggle service: %w", err) + } + return &svc, nil +} + +func (s *PostgresStore) UpdateService(id int, svc model.Service) (*model.Service, error) { + query := ` + UPDATE services + SET name = $1, url = $2, group_name = $3, interval_seconds = $4, discord_webhook_url = $5, alert_email = $6, keyword_to_find = $7 + WHERE id = $8 + RETURNING id, name, url, group_name, interval_seconds, is_active, discord_webhook_url, alert_email, keyword_to_find, created_at + ` + var updated model.Service + err := s.pool.QueryRow(context.Background(), query, + svc.Name, svc.URL, svc.GroupName, svc.IntervalSeconds, svc.DiscordWebhookURL, svc.AlertEmail, svc.KeywordToFind, id, + ).Scan(&updated.ID, &updated.Name, &updated.URL, &updated.GroupName, &updated.IntervalSeconds, &updated.IsActive, &updated.DiscordWebhookURL, &updated.AlertEmail, &updated.KeywordToFind, &updated.CreatedAt) + if err != nil { + if err == pgx.ErrNoRows { + return nil, fmt.Errorf("servico nao encontrado") + } + return nil, fmt.Errorf("update service: %w", err) + } + return &updated, nil +} + +func (s *PostgresStore) DeleteService(id int) error { + tag, err := s.pool.Exec(context.Background(), "DELETE FROM services WHERE id = $1", id) + if err != nil { + return fmt.Errorf("delete service: %w", err) + } + if tag.RowsAffected() == 0 { + return fmt.Errorf("servico nao encontrado") + } + return nil +} + +// --------------------------------------------------------------------------- +// Heartbeats +// --------------------------------------------------------------------------- + +func (s *PostgresStore) AddHeartbeat(hb model.Heartbeat) error { + query := ` + INSERT INTO heartbeats (service_id, status_code, response_time_ms, is_up, error_message, tested_at) + VALUES ($1, $2, $3, $4, $5, $6) + ` + _, err := s.pool.Exec(context.Background(), query, + hb.ServiceID, hb.StatusCode, hb.ResponseTimeMs, hb.IsUp, hb.ErrorMessage, hb.TestedAt) + if err != nil { + return fmt.Errorf("add heartbeat: %w", err) + } + return nil +} + +func (s *PostgresStore) GetHistory(serviceID int, limit int) ([]model.Heartbeat, error) { + query := ` + SELECT id, service_id, status_code, response_time_ms, is_up, error_message, tested_at + FROM heartbeats + WHERE service_id = $1 + ORDER BY tested_at DESC + LIMIT $2 + ` + rows, err := s.pool.Query(context.Background(), query, serviceID, limit) + if err != nil { + return nil, fmt.Errorf("get history: %w", err) + } + defer rows.Close() + + return pgx.CollectRows(rows, func(row pgx.CollectableRow) (model.Heartbeat, error) { + var hb model.Heartbeat + err := row.Scan(&hb.ID, &hb.ServiceID, &hb.StatusCode, &hb.ResponseTimeMs, &hb.IsUp, &hb.ErrorMessage, &hb.TestedAt) + return hb, err + }) +} + +func (s *PostgresStore) GetLastHeartbeat(serviceID int) (*model.Heartbeat, error) { + query := ` + SELECT id, service_id, status_code, response_time_ms, is_up, error_message, tested_at + FROM heartbeats + WHERE service_id = $1 + ORDER BY tested_at DESC + LIMIT 1 + ` + hb := &model.Heartbeat{} + err := s.pool.QueryRow(context.Background(), query, serviceID). + Scan(&hb.ID, &hb.ServiceID, &hb.StatusCode, &hb.ResponseTimeMs, &hb.IsUp, &hb.ErrorMessage, &hb.TestedAt) + if err != nil { + if err == pgx.ErrNoRows { + return nil, nil + } + return nil, fmt.Errorf("get last heartbeat: %w", err) + } + return hb, nil +} + +func (s *PostgresStore) GetServiceStats(serviceID int) (*model.ServiceStats, error) { + query := ` + SELECT + COALESCE(SUM(CASE WHEN tested_at >= NOW() - INTERVAL '24 hours' THEN 1 ELSE 0 END), 0), + COALESCE(SUM(CASE WHEN tested_at >= NOW() - INTERVAL '24 hours' AND is_up THEN 1 ELSE 0 END), 0), + COALESCE(AVG(response_time_ms) FILTER (WHERE tested_at >= NOW() - INTERVAL '24 hours'), 0), + COALESCE(SUM(CASE WHEN tested_at >= NOW() - INTERVAL '7 days' THEN 1 ELSE 0 END), 0), + COALESCE(SUM(CASE WHEN tested_at >= NOW() - INTERVAL '7 days' AND is_up THEN 1 ELSE 0 END), 0), + COALESCE(AVG(response_time_ms) FILTER (WHERE tested_at >= NOW() - INTERVAL '7 days'), 0), + COALESCE(SUM(CASE WHEN tested_at >= NOW() - INTERVAL '30 days' THEN 1 ELSE 0 END), 0), + COALESCE(SUM(CASE WHEN tested_at >= NOW() - INTERVAL '30 days' AND is_up THEN 1 ELSE 0 END), 0), + COALESCE(AVG(response_time_ms) FILTER (WHERE tested_at >= NOW() - INTERVAL '30 days'), 0) + FROM heartbeats + WHERE service_id = $1 + ` + var ( + t24, u24 int32 + a24 float64 + t7, u7 int32 + a7 float64 + t30, u30 int32 + a30 float64 + ) + err := s.pool.QueryRow(context.Background(), query, serviceID). + Scan(&t24, &u24, &a24, &t7, &u7, &a7, &t30, &u30, &a30) + if err != nil { + return nil, fmt.Errorf("get service stats: %w", err) + } + + pct := func(up, total int32) float64 { + if total == 0 { + return 0 + } + return float64(up) / float64(total) * 100 + } + + return &model.ServiceStats{ + ServiceID: serviceID, + Uptime24h: pct(u24, t24), + Uptime7d: pct(u7, t7), + Uptime30d: pct(u30, t30), + AvgResponseMs24h: a24, + AvgResponseMs7d: a7, + AvgResponseMs30d: a30, + TotalChecks24h: int(t24), + TotalChecks7d: int(t7), + TotalChecks30d: int(t30), + }, nil +} + +func (s *PostgresStore) GetActiveMaintenance(now time.Time) (*model.Maintenance, error) { + query := ` + SELECT id, title, start_time, end_time, is_active + FROM maintenances + WHERE is_active = true AND start_time <= $1 AND end_time > $1 + LIMIT 1 + ` + var m model.Maintenance + err := s.pool.QueryRow(context.Background(), query, now). + Scan(&m.ID, &m.Title, &m.StartTime, &m.EndTime, &m.IsActive) + if err != nil { + if err == pgx.ErrNoRows { + return nil, nil + } + return nil, fmt.Errorf("get active maintenance: %w", err) + } + return &m, nil +} |