105 lines
3.5 KiB
Go
105 lines
3.5 KiB
Go
package repo
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"errors"
|
|
"strconv"
|
|
"time"
|
|
|
|
"effective_mobile_test_go_api/internal/model"
|
|
|
|
"github.com/google/uuid"
|
|
)
|
|
|
|
type SubscriptionRepo struct {
|
|
db *sql.DB
|
|
}
|
|
|
|
func NewSubscriptionRepo(db *sql.DB) *SubscriptionRepo {
|
|
return &SubscriptionRepo{db: db}
|
|
}
|
|
|
|
func (r *SubscriptionRepo) Create(ctx context.Context, s *model.Subscription) error {
|
|
query := `INSERT INTO subscriptions (service_name, price, user_id, start_date, end_date) VALUES ($1, $2, $3, $4, $5) RETURNING id`
|
|
var id int
|
|
err := r.db.QueryRowContext(ctx, query, s.ServiceName, s.Price, s.UserID, s.StartDate, s.EndDate).Scan(&id)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
s.ID = id
|
|
return nil
|
|
}
|
|
|
|
func (r *SubscriptionRepo) GetByID(ctx context.Context, id int) (*model.Subscription, error) {
|
|
query := `SELECT id, service_name, price, user_id, start_date, end_date FROM subscriptions WHERE id = $1`
|
|
s := &model.Subscription{}
|
|
err := r.db.QueryRowContext(ctx, query, id).Scan(&s.ID, &s.ServiceName, &s.Price, &s.UserID, &s.StartDate, &s.EndDate)
|
|
if err == sql.ErrNoRows {
|
|
return nil, errors.New("подписка не найдена")
|
|
}
|
|
return s, err
|
|
}
|
|
|
|
func (r *SubscriptionRepo) Update(ctx context.Context, s *model.Subscription) error {
|
|
query := `UPDATE subscriptions SET service_name=$1, price=$2, user_id=$3, start_date=$4, end_date=$5 WHERE id=$6`
|
|
res, err := r.db.ExecContext(ctx, query, s.ServiceName, s.Price, s.UserID, s.StartDate, s.EndDate, s.ID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
rowsAffected, err := res.RowsAffected()
|
|
if err != nil || rowsAffected == 0 {
|
|
return errors.New("подписка не найдена или не обновлена")
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *SubscriptionRepo) Delete(ctx context.Context, id int) error {
|
|
query := `DELETE FROM subscriptions WHERE id = $1`
|
|
res, err := r.db.ExecContext(ctx, query, id)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
rowsAffected, err := res.RowsAffected()
|
|
if err != nil || rowsAffected == 0 {
|
|
return errors.New("подписка не найдена или не удалена")
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *SubscriptionRepo) List(ctx context.Context) ([]*model.Subscription, error) {
|
|
query := `SELECT id, service_name, price, user_id, start_date, end_date FROM subscriptions`
|
|
rows, err := r.db.QueryContext(ctx, query)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var subscriptions []*model.Subscription
|
|
for rows.Next() {
|
|
s := &model.Subscription{}
|
|
if err := rows.Scan(&s.ID, &s.ServiceName, &s.Price, &s.UserID, &s.StartDate, &s.EndDate); err != nil {
|
|
return nil, err
|
|
}
|
|
subscriptions = append(subscriptions, s)
|
|
}
|
|
return subscriptions, nil
|
|
}
|
|
|
|
func (r *SubscriptionRepo) SumPrice(ctx context.Context, userID *uuid.UUID, serviceName string, startPeriod, endPeriod time.Time) (int, error) {
|
|
baseQuery := `SELECT COALESCE(SUM(price), 0) FROM subscriptions WHERE start_date >= $1 AND start_date <= $2`
|
|
args := []interface{}{startPeriod, endPeriod}
|
|
i := 3
|
|
if userID != nil {
|
|
baseQuery += ` AND user_id = $` + strconv.Itoa(i)
|
|
args = append(args, *userID)
|
|
i++
|
|
}
|
|
if serviceName != "" {
|
|
baseQuery += ` AND service_name = $` + strconv.Itoa(i)
|
|
args = append(args, serviceName)
|
|
}
|
|
var sum int
|
|
err := r.db.QueryRowContext(ctx, baseQuery, args...).Scan(&sum)
|
|
return sum, err
|
|
}
|