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
}