package store import ( "context" "database/sql" "strings" "time" "github.com/google/uuid" ) type Broadcast struct { UUID string `json:"uuid"` DomainUUID string `json:"domain_uuid"` Title string `json:"title"` Body string `json:"body"` Severity string `json:"severity"` StartsAtMS int64 `json:"starts_at_ms"` EndsAtMS int64 `json:"ends_at_ms"` CreatedAtMS int64 `json:"created_at_ms"` UpdatedAtMS int64 `json:"updated_at_ms"` } func (s *Store) EnsureBroadcastSchema() error { return s.ensureSchemaComponent("broadcasts") } const broadcastColumns = `uuid,domain_uuid,title,body,severity,starts_at_ms,ends_at_ms,created_at_ms,updated_at_ms` func scanBroadcast(scanner interface{ Scan(...any) error }, b *Broadcast) error { return scanner.Scan(&b.UUID, &b.DomainUUID, &b.Title, &b.Body, &b.Severity, &b.StartsAtMS, &b.EndsAtMS, &b.CreatedAtMS, &b.UpdatedAtMS) } func (s *Store) ListActiveBroadcasts(ctx context.Context, domain string, nowMS int64) (_ []Broadcast, resultErr error) { defer func() { resultErr = s.sqlError("active broadcasts", resultErr) }() ctx, cancel := context.WithTimeout(ctx, 3*time.Second) defer cancel() if nowMS == 0 { nowMS = time.Now().UnixMilli() } rows, err := s.DB.QueryContext(ctx, `SELECT `+broadcastColumns+` FROM broadcasts WHERE domain_uuid=`+s.sqlParam(1)+` AND (starts_at_ms=0 OR starts_at_ms<=`+s.sqlParam(2)+`) AND (ends_at_ms=0 OR ends_at_ms>`+s.sqlParam(3)+`) ORDER BY severity DESC,updated_at_ms DESC LIMIT 20`, domain, nowMS, nowMS) if err != nil { return nil, err } defer rows.Close() out := []Broadcast{} for rows.Next() { var b Broadcast if err := scanBroadcast(rows, &b); err != nil { return nil, err } out = append(out, b) } return out, rows.Err() } func (s *Store) ListBroadcasts(ctx context.Context, domain string) (_ []Broadcast, resultErr error) { defer func() { resultErr = s.sqlError("broadcast listing", resultErr) }() ctx, cancel := context.WithTimeout(ctx, 3*time.Second) defer cancel() rows, err := s.DB.QueryContext(ctx, `SELECT `+broadcastColumns+` FROM broadcasts WHERE domain_uuid=`+s.sqlParam(1)+` ORDER BY updated_at_ms DESC LIMIT 100`, domain) if err != nil { return nil, err } defer rows.Close() out := []Broadcast{} for rows.Next() { var b Broadcast if err := scanBroadcast(rows, &b); err != nil { return nil, err } out = append(out, b) } return out, rows.Err() } func (s *Store) SaveBroadcast(b Broadcast) (resultErr error) { defer func() { resultErr = s.sqlError("broadcast save", resultErr) }() if s.ReadOnly { return ErrReadOnly } b.DomainUUID = strings.TrimSpace(b.DomainUUID) b.Title = strings.TrimSpace(b.Title) b.Body = strings.TrimSpace(b.Body) if b.UUID == "" { b.UUID = uuid.NewString() } if b.Severity != "success" && b.Severity != "warning" && b.Severity != "critical" { b.Severity = "info" } now := time.Now().UnixMilli() if b.CreatedAtMS == 0 { b.CreatedAtMS = now } b.UpdatedAtMS = now ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) defer cancel() tx, err := s.DB.BeginTx(ctx, nil) if err != nil { return err } defer tx.Rollback() if err = s.lockTaskWriter(ctx, tx); err != nil { return err } result, err := tx.ExecContext(ctx, `INSERT INTO broadcasts(`+broadcastColumns+`) VALUES(`+s.sqlValues(9)+`) ON CONFLICT(uuid) DO UPDATE SET title=excluded.title,body=excluded.body,severity=excluded.severity,starts_at_ms=excluded.starts_at_ms,ends_at_ms=excluded.ends_at_ms,updated_at_ms=excluded.updated_at_ms WHERE broadcasts.domain_uuid=excluded.domain_uuid`, b.UUID, b.DomainUUID, b.Title, b.Body, b.Severity, b.StartsAtMS, b.EndsAtMS, b.CreatedAtMS, b.UpdatedAtMS) if err != nil { return err } n, err := result.RowsAffected() if err != nil { return err } if n != 1 { return sql.ErrNoRows } return tx.Commit() } func (s *Store) DeleteBroadcast(domain, id string) (resultErr error) { defer func() { resultErr = s.sqlError("broadcast deletion", resultErr) }() ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) defer cancel() tx, err := s.DB.BeginTx(ctx, nil) if err != nil { return err } defer tx.Rollback() if err = s.lockTaskWriter(ctx, tx); err != nil { return err } if _, err = tx.Exec(`DELETE FROM notification_inbox WHERE domain_uuid=`+s.sqlParam(1)+` AND event_uuid=`+s.sqlParam(2)+` AND kind='broadcast'`, domain, id); err != nil { return err } if _, err = tx.Exec(`DELETE FROM broadcasts WHERE domain_uuid=`+s.sqlParam(1)+` AND uuid=`+s.sqlParam(2)+``, domain, id); err != nil { return err } return tx.Commit() } func (s *Store) GetBroadcast(ctx context.Context, domain, id string) (_ Broadcast, resultErr error) { defer func() { resultErr = s.sqlError("broadcast lookup", resultErr) }() ctx, cancel := context.WithTimeout(ctx, 3*time.Second) defer cancel() var b Broadcast err := scanBroadcast(s.DB.QueryRowContext(ctx, `SELECT `+broadcastColumns+` FROM broadcasts WHERE domain_uuid=`+s.sqlParam(1)+` AND uuid=`+s.sqlParam(2)+``, domain, id), &b) return b, err }