package test

import (
	"context"
	"fmt"
	"testing"

	"github.com/gogf/gf/v2/frame/g"
	"github.com/samber/lo"
)

type DuplUser struct {
	UserName string `json:"user_name"`
	Phone    string `json:"phone"`
	Openid   string `json:"openid"`
	ID       uint   `json:"id"`
}

// 查询具有相同手机号的用户
func TestDuplUser(t *testing.T) {
	// SELECT
	//
	//	id,
	//	user_name,
	//	phone,
	//	openid
	//
	// FROM
	//
	//	bus_construction_user
	//
	// WHERE
	//
	//	phone IN (
	//	  SELECT
	//	    phone
	//	  FROM
	//	    bus_construction_user
	//	  where
	//	    deleted_at is nullAND phone <> ''
	//	  GROUP BY
	//	    phone
	//	  HAVING
	//	    COUNT(phone) > 1
	//	)
	//	AND phone IS NOT NULL AND phone <> ''
	//
	// ORDER BY phone;
	var duplUsers []DuplUser
	// 查询具有相同手机号的用户
	query, _ := g.DB().Query(context.Background(), "SELECT id, user_name, phone, openid FROM bus_construction_user WHERE phone IN (SELECT phone FROM bus_construction_user WHERE deleted_at IS NULL AND phone <> '' GROUP BY phone HAVING COUNT(phone) > 1) AND phone IS NOT NULL AND phone <> '' and deleted_at is null ORDER BY phone")
	if err := query.Structs(&duplUsers); err != nil {
		t.Fatal(err)
	}

	var errOpenids []DuplUser
	duplUsers = lo.FilterMap(duplUsers, func(v DuplUser, _ int) (DuplUser, bool) {
		// 如果 openid 不包含任何字母，则认为是无效的 openid
		if !hasLetter(v.Openid) {
			errOpenids = append(errOpenids, v)
			return v, false
		}

		return v, true
	})

	for _, v := range duplUsers {
		fmt.Printf("User: %#v\n", v)
	}

	fmt.Println("错误的OpenID")
	for _, v := range errOpenids {
		fmt.Printf("User: %#v\n", v)
	}
}

// 判断字符串是否包含字母
func hasLetter(s string) bool {
	for _, r := range s {
		if (r >= 'a' && r <= 'z') || (r >= 'A' && r <= 'Z') {
			return true
		}
	}
	return false
}