path: root/backend/internal/store/postgres.go

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, offset 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
		OFFSET $3
	`
	rows, err := s.pool.Query(context.Background(), query, serviceID, limit, offset)
	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) CountHeartbeats(serviceID int) (int, error) {
	var count int
	err := s.pool.QueryRow(context.Background(),
		`SELECT COUNT(*) FROM heartbeats WHERE service_id = $1`,
		serviceID,
	).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("count heartbeats: %w", err)
	}
	return count, nil
}

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
}

func (s *PostgresStore) ListMaintenances() ([]model.Maintenance, error) {
	query := `SELECT id, title, start_time, end_time, is_active FROM maintenances ORDER BY start_time DESC`
	rows, err := s.pool.Query(context.Background(), query)
	if err != nil {
		return nil, fmt.Errorf("list maintenances: %w", err)
	}
	defer rows.Close()
	return pgx.CollectRows(rows, func(row pgx.CollectableRow) (model.Maintenance, error) {
		var m model.Maintenance
		err := row.Scan(&m.ID, &m.Title, &m.StartTime, &m.EndTime, &m.IsActive)
		return m, err
	})
}

func (s *PostgresStore) AddMaintenance(m model.Maintenance) (*model.Maintenance, error) {
	query := `
		INSERT INTO maintenances (title, start_time, end_time, is_active)
		VALUES ($1, $2, $3, $4)
		RETURNING id
	`
	err := s.pool.QueryRow(context.Background(), query, m.Title, m.StartTime, m.EndTime, m.IsActive).
		Scan(&m.ID)
	if err != nil {
		return nil, fmt.Errorf("add maintenance: %w", err)
	}
	return &m, nil
}

func (s *PostgresStore) UpdateMaintenance(id int, m model.Maintenance) (*model.Maintenance, error) {
	query := `
		UPDATE maintenances
		SET title = $1, start_time = $2, end_time = $3, is_active = $4
		WHERE id = $5
		RETURNING id, title, start_time, end_time, is_active
	`
	var updated model.Maintenance
	err := s.pool.QueryRow(context.Background(), query, m.Title, m.StartTime, m.EndTime, m.IsActive, id).
		Scan(&updated.ID, &updated.Title, &updated.StartTime, &updated.EndTime, &updated.IsActive)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, fmt.Errorf("manutencao nao encontrada")
		}
		return nil, fmt.Errorf("update maintenance: %w", err)
	}
	return &updated, nil
}

func (s *PostgresStore) DeleteMaintenance(id int) error {
	tag, err := s.pool.Exec(context.Background(), "DELETE FROM maintenances WHERE id = $1", id)
	if err != nil {
		return fmt.Errorf("delete maintenance: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("manutencao nao encontrada")
	}
	return nil
}

func (s *PostgresStore) GetSSLInfo(serviceID int) (*model.SSLInfo, error) {
	var info model.SSLInfo
	err := s.pool.QueryRow(context.Background(),
		`SELECT service_id, issuer, expires_at, days_remaining, checked_at FROM ssl_info WHERE service_id = $1`,
		serviceID,
	).Scan(&info.ServiceID, &info.Issuer, &info.ExpiresAt, &info.DaysRemaining, &info.CheckedAt)
	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("get ssl info: %w", err)
	}
	return &info, nil
}

func (s *PostgresStore) SaveSSLInfo(info model.SSLInfo) error {
	_, err := s.pool.Exec(context.Background(),
		`INSERT INTO ssl_info (service_id, issuer, expires_at, days_remaining, checked_at)
		 VALUES ($1, $2, $3, $4, $5)
		 ON CONFLICT (service_id) DO UPDATE SET issuer=$2, expires_at=$3, days_remaining=$4, checked_at=$5`,
		info.ServiceID, info.Issuer, info.ExpiresAt, info.DaysRemaining, info.CheckedAt,
	)
	if err != nil {
		return fmt.Errorf("save ssl info: %w", err)
	}
	return nil
}