package db

import (
	"database/sql"
	"errors"
	"fmt"
	"io/fs"
	"path"
	"regexp"
	"sort"
	"strconv"
	"time"
)

// migrationFilenameRe matches files named `<version>_<name>.sql`.
// Version is parsed as int; name is descriptive only.
var migrationFilenameRe = regexp.MustCompile(`^(\d+)_([A-Za-z0-9_\-]+)\.sql$`)

// Migration is one SQL file from the migrations directory.
type Migration struct {
	Version int
	Name    string
	Body    string
}

// LoadMigrations walks fsys for files matching `\d+_<name>.sql`, returning
// them sorted by version ascending. Non-matching files are ignored so that
// READMEs or test fixtures can live alongside without breaking the runner.
func LoadMigrations(fsys fs.FS) ([]Migration, error) {
	entries, err := fs.ReadDir(fsys, ".")
	if err != nil {
		return nil, fmt.Errorf("migrations: read dir: %w", err)
	}
	var out []Migration
	for _, e := range entries {
		if e.IsDir() {
			continue
		}
		m := migrationFilenameRe.FindStringSubmatch(e.Name())
		if m == nil {
			continue
		}
		version, err := strconv.Atoi(m[1])
		if err != nil {
			return nil, fmt.Errorf("migrations: bad version in %s: %w", e.Name(), err)
		}
		body, err := fs.ReadFile(fsys, path.Clean(e.Name()))
		if err != nil {
			return nil, fmt.Errorf("migrations: read %s: %w", e.Name(), err)
		}
		out = append(out, Migration{
			Version: version,
			Name:    m[2],
			Body:    string(body),
		})
	}
	sort.Slice(out, func(i, j int) bool { return out[i].Version < out[j].Version })

	// Detect duplicates.
	for i := 1; i < len(out); i++ {
		if out[i].Version == out[i-1].Version {
			return nil, fmt.Errorf("migrations: duplicate version %d (%s, %s)",
				out[i].Version, out[i-1].Name, out[i].Name)
		}
	}
	return out, nil
}

// Apply runs every migration whose version is greater than the highest one
// previously recorded in `schema_migrations`. The schema_migrations table is
// created if absent so that the very first migration can include it (or not,
// as Phase 0's 001_phase0_foundation.sql does — both work).
//
// Each migration is wrapped in a transaction; a failure rolls back that
// migration and aborts the run, leaving prior migrations applied.
//
// Apply is idempotent: running it twice with no new migrations is a no-op.
func Apply(db *sql.DB, migrations []Migration) (applied []Migration, err error) {
	if db == nil {
		return nil, errors.New("migrations: nil db")
	}

	if _, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS schema_migrations (
			version    INTEGER PRIMARY KEY,
			applied_at INTEGER NOT NULL,
			name       TEXT    NOT NULL
		);`); err != nil {
		return nil, fmt.Errorf("migrations: ensure schema_migrations: %w", err)
	}

	applied = make([]Migration, 0)
	for _, m := range migrations {
		var exists int
		err := db.QueryRow(`SELECT COUNT(*) FROM schema_migrations WHERE version = ?`, m.Version).Scan(&exists)
		if err != nil {
			return applied, fmt.Errorf("migrations: check version %d: %w", m.Version, err)
		}
		if exists > 0 {
			continue
		}

		tx, err := db.Begin()
		if err != nil {
			return applied, fmt.Errorf("migrations: begin tx for v%d: %w", m.Version, err)
		}
		if _, err := tx.Exec(m.Body); err != nil {
			_ = tx.Rollback()
			return applied, fmt.Errorf("migrations: apply v%d (%s): %w", m.Version, m.Name, err)
		}
		if _, err := tx.Exec(
			`INSERT INTO schema_migrations(version, applied_at, name) VALUES (?, ?, ?)`,
			m.Version, time.Now().Unix(), m.Name,
		); err != nil {
			_ = tx.Rollback()
			return applied, fmt.Errorf("migrations: record v%d: %w", m.Version, err)
		}
		if err := tx.Commit(); err != nil {
			return applied, fmt.Errorf("migrations: commit v%d: %w", m.Version, err)
		}
		applied = append(applied, m)
	}
	return applied, nil
}

// CurrentVersion returns the highest applied migration version, or 0 if none.
func CurrentVersion(db *sql.DB) (int, error) {
	var v sql.NullInt64
	err := db.QueryRow(`SELECT MAX(version) FROM schema_migrations`).Scan(&v)
	if err != nil {
		return 0, err
	}
	if !v.Valid {
		return 0, nil
	}
	return int(v.Int64), nil
}