package config

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

	_ "github.com/go-sql-driver/mysql"
	"github.com/joho/godotenv"
)

// Config holds the application configuration
type Config struct {
	DBHost            string
	DBPort            string
	DBUser            string
	DBPassword        string
	DBName            string
	CSVFilePathSO     string
	CSVFilePathPO     string
	CSVFilePathInOut  string
	CSVFilePathItem   string
	CSVFilePathCust   string
	CSVFilePathSup    string
	CSVFilePathInWIP  string
	CSVFilePathOutWIP string
	CSVFilePathMe     string
	CSVFilePathInFG   string
	CSVFilePathOutFG  string
	TIME_CRON         string
}

// LoadConfig loads configuration from environment variables
func LoadConfig() *Config {
	// Load .env file
	err := godotenv.Load()
	if err != nil {
		log.Printf("Error loading .env file: %v", err)
	}

	return &Config{
		DBHost:            os.Getenv("DB_HOST"),
		DBPort:            os.Getenv("DB_PORT"),
		DBUser:            os.Getenv("DB_USER"),
		DBPassword:        os.Getenv("DB_PASSWORD"),
		DBName:            os.Getenv("DB_NAME"),
		CSVFilePathSO:     os.Getenv("CSVFilePathSO"),
		CSVFilePathPO:     os.Getenv("CSVFilePathPO"),
		CSVFilePathInOut:  os.Getenv("CSVFilePathInOut"),
		CSVFilePathItem:   os.Getenv("CSVFilePathItem"),
		CSVFilePathCust:   os.Getenv("CSVFilePathCust"),
		CSVFilePathSup:    os.Getenv("CSVFilePathSup"),
		CSVFilePathInWIP:  os.Getenv("CSVFilePathInWIP"),
		CSVFilePathOutWIP: os.Getenv("CSVFilePathOutWIP"),
		CSVFilePathMe:     os.Getenv("CSVFilePathMe"),
		CSVFilePathInFG:   os.Getenv("CSVFilePathInFG"),
		CSVFilePathOutFG:  os.Getenv("CSVFilePathOutFG"),
		TIME_CRON:         os.Getenv("TIME_CRON"),
	}
}

// ConnectToDatabase establishes a connection to the MySQL database
func ConnectToDatabase(cfg *Config) (*sql.DB, error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
		cfg.DBUser,
		cfg.DBPassword,
		cfg.DBHost,
		cfg.DBPort,
		cfg.DBName,
	)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to connect to database: %v", err)
	}

	// Test the database connection
	err = db.Ping()
	if err != nil {
		return nil, fmt.Errorf("failed to ping database: %v", err)
	}

	log.Println("Connected to the database successfully.")
	return db, nil
}
