package platform

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
)

type Migration struct {
	Version int64
	Name    string
	SQL     string
}

func (a *App) RegisterMigration(migration Migration) {
	a.migrations = append(a.migrations, migration)
}

func (a *App) ApplyMigrations(ctx context.Context) error {
	if a.database == nil {
		return nil
	}
	if _, err := a.database.ExecContext(ctx, `
CREATE TABLE IF NOT EXISTS sense_schema_migrations (
    version BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
)`); err != nil {
		return fmt.Errorf("create Sense migration table: %w", err)
	}

	sort.Slice(a.migrations, func(i, j int) bool { return a.migrations[i].Version < a.migrations[j].Version })
	for _, migration := range a.migrations {
		if err := applyMigration(ctx, a.database, migration); err != nil {
			return err
		}
	}
	return nil
}

func applyMigration(ctx context.Context, database *sql.DB, migration Migration) error {
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin migration %d: %w", migration.Version, err)
	}
	defer tx.Rollback()

	var exists bool
	if err := tx.QueryRowContext(ctx, `SELECT EXISTS(SELECT 1 FROM sense_schema_migrations WHERE version = $1)`, migration.Version).Scan(&exists); err != nil {
		return fmt.Errorf("check migration %d: %w", migration.Version, err)
	}
	if exists {
		return tx.Commit()
	}
	if _, err := tx.ExecContext(ctx, migration.SQL); err != nil {
		return fmt.Errorf("apply migration %d (%s): %w", migration.Version, migration.Name, err)
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO sense_schema_migrations(version, name) VALUES($1, $2)`, migration.Version, migration.Name); err != nil {
		return fmt.Errorf("record migration %d: %w", migration.Version, err)
	}
	return tx.Commit()
}