package repository

import (
	"context"
	"errors"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	"backend/internal/models"
)

type ClientUserRepository struct {
	db *pgxpool.Pool
}

func NewClientUserRepository(db *pgxpool.Pool) *ClientUserRepository {
	return &ClientUserRepository{db: db}
}

// FindByID finds a client user by ID
func (r *ClientUserRepository) FindByID(ctx context.Context, id uuid.UUID) (*models.ClientUser, error) {
	query := `
		SELECT id, client_id, first_name, last_name, email, phone, date_of_birth, gender,
		       address, city, state, country, postal_code, username, password, role, permissions,
		       status, is_active, is_verified, email_verified_at, phone_verified_at,
		       avatar_url, bio, preferred_language, timezone,
		       last_login, last_login_ip, last_activity, login_count, failed_login_attempts,
		       locked_until, created_by, updated_by, created_at, updated_at, deleted_at
		FROM client_users
		WHERE id = $1 AND deleted_at IS NULL
	`

	var user models.ClientUser
	err := r.db.QueryRow(ctx, query, id).Scan(
		&user.ID, &user.ClientID, &user.FirstName, &user.LastName, &user.Email, &user.Phone,
		&user.DateOfBirth, &user.Gender, &user.Address, &user.City, &user.State, &user.Country,
		&user.PostalCode, &user.Username, &user.Password, &user.Role, &user.Permissions,
		&user.Status, &user.IsActive, &user.IsVerified, &user.EmailVerifiedAt, &user.PhoneVerifiedAt,
		&user.AvatarURL, &user.Bio, &user.PreferredLanguage, &user.Timezone,
		&user.LastLogin, &user.LastLoginIP, &user.LastActivity, &user.LoginCount,
		&user.FailedLoginAttempts, &user.LockedUntil, &user.CreatedBy, &user.UpdatedBy,
		&user.CreatedAt, &user.UpdatedAt, &user.DeletedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, errors.New("client user not found")
		}
		return nil, err
	}

	return &user, nil
}

// FindByUsername finds a client user by username
func (r *ClientUserRepository) FindByUsername(ctx context.Context, username string) (*models.ClientUser, error) {
	query := `
		SELECT id, client_id, first_name, last_name, email, phone, date_of_birth, gender,
		       address, city, state, country, postal_code, username, password, role, permissions,
		       status, is_active, is_verified, email_verified_at, phone_verified_at,
		       avatar_url, bio, preferred_language, timezone,
		       last_login, last_login_ip, last_activity, login_count, failed_login_attempts,
		       locked_until, created_by, updated_by, created_at, updated_at, deleted_at
		FROM client_users
		WHERE username = $1 AND deleted_at IS NULL
	`

	var user models.ClientUser
	err := r.db.QueryRow(ctx, query, username).Scan(
		&user.ID, &user.ClientID, &user.FirstName, &user.LastName, &user.Email, &user.Phone,
		&user.DateOfBirth, &user.Gender, &user.Address, &user.City, &user.State, &user.Country,
		&user.PostalCode, &user.Username, &user.Password, &user.Role, &user.Permissions,
		&user.Status, &user.IsActive, &user.IsVerified, &user.EmailVerifiedAt, &user.PhoneVerifiedAt,
		&user.AvatarURL, &user.Bio, &user.PreferredLanguage, &user.Timezone,
		&user.LastLogin, &user.LastLoginIP, &user.LastActivity, &user.LoginCount,
		&user.FailedLoginAttempts, &user.LockedUntil, &user.CreatedBy, &user.UpdatedBy,
		&user.CreatedAt, &user.UpdatedAt, &user.DeletedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, errors.New("client user not found")
		}
		return nil, err
	}

	return &user, nil
}

// FindByEmail finds a client user by email
func (r *ClientUserRepository) FindByEmail(ctx context.Context, email string) (*models.ClientUser, error) {
	query := `
		SELECT id, client_id, first_name, last_name, email, phone, date_of_birth, gender,
		       address, city, state, country, postal_code, username, password, role, permissions,
		       status, is_active, is_verified, email_verified_at, phone_verified_at,
		       avatar_url, bio, preferred_language, timezone,
		       last_login, last_login_ip, last_activity, login_count, failed_login_attempts,
		       locked_until, created_by, updated_by, created_at, updated_at, deleted_at
		FROM client_users
		WHERE email = $1 AND deleted_at IS NULL
	`

	var user models.ClientUser
	err := r.db.QueryRow(ctx, query, email).Scan(
		&user.ID, &user.ClientID, &user.FirstName, &user.LastName, &user.Email, &user.Phone,
		&user.DateOfBirth, &user.Gender, &user.Address, &user.City, &user.State, &user.Country,
		&user.PostalCode, &user.Username, &user.Password, &user.Role, &user.Permissions,
		&user.Status, &user.IsActive, &user.IsVerified, &user.EmailVerifiedAt, &user.PhoneVerifiedAt,
		&user.AvatarURL, &user.Bio, &user.PreferredLanguage, &user.Timezone,
		&user.LastLogin, &user.LastLoginIP, &user.LastActivity, &user.LoginCount,
		&user.FailedLoginAttempts, &user.LockedUntil, &user.CreatedBy, &user.UpdatedBy,
		&user.CreatedAt, &user.UpdatedAt, &user.DeletedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, errors.New("client user not found")
		}
		return nil, err
	}

	return &user, nil
}

// FindByClientID finds all client users for a specific client
func (r *ClientUserRepository) FindByClientID(ctx context.Context, clientID string, limit, offset int) ([]*models.ClientUserInfo, error) {
	query := `
		SELECT id, client_id, first_name, last_name, email, phone, username, role,
		       status, is_active, avatar_url, last_login, created_at
		FROM client_users
		WHERE client_id = $1 AND deleted_at IS NULL
		ORDER BY created_at DESC
		LIMIT $2 OFFSET $3
	`

	rows, err := r.db.Query(ctx, query, clientID, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []*models.ClientUserInfo
	for rows.Next() {
		var user models.ClientUserInfo
		err := rows.Scan(
			&user.ID, &user.ClientID, &user.FirstName, &user.LastName, &user.Email,
			&user.Phone, &user.Username, &user.Role, &user.Status, &user.IsActive,
			&user.AvatarURL, &user.LastLogin, &user.CreatedAt,
		)
		if err != nil {
			return nil, err
		}
		users = append(users, &user)
	}

	return users, rows.Err()
}

// Create creates a new client user
func (r *ClientUserRepository) Create(ctx context.Context, user *models.ClientUser) error {
	query := `
		INSERT INTO client_users (
			client_id, first_name, last_name, email, phone, date_of_birth, gender,
			address, city, state, country, postal_code, username, password, role, permissions,
			status, is_active, is_verified, avatar_url, bio, preferred_language, timezone,
			created_by
		) VALUES (
			$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16,
			$17, $18, $19, $20, $21, $22, $23, $24
		) RETURNING id, created_at, updated_at
	`

	err := r.db.QueryRow(
		ctx, query,
		user.ClientID, user.FirstName, user.LastName, user.Email, user.Phone,
		user.DateOfBirth, user.Gender, user.Address, user.City, user.State,
		user.Country, user.PostalCode, user.Username, user.Password, user.Role,
		user.Permissions, user.Status, user.IsActive, user.IsVerified,
		user.AvatarURL, user.Bio, user.PreferredLanguage, user.Timezone,
		user.CreatedBy,
	).Scan(&user.ID, &user.CreatedAt, &user.UpdatedAt)

	return err
}

// Update updates an existing client user
func (r *ClientUserRepository) Update(ctx context.Context, user *models.ClientUser) error {
	query := `
		UPDATE client_users
		SET first_name = COALESCE($2, first_name),
		    last_name = COALESCE($3, last_name),
		    email = COALESCE($4, email),
		    phone = COALESCE($5, phone),
		    date_of_birth = COALESCE($6, date_of_birth),
		    gender = COALESCE($7, gender),
		    address = COALESCE($8, address),
		    city = COALESCE($9, city),
		    state = COALESCE($10, state),
		    country = COALESCE($11, country),
		    postal_code = COALESCE($12, postal_code),
		    username = COALESCE($13, username),
		    role = COALESCE($14, role),
		    permissions = COALESCE($15, permissions),
		    status = COALESCE($16, status),
		    is_active = COALESCE($17, is_active),
		    avatar_url = COALESCE($18, avatar_url),
		    bio = COALESCE($19, bio),
		    updated_by = $20,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = $1 AND deleted_at IS NULL
		RETURNING updated_at
	`

	err := r.db.QueryRow(
		ctx, query,
		user.ID, user.FirstName, user.LastName, user.Email, user.Phone,
		user.DateOfBirth, user.Gender, user.Address, user.City, user.State,
		user.Country, user.PostalCode, user.Username, user.Role, user.Permissions,
		user.Status, &user.IsActive, user.AvatarURL, user.Bio, user.UpdatedBy,
	).Scan(&user.UpdatedAt)

	return err
}

// UpdatePassword updates the user's password
func (r *ClientUserRepository) UpdatePassword(ctx context.Context, userID uuid.UUID, hashedPassword string) error {
	query := `
		UPDATE client_users
		SET password = $2, updated_at = CURRENT_TIMESTAMP
		WHERE id = $1 AND deleted_at IS NULL
	`

	_, err := r.db.Exec(ctx, query, userID, hashedPassword)
	return err
}

// UpdateLastLogin updates the last login information
func (r *ClientUserRepository) UpdateLastLogin(ctx context.Context, userID uuid.UUID, ipAddress string) error {
	now := time.Now()
	query := `
		UPDATE client_users
		SET last_login = $2,
		    last_login_ip = $3,
		    last_activity = $2,
		    login_count = login_count + 1,
		    failed_login_attempts = 0,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = $1
	`

	_, err := r.db.Exec(ctx, query, userID, now, ipAddress)
	return err
}

// IncrementFailedLoginAttempts increments failed login attempts
func (r *ClientUserRepository) IncrementFailedLoginAttempts(ctx context.Context, userID uuid.UUID) error {
	query := `
		UPDATE client_users
		SET failed_login_attempts = failed_login_attempts + 1,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = $1 AND deleted_at IS NULL
	`

	_, err := r.db.Exec(ctx, query, userID)
	return err
}

// LockUser locks a user account
func (r *ClientUserRepository) LockUser(ctx context.Context, userID uuid.UUID, until time.Time) error {
	query := `
		UPDATE client_users
		SET locked_until = $2,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = $1 AND deleted_at IS NULL
	`

	_, err := r.db.Exec(ctx, query, userID, until)
	return err
}

// SoftDelete soft deletes a client user
func (r *ClientUserRepository) SoftDelete(ctx context.Context, userID uuid.UUID) error {
	query := `
		UPDATE client_users
		SET deleted_at = CURRENT_TIMESTAMP,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = $1 AND deleted_at IS NULL
	`

	_, err := r.db.Exec(ctx, query, userID)
	return err
}

// CountByClientID counts the number of active users for a client
func (r *ClientUserRepository) CountByClientID(ctx context.Context, clientID string) (int, error) {
	query := `
		SELECT COUNT(*) FROM client_users
		WHERE client_id = $1 AND deleted_at IS NULL
	`

	var count int
	err := r.db.QueryRow(ctx, query, clientID).Scan(&count)
	return count, err
}