package cron

import (
	"database/sql"
	"encoding/csv"
	"fmt"
	"io"
	"log"
	"math"
	"os"
	"path"
	"strconv"
	"strings"
	"time"

	"golang-csv/config"

	"github.com/pkg/sftp"
	"golang.org/x/crypto/ssh"
)

// SFTP config structure (you can move this to config.LoadConfig() if needed)
type SFTPConfig struct {
	Host       string
	Port       int
	User       string
	Password   string
	RemotePath string // Directory path
	LocalPath  string // Local destination path
}

// func downloadLatestCSVViaSFTP(cfg SFTPConfig) (string, error) {
// 	addr := fmt.Sprintf("%s:%d", cfg.Host, cfg.Port)
// 	sshConfig := &ssh.ClientConfig{
// 		User: cfg.User,
// 		Auth: []ssh.AuthMethod{
// 			ssh.Password(cfg.Password),
// 		},
// 		HostKeyCallback: ssh.InsecureIgnoreHostKey(), // ⚠️ Replace with secure verification in production
// 	}

// 	conn, err := ssh.Dial("tcp", addr, sshConfig)
// 	if err != nil {
// 		return "", fmt.Errorf("failed to dial SSH: %v", err)
// 	}
// 	defer conn.Close()

// 	client, err := sftp.NewClient(conn)
// 	if err != nil {
// 		return "", fmt.Errorf("failed to create SFTP client: %v", err)
// 	}
// 	defer client.Close()

// 	// List files in the remote directory
// 	files, err := client.ReadDir(cfg.RemotePath)
// 	if err != nil {
// 		return "", fmt.Errorf("failed to read remote directory: %v", err)
// 	}

// 	// Find the most recent CSV file
// 	var latestFile os.FileInfo
// 	var latestTime time.Time

// 	for _, file := range files {
// 		if file.IsDir() {
// 			continue
// 		}

// 		// Check if file has .csv extension (case insensitive)
// 		if strings.HasSuffix(strings.ToLower(file.Name()), ".csv") {
// 			if file.ModTime().After(latestTime) {
// 				latestTime = file.ModTime()
// 				latestFile = file
// 			}
// 		}
// 	}

// 	if latestFile == nil {
// 		return "", fmt.Errorf("no CSV files found in directory")
// 	}

// 	// Construct full remote path
// 	remoteFilePath := path.Join(cfg.RemotePath, latestFile.Name())

// 	// Open remote file
// 	srcFile, err := client.Open(remoteFilePath)
// 	if err != nil {
// 		return "", fmt.Errorf("failed to open remote CSV file: %v", err)
// 	}
// 	defer srcFile.Close()

// 	// Create local file
// 	localFile, err := os.Create(cfg.LocalPath)
// 	if err != nil {
// 		return "", fmt.Errorf("failed to create local file: %v", err)
// 	}
// 	defer localFile.Close()

// 	// Copy remote to local
// 	_, err = io.Copy(localFile, srcFile)
// 	if err != nil {
// 		return "", fmt.Errorf("failed to copy file from SFTP: %v", err)
// 	}

// 	log.Printf("Downloaded latest CSV file: %s (modified: %s)", latestFile.Name(), latestTime)
// 	return latestFile.Name(), nil
// }

func downloadLatestCSVViaSFTP(cfg SFTPConfig) (string, error) {
	addr := fmt.Sprintf("%s:%d", cfg.Host, cfg.Port)
	sshConfig := &ssh.ClientConfig{
		User: cfg.User,
		Auth: []ssh.AuthMethod{
			ssh.Password(cfg.Password),
		},
		HostKeyCallback: ssh.InsecureIgnoreHostKey(),
	}

	conn, err := ssh.Dial("tcp", addr, sshConfig)
	if err != nil {
		return "", fmt.Errorf("failed to dial SSH: %v", err)
	}
	defer conn.Close()

	client, err := sftp.NewClient(conn)
	if err != nil {
		return "", fmt.Errorf("failed to create SFTP client: %v", err)
	}
	defer client.Close()

	// Ganti menjadi folder path (bukan file)
	remoteDir := path.Dir(cfg.RemotePath)
	files, err := client.ReadDir(remoteDir)
	if err != nil {
		return "", fmt.Errorf("failed to read remote directory: %v", err)
	}

	// Cari file CSV dengan pola ZBCFG_*.CSV
	var latestFile os.FileInfo
	for _, file := range files {
		if file.IsDir() {
			continue
		}
		if strings.HasPrefix(file.Name(), "ZBCFG_") && strings.HasSuffix(file.Name(), ".CSV") {
			if latestFile == nil || file.ModTime().After(latestFile.ModTime()) {
				latestFile = file
			}
		}
	}

	if latestFile == nil {
		return "", fmt.Errorf("no matching CSV file found in remote directory")
	}

	remoteFilePath := path.Join(remoteDir, latestFile.Name())
	log.Printf("Found latest file: %s", remoteFilePath)

	// Download ke lokal
	localFilePath := path.Join(cfg.LocalPath, latestFile.Name())
	remoteFile, err := client.Open(remoteFilePath)
	if err != nil {
		return "", fmt.Errorf("failed to open remote file: %v", err)
	}
	defer remoteFile.Close()

	localFile, err := os.Create(cfg.LocalPath)
	if err != nil {
		return "", fmt.Errorf("failed to create local file: %v", err)
	}
	defer localFile.Close()

	_, err = io.Copy(localFile, remoteFile)
	if err != nil {
		return "", fmt.Errorf("failed to copy file: %v", err)
	}

	return localFilePath, nil
}

// CSV PO PROCESS
func ProcessCSVPo() error {
	log.Println("Entered processCSVPo function")
	cfg := config.LoadConfig()

	// STEP 1: Download the latest file
	sftpCfg := SFTPConfig{
		Host:       "166.0.202.166",
		Port:       22,
		User:       "admincorinthian",
		Password:   "fP9#vWq2@tXz!Lr7Dk$1",
		RemotePath: "/uploads/PO",
		LocalPath:  cfg.CSVFilePathPO,
	}

	filename, err := downloadLatestCSVViaSFTP(sftpCfg)
	if err != nil {
		return fmt.Errorf("SFTP download failed: %v", err)
	}
	log.Printf("Processing file: %s", filename)

	// STEP 2: Process the CSV file with proper quote handling
	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	file, err := os.Open(cfg.CSVFilePathPO)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %v", err)
	}
	defer file.Close()

	// Configure CSV reader
	reader := csv.NewReader(file)
	reader.Comma = ','
	reader.LazyQuotes = true
	reader.FieldsPerRecord = -1
	reader.TrimLeadingSpace = true

	// Skip header row
	if _, err := reader.Read(); err != nil {
		return fmt.Errorf("failed to read header row: %v", err)
	}

	// Initialize tracking variables
	lineNum := 1
	poQuantities := make(map[string]float64)
	poAmtQuantities := make(map[string]float64)
	poHeadersInserted := make(map[string]bool)
	poChildCounters := make(map[string]int) // Track child numbers per PO
	var poID int64

	for {
		lineNum++
		row, err := reader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			log.Printf("Skipping line %d due to parsing error: %v", lineNum, err)
			continue
		}

		if len(row) < 22 {
			log.Printf("Skipping line %d - insufficient columns", lineNum)
			continue
		}

		poNo := strings.TrimSpace(strings.Trim(row[0], `"`))
		if poNo == "" {
			log.Printf("Skipping line %d - empty PO number", lineNum)
			continue
		}

		// Initialize child counter for new PO
		if _, exists := poChildCounters[poNo]; !exists {
			poChildCounters[poNo] = 0
		}

		// Parse quantities
		qty, _ := parseNumberField(row[18])
		amt, _ := parseNumberField(row[21])
		poQuantities[poNo] += qty
		poAmtQuantities[poNo] += amt

		// Check if material is WIP
		isWip := checkIfWIP(db, strings.Trim(row[6], `"`))

		// Insert header if not exists
		if !poHeadersInserted[poNo] {
			poID, err = insertPurHDPo(db, cleanRow(row), poQuantities[poNo], isWip)
			if err != nil {
				log.Printf("Line %d - failed to insert header: %v", lineNum, err)
				continue
			}
			poHeadersInserted[poNo] = true
			poChildCounters[poNo] = 0 // Reset counter when new header is inserted
		}

		// Increment and use child number for this PO
		poChildCounters[poNo]++
		if err := insertPurDetPo(db, poID, cleanRow(row), poChildCounters[poNo]); err != nil {
			log.Printf("Line %d - failed to insert detail: %v", lineNum, err)
			continue
		}
	}

	// Update totals
	updatePOTotals(db, poQuantities, poAmtQuantities)

	log.Println("CSV processing completed successfully")
	return nil
}

// Helper functions
func cleanRow(row []string) []string {
	cleaned := make([]string, len(row))
	for i, field := range row {
		cleaned[i] = cleanCSVField(field)
	}
	return cleaned
}

func cleanCSVField(field string) string {
	// Remove outer quotes if they exist
	field = strings.TrimSpace(field)
	if len(field) >= 2 && field[0] == '"' && field[len(field)-1] == '"' {
		field = field[1 : len(field)-1]
	}
	// Replace internal double quotes with single quotes
	field = strings.ReplaceAll(field, `""`, `"`)
	return strings.TrimSpace(field)
}

func parseNumberField(field string) (float64, error) {
	cleaned := strings.ReplaceAll(cleanCSVField(field), ",", "")
	return strconv.ParseFloat(cleaned, 64)
}

func checkIfWIP(db *sql.DB, materialCode string) string {
	if materialCode == "" {
		return "0"
	}

	var kodeBarang string
	err := db.QueryRow("SELECT TpBarang FROM mst_barang WHERE KdBarang = ? LIMIT 1", materialCode).Scan(&kodeBarang)
	if err != nil && err != sql.ErrNoRows {
		log.Printf("Failed to check WIP status: %v", err)
	}

	if kodeBarang == "11" {
		return "1"
	}
	return "0"
}

func updatePOTotals(db *sql.DB, quantities, amounts map[string]float64) {
	for poNo, totalQty := range quantities {
		if err := updatePurHDWithQtyPo(db, poNo, totalQty); err != nil {
			log.Printf("Failed to update quantity for PO %s: %v", poNo, err)
		}
	}
	for poNo, totalAmt := range amounts {
		if err := updatePurHDWithamountPo(db, poNo, totalAmt); err != nil {
			log.Printf("Failed to update amount for PO %s: %v", poNo, err)
		}
	}
}

// Updated ProcessCSVPo function
// func ProcessCSVPo() error {
// 	log.Println("Entered processCSVPo function")
// 	cfg := config.LoadConfig()

// 	// STEP 1: Download the latest file
// 	sftpCfg := SFTPConfig{
// 		Host:       "166.0.202.166", // No sftp:// prefix
// 		Port:       22,
// 		User:       "admincorinthian",
// 		Password:   "fP9#vWq2@tXz!Lr7Dk$1",
// 		RemotePath: "/uploads/PO",     // Directory path only
// 		LocalPath:  cfg.CSVFilePathPO, // Local destination
// 	}

// 	filename, err := downloadLatestCSVViaSFTP(sftpCfg)
// 	if err != nil {
// 		return fmt.Errorf("SFTP download failed: %v", err)
// 	}
// 	log.Printf("Processing file: %s", filename)

// 	// Rest of your existing ProcessCSVPo function...
// 	db, err := config.ConnectToDatabase(cfg)
// 	if err != nil {
// 		return fmt.Errorf("failed to connect to database: %v", err)
// 	}
// 	defer db.Close()
// 	// STEP 2: Proceed as before

// 	log.Println("Entered processCSVSo function")

// 	db, err = config.ConnectToDatabase(cfg)
// 	if err != nil {
// 		return fmt.Errorf("failed to connect to database: %v", err)
// 	}
// 	defer db.Close()

// 	log.Println("Processing CSV file...")

// 	// Use CSV_FILE_PATH from the configuration
// 	filePath := cfg.CSVFilePathPO

// 	// Open the CSV file
// 	file, err := os.Open(filePath)
// 	if err != nil {
// 		return fmt.Errorf("failed to open CSV file: %v", err)
// 	}
// 	defer file.Close()

// 	log.Println("CSV file opened successfully...")

// 	// Set the CSV reader to use semicolon as delimiter
// 	reader := csv.NewReader(file)
// 	reader.Comma = ';' // Set semicolon as delimiter
// 	rows, err := reader.ReadAll()
// 	if err != nil {
// 		return fmt.Errorf("failed to read CSV file: %v", err)
// 	}

// 	log.Printf("Read %d rows from the CSV file.", len(rows))

// 	log.Println("Database connection established...")

// 	// Map to store the total quantity for each PO number
// 	poQuantities := make(map[string]float64)
// 	poAmtQuantities := make(map[string]float64)

// 	// Create a map to track which PO headers have already been inserted
// 	poHeadersInserted := make(map[string]bool)

// 	childNo := 0
// 	var poID int64

// 	for i, row := range rows {
// 		if i == 0 {
// 			continue // Skip header row
// 		}

// 		// Accumulate the quantity for the corresponding PO number
// 		poNo := row[0]
// 		qty := removeCommas(row[18])
// 		amt := removeCommas(row[21])

// 		poAmtQuantities[poNo] += amt
// 		poQuantities[poNo] += qty

// 		// Query for checking if barang is WIP
// 		isWip := "0"
// 		var kodeBarang string
// 		checkQuery := `
// 			SELECT TpBarang FROM mst_barang WHERE KdBarang = ? LIMIT 1`
// 		err = db.QueryRow(checkQuery, row[6]).Scan(&kodeBarang)

// 		if err != nil {
// 			log.Printf("Failed to check kode barang at row %d: %v", i, err)
// 			continue
// 		}
// 		//For checking if barang is WIP
// 		if kodeBarang == "11" {
// 			isWip = "1"
// 		}

// 		// Only insert header if it's a new PO number that hasn't been inserted
// 		if !poHeadersInserted[poNo] {
// 			// Insert into pur_hd and get the po_idn
// 			poID, err = insertPurHDPo(db, row, poQuantities[poNo], isWip)
// 			if err != nil {
// 				log.Printf("Failed to insert into pur_hd at row %d: %v", i, err)
// 				continue
// 			}

// 			// Mark this PO as inserted in the map
// 			poHeadersInserted[poNo] = true
// 		}

// 		// Insert into pur_det with the po_id for each row
// 		childNo++
// 		//	poID, err := strconv.ParseInt(poNo, 10, 64)

// 		err = insertPurDetPo(db, poID, row, childNo) // Passing converted poID
// 		if err != nil {
// 			log.Printf("Failed to insert into pur_det at row %d: %v", i, err)
// 			continue
// 		}
// 	}

// 	log.Println("CSV processing completed successfully")

// 	// Update the PO headers with the total quantities
// 	for poNo, totalQty := range poQuantities {
// 		log.Printf("Updating PO %s with total quantity: %.2f", poNo, totalQty)
// 		err := updatePurHDWithQtyPo(db, poNo, totalQty)
// 		if err != nil {
// 			log.Printf("Failed to update pur_pohdr for PO %s: %v", poNo, err)
// 		}
// 	}

// 	for poNo, totalAmt := range poAmtQuantities {
// 		log.Printf("Updating PO %s with total amountt: %.2f", poNo, totalAmt)
// 		err := updatePurHDWithamountPo(db, poNo, totalAmt)
// 		if err != nil {
// 			log.Printf("Failed to update pur_pohdr for PO %s: %v", poNo, err)
// 		}
// 	}

// 	return nil
// }

func updatePurHDWithamountPo(db *sql.DB, poNo string, totalAmount float64) error {
	query := `
        UPDATE pur_pohdr 
        SET tot_amount = ? 
        WHERE po_no = ?`

	_, err := db.Exec(query, totalAmount, poNo)
	return err
}

func updatePurHDWithQtyPo(db *sql.DB, poNo string, totalQty float64) error {
	query := `
        UPDATE pur_pohdr 
        SET tot_qty = ? 
        WHERE po_no = ?`

	_, err := db.Exec(query, totalQty, poNo)
	return err
}

// FUNCTION QUERY INSERT PO
func insertPurHDPo(db *sql.DB, row []string, totalQty float64, isWip string) (int64, error) {
	log.Printf("Inserting/Updating pur_hd with po_no: %s", row[1])

	// Remove commas from numeric fields like Net Value, Order Quantity, etc.
	netValue := row[15]
	orderQty := totalQty // Use the total quantity here
	//	dlvDateStr := row[4]
	//	dlvDate, err := time.Parse("1/2/2006", dlvDateStr)
	//	if err != nil {
	//		return 0, fmt.Errorf("failed to parse dlv_date: %v", err)
	//	}

	// poDateStr := row[4]
	// poDate, err := time.Parse("1/2/2006", poDateStr)
	// if err != nil {
	// 	return 0, fmt.Errorf("failed to parse po_date: %v", err)
	// }

	//	dlvDateFormatted := dlvDate.Format("2006-01-02") // Format the date to YYYY-MM-DD
	// poDateFormatted := poDate.Format("2006-01-02") // Format the date to YYYY-MM-DD
	parts := strings.Fields(row[22])

	// Step 1: Check if the po_no already exists
	var existingPoID int64
	checkQuery := `
		SELECT po_id FROM pur_pohdr WHERE po_no = ? LIMIT 1`
	err := db.QueryRow(checkQuery, row[0]).Scan(&existingPoID)

	if err == nil {
		// Step 2: If po_no exists, update the existing record
		updateQuery := `
			UPDATE pur_pohdr
			SET po_date = ?, supplier = ?, currency = ?, dlv_date = ?, wh_id = ?, notes = ?, tot_qty = ?, tot_amount = ?, kurs = ?, po_type_rm_fg = ?, sub_total = ?, disc_amt = ?, ppn = ?, disc_pct = ?
			WHERE po_id = ?`
		_, err = db.Exec(updateQuery, row[4], parts[0], row[16], row[4], 0, "-", orderQty, netValue, 1, "", 0, 0, 0, 0, existingPoID)
		if err != nil {
			return 0, err
		}
	} else if err == sql.ErrNoRows {
		// Step 3: If po_no does not exist, insert a new record
		po_type := "0"

		var kode_negara string
		checkQuery := `
			SELECT Negara FROM mst_perusahaan WHERE KdPrshn = ? LIMIT 1`
		err = db.QueryRow(checkQuery, parts[0]).Scan(&kode_negara)

		if isWip == "1" {
			po_type = "4"
		} else if kode_negara == "ID" {
			po_type = "3"
		}
		fmt.Println(row[4], "dlv_Date nih guys")
		insertQuery := `
			INSERT INTO pur_pohdr (po_type, po_no, po_date, supplier, currency, dlv_date, wh_id, notes, tot_qty, tot_amount, kurs, po_type_rm_fg, sub_total, disc_amt, ppn, disc_pct)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
		_, err = db.Exec(insertQuery, po_type, row[0], row[4], parts[0], row[16], row[4], 0, "-", orderQty, netValue, 1, "", 0, 0, 0, 0)
		if err != nil {
			return 0, err
		}
	} else {
		// Error checking for existing record
		log.Println("Error checking for existing record:", err)
		return 0, err
	}

	// Step 4: Retrieve the last po_id (whether inserted or updated)
	var lastPoID int64
	checkLastPoId := "SELECT MAX(po_id) AS last_po_id FROM pur_pohdr WHERE po_no = ? LIMIT 1"
	err = db.QueryRow(checkLastPoId, row[0]).Scan(&lastPoID)
	if err != nil {
		log.Fatal(err)
	}

	return lastPoID, nil
}

// FUNCTION QUERY INSERT PO DETAIL
func insertPurDetPo(db *sql.DB, poID int64, row []string, childNo int) error {
	log.Printf("Inserting/Updating pur_det for po_id: %d, child_no: %d", poID, childNo)

	// Get the delivered quantity and net value from the row
	qty := removeCommas(row[18])    // Delivered Quantity (column index 6)
	amount := removeCommas(row[21]) // Net Value (column index 10)

	// Get the price from the row
	price := removeCommas(row[15]) // Price (column index 8)

	// Step 1: Check if the record with po_id and mat_id exists
	var existingID int64
	checkQuery := `
		SELECT id FROM pur_podet WHERE po_id = ? AND mat_id = ? LIMIT 1`
	err := db.QueryRow(checkQuery, poID, row[6]).Scan(&existingID)

	// Step 2: If the record exists, update it
	if err == nil {
		// Record exists, update it
		updateQuery := `
			UPDATE pur_podet
			SET partNo = ?, qty = ?, amount = ?, price = ?, child_no = ?, remark_det = ?,netto = ?
			WHERE po_id = ? AND mat_id = ?`
		_, err = db.Exec(updateQuery, row[6], qty, amount, price, childNo, "", poID, row[7], row[30])
		if err != nil {
			return err
		}
	} else if err == sql.ErrNoRows {
		// Step 3: If no matching record, insert a new record

		insertQuery := `
			INSERT INTO pur_podet (po_id, mat_id, partNo, qty, amount, price, child_no, remark_det, netto)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)`
		_, err = db.Exec(insertQuery, poID, row[6], row[7], qty, amount, price, childNo, "", row[30])
		if err != nil {
			return err
		}
	} else {
		// Error checking for existing record
		log.Println("Error checking for existing record:", err)
		return err
	}

	return nil
}

// CSV SO PROCESS
func ProcessCSVSo() error {
	log.Println("Entered processCSVPo function")
	cfg := config.LoadConfig()

	// STEP 1: Download the latest file
	sftpCfg := SFTPConfig{
		Host:       "166.0.202.166",
		Port:       22,
		User:       "admincorinthian",
		Password:   "fP9#vWq2@tXz!Lr7Dk$1",
		RemotePath: "/uploads/SO",
		LocalPath:  cfg.CSVFilePathSO,
	}

	filename, err := downloadLatestCSVViaSFTP(sftpCfg)
	if err != nil {
		return fmt.Errorf("SFTP download failed: %v", err)
	}
	log.Printf("Processing file: %s", filename)

	// STEP 2: Process the CSV file with proper quote handling
	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	file, err := os.Open(cfg.CSVFilePathSO)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %v", err)
	}
	defer file.Close()

	// Configure CSV reader
	reader := csv.NewReader(file)
	reader.Comma = ','
	reader.LazyQuotes = true
	reader.FieldsPerRecord = -1
	reader.TrimLeadingSpace = true

	// Skip header row
	if _, err := reader.Read(); err != nil {
		return fmt.Errorf("failed to read header row: %v", err)
	}

	// Initialize tracking variables
	lineNum := 1
	poQuantities := make(map[string]float64)
	poAmtQuantities := make(map[string]float64)
	poHeadersInserted := make(map[string]bool)
	poChildCounters := make(map[string]int) // Track child numbers per PO
	var poID int64

	for {
		lineNum++
		row, err := reader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			log.Printf("Skipping line %d due to parsing error: %v", lineNum, err)
			continue
		}

		if len(row) < 22 {
			log.Printf("Skipping line %d - insufficient columns", lineNum)
			continue
		}

		poNo := strings.TrimSpace(strings.Trim(row[5], `"`))
		if poNo == "" {
			log.Printf("Skipping line %d - empty PO number", lineNum)
			continue
		}

		// Initialize child counter for new PO
		if _, exists := poChildCounters[poNo]; !exists {
			poChildCounters[poNo] = 0
		}

		// Parse quantities
		qty, _ := parseNumberField(row[10])

		poQuantities[poNo] += qty

		// Check if material is WIP

		// Insert header if not exists
		if !poHeadersInserted[poNo] {
			poID, err = insertPurHD(db, cleanRow(row), poQuantities[poNo])
			if err != nil {
				log.Printf("Line %d - failed to insert header: %v", lineNum, err)
				continue
			}
			poHeadersInserted[poNo] = true
			poChildCounters[poNo] = 0 // Reset counter when new header is inserted
		}

		// Increment and use child number for this PO
		poChildCounters[poNo]++
		if err := insertPurDet(db, poID, cleanRow(row), poChildCounters[poNo]); err != nil {
			log.Printf("Line %d - failed to insert detail: %v", lineNum, err)
			continue
		}
	}

	// Update totals
	updatePOTotals(db, poQuantities, poAmtQuantities)

	log.Println("CSV processing completed successfully")
	return nil
}

// func ProcessCSVSo() error {

// 	log.Println("Entered processCSVPo function")
// 	cfg := config.LoadConfig()

// 	// STEP 1: Download the latest file
// 	sftpCfg := SFTPConfig{
// 		Host:       "166.0.202.166", // No sftp:// prefix
// 		Port:       22,
// 		User:       "admincorinthian",
// 		Password:   "fP9#vWq2@tXz!Lr7Dk$1",
// 		RemotePath: "/uploads/SO",     // Directory path only
// 		LocalPath:  cfg.CSVFilePathSO, // Local destination
// 	}

// 	filename, err := downloadLatestCSVViaSFTP(sftpCfg)
// 	if err != nil {
// 		return fmt.Errorf("SFTP download failed: %v", err)
// 	}
// 	log.Printf("Processing file: %s", filename)

// 	// Rest of your existing ProcessCSVPo function...
// 	db, err := config.ConnectToDatabase(cfg)
// 	if err != nil {
// 		return fmt.Errorf("failed to connect to database: %v", err)
// 	}
// 	defer db.Close()
// 	// STEP 2: Proceed as before

// 	log.Println("Entered processCSVSo function")

// 	db, err = config.ConnectToDatabase(cfg)
// 	if err != nil {
// 		return fmt.Errorf("failed to connect to database: %v", err)
// 	}
// 	defer db.Close()

// 	log.Println("Processing CSV file...")

// 	// Use CSV_FILE_PATH from the configuration
// 	filePath := cfg.CSVFilePathSO

// 	// Open the CSV file
// 	file, err := os.Open(filePath)
// 	if err != nil {
// 		return fmt.Errorf("failed to open CSV file: %v", err)
// 	}
// 	defer file.Close()

// 	log.Println("CSV file opened successfully...")

// 	// Set the CSV reader to use semicolon as delimiter
// 	reader := csv.NewReader(file)
// 	reader.Comma = ';' // Set semicolon as delimiter
// 	rows, err := reader.ReadAll()
// 	if err != nil {
// 		return fmt.Errorf("failed to read CSV file: %v", err)
// 	}

// 	log.Printf("Read %d rows from the CSV file.", len(rows))

// 	log.Println("Database connection established...")

// 	// Map to store the total quantity for each PO number
// 	poQuantities := make(map[string]float64)

// 	// Create a map to track which PO headers have already been inserted
// 	poHeadersInserted := make(map[string]bool)

// 	childNo := 0
// 	var poID int64
// 	for i, row := range rows {
// 		if i == 0 {
// 			continue // Skip header row
// 		}

// 		// Accumulate the quantity for the corresponding PO number
// 		poNo := row[5]
// 		qty := removeCommas(row[10])

// 		poQuantities[poNo] += qty

// 		// Only insert header if it's a new PO number that hasn't been inserted
// 		if !poHeadersInserted[poNo] {
// 			// Insert into pur_hd and get the po_id
// 			poID, err = insertPurHD(db, row, poQuantities[poNo])
// 			if err != nil {
// 				log.Printf("Failed to insert into pur_hd at row %d: %v", i, err)
// 				continue
// 			}

// 			// Mark this PO as inserted in the map
// 			poHeadersInserted[poNo] = true
// 		}

// 		// Insert into pur_det with the po_id for each row
// 		childNo++
// 		//poID, err := strconv.ParseInt(poNo, 10, 64)
// 		if err != nil {
// 			log.Printf("Failed to convert poID to int64 at row %d: %v", i, err)
// 			continue
// 		}
// 		err = insertPurDet(db, poID, row, childNo) // Passing converted poID
// 		if err != nil {
// 			log.Printf("Failed to insert into pur_det at row %d: %v", i, err)
// 			continue
// 		}
// 	}

// 	log.Println("CSV processing completed successfully")

// 	// Update the PO headers with the total quantities
// 	for poNo, totalQty := range poQuantities {
// 		log.Printf("Updating SO %s with total quantity: %.2f", poNo, totalQty)
// 		err := updatePurHDWithQty(db, poNo, totalQty)
// 		if err != nil {
// 			log.Printf("Failed to update pur_pohdr for SO %s: %v", poNo, err)
// 		}
// 	}

// 	return nil
// }

func updatePurHDWithQty(db *sql.DB, poNo string, totalQty float64) error {
	query := `
        UPDATE pur_pohdr 
        SET tot_qty = ? 
        WHERE po_no = ?`

	_, err := db.Exec(query, totalQty, poNo)
	return err
}

// FUNTION QUERY INSERT SO
func insertPurHD(db *sql.DB, row []string, totalQty float64) (int64, error) {
	log.Printf("Inserting/Updating pur_hd with po_no: %s", row[5])

	// Remove commas from numeric fields like Net Value, Order Quantity, etc.
	netValue := removeCommas(row[13])
	orderQty := totalQty // Use the total quantity here
	// dlvDateStr := row[15]
	// dlvDate, err := time.Parse("1/2/2006", dlvDateStr)
	// if err != nil {
	// 	return 0, fmt.Errorf("failed to parse dlv_date: %v", err)
	// }

	// poDateStr := row[0]
	// poDate, err := time.Parse("1/2/2006", poDateStr)
	// if err != nil {
	// 	return 0, fmt.Errorf("failed to parse po_date: %v", err)
	// }

	// dlvDateFormatted := dlvDate.Format("2006-01-02") // Format the date to YYYY-MM-DD
	// poDateFormatted := poDate.Format("2006-01-02")   // Format the date to YYYY-MM-DD

	// Step 1: Check if the po_no already exists
	var existingPoID int64
	checkQuery := `
		SELECT po_id FROM pur_pohdr WHERE po_no = ? LIMIT 1`
	err := db.QueryRow(checkQuery, row[5]).Scan(&existingPoID)

	if err == nil {
		// Step 2: If po_no exists, update the existing record
		updateQuery := `
			UPDATE pur_pohdr
			SET po_date = ?, supplier = ?, currency = ?, dlv_date = ?, wh_id = ?, notes = ?, tot_qty = ?, tot_amount = ?, kurs = ?, po_type_rm_fg = ?, sub_total = ?, disc_amt = ?, ppn = ?, disc_pct = ?
			WHERE po_id = ?`
		_, err = db.Exec(updateQuery, row[0], row[1], "-", row[15], row[4], "-", orderQty, netValue, 1, "", 0, 0, 0, 0, existingPoID)
		if err != nil {
			return 0, err
		}
	} else if err == sql.ErrNoRows {
		// Step 3: If po_no does not exist, insert a new record
		insertQuery := `
			INSERT INTO pur_pohdr (po_type, po_no, po_date, supplier, currency, dlv_date, wh_id, notes, tot_qty, tot_amount, kurs, po_type_rm_fg, sub_total, disc_amt, ppn, disc_pct)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
		_, err = db.Exec(insertQuery, "2", row[5], row[0], row[1], "-", row[15], row[4], "-", orderQty, netValue, 1, "", 0, 0, 0, 0)
		if err != nil {
			return 0, err
		}
	} else {
		// Error checking for existing record
		log.Println("Error checking for existing record:", err)
		return 0, err
	}

	// Step 4: Get the last po_id (whether inserted or updated)
	var lastPoID int64
	checkPoID := "SELECT MAX(po_id) AS last_po_id FROM pur_pohdr WHERE po_no = ? LIMIT 1"
	err = db.QueryRow(checkPoID, row[5]).Scan(&lastPoID)
	if err != nil {
		log.Fatal(err)
	}

	return lastPoID, nil
}

// FUNTION QUERY INSERT SO DETAIL
func insertPurDet(db *sql.DB, poID int64, row []string, childNo int) error {
	log.Printf("Inserting/Updating pur_det for po_id: %d, child_no: %d", poID, childNo)

	// Get the delivered quantity and net value from the row
	qty := removeCommas(row[10])    // Delivered Quantity (column index 10)
	amount := removeCommas(row[14]) // Net Value (column index 14)

	// Calculate price (price = amount / qty)
	price := amount / qty

	// Step 1: Check if the row already exists (same po_id and mat_id)
	var existingID int64
	checkQuery := `
		SELECT id FROM pur_podet WHERE po_id = ? AND mat_id = ? LIMIT 1`
	err := db.QueryRow(checkQuery, poID, row[7]).Scan(&existingID)

	// Step 2: If it exists, update the record; if not, insert a new one
	if err == nil {
		// Row exists, perform an update
		updateQuery := `
			UPDATE pur_podet 
			SET partNo = ?, qty = ?, amount = ?, price = ?, child_no = ?, remark_det = ?
			WHERE po_id = ? AND mat_id = ?`
		_, err = db.Exec(updateQuery, row[9], qty, amount, price, childNo, "", poID, row[7])
	} else if err == sql.ErrNoRows {
		// Row does not exist, perform an insert
		insertQuery := `
			INSERT INTO pur_podet (po_id, mat_id, partNo, qty, amount, price, child_no, remark_det)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?)`
		_, err = db.Exec(insertQuery, poID, row[7], row[9], qty, amount, price, childNo, "")
	} else {
		// Error checking for existing record
		log.Println("Error checking for existing record:", err)
	}

	return err
}

// Helper function to remove commas from numeric strings
func removeCommas(value string) float64 {
	// Replace commas used as thousand separators (if any).
	value = strings.ReplaceAll(value, ".", "")
	// Replace the comma used as a decimal separator with a dot.
	value = strings.ReplaceAll(value, ",", ".")

	// Convert the value to a float64.
	num, err := strconv.ParseFloat(value, 64)
	if err != nil {
		log.Printf("Error converting value '%s' to float: %v", value, err)
		return 0
	}
	return num
}

//-- trx IN and OUT -- //

// func ProcessCSVInOut() error {
// 	log.Println("Entered processCSVSo function")
// 	cfg := config.LoadConfig()

// 	db, err := config.ConnectToDatabase(cfg)
// 	if err != nil {
// 		return fmt.Errorf("failed to connect to database: %v", err)
// 	}
// 	defer db.Close()

// 	log.Println("Processing CSV file...")

// 	// Use CSV_FILE_PATH from the configuration
// 	filePath := cfg.CSVFilePathInOut

// 	// Open the CSV file
// 	file, err := os.Open(filePath)
// 	if err != nil {
// 		return fmt.Errorf("failed to open CSV file: %v", err)
// 	}
// 	defer file.Close()

// 	reader := csv.NewReader(file)
// 	reader.Comma = ';'
// 	data, err := reader.ReadAll()
// 	if err != nil {
// 		log.Fatal(err)
// 	}

// 	for _, row := range data[1:] { // Skip the header row
// 		qty, _ := strconv.ParseFloat(row[4], 64) // "Qty in unit of entry"
// 		if qty == 0 {
// 			continue // Skip rows with zero quantity
// 		}

// 		// Header values
// 		documentNumber := row[11]
// 		documentDate := parseDate(row[12])
// 		entryDate := parseDate(row[13])
// 		//	postingDate := parseDate(row[14])
// 		currency := row[17]
// 		userName := row[10]
// 		//documentText := row[21]
// 		po_no := row[20]
// 		wh_id := row[8]
// 		move_type := row[6]
// 		matin_type := "-"
// 		// Insert into the appropriate header table
// 		var headerID int64
// 		if qty > 0 {

// 			if move_type == "101" {
// 				matin_type = "0"
// 			} else if move_type == "311" {
// 				matin_type = "1"

// 			} else if move_type == "641" {
// 				matin_type = "1"
// 			} else if move_type == "311" {
// 				matin_type = "1"
// 			} else if move_type == "541" {
// 				matin_type = "8"
// 			} else {
// 				matin_type = "6"
// 			}
// 			headerID = insertHeader_in(db, "mat_inchdr_sub", documentNumber, documentDate, entryDate, matin_type, currency, userName, wh_id, po_no)
// 			insertDetail_in(db, "mat_incdet_sub", headerID, row)
// 		} else {
// 			if move_type == "101" {
// 				matin_type = "0"
// 			} else if move_type == "311" {
// 				matin_type = "1"

// 			} else if move_type == "641" {
// 				matin_type = "1"
// 			} else if move_type == "311" {
// 				matin_type = "1"
// 			} else if move_type == "541" {
// 				matin_type = "8"
// 			} else {
// 				matin_type = "6"
// 			}
// 			headerID = insertHeader_out(db, "mat_outhdr_sub", documentNumber, documentDate, entryDate, matin_type, currency, userName, wh_id, po_no)
// 			insertDetail_out(db, "mat_outdet_sub", headerID, row)
// 		}
// 	}
// 	fmt.Println("Data insertion completed successfully!")
// 	return err
// }

// func parseDate(dateStr string) time.Time {
// 	date, _ := time.Parse("02-01-06", dateStr)
// 	return date
// }

// func insertHeader_in(db *sql.DB, table, documentNumber string, documentDate, entryDate time.Time, matin_type, currency, userName, wh_id, po_no string) int64 {
// 	query := fmt.Sprintf("INSERT INTO %s (matin_no, matin_date, Tgdaf, matin_type, currency, pic, wh_id,po_no) VALUES (?, ?, ?, ?, ?, ?, ?,?)", table)

// 	_, err := db.Exec(query, documentNumber, documentDate, entryDate, matin_type, currency, userName, wh_id, po_no)
// 	if err != nil {
// 		log.Fatal(err)
// 	}
// 	var headerID int64
// 	checkPoID := "SELECT MAX(matin_id) AS last_mat_id FROM mat_inchdr_sub WHERE matin_no = ? LIMIT 1"
// 	err = db.QueryRow(checkPoID, documentNumber).Scan(&headerID)
// 	if err != nil {
// 		log.Fatal(err)
// 	}

// 	return headerID
// }

// func insertHeader_out(db *sql.DB, table, documentNumber string, documentDate, entryDate time.Time, matin_type, currency, userName, wh_id, po_no string) int64 {
// 	query := fmt.Sprintf("INSERT INTO %s (matout_no, matout_date, Tgdaf, matout_type, currency, pic,ref_no) VALUES (?, ?, ?, ?, ?, ?,?)", table)

// 	_, err := db.Exec(query, documentNumber, documentDate, entryDate, matin_type, currency, userName, po_no)
// 	if err != nil {
// 		log.Fatal(err)
// 	}
// 	var headerID int64
// 	checkPoID := "SELECT MAX(matout_id) AS last_mat_id FROM mat_outhdr_sub WHERE matout_no = ? LIMIT 1"
// 	err = db.QueryRow(checkPoID, documentNumber).Scan(&headerID)
// 	if err != nil {
// 		log.Fatal(err)
// 	}

// 	return headerID
// }

// func insertDetail_in(db *sql.DB, table string, headerID int64, row []string) (int64, error) {
// 	// Build the SQL query string
// 	query := fmt.Sprintf("INSERT INTO %s (matin_id, child_no, mat_id, qty,dqty,poqty,partNo) VALUES (?, ?, ?, ?, ?, ?,?)", table)

// 	// Parse the quantity field as a float
// 	quantity, err := strconv.ParseFloat(row[4], 64)
// 	if err != nil {
// 		return 0, err // Return an error if the float parsing fails
// 	}

// 	// Execute the query
// 	res, err := db.Exec(query, headerID, row[1], row[2], quantity, quantity, quantity, quantity, row[3])
// 	if err != nil {
// 		return 0, err // Return an error if the database execution fails
// 	}

// 	// Fetch the last inserted ID
// 	lastInsertID, err := res.LastInsertId()
// 	if err != nil {
// 		return 0, err // Return an error if fetching the last inserted ID fails
// 	}

// 	// Ensure there's always a return statement
// 	return lastInsertID, nil
// }

// func insertDetail_out(db *sql.DB, table string, headerID int64, row []string) (int64, error) {
// 	// Build the SQL query string
// 	query := fmt.Sprintf("INSERT INTO %s (matout_id, child_no, mat_id, qty,dqty,poqty,partNo) VALUES (?, ?, ?, ?, ?, ?,?)", table)

// 	// Parse the quantity field as a float
// 	quantity, err := strconv.ParseFloat(row[4], 64)
// 	if err != nil {
// 		return 0, err // Return an error if the float parsing fails
// 	}

// 	// Execute the query
// 	res, err := db.Exec(query, headerID, row[1], row[2], quantity, quantity, quantity, quantity, row[3])
// 	if err != nil {
// 		return 0, err // Return an error if the database execution fails
// 	}

// 	// Fetch the last inserted ID
// 	lastInsertID, err := res.LastInsertId()
// 	if err != nil {
// 		return 0, err // Return an error if fetching the last inserted ID fails
// 	}

//		// Ensure there's always a return statement
//		return lastInsertID, nil
//	}

// func ProcessCSVInOut() error {
// 	log.Println("Entered processCSVInOut function")
// 	cfg := config.LoadConfig()

// 	db, err := config.ConnectToDatabase(cfg)
// 	if err != nil {
// 		return fmt.Errorf("failed to connect to database: %v", err)
// 	}
// 	defer db.Close()

// 	log.Println("Processing CSV file...")

// 	// Use CSV_FILE_PATH from the configuration
// 	filePath := cfg.CSVFilePathInOut

// 	// Open the CSV file
// 	file, err := os.Open(filePath)
// 	if err != nil {
// 		return fmt.Errorf("failed to open CSV file: %v", err)
// 	}
// 	defer file.Close()

// 	reader := csv.NewReader(file)
// 	reader.Comma = ';'
// 	data, err := reader.ReadAll()
// 	if err != nil {
// 		log.Fatal(err)
// 	}

// 	var lastMatinID, lastMatoutID int64
// 	var currentChildNoIn, currentChildNoOut int64

// 	for _, row := range data[1:] { // Skip the header row
// 		qty, err := strconv.ParseFloat(row[4], 64) // "Qty in unit of entry"
// 		if err != nil {
// 			log.Printf("Skipping row due to invalid quantity: %v", row)
// 			continue // Skip rows with invalid quantity
// 		}

// 		if qty == 0 {
// 			continue // Skip rows with zero quantity
// 		}

// 		// Header values
// 		documentNumber := row[11]
// 		documentDate := parseDate(row[12])
// 		entryDate := parseDate(row[13])
// 		currency := row[17]
// 		userName := row[10]
// 		po_no := row[20]
// 		wh_id := row[8]
// 		move_type := row[6]
// 		matin_type := "-"

// 		// Assign matin_type based on move_type
// 		if move_type == "101" {
// 			matin_type = "0"
// 		} else if move_type == "311" {
// 			matin_type = "1"
// 		} else if move_type == "641" {
// 			matin_type = "1"
// 		} else if move_type == "541" {
// 			matin_type = "8"
// 		} else {
// 			matin_type = "6"
// 		}

// 		// Debugging: log intermediate values
// 		log.Printf("Processing row: %v, move_type: %s, qty: %f, matin_type: %s", row, move_type, qty, matin_type)

// 		var headerID int64
// 		if qty < 0 {
// 			// Insert into "out" table (mat_outhdr_sub)
// 			headerID = insertHeader_out(db, "mat_outhdr_sub", documentNumber, documentDate, entryDate, matin_type, currency, userName, wh_id, po_no)

// 			// Reset child_no if we have a new matout_id
// 			if lastMatoutID != headerID {
// 				currentChildNoOut = 0
// 				lastMatoutID = headerID
// 			}
// 			insertDetail_out(db, "mat_outdet_sub", headerID, row, &currentChildNoOut, qty) // Pass qty directly
// 		} else {
// 			// Insert into "in" table (mat_inchdr_sub)
// 			headerID = insertHeader_in(db, "mat_inchdr_sub", documentNumber, documentDate, entryDate, matin_type, currency, userName, wh_id, po_no)

// 			// Reset child_no if we have a new matin_id
// 			if lastMatinID != headerID {
// 				currentChildNoIn = 0
// 				lastMatinID = headerID
// 			}
// 			insertDetail_in(db, "mat_incdet_sub", headerID, mat_id, &currentChildNoIn, qty) // Pass qty directly
// 		}
// 	}

// 	fmt.Println("Data insertion completed successfully!")
// 	return nil
// }

func parseDate(dateStr string) time.Time {
	date, _ := time.Parse("02-01-06", dateStr)
	return date
}
func parseDateInOut(dateStr string) (string, error) {
	parsedDate, err := time.Parse("1/2/2006", dateStr) // Adjust format to match CSV
	if err != nil {
		return "", fmt.Errorf("failed to parse date: %w", err)
	}
	return parsedDate.Format("2006-01-02"), nil // Convert to "YYYY-MM-DD"
}

func insertHeader_in(db *sql.DB, table, documentNumber string, documentDate, entryDate time.Time, matin_type, currency, userName, wh_id, po_no, TpBarang string) int64 {
	var existingMatinID int64
	checkExisting := "SELECT matin_id FROM mat_inchdr_sub WHERE matin_no = ? LIMIT 1"
	err := db.QueryRow(checkExisting, documentNumber).Scan(&existingMatinID)

	if err == sql.ErrNoRows {
		// No existing entry, insert a new one
		query := fmt.Sprintf("INSERT INTO %s (matin_no, matin_date, Tgdaf, matin_type, currency, pic, wh_id, po_no,mat_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)", table)
		_, err := db.Exec(query, documentNumber, documentDate, entryDate, matin_type, currency, userName, wh_id, po_no, TpBarang)
		if err != nil {
			log.Fatal(err)
		}
		// Now retrieve the matin_id
		err = db.QueryRow(checkExisting, documentNumber).Scan(&existingMatinID)
		if err != nil {
			log.Fatal(err)
		}
	} else if err != nil {
		log.Fatal(err)
	}

	return existingMatinID
}

func insertHeader_out(db *sql.DB, table, documentNumber string, documentDate, entryDate time.Time, matin_type, currency, userName, wh_id, po_no, TpBarang string) int64 {
	var existingMatoutID int64
	checkExisting := "SELECT matout_id FROM mat_outhdr_sub WHERE matout_no = ? LIMIT 1"
	err := db.QueryRow(checkExisting, documentNumber).Scan(&existingMatoutID)

	if err == sql.ErrNoRows {
		// No existing entry, insert a new one
		query := fmt.Sprintf("INSERT INTO %s (matout_no, matout_date, Tgdaf, matout_type, currency, pic, ref_no,mat_type) VALUES (?, ?, ?, ?, ?, ?, ?,?)", table)
		_, err := db.Exec(query, documentNumber, documentDate, entryDate, matin_type, currency, userName, po_no, TpBarang)
		if err != nil {
			log.Fatal(err)
		}
		// Now retrieve the matout_id
		err = db.QueryRow(checkExisting, documentNumber).Scan(&existingMatoutID)
		if err != nil {
			log.Fatal(err)
		}
	} else if err != nil {
		log.Fatal(err)
	}

	return existingMatoutID
}

func insertDetail_in(db *sql.DB, table string, headerID int64, partNo, mat_id string, childNo *int64, qty float64) (int64, error) {
	// Increment child_no
	*childNo++

	// Build the SQL query string
	query := fmt.Sprintf("INSERT INTO %s (matin_id, child_no, mat_id, qty, dqty, poqty, partNo) VALUES (?, ?, ?, ?, ?, ?, ?)", table)

	// Use absolute value for qty, as it's coming to the "in" table (always positive)
	quantity := math.Abs(qty)

	// Execute the query
	res, err := db.Exec(query, headerID, *childNo, mat_id, quantity, quantity, quantity, partNo)
	if err != nil {
		return 0, err // Return an error if the database execution fails
	}

	// Fetch the last inserted ID
	lastInsertID, err := res.LastInsertId()
	if err != nil {
		return 0, err // Return an error if fetching the last inserted ID fails
	}

	// Ensure there's always a return statement
	return lastInsertID, nil
}

func insertDetail_out(db *sql.DB, table string, headerID int64, partNo, mat_id string, childNo *int64, qty float64) (int64, error) {
	// Increment child_no
	*childNo++

	// Build the SQL query string
	query := fmt.Sprintf("INSERT INTO %s (matout_id, child_no, mat_id, qty, dqty, poqty, partNo) VALUES (?, ?, ?, ?, ?, ?, ?)", table)

	// Use the absolute value for qty in matout as well, since this is a "negative" quantity scenario
	quantity := math.Abs(qty)

	// Execute the query
	res, err := db.Exec(query, headerID, *childNo, mat_id, quantity, quantity, quantity, partNo)
	if err != nil {
		return 0, err // Return an error if the database execution fails
	}

	// Fetch the last inserted ID
	lastInsertID, err := res.LastInsertId()
	if err != nil {
		return 0, err // Return an error if fetching the last inserted ID fails
	}

	// Ensure there's always a return statement
	return lastInsertID, nil
}

// CSV TYPE FG
func InsertDataFromCSV() error {
	log.Println("Started processing CSV for material_movements")
	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	log.Println("Entered processCSVFG function")

	// STEP 1: Download the latest file sftp://admincorinthian@166.0.202.166/uploads/TEST/ZBCFG_20190816095334.CSV
	sftpCfg := SFTPConfig{
		Host:       "166.0.202.166",
		Port:       22,
		User:       "admincorinthian",
		Password:   "fP9#vWq2@tXz!Lr7Dk$1",
		RemotePath: "/uploads/TEST/ZBCFG_20190816,095334.CSV",
		LocalPath:  cfg.CSVFilePathInOut,
	}

	filename, err := downloadLatestCSVViaSFTP(sftpCfg)
	if err != nil {
		return fmt.Errorf("SFTP download failed: %v", err)
	}
	log.Printf("Processing file: %s", filename)

	filePath := cfg.CSVFilePathInOut
	file, err := os.Open(filePath)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %w", err)
	}
	defer file.Close()

	reader := csv.NewReader(file)
	reader.Comma = ',' // Set the delimiter to ;

	rows, err := reader.ReadAll()
	if err != nil {
		return fmt.Errorf("failed to read CSV file: %w", err)
	}

	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	query := `INSERT INTO material_movements (material, ` + "`order`" + `, plant, storage_location, 
			movement_type, special_stock, material_document,
			posting_date, qty_in_unit_entry, base_unit_of_measure, material_description, 
			amount_in_local_currency, currency) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	// query := `INSERT INTO material_movements (cost_center, ` + "`order`" + `, material, material_description,
	//     qty_in_unit_entry, base_unit_of_measure, movement_type, movement_type_text,
	//     storage_location, plant, user_name, material_document,
	//     document_date, entry_date, posting_date, time_of_entry,
	//     amount_in_local_currency, currency, movement_indicator, special_stock,
	//     reference, document_header_text) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	for i, row := range rows {
		if i == 0 {
			continue // Skip the header row
		}

		_, err := tx.Exec(query,
			nullify(row[0]), nullify(row[1]), nullify(row[2]), nullify(row[3]),
			nullify(row[4]), nullify(row[5]), nullify(row[6]),
			parseDate(row[7]), nullify(row[8]), nullify(row[9]), nullify(row[10]),
			nullify(row[11]), nullify(row[12]),
		)

		// _, err := tx.Exec(query,
		// 	nullify(row[0]), nullify(row[1]), nullify(row[2]), nullify(row[3]),
		// 	nullify(row[4]), nullify(row[5]), nullify(row[6]), nullify(row[7]),
		// 	nullify(row[8]), nullify(row[9]), nullify(row[10]), nullify(row[11]),
		// 	parseDate(row[12]), parseDate(row[13]), parseDate(row[14]), parseTime(row[15]),
		// 	nullify(row[16]), nullify(row[17]), nullify(row[18]), nullify(row[19]),
		// 	nullify(row[20]), nullify(row[21]),
		// )
		if err != nil {
			log.Printf("Failed to insert row %d: %v", i, err)
			tx.Rollback()
			return err
		}
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	log.Println("CSV processing completed successfully")
	return nil
}

func ProcessAndInsertWIPIn() error {
	log.Println("Started processing CSV for material_movements")
	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	filePath := cfg.CSVFilePathInWIP
	file, err := os.Open(filePath)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %w", err)
	}
	defer file.Close()

	reader := csv.NewReader(file)
	reader.Comma = ',' // Set the delimiter to ;

	rows, err := reader.ReadAll()
	if err != nil {
		return fmt.Errorf("failed to read CSV file: %w", err)
	}

	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	selectQuery := `
		SELECT COUNT(*) 
		FROM material_movements 
		WHERE material = ? AND material_description = ? AND material_document = ? AND 
		posting_date = ? AND entry_date = ? AND document_date = ? AND 
		plant = ? AND storage_location = ? AND movement_type = ? AND 
		qty_in_unit_entry = ? AND base_unit_of_measure = ?`

	updateQuery := `
		UPDATE material_movements 
		SET material_description = ?, posting_date = ?, entry_date = ?, 
		document_date = ?, plant = ?, storage_location = ?, movement_type = ?, 
		qty_in_unit_entry = ?, base_unit_of_measure = ? 
		WHERE material = ? AND material_document = ?`

	insertQuery := `
		INSERT INTO material_movements (material, material_description, material_document, posting_date, entry_date, document_date, 
		plant, storage_location, movement_type, qty_in_unit_entry, base_unit_of_measure) 
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	for i, row := range rows {
		if i == 0 {
			continue // Skip the header row
		}

		parseDateConv, err := parseDateInOut(row[4]) // Example assumes posting_date is in row[4]
		if err != nil {
			log.Printf("Failed to parse posting_date for row %d: %v", i, err)
			tx.Rollback()
			return fmt.Errorf("failed to parse posting_date for row %d: %w", i, err)
		}

		existingCount := 0
		err = tx.QueryRow(selectQuery,
			row[0], row[1], row[2], parseDateConv, parseDateConv, parseDateConv,
			row[5], row[6], row[7], row[8], row[9]).Scan(&existingCount)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("failed to check existing data for row %d: %w", i, err)
		}

		if existingCount > 0 {
			// Update if there are differences
			_, err = tx.Exec(updateQuery,
				row[1], parseDateConv, parseDateConv, parseDateConv,
				row[5], row[6], row[7], row[8], row[9],
				row[0], row[2])
			if err != nil {
				log.Printf("Failed to update row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to update row %d: %w", i, err)
			}
		} else {
			// Insert if no match is found
			_, err = tx.Exec(insertQuery,
				row[0], row[1], row[2], parseDateConv, parseDateConv, parseDateConv,
				row[5], row[6], row[7], row[8], row[9])
			if err != nil {
				log.Printf("Failed to insert row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to insert row %d: %w", i, err)
			}
		}
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	log.Println("CSV processing completed successfully")
	return nil
}

func ProcessAndInsertFGIn() error {
	log.Println("Started processing CSV for material_movements")
	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	filePath := cfg.CSVFilePathInFG
	file, err := os.Open(filePath)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %w", err)
	}
	defer file.Close()

	reader := csv.NewReader(file)
	reader.Comma = ',' // your CSV uses semicolon
	rows, err := reader.ReadAll()
	if err != nil {
		return fmt.Errorf("failed to read CSV file: %w", err)
	}

	// Helper to clean up number format (European: 1.000,50 → 1000.50)
	sanitizeNumber := func(s string) string {
		s = strings.ReplaceAll(s, ".", "")  // remove thousands separator
		s = strings.ReplaceAll(s, ",", ".") // convert decimal comma to dot
		return s
	}

	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	selectQuery := `
		SELECT COUNT(*) 
		FROM material_movements 
		WHERE material = ? AND material_description = ? AND material_document = ? AND 
		posting_date = ? AND entry_date = ? AND document_date = ? AND 
		plant = ? AND storage_location = ? AND movement_type = ? AND 
		qty_in_unit_entry = ? AND base_unit_of_measure = ?`

	updateQuery := `
		UPDATE material_movements 
		SET material_description = ?, posting_date = ?, entry_date = ?, 
		document_date = ?, plant = ?, storage_location = ?, movement_type = ?, 
		qty_in_unit_entry = ?, base_unit_of_measure = ? 
		WHERE material = ? AND material_document = ?`

	insertQuery := `
		INSERT INTO material_movements (material, material_description, material_document, posting_date, entry_date, document_date, 
		plant, storage_location, movement_type, qty_in_unit_entry, base_unit_of_measure) 
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	for i, row := range rows {
		if i == 0 {
			continue // Skip header
		}

		if len(row) < 14 {
			log.Printf("Skipping row %d: insufficient columns (%d columns): %v", i, len(row), row)
			continue
		}

		material := row[0]
		materialDocument := row[6]
		materialDescription := row[11]
		postingDateStr := row[8]
		qty := sanitizeNumber(row[9]) // Cleaned qty (e.g., 1.000,00 → 1000.00)
		unit := row[10]
		plant := row[2]
		storage := row[3]
		movementType := row[4]

		// Optional: Parse amount if needed later
		// amount := sanitizeNumber(row[12])
		// quantity := sanitizeNumber(row[13])

		// Parse date (e.g., 3/14/2025)
		parseDateConv, err := parseDateInOut(postingDateStr)
		if err != nil {
			log.Printf("Failed to parse posting_date for row %d: %v", i, err)
			tx.Rollback()
			return fmt.Errorf("failed to parse posting_date for row %d: %w", i, err)
		}

		// Check if record already exists
		existingCount := 0
		err = tx.QueryRow(selectQuery,
			material, materialDescription, materialDocument, parseDateConv, parseDateConv, parseDateConv,
			plant, storage, movementType, qty, unit).Scan(&existingCount)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("failed to check existing data for row %d: %w", i, err)
		}

		if existingCount > 0 {
			// Update existing record
			_, err = tx.Exec(updateQuery,
				materialDescription, parseDateConv, parseDateConv, parseDateConv,
				plant, storage, movementType, qty, unit,
				material, materialDocument)
			if err != nil {
				log.Printf("Failed to update row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to update row %d: %w", i, err)
			}
		} else {
			// Insert new record
			_, err = tx.Exec(insertQuery,
				material, materialDescription, materialDocument, parseDateConv, parseDateConv, parseDateConv,
				plant, storage, movementType, qty, unit)
			if err != nil {
				log.Printf("Failed to insert row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to insert row %d: %w", i, err)
			}
		}
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	log.Println("CSV processing completed successfully")
	return nil
}

func ProcessAndInsertFGOut() error {
	log.Println("Started processing CSV for material_movements")
	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	filePath := cfg.CSVFilePathOutFG
	file, err := os.Open(filePath)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %w", err)
	}
	defer file.Close()

	reader := csv.NewReader(file)
	reader.Comma = ';' // your CSV uses semicolon
	rows, err := reader.ReadAll()
	if err != nil {
		return fmt.Errorf("failed to read CSV file: %w", err)
	}

	// Helper to clean up number format (European: 1.000,50 → 1000.50)
	sanitizeNumber := func(s string) string {
		s = strings.ReplaceAll(s, ".", "")  // remove thousands separator
		s = strings.ReplaceAll(s, ",", ".") // convert decimal comma to dot
		return s
	}

	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	selectQuery := `
		SELECT COUNT(*) 
		FROM material_movements 
		WHERE material = ? AND material_description = ? AND material_document = ? AND 
		posting_date = ? AND entry_date = ? AND document_date = ? AND 
		plant = ? AND storage_location = ? AND movement_type = ? AND 
		qty_in_unit_entry = ? AND base_unit_of_measure = ?`

	updateQuery := `
		UPDATE material_movements 
		SET material_description = ?, posting_date = ?, entry_date = ?, 
		document_date = ?, plant = ?, storage_location = ?, movement_type = ?, 
		qty_in_unit_entry = ?, base_unit_of_measure = ? 
		WHERE material = ? AND material_document = ?`

	insertQuery := `
		INSERT INTO material_movements (material, material_description, material_document, posting_date, entry_date, document_date, 
		plant, storage_location, movement_type, qty_in_unit_entry, base_unit_of_measure) 
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	for i, row := range rows {
		if i == 0 {
			continue // Skip header
		}

		if len(row) < 14 {
			log.Printf("Skipping row %d: insufficient columns (%d columns): %v", i, len(row), row)
			continue
		}

		material := row[0]
		materialDocument := row[6]
		materialDescription := row[11]
		postingDateStr := row[8]
		qty := sanitizeNumber(row[9]) // Cleaned qty (e.g., 1.000,00 → 1000.00)
		unit := row[10]
		plant := row[2]
		storage := row[3]
		movementType := row[4]

		// Optional: Parse amount if needed later
		// amount := sanitizeNumber(row[12])
		// quantity := sanitizeNumber(row[13])

		// Parse date (e.g., 3/14/2025)
		parseDateConv, err := parseDateInOut(postingDateStr)
		if err != nil {
			log.Printf("Failed to parse posting_date for row %d: %v", i, err)
			tx.Rollback()
			return fmt.Errorf("failed to parse posting_date for row %d: %w", i, err)
		}

		// Check if record already exists
		existingCount := 0
		err = tx.QueryRow(selectQuery,
			material, materialDescription, materialDocument, parseDateConv, parseDateConv, parseDateConv,
			plant, storage, movementType, qty, unit).Scan(&existingCount)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("failed to check existing data for row %d: %w", i, err)
		}

		if existingCount > 0 {
			// Update existing record
			_, err = tx.Exec(updateQuery,
				materialDescription, parseDateConv, parseDateConv, parseDateConv,
				plant, storage, movementType, qty, unit,
				material, materialDocument)
			if err != nil {
				log.Printf("Failed to update row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to update row %d: %w", i, err)
			}
		} else {
			// Insert new record
			_, err = tx.Exec(insertQuery,
				material, materialDescription, materialDocument, parseDateConv, parseDateConv, parseDateConv,
				plant, storage, movementType, qty, unit)
			if err != nil {
				log.Printf("Failed to insert row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to insert row %d: %w", i, err)
			}
		}
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	log.Println("CSV processing completed successfully")
	return nil
}

func ProcessAndInsertME() error {
	log.Println("Started processing CSV for material_movements")
	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	filePath := cfg.CSVFilePathMe
	file, err := os.Open(filePath)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %w", err)
	}
	defer file.Close()

	reader := csv.NewReader(file)
	reader.Comma = ',' // your CSV uses semicolon
	rows, err := reader.ReadAll()
	if err != nil {
		return fmt.Errorf("failed to read CSV file: %w", err)
	}

	// Helper to clean up number format (European: 1.000,50 → 1000.50)
	sanitizeNumber := func(s string) string {
		s = strings.ReplaceAll(s, ".", "")  // remove thousands separator
		s = strings.ReplaceAll(s, ",", ".") // convert decimal comma to dot
		return s
	}

	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	selectQuery := `
		SELECT COUNT(*) 
		FROM material_movements 
		WHERE material = ? AND material_description = ? AND material_document = ? AND 
		posting_date = ? AND entry_date = ? AND document_date = ? AND 
		plant = ? AND storage_location = ? AND movement_type = ? AND 
		qty_in_unit_entry = ? AND base_unit_of_measure = ?`

	updateQuery := `
		UPDATE material_movements 
		SET material_description = ?, posting_date = ?, entry_date = ?, 
		document_date = ?, plant = ?, storage_location = ?, movement_type = ?, 
		qty_in_unit_entry = ?, base_unit_of_measure = ? 
		WHERE material = ? AND material_document = ?`

	insertQuery := `
		INSERT INTO material_movements (material, material_description, material_document, posting_date, entry_date, document_date, 
		plant, storage_location, movement_type, qty_in_unit_entry, base_unit_of_measure) 
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	for i, row := range rows {
		if i == 0 {
			continue // Skip header
		}

		if len(row) < 14 {
			log.Printf("Skipping row %d: insufficient columns (%d columns): %v", i, len(row), row)
			continue
		}

		material := row[0]
		materialDocument := row[6]
		materialDescription := row[11]
		postingDateStr := row[8]
		qty := sanitizeNumber(row[9]) // Cleaned qty (e.g., 1.000,00 → 1000.00)
		unit := row[10]
		plant := row[2]
		storage := row[3]
		movementType := row[4]

		// Optional: Parse amount if needed later
		// amount := sanitizeNumber(row[12])
		// quantity := sanitizeNumber(row[13])

		// Parse date (e.g., 3/14/2025)
		parseDateConv, err := parseDateInOut(postingDateStr)
		if err != nil {
			log.Printf("Failed to parse posting_date for row %d: %v", i, err)
			tx.Rollback()
			return fmt.Errorf("failed to parse posting_date for row %d: %w", i, err)
		}

		// Check if record already exists
		existingCount := 0
		err = tx.QueryRow(selectQuery,
			material, materialDescription, materialDocument, parseDateConv, parseDateConv, parseDateConv,
			plant, storage, movementType, qty, unit).Scan(&existingCount)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("failed to check existing data for row %d: %w", i, err)
		}

		if existingCount > 0 {
			// Update existing record
			_, err = tx.Exec(updateQuery,
				materialDescription, parseDateConv, parseDateConv, parseDateConv,
				plant, storage, movementType, qty, unit,
				material, materialDocument)
			if err != nil {
				log.Printf("Failed to update row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to update row %d: %w", i, err)
			}
		} else {
			// Insert new record
			_, err = tx.Exec(insertQuery,
				material, materialDescription, materialDocument, parseDateConv, parseDateConv, parseDateConv,
				plant, storage, movementType, qty, unit)
			if err != nil {
				log.Printf("Failed to insert row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to insert row %d: %w", i, err)
			}
		}
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	log.Println("CSV processing completed successfully")
	return nil
}

func ProcessAndInsertWIPOut() error {
	log.Println("Started processing CSV for material_movements")
	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	filePath := cfg.CSVFilePathOutWIP
	file, err := os.Open(filePath)
	if err != nil {
		return fmt.Errorf("failed to open CSV file: %w", err)
	}
	defer file.Close()

	reader := csv.NewReader(file)
	reader.Comma = ';' // Set the delimiter to ;

	rows, err := reader.ReadAll()
	if err != nil {
		return fmt.Errorf("failed to read CSV file: %w", err)
	}

	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	selectQuery := `
		SELECT COUNT(*) 
		FROM material_movements 
		WHERE material = ? AND material_description = ? AND material_document = ? AND 
		posting_date = ? AND entry_date = ? AND document_date = ? AND 
		plant = ? AND storage_location = ? AND movement_type = ? AND 
		qty_in_unit_entry = ? AND base_unit_of_measure = ?`

	updateQuery := `
		UPDATE material_movements 
		SET material_description = ?, posting_date = ?, entry_date = ?, 
		document_date = ?, plant = ?, storage_location = ?, movement_type = ?, 
		qty_in_unit_entry = ?, base_unit_of_measure = ? 
		WHERE material = ? AND material_document = ?`

	insertQuery := `
		INSERT INTO material_movements (material, material_description, material_document, posting_date, entry_date, document_date, 
		plant, storage_location, movement_type, qty_in_unit_entry, base_unit_of_measure) 
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	for i, row := range rows {
		if i == 0 {
			continue // Skip the header row
		}

		parseDateConv, err := parseDateInOut(row[4]) // Example assumes posting_date is in row[4]
		if err != nil {
			log.Printf("Failed to parse posting_date for row %d: %v", i, err)
			tx.Rollback()
			return fmt.Errorf("failed to parse posting_date for row %d: %w", i, err)
		}

		output := strings.Replace(row[8], "-", "", -1)

		existingCount := 0
		err = tx.QueryRow(selectQuery,
			row[0], row[1], row[2], parseDateConv, parseDateConv, parseDateConv,
			row[5], row[6], row[7], output, row[9]).Scan(&existingCount)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("failed to check existing data for row %d: %w", i, err)
		}

		if existingCount > 0 {
			fmt.Println("update")
			// Update if there are differences
			_, err = tx.Exec(updateQuery,
				row[1], parseDateConv, parseDateConv, parseDateConv,
				row[5], row[6], row[7], output, row[9],
				row[0], row[2])
			if err != nil {
				log.Printf("Failed to update row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to update row %d: %w", i, err)
			}
		} else {
			fmt.Println("insert")
			// Insert if no match is found
			_, err = tx.Exec(insertQuery,
				row[0], row[1], row[2], parseDateConv, parseDateConv, parseDateConv,
				row[5], row[6], row[7], output, row[9])
			if err != nil {
				log.Printf("Failed to insert row %d: %v", i, err)
				tx.Rollback()
				return fmt.Errorf("failed to insert row %d: %w", i, err)
			}
		}
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	log.Println("CSV processing completed successfully")
	return nil
}

// nullify returns nil if the string is empty, or the original string otherwise
func nullify(value string) interface{} {
	if value == "" {
		return nil
	}
	return value
}

// parseTime converts a time string to the correct format or returns nil if invalid
func parseTime(value string) interface{} {
	if value == "" {
		return nil
	}
	parsedTime, err := time.Parse("3:04:05 PM", value)
	if err != nil {
		log.Printf("Failed to parse time '%s': %v", value, err)
		return nil
	}
	return parsedTime.Format("15:04:05")
}

// func ProcessAndInsertDataIn() error {
// 	log.Println("Started processing data from material_movements")

// 	cfg := config.LoadConfig()

// 	db, err := config.ConnectToDatabase(cfg)
// 	if err != nil {
// 		return fmt.Errorf("failed to connect to database: %v", err)
// 	}
// 	defer db.Close()

// 	// Query to select data based on conditions
// 	query := `SELECT cost_center, ` + "`order`" + `, material, material_description,
//         qty_in_unit_entry, base_unit_of_measure, movement_type, movement_type_text,
//         storage_location, plant, user_name, material_document,
//         document_date, entry_date, posting_date, time_of_entry,
//         amount_in_local_currency, currency, movement_indicator, special_stock,
//         reference, document_header_text FROM material_movements
//         WHERE qty_in_unit_entry < 0 OR qty_in_unit_entry > 0`

// 	rows, err := db.Query(query)
// 	if err != nil {
// 		return fmt.Errorf("failed to select data from material_movements: %w", err)
// 	}
// 	defer rows.Close()

// 	for rows.Next() {
// 		var (
// 			costCenter, orderField, material, materialDescription             string
// 			qtyInUnitEntry, baseUnitOfMeasure, movementType, movementTypeText string
// 			storageLocation, plant, userName, materialDocument                string
// 			documentDate, entryDate, postingDate, timeOfEntry                 sql.NullString
// 			amountInLocalCurrency, currency, movementIndicator, specialStock  string
// 			reference, documentHeaderText                                     string
// 		)

// 		err := rows.Scan(
// 			&costCenter, &orderField, &material, &materialDescription,
// 			&qtyInUnitEntry, &baseUnitOfMeasure, &movementType, &movementTypeText,
// 			&storageLocation, &plant, &userName, &materialDocument,
// 			&documentDate, &entryDate, &postingDate, &timeOfEntry,
// 			&amountInLocalCurrency, &currency, &movementIndicator, &specialStock,
// 			&reference, &documentHeaderText,
// 		)
// 		if err != nil {
// 			log.Printf("Failed to scan row: %v", err)
// 			continue
// 		}

// 		// Determine target tables based on qty_in_unit_entry
// 		var hdrTable, detTable string
// 		if qtyInUnitEntry[0] == '-' { // Negative value
// 			hdrTable = "mat_outhdr_sub"
// 			detTable = "mat_outdet_sub"
// 		} else { // Positive value
// 			hdrTable = "mat_inchdr_sub"
// 			detTable = "mat_incdet_sub"
// 		}

// 		tx, err := db.Begin()
// 		if err != nil {
// 			return fmt.Errorf("failed to begin transaction: %w", err)
// 		}

// 		hdrQuery := `INSERT INTO ` + hdrTable + ` (cost_center, orderField, material, material_description,
//             qty_in_unit_entry, base_unit_of_measure, movement_type, movement_type_text, storage_location, plant, user_name,
//             material_document, document_date, entry_date, posting_date, time_of_entry, amount_in_local_currency, currency,
//             movement_indicator, special_stock, reference, document_header_text)
//             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

// 		_, err = tx.Exec(hdrQuery, costCenter, orderField, material, materialDescription,
// 			qtyInUnitEntry, baseUnitOfMeasure, movementType, movementTypeText,
// 			storageLocation, plant, userName, materialDocument,
// 			documentDate, entryDate, postingDate, timeOfEntry,
// 			amountInLocalCurrency, currency, movementIndicator, specialStock,
// 			reference, documentHeaderText)

// 		if err != nil {
// 			tx.Rollback()
// 			log.Printf("Failed to insert into %s: %v", hdrTable, err)
// 			continue
// 		}

// 		detQuery := `INSERT INTO ` + detTable + ` (material_document, material, qty_in_unit_entry)
//             VALUES (?, ?, ?)`

// 		_, err = tx.Exec(detQuery, materialDocument, material, qtyInUnitEntry)
// 		if err != nil {
// 			tx.Rollback()
// 			log.Printf("Failed to insert into %s: %v", detTable, err)
// 			continue
// 		}

// 		if err := tx.Commit(); err != nil {
// 			log.Printf("Failed to commit transaction: %v", err)
// 			return fmt.Errorf("failed to commit transaction: %w", err)
// 		}
// 	}

// 	log.Println("Data processing completed successfully")
// 	return nil
// }

func ProcessAndInsertDataIn() error {
	log.Println("Started processing data from material_movements")

	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	// Query to select data based on conditions
	query := `SELECT IFNULL(cost_center,""), IFNULL(` + "`order`" + `,""), IFNULL(material,""), IFNULL(material_description,""), 
        IFNULL(qty_in_unit_entry,0), IFNULL(base_unit_of_measure,""), IFNULL(movement_type,""),IFNULL(movement_type_text,""), 
        IFNULL(storage_location,""), IFNULL(plant,""), IFNULL(user_name,""), IFNULL(material_document,""), 
        IFNULL(document_date,""), IFNULL(entry_date,""), IFNULL(posting_date,""), IFNULL(time_of_entry,""), 
        IFNULL(amount_in_local_currency,""), IFNULL(currency,""), IFNULL(movement_indicator,""), IFNULL(special_stock,""), 
        IFNULL(reference,""), IFNULL(document_header_text,""), IFNULL(mb.TpBarang , "")
		FROM
			material_movements mv
		left join mst_barang mb on
			mv.material = mb.KdBarang
		WHERE  movement_type in ('101')`

	rows, err := db.Query(query)
	if err != nil {
		return fmt.Errorf("failed to select data from material_movements: %w", err)
	}
	defer rows.Close()

	var lastMatinID, currentChildNoIn int64
	for rows.Next() {
		var (
			costCenter, orderField, material, materialDescription             string
			qtyInUnitEntry, baseUnitOfMeasure, movementType, movementTypeText string
			storageLocation, plant, userName, materialDocument                string
			documentDate, entryDate, postingDate, timeOfEntry                 sql.NullString
			amountInLocalCurrency, currency, movementIndicator, specialStock  string
			reference, documentHeaderText, TpBarang                           string
		)

		err := rows.Scan(
			&costCenter, &orderField, &material, &materialDescription,
			&qtyInUnitEntry, &baseUnitOfMeasure, &movementType, &movementTypeText,
			&storageLocation, &plant, &userName, &materialDocument,
			&documentDate, &entryDate, &postingDate, &timeOfEntry,
			&amountInLocalCurrency, &currency, &movementIndicator, &specialStock,
			&reference, &documentHeaderText, &TpBarang,
		)
		if err != nil {
			log.Printf("Failed to scan row: %v", err)
			continue
		}

		// Determine target tables based on qty_in_unit_entry

		if err != nil {
			return fmt.Errorf("failed to begin transaction: %w", err)
		}

		documentNumber := materialDocument
		wh_id := storageLocation
		po_no := reference
		matin_type := getMovementValue(movementType)
		documentDateConv, _ := nullStringToTime(documentDate, "2006-01-02")
		entryDateConv, _ := nullStringToTime(entryDate, "2006-01-02")
		partNo := materialDescription
		mat_id := material

		qtyInUnitEntryConv, _ := stringToFloat(qtyInUnitEntry)
		var headerID int64
		// if qtyInUnitEntryConv < 0 {
		// 	// Insert into "out" table (mat_outhdr_sub)
		// 	headerID = insertHeader_out(db, "mat_outhdr_sub", documentNumber, documentDateConv, entryDateConv, strconv.Itoa(matin_type), currency, userName, wh_id, po_no, TpBarang)

		// 	// Reset child_no if we have a new matout_id
		// 	if lastMatoutID != headerID {
		// 		currentChildNoOut = 0
		// 		lastMatoutID = headerID
		// 	}
		// 	insertDetail_out(db, "mat_outdet_sub", headerID, partNo, mat_id, &currentChildNoOut, qtyInUnitEntryConv) // Pass qty directly
		// } else

		if qtyInUnitEntryConv > 0 {
			// Insert into "in" table (mat_inchdr_sub)
			headerID = insertHeader_in(db, "mat_inchdr_sub", documentNumber, documentDateConv, entryDateConv, strconv.Itoa(matin_type), currency, userName, wh_id, po_no, TpBarang)

			// Reset child_no if we have a new matin_id
			if lastMatinID != headerID {
				currentChildNoIn = 0
				lastMatinID = headerID
			}

			insertDetail_in(db, "mat_incdet_sub", headerID, partNo, mat_id, &currentChildNoIn, qtyInUnitEntryConv) // Pass qty directly
		}
	}

	log.Println("Data processing completed successfully")
	return nil
}

func ProcessAndInsertDataOut() error {
	log.Println("Started processing data from material_movements")

	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	// Query to select data based on conditions
	query := `SELECT IFNULL(cost_center,""), IFNULL(` + "`order`" + `,""), IFNULL(material,""), IFNULL(material_description,""), 
        IFNULL(qty_in_unit_entry,0), IFNULL(base_unit_of_measure,""), IFNULL(movement_type,""),IFNULL(movement_type_text,""), 
        IFNULL(storage_location,""), IFNULL(plant,""), IFNULL(user_name,""), IFNULL(material_document,""), 
        IFNULL(document_date,""), IFNULL(entry_date,""), IFNULL(posting_date,""), IFNULL(time_of_entry,""), 
        IFNULL(amount_in_local_currency,""), IFNULL(currency,""), IFNULL(movement_indicator,""), IFNULL(special_stock,""), 
        IFNULL(reference,""), IFNULL(document_header_text,""), IFNULL(mb.TpBarang , "")
		FROM
			material_movements mv
		left join mst_barang mb on
			mv.material = mb.KdBarang
		WHERE  movement_type not in ('101')`

	rows, err := db.Query(query)
	if err != nil {
		return fmt.Errorf("failed to select data from material_movements: %w", err)
	}
	defer rows.Close()

	var lastMatoutID, currentChildNoOut int64
	for rows.Next() {
		var (
			costCenter, orderField, material, materialDescription             string
			qtyInUnitEntry, baseUnitOfMeasure, movementType, movementTypeText string
			storageLocation, plant, userName, materialDocument                string
			documentDate, entryDate, postingDate, timeOfEntry                 sql.NullString
			amountInLocalCurrency, currency, movementIndicator, specialStock  string
			reference, documentHeaderText, TpBarang                           string
		)

		err := rows.Scan(
			&costCenter, &orderField, &material, &materialDescription,
			&qtyInUnitEntry, &baseUnitOfMeasure, &movementType, &movementTypeText,
			&storageLocation, &plant, &userName, &materialDocument,
			&documentDate, &entryDate, &postingDate, &timeOfEntry,
			&amountInLocalCurrency, &currency, &movementIndicator, &specialStock,
			&reference, &documentHeaderText, &TpBarang,
		)
		if err != nil {
			log.Printf("Failed to scan row: %v", err)
			continue
		}

		// Determine target tables based on qty_in_unit_entry

		if err != nil {
			return fmt.Errorf("failed to begin transaction: %w", err)
		}

		documentNumber := materialDocument
		wh_id := storageLocation
		po_no := reference
		matin_type := getMovementValue(movementType)
		documentDateConv, _ := nullStringToTime(documentDate, "2006-01-02")
		entryDateConv, _ := nullStringToTime(entryDate, "2006-01-02")
		partNo := materialDescription
		mat_id := material

		qtyInUnitEntryConv, _ := stringToFloat(qtyInUnitEntry)
		var headerID int64
		if qtyInUnitEntryConv > 0 {
			// Insert into "out" table (mat_outhdr_sub)
			headerID = insertHeader_out(db, "mat_outhdr_sub", documentNumber, documentDateConv, entryDateConv, strconv.Itoa(matin_type), currency, userName, wh_id, po_no, TpBarang)

			// Reset child_no if we have a new matout_id
			if lastMatoutID != headerID {
				currentChildNoOut = 0
				lastMatoutID = headerID
			}
			insertDetail_out(db, "mat_outdet_sub", headerID, partNo, mat_id, &currentChildNoOut, qtyInUnitEntryConv) // Pass qty directly
		}
		// else {
		// 	// Insert into "in" table (mat_inchdr_sub)
		// 	headerID = insertHeader_in(db, "mat_inchdr_sub", documentNumber, documentDateConv, entryDateConv, strconv.Itoa(matin_type), currency, userName, wh_id, po_no, TpBarang)

		// 	// Reset child_no if we have a new matin_id
		// 	if lastMatinID != headerID {
		// 		currentChildNoIn = 0
		// 		lastMatinID = headerID
		// 	}

		// 	insertDetail_in(db, "mat_incdet_sub", headerID, partNo, mat_id, &currentChildNoIn, qtyInUnitEntryConv) // Pass qty directly
		// }
	}

	log.Println("Data processing completed successfully")
	return nil
}

// Function to convert sql.NullString to time.Time
func nullStringToTime(nullStr sql.NullString, format string) (time.Time, error) {
	if !nullStr.Valid || nullStr.String == "" {
		return time.Time{}, nil // Return zero time if null or empty
	}
	parsedTime, err := time.Parse(format, nullStr.String)
	if err != nil {
		return time.Time{}, err
	}
	return parsedTime, nil
}

func stringToFloat(str string) (float64, error) {
	if str == "" {
		return 0, nil // Return 0 for empty strings
	}
	value, err := strconv.ParseFloat(str, 64)
	if err != nil {
		return 0, fmt.Errorf("failed to parse float: %v", err)
	}
	return value, nil
}

func getMovementValue(move string) int {
	if move == "101" {
		return 0
	} else if move == "102" {
		return 1
	} else if move == "107" {
		return 0
	} else if move == "109" {
		return 0
	} else if move == "122" {
		return 1
	} else if move == "201" {
		return 0
	} else if move == "202" {
		return 1
	} else if move == "261" {
		return 0
	} else if move == "262" {
		return 1
	} else if move == "311" {
		return 0
	} else if move == "321" {
		return 1
	} else if move == "322" {
		return 2
	} else if move == "343" {
		return 3
	} else if move == "531" {
		return 5
	} else if move == "532" {
		return 1
	} else if move == "541" {
		return 2
	} else if move == "542" {
		return 4
	} else if move == "543" {
		return 2
	} else if move == "544" {
		return 4
	} else if move == "522" {
		return 7
	} else if move == "601" {
		return 4
	} else if move == "641" {
		return 0
	} else if move == "687" {
		return 1
	} else if move == "688" {
		return 1
	}
	return -1 // Default case if the move code is not found
}

func ProcessAndInsertDataOpname() error {
	log.Println("Started processing data from material_movements")

	cfg := config.LoadConfig()

	db, err := config.ConnectToDatabase(cfg)
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}
	defer db.Close()

	// Query to select data based on conditions
	query := `SELECT IFNULL(cost_center,""), IFNULL(` + "`order`" + `,""), IFNULL(material,""), IFNULL(material_description,""), 
        IFNULL(qty_in_unit_entry,0), IFNULL(base_unit_of_measure,""), IFNULL(movement_type,""),IFNULL(movement_type_text,""), 
        IFNULL(storage_location,""), IFNULL(plant,""), IFNULL(user_name,""), IFNULL(material_document,""), 
        IFNULL(document_date,""), IFNULL(entry_date,""), IFNULL(posting_date,""), IFNULL(time_of_entry,""), 
        IFNULL(amount_in_local_currency,""), IFNULL(currency,""), IFNULL(movement_indicator,""), IFNULL(special_stock,""), 
        IFNULL(reference,""), IFNULL(document_header_text,""), IFNULL(mb.TpBarang , "")
		FROM
			material_movements mv
		left join mst_barang mb on
			mv.material = mb.KdBarang
		WHERE (qty_in_unit_entry < 0 OR qty_in_unit_entry > 0) and movement_type in ('701','702')`

	rows, err := db.Query(query)
	if err != nil {
		return fmt.Errorf("failed to select data from material_movements: %w", err)
	}
	defer rows.Close()

	var lastMatinID, lastMatoutID, currentChildNoOut, currentChildNoIn int64
	for rows.Next() {
		var (
			costCenter, orderField, material, materialDescription             string
			qtyInUnitEntry, baseUnitOfMeasure, movementType, movementTypeText string
			storageLocation, plant, userName, materialDocument                string
			documentDate, entryDate, postingDate, timeOfEntry                 sql.NullString
			amountInLocalCurrency, currency, movementIndicator, specialStock  string
			reference, documentHeaderText, TpBarang                           string
		)

		err := rows.Scan(
			&costCenter, &orderField, &material, &materialDescription,
			&qtyInUnitEntry, &baseUnitOfMeasure, &movementType, &movementTypeText,
			&storageLocation, &plant, &userName, &materialDocument,
			&documentDate, &entryDate, &postingDate, &timeOfEntry,
			&amountInLocalCurrency, &currency, &movementIndicator, &specialStock,
			&reference, &documentHeaderText, &TpBarang,
		)
		if err != nil {
			log.Printf("Failed to scan row: %v", err)
			continue
		}

		// Determine target tables based on qty_in_unit_entry

		if err != nil {
			return fmt.Errorf("failed to begin transaction: %w", err)
		}

		documentNumber := materialDocument
		wh_id := storageLocation
		po_no := reference
		matin_type := getMovementValue(movementType)
		documentDateConv, _ := nullStringToTime(documentDate, "2006-01-02")
		entryDateConv, _ := nullStringToTime(entryDate, "2006-01-02")
		partNo := materialDescription
		mat_id := material

		qtyInUnitEntryConv, _ := stringToFloat(qtyInUnitEntry)
		var headerID int64
		if qtyInUnitEntryConv < 0 {
			// Insert into "out" table (mat_outhdr_sub)
			headerID = insertHeader_out(db, "mat_outhdr_sub", documentNumber, documentDateConv, entryDateConv, strconv.Itoa(matin_type), currency, userName, wh_id, po_no, TpBarang)

			// Reset child_no if we have a new matout_id
			if lastMatoutID != headerID {
				currentChildNoOut = 0
				lastMatoutID = headerID
			}
			insertDetail_out(db, "mat_outdet_sub", headerID, partNo, mat_id, &currentChildNoOut, qtyInUnitEntryConv) // Pass qty directly
		} else {
			// Insert into "in" table (mat_inchdr_sub)
			headerID = insertHeader_opname(db, "mat_opnamehdr_sub", documentNumber, documentDateConv, entryDateConv, strconv.Itoa(matin_type), currency, userName, wh_id, po_no, TpBarang)

			// Reset child_no if we have a new matin_id
			if lastMatinID != headerID {
				currentChildNoIn = 0
				lastMatinID = headerID
			}

			insertDetail_opname(db, "mat_opnamedet_sub", headerID, partNo, mat_id, &currentChildNoIn, qtyInUnitEntryConv) // Pass qty directly
		}
	}

	log.Println("Data processing completed successfully")
	return nil
}

func insertHeader_opname(db *sql.DB, table, documentNumber string, documentDate, entryDate time.Time, matin_type, currency, userName, wh_id, po_no, TpBarang string) int64 {
	var existingMatoutID int64
	checkExisting := "SELECT opname_no FROM mat_opnamehdr_sub WHERE opname_no = ? LIMIT 1"
	err := db.QueryRow(checkExisting, documentNumber).Scan(&existingMatoutID)

	if err == sql.ErrNoRows {
		// No existing entry, insert a new one
		query := fmt.Sprintf("INSERT INTO %s (opname_no, opname_date, wh_id, mat_type, kd_cmpy, opname_type) VALUES (?, ?, ?, ?, ?, ?)", table)
		_, err := db.Exec(query, documentNumber, documentDate, wh_id, TpBarang, "", matin_type)
		if err != nil {
			log.Fatal(err)
		}
		// Now retrieve the matout_id
		err = db.QueryRow(checkExisting, documentNumber).Scan(&existingMatoutID)
		if err != nil {
			log.Fatal(err)
		}
	} else if err != nil {
		log.Fatal(err)
	}

	return existingMatoutID
}

func insertDetail_opname(db *sql.DB, table string, headerID int64, partNo, mat_id string, childNo *int64, qty float64) (int64, error) {
	// Increment child_no
	*childNo++

	// Build the SQL query string
	query := fmt.Sprintf("INSERT INTO %s (opname_id, child_no, mat_id, qty, dqty, poqty, mat_id) VALUES (?, ?, ?, ?, ?, ?, ?)", table)

	// Use absolute value for qty, as it's coming to the "in" table (always positive)
	quantity := math.Abs(qty)

	// Execute the query
	res, err := db.Exec(query, headerID, *childNo, mat_id, quantity, quantity, quantity, partNo)
	if err != nil {
		return 0, err // Return an error if the database execution fails
	}

	// Fetch the last inserted ID
	lastInsertID, err := res.LastInsertId()
	if err != nil {
		return 0, err // Return an error if fetching the last inserted ID fails
	}

	// Ensure there's always a return statement
	return lastInsertID, nil
}
