package storage

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"embed"
	"encoding/hex"
	"errors"
	"fmt"
	"io/fs"
	"path"
	"regexp"
	"strconv"
	"time"
)

//go:embed migrations/*.sql
var embeddedMigrations embed.FS

var migrationName = regexp.MustCompile(`^([0-9]{4})_[a-z0-9_]+\.sql$`)

type migration struct {
	version  int
	name     string
	checksum string
	sql      string
}

func loadMigrations(fsys fs.FS, directory string) ([]migration, error) {
	entries, err := fs.ReadDir(fsys, directory)
	if err != nil {
		return nil, fmt.Errorf("read migrations: %w", err)
	}
	migrations := make([]migration, 0, len(entries))
	for _, entry := range entries {
		if entry.IsDir() {
			return nil, fmt.Errorf("migration %q is a directory", entry.Name())
		}
		matches := migrationName.FindStringSubmatch(entry.Name())
		if matches == nil {
			return nil, fmt.Errorf("migration %q has invalid name", entry.Name())
		}
		version, err := strconv.Atoi(matches[1])
		if err != nil || version < 1 {
			return nil, fmt.Errorf("migration %q has invalid version", entry.Name())
		}
		content, err := fs.ReadFile(fsys, path.Join(directory, entry.Name()))
		if err != nil {
			return nil, fmt.Errorf("read migration %q: %w", entry.Name(), err)
		}
		if len(content) == 0 {
			return nil, fmt.Errorf("migration %q is empty", entry.Name())
		}
		digest := sha256.Sum256(content)
		migrations = append(migrations, migration{
			version:  version,
			name:     entry.Name(),
			checksum: hex.EncodeToString(digest[:]),
			sql:      string(content),
		})
	}
	for i, item := range migrations {
		want := i + 1
		if item.version != want {
			return nil, fmt.Errorf("migration %q has version %d, want %d", item.name, item.version, want)
		}
	}
	return migrations, nil
}

func applyMigrations(ctx context.Context, db *sql.DB, migrations []migration, now time.Time) error {
	conn, err := db.Conn(ctx)
	if err != nil {
		return classify("reserve migration connection", err)
	}
	defer conn.Close()
	var foreignKeys int
	if err := conn.QueryRowContext(ctx, `PRAGMA foreign_keys`).Scan(&foreignKeys); err != nil {
		return classify("read foreign_keys pragma", err)
	}
	if _, err := conn.ExecContext(ctx, `PRAGMA foreign_keys = OFF`); err != nil {
		return classify("turn foreign keys off for migrations", err)
	}
	err = applyMigrationsOn(ctx, conn, migrations, now)
	if _, restore := conn.ExecContext(context.WithoutCancel(ctx), `PRAGMA foreign_keys = `+strconv.Itoa(foreignKeys)); restore != nil {
		return errors.Join(err, classify("restore foreign_keys pragma", restore))
	}
	return err
}

func applyMigrationsOn(ctx context.Context, conn *sql.Conn, migrations []migration, now time.Time) error {
	tx, err := conn.BeginTx(ctx, nil)
	if err != nil {
		return classify("begin migrations", err)
	}
	defer tx.Rollback()

	if _, err := tx.ExecContext(ctx, `
CREATE TABLE IF NOT EXISTS schema_migrations (
    version INTEGER PRIMARY KEY CHECK (version > 0),
    checksum TEXT NOT NULL CHECK (length(checksum) = 64),
    applied_at INTEGER NOT NULL
) STRICT`); err != nil {
		return classify("create migration ledger", err)
	}

	rows, err := tx.QueryContext(ctx, `SELECT version, checksum FROM schema_migrations ORDER BY version`)
	if err != nil {
		return classify("read migration ledger", err)
	}
	applied := 0
	for rows.Next() {
		var version int
		var checksum string
		if err := rows.Scan(&version, &checksum); err != nil {
			rows.Close()
			return classify("scan migration ledger", err)
		}
		if applied >= len(migrations) {
			rows.Close()
			return fmt.Errorf("migration version %d: %w", version, ErrSchemaTooNew)
		}
		want := migrations[applied]
		if version != want.version || checksum != want.checksum {
			rows.Close()
			return fmt.Errorf("migration ledger row %d does not match %s: %w", version, want.name, ErrSchemaInvalid)
		}
		applied++
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return classify("iterate migration ledger", err)
	}
	if err := rows.Close(); err != nil {
		return classify("close migration ledger", err)
	}

	for _, item := range migrations[applied:] {
		if _, err := tx.ExecContext(ctx, item.sql); err != nil {
			return classify("apply migration "+item.name, err)
		}
		if _, err := tx.ExecContext(ctx,
			`INSERT INTO schema_migrations (version, checksum, applied_at) VALUES (?, ?, ?)`,
			item.version, item.checksum, now.UTC().UnixMicro()); err != nil {
			return classify("record migration "+item.name, err)
		}
	}
	if applied < len(migrations) {
		if err := checkForeignKeys(ctx, tx); err != nil {
			return err
		}
	}
	if err := tx.Commit(); err != nil {
		return classify("commit migrations", err)
	}
	return nil
}

func checkForeignKeys(ctx context.Context, tx *sql.Tx) error {
	var table, parent string
	var row sql.NullInt64
	var key int
	err := tx.QueryRowContext(ctx, `PRAGMA foreign_key_check`).Scan(&table, &row, &parent, &key)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return nil
	case err != nil:
		return classify("check foreign keys after migrations", err)
	}
	return fmt.Errorf("migrations left a row of %s whose %s row is missing: %w", table, parent, ErrSchemaInvalid)
}