package spread

import (
	"archive/zip"
	"bytes"
	"encoding/csv"
	"encoding/xml"
	"fmt"
	"io"
	"path/filepath"
	"sort"
	"strconv"
	"strings"
)

// ParseBOM turns an uploaded file into line items, dispatching on extension
// and falling back to CSV. Supported: .csv, .tsv, .txt, .xlsx.
func ParseBOM(filename string, data []byte) ([]LineItem, error) {
	switch strings.ToLower(filepath.Ext(filename)) {
	case ".xlsx":
		rows, err := readXLSX(data)
		if err != nil {
			return nil, err
		}
		return rowsToItems(rows)
	default:
		rows, err := readCSV(data)
		if err != nil {
			return nil, err
		}
		return rowsToItems(rows)
	}
}

func readCSV(data []byte) ([][]string, error) {
	data = bytes.TrimPrefix(data, []byte("\xef\xbb\xbf")) // strip UTF-8 BOM
	r := csv.NewReader(bytes.NewReader(data))
	r.FieldsPerRecord = -1
	r.TrimLeadingSpace = true
	if bytes.Count(data, []byte{'\t'}) > bytes.Count(data, []byte{','}) {
		r.Comma = '\t'
	}
	return r.ReadAll()
}

// --- column mapping -------------------------------------------------------

// fieldAliases maps a normalized header token to the canonical field it
// feeds. Headers are matched case-insensitively with punctuation stripped.
var fieldAliases = map[string]string{
	"partnumber": "pn", "part": "pn", "partno": "pn", "pn": "pn",
	"mpn": "pn", "manufacturerpartnumber": "pn", "mfgpn": "pn", "mfrpartnumber": "pn",
	"description": "desc", "desc": "desc", "name": "desc", "partdescription": "desc", "component": "desc",
	"manufacturer": "mfr", "mfr": "mfr", "mfg": "mfr", "brand": "mfr", "maker": "mfr",
	"quantity": "qty", "qty": "qty", "quantityperbom": "qty", "extendedqty": "qty", "count": "qty",
	"unitprice": "price", "price": "price", "cost": "price", "unitcost": "price",
	"currentprice": "price", "currentunitprice": "price", "market": "price",
	"marketprice": "price", "eachprice": "price", "priceeach": "price",
}

func normHeader(s string) string {
	var b strings.Builder
	for _, r := range strings.ToLower(s) {
		if (r >= 'a' && r <= 'z') || (r >= '0' && r <= '9') {
			b.WriteRune(r)
		}
	}
	return b.String()
}

func parseNumber(s string) float64 {
	s = strings.TrimSpace(s)
	s = strings.NewReplacer("$", "", ",", "", " ", "", "€", "", "£", "").Replace(s)
	if s == "" {
		return 0
	}
	f, _ := strconv.ParseFloat(s, 64)
	return f
}

// rowsToItems finds the header row, maps its columns, and reads the rest.
func rowsToItems(rows [][]string) ([]LineItem, error) {
	hdr := -1
	var colFor map[int]string
	for i, row := range rows {
		m := map[int]string{}
		for j, cell := range row {
			if f, ok := fieldAliases[normHeader(cell)]; ok {
				m[j] = f
			}
		}
		// A valid header needs at least a part/description column plus a price.
		hasName, hasPrice := false, false
		for _, f := range m {
			if f == "pn" || f == "desc" {
				hasName = true
			}
			if f == "price" {
				hasPrice = true
			}
		}
		if hasName && hasPrice {
			hdr, colFor = i, m
			break
		}
	}
	if hdr < 0 {
		return nil, fmt.Errorf("could not find a header row with a part/description column and a price column")
	}

	var items []LineItem
	line := 0
	for _, row := range rows[hdr+1:] {
		var li LineItem
		for j, cell := range row {
			cell = strings.TrimSpace(cell)
			switch colFor[j] {
			case "pn":
				li.PartNumber = cell
			case "desc":
				li.Description = cell
			case "mfr":
				li.Manufacturer = cell
			case "qty":
				li.Quantity = parseNumber(cell)
			case "price":
				li.UnitPrice = parseNumber(cell)
			}
		}
		if li.PartNumber == "" && li.Description == "" {
			continue // blank/spacer row
		}
		if li.Quantity <= 0 {
			li.Quantity = 1
		}
		if li.Description == "" {
			li.Description = li.PartNumber
		}
		line++
		li.Line = line
		items = append(items, li)
	}
	if len(items) == 0 {
		return nil, fmt.Errorf("no line items found below the header row")
	}
	return items, nil
}

// --- minimal XLSX reader (stdlib only) ------------------------------------

type xlsxSST struct {
	SI []struct {
		T string   `xml:"t"`
		R []string `xml:"r>t"`
	} `xml:"si"`
}

type xlsxSheet struct {
	Rows []struct {
		Cells []struct {
			Ref     string   `xml:"r,attr"`
			Type    string   `xml:"t,attr"`
			V       string   `xml:"v"`
			InlineT string   `xml:"is>t"`
			InlineR []string `xml:"is>r>t"`
		} `xml:"c"`
	} `xml:"sheetData>row"`
}

// colIndex converts a cell reference's column letters ("B12" -> 1).
func colIndex(ref string) int {
	n := 0
	for i := 0; i < len(ref); i++ {
		c := ref[i]
		switch {
		case c >= 'A' && c <= 'Z':
			n = n*26 + int(c-'A'+1)
		case c >= 'a' && c <= 'z':
			n = n*26 + int(c-'a'+1)
		default:
			return n - 1
		}
	}
	return n - 1
}

func readXLSX(data []byte) ([][]string, error) {
	zr, err := zip.NewReader(bytes.NewReader(data), int64(len(data)))
	if err != nil {
		return nil, fmt.Errorf("not a valid .xlsx file: %w", err)
	}

	var shared []string
	var sheetName string
	for _, f := range zr.File {
		if f.Name == "xl/sharedStrings.xml" {
			var sst xlsxSST
			if err := unmarshalZip(f, &sst); err != nil {
				return nil, err
			}
			for _, si := range sst.SI {
				if si.T != "" {
					shared = append(shared, si.T)
				} else {
					shared = append(shared, strings.Join(si.R, ""))
				}
			}
		}
	}
	// Pick the first worksheet (lexicographically) for the demo.
	var sheetFiles []string
	for _, f := range zr.File {
		if strings.HasPrefix(f.Name, "xl/worksheets/") && strings.HasSuffix(f.Name, ".xml") {
			sheetFiles = append(sheetFiles, f.Name)
		}
	}
	if len(sheetFiles) == 0 {
		return nil, fmt.Errorf("no worksheet found in .xlsx")
	}
	sort.Strings(sheetFiles)
	sheetName = sheetFiles[0]

	var sheet xlsxSheet
	for _, f := range zr.File {
		if f.Name == sheetName {
			if err := unmarshalZip(f, &sheet); err != nil {
				return nil, err
			}
		}
	}

	var out [][]string
	for _, row := range sheet.Rows {
		var cells []string
		for _, c := range row.Cells {
			idx := colIndex(c.Ref)
			if idx < 0 {
				idx = len(cells)
			}
			for len(cells) <= idx {
				cells = append(cells, "")
			}
			var val string
			switch c.Type {
			case "s": // shared string
				if i, err := strconv.Atoi(strings.TrimSpace(c.V)); err == nil && i >= 0 && i < len(shared) {
					val = shared[i]
				}
			case "inlineStr":
				if c.InlineT != "" {
					val = c.InlineT
				} else {
					val = strings.Join(c.InlineR, "")
				}
			default:
				val = c.V
			}
			cells[idx] = val
		}
		out = append(out, cells)
	}
	return out, nil
}

func unmarshalZip(f *zip.File, v any) error {
	rc, err := f.Open()
	if err != nil {
		return err
	}
	defer rc.Close()
	b, err := io.ReadAll(rc)
	if err != nil {
		return err
	}
	return xml.Unmarshal(b, v)
}