diff options
| -rw-r--r-- | .gitignore | 2 | ||||
| -rw-r--r-- | cmd/airlines/main.go | 60 | ||||
| -rw-r--r-- | pkg/adapters/json/json.go | 129 | ||||
| -rw-r--r-- | pkg/adapters/json/model.go | 77 | ||||
| -rw-r--r-- | pkg/db/db.go | 1 | ||||
| -rw-r--r-- | pkg/model/user.go | 76 | ||||
| -rw-r--r-- | pkg/store/db.go | 407 |
7 files changed, 695 insertions, 57 deletions
@@ -1,3 +1,5 @@ .idea .env .cache +*.json +*.yaml
\ No newline at end of file diff --git a/cmd/airlines/main.go b/cmd/airlines/main.go index 6bf097a..092a280 100644 --- a/cmd/airlines/main.go +++ b/cmd/airlines/main.go @@ -1,15 +1,15 @@ package main import ( + "context" + "encoding/json" "fmt" "os" - "time" - "github.com/joho/godotenv" - "gorm.io/driver/postgres" - "gorm.io/gorm" + ljson "airlines/pkg/adapters/json" + "airlines/pkg/store" - "airlines/pkg/model" + "github.com/joho/godotenv" ) func main() { @@ -19,44 +19,26 @@ func main() { fmt.Println(err) } - dsn := fmt.Sprintf("postgres://%s:%s@%s:%s/%s", os.Getenv("DB_USER"), os.Getenv("DB_PASSWORD"), os.Getenv("DB_HOST"), os.Getenv("DB_PORT"), os.Getenv("DB_NAME")) - db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{}) - + store, err := store.NewStore(fmt.Sprintf("postgres://%s:%s@%s:%s/%s", os.Getenv("DB_USER"), os.Getenv("DB_PASSWORD"), os.Getenv("DB_HOST"), os.Getenv("DB_PORT"), os.Getenv("DB_NAME"))) if err != nil { - fmt.Println(err) - return - } - // db.Migrator().DropTable(&model.User{}, &model.Card{}, &model.Flight{}) // ;-) - if err := db.AutoMigrate(&model.User{}, &model.Card{}, &model.Flight{}); err != nil { - return + panic(err) } + _ = store.AutoMigrate() - jinzhuFlights := []model.Flight{ - { - Number: "SU1111", - From: "North pole", - To: "Mars", - Departure: time.Now(), - Arrival: time.Now().AddDate(0, 0, 162), - }, - } - user := model.User{ - Name: "Jinzhu", - Age: 18, - Birthday: time.Now(), - Flights: jinzhuFlights, - Cards: []model.Card{ - { - Prefix: "AA", - Number: 123, - Bonusprogramm: "AbObA airlines SuPrEmE", - Flights: jinzhuFlights, - }, - }, + // i, err := json.ImportForumProfilesJSON(context.Background(), store, "../../full.json", 16384) + // fmt.Println(i, err) + f, err := os.Open("../../full.json") + + dec := json.NewDecoder(f) + // optional: be strict about unexpected fields + // dec.DisallowUnknownFields() + + var root ljson.JsonRoot + if err := dec.Decode(&root); err != nil { + panic(err) } - // Create a single record - result := db.Create(&user) - fmt.Println(result.Error) + root.DumpToDb(context.Background(), store) + // fmt.Println(root) } diff --git a/pkg/adapters/json/json.go b/pkg/adapters/json/json.go index c9ce212..8da149e 100644 --- a/pkg/adapters/json/json.go +++ b/pkg/adapters/json/json.go @@ -1,3 +1,132 @@ package json +import ( + "bytes" + "context" + "encoding/json" + "fmt" + "strings" + "time" + "airlines/pkg/model" + "airlines/pkg/store" +) + +type DateYMD struct { + time.Time + Valid bool +} + +func (d *DateYMD) UnmarshalJSON(b []byte) error { + nb := bytes.TrimSpace(b) + if bytes.Equal(nb, []byte("null")) { + *d = DateYMD{} + return nil + } + var s string + if err := json.Unmarshal(nb, &s); err != nil { + return err + } + if s == "" { + *d = DateYMD{} + return nil + } + t, err := time.Parse("2006-01-02", s) + if err != nil { + return err + } + d.Time = t + d.Valid = true + return nil +} + +/*----- Trimmed string (for loyalty Number: " 889..." ) -----*/ + +type Trimmed string + +func (t *Trimmed) UnmarshalJSON(b []byte) error { + var raw *string + if err := json.Unmarshal(b, &raw); err != nil { + return err + } + if raw == nil { + *t = "" + return nil + } + *t = Trimmed(strings.TrimSpace(*raw)) + return nil +} + +type JsonRoot struct { + ForumProfiles []JsonProfile `json:"Forum Profiles"` +} + +type JsonProfile struct { + NickName string `json:"NickName"` + Sex model.Sex `json:"Sex"` + RegisteredFlights []JsonFlight `json:"Registered Flights"` + TravelDocuments []TravelDoc `json:"Travel Documents"` + LoyaltyProgram []JsonCard `json:"Loyality Programm"` + RealName RealName `json:"Real Name"` +} + +type RealName struct { + LastName *string `json:"Last Name"` + FirstName *string `json:"First Name"` +} + +type TravelDoc struct { + Passports any `json:"Passports"` +} + +type JsonFlight struct { + Date DateYMD `json:"Date"` + Codeshare bool `json:"Codeshare"` + Flight string `json:"Flight"` + Arrival JsonPlace `json:"Arrival"` + Departure JsonPlace `json:"Departure"` +} + +type JsonPlace struct { + City string `json:"City"` + Airport string `json:"Airport"` + Country string `json:"Country"` +} + +type JsonCard struct { + Status string `json:"Status"` + Program string `json:"programm"` + Number Trimmed `json:"Number"` +} + +func (r *JsonRoot) DumpToDb(ctx context.Context, s *store.Store) { + var err error + for _, user := range r.ForumProfiles { + dbUser := user.ToUser() + dbUser, err = s.CreateOrGetUser(ctx, dbUser) + if err != nil { + panic(err) + } + + for _, card := range user.LoyaltyProgram { + dbCard, err := card.ToCard() + if err != nil { + panic(err) + } + _, err = s.AddCardsToUser(ctx, dbUser.ID, dbCard) + // данные говно + if err != nil { + fmt.Println(err) + } + + } + + for _, flight := range user.RegisteredFlights { + dbFlight := flight.ToFlight() + _, err = s.AddFlightToUser(ctx, dbUser.ID, dbFlight) + if err != nil { + fmt.Println(err) + } + } + } +} diff --git a/pkg/adapters/json/model.go b/pkg/adapters/json/model.go new file mode 100644 index 0000000..a010367 --- /dev/null +++ b/pkg/adapters/json/model.go @@ -0,0 +1,77 @@ +package json + +import ( + "strconv" + "time" + "unicode" + + "airlines/pkg/model" +) + +/* ---------- helpers ---------- */ + +func sOrEmpty(p *string) string { + if p == nil { + return "" + } + return *p +} + +func toDateUTC(d DateYMD) time.Time { + if !d.Valid { + return time.Time{} + } + return time.Date(d.Time.Year(), d.Time.Month(), d.Time.Day(), 0, 0, 0, 0, time.UTC) +} + +func onlyDigits(s string) string { + out := make([]rune, 0, len(s)) + for _, r := range s { + if unicode.IsDigit(r) { + out = append(out, r) + } + } + return string(out) +} + +func (jp JsonProfile) ToUser() *model.User { + return &model.User{ + Name: sOrEmpty(jp.RealName.FirstName), + Surname: sOrEmpty(jp.RealName.LastName), + Nick: sOrEmpty(&jp.NickName), + Fathersname: "", + Sex: jp.Sex, + Birthday: model.SentinelBirthday(), + } +} + +func (jf JsonFlight) ToFlight() *model.Flight { + d := toDateUTC(jf.Date) + return &model.Flight{ + Number: jf.Flight, + From: jf.Departure.Airport, + FromCity: jf.Departure.City, + FromCountry: jf.Departure.Country, + To: jf.Arrival.Airport, + ToCity: jf.Arrival.City, + ToCountry: jf.Arrival.Country, + Date: d, + } +} + +func (jc JsonCard) ToCard() (*model.Card, error) { + numStr := onlyDigits(string(jc.Number)) + var num uint64 + if numStr != "" { + v, err := strconv.ParseUint(numStr, 10, 64) + if err != nil { + return nil, err + } + num = v + } + return &model.Card{ + Prefix: jc.Program, + Number: num, + Bonusprogramm: jc.Status, + }, nil +} diff --git a/pkg/db/db.go b/pkg/db/db.go deleted file mode 100644 index 3a49c63..0000000 --- a/pkg/db/db.go +++ /dev/null @@ -1 +0,0 @@ -package db diff --git a/pkg/model/user.go b/pkg/model/user.go index af42e3c..85117f6 100644 --- a/pkg/model/user.go +++ b/pkg/model/user.go @@ -1,26 +1,63 @@ package model -import "time" +import ( + "encoding/json" + "strings" + "time" +) + +const sentinelYear = 1000 + +func SentinelBirthday() time.Time { + return time.Date(sentinelYear, 1, 1, 0, 0, 0, 0, time.UTC) +} -type Sex bool +type Sex uint8 const ( - SexMale Sex = false - SexFemale Sex = true + SexUnknown Sex = 0 + SexMale Sex = 1 + SexFemale Sex = 2 ) +func (s *Sex) UnmarshalJSON(b []byte) error { + var raw string + if err := json.Unmarshal(b, &raw); err == nil { + switch strings.ToLower(strings.TrimSpace(raw)) { + case "male": + *s = SexMale + return nil + case "female": + *s = SexFemale + return nil + case "", "unknown", "null": + *s = SexUnknown + return nil + } + } + // also accept numbers in JSON + var n int + if err := json.Unmarshal(b, &n); err == nil { + *s = Sex(n) + return nil + } + *s = SexUnknown + return nil +} + type User struct { - ID uint64 `gorm:"primaryKey"` + ID uint64 `gorm:"primaryKey"` + + Nick string `gorm:"not null;uniqueIndex:uniq_user_nick"` + Name string Surname string Fathersname string - Age uint8 - - Sex Sex + Sex Sex `gorm:"type:smallint;check:sex IN (0,1,2)"` Birthday time.Time - Cards []Card `gorm:"foreignKey:UserID"` + Cards []Card `gorm:"foreignKey:UserID"` // just for compatibility Flights []Flight `gorm:"many2many:user_flights;joinForeignKey:UserID;joinReferences:FlightID"` @@ -31,10 +68,10 @@ func (User) TableName() string { return "users" } type Card struct { ID uint64 `gorm:"primaryKey"` - Prefix string - Number uint64 + Prefix string `gorm:"not null;uniqueIndex:uniq_card_identity"` + Number uint64 `gorm:"not null;uniqueIndex:uniq_card_identity"` - Bonusprogramm string + Bonusprogramm string `gorm:"not null;uniqueIndex:uniq_card_identity"` // User has multiple cards -> each card has registered flights to it Flights []Flight `gorm:"many2many:card_flights;joinForeignKey:CardID;joinReferences:FlightID"` @@ -48,11 +85,16 @@ func (Card) TableName() string { return "cards" } type Flight struct { ID uint64 `gorm:"primaryKey"` - Number string - From string - To string - Departure time.Time - Arrival time.Time + Number string `gorm:"not null;uniqueIndex:uniq_flight_identity"` + From string `gorm:"not null;uniqueIndex:uniq_flight_identity"` + FromCity string `gorm:"not null;uniqueIndex:uniq_flight_identity"` + FromCountry string `gorm:"not null;uniqueIndex:uniq_flight_identity"` + + To string `gorm:"not null;uniqueIndex:uniq_flight_identity"` + ToCity string `gorm:"not null;uniqueIndex:uniq_flight_identity"` + ToCountry string `gorm:"not null;uniqueIndex:uniq_flight_identity"` + + Date time.Time `gorm:"not null;uniqueIndex:uniq_flight_identity"` Users []User `gorm:"many2many:user_flights;joinForeignKey:FlightID;joinReferences:UserID"` Cards []Card `gorm:"many2many:card_flights;joinForeignKey:FlightID;joinReferences:CardID"` 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 +} |