// Package bootstate holds generic, backend-free DB receipt/probe helpers that
// are shared between the control plane (internal/controlplane) and the platform
// bootstrap path (internal/bootstrap). They were relocated here verbatim from
// internal/bootstrap during Wave 5 so both consumers reach a neutral sibling
// module instead of the control plane importing internal/bootstrap. SQL strings
// and observable behavior are byte-for-byte identical to the originals.
package bootstate

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

// MigrationStamp helpers are the first users of `_System_Migration`, the
// "applied data migrations" table declared in system_tables.json. The schema
// existed before any callers needed it; PR #271 Round 9 introduces the first
// migration that requires a true one-shot guard (warm-upgrade-only data
// rewrite that cannot be safely re-run after the new authoring contract is
// live), so the helpers land alongside that migration's first use.
//
// Concurrency: callers run inside `ApplyAll`'s `withControlPlaneApplyLock`,
// so a single instance owns the writes; no extra locking is required here.
//
// Schema reference: backend/internal/bootstrap/system_tables.json (search
// for "_System_Migration"). Primary key Id is VARCHAR(255).

type migrationStampQuerier interface {
	QueryRowContext(context.Context, string, ...any) *sql.Row
}

type migrationStampExecer interface {
	ExecContext(context.Context, string, ...any) (sql.Result, error)
}

// MigrationApplied reports whether migrationId already has a row in
// `_System_Migration`. The check is read-only and never inserts.
func MigrationApplied(ctx context.Context, db *sql.DB, migrationId string) (bool, error) {
	return migrationApplied(ctx, db, migrationId)
}

func migrationApplied(ctx context.Context, db migrationStampQuerier, migrationId string) (bool, error) {
	const selectSQL = `SELECT 1 FROM _System_Migration WHERE Id = ? LIMIT 1`
	row := db.QueryRowContext(ctx, selectSQL, migrationId)
	var present int
	err := row.Scan(&present)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, fmt.Errorf("MigrationApplied(%s): %w", migrationId, err)
	}
	return true, nil
}

// RecordMigrationApplied inserts a stamp row marking migrationId as applied.
// Idempotent: re-recording the same migrationId is a no-op (ON DUPLICATE KEY
// UPDATE keeps the original AppliedAt so a second call cannot rewrite the
// historical record). Caller passes the migration's monotonically-increasing
// version (use 1 for the first revision; bump only when the migration's
// observable behavior changes such that re-running on stamped systems is
// required) and a short human-readable description for operator forensics.
func RecordMigrationApplied(ctx context.Context, db *sql.DB, migrationId string, version int, description string) error {
	return recordMigrationApplied(ctx, db, migrationId, version, description)
}

func recordMigrationApplied(ctx context.Context, db migrationStampExecer, migrationId string, version int, description string) error {
	const insertSQL = `INSERT INTO _System_Migration (Id, Version, Description, AppliedAt, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, NOW(), NOW(), NOW())
			ON DUPLICATE KEY UPDATE LastModifiedDate = NOW()`
	if _, err := db.ExecContext(ctx, insertSQL, migrationId, version, description); err != nil {
		return fmt.Errorf("RecordMigrationApplied(%s): %w", migrationId, err)
	}
	return nil
}