From bb833561aa74f02970aee13cdc75973b29716491 Mon Sep 17 00:00:00 2001 From: leshe4ka46 Date: Mon, 27 Oct 2025 20:36:28 +0300 Subject: # This is a combination of 2 commits. # This is the 1st commit message: unmarshal all formats, merge them in the single table, users are truly unique # This is the commit message #2: i --- pkg/store/db.go | 442 +++++++++++++++----------------------------------------- 1 file changed, 115 insertions(+), 327 deletions(-) (limited to 'pkg/store') diff --git a/pkg/store/db.go b/pkg/store/db.go index 648bca8..6853563 100644 --- a/pkg/store/db.go +++ b/pkg/store/db.go @@ -3,32 +3,27 @@ package store import ( "context" "errors" - "fmt" "strings" "time" "airlines/pkg/model" "gorm.io/driver/postgres" - _ "gorm.io/driver/sqlite" "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), + SkipDefaultTransaction: true, // you can wrap outside for big imports + PrepareStmt: true, // statement cache + DisableNestedTransaction: true, + Logger: logger.Default.LogMode(logger.Silent), }) if err != nil { return nil, err @@ -40,369 +35,162 @@ 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 -} +/* ============================= Users ============================= */ -func (s *Store) getFlightByIdentity(ctx context.Context, f *model.Flight) (*model.Flight, error) { - if strings.TrimSpace(f.Number) == "" { - return nil, gorm.ErrRecordNotFound +func (s *Store) SaveUser(ctx context.Context, in *model.User) (*model.User, error) { + if in == nil { + return nil, errors.New("nil user") } - - 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) { + in.Nick = strings.TrimSpace(in.Nick) + in.Name = strings.TrimSpace(in.Name) + in.Surname = strings.TrimSpace(in.Surname) + in.Fathersname = strings.TrimSpace(in.Fathersname) if in.Birthday.IsZero() { - in.Birthday = time.Date(1900, 1, 1, 0, 0, 0, 0, time.UTC) + in.Birthday = model.SentinelBirthday() } - in.Birthday = in.Birthday.UTC().Truncate(time.Second) + in.Birthday = in.Birthday.In(time.UTC) - var out model.User - err := s.withTx(ctx, func(tx *gorm.DB) error { - // 1) Try by unique nick (if provided) + out := &model.User{} + err := s.DB.WithContext(ctx).Transaction(func(tx *gorm.DB) error { + // 1) Try by Nick if in.Nick != "" { - if err := tx.Where("nick = ?", in.Nick).First(&out).Error; err == nil { + 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 { + // 2) Fallback by identity tuple + q := tx.Where("name = ? AND surname = ?", in.Name, in.Surname) + if in.Fathersname != "" { + q = q.Where("fathersname = ?", in.Fathersname) + } else { + q = q.Where("(fathersname IS NULL OR fathersname = '')") + } + if in.Birthday.Year() != model.SentinelBirthday().Year() { + q = q.Where("birthday = ?", in.Birthday) + } + if err := q.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 { + // 3) Not found → create + if err := tx.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 + *out = *in + return nil }) - if err != nil { - return nil, err - } - return &out, nil + return out, err } -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 - } +/* ============================= Cards ============================= */ +/* Card→User is a plain FK (cards.user_id). Keep it. */ - // 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) - } +func (s *Store) SaveCard(ctx context.Context, in *model.Card) (*model.Card, error) { + if in == nil { + return nil, errors.New("nil card") + } + in.Prefix = strings.TrimSpace(in.Prefix) + in.Bonusprogramm = strings.TrimSpace(in.Bonusprogramm) + if in.Prefix == "" || in.Bonusprogramm == "" { + return nil, errors.New("invalid card: empty prefix or bonusprogramm") + } - // 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 { + out := &model.Card{} + err := s.DB.WithContext(ctx).Transaction(func(tx *gorm.DB) error { + // find by unique triple + if err := tx.Where("prefix = ? AND number = ? AND bonusprogramm = ?", + in.Prefix, in.Number, in.Bonusprogramm).First(out).Error; err == nil { + // Link to user once if requested and not yet linked + if in.UserID != 0 && out.UserID == 0 { + if err := tx.Model(out).Update("user_id", in.UserID).Error; err != nil { return err } + out.UserID = in.UserID } - - 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) + // refuse stealing if different user already set + if in.UserID != 0 && out.UserID != 0 && out.UserID != in.UserID { + return errors.New("card already linked to another user") } - } - - // 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 nil + } else if !errors.Is(err, gorm.ErrRecordNotFound) { 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 { + // not found → create (includes FK if provided) + if err := tx.Create(in).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 - } - } - + *out = *in 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 + return out, err } -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) { +/* ============================= Flights ============================= */ +/* + Find/create by (number, from, to, date). Do NOT write user_id/card_id columns. + Use associations to link: + - flight.Users ↔ user_flights + - flight.Cards ↔ card_flights + This avoids “raw IDs” on the flight row and uses the relations instead. +*/ + +func (s *Store) SaveFlight(ctx context.Context, in *model.Flight) (*model.Flight, error) { + if in == nil { + return nil, errors.New("nil flight") + } + in.Date = in.Date.In(time.UTC) + + out := &model.Flight{} + err := s.DB.WithContext(ctx).Transaction(func(tx *gorm.DB) error { + // Try to find existing + if err := tx.Where(`"number" = ? AND "from" = ? AND "to" = ? AND "date" = ?`, + in.Number, in.From, in.To, in.Date).First(out).Error; err == nil { + // found: do NOT update columns; only link relations below + } else if errors.Is(err, gorm.ErrRecordNotFound) { + // Not found → create (no user_id/card_id columns are written) + cre := model.Flight{ + Number: in.Number, + From: in.From, + To: in.To, + Date: in.Date, + } + if err := tx.Clauses(clause.OnConflict{DoNothing: true}).Create(&cre).Error; err != nil { 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) + // If conflict, fetch the existing one + if cre.ID == 0 { + if err := tx.Where(`"number" = ? AND "from" = ? AND "to" = ? AND "date" = ?`, + in.Number, in.From, in.To, in.Date).First(&cre).Error; err != nil { + return err + } } + *out = cre + } else { + return err + } - // 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 { + // ---- Link relations via associations (no raw IDs in the row) ---- + // Link to User (many-to-many) if caller provided a UserID + if in.UserID != 0 { + u := model.User{ID: in.UserID} + // Avoid dup join rows by adding a unique index on (user_id, flight_id). + if err := tx.Model(out).Association("Users").Append(&u); 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 { + } + // Link to Card (many-to-many) + if in.CardID != 0 { + c := model.Card{ID: in.CardID} + if err := tx.Model(out).Association("Cards").Append(&c); 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 + return out, err } -- cgit v1.2.3