package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"net/http"
	"sort"
	"strings"

	"github.com/nexuscrm/backend/internal/layoutrouting"
	"github.com/nexuscrm/backend/pkg/apierr"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/backend/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/rs/zerolog/log"
)

func normalizeRouteSource(routeSource *string) *string {
	if routeSource == nil {
		return nil
	}
	trimmed := strings.TrimSpace(*routeSource)
	if trimmed == "" {
		return nil
	}
	return &trimmed
}

func loadLiveLayoutObjectAPIName(ctx context.Context, tx *sql.Tx, layoutID string) (string, error) {
	var objectAPIName string
	err := tx.QueryRowContext(
		ctx,
		fmt.Sprintf(
			"SELECT `%s` FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL) LIMIT 1",
			constants.FieldObjectAPIName,
			constants.TablePageLayout,
			constants.FieldID,
			constants.FieldIsDeleted,
			constants.FieldIsDeleted,
		),
		layoutID,
	).Scan(&objectAPIName)
	if err == sql.ErrNoRows {
		return "", apierr.NewSalesforce(
			http.StatusBadRequest,
			apierr.SFCodeInvalidCrossReferenceKey,
			fmt.Sprintf("layoutId %q does not reference a live page layout", layoutID),
			"layoutId",
		)
	}
	if err != nil {
		return "", err
	}
	return objectAPIName, nil
}

func canonicalizeAssignmentObjectAPIName(ctx context.Context, tx *sql.Tx, assignment *models.LayoutAssignment) error {
	if assignment == nil {
		return nil
	}

	layoutObjectAPIName, err := loadLiveLayoutObjectAPIName(ctx, tx, assignment.LayoutID)
	if err != nil {
		return err
	}
	if !strings.EqualFold(layoutObjectAPIName, assignment.ObjectAPIName) {
		return apierr.NewSalesforce(
			http.StatusBadRequest,
			apierr.SFCodeInvalidCrossReferenceKey,
			fmt.Sprintf("layoutId %q belongs to %q, not %q", assignment.LayoutID, layoutObjectAPIName, assignment.ObjectAPIName),
			"layoutId",
			"objectApiName",
		)
	}
	assignment.ObjectAPIName = layoutObjectAPIName
	return nil
}

func isWildcardLayoutAssignment(assignment *models.LayoutAssignment) bool {
	return assignment != nil &&
		assignment.RoleID == nil &&
		assignment.ProfileID == nil &&
		assignment.RecordTypeID == nil
}

func upsertRuntimeFallbackAssignmentTx(ctx context.Context, tx *sql.Tx, assignment *models.LayoutAssignment) error {
	var existingID, existingLayoutID string
	var existingRouteSource sql.NullString
	sqlStr := fmt.Sprintf(
		"SELECT `Id`, `LayoutId`, `RouteSource` FROM `%s` "+
			"WHERE LOWER(`ObjectApiName`) = LOWER(?) AND `RoleId` IS NULL AND `ProfileId` IS NULL AND `RecordTypeId` IS NULL "+
			"ORDER BY `CreatedDate` ASC LIMIT 1",
		constants.TableLayoutAssignment,
	)
	err := tx.QueryRowContext(ctx, sqlStr, assignment.ObjectAPIName).Scan(&existingID, &existingLayoutID, &existingRouteSource)
	if err != nil && err != sql.ErrNoRows {
		return err
	}
	if err == nil {
		routeSource := ""
		if existingRouteSource.Valid {
			routeSource = strings.TrimSpace(existingRouteSource.String)
		}
		if !strings.EqualFold(routeSource, models.LayoutAssignmentRouteSourceRuntimeFallback) {
			return nil
		}
		updateSQL := fmt.Sprintf(
			"UPDATE `%s` SET `LayoutId` = ?, `Specificity` = ?, `RouteSource` = ?, `LastModifiedDate` = CURRENT_TIMESTAMP WHERE `Id` = ?",
			constants.TableLayoutAssignment,
		)
		_, err := tx.ExecContext(ctx, updateSQL, assignment.LayoutID, assignment.Specificity, *assignment.RouteSource, existingID)
		return err
	}

	if assignment.ID == "" {
		assignment.ID = utils.GenerateID()
	}
	values := map[string]interface{}{
		constants.FieldID:               assignment.ID,
		"ObjectApiName":                 assignment.ObjectAPIName,
		"LayoutId":                      assignment.LayoutID,
		"RoleId":                        nil,
		"ProfileId":                     nil,
		"RecordTypeId":                  nil,
		"Specificity":                   assignment.Specificity,
		constants.FieldCreatedDate:      query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate: query.Raw("CURRENT_TIMESTAMP"),
		"CreatedById":                   assignment.CreatedByID,
		"RouteSource":                   *assignment.RouteSource,
	}
	qr := query.Insert(constants.TableLayoutAssignment, values).Build()
	_, err = tx.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

func upsertLayoutAssignmentTx(ctx context.Context, tx *sql.Tx, assignment *models.LayoutAssignment) error {
	assignment.ComputeSpecificity()
	assignment.RouteSource = normalizeRouteSource(assignment.RouteSource)
	if assignment.RouteSource == nil {
		return fmt.Errorf("routeSource is required")
	}

	if err := canonicalizeAssignmentObjectAPIName(ctx, tx, assignment); err != nil {
		return err
	}

	if isWildcardLayoutAssignment(assignment) &&
		strings.EqualFold(*assignment.RouteSource, models.LayoutAssignmentRouteSourceRuntimeFallback) {
		return upsertRuntimeFallbackAssignmentTx(ctx, tx, assignment)
	}

	routeSourceValue := *assignment.RouteSource

	whereClauses := []string{"LOWER(`ObjectApiName`) = LOWER(?)"}
	args := []interface{}{assignment.ObjectAPIName}

	if assignment.RoleID != nil {
		whereClauses = append(whereClauses, "`RoleId` = ?")
		args = append(args, *assignment.RoleID)
	} else {
		whereClauses = append(whereClauses, "`RoleId` IS NULL")
	}
	if assignment.ProfileID != nil {
		whereClauses = append(whereClauses, "`ProfileId` = ?")
		args = append(args, *assignment.ProfileID)
	} else {
		whereClauses = append(whereClauses, "`ProfileId` IS NULL")
	}
	if assignment.RecordTypeID != nil {
		whereClauses = append(whereClauses, "`RecordTypeId` = ?")
		args = append(args, *assignment.RecordTypeID)
	} else {
		whereClauses = append(whereClauses, "`RecordTypeId` IS NULL")
	}

	where := strings.Join(whereClauses, " AND ")

	updateSQL := fmt.Sprintf(
		"UPDATE `%s` SET `LayoutId` = ?, `Specificity` = ?, `RouteSource` = ?, `LastModifiedDate` = CURRENT_TIMESTAMP WHERE %s",
		constants.TableLayoutAssignment, where,
	)
	updateArgs := append([]interface{}{assignment.LayoutID, assignment.Specificity, routeSourceValue}, args...)
	if isAdminOwnedLayoutAssignment(assignment.CreatedByID) {
		updateSQL = fmt.Sprintf(
			"UPDATE `%s` SET `LayoutId` = ?, `Specificity` = ?, `CreatedById` = ?, `RouteSource` = ?, `LastModifiedDate` = CURRENT_TIMESTAMP WHERE %s",
			constants.TableLayoutAssignment, where,
		)
		updateArgs = append([]interface{}{assignment.LayoutID, assignment.Specificity, *assignment.CreatedByID, routeSourceValue}, args...)
	}
	result, err := tx.ExecContext(ctx, updateSQL, updateArgs...)
	if err != nil {
		return err
	}
	rowsAffected, _ := result.RowsAffected()
	if rowsAffected > 0 {
		return nil
	}

	if assignment.ID == "" {
		assignment.ID = utils.GenerateID()
	}
	values := map[string]interface{}{
		constants.FieldID:               assignment.ID,
		"ObjectApiName":                 assignment.ObjectAPIName,
		"LayoutId":                      assignment.LayoutID,
		"RoleId":                        assignment.RoleID,
		"ProfileId":                     assignment.ProfileID,
		"RecordTypeId":                  assignment.RecordTypeID,
		"Specificity":                   assignment.Specificity,
		constants.FieldCreatedDate:      query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate: query.Raw("CURRENT_TIMESTAMP"),
		"CreatedById":                   assignment.CreatedByID,
		"RouteSource":                   routeSourceValue,
	}
	qr := query.Insert(constants.TableLayoutAssignment, values).Build()
	_, err = tx.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

func (r *MetadataRepository) loadLiveWildcardLayoutID(ctx context.Context, objectAPIName string) (string, error) {
	target, err := layoutrouting.LoadLiveWildcardTarget(ctx, r.db, objectAPIName)
	if err != nil {
		return "", err
	}
	if target == nil {
		return "", nil
	}
	return target.LayoutID, nil
}

// getEffectiveDefaultLayoutID resolves the one default-layout read path for
// runtime/UI consumers. The wildcard assignment is the only SSoT.
func (r *MetadataRepository) getEffectiveDefaultLayoutID(ctx context.Context, objectAPIName string) (string, error) {
	return r.loadLiveWildcardLayoutID(ctx, objectAPIName)
}

func projectEffectiveDefault(layout *models.PageLayout, defaultLayoutID string) {
	if layout == nil {
		return
	}
	layout.IsDefault = defaultLayoutID != "" && layout.ID == defaultLayoutID
}

// GetLayouts queries all layouts for an object
func (r *MetadataRepository) GetLayouts(ctx context.Context, objectAPIName string) ([]*models.PageLayout, error) {
	qr := query.From(constants.TablePageLayout).
		Select(constants.ColsSystemPageLayout).
		Where(constants.FieldObjectAPIName, objectAPIName).
		Where(constants.FieldIsDeleted, false).
		WhereNot("Type", "Create"). // Exclude stale Type='Create' layouts from PR #580
		OrderBy(constants.FieldSysPageLayout_LayoutName, "ASC").
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	defaultLayoutID, err := r.getEffectiveDefaultLayoutID(ctx, objectAPIName)
	if err != nil {
		return nil, err
	}

	layouts := make([]*models.PageLayout, 0)
	for rows.Next() {
		layout, err := r.scanLayout(rows)
		if err != nil {
			log.Warn().Err(err).Msg("Failed to scan layout")
			continue
		}
		projectEffectiveDefault(layout, defaultLayoutID)
		layouts = append(layouts, layout)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating layouts", err)
	}
	sort.SliceStable(layouts, func(i, j int) bool {
		if layouts[i].IsDefault == layouts[j].IsDefault {
			return false
		}
		return layouts[i].IsDefault
	})
	return layouts, nil
}

// GetLayout queries a single layout by ID (excludes soft-deleted)
func (r *MetadataRepository) GetLayout(ctx context.Context, layoutID string) (*models.PageLayout, error) {
	qr := query.From(constants.TablePageLayout).
		Select(constants.ColsSystemPageLayout).
		Where(constants.FieldID, layoutID).
		Where(constants.FieldIsDeleted, false).
		Build()

	layout, err := r.scanLayout(r.db.QueryRowContext(ctx, qr.SQL, qr.Params...))
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	defaultLayoutID, err := r.getEffectiveDefaultLayoutID(ctx, layout.ObjectAPIName)
	if err != nil {
		return nil, err
	}
	projectEffectiveDefault(layout, defaultLayoutID)
	return layout, nil
}

// GetLayoutIDForProfile returns the live layout ID assigned to a profile for an object.
// Profile-specific routing now lives in _System_LayoutAssignment; ProfileLayout is
// legacy compat storage and no longer participates in runtime resolution.
func (r *MetadataRepository) GetLayoutIDForProfile(ctx context.Context, objectAPIName, profileID string) (string, error) {
	var layoutID string

	sqlStr := fmt.Sprintf(
		"SELECT la.`LayoutId` "+
			"FROM `%s` la "+
			"JOIN `%s` pl ON pl.`%s` = la.`LayoutId` "+
			"AND LOWER(pl.`%s`) = LOWER(la.`ObjectApiName`) "+
			"AND (pl.`%s` = 0 OR pl.`%s` IS NULL) "+
			"WHERE LOWER(la.`ObjectApiName`) = LOWER(?) "+
			"AND la.`ProfileId` = ? "+
			"AND la.`RoleId` IS NULL "+
			"AND la.`RecordTypeId` IS NULL "+
			"ORDER BY la.`CreatedDate` ASC LIMIT 1",
		constants.TableLayoutAssignment,
		constants.TablePageLayout,
		constants.ColID,
		constants.FieldObjectAPIName,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)

	err := r.db.QueryRowContext(ctx, sqlStr, objectAPIName, profileID).Scan(&layoutID)
	if err == sql.ErrNoRows {
		return "", nil // Not assigned
	}
	if err != nil {
		return "", err
	}
	return layoutID, nil
}

// SaveLayout saves or updates a page layout
func (r *MetadataRepository) SaveLayout(ctx context.Context, layout *models.PageLayout) error {
	configJSON, err := r.marshalJSON(layout)
	if err != nil {
		return apperrors.NewInternalError("failed to marshal layout", err)
	}

	qr := query.Insert(constants.TablePageLayout, map[string]interface{}{
		constants.FieldID:                              layout.ID,
		constants.FieldObjectAPIName:                   layout.ObjectAPIName,
		constants.FieldSysPageLayout_LayoutName:        layout.LayoutName,
		constants.FieldSysPageLayout_Type:              layout.Type,
		constants.FieldSysPageLayout_IsDefault:         layout.IsDefault,
		constants.FieldIsDeleted:                       layout.IsDeleted,
		constants.FieldSysPageLayout_Config:            configJSON,
		"LayoutVersion":                                layout.LayoutVersion,
		"IsCustomized":                                 layout.IsCustomized,
		constants.FieldSysPageLayout_Source:            layout.Source,
		constants.FieldSysPageLayout_GenerationVersion: layout.GenerationVersion,
		constants.FieldCreatedDate:                     query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate:                query.Raw("CURRENT_TIMESTAMP"),
	}).OnDuplicateKeyUpdate(map[string]interface{}{
		constants.FieldObjectAPIName:                   layout.ObjectAPIName,
		constants.FieldSysPageLayout_LayoutName:        layout.LayoutName,
		constants.FieldSysPageLayout_Type:              layout.Type,
		constants.FieldSysPageLayout_IsDefault:         layout.IsDefault,
		constants.FieldIsDeleted:                       layout.IsDeleted,
		constants.FieldSysPageLayout_Config:            configJSON,
		"LayoutVersion":                                layout.LayoutVersion,
		"IsCustomized":                                 layout.IsCustomized,
		constants.FieldSysPageLayout_Source:            layout.Source,
		constants.FieldSysPageLayout_GenerationVersion: layout.GenerationVersion,
		constants.FieldLastModifiedDate:                query.Raw("CURRENT_TIMESTAMP"),
	}).Build()

	_, err = r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// MarkLayoutCustomized sets IsCustomized=1 on a layout so bootstrap won't replace it
func (r *MetadataRepository) MarkLayoutCustomized(ctx context.Context, layoutID string) {
	r.db.ExecContext(ctx,
		fmt.Sprintf("UPDATE `%s` SET `IsCustomized` = 1 WHERE `%s` = ?", constants.TablePageLayout, constants.FieldID),
		layoutID,
	)
}

func isAdminOwnedLayoutAssignment(createdByID *string) bool {
	return createdByID != nil && strings.TrimSpace(*createdByID) != ""
}

// SetDefaultLayout clears isDefault on all layouts for the object, then sets it on the given layout.
// Both operations are wrapped in a transaction so that a failure in the second statement
// does not leave the object with no default layout.
func (r *MetadataRepository) SetDefaultLayout(ctx context.Context, objectAPIName string, layoutID string, actorID *string) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() //nolint:errcheck // rollback is a no-op after commit

	routeSource := models.LayoutAssignmentRouteSourceAdminAuthored
	defaultAssignment := &models.LayoutAssignment{
		ObjectAPIName: objectAPIName,
		LayoutID:      layoutID,
		CreatedByID:   actorID,
		RouteSource:   &routeSource,
	}
	if err := canonicalizeAssignmentObjectAPIName(ctx, tx, defaultAssignment); err != nil {
		return err
	}

	clearSQL := fmt.Sprintf("UPDATE `%s` SET `%s` = false, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ?",
		constants.TablePageLayout,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldLastModifiedDate,
		constants.FieldObjectAPIName,
	)
	if _, err := tx.ExecContext(ctx, clearSQL, defaultAssignment.ObjectAPIName); err != nil {
		return fmt.Errorf("failed to clear default: %w", err)
	}

	setSQL := fmt.Sprintf("UPDATE `%s` SET `%s` = true, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ? AND `%s` = ? AND (`%s` = false OR `%s` IS NULL)",
		constants.TablePageLayout,
		constants.FieldSysPageLayout_IsDefault,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)
	result, err := tx.ExecContext(ctx, setSQL, layoutID, defaultAssignment.ObjectAPIName)
	if err != nil {
		return fmt.Errorf("failed to set default: %w", err)
	}
	rowsAffected, _ := result.RowsAffected()
	if rowsAffected == 0 {
		return apierr.NewSalesforce(
			http.StatusBadRequest,
			apierr.SFCodeInvalidCrossReferenceKey,
			fmt.Sprintf("layoutId %q does not reference a live page layout for %q", layoutID, defaultAssignment.ObjectAPIName),
			"layoutId",
		)
	}
	if err := upsertLayoutAssignmentTx(ctx, tx, defaultAssignment); err != nil {
		return fmt.Errorf("failed to upsert wildcard assignment: %w", err)
	}

	// "Set Default" is routing intent, not content ownership. Persist the
	// wildcard assignment authorship, but keep Source/GenerationVersion on the
	// layout row unchanged so schema-driven regeneration continues to work.
	return tx.Commit()
}

// DeleteLayout soft-deletes a layout by setting IsDeleted = true
func (r *MetadataRepository) DeleteLayout(ctx context.Context, layoutID string) error {
	sql := fmt.Sprintf("UPDATE `%s` SET `%s` = true, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ?",
		constants.TablePageLayout,
		constants.FieldIsDeleted,
		constants.FieldLastModifiedDate,
		constants.FieldID,
	)
	_, err := r.db.ExecContext(ctx, sql, layoutID)
	return err
}

// AssignLayoutToProfile assigns a layout to a profile using the layout-assignment SSoT.
func (r *MetadataRepository) AssignLayoutToProfile(ctx context.Context, profileID, objectAPIName, layoutID string) error {
	routeSource := models.LayoutAssignmentRouteSourceAdminAuthored
	assignment := &models.LayoutAssignment{
		ObjectAPIName: objectAPIName,
		LayoutID:      layoutID,
		ProfileID:     &profileID,
		RouteSource:   &routeSource,
	}
	return r.CreateAssignment(ctx, assignment)
}

// UpsertLayout updates or inserts a page layout
func (r *MetadataRepository) UpsertLayout(ctx context.Context, layout *models.PageLayout) error {
	configJSON, err := r.marshalJSON(layout)
	if err != nil {
		return apperrors.NewInternalError("failed to marshal layout", err)
	}

	qr := query.Insert(constants.TablePageLayout, map[string]interface{}{
		constants.FieldID:                              layout.ID,
		constants.FieldObjectAPIName:                   layout.ObjectAPIName,
		constants.FieldSysPageLayout_LayoutName:        layout.LayoutName,
		constants.FieldSysPageLayout_Type:              layout.Type,
		constants.FieldSysPageLayout_IsDefault:         layout.IsDefault,
		constants.FieldIsDeleted:                       layout.IsDeleted,
		constants.FieldSysPageLayout_Config:            configJSON,
		"LayoutVersion":                                layout.LayoutVersion,
		"IsCustomized":                                 layout.IsCustomized,
		constants.FieldSysPageLayout_Source:            layout.Source,
		constants.FieldSysPageLayout_GenerationVersion: layout.GenerationVersion,
		constants.FieldCreatedDate:                     query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate:                query.Raw("CURRENT_TIMESTAMP"),
	}).
		OnDuplicateKeyUpdate(map[string]interface{}{
			constants.FieldObjectAPIName:                   layout.ObjectAPIName,
			constants.FieldSysPageLayout_LayoutName:        layout.LayoutName,
			constants.FieldSysPageLayout_Type:              layout.Type,
			constants.FieldSysPageLayout_IsDefault:         layout.IsDefault,
			constants.FieldIsDeleted:                       layout.IsDeleted,
			constants.FieldSysPageLayout_Config:            configJSON,
			"LayoutVersion":                                layout.LayoutVersion,
			"IsCustomized":                                 layout.IsCustomized,
			constants.FieldSysPageLayout_Source:            layout.Source,
			constants.FieldSysPageLayout_GenerationVersion: layout.GenerationVersion,
			constants.FieldLastModifiedDate:                query.Raw("NOW()"),
		}).
		Build()

	_, err = r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// BatchUpsertLayouts updates or inserts multiple page layouts
func (r *MetadataRepository) BatchUpsertLayouts(ctx context.Context, layouts []*models.PageLayout) error {
	for _, layout := range layouts {
		if err := r.UpsertLayout(ctx, layout); err != nil {
			return err
		}
	}
	return nil
}

// --- Layout Assignment (specificity-based routing) ---

// FindBestAssignment finds the highest-specificity assignment matching the user context.
// roleAncestors should include the user's own RoleID + all ancestors (or nil if no role).
func (r *MetadataRepository) FindBestAssignment(ctx context.Context, objectAPIName string, roleAncestors []string, profileID string, recordTypeID string) (*models.LayoutAssignment, error) {
	var args []interface{}
	args = append(args, objectAPIName)

	// Role clause: if no role, only match wildcard-role assignments
	roleClause := "`RoleId` IS NULL"
	if len(roleAncestors) > 0 {
		placeholders := make([]string, len(roleAncestors))
		for i, r := range roleAncestors {
			placeholders[i] = "?"
			args = append(args, r)
		}
		roleClause = fmt.Sprintf("(`RoleId` IS NULL OR `RoleId` IN (%s))", strings.Join(placeholders, ","))
	}

	// Profile clause
	profileClause := "`ProfileId` IS NULL"
	if profileID != "" {
		profileClause = "(`ProfileId` IS NULL OR `ProfileId` = ?)"
		args = append(args, profileID)
	}

	// RecordType clause
	rtClause := "`RecordTypeId` IS NULL"
	if recordTypeID != "" {
		rtClause = "(`RecordTypeId` IS NULL OR `RecordTypeId` = ?)"
		args = append(args, recordTypeID)
	}

	sqlStr := fmt.Sprintf(
		"SELECT `Id`, `ObjectApiName`, `LayoutId`, `RoleId`, `ProfileId`, `RecordTypeId`, `Specificity`, `CreatedDate`, `LastModifiedDate`, `CreatedById`, `RouteSource` "+
			"FROM `%s` WHERE LOWER(`ObjectApiName`) = LOWER(?) AND %s AND %s AND %s "+
			"ORDER BY `Specificity` DESC, (`RecordTypeId` IS NOT NULL) DESC, (`ProfileId` IS NOT NULL) DESC, (`RoleId` IS NOT NULL) DESC, `CreatedDate` ASC "+
			"LIMIT 1",
		constants.TableLayoutAssignment, roleClause, profileClause, rtClause,
	)

	var a models.LayoutAssignment
	var roleID, profID, rtID, createdByID, routeSource sql.NullString
	var createdDate, lastModifiedDate interface{}

	err := r.db.QueryRowContext(ctx, sqlStr, args...).Scan(
		&a.ID, &a.ObjectAPIName, &a.LayoutID,
		&roleID, &profID, &rtID,
		&a.Specificity, &createdDate, &lastModifiedDate, &createdByID, &routeSource,
	)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	if roleID.Valid {
		a.RoleID = &roleID.String
	}
	if profID.Valid {
		a.ProfileID = &profID.String
	}
	if rtID.Valid {
		a.RecordTypeID = &rtID.String
	}
	if createdByID.Valid {
		a.CreatedByID = &createdByID.String
	}
	if routeSource.Valid {
		a.RouteSource = &routeSource.String
	}
	a.CreatedDate = parseTime(createdDate)
	a.LastModifiedDate = parseTime(lastModifiedDate)
	return &a, nil
}

// CreateAssignment upserts a layout assignment.
// Because NULL != NULL in SQL unique indexes, ON DUPLICATE KEY UPDATE cannot
// deduplicate on (ObjectApiName, RoleId, ProfileId, RecordTypeId) when dimensions
// are NULL. Instead, we find any existing row with matching dimensions and UPDATE it,
// or INSERT a new row if none exists.
func (r *MetadataRepository) CreateAssignment(ctx context.Context, assignment *models.LayoutAssignment) error {
	assignment.RouteSource = normalizeRouteSource(assignment.RouteSource)
	if assignment.RouteSource == nil {
		return fmt.Errorf("routeSource is required")
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() //nolint:errcheck // rollback is a no-op after commit

	if err := upsertLayoutAssignmentTx(ctx, tx, assignment); err != nil {
		return err
	}
	// Non-wildcard assignment writes are also routing intent only. The
	// assignment row carries authorship; the target layout row keeps whatever
	// provenance it already had.
	return tx.Commit()
}

// DeleteAssignment hard-deletes a layout assignment by ID.
func (r *MetadataRepository) DeleteAssignment(ctx context.Context, id string) error {
	sqlStr := fmt.Sprintf("DELETE FROM `%s` WHERE `%s` = ?", constants.TableLayoutAssignment, constants.FieldID)
	_, err := r.db.ExecContext(ctx, sqlStr, id)
	return err
}

// GetAssignmentsForObject returns all assignments for an object.
func (r *MetadataRepository) GetAssignmentsForObject(ctx context.Context, objectAPIName string) ([]*models.LayoutAssignment, error) {
	sqlStr := fmt.Sprintf(
		"SELECT `Id`, `ObjectApiName`, `LayoutId`, `RoleId`, `ProfileId`, `RecordTypeId`, `Specificity`, `CreatedDate`, `LastModifiedDate`, `CreatedById`, `RouteSource` "+
			"FROM `%s` WHERE LOWER(`ObjectApiName`) = LOWER(?) ORDER BY `Specificity` DESC",
		constants.TableLayoutAssignment,
	)

	rows, err := r.db.QueryContext(ctx, sqlStr, objectAPIName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var assignments []*models.LayoutAssignment
	for rows.Next() {
		var a models.LayoutAssignment
		var roleID, profID, rtID, createdByID, routeSource sql.NullString
		var createdDate, lastModifiedDate interface{}

		if err := rows.Scan(&a.ID, &a.ObjectAPIName, &a.LayoutID, &roleID, &profID, &rtID, &a.Specificity, &createdDate, &lastModifiedDate, &createdByID, &routeSource); err != nil {
			continue
		}
		if roleID.Valid {
			a.RoleID = &roleID.String
		}
		if profID.Valid {
			a.ProfileID = &profID.String
		}
		if rtID.Valid {
			a.RecordTypeID = &rtID.String
		}
		if createdByID.Valid {
			a.CreatedByID = &createdByID.String
		}
		if routeSource.Valid {
			a.RouteSource = &routeSource.String
		}
		a.CreatedDate = parseTime(createdDate)
		a.LastModifiedDate = parseTime(lastModifiedDate)
		assignments = append(assignments, &a)
	}
	return assignments, rows.Err()
}

// DeleteAssignmentsByLayoutID removes all assignments pointing to a layout.
func (r *MetadataRepository) DeleteAssignmentsByLayoutID(ctx context.Context, layoutID string) error {
	sqlStr := fmt.Sprintf("DELETE FROM `%s` WHERE `LayoutId` = ?", constants.TableLayoutAssignment)
	_, err := r.db.ExecContext(ctx, sqlStr, layoutID)
	return err
}

// --- Layout History ---

// WriteLayoutHistory appends a version snapshot atomically using MAX()+1 subquery.
// This avoids the TOCTOU race of reading max version then inserting version+1.
func (r *MetadataRepository) WriteLayoutHistory(ctx context.Context, entry *models.PageLayoutHistoryEntry) error {
	if entry.ID == "" {
		entry.ID = utils.GenerateID()
	}

	sqlStr := fmt.Sprintf(
		"INSERT INTO `%s` (`Id`, `LayoutId`, `VersionNumber`, `Config`, `ChangedById`, `ChangeReason`, `CreatedDate`) "+
			"SELECT ?, ?, COALESCE(MAX(`VersionNumber`), 0) + 1, ?, ?, ?, CURRENT_TIMESTAMP "+
			"FROM `%s` WHERE `LayoutId` = ?",
		constants.TablePageLayoutHistory, constants.TablePageLayoutHistory,
	)
	_, err := r.db.ExecContext(ctx, sqlStr, entry.ID, entry.LayoutID, entry.Config, entry.ChangedByID, entry.ChangeReason, entry.LayoutID)
	return err
}

// GetLayoutHistoryMaxVersion returns the max version number for a layout's history (0 if none).
func (r *MetadataRepository) GetLayoutHistoryMaxVersion(ctx context.Context, layoutID string) (int, error) {
	var maxVersion sql.NullInt64
	sqlStr := fmt.Sprintf("SELECT MAX(`VersionNumber`) FROM `%s` WHERE `LayoutId` = ?", constants.TablePageLayoutHistory)
	err := r.db.QueryRowContext(ctx, sqlStr, layoutID).Scan(&maxVersion)
	if err != nil {
		return 0, err
	}
	if maxVersion.Valid {
		return int(maxVersion.Int64), nil
	}
	return 0, nil
}

// PruneLayoutHistory deletes history entries with VersionNumber <= keepAboveVersion.
func (r *MetadataRepository) PruneLayoutHistory(ctx context.Context, layoutID string, keepAboveVersion int) error {
	sqlStr := fmt.Sprintf("DELETE FROM `%s` WHERE `LayoutId` = ? AND `VersionNumber` <= ?", constants.TablePageLayoutHistory)
	_, err := r.db.ExecContext(ctx, sqlStr, layoutID, keepAboveVersion)
	return err
}

// GetLayoutHistoryConfig returns the Config JSON snapshot for a specific history version.
func (r *MetadataRepository) GetLayoutHistoryConfig(ctx context.Context, layoutID string, versionNumber int) (string, error) {
	var configJSON string
	sqlStr := fmt.Sprintf("SELECT `Config` FROM `%s` WHERE `LayoutId` = ? AND `VersionNumber` = ?", constants.TablePageLayoutHistory)
	err := r.db.QueryRowContext(ctx, sqlStr, layoutID, versionNumber).Scan(&configJSON)
	if err != nil {
		return "", err
	}
	return configJSON, nil
}

// --- Migration ---

// GetMigrationVersion returns the version for a migration ID (0 if not found).
func (r *MetadataRepository) GetMigrationVersion(ctx context.Context, migrationID string) (int, error) {
	var version int
	sqlStr := fmt.Sprintf("SELECT `Version` FROM `%s` WHERE `Id` = ?", constants.TableMigration)
	err := r.db.QueryRowContext(ctx, sqlStr, migrationID).Scan(&version)
	if err == sql.ErrNoRows {
		return 0, nil
	}
	if err != nil {
		return 0, err
	}
	return version, nil
}

// SetMigrationVersion records a migration version (upsert).
func (r *MetadataRepository) SetMigrationVersion(ctx context.Context, migrationID string, version int, description string) error {
	sqlStr := fmt.Sprintf(
		"INSERT INTO `%s` (`Id`, `Version`, `AppliedAt`, `Description`) VALUES (?, ?, CURRENT_TIMESTAMP, ?) "+
			"ON DUPLICATE KEY UPDATE `Version` = ?, `AppliedAt` = CURRENT_TIMESTAMP, `Description` = ?",
		constants.TableMigration,
	)
	_, err := r.db.ExecContext(ctx, sqlStr, migrationID, version, description, version, description)
	return err
}