package datamysql

import (
	"database/sql"
	"fmt"
	"time"

	"bitbucket.org/nemt/nemt-portal-api/domain/entity"
	"bitbucket.org/nemt/nemt-portal-api/infra/errors"
	"github.com/go-sql-driver/mysql"
	uuid "github.com/satori/go.uuid"
)

// userRepo maps methods to database
type userRepo struct {
	conn executor
}

func newUserRepo(conn executor) *userRepo {
	return &userRepo{
		conn: conn,
	}
}

func (c *userRepo) GetByUUID(uuid string, profile string) (entity.User, error) {
	params := make([]interface{}, 0)
	params = append(params, uuid)
	finalQuery := c.getQuery() + " AND a.user_uuid = ? "
	if profile == "US" {
		finalQuery += " AND e.key = ? "
		params = append(params, profile)
	}

	user, err := c.parseSet(c.conn.Query(finalQuery, params...))
	if err != nil {
		return entity.User{}, err
	}

	if len(user) > 0 {
		retVal := user[0]
		retVal.Contacts, err = c.GetContacts(retVal.ID)
		if err != nil {
			return entity.User{}, err
		}

		retVal.Addresses = nil
		retVal.Addresses, err = c.getAddressByUserID(retVal.ID)
		if err != nil {
			return entity.User{}, err
		}
		return retVal, nil
	} else {
		return entity.User{}, nil
	}
}

func (c *userRepo) GetContacts(userID int64) ([]entity.ContactInfo, error) {
	const (
		query = `
		SELECT 
								a.contact_id, 
								a.contact, 
								a.user_id,
								b.contact_type_id, 
								b.key               contact_type_key, 
								b.name              contact_type_name 
			FROM      tab_contact         a 
		INNER JOIN tab_contact_type    b 
				ON      a.contact_type_id = b.contact_type_id 
		WHERE      a.user_id         = ?;
		`
	)

	rows, err := c.conn.Query(query, userID)
	if err != nil {
		return nil, err
	}

	retVal := make([]entity.ContactInfo, 0)
	for rows.Next() {
		contact := entity.ContactInfo{}
		err = rows.Scan(&contact.ID, &contact.Value, &contact.UserID, &contact.Type.ID, &contact.Type.Key, &contact.Type.Value)
		if err != nil {
			return nil, err
		}
		retVal = append(retVal, contact)
	}

	return retVal, nil
}

func (c *userRepo) GetUsersByProfile(profile string) ([]entity.User, error) {
	query := c.getQuery()
	if profile == "" {
		query += "AND e.key <> 'US' "
		return c.parseSet(c.conn.Query(query))
	} else {
		return c.parseSet(c.conn.Query(query+"AND e.key = ? AND a.active = 1 ", profile))
	}
}

func (c *userRepo) parseSet(rows *sql.Rows, err error) ([]entity.User, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.User, 0)
	var lastUser entity.User
	for rows.Next() {
		entity, err := c.parseEntity(rows)
		if err != nil {
			return nil, errors.Wrap(err)
		}

		if entity.UUID == lastUser.UUID {
			lastUser.Profiles = append(lastUser.Profiles, entity.Profiles...)
		} else {
			if lastUser.UUID != "" {
				result = append(result, lastUser)
			}
			lastUser = entity
		}
	}
	result = append(result, lastUser)

	return result, nil
}

func (c *userRepo) GetContactType() (retVal []entity.ContactType, err error) {
	const (
		query = `
				SELECT 
								a.contact_type_id,
								a.key,
								a.name
					FROM tab_contact_type a
					WHERE a.visible = 1 `
	)

	rows, err := c.conn.Query(query)
	if err != nil {
		return nil, err
	}

	retVal = make([]entity.ContactType, 0)
	for rows.Next() {
		var contactType entity.ContactType
		err = rows.Scan(&contactType.ID, &contactType.Key, &contactType.Value)
		if err != nil {
			return nil, err
		}

		retVal = append(retVal, contactType)
	}

	return retVal, nil
}

// parseEntity parses a result to an entity
func (c *userRepo) parseEntity(row scanner) (retVal entity.User, err error) {
	var profile entity.Profile
	var birthDate mysql.NullTime

	var homeAddress entity.Address
	var workAddress entity.Address

	err = row.Scan(&retVal.ID, &retVal.UUID, &retVal.Name, &retVal.Member, &birthDate, &retVal.LoginID, &retVal.LoginUUID, &retVal.Email, &retVal.PhoneNumber, &retVal.LoginKey, &retVal.Gender, &retVal.Active, &retVal.Created, &retVal.Updated, &profile.ID, &profile.Name, &profile.Key, &profile.Active, &profile.Blocked, &profile.Suspended, &profile.Created, &profile.Updated, &profile.Organization.ID, &profile.Organization.UUID, &profile.Organization.Type.ID, &profile.Organization.Type.Name, &profile.Organization.Type.Key, &profile.Organization.Type.Description, &profile.Organization.Name, &profile.Organization.Description, &profile.Organization.ReferenceID, &profile.Organization.ParentID, &profile.Organization.Main,
		&homeAddress.ID, &homeAddress.UUID, &homeAddress.AddressType.ID, &homeAddress.AddressType.Key, &homeAddress.AddressType.Name, &homeAddress.Name, &homeAddress.Address, &homeAddress.Latitude, &homeAddress.Longitude,
		&workAddress.ID, &workAddress.UUID, &workAddress.AddressType.ID, &workAddress.AddressType.Key, &workAddress.AddressType.Name, &workAddress.Name, &workAddress.Address, &workAddress.Latitude, &workAddress.Longitude)
	if err != nil {
		if err != sql.ErrNoRows {
			return retVal, errors.Wrap(err)
		}
	}

	if birthDate.Valid {
		retVal.BirthDate = birthDate.Time
	}

	retVal.Profiles = append(retVal.Profiles, profile)
	if homeAddress.ID > 0 {
		retVal.Addresses = append(retVal.Addresses, homeAddress)
	}

	if workAddress.ID > 0 {
		retVal.Addresses = append(retVal.Addresses, workAddress)
	}

	return retVal, errors.Wrap(err)
}

func (c *userRepo) FullLogin(loginType string, key string, pass string, profile string) (user entity.User, err error) {
	return c.login(loginType, key, pass, profile)
}

//Login return a user based on email and password with the default `User` profile
func (c *userRepo) Login(email string, pass string) (user entity.User, err error) {
	return c.FullLogin("email", email, pass, "US")
}

func (c *userRepo) getQuery() string {
	const (
		query = `SELECT 
								a.user_id,
								a.user_uuid,
								a.name,
								IFNULL(a.member, '') member,
								a.birth_date,
								b.login_id,
								b.login_uuid,
								IFNULL(b.email, '') email,
								IFNULL(b.phone_number, '') phone_number,
								b.key login_key,
								IFNULL(a.gender, 'U') gender,
								(IFNULL(a.active, b'0') = b'1') active,
								a.create_time,
								a.update_time,
								e.profile_id,
								e.name profile_name,
								e.key profile_key,
								(IFNULL(c.active, b'0') = b'1') profile_active, 
								(IFNULL(c.blocked, b'0') = b'1') profile_blocked,
								(IFNULL(c.suspended, b'0') = b'1') profile_suspended,
								c.create_date profile_createat,
								c.update_date profile_updateat,
								IFNULL(f.organization_id, 0) organization_id,
								IFNULL(f.organization_uuid, '') organization_uuid,
								IFNULL(f.organization_type_id, 0) organization_type_id,
								IFNULL(g.organization_type, '') organization_type,
								IFNULL(g.organization_type_key, '') organization_type_key,
								IFNULL(g.description, '') organization_type_desc,
								IFNULL(f.organization_name, '') organization_name,
								IFNULL(f.organization_description, '') organization_desc,
								IFNULL(f.organization_reference_id, 0) organization_reference_id,
								IFNULL(f.organization_parent_id, 0) organization_parent_id,
								(IFNULL(f.main_organization, b'0') = b'1') main_organization,
                                IFNULL(h.address_id, 0) home_address_id,
                                IFNULL(h.address_uuid, '') home_address_uuid,
                                IFNULL(h.address_type_id, 0) home_address_type_id,
                                IFNULL(h.address_type_key, '') home_address_type_key,
                                IFNULL(h.address_type_name, '') home_address_type_name,
                                IFNULL(h.name, '') home_name,
                                IFNULL(h.address, '') home_address,
                                IFNULL(h.lat, 0) home_lat,
                                IFNULL(h.long, 0) home_long,
                                IFNULL(i.address_id, 0) work_address_id,
                                IFNULL(i.address_uuid, '') work_address_uuid,
                                IFNULL(i.address_type_id, 0) work_address_type_id,
                                IFNULL(i.address_type_key, '') work_address_type_key,
                                IFNULL(i.address_type_name, '') work_address_type_name,
                                IFNULL(i.name, '') work_name,
                                IFNULL(i.address, '') work_address,
                                IFNULL(i.lat, 0) work_lat,
                                IFNULL(i.long, 0) work_long
						FROM
								tab_user a
										INNER JOIN
								tab_login b ON a.user_id = b.user_id
										INNER JOIN
								tab_profile_login c ON c.login_id = b.login_id
										INNER JOIN
								tab_profile e ON e.profile_id = c.profile_id
										LEFT JOIN
								tab_organization f ON f.organization_id = IFNULL(c.organization_id, 0)
										LEFT JOIN
								tab_organization_type g ON IFNULL(g.organization_type_id, 0) = IFNULL(f.organization_type_id, 0)
                                        LEFT JOIN
                                viw_address_home h ON a.user_id = h.user_id
                                        LEFT JOIN
                                viw_address_work i ON a.user_id = i.user_id
						WHERE
								a.active = b.active
										AND b.active = c.active
										AND c.active = e.active `
	)

	return query
}

func (c *userRepo) login(loginType string, key string, pass string, profile string) (user entity.User, err error) {
	if loginType == "" {
		return user, errors.NewNullArgumentError("loginType")
	}

	if loginType != "phone_number" && loginType != "email" {
		return user, errors.NewValidationError("loginType", "invalid")
	}

	query := fmt.Sprintf(c.getQuery()+`
										AND a.active = 1
										AND b.%s = ?
										AND b.password = sha2(?, 512) `, loginType)

	users, err := c.parseSet(c.conn.Query(query, key, pass))
	if err != nil {
		return user, errors.Wrap(err)
	}

	return users[0], nil
}

func (c *userRepo) Create(user entity.User) (retVal entity.User, err error) {
	retVal = user

	if retVal.Member != "" {
		retVal, err = c.getUserByMember(retVal)
		if err != nil {
			return retVal, err
		}
	}

	if retVal.ID == 0 || retVal.Member == "" {
		userID, guid, err := c.createUser(user)
		if err != nil {
			return retVal, err
		}
		retVal.UUID = guid
		retVal.ID = userID
	}

	retVal, err = c.getUserLogin(retVal)
	if err != nil {
		return retVal, err
	}

	if retVal.LoginID == 0 {
		loginID, loginGUID, err := c.createLogin(retVal)
		if err != nil {
			return retVal, err
		}
		retVal.LoginUUID = loginGUID
		retVal.LoginID = loginID
	}

	for _, p := range user.Profiles {
		companyProfileID, err := c.getCompanyProfile(p.Key)
		if err != nil {
			return retVal, err
		}

		if len(user.Organizations) > 0 {
			for _, o := range user.Organizations {
				err = c.addProfileToUser(retVal.LoginID, companyProfileID, o)
				if err != nil {
					return retVal, err
				}
			}
		} else {
			err = c.addProfileToUser(retVal.LoginID, companyProfileID, entity.Organization{})
			if err != nil {
				return retVal, err
			}
		}
	}

	if retVal.Email != "" {
		contact := entity.ContactInfo{
			Type:   entity.ContactType{Key: "email"},
			Value:  retVal.Email,
			UserID: retVal.ID,
		}

		contact, err = c.addContactInfo(contact)
		if err != nil {
			return retVal, err
		}
	}

	if retVal.PhoneNumber != "" {
		contact := entity.ContactInfo{
			Type:   entity.ContactType{Key: "phone"},
			Value:  retVal.PhoneNumber,
			UserID: retVal.ID,
		}

		contact, err = c.addContactInfo(contact)
		if err != nil {
			return retVal, err
		}
	}

	return c.GetByUUID(retVal.UUID, "")
}

func (c *userRepo) SaveContact(contact entity.ContactInfo) (entity.ContactInfo, error) {
	return c.addContactInfo(contact)
}

func (c *userRepo) RemoveContact(contact entity.ContactInfo) (entity.ContactInfo, error) {
	const (
		query = `INSERT INTO tab_contact(contact_type_id, user_id, contact)
							SELECT a.contact_type_id, ? user_id, ? contact
							FROM 
										tab_contact_type a 
							LEFT JOIN 	tab_contact b 
								ON 		a.contact_type_id = b.contact_type_id 
							AND 		b.user_id = ? 
							AND 		b.contact = ? 
							WHERE 		a.key = ?
							AND       b.contact_id IS NULL;`
	)

	result, err := c.conn.Exec(query, contact.UserID, contact.Value, contact.UserID, contact.Value, contact.Type.Key)
	if err != nil {
		return contact, err
	}

	retVal := contact
	retVal.ID, err = result.LastInsertId()
	if err != nil {
		return contact, err
	}

	return retVal, nil
}

func (c *userRepo) addContactInfo(contact entity.ContactInfo) (entity.ContactInfo, error) {
	const (
		query = `INSERT INTO tab_contact(contact_type_id, user_id, contact)
							SELECT a.contact_type_id, ? user_id, ? contact
							FROM 
										tab_contact_type a 
							LEFT JOIN 	tab_contact b 
								ON 		a.contact_type_id = b.contact_type_id 
							AND 		b.user_id = ? 
							AND 		b.contact = ? 
							WHERE 		a.key = ?
							AND       b.contact_id IS NULL;`
	)

	result, err := c.conn.Exec(query, contact.UserID, contact.Value, contact.UserID, contact.Value, contact.Type.Key)
	if err != nil {
		return contact, err
	}

	retVal := contact
	retVal.ID, err = result.LastInsertId()
	if err != nil {
		return contact, err
	}

	return retVal, nil
}

func (c *userRepo) getUserByLoginAndProfile(loginID int64) (user entity.User, err error) {
	finalQuery := c.getQuery() + " AND a.active = 1 AND b.login_Id = ? "

	users, err := c.parseSet(c.conn.Query(finalQuery, loginID))
	if err != nil {
		return user, errors.Wrap(err)
	}

	return users[0], nil
}

func (c *userRepo) addProfileToUser(loginID int64, profileID int64, organization entity.Organization) error {
	const query = "INSERT INTO tab_profile_login(profile_id, login_id, active, blocked, suspended, organization_id) VALUES(?, ?, 1, 0, 0, ?) ON DUPLICATE KEY UPDATE profile_id = ?, login_id = ?, organization_id = ?;"
	const checkQuery = "SELECT COUNT(1) has_profile FROM tab_profile_login a WHERE a.login_id = ? AND a.profile_id = ? AND IFNULL(a.organization_id, 0) = ?;"

	var total int64
	row := c.conn.QueryRow(checkQuery, loginID, profileID, organization.ID)
	err := row.Scan(&total)
	if err != nil && err != sql.ErrNoRows {
		return err
	}

	if total < 1 {
		_, err := c.conn.Exec(query, profileID, loginID, organization.ID, profileID, loginID, toNullInt64(organization.ID))
		if err != nil {
			return err
		}
	}

	return nil
}

func (c *userRepo) getUserByMember(u entity.User) (user entity.User, err error) {
	user = u
	users, err := c.parseSet(c.conn.Query(c.getQuery()+" AND a.active = 1 AND e.key = 'US' AND IFNULL(a.member, '') = ?;", u.Member))
	if err != nil {
		return user, errors.Wrap(err)
	}

	if users[0].ID != 0 {
		user = users[0]
	}
	return user, nil
}

func (c *userRepo) getCompanyProfile(profile string) (int64, error) {
	const (
		query = "SELECT a.profile_id FROM tab_profile a WHERE a.key = ?;"
	)
	var companyProfileID int64
	row := c.conn.QueryRow(query, profile)
	err := row.Scan(&companyProfileID)
	if err != nil {
		return 0, err
	}

	return companyProfileID, nil
}

func (c *userRepo) getUserLogin(u entity.User) (user entity.User, err error) {
	const (
		query = "SELECT login_id, login_uuid, user_id, IFNULL(phone_number, '') phone_number, IFNULL(email, '') email, `key`, `password` FROM tab_login WHERE user_id = ?"
	)

	row := c.conn.QueryRow(query, user.Member)
	err = row.Scan(&user.LoginID, &user.LoginUUID, &user.ID, &user.PhoneNumber, &user.Email, &user.LoginKey, &user.Pass)
	if err != nil {
		if err == sql.ErrNoRows {
			return u, nil
		} else {
			return user, err
		}
	}

	return user, nil
}

func (c *userRepo) createLogin(user entity.User) (int64, string, error) {
	const (
		query = "INSERT INTO tab_login (login_uuid, user_id, phone_number, email, `key`, `password`) VALUES(?, ?, ?, ?, ?, sha2(?, 512)) ON DUPLICATE KEY UPDATE login_uuid = ?, user_id = ?, phone_number = ?, email = ?, `key` = ?, `password` = sha2(?, 512);"
	)
	guid, _ := uuid.NewV4()
	key, _ := uuid.NewV4()

	result, err := c.conn.Exec(query, guid.String(), user.ID, toNullString(user.PhoneNumber), toNullString(user.Email), key.String(), user.Pass, guid.String(), user.ID, user.PhoneNumber, user.Email, key.String(), user.Pass)
	if err != nil {
		return 0, "", err
	}

	loginID, err := result.LastInsertId()
	if err != nil {
		return 0, "", err
	}

	return loginID, guid.String(), nil
}

func (c *userRepo) createUser(user entity.User) (int64, string, error) {
	const (
		query = "INSERT INTO tab_user(user_uuid, `name`, member, birth_date, gender) VALUES(?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE user_uuid = ?, `name` = ?, member = ?, birth_date = ?, gender = ?;"
	)
	guid, _ := uuid.NewV4()

	result, err := c.conn.Exec(query, guid.String(), user.Name, toNullString(user.Member), toNullTime(user.BirthDate), toNullString(user.Gender), guid.String(), user.Name, toNullString(user.Member), toNullTime(user.BirthDate), toNullString(user.Gender))
	if err != nil {
		return 0, "", err
	}

	userID, err := result.LastInsertId()
	if err != nil {
		return 0, "", err
	}

	return userID, guid.String(), nil
}

func (c *userRepo) RemoveAddress(addressUUID string) error {
	const (
		query = "UPDATE tab_address SET active = 0 WHERE address_uuid = ?"
	)

	_, err := c.conn.Exec(query, addressUUID)
	return err
}

func (c *userRepo) SaveAddress(address entity.Address) (entity.Address, error) {
	const (
		query = "INSERT INTO tab_address(address_uuid,address_type_id,address_origin_id,user_id,internal_id,`name`,address,lat,`long`,created_user_id) VALUES (?,?,?,?,?,?,?,?,?,?);"
	)

	address, err := c.getAddressSecondaryData(address)
	if err != nil {
		return address, err
	}

	uuid, _ := uuid.NewV4()
	address.UUID = uuid.String()

	result, err := c.conn.Exec(query, address.UUID, address.AddressType.ID, address.Origin.ID, address.User.ID, address.InternalID, address.Name, address.Address, address.Latitude, address.Longitude, address.CreatedUser.ID)
	if err != nil {
		return address, err
	}

	addressID, err := result.LastInsertId()
	if err != nil {
		return address, err
	}

	return c.getAddressByID(addressID)
}

func (c *userRepo) getAddressByUserID(userID int64) ([]entity.Address, error) {
	const (
		query = `SELECT 
									a.address_id,
									a.address_uuid,
									a.address_type_id,
									d.key address_type_key,
									d.name address_type_name,
									a.address_origin_id,
									e.key address_origin_key,
									e.name address_origin_name,
									b.user_id,
									b.user_uuid,
									b.name user_name,
									a.internal_id,
									a.name,
									a.address,
									a.lat,
									a.long,
									c.user_id created_user_id,
									c.user_uuid created_user_uuid,
									c.name created_user_name
						FROM
													tab_address a
					INNER JOIN     tab_user b
							ON          a.user_id = b.user_id
					INNER JOIN     tab_user c
							ON          a.created_user_id = c.user_id
					INNER JOIN     tab_address_type d
							ON          a.address_type_id = d.address_type_id
					INNER JOIN     tab_address_origin e
							ON          a.address_origin_id = e.address_origin_id
					WHERE
									a.user_id = ? and a.active = 1;`
	)

	var addresses []entity.Address
	rows, err := c.conn.Query(query, userID)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var address entity.Address
		err := rows.Scan(&address.ID, &address.UUID, &address.AddressType.ID, &address.AddressType.Key, &address.AddressType.Name,
			&address.Origin.ID, &address.Origin.Key, &address.Origin.Name, &address.User.ID, &address.User.UUID, &address.User.Name,
			&address.InternalID, &address.Name, &address.Address, &address.Latitude, &address.Longitude, &address.CreatedUser.ID,
			&address.CreatedUser.UUID, &address.CreatedUser.Name)
		if err != nil {
			return nil, err
		}
		addresses = append(addresses, address)
	}
	return addresses, nil
}

func (c *userRepo) getAddressByID(addressID int64) (entity.Address, error) {
	const (
		query = `SELECT 
									a.address_id,
									a.address_uuid,
									a.address_type_id,
									d.key address_type_key,
									d.name address_type_name,
									a.address_origin_id,
									e.key address_origin_key,
									e.name address_origin_name,
									b.user_id,
									b.user_uuid,
									b.name user_name,
									a.internal_id,
									a.name,
									a.address,
									a.lat,
									a.long,
									c.user_id created_user_id,
									c.user_uuid created_user_uuid,
									c.name created_user_name
						FROM
													tab_address a
					INNER JOIN     tab_user b
							ON          a.user_id = b.user_id
					INNER JOIN     tab_user c
							ON          a.created_user_id = c.user_id
					INNER JOIN     tab_address_type d
							ON          a.address_type_id = d.address_type_id
					INNER JOIN     tab_address_origin e
							ON          a.address_origin_id = e.address_origin_id
					WHERE
									a.address_id = ?;`
	)

	var address entity.Address
	row := c.conn.QueryRow(query, addressID)
	err := row.Scan(&address.ID, &address.UUID, &address.AddressType.ID, &address.AddressType.Key, &address.AddressType.Name,
		&address.Origin.ID, &address.Origin.Key, &address.Origin.Name, &address.User.ID, &address.User.UUID, &address.User.Name,
		&address.InternalID, &address.Name, &address.Address, &address.Latitude, &address.Longitude, &address.CreatedUser.ID,
		&address.CreatedUser.UUID, &address.CreatedUser.Name)
	if err != nil {
		return entity.Address{}, err
	}

	return address, nil
}

func (c *userRepo) GetAddressByUUID(addressUUID string) (entity.Address, error) {
	const (
		query = `SELECT 
									a.address_id,
									a.address_uuid,
									a.address_type_id,
									d.key address_type_key,
									d.name address_type_name,
									a.address_origin_id,
									e.key address_origin_key,
									e.name address_origin_name,
									b.user_id,
									b.user_uuid,
									b.name user_name,
									a.internal_id,
									a.name,
									a.address,
									a.lat,
									a.long,
									c.user_id created_user_id,
									c.user_uuid created_user_uuid,
									c.name created_user_name
						FROM
													tab_address a
					INNER JOIN     tab_user b
							ON          a.user_id = b.user_id
					INNER JOIN     tab_user c
							ON          a.created_user_id = c.user_id
					INNER JOIN     tab_address_type d
							ON          a.address_type_id = d.address_type_id
					INNER JOIN     tab_address_origin e
							ON          a.address_origin_id = e.address_origin_id
					WHERE
									a.address_uuid = ?;`
	)

	var address entity.Address
	row := c.conn.QueryRow(query, addressUUID)
	err := row.Scan(&address.ID, &address.UUID, &address.AddressType.ID, &address.AddressType.Key, &address.AddressType.Name,
		&address.Origin.ID, &address.Origin.Key, &address.Origin.Name, &address.User.ID, &address.User.UUID, &address.User.Name,
		&address.InternalID, &address.Name, &address.Address, &address.Latitude, &address.Longitude, &address.CreatedUser.ID,
		&address.CreatedUser.UUID, &address.CreatedUser.Name)
	if err != nil {
		return entity.Address{}, err
	}

	return address, nil
}

func (c *userRepo) getAddressSecondaryData(address entity.Address) (entity.Address, error) {
	const (
		query = `SELECT
								a.user_id,
								a.user_uuid,
								a.name user_name,
								b.user_id created_user_id,
								b.user_uuid created_user_uuid,
								b.name created_user_name,
								c.address_type_id,
								c.key address_type_key,
								c.name address_type_name,
								d.address_origin_id,
								d.key address_origin_key,
								d.name address_origin_name
						FROM
								tab_user a,
								tab_user b,
								tab_address_type c,
								tab_address_origin d
						WHERE
								a.user_uuid = ?
						AND b.user_uuid = ?
						AND c.key = ?
						AND d.key = ?;`
	)

	row := c.conn.QueryRow(query, address.User.UUID, address.CreatedUser.UUID, address.AddressType.Key, address.Origin.Key)
	err := row.Scan(&address.User.ID, &address.User.UUID, &address.User.Name, &address.CreatedUser.ID, &address.CreatedUser.UUID, &address.CreatedUser.Name, &address.AddressType.ID, &address.AddressType.Key, &address.AddressType.Name, &address.Origin.ID, &address.Origin.Key, &address.Origin.Name)
	if err != nil {
		return address, err
	}

	return address, nil
}

// GetAll returns a list of all active cards
func (c *userRepo) GetAll() (list []entity.User, err error) {
	return c.parseSet(c.conn.Query(c.getQuery() + " AND a.active = 1 "))
}

// GetByID returns a single card data by its ID
func (c *userRepo) GetByID(userID int64) (retVal entity.User, err error) {
	user, err := c.parseSet(c.conn.Query(c.getQuery()+" AND a.user_id = ? ", userID))
	if err != nil {
		return entity.User{}, err
	}

	if len(user) > 0 {
		retVal := user[0]
		retVal.Contacts, err = c.GetContacts(retVal.ID)
		if err != nil {
			return entity.User{}, err
		}

		retVal.Addresses = nil
		retVal.Addresses, err = c.getAddressByUserID(retVal.ID)
		if err != nil {
			return entity.User{}, err
		}
		return retVal, nil
	} else {
		return retVal, nil
	}
}

func toNullString(s string) sql.NullString {
	return sql.NullString{String: s, Valid: s != ""}
}

func toNullInt64(i int64) sql.NullInt64 {
	return sql.NullInt64{Int64: i, Valid: i > 0}
}

func toNullTime(date time.Time) mysql.NullTime {
	return mysql.NullTime{Time: date, Valid: !date.IsZero()}
}