package controlplane

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

const navigationVisibilitySSoTMigrationID = "20260512_navigation_visibility_ssot"

type navigationVisibilityAppRow struct {
	ID               string
	AssignedProfiles []string
	IsDefault        bool
	NavigationItems  []models.NavigationItem
}

// BackfillNavigationVisibilitySSoT materializes legacy _System_App visibility
// into the Salesforce-modeled ProfileAppVisibility and ProfileTabSetting rows
// used by runtime navigation. The migration is insert-only for canonical rows:
// existing app/tab settings are never overwritten by legacy AssignedProfiles.
func BackfillNavigationVisibilitySSoT(ctx context.Context, db *sql.DB) (int, int, error) {
	if db == nil {
		return 0, 0, fmt.Errorf("navigation visibility SSoT backfill requires a database")
	}
	applied, err := bootstate.MigrationApplied(ctx, db, navigationVisibilitySSoTMigrationID)
	if err != nil {
		return 0, 0, err
	}
	if applied {
		return 0, 0, nil
	}

	profileIDs, err := listNavigationVisibilityProfiles(ctx, db)
	if err != nil {
		return 0, 0, err
	}
	apps, err := listNavigationVisibilityApps(ctx, db)
	if err != nil {
		return 0, 0, err
	}
	objectAPINameByLower, err := listNavigationVisibilityObjectAPINameMap(ctx, db)
	if err != nil {
		return 0, 0, err
	}

	appRows := 0
	tabRows := 0
	for _, app := range apps {
		assigned := stringSet(app.AssignedProfiles)
		visibleToAll := len(assigned) == 0
		for _, profileID := range profileIDs {
			visible := visibleToAll
			if !visible {
				_, visible = assigned[profileID]
			}
			if err := insertLegacyProfileAppVisibility(ctx, db, profileID, app.ID, visible, app.IsDefault && visible); err != nil {
				return appRows, tabRows, err
			}
			appRows++
		}

		visibleProfileIDs, err := listNavigationVisibilityVisibleProfilesForApp(ctx, db, app.ID)
		if err != nil {
			return appRows, tabRows, err
		}
		for _, tabName := range legacyNavigationTabNames(app.NavigationItems, objectAPINameByLower) {
			for _, profileID := range visibleProfileIDs {
				if err := insertLegacyProfileTabSetting(ctx, db, profileID, tabName); err != nil {
					return appRows, tabRows, err
				}
				tabRows++
			}
		}
	}

	if err := bootstate.RecordMigrationApplied(ctx, db, navigationVisibilitySSoTMigrationID, 1, "Backfill app/tab navigation visibility SSoT rows"); err != nil {
		return appRows, tabRows, err
	}
	return appRows, tabRows, nil
}

func listNavigationVisibilityProfiles(ctx context.Context, db *sql.DB) ([]string, error) {
	query := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` = 1 AND `%s` = 0 ORDER BY `%s`",
		constants.FieldID,
		constants.TableProfile,
		constants.FieldProfile_IsActive,
		constants.FieldProfile_IsDeleted,
		constants.FieldID,
	)
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query active profiles: %w", err)
	}
	defer rows.Close()

	var profileIDs []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("scan active profile: %w", err)
		}
		profileIDs = append(profileIDs, id)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active profiles: %w", err)
	}
	return profileIDs, nil
}

func listNavigationVisibilityVisibleProfilesForApp(ctx context.Context, db *sql.DB, appID string) ([]string, error) {
	query := fmt.Sprintf(
		"SELECT p.`%s` FROM `%s` p "+
			"INNER JOIN `%s` pav ON pav.`%s` = p.`%s` AND pav.`%s` = ? AND pav.`%s` = 1 "+
			"WHERE p.`%s` = 1 AND p.`%s` = 0 ORDER BY p.`%s`",
		constants.FieldID,
		constants.TableProfile,
		constants.TableProfileAppVisibility,
		constants.FieldProfileAppVisibility_ProfileID,
		constants.FieldID,
		constants.FieldProfileAppVisibility_AppID,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfile_IsActive,
		constants.FieldProfile_IsDeleted,
		constants.FieldID,
	)
	rows, err := db.QueryContext(ctx, query, appID)
	if err != nil {
		return nil, fmt.Errorf("query visible profiles for app %q: %w", appID, err)
	}
	defer rows.Close()

	var profileIDs []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("scan visible profile for app %q: %w", appID, err)
		}
		profileIDs = append(profileIDs, id)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate visible profiles for app %q: %w", appID, err)
	}
	return profileIDs, nil
}

func listNavigationVisibilityApps(ctx context.Context, db *sql.DB) ([]navigationVisibilityAppRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s`, `%s` FROM `%s` ORDER BY `%s`",
		constants.FieldID,
		constants.FieldSysApp_AssignedProfiles,
		constants.FieldSysApp_IsDefault,
		constants.FieldSysApp_NavigationItems,
		constants.TableApp,
		constants.FieldID,
	)
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query apps for navigation visibility: %w", err)
	}
	defer rows.Close()

	var apps []navigationVisibilityAppRow
	for rows.Next() {
		var app navigationVisibilityAppRow
		var assignedProfiles sql.NullString
		var navigationItems sql.NullString
		if err := rows.Scan(&app.ID, &assignedProfiles, &app.IsDefault, &navigationItems); err != nil {
			return nil, fmt.Errorf("scan app navigation visibility: %w", err)
		}
		profiles, err := parseLegacyAssignedProfiles(assignedProfiles)
		if err != nil {
			return nil, fmt.Errorf("parse AssignedProfiles for app %q: %w", app.ID, err)
		}
		items, err := parseLegacyNavigationItems(navigationItems)
		if err != nil {
			return nil, fmt.Errorf("parse NavigationItems for app %q: %w", app.ID, err)
		}
		app.AssignedProfiles = profiles
		app.NavigationItems = items
		apps = append(apps, app)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate app navigation visibility: %w", err)
	}
	return apps, nil
}

func listNavigationVisibilityObjectAPINameMap(ctx context.Context, db *sql.DB) (map[string]string, error) {
	query := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` = 0 AND `%s` IS NOT NULL AND `%s` <> ''",
		constants.FieldSysObject_APIName,
		constants.TableObject,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_APIName,
	)
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query object API names for navigation visibility: %w", err)
	}
	defer rows.Close()

	out := map[string]string{}
	for rows.Next() {
		var apiName string
		if err := rows.Scan(&apiName); err != nil {
			return nil, fmt.Errorf("scan object API name for navigation visibility: %w", err)
		}
		out[strings.ToLower(apiName)] = apiName
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate object API names for navigation visibility: %w", err)
	}
	return out, nil
}

func parseLegacyAssignedProfiles(raw sql.NullString) ([]string, error) {
	if !raw.Valid {
		return nil, nil
	}
	text := strings.TrimSpace(raw.String)
	if text == "" || text == "null" {
		return nil, nil
	}
	var profiles []string
	if err := json.Unmarshal([]byte(text), &profiles); err != nil {
		return nil, err
	}
	out := make([]string, 0, len(profiles))
	seen := map[string]struct{}{}
	for _, profile := range profiles {
		profile = strings.TrimSpace(profile)
		if profile == "" {
			continue
		}
		if _, ok := seen[profile]; ok {
			continue
		}
		seen[profile] = struct{}{}
		out = append(out, profile)
	}
	return out, nil
}

func parseLegacyNavigationItems(raw sql.NullString) ([]models.NavigationItem, error) {
	if !raw.Valid {
		return nil, nil
	}
	text := strings.TrimSpace(raw.String)
	if text == "" || text == "null" {
		return nil, nil
	}
	var items []models.NavigationItem
	if err := json.Unmarshal([]byte(text), &items); err != nil {
		return nil, err
	}
	return items, nil
}

func stringSet(values []string) map[string]struct{} {
	out := make(map[string]struct{}, len(values))
	for _, value := range values {
		out[value] = struct{}{}
	}
	return out
}

func legacyNavigationTabNames(items []models.NavigationItem, objectAPINameByLower map[string]string) []string {
	seen := map[string]struct{}{}
	tabNames := make([]string, 0, len(items))
	for _, item := range items {
		tabName, ok := legacyNavigationTabName(item, objectAPINameByLower)
		if !ok {
			continue
		}
		key := strings.ToLower(tabName)
		if _, exists := seen[key]; exists {
			continue
		}
		seen[key] = struct{}{}
		tabNames = append(tabNames, tabName)
	}
	return tabNames
}

func legacyNavigationTabName(item models.NavigationItem, objectAPINameByLower map[string]string) (string, bool) {
	if strings.EqualFold(item.Type, constants.NavigationTypeObject) {
		objectAPIName := strings.TrimSpace(item.ObjectAPIName)
		if objectAPIName == "" {
			return "", false
		}
		if canonical, ok := objectAPINameByLower[strings.ToLower(objectAPIName)]; ok {
			return canonical, true
		}
		return objectAPIName, true
	}
	return item.CanonicalTabName()
}

func insertLegacyProfileAppVisibility(ctx context.Context, db *sql.DB, profileID, appID string, isVisible, isDefault bool) error {
	query := fmt.Sprintf(
		"INSERT IGNORE INTO `%s` (`%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`) VALUES (?, ?, ?, ?, ?, NOW(), NOW())",
		constants.TableProfileAppVisibility,
		constants.FieldProfileAppVisibility_ID,
		constants.FieldProfileAppVisibility_ProfileID,
		constants.FieldProfileAppVisibility_AppID,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfileAppVisibility_IsDefault,
		constants.FieldProfileAppVisibility_CreatedDate,
		constants.FieldProfileAppVisibility_LastModifiedDate,
	)
	if _, err := db.ExecContext(ctx, query, utils.GenerateID(), profileID, appID, isVisible, isDefault); err != nil {
		return fmt.Errorf("insert ProfileAppVisibility %s/%s: %w", profileID, appID, err)
	}
	return nil
}

func insertLegacyProfileTabSetting(ctx context.Context, db *sql.DB, profileID, tabName string) error {
	query := fmt.Sprintf(
		"INSERT IGNORE INTO `%s` (`%s`, `%s`, `%s`, `%s`, `%s`, `%s`) VALUES (?, ?, ?, ?, NOW(), NOW())",
		constants.TableProfileTabSetting,
		constants.FieldProfileTabSetting_ID,
		constants.FieldProfileTabSetting_ProfileID,
		constants.FieldProfileTabSetting_TabName,
		constants.FieldProfileTabSetting_Visibility,
		constants.FieldProfileTabSetting_CreatedDate,
		constants.FieldProfileTabSetting_LastModifiedDate,
	)
	if _, err := db.ExecContext(ctx, query, utils.GenerateID(), profileID, tabName, constants.TabVisibilityDefaultOn); err != nil {
		return fmt.Errorf("insert ProfileTabSetting %s/%s: %w", profileID, tabName, err)
	}
	return nil
}