package persistence

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

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

type SystemRepository struct {
	db *sql.DB
}

func NewSystemRepository(db *sql.DB) *SystemRepository {
	return &SystemRepository{db: db}
}

// GetExecutor returns the transactional executor if present in context, or the DB connection
func (r *SystemRepository) GetExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// GetLogs retrieves system logs
func (r *SystemRepository) GetLogs(ctx context.Context, limit int) ([]*models.SystemLog, error) {
	if limit <= 0 {
		limit = 100
	}

	q := query.From(constants.TableLog).
		Select([]string{constants.FieldID, constants.FieldTimestamp, constants.FieldLevel, constants.FieldSource, constants.FieldMessage, constants.FieldDetails}).
		OrderBy(constants.FieldTimestamp, constants.SortDESC).
		Limit(limit).
		Build()

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

	logs := make([]*models.SystemLog, 0)
	for rows.Next() {
		var log models.SystemLog
		var details *string

		if err := rows.Scan(&log.ID, &log.Timestamp, &log.Level, &log.Source, &log.Message, &details); err != nil {
			continue
		}

		log.Details = details
		logs = append(logs, &log)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return logs, nil
}

// GetRecentItems retrieves recently viewed items for a user
func (r *SystemRepository) GetRecentItems(ctx context.Context, userID string, limit int) ([]*models.SystemRecent, error) {
	if userID == "" {
		return []*models.SystemRecent{}, nil
	}

	if limit <= 0 {
		limit = 10
	}

	q := query.From(constants.TableRecent).
		Select([]string{constants.FieldID, constants.FieldUserID, constants.FieldObjectAPIName, constants.FieldRecordID, constants.FieldRecordName, constants.FieldTimestamp}).
		Where(constants.FieldUserID, userID).
		OrderBy(constants.FieldTimestamp, constants.SortDESC).
		Limit(limit).
		Build()

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

	items := make([]*models.SystemRecent, 0)
	for rows.Next() {
		var item models.SystemRecent

		if err := rows.Scan(&item.ID, &item.UserID, &item.ObjectAPIName, &item.RecordID, &item.RecordName, &item.Timestamp); err != nil {
			continue
		}

		items = append(items, &item)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return items, nil
}

// GetConfig retrieves a system configuration value
func (r *SystemRepository) GetConfig(ctx context.Context, key string) (*string, error) {
	q := query.From(constants.TableConfig).
		SkipID().
		Select([]string{constants.FieldValue}).
		Where(constants.FieldKeyName, key).
		Limit(1).
		Build()

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

	if rows.Next() {
		var value string
		if err := rows.Scan(&value); err != nil {
			return nil, err
		}
		return &value, nil
	}

	return nil, nil
}

// GetAllConfigs retrieves all system configurations
func (r *SystemRepository) GetAllConfigs(ctx context.Context) ([]*models.SystemConfig, error) {
	q := query.From(constants.TableConfig).
		SkipID().
		Select([]string{constants.FieldKeyName, constants.FieldValue, constants.FieldIsSecret, constants.FieldDescription}).
		Build()

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

	configs := make([]*models.SystemConfig, 0)
	for rows.Next() {
		var config models.SystemConfig
		var isSecret int // TiDB/MySQL boolean is tinyint
		var description *string

		if err := rows.Scan(&config.KeyName, &config.Value, &isSecret, &description); err != nil {
			continue
		}

		config.IsSecret = isSecret != 0
		if description != nil {
			config.Description = description
		}

		configs = append(configs, &config)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return configs, nil
}

// GetConfigID checks if a config key exists and returns the ID if found
func (r *SystemRepository) GetConfigID(ctx context.Context, key string) (string, error) {
	qr := query.From(constants.TableConfig).
		SkipID().
		Select([]string{constants.FieldSysConfig_KeyName}).
		Where(constants.FieldKeyName, key).
		Build()

	exec := r.GetExecutor(ctx)
	var id string
	err := exec.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&id)
	if err == sql.ErrNoRows {
		return "", nil // Not found
	}
	if err != nil {
		return "", err
	}
	return id, nil
}

func (r *SystemRepository) UpsertConfig(ctx context.Context, config *models.SystemConfig) error {
	exec := r.GetExecutor(ctx)
	_, err := exec.ExecContext(
		ctx,
		`INSERT INTO _System_Config (KeyName, Value, IsSecret, Description, CreatedDate, LastModifiedDate)
		 VALUES (?, ?, ?, ?, NOW(), NOW())
		 ON DUPLICATE KEY UPDATE
		   Value = VALUES(Value),
		   IsSecret = VALUES(IsSecret),
		   Description = VALUES(Description),
		   LastModifiedDate = NOW()`,
		config.KeyName,
		config.Value,
		config.IsSecret,
		config.Description,
	)
	return err
}

func (r *SystemRepository) DeleteConfig(ctx context.Context, key string) error {
	exec := r.GetExecutor(ctx)
	_, err := exec.ExecContext(ctx, `DELETE FROM _System_Config WHERE KeyName = ?`, key)
	return err
}

// GetProfileIDByName checks existence by name and returns ID if found
func (r *SystemRepository) GetProfileIDByName(ctx context.Context, name string) (string, error) {
	qr := query.From(constants.TableProfile).
		Select([]string{constants.FieldID}).
		Where(constants.FieldName, name).
		Build()

	var id string
	exec := r.GetExecutor(ctx)
	err := exec.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&id)
	if err == sql.ErrNoRows {
		return "", nil // Not found
	}
	if err != nil {
		return "", err
	}
	return id, nil
}

// GetUserIDByEmail checks existence by email and returns ID if found
func (r *SystemRepository) GetUserIDByEmail(ctx context.Context, email string) (string, error) {
	qr := query.From(constants.TableUser).
		Select([]string{constants.FieldID}).
		Where(constants.FieldEmail, email).
		Build()

	var id string
	exec := r.GetExecutor(ctx)
	err := exec.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&id)
	if err == sql.ErrNoRows {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	return id, nil
}

// BatchUpsertProfiles inserts multiple profiles using direct SQL (Hardened for Aliasing)
func (r *SystemRepository) BatchUpsertProfiles(ctx context.Context, profiles []models.Profile) error {
	if len(profiles) == 0 {
		return nil
	}

	values := []string{}
	args := []interface{}{}

	for _, p := range profiles {
		// (id, name, desc, system, created_date, last_modified)
		values = append(values, fmt.Sprintf("(?, ?, ?, ?, %s, %s)", FuncCurrentTimestamp, FuncCurrentTimestamp))
		args = append(args, p.ID, p.Name, p.Description, p.IsSystem)
	}

	// Use Physical Column Names explicitly for System Tables
	// ID -> Id
	cols := strings.Join([]string{
		constants.ColID, // Id
		constants.FieldProfile_Name,
		constants.FieldProfile_Description,
		constants.FieldProfile_IsSystem,
		constants.ColCreatedDate,
		constants.ColLastModifiedDate,
	}, ", ")

	query := fmt.Sprintf(
		"%s %s (%s) %s %s %s %s=VALUES(%s), %s=VALUES(%s), %s=VALUES(%s), %s=%s",
		KeywordInsertInto, constants.TableProfile,
		cols,
		KeywordValues, strings.Join(values, ","),
		KeywordOnDuplicate,
		constants.ColID, constants.ColID, // Updates ID
		constants.FieldProfile_Description, constants.FieldProfile_Description,
		constants.FieldProfile_IsSystem, constants.FieldProfile_IsSystem,
		constants.ColLastModifiedDate, FuncCurrentTimestamp,
	)

	exec := r.GetExecutor(ctx)
	if _, err := exec.ExecContext(ctx, query, args...); err != nil {
		return apperrors.NewInternalError("batch upsert profiles failed", err)
	}
	return nil
}

// RenameDuplicateProfilesByName renames SF-migrated profiles that collide with bootstrap profile names.
// It updates any profile whose Name matches currentName but whose Id differs from bootstrapID.
// This is idempotent: once renamed, the WHERE clause no longer matches.
func (r *SystemRepository) RenameDuplicateProfilesByName(ctx context.Context, bootstrapID, currentName, newName string) (int64, error) {
	query := fmt.Sprintf(
		"UPDATE %s SET %s = ?, %s = %s WHERE %s = ? AND %s != ?",
		constants.TableProfile,
		constants.FieldProfile_Name,
		constants.ColLastModifiedDate, FuncCurrentTimestamp,
		constants.FieldProfile_Name,
		constants.ColID,
	)
	exec := r.GetExecutor(ctx)
	result, err := exec.ExecContext(ctx, query, newName, currentName, bootstrapID)
	if err != nil {
		return 0, apperrors.NewInternalError("rename duplicate profiles failed", err)
	}
	return result.RowsAffected()
}

// BatchUpsertUsers inserts multiple users using direct SQL (Hardened for Aliasing)
func (r *SystemRepository) BatchUpsertUsers(ctx context.Context, users []models.User) error {
	if len(users) == 0 {
		return nil
	}

	values := []string{}
	args := []interface{}{}

	for _, u := range users {
		values = append(values, fmt.Sprintf("(?, ?, ?, ?, ?, ?, ?, ?, ?, %s, %s)", FuncCurrentTimestamp, FuncCurrentTimestamp))
		// ID, Username, Email, Password, FirstName, LastName, ProfileID, UserRoleId, IsActive
		var roleID interface{}
		if u.UserRoleID != nil {
			roleID = *u.UserRoleID
		}
		args = append(args, u.ID, u.Username, u.Email, u.Password, u.FirstName, u.LastName, u.ProfileID, roleID, true)
	}

	// Use Physical Column Names explicitly for System Tables
	// ID -> Id
	cols := strings.Join([]string{
		constants.ColID, // Id
		constants.FieldUser_Username, constants.FieldUser_Email,
		constants.FieldUser_Password, constants.FieldUser_FirstName, constants.FieldUser_LastName,
		constants.FieldUser_ProfileID, constants.FieldUser_UserRoleID, constants.FieldUser_IsActive,
		constants.ColCreatedDate, constants.ColLastModifiedDate,
	}, ", ")

	// Password update strategy:
	// - On INSERT: always set the hashed password from seed data
	// - On UPDATE: only overwrite if the existing password is not a bcrypt hash
	// Preserve existing bcrypt hashes — setup apply runs on every container start (entrypoint.sh),
	// so we must not overwrite passwords that may have been changed through the app.
	// Only update if the existing value is not already a bcrypt hash (legacy plaintext fix).
	query := fmt.Sprintf(
		`%s %s (%s)
		%s %s
		%s
		%s=VALUES(%s), %s=VALUES(%s),
		%s=VALUES(%s), %s=VALUES(%s), %s=VALUES(%s),
		%s=VALUES(%s),
		%s=IF(%s LIKE '$2%%', %s, VALUES(%s)),
		%s=%s`,
		KeywordInsertInto, constants.TableUser, cols,
		KeywordValues, strings.Join(values, ","),
		KeywordOnDuplicate,
		constants.FieldUser_Username, constants.FieldUser_Username,
		constants.FieldUser_FirstName, constants.FieldUser_FirstName,
		constants.FieldUser_LastName, constants.FieldUser_LastName,
		constants.FieldUser_ProfileID, constants.FieldUser_ProfileID,
		constants.FieldUser_UserRoleID, constants.FieldUser_UserRoleID,
		constants.FieldUser_IsActive, constants.FieldUser_IsActive,
		constants.FieldUser_Password, constants.FieldUser_Password, constants.FieldUser_Password, constants.FieldUser_Password,
		constants.ColLastModifiedDate, FuncCurrentTimestamp,
	)

	exec := r.GetExecutor(ctx)
	if _, err := exec.ExecContext(ctx, query, args...); err != nil {
		return apperrors.NewInternalError("batch upsert users failed", err)
	}
	return nil
}

// ForceUpdateUserPassword updates a user's password unconditionally.
// Used to ensure explicitly configured admin passwords take effect on existing deployments.
func (r *SystemRepository) ForceUpdateUserPassword(ctx context.Context, email string, hashedPassword string) error {
	query := fmt.Sprintf("UPDATE %s SET %s = ?, %s = %s WHERE %s = ?",
		constants.TableUser,
		constants.FieldUser_Password,
		constants.ColLastModifiedDate, FuncCurrentTimestamp,
		constants.FieldUser_Email,
	)
	result, err := r.GetExecutor(ctx).ExecContext(ctx, query, hashedPassword, email)
	if err != nil {
		return err
	}
	rows, _ := result.RowsAffected()
	if rows == 0 {
		return nil // User doesn't exist yet, batch upsert will create them
	}
	return nil
}

// IsUserPermittedForApp checks whether the user has an access grant for the given Connected App.
// It returns true if the user's Profile or any of their PermissionSets is listed in
// _System_ConnectedAppAccess for the app.
func (r *SystemRepository) IsUserPermittedForApp(ctx context.Context, appID string, userID string) (bool, error) {
	var count int
	err := r.db.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM _System_ConnectedAppAccess caa
		WHERE caa.ConnectedAppId = ?
		AND (
			caa.ProfileId = (SELECT ProfileId FROM User WHERE Id = ?)
			OR caa.PermissionSetId IN (
				SELECT PermissionSetId FROM PermissionSetAssignment WHERE AssigneeId = ?
			)
		)`,
		appID, userID, userID,
	).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

// GetConnectedAppByClientID retrieves a connected app by Consumer Key
func (r *SystemRepository) GetConnectedAppByClientID(ctx context.Context, clientID string) (*models.SystemConnectedApp, error) {
	qr := query.From(constants.TableConnectedApp).
		Select([]string{
			constants.FieldID,
			constants.ColSysConnectedApp_Name,
			constants.ColSysConnectedApp_ConsumerKey,
			constants.ColSysConnectedApp_ConsumerSecret,
			constants.ColSysConnectedApp_CallbackURL,
			constants.ColSysConnectedApp_Scopes,
			constants.ColSysConnectedApp_IsActive,
			"RunAsUserId",
			"PermittedUsers",
		}).
		Where(constants.ColSysConnectedApp_ConsumerKey, clientID).
		Limit(1).
		Build()

	var app models.SystemConnectedApp
	var isActive int
	var runAsUserID, permittedUsers *string
	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(
		&app.ID,
		&app.Name,
		&app.ConsumerKey,
		&app.ConsumerSecret,
		&app.CallbackURL,
		&app.Scopes,
		&isActive,
		&runAsUserID,
		&permittedUsers,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, apperrors.NewNotFoundError("connected_app", clientID)
		}
		return nil, err
	}

	app.IsActive = isActive != 0

	if runAsUserID != nil {
		app.RunAsUserID = runAsUserID
	}
	if permittedUsers != nil {
		app.PermittedUsers = permittedUsers
	}

	if !app.IsActive {
		return nil, apperrors.NewForbiddenError("connected app is disabled")
	}

	return &app, nil
}