json tests

5 files changed, 672 insertions, 18 deletions

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
+}