package persistence

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

	pkgerrors "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"
)

// 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.db.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 {
			log.Warn().Err(err).Msg("Failed to scan app")
			continue
		}
		apps = append(apps, app)
	}
	if err := rows.Err(); err != nil {
		return nil, pkgerrors.NewInternalError("error iterating apps", err)
	}
	return apps, nil
}

// 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_Icon, constants.FieldSysApp_Color,
		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
	}

	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)
	}

	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_NavigationItems:  navItemsJSON,
		constants.FieldSysApp_UtilityItems:     utilityItemsJSON,
		constants.FieldSysApp_AssignedProfiles: assignedProfilesJSON,
		constants.FieldSysApp_IsDefault:        app.IsDefault,
		constants.FieldLastModifiedDate:        time.Now(),
	}

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

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

// CreateApp creates a new app
func (r *MetadataRepository) CreateApp(ctx context.Context, app *models.AppConfig) error {
	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_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 = r.db.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 {
	navItemsJSON, err := r.marshalJSON(updates.NavigationItems)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal navigation items", err)
	}
	utilityItemsJSON, err := r.marshalJSON(updates.UtilityItems)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal utility items", err)
	}
	assignedProfilesJSON, err := r.marshalJSON(updates.AssignedProfiles)
	if err != nil {
		return pkgerrors.NewInternalError("failed to marshal assigned profiles", err)
	}

	qr := query.Update(constants.TableApp).
		Set(map[string]interface{}{
			constants.FieldSysApp_Label:            updates.Label,
			constants.FieldSysApp_Description:      updates.Description,
			constants.FieldSysApp_Icon:             updates.Icon,
			constants.FieldSysApp_Color:            updates.Color,
			constants.FieldSysApp_IsDefault:        updates.IsDefault,
			constants.FieldSysApp_NavigationItems:  navItemsJSON,
			constants.FieldSysApp_UtilityItems:     utilityItemsJSON,
			constants.FieldSysApp_AssignedProfiles: assignedProfilesJSON,
			constants.FieldLastModifiedDate:        updates.LastModifiedDate,
		}).
		Where(constants.FieldID, appID).
		Build()

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

// DeleteApp deletes an app
func (r *MetadataRepository) DeleteApp(ctx context.Context, appID string) error {
	qr := query.Delete(constants.TableApp).
		Where(constants.FieldID, appID).
		Build()
	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}