package bootstrapimpl

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

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

// migrationIdDropGroupMemberUserFK is the stable `_System_Migration.Id` for
// this migration. Once stamped, subsequent RunRepair invocations no-op the
// entire function.
const migrationIdDropGroupMemberUserFK = "20260510_drop_groupmember_user_fk"

// DropGroupMemberUserFK removes the hard FK constraint on
// GroupMember.UserOrGroupId that points to user(Id).
//
// Why this is a one-time migration, not runtime logic:
//
//   - The FK physically blocks Group-target inserts (MySQL error 1452)
//     before any app-layer logic can run. Removing it is a schema
//     surgery operation — it must not be re-run after the first successful
//     application because the constraint will no longer exist.
//   - system_tables.json has already been updated to omit this FK for new
//     instances; this migration bridges warm-upgraded orgs that already
//     have the constraint physically present.
//   - Public REST CRUD now relies on polymorphic reference metadata to validate
//     UserOrGroupId targets. The full `referenceTo` list includes Group so
//     public, repair, and import paths resolve stored nested-group rows through
//     the same server-derived discriminator path.
//
// One-shot guard: this function is gated by a `_System_Migration` stamp
// (`migrationIdDropGroupMemberUserFK`). After the first successful run,
// every subsequent call returns 0 without touching the schema.
func DropGroupMemberUserFK(ctx context.Context, db *sql.DB) error {
	applied, err := MigrationApplied(ctx, db, migrationIdDropGroupMemberUserFK)
	if err != nil {
		return err
	}
	if applied {
		log.Printf("   ⏭  DropGroupMemberUserFK: already applied; skipping")
		return nil
	}

	// Dynamically discover the FK constraint name — MySQL/TiDB generates
	// names like fk_1, fk_2, etc., which are not stable across instances.
	const lookupSQL = `
		SELECT CONSTRAINT_NAME
		FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = ?
		  AND COLUMN_NAME = ?
		  AND REFERENCED_TABLE_NAME = ?
		LIMIT 1`

	var constraintName string
	err = db.QueryRowContext(ctx, lookupSQL,
		constants.TableGroupMember,
		"UserOrGroupId",
		constants.TableUser,
	).Scan(&constraintName)
	fkAlreadyAbsent := err == sql.ErrNoRows
	if err != nil && !fkAlreadyAbsent {
		return fmt.Errorf("DropGroupMemberUserFK lookup: %w", err)
	}

	if fkAlreadyAbsent {
		log.Printf("   ⏭  DropGroupMemberUserFK: FK on GroupMember.UserOrGroupId → User(Id) already absent; stamping")
	} else {
		dropSQL := fmt.Sprintf("ALTER TABLE `%s` DROP FOREIGN KEY `%s`",
			constants.TableGroupMember, constraintName)
		if _, err := db.ExecContext(ctx, dropSQL); err != nil {
			return fmt.Errorf("DropGroupMemberUserFK: %w (stmt=%s)", err, dropSQL)
		}
		log.Printf("   🧼 DropGroupMemberUserFK: dropped FK constraint %q on GroupMember.UserOrGroupId", constraintName)
	}

	return stampDropGroupMemberUserFK(ctx, db)
}

func stampDropGroupMemberUserFK(ctx context.Context, db *sql.DB) error {
	if err := RecordMigrationApplied(
		ctx, db,
		migrationIdDropGroupMemberUserFK,
		1,
		"Drop hard FK on GroupMember.UserOrGroupId → User(Id) to enable nested group membership (issue #396).",
	); err != nil {
		return fmt.Errorf("DropGroupMemberUserFK stamp: %w", err)
	}
	log.Printf("   ✅ DropGroupMemberUserFK: migration stamped")
	return nil
}