package persistence

import (
	"context"
	"fmt"
	"strings"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// GetEffectiveAppVisibilities returns effective app visibility from the
// Salesforce-modeled profile baseline plus additive permission-set grants.
// Ordinary PermissionSetAppVisibility false rows are ignored because permission
// sets can add app access but do not revoke profile access.
func (r *PermissionRepository) GetEffectiveAppVisibilities(ctx context.Context, userID, profileID string) (map[string]models.ProfileAppVisibility, error) {
	sqlQuery := fmt.Sprintf(
		"SELECT app_id, MAX(profile_visible), MAX(profile_hidden), MAX(profile_default), MAX(additive_visible) FROM ("+
			"SELECT pav.`%s` AS app_id, "+
			"CASE WHEN pav.`%s` = 1 THEN 1 ELSE 0 END AS profile_visible, "+
			"CASE WHEN pav.`%s` = 0 THEN 1 ELSE 0 END AS profile_hidden, "+
			"CASE WHEN pav.`%s` = 1 AND pav.`%s` = 1 THEN 1 ELSE 0 END AS profile_default, "+
			"0 AS additive_visible "+
			"FROM `%s` pav WHERE pav.`%s` = ? AND EXISTS (SELECT 1 FROM Profile p WHERE p.Id = pav.ProfileId AND p.IsDeleted = 0) "+
			"UNION ALL "+
			"SELECT psav.`%s` AS app_id, 0 AS profile_visible, 0 AS profile_hidden, 0 AS profile_default, 1 AS additive_visible "+
			"FROM `%s` psav WHERE psav.`%s` IN (%s) AND psav.`%s` = 1"+
			") effective_apps GROUP BY app_id",
		constants.FieldProfileAppVisibility_AppID,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfileAppVisibility_IsDefault,
		constants.TableProfileAppVisibility,
		constants.FieldProfileAppVisibility_ProfileID,
		constants.FieldPermissionSetAppVisibility_AppID,
		constants.TablePermissionSetAppVisibility,
		constants.FieldPermissionSetAppVisibility_PermissionSetID,
		additivePermissionSetIDsSQL(),
		constants.FieldPermissionSetAppVisibility_IsVisible,
	)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlQuery, profileID, userID, userID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query effective app visibility", err)
	}
	defer rows.Close()

	effective := map[string]models.ProfileAppVisibility{}
	for rows.Next() {
		var appID string
		var profileVisible, profileHidden, profileDefault, additiveVisible int
		if err := rows.Scan(&appID, &profileVisible, &profileHidden, &profileDefault, &additiveVisible); err != nil {
			return nil, apperrors.NewInternalError("failed to scan effective app visibility", err)
		}
		visible := additiveVisible != 0 || profileVisible != 0
		if !visible && profileHidden == 0 {
			continue
		}
		effective[appID] = models.ProfileAppVisibility{
			AppID:     appID,
			IsVisible: visible,
			IsDefault: visible && profileDefault != 0,
		}
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating effective app visibility", err)
	}
	return effective, nil
}

// GetEffectiveTabSettings returns the most-permissive effective tab setting.
// Ranking follows Salesforce semantics:
// DefaultOn/Visible > DefaultOff/Available > Hidden/None.
func (r *PermissionRepository) GetEffectiveTabSettings(ctx context.Context, userID, profileID string) (map[string]models.ProfileTabSetting, error) {
	profileRank := tabVisibilityRankSQL("pts", constants.FieldProfileTabSetting_Visibility)
	permSetRank := tabVisibilityRankSQL("psts", constants.FieldPermissionSetTabSetting_Visibility)
	sqlQuery := fmt.Sprintf(
		"SELECT tab_name, MAX(visibility_rank) FROM ("+
			"SELECT pts.`%s` AS tab_name, %s AS visibility_rank "+
			"FROM `%s` pts WHERE pts.`%s` = ? AND EXISTS (SELECT 1 FROM Profile p WHERE p.Id = pts.ProfileId AND p.IsDeleted = 0) "+
			"UNION ALL "+
			"SELECT psts.`%s` AS tab_name, %s AS visibility_rank "+
			"FROM `%s` psts WHERE psts.`%s` IN (%s)"+
			") effective_tabs GROUP BY tab_name",
		constants.FieldProfileTabSetting_TabName,
		profileRank,
		constants.TableProfileTabSetting,
		constants.FieldProfileTabSetting_ProfileID,
		constants.FieldPermissionSetTabSetting_TabName,
		permSetRank,
		constants.TablePermissionSetTabSetting,
		constants.FieldPermissionSetTabSetting_PermissionSetID,
		additivePermissionSetIDsSQL(),
	)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlQuery, profileID, userID, userID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query effective tab settings", err)
	}
	defer rows.Close()

	effective := map[string]models.ProfileTabSetting{}
	for rows.Next() {
		var tabName string
		var rank int
		if err := rows.Scan(&tabName, &rank); err != nil {
			return nil, apperrors.NewInternalError("failed to scan effective tab setting", err)
		}
		visibility := tabVisibilityFromRank(rank)
		if visibility == "" {
			continue
		}
		effective[tabName] = models.ProfileTabSetting{
			TabName:    tabName,
			Visibility: visibility,
		}
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating effective tab settings", err)
	}
	return effective, nil
}

// EnsureNavigationVisibilityForProfile inserts missing canonical navigation
// visibility rows for a Profile. App visibility for a new profile preserves the
// post-cutover canonical shape: apps visible to every other active profile
// remain visible; apps hidden from or missing for any other active profile
// remain hidden until an app-permission editor grants them. Tab grants are
// authored independently through canonical permission metadata, never menus.
func (r *PermissionRepository) EnsureNavigationVisibilityForProfile(ctx context.Context, profileID string) error {
	if strings.TrimSpace(profileID) == "" {
		return nil
	}
	if r.permissionStateDirectCoordinator != nil &&
		!r.permissionStateDirectCoordinator.transactions.ownsPessimisticTx(ctx) {
		return r.runPermissionStateDirectWriterGroup(ctx, func(txCtx context.Context) error {
			return r.EnsureNavigationVisibilityForProfile(txCtx, profileID)
		})
	}
	exec := r.getExecutor(ctx)
	return r.ensureProfileAppVisibilityRows(ctx, exec, profileID)
}

func (r *PermissionRepository) ensureProfileAppVisibilityRows(ctx context.Context, exec Executor, profileID string) error {
	const writerID = "nexuscrm-persistence/perm_app_tab.go#PermissionRepository.ensureProfileAppVisibilityRows"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				permissionStateDirectTargetFor(
					constants.TableProfileAppVisibility,
					permissionStateDirectEqual(constants.FieldProfileAppVisibility_ProfileID),
					profileID,
				),
			},
			func(txCtx context.Context) error {
				return r.ensureProfileAppVisibilityRows(txCtx, r.getExecutor(txCtx), profileID)
			},
		)
	}
	sqlQuery := fmt.Sprintf(`
INSERT IGNORE INTO %s (
	%s, %s, %s, %s, %s, %s, %s
)
SELECT
	UUID(),
	?,
	app.%s,
	IF(blocked_for_active_profile.%s IS NULL, 1, 0),
	IF(app.%s = 1 AND blocked_for_active_profile.%s IS NULL, 1, 0),
	NOW(),
	NOW()
FROM %s app
LEFT JOIN (
	SELECT DISTINCT app_inner.%s AS %s
	FROM %s app_inner
	INNER JOIN %s p
		ON p.%s <> ?
		AND p.%s = 1
		AND p.%s = 0
	LEFT JOIN %s pav
		ON pav.%s = p.%s
		AND pav.%s = app_inner.%s
	WHERE pav.%s IS NULL OR pav.%s = 0
) blocked_for_active_profile
	ON blocked_for_active_profile.%s = app.%s`,
		quotedNavigationTable(constants.TableProfileAppVisibility),
		constants.FieldProfileAppVisibility_ID,
		constants.FieldProfileAppVisibility_ProfileID,
		constants.FieldProfileAppVisibility_AppID,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfileAppVisibility_IsDefault,
		constants.FieldProfileAppVisibility_CreatedDate,
		constants.FieldProfileAppVisibility_LastModifiedDate,
		constants.FieldID,
		constants.FieldProfileAppVisibility_AppID,
		constants.FieldSysApp_IsDefault,
		constants.FieldProfileAppVisibility_AppID,
		quotedNavigationTable(constants.TableApp),
		constants.FieldID,
		constants.FieldProfileAppVisibility_AppID,
		quotedNavigationTable(constants.TableApp),
		quotedNavigationTable(constants.TableProfile),
		constants.FieldID,
		constants.FieldProfile_IsActive,
		constants.FieldProfile_IsDeleted,
		quotedNavigationTable(constants.TableProfileAppVisibility),
		constants.FieldProfileAppVisibility_ProfileID,
		constants.FieldID,
		constants.FieldProfileAppVisibility_AppID,
		constants.FieldID,
		constants.FieldProfileAppVisibility_ID,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfileAppVisibility_AppID,
		constants.FieldID,
	)
	if _, err := exec.ExecContext(ctx, sqlQuery, profileID, profileID); err != nil {
		return apperrors.NewInternalError("failed to ensure profile app visibility rows", err)
	}
	return nil
}

func quotedNavigationTable(name string) string {
	return fmt.Sprintf("`%s`", name)
}

func tabVisibilityRankSQL(alias, field string) string {
	var b strings.Builder
	fmt.Fprintf(&b, "CASE TRIM(%s.`%s`) ", alias, field)
	for _, entry := range constants.TabVisibilityRankEntries() {
		fmt.Fprintf(&b, "WHEN '%s' THEN %d ", entry.Visibility, entry.Rank)
	}
	b.WriteString("ELSE 0 END")
	return b.String()
}

func tabVisibilityFromRank(rank int) string {
	return constants.TabVisibilityFromRank(rank)
}