package services

import (
	"archive/zip"
	"bytes"
	"strings"
	"testing"
	"time"

	"git.mchus.pro/mchus/priceforge/internal/lotmatch"
	"git.mchus.pro/mchus/priceforge/internal/models"
	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

func TestParseMXLRows(t *testing.T) {
	content := strings.Join([]string{
		`MOXCEL`,
		`{16,2,{1,1,{"ru","Папка"}},0},1,`,
		`{16,2,{1,1,{"ru","Артикул"}},0},2,`,
		`{16,2,{1,1,{"ru","Описание"}},0},3,`,
		`{16,2,{1,1,{"ru","Вендор"}},0},4,`,
		`{16,2,{1,1,{"ru","Стоимость"}},0},5,`,
		`{16,2,{1,1,{"ru","Свободно"}},0},6,`,
		`{16,2,{1,1,{"ru","Серверы"}},0},1,`,
		`{16,2,{1,1,{"ru","CPU_X"}},0},2,`,
		`{16,2,{1,1,{"ru","Процессор"}},0},3,`,
		`{16,2,{1,1,{"ru","AMD"}},0},4,`,
		`{16,2,{1,1,{"ru","125,50"}},0},5,`,
		`{16,2,{1,1,{"ru","10"}},0},6,`,
	}, "\n")

	rows, err := parseMXLRows([]byte(content))
	if err != nil {
		t.Fatalf("parseMXLRows: %v", err)
	}
	if len(rows) != 1 {
		t.Fatalf("expected 1 row, got %d", len(rows))
	}
	if rows[0].Article != "CPU_X" {
		t.Fatalf("unexpected article: %s", rows[0].Article)
	}
	if rows[0].Price != 125.50 {
		t.Fatalf("unexpected price: %v", rows[0].Price)
	}
	if rows[0].Qty != 10 {
		t.Fatalf("unexpected qty: %v", rows[0].Qty)
	}
}

func TestParseMXLRows_EmptyQtyMarkedInvalid(t *testing.T) {
	content := strings.Join([]string{
		`MOXCEL`,
		`{16,2,{1,1,{"ru","Папка"}},0},1,`,
		`{16,2,{1,1,{"ru","Артикул"}},0},2,`,
		`{16,2,{1,1,{"ru","Описание"}},0},3,`,
		`{16,2,{1,1,{"ru","Вендор"}},0},4,`,
		`{16,2,{1,1,{"ru","Стоимость"}},0},5,`,
		`{16,2,{1,1,{"ru","Свободно"}},0},6,`,
		`{16,2,{1,1,{"ru","Серверы"}},0},1,`,
		`{16,2,{1,1,{"ru","CPU_X"}},0},2,`,
		`{16,2,{1,1,{"ru","Процессор"}},0},3,`,
		`{16,2,{1,1,{"ru","AMD"}},0},4,`,
		`{16,2,{1,1,{"ru","125,50"}},0},5,`,
		`{16,2,{1,1,{"ru",""}},0},6,`,
	}, "\n")

	rows, err := parseMXLRows([]byte(content))
	if err != nil {
		t.Fatalf("parseMXLRows: %v", err)
	}
	if len(rows) != 1 {
		t.Fatalf("expected 1 row, got %d", len(rows))
	}
	if !rows[0].QtyInvalid {
		t.Fatalf("expected QtyInvalid=true for empty qty")
	}
}

func TestParseMXLRows_WithAdditionalLeadingColumn(t *testing.T) {
	content := strings.Join([]string{
		`MOXCEL`,
		`{16,2,{1,1,{"ru","код"}},0},1,`,
		`{16,3,{1,1,{"ru","Папка"}},0},2,`,
		`{16,3,{1,1,{"ru","Артикул"}},0},3,`,
		`{16,3,{1,1,{"ru","Описание"}},0},4,`,
		`{16,4,{1,1,{"ru","Вендор"}},0},5,`,
		`{16,3,{1,1,{"ru","Стоимость"}},0},6,`,
		`{16,3,{1,1,{"ru","Свободно"}},0},7,`,
		`{16,6,{1,1,{"ru","n0000001"}},0},1,`,
		`{16,6,{1,1,{"ru","Серверы"}},0},2,`,
		`{16,7,{1,1,{"ru","CPU_X"}},0},3,`,
		`{16,8,{1,1,{"ru","Процессор"}},0},4,`,
		`{16,8,{1,1,{"ru","AMD"}},0},5,`,
		`{16,9,{1,1,{"ru","125,50"}},0},6,`,
		`{16,10,{1,1,{"ru","10"}},0},7,`,
	}, "\n")

	rows, err := parseMXLRows([]byte(content))
	if err != nil {
		t.Fatalf("parseMXLRows: %v", err)
	}
	if len(rows) != 1 {
		t.Fatalf("expected 1 row, got %d", len(rows))
	}
	if rows[0].Article != "CPU_X" {
		t.Fatalf("unexpected article: %s", rows[0].Article)
	}
	if rows[0].Folder != "Серверы" {
		t.Fatalf("unexpected folder: %s", rows[0].Folder)
	}
	if rows[0].Price != 125.50 {
		t.Fatalf("unexpected price: %v", rows[0].Price)
	}
	if rows[0].Qty != 10 {
		t.Fatalf("unexpected qty: %v", rows[0].Qty)
	}
}

func TestParseXLSXRows(t *testing.T) {
	xlsx := buildMinimalXLSX(t, []string{
		"Папка", "Артикул", "Описание", "Вендор", "Стоимость", "Свободно",
	}, []string{
		"Серверы", "CPU_A", "Процессор", "AMD", "99,25", "7",
	})

	rows, err := parseXLSXRows(xlsx)
	if err != nil {
		t.Fatalf("parseXLSXRows: %v", err)
	}
	if len(rows) != 1 {
		t.Fatalf("expected 1 row, got %d", len(rows))
	}
	if rows[0].Article != "CPU_A" {
		t.Fatalf("unexpected article: %s", rows[0].Article)
	}
	if rows[0].Price != 99.25 {
		t.Fatalf("unexpected price: %v", rows[0].Price)
	}
}

func TestLotResolverPrecedenceAndConflicts(t *testing.T) {
	resolver := lotmatch.NewLotResolver(
		[]models.LotPartnumber{
			{Partnumber: "pn-1", LotName: "LOT_MAPPED"},
			{Partnumber: "pn-conflict", LotName: "LOT_A"},
			{Partnumber: "pn-conflict", LotName: "LOT_B"},
		},
		[]models.Lot{
			{LotName: "CPU_A_LONG"},
			{LotName: "CPU_A"},
			{LotName: "ABC "},
			{LotName: "ABC\t"},
		},
	)

	lot, typ, err := resolver.Resolve("pn-1")
	if err != nil || lot != "LOT_MAPPED" || typ != "mapping_table" {
		t.Fatalf("mapping_table mismatch: lot=%s typ=%s err=%v", lot, typ, err)
	}

	lot, typ, err = resolver.Resolve("cpu_a")
	if err != nil || lot != "CPU_A" || typ != "article_exact" {
		t.Fatalf("article_exact mismatch: lot=%s typ=%s err=%v", lot, typ, err)
	}

	lot, typ, err = resolver.Resolve("cpu_a_long_suffix")
	if err != nil || lot != "CPU_A_LONG" || typ != "prefix" {
		t.Fatalf("prefix mismatch: lot=%s typ=%s err=%v", lot, typ, err)
	}

	_, _, err = resolver.Resolve("abx")
	if err == nil || err != lotmatch.ErrResolveNotFound {
		t.Fatalf("expected not found error, got %v", err)
	}

	_, _, err = resolver.Resolve("pn-conflict")
	if err == nil || err != lotmatch.ErrResolveConflict {
		t.Fatalf("expected conflict, got %v", err)
	}
}

func TestImportNoValidRowsKeepsStockLog(t *testing.T) {
	db := openTestDB(t)
	if err := db.AutoMigrate(&models.StockLog{}); err != nil {
		t.Fatalf("automigrate stock_log: %v", err)
	}

	existing := models.StockLog{
		Partnumber: "CPU_A",
		Date:       time.Now(),
		Price:      10,
	}
	if err := db.Create(&existing).Error; err != nil {
		t.Fatalf("seed stock_log: %v", err)
	}

	svc := NewStockImportService(db, nil)
	headerOnly := []byte(strings.Join([]string{
		`MOXCEL`,
		`{16,2,{1,1,{"ru","Папка"}},0},1,`,
		`{16,2,{1,1,{"ru","Артикул"}},0},2,`,
		`{16,2,{1,1,{"ru","Описание"}},0},3,`,
		`{16,2,{1,1,{"ru","Вендор"}},0},4,`,
		`{16,2,{1,1,{"ru","Стоимость"}},0},5,`,
		`{16,2,{1,1,{"ru","Свободно"}},0},6,`,
	}, "\n"))

	if _, err := svc.Import("test.mxl", headerOnly, time.Now(), "tester", false, nil); err == nil {
		t.Fatalf("expected import error")
	}

	var count int64
	if err := db.Model(&models.StockLog{}).Count(&count).Error; err != nil {
		t.Fatalf("count stock_log: %v", err)
	}
	if count != 1 {
		t.Fatalf("expected stock_log unchanged, got %d rows", count)
	}
}

func TestReplaceStockLogs(t *testing.T) {
	db := openTestDB(t)
	if err := db.AutoMigrate(&models.StockLog{}); err != nil {
		t.Fatalf("automigrate stock_log: %v", err)
	}

	if err := db.Create(&models.StockLog{Partnumber: "OLD", Date: time.Now(), Price: 1}).Error; err != nil {
		t.Fatalf("seed old row: %v", err)
	}

	svc := NewStockImportService(db, nil)
	records := []models.StockLog{
		{Partnumber: "NEW_1", Date: time.Now(), Price: 2},
		{Partnumber: "NEW_2", Date: time.Now(), Price: 3},
	}

	deleted, inserted, err := svc.replaceStockLogs(records)
	if err != nil {
		t.Fatalf("replaceStockLogs: %v", err)
	}
	if deleted != 1 || inserted != 2 {
		t.Fatalf("unexpected replace stats deleted=%d inserted=%d", deleted, inserted)
	}

	var rows []models.StockLog
	if err := db.Order("partnumber").Find(&rows).Error; err != nil {
		t.Fatalf("read rows: %v", err)
	}
	if len(rows) != 2 || rows[0].Partnumber != "NEW_1" || rows[1].Partnumber != "NEW_2" {
		t.Fatalf("unexpected rows after replace: %#v", rows)
	}
}

func TestWeightedMedian(t *testing.T) {
	got := weightedMedian([]weightedPricePoint{
		{price: 10, weight: 1},
		{price: 20, weight: 3},
		{price: 50, weight: 1},
	})
	if got != 20 {
		t.Fatalf("expected weighted median 20, got %v", got)
	}
}

func TestWeightedMedianFallbackToMedianWhenNoWeights(t *testing.T) {
	got := weightedMedian([]weightedPricePoint{
		{price: 10, weight: 0},
		{price: 20, weight: 0},
		{price: 30, weight: 0},
	})
	if got != 20 {
		t.Fatalf("expected fallback median 20, got %v", got)
	}
}

func TestBuildWarehousePricelistItems_UsesPrefixResolverAndWeightedAverage(t *testing.T) {
	db := openTestDB(t)
	if err := db.AutoMigrate(&models.StockLog{}, &models.Lot{}, &models.PartnumberBookItem{}); err != nil {
		t.Fatalf("automigrate: %v", err)
	}

	if err := db.Create(&models.Lot{LotName: "CPU_A"}).Error; err != nil {
		t.Fatalf("seed lot: %v", err)
	}

	qty1 := 3.0
	qty2 := 1.0
	now := time.Now()
	rows := []models.StockLog{
		{Partnumber: "CPU_A-001", Date: now, Price: 100, Qty: &qty1},
		{Partnumber: "CPU_A-XYZ", Date: now, Price: 120, Qty: &qty2},
	}
	if err := db.Create(&rows).Error; err != nil {
		t.Fatalf("seed stock_log: %v", err)
	}

	svc := NewStockImportService(db, nil)
	items, err := svc.buildWarehousePricelistItems()
	if err != nil {
		t.Fatalf("buildWarehousePricelistItems: %v", err)
	}
	if len(items) != 1 {
		t.Fatalf("expected 1 item, got %d", len(items))
	}
	if items[0].LotName != "CPU_A" {
		t.Fatalf("expected lot CPU_A, got %s", items[0].LotName)
	}
	if items[0].Price != 105 {
		t.Fatalf("expected weighted average 105, got %v", items[0].Price)
	}
}

func TestPartnumberMappings_CatalogAndLotFallback(t *testing.T) {
	db := openTestDB(t)
	if err := db.AutoMigrate(&models.PartnumberBookItem{}, &models.Lot{}); err != nil {
		t.Fatalf("automigrate: %v", err)
	}

	mappings := []models.PartnumberBookItem{
		{Partnumber: "R750XD", LotsJSON: `[{"lot_name":"SERVER_R750","qty":1}]`},
		{Partnumber: "HDD-01", LotsJSON: `[{"lot_name":"HDD_01","qty":1}]`},
	}
	if err := db.Create(&mappings).Error; err != nil {
		t.Fatalf("seed mappings: %v", err)
	}
	if err := db.Create(&[]models.Lot{
		{LotName: "MEM_DDR5_16G_4800"},
		{LotName: "SERVER_R750"},
		{LotName: "HDD_01"},
	}).Error; err != nil {
		t.Fatalf("seed lot: %v", err)
	}

	resolver, err := lotmatch.NewMappingMatcherFromDB(db)
	if err != nil {
		t.Fatalf("NewMappingMatcherFromDB: %v", err)
	}

	if got := resolver.MatchLots("R750XD"); len(got) != 1 || got[0] != "SERVER_R750" {
		t.Fatalf("expected catalog match SERVER_R750, got %#v", got)
	}
	if got := resolver.MatchLots("HDD-01"); len(got) != 1 || got[0] != "HDD_01" {
		t.Fatalf("expected exact match HDD_01, got %#v", got)
	}
	if got := resolver.MatchLots("UNKNOWN"); len(got) != 0 {
		t.Fatalf("expected no matches, got %#v", got)
	}
	if got := resolver.MatchLots("MEM_DDR5_16G_4800"); len(got) != 1 || got[0] != "MEM_DDR5_16G_4800" {
		t.Fatalf("expected exact lot fallback, got %#v", got)
	}
}

func TestUpsertSeenRows_DeduplicatesByPartnumber(t *testing.T) {
	db := openTestDB(t)
	if err := db.AutoMigrate(&models.VendorPartnumberSeen{}); err != nil {
		t.Fatalf("automigrate seen: %v", err)
	}
	svc := NewStockImportService(db, nil)

	firstDesc := "first"
	if err := svc.upsertSeenRows(map[string]models.VendorPartnumberSeen{
		"cpu123": {
			SourceType:  "stock",
			Vendor:      "",
			Partnumber:  "CPU-123",
			Description: &firstDesc,
		},
	}); err != nil {
		t.Fatalf("upsert first: %v", err)
	}

	secondDesc := "second"
	if err := svc.upsertSeenRows(map[string]models.VendorPartnumberSeen{
		"cpu123": {
			SourceType:  "manual",
			Vendor:      "Dell",
			Partnumber:  "CPU-123",
			Description: &secondDesc,
		},
	}); err != nil {
		t.Fatalf("upsert second: %v", err)
	}

	var rows []models.VendorPartnumberSeen
	if err := db.Where("partnumber = ?", "CPU-123").Find(&rows).Error; err != nil {
		t.Fatalf("query seen: %v", err)
	}
	if len(rows) != 1 {
		t.Fatalf("expected 1 seen row for partnumber, got %d", len(rows))
	}
	if rows[0].SourceType != "stock" {
		t.Fatalf("expected source_type stock to be preserved, got %s", rows[0].SourceType)
	}
	if rows[0].Vendor != "Dell" {
		t.Fatalf("expected non-empty vendor to be promoted, got %q", rows[0].Vendor)
	}
}

func TestIsIgnoredBySeenIndex_ByPartnumberOnly(t *testing.T) {
	index := &ignoreIndex{
		exact: map[string]struct{}{
			normalizeKey("CPU-123"): {},
		},
	}
	if !isIgnoredBySeenIndex(index, "Dell", "CPU-123") {
		t.Fatalf("expected ignore match by partnumber")
	}
	if isIgnoredBySeenIndex(index, "Dell", "CPU-999") {
		t.Fatalf("expected no ignore match for different partnumber")
	}
}

func openTestDB(t *testing.T) *gorm.DB {
	t.Helper()
	db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
	if err != nil {
		t.Fatalf("open sqlite: %v", err)
	}
	return db
}

func buildMinimalXLSX(t *testing.T, headers, values []string) []byte {
	t.Helper()
	var buf bytes.Buffer
	zw := zip.NewWriter(&buf)

	write := func(name, body string) {
		w, err := zw.Create(name)
		if err != nil {
			t.Fatalf("create zip entry %s: %v", name, err)
		}
		if _, err := w.Write([]byte(body)); err != nil {
			t.Fatalf("write zip entry %s: %v", name, err)
		}
	}

	write("[Content_Types].xml", `<?xml version="1.0" encoding="UTF-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml" ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
</Types>`)
	write("_rels/.rels", `<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>`)
	write("xl/workbook.xml", `<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <sheets>
    <sheet name="Sheet1" sheetId="1" r:id="rId1" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"/>
  </sheets>
</workbook>`)
	write("xl/_rels/workbook.xml.rels", `<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
</Relationships>`)

	makeCell := func(ref, value string) string {
		escaped := strings.ReplaceAll(value, "&", "&amp;")
		escaped = strings.ReplaceAll(escaped, "<", "&lt;")
		escaped = strings.ReplaceAll(escaped, ">", "&gt;")
		return `<c r="` + ref + `" t="inlineStr"><is><t>` + escaped + `</t></is></c>`
	}

	cols := []string{"A", "B", "C", "D", "E", "F"}
	var headerCells, valueCells strings.Builder
	for i := 0; i < len(cols) && i < len(headers); i++ {
		headerCells.WriteString(makeCell(cols[i]+"1", headers[i]))
	}
	for i := 0; i < len(cols) && i < len(values); i++ {
		valueCells.WriteString(makeCell(cols[i]+"2", values[i]))
	}

	write("xl/worksheets/sheet1.xml", `<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <sheetData>
    <row r="1">`+headerCells.String()+`</row>
    <row r="2">`+valueCells.String()+`</row>
  </sheetData>
</worksheet>`)

	if err := zw.Close(); err != nil {
		t.Fatalf("close zip: %v", err)
	}
	return buf.Bytes()
}