package main

import (
	"encoding/csv"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"regexp"
	"strconv"
	"strings"

	"github.com/xuri/excelize/v2"
)

func main() {
	outDir := "../../docs/database/csv"
	if err := os.MkdirAll(outDir, 0755); err != nil {
		log.Fatal(err)
	}

	if err := processMonroeApplications(outDir); err != nil {
		log.Printf("Monroe Applications error: %v", err)
	}

	if err := processMonroeDescriptions(outDir); err != nil {
		log.Printf("Monroe Descriptions error: %v", err)
	}

	if err := mergeMonroeCSV(outDir); err != nil {
		log.Printf("Merge error: %v", err)
	}

	fmt.Println("Monroe conversion completed")
}

func processMonroeApplications(outDir string) error {
	fp := "../../docs/database/aces.xlsx"
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	rows, err := f.GetRows("Products")
	if err != nil {
		return err
	}

	csvFile := filepath.Join(outDir, "monroe_applications.csv")
	file, err := os.Create(csvFile)
	if err != nil {
		return err
	}
	defer file.Close()

	writer := csv.NewWriter(file)
	writer.Comma = ';'

	writer.Write([]string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "pos", "img_url"})
	writer.Flush()

	count := 0
	for i, row := range rows {
		if i == 0 || len(row) < 20 {
			continue
		}

		make := cleanStr(row[2])
		model := cleanStr(row[3])
		yearStr := cleanStr(row[4])
		product := cleanStr(row[5])
		partnumber := cleanStr(row[6])
		mfrLabel := cleanStr(row[7])
		position := cleanStr(row[8])
		qty := cleanStr(row[18])

		if make == "" || model == "" || partnumber == "" {
			continue
		}

		year, _ := strconv.Atoi(yearStr)
		if year == 0 {
			year = 2025
		}

		writer.Write([]string{
			partnumber,
			qty,
			product,
			make,
			model,
			strconv.Itoa(year),
			strconv.Itoa(year),
			position,
			mfrLabel,
		})
		count++
	}
	writer.Flush()
	fmt.Printf("Monroe Applications: %d rows\n", count)
	return nil
}

func processMonroeDescriptions(outDir string) error {
	fp := "../../docs/database/aces.xlsx"
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	rows, err := f.GetRows("Parts")
	if err != nil {
		return err
	}

	csvFile := filepath.Join(outDir, "monroe_descriptions.csv")
	file, err := os.Create(csvFile)
	if err != nil {
		return err
	}
	defer file.Close()

	writer := csv.NewWriter(file)
	writer.Comma = ';'

	writer.Write([]string{"partnumber", "description"})
	writer.Flush()

	count := 0
	for i, row := range rows {
		if i == 0 || len(row) < 3 {
			continue
		}

		partnumber := cleanStr(row[2])
		description := cleanStr(row[0])

		if partnumber == "" {
			continue
		}

		writer.Write([]string{
			partnumber,
			description,
		})
		count++
	}
	writer.Flush()
	fmt.Printf("Monroe Descriptions: %d rows\n", count)
	return nil
}

func mergeMonroeCSV(outDir string) error {
	appsFile := filepath.Join(outDir, "monroe_applications.csv")
	descFile := filepath.Join(outDir, "monroe_descriptions.csv")
	outFile := filepath.Join(outDir, "monroe_merged.csv")

	apps, err := os.Open(appsFile)
	if err != nil {
		return err
	}
	defer apps.Close()

	descs, err := os.Open(descFile)
	if err != nil {
		return err
	}
	defer descs.Close()

	appsReader := csv.NewReader(apps)
	appsReader.Comma = ';'
	appsRecords, err := appsReader.ReadAll()
	if err != nil {
		return err
	}

	descReader := csv.NewReader(descs)
	descReader.Comma = ';'
	descRecords, err := descReader.ReadAll()
	if err != nil {
		return err
	}

	descMap := make(map[string]string)
	for i, row := range descRecords {
		if i == 0 {
			continue
		}
		if len(row) < 2 {
			continue
		}
		partnumber := row[0]
		description := row[1]
		if partnumber != "" {
			descMap[partnumber] = description
		}
	}

	out, err := os.Create(outFile)
	if err != nil {
		return err
	}
	defer out.Close()

	writer := csv.NewWriter(out)
	writer.Comma = ';'

	writer.Write([]string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "pos", "img_url", "description"})
	writer.Flush()

	count := 0
	for i, row := range appsRecords {
		if i == 0 {
			continue
		}
		if len(row) < 9 {
			continue
		}

		partnumber := row[0]
		description := descMap[partnumber]

		merged := append(row, description)
		writer.Write(merged)
		count++
	}
	writer.Flush()
	fmt.Printf("Monroe Merged: %d rows\n", count)
	return nil
}

func cleanStr(s string) string {
	s = strings.TrimSpace(s)
	s = regexp.MustCompile(`\s+`).ReplaceAllString(s, " ")
	return s
}