package main

import (
	"encoding/csv"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"regexp"
	"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 := processCano(outDir); err != nil {
		log.Printf("Cano error: %v", err)
	}

	fmt.Println("Cano conversion completed")
}

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

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

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

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

	writer.Write([]string{"codigo", "aplicacion", "posicion", "birlos"})
	writer.Flush()

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

		codigo := cleanStr(row[1])
		aplicacion := cleanStr(row[8])
		posicion := cleanStr(row[10])
		birlos := cleanStr(row[11])

		if codigo == "" || aplicacion == "" {
			continue
		}

		writer.Write([]string{
			codigo,
			aplicacion,
			posicion,
			birlos,
		})
		count++
	}
	writer.Flush()
	fmt.Printf("Cano: %d rows exported to canoe_hoja1.csv\n", count)
	return nil
}

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