package schema

import (
	"crypto/md5"
	"crypto/sha256"
	"encoding/hex"
	"fmt"
	"strings"

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

// GenerateSafeIdentifier ensures an identifier (column/FK) does not exceed 64 chars.
// If it does, it returns a safe version using MD5 hash: "col_{hash}" or "fk_{hash}".
// prefix is usually "col" or "fk".
func GenerateSafeIdentifier(name string, prefix string) string {
	if len(name) <= 64 {
		return name
	}

	hash := md5.Sum([]byte(name))
	return fmt.Sprintf("%s_%x", prefix, hash)
}

// GenerateObjectID generates a DETERMINISTIC, case-normalized ID for an
// object based on its API Name. The apiName is lowercased before hashing AND
// in the readable prefix, so GenerateObjectID is stable across API-name casing.
//
// Why lowercase: _System_Object.ApiName has a UNIQUE constraint with
// utf8mb4_unicode_ci collation (case-insensitive). If GenerateObjectID
// produced two different Ids for two casings of the same logical name,
// pack apply (which uses Pascal-case spec apiNames) would generate Ids
// that conflict with discovery's lowercase-physical-name rows on the
// UNIQUE(ApiName), and ON DUPLICATE KEY UPDATE would silently preserve
// the discovery Id while the pack still wrote field rows pointing at the
// pack-generated Id — orphans.
//
// Format: obj_{lowercase apiName}_{sha256 of "object:lowercase apiName"}_first_32_chars
func GenerateObjectID(apiName string) string {
	normalized := strings.ToLower(apiName)
	hash := sha256.Sum256([]byte("object:" + normalized))
	return fmt.Sprintf("%s%s_%s", constants.PrefixObject, normalized, hex.EncodeToString(hash[:16]))
}

// GenerateFieldID generates a DETERMINISTIC, case-normalized ID for a field.
// Both names are lowercased before hashing AND in the readable prefix, for
// the same reason as GenerateObjectID — _System_Field's
// UNIQUE(ObjectId, ApiName) is case-insensitive too, so two field IDs with
// only-casing-different field names would point to the same row but with
// different PKs.
//
// Format: fld_{lowercase objectAPIName}_{lowercase fieldAPIName}_{sha256 first 32 chars}
func GenerateFieldID(objectAPIName, fieldAPIName string) string {
	objNorm := strings.ToLower(objectAPIName)
	fldNorm := strings.ToLower(fieldAPIName)
	hash := sha256.Sum256([]byte("field:" + objNorm + ":" + fldNorm))
	return fmt.Sprintf("%s%s_%s_%s", constants.PrefixField, objNorm, fldNorm, hex.EncodeToString(hash[:16]))
}

// GenerateTableID generates a standardized ID for a table (runtime — uses UUID for uniqueness)
func GenerateTableID(tableName string) string {
	return fmt.Sprintf("%s%s_%s", constants.PrefixTable, tableName, uuid.NewString())
}

// GenerateAutoNumberID generates a standardized ID for an auto-number sequence (runtime)
func GenerateAutoNumberID(objectAPIName, fieldAPIName string) string {
	return fmt.Sprintf("%s%s_%s_%s", constants.PrefixAutoNumber, objectAPIName, fieldAPIName, uuid.NewString())
}

// GenerateAppID generates a standardized ID for an app (runtime)
func GenerateAppID(apiName string) string {
	return fmt.Sprintf("%s%s_%s", constants.PrefixApp, apiName, uuid.NewString())
}

// GenerateUniqueIndexName generates a standard name for unique indexes/constraints.
// Handles length limits (64 chars) by hashing if necessary.
func GenerateUniqueIndexName(tableName, columnName string) string {
	baseName := fmt.Sprintf("%s%s_%s", constants.UniqueIndexPrefix, tableName, columnName)
	if len(baseName) <= 64 {
		return baseName
	}

	// Length exceeded: Use MD5 hash of the full name to ensure uniqueness and fit usage.
	// uniq_<32_char_hash> = 37 chars. Guaranteed safe.
	hash := md5.Sum([]byte(baseName))
	return fmt.Sprintf("%s%x", constants.UniqueIndexPrefix, hash)
}