package cron_mst

import (
	"database/sql"
	"encoding/csv"
	"fmt"
	"log"
	"os"

	"golang-csv/config"
)

// -- trx SO and PO -- //
func ProcessCSVSupp() 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.CSVFilePathSup

	// 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...")

	// Create a map to track which PO headers have already been inserted

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

		err = InsertMstSupp(db, row)
		if err != nil {
			log.Printf("Failed to insert into pur_hd at row %d: %v", i, err)
			continue
		}

	}

	log.Println("CSV processing completed successfully")

	return nil
}

func InsertMstSupp(db *sql.DB, row []string) error {

	// Step 1: Check if the record with po_id and mat_id exists
	var existingID string
	checkQuery := `
		SELECT KdPrshn FROM mst_perusahaan WHERE KdPrshn = ? AND TpPrshn = ? LIMIT 1`
	err := db.QueryRow(checkQuery, row[0], "s").Scan(&existingID)

	// Step 2: If the record exists, update it
	if err == nil {
		fmt.Println("Record exists, update it")
		// Record exists, update it
		updateQuery := `
			UPDATE mst_perusahaan
			SET NmPrshn = ?, AlmtPrshn = ?, Kota = ?, KdPos = ?, tlp = ?,
			Valuta = ?, Negara = ?
			WHERE KdPrshn = ? AND TpPrshn = ? `
		_, err = db.Exec(updateQuery, row[1], row[2], row[5], row[4], "", row[20], row[3], string(row[0]), "s")
		if err != nil {
			return err
		}
	} else if err == sql.ErrNoRows {
		// Step 3: If no matching record, insert a new record
		fmt.Println("no matching record, insert a new record")
		insertQuery := `
			INSERT INTO mst_perusahaan (KdPrshn, TpPrshn, NmPrshn, AlmtPrshn, Kota, KdPos, tlp, Valuta,Negara)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)`
		_, err = db.Exec(insertQuery, string(row[0]), "s", row[1], row[2], row[5], row[4], "", row[20], row[3])
		if err != nil {
			return err
		}
	} else {
		// Error checking for existing record
		log.Println("Error checking for existing record:", err)
		return err
	}
	return err

}

func ProcessCSVCust() error {
	log.Println("Entered processCSVCust 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.CSVFilePathCust

	// 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...")

	// Create a map to track which PO headers have already been inserted

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

		err = InsertMstCust(db, row)
		if err != nil {
			log.Printf("Failed to insert into pur_hd at row %d: %v", i, err)
			continue
		}

	}

	log.Println("CSV processing completed successfully")

	return nil
}

func InsertMstCust(db *sql.DB, row []string) error {

	// Step 1: Check if the record with po_id and mat_id exists
	var existingID string
	checkQuery := `
		SELECT KdPrshn FROM mst_perusahaan WHERE KdPrshn = ? AND TpPrshn = ? LIMIT 1`
	err := db.QueryRow(checkQuery, row[0], "c").Scan(&existingID)

	// Step 2: If the record exists, update it
	if err == nil {
		// Record exists, update it
		fmt.Println("Record exists, update it")
		updateQuery := `
			UPDATE mst_perusahaan
			SET NmPrshn = ?, AlmtPrshn = ?, Kota = ?, KdPos = ?, tlp = ?,
			Valuta = ?, Negara = ?
			WHERE KdPrshn = ? AND TpPrshn = ? `
		_, err = db.Exec(updateQuery, row[2], row[8], row[3], row[4], row[9], "", row[1], row[0], "c")
		if err != nil {
			return err
		}
	} else if err == sql.ErrNoRows {
		// Step 3: If no matching record, insert a new record
		fmt.Println("no matching record, insert a new record")
		insertQuery := `
			INSERT INTO mst_perusahaan (KdPrshn, TpPrshn, NmPrshn, AlmtPrshn, Kota, KdPos, tlp, Valuta,Negara)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)`
		_, err = db.Exec(insertQuery, row[0], "c", row[2], row[8], row[3], row[4], row[9], "", row[1])
		if err != nil {
			return err
		}
	} else {
		// Error checking for existing record
		log.Println("Error checking for existing record:", err)
		return err
	}
	return err

}

func ProcessCSVItem() 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.CSVFilePathItem

	// 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...")

	// Create a map to track which PO headers have already been inserted

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

		err = InsertMstItem(db, row)
		if err != nil {
			log.Printf("Failed to insert into pur_hd at row %d: %v", i, err)
			continue
		}

	}

	log.Println("CSV processing completed successfully")

	return nil
}

func InsertMstItem(db *sql.DB, row []string) error {

	// Step 1: Check if the record with po_id and mat_id exists
	var existingID string

	jnsBarang := "-"
	if row[12] == "ZFRT" {
		jnsBarang = "0"
	} else if row[12] == "ZHIB" {
		jnsBarang = "5"
	} else if row[12] == "ZHLB" {
		jnsBarang = "11"
	} else if row[12] == "ZROH" {
		jnsBarang = "1"
	} else if row[12] == "ZRSA" {
		jnsBarang = "3"
	} else if row[12] == "ZVRP" {
		jnsBarang = "9"
	}
	checkQuery := `
		SELECT KdBarang FROM mst_barang WHERE KdBarang = ? LIMIT 1`
	err := db.QueryRow(checkQuery, row[0]).Scan(&existingID)

	// Step 2: If the record exists, update it
	if err == nil {
		// Record exists, update it
		updateQuery := `
			UPDATE mst_barang
			SET TpBarang = ?, MatGroup = ?, NmBarang = ?, sat = ?
			WHERE KdBarang = ?`
		_, err = db.Exec(updateQuery, jnsBarang, row[13], row[2], row[23], row[0])
		if err != nil {
			return err
		}
	} else if err == sql.ErrNoRows {
		// Step 3: If no matching record, insert a new record
		insertQuery := `
			INSERT INTO mst_barang (KdBarang, TpBarang, MatGroup, NmBarang, sat)
			VALUES (?, ?, ?, ?, ?)`
		_, err = db.Exec(insertQuery, row[0], jnsBarang, row[13], row[2], row[23])
		if err != nil {
			return err
		}
	} else {
		// Error checking for existing record
		log.Println("Error checking for existing record:", err)
		return err
	}
	return err

}
