package main

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

	"github.com/joho/godotenv"
	_ "github.com/lib/pq"
)

func main() {
	pwd, _ := os.Getwd()
	log.Printf("Current Working Directory: %s", pwd)

	if err := godotenv.Load(".env"); err != nil {
		// Try loading from parent if not in root
		if err := godotenv.Load("../.env"); err != nil {
			log.Println("No .env file found")
		}
	}

	dbURL := os.Getenv("DATABASE_URL")
	if dbURL == "" {
		log.Fatal("DATABASE_URL environment variable not set")
	}

	db, err := sql.Open("postgres", dbURL)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Discover migrations directory from several probable locations
	possibleDirs := []string{
		"migrations",
		"backend/migrations",
		"../migrations",
		"/home/yamamoto/lab/gohorsejobs/backend/migrations",
	}

	var migrationsDir string
	for _, d := range possibleDirs {
		if fi, err := os.Stat(d); err == nil && fi.IsDir() {
			migrationsDir = d
			break
		}
	}

	if migrationsDir == "" {
		log.Fatal("Could not find migrations directory; looked in common locations")
	}

	entries, err := os.ReadDir(migrationsDir)
	if err != nil {
		log.Fatalf("Failed reading migrations dir %s: %v", migrationsDir, err)
	}

	var files []string
	for _, e := range entries {
		if e.IsDir() {
			continue
		}
		name := e.Name()
		if strings.HasSuffix(name, ".sql") {
			files = append(files, name)
		}
	}

	// Sort filenames to ensure chronological order
	sort.Strings(files)

	for _, migFile := range files {
		fullPath := migrationsDir + "/" + migFile
		log.Printf("Processing migration: %s (from %s)", migFile, fullPath)

		content, err := os.ReadFile(fullPath)
		if err != nil {
			log.Fatalf("Could not read migration %s: %v", fullPath, err)
		}

		// Execute the whole file. lib/pq supports multi-statement Exec.
		sqlText := string(content)
		log.Printf("Executing migration file %s", fullPath)
		_, err = db.Exec(sqlText)
		if err != nil {
			errStr := err.Error()
			// Tolerable errors: object already exists, column doesn't exist in some contexts,
			// or duplicate key when updates are guarded by intent. Log and continue.
			if strings.Contains(errStr, "already exists") || strings.Contains(errStr, "column") && strings.Contains(errStr, "does not exist") || strings.Contains(errStr, "duplicate key value violates unique constraint") {
				log.Printf("Warning while applying %s: %v", migFile, err)
				continue
			}
			log.Fatalf("Failed applying migration %s: %v", migFile, err)
		}

		log.Printf("Migration %s applied successfully", migFile)
	}

	fmt.Println("All requested migrations applied.")
}