3 files changed, 297 insertions, 0 deletions
diff --git a/pkg/adapters/xlsx/model.go b/pkg/adapters/xlsx/model.go
new file mode 100644
index 0000000..d8c5194
--- /dev/null
+++ b/pkg/adapters/xlsx/model.go
@@ -0,0 +1,138 @@
+package xlsx
+
+import (
+	"errors"
+	"regexp"
+	"strconv"
+	"strings"
+	"time"
+
+	"github.com/leonm1/airports-go"
+)
+
+type Ticket struct {
+	Sheet        string
+	Passenger    string
+	Title        string
+	FlightNumber string
+	FromCity     string
+	ToCity       string
+	FromAirport  string
+	ToAirport    string
+	FlightDate   string // (raw, expected YYYY-MM-DD; Excel text may start with ')
+	FlightTime   string // (raw, expected HH-MM or HH:MM; Excel text may start with ')
+	PNR          string
+	Card         string
+	TicketNumber string //  (may have a leading ' in Excel)
+}
+
+func (t Ticket) DateTime() (time.Time, *time.Location, error) {
+	loc := t.inferLocationFromAirports()
+	date := strings.TrimLeft(strings.TrimSpace(t.FlightDate), "'")
+	hm := strings.TrimLeft(strings.TrimSpace(t.FlightTime), "'")
+	hm = strings.ReplaceAll(hm, "-", ":")
+
+	if date == "" || hm == "" {
+		return time.Time{}, loc, errors.New("missing FlightDate or FlightTime")
+	}
+	ts, err := time.ParseInLocation("2006-01-02 15:04", date+" "+hm, loc)
+	return ts, loc, err
+}
+
+func (t Ticket) inferLocationFromAirports() *time.Location {
+	if loc := iataToLocation(t.FromAirport); loc != nil {
+		return loc
+	}
+	if loc := iataToLocation(t.ToAirport); loc != nil {
+		return loc
+	}
+	return time.Local
+}
+
+func iataToLocation(code string) *time.Location {
+	iata := strings.ToUpper(strings.TrimSpace(code))
+	if len(iata) != 3 {
+		return nil
+	}
+	ap, err := airports.LookupIATA(iata)
+	if err != nil {
+		return nil
+	}
+	// Prefer IANA tz name
+	if tz := strings.TrimSpace(ap.Tz); tz != "" && tz != `\N` {
+		if loc, err := time.LoadLocation(tz); err == nil {
+			return loc
+		}
+	}
+	// Fallback: fixed offset (no DST)
+	if ap.Timezone != 0 {
+		sec := int(ap.Timezone * 3600.0)
+		return time.FixedZone("UTC"+offsetLabel(sec), sec)
+	}
+	return nil
+}
+
+func offsetLabel(sec int) string {
+	sign := "+"
+	if sec < 0 {
+		sign = "-"
+		sec = -sec
+	}
+	h := sec / 3600
+	m := (sec % 3600) / 60
+	return sign + two(h) + ":" + two(m)
+}
+func two(x int) string {
+	if x < 10 {
+		return "0" + strconv.Itoa(x)
+	}
+	return strconv.Itoa(x)
+}
+
+func parseCardLine(s string) (prefix string, number uint64, bonus string) {
+	raw := strings.TrimSpace(s)
+	if raw == "" {
+		return "", 0, ""
+	}
+	// number = last run of digits
+	if m := regexp.MustCompile(`(\d{3,})\D*$`).FindStringSubmatch(raw); len(m) == 2 {
+		if n, err := strconv.ParseUint(m[1], 10, 64); err == nil {
+			number = n
+		}
+	}
+
+	// tokens (letters with '-', '/', apostrophes)
+	tokRe := regexp.MustCompile(`[A-Za-z][A-Za-z'/-]*`)
+	toks := tokRe.FindAllString(s, -1)
+
+	// prefix = first 2–3 letter all-caps-ish token
+	for _, t := range toks {
+		u := strings.ToUpper(t)
+		if len(u) >= 2 && len(u) <= 3 && regexp.MustCompile(`^[A-Z]{2,3}$`).MatchString(u) {
+			prefix = u
+			break
+		}
+	}
+	// bonus = all tokens except prefix
+	words := []string{}
+	for _, t := range toks {
+		if strings.ToUpper(t) == prefix {
+			continue
+		}
+		words = append(words, t)
+	}
+	if len(words) > 0 {
+		bonus = strings.Join(words, " ")
+	}
+	if bonus == "" && prefix != "" {
+		bonus = prefix
+	}
+	return
+}
+
+func firstNonEmpty(a, b string) string {
+	if strings.TrimSpace(a) != "" {
+		return a
+	}
+	return b
+}
diff --git a/pkg/adapters/xlsx/registry.go b/pkg/adapters/xlsx/registry.go
new file mode 100644
index 0000000..46c395e
--- /dev/null
+++ b/pkg/adapters/xlsx/registry.go
@@ -0,0 +1,69 @@
+package xlsx
+
+import (
+	"fmt"
+	"strings"
+
+	"airlines/pkg/model"
+	"airlines/pkg/names"
+
+	"github.com/leonm1/airports-go"
+)
+
+func (t Ticket) ToUser() (model.User, error) {
+	fio, err := names.ParseLatinName(t.Passenger)
+	if err != nil {
+		return model.User{}, fmt.Errorf("%v %s", t.Sheet, err.Error())
+	}
+	sex := names.GenderFromTitle(t.Title)
+
+	u := model.User{
+		Nick:        "",
+		Name:        fio.First,
+		Surname:     fio.Last,
+		Fathersname: fio.Patronymic,
+		Sex:         sex,
+	}
+	return u, nil
+}
+
+func (t Ticket) ToCard() (model.Card, error) {
+	prefix, number, bonus := parseCardLine(t.Card)
+	if number == 0 && prefix == "" && bonus == "" {
+		return model.Card{}, nil
+	}
+	return model.Card{
+		Prefix:        prefix,
+		Number:        number,
+		Bonusprogramm: "",
+	}, nil
+}
+
+func (t Ticket) ToFlight() (model.Flight, error) {
+	// Resolve IATA records
+	fromIATA := strings.ToUpper(strings.TrimSpace(t.FromAirport))
+	toIATA := strings.ToUpper(strings.TrimSpace(t.ToAirport))
+
+	fromRec, _ := airports.LookupIATA(fromIATA)
+	toRec, _ := airports.LookupIATA(toIATA)
+
+	fromCity := firstNonEmpty(strings.TrimSpace(t.FromCity), fromRec.City)
+	toCity := firstNonEmpty(strings.TrimSpace(t.ToCity), toRec.City)
+
+	fromCountry := fromRec.Country
+	toCountry := toRec.Country
+	departUTC, _, err := t.DateTime()
+	if err != nil {
+		return model.Flight{}, err
+	}
+	return model.Flight{
+		Number:      strings.TrimSpace(t.FlightNumber),
+		From:        fromIATA,
+		FromCity:    fromCity,
+		FromCountry: fromCountry,
+		To:          toIATA,
+		ToCity:      toCity,
+		ToCountry:   toCountry,
+		Date:        departUTC,
+	}, nil
+}
diff --git a/pkg/adapters/xlsx/xlsx.go b/pkg/adapters/xlsx/xlsx.go
new file mode 100644
index 0000000..6ef9baa
--- /dev/null
+++ b/pkg/adapters/xlsx/xlsx.go
@@ -0,0 +1,90 @@
+package xlsx
+
+import (
+	"fmt"
+	"strings"
+
+	"github.com/xuri/excelize/v2"
+)
+
+func UnmarshallXlsxFile(fname string) ([]Ticket, error) {
+	var err error
+	f, err := excelize.OpenFile(fname)
+	if err != nil {
+		return nil, err
+	}
+	defer func() {
+		if err = f.Close(); err != nil {
+			fmt.Println(err)
+		}
+	}()
+
+	get := func(sheet, cell string) (string, error) {
+		v, err := f.GetCellValue(sheet, cell)
+		if err != nil {
+			return "", fmt.Errorf("%s %s: %w", sheet, cell, err)
+		}
+		v = strings.Trim(v, " `'\"")
+		return v, nil
+	}
+
+	sheetMap := f.GetSheetMap()
+	tickets := make([]Ticket, 0, len(sheetMap))
+
+	for _, sheet := range sheetMap {
+		t := Ticket{}
+		t.Sheet = sheet
+
+		t.Passenger, err = get(sheet, "B3")
+		if err != nil {
+			return nil, err
+		}
+		t.Title, err = get(sheet, "A3")
+		if err != nil {
+			return nil, err
+		}
+		t.FlightNumber, err = get(sheet, "A5")
+		if err != nil {
+			return nil, err
+		}
+		t.FromCity, err = get(sheet, "D5")
+		if err != nil {
+			return nil, err
+		}
+		t.ToCity, err = get(sheet, "H5")
+		if err != nil {
+			return nil, err
+		}
+		t.FromAirport, err = get(sheet, "D7")
+		if err != nil {
+			return nil, err
+		}
+		t.ToAirport, err = get(sheet, "H7")
+		if err != nil {
+			return nil, err
+		}
+		t.FlightDate, err = get(sheet, "A9")
+		if err != nil {
+			return nil, err
+		}
+		t.FlightTime, err = get(sheet, "C9")
+		if err != nil {
+			return nil, err
+		}
+		t.PNR, err = get(sheet, "B13")
+		if err != nil {
+			return nil, err
+		}
+		t.Card, err = get(sheet, "F3")
+		if err != nil {
+			return nil, err
+		}
+		t.TicketNumber, err = get(sheet, "E13")
+		if err != nil {
+			return nil, err
+		}
+
+		tickets = append(tickets, t)
+	}
+	return tickets, nil
+}