package persistence

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

	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/kernel/pkg/schemautil"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

type UserRepository struct {
	db                               *sql.DB
	permissionStateDirectCoordinator *PermissionStateDirectWriterCoordinator
}

func NewUserRepository(db *sql.DB) *UserRepository {
	return &UserRepository{db: db}
}

// SetPermissionStateDirectWriterCoordinator installs the production
// transaction owner for direct user repository writers.
func (r *UserRepository) SetPermissionStateDirectWriterCoordinator(
	coordinator *PermissionStateDirectWriterCoordinator,
) error {
	if r == nil || coordinator == nil {
		return fmt.Errorf("persistence: permission state direct writer coordinator is required")
	}
	r.permissionStateDirectCoordinator = coordinator
	return nil
}

func (r *UserRepository) CheckUserExistsByEmail(ctx context.Context, email string) (bool, error) {
	qr := query.From(constants.TableUser).
		AddSelectRaw("1").
		SkipID().
		Where(constants.FieldEmail, email).
		Limit(1).
		Build()

	var exists int
	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&exists)
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}
	return true, nil
}

func (r *UserRepository) CheckUserExistsByID(ctx context.Context, id string) (bool, error) {
	qr := query.From(constants.TableUser).
		AddSelectRaw("1").
		SkipID().
		Where(constants.FieldID, id).
		Limit(1).
		Build()

	var exists int
	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&exists)
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}
	return true, nil
}

func (r *UserRepository) CheckEmailConflict(ctx context.Context, email, excludeID string) (bool, error) {
	qr := query.From(constants.TableUser).
		AddSelectRaw("1").
		SkipID().
		Where(constants.FieldEmail, email).
		Where(constants.FieldID, excludeID, "!=").
		Limit(1).
		Build()

	var exists int
	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&exists)
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}
	return true, nil
}

// UpdateUser updates a user record
func (r *UserRepository) UpdateUser(ctx context.Context, userID string, updates map[string]interface{}) error {
	if len(updates) == 0 {
		return nil
	}

	// Always update last_modified_date
	updates[constants.FieldLastModifiedDate] = time.Now()

	qr := query.Update(constants.TableUser).
		Set(updates).
		Where(constants.FieldID, userID).
		Build()

	if mapHasField(updates, constants.FieldIsDeleted) || mapHasField(updates, constants.FieldUserRoleID) {
		return r.execUserMutationWithClosureRebuild(ctx, qr.SQL, qr.Params...)
	}

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

// DeleteUser deletes a user record
func (r *UserRepository) DeleteUser(ctx context.Context, userID string) error {
	const writerID = "nexuscrm-persistence/user_repository.go#UserRepository.DeleteUser"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		if r.permissionStateDirectCoordinator == nil {
			return errPermissionStateDirectWriterCoordinatorRequired
		}
		return WithGroupMembershipClosureLock(ctx, r.db, func(lockCtx context.Context) error {
			return r.permissionStateDirectCoordinator.runProjected(
				lockCtx,
				writerID,
				[]permissionStateDirectTarget{
					permissionStateDirectTargetFor(
						constants.TablePermissionSetAssignment,
						permissionStateDirectEqual(constants.FieldPermissionSetAssignment_AssigneeID),
						userID,
					),
					permissionStateDirectTargetFor(
						constants.TablePermissionSetGroupAssignment,
						permissionStateDirectEqual(constants.FieldPermissionSetGroupAssignment_AssigneeID),
						userID,
					),
				},
				r.getExecutor,
				func(txCtx context.Context) error {
					return r.DeleteUser(withPermissionStateDirectWriter(txCtx, writerID), userID)
				},
			)
		})
	}

	qr := query.Delete(constants.TableUser).
		Where(constants.FieldID, userID).
		Build()

	return r.execUserMutationWithClosureRebuild(ctx, qr.SQL, qr.Params...)
}

// FindAll retrieves all users
func (r *UserRepository) FindAll(ctx context.Context) ([]*models.User, error) {
	cols := []string{
		constants.FieldID, constants.FieldUsername, constants.FieldEmail,
		constants.FieldProfileID, constants.FieldUserRoleID, constants.FieldIsActive,
		constants.FieldCreatedDate, constants.FieldLastLoginDate,
		constants.FieldFirstName, constants.FieldLastName,
	}

	qr := query.From(constants.TableUser).
		Select(cols).
		OrderBy(constants.FieldCreatedDate, "DESC").
		Build()

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

	users := make([]*models.User, 0)
	for rows.Next() {
		var u models.User
		var createdDateRaw, lastLoginRaw []byte
		var firstName, lastName, roleID sql.NullString

		if err := rows.Scan(&u.ID, &u.Username, &u.Email, &u.ProfileID, &roleID, &u.IsActive, &createdDateRaw, &lastLoginRaw, &firstName, &lastName); err != nil {
			log.Printf("Warning: failed to scan user: %v", err)
			continue
		}

		u.FirstName = firstName.String
		u.LastName = lastName.String

		if roleID.Valid {
			rID := roleID.String
			u.UserRoleID = &rID
		}

		// Parse dates
		if len(createdDateRaw) > 0 {
			if t, err := schemautil.ParseDBTime(createdDateRaw); err == nil {
				u.CreatedDate = t
			}
		}
		if len(lastLoginRaw) > 0 {
			if t, err := schemautil.ParseDBTime(lastLoginRaw); err == nil {
				u.LastLoginDate = &t
			}
		}

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

// GetUserRoleID retrieves the role ID for a user
func (r *UserRepository) GetUserRoleID(ctx context.Context, userID string) (*string, error) {
	qr := query.From(constants.TableUser).
		SkipID().
		Select([]string{constants.FieldUser_UserRoleID}).
		Where(constants.FieldID, userID).
		Build()

	var roleID sql.NullString
	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&roleID)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	if roleID.Valid {
		return &roleID.String, nil
	}
	return nil, nil
}

// GetRoleNameByID retrieves the display name of a UserRole by its ID
func (r *UserRepository) GetRoleNameByID(ctx context.Context, roleID string) (string, error) {
	qr := query.From(constants.TableUserRole).
		SkipID().
		Select([]string{constants.FieldName}).
		Where(constants.FieldID, roleID).
		Build()

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

// GetProfileNameByID retrieves the display name of a Profile by its ID.
func (r *UserRepository) GetProfileNameByID(ctx context.Context, profileID string) (string, error) {
	qr := query.From(constants.TableProfile).
		SkipID().
		Select([]string{constants.FieldName}).
		Where(constants.FieldID, profileID).
		Build()

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

// UserWithPassword removed, using ports.UserWithPassword

// FindUserByEmailWithPassword retrieves a user and their password hash by email
func (r *UserRepository) FindUserByEmailWithPassword(ctx context.Context, email string) (*ports.UserWithPassword, error) {
	// We select specific fields needed for Auth (plus Password)
	cols := []string{
		constants.FieldID, constants.FieldUser_Username, constants.FieldUser_Email,
		constants.FieldUser_Password, constants.FieldUser_ProfileID, constants.FieldUser_UserRoleID,
		constants.FieldUser_FirstName, constants.FieldUser_LastName, constants.FieldIsActive,
		constants.FieldUser_FailedLoginAttempts, constants.FieldUser_LockedUntil,
	}

	qr := query.From(constants.TableUser).
		Select(cols).
		Where(constants.FieldUser_Email, email).
		WhereRaw(fmt.Sprintf("COALESCE(`%s`, 0) = 0", constants.FieldIsDeleted), nil).
		Limit(1).
		Build()

	var u ports.UserWithPassword
	var sysUser models.User
	u.User = &sysUser

	var password, roleID, firstName, lastName sql.NullString
	var lockedUntilRaw []byte

	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(
		&sysUser.ID,
		&sysUser.Username,
		&sysUser.Email,
		&password,
		&sysUser.ProfileID,
		&roleID,
		&firstName,
		&lastName,
		&sysUser.IsActive,
		&sysUser.FailedLoginAttempts,
		&lockedUntilRaw,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	if password.Valid {
		u.PasswordHash = password.String
	}

	// Map nullable fields to struct
	if firstName.Valid {
		sysUser.FirstName = firstName.String
	}
	if lastName.Valid {
		sysUser.LastName = lastName.String
	}
	if roleID.Valid {
		sysUser.UserRoleID = &roleID.String
	}
	if len(lockedUntilRaw) > 0 {
		if t, err := schemautil.ParseDBTime(lockedUntilRaw); err == nil {
			sysUser.LockedUntil = &t
		}
	}

	return &u, nil
}

// FindUserByIDWithPassword retrieves a user and their password by ID
func (r *UserRepository) FindUserByIDWithPassword(ctx context.Context, userID string) (*ports.UserWithPassword, error) {
	cols := []string{
		constants.FieldID, constants.FieldUser_Password,
		constants.FieldUser_FailedLoginAttempts, constants.FieldUser_LockedUntil,
	}

	qr := query.From(constants.TableUser).
		Select(cols).
		Where(constants.FieldID, userID).
		WhereRaw(fmt.Sprintf("COALESCE(`%s`, 0) = 0", constants.FieldIsDeleted), nil).
		Limit(1).
		Build()

	var u ports.UserWithPassword
	var sysUser models.User
	u.User = &sysUser
	var password sql.NullString
	var lockedUntilRaw []byte

	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(
		&sysUser.ID, &password,
		&sysUser.FailedLoginAttempts, &lockedUntilRaw,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	if password.Valid {
		u.PasswordHash = password.String
	}
	if len(lockedUntilRaw) > 0 {
		if t, err := schemautil.ParseDBTime(lockedUntilRaw); err == nil {
			sysUser.LockedUntil = &t
		}
	}
	return &u, nil
}

// UpdatePassword updates the user's password hash
func (r *UserRepository) UpdatePassword(ctx context.Context, userID, passwordHash string) error {
	qr := query.Update(constants.TableUser).
		Set(map[string]interface{}{
			constants.FieldUser_Password:    passwordHash,
			constants.FieldLastModifiedDate: time.Now(),
		}).
		Where(constants.FieldID, userID).
		Build()

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

// GetUserByID fetches basic user info. When the caller carries a pessimistic
// transaction, the read joins that transaction and locks the user row. OAuth
// credential issuance relies on this current-read lock so user deactivation
// cannot commit between the active-user check and the session insert.
func (r *UserRepository) GetUserByID(ctx context.Context, userID string) (*models.User, error) {
	cols := []string{
		constants.FieldID, constants.FieldUser_Username, constants.FieldUser_Email,
		constants.FieldUser_ProfileID, constants.FieldUser_UserRoleID,
		constants.FieldUser_FirstName, constants.FieldUser_LastName,
		constants.FieldUser_IsActive,
		constants.FieldUser_FailedLoginAttempts, constants.FieldUser_LockedUntil,
	}

	qr := query.From(constants.TableUser).
		Select(cols).
		Where(constants.FieldID, userID).
		WhereRaw(fmt.Sprintf("COALESCE(`%s`, 0) = 0", constants.FieldIsDeleted), nil).
		Limit(1).
		Build()
	querySQL := qr.SQL
	exec := r.getExecutor(ctx)
	if isPessimisticGrantTransaction(ctx) {
		if _, ok := exec.(*sql.Tx); !ok {
			return nil, fmt.Errorf("persistence: pessimistic user read requires an active transaction")
		}
		querySQL += " FOR UPDATE"
	}

	var u models.User
	var firstName, lastName sql.NullString
	var lockedUntilRaw []byte

	err := exec.QueryRowContext(ctx, querySQL, qr.Params...).Scan(
		&u.ID,
		&u.Username,
		&u.Email,
		&u.ProfileID,
		&u.UserRoleID,
		&firstName,
		&lastName,
		&u.IsActive,
		&u.FailedLoginAttempts,
		&lockedUntilRaw,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	if firstName.Valid {
		u.FirstName = firstName.String
	}
	if lastName.Valid {
		u.LastName = lastName.String
	}
	if len(lockedUntilRaw) > 0 {
		if t, err := schemautil.ParseDBTime(lockedUntilRaw); err == nil {
			u.LockedUntil = &t
		}
	}

	return &u, nil
}

// CreateUser inserts a new user record directly
func (r *UserRepository) CreateUser(ctx context.Context, user *models.User) error {
	qr := query.Insert(constants.TableUser, map[string]interface{}{
		constants.FieldID:               user.ID,
		constants.FieldUsername:         user.Username,
		constants.FieldEmail:            user.Email,
		constants.FieldPassword:         user.Password,
		constants.FieldFirstName:        user.FirstName,
		constants.FieldLastName:         user.LastName,
		constants.FieldProfileID:        user.ProfileID,
		constants.FieldUserRoleID:       user.UserRoleID,
		constants.FieldIsActive:         user.IsActive,
		constants.FieldCreatedDate:      user.CreatedDate,
		constants.FieldLastModifiedDate: time.Now(),
	}).Build()

	return r.execUserMutationWithClosureRebuild(ctx, qr.SQL, qr.Params...)
}

func (r *UserRepository) execUserMutationWithClosureRebuild(ctx context.Context, statement string, args ...interface{}) error {
	const deleteWriterID = "nexuscrm-persistence/user_repository.go#UserRepository.DeleteUser"
	activeDirectWriters, _ := ctx.Value(permissionStateDirectWriterContextKey{}).(map[string]struct{})
	_, deletingThroughCoordinator := activeDirectWriters[deleteWriterID]
	if deletingThroughCoordinator &&
		r.permissionStateDirectCoordinator != nil &&
		r.permissionStateDirectCoordinator.transactions.IsPessimisticTx(ctx) {
		exec := r.getExecutor(ctx)
		if _, err := exec.ExecContext(ctx, statement, args...); err != nil {
			return err
		}
		return RebuildGroupMembershipClosure(ctx, exec)
	}
	return withGroupMembershipClosureLockedTx(ctx, r.db, func(txCtx context.Context, tx *sql.Tx) error {
		if _, err := tx.ExecContext(txCtx, statement, args...); err != nil {
			return err
		}
		return RebuildGroupMembershipClosure(txCtx, tx)
	})
}

func (r *UserRepository) getExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

func (r *UserRepository) permissionStateDirectWriterShouldRun(
	ctx context.Context,
	writerID string,
) bool {
	if r == nil {
		return false
	}
	if permissionStateDirectWriterIsActive(ctx, writerID) {
		return false
	}
	if r.permissionStateDirectCoordinator == nil {
		return true
	}
	return r.permissionStateDirectCoordinator.shouldRun(ctx, writerID)
}

func mapHasField(data map[string]interface{}, fieldName string) bool {
	for key := range data {
		if strings.EqualFold(key, fieldName) {
			return true
		}
	}
	return false
}

// GetUserIDsByRoleIDs returns all user IDs whose UserRoleID is in the given list.
func (r *UserRepository) GetUserIDsByRoleIDs(ctx context.Context, roleIDs []string) ([]string, error) {
	if len(roleIDs) == 0 {
		return []string{}, nil
	}

	values := make([]interface{}, len(roleIDs))
	for i, id := range roleIDs {
		values[i] = id
	}

	qr := query.From(constants.TableUser).
		Select([]string{constants.FieldID}).
		WhereIn(constants.FieldUser_UserRoleID, values).
		Build()

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

	ids := make([]string, 0)
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

// ResetFailedLoginAttempts resets the failed login counter and clears any lockout for a user.
func (r *UserRepository) ResetFailedLoginAttempts(ctx context.Context, userID string) error {
	sql := "UPDATE `User` SET `FailedLoginAttempts` = 0, `LockedUntil` = NULL WHERE `Id` = ?"
	_, err := r.db.ExecContext(ctx, sql, userID)
	return err
}

// IncrementFailedAndLockIfNeeded atomically increments the failed login counter
// and locks the account if the threshold is reached.
func (r *UserRepository) IncrementFailedAndLockIfNeeded(ctx context.Context, userID string, maxAttempts int, lockDuration time.Duration) (locked bool, err error) {
	lockUntil := time.Now().Add(lockDuration)
	sqlStr := "UPDATE `User` SET `FailedLoginAttempts` = COALESCE(`FailedLoginAttempts`, 0) + 1, " +
		"`LockedUntil` = CASE WHEN COALESCE(`FailedLoginAttempts`, 0) + 1 >= ? THEN ? ELSE `LockedUntil` END " +
		"WHERE `Id` = ?"
	_, err = r.db.ExecContext(ctx, sqlStr, maxAttempts, lockUntil, userID)
	if err != nil {
		return false, err
	}
	// Read back actual count
	var count int
	r.db.QueryRowContext(ctx, "SELECT COALESCE(`FailedLoginAttempts`, 0) FROM `User` WHERE `Id` = ?", userID).Scan(&count)
	return count >= maxAttempts, nil
}

// GetFirstUserIDByProfile returns the first active user ID with the given profile ID.
// Used by OAuth client_credentials flow to resolve the service account.
func (r *UserRepository) GetFirstUserIDByProfile(ctx context.Context, profileID string) (string, error) {
	qr := query.From(constants.TableUser).
		Select([]string{constants.FieldID}).
		Where(constants.FieldUser_ProfileID, profileID).
		Where(constants.FieldUser_IsActive, true).
		OrderBy(constants.FieldCreatedDate, "ASC").
		Limit(1).
		Build()

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