package controlplane

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

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
)

// migrationIdMigrateLegacyNaivePlurals is the stable `_System_Migration.Id`
// for this one-shot data correction. Format follows the convention set by
// migrationIdNormalizeProcessActionLiteralFieldValue (yyyymmdd_<short_action>).
//
// Bumping this constant retires the stamp and forces a re-run on every
// existing instance, which is almost never what we want — the WHERE guard
// is idempotent so re-runs on already-corrected rows are no-ops, but a
// fresh stamp would still write an extra _System_Migration row. To force
// a re-run, bump the version arg passed to RecordMigrationApplied instead.
const migrationIdMigrateLegacyNaivePlurals = "20260510_migrate_legacy_naive_plurals"

// MigrateLegacyNaivePlurals recomputes _System_Object.PluralLabel rows
// that were written by an older code path predating the canonical
// Pluralize() rules in bootstrap/pluralize.go. Those rows used
// naive `Label + "s"`
// regardless of suffix, producing values like "Opportunitys" or "Price
// Book Entrys" that fail the Salesforce v63 contract for labelPlural on
// standard SObjects.
//
// Why this is a one-time migration, not runtime logic:
//
//   - The Pluralize() helper in bootstrap/pluralize.go is already
//     correct as of HEAD; newly-written _System_Object rows pick up
//     the right plural form automatically (including via
//     controlplane/packs/platform_core.go::buildObjectMetadata after
//     NX#492 routed it through the canonical algorithm). The bug is
//     purely legacy data.
//   - ensurePluralLabelsAndNameFields runs on every bootstrap and uses
//     equivalent SQL CASE logic, but its WHERE clause only fires on
//     empty PluralLabel (`PluralLabel = ” OR PluralLabel IS NULL`).
//     Wrong non-empty values are not nominated for backfill.
//   - Issuing the SQL CASE recomputation outside the empty-only guard
//     unconditionally on every boot would fight admin-customized plurals
//     (an admin might intentionally set Opportunity's plural to
//     "Pipelines"; we should not overwrite that). The stamp-guarded
//     one-shot keeps the intent crisp: this migration is for legacy
//     `Label + "s"` rows only, once.
//
// Idempotency model:
//
//   - Stamp guard: once `_System_Migration` records this migration's Id,
//     subsequent calls short-circuit at the SELECT and never re-issue
//     the UPDATE. The stamp is written only after the UPDATE succeeds,
//     so a partial-failure (UPDATE error before stamp write) is safe to
//     retry — the WHERE guard `PluralLabel = CONCAT(Label, 's')` no
//     longer matches the rows that were already corrected, so the next
//     attempt processes only what remains.
//   - WHERE guard 1: `PluralLabel = CONCAT(Label, 's')` filters to
//     rows that look like the legacy naive +s shape. Admin-customized
//     plurals shaped non-naively (e.g. "Pipelines" for Opportunity)
//     do not match this predicate and are structurally protected from
//     rewrite by this guard alone.
//   - WHERE guard 2: `COALESCE(IsCustomized, 0) = 0` aligns this
//     migration with the pack apply path's `preserveCustomized` /
//     `objectBatchCustomizedGuardFields` semantics
//     (`infrastructure/persistence/schema_metadata_persist.go:370`).
//     Without it, an admin who has explicitly customized a row to a
//     value that *happens* to equal `Label + "s"` (e.g.
//     `IsCustomized=1` with `PluralLabel="Opportunitys"` deliberately)
//     would have their override rewritten by this migration even
//     though the source-backed pack apply path leaves it alone. Codex
//     review on PR #494 (round 2 against `9f91a3b`) flagged this as a
//     pack-managed-column ownership violation; this guard closes it.
//     The 00-issue.md AC's promise of "non-naive admin overrides not
//     modified" is now strengthened to "any IsCustomized=1 row is
//     not modified", strictly more conservative.
//   - The trailing WHERE conjunct (`PluralLabel <> CASE ... END`)
//     suppresses no-op writes for rows where the canonical plural is
//     also `Label + "s"` (vowel-y endings like "Boy" → "Boys", or
//     labels already ending in 's'). Those rows would round-trip the
//     same value through the UPDATE and bump LastModifiedDate without
//     any data change.
//
// SQL CASE alignment caveat: the CASE expression mirrors the one in
// ensurePluralLabelsAndNameFieldsScoped (schema.go:2173). For inputs
// produced by legacy naive +s (the WHERE guard's domain), the two
// branches converge on the same canonical plural. There is one
// theoretical edge — Labels already ending in a non-sibilant 's' (e.g.
// "Cars") — where the SQL CASE would emit "Carses" and the Go
// Pluralize() would emit "Cars" unchanged. In practice no _System_Object
// Label has this shape (standard CRM objects are singular and custom
// objects rarely end in 's'; jq inventory of
// official CRM pack payload at NX#492 PR #494 confirmed zero such
// Labels). The Pluralize doc comment carries this caveat as the
// canonical anchor. Whoever introduces such a Label next is
// responsible for converging both SQL twins (this file's updateSQL
// and ensurePluralLabelsAndNameFieldsScoped) per REVIEWER_GUIDE
// cross-stage rule "SQL CASE 与 Go 函数的双实现：改一方时同步检查另一方".
//
// Symmetric direction (algorithm-side change → SQL update): see
// `bootstrap/pluralize.go` doc-comment "SQL twins (must change
// together)" section. Any change to Pluralize's rule set must also
// land matching updates to this updateSQL and to
// ensurePluralLabelsAndNameFieldsScoped in the same PR.
//
// Wired into RunRepair (controlplane/operations.go) as a non-fatal step:
// wrong PluralLabel is a UI-cosmetic / wire-shape issue (sidebar nav,
// describe.labelPlural), not L3 data corruption, so a transient failure
// should not block ApplyAll. Stamp-write failure is also safe to
// swallow: the migration's WHERE guard makes a re-run on the next boot
// a no-op once the corrections have already taken effect. This is an
// intentional deviation from the trackStepFatal pattern used by
// NormalizeProcessActionLiteralFieldValue and BackfillWorkflowAlertTemplateIds,
// which are fatal because they correct L3 data corruption / silent
// email drops. Issue NX#492.
func MigrateLegacyNaivePlurals(ctx context.Context, db *sql.DB) (int, error) {
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdMigrateLegacyNaivePlurals)
	if err != nil {
		return 0, err
	}
	if applied {
		log.Printf("   ⏭  MigrateLegacyNaivePlurals: already applied; skipping")
		return 0, nil
	}

	const updateSQL = `
UPDATE _System_Object
SET PluralLabel = CASE
        WHEN Label REGEXP '[^aeiou]y$'      THEN CONCAT(LEFT(Label, CHAR_LENGTH(Label)-1), 'ies')
        WHEN Label REGEXP '[^z]z$'          THEN CONCAT(Label, 'zes')
        WHEN Label REGEXP '(s|x|z|sh|ch)$'  THEN CONCAT(Label, 'es')
        ELSE CONCAT(Label, 's')
    END,
    LastModifiedDate = NOW()
WHERE IsDeleted = 0
  AND Label IS NOT NULL
  AND Label <> ''
  AND COALESCE(IsCustomized, 0) = 0
  AND PluralLabel = CONCAT(Label, 's')
  AND PluralLabel <> CASE
        WHEN Label REGEXP '[^aeiou]y$'      THEN CONCAT(LEFT(Label, CHAR_LENGTH(Label)-1), 'ies')
        WHEN Label REGEXP '[^z]z$'          THEN CONCAT(Label, 'zes')
        WHEN Label REGEXP '(s|x|z|sh|ch)$'  THEN CONCAT(Label, 'es')
        ELSE CONCAT(Label, 's')
    END`

	res, err := db.ExecContext(ctx, updateSQL)
	if err != nil {
		return 0, fmt.Errorf("MigrateLegacyNaivePlurals: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("MigrateLegacyNaivePlurals rows affected: %w", err)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx, db,
		migrationIdMigrateLegacyNaivePlurals,
		1,
		"Recompute legacy naive '+s' PluralLabel rows in _System_Object using the canonical English plural rules; SQL CASE mirrors ensurePluralLabelsAndNameFieldsScoped (NX#492).",
	); err != nil {
		return int(n), err
	}

	if n > 0 {
		log.Printf("   🧼 MigrateLegacyNaivePlurals: corrected %d _System_Object PluralLabel row(s)", n)
	}
	return int(n), nil
}