1 files changed, 407 insertions, 0 deletions
diff --git a/pkg/store/db.go b/pkg/store/db.go
new file mode 100644
index 0000000..802b4ec
--- /dev/null
+++ b/pkg/store/db.go
@@ -0,0 +1,407 @@
+package store
+
+import (
+	"context"
+	"errors"
+	"fmt"
+	"strings"
+	"time"
+
+	"airlines/pkg/model"
+
+	"gorm.io/driver/postgres"
+	"gorm.io/gorm"
+	"gorm.io/gorm/clause"
+	"gorm.io/gorm/logger"
+)
+
+func normTime(t time.Time) time.Time {
+	return t.UTC().Truncate(time.Second)
+}
+
+func normDateDay(t time.Time) time.Time { return t.UTC().Truncate(24 * time.Hour) }
+
+type Store struct {
+	DB *gorm.DB
+}
+
+func NewStore(dsn string) (*Store, error) {
+	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
+		Logger: logger.Default.LogMode(logger.Silent),
+	})
+	if err != nil {
+		return nil, err
+	}
+	return &Store{DB: db}, nil
+}
+
+func (s *Store) AutoMigrate() error {
+	return s.DB.AutoMigrate(&model.User{}, &model.Card{}, &model.Flight{})
+}
+
+func (s *Store) withTx(ctx context.Context, fn func(tx *gorm.DB) error) error {
+	return s.DB.WithContext(ctx).Transaction(fn)
+}
+
+func (s *Store) getUserByID(ctx context.Context, id uint64) (*model.User, error) {
+	var u model.User
+	if err := s.DB.WithContext(ctx).First(&u, id).Error; err != nil {
+		return nil, err
+	}
+	return &u, nil
+}
+
+func (s *Store) getUserByNick(ctx context.Context, nick string) (*model.User, error) {
+	var u model.User
+	if err := s.DB.WithContext(ctx).Where("nick = ?", nick).First(&u).Error; err != nil {
+		return nil, err
+	}
+	return &u, nil
+}
+
+func (s *Store) getCardByIdentity(ctx context.Context, prefix string, number uint64, bp string) (*model.Card, error) {
+	var c model.Card
+	err := s.DB.WithContext(ctx).
+		Where("prefix = ? AND number = ? AND bonusprogramm = ?", prefix, number, bp).
+		First(&c).Error
+	if err != nil {
+		return nil, err
+	}
+	return &c, nil
+}
+
+func (s *Store) getFlightByIdentity(ctx context.Context, f *model.Flight) (*model.Flight, error) {
+	if strings.TrimSpace(f.Number) == "" {
+		return nil, gorm.ErrRecordNotFound
+	}
+
+	q := s.DB.WithContext(ctx).Model(&model.Flight{})
+
+	q = q.Where("number = ?", f.Number)
+
+	if f.From != "" {
+		q = q.Where(`"from" = ?`, f.From)
+	}
+	if f.FromCity != "" {
+		q = q.Where("from_city = ?", f.FromCity)
+	}
+	if f.FromCountry != "" {
+		q = q.Where("from_country = ?", f.FromCountry)
+	}
+	if f.To != "" {
+		q = q.Where(`"to" = ?`, f.To)
+	}
+	if f.ToCity != "" {
+		q = q.Where("to_city = ?", f.ToCity)
+	}
+	if f.ToCountry != "" {
+		q = q.Where("to_country = ?", f.ToCountry)
+	}
+	if !f.Date.IsZero() {
+		q = q.Where(`"date" = ?`, normDateDay(f.Date))
+	}
+
+	var out model.Flight
+	if err := q.First(&out).Error; err != nil {
+		return nil, err
+	}
+	return &out, nil
+}
+
+func (s *Store) CreateOrGetUser(ctx context.Context, in *model.User) (*model.User, error) {
+	if in.Birthday.IsZero() {
+		in.Birthday = time.Date(1900, 1, 1, 0, 0, 0, 0, time.UTC)
+	}
+	in.Birthday = in.Birthday.UTC().Truncate(time.Second)
+
+	var out model.User
+	err := s.withTx(ctx, func(tx *gorm.DB) error {
+		// 1) Try by unique nick (if provided)
+		if in.Nick != "" {
+			if err := tx.Where("nick = ?", in.Nick).First(&out).Error; err == nil {
+				return nil
+			} else if !errors.Is(err, gorm.ErrRecordNotFound) {
+				return err
+			}
+
+			// 2) Nick not found — check composite identity
+			if in.Name != "" || in.Surname != "" || in.Fathersname != "" {
+				tmp := tx
+				if in.Name != "" {
+					tmp = tmp.Where("name = ?", in.Name)
+				}
+				if in.Surname != "" {
+					tmp = tmp.Where("surname = ?", in.Surname)
+				}
+				if in.Fathersname != "" {
+					tmp = tmp.Where("fathersname = ?", in.Fathersname)
+				}
+
+				if err := tmp.First(&out).Error; err == nil {
+					if out.Nick == "" {
+						_ = tx.Model(&out).Update("nick", in.Nick).Error
+					}
+					return nil
+				} else if !errors.Is(err, gorm.ErrRecordNotFound) {
+					return err
+				}
+			}
+
+			// 3) Neither nick nor composite exist — create with full payload
+			if err := tx.Create(in).Error; err != nil {
+				return err
+			}
+			// Prefer selecting by nick first; fallback to composite if needed
+			if err := tx.Where("nick = ?", in.Nick).First(&out).Error; err == nil {
+				return nil
+			}
+			return tx.Where(
+				"name = ? AND surname = ? AND fathersname = ? AND sex = ? AND birthday = ?",
+				in.Name, in.Surname, in.Fathersname, in.Sex, in.Birthday,
+			).First(&out).Error
+		}
+
+		// (Nick empty) — try composite identity
+		if err := tx.Where(
+			"name = ? AND surname = ? AND fathersname = ? AND sex = ? AND birthday = ?",
+			in.Name, in.Surname, in.Fathersname, in.Sex, in.Birthday,
+		).First(&out).Error; err == nil {
+			return nil
+		} else if !errors.Is(err, gorm.ErrRecordNotFound) {
+			return err
+		}
+
+		// Create by composite (no nick)
+		if err := tx.Clauses(clause.OnConflict{DoNothing: true}).Create(in).Error; err != nil {
+			return err
+		}
+		return tx.Where(
+			"name = ? AND surname = ? AND fathersname = ? AND sex = ? AND birthday = ?",
+			in.Name, in.Surname, in.Fathersname, in.Sex, in.Birthday,
+		).First(&out).Error
+	})
+	if err != nil {
+		return nil, err
+	}
+	return &out, nil
+}
+
+func (s *Store) AddCardsToUser(ctx context.Context, userID uint64, cards ...*model.Card) ([]model.Card, error) {
+	var attached []model.Card
+
+	err := s.withTx(ctx, func(tx *gorm.DB) error {
+		u := model.User{ID: userID}
+		if err := tx.First(&u).Error; err != nil {
+			return err
+		}
+
+		for i := range cards {
+			c := cards[i]
+			c.UserID = userID
+
+			// Upsert by card identity composite (prefix, number, bonusprogramm)
+			// We DoNothing on conflict and then SELECT.
+			if err := tx.Clauses(clause.OnConflict{DoNothing: true}).Create(c).Error; err != nil && !errors.Is(err, gorm.ErrDuplicatedKey) {
+				return err
+			}
+
+			var dbCard model.Card
+			if err := tx.
+				Where("prefix = ? AND number = ? AND bonusprogramm = ?", c.Prefix, c.Number, c.Bonusprogramm).
+				First(&dbCard).Error; err != nil {
+				return err
+			}
+
+			// If card is already bound to another user, fail explicitly
+			if dbCard.UserID != 0 && dbCard.UserID != u.ID {
+				return fmt.Errorf("card %+v already belongs to a user %+v", c)
+			}
+
+			// Attach to this user if not already
+			if dbCard.UserID != u.ID {
+				if err := tx.Model(&dbCard).Update("user_id", u.ID).Error; err != nil {
+					return err
+				}
+			}
+
+			attached = append(attached, dbCard)
+		}
+		return nil
+	})
+
+	if err != nil {
+		return nil, err
+	}
+	return attached, nil
+}
+
+func (s *Store) AddFlightForUserAndCard(ctx context.Context, userID, cardID uint64, in *model.Flight) (*model.Flight, error) {
+	// ensure day-only Date consistency
+	in.Date = normDateDay(in.Date)
+
+	var out model.Flight
+	err := s.withTx(ctx, func(tx *gorm.DB) error {
+		// 1) ensure user exists
+		var u model.User
+		if err := tx.First(&u, userID).Error; err != nil {
+			return err
+		}
+
+		// 2) if cardID provided, ensure card exists and belongs to the user
+		var c model.Card
+		if cardID != 0 {
+			if err := tx.First(&c, cardID).Error; err != nil {
+				return err
+			}
+			if c.UserID != u.ID {
+				return fmt.Errorf("card %d does not belong to user %d", c.ID, u.ID)
+			}
+		}
+
+		// 3) upsert/create flight by its unique identity
+		// If you actually created the unique index with name "uniq_flight_identity",
+		// you can use the Constraint form. Otherwise list Columns explicitly.
+		if err := tx.Clauses(clause.OnConflict{
+			DoNothing: true,
+		}).Create(in).Error; err != nil {
+			return err
+		}
+
+		// 4) re-select by identity
+		if err := tx.Where(
+			`number = ? AND "from" = ? AND from_city = ? AND from_country = ? AND "to" = ? AND to_city = ? AND to_country = ? AND "date" = ?`,
+			in.Number, in.From, in.FromCity, in.FromCountry, in.To, in.ToCity, in.ToCountry, in.Date,
+		).First(&out).Error; err != nil {
+			return err
+		}
+
+		// 5) link user <-> flight (user_flights)
+		if err := tx.Table("user_flights").
+			Clauses(clause.OnConflict{DoNothing: true}).
+			Create(map[string]any{"user_id": u.ID, "flight_id": out.ID}).Error; err != nil {
+			return err
+		}
+
+		// 6) optionally link card <-> flight (card_flights)
+		if cardID != 0 {
+			if err := tx.Table("card_flights").
+				Clauses(clause.OnConflict{DoNothing: true}).
+				Create(map[string]any{"card_id": c.ID, "flight_id": out.ID}).Error; err != nil {
+				return err
+			}
+		}
+
+		return nil
+	})
+	if err != nil {
+		return nil, err
+	}
+	return &out, nil
+}
+
+// Optional convenience: preload user’s cards & flights for display
+func (s *Store) GetUserFull(ctx context.Context, userID uint64) (*model.User, error) {
+	var u model.User
+	if err := s.DB.WithContext(ctx).
+		Preload("Cards").
+		Preload("Flights").
+		First(&u, userID).Error; err != nil {
+		return nil, err
+	}
+	return &u, nil
+}
+
+func (s *Store) AddFlightToUser(ctx context.Context, userID uint64, in *model.Flight) (*model.Flight, error) {
+	// normalize date if set
+	if !in.Date.IsZero() {
+		in.Date = normDateDay(in.Date)
+	}
+
+	var out model.Flight
+	err := s.withTx(ctx, func(tx *gorm.DB) error {
+		// 1) ensure user exists
+		var u model.User
+		if err := tx.First(&u, userID).Error; err != nil {
+			return err
+		}
+
+		// 2) try to find an existing flight using dynamic filters
+		q := tx.Model(&model.Flight{}).Where("number = ?", in.Number)
+		if in.From != "" {
+			q = q.Where(`"from" = ?`, in.From)
+		}
+		if in.FromCity != "" {
+			q = q.Where("from_city = ?", in.FromCity)
+		}
+		if in.FromCountry != "" {
+			q = q.Where("from_country = ?", in.FromCountry)
+		}
+		if in.To != "" {
+			q = q.Where(`"to" = ?`, in.To)
+		}
+		if in.ToCity != "" {
+			q = q.Where("to_city = ?", in.ToCity)
+		}
+		if in.ToCountry != "" {
+			q = q.Where("to_country = ?", in.ToCountry)
+		}
+		if !in.Date.IsZero() {
+			q = q.Where(`"date" = ?`, in.Date)
+		}
+
+		// deterministic pick if multiple match
+		if err := q.Order(`"date" DESC, id DESC`).First(&out).Error; err != nil {
+			if !errors.Is(err, gorm.ErrRecordNotFound) {
+				return err
+			}
+
+			// 3) not found -> create (requires full identity fields)
+			// Validate required identity fields before create
+			if strings.TrimSpace(in.Number) == "" ||
+				in.From == "" || in.FromCity == "" ||
+				in.To == "" || in.ToCity == "" ||
+				in.Date.IsZero() {
+				return fmt.Errorf("%+v cannot create flight: full identity required (number, from/from_city/from_country, to/to_city/to_country, date)", in)
+			}
+
+			// Upsert by unique identity
+			if err := tx.Clauses(clause.OnConflict{
+				Columns: []clause.Column{
+					{Name: "number"},
+					{Name: "from"},
+					{Name: "from_city"},
+					{Name: "from_country"},
+					{Name: "to"},
+					{Name: "to_city"},
+					{Name: "to_country"},
+					{Name: "date"},
+				},
+				DoNothing: true,
+			}).Create(in).Error; err != nil {
+				return err
+			}
+
+			// Re-select by full identity
+			if err := tx.Where(
+				`number = ? AND "from" = ? AND from_city = ? AND from_country = ? AND "to" = ? AND to_city = ? AND to_country = ? AND "date" = ?`,
+				in.Number, in.From, in.FromCity, in.FromCountry, in.To, in.ToCity, in.ToCountry, in.Date,
+			).First(&out).Error; err != nil {
+				return err
+			}
+		}
+
+		// 4) link user <-> flight (idempotent)
+		if err := tx.Table("user_flights").
+			Clauses(clause.OnConflict{DoNothing: true}).
+			Create(map[string]any{"user_id": u.ID, "flight_id": out.ID}).Error; err != nil {
+			return err
+		}
+
+		return nil
+	})
+
+	if err != nil {
+		return nil, err
+	}
+	return &out, nil
+}