package persistence

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

	"github.com/nexuscrm/backend/internal/domain/ports"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

type UserRepository struct {
	db *sql.DB
}

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

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

	_, 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 {
	qr := query.Delete(constants.TableUser).
		Where(constants.FieldID, userID).
		Build()

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

// 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 := ParseDBTime(createdDateRaw); err == nil {
				u.CreatedDate = t
			}
		}
		if len(lastLoginRaw) > 0 {
			if t, err := 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).
		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 := 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).
		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 := 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
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_FirstName, constants.FieldUser_LastName,
		constants.FieldUser_IsActive,
		constants.FieldUser_FailedLoginAttempts, constants.FieldUser_LockedUntil,
	}

	qr := query.From(constants.TableUser).
		Select(cols).
		Where(constants.FieldID, userID).
		Limit(1).
		Build()

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

	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(
		&u.ID,
		&u.Username,
		&u.Email,
		&u.ProfileID,
		&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 := 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()

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

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