package bootstrapimpl

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	"github.com/nexuscrm/shared/pkg/constants"
)

// The schema-bootstrap apply path (schema.go, relocated into this package in
// PR2 Step 2) consumes these unexported state helpers. They are self-contained
// (db/contract/shared only) and mirror the backend bootstrap copies that stay
// behind for backend's own non-CP callers; the duplication exists so
// controlplane/bootstrapimpl needs no edge to backend.

const (
	bootstrapStateTable     = "_System_BootstrapState"
	schemaBootstrapStateKey = "schema_initialize"
)

var bootstrapPhaseOrder = map[string]int{
	"start":                    0,
	"phase0_columns_reconcile": 1,
	"phase0_schema_reconcile":  2,
	"phase1_core_tables":       3,
	"phase1_batch_tables":      4,
	"phase2_object_metadata":   5,
	"phase2_read_object_ids":   6,
	"phase2_field_metadata":    7,
	"phase3_field_reconcile":   8,
	"phase4_name_fields":       9,
	"phase5_auto_numbers":      10,
	"phase6_field_labels":      11,
	"phase7_spec_defaults":     12,
	"phase7b_boolean_fallback": 13,
	"completed":                14,
}

type bootstrapStateSnapshot struct {
	RunID  string
	Phase  string
	Status string
}

func ensureBootstrapStateTable(rawDB *sql.DB) error {
	ddl := fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s (
		`+"`StateKey` VARCHAR(128) NOT NULL PRIMARY KEY,"+`
		`+"`RunID` VARCHAR(64) NOT NULL,"+`
		`+"`Phase` VARCHAR(128) NOT NULL,"+`
		`+"`Status` VARCHAR(32) NOT NULL,"+`
		`+"`LastError` TEXT NULL,"+`
		`+"`CreatedDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"+`
		`+"`LastModifiedDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"+`
	) %s`, bootstrapStateTable, constants.DefaultTableOptions)
	_, err := rawDB.Exec(ddl)
	return err
}

func loadBootstrapStateForKey(rawDB *sql.DB, stateKey string) (*bootstrapStateSnapshot, error) {
	row := rawDB.QueryRow(
		fmt.Sprintf("SELECT `RunID`, `Phase`, `Status` FROM `%s` WHERE `StateKey` = ?", bootstrapStateTable),
		stateKey,
	)
	var s bootstrapStateSnapshot
	if err := row.Scan(&s.RunID, &s.Phase, &s.Status); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &s, nil
}

func loadBootstrapState(rawDB *sql.DB) (*bootstrapStateSnapshot, error) {
	return loadBootstrapStateForKey(rawDB, schemaBootstrapStateKey)
}

func persistBootstrapStateForKey(rawDB *sql.DB, stateKey, runID, phase, status string, lastErr error) error {
	payload := ""
	if lastErr != nil {
		serialized, marshalErr := json.Marshal(map[string]string{
			"message":   lastErr.Error(),
			"timestamp": time.Now().UTC().Format(time.RFC3339),
		})
		if marshalErr != nil {
			return marshalErr
		}
		payload = string(serialized)
	}
	_, err := rawDB.Exec(
		fmt.Sprintf(
			"INSERT INTO `%s` (`StateKey`,`RunID`,`Phase`,`Status`,`LastError`,`CreatedDate`,`LastModifiedDate`) VALUES (?,?,?,?,?,NOW(),NOW()) "+
				"ON DUPLICATE KEY UPDATE `RunID`=VALUES(`RunID`),`Phase`=VALUES(`Phase`),`Status`=VALUES(`Status`),`LastError`=VALUES(`LastError`),`LastModifiedDate`=NOW()",
			bootstrapStateTable,
		),
		stateKey, runID, phase, status, payload,
	)
	return err
}

func persistBootstrapState(rawDB *sql.DB, runID, phase, status string, lastErr error) error {
	return persistBootstrapStateForKey(rawDB, schemaBootstrapStateKey, runID, phase, status, lastErr)
}

func shouldRunBootstrapPhase(resumeFrom, phase string) bool {
	if resumeFrom == "" {
		return true
	}
	resumeOrder, okResume := bootstrapPhaseOrder[resumeFrom]
	phaseOrder, okPhase := bootstrapPhaseOrder[phase]
	if !okResume || !okPhase {
		return true
	}
	return phaseOrder >= resumeOrder
}

func runBootstrapMutationUnderLock(rawDB *sql.DB, fn func() error) error {
	ctx := context.Background()
	lockConn, err := rawDB.Conn(ctx)
	if err != nil {
		return fmt.Errorf("acquiring connection for bootstrap lock: %w", err)
	}
	defer lockConn.Close()

	var lockResult int
	if err := lockConn.QueryRowContext(ctx,
		"SELECT GET_LOCK('nexuscrm_bootstrap_mutation', 600)",
	).Scan(&lockResult); err != nil || lockResult != 1 {
		return fmt.Errorf("acquiring bootstrap mutation lock (result=%d): %w", lockResult, err)
	}
	defer func() { _, _ = lockConn.ExecContext(ctx, "SELECT RELEASE_LOCK('nexuscrm_bootstrap_mutation')") }()

	return fn()
}