package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	pkgerrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// GetAllApps queries all apps
func (r *MetadataRepository) GetAllApps(ctx context.Context) ([]*models.AppConfig, error) {
	qr := query.From(constants.TableApp).
		Select(constants.ColsSystemApp).
		Build()

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

	apps := make([]*models.AppConfig, 0)
	for rows.Next() {
		app, err := r.scanApp(rows)
		if err != nil {
			return nil, pkgerrors.NewInternalError("failed to scan app", err)
		}
		apps = append(apps, app)
	}
	if err := rows.Err(); err != nil {
		return nil, pkgerrors.NewInternalError("error iterating apps", err)
	}
	return apps, nil
}

// GetApplicationDefinitions returns installable application shell facts only.
// Current navigation is deliberately not selected: Catalog compilation cannot
// capture an administrator's mutable navigation as release content.
func (r *MetadataRepository) GetApplicationDefinitions(ctx context.Context) ([]*models.AppConfig, error) {
	columns := append([]string(nil), constants.ColsSystemApp...)
	for i, column := range columns {
		if column == constants.FieldSysApp_NavigationItems {
			columns[i] = "JSON_ARRAY()"
		}
	}
	rows, err := r.GetExecutor(ctx).QueryContext(ctx, "SELECT "+strings.Join(columns, ", ")+" FROM `_System_App` ORDER BY Id")
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	result := make([]*models.AppConfig, 0)
	for rows.Next() {
		app, err := r.scanApp(rows)
		if err != nil {
			return nil, err
		}
		result = append(result, app)
	}
	return result, rows.Err()
}

// GetApp queries a single app by ID
func (r *MetadataRepository) GetApp(ctx context.Context, id string) (*models.AppConfig, error) {
	qr := query.From(constants.TableApp).
		Select(constants.ColsSystemApp).
		Where(constants.FieldID, id). // Use FieldID for consistency
		Build()

	app, err := r.scanApp(r.db.QueryRowContext(ctx, qr.SQL, qr.Params...))
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return app, nil
}

// GetAppWithTx queries a single app by ID within a transaction
func (r *MetadataRepository) GetAppWithTx(ctx context.Context, tx interface{}, id string) (*models.AppConfig, error) {
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db // Partial validation, though tx should be valid if passed
	}

	cols := strings.Join([]string{
		constants.ColID, constants.FieldSysApp_Name, constants.FieldSysApp_Label,
		constants.FieldSysApp_Description, constants.FieldSysApp_NamespacePrefix, constants.FieldSysApp_Icon, constants.FieldSysApp_Color,
		constants.FieldSysApp_FrontendAppID,
		constants.FieldSysApp_NavigationItems, constants.FieldSysApp_UtilityItems, constants.FieldSysApp_AssignedProfiles,
		constants.FieldSysApp_IsDefault, constants.FieldCreatedDate, constants.FieldLastModifiedDate,
	}, ", ")
	query := fmt.Sprintf("SELECT %s FROM %s WHERE %s = ?", cols, constants.TableApp, constants.ColID)
	app, err := r.scanApp(exec.QueryRowContext(ctx, query, id))
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return app, nil
}

// UpdateAppWithTx updates an existing app within a provided transaction
func (r *MetadataRepository) UpdateAppWithTx(ctx context.Context, tx interface{}, appID string, app *models.AppConfig) error {
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}
	if err := r.requirePermissionStateOwnedExecutor(ctx, exec); err != nil {
		return err
	}

	if err := r.updateAppWithExec(ctx, exec, appID, app); err != nil {
		return err
	}
	return r.ensureCanonicalAppVisibilityRows(ctx, exec, app)
}

func (r *MetadataRepository) updateAppWithExec(ctx context.Context, exec Executor, appID string, app *models.AppConfig) error {
	const writerID = "nexuscrm-persistence/repo_app.go#MetadataRepository.updateAppWithExec"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		if err := r.requirePermissionStateOwnedExecutor(ctx, exec); err != nil {
			return err
		}
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				permissionStateDirectTargetFor(
					constants.TableApp,
					permissionStateDirectEqual(constants.FieldID),
					appID,
				),
			},
			func(txCtx context.Context) error {
				return r.updateAppWithExec(
					withPermissionStateDirectWriter(txCtx, writerID),
					r.GetExecutor(txCtx),
					appID,
					app,
				)
			},
		)
	}

	utilityItemsJSON, err := r.marshalJSON(app.UtilityItems)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal utility items", err)
	}
	assignedProfilesJSON, err := r.marshalJSON(app.AssignedProfiles)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal assigned profiles", err)
	}

	values := map[string]interface{}{
		constants.FieldSysApp_Label:            app.Label,
		constants.FieldSysApp_Description:      app.Description,
		constants.FieldSysApp_Icon:             app.Icon,
		constants.FieldSysApp_Color:            app.Color,
		constants.FieldSysApp_FrontendAppID:    nullIfEmpty(app.FrontendAppID),
		constants.FieldSysApp_UtilityItems:     utilityItemsJSON,
		constants.FieldSysApp_AssignedProfiles: assignedProfilesJSON,
		constants.FieldSysApp_IsDefault:        app.IsDefault,
		constants.FieldLastModifiedDate:        time.Now(),
	}
	// Navigation and its owner are one durable identity. A shell edit may not
	// rebind an initialized document to a different frontend workload.
	var revision uint64
	var owner sql.NullString
	if err := exec.QueryRowContext(ctx, "SELECT NavigationRevision, FrontendAppId FROM `_System_App` WHERE Id = ? FOR UPDATE", appID).Scan(&revision, &owner); err != nil {
		return err
	}
	if revision != 0 && owner.String != app.FrontendAppID {
		return pkgerrors.NewValidationError("FrontendAppId", "initialized navigation cannot be rebound to another application owner")
	}
	if app.NavigationItems != nil {
		if revision != 0 {
			return pkgerrors.NewValidationError("NavigationItems", "initialized navigation must be saved through the application navigation owner")
		}
		navItemsJSON, err := r.marshalJSON(app.NavigationItems)
		if err != nil {
			return pkgerrors.NewInternalError("failed to marshal navigation items", err)
		}
		values[constants.FieldSysApp_NavigationItems] = navItemsJSON
	}

	qr := query.Update(constants.TableApp).
		Set(values).
		Where(constants.FieldID, appID).
		Build()

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

func nullIfEmpty(value string) sql.NullString {
	return sql.NullString{String: value, Valid: strings.TrimSpace(value) != ""}
}

// CreateApp creates a new app
func (r *MetadataRepository) CreateApp(ctx context.Context, app *models.AppConfig) error {
	if r.permissionStateDirectCoordinator != nil {
		return r.runPermissionStateDirectWriterGroup(ctx, func(txCtx context.Context) error {
			return r.createAppInOwnedTransaction(txCtx, app)
		})
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	if err := r.createAppWithExec(ctx, tx, app); err != nil {
		_ = tx.Rollback()
		return err
	}
	if err := r.ensureCanonicalAppVisibilityRows(ctx, tx, app); err != nil {
		_ = tx.Rollback()
		return err
	}
	return tx.Commit()
}

func (r *MetadataRepository) createAppInOwnedTransaction(ctx context.Context, app *models.AppConfig) error {
	exec := r.GetExecutor(ctx)
	if err := r.createAppWithExec(ctx, exec, app); err != nil {
		return err
	}
	return r.ensureCanonicalAppVisibilityRows(ctx, exec, app)
}

func (r *MetadataRepository) createAppWithExec(ctx context.Context, exec Executor, app *models.AppConfig) error {
	const writerID = "nexuscrm-persistence/repo_app.go#MetadataRepository.createAppWithExec"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		if err := r.requirePermissionStateOwnedExecutor(ctx, exec); err != nil {
			return err
		}
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				permissionStateDirectTargetFor(
					constants.TableApp,
					permissionStateDirectEqual(constants.FieldID),
					app.ID,
				),
			},
			func(txCtx context.Context) error {
				return r.createAppWithExec(
					withPermissionStateDirectWriter(txCtx, writerID),
					r.GetExecutor(txCtx),
					app,
				)
			},
		)
	}

	navItemsJSON, err := r.marshalJSON(app.NavigationItems)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal navigation items", err)
	}
	utilityItemsJSON, err := r.marshalJSON(app.UtilityItems)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal utility items", err)
	}
	assignedProfilesJSON, err := r.marshalJSON(app.AssignedProfiles)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal assigned profiles", err)
	}

	qr := query.Insert(constants.TableApp, map[string]interface{}{
		constants.FieldID:                      app.ID,
		constants.FieldSysApp_Name:             app.ID, // Name defaults to ID; distinct Name field may be added later
		constants.FieldSysApp_Label:            app.Label,
		constants.FieldSysApp_Description:      app.Description,
		constants.FieldSysApp_Icon:             app.Icon,
		constants.FieldSysApp_Color:            app.Color,
		constants.FieldSysApp_FrontendAppID:    nullIfEmpty(app.FrontendAppID),
		constants.FieldSysApp_NavigationItems:  navItemsJSON,
		constants.FieldSysApp_UtilityItems:     utilityItemsJSON,
		constants.FieldSysApp_AssignedProfiles: assignedProfilesJSON,
		constants.FieldSysApp_IsDefault:        app.IsDefault,
		constants.FieldCreatedDate:             app.CreatedDate,
		constants.FieldLastModifiedDate:        app.LastModifiedDate,
	}).Build()

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

// CreateAppMenuItem creates an entry in AppMenuItem table for an app (Salesforce parity)
// This ensures apps appear in App Launcher which queries AppMenuItem
func (r *MetadataRepository) CreateAppMenuItem(ctx context.Context, app *models.AppConfig) error {
	// Default StartUrl to app route
	startUrl := "/lightning/app/" + app.ID

	qr := query.Insert(constants.TableAppMenuItem, map[string]interface{}{
		constants.FieldAppMenuItem_ID:          utils.GenerateID(),
		constants.FieldAppMenuItem_Label:       app.Label,
		constants.FieldAppMenuItem_Description: app.Description,
		constants.FieldAppMenuItem_StartURL:    startUrl,
		constants.FieldAppMenuItem_Type:        "App",
		constants.FieldAppMenuItem_IsVisible:   true,
		constants.FieldAppMenuItem_SortOrder:   0,
		constants.FieldAppMenuItem_AppID:       app.ID,
		constants.FieldAppMenuItem_Icon:        app.Icon,
		constants.FieldAppMenuItem_Color:       app.Color,
		constants.FieldAppMenuItem_IsDeleted:   false,
		constants.FieldAppMenuItem_CreatedDate: app.CreatedDate,
	}).Build()

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

// UpdateApp updates an existing app
func (r *MetadataRepository) UpdateApp(ctx context.Context, appID string, updates *models.AppConfig) error {
	if r.permissionStateDirectCoordinator != nil {
		return r.runPermissionStateDirectWriterGroup(ctx, func(txCtx context.Context) error {
			return r.updateAppInOwnedTransaction(txCtx, appID, updates)
		})
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	if err := r.updateAppWithExec(ctx, tx, appID, updates); err != nil {
		_ = tx.Rollback()
		return err
	}
	if err := r.ensureCanonicalAppVisibilityRows(ctx, tx, updates); err != nil {
		_ = tx.Rollback()
		return err
	}
	return tx.Commit()
}

func (r *MetadataRepository) updateAppInOwnedTransaction(ctx context.Context, appID string, updates *models.AppConfig) error {
	exec := r.GetExecutor(ctx)
	if err := r.updateAppWithExec(ctx, exec, appID, updates); err != nil {
		return err
	}
	return r.ensureCanonicalAppVisibilityRows(ctx, exec, updates)
}

// DeleteApp deletes an app
func (r *MetadataRepository) DeleteApp(ctx context.Context, appID string) error {
	const writerID = "nexuscrm-persistence/repo_app.go#MetadataRepository.DeleteApp"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			deleteAppPermissionStateTargets(appID),
			func(txCtx context.Context) error {
				return r.DeleteApp(withPermissionStateDirectWriter(txCtx, writerID), appID)
			},
		)
	}

	qr := query.Delete(constants.TableApp).
		Where(constants.FieldID, appID).
		Build()
	_, err := r.GetExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

func deleteAppPermissionStateTargets(appID string) []permissionStateDirectTarget {
	return []permissionStateDirectTarget{
		permissionStateDirectTargetFor(
			constants.TableApp,
			permissionStateDirectEqual(constants.FieldID),
			appID,
		),
		permissionStateDirectTargetWithPermissionSetDefinitionCurrentOwnerDetachPrelock(
			permissionStateDirectTargetFor(
				constants.TablePermissionSetAppVisibility,
				permissionStateDirectEqual(constants.FieldPermissionSetAppVisibility_AppID),
				appID,
			),
			constants.FieldPermissionSetAppVisibility_PermissionSetID,
		),
		permissionStateDirectTargetFor(
			constants.TableProfileAppVisibility,
			permissionStateDirectEqual(constants.FieldProfileAppVisibility_AppID),
			appID,
		),
	}
}

func (r *MetadataRepository) ensureCanonicalAppVisibilityRows(ctx context.Context, exec Executor, app *models.AppConfig) error {
	const writerID = "nexuscrm-persistence/repo_app.go#MetadataRepository.ensureCanonicalAppVisibilityRows"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		if err := r.requirePermissionStateOwnedExecutor(ctx, exec); err != nil {
			return err
		}
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				permissionStateDirectTargetFor(
					constants.TableProfileAppVisibility,
					permissionStateDirectEqual(constants.FieldProfileAppVisibility_AppID),
					app.ID,
				),
			},
			func(txCtx context.Context) error {
				return r.ensureCanonicalAppVisibilityRows(
					withPermissionStateDirectWriter(txCtx, writerID),
					r.GetExecutor(txCtx),
					app,
				)
			},
		)
	}

	profileIDs, err := r.listActiveProfileIDsForAppVisibility(ctx, exec)
	if err != nil {
		return err
	}
	if len(profileIDs) == 0 {
		return nil
	}

	appInsertSQL := 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,
	)
	for _, profileID := range profileIDs {
		if _, err := exec.ExecContext(ctx, appInsertSQL, utils.GenerateID(), profileID, app.ID, true, app.IsDefault); err != nil {
			return pkgerrors.NewInternalError("failed to insert profile app visibility", err)
		}
	}

	defaultSQL := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = IF(`%s` = 1, ?, 0), `%s` = NOW() WHERE `%s` = ?",
		constants.TableProfileAppVisibility,
		constants.FieldProfileAppVisibility_IsDefault,
		constants.FieldProfileAppVisibility_IsVisible,
		constants.FieldProfileAppVisibility_LastModifiedDate,
		constants.FieldProfileAppVisibility_AppID,
	)
	if _, err := exec.ExecContext(ctx, defaultSQL, app.IsDefault, app.ID); err != nil {
		return pkgerrors.NewInternalError("failed to update profile app defaults", err)
	}

	return nil
}

func (r *MetadataRepository) listActiveProfileIDsForAppVisibility(ctx context.Context, exec Executor) ([]string, error) {
	sqlQuery := 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 := exec.QueryContext(ctx, sqlQuery)
	if err != nil {
		return nil, pkgerrors.NewInternalError("failed to query active profiles", err)
	}
	defer rows.Close()

	var profileIDs []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, pkgerrors.NewInternalError("failed to scan active profile", err)
		}
		profileIDs = append(profileIDs, id)
	}
	if err := rows.Err(); err != nil {
		return nil, pkgerrors.NewInternalError("error iterating active profiles", err)
	}
	return profileIDs, nil
}