package infra

import (
	"database/sql"
	"fmt"
	"sort"
	"strings"
	"time"
)

// MigrationGetStatus crosses migration files on disk with records in _migrations,
// returning a sorted list of MigrationStatus entries ordered by version ascending.
// Migrations present in _migrations but not on disk are included with Applied=true
// and marked as orphaned in their Name (suffix " (orphaned)").
// If _migrations does not exist yet, all file-based migrations are returned as pending.
func MigrationGetStatus(db *sql.DB, dir string) ([]MigrationStatus, error) {
	// Load files from disk (non-fatal if dir is empty or missing)
	fileMigrations, err := loadMigrationsFromDir(dir)
	if err != nil && !strings.Contains(err.Error(), "cannot read migrations directory") {
		return nil, fmt.Errorf("migration_status: %w", err)
	}
	if err != nil {
		// Directory doesn't exist — treat as empty
		fileMigrations = nil
	}

	// Build a map from version to file migration
	fileMap := make(map[int]Migration, len(fileMigrations))
	for _, m := range fileMigrations {
		fileMap[m.Version] = m
	}

	// Load applied migrations from DB (_migrations may not exist yet)
	dbMap, err := loadAppliedMigrations(db)
	if err != nil {
		return nil, fmt.Errorf("migration_status: %w", err)
	}

	// Merge: collect all known versions
	allVersions := make(map[int]struct{})
	for v := range fileMap {
		allVersions[v] = struct{}{}
	}
	for v := range dbMap {
		allVersions[v] = struct{}{}
	}

	// Build status list
	var statuses []MigrationStatus
	for v := range allVersions {
		dbEntry, inDB := dbMap[v]
		fileEntry, inFile := fileMap[v]

		name := fileEntry.Name
		if !inFile && inDB {
			// Orphaned: applied but no file on disk
			name = dbEntry.Name + " (orphaned)"
		}

		statuses = append(statuses, MigrationStatus{
			Version:   v,
			Name:      name,
			Applied:   inDB,
			AppliedAt: dbEntry.AppliedAt, // zero value if not in DB
		})
	}

	sort.Slice(statuses, func(i, j int) bool {
		return statuses[i].Version < statuses[j].Version
	})

	return statuses, nil
}

// migrationDBRecord holds data from the _migrations table.
type migrationDBRecord struct {
	Name      string
	AppliedAt time.Time
}

// loadAppliedMigrations queries _migrations and returns a map of version -> record.
// If the table does not exist, returns an empty map without error.
func loadAppliedMigrations(db *sql.DB) (map[int]migrationDBRecord, error) {
	result := make(map[int]migrationDBRecord)

	rows, err := db.Query("SELECT version, name, applied_at FROM _migrations ORDER BY version ASC")
	if err != nil {
		// Table doesn't exist yet — return empty map
		if strings.Contains(err.Error(), "no such table") {
			return result, nil
		}
		return nil, fmt.Errorf("query _migrations: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var version int
		var name, appliedAtStr string
		if err := rows.Scan(&version, &name, &appliedAtStr); err != nil {
			return nil, fmt.Errorf("scan _migrations row: %w", err)
		}
		appliedAt, _ := time.Parse("2006-01-02 15:04:05", appliedAtStr)
		result[version] = migrationDBRecord{Name: name, AppliedAt: appliedAt}
	}
	return result, rows.Err()
}