path: root/pkg/store/db.go

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),
	})
	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
}